通过BigQuery UI创建的GCS外部表如何添加文件名与创建日期列？

我来帮你排查并解决这个问题：

首先，你遇到的Unrecognized name: _FILE_NAME错误，最常见的原因是查询使用了Legacy SQL而不是Standard SQL——_FILE_NAME伪列是Standard SQL专属的特性，Legacy SQL不支持它。

第一步：切换到Standard SQL #

在BigQuery UI的查询编辑器顶部，找到「SQL dialect」下拉框，确认选择的是「Standard SQL」（默认应该是这个，但有时候可能不小心切换了）。切换后再执行你的查询，应该就能正常识别_FILE_NAME了。

第二步：正确提取文件名和处理创建日期 #

当_FILE_NAME可以正常使用后，你可以进一步处理得到纯文件名和创建日期：

1. 提取完整路径和纯文件名

SELECT
  _FILE_NAME AS full_file_path,
  -- 从完整路径中提取最后一段（纯文件名）
  REGEXP_EXTRACT(_FILE_NAME, r'[^/]+$') AS file_name,
  -- 保留原表的其他列
  * EXCEPT(_FILE_NAME)
FROM `my proj.myDataset.mydata`

2. 添加文件创建日期列

BigQuery没有内置伪列直接提供GCS文件的创建时间（这属于GCS对象的元数据，BigQuery不会自动同步），你有两种方案：

• 方案一：从文件名/路径中提取（如果你的GCS文件命名有日期规范，比如2024-05-20_sales_data.csv）：

  SELECT
    _FILE_NAME AS full_file_path,
    REGEXP_EXTRACT(_FILE_NAME, r'[^/]+$') AS file_name,
    -- 提取日期部分（根据你的实际命名规则调整正则）
    REGEXP_EXTRACT(_FILE_NAME, r'(\d{4}-\d{2}-\d{2})') AS file_create_date,
    * EXCEPT(_FILE_NAME)
  FROM `my proj.myDataset.mydata`
  

• 方案二：通过外部函数获取GCS元数据：如果文件名里没有日期，你可以创建一个Cloud Function作为外部函数，调用GCS API获取文件的创建时间戳，然后在BigQuery查询中调用这个函数。不过这个方案需要额外的开发工作，适合需要精准获取GCS对象创建时间的场景。

可选：持久化这些列到外部表 #

如果你不想每次查询都重复提取，可以在创建外部表时直接生成这些列：

CREATE OR REPLACE EXTERNAL TABLE `my proj.myDataset.mydata_enhanced`
OPTIONS (
  format = 'CSV', -- 替换成你的实际文件格式（JSON/Parquet等）
  uris = ['gs://your-bucket/path/to/files/*']
) AS
SELECT
  *,
  _FILE_NAME AS full_file_path,
  REGEXP_EXTRACT(_FILE_NAME, r'[^/]+$') AS file_name,
  REGEXP_EXTRACT(_FILE_NAME, r'(\d{4}-\d{2}-\d{2})') AS file_create_date
FROM `my proj.myDataset.mydata`

内容的提问来源于stack exchange，提问作者idan