什么时候使用 Google BigQuery 才有意义？

在我的职业生涯中，我不得不为不同类型的报告和图表编写复杂的分析查询。 大多数情况下，它是一些图表，显示按日期、周、季度等聚合的数据。 通常，创建此类报告是为了帮助客户识别趋势并说明他们的业务在高水平上的表现。 但是，当数据科学家和工程师需要基于大数据集创建更广泛的报告时会发生什么？

如果报表是基于少量数据的，则可以通过在关系数据库下编写 SQL 查询来解决该任务。 在这一步中，重要的是要了解编写查询的基础知识以及如何使它们更快、更高效。 但是，有时报表依赖于更大的数据集（例如，表格中的数百万行和更多行），但报表不依赖于输入变量（参数），或者您可能会发现值的数量非常少。 在这种情况下，SQL 查询可能会很慢，因此用户等待查询执行并不是最佳选择。 在这种情况下，最常见的做法是在客户请求报告之前提前运行查询。

此外，它需要实现一些缓存功能，因此客户端可以从缓存中获取数据，而不是实时运行查询。 如果您不需要显示实时数据，这种方法非常有效。 它可以显示一个小时甚至一天前计算的数据。 因此，实际报告/图表使用缓存数据显示，而不是基于实时数据。

转向 Google BigQuery

当我在制药行业从事分析项目时，我需要将邮政编码和药物名称作为输入参数的图表。 我还需要展示美国特定地区的药物之间的一些比较。

分析查询非常复杂，最终在我们的 Postgres 服务器（具有 16 GB RAM 的四核 CPU）上运行了大约 50 分钟。 我无法提前运行它并缓存结果，因为查询将邮政编码和药物作为输入参数，所以有数千种组合，无法预测哪个客户会选择。

即使我想尝试执行所有输入参数组合，我的数据库也很可能崩溃。 所以是时候选择一种不同的方法并选择一些易于使用的解决方案了。 该图表对客户很重要，但是，客户还没有准备好对架构进行重大更改或完全迁移到另一个数据库。

在那个特定的项目中，我们尝试了几种不同的方法：

• 服务器的垂直扩展（向 Postgres 服务器添加 RAM 和 CPU）
• 使用 Amazon Redshift 等替代数据库。
• 我们还研究了一个 NoSQL 解决方案，但它们中的大多数都非常复杂，需要对架构进行大量更改，其中许多更改对于客户端来说太大了。

最后，我们尝试了 Google BigQuery。 它满足了我们的期望，使我们能够完成工作，而无需进行客户不愿批准的巨大更改。 但是什么是 Google BigQuery 以及它的表现如何？

BigQuery 是一种基于 REST 的网络服务，可让您在大量数据下运行基于 SQL 的复杂分析查询。 在我们将数据上传到 BigQuery 并执行与 Postgres 相同的查询后（语法非常相似），我们的查询运行得更快，大约需要一分钟才能完成。 最终，仅通过使用不同的服务，我们就获得了 50 倍的性能提升。 值得注意的是，其他数据库并没有提供相同的性能提升，让我们大方一点，只是说它们甚至不接近。 老实说，BigQuery 提供的性能提升给我留下了深刻的印象，因为这些数据比我们任何人都希望的要好。

尽管如此，我不会将 BigQuery 宣传为世界上最好的数据库解决方案。 虽然它对我们的项目运行良好，但它仍然有很多限制，例如每天表中的更新数量有限，每个请求的数据大小限制等等。 您需要了解 BigQuery 不能用于替代关系数据库，它面向的是运行分析查询，而不是简单的 CRUD 操作和查询。

在本文中，我将尝试比较在实际用例场景中使用 Postgres（我最喜欢的关系数据库）和 BigQuery。 此外，我会在此过程中提供一些建议，即我对何时使用 BigQuery 真正有意义的看法。

样本数据

为了比较 Postgres 和 Google BigQuery，我获取了按国家、年龄、年份和性别分组的每个国家的公共人口统计信息（您可以从此链接下载相同的数据）。

我将数据添加到四个表中：

1. populations
2. locations
3. age_groups
4. populations_aggregated

最后一张表只是前三个表的汇总数据。 这是数据库架构：

我最终得到的populations表包含超过 690 万行。 这不是太多，但对于我的测试来说已经足够了。

基于示例数据，我尝试构建可用于构建现实分析报告和图表的查询。 所以我为下一份报告准备了查询：

1. 美国人口按年汇总。
2. 2019 年所有国家的人口，从最大的国家开始。
3. 每年排名前五的“最古老”国家。 “最老”是指60岁以上人口占总人口比例最高的国家。 该查询每年应给出五个结果。
4. 按年份汇总的前五个国家，其中男性和女性人口之间的差异最大。
5. 从“最古老”到“最年轻”的国家/地区，每年获取每个国家/地区的中位（平均）年龄。
6. 每年找出前五个“垂死”的国家。 “垂死”是指人口正在减少（人口减少最多）的国家。

查询 #1、#2 和 #6 非常简单直接，但查询 #3、#4 和 #5 并不那么容易编写——至少对我来说是这样。 请注意，我是一名后端工程师，编写复杂的 SQL 查询并不是我的专长，因此具有更多 SQL 经验的人可能会构建更智能的查询。 但是，目前我们需要检查 Postgres 和 BigQuery 如何使用相同的数据处理相同的查询。

我总共构建了 24 个查询：

• 6 用于 Postgres DB，它使用非聚合表（ populations 、 locations 、 age_groups ）
• 6 用于 Postgres DB，它使用populations_aggregated表
• BigQuery 的 6+6 个查询，使用聚合表和非聚合表。

让我分享聚合数据的 BigQuery 查询 #1 和 #5，以便您了解简单 (#1) 和复杂 #5 查询的复杂性。

按年查询汇总的美国人口：

 select sum (value), year from world_population.populations_aggregated where location_name = 'United States of America' group by 2 order by year asc

查询每个国家/地区每年的年龄中位数，按从大到小排序：

 --converts age to number with population_by_age_year_and_location as( select sum (value) as value, cast (regexp_replace(age_group_name, '\\+', '') as int64) as age, year, location_name from world_population.populations_aggregated where location_type = 'COUNTRY' group by 2,3,4), --calculates total population per country per year total_population_by_year_and_locations as ( select sum(value) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), --calculates total number of people in country per year age_multiplied_by_population_temp as ( select sum(value * age) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), median_per_year_country as ( select a.value / b.value as median, a.year, a.location_name from age_multiplied_by_population_temp a inner join total_population_by_year_and_locations b on a.location_name = b.location_name and a.year = b.year ) select * from median_per_year_country order by year desc, median desc

注意：您可以在我的 bitbucket 存储库中找到所有查询（链接在文章末尾）。

测试结果

对于运行查询，我使用了两个不同的 Postgres 服务器。 第一个有 1 个 CPU 内核和 4GB RAM，由 SSD 驱动器支持。 第二个服务器有 16 个 CPU 内核、64GB RAM，并且还使用 SSD 驱动器（第二个服务器具有 16 倍的 CPU 和 RAM 潜力）。

另外，请注意在运行测试期间数据库上没有负载。 我创建它们只是为了运行查询。 在现实生活中，查询会花费更长的时间，因为其他查询可能会同时运行，此外，那些并行运行的查询可能会锁定表。 为了检查查询速度，我使用了 pgAdmin3 和 BigQuery Web 界面。

在我的测试中，我得到了这些结果：

让我在查询 #1 和查询 #5 的条形图中显示这些结果。

注意： Postgres 数据库位于美国服务器上，而我位于欧洲，因此 Postgres 传输数据存在额外延迟。

BigQuery 性能和结论

根据我得到的结果，我得出以下结论：

• 在垂直扩展 Postgres 的情况下，即使是 16 倍，它在运行单个查询时也只能提供 10-25% 的性能。 换句话说，只有一个 CPU 内核和 4GB RAM 的 Postgres 服务器运行查询的时间与具有 16 个 CPU 内核和 64GB RAM 的服务器所需的时间非常相似。 当然，更大的服务器可以处理更大的数据集，但是，这并不能大大改善查询执行时间。
• 对于带有小表的 Postgres 连接（ locations表大约有 400 行， age_groups有 100 行）与在一个表中的聚合数据下运行查询相比并没有产生巨大的差异。 另外，我发现对于运行一到两秒的查询，使用内连接的查询更快，但对于长时间运行的查询，情况就不同了。
• 在 BigQuery 中，连接的情况完全不同。 BigQuery 不喜欢连接。 使用聚合数据和非聚合数据的查询之间的时间差异相当大（对于查询 #3 和 $5，它大约是两秒）。 这意味着，对于 BigQuery，您可以根据需要执行任意数量的子查询，但为了获得良好的性能，查询应该使用一个表。
• 对于使用简单聚合或过滤或使用小型数据集的查询，Postgres 更快。 我发现在 Postgres 中耗时不到 5 秒的查询在 BigQuery 中运行速度较慢。
• 对于长时间运行的查询，BigQuery 的性能要好得多。 随着数据集大小差异的增加，这些查询完成时间的差异也会增加。

何时使用 BigQuery 有意义

现在，让我们回到本文讨论的核心问题：什么时候应该真正使用 Google BigQuery？ 根据我的结论，我建议在满足以下条件时使用 BigQuery：

• 当您有在关系数据库中运行超过 5 秒的查询时使用它。 BigQuery 的想法是运行复杂的分析查询，这意味着运行进行简单聚合或过滤的查询毫无意义。 BigQuery 适用于“繁重”查询，即使用大量数据进行操作的查询。 数据集越大，使用 BigQuery 获得性能的可能性就越大。 我使用的数据集只有 330 MB（兆字节，甚至不是千兆字节）。
• BigQuery 不喜欢连接，因此您应该将数据合并到一个表中以获得更好的执行时间。 BigQuery 允许将查询结果保存在新表中，因此要创建新的聚合表，只需将所有数据上传到 BigQuery，运行将合并所有数据的查询，然后将其保存在新表中即可。
• BigQuery 适用于数据不经常更改并且您希望使用缓存的场景，因为它具有内置缓存。 这是什么意思？ 如果您运行相同的查询并且表中的数据没有更改（更新），BigQuery 将只使用缓存的结果，并且不会尝试再次执行查询。 此外，BigQuery 不会对缓存查询收费。 注意：即使是缓存查询也需要 1-1.2 秒才能返回结果。
• 当您想减少关系数据库的负载时，也可以使用 BigQuery。 分析查询是“繁重的”，在关系数据库下过度使用它们会导致性能问题。 因此，您最终可能会被迫考虑扩展您的服务器。 但是，使用 BigQuery，您可以将这些正在运行的查询移至第三方服务，因此它们不会影响您的主关系数据库。

最后，再说几句关于在现实生活中使用 BigQuery 的内容。 在我们的实际项目中，报告的数据每周或每月都会发生变化，因此我们可以手动将数据上传到 BigQuery。 但是，如果您的数据经常更改，那么在关系数据库和 BigQuery 之间同步数据可能并不那么简单，这是一个值得牢记的警告。

链接

您可以在此处找到本文中使用的示例数据，而 CSV 格式的查询和数据可在此处访问。