Chapter 8. Advanced Queries

In Chapters 2 and 3, we covered the essentials of the Standard SQL queries and data types supported in BigQuery. A parser and analyzer for the dialect of Standard SQL supported by BigQuery has been open sourced as ZetaSQL. The ZetaSQL parser and analyzer is used to provide consistent behavior, type checking, implicit casting, name resolution, and more across all the Google Cloud Platform (GCP) products that support SQL (e.g., Cloud Spanner and Cloud Dataflow). However, these query engines might not support all of the features in the ZetaSQL language. For example, BigQuery does not, as of this writing, support multistatement transactions. As of this writing, Cloud Dataflow does not support geographic queries, but if it ever does, the GIS SQL queries and geography types in Cloud Dataflow will be similar to those of BigQuery.

In this chapter, we look at features, data types, and functions of ZetaSQL supported by BigQuery that go beyond the Standard SQL or that might be unfamiliar to many data analysts. We begin by discussing the syntax of features like parameterized queries and user-defined functions that support reusability. Then we delve into the SQL syntax involving arrays, windows, table metadata, and data definition and manipulation. We cover how scripting and stored procedures are supported in BigQuery, and we end the chapter by covering Geographic Information Systems, statistical, and encryption functions.

Reusable Queries

BigQuery supports a number ...

Get Google BigQuery: The Definitive Guide now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.