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://thinkwithwp.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，权限里面要包含AmazonS3ReadOnlyAccess 和 AmazonAthenaFullAccess 策略。

创建好角色之后，将角色管理到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.thinkwithwp.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.thinkwithwp.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相关查询的相关指标，我们可以通过查询一下两个视图进行了解：

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咨询和实施工作经验。

亚马逊AWS官方博客