Memecahkan masalah kueri

Dokumen ini dimaksudkan untuk membantu Anda memecahkan masalah umum terkait menjalankan kueri, seperti mengidentifikasi alasan kueri lambat, atau memberikan langkah-langkah penyelesaian untuk error umum yang ditampilkan oleh kueri yang gagal.

Memecahkan masalah kueri lambat

Saat memecahkan masalah performa kueri yang lambat, pertimbangkan penyebab umum berikut:

  1. Periksa halaman Google Cloud Kesehatan Layanan untuk mengetahui apakah ada gangguan layanan BigQuery yang diketahui dan dapat memengaruhi performa kueri.

  2. Tinjau linimasa tugas untuk kueri Anda di halaman detail tugas untuk melihat durasi setiap tahap kueri dijalankan.

    • Jika sebagian besar waktu yang berlalu disebabkan oleh waktu pembuatan yang lama, hubungi Cloud Customer Care untuk mendapatkan bantuan.

    • Jika sebagian besar waktu yang berlalu disebabkan oleh waktu eksekusi yang lama, maka tinjau insight performa kueri Anda. Insight performa kueri dapat memberi tahu Anda jika kueri berjalan lebih lama daripada waktu eksekusi rata-rata, dan menyarankan kemungkinan penyebabnya. Kemungkinan penyebabnya dapat mencakup pertentangan slot kueri atau kuota pengacakan yang tidak memadai. Untuk mengetahui informasi selengkapnya tentang setiap masalah performa kueri dan kemungkinan solusinya, lihat Menafsirkan insight performa kueri.

  3. Tinjau kolom finalExecutionDurationMs di JobStatistics untuk tugas kueri Anda. Kueri mungkin telah dicoba lagi. Kolom finalExecutionDurationMs berisi durasi dalam milidetik eksekusi upaya terakhir tugas ini.

  4. Tinjau byte yang diproses di halaman detail tugas kueri untuk melihat apakah lebih tinggi dari yang diharapkan. Anda dapat melakukannya dengan membandingkan jumlah byte yang diproses oleh kueri saat ini dengan tugas kueri lain yang selesai dalam jangka waktu yang dapat diterima. Jika ada perbedaan besar pada byte yang diproses antara kedua kueri, mungkin kueri lambat karena volume data yang besar. Untuk mengetahui informasi tentang cara mengoptimalkan kueri Anda untuk menangani volume data yang besar, lihat Mengoptimalkan komputasi kueri.

    Anda juga dapat mengidentifikasi kueri dalam project yang memproses sejumlah besar data dengan menelusuri kueri paling mahal menggunakan tampilan INFORMATION_SCHEMA.JOBS.

Jika Anda masih tidak dapat menemukan alasan untuk menjelaskan performa kueri yang lebih lambat dari yang diharapkan, hubungi Customer Care Cloud untuk mendapatkan bantuan.

Resolusi skema Avro

String error: Cannot skip stream

Error ini dapat terjadi saat memuat beberapa file Avro dengan skema yang berbeda, yang mengakibatkan masalah resolusi skema dan menyebabkan tugas impor gagal dalam file acak.

Untuk mengatasi error ini, pastikan file alfabet terakhir dalam tugas pemuatan berisi superset (union) skema yang berbeda. Hal ini adalah persyaratan berdasarkan cara Avro menangani resolusi skema.

Kueri serentak yang bertentangan

String error: Concurrent jobs in the same session are not allowed

Error ini dapat terjadi jika beberapa kueri berjalan serentak dalam satu sesi, yang tidak didukung. Lihat batasan sesi.

Pernyataan DML yang bertentangan

String error: Could not serialize access to table due to concurrent update

Error ini dapat terjadi saat memutasi pernyataan bahasa manipulasi data (DML) yang berjalan serentak pada tabel yang sama saling bertentangan, atau saat tabel terpotong selama pernyataan DML yang bermutasi. Untuk mengetahui informasi selengkapnya, lihat Konflik pernyataan DML.

Untuk mengatasi error ini, jalankan operasi DML yang memengaruhi satu tabel agar tidak tumpang tindih.

Subkueri terkait

String error: Correlated subqueries that reference other tables are not supported unless they can be de-correlated

Error ini dapat terjadi saat kueri Anda berisi subkueri yang mereferensikan kolom dari luar subkueri tersebut, yang disebut kolom korelasi. Subkueri yang berkorelasi dievaluasi menggunakan strategi eksekusi bertingkat yang tidak efisien, yang mana subkueri dievaluasi untuk setiap baris dari kueri luar yang menghasilkan kolom korelasi. Terkadang, BigQuery dapat menulis ulang kueri dengan subkueri yang berkorelasi secara internal sehingga dapat dieksekusi secara lebih efisien. Error subkueri yang berkorelasi terjadi saat BigQuery tidak dapat mengoptimalkan kueri secara memadai.

Untuk mengatasi error ini, coba langkah-langkah berikut:

  • Hapus klausa ORDER BY, LIMIT, EXISTS, NOT EXISTS, atau IN dari subkueri Anda.
  • Gunakan kueri multi-pernyataan untuk membuat tabel sementara yang akan direferensikan dalam subkueri Anda.
  • Tulis ulang kueri Anda untuk menggunakan CROSS JOIN.

Izin kontrol akses tingkat kolom tidak memadai

String error: Requires raw access permissions on the read columns to execute the DML statements

Error ini terjadi saat Anda mencoba pernyataan DML DELETE, UPDATE, atau MERGE, tanpa memiliki izin Pembaca Terperinci pada kolom yang dipindai yang menggunakan kontrol akses tingkat kolom untuk membatasi akses di tingkat kolom. Untuk mengetahui informasi selengkapnya, lihat Dampak pada penulisan dari kontrol akses tingkat kolom.

Kredensial tidak valid untuk kueri terjadwal

String error:

  • Error code: INVALID_USERID
  • Error code 5: Authentication failure: User Id not found
  • PERMISSION_DENIED: BigQuery: Permission denied while getting Drive credentials

Error ini dapat terjadi jika kueri terjadwal gagal karena kredensial yang sudah tidak berlaku, terutama saat membuat kueri data Google Drive.

Untuk mengatasi error ini, ikuti langkah-langkah berikut:

Kredensial akun layanan tidak valid

String error: HttpError 403 when requesting returned: The caller does not have permission

Error ini mungkin muncul saat Anda mencoba menyiapkan kueri terjadwal dengan akun layanan. Untuk mengatasi error ini, lihat langkah-langkah pemecahan masalah di Masalah otorisasi dan izin.

Waktu snapshot tidak valid

String error: Invalid snapshot time

Error ini dapat terjadi saat mencoba membuat kueri data historis yang berada di luar periode perjalanan waktu untuk set data tersebut. Untuk mengatasi error ini, ubah kueri untuk mengakses data historis dalam periode perjalanan waktu set data.

Error ini juga dapat muncul jika salah satu tabel yang digunakan dalam kueri dihapus dan dibuat ulang setelah kueri dimulai. Periksa apakah ada kueri terjadwal atau aplikasi yang melakukan operasi ini yang berjalan bersamaan dengan kueri yang gagal. Jika ada, coba pindahkan proses yang melakukan operasi lepas dan buat ulang agar dapat berjalan pada waktu yang tidak bertentangan dengan kueri yang membaca tabel tersebut.

Tugas sudah ada

String error: Already Exists: Job <job name>

Error ini dapat terjadi untuk tugas kueri yang harus mengevaluasi array besar, sehingga perlu waktu lebih lama dari rata-rata untuk membuat tugas kueri. Misalnya, kueri dengan klausa WHERE seperti WHERE column IN (<2000+ elements array>).

Untuk mengatasi error ini, ikuti langkah-langkah berikut:

Tugas tidak ditemukan

String error: Job not found

Error ini dapat terjadi sebagai respons terhadap panggilan getQueryResults, jika tidak ada nilai yang ditentukan untuk kolom location. Jika demikian, coba panggilan lagi dan berikan nilai location.

Untuk mengetahui informasi selengkapnya, lihat Menghindari beberapa evaluasi Common Table Expressions (CTE) yang sama.

Lokasi tidak ditemukan

String error: Dataset [project_id]:[dataset_id] was not found in location [region]

Error ini muncul saat Anda merujuk ke resource set data yang tidak ada, atau jika lokasi dalam permintaan tidak cocok dengan lokasi set data.

Untuk mengatasi masalah ini, tentukan lokasi set data dalam kueri atau konfirmasi bahwa set data tersedia di lokasi yang sama.

Kueri melampaui batas waktu eksekusi

String error: Query fails due to reaching the execution time limit

Jika kueri Anda mencapai batas waktu eksekusi kueri, periksa waktu eksekusi kueri sebelumnya dengan membuat kueri tampilan INFORMATION_SCHEMA.JOBS dengan kueri yang mirip dengan contoh berikut:

SELECT TIMESTAMP_DIFF(end_time, start_time, SECOND) AS runtime_in_seconds
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE statement_type = 'QUERY'
AND query = "my query string";

Jika kueri yang dijalankan sebelumnya memerlukan waktu yang jauh lebih singkat, gunakan analisis performa kueri untuk menentukan dan mengatasi masalah yang mendasarinya.

Respons kueri terlalu besar

String error: responseTooLarge

Error ini terjadi jika hasil kueri Anda lebih besar dari ukuran respons maksimum.

Untuk mengatasi error ini, ikuti panduan yang disediakan untuk pesan error responseTooLarge.

Terlalu banyak pernyataan DML

String error: Too many DML statements outstanding against <table-name>, limit is 20

Error ini terjadi saat Anda melebihi batas 20 pernyataan DML dalam status PENDING dalam antrean untuk satu tabel. Error ini biasanya terjadi saat Anda mengirimkan tugas DML terhadap satu tabel lebih cepat daripada yang dapat diproses BigQuery.

Salah satu solusi yang memungkinkan adalah mengelompokkan beberapa operasi DML yang lebih kecil ke dalam tugas yang lebih besar tetapi lebih sedikit—misalnya, dengan mengelompokkan update dan penyisipan. Saat Anda mengelompokkan tugas yang lebih kecil ke dalam tugas yang lebih besar, biaya untuk menjalankan tugas yang lebih besar akan diamortisasi dan eksekusinya akan lebih cepat. Menggabungkan pernyataan DML yang memengaruhi data yang sama umumnya meningkatkan efisiensi tugas DML, dan cenderung tidak melebihi batas kuota ukuran antrean. Untuk mengetahui informasi selengkapnya tentang cara mengoptimalkan operasi DML, lihat Hindari pernyataan DML yang memperbarui atau menyisipkan baris tunggal.

Solusi lain untuk meningkatkan efisiensi DML Anda adalah dengan mempartisi atau mengelompokkan tabel Anda. Untuk mengetahui informasi selengkapnya, baca Praktik terbaik.

Pengguna tidak memiliki izin

String error:

  • Access Denied: Project [project_id]: User does not have bigquery.jobs.create permission in project [project_id].
  • User does not have permission to query table project-id:dataset.table.
  • Access Denied: User does not have permission to query table or perhaps it does not exist.

Error ini dapat terjadi saat Anda menjalankan kueri tanpa izin bigquery.jobs.create pada project tempat Anda menjalankan kueri, terlepas dari izin Anda pada project yang berisi data.

Anda juga dapat menerima error ini jika akun layanan, pengguna, atau grup Anda tidak memiliki izin bigquery.tables.getData pada semua tabel dan tampilan yang dirujuk oleh kueri Anda. Untuk mengetahui informasi selengkapnya tentang izin yang diperlukan untuk menjalankan kueri, lihat Peran yang diperlukan.

Error ini juga dapat terjadi jika tabel tidak ada di region yang dikueri, seperti asia-south1. Anda dapat memverifikasi region dengan memeriksa lokasi set data.

Saat mengatasi error ini, pertimbangkan hal berikut:

  • Akun layanan: Akun layanan harus memiliki izin bigquery.jobs.create di project tempat akun tersebut berjalan, dan harus memiliki izin bigquery.tables.getData di semua tabel dan tampilan yang direferensikan oleh kueri.

  • Peran kustom: Peran IAM kustom harus memiliki izin bigquery.jobs.create yang disertakan secara eksplisit dalam peran yang relevan, dan harus memiliki izin bigquery.tables.getData pada semua tabel dan tampilan yang dirujuk oleh kueri.

  • Set data bersama: Saat menggunakan set data bersama dalam project terpisah, Anda mungkin masih memerlukan izin bigquery.jobs.create dalam project untuk menjalankan kueri atau tugas dalam set data tersebut.

Untuk memberikan izin akses ke tabel atau tampilan, lihat Memberikan akses ke tabel atau tampilan.

Masalah resource yang terlampaui

Masalah berikut terjadi jika BigQuery tidak memiliki resource yang memadai untuk menyelesaikan kueri Anda.

Kueri melampaui resource CPU

String error: Query exceeded resource limits

Error ini terjadi saat kueri on-demand menggunakan terlalu banyak CPU dibandingkan dengan jumlah data yang dipindai. Untuk mengetahui informasi tentang cara mengatasi masalah ini, lihat Memecahkan masalah saat resource terlampaui.

Kueri melampaui resource memori

String error: Resources exceeded during query execution: The query could not be executed in the allotted memory

Untuk pernyataan SELECT, error ini terjadi saat kueri menggunakan terlalu banyak resource. Untuk mengatasi error ini, lihat Memecahkan masalah saat batas resource terlampaui.

Kehabisan ruang tumpukan

String error: Out of stack space due to deeply nested query expression during query resolution.

Error ini dapat terjadi jika kueri berisi terlalu banyak panggilan fungsi bertingkat. Terkadang, bagian kueri diterjemahkan menjadi panggilan fungsi selama penguraian. Misalnya, ekspresi dengan operator penggabungan yang berulang, seperti A || B || C || ..., menjadi CONCAT(A, CONCAT(B, CONCAT(C, ...))).

Untuk mengatasi error ini, tulis ulang kueri Anda untuk mengurangi jumlah nesting.

Resource terlampaui selama eksekusi kueri

String error: Resources exceeded during query execution: The query could not be executed in the allotted memory. Peak usage: [percentage]% of limit. Top memory consumer(s): ORDER BY operations.

Hal ini dapat terjadi dengan kueri ORDER BY ... LIMIT ... OFFSET .... Karena detail implementasi, pengurutan dapat terjadi pada satu unit komputasi, yang dapat kehabisan memori jika perlu memproses terlalu banyak baris sebelum LIMIT dan OFFSET diterapkan, terutama dengan OFFSET yang besar.

Untuk mengatasi error ini, hindari nilai OFFSET yang besar dalam kueri ORDER BY ... LIMIT. Atau, gunakan fungsi jendela ROW_NUMBER() yang dapat diskalakan untuk menetapkan peringkat berdasarkan urutan yang dipilih, lalu filter peringkat ini dalam klausa WHERE. Contoh:

SELECT ...
FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY ...) AS rn
  FROM ...
)
WHERE rn > @start_index AND rn <= @page_size + @start_index  -- note that row_number() starts with 1

Kueri melampaui resource pengacakan

String error: Resources exceeded during query execution: Your project or organization exceeded the maximum disk and memory limit available for shuffle operations

Error ini terjadi saat kueri tidak dapat mengakses resource acak yang memadai.

Untuk mengatasi error ini, sediakan lebih banyak slot atau kurangi jumlah data yang diproses oleh kueri. Untuk mengetahui informasi selengkapnya tentang cara melakukannya, lihat Kuota acak tidak memadai.

Untuk mengetahui informasi tambahan tentang cara menyelesaikan masalah ini, lihat Memecahkan masalah saat batas resource terlampaui.

Kueri terlalu rumit

String error: Resources exceeded during query execution: Not enough resources for query planning - too many subqueries or query is too complex

Error ini terjadi jika kueri terlalu kompleks. Penyebab utama kompleksitas adalah:

  • Klausul WITH yang bertingkat banyak atau digunakan berulang kali.
  • Tampilan yang bertingkat banyak atau digunakan berulang kali.
  • Penggunaan berulang operator UNION ALL.

Untuk mengatasi error ini, coba opsi berikut:

  • Pisahkan kueri menjadi beberapa kueri, lalu gunakan bahasa prosedur untuk menjalankan kueri tersebut secara berurutan dengan status bersama.
  • Gunakan tabel sementara, bukan klausa WITH.
  • Tulis ulang kueri Anda untuk mengurangi jumlah objek yang direferensikan dan perbandingan.

Anda dapat secara proaktif memantau kueri yang mendekati batas kompleksitas dengan menggunakan kolom query_info.resource_warning di tampilan INFORMATION_SCHEMA.JOBS. Contoh berikut menampilkan kueri dengan penggunaan resource yang tinggi selama tiga hari terakhir:

SELECT
  ANY_VALUE(query) AS query,
  MAX(query_info.resource_warning) AS resource_warning
FROM
  <your_project_id>.`region-us`.INFORMATION_SCHEMA.JOBS
WHERE
  creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)
  AND query_info.resource_warning IS NOT NULL
GROUP BY
  query_info.query_hashes.normalized_literals
LIMIT
  1000

Untuk mengetahui informasi tambahan tentang cara menyelesaikan masalah ini, lihat Memecahkan masalah saat batas resource terlampaui.

Memecahkan masalah resource yang terlampaui

Untuk tugas kueri:

Untuk mengoptimalkan kueri, coba langkah-langkah berikut:

  • Coba hapus klausa ORDER BY.
  • Jika kueri Anda menggunakan JOIN, pastikan tabel yang lebih besar berada di sisi kiri klausa. Pastikan juga data Anda tidak berisi kunci gabungan duplikat.
  • Jika kueri Anda menggunakan FLATTEN, tentukan apakah kueri ini diperlukan untuk kasus penggunaan Anda. Untuk informasi selengkapnya, lihat data bertingkat dan berulang.
  • Jika kueri Anda menggunakan EXACT_COUNT_DISTINCT, pertimbangkan untuk menggunakan COUNT(DISTINCT).
  • Jika kueri Anda menggunakan COUNT(DISTINCT <value>, <n>) dengan nilai <n> yang besar, sebaiknya gunakan GROUP BY. Untuk mengetahui informasi selengkapnya, lihat COUNT(DISTINCT).
  • Jika kueri Anda menggunakan UNIQUE, pertimbangkan untuk menggunakan GROUP BY, atau fungsi jendela di dalam subpilihan.
  • Jika kueri Anda mewujudkan banyak baris menggunakan klausa LIMIT, pertimbangkan untuk memfilter kolom lain, misalnya ROW_NUMBER(), atau menghapus klausa LIMIT sepenuhnya untuk memungkinkan paralelisasi penulisan.
  • Jika kueri Anda menggunakan tampilan bertingkat yang dalam dan klausa WITH, hal ini dapat menyebabkan pertumbuhan eksponensial dalam kompleksitas, sehingga mencapai batasnya.
  • Jangan ganti tabel sementara dengan klausa WITH. Klausa ini mungkin harus dihitung ulang beberapa kali, yang dapat membuat kueri menjadi kompleks dan lambat. Mempertahankan hasil perantara dalam tabel sementara justru membantu mengatasi kompleksitas
  • Hindari penggunaan kueri UNION ALL.

Untuk informasi selengkapnya, lihat referensi berikut:

Untuk tugas pemuatan:

Jika Anda memuat file Avro atau Parquet, kurangi ukuran baris dalam file. Periksa batasan ukuran tertentu untuk format file yang sedang Anda muat:

Jika Anda mendapatkan pesan error ini saat memuat file ORC, hubungi Dukungan.

Untuk Storage API:

String error: Stream memory usage exceeded

Selama panggilan ReadRows Storage Read API, beberapa aliran dengan penggunaan memori yang tinggi mungkin mendapatkan error RESOURCE_EXHAUSTED dengan pesan ini. Hal ini dapat terjadi saat membaca dari tabel lebar atau tabel dengan skema yang kompleks. Sebagai solusi, kurangi ukuran baris hasil dengan memilih lebih sedikit kolom untuk dibaca (menggunakan parameter selected_fields), atau dengan menyederhanakan skema tabel.

Memecahkan masalah konektivitas

Bagian berikut menjelaskan cara memecahkan masalah konektivitas saat mencoba berinteraksi dengan BigQuery:

Mengizinkan Google DNS

Gunakan alat Google IP Dig untuk menyelesaikan endpoint DNS BigQuery bigquery.googleapis.com ke satu IP data 'A'. Pastikan IP ini tidak diblokir di setelan firewall Anda.

Secara umum, sebaiknya Anda memasukkan nama DNS Google ke dalam daftar yang diizinkan. Rentang IP yang dibagikan dalam file https://www.gstatic.com/ipranges/goog.json dan https://www.gstatic.com/ipranges/cloud.json sering berubah. Oleh karena itu, sebaiknya izinkan nama DNS Google. Berikut adalah daftar nama DNS umum yang kami rekomendasikan untuk ditambahkan ke daftar yang diizinkan:

  • *.1e100.net
  • *.google.com
  • *.gstatic.com
  • *.googleapis.com
  • *.googleusercontent.com
  • *.appspot.com
  • *.gvt1.com

Mengidentifikasi proxy atau firewall yang membuang paket

Untuk mengidentifikasi semua hop paket antara klien dan Google Front End (GFE), jalankan perintah traceroute di komputer klien Anda yang dapat menandai server yang membuang paket yang diarahkan ke GFE. Berikut contoh perintah traceroute:

traceroute -T -p 443 bigquery.googleapis.com

Anda juga dapat mengidentifikasi lompatan paket untuk alamat IP GFE tertentu jika masalahnya terkait dengan alamat IP tertentu:

traceroute -T -p 443 142.250.178.138

Jika ada masalah waktu tunggu di sisi Google, Anda akan melihat permintaan sampai ke GFE.

Jika Anda melihat bahwa paket tidak pernah mencapai GFE, hubungi administrator jaringan Anda untuk menyelesaikan masalah ini.

Buat file PCAP dan analisis firewall atau proxy Anda

Buat file rekaman paket (PCAP) dan analisis file tersebut untuk memastikan firewall atau proxy tidak memfilter paket ke IP Google dan mengizinkan paket mencapai GFE.

Berikut adalah contoh perintah yang dapat dijalankan dengan alat tcpdump:

tcpdump -s 0 -w debug.pcap -K -n host bigquery.googleapis.com

Menyiapkan percobaan ulang untuk masalah konektivitas yang terputus-putus

Ada situasi saat load balancer GFE dapat menghentikan koneksi dari IP klien, misalnya, jika mendeteksi pola traffic DDOS, atau jika instance load balancer sedang di-downscale yang dapat menyebabkan IP endpoint didaur ulang. Jika load balancer GFE menghentikan koneksi, klien harus menangkap permintaan yang waktunya habis dan mencoba lagi permintaan ke endpoint DNS. Pastikan Anda tidak menggunakan alamat IP yang sama hingga permintaan berhasil, karena alamat IP mungkin telah berubah.

Jika Anda telah mengidentifikasi masalah dengan waktu tunggu habis yang konsisten di sisi Google dan percobaan ulang tidak membantu, hubungi Layanan Pelanggan Cloud dan pastikan untuk menyertakan file PCAP baru yang dihasilkan dengan menjalankan alat pengambilan paket seperti tcpdump.

Langkah berikutnya