BigQuery Short Notes

Dataset

Datasets are top-level containers that are used to organize and control access to your tables and views.

location

BigQuery processes queries in the same location.

Location cannot be changed after creation.

Use BigQuery Data Transfer Service or Cloud Composer to transfer data accross different locations.

There are two types of locations:

A region is a specific geographic place, such as us-central1.
A multi-region is a large geographic area, such as EU, US

considerations

Colocate with external data source
- If dataset is in the US multi-regional location, GCS bucket must be in a multi-regional bucket in the US.
- If dataset is in Cloud Bigtable, your dataset must be in the US or the EU multi-regional location.
Colocate with GCS buckets for loading and exporting data
- The GCS bucket must be in a regional or multi-regional bucket in the same location. Except US multi-regional location, you can load data from a GCS bucket in any regional or multi-regional location.

availability and durability

Failure domains

Machine-level
Zonal
Regional

Failure types

Soft: power failure, network partition, or a machine crash, should never lose data.
Hard: damage from floods, terrorist attacks, earthquakes, and hurricanes, data might be lost.

Single region

No backup or replication to another region.
Better considering create cross-region backups.

Multi region

Data is stored in a single region but is backed up in a geographically-separated region to provide resilience to a regional disaster.

Table

A BigQuery table contains individual records organized in rows. Each record is composed of columns (also called fields).

You can give a user access to specific tables or views without giving the user access to the complete dataset by ACL policy.

Table types

Native
External(federated): Bigtable, GCS, Cloud SQL, Drive
Views

schema

A schema cannot contain more than 15 levels of nested RECORD types.

BigQuery performs best when your data is denormalized. Rather than preserving a relational schema such as a star or snowflake schema, denormalize your data and take advantage of nested and repeated columns

BigQuery only support these modify operations:

Adding columns
Deleting columns
Relaxing a column’s mode from REQUIRED to NULLABLE

partitioning

A table that is divided into segments, called partitions.

Improve query performance.

Reducing the number of bytes read by a query. Lower cost.

BigQuery can more accurately estimate the bytes processed by a query before you run it. (take advantages of maintained metadata for partition)

Partitioned data is stored in physical blocks.

A table can be patritioned by:

Time-unit column: daily, hourly, monthly, or yearly
Ingestion time
Integer range

clustering

Improve the performance of certain types of queries such as queries that use filters clauses and queries that aggregate data.

The order of columns for clustering is important.

BigQuery might not be able to accurately estimate the bytes processed by the query or the query costs.

You can also combine partitioning with clustering. Data is first partitioned and then data in each partition is clustered by the clustering columns.

Sorts the data based on clustering columns and organizes them in optimally sized storage blocks.

Overlapping keys in one cluster block weaken the sort property of the table. BigQuery performs automatic re-clustering in the background to restore the sort property of the table.

View

A view is a virtual table defined by a SQL query.

BigQuery’s standard views are logical views, not materialized views.

materialized views

Precompute the standard views and periodically cache the results for increased performance and efficiency.

Each base table can be referenced by up to 20 materialized views from the same dataset.

A materialized view can reference only a single table, and cannot use joins.

After a materialized view is created on top of a partitioned base table, the table’s partition expiration cannot be changed.

When a base table is queried, the query optimizer might automatically rewrite the query to use the cached result that is stored in the materialized view.

If the base table is partitioned, then you can partition a materialized view on the same partitioning column.

A materialized view might not be up-to-dated: