BigQuery使用INNER JOIN创建materialized view报错求助

你遇到的Materialized view query contains unsupported feature报错由3个问题导致：

• BigQuery物化视图不支持WITH（CTE）语法，你定义的ids临时结果集直接触发特性限制。
• 物化视图仅支持基表之间的等值JOIN，不支持JOIN经过聚合计算生成的派生表/子查询，你先聚合得到最大时间戳再关联原表取数据的逻辑不符合该限制——注意INNER JOIN语法本身是支持的，不支持的是你JOIN的对象不是基表。
• 原SQL的JOIN条件中，两个等值判断之间缺少AND连接符，本身存在语法错误。

你不需要拆分表做JOIN，直接用ARG_MAX聚合函数单表计算即可，完全符合物化视图的语法限制，同时满足你的业务需求：

• 自动返回每个文档的最新版本内容
• 自动过滤掉最新操作为DELETE的已删除文档
• 支持增量刷新，查询性能符合预期

对应的创建SQL如下（替换成你自己的项目、数据集、changelog表名即可）：

CREATE OR REPLACE MATERIALIZED VIEW `XXX.XXX.latest_docs_view`
AS
SELECT
  document_name,
  ARG_MAX(data, timestamp) AS latest_doc_data,
  MAX(timestamp) AS latest_update_ts
FROM `XXX.XXX.firestore_changelog_table` -- 替换为Firestore Extension同步生成的changelog基表名
GROUP BY document_name
HAVING ARG_MAX(operation, timestamp) != 'DELETE';

方案说明 #

• ARG_MAX(目标字段, 排序字段)的作用是返回排序字段取最大值时对应的目标字段值，正好匹配你“取每个文档最大时间戳对应data内容”的需求，不需要额外做JOIN。
• HAVING子句会过滤掉最新操作是DELETE的文档分组，不会返回已删除的文档内容。
• 如果不需要返回文档ID和最新更新时间，可以去掉SELECT子句里对应的document_name和latest_update_ts字段，只保留latest_doc_data即可。

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