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

GoogleSQL es el nombre nuevo para SQL estándar de Google. Nuevo nombre, el mismo dialecto excelente de SQL.

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

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

DML detallado

Vista previa

Esta función está sujeta a las "Condiciones de las Ofertas de Fase Previa a la DG" de la sección Condiciones Generales del Servicio de las Condiciones Específicas del Servicio. Puedes procesar los datos personales para esta función como se describe en el Anexo de Procesamiento 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 fase previa a la DG están disponibles “tal cual” y podrían tener asistencia limitada. Para obtener más información, consulta las descripciones de la etapa de lanzamiento.

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

La DML detallada 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 mutables). Sin la DML detallada habilitada, las mutaciones se realizan a nivel del grupo de archivos, lo que puede generar reescrituras de datos ineficientes. La DML detallada presenta un enfoque más detallado que tiene como objetivo reducir la cantidad de datos que se deben reescribir y reducir el consumo general de ranuras.

Para expresar tu interés en inscribir un proyecto en la versión preliminar de DML detallada, completa el formulario de inscripción de DML detallada de BigQuery. Los proyectos se inscriben de forma selectiva en función de una evaluación de tus cargas de trabajo.

Habilita la DML detallada

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

Para crear una tabla nueva con DML detallada, 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 declaración ALTER TABLE:

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

Después de que la opción enable_fine_grained_mutations se establece en TRUE, las sentencias DML mutables se ejecutan con capacidades de DML detalladas habilitadas y usan la sintaxis de sentencias DML existente.

Para inhabilitar la DML detallada en una tabla, establece enable_fine_grained_mutations en FALSE con la declaración DDL ALTER TABLE.

Precios

Habilitar la DML detallada para una tabla puede generar costos de almacenamiento adicionales de BigQuery para almacenar los metadatos de mutación adicionales asociados con las operaciones de DML detallada. El costo real depende de la cantidad de datos que se modifican, pero, en la mayoría de las situaciones, se espera que sea despreciable en comparación con el tamaño de la tabla.

Los proyectos configurados para usar reservations usan ranuras para procesar declaraciones DML detalladas, incluido cualquier procesamiento en segundo plano de metadatos de tablas o mutaciones.

Consideraciones de datos borrados

Las operaciones DML detalladas procesan los datos borrados de forma sin conexión.

Los proyectos que realizan operaciones DML detalladas sin una asignación BACKGROUND borran datos con los precios según demanda. En este caso, el procesamiento de datos borrados se realiza con frecuencia mediante recursos internos de BigQuery.

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

Limitaciones

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

• No puedes usar el método tabledata.list para leer contenido de una tabla con la DML detallada habilitada. En su lugar, usa la API de Storage Read para leer registros de tablas con una API.
• No puedes crear una instantánea de tabla ni una clonación de tabla de una tabla con la DML detallada habilitada.
• No puedes habilitar la DML detallada en una tabla de un conjunto de datos replicado ni replicar un conjunto de datos que contenga una tabla con la DML detallada habilitada.
• Las declaraciones DML que se ejecutan en una transacción de varias instrucciones no se optimizan con la DML detallada.

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.

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.

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