在我的职业生涯中, 我不得不为不同类型的报告和图表编写复杂的分析查询. 大多数情况下，它是一些图表，显示按日期、星期、季度等汇总的数据. 通常, 创建这样的报告是为了帮助客户识别趋势，并说明他们的业务是如何在高水平上执行的. 但是当 数据科学家和工程师 需要基于大数据集创建一个更广泛的报告?

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

也, 它需要实现一些缓存功能, 因此，客户端可以从缓存中获取数据，而不是实时运行查询. 如果不需要显示实时数据，这种方法可以完美地工作. 它可以显示一小时甚至一天前计算的数据. 因此，实际的报告/图表是使用缓存数据显示的，而不是基于实时数据.

转到b谷歌BigQuery

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

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

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

在这个项目中，我们尝试了几种不同的方法:

• 服务器的垂直扩展(向Postgres服务器添加RAM和CPU)
• 使用备选数据库，如Amazon Redshift等.
• 我们还研究了一个NoSQL解决方案, 但它们中的大多数都非常复杂，需要在架构上进行大量更改, 其中很多对客户来说都太大了.

最后，我们尝试了 谷歌BigQuery. 它满足了我们的期望，使我们能够在不做客户不愿批准的巨大改变的情况下完成工作. 但是谷歌BigQuery是什么，它是如何执行的?

BigQuery是一个基于rest的web服务，它允许您在大型数据集下运行复杂的基于sql的分析查询. 在我们将数据上传到BigQuery并执行与之前相同的查询之后 Postgres (语法出奇地相似), 我们的查询运行得快得多，大约需要一分钟才能完成. 最终，我们通过使用不同的服务获得了50倍的性能提升. 值得注意的是，其他db没有提供相同的性能增益, 让我们宽宏大量地说，他们甚至没有接近. 说实话, 我对BigQuery提供的性能提升印象深刻, 因为这些数据比我们所有人预期的都要好.

尽管如此，我不会宣称BigQuery是世界上最好的数据库解决方案. 虽然它对我们的项目很有效, 它仍然有很多局限性, 例如，每天对表的更新次数有限, 每个请求的数据大小限制, 和其他人. 您需要了解BigQuery不能用于替代关系数据库, 它是面向运行分析查询的, 而不是简单的CRUD操作和查询.

在本文中, 我将尝试比较使用Postgres(我最喜欢的关系数据库)和BigQuery的实际用例场景. 也, 在此过程中，我将提供一些建议, 也就是我对何时使用BigQuery是有意义的看法.

样本数据

比较Postgres和谷歌BigQuery, 我把每个国家的公共人口统计信息按国家分组, 年龄, 年份和性别(你可以从这里下载相同的数据 link).

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

1. 人口
2. 位置
3. 年龄_groups
4. 人口_aggregated

最后一个表只是聚合了来自前三个表的数据. 下面是DB模式:

的 人口 最后得到的表包含6个以上.900万行. 虽然不多，但对我的考试来说已经足够了.

基于样本数据, 我尝试构建可用于构建现实生活中的分析报告和图表的查询. 因此，我为下一个报告准备了查询:

1. 美国人口按年计算.
2. 2019年所有国家的人口从最大的国家开始.
3. 每年排名前五的“最古老”国家. “最老”指的是60岁以上人口占总人口比例最高的国家. 该查询每年应该给出5个结果.
4. 排名前五的国家按年合计, 哪里的男女人口差异最大.
5. 从“最老”到“最年轻”的国家，得到每年每个国家的平均年龄.
6. 找出每年“垂死”的前五个国家. “死亡”指的是人口正在减少(人口减少率最高)的国家。.

查询# 1, #2, 第6条非常简单直接, 但是第三个问题, #4, 第5条不太好写——至少对我来说是这样. 请注意，我是一名后端工程师，编写复杂的SQL查询并不是我的专长, 因此，有更多SQL经验的人可能会构建更智能的查询. 然而, 现在我们需要检查一下Postgres和BigQuery是如何用相同的数据处理相同的查询的.

我总共构建了24个查询:

• Postgres数据库使用非聚合表(人口, 位置, 年龄_groups)
• 对于Postgres DB，使用的是 人口_aggregated table
• 使用聚合表和非聚合表的BigQuery的6+6查询.

让我来分享一下聚合数据的BigQuery查询#1和#5，这样你就可以理解简单查询(#1)和复杂查询(# 5)的复杂性.

美国人口按年合计查询:

select
   总和(值),
   一年
从
   world_population.人口_aggregated
在哪里
   location_name = 'United States of America'
集团
   2
命令
   年asc

查询每年每个国家的年龄中位数，按最年长到最年轻排序:

——将年龄转换为数字
将population_by_年龄_一年_and_location设置为(
select
    Sum (value)作为value，
    Cast (regexp_replace(年龄_group_name， '\\+'， ") as int64) as 年龄，
    一年,
    location_name
从
    world_population.人口_aggregated
在哪里
    location_type = '国家'
按2,3,4分组)，

——计算每个国家每年的总人口
Total_population_by_一年_and_位置 as (
select
    Sum (value)作为value，
    一年,
    location_name
从
    population_by_年龄_一年_and_location
集团
    2,3
),

-计算每年在该国的总人数
Age_multiplied_by_population_temp
select
    Sum (value * 年龄)作为value，
    一年,
    location_name
从
    population_by_年龄_一年_and_location
集团
    2,3
),

Median_per_一年_country = (
select
    a.值/ b.值为中值，
    a.一年,
    a.location_name
从
    年龄_multiplied_by_population_temp一
    内部连接total_population_by_一年_and_位置 b on a.Location_name = b.Location_name和a.年= b.一年
)

从median_per_一年_country中选择*
按年份排序，按中位数排序

注意:您可以在我的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连接的小表(位置 表大约有400行 年龄_groups 与在位于一个表中的聚合数据下运行查询相比，100行是否没有产生巨大的差异. 也, 我发现对于运行1到2秒的查询, 使用内部连接的查询速度更快, 但是对于长时间运行的查询, 情况不同了.
• 在BigQuery的情况下，连接是完全不同的. BigQuery不喜欢连接. 查询之间的时间差, 哪些使用聚合数据和非聚合数据, 非常大(对于查询#3和$5，大约需要2秒). 这意味着, 对于BigQuery, 您可以执行任意多的子查询, 但是要有好的表现, 查询应该使用一个表.
• 对于使用简单聚合或过滤或使用小数据集的查询，Postgres更快. 我发现在Postgres中花费不到5秒的查询在BigQuery中工作得更慢.
• 对于长时间运行的查询，BigQuery的性能要好得多. 随着数据集大小差异的增加, 完成这些查询所需时间的差异也会如此.

当使用BigQuery是有意义的

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

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

最后，再多说几句在实际生活中使用BigQuery的话. 在我们的实际项目中, 报告的数据每周或每月都在变化, 这样我们就可以手动将数据上传到BigQuery. 然而, 如果您的数据频繁更改, 在关系数据库和BigQuery之间同步数据可能没有那么简单, 这是一个值得记住的警告.

链接

您可以找到本文中使用的样例数据 在这里，而CSV格式的查询和数据是可访问的 在这里.