SQL Data Warehouse のセットアップは比較的簡単ですが、データロードをするには少々段取りが必要です。
普通のSQL ServerのようにBpcコマンドも使えますが、大規模ファイルのロードには向いていないようで、大規模、並列処理を前提としてPolyBaseを利用すべきとのことなので、使ってみます。
なお、SQL Data Warehouseの構築・簡単な利用はこちらをどうぞ。
PolyBaseとは
PolyBaseという目に見える具体的なツールがあるわけではなく、かつ、以前からSQL Server Parallel Database Warehouse等で利用されてきた構造化データ（RDB）と非構造化データ（CSV等）を関連付けるための一連の技術です（実体としてはWindowsサービスとしてSQL Server PolyBase Data Movement Service, SQL Server PolyBase Engineのようです）。
ここのところのビックデータ需要により、SQL Server 2016やData Warehouseで再登板という感じ。
Hdoopからいろいろ取り入れているようですが、Hadoop屋から見ると、まんまHiveのT-SQL版という感じ。
つまり、CREATE EXTERNAL TABLEで外部テーブルを定義して、T-SQLでクエリを投げられる技術。内部的にはHDFSも利用しているようですが、Hive(Tez)的なものか、ImpalaやPrestoのようなリアルタイム系のアーキテクチャかは現状不明。
この様にPolyBaseは、Data Warehouse用のデータロードツールというわけではなく、Data Warehouseで利用されるであろう大規模なデータは並列処理に対応したPolyBaseを利用した方がいいですよ！ってことらしい。
Impalaにデータロードする際、まずHiveスキーマを定義するのと同じ感覚。
Hiveの場合、
# スキーマ
CREATE EXTERNAL TABLE sales(
shop string,
sales int
)
# ファイル形式
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
# データの場所
STORED AS TEXTFILE LOCATION 'wasb://testcontainer@linuxhd.blob.core.windows.net/data/';
などとしますが、PolyBaseでも、同じように以下の5つの情報を定義します。
- １）データベースマスターキー（暗号化の際に使うKey：最初に1度だけ定義）
- ２）データベーススコープ（BLOBへのアクセス情報等を定義：最初に1度だけ定義）
- ３）外部データソース情報（場所の定義 上記だとLOCATION）
- ４）外部データソースのファイル形式情報（デリミタ等の定義 上記だとROW FORMAT）
- ５）外部テーブル（上記例だとCREATE TABLE）
１）はおまじない的なもの。
２）はBLOBへアクセスするのだから当然必要（HDInsightでは透過的）。
３）、４）５）はHiveではお馴染みと思えば、複雑ではありません。
外部（非構造化）データの場所
サンプルの多くはBLOBにデータがあることを前提としています。HDInsight等と連携することを前提とすると、ここでもデータはBLOBにあるものとして進めます。
別途Azure Storageサービスのアカウントを取得し、データをアップしておきます。私はMac環境なのでCyberDuckでアップロードしました。
Windows環境だとAzCopy使うのでそのメモはこちら。
データは、data.csvという名称で、dataコンテナ内に保存しました。
こんなデータです。売上(sales)データというイメージ。
1,新宿,300
2,渋谷,500
3,新橋,200
4,品川,500
5,大崎,120
なお、PolyBaseではアクセスできるStorageサービスのタイプに一部制限があるようです。
私は、一番安いローカル冗長を選びました。
外部テーブルの作成
設定は、SQL Data Warehouseのデータベースにおいてクエリを実行することで行ないます。
各種設定はSQL Data Warehouseのリソースとして管理されます。
基本的な手順はMSさんのサイトを参考に進めました。
データベースキー
暗号化等で利用するようですが、意識することはありません。
CREATE MASTER KEY;
データベーススコープ
利用するAzure Storageのアカウント、アクセスキーを設定します。
CREATE DATABASE SCOPED
CREDENTIAL ongo WITH IDENTITY = 'Azureストレージアカウント名',
Secret = 'Azureストレージのアクセスキー';
外部データソース情報
データのタイプ、場所、認証情報を設定します。認証情報は、上記でCREATEしたもの（ここではongo)になります。
CREATE EXTERNAL DATA SOURCE test_data
WITH
(
TYPE = HADOOP,
LOCATION ='wasbs://data@ongo.blob.core.windows.net',
CREDENTIAL = ongo
);
外部データソースのファイル形式情報
多くの場合元データはCSVでしょう。下記のように設定します。
CREATE EXTERNAL FILE FORMAT CSV
WITH
(
FORMAT_TYPE = DELIMITEDTEXT,
FORMAT_OPTIONS
(
FIELD_TERMINATOR =','
)
);
外部テーブルの作成
ここまで来る外部テーブルを設定する準備ができましたので、外部テーブルをCREATEします。
WITHオプションで、ここまでに設定してきた情報を指定します。
CREATE EXTERNAL TABLE ext_sales
(
id int,
shop nvarchar(32),
sales int
)
WITH
(
LOCATION = '/data.csv',
DATA_SOURCE = test_data,
FILE_FORMAT = CSV
);
既にテーブルがある場合はINSERT INTO SELECTとかで何とかなる。
複数のファイルを指定する場合はどうすればいいのでしょう？ LOCATION = ''や'/'ではダメでいた。。。誰か教えて。もしかすると、コンテナ直下はだめで、（仮想）ディレクトリ作ればOKなのかも（試してませんが）。
[追記]
上記、予想通り、コンテナ直下はダメですが、（仮想）ディレクトリは指定できるようです。
[追記]
EXTERNALテーブルで読込中に、
Query aborted-- the maximum reject threshold (0 rows) was reached while reading from an external source:
的なエラーがでるときがあります。これは読取りエラー行が閾値の最大値を超えた！的なエラーで、デフォルトは0のようなので、1行のエラーも認めません。この閾値は、REJECT_VALUE値で変えられるようです。CREATE EXTERNAL TABLE文のWITHオプションで、
WITH
(
LOCATION = '/data.csv',
DATA_SOURCE = test_data,
FILE_FORMAT = CSV,
REJECT_TYPE = value,
REJECT_VALUE = 100
);
のような感じで指定することで指定できるようです。なお、REJECT_TYPEはパーセントでも指定できるようです。
様子を見てみる
ここまで設定したものをGUI（VS）で見てみましょう。
リソースの確認
External Resourceとして、データソース、ファイルフォーマット等が生成されています。
また、作成した外部テーブルはExternal TablesとしてGUIで確認することもできます。
外部テーブルへのクエリ
外部テーブルには普通にクエリを投げることができます。
select * from ext_sales;
とすると、結果が戻ります。
ちゃんとひも付けができているようなので、この外部テーブルを経由してData Warehouseへデータをインポートします。
SQL Data Warehouseへのデータ取込
外部テーブルへのクエリ結果を元にテーブルを作成します。
普通のSQL Serverのテーブルから別のテーブルにインポートする場合と概念は変わりません。
ext_salesテーブルから全てをSELECTしてsalesテーブルを生成しています。
CREATE TABLE sales
WITH
(
CLUSTERED COLUMNSTORE INDEX,
DISTRIBUTION = HASH(id)
)
AS SELECT * FROM ext_sales;
クエリを最適化するための？統計情報を作成します。後々は自動化されるのかもしれません。
既存のテーブルの場合は、INSERT INTO TABLE SELECT * FROM EXT_TABLE;などとする。
create statistics id on sales(id);
create statistics shop on sales(shop);
create statistics sales on sales(sales);
データのエクスポート
MSのサンプルに添ってファイルをエクスポートしてみます。エクスポートも外部テーブル経由で行うといいみたいです。
CREATE EXTERNAL TABLE export_sales WITH
(
LOCATION='/export/',
DATA_SOURCE=test_data,
FILE_FORMAT=CSV
)
AS
SELECT * FROM sales where sales > 200;
が、上記の記述では、下記のように、結果のレコードが1行ずつ、別ファイルとして出力され、さらには、空行のファイルもたくさん出力されます。
想像ですが、その個数は常に60個なので、これはディストリビューション（ディスク分散）数と一致します。
なので、各ディストリビューション毎に分散処理した結果が、「結果なし」も含めて出力されているのでは？と推測されます。
有用な結果だけを１つのファイルに普通に出力する方法を調べてみる必要がありますね。
PolyBaseのコマンドでできるのか、出力後マージしなければならないのか、、、さて。
マージは、とりあえずファイルをダウンロードした後に、PowerShellなどで下記のようにしてみる。
cat .\export\* > .\out\list.txt -encoding utf8
BLOB上でやる方法ないのかなと。
その他
文字コード
PolyBaseはUTF-8に対応しており、その他の文字コードのファイルはUTF-8に変換する必要があります。
あと、照合順序が日本語系のものがサポートされておらず、日本語が検索できません！！！
照合順序
あと、照合順序が日本語系のものがサポートされておらず、SQL_Latin1_General_CP1_CI_ASのみのようです。
日本語を取り扱うときはN''オプション?を忘れずに。
select * from sales where shop like N'新%';
おまけ:PowerBIでアクセスしてみる
PowerBIでSQL Data Warehouseにアクセスしてみます。
どうやらPowerBI Proじゃないとダメみたいですが、60日試用できますので、やってみます。host名やデータベース名を入力するだけで簡単にアクセス可能です。
普通のテーブルであるsaleに加え、EXTERNAL TABLEも普通に見えています。当たり前といえば、当たり前ですがすごい。
見たいデータをテーブル化しておけば、SQL Data Warehouse内のデータを簡単に可視化できますね。
当然ですが、ODBC経由でExcelからもアクセスできます（試してませんが）。詳しくはこちらをどうぞ。