Google BigQuery: The Definitive Guide

Chapter 2. Query Essentials

BigQuery is first and foremost a data warehouse, by which we mean that it provides persistent storage for structured and semi-structured data (like JSON objects). The four basic CRUD operations are supported on this persistent storage:

Create

To insert new records. This is implemented through load operations, by the SQL INSERT statement, and through a streaming insert API. You can also use SQL to create database objects like tables, views, and machine learning models as part of BigQuery’s support of the Data Definition Language (DDL). We go into examples of each later.

Read

To retrieve records. This is implemented by the SQL SELECT statement as well as the bulk read API.

Update

To modify existing records. This is implemented by the SQL UPDATE and MERGE statements, which are part of BigQuery’s support of the Data Manipulation Language (DML). Note that, as we discussed in Chapter 1, BigQuery is an analytics tool and is not meant to be used for frequent updates.

Delete

To remove existing records. This is implemented by SQL DELETE, which is also a DML operation.

BigQuery is a tool for data analysis, and the majority of queries you can expect to write will be the aforementioned Read operations. Reading and analyzing your data is accomplished by the SELECT statement, which is the focus of this chapter. We cover creating, updating, and deleting data in later chapters.

Simple Queries

BigQuery supports a dialect of SQL that is compliant with SQL:2011. When ...