CSA JMCでAmazon Redshift環境に対してSQL実行を行う #データ統合基盤 #CSアナリティクス

「カスタマーストーリー」を支えるツール特集

#CSA: Job Management Console(JMC)

#ビジネス

#クラスメソッド

しんや

2020.12.22

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

当エントリは『クラスメソッド CSアナリティクス Advent Calendar 2020』22日目のエントリです。

当エントリでは、クラスメソッドが展開しているデータ統合基盤サービス『CSアナリティクス』(以降"CSA")のプロダクト「CSA JMC(Job Management Console)」でSQLをAmazon Redshiftに対して実行する際の手順について紹介します。

CSAの構成要素(SQL)機能を用いたAmazon Redshift環境へのSQL実践

CSA JMCでは、「ジョブ」で実行出来る処理要素を、以下の「構成要素」に分類する形で作成・管理出来るようにする体制を取っています。Redshift連携に於いては以下のような定義となっています。

構成要素(データ連携)：Amazon S3配下のデータをAmazon Redshift環境下に取り込む、所謂「COPYコマンド」で実行する内容を作成・管理・スケジュール連携するための要素
構成要素(SQL)：Amazon Redshift環境下のデータに対してSQLを実行するための要素
構成要素(プログラム)：任意のプログラムをジョブ実行の流れで実行するための要素(現状対応している言語はPythonのみ)

当エントリではこの中の「構成要素(SQL)」について解説する形となります。

想定ケースと事前準備

SQLを実行する対象はCSA JMCで「テーブル作成を行った」テーブルに対して行う想定とします。事前にテーブル作成が行われており、また「データ連携」処理が為されてデータが入っているテーブルに対して今回やろうとしているSQL処理を仕掛けよう...という流れですね。

実行するSQLは以下の様なものをイメージしています。不要なデータをDELETE文で削除しつつ、別途正規テーブルにデータを入れ替える..という処理です。このファイルは予めSQLファイルとして保存しておきます。

/** メタ情報管理：必要なもの以外を全部削除. */
DELETE FROM csademo.techblog_postmeta_load WHERE meta_key NOT IN(
 '_hatena_count',
 '_twitter_count',
 '_facebook_count',
 '_marge_count',
 '_view_count',
 '_social_status_cache_time',
 '_thumbnail_id',
 '_easy_wp_meta_description',
 '_gplus_count',
 '_book_amazon',
 'classic-editor-remember'
);

/** 正規テーブルにデータを入れ替え. */
DROP TABLE IF EXISTS csademo.wp_postmeta;
CREATE TABLE IF NOT EXISTS csademo.wp_postmeta (
  meta_id BIGINT encode zstd NOT NULL,
  post_id BIGINT encode zstd NOT NULL,
  meta_key VARCHAR(500) encode zstd,
  meta_value VARCHAR(65535) encode zstd,
  PRIMARY KEY(post_id)
)
DISTSTYLE EVEN;

INSERT INTO csademo.wp_postmeta (
SELECT meta_id, post_id, meta_key, meta_value FROM csademo.techblog_postmeta_load ORDER BY post_id
);

ちなみに処理実行前の件数はこんな感じです。

# SELECT COUNT(*) FROM csademo.techblog_postmeta_load;
 count  
--------
 112597
(1 row)

構成要素(SQL)の作成とジョブ実行

ここからは、実際にSQLをCSA JMC上に組み込んでジョブを実行する流れを説明していきます。

CSA JMCにてメニューから[構成要素]→[SQL]を選択。

構成要素(SQL)画面が表示されます。ちなみに初期状態ではまだ何も存在していません。

構成要素(SQL)にSQL処理を登録する際は、CSA JMCで連携している「構成要素(SQL)格納用S3バケット」にSQLファイルをアップロードしておくか、画面からSQLファイルを直接登録するかのいずれかを経て行います。今回は前者のS3アップロード手順を採りたいと思います。

CSA JMCで連携している「構成要素(SQL)格納用S3バケット」配下には、直下にsqlフォルダを必要とします。その下に、テーブル名やその他認識しやすいフォルダ構成を準備した上で、ファイルをアップロードしておきます。

CSA JMC側としては、一覧上の更新ボタンを押下することで認識する事が出来ます。リロード後、以下のような形で認識したことを確認出来ました。

今回登録した構成要素(SQL)を使ったジョブを選択し、

ジョブを新たに作成します。

作成したジョブをひとまずは手動で即時実行。

実行完了。ジョブと合わせて構成要素(SQL)も正常に実行出来ていることを確認出来ています。

実行結果をSQLで確認してみます。処理前よりも件数が減っており、また別途SQL処理内で作成した新規正規テーブルも作成されている事が確認出来ました。

# SELECT COUNT(*) FROM csademo.techblog_postmeta_load;
 count  
--------
 102684
(1 row)

# SELECT COUNT(*) FROM csademo.wp_postmeta;
 count  
--------
 102684
(1 row)

ちなみにファイルを直接画面経由からアップロードしたい場合は[フォルダを選択]から行うことも可能です。