Azure DataLake Analyticsでの.NETライブラリの使用方法

AzureDataLake Analyticsの特徴として、UDFとして.NETアセンブリが使えるという点があります。Pythonなどに比べて、データ処理に特化したようなライブラリは少ないのですが、業務に特化していたり、特殊な要件の処理を行わせたい場合に、既存の.NETライブラリを使うと、直接解決できる場合が多いです。

ここでは、.NETコードのU-SQLスクリプト内で使う方法をまとめます。

# C#コードのインライン利用

基本型や基本のアセンブリなどは、U-SQLスクリプトの中に直接書くことができます。U-SQLの基本型があるなら、変数やコレクションを特に変換する必要もありません。

[C# Functions and Operators (U-SQL)](https://msdn.microsoft.com/en-us/azure/data-lake-analytics/u-sql/csharp-functions-and-operators-u-sql)

数はそれほど多くありませんが、データ処理で必要そうな、基本的なオペランドはC#と共通のものが使えると思っていれば良いでしょう。SQLとプログラミング言語の接点で、行き来するときにいつも躓きますが、C#erとしてはこの仕様は非常にうれしいところです。

ちなみに、linqなどもインラインで使えました。

C# Operators
 ● ?: (Conditional)
 ● ?? (Null-Coalescing)
 ● => (Lamda)

String Methods
 ● Compare
 ● CompareOrdinal
 ● CompareTo
 ● Concat
 ● Contains
 ● EndsWith
 ● Equals
 ● Format
 ● GetHashCode
 ● GetTypeCode
 ● IndexOf
 ● Insert
 ● IsNullOrEmpty
 ● IsNullOrWhiteSpace
 ● Join
 ● LastIndexOf
 ● PadLeft
 ● PadRight
 ● Remove
 ● Replace
 ● Split
 ● StartsWith
 ● Substring
 ● ToCharArray
 ● ToLower
 ● ToUpper
 ● Trim

String Properties
 ● Length

Object Methods
 ● GetType

Date & Time
 ● DateTime to String
 ● String to DateTime
 ● Properties
 ● Some Methods
 ● Comparing DateTimes
 ● Some Operators
 ● Date Diff
 ● Add TimeSpan
 ● TimeSpan Plus TimeSpan

Math Methods
 ● Abs
 ● BigMul
 ● Ceiling
 ● Floor
 ● Max
 ● Min
 ● Pow
 ● Round
 ● Sign
 ● Sqrt
 ● Truncate

Random Methods
 ● Next

ちなみに、初期設定でビルトインされているアセンブリは以下です。
[REFERENCE SYSTEM ASSEMBLY (U-SQL)](https://msdn.microsoft.com/en-us/azure/data-lake-analytics/u-sql/reference-system-assembly-u-sql)

Assemblies: 

- mscorlib.dll 
- System.dll 
- System.Core.dll 
- System.Data.dll 

Namespaces: 

- System 
- System.Data 
- System.Text 
- System.Text.RegularExpressions 
- System.Linq 

Assemblies: 

- Microsoft.Analytics.Interfaces.dll 
- Microsoft.Analytics.Types.dll

Namespaces: 

- Microsoft.Analytics.Types 
- Microsoft.Analytics.Types.Sql 
- Microsoft.Analytics.Interfaces 


# コードビハインドによる.NETコードの利用

最も簡単なU-SQLの拡張がコードビハインドによるものでしょう。

[Extending U-SQL Expressions with User-Code](https://msdn.microsoft.com/en-us/azure/data-lake-analytics/u-sql/extending-u-sql-expressions-with-user-code)

以下のクラスをU-SQLから認識させたいとします。関数なども同様に参照可能です。

``` C#
namespace myFirstNamespace
{
    public class myFirstClass
    {
        public static string myFirstFunction(string s)
        {
            return s + s;
        }
    }
};
```

コードビハインドを使う場合は、上記のコードを以下の"Script.usql.cs"にU-SQLスクリプトのコードビハインドに張り付けるだけです。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/146939/281700eb-5d53-9eaf-ee4c-2c5974032452.png)

以下のように、上記関数を直接呼び出すことができます。

``` U-SQL
@result =
    SELECT EmpName,
           myFirstNamespace.myFirstClass.myFirstFunction(EmpName) AS myFirstFunction_CB
    FROM TestReferenceDB.dbo.simpleTable;
```

コードビハインドになっていることで、コンパイルなどのタイミングを同期でき、U-SQLとC#を順次変更しながら試すことができます。デバッグなども比較的辿りやすいエラーメッセージが出ますので、非常に使い勝手が良いです。


# ライブラリ参照による.NETコードの利用

XAMLでのコードビハインドなどでは、モデルはコードビハインドに書くななどが鉄則ですが、U-SQLでも以下の理由でコードビハインドを多用しすぎるのはあまり推薦されないと思います。

- 再利用性
- U-SQL層（準宣言型）とC#層（手続き型）の分離
- ポータビリティ

そこで、U-SQLでは外部アセンブリの参照が可能です。


## Class Library (For U-SQL)の作成

以下のようなプロジェクトテンプレートから、U-SQLのクラスライブラリを作成可能です。これによりU-SQLスクリプトから直接呼び出せるライブラリを開発可能になります。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/146939/468b36e3-5f22-68c8-8745-b1c545d2a6b7.png)

これは以下のような最小限のプロジェクトを作成しますので、Class1.csに先ほどのコードをコピーしてコンパイルすればOKです。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/146939/a4f915f7-9510-0c87-fe00-8e3aeea00dfa.png)


## Assembly の登録

(.NETerでない人のために、アセンブリとはアセンブリ言語のことではなくて、.NETのコンパイル済みライブラリの事です。Javaでいうjarのようなもの。.dllという拡張性で保存されることが多いため、Cライブラリなどと混同されることもあるようですが、中身は別です。)

上記で作成したアセンブリをU-SQLスクリプトから参照するには以下となります。
このようなU-SQLスクリプトを走らせることで、手元のdllを登録可能です。

``` U-SQL
DROP ASSEMBLY IF EXISTS myFirstAssembly;
CREATE ASSEMBLY myFirstAssembly
FROM @"<your path>\myFirstAssembly\bin\Debug\myFirstAssembly.dll";
```

これは、実際にはローカルのフォルダから、ADLA環境へのアセンブリ登録になります。

## Assembly の参照

一度アセンブリが登録されれば、以下のようにライブラリの参照ができます。

``` U-SQL
REFERENCE ASSEMBLY myFirstAssembly;
```

# UDO

U-SQLでは、Rowごと、Rowをまたいだ処理に対して、Operatorという一連のスーパークラスを継承することで、U-SQLの集約処理などを拡張可能です。

UDOと言いますが、以下のリンクにあるようにコードビハインドでも定義できます。
[U-SQL ユーザー定義演算子 (UDO) の開発](https://docs.microsoft.com/ja-jp/azure/data-lake-analytics/data-lake-analytics-u-sql-develop-user-defined-operators)

上記サンプルでは、 IProcessorという、最も単純な演算子を実装しています。これはデータ1行につき１行の出力を行うという単純な処理を行わせるものです。
以下のIProcessorインターフェイスから継承されたメソッドを定義することで、ユーザ定義の行変換処理を実現可能です。

``` C#
public override IRow Process(IRow input, IUpdatableRow output)
```

呼び出しは以下のように、USING句を用いて、Processorのインスタンスを生成した後、PROCESS句により対象となるデータを指定し、PRODUCE句で出力のカラムをU-SQL側から指定します。

``` U-SQL
 @drivers_CountryName =
     PROCESS @drivers
     PRODUCE UserID string,
             Name string,
             Address string,
             City string,
             State string,
             PostalCode string,
             Country string,
             Phone string
     USING new USQL_UDO.CountryName();
```

御覧のように、R dplyrのmutateのように同じデータフレーム内の処理を行うわけではなく、新しいカラム定義のデータフ
レームを１by１で作り出していくという感じです。

その他、1:n や n:1などの行処理を行うことができるため、ほぼどのような集約、変換処理も記述可能になります。

- ユーザー定義エクストラクター
 - 行ごとに抽出
 - カスタム構造化ファイルからのデータ抽出の実装に使用

- ユーザー定義アウトプッター
 - 行ごとに出力
 - カスタム データ型またはカスタム ファイル形式への出力に使用

- ユーザー定義プロセッサ
 - 1 つの行を取得し、1 つの行を生成
 -列の数を減らしたり、既存の列セットから派生した値を含む列を新しく生成したりする際に使用

- ユーザー定義アプライヤー
 - 1 つの行を取得し、0 ～ n 個の行を生成
 - OUTER/CROSS APPLY と併用

- ユーザー定義コンバイナー
 - 行セットを結合 (ユーザー定義の JOIN)
- ユーザー定義レジューサー
 - n 個の行を取得し、1 つの行を生成
 - 行の数を減らす際に使用