Kueri gabungan Cloud SQL

Kueri gabungan di BigQuery memungkinkan Anda membuat kueri data yang ada di Cloud SQL secara real time tanpa menyalin atau memindahkan data. Gabungan kueri mendukung instance Cloud SQL untuk MySQL dan Cloud SQL untuk PostgreSQL di Cloud SQL.

Atau, untuk mereplikasi data ke BigQuery, Anda dapat menggunakan Cloud Data Fusion atau Datastream. Untuk mengetahui informasi selengkapnya tentang penggunaan Cloud Data Fusion, lihat Mereplikasi data dari MySQL ke BigQuery.

Sebelum memulai

Untuk menjalankan kueri gabungan pada data Anda di Cloud SQL, lakukan hal berikut:

  1. Pastikan administrator BigQuery Anda telah membuat koneksi Cloud SQL dan membagikan koneksi tersebut kepada Anda.
  2. Pastikan agen layanan BigQuery Connection telah diberi peran Cloud SQL Client (roles/cloudsql.client). Untuk mengetahui informasi selengkapnya, lihat Memberikan akses ke agen layanan.
  3. Jika instance Cloud SQL Anda menggunakan alamat IP pribadi, pastikan jalur pribadi diaktifkan saat instance dibuat. Untuk mengetahui informasi selengkapnya, lihat Terhubung ke Cloud SQL dan Mengonfigurasi IP pribadi.

Peran yang diperlukan

Untuk mendapatkan izin yang diperlukan untuk membuat kueri instance Cloud SQL, minta administrator untuk memberi Anda peran IAM BigQuery Connection User (roles/bigquery.connectionUser) di project Anda. Untuk mengetahui informasi selengkapnya tentang cara memberikan peran, lihat Mengelola akses ke project, folder, dan organisasi.

Anda mungkin juga bisa mendapatkan izin yang diperlukan melalui peran khusus atau peran bawaan lainnya.

Data kueri

Untuk mengirim kueri gabungan ke Cloud SQL dari kueri GoogleSQL, gunakan fungsi EXTERNAL_QUERY.

Format ID koneksi

Argumen pertama untuk fungsi EXTERNAL_QUERY adalah ID koneksi. Anda dapat menentukan ID koneksi menggunakan salah satu format berikut:

  • Format pendek: [LOCATION.]CONNECTION_ID (misalnya, 'us.my-connection' atau 'my-connection')
  • Format yang sepenuhnya memenuhi syarat: projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID
  • Format jalur yang mudah dipahami: PROJECT_ID.LOCATION.CONNECTION_ID (misalnya, 'my-project.us.my-connection')

Jika Anda menjalankan kueri dalam tampilan bersama di beberapa project, selalu gunakan ID koneksi yang sepenuhnya memenuhi syarat untuk menghindari koneksi ke project yang salah.

Menjalankan kueri gabungan

Misalkan Anda menyimpan tabel pelanggan di BigQuery, sambil menyimpan tabel pesanan di Cloud SQL, dan ingin menggabungkan kedua tabel tersebut dalam satu kueri. Contoh berikut membuat kueri gabungan ke tabel Cloud SQL bernama orders dan menggabungkan hasilnya dengan tabel BigQuery bernama mydataset.customers.

Pilih salah satu opsi berikut untuk menjalankan kueri gabungan:

Konsol

  1. Di konsol Google Cloud , buka halaman BigQuery.

    Buka BigQuery

  2. Di editor kueri, masukkan kueri Anda:

    SELECT c.customer_id, c.name, rq.first_order_date
FROM mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'us.CONNECTION_ID',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''') AS rq ON rq.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, rq.first_order_date;

    Ganti CONNECTION_ID dengan ID koneksi BigQuery Anda.

  3. Klik Run.

    Hasil kueri akan muncul di panel Query results.

bq

  2. Di konsol Google Cloud , aktifkan Cloud Shell.

    Aktifkan Cloud Shell

    Di bagian bawah konsol Google Cloud , sesi Cloud Shell akan terbuka dan menampilkan prompt command line. Cloud Shell adalah lingkungan shell dengan Google Cloud CLI yang sudah terinstal, dan dengan nilai yang sudah ditetapkan untuk project Anda saat ini. Diperlukan waktu beberapa detik untuk melakukan inisialisasi pada sesi.

  3. Jalankan perintah bq query dengan flag --use_legacy_sql=false:

    bq query \
    --use_legacy_sql=false \
    'SELECT c.customer_id, c.name, rq.first_order_date
    FROM mydataset.customers AS c
    LEFT OUTER JOIN EXTERNAL_QUERY(
      "us.CONNECTION_ID",
      """SELECT customer_id, MIN(order_date) AS first_order_date
      FROM orders
      GROUP BY customer_id""") AS rq ON rq.customer_id = c.customer_id
    GROUP BY c.customer_id, c.name, rq.first_order_date;'

    Ganti CONNECTION_ID dengan ID koneksi BigQuery Anda.

Contoh kueri ini mencakup 3 bagian:

  1. Jalankan kueri eksternal SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id di database Cloud SQL operasional untuk mendapatkan tanggal pesanan pertama bagi setiap pelanggan melalui fungsi EXTERNAL_QUERY().
  2. Gabungkan tabel hasil kueri eksternal dengan tabel pelanggan di BigQuery berdasarkan customer_id.
  3. Pilih informasi pelanggan dan tanggal pesanan pertama.

Melihat skema tabel Cloud SQL

Anda dapat menggunakan fungsi EXTERNAL_QUERY() untuk mengkueri tabel virtual INFORMATION_SCHEMA guna mengakses metadata database, seperti mencantumkan semua tabel dalam database atau menampilkan skema tabel. Contoh kueri INFORMATION_SCHEMA berikut berfungsi di MySQL dan PostgreSQL. Anda dapat mempelajari lebih lanjut dari tabel information_schema MySQL dan tabel information_schema PostgreSQL.

-- List all tables in a database.
SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID",
"SELECT * FROM information_schema.tables;");

-- List all columns in a table.
SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID",
"SELECT * FROM information_schema.columns WHERE table_name='x';");

Ganti CONNECTION_ID dengan ID koneksi BigQuery Anda.

Melacak kueri gabungan BigQuery

Saat Anda menjalankan kueri gabungan terhadap Cloud SQL, BigQuery akan menganotasi kueri dengan komentar yang mirip dengan berikut ini:

/* Federated query from BigQuery. Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID. */

Jika Anda memantau log untuk penggunaan kueri di database MySQL atau PostgreSQL, anotasi akan membantu Anda mengidentifikasi kueri yang berasal dari BigQuery.

  1. Di konsol Google Cloud , buka halaman Logs Explorer.

    Buka Logs Explorer

  2. Di tab Query, masukkan kueri berikut:

    resource.type="cloudsql_database"
textPayload=~"Federated query from BigQuery"

  3. Klik Run query.

    Jika ada data yang tersedia untuk kueri gabungan BigQuery, daftar data yang mirip dengan berikut ini akan muncul di Query results:

    YYYY-MM-DD hh:mm:ss.millis UTC [3210064]: [4-1]
db=DATABASE, user=USER_ACCOUNT
STATEMENT: SELECT 1 FROM (SELECT FROM company_name_table) t;
/* Federated query from BigQuery.
Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID
*/

YYYY-MM-DD hh:mm:ss.millis UTC [3210532]: [2-1]
db=DATABASE, user=USER_ACCOUNT
STATEMENT: SELECT "company_id", "company type_id" FROM
(SELECT FROM company_name_table) t;
/* Federated query from BigQuery.
Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID
*/

Detail koneksi

Tabel berikut menunjukkan properti koneksi Cloud SQL:

Nama properti Nilai Deskripsi
name string Nama resource koneksi dalam format: projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID.
location string Lokasi koneksi yang harus cocok dengan lokasi instance Cloud SQL atau merupakan multi-region dari wilayah hukum yang sesuai. Misalnya, instance Cloud SQL di us-east4 dapat menggunakan US, sedangkan instance Cloud SQL di europe-north1 dapat menggunakan EU. Hanya kueri BigQuery yang berjalan di lokasi ini yang dapat menggunakan koneksi ini.
friendlyName string Nama tampilan yang mudah digunakan untuk koneksi.
description string Deskripsi koneksi.
cloudSql.type string Dapat berupa POSTGRES atau MYSQL.
cloudSql.instanceId string Nama instance Cloud SQL, biasanya dalam format: PROJECT_ID:LOCATION:INSTANCE_ID. Anda dapat menemukan ID instance di halaman instance Cloud SQL.
cloudSql.database string Database Cloud SQL yang akan dihubungkan.
cloudSql.serviceAccountId string Akun layanan yang dikonfigurasi untuk mengakses database Cloud SQL.

Tabel berikut menampilkan properti untuk kredensial instance Cloud SQL:

Nama properti Nilai Deskripsi
username string Nama pengguna database.
password string Sandi database.

Performa kueri dan praktik terbaik

Untuk mengoptimalkan performa kueri gabungan dan menghindari beban yang tidak perlu pada instance Cloud SQL, pertimbangkan praktik terbaik berikut:

  • Filter lebih awal dalam kueri eksternal: Terapkan filter WHERE dan agregasi GROUP BY di dalam pernyataan SQL EXTERNAL_QUERY, bukan memfilter dalam kueri BigQuery luar. Menjalankan filter di Cloud SQL akan mengurangi jumlah transfer data melalui jaringan ke BigQuery.
  • Pangkas kolom: Pilih hanya kolom tertentu yang diperlukan dalam pernyataan EXTERNAL_QUERY, bukan menggunakan SELECT *. Hal ini mengurangi volume transfer jaringan dan waktu eksekusi kueri.
  • Gunakan replika baca: Untuk mengisolasi workload kueri analisis dari database produksi utama, buat replika baca Cloud SQL dan konfigurasikan koneksi BigQuery Anda untuk mengkueri replika.
  • Tempatkan set data dan instance di lokasi yang sama: Untuk latensi terendah, tempatkan set data dan koneksi BigQuery Anda di region yang sama dengan instance Cloud SQL, atau di multi-region yang sesuai (seperti US atau EU). Untuk mengetahui informasi selengkapnya, lihat Region yang didukung.

Pemecahan masalah

Bagian ini membantu Anda memecahkan masalah yang mungkin Anda alami saat mengirim kueri gabungan ke Cloud SQL.

Gagal terhubung ke server database

Saat membuat kueri database MySQL, Anda mungkin menemukan error berikut:

Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2013): Lost connection to MySQL server during query.

Saat membuat kueri database PostgreSQL, Anda mungkin menemukan error berikut:

Invalid table-valued function EXTERNAL_QUERY Connect to PostgreSQL server failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

Resolusi

Untuk mengatasi masalah ini, verifikasi prasyarat berikut:

  1. Pastikan kredensial yang valid telah digunakan dan semua langkah penyiapan diikuti saat membuat koneksi untuk Cloud SQL.

  2. Pastikan agen layanan BigQuery Connection telah diberi peran Cloud SQL Client (roles/cloudsql.client). Email agen layanan diformat sebagai:

    service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.

    Untuk mendapatkan petunjuk mendetail, lihat Memberikan akses ke agen layanan.

  3. Jika instance Cloud SQL Anda menggunakan alamat IP pribadi, pastikan Anda mengaktifkan jalur pribadi saat membuat instance Cloud SQL. Dengan mengaktifkan jalur pribadi, BigQuery dapat mengakses data di Cloud SQL dan menjalankan kueri melalui koneksi pribadi.

Gagal terhubung ke database MySQL karena plugin autentikasi

Saat menjalankan kueri gabungan terhadap instance MySQL, Anda mungkin mengalami error berikut:

Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2059): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at [1:15]

Resolusi

Error ini terjadi untuk akun pengguna database yang dikonfigurasi dengan autentikasi caching_sha2_password, yang tidak didukung untuk kueri gabungan.

Jika instance Cloud SQL Anda menjalankan MySQL versi 8.0 atau yang lebih lama, Anda dapat mengubah plugin autentikasi untuk akun pengguna yang digunakan oleh BigQuery. Jalankan perintah berikut di instance Cloud SQL:

ALTER USER 'USERNAME'@'%' IDENTIFIED WITH mysql_native_password BY 'PASSWORD';

Ganti kode berikut:

  • USERNAME: Akun pengguna database yang digunakan BigQuery untuk terhubung ke instance Cloud SQL untuk MySQL Anda.
  • PASSWORD: Sandi untuk akun pengguna database.

Jika instance Cloud SQL Anda menjalankan MySQL versi 8.4, plugin mysql_native_password tidak digunakan lagi. Sebaiknya jangan downgrade database yang ada dari 8.4 ke 8.0 untuk workload produksi.

Mengupgrade instance dari versi sebelumnya ke MySQL 8.4 tidak akan memengaruhi koneksi database yang ada, asalkan Anda tidak mengubah plugin autentikasi pengguna sebelum mengupgrade. Karena hanya pengguna yang dibuat dengan mysql_native_password yang dapat digunakan untuk kueri gabungan, kueri yang dikirim ke MySQL 8.4 harus menggunakan akun pengguna yang dibuat sebelum upgrade.

Batasan

Kueri gabungan di Cloud SQL tunduk pada batasan berikut:

  • Kueri hanya baca: Kueri gabungan bersifat hanya baca. Kueri eksternal harus berupa pernyataan SELECT; pernyataan bahasa definisi data (DDL) dan bahasa pengolahan data (DML) tidak didukung.
  • Performa kueri: Kueri gabungan tidak secepat kueri penyimpanan BigQuery. BigQuery harus menunggu Cloud SQL menjalankan kueri eksternal dan mentransfer set hasil melalui jaringan.
  • Batasan region: Lokasi pemrosesan kueri BigQuery harus cocok atau kompatibel dengan lokasi instance Cloud SQL. Untuk mengetahui informasi selengkapnya, lihat Wilayah yang didukung.
  • Jenis data yang tidak didukung: Jika kueri eksternal menampilkan jenis data yang tidak didukung oleh BigQuery, kueri akan gagal. Anda dapat mentransmisikan jenis data yang tidak didukung ke jenis data yang didukung dalam kueri eksternal. Untuk mengetahui informasi selengkapnya, lihat Pemetaan jenis data.
  • Koneksi maksimum: Kueri gabungan dapat mereferensikan maksimal 10 koneksi unik.
  • Batasan plugin autentikasi: Plugin caching_sha2_password MySQL tidak didukung untuk kueri gabungan. Untuk mengetahui informasi selengkapnya, lihat Pemecahan masalah.

Langkah berikutnya