BigQuery INSERT and UPDATE Commands : For Efficient Data Management

Unlike its peers, BigQuery is serverless and hence provides services dynamically, which means that no provisioning or hardware management is required from the user.

This blog will introduce you to Bigquery and will explain the BigQuery INSERT and UPDATE command in detail. These are SQL queries that can help you in managing your data in Google BigQuery.

BigQuery INSERT and UPDATE

• BigQuery INSERT and UPDATE: INSERT Command
• BigQuery INSERT and UPDATE: UPDATE Command

1) BigQuery INSERT Command

• Out of the BigQuery INSERT and UPDATE commands, you must first learn the basic INSERT statement constructs to interact with the above table definitions.
• INSERT query follows the standard SQL syntax. The values that are being inserted should be used in the same order as the columns. The below image shows an example of INSERT command.
• You can execute a basic INSERT query with columns specified as given in the INSERT INTO example below.

INSERT INTO dataset.StockDetails(name, price) VALUES(‘Samsung Galaxy Note 10,’999’’)

An INSERT query without specifying columns can be executed as below.

INSERT INTO dataset.StockDetails VALUES(‘SG Note 10’,’Mystic Black’,’999’,’24’)

An INSERT query using a subquery can be executed as follows.

INSERT INTO dataset.stockDetails(product, color) VALUES(‘SG Note 10’, SELECT color from dataset.StockDetails WHERE product=’Samsung Galaxy Note 20’)

BigQuery also supports the WITH keyword in the QUERY syntax.

• A point to note here is the implicit data typing mapping that is executed by BigQuery in the case of INSERT statements.
• This can lead to corrupted Databases in case the user is not careful.
• BigQuery will raise an error for data type mismatch only in cases where the incoming column cannot be converted to the target column data type.

Pro Tip: With just a few clicks, connect any data source to Google BigQuery and watch your data load in real-time.

What Are The Implications Of Omitting Column Names In The Insert Statement In Bigquery?

• Compatibility Issues: Dynamic or programmatic queries are more error-prone with evolving schemas.
• Depends on Column Order: Omitting column names assumes values match the table’s column order, which can break if the schema changes.
• Risk of Misalignment: Adding or reordering columns may cause incorrect data insertion or query failure.
• Reduced Readability: Hard to understand which value maps to which column, especially in large tables.
• Increased Errors: Schema changes can cause values to map incorrectly without column names.
• Debugging Challenges: Identifying issues is harder when column-value mapping is implicit.

Value type compatibility requirements for INSERT statements

• The values provided must match the column’s data type (e.g., STRING, INT64, FLOAT64).
• BigQuery can perform implicit type conversions for compatible types, such as:
  • INT64 to FLOAT64
  • STRING to DATE (if formatted correctly)

• For columns of complex types (ARRAY, STRUCT), the inserted value must match the declared type and structure.
• If a column is NOT NULL, inserting a NULL value will result in an error.
• If a column has a default value, you can omit it from the INSERT statement, and the default will be applied.

2) BigQuery UPDATE Command

• In the case of BigQuery, each UPDATE statement must include a WHERE clause.
• This offers protection against the bulk erroneous updates if the developer accidentally forgets the WHERE clause.
• To update the complete table, you have to specifically mention WHERE TRUE.
• The most basic UPDATE example will look like below.

UPDATE dataset.StockDetails SET color = ‘Mystic Green’ WHERE product=’SG Note 20 Ultra’

To update records by using a string match operation, the query will be as follows.

UPDATE dataset.StockDetails SET color = ‘Mystic Green’ WHERE product LIKE ‘%Note%’

The above statement will update the color of all records where the product contains ‘Note’ in its name.

An Update statement using a JOIN clause can be executed as below.

UPDATE dataset.StockDetails a SET color = ‘black’ FROM dataset.Stock b WHERE a.product = b.product AND b.outlet = ‘central_park’

The above query updates the color of all the records that belong to a particular store.

An explicit JOIN keyword has to be used in case the JOIN is between tables that are not getting updated. An example will be as follows.

UPDATE dataset.StockDetails SET quantity=’10’ FROM dataset.Stock INNER JOIN dataset.Store on Stock.outlet = Outlet. outlet_name WHERE StockDetails.product = Stock.product and Outlet.location = ‘New York’

The above command will set the quantity as 10 for all items in all outlets belonging to the location ‘New York’.

BigQuery Insert With Complex Data Types

1. Examples of INSERT with STRUCT Types

CREATE TABLE my_table (
  id INT64,
  user_info STRUCT<name STRING, age INT64>
);
INSERT INTO my_table (id, user_info)
VALUES 
(1, STRUCT('Alice', 30)),
(2, STRUCT('Bob', 25));

2. Examples of INSERT with ARRAY Types

CREATE TABLE my_table (
  id INT64,
  tags ARRAY<STRING>
);
INSERT INTO my_table (id, tags)
VALUES 
(1, ['tag1', 'tag2']),
(2, ['tag3']);

3. Examples of INSERT with RANGE Types

CREATE TABLE my_table (
  id INT64,
  price_range NUMERIC_RANGE
);
INSERT INTO my_table (id, price_range)
VALUES 
(1, NUMERIC_RANGE(10, 20)),
(2, NUMERIC_RANGE(30, 40));

BigQuery UPDATE Examples

1. Examples Of Updating Nested Fields And Repeated Records

CREATE TABLE my_table (
  id INT64,
  user_info STRUCT<name STRING, age INT64>
);
UPDATE my_table
SET user_info.age = 35
WHERE user_info.name = 'Alice';

2. Example Of Using The Update Statement With A Range Of Column Values

CREATE TABLE my_table (
  id INT64,
  price NUMERIC
);
UPDATE my_table
SET price = price * 1.1
WHERE price BETWEEN 10 AND 50;

3. Examples Of Using The Update Statement With Complex Joins

CREATE TABLE orders (
  order_id INT64,
  customer_id INT64,
  status STRING
);
CREATE TABLE customers (
  customer_id INT64,
  region STRING
);
UPDATE orders
SET status = 'Priority'
FROM customers
WHERE orders.customer_id = customers.customer_id
AND customers.region = 'North America';

• That covers the basics of INSERT and UPDATE statements in BigQuery.
• While implementing an ETL pipeline to synchronize the data between your transactional databases and data warehouse, an automated scheduled script using the above constructs can be used.
• Implementing such a script has a lot more additional complexities because of the existence of many factors like duplicate rows, data type translations, etc. 

But there is an alternative.

• It is to use a completely managed ETL tool like Hevo that can accomplish data transfer between various sources and destinations easily.
• That’s it! You can now try to implement the BigQuery INSERT and UPDATE commands by yourself.

Learn More About:

How to Set Up BigQuery JDBC Connection

Conclusion

• This article teaches you how to work with Google BigQuery INSERT and UPDATE statements with ease.
• It also provides in-depth knowledge about the concepts behind every step to help you understand and implement them efficiently.
• Integrating complex data from a diverse set of data sources can be a challenging task and this is where Hevo saves the day!
• Learn how to transfer data from Mixpanel to BigQuery to enhance your data analysis. Our guide provides clear steps for effective integration.

FAQ on BigQuery INSERT and UPDATE Commands