Menjalankan kueri gabungan PostgreSQL

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

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

Batasan

Kueri gabungan PostgreSQL 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 BigQuery atau set data permanen yang langsung dipetakan ke tabel PostgreSQL tidak didukung.
  • Latensi kueri gabungan mungkin jauh lebih tinggi daripada menjalankan kueri langsung di PostgreSQL.
  • Layanan koneksi yang mendasarinya mungkin menghibernasi koneksi yang tidak aktif. Kueri pertama yang menggunakan koneksi PostgreSQL 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 PostgreSQL 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 PostgreSQL. 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 PostgreSQL internal untuk membatasi baris dan kolom yang ditransfer di seluruh jaringan.

  • Hanya autentikasi nama pengguna dan sandi database PostgreSQL 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 16-bit (int2, smallint, smallserial) atau kolom float presisi tunggal (float4, real). 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(small_col AS INT64) AS small_col,
CAST(real_col AS FLOAT64) AS real_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.

  • time [ without time zone ] dan time with time zone (timetz) memotong nilai pecahan subdetik (mikrodetik) menjadi detik utuh. Jika presisi mikrodetik diperlukan, format atau transmisikan kolom ke text atau varchar dalam pernyataan EXTERNAL_QUERY.

  • Semua kolom numeric dan decimal PostgreSQL dipetakan ke BIGNUMERIC(76, 38). Kolom dengan skala lebih besar dari 38 dibulatkan menjadi 38 tempat desimal, dan nilai yang melebihi rentang BIGNUMERIC (lebih dari 38 digit bilangan bulat penuh) akan gagal dengan error di luar rentang. Selain itu, nilai khusus NaN, Infinity, dan -Infinity tidak didukung dan gagal saat runtime. Untuk mengkueri angka dengan bagian bilangan bulat yang lebih besar, presisi pecahan yang lebih tinggi, atau nilai khusus, transmisikan kolom ke text dalam pernyataan EXTERNAL_QUERY.

  • Nilai character(n) / char(n) (bpchar) dengan panjang tetap mempertahankan spasi di akhir hingga panjang n saat dipetakan ke STRING BigQuery.

  • Tanggal dan stempel waktu sebelum 15 Oktober 1582 akan mengalami pergeseran kalender selama 10 hari karena transisi kalender Gregorian historis. Transmisikan tanggal historis ke text dalam pernyataan EXTERNAL_QUERY untuk mempertahankan tanggal yang tepat.

Lingkungan dan versi PostgreSQL yang didukung

Gabungan kueri mendukung database PostgreSQL di seluruh lingkungan deployment berikut:

  • Database PostgreSQL yang dihosting di cloud atau lokal yang dapat diakses melalui alamat IP publik atau lampiran jaringan (melalui Cloud VPN atau Cloud Interconnect)
  • Instance PostgreSQL yang dihosting sendiri di Compute Engine atau infrastruktur lainnya
  • PostgreSQL 16 (Direkomendasikan) dan PostgreSQL 15

Sebelum memulai

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

Peran yang diperlukan

Untuk mendapatkan izin yang diperlukan untuk membuat kueri PostgreSQL, 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 PostgreSQL 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 PostgreSQL:

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/postgres_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 PostgreSQL

Anda dapat mengkueri tabel information_schema PostgreSQL menggunakan EXTERNAL_QUERY untuk mengambil metadata database:

Mencantumkan semua tabel dalam skema

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT table_schema, table_name, table_type
     FROM information_schema.tables
     WHERE table_schema = 'SCHEMA_NAME'
     ORDER BY table_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
     FROM information_schema.columns
     WHERE table_schema = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME'
     ORDER BY ordinal_position''');

Pemetaan jenis data

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

Jenis data PostgreSQL Jenis data BigQuery
Jenis boolean
bool, boolean BOOL
Jenis numerik
int2, smallint, smallserial INT64 (kueri interaktif; lihat Batasan untuk CREATE TABLE ... AS SELECT)
int4, integer, serial INT64
int8, bigint, bigserial INT64
numeric, decimal BIGNUMERIC(76, 38)
float4, real FLOAT64 (kueri interaktif; lihat Batasan untuk CREATE TABLE ... AS SELECT)
float8, double precision FLOAT64
Jenis tanggal dan waktu
date DATE
timestamp [ without time zone ] DATETIME
timestamp with time zone, timestamptz TIMESTAMP
time [ without time zone ] TIME
time with time zone, timetz TIME
Jenis karakter dan teks
character(n), char(n), bpchar STRING
character varying(n), varchar(n) STRING
text STRING

Harga

Biaya menjalankan kueri gabungan didasarkan pada tiga faktor:

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

Semua biaya komputasi dan egress PostgreSQL ditagih langsung oleh penyedia PostgreSQL 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