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




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

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

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

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.

@RamyaG I have sent an email to your registered email


Community Software by Hittly