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