BigQuery - CRUD Operations

CRUD, standing for CREATE, REPLACE, UPDATE and DELETE, is a foundational SQL concept. Unlike a conventional query that will simply return data in a temporary table, run a CRUD operation, and a table's structure and schema fundamentally changes.

CREATE OR REPLACE Query

BigQuery combined the C and R of CRUD with its statement CREATE OR REPLACE.

CREATE OR REPLACE can be used with BigQuery's various entities, like −

• Tables
• Views
• User-defined functions (UDFs)

The syntax for using the CREATE OR REPLACE command is −

CREATE OR REPLACE project.dataset.table 

While a create operation will create an entirely new entity, an UPDATE statement will alter records at the row (not table) level.

UPDATE Query

Unlike CREATE OR REPLACE, UPDATE used another bit of syntax, SET. Finally, UPDATE must be used with a WHERE clause so UPDATE knows which records to change.

Put together, it looks like this −

The above query updates the table but only impacts rows where the date is equivalent to the current date. If this is the case, it will change the date to yesterday.

DELETE Command

Like UPDATE, DELETE also requires a WHERE clause. The syntax of DELTE query is simple −

DELETE FROM project.dataset.table
WHERE condition = TRUE 

ALTER Command

In addition to CRUD statements, BigQuery also has the previously covered ALTER statement. As a reminder, ALTER is used to −

• Add columns
• Drop columns
• Rename a table

Use each of these functions with caution, especially when working with production data.