CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquerydatabase-partitioning
NovasVilla
NovasVilla

Reputation: 56

Issue creating table partition by date with data from other table. In Google Cloud

I'm doing a course from Google. Modernizing Data Lakes and Data Warehouses. And in the quest they ask to create a partition table and populate all the info from the source table, I execute my code, the table is created but empty. I also try to create empty table and next insert, and also no info in my table., in this case the log says that are inserted: XXX lines. But when I check the preview, or I query a SELECT *, my table is still empty. Please, why is happening this? Help!

#standardSQL
CREATE OR REPLACE TABLE
  covid_598.oxford_policy_tracker_527
PARTITION BY
  date OPTIONS ( partition_expiration_days=360 ) AS (
  SELECT
    oxford.*
  FROM
    bigquery-public-data.covid19_govt_response AS oxford
  WHERE
    alpha_3_code NOT IN ("GBR",
      "BRA",
      "CAN",
      "USA") )

Upvotes: 0

Views: 356

Answers (1)

Jaytiger
Jaytiger

Reputation: 12274

When you run below query, it will create the table but you can't see any data in it.

#standardSQL
CREATE OR REPLACE TABLE `covid_598.oxford_policy_tracker_527`
PARTITION BY date OPTIONS ( partition_expiration_days=360 ) AS
SELECT oxford.*
  FROM `bigquery-public-data.covid19_govt_response.oxford_policy_tracker` AS oxford
 WHERE alpha_3_code NOT IN ("GBR", "BRA", "CAN", "USA");

It's because expiration time is set on each partion of the table.

When you try below query, you can see the max time of partition column date which is 2021-09-10.

SELECT MAX(date)
  FROM `bigquery-public-data.covid19_govt_response.oxford_policy_tracker`;

PO is taking this course at the time of 2022-09-25 and all the data in oxford_policy_tracker table already have passed the expiration day, i.e. '2021-09-10' + 360 days.

Therefore Bigquery will discard all the outdated data and nothing is put on new table.

Conclusion - Google Lab needs to update the course materials.

Upvotes: 2

Related Questions