Actualiza datos de tablas particionadas con DML

En esta página, se proporciona una descripción general de la asistencia del lenguaje de manipulación de datos (DML) para tablas particionadas.

Para obtener más información sobre DML, consulta:

Tablas usadas en ejemplos

Las siguientes definiciones de esquemas JSON representan las tablas utilizadas en los ejemplos de esta página.

mytable: una tabla particionada por tiempo de transferencia

    [
      {"name": "field1", "type": "INTEGER"},
      {"name": "field2", "type": "STRING"}
    ]

mytable2: una tabla estándar (no particionada)

    [
      {"name": "id", "type": "INTEGER"},
      {"name": "ts", "type": "TIMESTAMP"}
    ]

mycolumntable: una tabla particionada con el uso de la columna ts TIMESTAMP

    [
      {"name": "field1", "type": "INTEGER"},
      {"name": "field2", "type": "STRING"}
      {"name": "field3", "type": "BOOLEAN"}
      {"name": "ts", "type": "TIMESTAMP"}
    ]

En los ejemplos en los que aparece COLUMN_ID, reemplázalo por el nombre de la columna en la que deseas operar.

Inserta datos

Usa una declaración DMLINSERT para agregar filas a una tabla particionada.

Inserta datos en tablas particionadas por tiempo de transferencia

Cuando usas una declaración DML para agregar filas a una tabla particionada en tiempo de transferencia, puedes especificar la partición a la que se deben agregar las filas. La referencia a la partición se realiza con la pseudocolumna _PARTITIONTIME.

Por ejemplo, la siguiente declaración INSERT agrega una fila a la partición del 1 de mayo de 2017 de mytable - “2017-05-01”.

INSERT INTO
  project_id.dataset.mytable (_PARTITIONTIME,
    field1,
    field2)
SELECT
  TIMESTAMP("2017-05-01"),
  1,
  "one"

Solo se pueden usar las marcas de tiempo que corresponden a los límites de fecha exacta. Por ejemplo, la siguiente declaración DML muestra un error:

INSERT INTO
  project_id.dataset.mytable (_PARTITIONTIME,
    field1,
    field2)
SELECT
  TIMESTAMP("2017-05-01 21:30:00"),
  1,
  "one"

Inserta datos en tablas particionadas

La inserción de datos en una tabla particionada con DML se realiza de la misma manera que la inserción de datos en una tabla no particionada.

Por ejemplo, la siguiente declaración INSERT agrega filas a la tabla particionada mycolumntable mediante la selección de datos de mytable2 (una tabla no particionada).

INSERT INTO
  project_id.dataset.mycolumntable (ts,
    field1)
SELECT
  ts,
  id
FROM
  project_id.dataset.mytable2

Borra datos

Usa una declaración DML DELETE para borrar filas de una tabla particionada.

Borra datos en tablas particionadas por tiempo de transferencia

La siguiente declaración DELETE borra todas las filas de la partición del 1 de junio de 2017 ("2017-06-01") de mytable, en la que field1 es igual a 21. La referencia a la partición se realiza con la pseudocolumna _PARTITIONTIME.

DELETE
  project_id.dataset.mytable
WHERE
  field1 = 21
  AND _PARTITIONTIME = "2017-06-01"

Cómo borrar datos en tablas particionadas

La eliminación de datos de una tabla particionada con DML se realiza de la misma manera que la eliminación de datos de una tabla no particionada.

Por ejemplo, la siguiente declaración DELETE borra todas las filas de la partición del 1 de junio de 2017 ("2017-06-01") de mycolumntable, en la que field1 es igual a 21.

DELETE
  project_id.dataset.mycolumntable
WHERE
  field1 = 21
  AND DATE(ts) = "2017-06-01"

Usa DML DELETE para borrar particiones

Si una declaración DELETE apta abarca todas las filas de una partición, BigQuery quita toda la partición. Esta eliminación se realiza sin analizar bytes ni consumir ranuras. En el siguiente ejemplo de una declaración DELETE, se abarca la partición completa de un filtro en la seudocolumna _PARTITIONDATE:

DELETE mydataset.mytable
WHERE _PARTITIONDATE IN ('2076-10-07', '2076-03-06');

Descalificaciones comunes

Es posible que las consultas con las siguientes características no se beneficien de la optimización:

La elegibilidad para la optimización puede variar con el tipo de partición, los metadatos de almacenamiento subyacentes y los predicados de filtro. Como práctica recomendada, realiza una prueba de validación para verificar que la consulta genere 0 bytes procesados.

Transacción de varias declaraciones

Esta optimización funciona dentro de una transacción de varias declaraciones. En el siguiente ejemplo de consulta, se reemplaza una partición por datos de otra tabla en una sola transacción, sin analizar la partición para la declaración DELETE.

DECLARE REPLACE_DAY DATE;
BEGIN TRANSACTION;

-- find the partition which we want to replace
SET REPLACE_DAY = (SELECT MAX(d) FROM mydataset.mytable_staging);

-- delete the entire partition from mytable
DELETE FROM mydataset.mytable WHERE part_col = REPLACE_DAY;

-- insert the new data into the same partition in mytable
INSERT INTO mydataset.mytable
SELECT * FROM mydataset.mytable_staging WHERE part_col = REPLACE_DAY;

COMMIT TRANSACTION;

Actualiza datos

Usa una declaración UPDATE para actualizar filas en una tabla particionada.

Cómo actualizar datos en tablas particionadas en tiempo de transmisión

La siguiente declaración UPDATE mueve las filas de una partición a otra. Las filas en la partición del 1 de mayo de 2017 (“2017-05-01”) de mytable, en la que field1 es igual a 21, se mueven a la partición del 1 de junio de 2017 (“2017-06-01”).

UPDATE
  project_id.dataset.mytable
SET
  _PARTITIONTIME = "2017-06-01"
WHERE
  _PARTITIONTIME = "2017-05-01"
  AND field1 = 21

Cómo actualizar datos en tablas particionadas

La actualización de datos en una tabla particionada con DML se realiza de la misma manera que la actualización de datos en una tabla no particionada. Por ejemplo, la siguiente declaración UPDATE mueve las filas de una partición a otra. Las filas de la partición del 1 de mayo de 2017 (“2017-05-01”) de mytable, en la que field1 es igual a 21, se mueven a la partición del 1 de junio de 2017 (“2017-06-01”).

UPDATE
  project_id.dataset.mycolumntable
SET
  ts = "2017-06-01"
WHERE
  DATE(ts) = "2017-05-01"
  AND field1 = 21

DML en tablas particionadas por hora, mes y año

Puedes usar declaraciones DML para modificar una tabla particionada por hora, mes o año. Proporciona el rango de hora, mes o año de las fechas/marcas de tiempo/fechas relevantes; como se muestra en el siguiente ejemplo para tablas particionadas por mes:

    bq query --nouse_legacy_sql 'DELETE FROM my_dataset.my_table WHERE
    TIMESTAMP_TRUNC(ts_column, MONTH) = "2020-01-01 00:00:00";'

O bien otro ejemplo para tablas particionadas con la columna DATETIME:

    bq query --nouse_legacy_sql 'DELETE FROM my_dataset.my_table WHERE
    dt_column BETWEEN DATETIME("2020-01-01")
    AND DATETIME("2020-05-01");'

Usa una declaración MERGE

Usa una declaración DML MERGE para combinar operaciones INSERT, UPDATE y DELETE de una tabla particionada en una sola declaración y realizarlas atómicamente.

Reduce particiones cuando usas una declaración MERGE

Cuando ejecutas una declaración MERGE en una tabla particionada, puedes limitar las particiones que se analizarán si incluyes la columna de partición en un filtro de subconsulta, un filtro search_condition o un filtro merge_condition. La reducción puede ocurrir cuando se analiza la tabla de origen o la de destino, o ambas.

En cada uno de los ejemplos a continuación, se realiza una consulta a una tabla particionada por tiempo de transferencia, a través de la pseudocolumna _PARTITIONTIME como filtro.

Usa una subconsulta para filtrar los datos fuente

En la siguiente declaración MERGE, la subconsulta en la cláusula USING filtra la pseudocolumna _PARTITIONTIME en la tabla de origen.

MERGE dataset.target T
USING (SELECT * FROM dataset.source WHERE _PARTITIONTIME = '2018-01-01') S
ON T.COLUMN_ID = S.COLUMN_ID
WHEN MATCHED THEN
  DELETE

Si se observa el plan de ejecución de consultas, la subconsulta se ejecuta primero. Solo se analizan las filas en la partición '2018-01-01' de la tabla de origen. Esta es la etapa relevante en el plan de consultas:

READ $10:name, $11:_PARTITIONTIME
FROM temp.source
WHERE equal($11, 1514764800.000000000)

Usa un filtro en search_condition de una when_clause

Si una search_condition contiene un filtro, el optimizador de consultas intenta reducir las particiones. Por ejemplo, en la siguiente declaración MERGE, cada cláusula WHEN MATCHED y WHEN NOT MATCHED contiene un filtro en la seudocolumna _PARTITIONTIME.

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-01' THEN
  UPDATE SET COLUMN_ID = S.COLUMN_ID
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-02' THEN
  UPDATE SET COLUMN_ID = S.COLUMN_ID + 10
WHEN NOT MATCHED BY SOURCE AND T._PARTITIONTIME = '2018-01-03' THEN
  DELETE

Durante la etapa de unión, solo se analizan las siguientes particiones en la tabla de destino: '2018-01-01', '2018-01-02' y '2018-01-03', es decir, la unión de todos los filtros search_condition.

A partir del plan de ejecución de consultas:

READ
$1:COLUMN_ID, $2:_PARTITIONTIME, $3:$file_temp_id, $4:$row_temp_id
FROM temp.target
WHERE or(equal($2, 1514764800.000000000), equal($2, 1514851200.000000000), equal($2, 1514937600.000000000))

Sin embargo, en el siguiente ejemplo, la cláusula WHEN NOT MATCHED BY SOURCE no tiene una expresión de filtro:

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID
WHEN MATCHED AND T._PARTITIONTIME = '2018-01-01' THEN
  UPDATE SET COLUMN_ID = S.COLUMN_ID
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET COLUMN_ID = COLUMN_ID + 1

Esta consulta debe analizar toda la tabla de destino para calcular la cláusula WHEN NOT MATCHED BY SOURCE. Como resultado, no se reducen particiones.

Usa un predicado falso constante en una merge_condition

Si usas las cláusulas WHEN NOT MATCHED y WHEN NOT MATCHED BY SOURCE juntas, BigQuery suele realizar una unión externa completa, que no se puede reducir. Sin embargo, si la condición de combinación usa un predicado falso constante, BigQuery puede usar la condición del filtro para reducir la partición. Para obtener más información sobre el uso de predicados falsos constantes, consulta la descripción de la cláusula merge_condition en la documentación de la declaración MERGE.

En el siguiente ejemplo, se analiza solo la partición '2018-01-01' en las tablas de destino y de origen.

MERGE dataset.target T
USING dataset.source S
ON FALSE
WHEN NOT MATCHED AND _PARTITIONTIME = '2018-01-01' THEN
  INSERT(COLUMN_ID) VALUES(COLUMN_ID)
WHEN NOT MATCHED BY SOURCE AND _PARTITIONTIME = '2018-01-01' THEN
  DELETE

Usa un filtro en una merge_condition

El objetivo del optimizador de consultas es usar un filtro en una merge_condition para reducir las particiones. El optimizador de consultas podría enviar el predicado a la etapa de análisis de tabla o no hacerlo, según el tipo de unión.

En el siguiente ejemplo, merge_condition se usa como predicado para unir las tablas de origen y de destino. El optimizador de consultas puede reducir este predicado cuando analiza ambas tablas. Como resultado, la consulta solo analiza la partición '2018-01-01' en las tablas de destino y de origen.

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID AND
  T._PARTITIONTIME = '2018-01-01' AND
  S._PARTITIONTIME = '2018-01-01'
WHEN MATCHED THEN
  UPDATE SET COLUMN_ID = NEW_VALUE

En el siguiente ejemplo, merge_condition no contiene un predicado para la tabla de origen, por lo que no se puede reducir la partición en la tabla de origen. La instrucción contiene un predicado para la tabla de destino, pero usa una cláusula WHEN NOT MATCHED BY SOURCE en lugar de una cláusula WHEN MATCHED. Esto significa que la consulta debe analizar toda la tabla de destino en busca de filas que no coincidan.

MERGE dataset.target T
USING dataset.source S
ON T.COLUMN_ID = S.COLUMN_ID AND T._PARTITIONTIME = '2018-01-01'
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET COLUMN_ID = NEW_VALUE

Limitaciones

Para obtener información sobre las limitaciones de DML, consulta las limitaciones en la página de referencia de DML.

Cuotas

Para obtener información sobre las cuotas de DML, consulta las declaraciones DML en la página Cuotas y límites.

Precios

Para obtener información sobre los precios de DML, consulta Precios de DML para tablas particionadas.

Seguridad de las tablas

Para controlar el acceso a las tablas en BigQuery, consulta Introducción a los controles de acceso a tablas.

¿Qué sigue?