Transforma datos con el lenguaje de manipulación de datos (DML)

El lenguaje de manipulación de datos (DML) de BigQuery te permite actualizar, insertar y borrar datos de tus tablas de BigQuery.

Puedes ejecutar Declaraciones DML como lo harías con una declaración SELECT, con las siguientes condiciones:

  • Debes usar GoogleSQL. Para habilitar GoogleSQL, consulta Cambia los dialectos de SQL.
  • No se puede especificar una tabla de destino para la consulta.

Para obtener más información sobre cómo calcular la cantidad de bytes procesados por una declaración DML, consulta Cálculo del tamaño de la consulta a pedido.

Limitaciones

  • Cada declaración DML inicia una transacción implícita, lo que significa que los cambios realizados por la instrucción se confirman de forma automática al final de cada declaración DML exitosa.

  • Filas que se escribieron hace poco mediante el siguiente comandotabledata.insertall el método de transmisión no se puede modificar con lenguaje de manipulación de datos (DML), como instrucciones UPDATE, DELETE, MERGE o TRUNCATE. Las operaciones de escritura recientes son aquellas que ocurrieron en los últimos 30 minutos. Todas las demás filas de la tabla se pueden modificar mediante el uso de declaraciones UPDATE, DELETE, MERGE o TRUNCATE. Los datos transmitidos pueden tardar hasta 90 minutos en estar disponibles para las operaciones de copia.

    Como alternativa, las filas que se escribieron hace poco con la API de Storage Write se pueden modificar mediante declaraciones UPDATE, DELETE o MERGE. Para obtener más información, consulta Usa el lenguaje de manipulación de datos (DML) con datos transmitidos recientemente.

  • Las subconsultas correlacionadas dentro de una when_clause, search_condition, merge_update_clause o merge_insert_clause no son compatibles con las declaraciones MERGE.

  • Las consultas que contienen Declaraciones DML no pueden usar una tabla comodín como destino de la consulta. Por ejemplo, se puede usar una tabla comodín en la cláusula FROM de una consulta UPDATE, pero no se puede usar una como destino de la operación UPDATE.

Declaraciones DML

En las siguientes secciones, se describen los diferentes tipos de sentencias DML y cómo puedes usarlas.

Declaración INSERT

Usa la instrucción INSERT para agregar filas nuevas a una tabla existente. En el siguiente ejemplo, se insertan filas nuevas en la tabla dataset.Inventory con valores especificados de forma explícita.

INSERT dataset.Inventory (product, quantity)
VALUES('whole milk', 10),
      ('almond milk', 20),
      ('coffee beans', 30),
      ('sugar', 0),
      ('matcha', 20),
      ('oat milk', 30),
      ('chai', 5)

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       20 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       30 |
 | sugar             |        0 |
 | whole milk        |       10 |
 +-------------------+----------+/

Para obtener más información sobre las sentencias INSERT, consulta la sentencia INSERT.

Declaración DELETE

Usa la instrucción DELETE para borrar filas de una tabla. En el siguiente ejemplo, se borran todas las filas de la tabla dataset.Inventory que tienen el valor quantity 0.

DELETE dataset.Inventory
WHERE quantity = 0

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       20 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       30 |
 | whole milk        |       10 |
 +-------------------+----------+/

Para borrar todas las filas de una tabla, usa la declaración TRUNCATE TABLE. Para obtener más información sobre las instrucciones DELETE, consulta Instrucción DELETE.

Declaración TRUNCATE

Usa la instrucción TRUNCATE para quitar todas las filas de una tabla, pero deja los metadatos de la tabla intactos, incluidos el esquema, la descripción y las etiquetas de la tabla. En el siguiente ejemplo, se quitan todas las filas de la tabla dataset.Inventory.

TRUNCATE dataset.Inventory

Borra filas específicas en una tabla. En su lugar, usa la instrucción DELETE. Para obtener más información sobre la sentencia TRUNCATE, consulta la sentencia TRUNCATE.

Declaración UPDATE

Usa la instrucción UPDATE para actualizar las filas existentes en una tabla. La instrucción UPDATE también debe incluir la palabra clave WHERE para especificar una condición. En el siguiente ejemplo, se reduce el valor de quantity de las filas en 10 para los productos que contienen la cadena milk.

UPDATE dataset.Inventory
SET quantity = quantity - 10,
WHERE product LIKE '%milk%'

/+-------------------+----------+
 |      product      | quantity |
 +-------------------+----------+
 | almond milk       |       10 |
 | chai              |        5 |
 | coffee beans      |       30 |
 | matcha            |       20 |
 | oat milk          |       20 |
 | whole milk        |        0 |
 +-------------------+----------+/

Las instrucciones UPDATE también pueden incluir cláusulas FROM para incluir tablas unidas. Para obtener más información sobre las declaraciones UPDATE, consulta la declaración UPDATE.

Declaración MERGE

La declaración MERGE combina las operaciones INSERT, UPDATE y DELETE en una sola declaración y realiza las operaciones de forma atómica para combinar datos de una tabla a otra. Para obtener más información y ejemplos sobre la instrucción MERGE, consulta la instrucción MERGE.

Trabajos en simultáneo

BigQuery administra la simultaneidad de las declaraciones DML que agregan, modifican o borran filas en una tabla.

Simultaneidad de DML para INSERT

Durante cualquier período de 24 horas, las primeras 1,500 declaraciones INSERT se ejecutan de inmediato después de enviarlas. Una vez que se alcanza este límite, la simultaneidad de las declaraciones INSERT que se escriben en una tabla se limita a 10. Se agregan declaraciones INSERT adicionales a una cola PENDING. Se pueden poner en cola hasta 100 declaraciones INSERT en una tabla, en cualquier momento. Cuando se completa una declaración INSERT, la siguiente declaración INSERT se quita de la cola y se ejecuta.

Si debes ejecutar declaraciones INSERT DML con mayor frecuencia, considera transmitir datos a tu tabla mediante la API de Storage Write.

Simultaneidad de DML para UPDATE, DELETE y MERGE

Las declaraciones DML UPDATE, DELETE y MERGE se llaman declaraciones DML mutables. Si envías una o más declaraciones DML mutables en una tabla, mientras que otros trabajos DML mutables en ella aún se ejecutan (o están pendientes), BigQuery ejecuta hasta 2 de ellas de manera simultánea, después de lo cual hasta 20 se ponen en cola como PENDING: Cuando finaliza un trabajo en ejecución, se pone en cola el siguiente trabajo pendiente. En la actualidad, las declaraciones DML de mutación que están en cola, comparten una cola por tabla de máximo 20 trabajos. Las declaraciones adicionales posteriores a la longitud máxima de la cola para cada tabla fallan con el mensaje de error: Resources exceeded during query execution: Too many DML statements outstanding against table PROJECT_ID:DATASET.TABLE, limit is 20.

Los trabajos de DML de prioridad interactivos que están en cola durante más de 7 horas fallan con el siguiente mensaje de error:

DML statement has been queued for too long

Conflictos de Declaraciones DML

Las mutaciones de declaraciones DML que se ejecutan en una tabla de forma simultánea causan conflictos en las declaraciones DML cuando las declaraciones intentan mutar la misma partición. Las declaraciones se ejecutan de forma correcta, siempre y cuando no modifiquen la misma partición. BigQuery intenta volver a ejecutar las declaraciones con errores hasta tres veces.

  • Una Declaración DML INSERT que inserta filas en una tabla no entra en conflicto con ninguna otra declaración DML que se ejecute en simultáneo.

  • Una declaración DML MERGE no entra en conflicto con otras declaraciones DML que se ejecutan simultáneamente, siempre que la declaración solo inserte filas y no borre ni actualice ninguna fila existente. Esto puede incluir declaraciones MERGE con cláusulas UPDATE o DELETE, siempre que esas cláusulas no se invoquen cuando se ejecute la consulta.

DML detallado

El DML detallado es una mejora del rendimiento diseñada para optimizar la ejecución de las sentencias UPDATE, DELETE y MERGE (también conocidas como sentencias DML mutables). Si no se habilita el DML detallado, las mutaciones se realizan a nivel del grupo de archivos, lo que puede generar reescrituras de datos ineficientes. El DML detallado introduce un enfoque más granular que tiene como objetivo reducir la cantidad de datos que se deben volver a escribir y el consumo general de ranuras.

Habilita el DML detallado

Para habilitar el DML detallado, establece la opción de tabla enable_fine_grained_mutations en TRUE cuando ejecutes una instrucción DDL CREATE TABLE o ALTER TABLE.

Para crear una tabla nueva con DML detallado, usa la declaración CREATE TABLE:

CREATE TABLE mydataset.mytable (
  product STRING,
  inventory INT64)
OPTIONS(enable_fine_grained_mutations = TRUE);

Para modificar una tabla existente con DML detallado, usa la declaración ALTER TABLE:

ALTER TABLE mydataset.mytable
SET OPTIONS(enable_fine_grained_mutations = TRUE);

Para modificar todas las tablas existentes en un conjunto de datos con DML detallado, usa la declaración ALTER TABLE: 

FOR record IN
 (SELECT CONCAT(table_schema, '.', table_name) AS table_path
 FROM mydataset.INFORMATION_SCHEMA.TABLES)
DO
 EXECUTE IMMEDIATE
   "ALTER TABLE " || record.table_path || " SET OPTIONS(enable_fine_grained_mutations = TRUE)";
END FOR;

Después de que la opción enable_fine_grained_mutations se establece en TRUE, las declaraciones DML mutables se ejecutan con las capacidades DML detalladas habilitadas y usan la sintaxis de declaración DML existente.

Para inhabilitar el DML detallado en una tabla, configura enable_fine_grained_mutations como FALSE con la declaración DDL ALTER TABLE.

Precios

Habilitar el DML detallado para una tabla puede generar costos de almacenamiento de BigQuery adicionales para almacenar los metadatos de mutación adicionales asociados con las operaciones de DML detallado. El costo real depende de la cantidad de datos que se modifican, pero, en la mayoría de los casos, se espera que sea insignificante en comparación con el tamaño de la tabla.

Las operaciones DML detalladas procesan los datos borrados sin conexión. Estos trabajos de procesamiento de datos borrados generan costos de procesamiento de BigQuery adicionales.

Puedes usar reservas de BigQuery para asignar recursos de procesamiento dedicados de BigQuery a estos trabajos de procesamiento de datos borrados sin conexión. Las reservas te permiten establecer un límite para el costo de realizar estas operaciones. Este enfoque es particularmente útil para tablas muy grandes con operaciones frecuentes de DML de mutación detallada, que, de lo contrario, tendrían costos de demanda altos debido a la gran cantidad de bytes procesados cuando se realiza cada trabajo de procesamiento de datos borrados detallados sin conexión.

Los trabajos de procesamiento de datos borrados detallados sin conexión se consideran trabajos en segundo plano y usan el tipo de asignación BACKGROUND, en lugar del tipo de asignación QUERY. Los proyectos que realizan operaciones de DML detalladas sin un proceso de asignación de BACKGROUND borran datos con los precios según demanda.

En los proyectos configurados para usar precios de procesamiento según demanda, las instrucciones DML detalladas no reducirán los bytes analizados.

Para encontrar los trabajos de procesamiento de datos de DML detallados sin conexión que se borraron, haz lo siguiente: 

SELECT
  *
FROM
  region-us.INFORMATION_SCHEMA.JOBS
WHERE
  job_id LIKE "%fine_grained_mutation_garbage_collection%"

Consideraciones de datos borrados

Los proyectos que realizan operaciones de DML detalladas con precios on demand suelen tener datos borrados que se procesan con recursos internos de BigQuery.

Los proyectos que realizan operaciones DML detalladas con un proceso de asignación de BACKGROUND borran datos con ranuras y están sujetos a la disponibilidad de recursos de la reserva configurada. Si no hay suficientes recursos disponibles dentro de la reserva configurada, el procesamiento de los datos borrados puede tardar más de lo previsto.

Limitaciones

Las tablas habilitadas con DML detallado están sujetas a las siguientes limitaciones:

  • No puedes usar el método tabledata.list para leer contenido de una tabla con DML detallado habilitado. En su lugar, consulta la tabla con una declaración SELECT para leer los registros de la tabla.
  • No se puede obtener una vista previa de una tabla habilitada con DML detallado en la consola de BigQuery.
  • No puedes copiar una tabla con DML detallado habilitado después de ejecutar una instrucción UPDATE, DELETE o MERGE.
  • No puedes crear una instantánea de tabla ni una clonación de tabla de una tabla con DML detallado habilitado después de ejecutar una instrucción UPDATE, DELETE o MERGE.
  • No puedes habilitar el DML detallado en una tabla de un conjunto de datos replicado, ni replicar un conjunto de datos que contenga una tabla con el DML detallado habilitado.
  • Las declaraciones DML ejecutadas en una transacción de varias declaraciones no se optimizan con DML detallado.

Prácticas recomendadas

Para obtener el mejor rendimiento, Google recomienda los siguientes patrones:

  • Evita enviar grandes cantidades de actualizaciones o inserciones de filas individuales. Agrupa las operaciones DML cuando sea posible. Para obtener más información, consulta las declaraciones DML que actualizan o insertan filas individuales.

  • Si las actualizaciones o eliminaciones se suelen realizar en datos más antiguos o dentro de un rango de fechas en particular, considera particionar tus tablas. Esta acción garantiza que los cambios se limiten a particiones específicas dentro de la tabla.

  • Evita particionar las tablas si la cantidad de datos en cada partición es pequeña y si cada actualización modifica gran parte de las particiones.

  • Si sueles actualizar filas cuyas columnas se encuentran dentro de un rango pequeño de valores, considera usar tablas agrupadas. El agrupamiento en clústeres garantiza que los cambios se limiten a conjuntos de bloques específicos, lo que reduce la cantidad de datos que se deben leer y escribir. El siguiente es un ejemplo de una declaración UPDATE que filtra un rango de valores de columna:

    UPDATE mydataset.mytable
SET string_col = 'some string'
WHERE id BETWEEN 54 AND 75;

    A continuación, se muestra un ejemplo similar que filtra una lista pequeña de valores de columna:

    UPDATE mydataset.mytable
SET string_col = 'some string'
WHERE id IN (54, 57, 60);

    Considera agrupar en clústeres en la columna id en estos casos.

  • Si necesitas la funcionalidad OLTP, considera usar consultas federadas de Cloud SQL, que permiten que BigQuery consulte datos que residen en Cloud SQL.

Si deseas conocer las prácticas recomendadas para optimizar el rendimiento de las consultas, consulta Introducción a la optimización del rendimiento de las consultas.

¿Qué sigue?

  • Para obtener información y muestras de la sintaxis DML, consulta Sintaxis DML.
  • Para obtener información sobre el uso de declaraciones DML en consultas programadas, consulta Programa consultas.