PolyBaseを使ったAzure SQL DWへの高速インポート ～図解で分かりやすく説明～：Microsoft Azure SQL Data Warehouse を使ってみた(5)
- TAG : azure | Microsoft Azure SQL DW | Tech & Science | クラウドサービス
- POSTED : 2015.11.19 08:09
f t p h l
目次
PolyBaseは設定情報が複数に分かれる。それぞれの関係を正しく理解しよう
前回、PolyBaseから参照するネットワークストレージのAzure Storageをご紹介しました。今回は、実際にAzure SQL Data Warehouse(以下、SQL DW)からAzure Storage上のファイルをPolyBaseで参照し、インポート(データのロード)する方法をご紹介したいと思います。
SQL DWとAzure Storageの関係
PolyBaseを使ったSQL DWのインポートについて、Microsoftのチュートリアルに説明がありますが、設定する情報が多いため、理解するのに困難です。そこで、それぞれのSQL DW設定情報とAzure Storageの関係を図解したのが下記になります。(私が理解するために纏めた図のため、実際の機能と異なる場合があります)
SQL DWにインポートするためには、外部テーブルを作成し、間接的に外部テーブルから参照するAzure Storage上のインポートファイルを参照することができます。この外部テーブルは普通のテーブル同様にSQL命令で行の絞り込み(where)などが行えるため、外部テーブルからSQL命令(insert into ~ select ~)でインポート先テーブルへ行追加が行えます。
これから各情報の設定方法について、設定順にご紹介します。なお、使用しているSQL文は、Microsoftのチュートリアルに記載しているSQLを参考にして言いますので、合わせてご覧ください。
PolyBaseの設定ポイント
①Azure StorageのBLOBにインポートファイルをアップロード
前回、構築したAzure StorageにインポートファイルをAzure Storage Explorerなどを使ってアップロードします。ファイルをAzure Storageにインポートする前にBLOBのコンテナ(Container)を作成します。コンテナは、Amazon S3のバケットのようなもので、Azure Storageのサービスごとに作る必要があります。
インポートするファイルは以下のファイル形式で作成する必要があります。また、インポートファイルは、無圧縮、GZIP圧縮に対応しているようです。
- 文字コードはUTF-8
- CSVなどの区切り文字付きファイル
②データベースマスターキーの作成
次のステップで作成する「データベーススコープの資格情報」を登録するためにデータベースマスターキーの作成を行います。このデータベースマスターキーは、DBに登録する機密情報を登録する際の暗号化キーとして使用され、「データベーススコープの資格情報」を登録するためには必要な情報です。データベースマスターキーは、DBに1度登録すると、それ以降は登録の必要がありません。
|
1 |
CREATE MASTER KEY; |
③データベーススコープの資格情報の作成
データベーススコープの資格情報として、①でアップロードしたAzure Storageのアクセスキーを登録します。ここで入力したデータベーススコープ名は、次のステップで使用します。
|
1 2 3 4 |
CREATE DATABASE SCOPED CREDENTIAL GiXoStrage01 WITH IDENTITY = 'GiXoStrage01' , Secret = '[Azure Strageのプライマリアクセスキー]' ; |
④外部データソース情報の作成
外部データソース情報として、①でアップロードしたAzure Storageの接続情報を登録します。TYPEはAzure BLOB Storageに接続するため「HADOOP」、LOCATIONには「Azure Storageのプライマリ BLOB サービス エンドポイント」、CREDENTIALには③で作成したデータベーススコープ名を設定してください。ここで入力した外部データソース名は、⑥のステップで使用します。
|
1 2 3 4 5 6 7 8 |
CREATE EXTERNAL DATA SOURCE azure_storage_test WITH ( TYPE = HADOOP , LOCATION ='wasbs://test@gixo01.blob.core.windows.net' , CREDENTIAL = GiXoStrage01 ) ; |
⑤外部ファイル形式情報の作成
外部ファイル形式情報として、データ項目の区切り文字などを登録します。ここでは、区切り文字以外に日付フォーマット、圧縮形式などの細かいファイル形式の指定を行うことができます。単純なカンマ区切りのファイル形式の場合、下記の内容で十分です。(詳しくはCREATE EXTERNAL FILE FORMATの説明参照) ここで入力した外部ファイル形式名は、次のステップで使用します。
|
1 2 3 4 5 6 7 8 9 |
CREATE EXTERNAL FILE FORMAT CSV WITH ( FORMAT_TYPE = DELIMITEDTEXT , FORMAT_OPTIONS ( FIELD_TERMINATOR =',' ) ) ; |
⑥外部テーブルの作成
外部テーブルでは出力形式とインポートファイルを読み込むための情報を指定します。CREATE EXTERNAL TABLEの「WITH」の上部には、テーブル作成時と同様にデータ項目名とデータ型の指定を行います。そして、LOCATIONには①でアップロードしたAzure Storageのファイルパス、DATA_SOURCEには④の外部データソース名、FILE_FORMATには⑤の外部ファイル名を設定してください。あとは「select * from ext_sku_name」とSQL命令で問い合わせるだけでインポートファイルの情報を参照することができます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE EXTERNAL TABLE ext_sku_name ( sku_cd char(11) NULL , kana nvarchar(150) NULL , kanji nvarchar(150) NULL ) WITH ( LOCATION = '/sku_name.txt' , DATA_SOURCE = azure_storage_test , FILE_FORMAT = CSV ) ; |
補足：PolyBaseを使ったエクスポート(データのアンロード)の方法 (2015.12.22 Update)
PolyBaseはインポートだけでなく、SQLの抽出結果をAzure Storageにエクスポートする機能もあります。インポートで作成したSQL DWの設定情報を流用できるため、非常に簡単にエクスポートできます。
エクスポートは、インポートと同様に「CREATE EXTERNAL TABLE」で行えます。「WITH」句にエクスポート先と出力形式、「AS」句に抽出(select)命令を記載します。「WITH」句の「DATA_SOURCE」と「FILE_FORMAT」はインポートと同様の設定情報です。「WITH」句の「LOCATION」は、Azure Storageの出力先フォルダになり、「CREATE EXTERNAL TABLE」を実行するとAzure Storage内にフォルダが作成され、そこに分割されたエクスポートファイルが出力されます。
また、エクスポートをやり直す場合は、「DROP EXTERNAL TABLE」を実行して、外部テーブルを削除してから再作成する必要があります。（外部テーブルを削除してもAzure Storage内のエクスポートファイルは消えません）
|
1 2 3 4 5 6 7 8 9 10 |
CREATE EXTERNAL TABLE ext_pos_data WITH ( LOCATION = '/pos_export' , DATA_SOURCE = azure_storage_test , FILE_FORMAT = CSV ) AS select * from t_pos_data ; |
PolyBaseを使ったインポート処理速度はDBのスケール影響する
作成した外部テーブルを介して、インポートファイルをインポート先テーブルにインポート(追加登録)することができます。上記の外部テーブルの使った場合「insert into m_sku_name select * from ext_sku_name」のようなSQL命令でインポートできます。
100万行程度のインポートファイルの場合、1分ほどでインポート処理が完了しますが、1000万行を超えるインポート処理の場合、SQL DWのスケール(DWU)を上げるのが有効的です。こちらで確認した結果、約4600万行×90項目数のインポート処理に100DWUは16分27秒掛かっていたのに対して、500DWUでは4分13秒と約4分の1の処理時間になりました。
PolyBaseはビッグデータに対応した高機能なインポート機能
SQL DWにインポートを行う場合、従来のBCPコマンドを使ったやり方では、インポートファイルのレイアウトをインポート先テーブルに合わせる必要がありました。しかし、PolyBaseを使うことで外部テーブルから普通のテーブルのように参照できるため、インポート先テーブルに合わせてインポートファイルのレイアウトを合わせる必要はありません。また、BCPコマンドに比べ、PolyBaseのインポート処理は非常に高速なため、数十億行のインポートファイルでも数時間でインポートすることが可能です。
PolyBaseは、BCPコマンドに比べ設定する情報は多いですが、これから様々なファイル形式、ボリュームのデータを扱っていく中で必須のスキルになると思います。
【連載：Microsoft Azure SQL Data Warehouse を使ってみた】
- DB作成からデータインポートまで
- Azure SQL DWのパフォーマンス測定｜13億行のデータで検索と集計処理時間を計測
- すばやい一時停止とパフォーマンス変更でコストを削減できる
- PolyBaseを使ったAzure SQL DWへの高速インポート ～Azure Storageにデータをアップロードする～
- PolyBaseを使ったAzure SQL DWへの高速インポート ～図解で分かりやすく説明～ (本稿)
- Azure SQL DWパフォーマンスチューニング(リソースクラス編)
- Azure SQL DWパフォーマンスチューニング(ハッシュ分散編)
f t p h l