【サクッと学ぶ】U-SQLの基本

今日はAzure Data Lakeで利用するU-SQLのご紹介です。
長編なので、小分けにして書きます。

データレイクは、
簡単に言うと様々なデータを、生データのまま蓄積できる場所の事をいいます。

様々な企業でData Lakeサービスは提供されいてますが、
今日はその中から、Microsoft社がAzureサービスの中で提供している、
Azure Data Lakeを利用する際に使うU-SQLを紹介します。

U-SQLとは、データを摘出するSQLと、
摘出したデータに対して処理を命令するために、
C#を組み合わせたものになります。

U-SQLの特徴して、下記があります。

• 読込時にこちらでスキーマを定義して適用できる
• その際にカスタムロジックとUDSを挿入できる
• そこで非構造化データの処理ができる
• 実行する方法が細かく指定できる

見たほうが早いので、まずは下記のサンプルコードを見てみましょう。

@searchlog =
    EXTRACT UserId          int,
                      Start           DateTime,
                      Region          string,
                      Query           string,
                      Duration        int?,
                      Urls            string,
                      ClickedUrls     string
    FROM "/Samples/Data/SearchLog.tsv"
    USING Extractors.Tsv();

OUTPUT @searchlog   
    TO "/output/SearchLog-first-u-sql.csv"
    USING Outputters.Csv();

上記のコードは、SearchLog.tsvというファイルから
各フィールドを指定し、
SearchLog-first-u-sql.csvというファイルに書き出したものになります。

ここで見られるように、各クエリーを変数化することができます。
EXTRACTでは指定したフィールドを Extractors.Tsvを使い読み取り、
OUTPUTではCSVファイルに出力しています。

スカラー変数を使い、メンテナンスしやすいコードも書くことができます。

DECLARE @in  string = "/Samples/Data/SearchLog.tsv";
DECLARE @out string = "/output/SearchLog-scalar-variables.csv";

@searchlog =
    EXTRACT UserId          int,
            　　  Start           DateTime,
            　　  Region          string,
            　　  Query           string,
            　　  Duration        int?,
            　　  Urls            string,
            　　  ClickedUrls     string
    FROM @in
    USING Extractors.Tsv();

OUTPUT @searchlog   
    TO @out
    USING Outputters.Csv();

SELECTを使い、摘出するデータをフィルタリングすることもできます。

@searchlog =
    EXTRACT UserId          int,
            　　  Start           DateTime,
            　　  Region          string,
            　　  Query           string,
            　　  Duration        int?,
            　　  Urls            string,
            　　  ClickedUrls     string
    FROM "/Samples/Data/SearchLog.tsv"
    USING Extractors.Tsv();

@rs1 =
    　　SELECT Start, Region, Duration
    　　FROM @searchlog
　　　WHERE Region == "en-gb";

OUTPUT @rs1   
    　 TO "/output/SearchLog-transform-rowsets.csv"
    　 USING Outputters.Csv();

下記のように、複数回にわけてデータの条件を指定することもできます。

@searchlog =
    EXTRACT UserId          int,
            　　  Start           DateTime,
            　　  Region          string,
            　　  Query           string,
            　　  Duration        int?,
            　　  Urls            string,
            　　  ClickedUrls     string
    FROM "/Samples/Data/SearchLog.tsv"
    USING Extractors.Tsv();

@rs1 =
 　   SELECT Start, Region, Duration
 　   FROM @searchlog
WHERE Region == "en-gb";

@rs1 =
　    SELECT Start, Region, Duration
  　  FROM @rs1
   　 WHERE Start >= DateTime.Parse("2012/02/16") AND Start <= DateTime.Parse("2012/02/17");

OUTPUT @rs1   
    　TO "/output/SearchLog-transform-datetime.csv"
    　USING Outputters.Csv();

集計も、下記のようにビルトインされた関数を使って行うことができます。
下記のコードでは、
地域ごとの合計を期間でまとめて抽出したもの、
トップ5の地域を抽出したもの、
この２つがCSVファイルで出力されます。

DECLARE @outpref string = "/output/Searchlog-aggregation";
DECLARE @out1    string = @outpref+"_agg.csv";
DECLARE @out2    string = @outpref+"_top5agg.csv";

@searchlog =
    EXTRACT UserId          int,
            Start           DateTime,
            Region          string,
            Query           string,
            Duration        int?,
            Urls            string,
            ClickedUrls     string
    FROM "/Samples/Data/SearchLog.tsv"
    USING Extractors.Tsv();

@rs1 =
    SELECT
        Region,
        SUM(Duration) AS TotalDuration
    FROM @searchlog
GROUP BY Region;

@res =
    SELECT *
    FROM @rs1
    ORDER BY TotalDuration DESC
    FETCH 5 ROWS;

OUTPUT @rs1
    TO @out1
    ORDER BY TotalDuration DESC
    USING Outputters.Csv();

OUTPUT @res
    TO @out2
    ORDER BY TotalDuration DESC
    USING Outputters.Csv();

HAVING条件を使い、下記のように条件を満たしたものだけを出力することも可能です。

@searchlog =
    EXTRACT UserId          int,
   　　           Start           DateTime,
   　　           Region          string,
   　　           Query           string,
   　　           Duration        int?,
   　　           Urls            string,
   　　           ClickedUrls     string
　    FROM "/Samples/Data/SearchLog.tsv"
   　 USING Extractors.Tsv();

@res =
    SELECT
    　    Region,
       　 SUM(Duration) AS TotalDuration
    FROM @searchlog
    GROUP BY Region
    HAVING SUM(Duration) > 200;

OUTPUT @res
    TO "/output/Searchlog-having.csv"
    ORDER BY TotalDuration DESC
    USING Outputters.Csv();

ここまでが基本編となります。