SELECT TRANSFORM - MaxCompute - Alibaba Cloud ドキュメントセンター

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 の比較結果を示します。

カテゴリ	`select transform`	UDTF
データの型	子プロセスは、標準入力と標準出力を使用してデータを送信します。すべてのデータは文字列として処理されます。 `SELECT TRANSFORM` を使用する場合、データ型の変換が必要です。	UDTF の出力結果と入力パラメーターは、複数のデータ型をサポートしています。
データ送信	データ送信は、オペレーティングシステムのパイプラインに基づいています。ただし、パイプラインのキャッシュサイズは 4 KB のみで、変更できません。パイプラインが空であるか、完全に占有されている場合、`SELECT TRANSFORM` は実行できません。 SELECT TRANSFORM は、データ送信中に基盤となるシステムを呼び出してデータの読み取りと書き込みを行います。この文は、Java プログラムよりもデータ送信のパフォーマンスが高くなります。	パイプラインキャッシュに制限はありません。
定数パラメーターの送信	定数パラメーターを送信する必要があります。	定数パラメーターはオプションで送信されます。
プロセス	SELECT TRANSFORM は、親プロセスと子プロセスをサポートしています。計算リソースの使用率が高く、データスループットが低い場合、`SELECT TRANSFORM` はサーバーのマルチコア機能を使用できます。	単一のプロセスが使用されます。
パフォーマンス	`SELECT TRANSFORM` は、AWK などのツールのネイティブコードをサポートしています。これにより、`SELECT TRANSFORM` は Java プログラムよりも高いパフォーマンスを提供できます。	パフォーマンスが低くなります。

制限

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 が行区切り文字として使用され、\N が NULL 値を表すために使用されます。
説明
- field_delimiter または character_escape として使用できる文字は 1 文字だけです。これらのパラメーターに文字列を指定すると、文字列の最初の文字が使用されます。
- MaxCompute は、inputRecordReader、outputRecordReader、SerDe など、Apache Hive によって指定された形式の構文をサポートしています。これらの構文を使用するには、Hive 互換のデータ型エディションを有効にする必要があります。 Hive 互換のデータ型エディションを有効にするには、SQL 文の前に set odps.sql.hive.compatible=true; を追加します。 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 xrange(1, 51):  print(i);' as script
      ) t
;
-- 前述のステートメントは、次のステートメントと同等です。
select transform('for i in xrange(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 フィールドです。 AWK コマンドの出力を 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
line = sys.stdin.readline()
while line:
    token = line.split('\t')
    if (token[0] == '\\N') or (token[1] == '\\N'):
        print('\\N')
    else:
        print(str(token[0]) +'\t' + str(token[1]))
    line = sys.stdin.readline()

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          |
|            | NULL       |
| 2          | 5          |
|            | NULL       |
| 3          | 6          |
|            | NULL       |
+------------+------------+

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");
            }
            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;