Kueri gabungan Cloud SQL

Sebagai analis data, Anda dapat membuat kueri data di Cloud SQL dari BigQuery menggunakan kueri gabungan.

Gabungan BigQuery Cloud SQL memungkinkan BigQuery membuat kueri data yang ada di Cloud SQL secara real time, tanpa menyalin atau memindahkan data. Gabungan kueri mendukung instance MySQL (generasi ke-2) dan PostgreSQL di Cloud SQL.

Atau, untuk mereplikasi data ke BigQuery, Anda juga 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

Data kueri

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

Misalkan Anda menyimpan tabel pelanggan di BigQuery, sambil menyimpan tabel penjualan 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.

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;

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 PostgreSQL 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 membuat kueri tabel 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';");

Detail koneksi

Tabel berikut menunjukkan properti koneksi Cloud SQL:

Nama properti Nilai Deskripsi
name string Nama resource koneksi dalam format: project_id.location_id.connection_id.
location string Lokasi koneksi, yang sama seperti lokasi instance Cloud SQL dengan pengecualian berikut: us-central1 Cloud SQL dipetakan ke BigQuery US, europe-west1 Cloud SQL dipetakan ke BigQuery EU.
friendlyName string Nama tampilan yang mudah digunakan untuk koneksi.
description string Deskripsi koneksi.
cloudSql.type string Dapat berupa "POSTGRES" atau "MYSQL".
cloudSql.instanceId string NamaInstance Cloud SQL, biasanya dalam format:

Project-id:location-id:instance-id

Anda dapat menemukan ID instance di halaman detail Instance Cloud SQL.
cloudSql.database string Database Cloud SQL yang ingin Anda hubungkan.
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

Melacak kueri gabungan BigQuery

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

/* 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 berikut dapat membantu Anda mengidentifikasi kueri yang berasal dari BigQuery.

  1. 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 akan muncul di Hasil kueri:

    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
*/

Pemecahan masalah

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

Masalah: Gagal terhubung ke server database. Jika membuat kueri ke 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.

Atau, jika membuat kueri database PostgreSQL, Anda mungkin 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.
Penyelesaian: Pastikan kredensial yang valid telah digunakan dan semua prasyarat diikuti untuk membuat koneksi untuk Cloud SQL. Periksa apakah akun layanan yang otomatis dibuat saat koneksi ke Cloud SQL dibuat memiliki peran Cloud SQL Client (roles/cloudsql.client). Akun layanan memiliki format berikut: service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.iam.gserviceaccount.com. Untuk mendapatkan petunjuk mendetail, lihat Memberikan akses ke akun layanan.

Langkah berikutnya