MaxCompute:FAQ tentang operasi DQL

Bagaimana cara mengatasi error "Repeated key in GROUP BY" yang muncul saat menjalankan pernyataan SQL MaxCompute?

• Masalah

  Saat menjalankan pernyataan SQL MaxCompute, error berikut dikembalikan:

  FAILED: ODPS-0130071:Semantic analysis exception - Repeated key in GROUP BY.

• Penyebab

  Konstanta digunakan setelah `SELECT DISTINCT`, yang tidak diperbolehkan.

• Solusi

  Pisahkan pernyataan SQL menjadi dua lapisan. Lapisan dalam menangani logika `DISTINCT` tanpa konstanta, sedangkan lapisan luar menambahkan data konstan.

Bagaimana cara mengatasi error "Expression not in GROUP BY key" yang muncul saat menjalankan pernyataan SQL MaxCompute?

• Masalah

  Saat menjalankan pernyataan SQL MaxCompute, error berikut dikembalikan:

  FAILED: ODPS-0130071:Semantic analysis exception - Expression not in GROUP BY key : line 1:xx ‘xxx’

• Penyebab

  Kolom yang tidak termasuk dalam klausa `GROUP BY` direferensikan secara langsung. Untuk informasi lebih lanjut, lihat GROUP BY clause (col_list).

• Solusi

  Pastikan kolom dalam daftar `SELECT` merupakan bagian dari klausa `GROUP BY` atau telah diproses oleh fungsi agregat seperti `SUM` atau `COUNT`.

Saya menjalankan GROUP BY pada Tabel A untuk menghasilkan Tabel B. Tabel B memiliki jumlah baris lebih sedikit daripada Tabel A, tetapi penyimpanan fisiknya 10 kali lebih besar. Mengapa hal ini terjadi?

MaxCompute menggunakan kompresi kolom untuk penyimpanan. Jika nilai-nilai berurutan dalam kolom yang sama mirip, rasio kompresinya tinggi. Saat odps.sql.groupby.skewindata=true diaktifkan, data tersebar sehingga rasio kompresi menjadi lebih rendah. Untuk meningkatkan kompresi, lakukan pengurutan lokal saat menulis data dengan pernyataan SQL.

Apakah menjalankan kueri GROUP BY pada 10 miliar catatan data memengaruhi performa? Apakah ada batasan volume data untuk GROUP BY?

Tidak. `GROUP BY` tidak memiliki batasan volume data.

Bagaimana pengurutan data yang dikembalikan oleh kueri MaxCompute?

Data dibaca dari tabel MaxCompute dalam urutan yang tidak terdefinisi. Tanpa klausa pengurutan, hasil kueri juga tidak terurut.

Untuk mengurutkan data, tambahkan klausa order by xx limit n ke pernyataan SQL Anda.

Untuk pengurutan penuh, atur nilai limit n menjadi jumlah total catatan + 1.

Apakah MaxCompute mendukung sintaks ORDER BY FIELD NULLS LAST?

MaxCompute mendukung sintaks ini. Untuk informasi lebih lanjut, lihat Perbedaan dari sintaks SQL lainnya.

Bagaimana cara mengatasi error "ORDER BY must be used with a LIMIT clause" yang muncul saat menjalankan pernyataan SQL MaxCompute?

• Masalah

  Saat menjalankan pernyataan SQL MaxCompute, error berikut dikembalikan:

  FAILED: ODPS-0130071:[1,27] Semantic analysis exception - ORDER BY must be used with a LIMIT clause, please set odps.sql.validate.orderby.limit=false to use it.

• Penyebab

  `ORDER BY` melakukan pengurutan global pada satu node eksekusi, sehingga klausa `LIMIT` diperlukan secara default untuk mencegah pemrosesan data berlebihan pada node tersebut.

• Solusi

  Jika skenario Anda memerlukan `ORDER BY` tanpa klausa `LIMIT`, nonaktifkan persyaratan ini dengan salah satu cara berikut:

  • Pada tingkat proyek: Jalankan perintah setproject odps.sql.validate.orderby.limit=false; untuk menonaktifkan persyaratan bahwa order by harus digunakan dengan klausa limit.

  • Pada tingkat sesi: Jalankan perintah set odps.sql.validate.orderby.limit=false; untuk menonaktifkan persyaratan bahwa order by harus digunakan dengan klausa limit. Perintah ini harus dikirim bersamaan dengan pernyataan SQL.

    Catatan

    Menonaktifkan persyaratan order by-limit berarti melakukan pengurutan dataset besar pada satu node eksekusi, yang menurunkan performa dan meningkatkan konsumsi resource.

Untuk informasi lebih lanjut tentang `ORDER BY`, lihat ORDER BY clause (ORDER_condition).

Saat saya menjalankan pernyataan SQL MaxCompute dengan subquery NOT IN, subquery tersebut mengembalikan puluhan ribu catatan data. Namun, jika subquery setelah IN atau NOT IN mengembalikan partisi, jumlah maksimum partisi yang dikembalikan adalah 1.000. Bagaimana cara mengimplementasikan kueri ini jika saya harus menggunakan NOT IN?

select * from a where a.ds not in (select ds from b);
Ubah pernyataan menjadi sebagai berikut:
select a.* from a left outer join (select distinct ds from b) bb on a.ds=bb.ds where bb.ds is null;              

Bagaimana cara menggabungkan dua tabel yang tidak memiliki hubungan?

Untuk penggabungan vertikal, gunakan union all. Untuk penggabungan horizontal, gunakan fungsi row_number untuk menambahkan kolom ID ke kedua tabel, gabungkan berdasarkan ID tersebut, lalu pilih bidang yang diperlukan. Untuk informasi lebih lanjut, lihat Union atau ROW_NUMBER.

Bagaimana cara mengatasi error "ValidateJsonSize error" yang terjadi selama operasi UNION ALL?

• Gejala

  Saat menjalankan pernyataan SQL yang berisi 200 operasi UNION ALL, seperti select count(1) as co from client_table union all ..., error berikut terjadi:

  FAILED: build/release64/task/fuxiWrapper.cpp(344): ExceptionBase: Submit fuxi Job failed, {
      "ErrCode": "RPC_FAILED_REPLY",
      "ErrMsg": "exception: ExceptionBase:build/release64/fuxi/fuximaster/fuxi_master.cpp(1018): ExceptionBase: StartAppFail: ExceptionBase:build/release64/fuxi/fuximaster/app_master_mgr.cpp(706): ExceptionBase: ValidateJsonSize error: the size of compressed plan is larger than 1024KB\nStack      

• Penyebab

  • Penyebab 1: Rencana eksekusi melebihi batas 1024 KB dari arsitektur dasar. Panjang rencana eksekusi tidak berbanding lurus dengan panjang pernyataan SQL dan tidak dapat diperkirakan sebelumnya.

  • Penyebab 2: Jumlah partisi terlalu besar.

  • Penyebab 3: Terlalu banyak file kecil.

• Solusi

  • Solusi untuk Penyebab 1: Pisahkan pernyataan SQL panjang agar tidak melebihi batas panjang.

  • Solusi untuk Penyebab 2: Sesuaikan jumlah partisi. Untuk informasi lebih lanjut, lihat Partition.

  • Solusi untuk Penyebab 3: Gabungkan file-file kecil tersebut.

Bagaimana cara mengatasi error "Both left and right aliases encountered in JOIN" yang terjadi selama operasi JOIN?

• Masalah

  Saat menjalankan pernyataan SQL MaxCompute, error berikut dikembalikan:

  FAILED: ODPS-0130071:Semantic analysis exception - Both left and right aliases encountered in JOIN : line 3:3 ‘xx’: . I f you really want to perform this join, try mapjoin

• Penyebab

  • Penyebab 1: Non-equi-join ditentukan dalam klausa ON, seperti table1.c1>table2.c3.

  • Penyebab 2: Salah satu sisi kondisi JOIN mereferensikan kolom dari kedua tabel, seperti table1.col1 = concat(table1.col2,table2.col3).

• Solusi

  • Solusi untuk Penyebab 1: Ubah pernyataan SQL. Kondisi join harus berupa equi-join.

    Catatan

    Jika Anda harus menggunakan non-equi-join, Anda dapat menambahkan petunjuk mapjoin. Untuk informasi lebih lanjut, lihat ODPS-0130071.

  • Solusi untuk Penyebab 2: Jika salah satu tabel berukuran kecil, Anda dapat menggunakan metode MAPJOIN.

Bagaimana cara mengatasi error "Maximum 16 join inputs allowed" yang terjadi selama operasi JOIN?

• Gejala

  Saat menjalankan pernyataan SQL MaxCompute, error berikut dikembalikan:

  FAILED: ODPS-0123065:Join exception - Maximum 16 join inputs allowed

• Penyebab

  Dalam SQL MaxCompute, operasi MAPJOIN mendukung maksimal enam tabel kecil, dan satu operasi JOIN mendukung maksimal 16 tabel.

• Solusi

  Gabungkan beberapa tabel kecil menjadi tabel sementara terlebih dahulu. Hal ini mengurangi jumlah tabel masukan.

Selama operasi JOIN, jumlah catatan data dalam hasil lebih besar daripada jumlah catatan di tabel asli. Bagaimana cara memperbaikinya?

• Gejala

  Setelah menjalankan pernyataan SQL MaxCompute berikut, jumlah catatan dalam hasil kueri lebih besar daripada jumlah catatan di table1.

  select count(*) from table1 a left outer join table2 b on a.ID = b.ID;

• Penyebab

  Left outer join mengembalikan semua catatan dari table1, bahkan jika tidak ada catatan yang cocok di table2. Jika table2 berisi ID duplikat, jumlah catatan yang dikembalikan meningkat. Contohnya:

  Asumsikan `table1` berisi data berikut.

  id	values
  1	a
  1	b
  2	c

  Asumsikan `table2` berisi data berikut.

  id	values
  1	A
  1	B
  3	D

  Perintah select count(*) from table1 a left outer join table2 b on a.ID = b.ID; mengembalikan hasil berikut.

  id1	values1	id2	values2
  1	b	1	B
  1	b	1	A
  1	a	1	B
  1	a	1	A
  2	c	NULL	NULL

  • Catatan dengan `id=1` ada di kedua tabel. Produk Kartesius dilakukan, dan empat catatan dikembalikan.

  • Catatan dengan `id=2` hanya ada di `table1`. Satu catatan dikembalikan.

  • Catatan dengan `id=3` hanya ada di `table2`. Tidak ada catatan yang dikembalikan karena tidak ada catatan yang cocok di `table1`.

• Solusi

  Periksa apakah `table2` berisi ID duplikat:

  select id, count(*) as cnt from table2 group by id having cnt>1 limit 10;

  Untuk menghindari Produk Kartesius, tulis ulang pernyataan SQL sebagai berikut:

  select * from table1 a left outer join (select distinct id from table2) b on a.id = b.id;

Mengapa pemindaian tabel penuh dilarang dalam operasi JOIN meskipun kondisi partisi telah ditentukan?

• Masalah

  Saat kode yang sama dieksekusi di dua proyek, eksekusi berhasil di satu proyek tetapi gagal di proyek lain.

  select t.stat_date 
  from fddev.tmp_001 t  
  left outer join (select '20180830' as ds from fddev.dual ) t1 
  on t.ds = 20180830
  group by t.stat_date; 

  Eksekusi yang gagal melaporkan error berikut:

  Table(fddev,tmp_001) is full scan with all partitions,please specify partitions predicates.

• Penyebab

  Saat melakukan operasi SELECT, kondisi partisi harus berada di klausa WHERE. Menggunakan klausa ON untuk tujuan ini tidak sesuai standar.

  Eksekusi berhasil di satu proyek karena dikonfigurasi dengan perintah set odps.sql.outerjoin.supports.filters=false. Perintah ini mengubah kondisi di klausa ON menjadi kondisi filter. Perilaku ini kompatibel dengan sintaks Hive tetapi tidak sesuai dengan standar SQL.

• Solusi

  Letakkan kondisi filter partisi di klausa WHERE.

Untuk operasi JOIN, apakah pemangkasan partisi berlaku jika kondisi pemangkasan partisi berada di klausa ON atau di klausa WHERE?

• Jika kondisi pemangkasan partisi berada di klausa WHERE, pemangkasan partisi berlaku.

• Jika kondisi berada di klausa ON, pemangkasan partisi berlaku pada tabel detail tetapi tidak pada tabel utama. Hal ini mengakibatkan pemindaian tabel penuh pada tabel utama.

Untuk informasi lebih lanjut tentang pemangkasan partisi, lihat Evaluasi validitas pemangkasan partisi.

Bagaimana cara menggunakan MAPJOIN untuk menyimpan cache beberapa tabel kecil?

MAPJOIN mempercepat kueri dengan menyimpan cache tabel kecil di memori. Tentukan alias tabel dalam petunjuk MAPJOIN.

+——————————————————————————————————————————+

| Field           | Type       | Label | Comment                                     |
+——————————————————————————————————————————+

| sepal_length    | double     |       |                                             |

| sepal_width     | double     |       |                                             |

| petal_length    | double     |       |                                             |

| petal_width     | double     |       |                                             |

| category        | string     |       |                                             |

+——————————————————————————————————————————+
                

select 
  /*+ mapjoin(b,c) */
  a.category,
  b.cnt as cnt_category,
  c.cnt as cnt_all
from iris a
join
(
  select count(*) as cnt,category from iris group by category
) b
on a.category = b.category
cross join 
(
  select count(*) as cnt from iris
) c;              

Apakah tabel besar dan kecil dalam MAPJOIN dapat ditukar?

Ya. Sistem membedakan antara tabel besar dan kecil berdasarkan ukuran penyimpanan dan memuat tabel kecil ke memori untuk mempercepat operasi JOIN.

Setelah saya menetapkan kondisi filter untuk pernyataan SQL MaxCompute, muncul error yang menunjukkan bahwa data masukan melebihi 100 GB. Bagaimana cara memperbaikinya?

Filter data berdasarkan bidang partisi terlebih dahulu, lalu berdasarkan bidang non-partisi lainnya. Volume data masukan dihitung setelah pemfilteran tingkat partisi.

Apakah klausa WHERE untuk kueri fuzzy di SQL MaxCompute mendukung ekspresi reguler?

Ya. Contohnya, select * from user_info where address rlike '[0-9]{9}'; menemukan catatan yang berisi angka sembilan digit.

Jika saya hanya ingin menyinkronkan 100 catatan data, bagaimana cara menggunakan LIMIT dalam kondisi filter WHERE?

LIMIT tidak didukung dalam kondisi filter. Gunakan pernyataan SQL untuk memilih 100 catatan terlebih dahulu, lalu lakukan operasi sinkronisasi.

Bagaimana cara meningkatkan efisiensi kueri? Apakah saya dapat menyesuaikan pengaturan partisi?

Partisi tabel berdasarkan bidang partisinya untuk memungkinkan penambahan, pembaruan, atau pembacaan data di partisi tertentu tanpa pemindaian tabel penuh. Untuk informasi lebih lanjut, lihat Operasi tabel.

Apakah SQL MaxCompute mendukung pernyataan WITH AS?

Ya. MaxCompute mendukung Common Table Expressions (CTEs) SQL standar untuk meningkatkan keterbacaan dan efisiensi eksekusi. Untuk informasi lebih lanjut, lihat COMMON TABLE EXPRESSION (CTE).

Bagaimana cara membagi satu baris data menjadi beberapa baris?

Gunakan LATERAL VIEW dengan fungsi pembuat tabel seperti Split dan Explode untuk membagi satu baris menjadi beberapa baris, lalu agregasikan data yang dihasilkan.

Di file odps_config.ini pada client, saya mengatur use_instance_tunnel=false dan instance_tunnel_max_record=10. Mengapa pernyataan SELECT tetap mengeluarkan banyak catatan?

Ubah use_instance_tunnel=false menjadi use_instance_tunnel=true agar pengaturan instance_tunnel_max_record berlaku.

Bagaimana cara menggunakan ekspresi reguler untuk menentukan apakah suatu bidang berisi karakter Tionghoa?

select 'field' rlike '[\\x{4e00}-\\x{9fa5}]+';