Big Query DML Statements Technique: A small Guide

Table of contents

Reading Time: 3 minutes

In this blog we are going to learn about some of the key Big Query DML statements. Data plays an integral part in any organisation. With the data-driven nature of modern organisations, almost all businesses and their technological decisions are based on the available data. Let’s assume that we have an application distributed across multiple servers in different regions of a cloud service provider, and we need to store that application data in a centralised location. However, traditional databases are ill-suited to handle extremely large datasets and lack the features that would help data analysis. In that kind of situation, we will need a proper data warehousing solution like Google BigQuery.

What is Google BigQuery?

BigQuery is an enterprise-grade, fully managed data warehousing solution that is a part of the Google Cloud Platform. It is designed to store and query massive data sets while enabling users to manage data via the BigQuery data manipulation language (DML) based on the standard SQL dialect.

BigQuery also offers a robust set of tools to manage data sets, from its Cloud Console to BigQuery REST API with support for multiple programming languages such as Java, Python, .NET, etc. Additionally, BigQuery provides built-in support for Machine Learning and AI integrations with BigQueryML, AI Platform, and TensorFlow.

Creating a Table:

Since BigQuery supports the standard SQL dialect, we can use the “CREATE TABLE” command with appropriate data types to create the required tables.

CREATE TABLE IF NOT EXISTS `project.Test_data_table` (
`user_id` INT64 NOT NULL,
`username` STRING NOT NULL,
`user_type` STRING,
`session_start` DATETIME NOT NULL,
`session_end` DATETIME,
`country` STRING NOT NULL
);

Now that we know how to make a table, lets dive into some of the DML statements.

Data Manipulation Language (DML):

BigQuery DML enables users to perform various operations on BigQuery tables such as insert, update and delete using the standard SQL dialect. This option offers the most flexibility as it allows us to create queries to match any requirement using DML.

INSERT:

You can insert new data to your existing table using following statement.

INSERT INTO
`project.Test_data_table` (user_id,
username,
user_type,
session_start,
session_end,
country)
VALUES
(1560,'barry','admin', '2021-05-05 10:46:01', '2021-05-05 11:16:51', 'United Kingdom')

You can also add multiple values like this:

INSERT `project.Test_data_table` (user_id, username, user_type, session_start,session_end, country)
SELECT *
FROM UNNEST([
    (2054,'jake', 'user', '2021-05-06 05:05:41','2021-05-06 10:10:15',          'United States'),
    (8755, 'harry', 'user', '2021-05-04 01:10:01', '2021-05-04 03:45:15', 'Japan'),
]);

DELETE:

DELETE statement allows you to delete rows from a table. When using a DELETE statement, you must use WHERE clause followed by a condition.

DELETE FROM `project.Test_data_table` WHERE country = 'Japan';

UPDATE:

UPDATE statement allows you to modify existing rows in a table. Similar to DELETE statement, each UPDATE statement must include the WHERE clause followed by a condition.

UPDATE `project.Test_data_table`
SET user_type = 'admin' WHERE LOWER(username) = 'jake';

MERGE:

MERGE statement is a powerful construct and an optimisation pattern that combines INSERT, UPDATE and DELETE operations on a table into an “upsert” operation based on values matched from another table.

Conclusion:

So we have covered the basics of big query DML statements. Hope this article will find you useful and motivate you to try big query yourself. Big query is is an comprehensive tool for data warehouse solution and its beneficial to learn this well.