BigQuery Demystified

Lecturer: Omid Vahdaty 12.7.2022

In this session, We are going to review the basics of BigQuery SQL and practice with live demo.

Video

Slides

How to create a table in BigQuery with Partitioning and clustering via DDL command?

The following code snippet is the answer, however, be very careful about what you choose as cluster fields as this may cause unintended performance and costs penalties.

				
					CREATE TABLE my_dataset.new_clustered_table 
( 
date DATE, 
field_a STRING, 
field_b STRING, 
field_c STRING, 
field_d STRING, 
field_e STRING ) 
PARTITION BY date 
CLUSTER BY field_a, field_b 
OPTIONS ( description="a_table clustered_by_two_fields" )

This BigQuery create table example is also committed in our github

some relevant docs:

[1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#creating_a_clustered_table_from_the_result_of_a_query

[2]: https://cloud.google.com/bigquery/docs/clustered-tables

[3]: https://cloud.google.com/bigquery/docs/creating-clustered-tables#limitations

How to do something like show create table in BigQuery?

The short answer: You can’t. The good news: There is a work around.

				
					SELECT * FROM `myProjecId1234.MyDataSet.Table_Schema` 

where table_schema ='MyDataSet' and table_name ='MyTable'

The example is also committed in our GitHub

A good read about BigQuery implementation in Twitter:

https://blog.twitter.com/engineering/en_us/topics/infrastructure/2019/democratizing-data-analysis-with-google-bigquery.html

——————————————————————————————————————————
I put a lot of thoughts into these blogs, so I could share the information in a clear and useful way.
If you have any comments, thoughts, questions, or you need someone to consult with,

feel free to contact me via LinkedIn – Omid Vahdaty: