Search code examples
google-bigquerysnapshot

Extract last modified date from backup snapshots in bigquery

I have snapshots backed up everyday and named in this format: TableName_20221218

I want to extract the date from the name of the snapshots to create a date column

Currently, i am manually adding date columns this way but it is kinda inconvenient because i have to update the code everyday

select id, date('2022-11-17') as date, rfm_r, rfm_f, rfm_m, recency_score, phone, recency_score_detail, gender, age_group, city from `backup.t1_customer_dependent_20221117`

union all 

select id, date('2022-11-18') as date, rfm_r, rfm_f, rfm_m, recency_score, phone, recency_score_detail, gender, age_group, city from `backup.t1_customer_dependent_20221118`

union all 

select id, date('2022-11-19') as date, rfm_r, rfm_f, rfm_m, recency_score, phone, recency_score_detail, gender, age_group, city from `backup.t1_customer_dependent_20221119`

union all 

select id, date('2022-11-20') as date, rfm_r, rfm_f, rfm_m, recency_score, phone, recency_score_detail, gender, age_group, city from `backup.t1_customer_dependent_20221120`

Instead of that i want to automatically take the date from the name of the snapshot to create the date column and transform the code to some thing like this

select id, date, rfm_r, rfm_f, rfm_m, recency_score, phone, recency_score_detail, gender, age_group, city from `backup.t1_customer_dependent*`

Anyone know how to do this ?

Im new to bigquery so any help will be greatly appreciated

Thanks

Solution

  • Consider using _TABLE_SUFFIX for wildcard tables.

    SELECT id, PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) AS date, rfm_r ... 
  FROM `backup.t1_customer_dependent_*`