Cómo actualizar datos de tablas particionadas mediante declaraciones DML

Esta página proporciona una descripción general del soporte del lenguaje de manipulación de datos (DML) para tablas particionadas.

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

Tablas utilizadas en ejemplos

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

mytable: una tabla particionada en 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 para cuya partición se utilizó la columna ts TIMESTAMP

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

Cómo insertar datos

Se utiliza una declaración DML INSERT para agregar filas a una tabla particionada.

Cómo insertar datos en tablas particionadas en 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 utilizando 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 utilizar 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”

Cómo insertar 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 una tabla particionada mycolumntable mediante la selección de datos desde mytable2 (una tabla no particionada).

INSERT INTO PROJECT_ID.DATASET.mycolumntable (ts, field1) SELECT ts, id from PROJECT_ID.DATASET.mytable2

Cómo borrar datos

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

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

La siguiente declaración DELETE borra todas las filas de la partición del 1 de junio de 2017 (“2017-06-01”) o mytable, donde field1 es igual a 21. La referencia a la partición se realiza utilizando la pseudocolumna _PARTITIONTIME.

DELETE PROJECT_ID.DATASET.mytable WHERE field1 = 21 AND _PARTITIONTIME = “2017-05-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”) o mycolumntable, donde field1 es igual a 21.

DELETE PROJECT_ID.DATASET.mycolumntable WHERE field1 = 21 AND DATE(ts) = “2017-05-01”

Cómo actualizar datos

Se utiliza una declaración DML 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 de la partición del 1 de mayo de 2017 (“2017-05-01”) de mytable, donde 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, donde 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

Cómo usar una declaración MERGE

Se utiliza una declaración DML MERGE para combinar las operaciones INSERT, UPDATE y DELETE de una tabla particionada en una sola declaración y ejecutarlas de manera atómica.

Cómo reducir particiones al usar una declaración MERGE

Cuando ejecutas una declaración MERGE contra una tabla particionada, puedes limitar las particiones involucradas en la declaración utilizando la pseudocolumna _PARTITIONTIME (para tablas particionadas en tiempo de transferencia) o utilizando la columna de fecha o de marca de tiempo (para tablas particionadas). Las particiones reducidas disminuyen los costos y mejoran el rendimiento de las consultas.

Puedes utilizar las condiciones de las particiones reducidas en los siguientes sitios: en un filtro de subconsulta, en un filtro search_condition o en un filtro merge_condition.

Cada uno de los ejemplos a continuación consulta una tabla particionada en tiempo de transmisión utilizando la pseudocolumna _PARTITIONTIME.

Cómo utilizar una subconsulta para filtrar los datos fuente

Puedes utilizar un filtro en una subconsulta para reducir particiones. Por ejemplo, en la siguiente declaración MERGE, solo se escanean las filas en la partición '2018-01-01' de la tabla fuente.

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

Cómo usar un filtro en search_condition de when_clause

El objetivo del optimizador de consultas es utilizar un filtro en una search_condition para reducir las particiones. Por ejemplo, en la siguiente declaración MERGE, solo se escanean las filas en las siguientes particiones de la tabla meta: '2018-01-01', '2018-01-02' y '2018-01-03'.

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

En el siguiente ejemplo, la cláusula WHEN NOT MATCHED BY SOURCE necesita todos los datos de la tabla meta. Como resultado, todas las particiones se escanean y se cobra por los bytes leídos en todas ellas.

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

En general, cuando usas las cláusulas WHEN NOT MATCHED y WHEN NOT MATCHED BY SOURCE juntas, BigQuery asume que existe una FULL OUTER JOIN entre las tablas fuente y meta. Por lo general, las particiones no se pueden reducir en una FULL OUTER JOIN. Sin embargo, si se usa un predicado falso constante, se puede usar la condición del filtro para reducir la partición. La siguiente consulta utiliza la reducción de la partición para escanear solo la partición '2018-01-01', tanto en la tabla fuente como en la meta.

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

Cómo usar un filtro en una merge_condition

El objetivo del optimizador de consultas es utilizar un filtro en una merge_condition para reducir las particiones. Por ejemplo, la siguiente consulta puede escanear solo la partición '2018-01-01', tanto en la tabla fuente como en la meta.

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

En este ejemplo, merge_condition se usa como predicado para unir las tablas fuente y meta. El optimizador de consultas puede o no ser capaz de usar el despliegue del predicado (según el tipo de unión).

En el siguiente ejemplo, la declaración MERGE no permite la reducción de la partición, ya que el filtro de esta es un predicado en una condición de unión que no puede aplicarse directamente en la tabla.

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

Limitaciones

Para obtener información sobre las limitaciones de DML, consulta Limitaciones en la página Lenguaje de manipulación de datos.

Cuotas

Para obtener información sobre las cuotas de DML, consulta 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.

Próximos pasos

¿Te sirvió esta página? Envíanos tu opinión:

Enviar comentarios sobre…

¿Necesitas ayuda? Visita nuestra página de asistencia.