catch-img

【サクッと学ぶ】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の地域を抽出したもの、
この2つが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();

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

ECXサイト編集部
ECXサイト編集部

トランスコスモスのECX本部が運営するサービスサイト「ECX」編集部です。コーディング、WEBデザイン、SEM、UI改善などの実務経験豊富なメンバーで執筆・運営・管理をしております。

関連記事

お気軽に
お問い合わせください

Shopify (ショッピファイ) ECストア構築・運用代行