Amazon Redshift Spectrum 十二大最佳实践

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：

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) 文件定义其他非分区的外部表：

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

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

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 表是外部表。

并发的最佳实践

以下推荐做法能够帮助您利用 Redshift Spectrum 优化您的并发工作负载性能。

1.利用 Redshift Spectrum 改善并发工作负载

Redshift Spectrum 是一个复杂的执行引擎，可作为无服务器计算服务调用。当 SQL 查询从存储在 S3 中的外部表请求数据时，本地 Amazon Redshift 集群将其调用到 Redshift Spectrum 中。通过将 Redshift Spectrum 用于外部表，以从 S3 扫描、筛选、聚合并返回行到 Amazon Redshift 集群，使得并行大倍数成为可能。这些操作都是在 Amazon Redshift 之外完成的，这降低了 Amazon Redshift 集群上的计算负载并提高了并发处理速度。此外，Redshift Spectrum 还能够进行智能扩展。Redshift Spectrum 可根据您的查询需求使用数以千计的实例充分发挥大规模并行处理 (MPP) 的优势。

对于某些并行扫描实例、聚合密集型工作负载或两者而言，Redshift Spectrum 可能比本地 Amazon Redshift 的效率更高。

MPP 系统最占据资源的方面是数据加载过程，这是因为它不仅与主动分析查询竞争计算资源，而且还通过多版本并发控制 (MVCC) 锁定表格。与此相反，您可以通过将新文件写入 Amazon S3 将其添加到现有外部表，而不会对 Amazon Redshift 的资源产生影响。之后您可以更新元数据从而以新分区的形式包含文件，然后通过 Redshift Spectrum 访问这些文件。从而将这一数据加载过程从 Amazon Redshift 集群中消除。这对并发具有即时、直接的积极影响。

2.使用多个按需 Amazon Redshift 集群扩展并发

鉴于 Redshift Spectrum 是对存储在 Amazon S3 中的数据进行操作，您可以拥有多个 Amazon Redshift 集群以访问这些 S3 表格并提升并发工作负载性能。Amazon Redshift 的常见问题是如何应对季节性突增的高并发查询工作负载。在 Redshift Spectrum 出现之前，要处理增加的并发，您通常需要通过还原快照来建立多个只读 Amazon Redshift 集群。这种方法的问题是，对于具有数百 TB 数据的大型 Amazon Redshift 数据仓库而言，还原需要很长的时间，这会导致数据延迟的问题。

利用 Redshift Spectrum，您可以将最大的表迁移至 Amazon S3，而每个 Amazon Redshift 集群仅需要在本地磁盘上保留一小部分数据。由于数据量的减少，创建或还原多个只读 Amazon Redshift 集群来应对这些季节性突增查询工作负载的速度大幅提升。请参阅以下示例图，其中多个只读 Amazon Redshift 集群访问共享的 Redshift Spectrum 层。

为了降低成本，您应当在这些“按需”Amazon Redshift 集群完成工作后尽快终止它们。

Amazon Redshift 客户在部署多个 Amazon Redshift 集群以扩展并发时一直利用 pgbouncer-rr 简化和控制客户端查询路由。有关更多详情，可参阅 AWS 大数据博文 Query Routing and Rewrite: Introducing pgbouncer-rr for Amazon Redshift and PostgreSQL。

存储的最佳实践

出于存储优化考虑，每一步都要考虑降低 I/O 工作负载，从而倾向于使用基于列的文件格式，通过压缩在各个存储块中存储更多记录。Redshift Spectrum 支持的文件格式包括 CSV、TSV、Parquet、ORC、JSON、Amazon ION、Avro、RegExSerDe、Grok、RCFile 和 Sequence。

进一步优化可利用压缩。目前，Redshift Spectrum 支持 Gzip、Snappy、BZ2 和 Brotli（仅限于 Parquet）。

对于 Parquet、ORC 和text或使用 Bz2 压缩编解码器的文件，Redshift Spectrum 可能会将大文件的处理拆分为多个请求，这能够提升性能。对文件大小没有限制，但我们建议您避免太多 KB 大小的文件。

如果文件格式和压缩编解码器无法拆分（例如 Avro 或 Gzip），我们建议您不要使用太大的文件（即大小超过 512MB 的文件）。我们之所以这样建议，是因为使用太大的文件会降低并行度。为所有分区使用统一的文件大小有助于减少执行偏斜。

3.考虑列格式以提高性能、降低成本

Apache Parquet 和 ORC 是列式存储格式，适用于 Apache Hadoop 生态系统中的任何项目，无论您选择何种数据处理框架、数据模型或编程语言。如需了解详情，请参阅 Apache Parquet 和 Apache ORC 文档。

Redshift Spectrum 依据每个查询从 S3 中扫描的数据量向您收费。由于 Parquet 和 ORC 以列格式存储数据，Redshift Spectrum 仅读取查询所需的列，避免扫描剩余的列，从而降低查询成本。

多项测试显示，相比基于行的文件格式，列格式通常执行速度更快，并且通常更具有成本效益。

您可以对比处理文本文件和列格式文件的查询性能和成本。为此，您可以使用 SVL_S3QUERY_SUMMARY 深入了解一些有趣的 S3 指标。

Select * from SVL_S3QUERY_SUMMARY where query=<Query-ID>;

请特别注意一些有趣的指标：s3_scanned_rows、s3query_returned_rows、s3_scanned_bytes 和 s3query_returned_bytes。在与 CSV 文件进行对比时，请留意从 Redshift Spectrum 返回本地 Amazon Redshift 以进行最终处理的数据量的大幅减少。

当您以 Parquet 和 ORC 格式存储数据时，也可以通过对数据进行排序来进行优化。如果您的数据存储在经常筛选的列中，Redshift Spectrum 扫描工具会考虑最低和最高指数，并跳过读取整个行组。该功能适用于 Parquet，对 ORC 的支持即将推出。

4.频繁筛选的列上的分区文件

如果数据按一个或多个筛选过的列进行分区，Redshift Spectrum 可以利用分区修剪并跳过对不需要的分区和文件的扫描。常见做法是根据时间对数据进行分区。当您在决定最佳分区列时，请考虑以下几点：

用作常规筛选条件的列是很好的候选项。
如果您经常使用多个谓词，我们建议您进行多级分区。举个例子，您可以根据 SHIPDATE 和 STORE 进行分区。
过多的精细分区会增加检索分区信息的时间，但有助于进行分区修剪并减少从 S3 扫描的数据量。尽管如此，您可能仍希望避免使用创建数千万分区的分区模式。例如，使用二级精细度可能会适得其反。
实际的性能取决于查询模式、分区中的文件数、符合条件的分区数等等。
衡量并避免分区列发生数据偏斜。
Redshift Spectrum 现在在 Parquet 中支持 DATE 类型，可利用它并使用 DATE 类型进行快速筛选或分区修剪。

相比非分区的外部表，扫描分区的外部表的速度会快很多，而且成本更低。为了演示分区修剪的巨大优势，您应当考虑创建两个外部表：一个表不分区，另一个按日进行分区。

如何得知分区修剪是否有效？可以使用以下 SQL 分析分区修剪的有效性。如果查询仅涉及几个分区，可以验证一切是否按预期执行。

SELECT   query,
         segment,
         Max(total_partitions)     AS total_partitions,
         Max(qualified_partitions) AS qualified_partitions 
FROM     svl_s3partition 
WHERE    query=<query-id> 
GROUP BY 1, 2;

可以看到，（分区列中的）S3 谓词越有限，分区修剪的效果就越显著，Redshift Spectrum 查询的性能也就越好。Amazon RedShift 为外部表进行了静态和动态分区修剪。查询 1 进行了静态分区修剪，即谓词被放在分区列 l_shipdate 那里。我们建议您探索使用小维度表（如 Nation 或 Region）连接的其他查询示例，并从维度表中筛选列，这样有助于您了解动态分区修剪的效果。

集群配置的最佳实践

以下是针对配置您的 Amazon Redshift 集群以在 Redshift Spectrum 中发挥最佳性能的建议。

5.通过正确的 Amazon Redshift 集群配置优化性能

如果您的查询受到扫描和聚合的限制，Redshift Spectrum 提供的请求并行性会带来更好的整体查询性能。

要了解特定 Redshift Spectrum 查询的请求并行性，请执行以下操作：

SELECT query, files, splits, avg_request_parallelism FROM SVL_S3QUERY_SUMMARY 
WHERE query= xxxx;

query | files | splits | avg_request_parallelism
------+-------+--------+-------------------------
64577 |   112 |    328 |    5

以下是影响 Amazon S3 请求并行性的一些因素：

所扫描的所有文件的拆分数（一个不可拆分文件计为一个拆分）
集群中的切片总数
正在进行的并发查询数

简单的数学运算如下所示。当总文件拆分小于或等于 avg_request_parallelism 值的（例如 10）倍数 total_slices 时，调用具有更多节点的集群可能不会提升性能。

该指南用于核实 Redshift Spectrum 表有多少个文件。之后，您可以看到一个特定趋势：在特定集群大小（分片数）之后，即使集群节点数继续增加，性能也趋于稳定。给定节点类型的最佳 Amazon Redshift 集群大小就是您无法再实现性能增长时的大小。

有关何时使用 Redshift Spectrum 的最佳实践

利用 Redshift Spectrum，您可以对存储在 S3 数据湖中的数据运行 Amazon Redshift 查询，而无需将数据加载到 Amazon Redshift 中。这不仅可以缩减获得见解的时间，还可以减少数据的陈旧性。在某些情况下，Redshift Spectrum 是更好的执行选项。

6.利用 Redshift Spectrum 实现更迅速的扫描和聚合密集型查询

由于计算和存储的分离，Redshift Spectrum 可以立即扩展计算，以处理海量的数据。这对不需要在节点之间移动数据的繁重扫描和聚合工作而言是有好处的。

以下是一些好的使用案例：

大量但不经常访问的数据
繁重的扫描和聚合密集型查询
可以使用分区修剪和谓词下推的选择性查询，因此输出量很小

某些查询（例如前面的查询 1）没有连接。其性能通常由实际 I/O 成本（即扫描速度）决定。对于这些查询而言，Redshift Spectrum 实际上可能比本地 Amazon Redshift 更快。另一方面，对于涉及多表连接的查询（例如查询 2）而言，使用本地存储的高度优化的本地 Amazon Redshift 表更胜一筹。

7.简化 ETL 管道

在 Redshift Spectrum 出现之前，向 Amazon Redshift 摄取数据需要很多步骤。常规数据管道包含提取、加载和转换 (ETL) 的过程。您需要清理脏数据、转换数据并将其加载到暂存区，然后将数据加载到最终表。您可能需要为每个步骤使用不同的服务，并使之相互协调。

利用 Redshift Spectrum，您无需将数据实际加载到暂存区，可以简化复杂的数据工程处理过程。

为了实现这一目的，需创建指向存储在 S3 内的原始数据的外部schema或表，或者使用 AWS Glue 或 Athena 数据目录。在 SELECT 查询中写入转换逻辑，然后将结果提取到 Amazon Redshift 中。您只需在一个单独的查询中即可完成所有操作，无需额外的服务。

CREATE TABLE <Final Amazon Redshift table> AS SELECT … FROM s3_external_schema.xxx WHERE …;
SELECT … INTO <Final Amazon Redshift table> FROM s3_external_schema.xxx WHERE …;

查询性能的最佳实践

您可以遵循以下建议改善查询性能。

8.通过谓词下推改善 Amazon S3 查询性能

在 Redshift Spectrum 层（S3 扫描、投影、筛选和聚合）完成的处理独立于任何单个 Amazon Redshift 集群。一般而言，得益于支持 Redshift Spectrum 的强大基础架构，可下推到 Redshift Spectrum 的任何操作的性能均会得到很大提升。利用谓词下推还能够避免消耗 Amazon Redshift 集群中的资源。

您可以将许多 SQL 操作下推到 Redshift Spectrum 层。我们建议您尽可能利用这一优势。以下是您可以下推的操作的示例：

COUNT、SUM、AVG、MIN、MAX、GROUP BY 子句等常规聚合函数
regex_replace、to_upper、date_trunc 等字符串函数
LIKE、IS NULL 和CASE WHEN 等相等谓词和模式匹配条件

查看您的查询执行计划，验证谓词下推是否有效

以以下两个查询为例：

EXPLAIN SELECT * FROM s3_external_schema.sales WHERE memo LIKE 'return%';
XN S3 Query Scan sales  (cost=0.00..225000000.00 rows=10000000000 width=410)
  ->  S3 Seq Scan s3_external_schema.sales location:"s3://<your-bucket>/sales" format:TEXT  (cost=0.00..125000000.00 rows=10000000000 width=410)
        Filter: ((memo)::text ~~ 'return%'::text)

EXPLAIN SELECT * FROM s3_external_schema.sales WHERE memo ILIKE 'Return%';
XN S3 Query Scan sales  (cost=0.00..225000000.00 rows=512000001 width=410)
  Filter: ((memo)::text ~~* 'Return%'::text)
  ->  S3 Seq Scan s3_external_schema.sales location:"s3://<your-bucket>/sales" format:TEXT  (cost=0.00..100000000.00 rows=10000000000 width=410)

在第一个查询的执行计划中，S3 扫描筛选被下推到 Redshift Spectrum 层。因此，只有匹配的结果会返回给 Amazon Redshift 进行最终处理。

另一方面，看看第二个查询的执行计划。在该查询中，由于 ILIKE，没有谓词下推到 Spectrum。因此，该查询不得不从 S3 返回海量数据到 Amazon Redshift 进行筛选。

我们一直在改善谓词下推，并计划在一段时间后下推越来越多的 SQL 操作。例如，在当前的 Amazon Redshift 版本中，ILIKE 现已下推至 Redshift Spectrum。

9.在您的查询中将复杂的 DISTINCT 操作替换为 GROUP BY

在执行计划步骤的过程中，Amazon Redshift 可以自动重写简单的 DISTINCT（单列）查询，并将其下推至 Redshift Spectrum。

多列 DISTINCT 和 ORDER BY 等特定 SQL 操作必须在 Amazon Redshift 中执行，因为这些操作不能下推至 Redshift Spectrum。如果可能的话，您应当重写这些查询以最大限度减少或避免使用它们。

例如，查看以下两个具有相同功能的 SQL 语句。

具有多个列的查询使用了 DISTINCT：

SELECT DISTINCT l_returnflag,l_linestatus 
FROM 	s3_external_schema.LINEITEM_PART_PARQ 
WHERE 	EXTRACT(YEAR from l_shipdate::DATE) BETWEEN '<Start-Year>' AND  '<End-Year>' 
ORDER BY l_returnflag, l_linestatus;

具有相同功能的查询使用了 GROUP BY：

SELECT l_returnflag,l_linestatus 
FROM 	s3_external_schema.LINEITEM_PART_PARQ 
WHERE EXTRACT(YEAR from l_shipdate::DATE) BETWEEN '<Start-Year>' AND  '<End-Year>' 
GROUP BY l_returnflag, l_linestatus 
ORDER BY l_returnflag, l_linestatus;

在第一个查询中，您不能将多列 DISTINCT 操作下推至 Spectrum，因此大量行被返回 Amazon Redshift 进行排序和重复资料删除。在第二个查询中，S3 HashAggregate 被下推至 Redshift Spectrum 层，而大多数繁重工作和聚合已完成。您可以在 SVL_S3QUERY_SUMMARY 系统视图中查询这两个 SQL 语句（查看 s3query_returned_rows 列）。您应当能够发现，从 Redshift Spectrum 返回 Amazon Redshift 的行数具有很大的差别。

从中我们可以得知，在 SQL 语句中，您应当尽可能将 DISTINCT 替换为 GROUP BY。

表置放和统计的最佳实践

您可以遵循以下建议改善表置放和统计。

10.确定存储您的表的最佳位置

以下简单的指南可帮助您确定存储表以发挥最佳性能的最佳位置。

您可以在同一查询中访问存储在 Amazon Redshift 和 S3 中的数据。基于访问模式将数据置于正确的存储中，您可以以更低的成本获得更高的性能：

如果数据比较陈旧并且不常访问，可以在 S3 中加载数据并使用 Redshift Spectrum。
如果数据经常被访问且频繁使用，可以将其加载到 Amazon Redshift 中。除了text、JSON 和和 AVRO 之外，Amazon Redshift 现在还支持 Parquet 和 ORC 数据。
将 S3 数据复杂的报告整合到较小的本地 Amazon Redshift 表中。将 Redshift Spectrum 和 Amazon Redshift 的强大功能结合起来。利用 Redshift Spectrum 的强大计算能力执行繁重工作并将结果具体化。利用 Amazon Redshift 作为结果缓存，以更快速地响应多个用户。
如果您的一小部分数据经常被访问而剩余数据不经常访问，可以利用后期绑定视图将外部表与 Amazon Redshift 本地表相集成。
```
CREATE VIEW total_sales as
SELECT … FROM sales_last_month
UNION ALL
SELECT … FROM s3_external_schema.sales_historical
WITH NO SCHEMA BINDING
```

11.手动为 S3 外部表设置表统计 (numRows)

Amazon Redshift 优化器可使用外部表统计生成更强大的执行计划。没有统计，执行计划将在假设 S3 表格是相对较大表的前提下基于启发式算法生成。我们来看看以下查询的执行计划。

explain select * from s3_external_schema.event_desc ed join s3_external_schema.event e
on e.event_cd = ed.event_cd and e.event_type_cd = ed.event_type;

XN Hash Join DS_BCAST_INNER  (cost=250000000.00..356425000500000000.00 rows=173286210788786592 width=2620)
  Hash Cond: ((("outer".event_cd)::text = ("inner".event_cd)::text) AND (("outer".event_type)::text = ("inner".event_type_cd)::text))
  ->  XN S3 Query Scan e  (cost=0.00..200000000.00 rows=10000000000 width=1556)
        ->  S3 Seq Scan s3_external_schema.event_desc e location:"s3://<your-bucket>/<xyz>/event_desc/" format:PARQUET  (cost=0.00..100000000.00 rows=10000000000 width=1556)
  ->  XN Hash  (cost=200000000.00..200000000.00 rows=10000000000 width=1064)
        ->  XN S3 Query Scan ed  (cost=0.00..200000000.00 rows=10000000000 width=1064)
              ->  S3 Seq Scan s3_external_schema.event ed location:"s3://<your-bucket>/<xyz>/event/" format:PARQUET  (cost=0.00..100000000.00 rows=10000000000 width=1064)

正如您所看到的，连接顺序不是最佳顺序。如何解决这个问题呢？

虽然您不能为外部表执行 ANALYZE，但可以在 CREATE EXTERNAL TABLE 和 ALTER TABLE 命令中利用 TABLE PROPERTIES 子句手动设置表统计 (numRows)。

ALTER TABLE s3_external_schema.event SET TABLE PROPERTIES ('numRows'='799');
ALTER TABLE s3_external_schema.event_desc SET TABLE PROPERTIES ('numRows'=' 122857504');

凭借这一信息，Amazon Redshift 优化器可生成更优的执行计划并更快地完成查询。

XN Hash Join DS_BCAST_INNER  (cost=19.98..12696028004.76 rows=170103196 width=2620)
  Hash Cond: ((("outer".event_cd)::text = ("inner".event_cd)::text) AND (("outer".event_type_cd)::text = ("inner".event_type)::text))
  ->  XN S3 Query Scan e  (cost=0.00..2457150.08 rows=122857504 width=1064)
        ->  S3 Seq Scan s3_external_schema.event e location:"s3://<your-bucket>/<xyz>/event/" format:PARQUET  (cost=0.00..1228575.04 rows=122857504 width=1064)
  ->  XN Hash  (cost=15.98..15.98 rows=799 width=1556)
        ->  XN S3 Query Scan ed  (cost=0.00..15.98 rows=799 width=1556)
              ->  S3 Seq Scan s3_external_schema.event_desc ed location:"s3://<your-bucket>/<xyz>/event_desc/" format:PARQUET  (cost=0.00..7.99 rows=799 width=1556)

查询成本控制的最佳实践

您可以遵循以下建议帮助控制您的查询成本。

12.注意查询成本并使用查询监控规则

对于非选择性连接，需要读取大量数据才能执行连接。这会产生较高的数据转移成本和网络流量，从而导致性能低下，超出必要成本。

您的查询是否包含大量扫描、可选择或大量连接？要监控指标并了解您的查询模式，可以使用以下查询。

SELECT query,
       Round(elapsed :: FLOAT / 1000 / 1000, 3) AS elapsed_sec,
       aborted,
       is_partitioned,
       s3_scanned_rows,
       s3query_returned_rows,
       s3_scanned_bytes,
       s3query_returned_bytes,
       files,
       max_retries,
       Round(max_request_duration :: FLOAT / 1000 / 1000, 3) AS 
       max_request_dur_sec,
       Round(avg_request_duration :: FLOAT / 1000 / 1000, 3) AS 
       avg_request_dur_sec,
       Round(avg_request_parallelism, 2)                     AS 
       avg_request_parallelism 
FROM   svl_s3query_summary 
WHERE  query = xxxx;

为此，执行以下操作：

查看已扫描与已返回数据的比率以及并行度
核实您的查询是否能利用分区修剪（请参阅最佳实践 4）

当您了解具体情况后，可以设置工作负载管理 (WLM) 查询监控规则以停止恶意查询，从而避免预期之外的成本。

如果用户忘记添加筛选，或者数据未进行适当分区，查询可能会偶然扫描大量数据并产生较高的成本。要设置查询性能界限，可使用 WLM 查询监控规则并在查询超出这些界限时采取行动。例如，您可以设置规则，以在 spectrum_scan_size_mb 超过 20TB 或 spectrum_scan_row_count 超过 10 亿时终止查询。

利用这些规则及其他查询监控规则，您可以终止查询、将查询转到下一个匹配的队列，或者仅在触发一个或多个规则时加以记录。有关更多信息，请参阅 Amazon Redshift 文档中的 WLM 查询监控规则。

小结

在本博文中，我们提供了一些提升 Redshift Spectrum 性能的重要最佳实践。由于每个使用案例都是独一无二的，因此您应当判断如何将这些建议运用到您的具体情况中。

在此对我们的 AWS 同事 Bob Strahan、Abhishek Sinha、Maor Kleider、Jenny Chen、Martin Grund、Tony Gibbs 和 Derek Young 所提出的意见、见解及他们给予的帮助表示诚挚的感谢。

如果您有任何问题或建议，请在评论区留下反馈。如果您需要有关优化您的 Amazon Redshift 集群的进一步帮助，请联系您的 AWS 账户团队。

关于作者

Po Hong 博士是 AWS 专业服务部全球大数据与分析实践的大数据顾问。

Peter Dalton 先生是 AWS 专业服务部的首席顾问。

该博文于 2018 年 10 月 31 日更新，作者：

Matt Scaer 先生是首席数据仓库专家级解决方案架构师，具有超过 20 年的数据仓库经验，并已在 AWS 和 Amazon.com 任职超过 11 年。

Juan Yu 女士是 AWS 的数据仓库专家级解决方案架构师。

Ippokratis Pandis 先生是 AWS 负责开发 Amazon Redshift 和 Amazon Redshift Spectrum 的首席软件工程师。

亚马逊AWS官方博客