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 sentencias DML como lo harías con una sentencia 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 que procesa 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 sentencia se confirman de forma automática al final de cada declaración DML exitosa.

  • Las filas que se escribieron hace poco con la API de BigQuery Storage Write (REST) no se pueden modificar con lenguaje de manipulación de datos (DML), como las declaraciones 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 con las 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 (gRPC) se pueden modificar con 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 sentencias 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.

Sentencias DML

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

Declaración INSERT

Usa la sentencia 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 declaraciones INSERT, consulta la declaración INSERT.

Declaración DELETE

Usa la sentencia 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 sentencia TRUNCATE TABLE. Para obtener más información sobre las declaraciones DELETE, consulta Declaración DELETE.

Declaración TRUNCATE

Usa la sentencia TRUNCATE para quitar todas las filas de una tabla, pero deja intactos los metadatos de la tabla, 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

Para borrar filas específicas de una tabla, usa la sentencia DELETE. Para obtener más información sobre la sentencia TRUNCATE, consulta Sentencia TRUNCATE.

Declaración UPDATE

Usa la instrucción UPDATE para actualizar las filas existentes en una tabla. La sentencia UPDATE también debe incluir la palabra clave WHERE para especificar una condición. En el siguiente ejemplo, se reduce el valor 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 declaraciones UPDATE también pueden incluir cláusulas FROM para incluir tablas unidas. Para obtener más información sobre las declaraciones UPDATE, consulta Declaración UPDATE.

Declaración MERGE

La sentencia MERGE combina las operaciones INSERT, UPDATE y DELETE en una sola sentencia 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 declaración MERGE, consulta la declaración MERGE.

Trabajos en simultáneo

BigQuery administra la simultaneidad de las sentencias 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 sentencias DML INSERT con mayor frecuencia, considera transmitir datos a tu tabla con la API de Storage Write (gRPC).

Simultaneidad de DML para UPDATE, DELETE y MERGE

Las sentencias DML UPDATE, DELETE y MERGE se denominan sentencias DML de mutación. Si envías una o más sentencias DML de mutación en una tabla, mientras que otros trabajos DML de mutación en ella se encuentran en ejecución (o están pendientes), BigQuery ejecuta hasta 2 de ellas de manera simultánea, después de lo cual un máximo de 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 sentencias DML de mutación que están en cola comparten una cola por tabla que puede incluir hasta 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 declaración DML

Las mutaciones de sentencias DML que se ejecutan en una tabla de forma simultánea causan conflictos en las sentencias DML cuando las sentencias 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 sentencias DML que se ejecutan simultáneamente, siempre que la sentencia 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.

  • La puesta en cola automática de DML solo se aplica a las sentencias DML independientes que se ejecutan fuera de una transacción de varias sentencias. Las sentencias DML mutables dentro de una transacción de varias sentencias adquieren un bloqueo a nivel de tabla en lugar de bloquear particiones individuales. Las transacciones simultáneas de varias declaraciones que modifican la misma tabla no se ponen en cola y fallan de inmediato, incluso si se orientan a particiones diferentes. Si un procedimiento almacenado o una consulta de varias instrucciones ejecuta solo una declaración DML de mutación, como una sentencia MERGE, no la unas en sentencias BEGIN TRANSACTION y COMMIT TRANSACTION. Para obtener más información, consulta Simultaneidad de transacciones.

DML detallado

DML detallado es una mejora de rendimiento diseñada para optimizar la ejecución de las sentencias UPDATE, DELETE y MERGE (también conocidas como sentencias DML de mutación).

Consideraciones de rendimiento

Sin el DML detallado habilitado, las mutaciones de DML se realizan a nivel del grupo de archivos, lo que puede generar reescrituras de datos ineficientes, en especial para las mutaciones dispersas. Esto puede generar un consumo adicional de ranuras y tiempos de ejecución más largos.

DML detallado es una mejora de rendimiento diseñada para optimizar estas sentencias DML mutantes con la introducción de un enfoque más detallado que tiene como objetivo reducir la cantidad de datos que se deben reescribir a nivel del grupo de archivos. Este enfoque puede reducir de forma significativa el tiempo de procesamiento, de E/S y de ranura que se consume para mutar los trabajos de DML.

Existen algunas consideraciones de rendimiento que debes tener en cuenta cuando usas DML detallado:

  • Las operaciones de DML detalladas procesan los datos borrados con un enfoque híbrido que distribuye los costos de reescritura entre varias mutaciones de tablas. Cada operación de DML puede procesar una parte de los datos borrados y, luego, descargar el procesamiento de datos borrados restante en un proceso de recolección de elementos no utilizados en segundo plano. Para obtener más información, consulta Consideraciones sobre datos borrados.
  • Las tablas con operaciones de DML de mutación frecuentes pueden experimentar una mayor latencia para las consultas de SELECT y los trabajos de DML posteriores. Para evaluar el impacto de habilitar esta función, compara el rendimiento de una secuencia realista de operaciones de DML y lecturas posteriores.
  • Habilitar DML detallado no reducirá la cantidad de bytes analizados de la declaración DML de mutación en sí.

Habilita el DML detallado

Para habilitar el DML detallado, establece la opción de tabla enable_fine_grained_mutations en TRUE cuando ejecutes una declaració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 sentencia 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 sentencias DML mutables se ejecutan con las capacidades de DML detalladas habilitadas y usan la sintaxis de sentencias DML existente.

Para determinar si una tabla se habilitó con DML detallado, consulta la vista INFORMATION_SCHEMA.TABLES. En el siguiente ejemplo, se verifica qué tablas de un conjunto de datos se habilitaron con esta función:

SELECT
  table_schema AS datasetId,
  table_name AS tableId,
  is_fine_grained_mutations_enabled
FROM
  DATASET_NAME.INFORMATION_SCHEMA.TABLES;

Reemplaza DATASET_NAME por el nombre del conjunto de datos en el que se verificará si alguna tabla tiene habilitado el DML detallado.

Inhabilitar el DML detallado

Para inhabilitar la DML detallada de una tabla existente, usa la declaración ALTER TABLE.

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

Cuando inhabilitas el DML detallado, es posible que todos los datos borrados tarden un tiempo en procesarse por completo. Consulta Consideraciones sobre los datos borrados. Como resultado, las limitaciones de DML detalladas pueden persistir hasta que esto ocurra.

Precios

Habilitar el DML detallado para una tabla puede generar costos adicionales. Estos costos incluyen lo siguiente:

Puedes usar reservas de BigQuery para asignar recursos de procesamiento dedicados de BigQuery con el objetivo de procesar los trabajos de datos borrados descargados. Las reservas te permiten establecer un límite para el costo de realizar estas operaciones. Este enfoque es particularmente útil y, a menudo, se recomienda para tablas muy grandes con operaciones de DML mutantes detalladas frecuentes, 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 descargado.

Los trabajos de procesamiento de datos borrados descargados de DML detallada se consideran trabajos en segundo plano y requieren el uso del tipo de asignación de reserva BACKGROUND, en lugar del tipo de asignación de reserva QUERY. Los proyectos que realizan operaciones de DML detalladas sin una asignación de BACKGROUND usan precios a pedido para procesar los trabajos de datos borrados descargados.

Operación Precios según demanda Precios basados en la capacidad
Sentencias DML de mutación Usa el tamaño de DML estándar para determinar los cálculos de bytes analizados a pedido.

Habilitar DML detallado no reducirá la cantidad de bytes analizados de la declaración DML en sí.

 Consume ranuras asignadas con un tipo QUERY en el momento de la ejecución de la declaración.
Trabajos de procesamiento de datos borrados descargados Usa el tamaño de DML estándar para determinar los cálculos de bytes analizados a pedido cuando se ejecutan trabajos de procesamiento de datos borrados. Consume ranuras asignadas con un tipo BACKGROUND cuando se ejecutan trabajos de procesamiento de datos borrados.

Consideraciones de datos borrados

Las operaciones de DML detalladas usan un enfoque híbrido para administrar los datos borrados, ya que combinan el procesamiento intercalado con la recolección de elementos no utilizados descargada para distribuir los costos de reescritura y optimizar el rendimiento en varias sentencias DML mutantes emitidas en una tabla.

Durante la ejecución de una declaración DML de mutación, BigQuery intenta realizar una parte de la recolección de elementos no utilizados pertinente de las sentencias DML anteriores de forma intercalada. Los datos borrados que no se controlan de forma intercalada se descargan en un proceso en segundo plano para su posterior limpieza.

Los proyectos que realizan operaciones de DML detalladas con una asignación BACKGROUND procesan las tareas de recolección de elementos no utilizados descargadas con ranuras. El procesamiento de los datos borrados está sujeto a la disponibilidad de recursos de la reserva configurada. Si no hay suficientes recursos disponibles dentro de la reserva configurada, el procesamiento de las operaciones de recolección de elementos no utilizados descargadas puede tardar más de lo previsto.

Los proyectos que realizan operaciones de DML detalladas con precios a pedido o sin una asignación BACKGROUND, procesan tareas de recolección de elementos no utilizados descargadas con recursos internos de BigQuery y se cobran según las tarifas de precios a pedido. Para obtener más información, consulta Precios.

El momento en que se realizan las tareas de recolección de elementos no utilizados descargadas se determina según la frecuencia de la actividad de DML en la tabla y la disponibilidad de los recursos, si se usa una asignación de BACKGROUND:

  • En el caso de las tablas con operaciones de DML de mutación continua, cada DML procesa una parte de la carga de trabajo de recolección de elementos no utilizados para garantizar un rendimiento de lectura y escritura coherente y, como resultado, la recolección de elementos no utilizados se procesa con regularidad a medida que se ejecutan las DML posteriores.
  • Si no se produce ninguna actividad de DML posterior en una tabla, la recolección de elementos no utilizados descargada se activa automáticamente una vez que los datos borrados alcanzan los 5 días de antigüedad.
  • En casos excepcionales, es posible que se tarde más en procesar por completo los datos borrados.

Para identificar los trabajos de procesamiento de datos borrados de DML detallados que se descargaron, consulta la vista INFORMATION_SCHEMA.JOBS: 

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

Limitaciones

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

  • Para tablas grandes con particiones que mutan con frecuencia y superan los 2 TB, no se recomienda el DML detallado. Estas tablas pueden experimentar una mayor presión de memoria para las consultas posteriores, lo que puede generar latencia de lectura adicional o errores de consulta.
  • Solo se puede ejecutar una declaración DML de mutación a la vez en una tabla que tenga habilitada la DML detallada. Los trabajos posteriores se ponen en cola como PENDING. Para obtener más información sobre el comportamiento de simultaneidad de DML de mutación, consulta Simultaneidad de DML para UPDATE, DELETE y MERGE.
  • Una tabla habilitada con DML detallado no puede tener particiones borradas de forma individual ni reemplazadas. Para borrar o reemplazar datos dentro de una partición, debes usar una sentencia DML mutante, como DELETE, UPDATE, MERGE o TRUNCATE.
  • 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 sentencia SELECT para leer los registros de la tabla.
  • No se puede obtener una vista previa de una tabla habilitada con DML detallado con la consola de BigQuery.
  • No puedes copiar una tabla con DML detallado habilitado después de ejecutar una sentencia 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 sentencia UPDATE, DELETE o MERGE.
  • No puedes cambiar el nombre de una tabla con DML detallado habilitado después de ejecutar una declaración UPDATE, DELETE o MERGE.
  • La replicación entre regiones no es compatible con las tablas que tienen habilitado el DML detallado. Si bien el sistema bloquea la creación de conjuntos de datos replicados que ya contienen esas tablas, no bloquea la habilitación de DML detallado en tablas dentro de conjuntos de datos replicados existentes. Sin embargo, si lo haces, es posible que se produzcan resultados de consulta incorrectos en la réplica secundaria para la tabla habilitada para DML detallado.
  • Las sentencias DML ejecutadas en una transacción de varias sentencias no se optimizan con DML detallada.
  • No puedes habilitar el DML detallado en las tablas temporales creadas con la instrucción CREATE TEMP TABLE.
  • Los metadatos reflejados en las vistas INFORMATION_SCHEMA.TABLE_STORAGE y INFORMATION_SCHEMA.PARTITIONS pueden incluir de forma temporal datos borrados recientemente con DML detallado hasta que se completen los trabajos de recolección de elementos no utilizados en segundo plano.

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 Sentencias 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.

  • Para resolver y evitar el error de cuota Too many DML statements outstanding against table,, sigue la orientación para este error en la página de solución de problemas de BigQuery.

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?