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

El lenguaje de manipulación de datos de BigQuery (DML) 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 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 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, MERGE o TRUNCATE. 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 declaraciones DML y cómo puedes usarlas.

Declaración INSERT

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

Declaración DELETE

Usa la declaración DELETE para borrar filas en 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 declaraciones DELETE, consulta la declaración DELETE.

Declaración TRUNCATE

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

TRUNCATE dataset.Inventory

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

Declaración UPDATE

Usa la instrucción UPDATE para actualizar las filas existentes en una tabla. La declaración 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 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 declaración MERGE, consulta la declaración MERGE.

Trabajos en simultáneo

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

Nota: Las declaraciones DML están sujetas a límites de frecuencia, como la tasa máxima de escrituras de tablas. Puedes alcanzar un límite de frecuencia si envías una gran cantidad de trabajos en una tabla a la vez. Estas tasas no limitan la cantidad total de declaraciones DML que se pueden ejecutar. Si recibes un mensaje de error que indica que superaste un límite de frecuencia, vuelve a intentar la operación mediante una retirada exponencial entre los reintentos.

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 enviarse. 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 declaración 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

Vista previa

Esta función está sujeta a las "Condiciones de la oferta previa a la DG" en la sección Condiciones Generales del Servicio de las Condiciones Específicas del Servicio. Puedes tratar datos personales para esta función como se describe en el Anexo de Tratamiento de Datos de Cloud, sujeto a las obligaciones y restricciones que se describen en el acuerdo en virtud del cual accedes a Google Cloud. Las funciones de la fase previa a la DG están disponibles “sin modificaciones” y pueden tener asistencia limitada. Para obtener más información, consulta las descripciones de la etapa de lanzamiento.

Nota: Para proporcionar comentarios o solicitar asistencia para esta función, envía un correo electrónico a [e-mail].

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

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 mutaciones dispersas. Esto puede generar un consumo adicional de ranuras y tiempos de ejecución más largos.

El DML detallado es una mejora del rendimiento diseñada para optimizar estas declaraciones DML mutables mediante 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 significativamente el procesamiento, la E/S y el tiempo de ranura consumidos para los trabajos DML mutables.

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

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

Habilita el DML detallado

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

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

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

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

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 ALTER TABLE declaración:

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 de DML detallado habilitadas y usan la sintaxis de declaración DML existente.

Para determinar si una tabla se habilitó con DML detallado, consulta la INFORMATION_SCHEMA.TABLES vista. En el siguiente ejemplo, se verifica qué tablas dentro 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.

Inhabilita el DML detallado

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

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

Cuando se inhabilita el DML detallado, es posible que se tarde un tiempo en procesar por completo todos los datos borrados . Consulta Consideraciones de datos borrados. Como resultado, las limitaciones del DML detallado pueden persistir hasta que esto suceda.

Precios

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

• Costos de almacenamiento de BigQuery para almacenar los metadatos de mutación adicionales que están asociados con las operaciones de DML detallado. El costo de almacenamiento real depende de la cantidad de datos que se modifican, pero, en la mayoría de las situaciones, se espera que sea insignificante en comparación con el tamaño de la tabla.
• Costos de procesamiento de BigQuery para procesar datos borrados con trabajos de recolección de elementos no utilizados descargados, y consultas SELECT posteriores que procesan metadatos de borrado adicionales que aún no se recopilaron.

Puedes usar reservas de BigQuery para asignar recursos de procesamiento dedicados de BigQuery para procesar 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 DML mutables 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 descargados.

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

Operación	Precios según demanda	Precios basados en la capacidad
Declaraciones DML mutables	Usa el tamaño estándar de DML para determinar los cálculos de bytes analizados a pedido. Habilitar el 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 tiempo de ejecución de la declaración.
Trabajos de procesamiento de datos borrados descargados	Usa el tamaño estándar de DML 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 detallado usan un enfoque híbrido para administrar los datos borrados, que combina el procesamiento intercalado con la recolección de elementos no utilizados descargada para distribuir los costos de reescritura y optimizar el rendimiento en varias declaraciones DML mutables emitidas en una tabla.

Durante la ejecución de una declaración DML mutable, BigQuery intenta realizar una parte de la recolección de elementos no utilizados relevantes de las declaraciones DML anteriores intercaladas. Cualquier dato borrado que no se maneje intercalado se descarga a un proceso en segundo plano para su limpieza posterior.

Los proyectos que realizan operaciones de DML detallado con una asignación BACKGROUND procesan las tareas de recolección de elementos no utilizados descargadas con ranuras. El procesamiento de 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 detallado con precios a pedido o sin una asignación BACKGROUND procesan las tareas de recolección de elementos no utilizados descargadas con recursos internos de BigQuery y se les cobra según las tarifas de precios a pedido. Para obtener más información, consulta Precios.

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

• Para las tablas con operaciones DML mutables continuas, 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. Como resultado, la recolección de elementos no utilizados se procesa con regularidad a medida que se ejecutan los DML posteriores.
• Si no se produce ninguna actividad 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 del DML detallado descargados, consulta la INFORMATION_SCHEMA.JOBS vista:

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 mutadas con frecuencia que 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 una latencia de lectura adicional o errores de consulta.
• Solo se puede ejecutar una declaración DML mutable a la vez en una tabla que tiene habilitado el DML detallado. Los trabajos posteriores se ponen en cola como PENDING. Para obtener más información sobre el comportamiento de simultaneidad de DML mutable, consulta Simultaneidad de DML para UPDATE, DELETE y MERGE.
• Una tabla habilitada con DML detallado no puede tener particiones borradas de forma individual o reemplazadas. Para borrar o reemplazar datos dentro de una partición, debes usar una declaración DML mutable, como DELETE, UPDATE, MERGE o TRUNCATE.
• No puedes usar el tabledata.list método 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 mediante la consola de BigQuery.
• No puedes copiar una tabla con DML detallado habilitado después de ejecutar una declaración UPDATE, DELETE o MERGE.
• No puedes crear una instantánea de tabla ni un clon de tabla de una tabla con DML detallado habilitado después de ejecutar una declaración UPDATE, DELETE, o MERGE.
• No puedes habilitar el DML detallado en una tabla de un conjunto de datos replicado, ni puedes replicar un conjunto de datos que contenga una tabla con DML detallado habilitado.
• Las declaraciones DML ejecutadas en una transacción de varias declaraciones no se optimizan con DML detallado.
• No puedes habilitar el DML detallado en tablas temporales creadas con la CREATE TEMP TABLE declaración.
• Los metadatos reflejados en las INFORMATION_SCHEMA.TABLE_STORAGE vistas y INFORMATION_SCHEMA.PARTITIONS vistas pueden incluir temporalmente 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 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.

• Para resolver y evitar el error de cuota Too many DML statements outstanding against table, sigue las instrucciones para este error en la página 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?

• Para obtener información y muestras de la sintaxis DML, consulta Sintaxis DML.
• Obtén más información para actualizar datos de tabla particionada con DML.
• Para obtener información sobre el uso de declaraciones DML en consultas programadas, consulta Programa consultas.