データ統合基盤CSアナリティクスでSQLを実行してみる

2020.08.11

弊社では、短期間かつお手軽にAWS上にデータ分析基盤を導入可能なCSA(Customer Story Analytics)というデータ統合基盤パッケージを提供しています。

CSAのジョブ管理ツールであるJMC(Job Management Console)では、ジョブの構成要素として「データ連携」、「プログラム」、「SQL」の3つを定義することが可能で、その構成要素の一つ、あるいは複数を組み合わせる事によってジョブとして構成し、実行します。今回は、その中でも「SQL」を紹介します。(参考:JMCの概要説明

なお、JMC含め、CSAの全体像の説明については、こちらのブログでも触れていますので、是非ご一読ください。

目次

構成要素「SQL」とは?

    • 任意のSQLコードを実行することが可能です。
    • ユースケースとしては、Redshiftに登録されているテーブルを元に、データマートを作成する場合等です。
    • 通常、その他の構成要素「プログラム」、「データ連携」と組み合わせて利用します。(たとえば、「プログラム」でAPIを呼んで、JSONでデータ取得、CSVに変換し、S3に格納。そのCSVファイルを「データ連携」でRedshiftに反映させ、そのRedshift上のデータを用いて「SQL」でデータマートを作成するいった流れです。)
    • 2020年8月時点でCSAがサポートするデータソースはAmazon Redshiftのみとなりますので、登録・使用可能なSQLは、Amazon Redshiftに準拠したSQLとなります。

サンプルコード

Redshift上の public スキーマの topbabies テーブルからデータを抽出し、本日日付を付加した上で、sample_schemasample_martテーブルにその抽出データを流し込む、という流れです。

SQLをジョブとして実行する事前準備として、SQLクライアントツールを使ってRedshift上にテーブルを作成しておきます。

※ご参考:SQLクライアントツール:DBeaver

 /** ↓スキーマ作成 */
create schema sample_schema

 /** ↓テーブル作成 */
create table sample_schema.sample_mart(
  date DATE NOT NULL,
  state CHAR(2) NOT NULL,
  gender CHAR(2) NOT NULL,
  year INT NOT NULL,
  top_name VARCHAR(100) NOT NULL,
  occurences INT NOT NULL
)

JMC上で実行するサンプルコードは以下のとおりです。

truncate table {{ vars.schema }}.sample_mart;

insert into {{ vars.schema }}.sample_mart
select
  '{{ today }}',
  state,
  gender,
  year,
  "top name",
  occurences
from public.topbabies

構成要素「SQL」 では、実行時に任意の引数や、予め設定された組み込み変数を呼び出すことが可能です。

{{ vars.schema }}」の箇所で後述する任意の引数に置き換えられます。変数名の先頭に「vars.」を付与し、ブラケット2つで変数名を囲み、「{{ vars.変数名 }}」」という形で記載します。

{{ today }}」の箇所は、下記の組み込み変数に置き換えられます。ブラケット2つで変数名を囲み、「{{ 変数名 }}」」という形で記載します。

変数名 説明 サンプル値 備考
today ジョブ実行日付 2019-03-04
today_nodash ジョブ実行日付(「-」無し) 20190304
today_slash ジョブ実行日付(「/」区切り) 2019/03/04
yesterday ジョブ実行日付の前日 2019-03-03
yesterday_nodash ジョブ実行日付の前日(「-」無し) 20190303
yesterday_slash ジョブ実行日付の前日(「/」区切り) 2019/03/03
tomorrow ジョブ実行日付の翌日 2019-03-05
tomorrow_nodash ジョブ実行日付の翌日(「-」無し) 20190305
tomorrow_slash ジョブ実行日付の翌日(「/」区切り) 2019/03/05
now ジョブ実行日時 2019-03-04 15:06:07
now_slash ジョブ実行日時(「/」区切り) 2019/03/04 15:06:07
current_year ジョブ実行年 2019
current_month ジョブ実行月 03
current_date ジョブ実行日 04
current_hour ジョブ実行時間 15
current_minute ジョブ実行時間 06
current_second ジョブ実行秒 07
job_name 実行ジョブ名 サンプルジョブ
schedule_unit 実行サイクル名 daily
iam.redshift_role RedshiftのIAMロール名 arn:aws:iam::1234567890:role/xxxxxxx
s3.bucket S3のバケット名 csa-sample-s3
run_type ジョブ実行種別 scheduled 手動実行の場合「manual」,スケジュール実行の場合「scheduled」

JMCにサンプルコードをアップロードする

JMCにログインして、ヘッダーメニューの「構成要素」から「SQL」を選択します。

その後の操作は構成要素「プログラム」と同じとなりますので、構成要素「プログラム」のブログ(「JMCにサンプルコードをアップロードする」という見出しの部分)をご参照下さい。

なお、「SQL」では、コードのアップロード先が、対象バケット配下の「sql」になります。(構成要素「プログラム」の場合は「program」フォルダにアップロードされます。)

ジョブ設定

ヘッダーメニューの「ジョブ」から「ジョブ一覧」を選択し、ジョブを作成します。

ジョブの設定手順については、構成要素「プログラム」と変わりませんので、こちらも構成要素「プログラム」のブログ(「ジョブ設定」という見出しの部分)をご参照下さい。

引数を指定する箇所については、今回、以下のように設定します。これで、前述する「{{ vars.schema }}」が「sample_schema」に置き換わるようになります。

ジョブ実行・結果確認

対象のジョブを実行し、結果を確認します。

【SQL START】
truncate table sample_schema.sample_mart;

insert into sample_schema.sample_mart
select
    '2020-08-06',
    state,
    gender,
    year,
    "top name",
    occurences
from public.topbabies;
【SQL END】
【MAIL】
sample_sql_job の実行が完了しました。
成功しています。

上記、ログを確認すると、「{{ vars.schema }}」が「 sample_schema 」に、「{{ today }}」が「 '2020-08-06' 」に置き換わっていることが確認できます。

SQLクライアントツールからもsample_martテーブルに日付が付加されたデータが反映されていることが確認できます。

日付を加えたことで、データの追加日をwhere句に指定して(select * from sample_schema.sample_mart where date = '2020-08-06'select * from sample_schema.sample_mart where date between '2020-08-06' and '2020-08-07'のように)、絞り込みを行う事が可能になります。

まとめ

構成要素「SQL」を利用することで、Redshiftに反映されたデータを元に、データマートを作成するELT処理が可能となります。

前述のとおり、「{{ 変数名 }}」という形で組み込み変数を用いて、データマートにデータを登録した日付を加えることで、特定日付、期間での絞り込みが可能になり、データの反映の履歴を検索する時等に便利かと思います。

また、「{{ vars.変数名 }}」という形で変数を定義すれば、GUIから変数を定義できますので、SQLコードに直接触れることなく、内容の一部を編集することも可能です。

なお、その他の構成要素である「データ連携」、「プログラム」については、下記のブログで説明しておりますので、併せて確認頂けますと幸いです。

構成要素「データ連携」:データ統合基盤CSアナリティクスを使ってS3上のデータをRedshiftに定期連携してみる

構成要素「プログラム」:データ統合基盤CSアナリティクスでpythonコードを実行してみる

今後もCSAの機能について定期的に発信していきます。