スクリプトモードのSQL - MaxCompute - Alibaba Cloud ドキュメントセンター

大規模なデータセットに対してETL (extract、transform、and load) タスク、自動化された定期的なタスク、および複雑なクエリオーケストレーションタスクを実行する場合は、MaxComputeの現在のSQLエンジンでサポートされているスクリプトモードを使用できます。スクリプトモードを使用してSQLスクリプトファイルをコンパイルすると、ファイル内のすべてのステートメントが同時にコンパイルされます。各ステートメントをコンパイルする必要はありません。スクリプトファイルがコンパイルされた後、スクリプトファイルはMaxComputeに送信され、実行計画が生成されます。このように、スクリプトファイル内のステートメントは1つのキューにスケジュールされ、1回実行されます。これにより、MaxComputeのリソースを完全に活用できます。このようにして、作業効率が向上し、データ処理および分析ワークフローの柔軟性とセキュリティが強化されます。

説明

スクリプトモードでは、[コスト推定] で説明されている [コストSQL] コマンドを使用して、SQL文のコストを推定することはできません。正確な費用の請求書は、Alibaba Cloud管理コンソールで確認できます。詳細については、「課金の詳細を見る」をご参照ください。
スクリプトファイルで参照されるテーブルの数は10,000を超えることはできません。スクリプトファイルにビューが含まれている場合、ビューの作成時に参照されるテーブルの数が計算されます。スクリプトファイル内でテーブルが複数回参照されている場合は、テーブルの参照回数が参照されるテーブルの数にカウントされます。

スクリプトモードでは、一般的なプログラミング言語でSQL文をコンパイルするのと同様に、SQL文をコンパイルできます。ステートメントを整理する方法を検討する必要はありません。

構文

--set
set odps.sql.type.system.odps2=true;
[set odps.stage.reducer.num=xxx;]
[...]
--ddl
create table table1 xxx;
[create table table2 xxx;]
[...]
--dml
@var1 := SELECT [ALL | DISTINCT] select_expr, select_expr, ...
        FROM table3
        [WHERE where_condition];
@var2 := SELECT [ALL | DISTINCT] select_expr, select_expr, ...
        FROM table4
        [WHERE where_condition];
@var3 := SELECT [ALL | DISTINCT] var1.select_expr, var2.select_expr, ...
        FROM @var1 join @var2 on ...;
INSERT OVERWRITE|INTO TABLE [PARTITION (partcol1=val1, partcol2=val2 ...)]
        SELECT [ALL | DISTINCT] select_expr, select_expr, ...
        FROM @var3;    
[@var4 := SELECT [ALL | DISTINCT] var1.select_expr, var.select_expr, ... FROM @var1 
        UNION ALL | UNION 
        SELECT [ALL | DISTINCT] var1.select_expr, var.select_expr, ... FROM @var2;    
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
        AS 
        SELECT [ALL | DISTINCT] select_expr, select_expr, ...
        FROM @var4;]
[...]

構文の説明

スクリプトモードでは、SETステートメント、DMLステートメント、および一部のDDLステートメントの3種類のステートメントがサポートされています。 DESCやSHOWなど、結果の表示に使用されるDDLステートメントはサポートされていません。
完全なスクリプトファイルは、SET、DDL、およびDMLステートメントのリストである必要があります。スクリプトファイルには、各型の複数のステートメントを含めることができますが、同じ型のステートメントをまとめて配置する必要があります。 3種類のステートメントを前の順序で配置する必要があります。
at記号 (@) は変数を宣言します。
スクリプトファイルには、SELECT文など、結果の表示に使用する文を1つまで含めることができます。スクリプトファイルにそのようなステートメントが複数含まれている場合は、エラーが返されます。スクリプトファイルに結果を表示するために使用されるSELECTステートメントを含めないことをお勧めします。
スクリプトファイルには、最大1つのCREATE TABLE ASステートメントを含めることができ、このステートメントで終了する必要があります。テーブルの作成に使用するステートメントとINSERTステートメントを同じスクリプトコマンドに含めないことをお勧めします。
スクリプトモードでは、スクリプトファイル内のステートメントの実行に失敗すると、スクリプトファイル内の他のすべてのステートメントも失敗します。
スクリプトモードでは、すべての入力データが準備されて挿入された後にのみ、データを処理するジョブを生成できます。
スクリプトモードでは、table型の変数の値を、指定したデータ型の変数に代入することはできません。サンプルコード：
```
@a table (name string);
@a:= select 'tom';
@b string;
@b:= select * from @a;
```
スクリプトモードでは、テーブルが書き込まれてから読み取られると、次のエラーが返されます。
```
INSERT OVERWRITE table src2 SELECT * FROM src WHERE key > 0;
@a := SELECT * FROM src2;
SELECT * FROM @a;
```
このエラーを防ぐには、次のサンプルスクリプトに基づいてスクリプトファイルを変更します。
```
@a := SELECT * FROM src WHERE key > 0;
INSERT OVERWRITE table src2 SELECT * FROM @a;
SELECT * FROM @a;
```
スクリプトモードでは、IFステートメントがサポートされます。
- IFステートメントにより、システムは特定の条件に基づいて実行ロジックを自動的に選択できます。 MaxComputeは、次のIF構文をサポートしています。
```
IF (condition) BEGIN
  statement 1
  statement 2
  ...
END

IF (condition) BEGIN
  statements
END ELSE IF (condition2) BEGIN
  statements
END ELSE BEGIN
  statements
END
```
  説明
  - BEGINおよびEND条件句に1つのステートメントしか含まれていない場合は、BEGINおよびEND条件句を省略できます。これは、Javaのコードブロック { } に似ています。
  - IFステートメントには、CREATE TABLE、ALTER TABLE、TRUNCATE TABLEなどのDDLステートメントを含めることはできません。
- IFステートメントには、次の種類の条件を含めることができます。
  - BOOLEAN型の式。 IF ELSEステートメントのBOOLEAN型の式は、コードのコンパイル中に実行するブランチを決定します。サンプルコード：
```
@date := '20190101';
@row TABLE(id STRING); -- Declare the row variable. The row type is TABLE and the fields in the table are of the STRING data type. 
IF ( cast(@date  as bigint) % 2 == 0 ) BEGIN 
@row  := SELECT id from src1; 
END ELSE BEGIN
@row  := SELECT id from src2; 
END
INSERT OVERWRITE TABLE dest SELECT * FROM @row; 
```
  - BOOLEAN型のスカラーサブクエリ。 IF ELSEステートメントのBOOLEAN型スカラサブクエリは、ジョブの実行中に実行する分岐を決定します。したがって、複数のジョブを送信する必要があります。サンプルコード：
```
@i bigint;
@t table(id bigint, value bigint);
IF ((SELECT count(*) FROM src WHERE a = '5') > 1)  BEGIN
@i := 1;
@t := select @i, @i*2;
END ELSE
BEGIN
@i := 2;
@t := select @i, @i*2;
END
select id, value from @t; 
```
スクリプトモードは、次のシナリオで使用できます。
- ネストされたサブクエリを持つ単一のステートメントを書き直すか、複雑なスクリプトを複数のステートメントに分割する必要があります。
- 異なるデータソースからのデータは異なる時点で準備され、時間差は大きい。たとえば、あるデータソースからのデータは01:00に準備され、別のデータソースからのデータは07:00に準備されます。この場合、テーブル変数は、異なる型のステートメントをスクリプトファイルにパッケージ化するのには適していません。
- 変数に定数値を割り当て、SELECT * FROM variableステートメントを実行して変数をスカラー値に変換する必要があります。スカラー値は、他の列とともに計算に使用されます。定数値は、行が1つしかないテーブルに格納することもできます。次のステートメントは例を示します。 SELECT * FROM Variableステートメントの構文の詳細については、「サブクエリ」をご参照ください。
```
@a := SELECT 10; -- Assign the constant value 10 to @a. You can also execute the SELECT col1 FROM t1 statement to store the constant value to table t1 that has only one row. 
@b := SELECT key,value+(SELECT * FROM @a) FROM t2 WHERE key >10000; -- Use the value of @a with value in table t2 for calculation. 
SELECT * FROM @b;
```

例：

次のサンプルコードは、スクリプトモードでMaxCompute SQL文を実行する方法の例を示しています。

CREATE TABLE IF NOT EXISTS dest(key STRING, value BIGINT) PARTITIONED BY (d STRING);
CREATE TABLE IF NOT EXISTS dest2(key STRING, value BIGINT) PARTITIONED  BY (d STRING);
@a := SELECT * FROM src WHERE value >0;
@b := SELECT * FROM src2 WHERE key is not null;
@c := SELECT * FROM src3 WHERE value is not null;
@d := SELECT a.key,b.value FROM @a LEFT OUTER JOIN @b ON a.key=b.key AND b.value>0;
@e := SELECT a.key,c.value FROM @a INNER JOIN @c ON a.key=c.key;
@f := SELECT * FROM @d UNION SELECT * FROM @e UNION SELECT * FROM @a;
INSERT OVERWRITE table dest PARTITION (d='20171111') SELECT * FROM @f;
@g := SELECT e.key,c.value FROM @e JOIN @c ON e.key=c.key;
INSERT OVERWRITE TABLE dest2 PARTITION (d='20171111') SELECT * FROM @g;

必要なツール

MaxCompute Studio。詳細については、「MaxCompute Studioとは」をご参照ください。
MaxComputeクライアント (odpscmd) 。詳細については、「MaxComputeクライアントのインストールと設定」をご参照ください。
DataWorksコンソール。詳細については、「MaxComputeスクリプトタスクの開発」をご参照ください。

スクリプトモードの使用方法

MaxCompute Studioの使用
MaxCompute Studioを使用する前に、MaxCompute Studioがインストールされ、プロジェクト接続が追加され、MaxCompute SQLスクリプトファイルが作成されていることを確認してください。詳細については、「IntelliJ IDEAのインストール」、「プロジェクト接続の管理」、および「MaxComputeスクリプトモジュールの作成」をご参照ください。

スクリプトファイルをコンパイルして送信した後、グラフィック実行計画を表示できます。スクリプトファイルには複数のステートメントが含まれていますが、グラフィック実行プランは有向非巡回グラフ (DAG) で表示されます。
MaxComputeクライアントの使用
スクリプトファイルを送信するには、0.27以降のバージョンのMaxComputeクライアントを使用する必要があります。 MaxComputeクライアントインストールパッケージの最新バージョンをインストールすることを推奨します。最新バージョンのパッケージをインストールしたら、-sオプションを使用してスクリプトファイルを送信します。
スクリプトコードのソースコードファイルmyscript.sqlを編集し、コマンドラインインターフェイス (CLI) でodpscmdを呼び出して次のコマンドを実行します。 CLIを使用してMaxComputeクライアントを実行する方法の詳細については、「MaxComputeクライアントの実行」をご参照ください。
```
..\bin>odpscmd -s myscript.sql
```
説明
-fおよび -eオプションと同様に、-sオプションはMaxComputeクライアントのコマンドラインオプションです。 -sオプションは、対話型環境ではコマンドではありません。 MaxComputeクライアント (odpscmd) がインタラクティブ環境で使用されている場合、スクリプトモードとテーブル変数はサポートされません。
DataWorksコンソールの使用
DataWorksコンソールで、ODPSスクリプトノードを作成できます。 ODPSスクリプトノードの作成方法を次の図に示します。
ノードの作成後、スクリプトモードを使用してスクリプトファイルを編集し、ツールバーの [実行] アイコンをクリックしてスクリプトファイルをMaxComputeに送信します。これにより、出力のLogView URLを使用して、グラフィック実行計画と結果を表示できます。

SDK for Javaの使用

SDK for Javaを使用してSQLスクリプトファイルを作成できます。 SDK For Javaの詳細については、「SDK for Java」をご参照ください。次のサンプルコードは、SQLスクリプトファイルの作成方法の例を示しています。

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.aliyun.odps.Instance;
import com.aliyun.odps.Odps;
import com.aliyun.odps.OdpsException;
import com.aliyun.odps.account.Account;
import com.aliyun.odps.account.AliyunAccount;
import com.aliyun.odps.data.Record;
import com.aliyun.odps.task.SQLTask;

public class SdkTest {

  public static void main(String[] args) throws OdpsException {
		// The AccessKey pair of an Alibaba Cloud account has permissions on all API operations. If you use an AccessKey pair to call API operations, risks may occur. We recommend that you use a RAM user to call API operations or perform routine O&M. To create a RAM user, log on to the RAM console.
		// In this example, the AccessKey ID and AccessKey secret are configured as environment variables. You can also save your AccessKey pair in the configuration file based on your business requirements.
		// We recommend that you do not directly specify the AccessKey ID and AccessKey secret in code to prevent AccessKey pair leaks.
    Account account = new AliyunAccount(System.getenv("ALIBABA_CLOUD_ACCESS_KEY_ID"), System.getenv("ALIBABA_CLOUD_ACCESS_KEY_SECRET"));
    Odps odps = new Odps(account);
    odps.setDefaultProject("your project_name");
    odps.setEndpoint("your end_point");

    String sqlScript = "@a := SELECT * FROM jdbc_test;\n"
                       + "SELECT * FROM @a;";

    // Make sure that you add the following configuration to the SQL script file.
    Map<String, String> hints = new HashMap<>();
    hints.put("odps.sql.submit.mode", "script");

    Instance instance = SQLTask.run(odps, "your project_name", sqlScript, hints, null);
    instance.waitForSuccess();

    List<Record> recordList = SQLTask.getResult(instance);
    for (Record record : recordList) {
      System.out.println(record.get(0));
      System.out.println(record.get(1));
    }
  }

}