table_changes table-valued function

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Returns a log of changes to a Delta Lake table with Change Data Feed enabled.

To invoke this function you need to have at least one of the following:

  • SELECT privilege on the specified table
  • Be the owner of the table
  • Have administrative privileges

Syntax

table_changes ( table_str, start [, end ] )

Arguments

  • table_str: A STRING literal representing the optionally qualified name of the table.

  • start: A BIGINT or TIMESTAMP literal, representing the first version or timestamp of change to return.

    The function returns an error if start is:

    • A version earlier than the earliest available version of the table.
    • A version later than the latest version of the table.
    • A timestamp later than the latest commit timestamp.

  • end: An optional BIGINT or TIMESTAMP literal, representing the last version or timestamp of change to return. If not specified, all changes from start up to the current change are returned.

    The function returns an error if end is:

    • A version later than the latest version of the table, or a timestamp later than the latest commit timestamp.
    • A version earlier than the earliest available version of the table, or a timestamp earlier than the earliest available commit timestamp.

    To return all changes from start up to the latest version, omit end.

Returns

A table including all columns of the table identified in table_str, plus the following columns:

  • _change_type STRING NOT NULL

    Specifies the change: delete, insert, update_preimage, or update_postimage

  • _commit_version BIGINT NOT NULL

    Specifies the commit version of the table associated with the change.

  • _commit_timestamp TIMESTAMP NOT NULL

    Specifies the commit timestamp associated with the change.

If table_str does not represent a qualified table name the name is qualified with the value of current_schema. If the table name contains spaces or dots use back quotes within the string to quote that part of the name.

Examples

-- Create a Delta table with Change Data Feed;
> CREATE TABLE myschema.t(c1 INT, c2 STRING) TBLPROPERTIES(delta.enableChangeDataFeed=true);

-- Modify the table
> INSERT INTO myschema.t VALUES (1, 'Hello'), (2, 'World');
> INSERT INTO myschema.t VALUES (3, '!');
> UPDATE myschema.t SET c2 = upper(c2) WHERE c1 < 3;
> DELETE FROM myschema.t WHERE c1 = 3;

-- Show the history of table change events
> DESCRIBE HISTORY myschema.t;
 version timestamp                    userId           userName      operation    operationParameters                                            ...
       4 2022-09-01T18:32:35.000+0000 6167625779053302 alf@melmak.et DELETE       {"predicate":"[\"(spark_catalog.myschema.t.c1 = 3)\"]"}
       3 2022-09-01T18:32:32.000+0000 6167625779053302 alf@melmak.et UPDATE       {"predicate":"(c1#3195878 < 3)"}
       2 2022-09-01T18:32:28.000+0000 6167625779053302 alf@melmak.et WRITE        {"mode":"Append","partitionBy":"[]"}
       1 2022-09-01T18:32:26.000+0000 6167625779053302 alf@melmak.et WRITE        {"mode":"Append","partitionBy":"[]"}
       0 2022-09-01T18:32:23.000+0000 6167625779053302 alf@melmak.et CREATE TABLE {"isManaged":"true","description":null,"partitionBy":"[]","properties":"{\"delta.enableChangeDataFeed\":\"true\"}"}

-- Show the change table feed using a the commit timestamp retrieved from the history.
> SELECT * FROM table_changes('`myschema`.`t`', 2);
 c1 c2     _change_type    _commit_version _commit_timestamp
  3 !      insert                        2 2022-09-01T18:32:28.000+0000
  2 WORLD  update_postimage              3 2022-09-01T18:32:32.000+0000
  2 World  update_preimage               3 2022-09-01T18:32:32.000+0000
  1 Hello  update_preimage               3 2022-09-01T18:32:32.000+0000
  1 HELLO  update_postimage              3 2022-09-01T18:32:32.000+0000
  3 !      delete                        4 2022-09-01T18:32:35.000+0000

-- Show the ame change table feed using a point in time.
> SELECT * FROM table_changes('`myschema`.`t`', '2022-09-01T18:32:27.000+0000') ORDER BY _commit_version;
 c1 c2     _change_type    _commit_version _commit_timestamp
  3 !      insert                        2 2022-09-01T18:32:28.000+0000
  2 WORLD  update_postimage              3 2022-09-01T18:32:32.000+0000
  2 World  update_preimage               3 2022-09-01T18:32:32.000+0000
  1 Hello  update_preimage               3 2022-09-01T18:32:32.000+0000
  1 HELLO  update_postimage              3 2022-09-01T18:32:32.000+0000
  3 !      delete                        4 2022-09-01T18:32:35.000+0000
  • Last updated on