How to build a calendar DIM_DATE table in Bigquery without using any source table?
The calendar table is used extensively in reporting to generate weekly / monthly /quarterly reports. To build a calendar table, you don't have to start from scratch, you can use the below query to build a Calendar table in BigQuery.
CREATE OR REPLACE TABLE Dataset2.MY_DATE_DIMENSION (
MY_DATE DATE NOT NULL
,YEAR INT64 NOT NULL
,MONTH INT64 NOT NULL
,MONTH_NAME STRING NOT NULL
,DAY_OF_MON INT64 NOT NULL
,DAY_OF_WEEK INT64 NOT NULL
,WEEK_OF_YEAR INT64 NOT NULL
,DAY_OF_YEAR INT64 NOT NULL
CLUSTER BY MY_DATE,YEAR,MONTH;
And then insert the date values:
INSERT INTO Dataset2.MY_DATE_DIMENSION
,EXTRACT(YEAR FROM MY_DATE)
,EXTRACT(MONTH FROM MY_DATE)
,EXTRACT(DAY FROM MY_DATE)
,EXTRACT(DAYOFWEEK FROM MY_DATE)
,EXTRACT(WEEK FROM MY_DATE)
,EXTRACT(DAYOFYEAR FROM MY_DATE)
SELECT DATE_ADD('2000-01-01',INTERVAL param DAY) AS MY_DATE
FROM unnest(GENERATE_ARRAY(0, 10000, 1)) as param
Enjoy great content like this and a lot more !
Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds