Transformar 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 eliminar datos de tus tablas de BigQuery.

Puedes ejecutar instrucciones DML igual que una instrucción SELECT, con las siguientes condiciones:

  • Debes usar GoogleSQL. Para habilitar GoogleSQL, consulta el artículo sobre cómo cambiar de dialecto de SQL.
  • No puedes especificar una tabla de destino para la consulta.

Para obtener más información sobre cómo calcular el número de bytes procesados por una instrucción DML, consulta la sección Calcular el tamaño de las consultas bajo demanda.

Limitaciones

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

  • Las filas que se han escrito recientemente con el método de streaming tabledata.insertall no se pueden modificar con el lenguaje de manipulación de datos (DML), como las instrucciones UPDATE, DELETE, MERGE o TRUNCATE. Las escrituras recientes son las que se han producido en los últimos 30 minutos. El resto de las filas de la tabla se pueden modificar mediante las instrucciones UPDATE, DELETE, MERGE o TRUNCATE. Los datos transmitidos pueden tardar hasta 90 minutos en estar disponibles para las operaciones de copia.

    También se pueden modificar las filas que se hayan escrito recientemente mediante la API Storage Write con las instrucciones UPDATE, DELETE o MERGE. Para obtener más información, consulta Usar el lenguaje de manipulación de datos (DML) con datos transmitidos recientemente.

  • No se admiten subconsultas correlacionadas en when_clause, search_condition, merge_update_clause ni merge_insert_clause para las instrucciones MERGE.

  • Las consultas que contienen declaraciones de 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 como destino de la operación UPDATE.

Instrucciones DML

En las siguientes secciones se describen los diferentes tipos de instrucciones DML y cómo puede usarlos.

INSERT declaración

Usa la instrucción INSERT para añadir filas a una tabla. En el siguiente ejemplo se insertan filas nuevas en la tabla dataset.Inventory con valores especificados explícitamente.

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 instrucciones INSERT, consulta la instrucción INSERT.

DELETE declaración

Usa la instrucción DELETE para eliminar filas de una tabla. En el siguiente ejemplo se eliminan 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 eliminar todas las filas de una tabla, usa la instrucción TRUNCATE TABLE. Para obtener más información sobre los extractos de DELETE, consulta Extracto de DELETE.

TRUNCATE declaración

Usa la instrucción TRUNCATE para eliminar todas las filas de una tabla, pero dejando intactos los metadatos de la tabla, como el esquema, la descripción y las etiquetas. En el siguiente ejemplo se eliminan todas las filas de la tabla dataset.Inventory.

TRUNCATE dataset.Inventory

Para eliminar filas específicas de una tabla. Usa la instrucción DELETE. Para obtener más información sobre la instrucción TRUNCATE, consulta la instrucción TRUNCATE.

UPDATE declaración

Usa la instrucción UPDATE para actualizar las filas de 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 en 10 el valor de quantity de las filas de 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 combinadas. Para obtener más información sobre los extractos de UPDATE, consulta el extracto de UPDATE.

MERGE declaración

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

Tareas simultáneas

BigQuery gestiona la simultaneidad de las instrucciones DML que añaden, modifican o eliminan filas de una tabla.

vuelve a intentar la operación con un tiempo de espera exponencial entre cada intento.

Simultaneidad de DML INSERT

En cualquier periodo de 24 horas, las primeras 1500 instrucciones INSERT se ejecutan inmediatamente después de enviarse. Una vez alcanzado este límite, la simultaneidad de las instrucciones INSERT que escriben en una tabla se limita a 10. Las instrucciones INSERT adicionales se añaden a una cola PENDING. Se pueden poner en cola hasta 100 INSERT instrucciones en una tabla en cualquier momento. Cuando se completa una instrucción INSERT, la siguiente instrucción INSERT se elimina de la cola y se ejecuta.

Si debes ejecutar instrucciones INSERT de lenguaje de manipulación de datos (DML) con más frecuencia, te recomendamos que envíes datos a tu tabla mediante la API Storage Write.

Simultaneidad de DML de UPDATE, DELETE y MERGE

Las instrucciones de DML UPDATE, DELETE y MERGE se denominan instrucciones de DML mutadoras. Si envías una o varias instrucciones DML de mutación en una tabla mientras se están ejecutando (o pendientes) otras tareas DML de mutación en ella, BigQuery ejecutará hasta dos de ellas simultáneamente. Después, se pondrán en cola hasta 20 tareas como PENDING. Cuando finaliza una tarea que se estaba ejecutando, se quita de la cola y se ejecuta la siguiente tarea pendiente. Las instrucciones de DML mutadoras en cola comparten una cola por tabla con una longitud máxima de 20. Las instrucciones adicionales que superen la longitud máxima de la cola de cada tabla fallarán y mostrarán el siguiente 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 interactivos con prioridad que se ponen en cola durante más de 7 horas fallan y muestran el siguiente mensaje de error:

DML statement has been queued for too long

Conflictos de instrucciones DML

Las declaraciones de DML de mutación que se ejecutan simultáneamente en una tabla provocan conflictos de declaraciones de DML cuando las declaraciones intentan mutar la misma partición. Las instrucciones se ejecutan correctamente siempre que no modifiquen la misma partición. BigQuery intenta volver a ejecutar las instrucciones fallidas hasta tres veces.

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

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

DML pormenorizado

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

Habilitar DML detallado

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

Para crear una tabla 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 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 de 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;

Una vez que la opción enable_fine_grained_mutations se ha definido como TRUE, las instrucciones DML de mutación se ejecutan con las funciones DML detalladas habilitadas y usan la sintaxis de las instrucciones DML.

Para determinar si una tabla tiene habilitado el DML detallado, consulta la vista INFORMATION_SCHEMA.TABLES. En el siguiente ejemplo se comprueba qué tablas de un conjunto de datos tienen habilitada esta función:

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

Sustituye DATASET_NAME por el nombre del conjunto de datos en el que quieras comprobar si alguna tabla tiene habilitado el lenguaje de manipulación de datos (DML) detallado.

Inhabilitar DML detallado

Para inhabilitar DML detallado en una tabla, usa la declaración ALTER TABLE.

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

Cuando se inhabilita la DML granular, puede que se tarde un tiempo en procesar por completo todos los datos eliminados. Consulta las consideraciones sobre los datos eliminados. Como consecuencia, las limitaciones de DML detalladas pueden persistir hasta que esto ocurra.

Precios

Si habilitas DML detallado en una tabla, se pueden generar costes de almacenamiento de BigQuery adicionales para almacenar los metadatos de mutación adicionales asociados a las operaciones de DML detallado. El coste real depende de la cantidad de datos que se modifiquen, 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 de DML detalladas procesan los datos eliminados con un enfoque híbrido que distribuye los costes de reescritura en numerosas mutaciones de la tabla. Cada operación de DML procesa una parte de los datos eliminados y el sistema también descarga el procesamiento restante en segundo plano. Estas tareas de procesamiento de datos eliminadas generan costes de computación de BigQuery adicionales.

Puedes usar las reservas de BigQuery para asignar recursos de computación de BigQuery dedicados a estos trabajos de procesamiento de datos eliminados sin conexión. Las reservas te permiten fijar un límite en el coste de realizar estas operaciones. Este enfoque es especialmente útil en el caso de tablas muy grandes con operaciones DML de mutación detalladas frecuentes, que de lo contrario tendrían costes bajo demanda elevados debido al gran número de bytes procesados al realizar cada trabajo de procesamiento de datos eliminados detallado sin conexión.

Los trabajos de procesamiento de datos eliminados de forma precisa 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 DML detalladas sin un proceso de asignación de BACKGROUND eliminan datos con precios según demanda.

En los proyectos configurados para usar los precios de computación bajo demanda, las instrucciones DML detalladas no reducirán los bytes analizados.

Para encontrar los trabajos de procesamiento de datos eliminados de DML detallado sin conexión, sigue estos pasos: 

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

Si la tabla recibe un gran número de tareas de DML, el procesamiento de los datos eliminados se puede gestionar por completo mediante DMLs, lo que elimina la necesidad de procesamiento en segundo plano.

Consideraciones sobre los datos eliminados

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

Los proyectos que realizan operaciones DML detalladas mediante la tarifa bajo demanda o sin una asignación de BACKGROUND procesan los datos eliminados mediante la tarifa bajo demanda y, con regularidad, procesan los datos eliminados mediante recursos internos de BigQuery.

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 granular habilitado. En su lugar, consulta la tabla con una instrucción SELECT para leer los registros de la tabla.
  • No se puede obtener una vista previa de una tabla habilitada con DML pormenorizado mediante 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 un clon de tabla de una tabla con DML detallado habilitado después de ejecutar una instrucción UPDATE, DELETE o MERGE.
  • No puedes habilitar DML detallado en una tabla de un conjunto de datos replicado, ni replicar un conjunto de datos que contenga una tabla con DML detallado habilitado.
  • Las instrucciones DML ejecutadas en una transacción con varias instrucciones no se optimizan con DML detallado.
  • No puedes habilitar DML pormenorizado en tablas temporales creadas con la instrucción CREATE TEMP TABLE.

Prácticas recomendadas

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

  • Evita enviar un gran número de actualizaciones o inserciones de filas individuales. En su lugar, agrupa las operaciones DML cuando sea posible. Para obtener más información, consulta Instrucciones DML que actualizan o insertan filas individuales.

  • Si las actualizaciones o eliminaciones suelen producirse en datos antiguos o en un intervalo de fechas concreto, considera la posibilidad de crear particiones en tus tablas. Las particiones aseguran que los cambios se limiten a particiones específicas de la tabla.

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

  • Si suele actualizar filas en las que una o varias columnas se encuentran dentro de un intervalo de valores reducido, le recomendamos que utilice tablas agrupadas en clústeres. El agrupamiento asegura que los cambios se limiten a conjuntos específicos de bloques, lo que reduce la cantidad de datos que se deben leer y escribir. A continuación, se muestra un ejemplo de una UPDATE instrucción que filtra por un intervalo de valores de columna:

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

    Aquí tiene 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);

    En estos casos, te recomendamos que agrupes los datos por la columna id.

  • Si necesitas funciones de procesamiento de transacciones online (OLTP), te recomendamos que uses las consultas federadas de Cloud SQL, que permiten a BigQuery consultar datos que se encuentran en Cloud SQL.

Para consultar las prácticas recomendadas para optimizar el rendimiento de las consultas, consulta el artículo Introducción a la optimización del rendimiento de las consultas.

Siguientes pasos