我想要“实时物化视图"，以及任何行的最新信息 [英] I want "live materialized views", with the latest info for any row

问题描述

我将此解决方案视为物化视图的替代方案:

I saw this solution as an alternative to materialized views:

• 我想要一个物化视图"；最新记录

但它使用最多每 3 小时运行一次的预定查询.我的用户期待实时数据，我该怎么办?

But it's using the scheduled queries that run at most every 3 hours. My users are expecting live data, what can I do?

推荐答案

2018-10:BigQuery 不支持 物化视图，但您可以使用这种方法:

2018-10: BigQuery doesn't support materialized views, but you can use this approach:

• 使用之前的解决方案实现" 最新数据的摘要，直到计划查询运行的时间.
• 创建一个将物化数据与仅追加表上最新数据的实时视图相结合的视图.

• Use the previous solution to "materialize" a summary of the latest data, until the time that scheduled query ran.
• Create a view that combines the materialized data, with a live view of the latest data on the append-only table.

代码看起来像这样:

CREATE OR REPLACE VIEW `wikipedia_vt.just_latest_rows_live` AS

SELECT latest_row.* 
FROM (
  SELECT ARRAY_AGG(a ORDER BY datehour DESC LIMIT 1)[OFFSET(0)] latest_row
  FROM (
    SELECT * FROM `fh-bigquery.wikipedia_vt.just_latest_rows`
    # previously "materialized" results
    UNION ALL 
    SELECT * FROM `fh-bigquery.wikipedia_v3.pageviews_2018`
    # append-only table, source of truth
    WHERE datehour > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY )

  ) a
  GROUP BY title
)

请注意，BigQuery 能够使用 TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY ) 来有效地修剪分区.

Note that BigQuery is able to use TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY ) to prune partitions effectively.

这篇关于我想要“实时物化视图"，以及任何行的最新信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！