Cortana Intelligence Suiteを構築（第4回）- ビッグデータをAzure Data Lake Analyticsでクエリ処理してみる

#背景
従来のデータ分析の世界では、一般的に、データウェアハウス等で加工（スキーマを定義）した状態のデータを利用し、キューブなどを構築するといった流れでデータ分析をしてきました。そして近年、ビッグデータを取り扱うようになり、必ず指定されたスキーマでデータウェアハウスに蓄積するアプローチではなく、生データから都度好きなデータを抽出するといったアプローチの方法も出てきました。Microsoft Azureでは、この後者のアプローチをAzure Data Lake、Azure HDInsightといったサービスで提供しています。
今回は、Azure Data Lakeの使い方と、独自言語のU-SQLを活用し、[「Cortana Intelligence Suiteを構築（第3回）」](URL http://qiita.com/taiki_yoshida/items/58cd5af970e24f2453de)までで構築し、データを蓄積してきた、Azure BLOBストレージのデータを分析してみます。

#事前に準備するもの
* Azure サブスクリプション
* Visual Studio 2015以降（どのエディションも利用可能）
    * 事前にAzure SDKが必要です（https://www.visualstudio.com/ja-jp/features/azure-tools-vs.aspx）
    * 事前にAzure Data Lake Tools for Visual Studioが必要です（https://www.microsoft.com/en-us/download/details.aspx?id=49504)

#設定手順

##Azure Data Lake Analyticsの準備
Azure Data Lake Analyticsを利用するために、まずはAzureポータルからAzure Data Lake Analyticsをデプロイする必要があります。

1. 「新規」>「Intelligence + analytics」>「Data Lake Analytics」の順にクリックします。
![image](https://qiita-image-store.s3.amazonaws.com/0/136660/44a7e0e4-0c04-1518-f6df-a2d59d419f6b.png) 

2. 各項目を記入していきます。記入したら、「作成」をクリックします。
現在、場所は米国東部２のリージョンのみ選択できます。
価格レベルは従量課金制なので、クエリを実行した分のみ課金される仕組みとなっています。 
![image](https://qiita-image-store.s3.amazonaws.com/0/136660/6e85d0bf-9c8d-1f47-174f-558c3c4f1da1.png)

3. デプロイが開始されます。１～３分ほどで完了します。
デプロイが完了すると、以下のように表示されます。
![image](https://qiita-image-store.s3.amazonaws.com/0/136660/e9db6e02-2243-c138-4520-63d88e9c9ca6.png)

4. 「その他サービス」>「データ + 分析」>「Data Lake Analytics」の順にクリックします。
![image](https://qiita-image-store.s3.amazonaws.com/0/136660/389e1440-a560-4669-9dfe-63cace3550f4.png)

5. (2)で記入した名前のData Lake Analyticsをクリックします。
![image](https://qiita-image-store.s3.amazonaws.com/0/136660/f949c477-3f9b-c5ed-b0f6-e0163ccc130a.png)

6. これで、Azure Data Lake Analyticsの準備が整いました。
![image](https://qiita-image-store.s3.amazonaws.com/0/136660/184019c9-e76a-5a8a-cf1b-6aae89ff0875.png)

##分析するための下準備
Data Lake Analyticsを利用するためには、U-SQLという新しい言語でクエリを作成します。
ざっくり言うと、普通のSQL言語のようでC#も含められてしまう、パワフルなクエリ言語です。ここではあまり詳しく触れませんが、必要最低限のところまでご紹介します。
今回は、JSON形式の生ファイルを読み込んで、それをクエリで集約したものをSQL Databaseへ書き込んでしまうということを実施してみようと思います。ですが、Azureポータルの画面でジョブを作成する画面からは、直接JSON形式のファイルは読み込めないんです。残念。
ということで、JSONを扱うためのアセンブリをVisual Studioから登録する流れとなります。

###アセンブリの登録
JSONを取り扱うには、事前にNewtosoft.Json.dll等のアセンブリーをAzure Data Lake上へ登録しておく必要があります。通常のC#プロジェクトなどとは違い、NuGetは利用できません。そのため、Newtonsoft.Json.dllをビルドして、ビルドしたものをAzure Data Lakeへアップロードします。

1. Githubから[USQLのレポジトリ](URL https://github.com/Azure/usql)をダウンロードしておきましょう。ここには、JSONから読み込むためのサンプルコードなどが用意されており、非常に便利です。

2. ダウンロードしたフォルダ内の、Examples/DataFormats/Microsoft.Analytics.Samples.Formatsにあるソリューションファイルを開きます。

3. 開いたら、そのままビルドするだけです。
![image](https://qiita-image-store.s3.amazonaws.com/0/136660/dc2f885d-74d1-2096-eba9-8563536ac2e1.png)

4. クラウドエクスプローラーから、Data Lake Analyticsを開き、Databases > master > Assembliesを右クリックし、「アセンブリの登録」をクリックします。
![image](https://qiita-image-store.s3.amazonaws.com/0/136660/df43d9c8-4e28-23ad-96bd-2190688a8900.png)

5. 「Load assembly from path」の「…」をクリックします。
![image](https://qiita-image-store.s3.amazonaws.com/0/136660/3cb544c8-12fa-6953-04af-ce4f07f75dc9.png)

6. 「Local」を選択し、「…」をクリックします。ポップアップで、先ほど（3）でビルドしたdllファイルを選択します。
![image](https://qiita-image-store.s3.amazonaws.com/0/136660/e493b796-a545-a65d-f71c-7038233fea6a.png)

7. 「Input Status」が「Ready」になっていれば、登録準備が完了です。
![image](https://qiita-image-store.s3.amazonaws.com/0/136660/eb02e909-1ef2-326c-0514-737314486824.png)

8. 一時的にファイルストレージを利用するといったメッセージが表示されます。ここは「Yes」を選択しましょう。
![image](https://qiita-image-store.s3.amazonaws.com/0/136660/6d913766-c11a-133d-c94e-56bcb1b63da3.png)

9. ステップ5~8までを、Microsoft.Analytics.Samples.Formats.dllに対しても行います。

###ストレージアカウントの関連付け
Azure Data Lake AnalyticsでAzureストレージアカウントを利用するために、関連付けをしておく必要があります。
それをすることで、アドレス形式で、Azureストレージアカウントをデータ元として指定できるようになります。

1. Data Lake AnalyticsをAzureポータルから開き、「データソース」>「データソースの追加」をクリックします。
![image](https://qiita-image-store.s3.amazonaws.com/0/136660/45121c9d-bb83-2c4f-a78a-63a341395231.png)

2. ストレージの種類を「Azure Storage」にし、メソッドの選択は「アカウントの選択」をクリックし、Azure Storageでは関連付けしたいストレージアカウントを指定します。あとは「追加」をクリックするだけです。
![image](https://qiita-image-store.s3.amazonaws.com/0/136660/6cc0cfa1-9101-d1f0-5705-19588e1f9377.png)

3. 正しく関連付けできたかどうかは、「データエクスプローラー」から、確認することができます。
![image](https://qiita-image-store.s3.amazonaws.com/0/136660/b5c05b40-e593-303b-433f-d800e90da203.png)


##クエリの作成
1. 別のVisual Studioを開き、新しいU-SQLプロジェクトを作成します。
![image](https://qiita-image-store.s3.amazonaws.com/0/136660/bbea284d-63b1-74d2-65b3-c51c6b539728.png)

2. 以下のコードを利用します。 「wasb://」のアドレスは、事前に関連付けしたAzureストレージアカウントのアドレスです。{*}と入力することで、フォルダ内/ファイル名すべてを選択したことを意味します。

 ```sql
CREATE ASSEMBLY IF NOT EXISTS [Newtonsoft.Json] FROM "assemblies/Newtonsoft.Json.dll";
CREATE ASSEMBLY IF NOT EXISTS [Microsoft.Analytics.Samples.Formats] FROM "assemblies/Microsoft.Analytics.Samples.Formats.dll";

REFERENCE ASSEMBLY [Newtonsoft.Json];
REFERENCE ASSEMBLY [Microsoft.Analytics.Samples.Formats]; 

//USE DATABASE [traindetail];

@json = 
    EXTRACT jsonString string FROM @"wasb://tyqiita@tyqiita/logs/{*}/{*}/{*}.json" USING Extractors.Text(delimiter:'\b', quoting:false);

@jsonify = SELECT Microsoft.Analytics.Samples.Formats.Json.JsonFunctions.JsonTuple(jsonString) AS rec FROM @json;

@columnized = SELECT 
            DateTime.Parse(rec["dc:date"]) AS date,
            rec["odpt:railway"] AS railway,
            rec["odpt:trainNumber"] AS trainnumber,
            Double.Parse((rec["odpt:delay"] ?? "0")) AS delay,
            rec["odpt:startingStation"] AS startingstation,
            rec["odpt:terminalStation"] AS terminalstation, 
            rec["odpt:fromStation"] AS fromstation, 
            rec["odpt:toStation"] AS tostation,
            rec["odpt:railDirection"] AS raildirection 
    FROM @jsonify;

OUTPUT @columnized
TO @"wasb://tyqiita@tyqiita/output/result.txt"
USING Outputters.Text();
```

##クエリを実行
Data Lake Analyticsでの実行処理を「ジョブ」と言います。
Visual Studioからジョブを発行することで、ジョブがData Lake Analytics内で実行されるようなイメージです。

1. Visual Studioのソリューションエクスプローラーから、作成したUSQLを右クリックし、「Submit Script」をクリックします。
![image](https://qiita-image-store.s3.amazonaws.com/0/136660/d5efd672-44b8-0522-1871-f64ebe56668d.png)

2. Submit Jobの画面が表示されます。ここで、Job Nameをわかりやすい名前に変更します。複数のData Lake Analyticsを作成している場合は、ジョブを実行するData Lake Analyticsのアカウントを選びます。Parallelismとは、この処理の並列実行数のことです。多ければ多いほど、並列で処理されるため、早くはなりますが、データ量が少ない場合は並列実行のためのオーバーヘッド（準備処理）の方が時間がかかるため、あまり差は感じられません。
![image](https://qiita-image-store.s3.amazonaws.com/0/136660/6437f24e-6b03-171b-2c5a-37292c63ebe7.png)

3. 実行開始されると、以下のように進捗状況と、詳細のステップが視覚的に確認できます。
![image](https://qiita-image-store.s3.amazonaws.com/0/136660/489439c5-22d7-9d53-d22b-66a4c8e43903.png)

4. 画面左側の進捗が「Finalizing」まで進むと、完了したことを意味します。
![image](https://qiita-image-store.s3.amazonaws.com/0/136660/abcd3c46-126b-f19b-017a-ddf22e52a5a7.png)

5. 出力されたデータは、AzureポータルでData Lake Analyticsへアクセスし、「データエクスプローラー」または、「Microsoft Azure Storage Explorer」から確認/ダウンロードが可能です。
![image](https://qiita-image-store.s3.amazonaws.com/0/136660/47bf18cc-b163-747f-ea68-0f3b668f3e63.png)
![image](https://qiita-image-store.s3.amazonaws.com/0/136660/fc2ded6b-4dfd-f47e-f312-477d0d33307f.png)


#リンク集
著者が作成する際に、参考にしたリンクです：
http://sqlblog.com/blogs/jorg_klein/archive/2016/06/27/use-adlcopy-to-generate-u-sql-jobs-that-copy-data-between-azure-blob-storage-and-azure-data-lake-store.aspx
https://azure.microsoft.com/en-us/documentation/articles/data-lake-analytics-manage-use-portal/
https://azure.microsoft.com/en-us/documentation/articles/data-lake-store-copy-data-azure-storage-blob/
http://www.rcloudnetwork.com/the-cloud-in-the-news/creating-big-data-pipelines-using-azure-data-lake-and-azure-data-factory/
https://github.com/Azure/usql
https://blogs.msdn.microsoft.com/robinlester/2016/01/04/an-introduction-to-u-sql-in-azure-data-lake/