My BigQuery Query Cheat Sheet

Explains Query template, searches for Struct and Array field

From unsplash @glenncarstenspeters

I have been performing data analysis with Google BigQuery, mainly focusing on reading and parsing all the asset inventory and other BigQuery data by SQL and obtaining the expected result.

As a backend developer, I am not a SQL guy. Instead, I tend to distribute data in various tables and process the data in memory via code when taking performance, micro-services, sub-database, and sub-table into account. However, data analysis is not the case, and SQL is inevitable.

With the “Data Analyst” hat🎩 on, I need to get myself well prepared before my “performance,” such as understanding the BigQuery data structure, mastering DML, DDL, QUERY statements, and the built-in functions, etc. I made a cheat sheet again to save myself from the “horrible” Google documents and StackOverFlow.

Understand BigQuery

The following picture from Google well explains the definition, features, and working methods of BigQuery.

from https://storage.googleapis.com/gweb-cloudblog-publish/images/BigQuery.max-1600x1600.png

We use query, which is the most used method, to integrate the required information, generate new tables, update the tables regularly and eventually visualize charts or serve the next server business.

Data type

We start the query by understanding the table, which consists of a set of fields of various types. And BigQuery involves the following data types.

• STRING
• BOOL
• BYTES
• INT64 with alias INT, SMALLINT, INTEGER, BIGINT, TINYINT, BYTEINT
• NUMERIC
• BIGNUMERIC
• ARRAY
• STRUCT
• DATE/DATETIME
• TIME/TIMESTAMP
• GEOGRAPHY
• INTERVAL

STRING, INTEGER, and BOOL are the most common types, which are easy to master for most users. We only need to note their value ranges and select the appropriate type in the…