CODEWITHSUNDEEP
sqldatetimegoogle-bigquery
john mich
john mich

Reputation: 2873

Given a string value and a timestamp, how to create a custom year based partition table in BigQuery?

I have some values in a non-partitioned table 'A' shown below

{'column_1':'string_1','timestamp':2018-01-01 00:00:00}
{'column_1':'string_6','timestamp':2021-01-01 00:00:00}
{'column_1':'string_2','timestamp':2018-01-01 00:00:00}
{'column_1':'string_4','timestamp':2020-01-01 00:00:00}
{'column_1':'string_3','timestamp':2019-01-01 00:00:00}
{'column_1':'string_5','timestamp':2021-01-01 00:00:00}

How can I create a new table Table 'B' which is partitioned yearly-wise and new values get automatically inserted into the right year partitions upon firing an insert command into BigQuery?

Structure of table 'B'

string_1 | 2018-01-01 00:00:00 | 2018
string_2 | 2018-01-01 00:00:00 | 2018
string_3 | 2019-01-01 00:00:00 | 2019
string_4 | 2020-01-01 00:00:00 | 2020
string_5 | 2021-01-01 00:00:00 | 2021
string_6 | 2021-01-01 00:00:00 | 2021

Upvotes: 1

Views: 259

Answers (1)

Yun Zhang
Yun Zhang

Reputation: 5518

You can use a technique used by BigQuery public dataset table bigquery-public-data:crypto_bitcoin.blocks which is a DAY partitioned table, and uses the first day of a month as the partition column timestamp_month.

CREATE TEMP TABLE table_a (
   column_1 STRING,
   timestamp TIMESTAMP,
) as 
  select "string_1", TIMESTAMP "2018-01-01 00:00:00" UNION ALL
  select "string_2", TIMESTAMP "2019-01-01 00:00:00";

CREATE TEMP TABLE table_b (
   column_1 STRING,
   timestamp TIMESTAMP,
   timestamp_year DATE
) PARTITION BY timestamp_year;


INSERT INTO table_b
SELECT column_1, 
       timestamp, 
       DATE(EXTRACT(YEAR FROM DATE(timestamp)), 1, 1) timestamp_year
FROM table_a;

SELECT *
FROM table_b;

Output:

+----------+---------------------+----------------+
| column_1 |      timestamp      | timestamp_year |
+----------+---------------------+----------------+
| string_1 | 2018-01-01 00:00:00 |     2018-01-01 |
| string_2 | 2019-01-01 00:00:00 |     2019-01-01 |
+----------+---------------------+----------------+

Upvotes: 1

Related Questions