■はじめに大量のデータを集約・蓄積し、ビジネス判断のために分析結果をスピーディに展開するために高パフォーマンスのDWHが求められています。近年では、データ量や使用頻度に応じて柔軟にスケールできるよう、クラウド上にDWHを配置したり、Saasを使用する機会が多くなっています。 ■Snowflakeとは Snowflake はクラウド上で実行されるDWHです。 Amazon Web Services、Microsoft Azure、Google Cloud Storage といった複数のクラウドサービスで使用でき、各クラウドサービスのストレージにあるファイルをロード(読み込み)、およびアンロード(書き出し)を行うことができます。 Snowflakeはデータベース-スキーマ-テーブルの階層となっており、仮想ウェアハウスはCOPY INTO や SELECT といったクエリを実行するための環境となります。また、外部ストレージと接続を行う場合はストレージ統合(STORAGE INTEGRATION)を経由して接続を行います。 ■検証内容今回は、DataSpiderを活用して以下の処理が実行できることを確認していきます。 ①ストレージ(ファイル)⇒Snowflakeへのロード ②Snowflake⇒別のSnowflakeのテーブルへデータを集計 ③Snowflake⇒ストレージ(ファイル)へのアンロード ④Snowflakeから集計結果をエクスポート ■検証環境使用した環境はこちらです。・Snowflake(AWS) ・DataSpider Servista 4.2 SP7 ・Windows Server 2016(Amazon EC2) (DataSpider Servista をインストールする環境のPaasとして使用します。) ・Amazon S3 (今回はAWS上でSnowflakeを利用するため、ストレージサービスとして使用します) ■事前準備 ◎Snowflake設定まずはSnowflakeの設定をしていきます。今回は接続したいSnowflakeのアカウントとIAMロール、S3のバケットがすでに作成済みの想定で検証を行っていきます。Snowflakeに接続したい環境がない場合は以下のリンクの手順で作成してください。 --------------------------------------------------------------------------------- ・トライアルアカウント作成・IAMロール作成 --------------------------------------------------------------------------------- ・S3ファイル準備「① ストレージ(ファイル)⇒Snowflakeへのロード」と、「③Snowflake⇒ストレージ(ファイル)へのアンロード」を実施するため、S3のバケットに、Snowflakeへロードするファイルを配置するフォルダと、Snowflake からアンロードしたファイルを配置するフォルダを作成します。その後、Snowflakeへロードするファイルを配置するフォルダに、ロードしたいCSVファイルを配置します。なお、CSVファイルは、以下のようなデータを格納しています。 1,2004-07-13,1,1,和歌山県和歌山市,073-123-XXXX,2004-07-18 00:00:00,60000,100,3000 ◎DataSpider 設定 Snowflake に接続を行うDataSpiderの設定を、以下の流れで行っていきます。・JDBCドライバの配置・グローバルリソースの設定・スクリプトの作成と実行なお、Snowflake 側では、あらかじめ以下のような名称で設定しています。データベース：DS_SNOWFLAKE スキーマ：DS_SCHEMA テーブル：DS_SALES、DS_EMPLOYEE_SALES ウェアハウス：DS_WAREHOUSE テーブルは以下のような定義で作成しています。【テーブル：DS_SALES】 CREATE TABLE DS_SALES ( sales_no INTEGER, sales_date DATE, employee_id INTEGER, customer_id INTEGER, shipping_address VARCHAR(100), shipping_telphone VARCHAR(20), delivery_time DATETIME, total NUMERIC(9,2), carriage NUMERIC(9,2), excise NUMERIC(9,2) ) 【テーブル：DS_EMPLOYEE_SALES】 CREATE TABLE DS_EMPLOYEE_SALES ( employee_id INTEGER, total NUMERIC(9,2) ) また、S3と接続するためのストレージ統合「DS_STORAGE」を作成するため、以下のクエリを実行します。【ストレージ統合：DS_STORAGE】 CREATE STORAGE INTEGRATION DS_STORAGE TYPE = EXTERNAL_STAGE STORAGE_PROVIDER = S3 ENABLED = TRUE STORAGE_AWS_ROLE_ARN = 'IAMロールのARN名' STORAGE_ALLOWED_LOCATIONS = ‘s3://’ ストレージ統合が作成されたら、AWS側でストレージ統合からのアクセスを許可するため、「STORAGE_AWS_IAM_USER_ARN」と「STORAGE_AWS_EXTERNAL_ID」を IAMロールの「信頼関係の編集」で編集するJSONに、以下のように設定していきます。【IAMロールの信頼関係】 { "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "AWS": [ "< STORAGE_AWS_IAM_USER_ARN >" ] }, "Action": "sts:AssumeRole", "Condition": { "StringEquals": { "sts:ExternalId": "< STORAGE_AWS_EXTERNAL_ID >" } } } ] } ■JDBCドライバの配置始めにSnowflakeへの接続を行うためのDataSpiderの準備を行います。今回は、Snowflakeに対してJDBCドライバ経由でクエリを実行するために、JDBCアダプタを使用します。 SnowflakeのJDBCドライバは、以下のリンクからダウンロードできます。 https://docs.snowflake.com/ja/user-guide/jdbc-download.html $DATASPIDER_HOME /server/plugin/data_processing/modules/jdbc_adapter に、SnowflakeのJDBCドライバを配置してからDataSpiderServerを起動することで、 JDBCアダプタの準備は完了です。 ■グローバルリソースの設定グローバルリソースを開き、「新しいグローバルリソースの作成」をクリックします。「データベース」カテゴリーの「JDBC」を選択し以下のように接続情報を入力していきます。ドライバクラス名：net.snowflake.client.jdbc.SnowflakeDriver URL：jdbc:snowflake://.snowflakecomputing.com/ ?db=<データベース名>&warehouse=<ウェアハウス名>&schema=<スキーマ名>&role=ACCOUNTADMIN ユーザ名：<作成したユーザ名> パスワード：<作成したユーザのパスワード> 今回はACCOUNTADMIN権限で実行するため、URLの末尾にrole=ACCOUNTADMINを設定しています。 URLの記述方法は、Snowflake の公開ドキュメントに記載されています。 https://docs.snowflake.com/ja/user-guide/jdbc-configure.html#jdbc-driver-connection-string ■スクリプトの作成と実行【S3からのロード】 S3に配置したファイルをSnowflakeにロードしてみます。ファイルのロードは、以下のSQL文を更新系SQＬ実行処理に設定して実行します。 COPY INTO DS_SALES FROM s3://<バケット名>/<フォルダパス>/<ファイル名> STORAGE_INTEGRATION = <ストレージ統合> 正常に処理が完了することを確認します。先ほどS3に配置したファイルがテーブルにロードされデータが書き込まれていることが確認できます。【データマートの書き込み】次にDWHのテーブルに格納したデータを、社員ごとに集計した売上をデータマートのテーブルに書き込みます。集計しながら書き込むために、以下のSQL文を実行します。 INSERT INTO DS_EMPLOYEE_SALES (employee_id,total) SELECT employee_id,SUM(total) FROM DS_SALES GROUP BY employee_id 正常に処理が完了することを確認します。同様に、テーブルブラウザで集計された結果が書き込まれていることを確認します。【S3へのアンロード】集計したデータマート用のテーブルデータを、S3にファイルとしてアンロードします。ロードとはテーブルの箇所を逆にすれば、アンロードのSQL文となります。 COPY INTO s3://<バケット名>/<フォルダパス>/<ファイル名> FROM DS_EMPLOYEE_SALES STORAGE_INTEGRATION = <ストレージ統合名> 正常に処理が完了することを確認します。 S3を確認しにいくと、ファイルが作成されていることが確認できます。展開してファイルの中身を確認すると、以下のように集計結果が書き込まれていることが確認できます。【データの取得】最後に、データマート用のテーブルのデータをExcelに書き込み、結果を一覧表示します。データマート用のテーブルのデータをそのまま取得したい場合は、テーブル読み取り処理でテーブル名を指定すれば取得できます。データマート用のテーブルのデータをそのまま取得したい場合は、テーブル読み取り処理でテーブル名を指定すれば取得できます。マッピングが設定されていることを確認すれば、書き込み完了な状態です。スクリプトを実行し、Excelにデータが書き込まれていることを確認します。 Snowflakeと同じ集計結果をExcelに出力することができました。 ■おわりにいかがだったでしょうか。 DataSpiderを利用することで、Snowflakeにデータをロード、集計、アンロードといった操作を行うことができました。今回実施した内容以外に、データをロードするテーブルの作成を行ったりすることもできますので、こちらの記事を参考にSnowflakeとの連携をDataSpiderでお試しいただければと思います。

Snowflakeにつないでみた