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改善などの実務経験豊富なメンバーで執筆・運営・管理をしております。

関連記事


【無料】セミナー動画視聴

2021年7月13日オンラインECセミナー動画【無料視聴】

デジタルシフトが求められる今、D2Cへの関心が高まり、ダイレクトにお客様とつながる D2C ビジネスは注目を集めています。本セミナーでは、顧客視点で取り組むD2CとEC事業を成功させるために不可欠なプラットフォーム『Shopify』の歴史・メリットなど基本のすべてをお伝えさせていただきます。

2021年1月27日オンラインECセミナー動画【無料視聴】

トランスコスモスのEC支援に対する豊富な実績をもとに今後の変革に必要と考えられる「CRM、データドリブン、生産性向上」をテーマとした当社コンサルティングサービスについて、講義形式のセミナー開催

2020年11月30日オンラインECセミナー動画【無料視聴】

【Shopify構築・運用セミナー】拡大するEC市場に不可欠なバックエンド業務の最新トレンド。EC事業を成功させるために『効率化に向けたバックエンド業務の最新トレンド』と題して、Shopify構築・運用に連携できるサービスをご紹介。


EC関連サービス

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

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

トランスコスモスは日本に6社しかない最上位の「Shopifyプラスパートナー」企業です

Shopify人気記事ランキング

 Tag(タグ)

 記事検索

 カテゴリー

 【無料】募集中セミナー

全8件中 1〜 4件を表示

 【無料】資料ダウンロード

Shopify操作マニュアルのダウンロード一覧

ショッピファイの管理画面の使い方を図解で開設した操作マニュアル資料をPDFにてダウンロード頂けます。

ECサービス紹介資料無料ダウンロード一覧

トランスコスモスのEC関連のサービス資料の一覧ページです。PDFにて概要のわかる資料を「無料」でダウンロードして頂けます。

セミナー発表資料の無料ダウンロード一覧

トランスコスモスのEC関連のWEBセミナーで使用した資料一覧ページです。PDFにてセミナーの内容がわかる資料を「無料」でダウンロードして頂けます。

全3件中 1〜 3件を表示

Shopify認定パートナー企業

トランスコスモスは日本に7社しかない最上位の「Shopifyプラスパートナー」です。
Shopifyでのサイト制作ならお任せください