Kembangkan tugas ODPS SQL - DataWorks

DataWorks menyediakan node ODPS SQL untuk menjadwalkan tugas MaxCompute SQL secara berkala dan mengintegrasikannya dengan jenis node lainnya. Tugas MaxCompute SQL menggunakan sintaks yang mirip SQL dan cocok untuk skenario pemrosesan terdistribusi yang melibatkan volume data besar (skala terabyte) tetapi tidak memerlukan performa real-time tinggi. Topik ini menjelaskan cara mengembangkan tugas SQL di DataWorks serta pertimbangan penting terkaitnya.

Ikhtisar node

Anda dapat menggunakan ODPS SQL untuk memproses dan mengkueri data di MaxCompute. ODPS SQL mendukung operasi SQL umum, seperti SELECT, INSERT, UPDATE, dan DELETE, serta sintaks dan fungsi khusus MaxCompute. Anda dapat menulis pernyataan mirip SQL untuk mengkueri dan memproses data tanpa harus menulis logika pemrosesan data yang kompleks. Untuk informasi lebih lanjut tentang sintaks SQL, lihat Ikhtisar SQL.

Batasan

Batasan berikut berlaku saat Anda mengembangkan node ODPS SQL di DataWorks:

Kategori	Deskripsi
Komentar	Hanya komentar satu baris dalam format `-- Comment` yang didukung. Komentar multi-baris dalam format `/* Comment */` tidak didukung. Untuk informasi lebih lanjut, lihat Komentar MaxCompute SQL. Batasan berikut juga berlaku untuk komentar. Anda tidak dapat menambahkan komentar setelah pernyataan yang hanya berisi kata kunci, seperti SET atau USE. Anda tidak dapat menggunakan titik koma (;) dalam komentar. Anda tidak dapat menambahkan komentar di akhir pernyataan yang telah lengkap. Pernyataan dianggap lengkap jika diakhiri dengan titik koma (;).


Pengiriman SQL	Dalam ODPS SQL, Anda tidak dapat menjalankan pernyataan SET atau USE secara terpisah. Pernyataan tersebut harus dieksekusi bersama dengan pernyataan SQL tertentu.
Pengembangan SQL	Ukuran kode SQL tidak boleh melebihi 128 KB. Jumlah perintah SQL tidak boleh melebihi 200.
Hasil kueri	Hanya pernyataan SQL yang dimulai dengan SELECT atau WITH yang dapat menghasilkan set hasil terformat. Batasan berikut berlaku untuk hasil kueri: Jika hasil kueri berisi lebih dari 10.000 baris, maksimal 10.000 baris yang dapat ditampilkan. Jika ukuran hasil kueri melebihi 10 MB, pesan error `Result is too large, exceed the limit size: 10MB` akan muncul. Catatan Jika Anda menghadapi batasan ini, Anda dapat mengunduh hasil kueri ke komputer lokal dengan salah satu metode berikut: Metode 1: Jalankan perintah Tunnel Download menggunakan klien lokal MaxCompute (odpscmd). Metode 2: Gunakan fitur ekspor modul SQL Query di DataWorks untuk mengekspor hasil kueri ke komputer lokal.

Catatan

Pastikan akun yang digunakan untuk menjalankan tugas ODPS SQL memiliki izin yang diperlukan pada proyek MaxCompute terkait. Untuk informasi lebih lanjut, lihat Kontrol akses DataWorks On MaxCompute dan Izin MaxCompute.
Tugas MaxCompute SQL memerlukan sumber daya kuota untuk dijalankan. Jika tugas Anda berjalan lama, buka konsol MaxCompute untuk memeriksa konsumsi sumber daya kuota dan pastikan tersedia sumber daya yang cukup untuk menjalankan tugas tersebut. Untuk informasi lebih lanjut, lihat Sumber daya komputasi - Manajemen kuota.
Saat mengembangkan tugas node ODPS SQL, Anda harus membungkus parameter khusus, seperti alamat OSS, dalam tanda kutip ganda (""). Jika tidak, tugas mungkin gagal mengurai dan menjalankan.

Saat menjalankan pernyataan yang mengandung kata kunci seperti SET atau USE di lingkungan DataWorks yang berbeda, urutan eksekusinya bervariasi.

Di DataStudio untuk Pengembangan Data: Semua pernyataan yang mengandung kata kunci seperti SET atau USE dalam kode tugas saat ini digabungkan dan digunakan sebagai pernyataan preamble untuk semua pernyataan SQL berikutnya.
Di lingkungan penjadwalan: Pernyataan dieksekusi sesuai urutan penulisannya.

Sebagai contoh, asumsikan kode yang didefinisikan dalam node adalah sebagai berikut.

SET a=b;
CREATE TABLE name1(id string);
SET c=d;
CREATE TABLE name2(id string);

Urutan eksekusi di lingkungan yang berbeda adalah sebagai berikut:

Pernyataan SQL yang dieksekusi	DataStudio untuk Pengembangan Data	Scheduling O&M
Pernyataan SQL pertama	`SET a=b; SET c=d; CREATE TABLE name1(id string);`	`SET a=b; CREATE TABLE name1(id string);`
Pernyataan SQL kedua	`SET a=b; SET c=d; CREATE TABLE name2(id string);`	`SET c=d; CREATE TABLE name2(id string);`

Edit kode: Contoh sederhana

Perintah SQL

Perintah MaxCompute SQL menggunakan sintaks yang mirip SQL standar. Perintah ini mendukung pernyataan DDL, DML, dan DQL, serta perintah khusus MaxCompute. Untuk informasi lebih lanjut tentang persyaratan sintaks dan contoh penggunaan setiap perintah SQL, lihat Ikhtisar SQL. Contoh berikut menunjukkan cara mengembangkan dan menjalankan perintah SQL sederhana.

Catatan

Saat menggunakan tipe data baru dalam fungsi ekstensi MaxCompute V2.0, Anda harus menambahkan SET odps.sql.type.system.odps2=true; sebelum pernyataan SQL fungsi tersebut dan mengirimkannya bersama sebagai satu pekerjaan. Hal ini memastikan tipe data baru dapat diproses dengan benar. Untuk informasi lebih lanjut tentang tipe data V2.0, lihat Tipe data (V2.0).
DataWorks menyediakan parameter penjadwalan yang memungkinkan Anda meneruskan parameter secara dinamis ke kode dalam skenario penjadwalan. Di node ODPS SQL, Anda dapat mendefinisikan variabel dalam format ${variable_name} di kode Anda. Kemudian, pada tab Scheduling Configuration, di bagian Parameter, Anda dapat memberikan nilai untuk variabel tersebut. Untuk informasi lebih lanjut tentang format parameter penjadwalan yang didukung, lihat Format parameter penjadwalan yang didukung.

Buat tabel
Anda dapat menggunakan pernyataan CREATE TABLE untuk membuat tabel non-partisi, tabel partisi, tabel eksternal, dan tabel terkluster. Untuk informasi lebih lanjut, lihat CREATE TABLE. Kode berikut memberikan contoh SQL:
```
-- Buat tabel partisi bernama students.
CREATE TABLE if NOT EXISTS students
( id BIGINT,
  name STRING,
  age BIGINT,
  birth DATE)
partitioned BY (gender STRING); 
```
Masukkan data
Anda dapat menggunakan pernyataan INSERT INTO atau INSERT OVERWRITE untuk memasukkan data ke atau menimpa data di tabel tujuan. Untuk informasi lebih lanjut, lihat Masukkan atau timpa data (INSERT INTO | INSERT OVERWRITE). Kode berikut memberikan contoh SQL:
```
-- Masukkan data.
INSERT INTO students PARTITION(gender='boy') VALUES (1,'Zhang San',15,DATE '2008-05-15') ;
INSERT INTO students PARTITION(gender='boy') VALUES (2,'Li Si',17,DATE '2006-07-20') ;
INSERT INTO students PARTITION(gender='girl') VALUES (3,'Li Xia',20,DATE '2003-04-20') ;
INSERT INTO students PARTITION(gender='girl') VALUES (4,'Wang Lan',21,DATE '2002-01-08') ;
INSERT INTO students PARTITION(gender='boy') VALUES (5,'Wang Wu',17,DATE '2006-09-12') ;
```
Penting
Hindari menggunakan pernyataan INSERT INTO untuk memasukkan data. Pernyataan ini dapat menyebabkan duplikasi data yang tidak diinginkan. Kami menyarankan Anda menggunakan pernyataan INSERT OVERWRITE sebagai gantinya. Untuk informasi lebih lanjut, lihat Masukkan atau timpa data.
Kueri data
Anda dapat menggunakan pernyataan SELECT untuk melakukan operasi seperti kueri bersarang, kueri kelompok, dan pengurutan. Untuk informasi lebih lanjut, lihat Sintaks SELECT. Kode berikut memberikan contoh SQL:
```
-- (Opsional) Aktifkan pemindaian tabel penuh di tingkat proyek. Operasi ini memerlukan izin tingkat tinggi.
-- SETPROJECT odps.sql.allow.fullscan=true;  

-- Aktifkan pemindaian tabel penuh di tingkat sesi. Pengaturan ini hanya berlaku untuk sesi saat ini.
SET odps.sql.allow.fullscan=true; 
-- Kueri informasi semua siswa laki-laki dan urutkan hasil berdasarkan ID secara ascending.
SELECT * FROM students WHERE gender='boy' ORDER BY id;
```
Catatan
Secara default, pengguna Resource Access Management (RAM) tidak memiliki izin untuk mengkueri tabel produksi. Untuk mendapatkan izin tersebut, Anda harus mengajukannya melalui Security Center. Untuk informasi lebih lanjut tentang izin database preset dan kontrol akses MaxCompute di DataWorks, lihat Detail kontrol izin database MaxCompute. Untuk informasi lebih lanjut tentang cara memberikan izin menggunakan perintah MaxCompute, lihat Kelola izin pengguna menggunakan perintah.

Fungsi SQL

MaxCompute mendukung fungsi bawaan dan user-defined function (UDF) untuk pengembangan dan analisis data. Untuk informasi lebih lanjut tentang fungsi bawaan, lihat Fungsi bawaan. Untuk informasi lebih lanjut tentang UDF, lihat UDF MaxCompute. Contoh berikut menunjukkan cara menggunakan fungsi SQL.

Fungsi bawaan: Fungsi bawaan telah dipreset di MaxCompute dan dapat dipanggil langsung. Sebagai contoh, berdasarkan contoh sebelumnya, Anda dapat menggunakan fungsi dateadd untuk mengubah nilai di kolom birth berdasarkan unit dan interval tertentu. Kode berikut memberikan contoh perintah:
```
-- Aktifkan pemindaian tabel penuh di tingkat sesi. Pengaturan ini hanya berlaku untuk sesi saat ini.
SET odps.sql.allow.fullscan=true; 
SELECT id, name, age, birth, dateadd(birth,1,'mm') AS birth_dateadd FROM students;
```
User-defined function (UDF): Untuk menggunakan UDF, Anda harus menulis kode fungsi, mengunggahnya sebagai resource, dan mendaftarkan fungsi tersebut. Untuk informasi lebih lanjut, lihat Buat dan gunakan UDF.

Runtime dan hasil

Hasil eksekusi ditampilkan dalam workbook. Anda dapat membuka hasilnya di workbook, atau menyalin dan menempel kontennya ke file Excel lokal. Untuk informasi lebih lanjut, lihat Proses debugging tugas.
Catatan
Karena penyesuaian informasi zona waktu Tiongkok yang dirilis oleh Organisasi Internasional untuk Standardisasi, saat Anda menjalankan pernyataan SQL terkait di DataWorks, selisih waktu mungkin terjadi untuk periode tertentu: selisih 5 menit 52 detik untuk tanggal dan waktu antara tahun 1900–1928, serta selisih 9 detik untuk tanggal dan waktu sebelum tahun 1900.
Log operasional: Anda dapat mengklik tab Operational Log untuk melihat log di Logview. Untuk informasi lebih lanjut, lihat Gunakan Logview V2.0 untuk melihat informasi eksekusi pekerjaan.

Hasil:

Kueri informasi semua siswa laki-laki dan urutkan hasil berdasarkan ID secara ascending.

+------------+------------+------------+------------+------------+
| id         | name       | age        | birth      | gender     |
+------------+------------+------------+------------+------------+
| 1          | Zhang San  | 15         | 2008-05-15 | boy        |
| 2          | Li Si      | 17         | 2006-07-20 | boy        |
| 5          | Wang Wu    | 17         | 2006-09-12 | boy        |
+------------+------------+------------+------------+------------+

Ubah nilai di kolom birth berdasarkan unit dan interval tertentu.

+------------+------------+------------+------------+---------------+
| id         | name       | age        | birth      | birth_dateadd |
+------------+------------+------------+------------+---------------+
| 4          | Wang Lan   | 21         | 2002-01-08 | 2002-02-08    |
| 3          | Li Xia     | 20         | 2003-04-20 | 2003-05-20    |
| 2          | Li Si      | 17         | 2006-07-20 | 2006-08-20    |
| 1          | Zhang San  | 15         | 2008-05-15 | 2008-06-15    |
| 5          | Wang Wu    | 17         | 2006-09-12 | 2006-10-12    |
+------------+------------+------------+------------+---------------+

Di halaman hasil kueri, Anda dapat mengurutkan hasil berdasarkan bidang tertentu dan mengunduh hasilnya. Untuk informasi lebih lanjut, lihat Proses hasil kueri.

Edit kode: Contoh lanjutan

Topik berikut menyediakan contoh lebih lanjut mengenai tugas ODPS SQL:

FAQ

T: Mengapa tugas ODPS SQL saya berjalan lama atau tetap dalam status menunggu?
J: Tugas MaxCompute SQL memerlukan sumber daya kuota untuk dijalankan. Jika tugas Anda berjalan lama, buka konsol MaxCompute untuk memeriksa konsumsi sumber daya kuota dan pastikan tersedia sumber daya yang cukup untuk menjalankan tugas tersebut. Untuk informasi lebih lanjut, lihat Sumber daya komputasi - Manajemen kuota.
T: Mengapa saya menerima pesan error "You have No privilege 'odps:xxxx' on xxxx" saat menjalankan tugas ODPS SQL?
J: Error ini menunjukkan bahwa akun yang digunakan untuk menjalankan tugas ODPS SQL tidak memiliki izin yang diperlukan pada proyek MaxCompute terkait. Untuk informasi lebih lanjut, lihat Izin MaxCompute.