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 how to create and update tables from query results.
Store query results in a table
Example
- Create a table with the query results
- Each execution replaces a new table, so no records or columns remain from the previous execution.
- Include a description
/* table */ CREATE OR REPLACE TABLE `project_name.dataset_name`.test_table OPTIONS(description="about") AS ( SELECT "ABC" AS val01, 123 AS val02 ) ; /* check */ SELECT * FROM `project_name.dataset_name`.test_table LIMIT 100
Overwrites the query results to the specified partition of the partitioned table.
Example
- Delete partition data first, then add query results
- Specify the order of the columns of data to be added
/* delete partition */ DELETE FROM `project_name.dataset_name`.test_table WHERE DATE(created_at)="2022-01-01" ; /* add data */ INSERT INTO `project_name.dataset_name`.test_table( created_at, val_01 ) ( SELECT TIMESTAMP("2022-01-01"), 1 ) ; /* check */ SELECT * FROM `project_name.dataset_name`.test_table WHERE DATE(created_at)="2022-01-01" LIMIT 100
Overwrites the query results into the specified partition of the fetch time table.
Example
- Delete partition data first, then add query results
- Specify the order of the columns of data to be added
- Partitions to be added are specified by
_PARTITIONTIME
/* delete partition */ DELETE FROM `project_name.dataset_name`.test_table WHERE DATE(_PARTITIONTIME)="2022-01-01" ; /* add data */ INSERT INTO `project_name.dataset_name`.test_table( _PARTITIONTIME, created_at, val_01 ) ( SELECT TIMESTAMP("2022-01-01"), /* _PARTITIONTIME */ TIMESTAMP("2022-01-01"), 1 ) ; /* check */ SELECT * FROM `project_name.dataset_name`.test_table WHERE DATE(_PARTITIONTIME)="2022-01-01" LIMIT 100
