MaxCompute:スクリプトモードの SQL

シナリオ

• スクリプトモードは、ネストされたサブクエリを必要とする単一の複雑なステートメントを書き換えたり、複数のステートメントを単一のスクリプトに結合したりするのに適しています。

• 複数のデータソースからのデータが長い間隔で準備される場合 (たとえば、一方が 01:00 に、もう一方が 07:00 に準備が完了する場合)、テーブル変数を使用してスクリプトモードで大規模な 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, var1.select_expr, ... FROM @var1 
        UNION ALL | UNION 
        SELECT [ALL | DISTINCT] var2.select_expr, var2.select_expr, ... FROM @var2;    
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
        AS 
        SELECT [ALL | DISTINCT] select_expr, select_expr, ...
        FROM @var4;]
[...]

構文の説明

• ステートメントの種類

  スクリプトモードは、SET ステートメント、一部の DDL ステートメント、および DML ステートメントをサポートしています。DESC や SHOW のように画面に結果を表示する DDL ステートメントはサポートされていません。

• ステートメントの順序 スクリプトは、SET、DDL、DML の順序に従う必要があります。各セクションには 0 個以上の SQL ステートメントを含めることができますが、セクションを交互に配置することはできません。

• スクリプトの実行

  • アトミック実行: スクリプトモードでは、いずれかのステートメントが失敗すると、スクリプト全体が失敗し、すべての操作がロールバックされます。

  • 単一ジョブ: スクリプトモードでは、データ処理のために単一のジョブが生成されます。このジョブは、すべての入力データの準備が完了した後にのみ実行されます。

• 変数の使用ルール

  • アットマーク (@) を使用して変数を宣言します。

  • スクリプトモードでは、table 型の変数を、指定されたデータ型の別の変数に割り当てることはできません。例:

    @a TABLE (name STRING);
    @a:= SELECT 'tom';
    @b STRING;
    @b:= SELECT * FROM @a;

  • スクリプトモードでは、変数に定数値を割り当てることができます。その後、SELECT * FROM variable ステートメントを実行して、変数を他の列との計算用のスカラー値に変換できます。次の例に示すように、定数値は単一行のテーブルに格納することもできます。変換構文の詳細については、「サブクエリ」をご参照ください。

    @a := SELECT 10; -- 定数 10 を @a に割り当てるか、SELECT col1 FROM t1 を使用して単一行テーブル t1 から値を割り当てます。
    @b := SELECT key,VALUE+(SELECT * FROM @a) FROM t2 WHERE key >10000; -- テーブル t2 の value 列と @a の値を使用して計算します。
    SELECT * FROM @b;

• 主要なステートメントの制限

  • スクリプトには、スタンドアロンの SELECT ステートメントなど、画面に結果を表示するステートメントを最大 1 つ含めることができます。スクリプトにそのようなステートメントが複数含まれている場合、エラーが発生します。

  • スクリプトには CREATE TABLE AS ステートメントを 1 回だけ含めることができ、それはスクリプトの最後の実行可能なステートメントでなければなりません。テーブルの作成とデータの挿入は、別々の操作で行うことをお勧めします。

  • 同じスクリプト内で、同じテーブルに対して `OVERWRITE` と `INTO` の両方の操作を実行することはできません。また、同じスクリプト内でトランザクションテーブルと標準テーブルの両方に対して DML 操作を実行することもできません。

  • スクリプトモードは 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 ステートメントの条件は、次の 2 種類のうちのいずれかです。

      BOOLEAN 型の式。このタイプの IF ELSE ステートメントの場合、実行するブランチはコンパイル時に決定されます。例:

      -- 
      date := '20190101';
      @row  TABLE(id STRING); -- 変数 row を宣言します。その型は Table で、スキーマは string です。 
      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 ステートメントの場合、実行するブランチはコンパイル時ではなく、実行時に決定されます。したがって、システムは複数のジョブを送信する可能性があります。例:

      @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; 

  • スクリプトモードでは、次の例に示すように、同じスクリプトでテーブルに書き込んだ後にそのテーブルから読み取ろうとすると、エラーが発生します。

    -- テーブルへの書き込み後に読み取りが行われるため、エラーが発生します。
    INSERT OVERWRITE TABLE src2 SELECT * FROM src WHERE key > 0;
    @a := SELECT * FROM src2;
    SELECT * FROM @a;
    
    -- このエラーを防ぐには、SQL スクリプトを次のように変更します。
    @a := SELECT * FROM src WHERE key > 0;
    INSERT OVERWRITE TABLE src2 SELECT * FROM @a;
    SELECT * FROM @a;

例

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;

ツールのサポート

SQL スクリプトモードは、MaxCompute Studio、MaxCompute クライアント (odpscmd)、DataWorks、および SDK で使用できます。次のセクションでは、これらのツールの使用方法について説明します。

..\bin>odpscmd -s myscript.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 {
		// Alibaba Cloud アカウントの AccessKey ペアは、アカウントのすべてのリソースに対する権限を持っています。これは高いセキュリティリスクをもたらします。RAM ユーザーを作成して使用し、API 操作を呼び出したり、日常の O&M を実行したりすることをお勧めします。RAM コンソールにログインして RAM ユーザーを作成します。
		// この例では、AccessKey ID と AccessKey Secret を環境変数に格納する方法を示します。必要に応じて、構成ファイルに格納することもできます。
		// AccessKey ID と AccessKey Secret をコードにハードコーディングしないでください。そうしないと、AccessKey ペアが漏洩する可能性があります。
    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;";

    // この構成を追加する必要があります。
    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));
    }
  }

}

import os
from odps import ODPS


# Alibaba Cloud アカウントの AccessKey ペアは、アカウントのすべてのリソースに対する権限を持っています。これは高いセキュリティリスクをもたらします。RAM ユーザーを作成して使用し、API 操作を呼び出したり、日常の O&M を実行したりすることをお勧めします。RAM コンソールにログインして RAM ユーザーを作成します。
# この例では、AccessKey ID と AccessKey Secret を環境変数に格納する方法を示します。必要に応じて、構成ファイルに格納することもできます。
# AccessKey ID と AccessKey Secret をコードにハードコーディングしないでください。そうしないと、AccessKey ペアが漏洩する可能性があります。
o = ODPS(
    os.environ["ALIBABA_CLOUD_ACCESS_KEY_ID"],
    os.environ["ALIBABA_CLOUD_ACCESS_KEY_SECRET"],
    "your project_name",
    "your end_point"
)

sql_script = """
@a := SELECT * FROM jdbc_test;
SELECT * FROM @a;
"""

# この構成を追加する必要があります。
hints = {"odps.sql.submit.mode", "script"}
instance = o.execute_sql(sql_script, hints=hints)

with instance.open_reader() as reader:
    for rec in reader:
        print(rec[0], rec[1])