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，权限里面要包含 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.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 咨询和实施工作经验。

本文转载自 AWS 技术博客。

原文链接：

https://amazonaws-china.com/cn/blogs/china/redshift-new-function/