U-SQL 入門⑤ ~ データ型 ~
U-SQL のデータ型は通常の SQL とは異なり、C# のデータ型を使用します。間違えないように気を付けたいところ。
C# のデータ型は以下の URL を参照。
型 (C# リファレンス) | Microsoft Docs
下記のようにデータ型指定の隣に ? を付けると null 値を許容するという指定になります。
@searchlog =
EXTRACT UserId int,
Start DateTime,
Region string,
Query string,
Duration int?,
Urls string,
ClickedUrls string
FROM "/Samples/Data/SearchLog.tsv"
USING Extractors.Tsv();
Numeric 型
| byte | 符号なし 8 ビット整数 |
| sbyte | 符号付き 8 ビット整数 |
| int | 符号付き 32 ビット整数 |
| uint | 符号なし 32 ビット整数 |
| long | 符号付き 64 ビット整数 |
| ulong | 符号なし 64 ビット整数 |
| float | 32 ビット浮動小数 |
| double | 64 ビット浮動小数 |
| decimal | 128 ビット数値データ |
| short | 符号付き 16 ビット整数 |
| ushort | 符号なし 16 ビット整数 |
その他
| DateTime | 日時を表す |
| bool | ブール値 (true / false) を格納 |
| Guid | グローバル一意識別子 |
| byte[] * | バイト配列 |
* string 型と byte[] 型は null 許容指定はできない。
この記事はこちらの公式ドキュメントを参考にしたものです。
https://msdn.microsoft.com/en-us/azure/data-lake-analytics/u-sql/built-in-u-sql-types
U-SQL 入門④ ~ FileSets ~
FileSets を使って複数のファイルを EXTRACT 文で読み込むことができます。
基本
以下のように 5 ファイルがある場合、
/input/2018-01-01.log /input/2018-02-01.log /input/2018-03-01.log /input/2018-04-01.log /input/2018-05-01.log
以下の EXTRACT 文で 5 ファイルをすべて読み込むことができます。また、{suffix} にマッチした部分を suffix 列に入れることができます。
@rs = EXTRACT user string, id string, suffix string FROM "/input/{suffix}.log" USING Extractors.Csv();
日付形式の対応
以下のように 5 ファイルがある場合、
/input/2018-01-01.log /input/2018-02-01.log /input/2018-03-01.log /input/2018-04-01.log /input/2018-05-01.log
以下の EXTRACT 文ではファイル名の日付情報を、DateTime 型の値として取得することができます。
@rs = EXTRACT user string, id string, date DateTime FROM "/input/{date:yyyy}-{date:MM}-{date:dd}.log" USING Extractors.Csv();
以下のようなフォルダ構成になっている場合、
/input/2017/01/01/data.txt /input/2017/02/01/data.txt /input/2017/03/01/data.txt /input/2017/04/01/data.txt /input/2017/05/01/data.txt
以下の EXTRACT 文でフォルダ構造から日付情報を、DateTime 型の値として取得することができます。
@rs = EXTRACT user string, id string, date DateTime FROM "/input/{date:yyyy}/{date:MM}/{date:dd}/data.txt" USING Extractors.Csv();
このデータに SELECT を実行する際に以下のように WHERE 句で DateTime 型の範囲でフィルターをかけることができます。
@rs = SELECT * FROM @rs WHERE date >= System.DateTime.Parse("2018/1/1") AND date < System.DateTime.Parse("2018/2/28");
フィルターの効能
以下のようなファイルを読み込むとき、
/input/ja_data-01.log /input/ja_data-02.log /input/en_data-01.log /input/en_data-02.log /input/cn_data-01.log
以下の EXTRACT 文でファイル名の先頭にある文字列を {Market} に格納できます。
@rs = EXTRACT user string, id string, Market string FROM "/input/{Market}_{*}" USING Extractors.Csv();
SELECT 文を実行する際に以下のように Market 列でフィルターをかけると、/input/ja_data-01.log、/input/ja_data-02.log の 2 つのファイルのみが読み取られ、その他のファイルにはアクセスされないため、I/O 効率が良くなります。
@us = SELECT * FROM @rs WHERE Market == “ja" ;
U-SQL 入門③ ~ ファイルのアウトプット ~
U-SQL でのファイルのアウトプットは OUTPUT 文のTO 句 で PATH を指定して、 USING 句で Outputter を指定します。
OUTPUT @rows TO “/data.csv” USING Outputters.Csv();
Outputtersで書き込むファイルのフォーマットを指定します。
// ヘッダ付きの CSV 形式で出力する OUTPUT @rows TO “/data.csv” USING Outputters.Csv(outputHeader: true); // ダブルクォート無しで TSV 形式で出力する OUTPUT @rows TO “/data.tsv” USING Outputters.Tsv(quoting: false); // "|" で区切られたファイル形式で出力する OUTPUT @rows TO “/data.txt” USING Outputters.Text(delimiter: '|');
Outputter 共通のパラメータ
| パラメータ | デフォルト値 | 指定できる値 | 説明 |
|---|---|---|---|
| dateTimeFormat | "o" | datetime側のカラムがあった場合、このパラメータで出力フォーマットを指定できる。指定できるフォーマットはこちらに書いてある。 https://msdn.microsoft.com/library/az4se3k1(v=vs.110).aspx |
|
| encoding | Encoding.UTF8 | Encoding.[ASCII] Encoding.BigEndianUnicode Encoding.Unicode Encoding.UTF7 Encoding.UTF8 Encoding.UTF32 |
ファイルのエンコード指定 |
| escapeCharacter | null | エスケープ用の文字を指定 | |
| nullEscape | null | null 値と解釈する文字を指定 | |
| quoting | true | true false |
アウトプットするデータのフィールドを " (ダブルクォート) でくくるかどうか |
| rowDelimiter | \r\n | 行デリミタを指定。デフォルトではCRLF | |
| outputHeader | false | true |
ヘッダにフィールド名を付けるかどうか |
U-SQL 入門② ~ ファイルのインプット ~
※ストレージは ADLA のデータソースとして登録しておく必要があります。
ファイルインプットの例
// デフォルトの ADLS の PATH を指定する場合 @rows = EXTRACT name string, id int FROM “/…/data.csv” USING Extractors.Csv(); // ADLS を指定する絶対パスの場合 @rows = EXTRACT name string, id int FROM “adl://…/data.csv” USING Extractors.Csv(); // Blob ストレージを指定する場合 @rows = EXTRACT name string, id int FROM “wasb://…/data.csv” USING Extractors.Csv();
USING 句で Extractor を指定して、読み込むファイルのフォーマットを指定します。
ビルトインの Extractor は以下の 3 つがあります。
- Extractors.Text() : ファイルを指定したデリミタで区切って読み取る
- Extractors.Csv() : CSV ファイルを読み取る
- Extractors.Tsv() : TSV ファイルを読み取る
// "|" で区切られたテキストファイルを読み込む。最初の 1 行目はスキップする @rows = EXTRACT name string, id int, FROM "/file.text" USING Extractors.text(delimiter: "|", skipFirstNRows: 1) // CSV ファイルを読み込む。カラム数が合わない場合やデータ型が合わない場合はその行をスキップする @rows = EXTRACT name string, id int, FROM "/file.csv" USING Extractors.csv(silent: true) // TSV ファイルを読み込む。ASCII エンコードを指定 @rows = EXTRACT name string, id int, FROM "/file.tsv" USING Extractors.csv(encoding: Encoding.[ASCII])
Extractor の共通パラメータ
| パラメータ | デフォルト値 | 指定できる値 | 説明 |
|---|---|---|---|
| encoding | Engoding.UTF8 | Encoding.[ASCII] Encoding.BigEndianUnicode Encoding.Unicode Encoding.UTF7Encoding.UTF8 Encoding.UTF32 |
ファイルのエンコード指定 |
| escapeCharacter | null | エスケープ用の文字を指定 | |
| nullExcape | null | null 値と解釈する文字を指定 | |
| quoting | true | true false |
カラムのフィールドの " (ダブルクォート) を考慮にいれるかどうか現在は " (ダブルクォート) 以外には対応していない |
| rowDelimiter | "\r\n" "\r" "\n" |
行のセパレータを何にするか。デフォルトでは改行コードが指定される | |
| silent | false | true false |
指定したカラム数と違うカラム数の行が見つかった場合や指定したものと異なるデータ型があった場合に、スキップして読み込むか、それともエラーを吐いて処理を止めるか |
| skipFirstNRows | 0 | 読み込むときに先頭から指定した行数だけスキップする |
U-SQL 入門① ~ U-SQL の基本形 ~
Azure Data Lake Analytics の U-SQL というクエリ言語についてメモ用に基本的な事をまとめていきます。
Azure Data Lake そのものについてや構築方法等はこちらのブログ (Data Platform Tech Sales Team Blog | Azure Data Lake)を見るとわかりやすいと思います。
U-SQL とは？
U-SQL の基本形
@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();
それぞれの個所で以下のような動作をする。
EXRACT の個所
- /Samples/Data/SearchLog.tsv ファイルを読み込む
- Extractors.Tsv() でタブで区切る
- UserId, Start, ... と列名を付与し、データ型も定義
- @searchlog という変数にセット
SELECT の個所
- @searchlog に対して SELECT を実行
- Region 列の値が en-gb のデータにフィルタ
- Start, Region, Duration 列のみを選択
- @rs1 変数にセット
OUTPUT の個所
- @rs1 にセットされた SELECT の結果を /output/SearchLog-transform-rowsets.csv というファイルに書き出す
- Outputters.Csv() によって CSV フォーマットに変換される
Azure Data Lake Analytics
