本文介绍了我想要“实时实例化视图",以及任何行的最新信息的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我将这种解决方案视为物化视图的替代方案:
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.
这篇关于我想要“实时实例化视图",以及任何行的最新信息的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!