如何从BigQuery备份快照名称自动提取日期生成日期列？

你可以利用BigQuery的通配符表查询和内置变量_TABLE_SUFFIX来实现自动提取日期，无需手动维护UNION ALL语句。

最终SQL代码 #

SELECT
  id,
  PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) AS date,
  rfm_r,
  rfm_f,
  rfm_m,
  recency_score,
  phone,
  recency_score_detail,
  gender,
  age_group,
  city
FROM `backup.t1_customer_dependent_*`

关键说明 #

1. _TABLE_SUFFIX变量：当使用通配符*查询一组表时，这个变量会自动返回每个匹配表名中*对应的部分。比如对于表backup.t1_customer_dependent_20221117，_TABLE_SUFFIX的值就是20221117。
2. 日期转换：用PARSE_DATE('%Y%m%d', _TABLE_SUFFIX)把8位数字的日期字符串转换成标准DATE类型，格式符%Y%m%d对应四位年份、两位月份、两位日期的格式。

额外注意事项 #

• 确保所有匹配的快照表结构完全一致（列名、数据类型都相同），否则通配符查询会报错。
• 如果有其他带相同前缀但不是快照的表（比如后缀不是8位数字），可以加WHERE子句过滤：

  WHERE _TABLE_SUFFIX REGEXP '^[0-9]{8}$'
  

  这样只会包含后缀为8位数字的快照表，避免混入无效数据。

内容的提问来源于stack exchange，提问作者Minh Nguyễn Nhật