BigQuery外部表匹配当日GCS文件报错，求解决方案

问题场景 #

每日有大量文件落地GCS存储桶，需合并后复制至新位置。原本通过config*前缀匹配创建BigQuery外部表可行，但尝试匹配当日日期格式的文件路径（如config-*-YYYYMMDD-*.csv）时，无论是直接用函数生成URI还是尝试EXECUTE IMMEDIATE动态语句，均触发错误：

发现不支持的函数调用'ARRAY[...]'；无法计算外部表OPTIONS中的'uris'参数

错误原因 #

BigQuery外部表的OPTIONS(uris=...)参数不支持在数组内直接使用函数拼接字符串；而原本的EXECUTE IMMEDIATE写法存在语法缺陷——生成的SQL中URI字符串未被单引号包裹，导致数组语法错误，触发了ARRAY相关的不支持调用报错。

可行解决方法：修正动态SQL写法 #

通过FORMAT函数正确构造带单引号的URI字符串，确保生成的SQL语法符合BigQuery要求：

BEGIN
DECLARE gcs_uri_pattern STRING;
-- 直接用FORMAT_DATE生成YYYYMMDD格式的日期，替代正则替换写法
SET gcs_uri_pattern = CONCAT(
  'gs://test-greenplum-bq-bucket/test_input/config-*-',
  FORMAT_DATE('%Y%m%d', CURRENT_DATE()),
  '-*.csv'
);
-- 用%s占位符注入URI，同时确保URI被单引号包裹在数组中
EXECUTE IMMEDIATE FORMAT("""
CREATE OR REPLACE EXTERNAL TABLE `test-project.TEMP_PROCESSING.test_external_table`
OPTIONS (
  format = 'CSV',
  uris = ['%s'],
  location = 'EU',
  skip_leading_rows = 1
);
""", gcs_uri_pattern);
END;

关键优化点 #

1. 用FORMAT_DATE('%Y%m%d', CURRENT_DATE())替代regexp_replace(cast(current_date as string),'-','')，更简洁且不易出错；
2. 在FORMAT模板中给%s占位符添加单引号，确保生成的SQL中URI是带引号的字符串，符合数组元素的语法要求。

替代方案：使用分区外部表自动匹配日期 #

如果无需每日手动创建外部表，可通过分区外部表让BigQuery自动从文件名/路径中提取日期分区，实现自动匹配当日文件：

CREATE OR REPLACE EXTERNAL TABLE `test-project.TEMP_PROCESSING.test_external_table`
-- 从文件名中提取日期作为分区字段
PARTITION BY DATE(PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(_FILE_NAME, r'config-*-(\d{8})-*.csv')))
OPTIONS (
  format = 'CSV',
  -- 匹配所有日期格式的文件
  uris = ['gs://test-greenplum-bq-bucket/test_input/config-*-*.csv'],
  skip_leading_rows = 1,
  location = 'EU'
);

优势 #

• 无需每日执行动态SQL创建表；
• 查询时指定WHERE _PARTITIONDATE = CURRENT_DATE()即可仅扫描当日文件，提升查询效率。

内容的提问来源于stack exchange，提问作者Vikrant Singh Rana