Alteryx DesignerからSnowflakeにデータをバルクロードする – Snowflake Advent Calendar 2019 #SnowflakeDB
こんにちは。DA部のスズです。
本エントリはSnowflake Advent Calender 2019の23日目のエントリです。
本エントリでは、Alteryx Designerのデータ出力ツールを使用して、Snowflakeにデータをバルクロードする方法をご紹介します。
準備
事前準備&動作環境
今回利用したAlteryx Designerの環境は以下の通りです。
- Windows 10 Pro
- Alteryx Designer Non-Admin 日本語 バージョン2019.4.4.20206
Alteryx DesignerからSnowflakeへの接続には、今回はODBCを使用します。以下のドキュメントからWindows用のインストーラを入手しています。なお、今回はsnowflake64_odbc-2.20.0.msiを利用しています。
ODBCのインストールについては、以下のエントリにて紹介しております。
また、AlteryxのヘルプにはSnowflakeとの連携について記載があります。あわせてご参照ください。
また、Snowflake、Alteryx Designer以外に、Amazon S3を使用します。あらかじめ、Amazon S3のバケットと、アクセスキー、シークレットキーを用意しておく必要があります。なお、Snowflakeにデータを出力したのちS3バケットからデータは削除されるため、S3バケットにデータは残りません。
Alteryx DesignerからSnowflakeへの接続に使用したSnowflakeのユーザーアカウントについては、Default RoleがSYSADMIN、Default Warehouseに任意のWarehouseが設定されているものを使用しています。
Alteryx Designerからデータをバルクロードする
実践
Snowflake側には、あらかじめデータベースを作成しておきます。Alteryx Designerからのデータの出力先として、「FROM_ALTERYX」というデータベースを用意しておきました。
バルクロードするデータとして、150000レコードあるものを用意しました。以下の画像は、Alteryx Designer上でそのデータを表示している状態です。
Snowflakeへのデータ出力には、Alteryx Designerのデータ出力ツールを使用します。
データ接続のデータソースを開き、Snoflakeのところにある一括を選択します。
Snowflakeバルク接続というウィンドウが表示されますので、設定を行います。
今回は以下の項目を設定しました。
- ODBCデータソース:
- データソース名:ODBCに設定したDSN
- ユーザー名:Snowflakeへのログインに使うユーザー
- パスワード:Snowflakeへのログインに使うパスワード
- Amazon S3:
- AWSアクセスキー:Amazon S3にアクセスするためのアクセスキー
- AWSシークレットキー:Amazon S3にアクセスするためのシークレットキー
- エンドポイント:作成したバケットがあるリージョンのエンドポイント
- バケット名:データの出力先となるS3バケット
データ接続を設定したのち、出力テーブルの設定があります。今回は「AlteryxBulkTest」というテーブルにしました。
データ出力ツールの設定に戻り、SQLステートメント事前作成にUSE DATABASE <データベース>
でデータベースの指定を追加します。これでデータ出力ツールの設定は完了です。
ワークフローを実行、正常に終了しました。
Snowflakeでデータベースを確認すると、新しくテーブル「AlteryxBulkTest」が作成されています。
Alteryx Designerのデータ入力ツールを使って、新しく作成されたテーブルの中身を確認してみます。Snowflakeからデータを取得してみると、データがあることを確認できました。
実行されたSQL
SnowflakeのHistoryでは、発行されたSQLを確認することができます。
本エントリの実践の内容では、以下のSQLが実行されていました。USE DATABASE
でデータベースの選択後、CREATE TABLE
でテーブルを作成し、COPY INTO
でS3バケットからデータをコピーしているようです。
USE DATABASE <データベース>; CREATE TABLE "<テーブル>" ("Sepal.Length" varchar(254),"Sepal.Width" varchar(254),"Petal.Length" varchar(254),"Petal.Width" varchar(254),"Species" varchar(254),"RowCount" varchar(254)) copy into "<テーブル名>" from '<S3バケット>' credentials=(aws_key_id='<アクセスキー>' aws_secret_key='<シークレットキー>') file_format=(type=csv field_delimiter='0x01' escape_unenclosed_field=none);
エラーになった話
本エントリの執筆中、Alteryx Designerからバルクロードを試した際、以下のエラーが発生することがありました。
Error: データ出力 (5): COPYはエラーで失敗しました: コピー操作後に一部のファイルをS3から削除できませんでした!10AWSからのエラー: The bucket you are attempting to access must be addressed using the specified endpoint. Please send all future requests to this endpoint.
Snowflakeバルク接続の設定時に、エンドポイントをDefaultのまま設定していなかったことが原因でした。Amazon S3で用意したバケットにあわせてエンドポイントを設定する必要があります。エンドポイントはAWSのWebサイトにてご確認いただけます。
ODBCで同じデータを出力した場合
同じデータを使用して、Alteryx Designerのデータ出力ツールからODBCを選択し、データ出力を試してみました。
以下の画像は、左がバルクロードでのデータ出力、右がODBCでのデータ出力の結果画面になります。ワークフローの実行完了まで、バルクロードの場合は約8秒程度ですが、ODBCの場合は約10分ほどかかっていることが分かります。
利用する環境によって異なるかと思いますが、データのサイズが大きい場合、ODBCでのデータの出力に時間がかかる可能性があります。時間がかかる場合には、バルクロードでの対応をご検討いただくといいかと思います。
最後に
今回はAlteryx Designerを使ってSnowflakeにデータをバルクロードする方法をご紹介しました。
Snowflakeアドベントカレンダーも、残すところあと2日。明日の24日目は、大場 裕喜による「#24:ゼロコピークローン機能をまとめる」の予定です。お楽しみに!