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 batasanPRIMARY KEYatau setidaknya satu batasanUNIQUE.Instance AlloyDB target harus berupa instance
PRIMARYdalam statusREADY.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 KEYatauUNIQUEINSERT,UPDATE,SELECTUSAGECONNECT,TEMPORARYDiperlukan untuk mode UPSERTdanAPPENDguna mendukung penggantian percobaan ulang otomatis.Tabel tanpa batasan INSERTUSAGECONNECTUntuk 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 memilikiPRIMARY KEYatau setidaknya satu batasanUNIQUE, dan kueriSELECTAnda harus memproyeksikan semua kolom yang termasuk dalam batasan tersebut. Anda hanya dapat mengekspor tabel tanpa kunci utama atau batasan unik menggunakan modeAPPEND.Mengekspor data dalam mode
UPSERTmemiliki throughput tulis yang lebih rendah serta overhead CPU dan I/O yang lebih tinggi pada instance AlloyDB dibandingkan modeAPPEND. Meskipun modeAPPENDmengalirkan baris secara langsung menggunakan perintahCOPYmassal PostgreSQL, modeUPSERTmelakukan 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 modeAPPENDuntuk performa yang optimal.Jika tabel tujuan tidak memiliki batasan yang ditentukan, menjalankan beberapa ekspor
APPENDdengan data yang sama akan menghasilkan baris duplikat.Sebaiknya jangan jalankan beberapa tugas
EXPORT DATAserentak 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, danSTRUCTtidak didukung.Jika pernyataan
SELECTBigQuery menghilangkan kolom yang ada di tabel AlloyDB target, kolom tersebut harus mengizinkan nilaiNULLatau memiliki nilai default yang ditentukan di AlloyDB. Jika memiliki batasanNOT NULLdan 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-east1hanya dapat diekspor ke instance AlloyDB dius-east1.Ekspor multi-region:
- Set data di multi-region
UShanya dapat diekspor ke instance AlloyDB yang berada di regionus-central1. - Set data di multi-region
EUhanya dapat diekspor ke instance AlloyDB yang berada di regioneurope-west4.
- Set data di multi-region
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 (biasanyapublic).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 batasanPRIMARY KEYatauUNIQUEdan 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 modeUPSERT. Jika Anda ingin ekspor memperbarui baris yang ada, tentukanUPSERTsecara 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 batasanPRIMARY KEYatauUNIQUEpada tabel tujuan, dengan semua kolom kunci dipilih dalam kueri. BigQuery mencocokkan baris menggunakanPRIMARY KEYtabel, atau batasanUNIQUEjika tabel tidak memiliki kunci utama. Jika tabel memiliki batasanUNIQUEtambahan 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 harus1atau 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.