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 una lista de declaraciones DML de BigQuery y ejemplos de cómo usarlas, consulta Declaraciones de lenguaje de manipulación de datos en GoogleSQL.

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.

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:TABLE, limit is 20.

Los trabajos de DML de prioridad interactivos que están en cola durante más de 6 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.

Precios

Para obtener información sobre los precios de DML, consulta los precios del lenguaje de manipulación de datos en la página Precios.

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.