MaxCompute:Menulis ulang SQL yang tidak kompatibel

Informasi latar belakang

MaxCompute V2.0 sepenuhnya mengadopsi ekosistem open source, mendukung lebih banyak fitur bahasa, dan berjalan lebih cepat. Namun, MaxCompute V2.0 menerapkan pemeriksaan sintaks yang lebih ketat. Kueri dengan sintaks tidak tepat yang sebelumnya berhasil dijalankan pada versi kompilator sebelumnya kini dapat menyebabkan error di MaxCompute V2.0.

group.by.with.star

bad.escape

Deskripsi: Masalah ini terkait dengan urutan escape yang salah.

Sesuai aturan MaxCompute, Anda harus menggunakan backslash diikuti angka oktal tiga digit untuk merepresentasikan karakter ASCII dari 0 hingga 127 dalam literal string. Misalnya, Anda dapat menggunakan "\001" dan "\002" untuk merepresentasikan 0 dan 1. Namun, versi sebelumnya juga memproses \01 dan \0001 sebagai \001.

column.repeated.in.creation

Deskripsi: Di MaxCompute V2.0, error terjadi jika Anda menggunakan nama kolom duplikat saat membuat tabel.

string.join.double

window.ref.prev.window.alias

Deskripsi: Masalah ini terjadi saat fungsi jendela mereferensikan alias fungsi jendela lain dalam daftar `SELECT` yang sama.

select.invalid.token.after.star

Deskripsi: Dalam daftar `SELECT`, Anda dapat menggunakan tanda bintang (*) untuk memilih semua kolom dari tabel. Namun, Anda tidak dapat menambahkan alias setelah tanda bintang (*). Sintaks ini tidak diperbolehkan bahkan jika tanda bintang (*) hanya mengembangkan satu kolom. Kompilator MaxCompute V2.0 melaporkan error untuk sintaks ini.

agg.having.ref.prev.agg.alias

Deskripsi: Masalah ini terjadi saat daftar `SELECT` mereferensikan alias fungsi agregat sebelumnya dan klausa `HAVING` ada.

order.by.no.limit

Deskripsi: Secara default, MaxCompute mengharuskan Anda menambahkan klausa limit setelah klausa order by untuk membatasi jumlah hasil yang dikembalikan. Persyaratan ini ada karena order by melakukan pengurutan penuh, yang memiliki performa eksekusi rendah jika klausa limit tidak digunakan.

Tambahkan klausa limit ke subkueri order by v.uv desc.

create view table_view as select id from table_view order by id;

Untuk mengakses view ini:

select * from table_view;

MaxCompute V1.0 tidak melaporkan error. MaxCompute V2.0 melaporkan pesan error berikut:

FAILED: ODPS-0130071:[1,15] Semantic analysis exception - while resolving view xdj.xdj_view_limit - ORDER BY must be used with a LIMIT clause

generated.column.name.multi.window

Deskripsi: Masalah ini terkait penggunaan aliases yang dihasilkan secara otomatis.

Versi MaxCompute sebelumnya secara otomatis menghasilkan alias untuk setiap ekspresi dalam pernyataan `SELECT`. Alias ini ditampilkan di Konsol. Namun, aturan untuk menghasilkan alias ini tidak dijamin dan dapat berubah. Oleh karena itu, Anda tidak boleh menggunakan alias yang dihasilkan secara otomatis.

MaxCompute V2.0 memberikan peringatan saat alias yang dihasilkan secara otomatis digunakan. Praktik ini belum dapat dilarang saat ini karena sudah banyak digunakan.

Dalam beberapa kasus, aturan untuk menghasilkan alias berubah antar versi MaxCompute. Karena beberapa pekerjaan online bergantung pada alias ini, kueri tersebut dapat gagal selama peningkatan atau rollback versi MaxCompute. Jika Anda menghadapi masalah ini, Anda harus memodifikasi kueri Anda untuk secara eksplisit menentukan alias untuk kolom yang ingin Anda gunakan.

non.boolean.filter

Masalah penggunaan kondisi filter non-BOOLEAN.

MaxCompute tidak mengizinkan konversi implisit antara tipe Boolean dan tipe data lainnya. Namun, versi MaxCompute sebelumnya mengizinkan Anda menggunakan `BIGINT` sebagai kondisi filter dalam beberapa kasus. MaxCompute V2.0 tidak lagi mengizinkan perilaku ini. Jika skrip Anda berisi kondisi filter seperti itu, Anda harus memodifikasinya. Contohnya sebagai berikut:

Sintaks salah:

select id, count(*) from table_name group by id having id;

Pesan error:

FAILED: ODPS-0130071:[1,50] Semantic analysis exception - expect a BOOLEAN expression

Metode yang benar adalah sebagai berikut:

select id, count(*) from table_name group by id having id <> 0;

post.select.ambiguous

Masalah mereferensikan kolom dengan nama yang bertentangan dalam pernyataan `order by`, `cluster by`, `distribute by`, atau `sort by`.

Dalam versi MaxCompute sebelumnya, sistem memilih kolom terakhir dalam daftar `SELECT` sebagai objek operasi secara default. MaxCompute V2.0 melaporkan error. Anda harus memodifikasi pernyataan Anda. Contohnya sebagai berikut:

Sintaks salah:

select a, b as a from t order by a limit 10;

Pesan error:

FAILED: ODPS-0130071:[1,34] Semantic analysis exception - a is ambiguous, can be both t.a or null.a

Berikut adalah modifikasi yang benar:

select a as c, b as a from t order by a limit 10;

Perubahan ini juga mencakup kasus di mana nama bertentangan tetapi semantiknya sama. Meskipun ini tidak menyebabkan ambiguitas, peringatan dilaporkan untuk mendorong Anda melakukan koreksi karena sintaks ini dapat dengan mudah menyebabkan kesalahan.

duplicated.partition.column

Masalah menentukan partisi dengan nama yang sama dalam kueri.

Versi MaxCompute sebelumnya tidak melaporkan error saat Anda menentukan kunci partisi dengan nama yang sama. Sebaliknya, nilai kunci yang terakhir menimpa nilai kunci yang pertama. Perilaku ini dapat dengan mudah menyebabkan kebingungan. MaxCompute V2.0 melaporkan error untuk kasus ini. Contoh berikut menunjukkan masalah tersebut:

Sintaks salah 1:

insert overwrite table partition (ds = '1', ds = '2'）select ... ;

Saat runtime, ds = ‘1’ diabaikan.

Metode yang benar adalah sebagai berikut:

insert overwrite table partition (ds = '2'）select ... ;

Sintaks salah 2:

create table t (a bigint, ds string) partitioned by (ds string);

Prosedur yang benar:

create table t (a bigint) partitioned by (ds string);

order.by.col.ambiguous

Masalah alias duplikat dalam daftar `select` yang kemudian direferensikan oleh klausa `order by`.

Sintaks salah:

select id, id
from table_test 
order by id;

Metode yang benar adalah sebagai berikut:

select id, id id2
from table_name 
order by id;

Hapus alias duplikat sebelum mereferensikannya dalam klausa `order by`.

in.subquery.without.result

Masalah di mana `colx` dilaporkan tidak ada di tabel sumber jika `colx in subquery` tidak mengembalikan hasil.

Sintaks salah:

select * from table_name
where not_exist_col in (select id from table_name limit 0);

Pesan error:

FAILED: ODPS-0130071:[2,7] Semantic analysis exception - column not_exist_col cannot be resolved

ctas.if.not.exists

Masalah sintaks salah untuk tabel tujuan.

Jika tabel tujuan sudah ada, versi MaxCompute sebelumnya tidak melakukan pemeriksaan sintaks. MaxCompute V2.0 melakukan pemeriksaan sintaks normal. Perubahan ini dapat menyebabkan banyak pesan error. Contohnya sebagai berikut:

Sintaks salah:

create table if not exists table_name
as
select * from not_exist_table;

Pesan error:

FAILED: ODPS-0130131:[1,50] Table not found - table meta_dev.not_exist_table cannot be resolved

worker.restart.instance.timeout

Dalam versi MaxCompute sebelumnya, setiap catatan yang dioutput oleh User-Defined Function (UDF) memicu operasi tulis ke sistem file terdistribusi dan mengirim heartbeat ke Fuxi. Jika UDF tidak mengoutput hasil apa pun selama 10 menit, Anda akan menerima pesan error berikut:

FAILED: ODPS-0123144: Fuxi job failed - WorkerRestart errCode:252,errMsg:kInstanceMonitorTimeout, usually caused by bad udf performance.

Kerangka waktu proses MaxCompute V2.0 mendukung vektorisasi. Vektorisasi memproses beberapa baris kolom sekaligus untuk meningkatkan efisiensi eksekusi. Namun, vektorisasi dapat menyebabkan pernyataan yang sebelumnya berjalan tanpa error menjadi timeout. Masalah ini dapat terjadi jika interval antara output dua catatan kurang dari 10 menit. Karena beberapa baris diproses sekaligus, heartbeat mungkin tidak dikirim ke Fuxi tepat waktu.

Jika Anda menghadapi error ini, pertama-tama periksa apakah UDF Anda memiliki masalah performa, seperti setiap catatan membutuhkan beberapa detik untuk diproses. Jika Anda tidak dapat mengoptimalkan performa UDF, Anda dapat mencoba menyelesaikan masalah dengan mengatur ukuran batch baris secara manual. Nilai default adalah 1024.

set odps.sql.executionengine.batch.rowcount=16;

divide.nan.or.overflow

Masalah di mana versi MaxCompute sebelumnya tidak melakukan constant folding untuk pembagian.

Misalnya, untuk pernyataan berikut, rencana eksekusi fisik dalam versi MaxCompute sebelumnya adalah sebagai berikut:

explain
select if(false, 0/0, 1.0)
from table_name;
in task M1_Stg1:
    Data source: meta_dev.table_name
    TS: alias: table_name
      SEL: If(False, Divide(UDFToDouble(0), UDFToDouble(0)), 1.0)
        FS: output: None

Seperti yang Anda lihat, fungsi `IF` dan `Divide` dipertahankan. Saat runtime, ekspresi `Divide` pada parameter kedua tidak dievaluasi karena parameter pertama `IF` bernilai false. Oleh karena itu, tidak terjadi exception pembagian dengan nol.

Namun, MaxCompute V2.0 mendukung constant folding untuk pembagian dan melaporkan error, seperti pada contoh berikut:

Sintaks salah:

select IF(FALSE, 0/0, 1.0)
from table_name;

Pesan error:

FAILED: ODPS-0130071:[1,19] Semantic analysis exception - encounter runtime exception while evaluating function /, detailed message: DIVIDE func result NaN, two params are 0.000000 and 0.000000

Selain error di atas, Anda juga dapat mengalami error overflow. Misalnya:

Sintaks salah:

select if(false, 1/0, 1.0)
from table_name;

Pesan error:

FAILED: ODPS-0130071:[1,19] Semantic analysis exception - encounter runtime exception while evaluating function /, detailed message: DIVIDE func result overflow, two params are 1.000000 and 0.000000

Metode yang benar adalah sebagai berikut:

Hapus penggunaan /0 dan ganti dengan konstanta yang valid.

`CASE WHEN` constant folding memiliki masalah serupa. Misalnya, dalam `CASE WHEN TRUE THEN 0 ELSE 0/0`, MaxCompute V2.0 mengevaluasi semua sub-ekspresi selama constant folding. Hal ini menyebabkan error pembagian dengan nol.

`CASE WHEN` dapat melibatkan skenario optimasi yang lebih kompleks. Misalnya:

select case when key = 0 then 0 else 1/key end
from (
select 0 as key from src
union all
select key from src) r;

Pengoptimal melakukan push-down operasi pembagian ke dalam subkueri. Transformasinya mirip dengan berikut:

M (
select case when 0 = 0 then 0 else 1/0 end c1 from src
UNION ALL
select case when key = 0 then 0 else 1/key end c1 from src) r;

Pesan error:

FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: java.lang.ArithmeticException: DIVIDE func result overflow, two params are 1.000000 and 0.000000

Dalam kasus ini, constant folding untuk klausa pertama `UNION ALL` melaporkan error. Untuk menyelesaikan masalah ini, pindahkan klausa `CASE WHEN` dalam pernyataan SQL ke dalam subkueri, hapus klausa `CASE WHEN` yang tidak perlu, dan hapus penggunaan /0:

select c1 end
from (
select 0 c1 end from src
union all
select case when key = 0 then 0 else 1/key end) r;

small.table.exceeds.mem.limit

Versi MaxCompute sebelumnya mendukung optimasi Multi-way Join. Beberapa join yang menggunakan kunci gabungan yang sama digabungkan menjadi satu task Fuxi. Contohnya adalah `J4_1_2_3_Stg1` dalam kueri berikut:

explain
select t1.*
from t1 join t2 on t1.c1 = t2.c1
join t3 on t1.c1 = t3.c1;

Rencana eksekusi fisik dalam versi MaxCompute sebelumnya:

In Job job0:
root Tasks: M1_Stg1, M2_Stg1, M3_Stg1
J4_1_2_3_Stg1 depends on: M1_Stg1, M2_Stg1, M3_Stg1

In Task M1_Stg1:
    Data source: meta_dev.t1

In Task M2_Stg1:
    Data source: meta_dev.t2

In Task M3_Stg1:
    Data source: meta_dev.t3

In Task J4_1_2_3_Stg1:
    JOIN: t1 INNER JOIN unknown INNER JOIN unknown
        SEL: t1._col0, t1._col1, t1._col2
            FS: output: None

Jika Anda menambahkan petunjuk `MapJoin`, rencana eksekusi fisik dalam versi MaxCompute sebelumnya tidak berubah. Ini menunjukkan bahwa versi MaxCompute sebelumnya memprioritaskan optimasi Multi-way Join dan mungkin mengabaikan petunjuk `MapJoin` yang ditentukan pengguna.

explain
select /* +mapjoin(t1) */ t1.*
from t1 join t2 on t1.c1 = t2.c1
join t3 on t1.c1 = t3.c1;

Rencana eksekusi fisik dalam versi MaxCompute sebelumnya sama dengan rencana sebelumnya.

Pengoptimal MaxCompute V2.0 memprioritaskan petunjuk `MapJoin` yang ditentukan pengguna. Untuk contoh di atas, jika t1 besar, Anda mungkin mengalami error seperti berikut:

FAILED: ODPS-0010000:System internal error - SQL Runtime Internal Error: Hash Join Cursor HashJoin_REL… small table exceeds, memory limit(MB) 640, fixed memory used …, variable memory used …

Dalam kasus ini, jika perilaku `MapJoin` tidak sesuai harapan Anda, Anda dapat menghapus petunjuk `MapJoin`.

sigkill.oom

Serupa dengan masalah `small.table.exceeds.mem.limit`, jika Anda menentukan petunjuk `MapJoin` dan tabel kecil yang ditentukan ternyata besar, kueri dapat berhasil di versi MaxCompute sebelumnya karena dioptimalkan sebagai Multi-way Join. Di MaxCompute V2.0, Anda dapat mengatur odps.sql.mapjoin.memory.max untuk menghindari error yang terjadi karena tabel kecil melebihi batas memori. Namun, setiap worker MaxCompute memiliki batas memori tetap. Jika tabel kecil terlalu besar, worker MaxCompute akan dihentikan karena error kehabisan memori (OOM). Error tersebut mirip dengan berikut:

Fuxi job failed - WorkerRestart errCode:9,errMsg:SigKill(OOM), usually caused by OOM(outof memory).

Dalam kasus ini, Anda dapat menghapus petunjuk `MapJoin` dan menggunakan Multi-way Join.

wm_concat.first.argument.const

Deskripsi `WM_CONCAT` dalam Fungsi agregat selalu mensyaratkan parameter pertama `WM_CONCAT` harus berupa konstanta. Versi MaxCompute sebelumnya tidak ketat dalam pemeriksaan ini. Misalnya, jika tabel sumber tidak memiliki data, tidak ada error yang dilaporkan meskipun parameter pertama `WM_CONCAT` adalah `ColumnReference`.

Function declaration:
string wm_concat(string separator, string str)
Parameter description:
separator: A constant of the String type. This is the separator. Other data types or non-constants will cause an exception.

MaxCompute V2.0 memeriksa validitas parameter selama fase perencanaan. Jika parameter pertama `WM_CONCAT` bukan konstanta, error dilaporkan segera. Contohnya sebagai berikut:

Sintaks salah:

select wm_concat(value, ',') FROM src group by value;

Pesan error:

FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: com.aliyun.odps.lot.cbo.validator.AggregateCallValidator$AggregateCallValidationException: Invalid argument type - The first argument of WM_CONCAT must be constant string.

pt.implicit.convertion.failed

`srcpt` adalah tabel partisi yang memiliki dua partisi:

create table srcpt(key STRING, value STRING) partitioned by (pt STRING);
alter table srcpt add partition (pt='pt1');
alter table srcpt add partition (pt='pt2');

Untuk pernyataan SQL di atas, kolom `pt` bertipe `String` dan konstanta bertipe `INT` keduanya dikonversi ke tipe `DOUBLE` untuk perbandingan. Bahkan jika proyek diatur ke odps.sql.udf.strict.mode=true, versi MaxCompute sebelumnya tidak melaporkan error. Semua nilai `pt` difilter keluar. MaxCompute V2.0 melaporkan error. Contohnya sebagai berikut:

Sintaks salah:

select key from srcpt where pt in (1, 2);

Pesan error:

FAILED: ODPS-0130071:[0,0] Semantic analysis exception - physical plan generation failed: java.lang.NumberFormatException: ODPS-0123091:Illegal type cast - In function cast, value 'pt1' cannot be casted from String to Double.

Hindari membandingkan kolom kunci partisi `STRING` dengan konstanta bertipe `INT`. Anda harus mengubah konstanta bertipe `INT` menjadi tipe `STRING`.

having.use.select.alias

Spesifikasi SQL menetapkan bahwa klausa `GROUP BY` dan `HAVING` diproses sebelum klausa `SELECT`. Oleh karena itu, klausa `HAVING` tidak dapat menggunakan alias kolom yang dihasilkan oleh klausa `SELECT`.

dynamic.pt.to.static

Deskripsi: Di MaxCompute V2.0, partisi dinamis kadang-kadang dikonversi menjadi partisi statis oleh pengoptimal.

insert overwrite table srcpt partition(pt) select id, 'pt1' from table_name;

dikonversi menjadi

insert overwrite table srcpt partition(pt='pt1') select id from table_name;

Jika Anda menentukan nilai partisi yang tidak valid, seperti penggunaan salah '${bizdate}', MaxCompute V2.0 melaporkan error selama fase pemeriksaan sintaks. Untuk informasi lebih lanjut, lihat Partisi.

Sintaks salah:

insert overwrite table srcpt partition(pt) select id, '${bizdate}' from table_name limit 0;

Pesan error:

FAILED: ODPS-0130071:[1,24] Semantic analysis exception - wrong columns count 2 in data source, requires 3 columns (includes dynamic partitions if any)

Dalam versi MaxCompute sebelumnya, pernyataan SQL tidak mengoutput data apa pun karena klausa `LIMIT 0`, dan tidak ada partisi dinamis yang dibuat. Oleh karena itu, tidak ada error yang dilaporkan.

lot.not.in.subquery

Deskripsi: Masalah ini terkait cara penanganan nilai `NULL` dalam subkueri `IN`.

Dalam operasi `IN` SQL standar, jika daftar nilai berisi `NULL`, nilai kembali bukan `false`. Nilai kembali hanya bisa `NULL` atau `true`. Misalnya, `1 in (null, 1, 2, 3)` mengembalikan `true`, `1 in (null, 2, 3)` mengembalikan `NULL`, dan `null in (null, 1, 2, 3)` mengembalikan `NULL`. Demikian pula, untuk operasi `NOT IN`, jika daftar berisi `NULL`, nilai kembali hanya `false` atau `NULL`, dan tidak pernah `true`.

MaxCompute V2.0 menangani masalah ini berdasarkan perilaku SQL standar. Jika Anda menerima pengingat ini, periksa kueri Anda untuk menentukan apakah subkueri dalam operasi `IN` dapat mengembalikan nilai null. Periksa apakah perilaku tersebut sesuai harapan Anda saat nilai null muncul. Jika tidak, lakukan modifikasi yang diperlukan.