Trabaja con el historial de cambios

El historial de cambios de BigQuery te permite hacer un seguimiento del historial de cambios de una tabla de BigQuery. El historial de cambios de una tabla se expone como una función con valor de tabla (TVF) de SQL que te muestra tipos específicos de cambios realizados durante un período específico. Esta función te permite procesar cambios incrementales en una tabla. Comprender los cambios que se realizaron en una tabla puede ayudarte a mantener una réplica de la tabla fuera de BigQuery y, a su vez, evitar las copias costosas.

Permisos necesarios

Para ver el historial de cambios en una tabla, necesitas el permiso bigquery.tables.getData en esa tabla. Las siguientes funciones predefinidas de la administración de identidades y accesos (IAM) incluyen este permiso:

roles/bigquery.dataViewer
roles/bigquery.dataEditor
roles/bigquery.dataOwner
roles/bigquery.admin

Si una tabla tiene o tuvo políticas de acceso a nivel de fila, solo un administrador de tabla puede acceder a los datos históricos de la tabla. El permiso bigquery.rowAccessPolicies.overrideTimeTravelRestrictions es obligatorio en la tabla y se incluye en la función de IAM predefinida roles/bigquery.admin.

Si una tabla tiene seguridad a nivel de columnas, solo puedes ver el historial de cambios en las columnas a las que tienes acceso.

`APPENDS` TVF

El TVF APPENDS muestra una tabla de todas las filas adjuntas a una tabla durante un período determinado. Las siguientes operaciones agregan filas al historial de cambios APPENDS:

Declaración DDL CREATE TABLE
Declaración DML INSERT
Declaración DML MERGE
Cargar datos en BigQuery
Transferencia de transmisión

Sintaxis

APPENDS(
  TABLE table,
  start_timestamp DEFAULT NULL,
  end_timestamp DEFAULT NULL)

table: el nombre de la tabla de BigQuery. No puede ser una vista, una subconsulta, una tabla externa, una vista materializada o una tabla comodín. Este argumento debe estar precedido por la palabra TABLE.
start_timestamp: una TIMESTAMP que indica la primera hora a la que se incluye un cambio en el resultado. Si es NULL, se muestran todos los cambios desde la creación de la tabla. En cambio, si se crea la tabla después de start_timestamp, se usa el tiempo de creación real de la tabla. Si el tiempo es anterior a lo que permite el viaje en el tiempo, se muestra un error. Para las tablas estándar, este período es de siete días, pero puedes configurar el período de viaje en el tiempo para que sea menor.
end_timestamp: una TIMESTAMP que indica la última hora, exclusiva, en la que se incluyó un cambio en el resultado. Si es NULL, se incluyen todos los cambios realizados hasta el inicio de la consulta.

Valor de muestra

La TVF APPENDS muestra una tabla con las siguientes columnas:

Todas las columnas de la tabla de entrada en el momento en que se ejecuta la consulta. Si una columna se agrega después de end_timestamp, aparece con valores NULL propagados en cualquier fila que se insertó antes de agregar la columna.
_CHANGE_TYPE: una STRING que indica el tipo de cambio que produjo la fila. Para APPENDS, el único valor admitido es INSERT.
_CHANGE_TIMESTAMP: un TIMESTAMP que indica el tiempo de confirmación de la transacción que realizó el cambio.

Detalles

Los registros de las filas insertadas se conservan incluso si más tarde se borran esos datos. Las eliminaciones no se reflejan en la TVF APPENDS. Si se copia una tabla, llamar a la TVF APPENDS en la tabla copiada muestra cada fila como se insertó cuando se creó la tabla. Si una fila se modifica debido a una operación UPDATE, no tendrá efecto.

Ejemplos

En este ejemplo, se muestra el historial de cambios que muestra APPENDS mientras se realizan varios cambios en una tabla llamada Produce. Es posible que este ejemplo no funcione si se completa durante un período más largo que tu período de viaje en el tiempo. Primero, crea la tabla.

CREATE TABLE mydataset.Produce (product STRING, inventory INT64) AS (
  SELECT "apples" AS product, 10 AS inventory);

A continuación, inserta dos filas en la tabla.

INSERT INTO mydataset.Produce
VALUES
  ("bananas", 20),
  ("carrots", 30);

Visualiza el historial de cambios completo de los anexos. Usa valores NULL para obtener el historial completo dentro de la ventana de viaje en el tiempo.

SELECT
  product,
  inventory,
  _CHANGE_TYPE AS change_type,
  _CHANGE_TIMESTAMP AS change_time
FROM
  APPENDS(TABLE mydataset.Produce, NULL, NULL);

El resultado es similar a este:

+---------+-----------+-------------+--------------------------------+
| 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 |
+---------+-----------+-------------+--------------------------------+

A continuación, agrega una columna, inserta una fila nueva de valores, actualiza el inventario y borra la fila “bananas”.

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";

Ver la tabla nueva

SELECT * FROM mydataset.Produce;

+---------+-----------+--------+
| product | inventory | color  |
+---------+-----------+--------+
| apples  | 15        | NULL   |
| carrots | 35        | NULL   |
| grapes  | 45        | purple |
+---------+-----------+--------+

Visualiza el historial de cambios completo de los anexos.

SELECT
  product,
  inventory,
  color,
  _CHANGE_TYPE AS change_type,
  _CHANGE_TIMESTAMP AS change_time
FROM
  APPENDS(TABLE mydataset.Produce, NULL, NULL);