Alteryxを使用してRedshiftへテキストデータをバルクロードする
はじめに
こんにちは、yokatsukiです。
今回、とある案件でAlteryxの処理結果をRedshiftにロードする必要が出てきました。Alteryxは、Redshiftにロードする方法がいくつかあるので、それぞれについて動作を確認しました。
AlteryxによるRedshiftデータロードを試す
環境
- MacBook Pro (Retina, 13-inch, Early 2015)
- macOS Sierra 10.12.5
- Windows 10 Pro 64bit on WMware Fusion 8.5.7
- Alteryx Designer x64 11.0.6.28907
- Amazon Redshift ODBC Driver x64 1.3.1.1000
- Amazon Redshift 1.0.1351
- dc1.large 2nodes
準備
入力データ準備
まず、ロードに使用するデータを準備します。
今回は、入力データとしてTableau社提供のサンプルデータ「Superstore」の日本語版を使用します。このサンプルデータにはテーブルに相当する複数のシートが含まれていますが、この中でも一番ポピュラーなOrdersを使用します。
Alteryxは本来、Excelのワークシートを直接読み取ることができるのですが、現在のところShift-JISが扱えないので、対策としてUTF-8形式のテキストファイルに変換します。ついでに区切り記号も信頼と実績のTSV(タブ区切り)にしておきます。
2017年6月28日追記：Alteryxの新バージョン 11.3でShift-JISが使用できるようになりました！
リンク先のワークブックをダウンロード後、Excelで開き、Ordersシートをタブ区切りテキストとして保存します。
そして、保存した.txtファイルをメモ帳で開き、文字コードをUTF-8に変換して保存します。
これで、ヘッダ付きのUTF-8形式、タブ区切りのデータが準備できました。
参考までに、作成したファイルをmacOSのfileコマンドで確認します。結果は以下です。
% file 0-Sample\ -\ Superstore\ Sales\ Japan-2013\(revised\).txt 0-Sample - Superstore Sales Japan-2013(revised).txt: UTF-8 Unicode (with BOM) text, with very long lines, with CRLF line terminators
こちらのファイルの中身をRedshiftにロードします。
ODBC設定
次に、AlteryxとRedshiftを繋ぐ設定を行います。
Alteryxとデータベースの接続は、Oracleなど一部のベンダー製品は固有の接続方法が使えますが、基本的にはODBCを使います。Redshiftもこの基本に従い、ODBCを使った接続設定を行います。
ODBC接続の設定方法は、下記リンクが参考になります。
最新版をインストールするために、下記ページからWindows用のRedshift ODBCドライバをダウンロードし、インストールします。
余談ですが、ODBCドライバは上書きインストールできません。旧バージョンのODBCドライバをインストールしている場合は、一旦アンインストールしてから最新版をインストールして下さい。
インストールしたドライバに対し、Redshiftの接続情報を追加します。スタートメニューから、[Windows管理ツール]-[ODBCデータソース(64ビット)]を選択します。
ODBCデータソースアドミニストレーター(64ビット)ウィンドウが開きます。まずは追加を押します。タブは今回本ユーザしか使用しないので、ユーザーDSNのままで良いでしょう。表示されたデータソースの新規作成ウィンドウで、Amazon Redshift (x64)を選択し、完了を押します。
新しいウィンドウAmazon Redshift ODBC Driver DSN Setupが開くので、AWS管理コンソールからRedshiftクラスタの情報を確認し、サーバー名やポート番号、ユーザ名、パスワードなど、適切な接続情報を記入します。Data Source Nameは自由に付けて構いません。目的が分かるように付けておきます。
クエリのデータサイズは多くないので、データをまとめてメモリ上で処理できるよう設定します。Additional Optionsボタンを押し、表示されたAdditional Configrationウィンドウで、Retrieve Entire Results Into Memoryが選択されていることを確認します。確認したらOKボタンを押してウィンドウを閉じます。
設定が完了したらウィンドウ下のTestボタンを押します。成功を表すメッセージが出ればO.K.です。
失敗する場合は、エラーメッセージを確認しましょう。下の例では、パスワードを間違えています。
OKボタンを押すとウィンドウは閉じ、元のODBCデータソースアドミニストレーター(64ビット)ウィンドウに、作成したデータソースが追加されていることが確認できます。これでODBC接続の準備は完了です。
ワークフロー設定
準備ができたので、いよいよAlteryxのワークフロー設定を行います。Alteryx Designerを起動し、新しいワークフローにデータロードのフローを構成します。
ツール"Input Data"配置
AlteryxのツールパレットFavoritesもしくはIn/OutからInput Dataツールをフローへドラッグ＆ドロップします。
ツールを選択し、画面左側のConfigurationペインで、ファイル入力の詳細設定を加えます。
Configurationペインの一番上、Connect a File or Databaseのドロップダウンリストの右側▽を押し、File...を選択します。
ファイルから、先ほど作成したUTF-8タブ区切りのテキストファイルを選択します。
選択すると、追加の設定ウィンドウ(Resolve File Type)が表示されます。ここで、タブ区切りのテキストデータであること、一行目は見出しであること(First Row Contains Field Names)を設定しておきます。
ファイルの設定ができました。Update Sample(青いリンク)をクリックすると、ファイルの取り込みプレビューが行なえます。念のため、設定を一通り確認して、Code PageをUnicode UTF-8に設定しておきます。
これで一通りのデータ取り込み設定ができました。次はこのデータをRedshiftに出力する設定を行います。
ツール"Output Data"配置
データをRedshiftへバルクロードする設定を行います。バルクロードは内部で以下の動作を行います。
- データをgzファイルにアーカイブ
- アーカイブしたファイルをS3バケットへアップロード
- (設定に応じてRedshiftテーブルの切り捨てや再作成)
- COPYコマンドを使い、ロードしたファイルの内容をRedshiftテーブルへロード
- S3へアップロードしたファイルを削除
ツールパレットFavoritesもしくはIn/OutからOutput Dataツールをフローへドラッグ＆ドロップします。Input Dataツールと近い場所に配置した場合は、自動的にワイヤ(接続線)が引かれますが、そうでない場合は手動でワイヤを引き、フローとして繋がるように設定します。
Output Dataツールを選択し、画面左側のConfigrationペインで、ファイル出力設定を加えます。
Configurationペインの一番上、Write to File or Databaseのドロップダウンリストの右側▽を押し、[Other Databases] - [Amazon Redshift Bulk...]を選択します。
Amazon Redshift Bluk Connectionウィンドウが表示されます。
ここで以下項目を入力します。
Data Source Name
上記ODBC設定時に作成したデータソース名を指定します。
ユーザ名やパスワードはデータソースの情報に含まれているのでここで改めて記入する必要はありません。
AWS Access Key & Secret Key
バルクロードで使用するS3バケットへのアクセス情報を記入します。あらかじめAWS管理コンソールなどからアクセスキーを発行しておいて下さい。
Secret Key Encryption
上記フォームで記入したシークレットキーの情報をどのような形式で暗号化するかを決めます。
Endpoint
使用するS3バケットのエンドポイントを、ドロップダウンリストで選択します。データをロードしたいRedshiftクラスタと同じリージョンを指定して下さい。
Bucket Name
ファイルをアップロードするバケットおよびフォルダ名を指定します。先頭のs3://やフォルダ名の後ろの/は不要です。
設定が一通りできたら、OKを押して次に進みます。
次にOutput Tableウィンドウが表示され、ロードしたいRedshiftテーブルの名前を指定します。ここではスキーマ名も含めcm_yokatsuki.ordersと設定しました。
Output Toolの設定ができました。
Configurationペインの中に、Output Optionsとして、テーブルの制御に関する設定ができます。ここでデータを追記(Append Existing)するか、洗い替え(Delete Data & Append)するか等を選択しておきます。
また、このConfigurationペインでは、Distribution Styleとして、分散キーの設定などもできるので、あらかじめ問合せの特性に応じたデータのロードもできるようになっています。
実行
作成したワークフローを実行し、結果を確認します。
Alteryxから確認
Alteryxからは、以下のような画面で動作完了を確認することができました。今回の環境では8369件のデータを30秒弱でロードできたようです。
ちなみにこの処理の間、S3バケットをよく観察していると、gz圧縮されたファイルがアップロードされる様子を見ることもできます。
SQLクライアントでRedshiftにアクセスし、テーブルがロードできている事を確認します。
AWS管理コンソールから確認
ロードされた様子をAWS管理コンソール側からも確認します。
確かにCOPYコマンドでロードが行われていることが確認できました。
まとめ
AlteryxのRedshiftバルクロード機能を使い、Redshiftへデータをロードしてみました。その結果、Output DataツールのRedshiftバルクロード機能では、以下の事ができることがわかりました。
- 内部動作としてCOPYコマンドを使う(従ってS3バケットアクセスが必要)
- テーブルの作成に関するルール設定が可能
- 既存のテーブルに追加(Append Existing)
- テーブルは残しつつ、データだけ洗い替え(Delete Data & Append)
- テーブル再作成(Overwrite Table(Drop))
- テーブル新規作成(Create New Table)
- テーブル再作成にまつわるDROPテーブルエラー無視
- カラム名を見出し(データ先頭行)から自動設定
- プライマリキー設定
- 分散スタイル、分散キー設定
- ソートスタイル、ソートキー設定
- ロード前に特定SQL実行
- ロード後に特定SQL実行
- ロード後のVACUUM / ANALYZE実行
- バルクロード時のデータチャンクサイズ設定(1MB〜100GB)
データのロード方法としては、他にも汎用ODBC設定や、In-DB機能等があるので、今後はそれらを比較したいと思います。それでは、また。
追記：汎用ODBC設定の場合の挙動を確認しました。併せてご覧ください。
![[初学者向け] IAM 設計の観点解説とテンプレート化してみた](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-7bf2dfb6b7a0dd9b7561e87b396ccdc3/018019c86ac567eceba7268550750d9f/aws-iam)
