SELECT TRANSFORM ステートメントを使用すると、指定された子プロセスを起動し、標準入力を使用して必要な形式でデータを入力できます。その後、子プロセスの標準出力を解析して出力データを取得できます。SELECT TRANSFORM ステートメントを使用すると、ユーザー定義テーブル値関数 (UDTF) を作成する必要なく、他のプログラミング言語のスクリプトを実行できます。
概要
SELECT TRANSFORM と UDTF のパフォーマンスは、シナリオによって異なります。比較テストの結果、少量のデータをクエリする場合、SELECT TRANSFORM は UDTF よりもパフォーマンスが優れています。しかし、大量のデータをクエリする場合、UDTF は SELECT TRANSFORM よりもパフォーマンスが優れています。SELECT TRANSFORM は開発が容易で、アドホッククエリにより適しています。
SELECT TRANSFORM は、さまざまなプログラミング言語と互換性があります。 このステートメントでは、コマンドでスクリプトを記述して、単純な機能を実装できます。 AWK、Python、Perl、Shell などのプログラミング言語がこの操作をサポートしています。 この方法では、スクリプトファイルの記述やリソースのアップロードなどの追加操作が不要になり、開発プロセスが簡素化されます。 複雑な機能を実装するには、スクリプトファイルをアップロードできます。 詳細については、「Python スクリプトの呼び出し例」および「Java スクリプトの呼び出し例」をご参照ください。
以下の表に、さまざまなディメンションにおける UDTF と SELECT TRANSFORM の比較結果を示します。
|
カテゴリ |
|
UDTF |
|
データ型 |
子プロセスは、標準入出力を使用してデータをやり取りします。すべてのデータは文字列として処理されます。 |
UDTF の出力結果と入力パラメーターは、複数のデータ型をサポートしています。 |
|
データ転送 |
データ伝送は、オペレーティングシステムのパイプラインに基づいています。ただし、パイプラインのキャッシュサイズは 4 KB のみで、変更できません。パイプラインが空または満杯の場合、
|
パイプラインキャッシュに制限はありません。 |
|
定数パラメーターの転送 |
定数パラメーターの転送が必要です。 |
定数パラメーターの転送は任意です。 |
|
プロセス |
SELECT TRANSFORM は親プロセスと子プロセスをサポートします。コンピューティングリソースの使用率が高く、データスループットが低い場合、 |
単一のプロセスが使用されます。 |
|
パフォーマンス |
|
パフォーマンスは低いです。 |
制限事項
PHP と Ruby は MaxCompute のコンピューティングクラスターにデプロイされていません。したがって、MaxCompute で PHP または Ruby のスクリプトを呼び出すことはできません。
構文
SELECT TRANSFORM (<arg1>, <arg2> ...)
[(ROW FORMAT DELIMITED (FIELDS TERMINATED BY <field_delimiter> (ESCAPED BY <character_escape>)) (NULL DEFINED AS <null_value>))]
USING '<unix_command_line>'
(RESOURCES '<res_name>' (',' '<res_name>')*)
[(AS <col1>, <col2> ...)]
(ROW FORMAT DELIMITED (FIELDS TERMINATED BY <field_delimiter> (ESCAPED BY <character_escape>)) (NULL DEFINED AS <null_value>))-
SELECT TRANSFORM キーワード: 必須。 このキーワードは、同じセマンティクスを持つ
mapまたはreduceに置き換えることができます。 構文をより明確にするために、SELECT TRANSFORM を使用することをお勧めします。 -
arg1,arg2...: 必須。これらのパラメーターは入力データを指定します。これらのパラメーターのフォーマットは、
SELECT文のフォーマットと同じです。デフォルトのフォーマットを使用する場合、各パラメーターの式の結果は暗黙的に STRING 型の値に変換されます。その後、パラメーターは\tと結合され、指定された子プロセスに渡されます。 -
ROW FORMAT 句：オプション。この句を使用すると、入力データと出力データのフォーマットをカスタマイズできます。
構文では 2 つの ROW FORMAT 句が使用されます。 最初の句は入力データの形式を指定し、2 番目の句は出力データの形式を指定します。 デフォルトでは、列区切り文字として
\t、行区切り文字として\nが使用され、NULL 値は\Nで表されます。説明-
field_delimiter または character_escape には、1 文字しか使用できません。これらのパラメーターに文字列を指定した場合、文字列の最初の文字が使用されます。
-
MaxCompute は、
inputRecordReader、outputRecordReader、SerDeなど、Apache Hive で指定されている形式の構文をサポートしています。これらの構文を使用するには、SQL 文の前にset odps.sql.hive.compatible=true;を追加して、Hive 互換データ型エディションを有効にする必要があります。Apache Hive でサポートされている構文の詳細については、「Hive ドキュメント」をご参照ください。 -
inputRecordReaderやoutputRecordReaderなど、Apache Hive でサポートされている構文を指定すると、SQL 文の実行速度が低下する可能性があります。
-
-
USING 句: 必須。子プロセスを起動するコマンドを指定します。
-
ほとんどの MaxCompute SQL 文では、USING 句はリソースを指定します。しかし、SELECT TRANSFORM 文の USING 句は、子プロセスを起動するコマンドを指定します。この USING 句は、Apache Hive の構文との互換性を確保するために使用されます。
-
USING 句の構文は、シェルスクリプトの構文と似ています。しかし、USING 句はシェルスクリプトを実行する代わりに、指定したコマンドに基づいて子プロセスを作成します。そのため、入出力リダイレクション、パイプライン、ループなど、一部のシェルの機能は使用できません。必要に応じて、子プロセスを開始するコマンドとしてシェルスクリプトを使用できます。
-
-
RESOURCES 句: 任意。この句は、子プロセスがアクセスできるリソースを指定します。子プロセスがアクセスできるリソースは、次のいずれかの方法で指定できます。
-
RESOURCES 句を使用して、
using 'sh foo.sh bar.txt' resources 'foo.sh','bar.txt'などのリソースを指定します。 -
set odps.sql.session.resources を使用してリソースを指定します。 たとえば、SQL 文の前に
set odps.sql.session.resources=foo.sh,bar.txt;フラグを追加してリソースを指定できます。このグローバル設定が適用されると、すべての SELECT TRANSFORM ステートメントがリソースにアクセスできます。複数のリソースファイルは、カンマ (,) で区切ります。
-
-
AS 句: 任意。この句は、
as(col1 bigint, col2 boolean)などの出力列とそのデータ型を指定します。-
出力列のデータ型を指定しない場合、デフォルトのデータ型である STRING が使用されます。たとえば、
AS(col1, col2)は、出力列が STRING 型であることを示します。 -
出力データは、子プロセスの標準出力を解析して取得します。指定されたデータが STRING 型でない場合、MaxCompute は暗黙的に
CAST関数を呼び出して、データ型を STRING に変換します。変換中にランタイム例外が発生する可能性があります。 -
as(col1, col2:bigint)のように、指定された列の一部にのみデータ型を指定することはできません。 -
AS句を省略した場合、標準出力データの最初の\tより前のフィールドがキーとなり、それ以降のすべての部分が値になります。これはAS(key, value)に相当します。
-
シェルコマンドの呼び出し例
シェルコマンドを実行して、1 から 50 までの 50 行のデータを生成します。出力は data フィールドです。シェルコマンドの出力を SELECT TRANSFORM の入力として使用します。ステートメントの例:
SELECT TRANSFORM(script) USING 'sh' AS (data)
FROM (
SELECT 'for i in `seq 1 50`; do echo $i; done' AS script
) t
;
-- 上記の文は、次の文と同じです。
SELECT TRANSFORM('for i in `seq 1 50`; do echo $i; done') USING 'sh' AS (data);次の結果が返されます。
+------------+
| data |
+------------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
| 21 |
| 22 |
| 23 |
| 24 |
| 25 |
| 26 |
| 27 |
| 28 |
| 29 |
| 30 |
| 31 |
| 32 |
| 33 |
| 34 |
| 35 |
| 36 |
| 37 |
| 38 |
| 39 |
| 40 |
| 41 |
| 42 |
| 43 |
| 44 |
| 45 |
| 46 |
| 47 |
| 48 |
| 49 |
| 50 |
+------------+Python コマンドの呼び出し例
Python コマンドを使用して、1 から 50 で始まる 50 行のデータを生成します。出力は data フィールドです。Python コマンドの出力を SELECT TRANSFORM の入力として使用します。ステートメントの例:
SELECT TRANSFORM(script) USING 'python' AS (data)
FROM (
SELECT 'for i in range(1, 51): print(i);' AS script
) t
;
-- 上記の文は、次の文と同じです。
SELECT TRANSFORM('for i in range(1, 51): print(i);') USING 'python' AS (data);次の結果が返されます。
+------------+
| data |
+------------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |
| 21 |
| 22 |
| 23 |
| 24 |
| 25 |
| 26 |
| 27 |
| 28 |
| 29 |
| 30 |
| 31 |
| 32 |
| 33 |
| 34 |
| 35 |
| 36 |
| 37 |
| 38 |
| 39 |
| 40 |
| 41 |
| 42 |
| 43 |
| 44 |
| 45 |
| 46 |
| 47 |
| 48 |
| 49 |
| 50 |
+------------+AWK コマンドの呼び出し例
テストテーブルを作成し、AWK コマンドを実行してテストテーブルの 2 列目を出力します。この出力データ (data フィールド) を SELECT TRANSFORM の入力として使用します。サンプルステートメント：
-- テストテーブルを作成します。
CREATE TABLE testdata(c1 BIGINT,c2 BIGINT);
-- テストテーブルにテストデータを挿入します。
INSERT INTO TABLE testdata VALUES (1,4),(2,5),(3,6);
-- SELECT TRANSFORM 文を実行します。
SELECT TRANSFORM(*) USING "awk '//{print $2}'" AS (data) FROM testdata;次の結果が返されます。
+------------+
| data |
+------------+
| 4 |
| 5 |
| 6 |
+------------+Perl コマンドの呼び出し例
テストテーブルを作成します。Perl コマンドを実行し、テストテーブルのデータを出力します。出力データは data フィールドです。Perl コマンドの出力を SELECT TRANSFORM の入力として使用します。サンプル文:
-- テストテーブルを作成します。
CREATE TABLE testdata(c1 BIGINT,c2 BIGINT);
-- テストテーブルにテストデータを挿入します。
INSERT INTO TABLE testdata VALUES (1,4),(2,5),(3,6);
-- SELECT TRANSFORM 文を実行します。
SELECT TRANSFORM(testdata.c1, testdata.c2) USING "perl -e 'while($input = <STDIN>){print $input;}'" FROM testdata;次の結果が返されます。
+------------+------------+
| key | value |
+------------+------------+
| 1 | 4 |
| 2 | 5 |
| 3 | 6 |
+------------+------------+Python スクリプトの呼び出し例
-
myplus.py ファイルを作成します。サンプルコード：
#!/usr/bin/env python import sys # 標準入力から各行を反復処理します for line in sys.stdin: # 先頭/末尾の空白、特に改行文字を削除します line = line.strip() if not line: continue # 空行をスキップします tokens = line.split('\t') # 行に少なくとも 2 つの列があることを確認します if len(tokens) >= 2: # 元のロジックのように、NULL 値を確認します if tokens[0] == '\\N' or tokens[1] == '\\N': # どちらかが NULL の場合、'as (result1,result2)' スキーマに合わせて 2 つの NULL 列を出力します print('\\N' + '\t' + '\\N') else: # それ以外の場合は、最初の 2 つの列を出力します print(tokens[0] + '\t' + tokens[1]) -
Python スクリプトファイルをリソースとして MaxCompute に追加します。
ADD PY ./myplus.py -f;説明DataWorks コンソールを使用して、Python ファイルをリソースとして追加することもできます。詳細については、「MaxCompute リソースの作成と使用」をご参照ください。
-
SELECT TRANSFORMステートメントを実行して、このファイルを呼び出します。-- テストテーブルを作成します。 CREATE TABLE testdata(c1 BIGINT,c2 BIGINT); -- テストテーブルにテストデータを挿入します。 INSERT INTO TABLE testdata VALUES (1,4),(2,5),(3,6); -- SELECT TRANSFORM 文を実行します。 SELECT TRANSFORM (testdata.c1, testdata.c2) USING 'python myplus.py' RESOURCES 'myplus.py' AS (result1,result2) FROM testdata; -- 上記の文は、次の文と同じです。 SET odps.sql.session.resources=myplus.py; SELECT TRANSFORM (testdata.c1, testdata.c2) USING 'python myplus.py' AS (result1,result2) FROM testdata;次の結果が返されます。
+------------+------------+ | result1 | result2 | +------------+------------+ | 1 | 4 | | 2 | 5 | | 3 | 6 | +------------+------------+
Java プログラムの呼び出し例
-
Java プログラムを作成し、Sum.jar ファイルとしてエクスポートします。Java のサンプルコード：
package com.aliyun.odps.test; import java.util.Scanner; public class Sum { public static void main(String[] args) { Scanner sc = new Scanner(System.in); while (sc.hasNext()) { String s = sc.nextLine(); String[] tokens = s.split("\t"); if (tokens.length < 2) { throw new RuntimeException("illegal input"); } if (tokens[0].equals("\\N") || tokens[1].equals("\\N")) { System.out.println("\\N"); } else { System.out.println(Long.parseLong(tokens[0]) + Long.parseLong(tokens[1])); } } } } -
ファイルをリソースとして MaxCompute に追加します。
ADD JAR ./Sum.jar -f; -
SELECT TRANSFORMステートメントを実行して、このファイルを呼び出します。-- テストテーブルを作成します。 CREATE TABLE testdata(c1 BIGINT,c2 BIGINT); -- テストテーブルにテストデータを挿入します。 INSERT INTO TABLE testdata VALUES (1,4),(2,5),(3,6); -- SELECT TRANSFORM 文を実行します。 SELECT TRANSFORM(testdata.c1, testdata.c2) USING 'java -cp Sum.jar com.aliyun.odps.test.Sum' RESOURCES 'Sum.jar' AS cnt FROM testdata; -- 上記の文は、次の文と同じです。 SET odps.sql.session.resources=Sum.jar; SELECT TRANSFORM(testdata.c1, testdata.c2) USING 'java -cp Sum.jar com.aliyun.odps.test.Sum' AS cnt FROM testdata;次の結果が返されます。
+-----+ | cnt | +-----+ | 5 | | 7 | | 9 | +-----+
Java と Python には、すぐに使える UDTF フレームワークがあります。しかし、SELECT TRANSFORM を使用すると、スクリプトをより簡単に記述できます。SELECT TRANSFORM は、追加の依存関係が不要で、フォーマット要件もなく、オフラインスクリプトを直接使用することもできます。オフラインの Java スクリプトを保存するディレクトリは、JAVA_HOME 環境変数から取得できます。オフラインの Python スクリプトを保存するディレクトリは、PYTHON_HOME 環境変数から取得できます。
スクリプトの直列実行の例
SELECT TRANSFORM ステートメントを連続して実行できます。 この操作を実行するには、DISTRIBUTE BY 句および SORT BY 句を使用して入力データを前処理できます。 ステートメントの例:
SELECT TRANSFORM(key, value) USING '<cmd2>' FROM
(
SELECT TRANSFORM(*) USING '<cmd1>' FROM
(
SELECT * FROM testdata DISTRIBUTE BY c2 SORT BY c1
) t DISTRIBUTE BY key SORT BY value
) t2;cmd1 と cmd2 は、子プロセスを開始するために使用されるコマンドです。
また、map および reduce キーワードを使用して、SELECT TRANSFORM ステートメントを連続して実行することもできます。
@a := SELECT * FROM data DISTRIBUTE BY col2 SORT BY col1;
@b := MAP * USING 'cmd1' DISTRIBUTE BY col1 SORT BY col2 FROM @a;
REDUCE * USING 'cmd2' FROM @b;