U-SQLで動的なカラムごとにファイル出力する方法

# U-SQLで動的なカラムごとにファイル出力する方法

- Azure Data Lake StoreにJSONを配置
- 動的なカラムごとにまとめてBlobにCSVファイルとして出力

を実現したい

## ゴールまでの流れ

1. Data Factoryの作成
2. サービスプリンシパルの作成
3. Azure Data Lake Storeのフォルダの権限を変更する
4. 実行するU-SQLファイルの配置
5. Data Factoryからサービスへのリンク作成
6. Pipelinesの作成


## Data Factoryの作成

このドキュメントの「Data Factoryを作成する。」のみ実施
[Azure Data Factory UI を使用してデータ ファクトリを作成する | Microsoft Doc](https://docs.microsoft.com/ja-jp/azure/data-factory/quickstart-create-data-factory-portal#create-a-data-factory)

- Blobの作成が一番目の手順となっているが、必要なければやらなくてよい
- 「リンクされたサービスを作成する」以降は後で実施

## サービスプリンシパルの作成

Azure Data FactoryからAzure Data Analyticsの実行、Azure Data Lake Storeへのアクセスにはサービスプリンシパルの作成が必要

### サービスプリンシパルとは？

まあ「アプリを実行するためのアカウント」という認識で間違いなさそう
[3分でわかるAzureでのService Principal - ShareSlide](https://www.slideshare.net/ToruMakabe/3azure-service-principal)
[サービスプリンシパル(Service Principal)が作れない！ - ネスケラボ](https://blog.nextscape.net/archives/Date/2016/07/azure-ad-serviceprincipal)


### サービスプリンシパルの作成

このドキュメント通りやればできる
[リソースにアクセスできる Azure Active Directory アプリケーションとサービス プリンシパルをポータルで作成する | Microsoft Doc](https://docs.microsoft.com/ja-jp/azure/azure-resource-manager/resource-group-create-service-principal-portal)

- 簡単に言うと、Azure Active Directoryに新規アプリを登録し、サービスプリンシパルを作成
- 1つ作成すればOK
- アプリケーションIDがData FactoryではService principal IDに該当する
- 「アプリケーションにロールを割り当てる」をやらないとAzure Data Analyticsへのリンクが作成できないっぽい
    - これはサブスクリプションの所有者しかできない

## Azure Data Lake Storeのフォルダの権限を変更する

作成したサービスプリンシパルがアクセルできるようにする
[Data Lake Store での Azure Active Directory を使用したサービス間認証 | Microsoft Doc](https://docs.microsoft.com/ja-jp/azure/data-lake-store/data-lake-store-service-to-service-authenticate-using-active-directory)

- 下記キャプチャのように一番上の階層の**[Access]**に作成したサービスプリンシパルを追加する必要があるため注意

![image.png](https://qiita-image-store.s3.amazonaws.com/0/123610/e8a0915d-855c-d719-2156-4ab23f0a8c34.png)

## 実行するU-SQLファイルの配置

ADLSにU-SQLファイルを配置し、アクセス権限を変更する
![image.png](https://qiita-image-store.s3.amazonaws.com/0/123610/c99bed0c-3d8d-1c95-92f5-c6b6b06bad04.png)

赤で囲んだところに追加されればOK
![image.png](https://qiita-image-store.s3.amazonaws.com/0/123610/ae00ef1d-96f9-7702-00bc-de74757ad38a.png)

## Data Factoryからサービスへのリンク作成

このドキュメントの「リンクされたサービスを作成する」を実施（Blobへのリンク作成方法しかのっていない）
[Azure Data Factory UI を使用してデータ ファクトリを作成する | Microsoft Doc](https://docs.microsoft.com/ja-jp/azure/data-factory/quickstart-create-data-factory-portal#create-a-linked-service)

今回、Data Factoryに登録するサービスは

- Azure Data Lake Store
- Azure Data Analytics
- ストレージアカウント（Blob）

となる

### Azure Data Lake Storeへのリンク作成方法

以下のように選択
![image.png](https://qiita-image-store.s3.amazonaws.com/0/123610/133e5d9a-31ce-d40c-fead-263c9ecd18f5.png)

黄色のところを選択（サブスクリプションとData Lake Storeのアカウント名）
![image.png](https://qiita-image-store.s3.amazonaws.com/0/123610/62aa559a-b713-d156-bea8-50f66a1140c7.png)

黄色のところを入力（Azure Active Directoryで登録したアプリIDがService principal ID、キーがService principal key）
![image.png](https://qiita-image-store.s3.amazonaws.com/0/123610/bdc22f78-2b66-e0ec-888b-2555ce2f82c4.png)

Service principal IDは下記のアプリケーションID
![image.png](https://qiita-image-store.s3.amazonaws.com/0/123610/64929c93-bbcb-0fc0-4653-d4e0f91a3162.png)

キーは保存した直後しか確認できないため、わからなくなった場合再度キーを作成する

Test ConnectionでConnection successfulとなればOK
もしならなければ、今までの手順を見直す
![image.png](https://qiita-image-store.s3.amazonaws.com/0/123610/d9d2dc8d-bef3-3bd6-a7e5-3099bda4dfab.png)

### Azure Data Analyticsへのリンク作成方法

以下のように選択
![image.png](https://qiita-image-store.s3.amazonaws.com/0/123610/c8a7750a-3fe3-afac-6d1d-8bce201eb4b2.png)

あとはAzure Data Lake Storeと同様

### 注意

サブスクリプションのアクセス制御 (IAM) が「所有者」でないと、以下のドキュメントの「アプリケーションにロールを割り当てる」ができずAzure Data Analyticsへのリンクが作成できない
[リソースにアクセスできる Azure Active Directory アプリケーションとサービス プリンシパルをポータルで作成する | Microsoft Doc](https://docs.microsoft.com/ja-jp/azure/azure-resource-manager/resource-group-create-service-principal-portal)
また、このロールの割り当てが効くのに時間がかかるみたい（おそらく）なので30分ほど待ってからAzure Data Analyticsへのリンクが作成してみるとよい

## Pipelinesの作成

Pipelinesの作成
![image.png](https://qiita-image-store.s3.amazonaws.com/0/123610/53a113d3-f126-4499-2326-fad12eac4b9c.png)

作成したADLAへのリンクを選択、接続確認
![image.png](https://qiita-image-store.s3.amazonaws.com/0/123610/cbd84ea5-5641-ab78-4762-28e247641e79.png)

作成したADLSへのリンクを選択、接続確認
実行したいU-SQLの選択
![image.png](https://qiita-image-store.s3.amazonaws.com/0/123610/e5efe832-f7fe-5f6a-ea2a-7ab02e1ac77f.png)
ちなみに上のキャプチャの**[Parameters]**の**[New]**でU-SQLに引数を渡せる（今回は使用しない）

後は**[Debug]**で実行できたらOK






# 前段の調査

まず、前提として動的なファイル名をつけてファイル出力することはできないみたい。（2018/04/10 時点）
[Support 'dynamic' output file names in ADLA | Microsoft Azure](https://feedback.azure.com/forums/327234-data-lake/suggestions/10550388-support-dynamic-output-file-names-in-adla)

ループを回したりすることもできない
[How to process one row at a time in USQL #35 | GitHub](https://github.com/Azure/usql/issues/35)

## Azure Data Factoryでパイプラインを使えばできるらしい

### U-SQLのサンプル

[U-SQL Split a CSV file to multiple files based on Distinct values in file | stackoverflow](https://stackoverflow.com/questions/43227421/u-sql-split-a-csv-file-to-multiple-files-based-on-distinct-values-in-file)

- 結局 "script the script" 
- U-SQLでU-SQLを生成して、その生成したU-SQLをPowershellかAzure Data Factoryから呼んであげる必要があるみたい

[U-SQL Output in Azure Data Lake | stackoverflow](https://stackoverflow.com/questions/42636855/u-sql-output-in-azure-data-lake/42676271#42676271)

- U-SQLでU-SQLを生成するわかりやすいサンプルがある
- U-SQLの中の人が回答しているみたいだから安心感あり

### Azure参考

[Azure DataLake 大全 | slideshare](https://www.slideshare.net/dahatake/azure-datalake/39)

- Azureの全貌を把握できるスライド

### Data Factory参考

[Azure Data Factory の概要 | Microsoft Doc](https://docs.microsoft.com/ja-jp/azure/data-factory/v1/data-factory-introduction)
[ビッグ データ - Azure Data Lake と Azure Data Factory を使用したビッグ データのパイプラインの作成 | MSDN マガジン January 2016](https://msdn.microsoft.com/ja-jp/magazine/mt614253.aspx)