ablog

不器用で落着きのない技術者のメモ

Redshift Spectrum で外部スキーマの作成と同時に外部データベースを作成する

AWS

Redshift Spectrum で外部スキーマの作成と同時に外部データベースを作成する。

外部スキーマの作成と同時に外部データベースを作成するには、FROM DATA CATALOGステートメントで CREATE EXTERNAL DATABASE を指定して CREATE EXTERNAL SCHEMA 句を含めます。
次の例では、外部データベース spectrum_schema を使用して spectrum_db という名前の外部スキーマを作成します。

create external schema spectrum_schema from data catalog 
database 'spectrum_db' 
iam_role 'arn:aws:iam::123456789012:role/MySpectrumRole'
create external database if not exists;
Amazon Redshift Spectrum 用の外部スキーマ - Amazon Redshift

Redshift Spectrum でサポートされるデータ型

AWS

次の データ型 がサポートされています。

  • SMALLINT (INT2)
  • INTEGER (INT、INT4)
  • BIGINT (INT8)
  • DECIMAL (NUMERIC)
  • REAL (FLOAT4)
  • DOUBLE PRECISION (FLOAT8)
  • BOOLEAN (BOOL)
  • CHAR (CHARACTER)
  • VARCHAR (CHARACTER VARYING)
  • VARBYTE (CHARACTER VARYING) – Parquet および ORC データファイルで、パーティション化されていないテーブルでのみ使用できます。
  • DATE – テキスト、Parquet、または ORC データファイルでのみ使用できます。またはパーティション列としてのみ使用できます。
  • TIMESTAMP
CREATE EXTERNAL TABLE - Amazon Redshift

Redshift の STL_QUERY.query と SYS_QUERY_HISTORY. query_id の対応表を作成する

AWS

Redshift の STL テーブルのクエリID（query列）と SYS ビューのクエリID（query_id）は別の値のため、クエリIDで結合することはできない。トランザクションID（STLとSYSのトランザクションIDが 1:1 の場合）で結合することができる。

Redshift の STL テーブルと SYS ビュー - ablog

STL_QUERY と SYS_QUERY_HISTORY を CSV（|区切り）に出力したファイルを transaction_id(xid) で結合してして、query と query_id の対応表を作成したメモ。

  • STL_QUERY から query と xid だけを抽出して重複排除する。
perl -F'\|' -lane 'print qq/$F[3]|$F[4]/' STL_QUERY.csv|sort -u > STL_QUERY_qid_xid.csv
  • SYS_QUERY_HISTORY から query_id と transaction_id だけを抽出して重複排除する。
perl -F'\|' -lane 'print qq/$F[1]|$F[3]/' SYS_QUERY_HISTORY.csv|sort -u > SYS_QUERY_HISTORY_qid_xid.csv
  • 作成した2ファイルを結合して対応表を作成する。
  • 列は transaction_id、query_id、query という並び。
join -t'|' -1 2 -2 2 SYS_QUERY_HISTORY_qid_xid.csv STL_QUERY_qid_xid.csv > query_id_query.csv

参考

Redshift に Data API でアクセスするために必要な設定

AWS

Redshift に Data API でアクセスする場合、Internet Gateway や Public Access 許可がなくても、必要な IAM 権限があればアクセスできる。

DataAPIでは、AWSのIAMによる認証でクラスタにクエリを投げられるというのがウリなので、 出来るだけ外からは接続できない場所にクラスタを立てます。 具体的には

Internet Gatewayなし
パブリックアクセスの許可なし
セキュリティグループはデフォルト設定
インバウンドは全てクローズ
という孤立した状況のクラスタを立ててみました。

（中略）

LambdaからDataAPIを使ってRedshiftでクエリを実行してみました。 従来はLambdaからRedshiftへクエリを実行したい場合、 Lambda関数はVPC内に設置しなければならない場合が多く、 そのLambdaから他のサービスに接続するような際にはVPCエンドポイントを準備しなければならないなど、 いくつか面倒なこともありました。 DataAPIによってLambdaとRedshiftの繋がりがより疎になり、シンプルな構成にすることができました！

サブネットもセキュリティグループも考慮不要！Lambda関数からData API for Redshiftでクエリ実行してみた。 | DevelopersIO