I usually use Google BigQuery only for simple aggregate SQL, but there are many other useful functions besides aggregate SQL.
However, since I don't use it often and when I do try to use it, I start by looking for a manual, so I'll put together a brief usage guide for myself.
This section summarizes view, materialized view, and table functions.
view
- The so-called view
- SELECT queries can be saved as views
- Views can be called from other queries
- Cannot be a view of a multi-statement query
- Cannot partition
materialized view
- View with cache
- Once a view is called, the result is cached, and the next time it is called, the cache is referenced and the original data call or operation is not performed, resulting in higher speed.
- When the original data is updated, the cache is recalculated for the updated part.
- Storage fees are charged against the cache.
query constraint
- Only some aggregate functions are available in the query.
- Even an aggregate function that can be used in a query cannot perform further operations on the results of that function
- Cannot use OUTER JOIN in query
- Can be partitioned, but cannot be used for take-up time partitioning
- Cannot call tables across projects
- When using partitioning, output the source partitioning column directly to the view column
Because of its many limitations, it is safe to use it specifically for simple conversion views of split tables (not partitioned at fetch time).
Example
/* materialized view */ CREATE OR REPLACE MATERIALIZED VIEW `project_name.dataset_name`.test_materialized_view PARTITION BY DATE(created_at) AS ( WITH tbl AS ( SELECT created_at, val_01 FROM `project_name.dataset_name`.table_name ) SELECT created_at, val_01*10 AS val_10 FROM tbl ) ; /* check */ SELECT * FROM `project_name.dataset_name`.test_materialized_view WHERE DATE(created_at)="2022-01-01" LIMIT 100
table function
- User-defined functions that return tables
A kind of view that can pass variables
- Easier to use than view
Sometimes you want a view that can be partitioned, and the equivalent can be created with a table function.
Example
/* table function */ CREATE OR REPLACE TABLE FUNCTION `project_name.dataset_name`.test_table_function( param_dt DATE, param_int INT64, param_any ANY TYPE ) AS ( SELECT param_dt AS val_dt, param_int AS val_int, param_any AS val_any ); /* check */ SELECT * FROM `project_name.dataset_name`.test_table_function( DATE("2022-01-01"), 123, "ABC" ) LIMIT 100
