Amazon Redshift Spectrum 使您能够对存储在 Amazon S3 中的数据运行 Amazon Redshift SQL 查询。利用 Redshift Spectrum，您可以将 Amazon Redshift 的强大分析能力扩展到存储于 Amazon Redshift 本地的数据之外。Redshift Spectrum 提供的多种功能能够扩大您可能实施的战略。例如，它能够扩展 Amazon Redshift 可访问的数据大小，并能让您将计算与存储分离，从而提升混合工作负载用例的处理速度。Redshift Spectrum 还能够提高数据的互操作性，因为您可以从 Amazon Redshift 之外的多个计算平台访问同一 S3 对象。这些平台包括 Amazon Athena、Amazon EMR with Apache Spark、Amazon EMR with Apache Hive、Presto 及可访问 S3 的任何其他计算平台。因此，您无需通过繁琐、耗时的提取、转换、加载 (ETL) 流程，即可查询您的 Amazon S3 数据湖中的海量数据。您还可以连接外部 S3 表与集群本地磁盘上的表。Redshift Spectrum 对数以千计的节点进行复杂的查询优化和扩展处理，从而交付快速的性能。在本博文中，我们收集了 Redshift Spectrum 的 12 大重要最佳实践，并将这些实践分成不同的功能组。这些指南基于我们与 Amazon Redshift 客户的许多交互以及大量直接项目工作。在您开始使用之前，需要遵循以下步骤进行设置。有关开始使用 Redshift Spectrum 的先决条件及步骤的更多信息，请参阅 Amazon Redshift 文档中的Amazon Redshift Spectrum 入门。

设置测试环境

要进行测试以验证本博文中概述的最佳实践，您可以使用任何数据集。Redshift Spectrum 支持多种常见数据格式：Text、Parquet、ORC、JSON、Avro 等等。您可以使用数据的原始格式进行查询，也可以根据数据访问模式、存储要求等等将数据转换为更高效的格式。例如，如果您经常访问列的子集，Parquet 和 ORC 等列格式能够仅读取所需列，从而大大降低 I/O。如何转换文件格式不在本博文的探讨范围之内，有关如何转换文件格式的更多信息，请参阅以下资源：

aws-blog-spark-parquet-conversion AWS 示例 GitHub 存储库
Amazon Athena 文档中的转换为列格式（使用 Amazon EMR with Apache Hive 进行转换）
AWS 大数据博客中的 Visualize AWS CloudTrail Logs Using AWS Glue and Amazon QuickSight

创建外部schema

您可以遵循以下方法创建名为 s3_external_schema 的外部schema：

SQL

create external schema s3_external_schema 
from data catalog 
database 'spectrumdb' 
iam_role 'arn:aws:iam::<AWS_ACCOUNT_ID>:role/aod-redshift-role'
create external database if not exists;

Amazon Redshift 集群和 Amazon S3 中的数据文件必须位于同一 AWS 区域。您可以在 Amazon Redshift、AWS Glue、Athena Data Catalog 或您自己的 Apache Hive 元存储中创建外部数据库。您的 Amazon Redshift 集群需要授权才能访问您的外部数据目录以及 Amazon S3 中的数据文件。您需要引用附加到您集群的 AWS Identity and Access Management (IAM) 角色（例如 aod-redshift-role）来提供授权。有关更多信息，请参阅 Amazon Redshift 文档中的为 Amazon Redshift 创建 IAM 角色。

定义外部表

您可以使用 Parquet 文件定义分区的外部表，并使用如下逗号分隔值 (CSV) 文件定义其他非分区的外部表：

SQL

CREATE  external table s3_external_schema.LINEITEM_PART_PARQ ( 
 L_ORDERKEY BIGINT,
 L_PARTKEY BIGINT,
 L_SUPPKEY BIGINT,
 L_LINENUMBER INT,
 L_QUANTITY DECIMAL(12,2),
 L_EXTENDEDPRICE DECIMAL(12,2),
 L_DISCOUNT DECIMAL(12,2),
 L_TAX DECIMAL(12,2),
 L_RETURNFLAG VARCHAR(128),
 L_LINESTATUS VARCHAR(128),
 L_COMMITDATE DATE,
 L_RECEIPTDATE DATE,
 L_SHIPINSTRUCT VARCHAR(128),
 L_SHIPMODE VARCHAR(128),
 L_COMMENT VARCHAR(128))
partitioned by (L_SHIPDATE DATE)
stored as PARQUET
location 's3://<your-bucket>/<xyz>/lineitem_partition/';

CREATE  external table s3_external_schema.LINEITEM_CSV ( 
 L_ORDERKEY BIGINT,
 L_PARTKEY INT,
 L_SUPPKEY INT,
 L_LINENUMBER INT,
 L_QUANTITY DECIMAL(12,2),
 L_EXTENDEDPRICE DECIMAL(12,2),
 L_DISCOUNT DECIMAL(12,2),
 L_TAX DECIMAL(12,2),
 L_RETURNFLAG VARCHAR(128),
 L_LINESTATUS VARCHAR(128),
 L_SHIPDATE DATE ,
 L_COMMITDATE DATE,
 L_RECEIPTDATE DATE,
 L_SHIPINSTRUCT VARCHAR(128),
 L_SHIPMODE VARCHAR(128),
 L_COMMENT VARCHAR(128))
row format delimited
fields terminated by '|'
stored as textfile
location 's3://<your-bucket>/<xyz>/lineitem_csv/';

查询数据

总的来说，Amazon Redshift 通过 Redshift Spectrum 访问存储在 Amazon S3 中的外部表。您可以使用用于其他 Amazon Redshift 表的相同的 SELECT 语法查询外部表。目前，所有外部表均为只读格式。

您必须在您的 SELECT 语句中引用外部表（方法是在表名称前面用schema名称做前缀），无需创建表并将其加载到 Amazon Redshift 中。

如希望使用 Redshift Spectrum 执行测试，可从以下两个查询着手。

查询 1

SQL

SELECT  l_returnflag,
        l_linestatus,
        sum(l_quantity) as sum_qty,
        sum(l_extendedprice) as sum_base_price,
        sum(l_extendedprice*(1-l_discount)) as sum_disc_price,
        sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge,
        avg(l_quantity) as avg_qty,
        avg(l_extendedprice) as avg_price
FROM s3_external_schema.LINEITEM_PART_PARQ
WHERE l_shipdate BETWEEN '1998-12-01' AND '1998-12-31'
GROUP BY l_returnflag, l_linestatus
ORDER BY l_returnflag, l_linestatus;

该查询仅访问一个外部表，可用于突出显示 Redshift Spectrum 层提供的额外处理能力。

查询 2

SQL

SELECT   l_orderkey,
         Sum(l_extendedprice * (1 - l_discount)) AS revenue,
         o_orderdate,
         o_shippriority 
FROM     customer, orders, s3_external_schema.lineitem_part_parq 
WHERE    c_mktsegment = 'BUILDING' 
         AND      c_custkey = o_custkey 
         AND      l_orderkey = o_orderkey 
         AND      o_orderdate < date '1995-03-15' 
         AND      l_shipdate >  date '1995-03-15' 
GROUP BY l_orderkey, o_orderdate, o_shippriority 
ORDER BY revenue DESC, o_orderdate 
LIMIT 20;

该查询将三个表连接在一起：customer 和 orders 表是本地 Amazon Redshift 表，而 LINEITEM_PART_PARQ 表是外部表。

本文转载自AWS技术博客。

原文链接：https://amazonaws-china.com/cn/blogs/china/12-best-practices-for-amazon-redshift-spectrum/

创作场景

Amazon Redshift Spectrum 十二大最佳实践（一）