SQLによるバッチ処理(ELT)
この時点で、BIツールやDBクライアントツールなどからアクセスすることは可能となっていますが、先に進む前に『SQLによるバッチ処理』について触れておきたいと思います。
連載第2回目の『分析テーマから必要なデータを考える』の項で言及していますが、実際の業務で利用するデータは取り込んだだけではデータや構成が不十分なことがあります。分析・参照する上では項目が足りない、マスタコードに対する名称がない、または逆に不要なデータや項目があるので除去しておきたい……などです(※「ケース2」が該当))。
こういうような要望が挙がった場合、バッチ処理としてSQLを実行することでそれらを実現することが可能です。"ETL"というフレーズがExtract(抽出)→Transform(変換)→Load(ロード)という「データソースに投入する前に内容に変換をかける」処理であるのに対し、この「データソースに投入してから内容に変換をかける」ことをELT(Extract/Load/Transfotm)とも呼ぶこともあります。
例えば、ordersの情報とcustomerの情報をBIツール上で結合して参照するのではなく、DB上で結合した状態で見たい場合があるとします(※件数にもよりますが、あらかじめデータを結合させておく、非正規化の状態にしておくことはパフォーマンスの観点から見てもベストプラクティスの一つでもあります)。以下のような結合用のテーブルを用意しておき、
CREATE TABLE codezine.orders_and_customer ( /** (ordersテーブルの必要な項目定義を列挙 */ /** (customerテーブルの必要な項目定義を列挙 */ )
テーブルへのCOPY処理が完了した時点で、以下のようなINSERT~SELECT文を実行することで目的を達成できます。
INSERT INTO codezine.orders_and_customer ( SELECT (ordersテーブルの必要な項目), (customerテーブルの必要な項目) FROM codezine.orders INNER JOIN codezine.customer ON codezine.orders.customer_name = codezine.customer.customer_name )
更新や削除を伴う、または一連の操作がすべて完了した上で処理を完結させたい場合は、トランザクションを用いたSQLを実行するようにしてください。
BEGIN; (一連のSQL処理を記述) COMMIT;
データのCOPYからこれらのSQLによるバッチ処理については、シェルスクリプトからDB環境にアクセスし、トランザクションの管理下で一連のSQLを実行させるなどの工夫が必要です。実運用の場合、cronやタスクスケジューラにスクリプトを設定する作業なども必要となるでしょう。この部分の解説については割愛致しますが、環境に応じたフロー制御や自動化の仕組みが必要となってくる部分ではあります。
その他SQLによるバッチ処理の実行についてのポイントについては、以下のとおりです。
- Amaozn Redshiftが準拠しているPostgreSQL同様、INSERT/DELETE/UPDATEの実行も問題なく行えます。ただUPDATE/DELETE文を実行した際はVACUUM処理を行ってディスクスペースの整理を行う必要も出て来ますので気を付けてください(参考:『VACUUM - Amazon Redshift』)。
- INSERT~SELECT文はデータの追記において高速に働きますが、INSERT INTO文でデータを1行ずつ投入する操作は著しく効率が落ちますので気を付けてください(参考:『INSERT - Amazon Redshift』)。
BIツールからの接続確認
これでデータアクセスの準備が整いました! いよいよ実際にBIツールからアクセスしてそのデータを可視化してみたいと思います。
冒頭および連載初頭でご紹介したように、BIツールについてはTableau Desktopを利用します。ツールには2週間の無料トライアル期間も付いていますので、これを機会にTableauを利用してみてはいかがでしょうか。
インストールおよび情報の登録を完了させ、Tableau Desktopを起動(インストール作業については特に複雑な処理はないので割愛します) 、『データに接続』を選択します。
接続先の種類を選択します(「サーバー上」→「Amazon Redshift」)。
Amazon Redshiftクラスタ内のDBに接続するための情報入力を求められるので、所定の内容を設定します。設定が完了したら『接続』を押下します。なお、このタイミングで接続が行えない場合、ネットワークやセキュリティグループの設定が意図したものではない場合がありますので内容を確認してください。
詳細な接続設定画面に遷移しました。ここでスキーマ『codezine』を選択するとスキーマ内に構築したデータベースの一覧が表示されます。ordersテーブルを画面右上部分の領域にドラッグ&ドロップすると右下領域にデータ内容を確認することも可能です。「ワークシートに移動」を選択します。
Tableau Desktopの編集画面に遷移しました。
数回の操作でTableau上に以下のようなグラフを作成することができました。
まとめ
以上で連載第4回目の内容は終了です。ここまでで、ひとまずの『ファイルからデータ可視化』までの一連の流れが線を結んだ形となりました。
この後はゴールとなった可視化の内容を踏まえて可視化フローをブラッシュアップしたり、可視化フローの本数を増やしたり、データの整形処理を自動化したりパフォーマンス改善を施したり……というような形で進化・改善を重ねていく形です。データやファイルがあるだけではピンと来なかったけども、実際にデータを可視化してみて見えてくる部分、問題点や新たなアイデアが出てくることも多いです。まずは身近な部分、一番気になる部分からデータの可視化を行ってみてください。その際にこの連載内容が作業の一助となれば幸いです。