Mengekspor data ke AlloyDB (reverse ETL)

Alur kerja ekstrak, transformasi, dan muat terbalik (ETL terbalik) dari BigQuery ke AlloyDB untuk PostgreSQL memungkinkan Anda memindahkan data dari BigQuery ke database AlloyDB. Anda dapat menggunakan pernyataan EXPORT DATA untuk memproses atau memfilter data dengan kueri sebelum mengekspornya, atau Anda dapat menyinkronkan tabel BigQuery ke AlloyDB untuk menyalin atau mencerminkan tabel lengkap.

Sebelum memulai

  • Buat cluster dan instance AlloyDB, yang mencakup database, skema, dan tabel, untuk menerima data yang diekspor. Anda harus memiliki skema dan tabel target sebelum menjalankan tugas ekspor. Untuk mode UPSERT, tabel tujuan harus memiliki batasan PRIMARY KEY atau setidaknya satu batasan UNIQUE.

  • Instance AlloyDB target harus berupa instance PRIMARY dalam status READY.

  • Buat koneksi BigQuery untuk terhubung ke instance AlloyDB Anda. Koneksi harus menggunakan autentikasi nama pengguna dan sandi. Pengguna database memerlukan hak istimewa berikut:

    Jenis tabel Hak istimewa tabel tujuan Hak istimewa skema tujuan Hak istimewa database Catatan
    Tabel dengan batasan PRIMARY KEY atau UNIQUE INSERT, UPDATE, SELECT USAGE CONNECT, TEMPORARY Diperlukan untuk mode UPSERT dan APPEND guna mendukung penggantian percobaan ulang otomatis.
    Tabel tanpa batasan INSERT USAGE CONNECT
  • Untuk menggunakan fitur ini, Anda harus membuat pemesanan paket BigQuery Enterprise atau Enterprise Plus. Ekspor ke AlloyDB hanya didukung untuk pemesanan dengan penetapan QUERY.

  • Berikan peran Identity and Access Management (IAM) yang memberi pengguna izin yang diperlukan untuk melakukan setiap tugas dalam dokumen ini.

Peran yang diperlukan

Untuk mendapatkan izin yang Anda perlukan untuk mengekspor data BigQuery ke AlloyDB, minta administrator Anda untuk memberi Anda peran IAM berikut pada project Anda:

  • Mengekspor data dari tabel BigQuery: BigQuery Data Viewer (roles/bigquery.dataViewer)
  • Menjalankan tugas ekstrak: BigQuery User (roles/bigquery.user)
  • Menggunakan koneksi BigQuery: BigQuery Connection User (roles/bigquery.connectionUser)
  • Terhubung ke instance AlloyDB: Klien AlloyDB (roles/alloydb.client) - akun layanan koneksi

Untuk mengetahui informasi selengkapnya tentang pemberian peran, lihat Mengelola akses ke project, folder, dan organisasi.

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

Batasan

  • Ekspor AlloyDB hanya mendukung ekspor batch. Anda tidak dapat menggunakan kueri berkelanjutan untuk mengekspor ke AlloyDB.

  • Untuk menggunakan mode UPSERT, tabel AlloyDB tujuan harus memiliki PRIMARY KEY atau setidaknya satu batasan UNIQUE, dan kueri SELECT Anda harus memproyeksikan semua kolom yang termasuk dalam batasan tersebut. Anda hanya dapat mengekspor tabel tanpa kunci utama atau batasan unik menggunakan mode APPEND.

  • Mengekspor data dalam mode UPSERT memiliki throughput tulis yang lebih rendah serta overhead CPU dan I/O yang lebih tinggi pada instance AlloyDB dibandingkan mode APPEND. Meskipun mode APPEND mengalirkan baris secara langsung menggunakan perintah COPY massal PostgreSQL, mode UPSERT melakukan operasi insert-or-update (INSERT ... ON CONFLICT DO UPDATE), yang mengharuskan AlloyDB memeriksa indeks unik untuk kunci yang ada dan memperbarui baris yang cocok. Saat Anda hanya mengekspor data baru, gunakan mode APPEND untuk performa yang optimal.

  • Jika tabel tujuan tidak memiliki batasan yang ditentukan, menjalankan beberapa ekspor APPEND dengan data yang sama akan menghasilkan baris duplikat.

  • Sebaiknya jangan jalankan beberapa tugas EXPORT DATA serentak ke tabel AlloyDB yang sama. Melakukannya dapat menyebabkan perilaku yang tidak dapat diprediksi, seperti kehilangan data atau kegagalan tugas. Sebaiknya pastikan bahwa hanya satu tugas ekspor yang menulis ke tabel tertentu dalam satu waktu.

  • Hanya autentikasi nama pengguna dan sandi melalui koneksi BigQuery yang didukung.

  • Jenis data BigQuery ARRAY, BYTES, GEOGRAPHY, INTERVAL, dan STRUCT tidak didukung.

  • Jika pernyataan SELECT BigQuery menghilangkan kolom yang ada di tabel AlloyDB target, kolom tersebut harus mengizinkan nilai NULL atau memiliki nilai default yang ditentukan di AlloyDB. Jika memiliki batasan NOT NULL dan tidak ada nilai default, ekspor akan gagal.

  • Ekspor ke AlloyDB hanya didukung untuk edisi BigQuery Enterprise atau Enterprise Plus. Edisi Standar BigQuery dan komputasi on-demand tidak didukung. Untuk mengetahui informasi selengkapnya, lihat Fitur administrasi.

  • Tugas BigQuery, seperti tugas ekstrak ke AlloyDB, memiliki durasi maksimum 6 jam. Untuk ekspor yang sangat besar, sebaiknya bagi ekspor menjadi beberapa tugas yang lebih kecil.

Pertimbangan lokasi

Mengekspor data ke AlloyDB memiliki persyaratan khusus terkait lokasi set data BigQuery dan instance AlloyDB Anda:

  • Ekspor region yang sama: Instance AlloyDB target harus berada di region Google Cloud yang sama persis dengan set data BigQuery. Misalnya, set data di us-east1 hanya dapat diekspor ke instance AlloyDB di us-east1.

  • Ekspor multi-region:

    • Set data di multi-region US hanya dapat diekspor ke instance AlloyDB yang berada di region us-central1.
    • Set data di multi-region EU hanya dapat diekspor ke instance AlloyDB yang berada di region europe-west4.

Ekspor lintas region selain kombinasi yang disebutkan sebelumnya tidak didukung.

Mengonfigurasi ekspor dengan alloydb_options

Anda dapat menggunakan opsi alloydb_options dalam pernyataan EXPORT DATA untuk menentukan skema, tabel, mode tulis, dan koneksi maksimum AlloyDB tujuan. Konfigurasi dinyatakan sebagai string JSON. Hanya parameter table yang diperlukan. Semua parameter lainnya bersifat opsional.

Saat mengonfigurasi ekspor, kolom dalam pernyataan SELECT harus memiliki alias yang cocok dengan nama kolom dalam tabel AlloyDB target.

EXPORT DATA
  WITH CONNECTION `PROJECT_ID.LOCATION.CONNECTION_ID`
  OPTIONS(
    format='ALLOYDB',
    uri="https://alloydb.googleapis.com/v1/projects/PROJECT_ID/locations/LOCATION/clusters/CLUSTER_ID/instances/INSTANCE_ID",
    alloydb_options="""{
      "schema": "SCHEMA_NAME",
      "table": "TABLE_NAME",
      "write_mode": "WRITE_MODE",
      "max_parallel_connections": MAX_CONNECTIONS
    }"""
  )
AS SELECT * FROM `mydataset.table1`;

Ganti kode berikut:

  • PROJECT_ID: nama project Google Cloud Anda.
  • LOCATION: lokasi koneksi dan instance target Anda.
  • CONNECTION_ID: nama koneksi BigQuery Anda.
  • CLUSTER_ID: nama cluster AlloyDB Anda.
  • INSTANCE_ID: nama instance AlloyDB target Anda.
  • SCHEMA_NAME (Opsional): nama skema tujuan di AlloyDB. Jika tidak diberikan, skema default yang dikonfigurasi untuk pengguna database akan digunakan (biasanya public).
  • TABLE_NAME: nama tabel tujuan yang ada di AlloyDB, tanpa awalan skema.
  • WRITE_MODE (Opsional): mode tulis yang akan digunakan saat mengekspor data. Nilai yang didukung:
    • APPEND: secara default, nilai ini langsung menyisipkan baris yang diekspor untuk throughput yang lebih cepat saat Anda mengekspor data baru. Jika tabel tujuan memiliki batasan PRIMARY KEY atau UNIQUE dan terjadi konflik kunci duplikat, misalnya, karena kunci yang cocok sudah ada dalam tabel atau upaya tugas yang gagal dicoba lagi setelah menulis data parsial, BigQuery akan otomatis mencoba lagi ekspor dalam mode UPSERT. Jika Anda ingin ekspor memperbarui baris yang ada, tentukan UPSERT secara langsung untuk menghindari overhead percobaan ulang.
    • UPSERT: memperbarui data yang ada jika ada kunci yang cocok, atau menyisipkan data baru jika tidak ditemukan kunci yang cocok. Memerlukan batasan PRIMARY KEY atau UNIQUE pada tabel tujuan, dengan semua kolom kunci dipilih dalam kueri. BigQuery mencocokkan baris menggunakan PRIMARY KEY tabel, atau batasan UNIQUE jika tabel tidak memiliki kunci utama. Jika tabel memiliki batasan UNIQUE tambahan dan baris yang diekspor berisi nilai duplikat di salah satu kolom tersebut, ekspor akan gagal.
  • MAX_CONNECTIONS (Opsional): jumlah maksimum koneksi paralel serentak dari worker BigQuery ke instance AlloyDB. Jika ditentukan, nilai harus 1 atau lebih besar. Membatasi koneksi dapat mencegah kelebihan beban pada instance target selama ekspor besar.

Contoh pernyataan ekspor

Bagian ini memberikan contoh pernyataan EXPORT DATA untuk berbagai kasus penggunaan.

Ekspor dalam mode APPEND

Contoh berikut menambahkan kolom yang dipilih dari tabel mydataset.table1 ke tabel AlloyDB bernama my_target_table:

EXPORT DATA
  WITH CONNECTION `myproject.us-central1.my-alloydb-conn`
  OPTIONS (
    format='ALLOYDB',
    uri="https://alloydb.googleapis.com/v1/projects/myproject/locations/us-central1/clusters/my-cluster/instances/my-instance",
    alloydb_options="""{
      "schema": "public",
      "table": "my_target_table",
      "write_mode": "APPEND"
    }"""
  )
AS SELECT
  col1 AS id,
  col2 AS name,
  col3 AS value
FROM
  `mydataset.table1`;

Ekspor dalam mode UPSERT

Contoh berikut meng-upsert kumpulan data dari mydataset.customers ke dalam tabel customer_profiles AlloyDB, yang mencakup pembaruan kumpulan data yang ada yang cocok dengan kunci utama customer_id dan penyisipan kumpulan data baru:

EXPORT DATA
  WITH CONNECTION `myproject.us-central1.my-alloydb-conn`
  OPTIONS (
    format='ALLOYDB',
    uri="https://alloydb.googleapis.com/v1/projects/myproject/locations/us-central1/clusters/my-cluster/instances/my-instance",
    alloydb_options="""{
      "schema": "public",
      "table": "customer_profiles",
      "write_mode": "UPSERT",
      "max_parallel_connections": 8
    }"""
  )
AS SELECT
  customer_id,
  email,
  status,
  updated_at
FROM
  `mydataset.customers`;

Menyinkronkan tabel untuk ekspor

Untuk salinan tabel lengkap atau pencerminan otomatis tanpa memerlukan transformasi berbasis SQL, Anda dapat menyinkronkan tabel BigQuery langsung ke AlloyDB. Meskipun pernyataan EXPORT DATA memungkinkan Anda menggunakan kueri SELECT untuk memproses, memberi alias, atau memfilter data sebelum dipindahkan, Anda dapat menggunakan sinkronisasi tabel untuk migrasi satu per satu tabel BigQuery.

Sinkronisasi tabel tersedia dalam mode berikut:

  • Impor satu kali: Membuat salinan tabel BigQuery yang dapat ditulis dan independen dalam AlloyDB.
  • Sinkronisasi berkala (pencerminan): Membuat tabel lokal hanya baca di AlloyDB yang otomatis diperbarui dari BigQuery sesuai jadwal yang ditentukan.

Untuk mengetahui informasi selengkapnya, lihat Menyinkronkan data BigQuery ke AlloyDB.

Harga

Saat mengekspor data ke AlloyDB menggunakan pernyataan EXPORT DATA, Anda akan ditagih menggunakan harga komputasi kapasitas BigQuery.

Setelah data diekspor, Anda dikenai biaya penyimpanan data di AlloyDB. Untuk mengetahui informasi selengkapnya, lihat Harga AlloyDB untuk PostgreSQL.