Memuat data PostgreSQL ke BigQuery

Anda dapat memuat data dari PostgreSQL ke BigQuery menggunakan konektor BigQuery Data Transfer Service untuk PostgreSQL. Konektor mendukung instance PostgreSQL yang dihosting di lingkungan lokal, Cloud SQL, dan penyedia cloud publik lainnya seperti Amazon Web Services (AWS) dan Microsoft Azure. Dengan BigQuery Data Transfer Service, Anda dapat menjadwalkan tugas transfer berulang yang menambahkan data terbaru dari PostgreSQL ke BigQuery.

Batasan

Transfer data PostgreSQL tunduk pada batasan berikut:

• Jumlah maksimum operasi transfer serentak ke satu database PostgreSQL ditentukan oleh jumlah maksimum koneksi serentak yang didukung oleh database PostgreSQL. Jumlah tugas transfer serentak harus dibatasi hingga nilai yang lebih kecil dari jumlah maksimum koneksi serentak yang didukung oleh database PostgreSQL.

• Satu konfigurasi transfer hanya dapat mendukung satu proses transfer data pada waktu tertentu. Jika transfer data kedua dijadwalkan untuk berjalan sebelum transfer pertama selesai, maka hanya transfer data pertama yang akan selesai, sedangkan transfer data lain yang tumpang-tindih dengan transfer pertama akan dilewati.

  Untuk menghindari transfer yang dilewati dalam satu konfigurasi transfer, sebaiknya Anda meningkatkan durasi waktu antara transfer data besar dengan mengonfigurasi frekuensi pengulangan.

• Selama transfer data, konektor PostgreSQL mengidentifikasi kolom kunci yang diindeks dan dipartisi untuk mentransfer data Anda dalam batch paralel. Oleh karena itu, sebaiknya Anda menentukan kolom kunci utama atau menggunakan kolom yang diindeks dalam tabel untuk meningkatkan performa dan mengurangi tingkat error dalam transfer data. Pertimbangkan hal berikut:

  • Jika Anda memiliki batasan kunci utama atau terindeks, hanya jenis kolom berikut yang didukung untuk membuat batch paralel:
    • INTEGER
    • TINYINT
    • SMALLINT
    • FLOAT
    • REAL
    • DOUBLE
    • NUMERIC
    • BIGINT
    • DECIMAL
    • DATE
  • Transfer data PostgreSQL yang tidak menggunakan kunci utama atau kolom yang diindeks tidak dapat mendukung lebih dari 2.000.000 record per tabel.

Batasan transfer inkremental

• Anda hanya dapat memilih TIMESTAMP kolom sebagai kolom tanda air.
• Penyerapan inkremental hanya didukung untuk aset dengan kolom watermark yang valid.
• Nilai dalam kolom tanda air harus meningkat secara monoton.
• Transfer inkremental tidak dapat menyinkronkan operasi penghapusan di tabel sumber.
• Konfigurasi transfer tunggal hanya dapat mendukung penyerapan inkremental atau penuh.
• Anda tidak dapat memperbarui objek dalam daftar asset setelah menjalankan penyerapan inkremental pertama.
• Anda tidak dapat mengubah mode penulisan dalam konfigurasi transfer setelah menjalankan penyerapan inkremental pertama.
• Anda tidak dapat mengubah kolom tanda air atau kunci utama setelah menjalankan penyerapan inkremental pertama.
• Tabel BigQuery tujuan dikelompokkan menggunakan kunci utama yang diberikan dan tunduk pada batasan tabel berklaster.
• Saat Anda memperbarui konfigurasi transfer yang ada ke mode penyerapan inkremental untuk pertama kalinya, transfer data pertama setelah pembaruan tersebut akan mentransfer semua data yang tersedia dari sumber data Anda. Setiap transfer data inkremental berikutnya hanya akan mentransfer baris baru dan yang diperbarui dari sumber data Anda.
• Sebaiknya buat indeks pada kolom tanda air. Konektor ini menggunakan kolom tanda air untuk filter dalam transfer inkremental, sehingga pengindeksan kolom ini dapat meningkatkan performa.

Opsi penyerapan data

Bagian berikut memberikan informasi tentang opsi penyerapan data saat Anda menyiapkan transfer data PostgreSQL.

Konfigurasi TLS

Konektor PostgreSQL mendukung konfigurasi untuk keamanan tingkat transportasi (TLS) guna mengenkripsi transfer data Anda ke BigQuery. Konektor PostgreSQL mendukung konfigurasi TLS berikut:

• Mode Enkripsi data, dan verifikasi CA dan nama host. Mode ini melakukan validasi penuh server menggunakan TLS melalui protokol TCPS. Fitur ini mengenkripsi semua data dalam pengiriman dan memverifikasi bahwa sertifikat server database ditandatangani oleh certificate authority (CA) tepercaya. Mode ini juga memeriksa apakah nama host yang Anda hubungkan sama persis dengan Nama Umum (CN) atau Nama Alternatif Subjek (SAN) pada sertifikat server. Mode ini mencegah penyerang menggunakan sertifikat yang valid untuk domain lain guna meniru server database Anda.

  Jika nama host Anda tidak cocok dengan CN atau SAN sertifikat, koneksi akan gagal. Anda harus mengonfigurasi resolusi DNS agar cocok dengan sertifikat atau menggunakan mode keamanan yang berbeda. Gunakan mode ini untuk opsi paling aman guna mencegah serangan person-in-the-middle (PITM).

• Mode Enkripsi data, dan verifikasi CA saja. Mode ini mengenkripsi semua data menggunakan TLS melalui protokol TCPS dan memverifikasi bahwa sertifikat server ditandatangani oleh CA yang dipercaya oleh klien. Namun, mode ini tidak memverifikasi nama host server. Mode ini berhasil terhubung selama sertifikat valid dan diterbitkan oleh CA tepercaya, terlepas dari apakah nama host dalam sertifikat cocok dengan nama host yang Anda hubungkan.

  Gunakan mode ini jika Anda ingin memastikan bahwa Anda terhubung ke server yang sertifikatnya ditandatangani oleh CA tepercaya, tetapi nama host tidak dapat diverifikasi atau Anda tidak memiliki kontrol atas konfigurasi nama host.

• Mode Khusus enkripsi. Mode ini mengenkripsi semua data yang ditransfer antara klien dan server. Tidak melakukan validasi sertifikat atau nama host apa pun.

  Mode ini memberikan tingkat keamanan tertentu dengan melindungi data dalam pengiriman, tetapi dapat rentan terhadap serangan PITM.

  Gunakan mode ini jika Anda perlu memastikan semua data dienkripsi, tetapi tidak dapat atau tidak ingin memverifikasi identitas server. Sebaiknya gunakan mode ini saat bekerja dengan VPC pribadi.

• Mode Tidak ada enkripsi atau verifikasi. Mode ini tidak mengenkripsi data apa pun dan tidak melakukan verifikasi sertifikat atau hostname apa pun. Semua data dikirim sebagai teks biasa.

  Sebaiknya jangan gunakan mode ini di lingkungan tempat data sensitif ditangani. Sebaiknya gunakan mode ini hanya untuk tujuan pengujian di jaringan terisolasi yang tidak mengkhawatirkan keamanan.

Sertifikat Server Tepercaya (PEM)

Jika Anda menggunakan mode Enkripsi data, dan verifikasi CA serta nama host atau mode Enkripsi data, dan verifikasi CA, Anda juga dapat memberikan satu atau beberapa sertifikat yang dienkode PEM. Sertifikat ini diperlukan dalam beberapa skenario saat BigQuery Data Transfer Service perlu memverifikasi identitas server database Anda selama koneksi TLS:

• Jika Anda menggunakan sertifikat yang ditandatangani oleh CA pribadi dalam organisasi Anda atau sertifikat yang ditandatangani sendiri, Anda harus memberikan rantai sertifikat lengkap atau sertifikat yang ditandatangani sendiri. Hal ini diperlukan untuk sertifikat yang dikeluarkan oleh CA internal layanan penyedia cloud terkelola, seperti Amazon Relational Database Service (RDS).
• Jika sertifikat server database Anda ditandatangani oleh CA publik (misalnya, Let's Encrypt, DigiCert, atau GlobalSign), Anda tidak perlu memberikan sertifikat. Sertifikat root untuk CA publik ini telah diinstal sebelumnya dan dipercaya oleh BigQuery Data Transfer Service.

Anda dapat menentukan sertifikat berenkode PEM di kolom Sertifikat PEM Tepercaya dalam konfigurasi transfer, dengan persyaratan berikut:

• Sertifikat harus berupa rantai sertifikat berenkode PEM yang valid.
• Sertifikat harus benar sepenuhnya. Sertifikat yang tidak ada dalam rantai atau konten yang salah menyebabkan koneksi TLS gagal.
• Untuk satu sertifikat, Anda dapat memberikan satu sertifikat yang ditandatangani sendiri dari server database.
• Untuk rantai sertifikat lengkap yang diterbitkan oleh CA pribadi, Anda harus memberikan rantai kepercayaan lengkap. Hal ini mencakup sertifikat dari server database dan sertifikat CA perantara dan root.

Transfer penuh atau inkremental

Anda dapat menentukan cara data dimuat ke BigQuery dengan memilih preferensi penulisan Penuh atau Inkremental di konfigurasi transfer saat menyiapkan transfer PostgreSQL. Transfer inkremental didukung dalam Pratinjau.

Atau, Anda dapat mengonfigurasi transfer data inkremental (Pratinjau) untuk hanya mentransfer data yang berubah sejak transfer data terakhir, bukan memuat seluruh set data dengan setiap transfer data. Jika telah mengonfigurasi transfer data inkremental, Anda harus menentukan mode penulisan tambahkan atau upsert untuk menentukan cara data ditulis ke BigQuery selama transfer data inkremental. Bagian berikut menjelaskan mode penulisan yang tersedia.

Mode tulis tambahan

Mode penulisan penambahan hanya menyisipkan baris baru ke tabel tujuan Anda. Opsi ini menambahkan data yang ditransfer secara ketat tanpa memeriksa apakah ada data yang sudah ada, sehingga mode ini berpotensi menyebabkan duplikasi data dalam tabel tujuan.

Saat memilih mode penambahan, Anda harus memilih kolom watermark. Kolom penanda waktu diperlukan agar konektor PostgreSQL dapat melacak perubahan dalam tabel sumber.

Mode penulisan upsert

Mode penulisan upsert memperbarui baris atau menyisipkan baris baru di tabel tujuan dengan memeriksa kunci utama. Anda dapat menentukan kunci utama agar konektor PostgreSQL dapat menentukan perubahan yang diperlukan untuk menjaga tabel tujuan tetap terbaru dengan tabel sumber. Jika kunci primer yang ditentukan ada di tabel BigQuery tujuan selama transfer data, konektor PostgreSQL akan memperbarui baris tersebut dengan data baru dari tabel sumber. Jika kunci primer tidak ada selama transfer data, konektor PostgreSQL akan menyisipkan baris baru.

Saat memilih mode upsert, Anda harus memilih kolom watermark dan kunci utama:

• Kolom tanda air diperlukan agar konektor PostgreSQL dapat melacak perubahan pada tabel sumber.

  Pilih kolom tanda air yang diperbarui setiap kali baris diubah. Kami merekomendasikan kolom yang mirip dengan kolom UPDATED_AT atau LAST_MODIFIED.

• Kunci utama dapat berupa satu atau beberapa kolom di tabel Anda yang diperlukan agar konektor PostgreSQL dapat menentukan apakah perlu menyisipkan atau memperbarui baris.

  Pilih kolom yang berisi nilai non-null yang unik di semua baris tabel. Sebaiknya gunakan kolom yang menyertakan ID yang dibuat sistem, kode referensi unik (misalnya, ID yang bertambah otomatis), atau ID urutan berbasis waktu yang tidak dapat diubah.

  Untuk mencegah kemungkinan hilangnya data atau kerusakan data, kolom kunci utama yang Anda pilih harus memiliki nilai unik. Jika Anda meragukan keunikan kolom kunci utama yang Anda pilih, sebaiknya gunakan mode penulisan penambahan.

Perilaku penyerapan inkremental

Saat Anda membuat perubahan pada skema tabel di sumber data, transfer data inkremental dari tabel tersebut akan tercermin di BigQuery dengan cara berikut:

Sebelum memulai

• Buat pengguna di database PostgreSQL.
• Pastikan Anda telah menyelesaikan semua tindakan yang diperlukan untuk mengaktifkan BigQuery Data Transfer Service.
• Buat set data BigQuery untuk menyimpan data Anda.
• Pastikan Anda memiliki peran yang diperlukan untuk menyelesaikan tugas dalam dokumen ini.

Peran yang diperlukan

Jika ingin menyiapkan notifikasi operasi transfer untuk Pub/Sub, pastikan Anda memiliki izin Identity and Access Management (IAM) pubsub.topics.setIamPolicy. Izin Pub/Sub tidak diperlukan jika Anda hanya menyiapkan notifikasi email. Untuk informasi selengkapnya, lihat Notifikasi operasi BigQuery Data Transfer Service.

Untuk mendapatkan izin yang diperlukan guna membuat transfer data BigQuery Data Transfer Service, minta administrator untuk memberi Anda peran IAM BigQuery Admin (roles/bigquery.admin) di project Anda. Untuk mengetahui informasi selengkapnya tentang cara memberikan peran, lihat Mengelola akses ke project, folder, dan organisasi.

Peran bawaan ini berisi izin yang diperlukan untuk membuat transfer data BigQuery Data Transfer Service. Untuk melihat izin yang benar-benar diperlukan, perluas bagian Izin yang diperlukan:

Anda mungkin juga bisa mendapatkan izin ini dengan peran khusus atau peran bawaan lainnya.

Untuk mengetahui informasi selengkapnya, lihat Memberikan akses bigquery.admin.

Koneksi jaringan

Jika alamat IP publik tidak tersedia untuk koneksi database PostgreSQL, Anda harus menyiapkan lampiran jaringan.

Untuk petunjuk mendetail tentang penyiapan jaringan yang diperlukan, lihat dokumen berikut:

• Jika Anda mentransfer dari Cloud SQL, lihat Mengonfigurasi akses instance Cloud SQL.
• Jika Anda mentransfer dari AWS, lihat Menyiapkan AWS-Cloud de Confiance VPN dan lampiran jaringan.
• Jika Anda mentransfer dari Azure, lihat MenyiapkanCloud de Confiance VPN dan lampiran jaringan Azure.

Menyiapkan transfer data PostgreSQL

Tambahkan data PostgreSQL ke BigQuery dengan menyiapkan konfigurasi transfer menggunakan salah satu opsi berikut:

bq mk
    --transfer_config
    --project_id=PROJECT_ID
    --data_source=DATA_SOURCE
    --display_name=DISPLAY_NAME
    --target_dataset=DATASET
    --params='PARAMETERS'

bq mk
    --transfer_config
    --target_dataset=mydataset
    --data_source=postgresql
    --display_name='My Transfer'
    --params='{"assets":["DB1/PUBLIC/DEPARTMENT","DB1/PUBLIC/EMPLOYEES"],
        "connector.authentication.username": "User1",
        "connector.authentication.password":"ABC12345",
        "connector.database":"DB1",
        "connector.endpoint.host":"192.168.0.1",
        "connector.endpoint.port":5432,
        "ingestionType":"incremental",
        "writeMode":"WRITE_MODE_APPEND",
        "watermarkColumns":["createdAt","createdAt"],
        "primaryKeys":[['dep_id'], ['report_by','report_title']],
        "connector.tls.mode": "ENCRYPT_VERIFY_CA_AND_HOST",
        "connector.tls.trustedServerCertificate": "PEM-encoded certificate"}'

      "primaryKeys":[['dep_id'], ['report_by','report_title']],
      "assets":["DB1/USER1/DEPARTMENT","DB1/USER1/EMPLOYEES"],
  

Untuk menjalankan transfer data secara manual di luar jadwal rutin, Anda dapat memulai operasi pengisian ulang.

Pemetaan jenis data

Tabel berikut memetakan jenis data PostgreSQL ke jenis data BigQuery yang sesuai.

Memecahkan masalah

Jika Anda mengalami masalah saat menyiapkan transfer data, lihat Masalah transfer PostgreSQL.

Harga

Untuk mengetahui informasi harga tentang transfer PostgreSQL, lihat Harga Data Transfer Service.

Langkah berikutnya

• Baca ringkasan tentang BigQuery Data Transfer Service.
• Pelajari cara mengelola transfer, termasuk mendapatkan informasi tentang konfigurasi transfer, membuat listingan konfigurasi transfer, dan melihat histori pelaksanaan transfer.
• Pelajari cara memuat data dengan operasi lintas-cloud.