能否创建BigQuery函数返回指定表的最后访问日期？

首先明确：你期望的SELECT *, GET_LAST_ACCESS(my_table) FROM myListOfTables这种直接在SELECT中调用UDF获取对应表访问日期的方式，在BigQuery里无法直接实现。原因是BigQuery的标量UDF或表值UDF不能在执行时动态访问元数据系统表（比如INFORMATION_SCHEMA.TABLES）或调用元数据API来关联输入的表名。

替代实现方案 #

1. 关联元数据系统表查询

如果你的myListOfTables是存储表名的列表（比如临时表或现有表），可以直接关联INFORMATION_SCHEMA.TABLES来获取最后访问日期：

-- 假设myListOfTables表有一列名为table_id，存储project.dataset.table格式的表名
SELECT
  t.*,
  tbl.last_access_time
FROM myListOfTables t
LEFT JOIN `region-us`.INFORMATION_SCHEMA.TABLES tbl
ON t.table_id = CONCAT(tbl.table_catalog, '.', tbl.table_schema, '.', tbl.table_name)

注意：需要替换region-us为你的数据集所在区域，同时确保拥有访问元数据的权限。

2. 编写存储过程批量处理

如果需要批量处理多个表并返回结果，可以编写存储过程动态生成查询：

CREATE OR REPLACE PROCEDURE `your-project.your-dataset.get_table_last_access`(table_list ARRAY<STRING>)
BEGIN
  DECLARE sql STRING;
  DECLARE i INT64 DEFAULT 0;
  DECLARE table_id STRING;
  
  SET sql = 'SELECT ';
  
  -- 遍历表列表生成查询语句
  WHILE i < ARRAY_LENGTH(table_list) DO
    SET table_id = table_list[OFFSET(i)];
    SET sql = sql || CONCAT('"', table_id, '" AS table_id, (SELECT last_access_time FROM `', SPLIT(table_id, '.')[OFFSET(0)], '.', SPLIT(table_id, '.')[OFFSET(1)], '.INFORMATION_SCHEMA.TABLES` WHERE table_name = "', SPLIT(table_id, '.')[OFFSET(2)], '") AS last_access_time');
    IF i < ARRAY_LENGTH(table_list) - 1 THEN
      SET sql = sql || ' UNION ALL ';
    END IF;
    SET i = i + 1;
  END WHILE;
  
  EXECUTE IMMEDIATE sql;
END;

调用方式：

CALL `your-project.your-dataset.get_table_last_access`(['b2bproject.sales.daily_agg', 'other-project.other-dataset.other_table']);

3. 生成动态SQL脚本

如果只是一次性查询，可以手动或通过脚本生成动态SQL：

SELECT
  'b2bproject.sales.daily_agg' AS table_id,
  (SELECT last_access_time FROM `b2bproject.sales.INFORMATION_SCHEMA.TABLES` WHERE table_name = 'daily_agg') AS last_access_time
UNION ALL
SELECT
  'other-project.other-dataset.other_table' AS table_id,
  (SELECT last_access_time FROM `other-project.other-dataset.INFORMATION_SCHEMA.TABLES` WHERE table_name = 'other_table') AS last_access_time

补充说明 #

• last_access_time字段记录的是表被查询（读操作）的最近时间，不包含写入操作；若表从未被访问过，该字段值为NULL。
• 元数据系统表的更新存在延迟，通常为几小时到一天，因此获取的日期并非实时数据。

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