Azure と Snowflake を連携させてデータロードをしてみよう

Snowflake と Azure を連携させる準備をしよう

Azure Blob ストレージからデータを Snowflake にロードする場合、下図のように2つの方法が用意されています。１つは Azure コンテナーを参照する外部ステージを使用してデータをロードする方法と、Azure コンテナーから直接データをロードする方法です。同じ場所からデータを繰り返し取得する場合は前者が推奨されているので、本ブログでは外部ステージを使用したデータロード手順を紹介します。

Microsoft Azure からの一括ロード — Snowflake Documentation (https://docs.snowflake.com/ja/user-guide/data-load-azure.html) より引用

また今回はより安全な Azure Blob Storage へのアクセスを実現するために Snowflake ストレージ統合オブジェクトを構成し、 Azure AD に認証を委任します。これにより Snowflake からAzure Blob ストレージにアクセスする際に秘密キーやアクセストークンなどの明示的な認証情報を渡す必要が無くなります。

なお今回の作業は前提として 「ACCOUTADMIN」 ロールが割り当てられている Snowflake ユーザーが使え、Azure AD にて 「Application administrator」 などのアプリケーション作成が許可されているロールが付与されている必要があります。また Snowflake にロードするCSV形式のデータを Blob Storage のコンテナー内に用意する必要があります。本ブログでは Microsoft が用意している 「Adventure Works」 というサンプルの売上データセットを CSV フォーマットに修正して使用しています。

【参考: Adventure Works データセットについて】
https://docs.microsoft.com/ja-jp/sql/samples/adventureworks-install-configure

Snowflake でクラウドストレージ統合を作成する

まずは Azure AD に Snowflake サービスプリンシパルを作成するために、 Snowflake クラウドストレージ統合を作成していきます。 Snowflake の Web UI ページにログインし、任意のワークシートに下記のクエリを入力します。その際、 「AZURE_TENANT_ID」 をお使いの Azure テナントIDに、 「STORAGE_ALLOWED_LOCATIONS」 をデータロード元の　Storage Account コンテナーロケーションに変更し、実行します。

--サービスプリンシパルの作成やステージ作成はACCOUNTADMINで実施する。
USE ROLE ACCOUNTADMIN;

--Snowflakeクラウドストレージ統合を作成
CREATE STORAGE INTEGRATION azure2snowflake
    TYPE = EXTERNAL_STAGE
    STORAGE_PROVIDER = AZURE
    ENABLED = TRUE
    AZURE_TENANT_ID = '[お使いのAzure tenant ID]' 
    STORAGE_ALLOWED_LOCATIONS = ('azure://[お使いの　Storage Account 名].blob.core.windows.net/[データロード元のコンテナー]/');

同意 URL の取得および Snowflake アクセスの許可

Snowflake クラウドストレージ統合を作成した後、下記のクエリを実行します。クエリ結果の 「AZURE_CONSENT_URL」 プロパティの 「Property_value」 箇所に同意 URL が記載されているのでクリックしてページ遷移し、 「Accept」 ボタンをクリックします。このアクションにより、 Snowflake 用に作成された Azure サービスプリンシパルは、テナント内のリソースに対してのアクセストークンを取得することができます。

なお 「Accept」 が完了した後は Snowflake の企業サイトにリダイレクトされます。

--同意URLの取得
desc storage integration AZURE2SNOWFLAKE;

Snowflake サービスプリンシパルを Azure が作成するのに1時間以上かかる場合があります。サービスプリンシパルは上図の 「AZURE_MULTI_TENANT_APP_NAME」 項目の 「property_value」 に記載された名称で作成されており、 Azure AD の「エンタープライズ アプリケーション」項目で確認することができます。

Snowflake サービスプリンシパルにロールを付与する

今回は Azure Blob Storage からデータをロードするので、下図のように Storage Account の「アクセス制御 (IAM) 」から Snowflake サービスプリンシパルに 「Storage Blob Data Reader」 ロールを付与します。本ブログでは実施しませんが、もし Blob Storage にデータをアンロードする必要がある場合は 「Storage Blob Data Contributor」 ロールを付与します。

以上で Snowflake と Azure を連携させる準備は完了です。

Azure Blob Storage からデータを取得しよう

今回 Azure Blob Storage からデータを取得するためには、 Snowflake 側で外部ステージ、データウェアハウスを作成する必要があります。また取得したデータはデータベースに格納したいので、本章では外部ステージ、データウェアハウス、データベースの作成を実施し、 SELECT 文で外部ステージに配置されているファイルに対してクエリを行います。

作業用ロールの作成

今回は 「DATA_TEAM」 というロールを作成して、そのロールを使用してテーブルの作成やデータロードを実施します。下記のクエリを実行し、ロールを作成します。

--作業用ロールの作成
CREATE ROLE DATA_TEAM;
GRANT ROLE DATA_TEAM TO ROLE SYSADMIN;

データウェアハウスとデータベースの作成

下記のクエリを実行し、データウェアハウスとデータベースを作成していきます。ウェアハウスとデータベースの名称は任意のものに変更してください。

作成したウェアハウスの使用権限、作成したデータベースでのテーブル作成権限、すべてのスキーマの使用権限、ファイルフォーマットの作成権限を作成したロール 「DATA_TEAM」 に付与します。

--使用するWarehouseとDB作成およびDATA_TEAMロールへの付与
CREATE WAREHOUSE DATA_SOKAMURA
    WAREHOUSE_SIZE = XSMALL
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = TRUE;
    
GRANT USAGE ON WAREHOUSE DATA_SOKAMURA TO ROLE DATA_TEAM; 

CREATE DATABASE ADVENTURE_WORKS_DB;

GRANT USAGE ON DATABASE ADVENTURE_WORKS_DB TO ROLE DATA_TEAM;
    
GRANT USAGE ON ALL SCHEMAS IN DATABASE ADVENTURE_WORKS_DB TO ROLE DATA_TEAM;
    
GRANT CREATE TABLE ON ALL SCHEMAS IN DATABASE ADVENTURE_WORKS_DB TO ROLE DATA_TEAM;
    
GRANT CREATE FILE FORMAT ON ALL SCHEMAS IN DATABASE ADVENTURE_WORKS_DB TO ROLE DATA_TEAM;

続いて下記のクエリを実行し、外部ステージを作成していきます。ステージの名称は任意のもの変更してください。またステージに対する使用権限を 「DATA_TEAM」 に付与します。

--stage作成
USE ADVENTURE_WORKS_DB.PUBLIC;

CREATE STAGE blob4sokamura
    URL = 'azure://[お使いの　Storage Account 名].blob.core.windows.net/[データロード元のコンテナー]'
    STORAGE_INTEGRATION = azure2snowflake;
    
GRANT USAGE ON ALL STAGES IN DATABASE ADVENTURE_WORKS_DB
    TO ROLE DATA_TEAM;

ここまででデータ取得の準備は完了です。続いて下記のクエリを実行し、外部ステージの確認を行います。

--ここから作成したロールDATA_TEAMで作業
USE ROLE DATA_TEAM;

--外部ステージの確認
LIST @blob4sokamura/customers/;

外部ステージが正常に作成されていると、下画像のように外部ステージ上のファイル情報を取得することができます。

ステージ上にあるファイルはクエリを実行し、データ内容を確認することができます。データ内容の確認のために下記のクエリを実行し、ファイルフォーマットの作成を行います。ファイルフォーマット名は任意のものに設定してください。なお今回の CSV データは行の先頭にカラム名が記載されているので、1行目はスキップする設定をしています。

--ファイルフォーマットを作成
CREATE FILE FORMAT MYCSVFORMAT
    TYPE=CSV
    COMPRESSION=NONE
    FIELD_DELIMITER=','
    FILE_EXTENSION='csv'
    SKIP_HEADER=1
    ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE;

下記のクエリを実行し、 Adventure Works の Customer データを確認します。

--Customer CSVファイルをクエリ
SELECT t.$1::Number(10,0),
    t.$2::VARCHAR(5),
    t.$5::Date,
    t.$6::VARCHAR(50),
    t.$7::VARCHAR(50),
    REPLACE(REPLACE(CONCAT(t.$9::VARCHAR(20),t.$10::VARCHAR(20)),'$'),'"')::Number(10,0),
    t.$11::Number(2,0),
    t.$12::VARCHAR(50),
    t.$13::VARCHAR(50),
    t.$14::VARCHAR(2)
FROM @blob4sokamura/customers/AdventureWorks_Customers.csv (FILE_FORMAT => MYCSVFORMAT) t;

下画像のように、実行結果が返ってきます。

以上で Azure Blob Storage からデータが取得できることが確認できました。次章では取得したデータをテーブルにロードするところまでに実施します。

テーブルにデータロードしよう

本章では、テーブルを作成し、外部ステージ上のファイルをテーブルにロードします。下記のクエリを実行し、 CUSTOMER テーブルを作成します。

--Customer tableの作成
CREATE OR REPLACE TABLE CUSTOMER (
    CustomerKey Number(10, 0),
    Prefix VARCHAR(5),
    FirstName VARCHAR(20),
    LastName VARCHAR(20),
    BirthDate Date,
    MartialStatus VARCHAR(50),
    Gender VARCHAR(50),
    EmailAddress VARCHAR(50),
    AnnualIncome Number(10,0),
    TotalChilderen Number(2,0),
    EducationLevel VARCHAR(50),
    Occupation VARCHAR(50),
    HomeOwner VARCHAR(2));

下記クエリを実行して、外部ステージ上のファイルから CUSTOMER テーブルにデータをロードします。

COPY INTO CUSTOMER
FROM (SELECT t.$1::Number(10,0),
    t.$2::VARCHAR(5),
    t.$3::VARCHAR(20),
    t.$4::VARCHAR(20),
    t.$5::Date,
    t.$6::VARCHAR(50),
    t.$7::VARCHAR(50),
    t.$8::VARCHAR(50),
    REPLACE(REPLACE(CONCAT(t.$9::VARCHAR(20),t.$10::VARCHAR(20)),'$'),'"')::Number(10,0),
    t.$11::Number(2,0),
    t.$12::VARCHAR(50),
    t.$13::VARCHAR(50),
    t.$14::VARCHAR(2)
FROM @blob4sokamura/customers/AdventureWorks_Customers.csv (FILE_FORMAT => MYCSVFORMAT) t)
ON_ERROR = CONTINUE;

下記クエリを実行し、テーブルにロードしたデータを確認してみましょう。

SELECT TOP 10 * FROM CUSTOMER;

上図のようにクエリの実行結果が返り、対象テーブルにデータがロードできていることが確認できます。以上でテーブルへのデータロードは完了です。

おわりに

本記事では Snowflake 上で Azure Blob Storage からのデータロード・テーブル作成の手順について説明しました。続編として Snowflake の機能検証や Power BI との連携方法についても記事を執筆予定ですので、ぜひご覧ください。

また当社では DATA ビジネスに実績と強みがあり、データ基盤構築から BI ツール (Power BI) の導入・技術支援、AIサービスの提供・技術支援までお客様のデータ利活用をワンストップで支援可能です。 持っているデータを活用したい、 BI や AI を使ってみたいけど何をすればよいかわからない、やりたいことのイメージはあるけれどどのようなデータを取得すればよいか判断できないなど、データ活用に関することであればまず一度ご相談ください。一緒に何をするべきか検討するところからご支援致します。