If you are looking to get started with Bigquery, here are the concepts that you need to be familiar with so you can get the most optimum results from Google Bigquery.
It is useful to use the date time column to partition Bigquery datasets, this helps with the improvement of performance. If a date-time column is not available in the data set, you can use the ingestion time to partition the data set.
You can further optimize your queries in Bigquery by clustering according to some rows.
You should cluster based on the most used rows for your queries. The query below will be optimized based on wiki and table
CREATE TABLE `fh-bigquery.wikipedia_v3.pageviews_2017` PARTITION BY DATE(datehour) CLUSTER BY wiki, title OPTIONS( description="Wikipedia pageviews - partitioned by day, clustered by (wiki, title). Contact https://twitter.com/felipehoffa" , require_partition_filter=true ) AS SELECT * FROM `fh-bigquery.wikipedia_v2.pageviews_2017` WHERE datehour > '1990-01-01'
Bigquery works best with denormalized data, so the use of nested data and repeated fields is recommended over star schema or snowflake schema.
A good example of this is a library, usually in a relational database, you will have authors, books databases and then you will have a fact database defining the relationships between books and authors.
This example I have taken from the official bigquery documentation, and you will have the relationship in this way in a bigquery table.
- id first_name
- dob (date of birth)
- addresses (a nested and repeated field)
- addresses.status (current or previous)
- addresses.numberOfYears (years at the address)
Some other examples of the same are, you will sessions and have events that happened in that session as nested and repeated fields.
For practice, it is good to work with the google analytics public data set in Bigquery.
The google analytics table has a deep nested structure, with records fields that have other records that are nested in them.
Here is a query you can try out.
SELECT date, product.productPrice FROM bigquery-public-data.google_analytics_sample.ga_sessions_20170801,UNNEST(hits) as hits, UNNEST(hits.product) as product WHERE visitId=1501583974