Pengantar kueri gabungan

Halaman ini memperkenalkan cara menggunakan kueri gabungan dan menyediakan panduan untuk membuat kueri data Spanner, AlloyDB, dan Cloud SQL dari BigQuery.

Kueri gabungan memungkinkan Anda mengirim pernyataan kueri ke database AlloyDB, Spanner, atau Cloud SQL dan mendapatkan kembali hasilnya sebagai tabel sementara. Kueri gabungan menggunakan BigQuery Connection API untuk membuat koneksi dengan AlloyDB, Spanner, atau Cloud SQL. Dalam kueri Anda, gunakan fungsi EXTERNAL_QUERY untuk mengirim pernyataan kueri ke database eksternal, menggunakan dialek SQL database tersebut. Hasilnya dikonversi ke jenis data GoogleSQL.

Penyimpanan data yang didukung

Anda dapat menggunakan kueri gabungan dengan penyimpanan data berikut:

• Spanner
• Cloud SQL
• AlloyDB
• SAP Datasphere (Pratinjau)

Alur kerja

• Identifikasi project Google Cloud yang menyertakan sumber data yang ingin Anda buat kuerinya.
• Pengguna bigquery.admin membuat resource koneksi di BigQuery.
• Pengguna admin tersebut memberikan izin untuk menggunakan resource koneksi kepada pengguna B.
  • Jika admin dan pengguna B adalah orang yang sama, izin tidak perlu diberikan.
• Pengguna B menulis kueri di BigQuery dengan fungsi SQL EXTERNAL_QUERY yang baru.

Perhatian: Performa kueri gabungan mungkin lebih rendah daripada kueri yang membaca data yang berada di penyimpanan BigQuery.

Alternatif untuk kueri gabungan: tabel dan set data eksternal

Opsi lain untuk membuat kueri database operasional seperti Bigtable, Spanner, Cloud Storage, Google Drive, dan Salesforce Data Cloud adalah menggunakan tabel dan set data eksternal. Set data dan tabel eksternal memungkinkan Anda melihat tabel dan skema serta membuat kueri tanpa menggunakan fungsi SQL EXTERNAL_QUERY. Anda tidak perlu memasukkan kembali data ke BigQuery dan dapat menggunakan sintaksis BigQuery, bukan menulis dalam dialek database SQL tertentu.

Region yang didukung

Kueri gabungan hanya didukung di region yang mendukung sumber data eksternal dan BigQuery. Untuk mengetahui daftar lokasi yang didukung, lihat bagian berikut:

• Konfigurasi regional dan multi-regional Spanner.
• Lokasi instance Cloud SQL.
• Lokasi AlloyDB.
• Lokasi set data BigQuery.

Anda dapat membuat koneksi dan menjalankan kueri gabungan di berbagai region sesuai dengan aturan berikut:

Satu region

Satu region BigQuery hanya dapat membuat kueri resource di region yang sama.

Misalnya, jika set data Anda berada di us-east4, Anda dapat membuat kueri instance Cloud SQL, instance AlloyDB, atau database Spanner yang berada di us-east4. Lokasi pemrosesan kueri adalah satu region BigQuery.

Multi-region

Multi-region BigQuery dapat membuat kueri region sumber data apa pun di area geografis yang luas dan sama (Amerika Serikat, Uni Eropa). Lokasi multi-regional tidak tersedia untuk instance Cloud SQL, karena hanya digunakan untuk pencadangan. Multi-region BigQuery juga dapat membuat kueri instance Spanner di multi-region yang sama.

• Kueri yang berjalan di multi-region Amerika Serikat BigQuery dapat membuat kueri setiap region di area geografis Amerika Serikat, seperti us-central1, us-east4, atau us-west2.

  Perhatian: Membuat kueri sumber data eksternal yang berada di southamerica-east1 dari set data BigQuery di multi-region Amerika Serikat tidak didukung.

• Kueri yang berjalan di multi-region Uni Eropa BigQuery dapat membuat kueri setiap wilayah di negara anggota Uni Eropa, seperti europe-north1 atau europe-west3.

• Lokasi tempat kueri berjalan harus sama dengan lokasi resource koneksi. Misalnya, kueri yang dijalankan dari multi-region Amerika Serikat harus menggunakan koneksi yang berada di multi-region Amerika Serikat juga.

  Perhatian: Kueri yang berasal dari multi-region tidak dapat lagi mereferensikan koneksi di satu region. Jika Anda memiliki koneksi yang terpengaruh, buat ulang koneksi di multi-region yang sama dengan kueri Anda.

Performa kueri bervariasi berdasarkan kedekatan antara set data dan sumber data eksternal. Misalnya, kueri gabungan antara set data di multi-region Amerika Serikat dan instance Cloud SQL di us-central1 dapat dijalankan dengan cepat. Namun, jika Anda menjalankan kueri yang sama antara multi-region Amerika Serikat dan instance Cloud SQL di us-east4, performanya mungkin lebih lambat.

Lokasi pemrosesan kueri adalah lokasi multi-region, baik US atau EU.

Pemetaan jenis data

Saat Anda menjalankan kueri gabungan, data dari sumber data eksternal akan dikonversi ke jenis GoogleSQL. Untuk mengetahui informasi selengkapnya, lihat Kueri gabungan Cloud SQL.

Kuota dan batas

• Kueri gabungan lintas region. Jika lokasi pemrosesan kueri BigQuery dan lokasi sumber data eksternal berbeda, berarti ini adalah kueri lintas region. Anda dapat menjalankan hingga 1 TB dalam kueri lintas region per project per hari. Berikut contoh kueri lintas region.
  • Instance Cloud SQL berada di us-west1, sedangkan koneksi BigQuery berbasis di multi-region Amerika Serikat. Lokasi pemrosesan kueri BigQuery adalah US.
• Kuota. Pengguna harus mengontrol kuota kueri di sumber data eksternal, seperti Cloud SQL atau AlloyDB. Tidak ada setelan kuota tambahan untuk kueri gabungan. Untuk mencapai isolasi workload, sebaiknya hanya buat kueri replika baca database.
• Byte maksimum yang ditagih yang diizinkan. Kolom ini tidak didukung untuk kueri gabungan. Penghitungan byte yang ditagih sebelum benar-benar menjalankan kueri gabungan tidak dapat dilakukan.
• Jumlah koneksi. Kueri gabungan dapat memiliki maksimal 10 koneksi unik.
• Cloud SQL MySQL dan PostgreSQL. Kuota dan batasan berlaku.

Batasan

Kueri gabungan tunduk pada batasan berikut:

• Performa. Kueri gabungan kemungkinan tidak akan secepat membuat kueri penyimpanan BigQuery saja. BigQuery harus menunggu database sumber untuk menjalankan kueri eksternal dan memindahkan data untuk sementara dari sumber data eksternal ke BigQuery. Selain itu, database sumber mungkin tidak dioptimalkan untuk kueri analisis yang kompleks.

  Performa kueri juga bervariasi berdasarkan kedekatan antara set data dan sumber data eksternal. Untuk mengetahui informasi selengkapnya, lihat Region yang didukung.

• Kueri gabungan bersifat hanya baca. Kueri eksternal yang dijalankan dalam database sumber harus bersifat hanya baca. Oleh karena itu, pernyataan DML atau DDL tidak didukung.

• Jenis data yang tidak didukung. Jika kueri eksternal Anda berisi jenis data yang tidak didukung di BigQuery, kueri tersebut akan langsung gagal. Anda dapat mentransmisikan jenis data yang tidak didukung ke jenis data lain yang didukung.

• Project. Anda harus membuat resource koneksi dalam project yang sama dengan instance Cloud SQL atau AlloyDB.

Harga

• Jika menggunakan model harga on-demand, Anda akan dikenai biaya untuk jumlah byte yang ditampilkan dari kueri eksternal saat menjalankan kueri gabungan dari BigQuery. Untuk mengetahui informasi selengkapnya, lihat Harga analisis on-demand.

• Jika menggunakan edisi BigQuery, Anda akan dikenai biaya berdasarkan jumlah slot yang digunakan. Untuk mengetahui informasi selengkapnya, lihat Harga komputasi kapasitas.

Pushdown SQL

Kueri gabungan tunduk pada teknik pengoptimalan yang dikenal sebagai pushdown SQL. Fungsi ini meningkatkan performa kueri dengan mendelegasikan operasi seperti memfilter ke sumber data eksternal, bukan melakukannya di BigQuery. Mengurangi jumlah data yang ditransfer dari sumber data eksternal dapat mengurangi waktu eksekusi kueri dan mengurangi biaya. Pushdown SQL mencakup pruning kolom (klausa SELECT) dan pushdown filter (klausa WHERE).

Saat Anda menggunakan fungsi EXTERNAL_QUERY, pushdown SQL bekerja dengan menulis ulang kueri yang asli. Pada contoh berikut, fungsi EXTERNAL_QUERY digunakan untuk berkomunikasi dengan database Cloud SQL:

SELECT COUNT(*)
FROM (
  SELECT * FROM EXTERNAL_QUERY("<connection>", "select * from operations_table")
  )
WHERE a = 'Y' AND b NOT IN ('COMPLETE','CANCELLED');

Tanpa pushdown SQL, kueri berikut akan dikirim ke Cloud SQL:

SELECT *
FROM operations_table

Saat kueri ini dijalankan, seluruh tabel akan dikirim kembali ke BigQuery, meskipun hanya beberapa baris dan kolom yang diperlukan.

Dengan pushdown SQL, kueri berikut dikirim ke Cloud SQL:

SELECT `a`, `b`
FROM (
  SELECT * FROM operations_table) t
WHERE ((`a` = 'Y') AND (NOT `b` IN ('COMPLETE', 'CANCELLED'))

Saat kueri ini dijalankan, hanya dua kolom dan baris yang cocok dengan predikat pemfilteran yang akan dikirim kembali ke BigQuery.

Pushdown SQL juga diterapkan saat menjalankan kueri gabungan dengan set data eksternal Spanner.

Anda dapat memeriksa pushdown yang diterapkan (jika ada) di paket kueri.

Batasan

Pushdown SQL memiliki berbagai batasan yang bervariasi bergantung pada sumber data eksternal dan cara Anda membuat kueri data.

Batasan untuk penggabungan kueri saat menggunakan EXTERNAL_QUERY

• Pushdown SQL hanya diterapkan pada kueri gabungan dengan format SELECT * FROM T.
• Hanya pruning kolom dan pushdown filter yang didukung. Secara khusus, pushdown komputasi, join, batas, urutan menurut, dan agregasi tidak didukung.
• Untuk pushdown filter, literal harus berupa salah satu dari jenis berikut: BOOL, INT64, FLOAT64, STRING, DATE, DATETIME, TIMESTAMP. Literal yang berupa struct tidak didukung.
• Pushdown fungsi SQL hanya diterapkan untuk fungsi yang didukung oleh BigQuery dan database tujuan.
• Pushdown SQL hanya didukung untuk AlloyDB, Cloud SQL, dan Spanner.
• Pushdown SQL tidak didukung untuk SAP Datasphere.

Batasan untuk penggabungan kueri saat menggunakan set data eksternal Spanner

• Pushdown agregasi parsial, komputasi, filter, dan pemangkasan kolom didukung. Secara khusus, penggabungan, pembatasan, dan pengurutan menurut agregasi tidak didukung.
• Untuk pushdown filter, literal harus berupa salah satu dari jenis berikut: BOOL, INT64, FLOAT64, STRING, DATE, DATETIME, TIMESTAMP, BYTE, atau Array. Literal yang berupa struct tidak didukung.
• Pushdown fungsi SQL hanya diterapkan untuk fungsi yang didukung oleh BigQuery dan Spanner.

Fungsi yang didukung oleh sumber data

Berikut adalah fungsi SQL yang didukung berdasarkan sumber data. Tidak ada fungsi yang didukung untuk SAP Datasphere.

Cloud SQL untuk MySQL

• Operator logika: AND, OR, NOT.
• Operator perbandingan: =, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
• Operator aritmetika: +, -, * (hanya untuk INT64 dan FLOAT64).

Cloud SQL PostgreSQL dan AlloyDB

• Operator logika: AND, OR, NOT.
• Operator perbandingan: =, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
• Operator aritmetika: +, -, *, / (hanya untuk jenis INT64, FLOAT64, dan DATE, kecuali untuk pengurangan DATE).

Spanner - Dialek PostgreSQL

• Operator logika: AND, OR, NOT.
• Operator perbandingan: =, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
• Operator aritmetika: +, -, *, / (hanya untuk INT64, FLOAT64, NUMERIC).

Spanner - Dialek GoogleSQL

• Operator logika: AND, OR, NOT.
• Operator perbandingan: =, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
• Operator aritmetika: +, -, *, / (hanya untuk INT64, FLOAT64, NUMERIC).
• Operator aritmetika yang aman: SAFE_ADD, SAFE_SUBTRACT, SAFE_MULTIPLY, SAFE_DIVIDE (hanya untuk INT64, FLOAT64, NUMERIC).
• Saat menggunakan set data eksternal, selain itu:
  • Pushdown Compute,
  • Pushdown Agregat Sebagian,
  • Fungsi String,
  • Fungsi Matematika,
  • Fungsi Transmisikan,
  • Fungsi Array.

Langkah selanjutnya

• Pelajari cara membuat kueri data Spanner.
• Pelajari cara membuat set data eksternal Spanner.
• Pelajari cara melakukan kueri data Cloud SQL.
• Pelajari cara membuat kueri data AlloyDB.
• Pelajari cara membuat kueri data SAP Datasphere