Transform data with data manipulation language (DML)

The BigQuery data manipulation language (DML) lets you toupdate, insert, and delete data from your BigQuery tables.

You can execute DML statements just as you would aSELECT statement, with thefollowing conditions:

• You must use GoogleSQL. To enable GoogleSQL, seeSwitching SQL dialects.
• You cannot specify a destination table for the query.

For more information about how to compute the number of bytes processed by aDML statement, seeOn-demand query size calculation.

Limitations

• Each DML statement initiates an implicit transaction, which means that changesmade by the statement are automatically committed at the end of eachsuccessful DML statement.

• Rows that were recently written using thetabledata.insertall streaming method can't be modified with data manipulationlanguage (DML), such asUPDATE,DELETE,MERGE, orTRUNCATE statements. The recent writes are those that occurredwithin the last 30 minutes. All other rows in the table remain modifiable by usingUPDATE,DELETE,MERGE, orTRUNCATE statements. The streamed data can take up to 90minutes to become available for copy operations.

  Alternatively, rows that were recently written using the Storage Write APIcan be modified usingUPDATE,DELETE, orMERGE statements. For moreinformation, seeUse data manipulation language (DML) with recently streamed data.

• Correlated subqueries within awhen_clause,search_condition,merge_update_clause ormerge_insert_clause are not supported forMERGEstatements.

• Queries that containDML statementscannot use awildcard tableas the target of the query. For example, a wildcard table can be used in theFROM clause of anUPDATE query, but a wildcard table cannot be used as thetarget of theUPDATE operation.

DML statements

The following sections describe the different types of DML statements and howyou can use them.

INSERT statement

Use theINSERT statement to add new rows to an existing table. The followingexample inserts new rows into the tabledataset.Inventory with explicitlyspecified values.

INSERTdataset.Inventory(product,quantity)VALUES('whole milk',10),('almond milk',20),('coffee beans',30),('sugar',0),('matcha',20),('oat milk',30),('chai',5)/+-------------------+----------+|product|quantity|+-------------------+----------+|almondmilk|20||chai|5||coffeebeans|30||matcha|20||oatmilk|30||sugar|0||wholemilk|10|+-------------------+----------+/

For more information about INSERT statements, seeINSERT statement.

DELETE statement

Use theDELETE statement to delete rows in a table. The following exampledeletes all rows in the tabledataset.Inventory that have thequantity value0.

DELETEdataset.InventoryWHEREquantity=0/+-------------------+----------+|product|quantity|+-------------------+----------+|almondmilk|20||chai|5||coffeebeans|30||matcha|20||oatmilk|30||wholemilk|10|+-------------------+----------+/

To delete all rows in a table, use theTRUNCATE TABLE statement instead. Formore information aboutDELETE statements, seeDELETE statement.

TRUNCATE statement

Use the TRUNCATE statement to remove all rows from a table, but leave the tablemetadata intact, including table schema, description, and labels. The followingexample removes all rows from the tabledataset.Inventory.

TRUNCATEdataset.Inventory

To delete specific rows in a table. Use the DELETE statement instead. For moreinformation about the TRUNCATE statement, seeTRUNCATE statement.

UPDATE statement

Use theUPDATE statement to update existing rows in a table. TheUPDATEstatement must also include the WHERE keyword to specify a condition. Thefollowing example reduces thequantity value of rows by 10 for products thatcontain the stringmilk.

UPDATEdataset.InventorySETquantity=quantity-10,WHEREproductLIKE'%milk%'/+-------------------+----------+|product|quantity|+-------------------+----------+|almondmilk|10||chai|5||coffeebeans|30||matcha|20||oatmilk|20||wholemilk|0|+-------------------+----------+/

UPDATE statements can also includeFROM clauses to include joined tables.For more information aboutUPDATE statements, seeUPDATE statement.

MERGE statement

The MERGE statement combines theINSERT,UPDATE, andDELETE operationsinto a single statement and performs the operations atomically to merge datafrom one table to another. For more information and examples about the MERGEstatement, seeMERGE statement.

Concurrent jobs

BigQuery manages the concurrency of DML statements that add,modify, or delete rows in a table.

INSERT DML concurrency

During any 24 hour period, the first 1500INSERT statements run immediatelyafter they are submitted. After this limit is reached, the concurrencyofINSERT statements that write to a table is limited to 10. AdditionalINSERT statements are added to aPENDING queue. Up to 100INSERTstatements can be queued against a table at any given time. When anINSERTstatement completes, the nextINSERT statement is removed from the queue and run.

If you must run DMLINSERT statements more frequently,consider streaming data to your table using theStorage Write API.

UPDATE, DELETE, MERGE DML concurrency

TheUPDATE,DELETE, andMERGE DML statements are calledmutating DML statements. If you submit one or more mutating DML statements on a table whileother mutating DML jobs on it are still running (or pending),BigQuery runs up to 2 of them concurrently, after which up to 20are queued asPENDING. When a previously running job finishes, the nextpending job is dequeued and run. Queued mutating DML statementsshare a per-table queue with maximum length 20. Additional statements pastthe maximum queue length for each table fail with the error message:Resourcesexceeded during query execution: Too many DML statements outstanding againsttable PROJECT_ID:DATASET.TABLE, limit is 20.

Interactive priority DML jobs that are queued for more than 7 hours fail withthe following error message:

DML statement has been queued for too long

DML statement conflicts

Mutating DML statements that run concurrently on a table cause DML statement conflicts when the statements try to mutate the same partition. The statements succeed as long as they don't modify the same partition. BigQuery tries to rerun failed statements up to three times.

• AnINSERT DML statement that inserts rows to a table doesn't conflictwith any other concurrently running DML statement.

• AMERGE DML statement does not conflict with other concurrently running DMLstatements as long as the statement only inserts rows and does not delete orupdate any existing rows. This can includeMERGE statements withUPDATEorDELETE clauses, as long as those clauses aren't invoked when thequery runs.

Fine-grained DML

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. You can process personal data for this feature as outlined in theCloud Data Processing Addendum, subject to the obligations and restrictions described in the agreement under which you access Google Cloud. Pre-GA features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

Fine-grained DML is a performance enhancement designedto optimize the execution ofUPDATE,DELETE, andMERGE statements (alsoknown asmutating DML statements).

Performance considerations

Without fine-grained DML enabled, DML mutations areperformed at the file-group level, which can lead to inefficient data rewrites,especially for sparse mutations. This can lead to additional slot consumptionand longer execution times.

Fine-grained DML is a performance enhancement designed to optimize thesemutating DML statements by introducing a more granular approach that aims toreduce the amount of data that needs to be rewritten at the file-group level.This approach can significantly reduce the processing, I/O, and slot timeconsumed for mutating DML jobs.

There are some performance considerations to be aware of when usingfine-grained DML:

• Fine-grained DML operations process deleted data in a hybrid approach thatdistributes rewrite costs across numerous table mutations. Each DML operationmight processes a portion of the deleted data, and then offload the remainingdeleted data processing to a background garbage collection process. Formore information, seedeleted data considerations.
• Tables with frequent mutating DML operations might experience increasedlatency for subsequentSELECT queries and DML jobs. To evaluate the impactof enabling this feature, benchmark the performance of a realistic sequence ofDML operations and subsequent reads.
• Enabling fine-grained DML won't reduce the amount of scanned bytes of themutating DML statement itself.

Enable fine-grained DML

To enable fine-grained DML, set theenable_fine_grained_mutations table optiontoTRUE when you run aCREATE TABLE orALTER TABLE DDL statement.

To create a new table with fine-grained DML, use theCREATE TABLE statement:

CREATETABLEmydataset.mytable(productSTRING,inventoryINT64)OPTIONS(enable_fine_grained_mutations=TRUE);

To alter an existing table with fine-grained DML, use theALTER TABLE statement:

ALTERTABLEmydataset.mytableSETOPTIONS(enable_fine_grained_mutations=TRUE);

To alter all existing tables in a dataset with fine-grained DML, use theALTER TABLE statement:

FORrecordIN(SELECTCONCAT(table_schema,'.',table_name)AStable_pathFROMmydataset.INFORMATION_SCHEMA.TABLES)DOEXECUTEIMMEDIATE"ALTER TABLE"||record.table_path||" SET OPTIONS(enable_fine_grained_mutations = TRUE)";ENDFOR;

After theenable_fine_grained_mutations option is set toTRUE, mutatingDML statements are run with fine-grained DML capabilities enabled anduse existingDML statement syntax.

To determine if a table has been enabled with fine-grained DML, query theINFORMATION_SCHEMA.TABLES view.The following example checks which tables within a dataset have been enabledwith this feature:

SELECTtable_schemaASdatasetId,table_nameAStableId,is_fine_grained_mutations_enabledFROMDATASET_NAME.INFORMATION_SCHEMA.TABLES;

ReplaceDATASET_NAME with the name of the dataset inwhich to check if any tables have fine-grained DML enabled.

Disable fine-grained DML

To disable fine-grained DML from an existing table, use theALTER TABLE statement.

ALTERTABLEmydataset.mytableSETOPTIONS(enable_fine_grained_mutations=FALSE);

When disabling fine-grained DML, it may take some time for all deleted datato be fully processed, seeDeleted data considerations. As a result,fine-grained DML limitations may persist untilthis has occurred.

Pricing

Enabling fine-grained DML for a table can incur additional costs.These costs include the following:

• BigQuery storage coststo store the extra mutation metadata that is associated with fine-grained DMLoperations. The actual storage cost depends on the amount of data that ismodified, but for most situations it's expected to be negligible incomparison to the size of the table itself.
• BigQuery compute coststo process deleted data using offloadedgarbage collection jobs, and subsequentSELECT queries processing additional deletion metadata which has yet to begarbage collected.

You can useBigQuery reservationsto allocate dedicated BigQuery compute resources to processoffloaded deleted data jobs. Reservations let you set a cap on the cost ofperforming these operations. This approach is particularly useful, and oftenrecommended, for very large tables with frequent fine-grained mutating DMLoperations, which otherwise would have high on-demand costs due to the largenumber of bytes processed when performing each offloaded deleted data processingjob.

Fine-grained DML's offloaded deleted data processing jobs are consideredbackground jobs and require the use of theBACKGROUND reservation assignment type,rather than theQUERY reservation assignment type.Projects that perform fine-grained DML operations without aBACKGROUND assignment useon-demand pricingto process the offloaded deleted data jobs.

Deleted data considerations

Fine-grained DML operations use a hybrid approach to manage deleted data,combining inline processing with offloaded garbage collection to distributerewrite costs and optimize performance across multiple mutating DML statementsissued against a table.

During the execution of a mutating DML statement, BigQueryattempts to perform a portion of relevant garbage collection from prior DMLstatements inline. Any deleted data not handled inline is offloaded to abackground process for later cleanup.

Projects that perform fine-grained DML operations with aBACKGROUND assignmentprocess offloaded garbage collection tasks using slots. Processing deleteddata is subject to the configured reservation's resource availability. If therearen't enough resources available within the configured reservation, processingoffloaded garbage collection operations might take longer than anticipated.

Projects that perform fine-grained DML operations by usingon-demand pricing, or without aBACKGROUND assignment, process offloaded garbage collection tasks usinginternal BigQuery resources and are charged aton-demand pricing rates. For moreinformation, seePricing.

The timing of offloaded garbage collection tasks is determined by the frequencyof DML activity on the table and the availability of resources, if using aBACKGROUND assignment:

• For tables with continuous mutating DML operations, each DML processes aportion of the garbage collection workload to ensure consistent read and writeperformance, and as a result, the garbage collection is regularly processedas the subsequent DMLs are executed.
• If no subsequent DML activity occurs on a table, offloaded garbage collectionis automatically triggered once the deleted data reaches 5 days of age.
• In rare cases, it might take longer to fully process deleted data.

To identify offloaded fine-grained DML deleted data processing jobs, query theINFORMATION_SCHEMA.JOBS view:

SELECT*FROMregion-us.INFORMATION_SCHEMA.JOBSWHEREjob_idLIKE"%fine_grained_mutation_garbage_collection%"

Limitations

Tables enabled with fine-grained DML are subject to the following limitations:

• For large tables with frequently mutated partitions exceeding 2TB,fine-grained DML is not recommended. These tables may experience added memorypressure for subsequent queries, which can lead to additional read latency orquery errors.
• Only one mutating DML statement can run at a time on a table that hasfine-grained DML enabled. Subsequent jobs are queued asPENDING. For moreinformation about mutating DML concurrency behavior, seeUPDATE, DELETE, MERGE DML concurrency.
• You can't use thetabledata.list methodto read content from a table with fine-grained DML enabled. Instead, querythe table with aSELECT statement to read table records.
• A table enabled with fine-grained DML cannot be previewed using theBigQuery console.
• You can'tcopy a table withfine-grained DML enabled after executing anUPDATE,DELETE, orMERGE statement.
• You can't create atable snapshotortable clone of a table withfine-grained DML enabled enabled after executing anUPDATE,DELETE, orMERGE statement.
• You can't enable fine-grained DML on a table in areplicated dataset, and you can't replicatea dataset that contains a table with fine-grained DML enabled.
• DML statements executed in amulti-statement transaction aren't optimizedwith fine-grained DML.
• You can't enable fine-grained DML on temporary tables created with theCREATE TEMP TABLEstatement.
• Metadata reflected within theINFORMATION_SCHEMA.TABLE_STORAGE viewsandINFORMATION_SCHEMA.PARTITIONS viewscan temporarily include recently deleted data using fine-grained DML untilbackground garbage collection jobs have completed.

Best practices

For best performance, Google recommends the following patterns:

• Avoid submitting large numbers of individual row updates or insertions.Instead, group DML operations together when possible. For more information,seeDML statements that update or insert single rows.

• If updates or deletions generally happen on older data, or within a particularrange of dates, considerpartitioningyour tables. Partitioning ensures that the changes are limited to specificpartitions within the table.

• Avoid partitioning tables if the amount of data in each partition is small andeach update modifies a large fraction of the partitions.

• If you often update rows where one or more columns fall within a narrow rangeof values, consider usingclustered tables. Clustering ensures thatchanges are limited to specific sets of blocks, reducing the amount of datathat needs to be read and written. The following is an example of anUPDATEstatement that filters on a range of column values:

  UPDATEmydataset.mytableSETstring_col='some string'WHEREidBETWEEN54AND75;

  Here is a similar example that filters on a small list of column values:

  UPDATEmydataset.mytableSETstring_col='some string'WHEREidIN(54,57,60);

  Consider clustering on theid column in these cases.

• If you need OLTP functionality, consider usingCloud SQL federated queries,which enable BigQuery to query data that resides inCloud SQL.

• To resolve and prevent the quota errorToo many DML statements outstanding against table, followthe guidance for this error on theBigQuery Troubleshooting page.

For best practices to optimize query performance, seeIntroduction to optimizing query performance.

What's next

• For DML syntax information and samples, seeDML syntax.
• Learn more aboutUpdating partitioned table data using DML.
• For information about using DML statements in scheduled queries, seeScheduling queries.