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 con la API Storage Write mediante 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.

Nota: Las declaraciones de DML están sujetas a límites de frecuencia, como la frecuencia máxima de escritura en tablas. Es posible que alcances un límite de frecuencia si envías un gran número de tareas a una tabla al mismo tiempo. Estas tarifas no limitan el número total de instrucciones DML que se pueden ejecutar. Si recibes un mensaje de error que indica que has superado un límite de frecuencia,

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. Se añaden instrucciones INSERT adicionales 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 de mutación. 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 ejecuten 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

Vista previa

Esta función está sujeta a los "Términos de las Ofertas de Acceso Previo a la Disponibilidad General" de la sección Términos Generales de los Servicios de los Términos Específicos de los Servicios. Puedes tratar datos personales para esta función tal como se indica en la Adenda sobre Tratamiento de Datos de Cloud, de acuerdo con las obligaciones y restricciones descritas en el contrato en virtud del cual accedes a Google Cloud. Las funciones previas a la disponibilidad general están disponibles tal cual y pueden tener una compatibilidad y asistencia limitadas. Para obtener más información, consulta las descripciones de las fases de lanzamiento.

Nota: Para enviar comentarios o solicitar asistencia sobre esta función, escribe un correo a [e-mail].

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

Consideraciones sobre el rendimiento.

Si no se habilita la DML granular, las mutaciones de DML se realizan a nivel de grupo de archivos, lo que puede provocar reescrituras de datos ineficientes, especialmente en el caso de mutaciones dispersas. Esto puede provocar un consumo adicional de ranuras y tiempos de ejecución más largos.

El lenguaje de manipulación de datos (DML) detallado es una mejora del rendimiento diseñada para optimizar estas declaraciones de DML mutables. Para ello, se introduce un enfoque más granular que tiene como objetivo reducir la cantidad de datos que se deben reescribir a nivel de grupo de archivos. Este enfoque puede reducir significativamente el tiempo de procesamiento, E/S y de ranura consumido por los trabajos de DML de mutación.

Debes tener en cuenta algunos aspectos relacionados con el rendimiento al usar DML detallado:

• 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 DML puede procesar una parte de los datos eliminados y, a continuación, descargar el procesamiento de los datos eliminados restantes en un proceso de recogida de elementos no utilizados en segundo plano. Para obtener más información, consulta las consideraciones sobre los datos eliminados.
• Las tablas con operaciones DML de mutación frecuentes pueden experimentar una mayor latencia en las consultas SELECT y los trabajos 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.
• En el caso de las tablas grandes con particiones que se modifican con frecuencia y superan los 2 TB, no se recomienda usar DML de granularidad fina. Estas tablas pueden experimentar una presión de memoria adicional en las consultas posteriores, lo que puede provocar una latencia de lectura adicional o errores de consulta.
• Habilitar DML detallado no reducirá la cantidad de bytes analizados de la propia instrucción DML de mutación.

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;

Cuando la opción enable_fine_grained_mutations se define 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

Habilitar DML detallado en una tabla puede conllevar costes adicionales. Estos costes incluyen lo siguiente:

• Costes de almacenamiento de BigQuery para almacenar los metadatos de mutación adicionales asociados a las operaciones de DML detalladas. El coste de almacenamiento 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.
• Costes de computación de BigQuery para procesar los datos eliminados mediante trabajos de recogida de elementos no utilizados descargados y las SELECTconsultas posteriores que procesan metadatos de eliminación adicionales que aún no se han recogido.

Puedes usar reservas de BigQuery para asignar recursos de computación de BigQuery dedicados a procesar tareas de datos eliminados descargados. Las reservas te permiten fijar un límite en el coste de realizar estas operaciones. Este enfoque es especialmente útil y, a menudo, se recomienda para tablas muy grandes con operaciones de DML de mutación detalladas frecuentes, que de otro modo tendrían costes bajo demanda elevados debido al gran número de bytes procesados al realizar cada trabajo de procesamiento de datos eliminados descargado.

Las tareas de procesamiento de datos eliminados de DML detallado que se descargan se consideran tareas 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 DML detalladas sin una BACKGROUNDasignación usan precios según demanda para procesar los trabajos de datos eliminados descargados.

Operación	Precios bajo demanda	Precios basados en la capacidad
Declaraciones DML de mutación	Utiliza el tamaño de DML estándar para determinar los cálculos de bytes analizados bajo demanda. Si habilita DML detallado, no se reducirá la cantidad de bytes analizados de la propia instrucción DML.	Consume las ranuras asignadas con el tipo QUERY en el momento de ejecutar la instrucción.
Se han descargado las tareas de procesamiento de datos eliminadas	Usa el tamaño de DML estándar para determinar los cálculos de bytes analizados bajo demanda cuando se ejecuten trabajos de procesamiento de datos eliminados.	Consume las ranuras asignadas con un tipo BACKGROUND cuando se ejecuten trabajos de procesamiento de datos eliminados.

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 suelen procesar los datos eliminados con recursos internos de BigQuery.

Para identificar las tareas de procesamiento de datos eliminados de DML detallado que se han descargado, consulte 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:

• 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 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 únicas.

• 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 actualiza con frecuencia 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 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 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.

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

• Para obtener información sobre la sintaxis de DML y ejemplos, consulta Sintaxis de DML.
• Consulta más información sobre cómo actualizar datos de tablas con particiones mediante DML.
• Para obtener información sobre cómo usar las instrucciones de DML en consultas programadas, consulta Programar consultas.