dblinkを利用して、Amazon RedshiftとRDS PostgreSQLのデータをジョインする
本エントリーは、AWS Big Data Blogで公開された「JOIN Amazon Redshift AND Amazon RDS PostgreSQL WITH dblink」を翻訳したものです。
Redshiftはデータウェアハウス的な用途に特化したRDBですので、並列で多数アクセスされるOLTP的な操作は苦手です。そこでPostgreSQLと組み合わせて使うことでその弱点をカバーしようという内容になっています。Redshiftを活用する上で非常に有益な内容になっていると思いますので、ぜひ読んでみてください。
翻訳：下佐粉 昭（@simosako）
著者Tony Gibbs はAWSのソリューションアーキテクト
SQLベースのデータベースをAWS上で選択しようとする際、いくつかの選択肢が考えられます。どれを選択するかを考えるのは難しいものです。例えば、Amazon RDS PostgreSQL や Amazon Redshiftではなく、Amazon Auroraを選択すべきなのはどういう時でしょうか？これに回答するには、まずワークロードの性質を把握し、その上で別の要素、例えばデータ量やクエリのアクセスパターンを把握する必要があります。
設計や機能が異なるAWSサービスがあるということは、それぞれのサービスは異なるワークロードに対して強みや優位性を持っているということです。つまり、ジョブに適切なツールを選択するという事は結果としてトレードオフを生みます。しかし、場合によってはそのトレードオフが許容できない場合もあるでしょう。
このポストでは、Amazon Redshift と Amazon RDS PostgreSQL を組み合わせて使うことで、列指向（カラムナ）ストアと、行指向（行ベース）ストアの間にあるトレードオフをどのように回避するかを説明します。
Amazon Redshift
Amazon Redshiftは、ハイパフォーマンス、ペタバイトスケールのデータウェアハウスサービスであり、オンライン分析処理（online analytical processing - OLAP）に優れています。RDS PostgreSQLやAmazon Auroraのようなデータベースは一般的にはテラバイト級のデータを扱い、オンライントランザクション処理（online transaction processing - OLTP）に強みがあります。
Amazon Redshiftは列指向アーキテクチャを採用しており、これはデータがOLTP型データベースのような行ごとの格納ではなく、列の単位でディスク上は管理されているということです。列指向アーキテクチャは、表にある列のうち一部をクエリするようなケースでディスクI/Oを削減できるというアドバンテージがあります。また、列単位でデータを格納することで高い圧縮率を実現可能であり、I/Oの削減、より多くのデータ格納と、クエリ速度の向上を実現しています。
RDS PostgreSQLは行指向のアーキテクチャであり、少ない数の行に対してセレクト、インサート、アップデートを実行するような、一般的にはOLTPと呼ばれるワークロードでアドバンテージがあります。
Amazon RedshiftはMPP（massively parallel processing - 超並列処理）、シェアードナッシングのアーキテクチャです。つまりAmazon Redshiftは、たった１つのクエリを実行する際にも多くのサーバ（ノードと呼ばれます）をまたがってコンピュートリソースを使用するということです。これは大量のデータに対して分析するクエリに素晴らしいパフォーマンスを提供します。対照的に、ほとんどのOLTP型データベースは各クエリの処理に1つのサーバ上の部分的なリソースしか使用しません。このアーキテクチャの差は、多くのOLTP型データベースはより多くの並列クエリを処理できる事に繋がります。なぜならAmazon Redshiftと比較して各クエリごとに消費するリソースをできるだけ小さくし、効率的に実行できるように設計されているからです。
ハイパフォーマンスなAmazon Redshiftを機能豊かなRDS PostgreSQLと組み合わせて利用することは魅力的な提案になります。なぜなら、これら2つは相互に補完しあえるからです。どのようにこれら2つのシステムを連携させれば良いのでしょうか？RDS PostgreSQLデータベースはMPP型データベースではありませんが、複数のインスタンスを他のインスタンスにリンクさせる機能を持っています。
興味深いポイントは、Amazon Redshiftは元々はPostgreSQLからフォーク（分岐）しており、そのためPostgreSQLドライバーやAPI（libpq）はAmazon Redshiftに対して利用可能だということです。PostgreSQLの機能、およびAmazon Redshiftが互換性を持っていることは、これら2つのシステムを接続して利用することを可能にします。PostgreSQLでクエリーを受け、Amazon Redshiftで処理をし、その結果をPostgreSQLに戻すことが可能になるのです。
Amazon RedshiftとRDS PostgreSQLを組み合わせて使うことで以下のメリットが得られます。
- 高い並列アクセスが求められるダッシュボード用途にマテリアライズド・ビュー経由でデータのキャッシュを作成する
- 高い並列アクセスされるパーティション単位での表の結合をブロックレンジインデックス（BRIN）で実現する
- PL/pgSQLのユーザ定義関数（UDF）からダイナミックSQLでAmazon Redshiftをクエリする
- Amazon Redshiftの後処理：例えば結果セットをPostgreSQLでJSONフォーマットに変換する
上記ダイアグラムはエンドユーザと２つのデータベースの間のデータ・フローを示したものです。もし必要なら別途Amazon Redshiftに直接接続することも可能です。そういったケースでは pgbouncer-rrをAmazon EC2上に設置し複数DBへの接続管理をシンプルにしても良いでしょう。以下のダイアグラムがそのソリューションを説明したものです:
詳細を知りたい場合はBob Strahanのブログポストである「Query Routing and Rewrite: Introducing pgbouncer-rr for Amazon Redshift and PostgreSQL」を参照してください。
RDS PostgreSQLはリモートへのクエリ実行のための2つのエクステンションを利用できます。1つ目のエクステンションは、PostgreSQLの外部データラッパー(foreign-data wrapper)である、postgres_fdwです。postgres_fdwモジュールは外部テーブル（external table）の作成を可能にします。外部テーブルはローカルにあるネイティブのテーブルと同じようにクエリできます。しかし、現時点ではそのクエリは全てがリモート側で実行されるわけではありません。なぜなら postgres_fdwはアグリゲーション関数やLIMIT句等をリモートにプッシュダウン（処理をリモートのサーバに任せること）出来ないからです。外部テーブルに対してアグリゲーションクエリを実行した場合、アグリゲーションを実行するために必要な全データが一旦PostgreSQLに全て転送されます。これは大量の行を操作する場合に、利用が現実的では無いほど遅くなってしまいます。
もう一つがdblinkと呼ばれる機能を実現するdblinkエクステンションです。dblink機能はクエリ全体をPostgreSQLからAmazon Redshiftに送信するとを可能にします。これはAmazon Redshiftの能力を活用したクエリの実行が可能です - 大量のデータをRedshiftの機能で効率的にクエリし、結果セットだけをPostgreSQLの後続処理に返します。
導入とセットアップ
以下の手順でこのソリューションをセットアップします:
- Amazon Redshiftクラスターを起動
- RDS PostgreSQL (9.5以降)をステップ1と同じAZ（アベイラビリティゾーン）に起動
- Amazon Redshiftのセキュリティグループを設定しRDS PostgreSQLのエンドポイントからの接続を許可します
- （オプション）Amazon Redshiftにサンプルデータをロードし、この記事で紹介しているクエリを実行できるようにします
- RDS PostgreSQLインスタンスに接続し、以下のSQLコードを実行します。この時<プレースホルダ>の部分はご利用のインスタンスに合わせて変更してください
|
1
2
3
4
5
6
7
8
|
CREATE EXTENSION postgres_fdw;CREATE EXTENSION dblink;CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '<amazon_redshift _ip>', port '<port>', dbname '<database_name>', sslmode 'require');CREATE USER MAPPING FOR <rds_postgresql_username> SERVER foreign_server OPTIONS (user '<amazon_redshift_username>', password '<password>'); |
上記を詳細に知りたい場合はPostgreSQLドキュメントのdblinkを参照してください。（日本語ドキュメントはこちら）
ベーシックなクエリの実行
dblink関数は、以下のクエリのように送信したいSQLステートメントを文字列で指定し、リザルトセットの形も指定する必要があります:
|
1
2
3
4
5
6
7
8
9
|
SELECT *FROM dblink('foreign_server',$REDSHIFT$ SELECT sellerid, sum(pricepaid) sales FROM sales WHERE saletime >= '2008-01-01' AND saletime < '2008-02-01' GROUP BY sellerid ORDER BY sales DESC$REDSHIFT$) AS t1 (sellerid int, sales decimal); |
上記例では:
- dblink関数が 前のステップで作成した('foreign_server')の接続を受け取り、
- $記号で囲まれた文字列 ($REDSHIFT$)の間にあるクエリを送信します。($...$)と($...$)で囲んでクオートするのはクエリを読み書きしやすくするためです
- ($...$)の中にREDSHIFTと書いたのは、このクエリがAmazon Redshiftに送信される事を分かりやすくするためです
- クエリの結果は、指定した名前やデータタイプ (AS t1(sellerid int, sales decimal))に合わせてレコードセットとして返されます。これはこの後続でのジョイン等の処理を可能にします
このクエリによるここまでの結果セットは以下の通りです:
Redshiftへ投入したクエリから結果が変えると、PostgreSQLは後続処理を実行します。例えば以下のようにJSONに変換する等です:
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SELECT array_to_json(array_agg(t1)) FROM ( SELECT * FROM dblink('foreign_server',$REDSHIFT$ SELECT sellerid, sum(pricepaid) sales FROM sales WHERE saletime >= '2008-01-01' AND saletime < '2008-02-01' GROUP BY sellerid ORDER BY sales DESC $REDSHIFT$) AS t1 (sellerid int, sales decimal)) t1; |
ビューに対してクエリする
SQLをより楽に書けるように、ここまでのクエリをビュー(VIEW)として定義することができます。以下がそのベーシックなクエリのビューです。
|
1
2
3
4
5
6
7
8
9
10
|
CREATE OR REPLACE VIEW v_sales ASSELECT *FROM dblink ('foreign_server',$REDSHIFT$ SELECT sellerid, sum(pricepaid) sales FROM sales WHERE saletime >= '2008-01-01' AND saletime < '2008-02-01' GROUP BY sellerid ORDER BY sales DESC$REDSHIFT$) AS t1 (sellerid int, sales decimal); |
このビューに対してクエリすると前と同じ結果が返ってきます:
|
1
|
SELECT * from v_sales; |
UDFを使ったクエリ
その他の方法としては、UDFを使ったクエリがあります。ビューを使う場合との違いとしては、UDFはその使用時にパラメータを渡すことができます。この前の例では、日付の範囲はクエリの中にハードコートされていました。UDFを使うことでクエリ時に任意の範囲を指定することができます。以下のPL/pgSQLコードはPostgreSQL内にUDFを作成します:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
CREATE OR REPLACE FUNCTION get_sales(_startdate timestamp, _enddate timestamp)RETURNS TABLE (sellerid int, sales decimal) AS$BODY$DECLAREremote_sql TEXT;BEGINremote_sql = FORMAT( ' SELECT sellerid, sum(pricepaid) sales FROM sales WHERE saletime >= %L AND saletime < %L GROUP BY sellerid ORDER BY sales DESC', _startdate, _enddate);RETURN QUERY SELECT *FROM dblink('foreign_server', remote_sql) AS t1 (sellerid int, sales decimal);END;$BODY$LANGUAGE plpgsql VOLATILE; |
この関数はPostgreSQLから以下のように実行することができます。
|
1
|
SELECT * FROM get_sales ('2008-01-01', '2008-02-01'); |
このクエリを実行すると想定どおりの結果が返ってきます。
マテリアライズド・ビューを利用してデータをキャッシュする
データが頻繁にアクセスされるようなケースでは、マテリアライズド・ビューを利用するのが良い方法になりえます。マテリアライズド・ビューはクエリ結果をキャッシュするため、実行を複数回実行することをスキップすることができます。ダッシュボードで頻繁にアクセスされる小規模なデータをキャッシュしておく場合等に有効です。
以下のマテリアライズド・ビューはスポーツを好むユーザの数をカウントし、ステート（state）ごとにグルーピングします。マテリアライズド・ビュー作成のDDLは以下の通りです。
|
1
2
3
4
5
6
7
|
CREATE MATERIALIZED VIEW v_users_likes_by_state ASSELECT *FROM dblink('foreign_server',$REDSHIFT$ SELECT state, sum(likesports::int) sports_like_count FROM users GROUP BY state$REDSHIFT$) AS t1 (state text, sports_like_count int); |
上記のマテリアライズド・ビューが作成されると、Amazon Redshiftにクエリが発行されます。マテリアライズド・ビューに対してクエリが発行されてもAmazon Redshiftへはクエリーは発行されず、代わりにPostgreSQLの中で結果セットが作成され、返されます。マテリアライズド・ビューへのクエリは通常のビューへのクエリと同じように利用できます。以下が例です:
SELECT * FROM v_users_likes_by_state;
マテリアライズド・ビューが保持するキャシュが有効でなくなる場合もあります。以下のSQLステートメントは、再度元のクエリーを実行することでマテリアライズド・ビューのキャッシュをリフレッシュします。
|
1
|
REFRESH MATERIALIZED VIEW v_users_likes_by_state; |
より詳細な情報はPostgreSQLドキュメントのMaterialized Views を参照してください。（日本語ドキュメントはこちら）
定期的にマテリアライズド・ビューをリフレッシュしたい場合は、AWS Lambdaを利用することができます。以下のNode.jsのコードはマテリアライズド・ビューをリフレッシュします:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
var pg = require("pg");exports.handler = function(event, context) { var client = new pg.Client(conn); client.connect(function(err) { if (err) { context.fail("Failed" + err); } client.query('REFRESH MATERIALIZED VIEW v_users_likes_by_state', function (err, result) { if (err) { context.fail("Failed to run query" + err); } client.end(); context.succeed("Successfully Refreshed."); }); });}; |
上記Lambda関数の中ではpgモジュールが必要です。以下のコマンドでインストール可能です。
|
1
|
npm install pg |
Lambda関数作成についてより詳細な情報が必要な場合はこちらをご覧ください：デプロイパッケージの作成（Node.js）
Amazon Redshift から RDS PostgreSQLへデータをコピーする
大量のデータがあるようなケースでは、マテリアライズド・ビューを利用するより、dblink関数を利用してデータをPostgreSQL上の表にコピーする方が良い場合があります。これはデータの最新部分だけをコピーすれば良いようなケースで有用です。マテリアライズド・ビューはリフレッシュ時にデータを頭から全体をコピーするために、速度面で不利になります。
表を作成するSQLは以下の通りです:
|
1
2
3
4
5
|
CREATE TABLE sales_summary ( saletime timestamp, sellerid int, sales decimal); |
PostgreSQLはこのデータを読む際にインデックスを利用して最適化をし、BRINは時系列で並べられたデータにとって適切なインデックスになります。インデックスを作成するためのSQLは以下の通りです。
|
1
2
3
|
CREATE INDEX idx_sales_summary_brin ON sales_summary USING BRIN (saletime); |
以下のクエリはdblink関数を利用してデータを表にインサートする方法を示しています。以下のように差分でデータをインサートすることで、マテリアライズド・ビュー全体をリフレッシュするよりも高速に更新を行います。
|
1
2
3
4
5
6
7
8
9
10
|
INSERT INTO sales_summarySELECT *FROM dblink('foreign_server',$REDSHIFT$ SELECT date_trunc('hours', saletime) AS ts, sellerid, sum(pricepaid) sales FROM sales WHERE saletime >= '2008-01-01' AND saletime < '2008-02-01' GROUP BY ts, sellerid ORDER BY sales $REDSHIFT$) AS t1 (saletime timestamp, sellerid int, sales decimal); |
まとめ
dblink拡張でAmazon Redshitに接続し、PostgreSQLの機能を活用する方法を説明しました。頻繁にアクセスされる小規模なデータセットをキャッシュし、大きいデータはマテリアライズド・ビューを作成してリフレッシュするか、データを表にコピーするかを選択することができます。キャッシュされていないデータへのクエリについては、さらに2つの選択肢があります：１つは通常のビューを作成すること、もう一つはUDFを作成し引数を受け取れるようにすることす。dblink拡張とAmazon Redshiftの組み合わせは他にも多数の活用方法があります。例えばPosrtgreSQL上のPostGISやLDAP（こちらはRDSには存在しないためEC2上に入れたPosrtgreSQLで）を活用する等です。しかしこれらについてはこのポストで扱う範囲を超えています。
ご質問や指摘がある場合はコメントを残してください。
-------------------------------------
参考
Real-time in-memory OLTP and Analytics with Apache Ignite on AWS（AWS上のApache Igniteによるインメモリ・リアルタイムOTLP分析）
コメント