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:

Alur kerja

  • Identifikasi Google Cloud project 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.

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 dengan menggunakan tabel dan set data eksternal. Set data dan tabel eksternal memungkinkan Anda melihat tabel dan skemanya serta membuat kueri tanpa menggunakan fungsi SQL EXTERNAL_QUERY. Anda tidak perlu memasukkan kembali data ke BigQuery dan Anda dapat menggunakan sintaksis BigQuery, bukan menulis dalam dialek SQL database SQL tertentu.

Region yang didukung

Untuk mengetahui daftar lokasi yang didukung, lihat bagian berikut:

AlloyDB dan Cloud SQL

Kueri gabungan hanya didukung di region yang mendukung sumber data eksternal dan 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 atau instance AlloyDB 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.

  • 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.

  • 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.

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.

Spanner

Untuk Spanner, konfigurasi regional dan multi-regional didukung. Satu region/multi-region BigQuery dapat membuat kueri instance Spanner di region Spanner yang didukung. Untuk mengetahui detail selengkapnya, lihat kueri lintas region.

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.

  • Kunci enkripsi yang dikelola pelanggan (CMEK) CMEK dikonfigurasi secara terpisah untuk BigQuery dan untuk sumber data eksternal. Jika Anda mengonfigurasi database sumber untuk menggunakan CMEK, tetapi tidak menggunakan BigQuery, tabel sementara yang berisi hasil kueri gabungan akan dienkripsi dengan Google-owned and Google-managed encryption key.

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_ID", "select * from operations_table")
  )
WHERE a = 'Y' AND b NOT IN ('COMPLETE','CANCELLED');

Ganti CONNECTION_ID dengan ID koneksi BigQuery.

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 yang dijalankan dan baris yang cocok dengan predikat pemfilteran akan dikirim kembali ke BigQuery.

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

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

Batasan

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

Batasan untuk gabungan 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. Khususnya pushdown komputasi, gabungan, batas, pengurutan 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.
  • Pengoptimalan 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 gabungan kueri saat menggunakan set data eksternal Spanner

  • Pushdown pemangkasan kolom, filter, komputasi, dan agregasi parsial didukung. Secara khusus, gabungan, batas, dan urutan 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.
  • Pengoptimalan fungsi SQL hanya diterapkan untuk fungsi yang didukung oleh BigQuery dan Spanner.

Fungsi yang didukung oleh sumber data

Berikut adalah fungsi SQL yang didukung menurut 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:
    • Penyusutan Compute,
    • Pengurangan Partial Aggregate,
    • Fungsi string,
    • Fungsi Math,
    • Fungsi Cast,
    • Fungsi Array.

Bekerja dengan kolasi di sumber data eksternal

Sumber data eksternal mungkin memiliki set kolasi pada kolom (misalnya, tidak peka huruf besar/kecil). Saat Anda menjalankan kueri gabungan, database jarak jauh akan mempertimbangkan kolasi yang dikonfigurasi.

Pertimbangkan contoh berikut di mana Anda memiliki kolom flag dengan pengurutan yang tidak peka huruf besar/kecil di sumber data eksternal:

SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table where flag = 'Y'")

Ganti CONNECTION_ID dengan ID koneksi BigQuery.

Kueri sebelumnya menampilkan baris dengan flag adalah y atau Y karena kueri dijalankan pada sumber data eksternal.

Namun, untuk gabungan kueri dengan sumber data Cloud SQL, SAP Datasphere, atau AlloyDB, jika Anda menambahkan filter pada kueri utama, kueri akan dieksekusi di sisi BigQuery dengan kolasi default. Lihat kueri berikut:

SELECT * FROM
  (
    SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table")
  )
WHERE flag = 'Y'

Karena kolasi peka huruf besar/kecil default di BigQuery, kueri sebelumnya hanya menampilkan baris dengan tanda Y dan memfilter baris dengan tanda y. Untuk membuat klausa WHERE Anda tidak peka huruf besar/kecil, tentukan kolasi dalam kueri:

SELECT * FROM
  (
    SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table")
  )
WHERE COLLATE(flag, 'und:ci') = 'Y'

Langkah berikutnya