：FAQ tentang operasi DQL

Apa yang harus saya lakukan jika pesan kesalahan "Repeated key in GROUP BY" muncul saat saya mengeksekusi pernyataan SQL MaxCompute?

• Deskripsi Masalah

  Saat mengeksekusi pernyataan SQL MaxCompute, pesan kesalahan berikut muncul:

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

• Penyebab

  SELECT DISTINCT tidak dapat diikuti oleh konstanta.

• Solusi

  Pisahkan logika eksekusi pernyataan SQL menjadi dua lapisan. Logika DISTINCT tanpa konstanta diproses di lapisan dalam, dan data konstan ditambahkan ke lapisan luar.

Apa yang harus saya lakukan jika pesan kesalahan "Expression not in GROUP BY key" muncul saat saya mengeksekusi pernyataan SQL MaxCompute?

• Deskripsi Masalah

  Saat mengeksekusi pernyataan SQL MaxCompute, pesan kesalahan berikut muncul:

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

• Penyebab

  Kolom yang tidak ditentukan dalam klausa GROUP BY tidak dapat dirujuk secara langsung. Untuk informasi lebih lanjut, lihat GROUP BY (col_list).

• Solusi

  Ubah pernyataan SQL untuk memastikan bahwa kolom yang dikueri menggunakan SELECT adalah kolom yang ditentukan dalam klausa GROUP BY atau kolom yang diproses menggunakan fungsi agregat seperti SUM atau COUNT.

Tabel B dihasilkan setelah saya menjalankan GROUP BY pada Tabel A. Jumlah baris di Tabel B lebih sedikit daripada jumlah baris di Tabel A, tetapi kapasitas penyimpanan fisik Tabel B sepuluh kali lipat dari kapasitas penyimpanan fisik Tabel A. Mengapa?

Di MaxCompute, data disimpan dalam mode kompresi kolom. Jika data yang berdekatan di kolom yang sama serupa, rasio kompresi data tinggi. Jika odps.sql.groupby.skewindata diatur ke true, data tersebar dan rasio kompresi data rendah. Untuk mendapatkan rasio kompresi data yang tinggi, Anda dapat mengurutkan data tertentu saat mengeksekusi pernyataan SQL untuk menulis data.

Apakah kinerja kueri terpengaruh ketika saya menggunakan GROUP BY untuk mengkueri 10 miliar catatan data? Apakah jumlah data dibatasi ketika saya menggunakan GROUP BY untuk mengkueri data?

Tidak, kinerja kueri tidak terpengaruh. Jumlah data tidak dibatasi saat menggunakan GROUP BY untuk mengkueri data. Untuk informasi lebih lanjut tentang GROUP BY, lihat GROUP BY (col_list).

Setelah saya mengkueri data di MaxCompute, bagaimana hasil kueri diurutkan?

Data tabel MaxCompute disusun secara acak. Jika Anda tidak mengonfigurasi pengaturan urutan, data juga akan dikembalikan secara acak.

Jika ingin mendapatkan data yang diurutkan, tentukan order by xx limit n dalam pernyataan SQL untuk mengurutkan data.

Untuk mengurutkan seluruh data, atur n setelah limit ke nilai total jumlah catatan data + 1.

Apakah MaxCompute mendukung ORDER BY FIELD NULLS LAST?

Ya, MaxCompute mendukung ORDER BY FIELD NULLS LAST. Untuk informasi lebih lanjut tentang sintaks yang didukung oleh MaxCompute, lihat Perbedaan dalam dukungan untuk pernyataan SQL.

Apa yang harus saya lakukan jika pesan kesalahan "ORDER BY must be used with a LIMIT clause" muncul saat saya mengeksekusi pernyataan SQL MaxCompute?

• Deskripsi Masalah

  Saat mengeksekusi pernyataan SQL MaxCompute, pesan kesalahan berikut muncul:

  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

  Klausa ORDER BY perlu mengurutkan semua data dari satu node tunggal. Secara default, klausa ORDER BY digunakan bersama dengan klausa LIMIT untuk mencegah satu node memproses sejumlah besar data.

• Solusi

  Anda dapat menghapus batasan pelaksanaan simultan klausa ORDER BY dan LIMIT untuk proyek atau sesi.

  • Untuk menghapus batasan untuk proyek, jalankan perintah setproject odps.sql.validate.orderby.limit=false;.

  • Untuk menghapus batasan untuk sesi, kirim dan jalankan perintah set odps.sql.validate.orderby.limit=false; dengan pernyataan SQL yang ingin dieksekusi.

    Catatan

    Jika satu node memiliki sejumlah besar data untuk diurutkan setelah menghapus batasan, sejumlah besar sumber daya dan waktu akan dikonsumsi.

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

Ketika saya mengeksekusi pernyataan SQL MaxCompute dengan NOT IN yang diikuti oleh subquery, subquery tersebut diharapkan mengembalikan puluhan ribu catatan data. Namun, jika subquery yang mengikuti IN atau NOT IN mengembalikan data partisi, jumlah maksimum catatan data yang dapat dikembalikan adalah 1.000. Bagaimana cara memastikan bahwa subquery mengembalikan jumlah catatan data yang diharapkan dan logika NOT IN diterapkan?

select * from a where a.ds not in (select ds from b);
Ganti pernyataan sebelumnya dengan pernyataan 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 saling terkait?

Anda dapat menggunakan operasi UNION ALL untuk menyelesaikan penggabungan vertikal dan menggunakan fungsi row_number untuk menyelesaikan penggabungan horizontal. Tambahkan kolom ID ke kedua tabel, asosiasikan tabel menggunakan kolom ID, dan kemudian pilih bidang yang diperlukan. Untuk informasi lebih lanjut, lihat UNION atau ROW_NUMBER.

Apa yang harus saya lakukan jika pesan kesalahan "ValidateJsonSize error" muncul saat saya melakukan operasi UNION ALL?

• Deskripsi Masalah

  Saat mengeksekusi pernyataan SQL select count(1) as co from client_table union all ... yang berisi 200 operasi UNION ALL, pesan kesalahan berikut muncul:

  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: Panjang rencana eksekusi melebihi 1024 KB, yang merupakan ukuran maksimum yang diizinkan oleh arsitektur dasar. Akibatnya, kesalahan eksekusi SQL dikembalikan. Panjang rencana eksekusi tidak secara langsung terkait dengan panjang pernyataan SQL. Oleh karena itu, panjang rencana eksekusi tidak dapat diperkirakan.

  • Penyebab 2: Jumlah partisi terlalu besar.

  • Penyebab 3: Jumlah file kecil terlalu besar.

• Solusi

  • Solusi untuk Penyebab 1: Pisahkan pernyataan SQL yang terlalu panjang untuk mencegah rencana eksekusi yang dihasilkan melebihi panjang maksimum.

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

  • Solusi untuk Penyebab 3: Gabungkan file kecil.

Apa yang harus saya lakukan jika pesan kesalahan "Both left and right aliases encountered in JOIN" muncul saat saya melakukan operasi JOIN?

• Deskripsi Masalah

  Saat mengeksekusi pernyataan SQL MaxCompute, pesan kesalahan berikut muncul:

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

• Penyebab

  • Penyebab 1: Kondisi ON dalam pernyataan SQL mencakup non-equi join, seperti table1.c1>table2.c3.

  • Penyebab 2: Data di salah satu sisi kondisi JOIN dalam pernyataan SQL berasal dari dua tabel, seperti table1.col1 = concat(table1.col2,table2.col3).

• Solusi

  • Solusi untuk Penyebab 1: Ubah non-equi join dalam kondisi ON dalam pernyataan SQL menjadi equi-join.

    Catatan

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

  • Solusi untuk Penyebab 2: Jika salah satu tabel kecil, gunakan metode MAPJOIN.

Apa yang harus saya lakukan jika pesan kesalahan "Maximum 16 join inputs allowed" muncul saat saya melakukan operasi JOIN?

• Deskripsi Masalah

  Saat mengeksekusi pernyataan SQL MaxCompute, pesan kesalahan berikut muncul:

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

• Penyebab

  Pernyataan SQL MaxCompute dapat melakukan MAPJOIN pada maksimal enam tabel kecil dan dapat bergabung berturut-turut hingga 16 tabel.

• Solusi

  Gabungkan beberapa tabel kecil menjadi tabel sementara sebagai tabel input untuk mengurangi jumlah tabel input.

Apa yang harus saya lakukan jika jumlah catatan data yang dikembalikan lebih besar daripada jumlah catatan data di salah satu tabel sumber setelah saya melakukan operasi JOIN?

• Deskripsi Masalah

  Setelah mengeksekusi pernyataan SQL MaxCompute berikut, jumlah catatan data yang dikembalikan lebih besar daripada jumlah catatan data di tabel table1.

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

• Penyebab

  Left outer join mengembalikan semua data dari table1, bahkan jika tidak ditemukan entri yang cocok di table2. Jika ada ID duplikat di table2, ini akan mengakibatkan peningkatan jumlah baris yang dikembalikan dalam set hasil.

  Tabel berikut memberikan data sampel di table1.

  id	values
  1	a
  1	b
  2	c

  Tabel berikut memberikan data sampel di table2.

  id	values
  1	A
  1	B
  3	D

  Tabel berikut mencantumkan hasil yang dikembalikan setelah select count(*) from table1 a left outer join table2 b on a.ID = b.ID; dieksekusi.

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

  • Kedua tabel memiliki data yang nilainya pada kolom ID adalah 1. Oleh karena itu, operasi Produk Kartesius dilakukan dan empat catatan data dikembalikan.

  • Hanya table1 yang memiliki data yang nilainya pada kolom ID adalah 2. Oleh karena itu, satu catatan data dikembalikan.

  • Hanya table2 yang memiliki data yang nilainya pada kolom ID adalah 3. Oleh karena itu, tidak ada data yang dikembalikan.

• Solusi

  Periksa apakah ada data duplikat di table2. Contoh pernyataan:

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

  Jika Anda tidak ingin melakukan operasi Produk Kartesius, Anda dapat menggunakan pernyataan berikut:

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

Saya menentukan kondisi partisi saat saya melakukan operasi JOIN, tetapi sistem memperingatkan bahwa pemindaian tabel penuh dilarang. Mengapa?

• Deskripsi Masalah

  Saat mengeksekusi pernyataan berikut di dua proyek, pernyataan tersebut berhasil dieksekusi hanya di salah satu proyek.

  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; 

  Pesan kesalahan berikut muncul:

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

• Penyebab

  Saat mengeksekusi pernyataan SELECT, kondisi partisi harus ditentukan menggunakan klausa WHERE, karena penggunaan klausa ON untuk tujuan ini tidak standar.

  Perintah set odps.sql.outerjoin.supports.filters=false dijalankan di proyek tempat pernyataan berhasil dieksekusi. Konfigurasi ini mengubah kondisi dalam klausa ON menjadi kondisi filter untuk mengizinkan pernyataan SQL non-standar. Konfigurasi ini kompatibel dengan sintaks Hive tetapi tidak sesuai dengan standar SQL.

• Solusi

  Kami sarankan Anda meletakkan kondisi filter partisi dalam klausa WHERE.

Ketika saya melakukan operasi JOIN, apakah pemangkasan partisi berlaku jika kondisi pemangkasan partisi ditentukan dalam klausa ON atau klausa WHERE?

• Pemangkasan partisi berlaku jika kondisi pemangkasan partisi ditentukan dalam klausa WHERE.

• Jika kondisi pemangkasan partisi ditentukan dalam klausa ON, pemangkasan partisi berlaku pada tabel sekunder. Pemangkasan partisi tidak berlaku pada tabel utama, sehingga pemindaian tabel penuh dipicu.

Untuk informasi lebih lanjut tentang pemangkasan partisi, lihat Periksa apakah pemangkasan partisi efektif.

Bagaimana cara menggunakan MAPJOIN untuk menyimpan cache beberapa tabel kecil?

MAPJOIN adalah teknik optimasi yang mempercepat kueri dengan menyimpan cache tabel kecil ke dalam memori. Anda dapat menentukan alias tabel yang ingin disimpan dalam pernyataan 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
join 
(
  select count(*) as cnt from iris
) c;              

Bisakah saya menukar tabel besar dan tabel kecil yang ditentukan dalam pernyataan MAPJOIN?

Ya, Anda bisa. Tabel besar dan tabel kecil dalam pernyataan MAPJOIN dibedakan berdasarkan ukuran ruang yang digunakan oleh setiap tabel. Sistem memuat semua data dalam tabel kecil yang ditentukan ke dalam memori untuk mempercepat operasi JOIN.

Setelah saya mengonfigurasi kondisi filter dalam pernyataan SQL MaxCompute, muncul pesan kesalahan yang menunjukkan bahwa ukuran data input melebihi 100 GB. Apa yang harus saya lakukan?

Filter bidang partisi untuk mengambil data, lalu filter bidang non-partisi lainnya. Ukuran tabel input bergantung pada jumlah data setelah memfilter bidang partisi.

Apakah kondisi WHERE dalam kueri fuzzy di MaxCompute SQL mendukung ekspresi reguler?

Ya, kondisi WHERE dalam kueri fuzzy di MaxCompute SQL mendukung ekspresi reguler. Sebagai contoh, select * from user_info where address rlike '[0-9]{9}'; menunjukkan bahwa ID yang terdiri dari sembilan digit dicari.

Jika saya ingin menyinkronkan hanya 100 catatan data, bagaimana cara menggunakan LIMIT untuk menentukan jumlah catatan data yang ingin saya sinkronkan dalam klausa WHERE?

LIMIT tidak dapat digunakan dalam klausa WHERE. Anda dapat mengeksekusi pernyataan SQL untuk membaca 100 catatan data sebelum menyinkronkan data.

Bagaimana cara meningkatkan efisiensi kueri? Bisakah saya menyesuaikan pengaturan partisi?

Jika Anda menggunakan bidang partisi untuk mempartisi tabel, pemindaian tabel penuh tidak dipicu ketika partisi ditambahkan atau ketika data partisi diperbarui atau dibaca. Ini meningkatkan efisiensi pemrosesan data. Untuk informasi lebih lanjut, lihat Operasi tabel.

Apakah MaxCompute SQL mendukung pernyataan WITH AS?

Ya, MaxCompute SQL mendukung pernyataan WITH AS. MaxCompute mendukung ekspresi tabel umum (CTE) yang sesuai dengan SQL untuk meningkatkan keterbacaan dan efisiensi eksekusi pernyataan SQL. Untuk informasi lebih lanjut, lihat Ekspresi Tabel Umum (CTE).

Bagaimana cara membagi satu baris data menjadi beberapa baris?

Anda dapat menggunakan Lateral View dengan fungsi pembuatan tabel, seperti SPLIT dan EXPLODE, untuk membagi satu baris data menjadi beberapa baris data dan menggabungkan data yang telah dibagi.

Setelah saya menentukan use_instance_tunnel=false dan instance_tunnel_max_record=10 dalam file odps_config.ini klien, pernyataan SELECT masih menghasilkan sejumlah besar catatan keluaran. Mengapa?

Untuk menggunakan instance_tunnel_max_record untuk mengontrol jumlah catatan keluaran, Anda harus mengubah use_instance_tunnel=false menjadi use_instance_tunnel=true.

Bagaimana cara menggunakan ekspresi reguler untuk menentukan apakah nilai suatu bidang dalam bahasa Cina?

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