google-bigquery - BigQuery - 6 年订单迁移、表/查询设计

Question

我正在构建一个 Spark 作业，将 6 年的电子商务订单/事件迁移到我们新的 BigQuery 仓库中，以补充新的流媒体功能。迁移的数据与新数据位于同一模型中， View 将根据需要公开部分。

我们有 4 个数据集:

数据(原始数据 - 分层 JSON) View (如原始数据上的 DIMS - 通常平坦但并非总是如此)pre_aggs(例如货币转换原始总数，包括一些关键的汇总暗淡 - 平坦)报告(如您所想)

与一些新的流式传输和无限制的实时数据不同，此历史数据的迁移是批量和有限制的(例如，我不必担心延迟到达的事件/水印和重复)。我还可以通过事件 时间 (orderPlacedTimestamp) 手动对数据进行分区，并保存在正确的日期分区表中(后缀？)。完整数据在 BQ 中压缩后约为 2 GB 和 200 万行，因此结构不是很大但相当复杂，在 View 层中需要取消嵌套。我可以选择将原始数据和聚合数据编写为 Spark 的具体化表，所以我真的很想按照最佳实践来执行此操作并优化性能(查询速度更重要，值得为此付出额外的代价)

我在 SQL/Query lambda 架构上发现了这个非常好的博客，它提供了一些灵感，我会尝试做类似的事情。

https://www.linkedin.com/pulse/building-robust-real-time-etl-google-bigquery-lambda-novozhilov

我仍然想知道如何最好地存储/分区这些数据，然后构建基于时间的查询来匹配。周复一周、月复一月的报告可能是最常见的。

我的选择似乎是:

1. 所有内容都在一张表中 - 看起来不错且简单，没有随着时间的推移进行持续的表管理，但这意味着对每个查询进行全面扫描，而我通常只想从某个时间点最多回溯一年或两年？

2. 每个时间段一张表每年，每月

order_history_2017 或 order_history_201701

我们有一个日历查找暗淡的，每一行都有可以使用上面的后缀的键——例如 2017 年 1 月的 201701。

每月意味着 72 张 table ，这似乎有点多，也许每年更好？

为了争论，比如它的月度表，BQ(标准 SQL)中最好的方法是什么，然后查询包含连续时间线的表的正确前缀，使用正确的后缀动态构建表名(也许是动态的？

例如，我想查询 2017-01-10 和 2017-02-10 之间的所有订单(订单有一个 orderPlacedTimestamp)——这意味着只扫描(和合并？)order_history_2017-01-10 和 order_history_2017-02 -10 个表在这种情况下，执行如下所示的 BETWEEN:

SELECT *
FROM order_history_201701 UNION ALL
SELECT *
FROM order_history_201702
WHERE order.orderPlacedTimestamp BETWEEN DATE(“2017–01-10”) and DATE(“2017-02-10”)

然后我可能会遇到这种历史数据也需要与“实时”(流式)数据结合的场景 - 像关于 lambda 设计的文章一样包装在 View 中。

1. 一个我没想到的选项。

BQ 有这么多选择! :)

无论如何，这是我目前的想法，在表设计和优化查询构造方面，任何有关此主题的智慧之言都将不胜感激。

谢谢大家!

Answer 1

我的建议是认真考虑 Partitioned Tables 的原生 BigQuery 功能

虽然流式传输到分区表有一些 limitations (您可以流式传输到相对于当前日期的过去 30 天内和 future 5 天内的分区)加载或查询作业没有此类限制

很久以前，我考虑过使用此选项来解决按列而不是日期进行分区的问题，方法是将一些属性映射到“0001-01-01”和“9999-12-31”(3,652,058 天)之间的日期- 因此不同的属性值被划分)。 POC 在概念上是成功的，但我仍然不喜欢它，尤其是谷歌团队强烈 promise (至少那时候我是这么想的)介绍 partitioning by date or int column 表的。我决定等这个。

同时，刷新并仔细检查分区表中的加载或查询仍然没有特定于分区的限制(因为它在 SO Post 中以某种方式听起来) - 我做了快速测试，您可以在下面看到结果

第 1 步 - 创建分区表 - project.dataset.partitioned_table我只是为此使用了 UI。

第 2 步 - 将查询结果插入表的不同分区

#standardSQL
SELECT 13 AS a

以 project.dataset.partitioned_table$YYYYMMDD 作为目的地(你可以为此使用 DML 的 INSERT)

我在 AC (0001-01-01) 和 ? (9999-21-31)

第 3 步 - 检查结果

#standardSQL
SELECT DATE(_partitiontime) AS partition_, a
FROM `project.dataset.partitioned_table`
ORDER BY a

结果是(记住 - 这里 partition_ 的格式是 YYYYMMDD)

partition_      a
----------      --
2017-07-16      1
2017-07-16      2
2017-07-16      3
2017-07-16      4
2017-07-15      5
2017-07-14      6
2010-01-01      7
2001-01-01      8
1001-01-01      9
0001-01-01      10
4001-01-01      11
7001-01-01      12
9999-12-31      13