Menjalankan kueri gabungan MySQL

Sebagai analis data, Anda dapat membuat kueri data relasional yang ada di MySQL langsung dari BigQuery secara real time, tanpa menyalin atau memindahkan data.

Federasi MySQL BigQuery menggunakan BigQuery Connection API untuk terhubung ke mesin database MySQL. Kemudian, Anda dapat menggunakan fungsi EXTERNAL_QUERY untuk mengirim kueri SQL langsung ke MySQL untuk dieksekusi, dan hasilnya akan di-streaming langsung kembali.

Batasan

Kueri gabungan MySQL tunduk pada batasan berikut:

  • Anda hanya dapat menjalankan kueri hanya baca melalui fungsi EXTERNAL_QUERY. Pernyataan bahasa definisi data (DDL) dan bahasa pengolahan data (DML) tidak didukung.
  • Federasi hanya dapat diakses melalui fungsi EXTERNAL_QUERY. Pembuatan tabel eksternal atau set data permanen BigQuery yang langsung dipetakan ke tabel MySQL tidak didukung.
  • Latensi kueri gabungan mungkin jauh lebih tinggi daripada menjalankan kueri langsung di MySQL.
  • Layanan koneksi yang mendasarinya mungkin menghibernasi koneksi yang tidak aktif. Kueri pertama yang menggunakan koneksi MySQL setelah periode tidak aktif mungkin memerlukan waktu lebih dari satu menit untuk dijalankan saat konektor melakukan inisialisasi.
  • Peering DNS tidak didukung. Anda harus terhubung menggunakan alamat IP atau nama host yang dapat di-resolve sepenuhnya dalam jaringan VPC Anda.
  • Kueri SQL MySQL harus menentukan alias kolom eksplisit untuk setiap kolom yang berisi hasil atau ekspresi fungsi (misalnya, SELECT COUNT(*) AS total_count). Ekspresi tanpa alias menghasilkan nama kolom dengan karakter khusus yang tidak valid dalam definisi skema tabel BigQuery.
  • Jika penggunaan Compute Engine API dalam project kueri dibatasi oleh Kontrol Layanan VPC, kueri gabungan akan gagal jika koneksi menggunakan lampiran jaringan. Pastikan aturan ingress dan egress Kontrol Layanan VPC yang sesuai telah dikonfigurasi.

  • Tidak ada pushdown SQL tambahan yang didukung untuk MySQL. Filter dan agregasi dalam kueri BigQuery luar tidak didorong ke kueri dalam di dalam fungsi EXTERNAL_QUERY. Anda harus menentukan predikat dan proyeksi kolom langsung di dalam pernyataan SQL MySQL internal untuk membatasi baris dan kolom yang ditransfer di seluruh jaringan.

  • Hanya autentikasi nama pengguna dan sandi database MySQL yang didukung. Penyedia identitas pihak ketiga, Kerberos, dan autentikasi IAM penyedia cloud tidak didukung.

  • Pernyataan CREATE TABLE ... AS SELECT (CTAS) gagal dengan error Unsupported type INT32 atau Unsupported type FLOAT jika kueri memilih kolom bilangan bulat 32-bit (TINYINT, SMALLINT, dan BOOLEAN / TINYINT(1)) atau kolom float presisi tunggal (FLOAT). Untuk menyimpan hasil kueri ke dalam tabel BigQuery baru, transmisikan kolom bilangan bulat secara eksplisit ke INT64 dan kolom float ke FLOAT64 dalam kueri Anda:

    CREATE TABLE mydataset.mytable AS
SELECT
CAST(tiny_col AS INT64) AS tiny_col,
CAST(small_col AS INT64) AS small_col,
...
FROM EXTERNAL_QUERY(...);

    Penyisipan ke dalam tabel yang ada dengan INSERT INTO <table> SELECT ... berhasil tanpa transmisi manual karena BigQuery otomatis memperluas nilai INT32 dan FLOAT agar sesuai dengan kolom INT64 dan FLOAT64 tabel target.

  • Meskipun kolom DECIMAL dan NUMERIC MySQL dipetakan ke BIGNUMERIC(76, 38), MySQL memungkinkan presisi hingga 65. Jika suatu nilai berisi lebih dari 38 digit bilangan bulat (presisi dikurangi skala lebih besar dari 38), nilai tersebut akan melebihi rentang bilangan bulat BIGNUMERIC dan kueri akan gagal saat runtime. Untuk mengkueri nilai dengan lebih dari 38 digit bilangan bulat, transmisikan ke CHAR atau VARCHAR dalam pernyataan EXTERNAL_QUERY.

  • Jenis data TIME memotong nilai pecahan sub-detik (mikrodetik) menjadi detik utuh (misalnya, 12:34:56.789123 menjadi 12:34:56). Selain itu, TIME MySQL mendukung durasi yang berlalu dari -838:59:59 hingga 838:59:59, sedangkan TIME BigQuery hanya mendukung nilai waktu dari 00:00:00 hingga 23:59:59.999999. Nilai di luar 00:00:00 hingga 23:59:59 akan menyebabkan error kueri runtime. Jika presisi mikrodetik atau nilai durasi yang berlalu diperlukan, transmisikan kolom ke CHAR atau VARCHAR di dalam pernyataan EXTERNAL_QUERY.

  • Tanggal dan stempel waktu sebelum 15 Oktober 1582 akan mengalami pergeseran kalender selama 10 hari karena transisi kalender Gregorian historis. Untuk mempertahankan tanggal historis yang tepat sebelum tahun 1582, transmisikan tanggal tersebut ke CHAR atau VARCHAR dalam pernyataan EXTERNAL_QUERY.

Lingkungan dan versi MySQL yang didukung

Gabungan kueri mendukung database MySQL di seluruh lingkungan deployment berikut:

  • Database MySQL yang dihosting di cloud atau lokal yang dapat diakses melalui alamat IP publik atau lampiran jaringan (melalui Cloud VPN atau Cloud Interconnect)
  • Instance MySQL yang dihosting sendiri di Compute Engine atau infrastruktur lainnya
  • MySQL 8.4 LTS dan 8.0

Sebelum memulai

Pastikan administrator BigQuery Anda telah membuat koneksi MySQL dan membagikannya kepada Anda.

Peran yang diperlukan

Untuk mendapatkan izin yang diperlukan untuk membuat kueri MySQL, minta administrator untuk memberi Anda peran IAM BigQuery Connection User (roles/bigquery.connectionUser) pada project. 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 MySQL dari kueri GoogleSQL, gunakan fungsi EXTERNAL_QUERY.

Contoh berikut adalah kueri gabungan yang menggabungkan data pelanggan di BigQuery bernama mydataset.customers dengan pesanan penjualan transaksional di MySQL:

SELECT
  c.customer_id,
  c.name,
  fo.first_order_date
FROM
  mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'projects/my-project/locations/us-central1/connections/mysql_prod_conn',
  '''SELECT CUSTOMER_ID, MIN(ORDER_DATE) AS first_order_date
     FROM ORDERS
     GROUP BY CUSTOMER_ID''') AS fo
  ON fo.customer_id = c.customer_id
GROUP BY
  c.customer_id,
  c.name,
  fo.first_order_date;

Melihat skema tabel MySQL

Anda dapat mengkueri tampilan INFORMATION_SCHEMA MySQL melalui EXTERNAL_QUERY untuk memeriksa metadata database, menemukan tabel, dan meninjau skema kolom sebelum menjalankan analisis.

Mencantumkan semua database

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME
     FROM INFORMATION_SCHEMA.SCHEMATA''');

Mencantumkan tabel dalam database

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  """SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE
     FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_SCHEMA = 'DATABASE_NAME'""");

Melihat kolom dan jenis data dalam tabel

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  """SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_TYPE
     FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = 'DATABASE_NAME' AND TABLE_NAME = 'TABLE_NAME'""");

Pemetaan jenis data

Saat Anda menjalankan kueri gabungan, BigQuery akan mengonversi data dari MySQL menjadi jenis data GoogleSQL sesuai dengan tabel berikut:

Jenis data MySQL Jenis data BigQuery
Jenis numerik
BOOLEAN, BOOL, TINYINT(1) INT64 (kueri interaktif; lihat Batasan untuk CREATE TABLE ... AS SELECT)
TINYINT, SMALLINT INT64 (kueri interaktif; lihat Batasan untuk CREATE TABLE ... AS SELECT)
MEDIUMINT, INT, INTEGER INT64
BIGINT INT64
BIGINT UNSIGNED, BIGINT ZEROFILL, SERIAL BIGNUMERIC(76, 38)
DECIMAL, DEC, NUMERIC, FIXED BIGNUMERIC(76, 38)
FLOAT FLOAT64 (kueri interaktif; lihat Batasan untuk CREATE TABLE ... AS SELECT)
DOUBLE, DOUBLE PRECISION, REAL FLOAT64
Jenis tanggal dan waktu
DATE DATE
DATETIME DATETIME
TIMESTAMP TIMESTAMP
TIME TIME
Jenis string dan teks
CHAR STRING
VARCHAR STRING
BINARY BYTES
VARBINARY BYTES
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB BYTES
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT STRING
ENUM STRING
SET STRING

Harga

Biaya menjalankan kueri gabungan didasarkan pada tiga faktor:

  • Biaya komputasi untuk menjalankan kueri di MySQL (ditagih oleh penyedia database MySQL atau lingkungan hosting Anda)
  • Biaya bandwidth untuk mentransfer hasil kueri dari MySQL ke BigQuery (biaya jaringan keluar dari penyedia hosting database Anda)
  • Biaya komputasi untuk menjalankan kueri di BigQuery

Setiap biaya komputasi dan egress MySQL ditagih langsung oleh penyedia MySQL atau host infrastruktur Anda, bukan Google. Untuk membatasi biaya bandwidth, sebaiknya tulis kueri dalam pernyataan EXTERNAL_QUERY sehingga mengecualikan semua kolom dan baris yang tidak diperlukan untuk menghitung hasil akhir.

Tidak ada biaya tambahan untuk menjalankan kueri gabungan di BigQuery. Untuk mengetahui informasi selengkapnya tentang harga BigQuery, lihat Harga.

Jika koneksi Anda menggunakan komponen jaringan Google Cloud , harga berikut juga mungkin berlaku:

Langkah berikutnya