Memecahkan masalah kueri

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

Memecahkan masalah kueri yang lambat

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

  1. Periksa halaman Google Cloud Service Health untuk mengetahui pemadaman layanan BigQuery yang diketahui yang dapat memengaruhi performa kueri.

  2. Tinjau linimasa tugas untuk kueri Anda di halaman detail tugas untuk melihat berapa lama waktu yang diperlukan setiap tahap kueri untuk 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, tinjau insight performa kueri Anda. Insight performa kueri dapat memberi tahu Anda jika kueri berjalan lebih lama dari waktu eksekusi rata-rata, dan menyarankan kemungkinan penyebabnya. Kemungkinan penyebabnya mungkin termasuk pertentangan slot kueri atau kuota shuffle yang tidak memadai. Untuk informasi selengkapnya tentang setiap masalah performa kueri dan kemungkinan penyelesaiannya, lihat Menafsirkan insight performa kueri.

  3. Tinjau byte yang diproses di halaman detail tugas kueri untuk melihat apakah byte tersebut 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 waktu yang dapat diterima. Jika ada perbedaan byte yang besar yang diproses antara dua kueri, mungkin kueri lambat karena volume data yang besar. Untuk informasi tentang cara mengoptimalkan kueri 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 yang 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 yang berkorelasi

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

Error ini dapat terjadi jika kueri Anda berisi subkueri yang mereferensikan kolom dari luar subkueri tersebut, yang disebut kolom korelasi. Subkueri yang dikorelasikan dievaluasi menggunakan strategi eksekusi bertingkat yang tidak efisien, dengan subkueri dievaluasi untuk setiap baris dari kueri luar yang menghasilkan kolom korelasi. Terkadang, BigQuery dapat menulis ulang kueri secara internal dengan subkueri yang berkorelasi sehingga kueri tersebut dijalankan dengan 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 dirujuk 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 yang Mendetail 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 ditampilkan saat Anda merujuk ke resource set data yang tidak ada, atau saat lokasi dalam permintaan tidak cocok dengan lokasi set data.

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

Kueri melebihi 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 Menghindari 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.

Error ini terjadi saat Anda menjalankan kueri tanpa izin bigquery.jobs.create di project tempat Anda menjalankan kueri, terlepas dari izin Anda di project yang berisi data. Anda juga harus memiliki izin bigquery.tables.getData pada semua tabel dan tampilan yang dirujuk oleh kueri Anda.

Error ini juga dapat terjadi jika tabel tidak ada di wilayah yang dikueri, seperti asia-south1. Untuk membuat kueri tampilan, Anda juga memerlukan izin ini pada semua tabel dan tampilan yang mendasarinya. Untuk mengetahui informasi selengkapnya tentang izin yang diperlukan, lihat Menjalankan kueri.

Saat mengatasi error ini, pertimbangkan hal berikut:

  • Akun layanan: Akun layanan harus memiliki izin bigquery.jobs.create di project tempat akun tersebut dijalankan.

  • Peran kustom: Peran IAM kustom harus memiliki izin bigquery.jobs.create yang secara eksplisit disertakan dalam peran yang relevan.

  • 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 guna mengakses tabel

Untuk memberikan izin akses ke tabel kepada akun utama, ikuti langkah-langkah berikut:

  1. Buka halaman BigQuery.

    Buka BigQuery

  2. Di Explorer, buka tabel yang perlu Anda akses, pilih View actions, pilih Share, lalu klik Manage Permissions.

  3. Di Add principals, masukkan nama pengguna, grup, domain, atau akun layanan yang ingin Anda tambahkan.

  4. Di Tetapkan peran, pilih izin bigquery.jobs.create. Sebagai alternatif, memberikan peran roles/bigquery.jobUser dalam project tempat kueri dibuat akan memberikan izin yang diperlukan.

  5. Klik Simpan.

Masalah resource terlampaui

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

Kueri melebihi 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 informasi tentang cara mengatasi masalah ini, lihat Memecahkan masalah resource yang terlampaui.

Kueri melebihi 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 resource yang terlampaui.

Kueri melebihi 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 informasi tambahan tentang cara menyelesaikan masalah ini, lihat Memecahkan masalah resource yang 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 memantau kueri secara proaktif yang mendekati batas kompleksitas 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 informasi tambahan tentang cara menyelesaikan masalah ini, lihat Memecahkan masalah resource yang 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.
  • 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, sebaiknya gunakan COUNT(DISTINCT).
  • Jika kueri Anda menggunakan COUNT(DISTINCT <value>, <n>) dengan nilai <n> yang besar, sebaiknya gunakan GROUP BY. Untuk 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 streaming 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 resolusi, 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:

Mencantumkan DNS Google ke daftar yang diizinkan

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

Secara umum, sebaiknya izinkan nama DNS Google. 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 menjatuhkan paket

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

traceroute -T -p 443 bigquery.googleapis.com

Anda juga dapat mengidentifikasi hop 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 habis di sisi Google, Anda akan melihat permintaan tersebut sampai ke GFE.

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

Membuat file PCAP dan menganalisis firewall atau proxy

Buat file packet capture (PCAP) dan analisis file tersebut untuk memastikan firewall atau proxy tidak memfilter paket ke IP Google dan mengizinkan paket menjangkau 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 mungkin memutuskan koneksi dari IP klien - misalnya, jika mendeteksi pola traffic DDOS, atau jika instance load balancer diskalakan ke bawah yang dapat menyebabkan IP endpoint didaur ulang. Jika load balancer GFE memutuskan koneksi, klien harus menangkap permintaan yang habis waktu tunggunya dan mencoba lagi permintaan ke endpoint DNS. Pastikan Anda tidak menggunakan alamat IP yang sama hingga permintaan akhirnya berhasil, karena alamat IP mungkin telah berubah.

Jika Anda telah mengidentifikasi masalah dengan waktu tunggu sisi Google yang konsisten 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 selanjutnya