Amazon Redshift 横串検索のベストプラクティス

-- ETL usage - broad access
CREATE EXTERNAL SCHEMA IF NOT EXISTS apg_etl
FROM POSTGRES DATABASE 'tpch' SCHEMA 'public'
URI 'aurora-postgres-ro.cr7d8lhiupkf.us-west-2.rds.amazonaws.com' PORT 8192
IAM_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 access
CREATE EXTERNAL SCHEMA IF NOT EXISTS apg_adhoc
FROM POSTGRES DATABASE 'tpch' SCHEMA 'public'
URI 'aurora-postgres-ro.cr7d8lhiupkf.us-west-2.rds.amazonaws.com' PORT 8192
IAM_ROLE 'arn:aws:iam::123456789012:role/apg-federation-role'
SECRET_ARN 'arn:aws:secretsmanager:us-west-2:123456789012:secret:apg-redshift-adhoc-secret-187Asd'
;

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

<< 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)

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_val
FROM cte
WHERE is_promo IS TRUE --<< PostgreSQL filter predicate pt2
GROUP BY 1;

<< 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

-- Create the materialized view
CREATE MATERIALIZED VIEW mv_store_quantities_by_quarter AS
SELECT ss_store_sk
     , d_quarter_name
     , COUNT(ss_quantity) AS count_quantity
     , AVG(ss_quantity) AS avg_quantity
FROM public.store_sales
JOIN apg_tpcds.date_dim --<< federated table
    ON d_date_sk = ss_sold_date_sk
GROUP BY ss_store_sk
ORDER BY ss_store_sk
;
--Query the materialized view
SELECT * 
FROM mv_store_quanties_by_quarter
WHERE d_quarter_name = '1998Q1'
;
--Refresh the materialized view
REFRESH MATERIALIZED VIEW mv_store_quanties_by_quarter
;

-- Create the materialized view
CREATE MATERIALIZED VIEW mv_apg_part AS
SELECT * FROM apg_tpch_100g.part
;
--Query the materialized view
SELECT SUM(l_extendedprice * (1 - l_discount)) AS discounted_price
FROM public.lineitem, mv_apg_part
WHERE l_partkey = p_partkey
  AND l_shipdate BETWEEN '1997-03-01' AND '1997-04-01'
;

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')
;

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

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 

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

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 ストアドプロシージャを呼び出してテーブルを同期します。

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 インスタンスの 1 つ以上のリードレプリカを設定できます。この記事の執筆時点では、横串検索では統合データベースへの書き込みが許可されていないため、外部スキーマのターゲットとして読み取り専用のエンドポイントを使用する必要があります。これにより、Amazon Redshift が発行するサブ横串検索によるマスターデータベースインスタンスへの影響が最小限に抑えられます。これにより、多くの場合、多数の小規模な書き込みトランザクションがすばやく実行されます。

リードレプリカの詳細については、「DB クラスターへの Aurora レプリカの追加」と「Amazon RDS での PostgreSQL リードレプリカの使用」を参照してください。

次のサンプルコードでは、読み取り専用のエンドポイントを使用して外部スキーマを作成します。エンドポイントの URI 設定で -ro の名前を確認できます。

--In Amazon Redshift: 
CREATE EXTERNAL SCHEMA IF NOT EXISTS apg_etl
FROM POSTGRES DATABASE 'tpch' SCHEMA 'public'
URI 'aurora-postgres-ro.cr7d8lhiupkf.us-west-2.rds.amazonaws.com' PORT 8192
IAM_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 ユーザーを使用する

個別の外部スキーマに関する最初のベストプラクティスで述べたように、横串検索のユースケースごとに個別の PostgreSQL ユーザーを作成することをご検討ください。複数のユーザーがいると、特定のユースケースごとに必要なアクセス許可のみを付与できます。ユーザーごとに、アクセス認証情報を含む異なる SECRET_ARN が必要です。これは、Amazon Redshift 外部スキーマが使用します。次のコードを参照してください。

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

ユーザータイムアウトを適用してクエリの実行時間を制限する

PostgreSQL ユーザーに statement_timeout を設定することをご検討ください。ユーザークエリは、誤って何百万もの行を外部リレーションから取得しようとし、長時間実行し続け、Amazon Redshift と PostgreSQL の両方でオープンリソースを保持することになる可能性があります。これを防ぐには、予想される使用法に従って、ユーザーごとに異なるタイムアウト値を指定します。次のサンプルコードでは、ETL ユーザーとアドホックレポートユーザーにタイムアウトを設定します。

-- Set ETL user timeout to 1 hour
ALTER USER redshift_etl SET statement_timeout TO 3600000;
-- Set Ad-Hoc user timeout to 15 minutes
ALTER USER redshift_adhoc SET statement_timeout TO 900000;

9.PostgreSQL テーブルに正しくインデックスが作成されていることを確認する

Amazon Redshift の統合クエリが効率的に実行されるように、PostgreSQL インデックスを追加または変更することをご検討ください。Amazon Redshift は、リモートデータベースに対して通常の SQL クエリを使用して、PostgreSQL からデータを取得します。インデックスを使用する場合、特にクエリがテーブルの小さな部分を返す場合には、クエリはしばしばより高速です。

lineitem テーブルに対する Amazon Redshift 横串検索の次のサンプルコードをご検討ください。

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

Amazon Redshift はこれを次のサブ横串検索に書き換えて、PostgreSQL で実行します。

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 から次のプランを取得します。

<< 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))

次のインデックスを追加すると、このクエリに必要なデータを正確にカバーできます。

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

新しいインデックスが用意できると、次のプランが表示されます。

<< 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))

改訂されたプランでは、最大コストは元の 16223550 に対して 839080 になります。これは、19 分の 1 のコストです。コストの削減は、インデックスを使用するとクエリが高速になることを示唆していますが、これを確認するにはテストが必要です。

インデックスは慎重に検討する必要があります。PostgreSQL にインデックスを追加することの詳細なトレードオフ、利用可能な特定の PostgreSQL インデックスタイプ、およびインデックスの使用方法については、この記事では扱いません。

10.制限的な結合をリモートビューに置き換える

多くの分析クエリは結合を使用して、クエリが返す行を制限できます。たとえば、calender_quarter='2019Q4' などの述語を date_dim テーブルに適用し、大きなファクトテーブルに結合することができます。date_dim のフィルターは、ファクトテーブルから返される行を 1 桁減らします。ただし、この記事の執筆時点では、Amazon Redshift はそのような結合制限を統合関係にプッシュできません。

2 つの統合テーブル間の結合を使用した次のサンプルクエリをご検討ください。

SELECT ss_store_sk
,COUNT(ss_quantity) AS count_quantity
,AVG(ss_quantity) AS avg_quantity
FROM apg_tpcds.store_sales
JOIN apg_tpcds.date_dim
  ON d_date_sk = ss_sold_date_sk
WHERE d_quarter_name = '1998Q1'
GROUP BY ss_store_sk
ORDER BY ss_store_sk
LIMIT 100;

Amazon Redshift でこのクエリを EXPLAIN すると、次のプランが表示されます。

<< 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 からすべての行を取得してから、結合を使用して行をフィルタリングします。store_sales は非常に大きなテーブルであるため、このクエリを定期的に実行する場合は特に、この操作に時間がかかりすぎる可能性があります。

解決策として、この結合をカプセル化する次のビューを PostgreSQL で作成できます。

CREATE VIEW vw_store_sales_quarter
AS 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;

次のように、このビューを使用するように Amazon Redshift クエリを書き換えます。

SELECT ss_store_sk
      ,COUNT(ss_quantity) AS count_quantity
      ,AVG(ss_quantity) AS avg_quantity
FROM apg_tpcds_10g.vw_store_sales_date
WHERE ss_quarter_name = '1998Q1'
GROUP BY ss_store_sk
ORDER BY ss_store_sk
LIMIT 100;

Amazon Redshift でこの書き換えられたクエリを EXPLAIN すると、次のプランが表示されます。

<< 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 は、フィルターをビューにプッシュします。結合の制限は PostgreSQL に適用され、Amazon Redshift に返される行がはるかに少なくなります。Remote PG Seq Scan が rows=1000 を表示していることに気付くかもしれません。これは、PostgreSQL がテーブル統計を提供できない場合にクエリオプティマイザーが使用するデフォルト値です。

まとめ

この記事では、Amazon Redshift 横串検索のパフォーマンスを最大化するのに役立つ 10 のベストプラクティスをご紹介しました。ユースケースはそれぞれ異なるため、これらの推奨事項を特定の状況にどのように適用できるかを慎重に評価してください。

AWS は引き続き Amazon Redshift 横串検索を強化および改善し、お客様のフィードバックを歓迎します。質問や提案がある場合は、コメント欄にフィードバックをお寄せください。Amazon Redshift クラスターの最適化についてさらにサポートが必要な場合は、AWS アカウントチームにお問い合わせください。

この記事を執筆するにあたり、AWS の同僚である Sriram Krishnamurthy、Entong Shen、Niranjan Kamat、Vuk Ercegovac、および Ippokratis Pandis から支援とサポートを受けたことに感謝申し上げます。

著者について

Joe Harris は、AWS のシニア Redshift データベースエンジニアで、Redshift のパフォーマンスに焦点を当てています。 彼は 20 年間にわたり、さまざまなプラットフォームでデータを分析し、データウェアハウスを構築してきました。AWS に入社する前は、2013 年の開始日から Redshift のお客様であり、Redshift フォーラムの最大の貢献者でした。