Tag: Amazon Redshift


使用 Amazon Redshift 中的查询监控规则管理查询工作负载

本文主要介绍了如何利用Amazon Redshift的WLM(工作负载管理)功能,监控数据仓库的查询性能,从而优化队列优先级并保障关键任务的执行。本文还列出了三个常见场景,给出了简单的配置过程。

众所周知,数据仓库的工作负载由于周期性、潜在高开销的数据探索查询以及SQL开发人员不同的技能水平等会出现比较大的性能变化。

为了在面临高度变化的工作负载下仍然能使Redshift集群获得较高的性能,Amazon Redshift工作负载管理(WLM)使您能够灵活地管理任务优先级和资源使用情况。通过配置WLM,短时间,快速运行的查询不会停留在需要较长时间运行的查询之后的队列中。 但尽管如此,某些查询有时可能会陷入不相称的资源分配,并影响系统中的其他查询。 这种查询通常被称为流氓查询或失控查询。

虽然WLM提供了一种限制内存使用并将超时查询移动到其他队列的方法,但多重精细控制依然很需要。您现在可以使用query monitoring rules查询监视规则为查询创建资源使用规则,监视查询的资源使用情况,然后在查询违反规则时执行操作。

工作负载管理并发和查询监控规则

在Amazon Redshift环境中,单个集群最多可以同时连接500个连接。 吞吐量(Throughput)通常表示为每小时的查询量以最大化性能,但像MySQL这样的行数据库使用并发连接数进行衡量。 在Amazon Redshift中,工作负载管理(WLM)可以最大限度地提高吞吐量,而不太考虑并发性。 WLM有两个主要部分:队列和并发。 队列允许您在用户组或查询组级别分配内存。 并发或内存是如何进一步细分和分配内存到一个查询。

例如,假设您有一个并发度为10的队列(100%内存分配)。这意味着每个查询最多可以获得10%的内存。 如果大部分查询需要20%的内存,那么这些查询将交换到磁盘,导致较低的吞吐量。 但是,如果将并发度降低到5,则每个查询分配20%的内存,并且最终结果是更高的吞吐量和更快的SQL客户端响应时间。 当从行数据库切换到基于列的数据库的时候,常见的错误认知是认为更高的并发性将产生更好的性能。

现在你了解了并发性,这里有更多关于查询监控规则的细节。 您可以基于资源使用情况定义规则,如果查询违反了该规则,则会执行相应的操作。 可以使用十二种不同的资源使用指标,例如查询使用CPU,查询执行时间,扫描行数,返回行数,嵌套循环连接等。

每个规则包括最多三个条件,或谓词,和一个动作。谓词由一个指标,比较条件(=、<、>),和一个值组成。如果所有的谓词满足任何规则,该规则的行动被触发。可能的规则操作包括日志记录、跳过任务和中止任务。

这样就可以在导致严重问题前捕获流氓或失控查询。该规则触发一个动作来释放队列,从而提高吞吐量和响应速度。

例如,对于专用于短时运行查询的队列,您可能会创建一个规则来中止超过60秒的查询。 要跟踪设计不当的查询,您可能会有另一个规则记录包含嵌套循环的查询。 在Amazon Redshift控制台中有预定义的规则模板让您使用。

使用场景

使用查询监控规则来执行查询级别的操作,从简单地记录查询到中止查询,以下所有采取的操作都记录在STL_WLM_RULE_ACTION表中:

  • 日志记录(log):记录信息并继续监视查询。
  • 跳出(hog):终止查询,并重新启动下一个匹配队列。 如果没有其他匹配队列,查询将被取消。
  • 中止(abort):中止违反规则的查询。

以下三个示例场景显示如何使用查询监视规则。

场景1:如何管理您临时查询队列中的未优化查询?

连接两个大表的失控查询可能返回十亿行或更多行。 您可以通过创建规则来中止返回超过十亿行的任何查询来保护您的临时队列。 在逻辑上如下所示:

IF return_row_count > 1B rows then ABORT.

在以下截图中,任何返回BI_USER组中超过十亿行的查询都将中止。

场景2:如何管理和控制未调优的CPU密集型查询?

偶尔引起CPU飙升的查询不一定有问题。 然而,持续的高CPU使用率可能会导致其他并发运行查询的延迟时间增加。 例如,在较长时间内使用高百分比CPU的未调优查询可能是由于不正确的嵌套连接引起的。

您可以通过创建规则来中止超过10分钟使用80%或更多CPU的任何查询来提高群集吞吐量和响应能力。 在逻辑上如下所示:

IF cpu_usage > 80% AND query_exec_time > 10m then ABORT

以下屏幕截图显示,任何使用超过80%CPU超过10分钟的查询都将中止。

您可以通过使用80%CPU记录查询超过5分钟进一步扩展此规则,并终止使用了80%CPU超过10分钟的查询。 在逻辑上如下所示:

IF cpu_usage > 80% AND query_exec_time > 5m then LOG and  IF cpu_usage > 80% AND query_exec_time > 10m then ABORT

以下屏幕截图显示,系统将记录使用了80%CPU并运行5分钟以上的查询,并且中止使用了80%CPU并运行超过10分钟的查询。

场景3:如何监视和记录没有任何进展的查询?

例如,在混合工作负载环境中,ETL作业可能会将S3中的大量数据从大量的数据传输到Amazon Redshift中。 在数据摄取过程中,您可能会发现一个COPY命令被卡在队列中而没有进行任何进展。 这样的查询可能会增加数据吞吐延迟并影响业务SLA。

您可以通过创建跟踪和记录查询的规则来查找此类查询。 创建一个规则来查找具有低CPU利用率和过长执行时间的查询,例如,使用1%CPU记录查询超过10分钟的规则。 在逻辑上如下所示:

IF cpu_usage < 1% AND query_exec_time > 10m then LOG

以下屏幕截图显示,系统将记录使用1%CPU并运行10分钟以上的查询。

总结

Amazon Redshift是一个功能强大,全托管的数据仓库,可以在云计算框架中显著提升性能并降低成本。 但是,查询集群资源(流氓查询)可能会影响您的体验。

在这篇文章中,我们讨论了如何使用查询监视规则帮助过滤和中止不符合要求的任务。 这反过来也可以帮助您在支持混合工作负载时顺利地进行业务操作,以最大限度地提高集群性能和吞吐量。

如果您有任何问题或建议,请在下面留言。


关于作者

Gaurav Saxena是Amazon Redshift查询处理团队的软件工程师。 他负责Amazon Redshift工作负载管理和性能改进的几个方面。 在业余时间,他喜欢在他的PlayStation上玩游戏。

Suresh Akena是AWS专业服务的高级大数据/ IT转型架构师。 他与企业客户合作,为大型数据战略提供领导,包括迁移到AWS平台,大数据和分析项目,并帮助他们在使用AWS时优化和改进数据驱动应用的上市时间。 在业余时间,他喜欢和他8岁和3岁的女儿一起玩,看电影。

译者:

屈铭,AWS中国专业服务团队大数据咨询顾问

曾供职于亚马逊电商和澳大利亚智能交通研究机构,拥有多年电商平台和智慧供应链的数据分析经验。现任职于AWS中国专业服务团队,主要为客户提供云上大数据平台设计,数据仓库解决方案和优化,数据建模等咨询服务。

Redshift又添新功能:让用户直接查询S3中的海量数据而无需复制到本地

背景

在Amazon Redshift 数据仓库为核心的用户,常常陷入一个困境,要想利用该MPP架构的云端数据仓库能力,用户通常需要利用Redshift的 copy命令将数据从S3并行拷贝到Redshift中,如果在数据量比较大的情况下,成本上的考量和业务上的诉求的矛盾会让用户犹豫不定; 尤其突出的矛盾是,客户的业务部门的需求涵盖数据范围同时包含数据仓库的数据和放在S3上的中间或者原始数据集,此时,我们能怎么做?

AWS大数据最佳实践的启示

AWS大数据最佳实践告诉我们要将数据的存储和处理、分析相分离,比如在Amazon EMR服务架构中(如下图),要分析的数据集按照一定的格式压缩存储在Amazon S3上,在EMR中通过Hive定义外表关联到S3上的数据,但不复制到EMR本地,从而实现了数据存储和分析处理的解耦;在大量的用户实践中,我们发现如此的架构优化,可以帮助客户节约大量的存储成本,同时,EMR分析集群无状态化,可以按需动态启动和停止EMR集群,从而优化了计算成本。同理,我们能否在Redshift数据仓库中引入类似的外部表的概念呢?

Amazon Redshift Spectrum简介

Amazon Redshift Spectrum是Redshift的一个新特性,它可以帮助客户将Redshift的分析能力从本地存储扩展到Amazon S3数据湖中海量的非结构化数据而不需要加载数据。通过Redshift Spectrum您可以将热数据存储到 Amazon Redshift 群集中,以获得本地磁盘性能;同时使用 Amazon Redshift Spectrum 将您的查询扩展到 Amazon S3 中存储的冷数据,以获得无限的可扩展性和低成本。

详细情况请参考官方介绍:https://aws.amazon.com/cn/redshift/spectrum/

目标人群及应用场景

该新功能的推出完善了Redshift数据仓库用户的大数据分析的应用场景,客户可以直接利用Redshift和Redshift Spectrum的能力同时处理本地和S3上的数据集;所以,目标受众是Redshift数据仓库的用户比如金融,电商,游戏等等行业客户。

从应用场景来看,可以满足如下业务需求:

  • 针对数据仓库本地数据和S3上的数据提供一致的、熟悉的数据仓库操作体验
  • 提供终端用户统一的BI或者SQL客户端接入
  • 跨数据仓库热数据和S3冷数据的复杂混合查询
  • 满足低频的业务全数据的低成本即席查询

大数据处理示例管道

本大数据处理示例管道展示了以Redshift数据仓库为核心的典型用户场景,原始数据,中间结果和ETL处理之后的数据都保存在数据湖Amazon S3上;用户通过BI工具或者熟悉的SQL客户端通过Redshift(包括Redshift Spectrum)操作所有的业务数据,包括大数据量的原始数据和存储在数据仓库本地的热数据;客户无需专门为了某个业务的特殊需求,将数据从冷数据从S3复制到Redshift本地再作分析。

支持的数据格式

Redshift Spectrum 使用您已使用的开发数据格式在 Amazon S3 中直接查询数据,这些格式包括

  • 文本文件如 CSV格式文件
  • 日志文件如TSV格式
  • 列式格式如Apache Parquet和Hive中的RCFile格式文件
  • 二进制文件:Sequence格式文件
  • 压缩格式支持:gzip、snappy、bz2

动手做个试验

我们通过一个动手实验来体验一下,Redshift Spectrum的新功能,该新功能目前在如下三个区域可用:us-east-1、us-east-2和us-west-2;而且Redshift Spectrum只支持本区域的S3数据查询。

创建和关联IAM角色

Redshift Spectrum的功能是针对S3里面的数据,所以需要授权能够只读访问S3,同时定义的外部表默认保存在Athena数据目录里面(详情见下一个章节),所以同时需要授权访问Athena。

本实验创建了一个IAM角色SpectrumRole,权限里面要包含AmazonS3ReadOnlyAccessAmazonAthenaFullAccess 策略。

创建好角色之后,将角色管理到Redshift集群,管理控制台转到Redshift页面,勾选要应用的集群,点击管理角色按钮进行操作:

创建一个External Schema

经过上一个步骤之后,我们Redshift集群的权限已经准备好,接下来和你可以使用你熟悉的SQL客户端来定义表结构和执行查询。与Redshift本地数据表有些差别的地方是,Redshift Spectrum引入了外部Schema和外部表的概念;通过Redshift Spectrum定义的外部数据库存放在外部的数据目录里面,Redshift Spectrum将默认会将该外部数据库定义存放到了Athena的数据目录里,当然也可以显式指定存储在你的EMR集群的Hive的元数据目录里面;

下面我们执行如下SQL语句为Redshift创建一个外部数据库”spectrumdb”:

create external schema spectrum
from data catalog
database 'spectrumdb'
iam_role 'arn:aws:iam::183449792837:role/SpectrumRole'
create external database if not exists;

同时在Athena的数据目录里面可以看到我们刚刚创建的外部数据库:

我们也可以利用以下语法,直接从Redshift Spectrum中引用Athena的已经存在的数据库:

create external schema athena_schema from data catalog
database 'sampledb'
iam_role 'arn:aws:iam::183449792837:role/SpectrumRole'
region 'us-east-1';

更多详情请参考该命令的官方页面:http://docs.aws.amazon.com/zh_cn/redshift/latest/dg/r_CREATE_EXTERNAL_SCHEMA.html

创建演示的External Tables

上一个章节我们在Athena中新建了一个外部Schema “spectrum”,接下来我们就可以在该External Schema中定义关联到S3的外部表(External Table):

create external table spectrum.sales(
salesid integer,
listid integer,
sellerid integer,
buyerid integer,
eventid integer,
dateid smallint,
qtysold smallint,
pricepaid decimal(8,2),
commission decimal(8,2),
saletime timestamp)
row format delimited
fields terminated by '\t'
stored as textfile
location 's3://jxlabs/spectrum/sales/';

该SQL语句在External Schema的spctrum中定义了一个sales表,映射到S3的文本数据文件,该文件内容字段以tab分隔;

具体创建External Table 语句请参考官方文档:http://docs.aws.amazon.com/zh_cn/redshift/latest/dg/r_CREATE_EXTERNAL_TABLE.html

 

用户可以查询SVV_EXTERNAL_TABLES视图查看所定义的所有的External Tables:

select * from SVV_EXTERNAL_TABLES swets;

结果参考如下,从查询结果中,我们可以了解更多细节,比如序列化/反序列化的库等。

查询演示

我们在上一个章节创建了一个sales表,下面我们进行一些查询,来体验下如何混合查询Redshift本地数据和S3上的冷数据:

第一个查询:查询S3上数据表Sales的数据

select count(*) from spectrum.sales;

该表有18万行左右的数据,执行时间大概在2秒左右:

第二个查询:混合查询S3上数据表Sales的数据和本地数据表event

event表的数据结构如下:

select top 10 event.eventname, sum(spectrum.sales.pricepaid) from spectrum.sales, event
where spectrum.sales.eventid = event.eventid
group by eventname
order by 2 desc;

该查询基于eventid把sales和events做了一次join操作找出了总销量Top 10的活动(event):

在该SQL语句前加上Explain来查看Redshift的执行计划,发现,针对S3上的数据,Redshift Spectrum 会负责执行S3 Seq Scan、S3 HashAggregate和S3 Query Scan操作;同时Redshift不会执行外部表的统计信息(statistics),执行计划会提示“Tables missing statistics: spectrum_sales”,该执行计划会默认本地表的数据量要远远少于存储在S3上的外部数据量。

explain
select top 10 event.eventname, sum(spectrum.sales.pricepaid) from spectrum.sales, event
where spectrum.sales.eventid = event.eventid
group by eventname
order by 2 desc;

与 Amazon Athena、EMR及S3的关系

通过前面的章节我们对Redshift Spectrum有了比较直观的理解,这个章节我们从Redshift 的角度来探讨下和Amazon Athena,EMR及S3的关系,我做了一个示意图如下:

Amazon Redshift本身提供了适合企业数据仓库的大数据计算,支持从Amazon S3、ERM、Dynamo DB甚至远程主机通过多节点并行复制数据到Redshift本地表,同时提供了UNLOAD命令帮助客户直接将本地数据卸载保存到Amazon S3;发布Redshift Spectrum功能,进一步帮助客户优化以Redshift为核心的数据仓库场景,进一步将数据和计算分析进行解耦,让用户可以灵活根据具体需求将数据存储在Redshift本地或者S3上,取得性能、可扩展性和成本的最优化结果。

Redshift Spectrum 和Athena的关系通过官方文档可以看出,底层的高级查询优化技术和Athena是独立的,但他们可以共享针对S3上数据的外部数据表定义,这样做的结果是,通过Redshift Spectrum定义的存储在Athena数据目录中的外部表,同时也可以通过Athena直接进行查询;但只有从Redshift才能执行针对S3数据的外部表和Redshift 本地表的混合查询。

监控和查看查询细节

要查看Redshift Spectrum相关查询的相关指标,我们可以通过查询一下两个视图进行了解:

  1. SVL_S3QUERY_SUMMARY

通过该视图,我们可以知道系统中所有运行过的S3 查询,以及该查询涉及到的:

  • 通过Redshift Spectrum返回给集群的数据量(bytes);
  • Redshift Spectrum节点最大和平均请求时间
  • 扫描的S3数据量(bytes),这也是反应到Redshift Spectrum成本的数据量值,比如目前每TB收费5美金,就是指这里的扫描的S3数据量。
  • 扫描的S3的文件数量,以及最大和平均文件大小

下面是该视图的字段供参考:

2. SVL_S3QUERY

该视图可以让用户从segment和node slice角度查看Redshift Spectrum的S3查询细节。与SUMMARY视图相比,粒度更细,多了segment,slice,node等字段:

性能和成本

通常,相对比Redshift本地数据存储在EBS或者本地磁盘的查询,针对S3的数据扫描会慢一些,因为目前Redshift Spectrum没有缓存,没有排序键等等;但对于交互式查询,Redshift Spectrum提供了并行处理和无限扩展的优化,性能足够应对非常多的用户场景。而且,如果存储在S3中的数据格式采用列式结构如Parquet,针对不需要扫描全部字段,仅仅涉及部分列的查询可以极大提升性能和降低成本。

对于一些对性能要求高的业务需求,我们可以定期将计算后的结果经过存储在Redshift本地表当中,使用我们熟悉的Redshift的优化方法,提升处理性能。Redshift Spectrum 的成本计算和Amazon Athena 类似,都是针对处理的S3数据量来计算,每处理TB数据目前5美金;

总结

本文和大家一起学习了Redshift Spectrum的新功能,并动手做了一个实验来进一步体验并思考在实际工作中可能遇到的问题。同时,我们一起梳理了以Amazon Redshift数据仓库为核心的大数据架构,以及和其他Amazon 大数据服务之间的相互关系。相信很多客户都可以从该新功能获益。

作者介绍

薛军

AWS 解决方案架构师,获得AWS解决方案架构师专业级认证和DevOps工程师专业级认证。负责基于AWS的云计算方案架构的咨询和设计,同时致力于AWS云服务在国内的应用和推广,在互联网金融、保险、企业混合IT、微服务等方面有着丰富的实践经验。在加入AWS之前已有接近10年的软件开发管理、企业IT咨询和实施工作经验。

 

利用Amazon Redshift构建新一代数据分析BI系统

本文主要介绍了Amazon Redshift新一代企业级云平台数据仓库服务,并结合实际的客户使用案例与场景描述了如何基于Amazon Redshift构建高可靠,性能优化,并且成本节约的数据仓库系统。因为Amazon Redshift优异的计算效率与性能,基于Amazon Redshift的BI系统被广泛地应用于互联网数据分析类场景,例如电商中产品维度报表的计算生成,社交类应用中用户画像计算与分析,或者用于替代传统的数据仓库的解决方案。

Amazon Redshift是性能优异并且完全托管的PB级别数据仓库服务。Amazon Redshift提供了标准SQL数据库访问接口,并且可以十分方便地与现有的主流商业智能数据分析工具整合,构建企业级数据仓库。

Amazon Redshift高性能硬件架构

Amazon Redshift底层硬件是基于高度定制化的高性能硬件节点,整个集群是由头节点(leader node,又称领导节点)与计算节点(compute node)的架构组成,如图1所示。其中,头节点负责与所有的客户端程序(标准SQL兼容的客户端,或者通过JDBC/ODBC访问的客户端应用)进行通信,并把对应的SQL命令进行编译后分发给底层的计算节点。同时,头节点还负责存储所有的数据仓库元数据(metadata)。需要注意的是,所有的计算节点同时也是存储节点(单个节点最大支持2TB的存储量)。每个计算节点上配置有定制化的高性能CPU、内存及直接连接硬盘的存储介质。当用户数据仓库的数据量增加的时候,可以通过动态地增加计算节点的数目,以及升级对应计算节点的硬件配置提升集群的存储容量与计算能力。同时,节点与节点的通信是基于AWS定制化的高速内网带宽,减少了因为数据传输带来的时延,提高了计算效率。

图1 Amazon Redshift架构示意图

目前,Amazon Redshift主要支持两大类计算节点类型——DS1/DS2与DC1。其中DS类型节点是为大数据量的工作复杂优化而设计,而且DS2是DS1的硬件升级版本。DC1主要应用于数据计算要求相对更高但是数据总量相对较小的场景。

从应用的角度结合上述架构看,Amazon Redshift的头节点负责基本的SQL编译,查询计划的优化,以及数据仓库原数据的存储。所有的用户数据会以列式存储的方式存放与计算节点之上。因为大部分数据仓库的应用计算围绕于具体的属性列做查询筛选,所以列式存储的计算方式大大提高了数据仓库的计算效率。同时,以MPP的架构组织数据为例,Amazon Redshift也从表设计的角度为用户提供了数据在计算节点的存放方式,用户可以根据具体的SQL表中的键值做分布式存放,或者对某些常用维度表做所有计算节点的全分布存放,从而大大减少数据在节点之间的传输,以提高整体的计算效率。从图1还可以看到,计算节点以MPP的方式并行的从Amazon S3、Amazon DynamoDB、SSH及Amazon EMR并发的实现数据快速加载。另外,Amazon Redshift的整体设计实现了数据的多份冗余存放(对用户使用量透明)——计算节点之间冗余存放,同时定期对数据以增量快照的方式存放于高持久度的Amazon S3之上。

基于Amazon Redshift的BI大数据分析架构

Amazon Redshift针对数据仓库提供了优异的计算与存储效率,利用Amazon Redshift托管服务可以十分方便地构建智能数据仓库系统。同时,因为AWS云计算平台提供了一整套完整的数据分析套件与工具,利用这些组件与Amazon Redshift相结合,可以十分轻松地实现性能优化、成本经济、可靠性强、安全度高的大数据分析架构。图2为一个典型的数据分析平台的基础数据架构。

图2 基于AWS数据分析组件的数据架构

图2中的架构是基于AWS的典型的实时与批量叠加的大数据分析架构。其中Amazon Kinesis是托管的高速实时流分析服务,可以从前端的应用服务器(例如Web服务器)或者移动的客户端(手机等移动设备或者IoT设备)直接注入流式数据,数据可以通过EMR进行流式处理和计算(例如基于Spark Stream的EMR计算框架),并将数据存储于Amazon DynamoDB或者对象存储S3之上。其中,Amazon DynamoDB是托管的高性能NoSQL数据库,可以承载100TB数据量级别而响应时间低于10毫秒。S3作为高可靠(11个9的持久度)的对象存储,在大量的AWS应用场景中,被作为典型的数据湖(data lake)的应用。利用Amazon EMR对S3上的原始数据进行基本的ETL或者结构化操作之后,可以直接从S3以SQL的“copy”命令复制到Amazon Redshift数据仓库中进行SQL的维度计算。另外,可以利用AWS集成的BI分析工具(Quick Sight)或者已有的商业套件直接实现对Amazon Redshift上的数据进行分析与展示。

在实际的业务场景中,数据库的来源包含Amazon DynamoDB或者Amazon RDS这类业务数据库,以及用户活动日志或者行为日志等Web前端日志。这些数据需要以增量的方式汇聚于AWS S3及ETL之后进入到Amazon Redshift之中。常用的做法,可以利用AWS的Data Pipeline服务直接定义对应的原端数据源及对应的后端数据目标,自定义采集周期,一次性配置之后就可以直接进行数据通路的增量拷贝。

小红书电商基于Amazon Redshift的用户数据分析

小红书是新一代的社区电商,它将海外购物分享社区与跨境电商相结合,精准捕捉85后和90后的消费升级需求,迅速发展成为极具影响力的全球购物分享社区。目前小红书的注册用户数量已超过1800万,其中近90%是女性、超过50%是90后。作为新一代消费人群,这些用户有着共同的价值观,更注重感觉和体验,对优质商品和生活充满向往。“社区+电商”的模式推动了小红书的快速发展,在电商平台成立的半年内,其销售额就达到7亿人民币。

与小红书自身高速发展的业务模式一样,小红书的数据架构与数据分析团队也经历了从基本日志服务器脚本分析到目前利用Amazon Redshift作为数据仓库与数据分析核心工具的演化。图3是目前小红书数据分析的主要架构。

图3 小红书数据架构示意图

NoSQL DB(主要是MongoDB)小红书的业务数据库数据,其中的数据库业务日志通过Fluentd的流式客户端经过Amazon Kinesis的方式进入到AWS中国北京区域。之后,Amazon Kinesis的流式数据会写入S3作为整个原始数据存储。当然,Amazon S3还会作为数据湖汇聚其他的前段web服务器的日志,或者其他的数据来源。其中,构建于AmazonEMR的Spark集群对S3中的日志进行批量和实时的ETL。之后,结构化的数据从S3通过并行的拷贝直接进入Amazon Redshift进行数据分析师与工程师的业务分析。整个数据分析链条与分析架构实现了端到端的实时分析。其中,数据通路上的各个组件,Amazon Kinesis、Amazon S3、Amazon EMR与Amazon Redshift可以十分简单与方便地实现水平扩展以提高计算与处理能力。因为S3作为整个数据架构的数据湖,并且基于S3自身分布式无限制的容量大小的设计,小红书的架构系统可以十分方便的实现数据容量的夸张和升级。同时,因为EMR利用EMRFS实现了存储S3(类似于传统的Hadoop集群的HDFS)与计算(EMR计算实例)的分离,从而从架构上解决了数据系统ETL弹性与增长的需求。小红书又利用Amazon Kinesis来实时地解析同步用户行为日志,并开发了销售实时监控系统。使用AWS使小红书在两个方面获益匪浅:其一是大幅度缩短了数据处理系统上线的时间;其二是改变了整个公司的业务模式。目前,小红书数据团队正在持续优化其数据处理架构,包括提供更直观的展示平台、提升处理速度等,同时包括Spark在内的离线计算系统也开始投入使用。目前,业务数据的增量以每个月3~5TB的存量增加,并且随着业务增加还有快速递增的趋势。

小红书的实际使用经验也已经被更多的电商用户及数据分析团队所采用。

综上所述,Amazon Redshift作为一款AWS数据仓库的明星产品,因为其优异的计算性能(10亿条记录TPC-H测试个位数秒级别)被越来越多的用户熟悉和使用,并且结合Amazon天然的高可扩展的云平台被广泛地应用于各个行业应用和数据分析中。

作者介绍

肖凌,AWS解决方案架构师,负责基于AWS的云计算方案架构的咨询和设计,同时致力于AWS云服务在国内和全球的应用和推广,在大规模并发后台架构、跨境电商应用、社交媒体分享 、Hadoop大数据架构以及数据仓库等方面有着广泛的设计和实践经验。在加入AWS之前曾长期从事移动端嵌入式系统开发,IBM服务器开发工程师。并负责IBM亚太地区企业级高端存储产品支持团队,对基于企业存储应用的高可用存储架构和方案有深入的研究。

手把手教你调校AWS PB级数据仓库

什么是一个好的数据仓库?
Redshift是AWS云计算中的一个完全托管的,PB级别规模的数据仓库服务。即使在数据量非常小的时候(比如几百个GB的数据)你就可以开始使用Redshift,Redshift集群可以随着你数据的增加而不断扩容,甚至达到PB级。云计算中数据仓库的优势非常明显,不需要license,不需要预先配置非常大的数据仓库集群,扩容简单,仅仅需要为你实际所使用的数据仓库付费。
Redshift作为一个企业级数据仓库完全支持SQL语法,无学习成本,支持很多种客户端连接,包括各种市场上的BI工具,报表以及数据分析工具。

Redshift的概览
Redshift通过支持大规模并行处理(MPP),列式存储,对不同列数据使用不同数据压缩算法,关系型数据仓库(SQL),灵活的扩容管理等众多优点,兼顾了数仓性能,同时也考虑学习成本及使用成本。

Redshift系统架构及要点
图1,Redshift系统架构图

  • 主节点负责客户端与计算节点之间的所有通讯,编译代码并负责将编译好的代码分发给各个计算节点处理,负责分配数据到不同的计算节点,主节点对客户不可见的,无需客户管理主节点的压力,更重要的是主节点免费。
  • 计算节点是具体的干活的,并处理好的任务送给主节点进行合并后返回给客户端应用程序。每个计算节点都有自己独立的CPU,内存以及直连存储。Redshift集群规模大小通常就是指计算节点的个数以及计算节点机器类型。
  • 节点分片是指将计算节点被分成若干的分片,根据计算节点类型不同,每个节点包含的分片数量不同,通常1个vCPU对应一个分片,ds2的机型除外。每个分片都会分配独立的内存及存储资源,接受来自主节点分配的任务。分片跟另外一个重要概念Dist Key紧密相关, 这里先提一下,接下来会具体介绍Dist Key。
  • 排序键(Sort Key)是一个顺序键,即Redshift会根据这个键来将数据按顺序存储在硬盘上。Redshift的查询优化程序(只要理解有这么个东西存在就好,客户不需要任何维护,对客户也是透明的)也会根据这个排序来进行执行查询优化计划。这是Redshift性能调优的一个非常重要的参数。
  • 分配键(Distribution Key)是控制加载到表的数据如何分布在各个计算节点的一个键,有好几种分布的风格,接下来会重点讲到,这是Redshift调优的非常重要的另外一个参数。

Redshift的几个常用最佳实践
选择最佳排序键

  • 如果最近使用的数据查询频率最高,则指定时间戳列作为排序键的第一列;
  • 如果您经常对某列进行范围筛选或相等性筛选,则指定该列作为排序键;
  • 如果您频繁联接表,则指定联接列作为排序键和分配键;

熟悉Redshift的朋友可能知道可以指定多列作为排序键,而且排序键还有两种方式,组合式和交叉式。限于篇幅的原因,在接下来的调优测试中我们采用的是某一列作为排序键,如果有对其他排序键风格感兴趣的朋友,可以单独联系我们进行讨论。

选择最佳分配键

选择表分配方式的目的是通过在执行查询前将数据放在需要的位置来最大程度地减小重新分配步骤的影响,最好这个查询不需要二次移动数据。

分配键有三种风格,均匀分布(Even),键分布(Key),全分布(All),默认是均匀分布。

  • 根据共同列分配事实数据表和一个维度表;

事实数据表只能有一个分配键。任何通过其他键联接的表都不能与事实数据表并置。根据联接频率和联接行的大小选择一个要并置的维度。将维度表的主键和事实数据表对应的外键指定为 DISTKEY。

  • 根据筛选的数据集的大小选择最大的维度;

只有用于联接的行需要分配,因此需要考虑筛选后的数据集的大小,而不是表的大小。

  • 在筛选结果集中选择基数高的列;

例如,如果您在日期列上分配了一个销售表,您可能获得非常均匀的数据分配,除非您的大多数销售都是季节性的。但是,如果您通常使用范围受限谓词进行筛选以缩小日期期间的范围,则大多数筛选行将位于有限的一组切片上并且查询工作负载将偏斜。

  • 将一些维度表改为使用 ALL 分配;

如果一个维度表不能与事实数据表或其他重要的联接表并置,您可以通过将整个表分配到所有节点来大大提高查询性能。使用 ALL 分配会使存储空间需求成倍增长,并且会增加加载时间和维护操作,所以在选择 ALL 分配前应权衡所有因素。

优化COPY,提高数据加载速度
当你将要数据加载到Redshift的某个表时,不要让单个输入文件过大,最好是将这些输入文件切成多份,具体数量最好是跟分片数量匹配,这样可以充分利用所有分片,配合分配键能达到最佳效果。

图2,COPY输入的最优方式

让COPY选择自动压缩
作为数据仓库,Redshift通常会需要大量导入数据,这时使用做多的,效率最好的是COPY命令。在使用COPY时建议将COMPUPDATE参数设置为ON,这样数据在加载进库时是自动压缩的,好处是可以节省存储空间,提高查询的速度,不过这会增加数据加载进表的时间,这个可以根据你的业务需求,再具体衡量。

Redshift调优实战
测试结论

  1. 选择合适的排序键,分配键,及自动压缩对表的查询速度,存储效率很大提升。本次测试中,优化后查询速度有高达75%的提升,存储空间节省50%。
  2. 相同节点类型情况下,多节点性能比单节点性能提升明显。本次测试中,采用了4节点与单节点对比,4节点查询速度比单节点提升75%。
  3. 节点数量相同的情况下,dc系列节点的查询速度比ds系列节点的查询速度要快。本次测试中,采用了dc1.large和ds1.xlarge两种节点类型进行对比,dc系列节点的查询速度比ds系列快20% 。
  4. 使用JOIN与不使用JOIN查询速度无明显差别。本次测试中,三个不同的查询及对应的JOIN查询,在查询速度上的差别非常小。这部分的详细测试结果,请参见附录一。
  5. 查询速度达到一定值时,再增加节点对查询优化的效果有限。本次测试中,在相同环境中,将节点数量从8个dc1.large节点增加到12个dc1.large节点,三个查询只有一个查询的速度有一定提升,其他2个查询速度基本没有太大变化。这部分的详细测试结果,请参见附录二。

图3,调优前后性能对比图

备注:性能对比图从三个方面进行了对比,数据加载速度表存储空间查询的速度。本次测试的原始数据放在AWS Oregon S3,Redshift也在Oregon区域。

 

测试场景
表1,本次测试中用到的表及表的大小

图4,本次测试中表之间的关系

测试步骤

注意:本次测试步骤已假设Redshift集群已启动,且用户知道如何通过JDBC方式连接Redshift集群。

Before(不做任何优化):

  1. 创建表(不指定排序键和分配键);
  2. 加载数据(不进行自动压缩);
  3. 查询Redshift中各个表的存储空间;
  4. 执行三种不同查询,均取第2次查询所耗时间;
  5. 相同条件,使用JOIN查询所耗时间;

After(指定排序键和分配键,加载数据时进行了自动压缩):

  1. 删除表;
  2. 创建表(指定排序键和分配键);
  3. 加载数据(根据不同数据类型选择合适的压缩算法);
  4. 查询Redshift中各个表的存储空间;
  5. 执行三种不同查询,均取第2次查询所耗时间;
  6. 相同条件,使用JOIN查询所耗时间;

测试截图
图5,单个节点(ds1.xlarge)的数据加载时间(优化前)

图6,单个节点(ds1.xlarge)的数据加载时间(优化后)

图7,单个节点(ds1.xlarge)的数据存储空间(优化前)

图8,单个节点(ds1.xlarge)的数据存储空间(优化后)

图9,单个节点(ds1.xlarge)的查询时间(优化前)

图10,单个节点(ds1.xlarge)的查询时间(优化后)

图11,4个节点(ds1.xlarge)的数据加载时间(优化前)

图12,4个节点(ds1.xlarge)的数据加载时间(优化后)

图13,4个节点(ds1.xlarge)的数据存储空间(优化前)

图14,4个节点(ds1.xlarge)的数据存储空间(优化后)

图15,4个节点(ds1.xlarge)的查询时间 (优化前)

图16,4个节点(ds1.xlarge)的查询时间 (优化后)

图17,4个节点(dc1.large)的数据加载时间 (优化前)

图18,4个节点(dc1.large)的数据加载时间 (优化后)

图19,4个节点(dc1.large)的数据存储空间 (优化前)

图20,4个节点(dc1.large)的数据存储空间 (优化后)

图21,4个节点(dc1.large)的查询时间 (优化前)

图22,4个节点(dc1.large)的查询时间 (优化后)

 

本次测试中用到的命令参数
Before (优化前)

CREATE TABLE part

(

p_partkey     INTEGER NOT NULL,

p_name        VARCHAR(22) NOT NULL,

p_mfgr        VARCHAR(6) NOT NULL,

p_category    VARCHAR(7) NOT NULL,

p_brand1      VARCHAR(9) NOT NULL,

p_color       VARCHAR(11) NOT NULL,

p_type        VARCHAR(25) NOT NULL,

p_size        INTEGER NOT NULL,

p_container   VARCHAR(10) NOT NULL

);

CREATE TABLE supplier

(

s_suppkey   INTEGER NOT NULL,

s_name      VARCHAR(25) NOT NULL,

s_address   VARCHAR(25) NOT NULL,

s_city      VARCHAR(10) NOT NULL,

s_nation    VARCHAR(15) NOT NULL,

s_region    VARCHAR(12) NOT NULL,

s_phone     VARCHAR(15) NOT NULL

);

CREATE TABLE customer

(

c_custkey      INTEGER NOT NULL,

c_name         VARCHAR(25) NOT NULL,

c_address      VARCHAR(25) NOT NULL,

c_city         VARCHAR(10) NOT NULL,

c_nation       VARCHAR(15) NOT NULL,

c_region       VARCHAR(12) NOT NULL,

c_phone        VARCHAR(15) NOT NULL,

c_mktsegment   VARCHAR(10) NOT NULL

);

CREATE TABLE dwdate

(

d_datekey            INTEGER NOT NULL,

d_date               VARCHAR(19) NOT NULL,

d_dayofweek          VARCHAR(10) NOT NULL,

d_month              VARCHAR(10) NOT NULL,

d_year               INTEGER NOT NULL,

d_yearmonthnum       INTEGER NOT NULL,

d_yearmonth          VARCHAR(8) NOT NULL,

d_daynuminweek       INTEGER NOT NULL,

d_daynuminmonth      INTEGER NOT NULL,

d_daynuminyear       INTEGER NOT NULL,

d_monthnuminyear     INTEGER NOT NULL,

d_weeknuminyear      INTEGER NOT NULL,

d_sellingseason      VARCHAR(13) NOT NULL,

d_lastdayinweekfl    VARCHAR(1) NOT NULL,

d_lastdayinmonthfl   VARCHAR(1) NOT NULL,

d_holidayfl          VARCHAR(1) NOT NULL,

d_weekdayfl          VARCHAR(1) NOT NULL

);

CREATE TABLE lineorder

(

lo_orderkey          INTEGER NOT NULL,

lo_linenumber        INTEGER NOT NULL,

lo_custkey           INTEGER NOT NULL,

lo_partkey           INTEGER NOT NULL,

lo_suppkey           INTEGER NOT NULL,

lo_orderdate         INTEGER NOT NULL,

lo_orderpriority     VARCHAR(15) NOT NULL,

lo_shippriority      VARCHAR(1) NOT NULL,

lo_quantity          INTEGER NOT NULL,

lo_extendedprice     INTEGER NOT NULL,

lo_ordertotalprice   INTEGER NOT NULL,

lo_discount          INTEGER NOT NULL,

lo_revenue           INTEGER NOT NULL,

lo_supplycost        INTEGER NOT NULL,

lo_tax               INTEGER NOT NULL,

lo_commitdate        INTEGER NOT NULL,

lo_shipmode          VARCHAR(10) NOT NULL

);

copy customer from ‘s3://lyz/redshift/customer’

credentials ‘aws_access_key_id= your-key;aws_secret_access_key=your-secret-key’

gzip compupdate off region ‘us-west-2’;

 

copy dwdate from ‘s3://lyz/redshift/dwdate’

credentials ‘aws_access_key_id= your-key;aws_secret_access_key= your-secret-key ‘

gzip compupdate off region ‘us-west-2’;

 

copy lineorder from ‘s3://lyz/redshift/lineorder’

credentials ‘aws_access_key_id= your-key;aws_secret_access_key= your-secret-key ‘

gzip compupdate off region ‘us-west-2’;

 

copy part from ‘s3://lyz/redshift/part’

credentials ‘aws_access_key_id= your-key;aws_secret_access_key= your-secret-key ‘

gzip compupdate off region ‘us-west-2’;

 

copy supplier from ‘s3://lyz/redshift/supplier’

credentials ‘aws_access_key_id= your-key;aws_secret_access_key= your-secret-key ‘

gzip compupdate off region ‘us-west-2’;

 

select count(*) from LINEORDER;

select count(*) from PART;

select count(*) from  CUSTOMER;

select count(*) from  SUPPLIER;

select count(*) from  DWDATE;

 

select stv_tbl_perm.name as table, count(*) as mb

from stv_blocklist, stv_tbl_perm

where stv_blocklist.tbl = stv_tbl_perm.id

and stv_blocklist.slice = stv_tbl_perm.slice

and stv_tbl_perm.name in (‘lineorder’,’part’,’customer’,’dwdate’,’supplier’)

group by stv_tbl_perm.name

order by 1 asc;

 

— Query 1

— Restrictions on only one dimension.

select sum(lo_extendedprice*lo_discount) as revenue

from lineorder, dwdate

where lo_orderdate = d_datekey

and d_year = 1997

and lo_discount between 1 and 3

and lo_quantity < 24;

 

— Query 2

— Restrictions on two dimensions

 

select sum(lo_revenue), d_year, p_brand1

from lineorder, dwdate, part, supplier

where lo_orderdate = d_datekey

and lo_partkey = p_partkey

and lo_suppkey = s_suppkey

and p_category = ‘MFGR#12’

and s_region = ‘AMERICA’

group by d_year, p_brand1

order by d_year, p_brand1;

 

— Query 3

— Drill down in time to just one month

 

select c_city, s_city, d_year, sum(lo_revenue) as revenue

from customer, lineorder, supplier, dwdate

where lo_custkey = c_custkey

and lo_suppkey = s_suppkey

and lo_orderdate = d_datekey

and (c_city=’UNITED KI1′ or

c_city=’UNITED KI5′)

and (s_city=’UNITED KI1′ or

s_city=’UNITED KI5′)

and d_yearmonth = ‘Dec1997’

group by c_city, s_city, d_year

order by d_year asc, revenue desc;

After(优化后):

drop table part cascade;

drop table supplier cascade;

drop table customer cascade;

drop table dwdate cascade;

drop table lineorder cascade;

 

CREATE TABLE part (

p_partkey     integer             not null sortkey distkey,

p_name        varchar(22)      not null,

p_mfgr           varchar(6)      not null,

p_category    varchar(7)      not null,

p_brand1      varchar(9)      not null,

p_color          varchar(11)      not null,

p_type           varchar(25)      not null,

p_size            integer             not null,

p_container   varchar(10)     not null

);

 

CREATE TABLE supplier (

s_suppkey                 integer        not null sortkey,

s_name        varchar(25)    not null,

s_address     varchar(25)    not null,

s_city             varchar(10)    not null,

s_nation         varchar(15)    not null,

s_region        varchar(12)    not null,

s_phone       varchar(15)    not null)

diststyle all;

 

CREATE TABLE customer (

c_custkey     integer        not null sortkey,

c_name        varchar(25)    not null,

c_address     varchar(25)    not null,

c_city             varchar(10)    not null,

c_nation         varchar(15)    not null,

c_region        varchar(12)    not null,

c_phone       varchar(15)    not null,

c_mktsegment      varchar(10)    not null)

diststyle all;

 

CREATE TABLE dwdate (

d_datekey            integer       not null sortkey,

d_date               varchar(19)   not null,

d_dayofweek       varchar(10)   not null,

d_month            varchar(10)   not null,

d_year               integer       not null,

d_yearmonthnum       integer            not null,

d_yearmonth          varchar(8)           not null,

d_daynuminweek       integer       not null,

d_daynuminmonth      integer       not null,

d_daynuminyear       integer       not null,

d_monthnuminyear     integer       not null,

d_weeknuminyear      integer       not null,

d_sellingseason      varchar(13)    not null,

d_lastdayinweekfl    varchar(1)    not null,

d_lastdayinmonthfl   varchar(1)    not null,

d_holidayfl          varchar(1)    not null,

d_weekdayfl          varchar(1)    not null)

diststyle all;

 

CREATE TABLE lineorder (

lo_orderkey                   integer         not null,

lo_linenumber           integer             not null,

lo_custkey                 integer             not null,

lo_partkey                  integer             not null distkey,

lo_suppkey                integer             not null,

lo_orderdate              integer             not null sortkey,

lo_orderpriority          varchar(15)     not null,

lo_shippriority            varchar(1)      not null,

lo_quantity                 integer             not null,

lo_extendedprice       integer             not null,

lo_ordertotalprice      integer             not null,

lo_discount                integer             not null,

lo_revenue                integer             not null,

lo_supplycost            integer             not null,

lo_tax                         integer             not null,

lo_commitdate         integer         not null,

lo_shipmode              varchar(10)     not null

);

 

copy customer from ‘s3://lyz/redshift/customer’

credentials ‘aws_access_key_id=your-key;aws_secret_access_key=your-secret-key’

gzip region ‘us-west-2’;

 

copy dwdate from ‘s3://lyz/redshift/dwdate’

credentials ‘aws_access_key_id= your-key;aws_secret_access_key= your-secret-key ‘

gzip region ‘us-west-2’;

 

copy lineorder from ‘s3://lyz/redshift/lineorder’

credentials ‘aws_access_key_id= your-key;aws_secret_access_key= your-secret-key ‘

gzip region ‘us-west-2’;

 

copy part from ‘s3://lyz/redshift/part’

credentials ‘aws_access_key_id= your-key;aws_secret_access_key= your-secret-key ‘

gzip region ‘us-west-2’;

 

copy supplier from ‘s3://lyz/redshift/supplier’

credentials ‘aws_access_key_id= your-key;aws_secret_access_key= your-secret-key ‘

gzip region ‘us-west-2’;

 

select stv_tbl_perm.name as table, count(*) as mb

from stv_blocklist, stv_tbl_perm

where stv_blocklist.tbl = stv_tbl_perm.id

and stv_blocklist.slice = stv_tbl_perm.slice

and stv_tbl_perm.name in (‘lineorder’,’part’,’customer’,’dwdate’,’supplier’)

group by stv_tbl_perm.name

order by 1 asc;

 

— Query 1

— Restrictions on only one dimension.

select sum(lo_extendedprice*lo_discount) as revenue

from lineorder, dwdate

where lo_orderdate = d_datekey

and d_year = 1997

and lo_discount between 1 and 3

and lo_quantity < 24;

 

— Query 2

— Restrictions on two dimensions

 

select sum(lo_revenue), d_year, p_brand1

from lineorder, dwdate, part, supplier

where lo_orderdate = d_datekey

and lo_partkey = p_partkey

and lo_suppkey = s_suppkey

and p_category = ‘MFGR#12’

and s_region = ‘AMERICA’

group by d_year, p_brand1

order by d_year, p_brand1;

 

— Query 3

— Drill down in time to just one month

 

select c_city, s_city, d_year, sum(lo_revenue) as revenue

from customer, lineorder, supplier, dwdate

where lo_custkey = c_custkey

and lo_suppkey = s_suppkey

and lo_orderdate = d_datekey

and (c_city=’UNITED KI1′ or

c_city=’UNITED KI5′)

and (s_city=’UNITED KI1′ or

s_city=’UNITED KI5′)

and d_yearmonth = ‘Dec1997’

group by c_city, s_city, d_year

order by d_year asc, revenue desc;

附录一
图23,查询1所耗时间,8节点(dc1.large)

图24,查询1使用JOIN所耗时间,8节点(dc1.large)

图25,查询2所耗时间,8节点(dc1.large)

图26,查询2使用JOIN所耗时间,8节点(dc1.large)

图27,查询3所耗时间,8节点(dc1.large)

图28,查询3使用JOIN所耗时间,8节点(dc1.large)

附录二
图29,查询1所耗时间,12节点(dc1.large)

图30,查询2所耗时间,12节点(dc1.large)

图31,查询3所耗时间,12节点(dc1.large)

作者介绍:

郑进佳

亚马逊AWS解决方案架构师,在加入AWS之前,在多家跨国公司有着超过7年的架构设计和项目管理的经验,对AWS云端高可用架构有着深刻的理解,以及对企业级应用如何迁移到云端的架构设计有实战方面的经验。