トランスコスモス 技術研究所

業務の効率化・自動化、コストカットなどの課題を技術で解決。 AI、ML、ビックデータ、RPAの設計・活用・運用はご相談ください。 APIサービス、ウェブサービスの構築とサービス提供もいたしております。

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

Microsoft Azure Data Lakeサービスで利用するU-SQLの基本編。U-SQL X Data Lakeを使い、ビックデータ分析、機械学習、レポートを作成してみましょう。基本編からアドバンス編までU-SQLの活用方法の紹介+解説です。

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

03/07/2019

こんにちは、所長の下田です。
今日は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();

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


トランスコスモス技術研究所では一緒に様々なウェブサービスを開発するメンバーを募集しています。詳しくはこちらの求人ページにてご確認ください。



シェア シェア
SHO SHIMODA

お問合せ