什么时候使用Google BigQuery有意义（） _数据科学

本文概述

转向Google BigQuery
样本数据
检测结果
BigQuery的性能和结论
使用BigQuery有意义时

在我的职业生涯中, 我不得不为各种报告和图表编写复杂的分析查询。最常见的是一些图表, 显示按日期, 星期, 季度等汇总的数据。通常, 创建此类报告是为了帮助客户识别趋势并说明其业务在较高水平上的表现。但是, 当数据科学家和工程师需要基于大数据集创建更广泛的报告时, 会发生什么？
如果报告基于少量数据, 则可以通过在关系数据库下编写SQL查询来解决该任务。在此步骤中, 重要的是要了解编写查询的基础知识以及如何使查询更快和更有效。但是, 有时报告依赖于更大的数据集(例如, 表中有数百万和更多的行), 但是报告并不依赖于输入变量(参数), 或者你可能会发现值的数量非常少。在这种情况下, SQL查询可能会很慢, 因此对于用户来说, 等待执行查询并不是最佳选择。在这种情况下, 最常见的做法是在客户端请求报告之前提前运行查询。
而且, 它需要实现一些缓存功能, 因此客户端可以从缓存中获取数据, 而不是实时运行查询。如果你不需要显示实时数据, 则此方法非常有效。它可以显示一个小时甚至一天之前计算出的数据。因此, 实际报告/图表是使用缓存的数据显示的, 而不是基于实时数据的。
转向Google BigQuery 在从事制药行业的分析项目时, 我需要将邮政编码和药品名称作为输入参数的图表。我还需要展示美国特定地区的毒品之间的一些比较。
分析查询非常复杂, 最终在我们的Postgres服务器(带16 GB RAM的四核CPU)上运行了大约50分钟。我无法提前运行它并缓存结果, 因为查询将邮政编码和药品作为输入参数, 因此存在成千上万种组合, 并且无法预测哪个客户会选择。
即使我想尝试执行所有输入参数组合, 我的数据库也很可能崩溃了。因此, 是时候选择其他方法并选择一些易于使用的解决方案了。该图表对客户端很重要, 但是, 客户端尚未准备好对体系结构进行重大更改或完全迁移到另一个DB。
在该特定项目中, 我们尝试了几种不同的方法：

服务器的垂直缩放(将RAM和CPU添加到Postgres服务器)
使用替代数据库, 例如Amazon Redshift和其他数据库。
我们还研究了NoSQL解决方案, 但是大多数解决方案都很复杂, 需要对体系结构进行很多更改, 其中许多更改对于客户端而言可能太大了。

最后, 我们尝试了Google BigQuery。它满足了我们的期望, 并且使我们能够完成工作, 而无需进行客户不愿批准的巨大更改。但是什么是Google BigQuery？它的性能如何？
BigQuery是基于REST的Web服务, 它使你可以在大量数据下运行基于SQL的复杂分析型查询。将数据上传到BigQuery并执行与Postgres相同的查询(语法极为相似)后, 我们的查询运行得快得多, 大约需要一分钟才能完成。最终, 我们通过使用其他服务而获得了50倍的性能提升。值得一提的是, 其他DB并没有提供相同的性能提升, 我们要大方一些, 只是说它们还差得远。老实说, BigQuery所带来的性能提升给我留下了深刻的印象, 因为这些数据比我们任何人都希望的要好。
【什么时候使用Google BigQuery有意义（）】尽管如此, 我还是不会将BigQuery宣传为世界上最好的数据库解决方案。尽管它对我们的项目效果很好, 但它仍然有很多限制, 例如每天表中更新的数量有限, 每个请求的数据大小受到限制等。你需要了解, BigQuery不能用来替代关系数据库, 它是针对运行分析查询的, 而不是针对简单的CRUD操作和查询的。
在本文中, 我将尝试比较Postgres(我最喜欢的关系数据库)和BigQuery在现实中的用例场景。另外, 我会在此过程中提供一些建议, 即我对使用BigQuery何时有意义的意见。
样本数据为了比较Postgres和Google BigQuery, 我按照国家, 年龄, 年龄和性别对每个国家/地区进行了公开的人口统计信息(你可以从此链接下载相同的数据)。
我将数据添加到四个表中：

人口
地点
年龄组
人口总数

最后一个表只是来自前三个表的汇总数据。这是数据库模式：

文章图片
我最终得出的人口表包含超过690万行。虽然不算太多, 但足以进行我的测试。
根据示例数据, 我尝试构建可用于构建现实生活中的分析报告和图表的查询。因此, 我为下一份报告准备了查询：

美国的人口按年累加。
从最大的国家开始, 所有国家的2019年人口。
每年排名前五的” 最古老” 国家。 “ 最古老的” 是指60岁以上人口占总人数百分比最高的国家。该查询每年应给出五个结果。
按年度汇总的前五名国家中, 男性和女性人口之间的差异最大。
从” 最老” 到” 最年轻” 的国家/地区, 获取每年每个国家/地区的中位数(平均)年龄。
每年找出排名前五的” 垂死” 国家。 “ 就寝” 是指人口在减少的国家(人口最多的国家)。

查询＃1, ＃2和＃6非常容易和直接, 但是查询＃3, ＃4和＃5并不是那么容易编写-至少对我而言。请注意, 我是一名后端工程师, 编写复杂的SQL查询并不是我的专长, 因此拥有更多SQL经验的人可能会构建更智能的查询。但是, 目前, 我们需要检查Postgres和BigQuery如何处理具有相同数据的相同查询。
我总共建立了24个查询：

对于使用非汇总表(填充, 位置, age_groups)的Postgres DB为6
对于Postgres DB, 使用人口统计表6
使用汇总表和非汇总表的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界面。
在测试中, 我得到了以下结果：

	Postgres (1 CPU 4 RAM, SSD)	Postgres (16 CPU 64 RAM, SSD)	大查询
	汇总	未汇总	汇总	未汇总	汇总	未汇总
Query 1 (US Population aggregated by Years)	1.3秒	0.96秒	0.87s	0.81秒	2.8秒	2.4秒
Query 2 (Population by Countries in 2019)	1.1秒	0.88秒	0.87s	0.78秒	1.7秒	2.6秒
Query 3 (Top 5 Oldest nations by years)	34.9秒	35.6秒	30.8秒	31.4秒	15.6秒	17.2秒
Query 4 (Top 5 Countries with the biggest difference in male and female population)	16.2秒	15.6秒	14.8秒	14.5s	4.3秒	4.6秒
Query 5 (Age median per country, year)	45.6秒	45.1秒	38.8秒	40.8秒	15.4秒	18岁
查询6(每年排名前5位的” 垂死” 国家)	3.3秒	4.0秒	3.0秒	3.3秒	4.6秒	6.5s

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

文章图片
注意：Postgres数据库位于美国服务器上, 而我位于欧洲, 因此Postgres传输数据会有额外的延迟。
BigQuery的性能和结论根据我得到的结果, 我得出以下结论：

如果纵向扩展Postgres, 即使是16倍, 它在运行单个查询时也只能提供10-25％的性能。换句话说, 只有一个CPU内核和4GB RAM的Postgres服务器运行查询的时间与具有16个CPU内核和64GB RAM的服务器所需的时间非常相似。当然, 较大的服务器可以处理更大的数据集, 但是, 这并没有在查询执行时间上带来很多改进。
对于使用小型表的Postgres联接(位置表具有约400行, age_groups具有100行), 与在位于一个表中的聚合数据下运行查询相比, 它们不会产生巨大差异。此外, 我发现对于运行一到两秒钟的查询, 使用内部联接的查询速度更快, 但是对于长时间运行的查询, 情况则有所不同。
在BigQuery中, 具有联接的情况完全不同。 BigQuery不喜欢加入。使用聚合数据和非聚合数据的查询之间的时间差异很大(对于查询3和$ 5, 查询时间大约是两秒钟)。这意味着, 对于BigQuery, 你可以根据需要执行任意多个子查询, 但是为了获得良好的性能, 查询应使用一个表。
对于使用简单聚合或过滤或使用小型数据集的查询, Postgres速度更快。我发现在Postgres中花费不到五秒钟的查询在BigQuery中的运行速度较慢。
对于长时间运行的查询, BigQuery的性能要好得多。随着数据集大小差异的增加, 完成这些查询所花费的时间也会随之增加。

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

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

最后, 再说几句有关在现实生活中使用BigQuery的信息。在我们的实际项目中, 报告的数据每周或每月更改一次, 因此我们可以手动将数据上传到BigQuery。但是, 如果你的数据经常更改, 则在关系数据库和BigQuery之间同步数据可能不是那么简单, 这是一个需要牢记的警告。
链接
你可以在此处找到本文中使用的示例数据, 而在此处可以访问CSV格式的查询和数据。