Actualizar datos de tablas con particiones mediante DML

En esta página se ofrece una descripción general de la compatibilidad con el lenguaje de manipulación de datos (DML) en las tablas particionadas.

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

Tablas usadas en los ejemplos

Las siguientes definiciones de esquema JSON representan las tablas que se usan en los ejemplos de esta página.

mytable: una tabla con particiones por hora de ingestión 

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

mytable2: una tabla estándar (sin particiones) 

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

mycolumntable: una tabla con particiones que se ha particionado mediante 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, sustitúyelo por el nombre de la columna en la que quieras realizar la operación.

Insertar datos

Para añadir filas a una tabla con particiones, usa una declaración de DML INSERT.

Insertar datos en tablas con particiones por hora de ingestión

Cuando usas una instrucción DML para añadir filas a una tabla con particiones por hora de ingestión, puedes especificar la partición a la que se deben añadir las filas. Haces referencia a la partición mediante la pseudocolumna _PARTITIONTIME.

Por ejemplo, la siguiente instrucción INSERT añade una fila a la partición del 1 de mayo del 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 correspondan a límites de fecha exactos. Por ejemplo, la siguiente instrucción DML devuelve un error:

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

Insertar datos en tablas con particiones

Insertar datos en una tabla con particiones mediante DML es lo mismo que insertarlos en una tabla sin particiones.

Por ejemplo, la siguiente instrucción INSERT añade filas a la tabla con particiones mycolumntable seleccionando datos de mytable2 (una tabla sin particiones).

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

Eliminar datos

Para eliminar filas de una tabla con particiones, se usa una declaración de DML DELETE.

Eliminar datos de tablas con particiones por hora de ingestión

La siguiente instrucción DELETE elimina todas las filas de la partición del 1 de junio del 2017 ("2017-06-01") de mytable donde field1 es igual a 21. Para hacer referencia a la partición, usa la pseudocolumna _PARTITIONTIME.

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

Eliminar datos de tablas con particiones

Eliminar datos de una tabla con particiones mediante DML es lo mismo que eliminar datos de una tabla sin particiones.

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

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

Usar la instrucción DELETE de DML para eliminar particiones

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

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

Descalificaciones habituales

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

La idoneidad para la optimización puede variar en función del tipo de partición, los metadatos de almacenamiento subyacentes y los predicados de filtro. Como práctica recomendada, haz una prueba de funcionamiento para verificar que la consulta da como resultado 0 bytes procesados.

Transacción con varias instrucciones

Esta optimización funciona en una transacción con varias instrucciones. En el siguiente ejemplo de consulta se sustituye una partición por datos de otra tabla en una sola transacción, sin analizar la partición para la instrucció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;

Actualizar datos

Puedes usar una declaración UPDATE para actualizar filas de una tabla con particiones.

Actualizar datos en tablas con particiones por hora de ingestión

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

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

Actualizar datos en tablas con particiones

Actualizar datos de una tabla con particiones mediante DML es lo mismo que actualizar datos de una tabla sin particiones. Por ejemplo, la siguiente instrucción UPDATE mueve filas de una partición a otra. Las filas de la partición del 1 de mayo del 2017 (“2017-05-01”) de mytable en las que field1 es igual a 21 se mueven a la partición del 1 de junio del 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 con particiones por hora, mes y año

Puedes usar instrucciones DML para modificar una tabla con particiones por hora, mes o año. Indica el intervalo de horas, meses o años de las fechas, marcas de tiempo o fechas y horas pertinentes, como en el siguiente ejemplo de tablas con particiones mensuales:

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

Otro ejemplo de tablas con particiones por 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");'

Usar una instrucción MERGE

Puedes usar una declaración MERGE de DML para combinar las operaciones INSERT, UPDATE y DELETE de una tabla con particiones en una sola declaración y ejecutarlas de forma atómica.

Eliminar particiones al usar una instrucción MERGE

Cuando ejecutas una instrucción MERGE en una tabla particionada, puedes limitar las particiones que se analizan incluyendo la columna de partición en un filtro de subconsulta, en un filtro search_condition o en un filtro merge_condition. La poda puede producirse al analizar la tabla de origen, la de destino o ambas.

En cada uno de los ejemplos siguientes se consulta una tabla con particiones por hora de ingestión usando la pseudocolumna _PARTITIONTIME como filtro.

Usar una subconsulta para filtrar datos de origen

En la siguiente instrucción MERGE, la subconsulta de la cláusula USING filtra la pseudocolumna _PARTITIONTIME de 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 observamos el plan de ejecución de la consulta, la subconsulta se ejecuta primero. Solo se analizan las filas de la partición '2018-01-01' de la tabla de origen. Esta es la fase correspondiente del plan de consulta:

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

Usar un filtro en el search_condition de un when_clause

Si un search_condition contiene un filtro, el optimizador de consultas intenta eliminar particiones. Por ejemplo, en la siguiente instrucción MERGE, cada cláusula WHEN MATCHED y WHEN NOT MATCHED contiene un filtro en la pseudocolumna _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 fase de unión, solo se analizan las siguientes particiones de 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.

En el plan de ejecución de la consulta:

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. Por lo tanto, no se elimina ninguna partición.

Usar un predicado falso constante en un merge_condition

Si usa 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 podar. Sin embargo, si la condición de combinación usa un predicado constante falso, BigQuery puede usar la condición de filtro para eliminar particiones. Para obtener más información sobre el uso de predicados constantes falsos, consulta la descripción de la cláusula merge_condition en la documentación de la instrucción MERGE.

En el siguiente ejemplo, solo se analiza la partición '2018-01-01' de 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

Usar un filtro en una merge_condition

El optimizador de consultas intenta usar un filtro en un merge_condition para eliminar particiones. El optimizador de consultas puede o no insertar el predicado en la fase de análisis de la tabla, en función del tipo de combinación.

En el siguiente ejemplo, se usa merge_condition como predicado para unir las tablas de origen y de destino. El optimizador de consultas puede insertar este predicado cuando analiza ambas tablas. Como resultado, la consulta solo analiza la '2018-01-01' partición de 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 realizar ninguna eliminación de particiones 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 tiene que analizar toda la tabla de destino para buscar las filas que no coinciden.

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 la sección Limitaciones de la página de referencia de DML.

Cuotas

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

Seguridad de las tablas

Para controlar el acceso a las tablas de BigQuery, consulta el artículo sobre cómo controlar el acceso a los recursos con la gestión de identidades y accesos.

Siguientes pasos