16 Sep 20 · npack · #Bigquery ·   Bookmark   ×

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
SELECT
MY_DATE
,EXTRACT(YEAR FROM MY_DATE)
,EXTRACT(MONTH FROM MY_DATE)
,FORMAT_DATETIME("%B", DATETIME(MY_DATE))
,EXTRACT(DAY FROM MY_DATE)
,EXTRACT(DAYOFWEEK FROM MY_DATE)
,EXTRACT(WEEK FROM MY_DATE)
,EXTRACT(DAYOFYEAR FROM MY_DATE)
FROM
(
SELECT DATE_ADD('2000-01-01',INTERVAL param DAY) AS MY_DATE
FROM unnest(GENERATE_ARRAY(0, 10000, 1)) as param
)
;

npack

posted on 16 Sep 20

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




npack16-Sep-20

Hello @RamyaG i have edited your question with more context and added the solution as well

RamyaG16-Sep-20

Hello @npack thanks Is it possible to build a calendar stage date table as same as above one

npack16-Sep-20

What do you mean by Calendar Stage? If its just the table name, you can change the name of the table in the above script

RamyaG17-Sep-20

Hi @npack Can I get your email id or any chat box to have a live conversation with you because i need help from you regarding query converting snowflake to bigquery I'm struggling to convert them.

npack17-Sep-20

@RamyaG I have sent an email to your registered email