MaxCompute：SQL dalam mode skrip

Skenario

• Mode skrip cocok untuk menulis ulang satu pernyataan kompleks yang memerlukan subkueri bersarang atau untuk menggabungkan beberapa pernyataan menjadi satu skrip.

• Jika data dari beberapa sumber disiapkan dalam interval waktu yang panjang (misalnya, satu siap pukul 01.00 dan yang lain pukul 07.00), jangan gunakan variabel tabel untuk menyusun skrip SQL besar dalam mode skrip.

Sintaksis

-- 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;]
[...]

Deskripsi sintaksis

• Jenis pernyataan

  Mode skrip mendukung pernyataan SET, beberapa pernyataan DDL, dan pernyataan DML. Pernyataan DDL yang menampilkan hasil di layar, seperti DESC atau SHOW, tidak didukung.

• Urutan pernyataanSkrip harus mengikuti urutan SET, DDL, lalu DML. Setiap bagian dapat berisi nol atau lebih pernyataan SQL, tetapi urutan tersebut tidak boleh diubah.

• Eksekusi skrip

  • Eksekusi atomik: Dalam mode skrip, jika ada satu pernyataan gagal, seluruh skrip gagal dan semua operasi dikembalikan (rollback).

  • Pekerjaan tunggal: Dalam mode skrip, satu pekerjaan dihasilkan untuk pemrosesan data. Pekerjaan ini hanya dijalankan setelah semua data masukan siap.

• Aturan penggunaan variabel

  • Gunakan tanda at (@) untuk mendeklarasikan variabel.

  • Dalam mode skrip, Anda tidak dapat menetapkan variabel bertipe table ke variabel lain dengan tipe data tertentu. Contoh:

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

  • Dalam mode skrip, Anda dapat menetapkan nilai konstan ke variabel. Anda kemudian dapat menjalankan pernyataan SELECT * FROM variable untuk mengonversi variabel tersebut menjadi nilai skalar guna perhitungan dengan kolom lain. Nilai konstan juga dapat disimpan dalam tabel satu baris, seperti pada contoh berikut. Untuk informasi selengkapnya tentang sintaks konversi, lihat Subkueri.

    @a := SELECT 10; -- Tetapkan konstanta 10 ke @a, atau tetapkan nilai dari tabel satu baris t1 menggunakan SELECT col1 FROM t1.
    @b := SELECT key,VALUE+(SELECT * FROM @a) FROM t2 WHERE key >10000; -- Hitung menggunakan kolom value di tabel t2 dan nilai @a.
    SELECT * FROM @b;

• Batasan pada pernyataan utama

  • Skrip dapat berisi maksimal satu pernyataan yang menampilkan hasil di layar, seperti pernyataan SELECT mandiri. Jika skrip berisi lebih dari satu pernyataan semacam itu, terjadi kesalahan.

  • Skrip hanya dapat berisi pernyataan CREATE TABLE AS satu kali, dan harus menjadi pernyataan yang dapat dieksekusi terakhir dalam skrip. Kami menyarankan agar Anda membuat tabel dan memasukkan data dalam operasi terpisah.

  • Dalam skrip yang sama, Anda tidak dapat melakukan operasi `OVERWRITE` dan `INTO` pada tabel yang sama. Anda juga tidak dapat melakukan operasi DML pada tabel transaksional dan tabel standar dalam skrip yang sama.

  • Mode skrip mendukung pernyataan IF. Pernyataan IF memungkinkan program memilih logika eksekusi berdasarkan kondisi.

    • MaxCompute mendukung jenis sintaks IF berikut.

      IF (condition) BEGIN
        statement 1
        statement 2
        ...
      END
      
      IF (condition) BEGIN
        statements
      END ELSE IF (condition2) BEGIN
        statements
      END ELSE BEGIN
        statements
      END

      Catatan

      • Jika klausa BEGIN dan END hanya berisi satu pernyataan, Anda dapat menghilangkan kata kunci BEGIN dan END. Ini mirip dengan blok kode { } dalam Java.

      • Pernyataan dalam setiap cabang sintaks IF tidak mendukung pernyataan DDL, seperti CREATE TABLE, ALTER TABLE, atau TRUNCATE TABLE.

    • Kondisi dalam pernyataan IF dapat berupa salah satu dari dua tipe berikut:

      Ekspresi bertipe BOOLEAN. Untuk pernyataan IF ELSE tipe ini, cabang yang akan dieksekusi ditentukan saat kompilasi. Contoh:

      -- 
      date := '20190101';
      @row  TABLE(id STRING); -- Deklarasikan variabel row. Tipenya adalah Table dan skemanya adalah 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; 

      Subkueri skalar bertipe BOOLEAN. Untuk pernyataan IF ELSE tipe ini, cabang yang akan dieksekusi ditentukan saat waktu proses, bukan saat kompilasi. Oleh karena itu, sistem mungkin mengirimkan beberapa pekerjaan. Contoh:

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

  • Dalam mode skrip, terjadi kesalahan jika Anda mencoba membaca dari tabel dalam skrip yang sama setelah menulis ke tabel tersebut, seperti pada contoh berikut:

    -- Terjadi kesalahan karena tabel ditulis lalu dibaca.
    INSERT OVERWRITE TABLE src2 SELECT * FROM src WHERE key > 0;
    @a := SELECT * FROM src2;
    SELECT * FROM @a;
    
    -- Untuk mencegah kesalahan ini, ubah skrip SQL sebagai berikut:
    @a := SELECT * FROM src WHERE key > 0;
    INSERT OVERWRITE TABLE src2 SELECT * FROM @a;
    SELECT * FROM @a;

Contoh

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;

Dukungan alat

Anda dapat menggunakan mode skrip SQL di MaxCompute Studio, Klien MaxCompute (odpscmd), DataWorks, dan SDK. Bagian berikut menjelaskan cara menggunakan alat-alat ini.

..\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 {
		// Pasangan Kunci Akses akun Alibaba Cloud memiliki semua izin atas sumber daya akun tersebut. Hal ini menimbulkan ancaman keamanan tinggi. Kami menyarankan agar Anda membuat dan menggunakan Pengguna RAM untuk memanggil operasi API atau melakukan O&M rutin. Masuk ke konsol RAM untuk membuat Pengguna RAM.
		// Contoh ini menunjukkan cara menyimpan ID AccessKey dan Rahasia AccessKey dalam variabel lingkungan. Anda juga dapat menyimpannya dalam berkas konfigurasi sesuai kebutuhan.
		// Jangan menyematkan ID AccessKey dan Rahasia AccessKey secara langsung dalam kode Anda. Jika tidak, pasangan Kunci Akses tersebut mungkin bocor.
    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;";

    // Anda harus menambahkan konfigurasi ini.
    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


# Pasangan Kunci Akses akun Alibaba Cloud memiliki semua izin atas sumber daya akun tersebut. Hal ini menimbulkan ancaman keamanan tinggi. Kami menyarankan agar Anda membuat dan menggunakan Pengguna RAM untuk memanggil operasi API atau melakukan O&M rutin. Masuk ke konsol RAM untuk membuat Pengguna RAM.
# Contoh ini menunjukkan cara menyimpan ID AccessKey dan Rahasia AccessKey dalam variabel lingkungan. Anda juga dapat menyimpannya dalam berkas konfigurasi sesuai kebutuhan.
# Jangan menyematkan ID AccessKey dan Rahasia AccessKey secara langsung dalam kode Anda. Jika tidak, pasangan Kunci Akses tersebut mungkin bocor.
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;
"""

# Anda harus menambahkan konfigurasi ini.
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])