从bigquery的备份快照中提取最后修改日期

Question

我每天备份快照，并以以下格式命名:TableName_20221218

我想从快照的名称中提取日期来创建一个日期列

目前，我手动添加日期列这种方式，但它有点不方便，因为我必须每天更新代码

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`

相反，我想自动从快照的名称中获取日期来创建日期列并将代码转换为如下内容

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

有人知道怎么做吗?

我是bigquery的新手，所以任何帮助都会非常感谢

感谢

Answer 1

考虑使用_TABLE_SUFFIX作为通配符表。

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