Transforma 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 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 procesados por 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 comando
tabledata.insertall
el método de transmisión no se puede modificar con lenguaje de manipulación de datos (DML), como instruccionesUPDATE
,DELETE
,MERGE
oTRUNCATE
. 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 declaracionesUPDATE
,DELETE
,MERGE
oTRUNCATE
. 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
oMERGE
. 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
omerge_insert_clause
no son compatibles con las declaracionesMERGE
.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 consultaUPDATE
, pero no se puede usar una como destino de la operaciónUPDATE
.
Declaraciones DML
En las siguientes secciones, se describen los diferentes tipos de sentencias DML y cómo puedes usarlas.
Declaración INSERT
Usa la instrucció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 sentencias INSERT, consulta la sentencia INSERT
.
Declaración DELETE
Usa la instrucción DELETE
para borrar filas de 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 instrucciones DELETE
, consulta Instrucción DELETE
.
Declaración TRUNCATE
Usa la instrucción TRUNCATE para quitar todas las filas de una tabla, pero deja los metadatos de la tabla intactos, incluidos el esquema, la descripción y las etiquetas de la tabla. En el siguiente ejemplo, se quitan todas las filas de la tabla dataset.Inventory
.
TRUNCATE dataset.Inventory
Borra filas específicas en una tabla. En su lugar, usa la instrucción DELETE. Para obtener más información sobre la sentencia TRUNCATE, consulta la sentencia TRUNCATE
.
Declaración UPDATE
Usa la instrucción UPDATE
para actualizar las filas existentes en 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 el valor de 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 instrucciones 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 instrucción MERGE, consulta la instrucción MERGE
.
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: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 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 declaracionesMERGE
con cláusulasUPDATE
oDELETE
, siempre que esas cláusulas no se invoquen cuando se ejecute la consulta.
DML detallado
El DML detallado es una mejora del rendimiento diseñada para optimizar la ejecución de las sentencias UPDATE
, DELETE
y MERGE
(también conocidas como sentencias DML mutables). Si no se habilita el DML detallado, las mutaciones se realizan a nivel del grupo de archivos, lo que puede generar reescrituras de datos ineficientes. El DML detallado introduce un enfoque más granular que tiene como objetivo reducir la cantidad de datos que se deben volver a escribir y el consumo general de ranuras.
Habilita el DML detallado
Para habilitar el DML detallado, establece la opción de tabla enable_fine_grained_mutations
en TRUE
cuando ejecutes una instrucción DDL CREATE TABLE
o ALTER TABLE
.
Para crear una tabla nueva 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 existente 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 existentes en 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;
Después de que la opción enable_fine_grained_mutations
se establece en TRUE
, las declaraciones DML mutables se ejecutan con las capacidades DML detalladas habilitadas y usan la sintaxis de declaración DML existente.
Para inhabilitar el DML detallado en una tabla, configura enable_fine_grained_mutations
como FALSE
con la declaración DDL ALTER TABLE
.
Precios
Habilitar el DML detallado para una tabla puede generar costos de almacenamiento de BigQuery adicionales para almacenar los metadatos de mutación adicionales asociados con las operaciones de DML detallado. El costo real depende de la cantidad de datos que se modifican, 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 DML detalladas procesan los datos borrados sin conexión. Estos trabajos de procesamiento de datos borrados generan costos de procesamiento de BigQuery adicionales.
Puedes usar reservas de BigQuery para asignar recursos de procesamiento dedicados de BigQuery a estos trabajos de procesamiento de datos borrados sin conexión. Las reservas te permiten establecer un límite para el costo de realizar estas operaciones. Este enfoque es particularmente útil para tablas muy grandes con operaciones frecuentes de DML de mutación detallada, 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 detallados sin conexión.
Los trabajos de procesamiento de datos borrados detallados 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 de DML detalladas sin un proceso de asignación de BACKGROUND
borran datos con los precios según demanda.
En los proyectos configurados para usar precios de procesamiento según demanda, las instrucciones DML detalladas no reducirán los bytes analizados.
Para encontrar los trabajos de procesamiento de datos de DML detallados sin conexión que se borraron, haz lo siguiente:
SELECT * FROM region-us.INFORMATION_SCHEMA.JOBS WHERE job_id LIKE "%fine_grained_mutation_garbage_collection%"
Consideraciones de datos borrados
Los proyectos que realizan operaciones de DML detalladas con precios on demand suelen tener datos borrados que se procesan con recursos internos de BigQuery.
Los proyectos que realizan operaciones DML detalladas con un proceso de asignación de BACKGROUND
borran datos 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 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 declaraciónSELECT
para leer los registros de la tabla. - No se puede obtener una vista previa de una tabla habilitada con DML detallado en la consola de BigQuery.
- No puedes copiar una tabla con DML detallado habilitado después de ejecutar una instrucción
UPDATE
,DELETE
oMERGE
. - No puedes crear una instantánea de tabla ni una clonación de tabla de una tabla con DML detallado habilitado después de ejecutar una instrucción
UPDATE
,DELETE
oMERGE
. - No puedes habilitar el DML detallado en una tabla de un conjunto de datos replicado, ni replicar un conjunto de datos que contenga una tabla con el DML detallado habilitado.
- Las declaraciones DML ejecutadas en una transacción de varias declaraciones no se optimizan con DML detallado.
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.