Work with change history
BigQuery change history lets you track the history of changes to a BigQuery table. The change history for a table is exposed as a SQL table-valued function (TVF) that shows you particular types of changes made during a specified time range. This feature lets you process incremental changes made to a table. Understanding what changes have been made to a table can help you do things like incrementally maintain a table replica outside of BigQuery while avoiding costly copies.
Required permissions
To view the change history on a table, you need the bigquery.tables.getData
permission on that table. The following predefined Identity and Access Management (IAM)
roles include this permission:
roles/bigquery.dataViewer
roles/bigquery.dataEditor
roles/bigquery.dataOwner
roles/bigquery.admin
If a table has, or has had,
row-level access policies, then only
a table administrator can access historical data for the table. The
bigquery.rowAccessPolicies.overrideTimeTravelRestrictions
permission is
required on the table and is included in the predefined roles/bigquery.admin
IAM role.
If a table has column-level security, you can only view the change history on the columns that you have access to.
APPENDS
TVF
The APPENDS
TVF returns a table of all rows appended to a table for a given
time range. The following operations add rows to the APPENDS
change
history:
CREATE TABLE
DDL statementINSERT
DML statementMERGE
DML statement- Loading data into BigQuery
- Streaming ingestion
Syntax
APPENDS( TABLE table, start_timestamp DEFAULT NULL, end_timestamp DEFAULT NULL)
table
: the BigQuery table name. It can not be a view, subquery, external table, materialized view, or wildcard table. This argument must be preceded by the wordTABLE
.start_timestamp
: aTIMESTAMP
indicating the earliest time at which a change is included in the output. If it isNULL
, all changes since the table creation are returned. If the table is created afterstart_timestamp
, the actual table creation time is used instead. If the time is earlier than allowed by time travel, an error is returned. For standard tables, this window is seven days, but you may configure the time travel window to be less than that.end_timestamp
: aTIMESTAMP
indicating the latest time, exclusive, at which a change is included in the output. If it isNULL
, all changes made until the start of the query are included.
Return value
The APPENDS
TVF returns a table with the following columns:
- All columns of the input table at the time the query is run. If a column is
added after
end_timestamp
, it appears withNULL
values populated in any rows that were inserted before the addition of the column. _CHANGE_TYPE
: aSTRING
indicating the type of change that produced the row. ForAPPENDS
, the only supported value isINSERT
._CHANGE_TIMESTAMP
: aTIMESTAMP
indicating the commit time of the transaction that made the change.
Details
Records of inserted rows persist even if that data is later deleted. Deletions
are not reflected in the APPENDS
TVF. If a table
is copied, calling the APPENDS
TVF on the copied table returns every row
as inserted at the time of table creation. If a row is modified due to an
UPDATE
operation there is no effect.
Examples
This example shows the change history returned by APPENDS
as various
changes are made to a table called Produce
. This example might not work if
completed over a duration longer than your
time travel window.
First, create the table.
CREATE TABLE mydataset.Produce (product STRING, inventory INT64) AS ( SELECT "apples" AS product, 10 AS inventory);
Next, insert two rows into the table.
INSERT INTO mydataset.Produce VALUES ("bananas", 20), ("carrots", 30);
View the full change history of appends. Use NULL
values to get the full
history within the time travel window.
SELECT product, inventory, _CHANGE_TYPE AS change_type, _CHANGE_TIMESTAMP AS change_time FROM APPENDS(TABLE mydataset.Produce, NULL, NULL);
The output is similar to the following:
+---------+-----------+-------------+--------------------------------+ | product | inventory | change_type | change_time | +---------+-----------+-------------+--------------------------------+ | apples | 10 | INSERT | 2022-04-15 20:06:00.488000 UTC | | bananas | 20 | INSERT | 2022-04-15 20:06:08.490000 UTC | | carrots | 30 | INSERT | 2022-04-15 20:06:08.490000 UTC | +---------+-----------+-------------+--------------------------------+
Next, add a column, insert a new row of values, update the inventory, and delete the "bananas" row.
ALTER TABLE mydataset.Produce ADD COLUMN color STRING; INSERT INTO mydataset.Produce VALUES ("grapes", 40, "purple"); UPDATE mydataset.Produce SET inventory = inventory + 5 WHERE TRUE; DELETE mydataset.Produce WHERE product = "bananas";
View the new table.
SELECT * FROM mydataset.Produce;
+---------+-----------+--------+ | product | inventory | color | +---------+-----------+--------+ | apples | 15 | NULL | | carrots | 35 | NULL | | grapes | 45 | purple | +---------+-----------+--------+
View the full change history of appends.
SELECT product, inventory, color, _CHANGE_TYPE AS change_type, _CHANGE_TIMESTAMP AS change_time FROM APPENDS(TABLE mydataset.Produce, NULL, NULL);
The output is similar to the following:
+---------+-----------+--------+-------------+--------------------------------+ | product | inventory | color | change_type | change_time | +---------+-----------+--------+-------------+--------------------------------+ | apples | 10 | NULL | INSERT | 2022-04-15 20:06:00.488000 UTC | | bananas | 20 | NULL | INSERT | 2022-04-15 20:06:08.490000 UTC | | carrots | 30 | NULL | INSERT | 2022-04-15 20:06:08.490000 UTC | | grapes | 40 | purple | INSERT | 2022-04-15 20:07:45.751000 UTC | +---------+-----------+--------+-------------+--------------------------------+
The inventory
column displays the values that were set when the rows were
originally inserted into to the table. It does not show the changes from the
UPDATE
statement. The row with information on bananas is still present since
the APPENDS
TVF only captures additions to tables, not deletions.
Limitations
Change history is subject to the following limitations:
- You can only view information about appends, not updates or deletions.
- The data is limited to the time travel window of the table.