sql - BigQuery 表中按 ID 显示最新行的物化 View ？

Question

我有一个包含约 5k 个唯一 ID 的 BigQuery 表。每天都会为可能存在或可能不存在的 ID 插入新行。

我们使用此查询来查找最近的行:

SELECT t.*
EXCEPT (seqnum),
FROM (SELECT t.*,
             ROW_NUMBER() OVER (PARTITION BY id
                                ORDER BY date_of_data DESC
                               ) as seqnum
      FROM `[project]`.[dataset].[table] t
     ) t
WHERE seqnum = 1

虽然我们只需要每个 ID 的最新行，但此查询必须扫描整个表。随着表大小的增长，此查询每天都变得更慢且成本更高。现在，对于一个 8GB 的​​表，上面的查询创建了一个 22MB 的表。如果 22MB 的表可以保持最新，我们更愿意查询它。

是否可以创建一个物化 View 来获取每个 ID 的最新行？

有比将表格增长到无穷大更好的解决方案吗？

其他要求:

• 保留历史数据(在某处)
• 无法使用更新 - 我们每天会更新 1,500 次以上 - https://cloud.google.com/bigquery/quotas

Answer 1

解决方案之一是 partition您的主表(包含所有行)按列 date_of_data 以每日为粒度。

创建一个单独的表，该表将只保留每个 ID 的最新行。通过对整个主表的单次扫描填充它一次，然后通过仅查询主表的最后一天来每天更新它。由于分区查询主表的最后一天将只扫描主表的最后一天。