SQL

【サクッと学ぶ】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(ショッピファイ)ストア構築・制作、
運用代行について問い合わせ

Shopifyアプリ解説・紹介記事

ShopifyのECサイト制作ならトランスコスモス

トランスコスモスは、Shopifyのエンタープライズ版「Shopify Plus」の優れた成果を持つ提携企業として認定された、国内に7社しかいない(2021年9月時点)最上位の公式パートナーです。

Shopifyを使ったECサイト制作から、調査分析・戦略立案、WEB広告(SEM)、ECサイト制作、お客様サポート、受発注、フルフィルメントまで業務設計・運用代行いたします。

トランスコスモスのEC全領域を網羅するサービス

関連記事


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


EC関連サービス


数字で見るトランスコスモスの強み

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

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

トランスコスモスは日本最上位の「Shopifyプラスパートナー」企業です
ロジスティクスサービス

 記事カテゴリー

【週間ランキング】ECブログ人気記事

ロジスティクス関連 新着記事

【初心者必見!】Shopifyマニュアル

 Tag(タグ)一覧

正社員募集(求人)

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

Shopify認定パートナー企業

shopify plus partner
トランスコスモスは最上位の「Shopify Plus partner(ショッピファイプラスパートナー)」です。
Shopifyでのサイト制作ならお任せください!
ECソリューションをお届けするサービスサイト

トランスコスモス株式会社
CX事業統括
ECX本部

〒150-0011
東京都渋谷区東1-2-20
渋谷ファーストタワー
050-1751-7700(代表)

経済産業省が定める「DX認定事業者」
トランスコスモスは経済産業省が定める「DX認定事業者」
トランスプラス
トランスコスモスの全社的な情報を発信するオウンドメディア
cotra
コンタクト/コールセンターに携わる方への情報サイト
shopify構築・制作・運用