BigQuery error: Table name "XYZ" missing dataset while no default dataset is set in the request. at [x:y]

When creating a table in Bigquery make sure the table name is prefixed with the dataset (i.e. Database) name. Look at the below example:

CREATE OR REPLACE TABLE 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      STRING  NOT NULL
  ,WEEK_OF_YEAR     INT64    NOT NULL
  ,DAY_OF_YEAR      INT64    NOT NULL
)
CLUSTER BY MY_DATE,YEAR,MONTH;

Query error: Table name "MY_DATE_DIMENSION" missing dataset while no 
default dataset is set in the request. at [1:1]

Solution:

To fix this, simply prefix the dataset name to the table, like shown below:

CREATE OR REPLACE TABLE DATASET.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      STRING  NOT NULL
  ,WEEK_OF_YEAR     INT64    NOT NULL
  ,DAY_OF_YEAR      INT64    NOT NULL
)
CLUSTER BY MY_DATE,YEAR,MONTH;

nVector

posted on 16 Sep 20