この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
当エントリは『クラスメソッド CSアナリティクス Advent Calendar 2020』22日目のエントリです。
- クラスメソッド CSアナリティクス Advent Calendar 2020 - Qiita
- クラスメソッド CSアナリティクス Advent Calendar 2020 | 特集カテゴリー | Developers.IO
- ビッグデータ分析支援のカスタマーストーリーアナリティクス|クラスメソッド|クラスメソッドのサービス
当エントリでは、クラスメソッドが展開しているデータ統合基盤サービス『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ファイルとして保存しておきます。
wp_postmeta.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)
ちなみにファイルを直接画面経由からアップロードしたい場合は[フォルダを選択]から行うことも可能です。
まとめ
という訳で、『クラスメソッド CSアナリティクス Advent Calendar 2020』22本目のエントリ、「CSA JMC(Job Management Console)」でAmazon Redshift環境上でSQLを実行する際の手順に関する内容の紹介でした。
CSA Data Uploaderは1ヶ月間のトライアル利用が可能となっています。興味をお持ち頂いた方は是非無料版ダウンロードページからインストーラを入手頂き、お試し頂けますと幸いです。また、CSA JMCに関しても下記バナーから製品ページにアクセスする事が出来ます。是非御覧ください。
では、明日(22日目)のエントリもお楽しみに!