Amazon Redshift Federated Query 最佳实践-InfoQ

Original URL：https://aws.amazon.com/cn/blogs/big-data/amazon-redshift-federated-query-best-practices-and-performance-considerations/

Amazon Redshift Federated Query 旨在帮助用户使用 Amazon Redshift 提供的分析功能直接查询存储在 Amazon Aurora PostgreSQL 与 Amazon RDS for PostgreSQL 数据库内的数据。关于设置环境以实现联邦查询的更多详细信息，请参阅通过AWS CloudFormation加速Amazon Redshift Rederated Query的应用。

Federated Query 可实现实时数据集成并简化 ETL 处理流程。现在，大家可以直接在 Amazon Redshift 中连接实时数据源，并借此提供实时报告与分析结果。以前，我们需要先将数据从 PostgreSQL 数据库提取至 Amazon Simple Storage Service（Amazon S3），而后使用COPY将其加载至 Amazon Redshift，或者使用 Amazon Redshift Spectrum 对 Amazon S3 进行直接查询。关于 Federated Query 优势的更多详细信息，请参阅使用Amazon Redshift Federated Query建立起更简单的ETL与实时数据查询解决方案。

本文将探讨十种最佳实践，帮助大家在拥有较大的联邦数据集时、利用联邦查询检索大规模数据时、或者有众多 Redshift 用户访问联邦数据集时，充分发挥 Federated Query 的优势。当然，这里提到的方法在 Federated Query 的常规使用场景下并非必需。本文主要面向希望深度挖掘 Federated Query 潜力的高级用户。

以下最佳实践主要分为两大类：第一类为面向 Amazon Redshift 集群的建议；第二类则为面向 Aurora PostgreSQL 与 Amazon RDS for PostgreSQL 环境的建议。

本文提及的示例代码来自CloudDataWarehouseBenchmark GitHub repo（基于 TPC-H 与 TPC-DS）。

适用于 Amazon Redshift 的最佳实践

在使用联邦查询访问 Aurora 或者 Amazon RDS for PostgreSQL 实例时，以下最佳实践适用于我们的 Amazon Redshift 集群。

1. 在各个用例中使用单独的外部 schema

考虑使用单独的远程 PostgreSQL 用户，为每个特定的 Amazon Redshift 用例创建单独的 Amazon Redshift 外部 schema。通过这一实践，大家可以对能够访问外部数据库的用户及组进行额外控制。例如，您可能希望建立起一套面向 ETL 使用方式的外部 schema，并为其设置一个具有广泛访问权限的相关 PostgreSQL 用户；而在另一套独立 schema 中，则配备一个专门负责即席报告与分析的相关 PostgreSQL 用户，仅允许其访问特定几种资源。

以下示例代码将为 ETL 与即席报告创建两种外部 schema。每个 schema 将使用不同的SECRET_ARN 以包含 PostgreSQL 数据库中各单独用户的凭证。

SQL

-- ETL usage - broad accessCREATE EXTERNAL SCHEMA IF NOT EXISTS apg_etlFROM POSTGRES DATABASE 'tpch' SCHEMA 'public'URI 'aurora-postgres-ro.cr7d8lhiupkf.us-west-2.rds.amazonaws.com' PORT 8192IAM_ROLE 'arn:aws:iam::123456789012:role/apg-federation-role'SECRET_ARN 'arn:aws:secretsmanager:us-west-2:123456789012:secret:apg-redshift-etl-secret-187Asd';-- Ad-Hoc usage - limited accessCREATE EXTERNAL SCHEMA IF NOT EXISTS apg_adhocFROM POSTGRES DATABASE 'tpch' SCHEMA 'public'URI 'aurora-postgres-ro.cr7d8lhiupkf.us-west-2.rds.amazonaws.com' PORT 8192IAM_ROLE 'arn:aws:iam::123456789012:role/apg-federation-role'SECRET_ARN 'arn:aws:secretsmanager:us-west-2:123456789012:secret:apg-redshift-adhoc-secret-187Asd';

复制代码

2. 使用查询超时来限制总运行时间

考虑为有权访问您外部 schema 的用户或组设置超时。用户查询可能会在无意间尝试从外部关系当中检索大量行，且持续运行较长时间，这将严重占用 Amazon Redshift 与 PostgreSQL 中的开放资源。

要限制用户查询的总运行时间，您可以为所有用户查询设置一项 [statement_timeout](https://docs.aws.amazon.com/redshift/latest/dg/r_statement_timeout.html)。以下代码示例，为将 ETL 用户设置 2 小时的超时周期：

SQL

-- Set ETL user timeout to 2 hoursALTER USER etl_user SET statement_timeout TO 7200000;

复制代码

如果有众多用户都可以访问您的外部 schema，那么为每位用户单独定义 [statement_timeout](https://docs.aws.amazon.com/redshift/latest/dg/r_statement_timeout.html)显然不太实际。相反，大家可以使用query_execution_time指标在 WLM 配置当中添加查询监控规则。以下截屏所示为一项自动 WLM 配置，它为adhoc组内的用户提供一个临时报告队列，且规则会取消一切运行时间超过 1800 秒（30 分钟）的查询。

3. 保证 Amazon Redshift 查询计划的高效执行

查看整体查询计划与联邦查询的查询指标，以保证 Amazon Redshift 能够高效处理这些查询。关于查询计划的更多详细信息，请参阅评估查询计划。

查看 Amazon Redshift 查询解释计划

我们可以在 SQL 查询中添加EXPLAIN前缀，并在 SQL 客户端内运行的方式检索解释计划。以下示例代码为演示查询所对应的解释输出：

SQL

<< REDSHIFT >> QUERY PLAN------------------------------------------------------------------------------------------------------------- XN Aggregate  (cost=6396670427721.37..6396670427721.37 rows=1 width=32)   ->  XN Hash Join DS_BCAST_INNER  (cost=499986.50..6396670410690.30 rows=6812425 width=32)         Hash Cond: ("outer".l_partkey = ("inner".p_partkey)::bigint)         ->  XN Seq Scan on lineitem  (cost=0.00..2997629.29 rows=199841953 width=40)               Filter: ((l_shipdate < '1994-03-01'::date) AND (l_shipdate >= '1994-02-01'::date))         ->  XN Hash  (cost=449987.85..449987.85 rows=19999460 width=4)               ->  XN PG Query Scan part  (cost=0.00..449987.85 rows=19999460 width=4)                     ->  Remote PG Seq Scan apg_tpch_100g.part  (cost=0.00..249993.25 rows=19999460 width=4)                           Filter: ((p_type)::text ~~ 'PROMO%'::text)

复制代码

操作符XN PG Query Scan表示，对于这部分查询，Amazon Redshift 将针对联邦 PostgreSQL 数据库运行查询。在本文中，我们将其称为“联邦子查询”。当查询中涉及多个联邦数据源时，Amazon Redshift 将对各个源运行一项联邦子查询。Amazon Redshift 会从集群中随机选择节点以运行各项联邦子查询。

在XN PG Query Scan行的下方，我们可以看到Remote PG Seq Scan，其后是带有Filter:元素的行。这两行定义了 Amazon Redshift 如何访问外部数据，以及在联邦子查询中使用的谓词。可以看到，该联邦子查询将针对联邦表apg_tpch.part运行。

通过rows=19999460，我们还可以看到 Amazon Redshift 预计该查询最多可以从 PostgreSQL 处返回 2000 万行。这一估计结论，来自向 PostgreSQL 询问后得到的表统计信息。

Joins

由于每项联邦子查询都通过集群中的单一节点运行，因此 Amazon Redshift 必须选择一项 join 分发策略，用以将从联邦子查询返回的各行发送至集群中的其余位置，从而完成查询中的 joins 操作。这里使用的广播或分发策略，将在解释计划中进行定义。以DS_BCAST开头的操作符将把副本的完整副本广播至所有节点；而以DS_DIST开头的操作符则将数据中的一部分发送至集群中的各个节点。

一般来讲，对较小的结果进行广播、对较大的结果进行分发往往执行效率更高。当计划程序能够比较准确地估计联邦子查询将要返回的行数时，即可选择正确的 join 分发策略。然而，如果计算程序的估算结果不准确，则可能选择广播过大的结果，从而降低查询速度。

Join Order

Joins 应使用较小的结果作为内部关系。当我们的查询 joins 两个表（或者两项联邦子查询）时，Amazon Redshift 必须选择如何更好地执行 join。查询计划程序可能无法按照查询中声明的顺序执行 join；相反，它只能使用待 join 关系的相关信息为各种可能的执行计划建立成本估算。估算完成后，它会选择其中预期成本最低的计划（包括 join order）。

当我们使用 hash join（最常见的 join）时，Amazon Redshift 会使用内部表（或结果）构造一个哈希表，并将其与外部表进行逐行比较。这里最好是使用最小结果作为内部值，以确保哈希表可以容纳在内存当中。如果计划程序的估算无法反映查询中各个步骤所对应的实际结果大小，则所选的 join order 有可能不是真正的最佳选择。

提高查询效率

下面来看提高执行效率的高级建议。关于更多详细信息，请参阅分析查询计划。

检查查询各部分的对应计划。如果您的查询中包含多个joins或者涉及子查询，则可以针对各项join或子查询审查解释计划，借此判断能否对查询做出进一步简化。例如，如果您使用多个joins，不妨使用单一join以简化查询本体，并观察Amazon Redshift如何自主规划这项join操作。
检查外部joins的顺序并使用内部join。计划程序有时候无法对外部join进行重新排序。如果能够将外部join转换为内部join，也许计划程序能够找出效率更高的执行方式。
在join当中引用最大Amazon Redshift表的分发键。当join引用分发键时，Amazon Redshift可以并行完成各个节点上的join，而无需在集群中移动Redshift表中的各行数据。
将联邦子查询的结果插入表中。当数据来自本地临时或永久表时，Amazon Redshift提供的统计信息最为准确。只在极少数情况下，最有效的作法才是先将联邦数据存储在临时表中，而后将其join至您的Amazon Redshift数据。

4. 确保将谓词下推至远程查询

Amazon Redshift 的查询优化器能够高效将谓词条件下推至 PostgreSQL 中运行的联邦子查询当中。因此，请检查重要或者需要长期运行的联邦查询所对应的查询计划，判断 Amazon Redshift 是否将所有适用的谓词都应用于各项子查询。

考虑以下示例查询，其中谓词处于 CASE 语句之内，而联邦关系则处于 CTE 子查询内：

SQL

WITH cte AS (SELECT p_type, l_extendedprice, l_discount, l_quantity     FROM public.lineitem    JOIN apg_tpch.part --<< PostgreSQL table         ON l_partkey = p_partkey    WHERE l_shipdate >= DATE '1994-02-01'      AND l_shipdate < (DATE '1994-02-01' + INTERVAL '1 month'))SELECT CASE WHEN p_type LIKE 'PROMO%' --<< PostgreSQL filter predicate pt1            THEN TRUE ELSE FALSE END AS is_promo     , AVG( ( l_extendedprice * l_discount) / l_quantity ) AS avg_promo_disc_valFROM cteWHERE is_promo IS TRUE --<< PostgreSQL filter predicate pt2GROUP BY 1;

复制代码

通过将过滤器下推至远程关系中，Amazon Redshift 仍可有效优化联邦子查询，具体请参阅以下计划：

SQL

<< REDSHIFT >> QUERY PLAN------------------------------------------------------------------------------------------------------------------- XN HashAggregate  (cost=17596843176454.83..17596843176456.83 rows=200 width=87)   ->  XN Hash Join DS_BCAST_INNER  (cost=500000.00..17596843142391.79 rows=6812609 width=87)         Hash Cond: ("outer".l_partkey = ("inner".p_partkey)::bigint)         ->  XN Seq Scan on lineitem  (cost=0.00..2997629.29 rows=199841953 width=40)               Filter: ((l_shipdate < '1994-03-01'::date) AND (l_shipdate >= '1994-02-01'::date))         ->  XN Hash  (cost=450000.00..450000.00 rows=20000000 width=59)-- Federated subquery >>               ->  XN PG Query Scan part  (cost=0.00..450000.00 rows=20000000 width=59)                     ->  Remote PG Seq Scan apg_tpch.part  (cost=0.00..250000.00 rows=20000000 width=59)                           Filter: (CASE WHEN ((p_type)::text ~~ 'PROMO%'::text) THEN true ELSE false END IS TRUE)-- << Federated subquery

复制代码

如果 Redshift 无法根据需求完成谓词下推，或者查询返回的数据量仍然过大，请考虑以下两条最佳实践中关于视图实体化与表同步的建议。要轻松重写查询以有效完成过滤器下推，请参考最后一条最佳实践中关于持久存储高查询频率数据的建议。

5. 使用物化视图以缓存高访问频率数据

Amazon Redshift 现在支持创建物化视图，用于从外部 schema 中引用联邦表。

缓存经常运行的查询

大家可以考虑使用物化视图在 Amazon Redshift 集群中缓存那些运行频率较高的查询。如果很多用户会定期运行相同的联邦查询，且每次执行都需要对远程内容进行重新检索，那么通过物化视图，这些联邦查询可以从您的 Amazon Redshift 集群中检索结果，而无需从远程数据库中获取相同的数据。接下来，大家可以指定时间段对物化视图进行刷新，具体间隔取决于远程数据的变更率与重要性。

以下示例代码，演示了查询使用联邦源表对物化视图进行创建、查询与刷新的具体方法：

SQL

-- Create the materialized viewCREATE MATERIALIZED VIEW mv_store_quantities_by_quarter ASSELECT ss_store_sk     , d_quarter_name     , COUNT(ss_quantity) AS count_quantity     , AVG(ss_quantity) AS avg_quantityFROM public.store_salesJOIN apg_tpcds.date_dim --<< federated table    ON d_date_sk = ss_sold_date_skGROUP BY ss_store_skORDER BY ss_store_sk;--Query the materialized viewSELECT * FROM mv_store_quanties_by_quarterWHERE d_quarter_name = '1998Q1';--Refresh the materialized viewREFRESH MATERIALIZED VIEW mv_store_quanties_by_quarter;

复制代码

缓存查询频率较高的表

大家也可以考虑使用物化视图缓存那些查询频率较高的表。当多项不同查询使用同一联邦表时，通常有必要将该联邦表创建为物化视图，以供其他查询执行引用。

以下示例代码，演示了如何在单一联邦源表上创建并查询物化视图：

SQL

-- Create the materialized viewCREATE MATERIALIZED VIEW mv_apg_part ASSELECT * FROM apg_tpch_100g.part;--Query the materialized viewSELECT SUM(l_extendedprice * (1 - l_discount)) AS discounted_priceFROM public.lineitem, mv_apg_partWHERE l_partkey = p_partkey  AND l_shipdate BETWEEN '1997-03-01' AND '1997-04-01';

复制代码

截至本文撰稿时，大家还无法在物化视图中引用其他物化视图。换言之，缓存表内使用的其他视图，必须为常规视图。

在缓存与刷新时间/频率间取得平衡

物化视图最适合匹配那些运行速度比更新周期更快的查询。例如，如果物化视图每小时刷新一次，则其运行时长应该在几分钟以内；如果物化视图每天刷新一次，则运行时长应该在一小时以内。截至本文撰稿时，引用外部表的物化视图还无法实时增量式刷新。当我们运行[REFRESH MATERIALIZED VIEW](https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-refresh-sql-command.html)并重新创建完整结果时，物化视图将完全刷新。

使用物化视图限制远程访问

另外，还应该考虑使用物化视图控制可以直接对远程数据库发出查询的用户数量。我们可以仅向刷新物化视图的用户授权外部 schema 访问权限，而其他 Amazon Redshift 用户则仅具备对物化视图的访问权限。

在对包含敏感信息的远程生产数据库进行查询时，以这种方式限制访问范围属于一项数据安全层面的最佳实践。

6. 将大型远程表与本地副本进行同步

考虑在 Amazon Redshift 永久表中为远程表保留一份副本。如果您的远程表很大，而完全刷新物化视图又相当费时，那么使用同步过程保持本地副本更新往往更为有效。

同步新添加的远程数据

当大型远程表中仅添加了新的行——而未发生任何更新或删除时，则可以通过定期将远程表内的新行插入副本实现对 Amazon Redshift 副本的同步。我们可以使用 GitHub 上的示例存储过程[sp_sync_get_new_rows](https://github.com/awslabs/amazon-redshift-utils/blob/master/src/StoredProcedures/sp_sync_get_new_rows.sql)自动实现这一同步操作。

此示例存储过程要求源表中包含一个自动递增的标识列以作为其主键。示例存储过程会在我们的 Amazon Redshift 表中找到当前最大值，检索联邦表中具有更高 ID 值的所有行，并将其插入到 Amazon Redshift 表当中。

以下示例代码，演示了从联邦源表到 Amazon Redshift 目标表的同步方法：

SQL

CREATE TABLE public.pg_source (       pk_col   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY    , data_col VARCHAR(20));INSERT INTO public.pg_tbl (data_col)VALUES ('aardvark'),('aardvarks'),('aardwolf'),('aardwolves');

复制代码

在您的 Amazon Redshift 集群中创建一个包含两行的目标表：

SQL

CREATE TABLE public.rs_target (      pk_col   BIGINT PRIMARY KEY    , data_col VARCHAR(20));INSERT INTO public.rs_tblVALUES (1,'aardvark'), (2,'aardvarks');

复制代码

调用 Amazon Redshift 存储过程以实现表同步：

SQL

CALL sp_sync_get_new_rows(SYSDATE,'apg_tpch.pg_source','public.rs_target','pk_col','public.sp_logs',0);-- INFO:  SUCCESS - 2 new rows inserted into `target_table`.
SELECT * FROM public.rs_tbl;--  pk_col |  data_col  -- --------+--------------       1 | aardvark  --       2 | aardvarks  --       4 | aardwolves--       3 | aardwolf

复制代码

合并远程数据变更

在对远程表进行行更新或插入之后，大家可以定期将远程表中的变更行与新行合并到副本中来，借此完成 Amazon Redshift 副本同步。这种方法特别适合对表内变更做出明确标记的情况，这样我们可以轻松对新行或变更过的行进行检索。大家可以使用 GitHub 上的示例存储过程[sp_sync_merge_changes](https://github.com/awslabs/amazon-redshift-utils/blob/master/src/StoredProcedures/sp_sync_merge_changes.sql)。

此示例存储过程要求源表中包含一个 date/time 列，该列用于指示上一次各行的修改时间。它使用此列查找需要同步的变更，更新发生变更的行或在 Amazon Redshift 副本中插入新行。该存储过程还要求表中包含已声明的主键，它会使用主键以识别需要在本地数据副本中更新的行。

以下示例代码，演示了如何根据联邦源表对 Amazon Redshift 目标表进行刷新。首先，我们在 Amazon Redshift 集群中创建一个包含两行的示例表：

SQL

CREATE TABLE public.rs_tbl (       pk_col   INTEGER PRIMARY KEY    , data_col VARCHAR(20)    , last_mod TIMESTAMP);INSERT INTO public.rs_tbl VALUES (1,'aardvark', SYSDATE), (2,'aardvarks', SYSDATE);
SELECT * FROM public.rs_tbl;--  pk_col |  data_col  |     last_mod-- --------+------------+-----------------------       1 | aardvark   | 2020-04-01 18:01:02--       2 | aardvarks  | 2020-04-01 18:01:02

复制代码

在我们的 PostgreSQL 数据库中创建一个包含四行的源表：

SQL

CREATE TABLE public.pg_tbl (` `      pk_col   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY    , data_col VARCHAR(20)    , last_mod TIMESTAMP);INSERT INTO public.pg_tbl (data_col, last_mod)VALUES ('aardvark', NOW()),('aardvarks', NOW()),('aardwolf', NOW()),('aardwolves', NOW());

复制代码

调用 Amazon Redshift 存储过程以实现表同步：

SQL

CALL sp_sync_merge_changes(SYSDATE,'apg_tpch.pg_tbl','public.rs_tbl','last_mod','public.sp_logs',0);-- INFO:  SUCCESS - 4 rows synced.
SELECT * FROM public.rs_tbl;--  pk_col |  data_col  |      last_mod-- --------+------------+-----------------------       1 | aardvark   | 2020-04-01 18:09:56--       2 | aardvarks  | 2020-04-01 18:09:56--       4 | aardwolves | 2020-04-01 18:09:56--       3 | aardwolf   | 2020-04-01 18:09:56

复制代码

适用于 Aurora 或 Amazon RDS 的最佳实践

以下几项最佳实践适用于配合 Aurora 或者 Amazon RDS for PostgreSQL 实例使用的 Amazon Redshift 联邦查询。

7. 使用只读副本尽可能减少对 Aurora 或 RDS 的影响

Aurora 以及 Amazon RDS 都允许我们为 PostgreSQL 实例配置一个或多个只读副本。截至本文撰稿时，Federated Query 还不允许对联邦数据库进行写入，因此大家应将这些只读端点指定为外部 schema 的目标。这也能保证 Amazon Redshift 发出的联邦子查询不致过度影响主数据库实例的性能表现，使主数据库实例专注于运行大批量、小型、快速写入事务。

关于只读副本的更多详细信息，请参阅向数据库集群内添加Aurora副本以及在Amazon RDS中使用PostgreSQL只读副本。

以下示例代码，使用一个只读端点创建出外部 schema。大家可以看到端点URI配置中的-ro命名，表示对象为只读副本：

SQL

--In Amazon Redshift: CREATE EXTERNAL SCHEMA IF NOT EXISTS apg_etlFROM POSTGRES DATABASE 'tpch' SCHEMA 'public'URI 'aurora-postgres-ro.cr7d8lhiupkf.us-west-2.rds.amazonaws.com' PORT 8192IAM_ROLE 'arn:aws:iam::123456789012:role/apg-federation-role'SECRET_ARN 'arn:aws:secretsmanager:us-west-2:123456789012:secret:apg-redshift-etl-secret-187Asd';

复制代码

8. 对于各个用例，请使用特定且受限的 PostgreSQL 用户

正如第一项最佳实践中关于单独外部 schema 的表述，请考虑为每项联邦查询用例创建单独的 PostgreSQL 用户。通过多个用户，我们可以保证仅为特定用例授权必要的访问权限。不同用户需要对应不同的 SECRET_ARN（用于容纳访问凭证）以供 Amazon Redshift 外部 schema 使用。具体请参见以下代码：

SQL

-- Create an ETL user who will have broad accessCREATE USER redshift_etl WITH PASSWORD '<<example>>';-- Create an Ad-Hoc user who will have limited accessCREATE USER redshift_adhoc WITH PASSWORD '<<example>>';

复制代码

使用用户超时以限制查询运行时间

考虑在 PostgreSQL 用户上设置一条statement_timeout。用户查询可能会在不自觉中尝试对外部关系进行多达数百万行的高强度检索，且长时间保持运行，这将严重占用 Amazon Redshift 与 PostgreSQL 中的开放资源。为了避免这种情况，请根据不同用户的预期作用为其指定不同的超时值。以下示例代码，将为 ETL 用户与即席报告用户设置超时：

SQL

-- Set ETL user timeout to 1 hourALTER USER redshift_etl SET statement_timeout TO 3600000;-- Set Ad-Hoc user timeout to 15 minutesALTER USER redshift_adhoc SET statement_timeout TO 900000;

复制代码

9. 确保 PostgreSQL 表已恰当使用索引

考虑添加或者修改 PostgreSQL 索引，以确保 Amazon Redshift 联邦查询得以高效运行。Amazon Redshift 会使用常规 SQL 查询从远程 PostgreSQL 数据库中检索数据。在使用索引的情况下，查询速度通常会更快，这一点在仅查询表内一小部分数据时体现得尤其明显。

考虑以下指向lineitem表的 Amazon Redshift 联邦查询代码示例：

SQL

SELECT AVG( ( l_extendedprice * l_discount) / l_quantity ) AS avg_disc_valFROM apg_tpch.lineitemWHERE l_shipdate >= DATE '1994-02-01'  AND l_shipdate < (DATE '1994-02-01' + INTERVAL '1 day');

复制代码

Amazon Redshift 将重写上述代码，以供下列联邦子查询在 PostgreSQL 中运行：

SQL

SELECT pg_catalog."numeric"(l_discount)     , pg_catalog."numeric"(l_extendedprice)     , pg_catalog."numeric"(l_quantity) FROM public.lineitem  WHERE (l_shipdate < '1994-02-02'::date)   AND (l_shipdate >= '1994-02-01'::date);

复制代码

如果没有索引，我们将从 PostgreSQL 处得到以下计划：

SQL

<< POSTGRESQL >> QUERY PLAN [No Index]-------------------------------------------------------------------------------------------- Gather  (cost=1000.00..16223550.40 rows=232856 width=17)   Workers Planned: 2   ->  Parallel Seq Scan on lineitem  (cost=0.00..16199264.80 rows=97023 width=17)         Filter: ((l_shipdate < '1994-02-02'::date) AND (l_shipdate >= '1994-02-01'::date))

复制代码

大家可以添加以下索引，涵盖本次查询所需要的全部数据：

SQL

CREATE INDEX lineitem_ix_covering ON public.lineitem (l_shipdate, l_extendedprice, l_discount, l_quantity);

复制代码

有了新的索引，我们将看到以下计划：

SQL

<< POSTGRESQL >> QUERY PLAN [With Covering Index]------------------------------------------------------------------------------------------------ Bitmap Heap Scan on lineitem  (cost=7007.35..839080.74 rows=232856 width=17)   Recheck Cond: ((l_shipdate < '1994-02-02'::date) AND (l_shipdate >= '1994-02-01'::date))   ->  Bitmap Index Scan on lineitem_ix_covering  (cost=0.00..6949.13 rows=232856 width=0)         Index Cond: ((l_shipdate < '1994-02-02'::date) AND (l_shipdate >= '1994-02-01'::date))

复制代码

在修订后的计划中，最大成本为839080，而原始成本为16223550——削减至后者的十九分之一。成本的降低，代表着使用索引时的查询速度更快，但我们还需要通过测试进一步确认这一点。

大家需要谨慎对待索引。受篇幅所限，本文暂不讨论在 PostgreSQL 中添加索引需要考虑的权衡因素、各类特定 PostgreSQL 索引类型以及索引使用技巧等问题。

10. 使用远程视图替换含过滤条件的 joins

大部分分析查询会使用 joins 以限制查询所返回的行。例如，我们可以将calender_quarter='2019Q4'等谓词应用于date_dim表，而后 join 至大型事实表。其中date_dim上的过滤器能够将事实表返回的行数降低一个数量级。但截至本文撰稿时，Amazon Redshift 还无法将含过滤条件的 join 下推至联邦关系当中。

考虑以下示例查询，其在两个联邦表间具有一项 join：

SQL

SELECT ss_store_sk,COUNT(ss_quantity) AS count_quantity,AVG(ss_quantity) AS avg_quantityFROM apg_tpcds.store_salesJOIN apg_tpcds.date_dim  ON d_date_sk = ss_sold_date_skWHERE d_quarter_name = '1998Q1'GROUP BY ss_store_skORDER BY ss_store_skLIMIT 100;

复制代码

当我们在 Amazon Redshift 中EXPLAIN此查询时，将看到以下计划：

SQL

<< REDSHIFT >> QUERY PLAN [Original]----------------------------------------------------------------------------------------------------------------------------------<< snip >>  ->  XN PG Query Scan store_sales  (cost=0.00..576019.84 rows=28800992 width=12)        ->  Remote PG Seq Scan store_sales  (cost=0.00..288009.92 rows=28800992 width=12)  ->  XN Hash  (cost=1643.60..1643.60 rows=73049 width=4)        ->  XN PG Query Scan date_dim  (cost=0.00..1643.60 rows=73049 width=4)              ->  Remote PG Seq Scan date_dim  (cost=0.00..913.11 rows=73049 width=4)                    Filter: (d_quarter_name = '1998Q1'::bpchar)

复制代码

查询计划显示 date_dim已被过滤，但store_sales并不具备过滤器。这意味着 Amazon Redshift 从store_sales检索所有行，而后才使用 join 对各行进行过滤。因为store_sales是个巨大的表，所以整个过程可能耗费很长时间，这在定期运行此项查询的场景下会带来更严重的问题。

为了解决问题，我们可以在 PostgreSQL 中创建以下视图以封装此 join：

SQL

CREATE VIEW vw_store_sales_quarterAS SELECT ss.*, dd.d_quarter_name ss_quarter_name FROM store_sales   ss JOIN date_dim      dd     ON ss.ss_sold_date_sk = dd.d_date_sk;

复制代码

Rewrite the Amazon Redshift query to use the view as follows:

SQL

SELECT ss_store_sk      ,COUNT(ss_quantity) AS count_quantity      ,AVG(ss_quantity) AS avg_quantityFROM apg_tpcds_10g.vw_store_sales_dateWHERE ss_quarter_name = '1998Q1'GROUP BY ss_store_skORDER BY ss_store_skLIMIT 100;

复制代码

在 Amazon Redshift EXPLAIN这条重新编写的查询之后，我们会看到以下计划：

SQL

<< REDSHIFT >> QUERY PLAN [Remote View]----------------------------------------------------------------------------------------------------------------------------------<< snip >>  ->  XN HashAggregate  (cost=30.00..31.00 rows=200 width=8)        ->  XN PG Query Scan vw_store_sales_date  (cost=0.00..22.50 rows=1000 width=8)              ->  Remote PG Seq Scan vw_store_sales_date  (cost=0.00..12.50 rows=1000 width=8)                    Filter: (ss_quarter_name = '1998Q1'::bpchar)

复制代码

Amazon Redshift 现在将过滤器下推到我们的视图当中。其中的含过滤条件的 join 可正确应用于 PostgreSQL，且返回至 Amazon Redshift 的行数也更少。大家可能会注意到， Remote PG Seq Scan现在显示rows=1000；这是在 PostgreSQL 无法提供表统计信息时，查询优化器所使用的默认值。

总结

本文回顾了能够帮助大家尽可能提升 Amazon Redshift 联邦查询性能的十项最佳实践。当然，每项实践都对应特定用例，请在具体采用之前认真评估您的当前场景是否与之匹配。

AWS 将继续增强并改进 Amazon Redshift Federated Query，也欢迎大家提供反馈意见。如果您有任何疑问或建议，请在评论中与我们交流。如果您需要进一步帮助以优化 Amazon Redshift 集群，请联系您的 AWS 客户服务团队。

这里要特别鸣谢 AWS 同事 Sriram Krishnamurthy、Entong Shen、Niranjan Kamat、Vuk Ercegovac 以及 Ippokratis Pandis 为本文编撰提供的帮助与支持。

作者介绍：

Joe Harris

Joe Harris 是 AWS 的高级 Redshift 数据库工程师，专注于 Redshift 性能问题研究他从事各种平台数据分析和数据仓库构建工作已有二十年之久。在加入 AWS 之前，自 2013 年 Redshift 发布时，他就一直是 Redshift 客户，同时也是 Redshift 论坛中的杰出贡献者。

本文转载自亚马逊 AWS 官方博客。

原文链接：

Amazon Redshift Federated Query 最佳实践

发布

暂无评论

Amazon Redshift Federated Query 最佳实践

适用于 Amazon Redshift 的最佳实践

1. 在各个用例中使用单独的外部 schema

2. 使用查询超时来限制总运行时间

3. 保证 Amazon Redshift 查询计划的高效执行

查看 Amazon Redshift 查询解释计划

Joins

Join Order

提高查询效率

4. 确保将谓词下推至远程查询

5. 使用物化视图以缓存高访问频率数据

缓存经常运行的查询

缓存查询频率较高的表

在缓存与刷新时间/频率间取得平衡

使用物化视图限制远程访问

6. 将大型远程表与本地副本进行同步

同步新添加的远程数据

合并远程数据变更

适用于 Aurora 或 Amazon RDS 的最佳实践

7. 使用只读副本尽可能减少对 Aurora 或 RDS 的影响

8. 对于各个用例，请使用特定且受限的 PostgreSQL 用户

使用用户超时以限制查询运行时间

9. 确保 PostgreSQL 表已恰当使用索引

10. 使用远程视图替换含过滤条件的 joins

总结

评论

更多内容推荐

推荐阅读

电子书

大厂实战PPT下载