Buat node skrip MaxCompute untuk eksekusi SQL - DataWorks

DataWorks menyediakan node MaxCompute Script yang mendukung pengembangan skrip menggunakan mesin SQL MaxCompute V2.0. Topik ini menjelaskan cara menggunakan node MaxCompute Script.

Informasi latar belakang

Mesin SQL MaxCompute mendukung mode skrip yang memungkinkan Anda menggabungkan beberapa pernyataan SQL ke dalam satu skrip untuk dikompilasi dan dieksekusi. Mode ini ideal untuk kueri kompleks, seperti yang melibatkan subkueri bersarang atau operasi multi-langkah. Saat Anda mengirimkan seluruh skrip sekaligus, rencana eksekusi terpadu akan dihasilkan. Pekerjaan tersebut kemudian masuk ke antrian dan dijalankan hanya sekali, sehingga menghasilkan penggunaan sumber daya yang lebih efisien. Untuk informasi selengkapnya, lihat SQL dalam mode skrip.

Skenario

Mode skrip cocok untuk subkueri bersarang bertingkat yang memerlukan penulisan ulang, atau untuk skrip kompleks yang harus dipecah menjadi beberapa pernyataan.

Sebagai contoh, dalam mode skrip, Anda dapat menetapkan nilai konstan ke variabel. Kemudian, Anda dapat mengonversi pernyataan SELECT * FROM variable menjadi skalar dan menggunakannya dalam perhitungan dengan kolom lain. Nilai konstan tersebut juga dapat disimpan dalam tabel satu baris. Kode berikut memberikan contohnya. Untuk informasi tentang sintaks konversi, lihat Subkueri (SUBQUERY).

@a := SELECT 10; -- Tetapkan konstanta 10 ke @a. Anda juga dapat menetapkan nilai dari tabel satu baris t1 dengan menjalankan SELECT col1 FROM t1.
@b := SELECT key,VALUE+(SELECT * FROM @a) FROM t2 WHERE key >10000; -- Lakukan perhitungan menggunakan nilai dari kolom value di tabel t2 dan nilai @a.
SELECT * FROM @b;

Catatan

Jika data dari beberapa sumber data tersedia pada waktu yang sangat berbeda—misalnya, satu pukul 01.00 dan yang lain pukul 07.00—tidak sesuai menggunakan variabel tabel untuk menggabungkan pernyataan menjadi skrip SQL besar.

Prasyarat

Sumber daya komputasi MaxCompute telah disambungkan ke ruang kerja DataWorks.
Tabel yang diperlukan telah dibuat di MaxCompute, dan data sampel telah ditambahkan ke tabel tersebut. Untuk informasi selengkapnya, lihat Pernyataan pembuatan tabel dan data uji sampel.

Sintaks

Skrip MaxCompute lengkap mengikuti struktur berikut: Pernyataan SET > Pernyataan DDL > Pernyataan DML. Setiap bagian dapat berisi nol atau lebih pernyataan, tetapi Anda tidak boleh mencampur jenis pernyataan. Pernyataan yang diawali tanda at (@) menunjukkan variabel. Sintaksnya adalah sebagai berikut:

-- Pernyataan SET
SET odps.sql.type.system.odps2=true;
[SET odps.stage.reducer.num=***;]
[...]
-- Pernyataan DDL
CREATE TABLE table1 xxx;
[CREATE TABLE table2 xxx;]
[...]
-- Pernyataan 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;]

Batasan

Batasan berikut berlaku untuk node MaxCompute Script:

Mode skrip mendukung pernyataan SET, pernyataan DML, dan beberapa pernyataan DDL. Pernyataan DDL yang menampilkan hasil di layar, seperti DESC dan SHOW, tidak didukung.
Skrip hanya boleh berisi satu pernyataan yang menampilkan hasil di layar, seperti pernyataan SELECT mandiri. Jika Anda menyertakan lebih dari satu, akan terjadi error. Hindari menjalankan pernyataan SELECT yang menampilkan hasil dalam skrip.
Skrip hanya boleh berisi maksimal satu pernyataan CREATE TABLE AS. Pernyataan ini harus menjadi yang terakhir dalam skrip. Tulis pernyataan INSERT dan pernyataan CREATE TABLE secara terpisah.
Dalam mode skrip, seluruh skrip gagal jika salah satu pernyataannya gagal.
Dalam mode skrip, pekerjaan untuk memproses data hanya dihasilkan setelah semua data masukan siap.
Dalam mode skrip, terjadi error jika Anda menulis data ke tabel lalu membaca data dari tabel yang sama dalam skrip yang sama.
Contoh yang salah:
```
INSERT OVERWRITE TABLE src2 SELECT * FROM src WHERE key > 0;
@a := SELECT * FROM src2;
SELECT * FROM @a;
```
Contoh yang benar:
Untuk menghindari error ini, ubah pernyataannya sebagai berikut.
```
@a := SELECT * FROM src WHERE key > 0;
INSERT OVERWRITE TABLE src2 SELECT * FROM @a;
SELECT * FROM @a;
```

Buat node MaxCompute Script

Untuk informasi selengkapnya tentang cara membuat node, lihat Buat node MaxCompute Script.

Kembangkan node MaxCompute Script

Pada halaman editor node MaxCompute Script, Anda dapat menggunakan kode skrip contoh di bawah ini untuk pengembangan sesuai kebutuhan.

Pengembangan skrip dasar

Dalam mode skrip MaxCompute, proses kompilasi SQL bersifat langsung. Contoh berikut menunjukkan cara menggunakan node MaxCompute Script.

CREATE TABLE IF NOT EXISTS dest(key string , VALUE bigint) ;
CREATE TABLE IF NOT EXISTS dest2(key string,VALUE bigint ) ;
@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  SELECT * FROM @f;
@g := SELECT e.key,c.value  FROM @e JOIN @c ON e.key=c.key;
INSERT OVERWRITE TABLE dest2 SELECT * FROM @g;

Pengembangan skrip lanjutan

Mode skrip MaxCompute mendukung pernyataan IF. Pernyataan IF memungkinkan program memilih logika eksekusi secara otomatis berdasarkan suatu kondisi. Sintaks IF MaxCompute dibagi menjadi dua jenis berdasarkan kondisi: ekspresi BOOLEAN dan subkueri skalar BOOLEAN.

Kondisi IF berupa ekspresi BOOLEAN

Jenis pernyataan IF ELSE ini menentukan cabang mana yang akan dieksekusi pada tahap kompilasi. Kode berikut memberikan contohnya:

-- Pemrosesan data
SET odps.sql.allow.fullscan=true;
SET odps.optimizer.cbo.rule.filter.black=LM; 
@date := '${var}';
@row  TABLE(key int,VALUE bigint); -- Deklarasikan variabel row. Jenis variabelnya adalah Table, dan skemanya adalah string. 
IF ( cast(@date  AS bigint) % 2 == 0 ) BEGIN 
@row  := SELECT key,VALUE FROM src1; 
END ELSE BEGIN
@row  := SELECT key,VALUE FROM src2; 
END
INSERT OVERWRITE TABLE dest1 PARTITION(p='${var}')  SELECT key,VALUE FROM @row;

Catatan

Variabel bernama var didefinisikan dalam kode. Anda harus menetapkan nilai ke variabel var dalam pengaturan parameter penjadwalan.

Kondisi IF berupa subkueri skalar BOOLEAN
Jenis pernyataan IF ELSE ini tidak dapat menentukan cabang mana yang akan dieksekusi pada tahap kompilasi. Keputusan dibuat saat waktu proses. Oleh karena itu, Anda harus mengirimkan beberapa pekerjaan. Kode berikut memberikan contohnya:
```
@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; 
```
Pengembangan fungsi yang ditentukan pengguna tertanam (UDF)
Anda juga dapat menggunakan mode skrip MaxCompute untuk mengembangkan fungsi yang ditentukan pengguna tertanam (UDF) dengan menyematkan kode Java atau Python ke dalam skrip SQL. Untuk informasi selengkapnya, lihat UDF Tertanam.

Uji node MaxCompute Script

Pada halaman editor node, konfigurasikan parameter di bagian Run Configuration di sebelah kanan.

Parameter	Deskripsi
Computing resource	Pilih sumber daya komputasi MaxCompute yang telah disambungkan.
Computing quota	Pilih kuota komputasi (Quota) yang telah Anda buat. Kuota tersebut menyediakan sumber daya komputasi, seperti CPU dan memori, untuk pekerjaan. Jika tidak tersedia kuota komputasi, klik Create Computing Quota dalam daftar drop-down untuk membuat dan mengonfigurasi kuota di Konsol MaxCompute.
Resource group	Pilih kelompok sumber daya penjadwalan yang telah lulus uji konektivitas dengan sumber daya komputasi. Untuk informasi selengkapnya, lihat Solusi konektivitas jaringan.

Pada kotak dialog parameter di bilah alat, pilih sumber data MaxCompute yang telah Anda buat, lalu klik Run untuk menjalankan task MaxCompute Script.

Langkah selanjutnya

Konfigurasi penjadwalan node: Jika node dalam folder proyek harus dijalankan sesuai jadwal, atur Scheduling dan konfigurasikan properti penjadwalan di bagian Scheduling Policies di sisi kanan node.
Penerapan node: Jika task harus dijalankan di lingkungan produksi, klik ikon untuk memulai proses penerapan. Proses ini menerbitkan task ke lingkungan produksi. Node dalam folder proyek hanya dijalankan sesuai jadwal setelah diterbitkan ke lingkungan produksi.

Pernyataan pembuatan tabel dan data uji sampel

Sebagai contoh:

-- Buat tabel.
CREATE TABLE IF NOT EXISTS src(key string ,VALUE BIGINT);
INSERT INTO src VALUES ('1',11) ;
INSERT INTO src VALUES ('1',11) ;
CREATE TABLE IF NOT EXISTS  src2(key string ,VALUE BIGINT);
INSERT INTO src2  VALUES ('1',22);
INSERT INTO src2  VALUES ('2',22);
CREATE TABLE IF NOT EXISTS  src3(key string ,VALUE BIGINT);
INSERT INTO src3 VALUES ('1',33);
INSERT INTO src3 VALUES ('3',33);