PolyBaseを使ったAzure SQL DWへの高速インポート ～図解で分かりやすく説明～：Microsoft Azure SQL Data Warehouse を使ってみた(5)

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度登録すると、それ以降は登録の必要がありません。

③データベーススコープの資格情報の作成

データベーススコープの資格情報として、①でアップロードしたAzure Storageのアクセスキーを登録します。ここで入力したデータベーススコープ名は、次のステップで使用します。

④外部データソース情報の作成

外部データソース情報として、①でアップロードしたAzure Storageの接続情報を登録します。TYPEはAzure BLOB Storageに接続するため「HADOOP」、LOCATIONには「Azure Storageのプライマリ BLOB サービス エンドポイント」、CREDENTIALには③で作成したデータベーススコープ名を設定してください。ここで入力した外部データソース名は、⑥のステップで使用します。

⑤外部ファイル形式情報の作成

外部ファイル形式情報として、データ項目の区切り文字などを登録します。ここでは、区切り文字以外に日付フォーマット、圧縮形式などの細かいファイル形式の指定を行うことができます。単純なカンマ区切りのファイル形式の場合、下記の内容で十分です。(詳しくはCREATE EXTERNAL FILE FORMATの説明参照) ここで入力した外部ファイル形式名は、次のステップで使用します。

⑥外部テーブルの作成

外部テーブルでは出力形式とインポートファイルを読み込むための情報を指定します。CREATE EXTERNAL TABLEの「WITH」の上部には、テーブル作成時と同様にデータ項目名とデータ型の指定を行います。そして、LOCATIONには①でアップロードしたAzure Storageのファイルパス、DATA_SOURCEには④の外部データソース名、FILE_FORMATには⑤の外部ファイル名を設定してください。あとは「select * from ext_sku_name」とSQL命令で問い合わせるだけでインポートファイルの情報を参照することができます。

補足：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内のエクスポートファイルは消えません）

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パフォーマンスチューニング(ハッシュ分散編)