Chapter 2 SQL with the BigQuery editor

2.1 SQL differences

BigQuery uses Google SQL, Microsoft SQL server uses T-SQL. The differences are all outlined in this document.

The differences we’ve found:

  • CREATE TABLE instead of SELECT INTO,
  • EXTRACT() instead of DATEPART,
  • LIMIT instead of TOP,
  • declared variables don’t need ‘@’ ,
  • UPDATE SET is fine, but requires a CTE if you want to update with a select statement,
  • capitalisation is important in table names and WHERE statements - so good practice is needed.
  • the general cognitive shift from having tables per year/reference, rather than having one large partitioned table - which does help significantly with automation

2.2 Creating Datasets and Tables

In this chapter we will give you the opportunity to run through several exercises to familiarise yourself with Google BigQuery. In BigQuery, datasets are used to organize and manage your data, while tables store the actual data. An example of creating a table in BigQuery:

CREATE TABLE `dft-stats-gcp-showcase.TS_Training.tst_max_wind` as
SELECT status, MAX(wind) as max_wind 
FROM `dft-stats-gcp-showcase.TS_Training.tst_storms` 
group by status

2.2.1 Exercise 1

  1. Create your own dataset, name it what you like.
  2. Create a table by selecting from one of the tables in TS_Training
  3. Run a query against this new table. Open up the execution details, and investigate.

2.3 Temporary tables, views and stored proceedures

It is possible to create temporary tables, views and stored procedures in Google BigQuery.

An example of a temporary table:

CREATE TEMPORARY TABLE hurricanes AS
SELECT * 
FROM `dft-stats-gcp-showcase.TS_Training.tst_storms` 
WHERE status = 'hurricane';

An example of creating a view:

CREATE VIEW `dft-stats-gcp-showcase.TS_Training.hurricane_view` AS
SELECT * 
FROM `dft-stats-gcp-showcase.TS_Training.tst_storms` 
WHERE status = 'hurricane';

An example of a stored procedure:

CREATE PROCEDURE procedure_name(parameter_list)
BEGIN
  -- SQL statements here
END;

This statement creates a stored procedure called procedure_name that takes a list of parameters and contains the SQL statements defined within the BEGIN and END blocks. Note that the parameter list is optional, and that stored procedures can be called from within other SQL queries using the CALL statement:

CALL procedure_name(parameter_list);

The example below will insert a droid into our training record every time we call the proceedure.

create procedure TS_Training.insert_droids()
BEGIN
INSERT INTO`dft-stats-gcp-showcase.TS_Training.training_record` 
VALUES("grey", "15th March", "Droid");
END

Call this proceedure like:

CALL TS_Training.insert_droids();