ETL terbalik dari Snowflake ke Spanner menggunakan BQ

1. Membangun Pipeline Reverse ETL dari Snowflake ke Spanner menggunakan Google Cloud Storage dan BigQuery

Pengantar

Dalam codelab ini, pipeline Reverse ETL dibangun dari Snowflake ke Spanner. Biasanya, pipeline ETL (Extract, Transform, Load) memindahkan data dari database operasional ke data warehouse seperti Snowflake untuk analisis. Pipeline Reverse ETL melakukan hal sebaliknya: memindahkan data yang telah dikurasi dan diproses dari data warehouse kembali ke sistem operasional tempat data tersebut dapat mendukung aplikasi, menyajikan fitur yang terlihat oleh pengguna, atau digunakan untuk pengambilan keputusan real-time.

Tujuannya adalah memindahkan set data gabungan dari tabel Snowflake Iceberg ke Spanner, database relasional yang didistribusikan secara global dan ideal untuk aplikasi dengan ketersediaan tinggi.

Untuk mencapainya, Google Cloud Storage (GCS) dan BigQuery digunakan sebagai langkah perantara. Berikut adalah uraian aliran data dan alasan di balik arsitektur ini:

1. Snowflake ke Google Cloud Storage (GCS) dalam Format Iceberg:

• Langkah pertama adalah mengeluarkan data dari Snowflake dalam format terbuka yang terdefinisi dengan baik. Tabel diekspor dalam format Apache Iceberg. Proses ini menulis data pokok sebagai serangkaian file Parquet dan metadata tabel (skema, partisi, lokasi file) sebagai file JSON dan Avro. Menyusun struktur tabel lengkap ini di GCS membuat data dapat diakses dan portabel ke sistem apa pun yang memahami format Iceberg.

2. Mengonversi tabel Iceberg di GCS menjadi tabel eksternal BigLake BigQuery:

• Daripada memuat data langsung dari GCS ke Spanner, BigQuery digunakan sebagai perantara yang efektif. Anda akan membuat tabel eksternal BigLake di BigQuery yang mengarah langsung ke file metadata Iceberg di GCS. Pendekatan ini memiliki beberapa manfaat:
• Tidak Ada Duplikasi Data: BigQuery membaca struktur tabel dari metadata dan membuat kueri file data Parquet di tempat tanpa menyerapnya, sehingga menghemat waktu dan biaya penyimpanan secara signifikan.
• Kueri Gabungan: Memungkinkan menjalankan kueri SQL yang kompleks pada data GCS seolah-olah data tersebut adalah tabel BigQuery native.

3. BigQuery ke Spanner:

• Langkah terakhir adalah memindahkan data dari BigQuery ke Spanner. Anda akan melakukannya menggunakan fitur canggih di BigQuery yang disebut kueri EXPORT DATA, yang merupakan langkah "Reverse ETL".
• Kesiapan Operasional: Spanner dirancang untuk beban kerja transaksional, yang memberikan konsistensi kuat dan ketersediaan tinggi untuk aplikasi. Dengan memindahkan data ke Spanner, data tersebut dapat diakses oleh aplikasi yang berinteraksi dengan pengguna, API, dan sistem operasional lainnya yang memerlukan pencarian titik berlatensi rendah.
• Skalabilitas: Pola ini memungkinkan pemanfaatan kemampuan analisis BigQuery untuk memproses set data besar, lalu menyajikan hasilnya secara efisien melalui infrastruktur Spanner yang skalabel secara global.

Layanan dan Terminologi

• Snowflake - Platform data cloud yang menyediakan data warehouse-as-a-service.
• Spanner - Database relasional yang terkelola sepenuhnya dan didistribusikan secara global.
• Google Cloud Storage - Penawaran penyimpanan blob Google Cloud.
• BigQuery - Data warehouse serverless yang terkelola sepenuhnya untuk analisis.
• Iceberg - Format tabel terbuka yang ditentukan oleh Apache yang menyediakan abstraksi atas format file data open source umum.
• Parquet - Format file data biner kolom open source dari Apache.

Yang akan Anda pelajari

• Cara memuat data ke Snowflake
• Cara membuat Bucket GCS
• Cara mengekspor tabel Snowflake ke GCS dalam format Iceberg
• Cara menyiapkan instance Spanner
• Cara memuat Tabel Eksternal BigLake di BigQuery ke Spanner

2. Penyiapan, Persyaratan & Batasan

Prasyarat

• Akun Snowflake
• Akun Google Cloud dengan reservasi tingkat Enterprise atau yang lebih tinggi BigQuery diperlukan untuk mengekspor dari BigQuery ke Spanner.
• Akses ke Konsol Google Cloud melalui browser web
• Terminal untuk menjalankan perintah Google Cloud CLI
• Jika organisasi Google Cloud Anda mengaktifkan kebijakan iam.allowedPolicyMemberDomains, administrator mungkin perlu memberikan pengecualian untuk mengizinkan akun layanan dari domain eksternal. Hal ini akan dibahas pada langkah selanjutnya jika berlaku.

Batasan

Penting untuk mengetahui batasan tertentu dan ketidakcocokan jenis data yang dapat muncul dalam pipeline ini.

Snowflake ke Iceberg

Jenis data kolom berbeda antara Snowflake dan Iceberg. Informasi tentang cara menerjemahkan di antara keduanya tersedia di dokumentasi Snowflake.

Iceberg ke BigQuery

Saat menggunakan BigQuery untuk membuat kueri tabel Iceberg, ada beberapa batasan. Untuk mengetahui daftar lengkapnya, lihat dokumentasi BigQuery. Perhatikan bahwa jenis seperti BIGNUMERIC, INTERVAL, JSON, RANGE, atau GEOGRAPHY saat ini tidak didukung.

BigQuery ke Spanner

Perintah EXPORT DATA dari BigQuery ke Spanner tidak mendukung semua jenis data BigQuery. Mengekspor tabel dengan jenis berikut akan menghasilkan error:

• STRUCT
• GEOGRAPHY
• DATETIME
• RANGE
• TIME

Selain itu, jika project BigQuery menggunakan dialek GoogleSQL, jenis numerik berikut juga tidak didukung untuk diekspor ke Spanner:

• BIGNUMERIC

Untuk mengetahui daftar lengkap dan terbaru batasan, lihat dokumentasi resmi: Batasan Mengekspor ke Spanner.

Snowflake

Untuk codelab ini, Anda dapat menggunakan akun Snowflake yang ada, atau menyiapkan akun uji coba gratis.

Izin IAM Google Cloud Platform

Akun Google akan memerlukan izin berikut untuk menjalankan semua langkah dalam codelab ini.

Akun Layanan
iam.serviceAccountKeys.create	Mengizinkan pembuatan Akun Layanan.
Spanner
spanner.instances.create	Memungkinkan pembuatan instance Spanner baru.
spanner.databases.create	Mengizinkan menjalankan pernyataan DDL untuk membuat
spanner.databases.updateDdl	Memungkinkan menjalankan pernyataan DDL untuk membuat tabel dalam database.
Google Cloud Storage
storage.buckets.create	Memungkinkan pembuatan bucket GCS baru untuk menyimpan file Parquet yang diekspor.
storage.objects.create	Mengizinkan penulisan file Parquet yang diekspor ke bucket GCS.
storage.objects.get	Mengizinkan BigQuery membaca file Parquet dari bucket GCS.
storage.objects.list	Mengizinkan BigQuery mencantumkan file Parquet di bucket GCS.
Dataflow
Dataflow.workitems.lease	Mengizinkan klaim item kerja dari Dataflow.
Dataflow.workitems.sendMessage	Memungkinkan pekerja Dataflow mengirim pesan kembali ke layanan Dataflow.
Logging.logEntries.create	Memungkinkan pekerja Dataflow menulis entri log ke Google Cloud Logging.

Untuk mempermudah, peran bawaan yang berisi izin ini dapat digunakan.

roles/resourcemanager.projectIamAdmin

roles/iam.serviceAccountKeyAdmin

roles/spanner.instanceAdmin

roles/spanner.databaseAdmin

roles/storage.admin

roles/dataflow.serviceAgent

roles/dataflow.worker

roles/dataflow.serviceAgent

Menyiapkan Properti yang Dapat Digunakan Kembali

Ada beberapa nilai yang akan diperlukan berulang kali di sepanjang lab ini. Untuk mempermudah, kita akan menetapkan nilai ini ke variabel shell untuk digunakan nanti.

• GCP_REGION - Region spesifik tempat resource GCP akan berada. Daftar wilayah dapat ditemukan di sini.
• GCP_PROJECT - ID Project GCP yang akan digunakan.
• GCP_BUCKET_NAME - Nama Bucket GCS yang akan dibuat, dan tempat file data akan disimpan.

Nama bucket harus unik secara global. Pola umum adalah: [organization_name]-[project_id]-[environment]-[purpose].

export GCP_REGION = <GCP REGION HERE> 
export GCP_PROJECT= <GCP PROJECT HERE>
export GCS_BUCKET_NAME = <GCS BUCKET NAME HERE>
export SPANNER_INSTANCE = <SPANNER INSTANCE ID HERE>
export SPANNER_DB = <SPANNER DATABASE ID HERE>

Perhatikan bahwa variabel lingkungan ini hanya akan ditetapkan di instance shell tempat variabel tersebut dijalankan, dan harus ditetapkan lagi jika Anda menjalankan perintah di lab ini di instance shell yang berbeda nanti.

Project Google Cloud

Project adalah unit dasar organisasi di Google Cloud. Jika administrator telah menyediakannya untuk digunakan, langkah ini dapat dilewati.

Project dapat dibuat menggunakan CLI seperti ini:

gcloud projects create $GCP_PROJECT
gcloud config set project $GCP_PROJECT

Pelajari lebih lanjut cara membuat dan mengelola project di sini.

Menyiapkan Spanner

Untuk mulai menggunakan Spanner, Anda perlu menyediakan instance dan database. Detail tentang cara mengonfigurasi dan membuat Instance Spanner dapat ditemukan di sini.

Buat Instance

gcloud spanner instances create $SPANNER_INSTANCE \
--config=regional-$GCP_REGION \
--description="Codelabs Snowflake RETL" \
--processing-units=100 \
--edition=ENTERPRISE

Membuat Database

gcloud spanner databases create $SPANNER_DB \
--instance=$SPANNER_INSTANCE

3. Membuat bucket Google Cloud Storage

Google Cloud Storage (GCS) akan digunakan untuk menyimpan file data Parquet dan metadata Iceberg yang dihasilkan oleh Snowflake. Untuk melakukannya, bucket baru harus dibuat terlebih dahulu untuk digunakan sebagai tujuan file. Dari jendela Terminal di komputer lokal, ikuti langkah-langkah berikut.

Buat bucket

Gunakan perintah berikut untuk membuat bucket penyimpanan di region tertentu (misalnya, us-central1).

gcloud storage buckets create gs://$GCS_BUCKET_NAME --location=$GCP_REGION

Verifikasi pembuatan bucket

Setelah perintah tersebut berhasil, periksa hasilnya dengan mencantumkan semua bucket. Bucket baru akan muncul dalam daftar hasil. Referensi bucket biasanya muncul dengan awalan gs:// di depan nama bucket.

gcloud storage ls | grep gs://$GCS_BUCKET_NAME

Menguji izin menulis

Langkah ini memastikan bahwa lingkungan lokal diautentikasi dengan benar dan memiliki izin yang diperlukan untuk menulis file ke bucket yang baru dibuat.

echo "Hello, GCS" | gcloud storage cp - gs://$GCS_BUCKET_NAME/hello.txt

Memverifikasi file yang diupload

Mencantumkan objek dalam bucket. Jalur lengkap file yang baru saja diupload akan muncul.

gcloud storage ls gs://$GCS_BUCKET_NAME

Anda akan melihat output berikut:

gs://$GCS_BUCKET_NAME/hello.txt

Untuk melihat isi objek dalam bucket, gcloud storage cat dapat digunakan.

gcloud storage cat gs://$GCS_BUCKET_NAME/hello.txt

Isi file akan terlihat:

Hello, GCS

Membersihkan file pengujian

Bucket Cloud Storage kini telah disiapkan. File pengujian sementara kini dapat dihapus.

gcloud storage rm gs://$GCS_BUCKET_NAME/hello.txt

Output akan mengonfirmasi penghapusan:

Removing gs://$GCS_BUCKET_NAME/hello.txt...
/ [1 objects]
Operation completed over 1 objects.

4. Mengekspor dari Snowflake ke GCS

Untuk lab ini, Anda akan menggunakan set data TPC-H, yang merupakan benchmark standar industri untuk sistem dukungan keputusan. Skemanya memodelkan lingkungan bisnis yang realistis dengan pelanggan, pesanan, pemasok, dan suku cadang, sehingga sempurna untuk mendemonstrasikan skenario analisis dan pergerakan data di dunia nyata. Set data ini tersedia secara default di semua akun Snowflake.

Daripada menggunakan tabel TPC-H mentah yang dinormalisasi, Anda akan membuat tabel baru yang diagregasi. Tabel baru ini akan menggabungkan data dari tabel orders, customer, dan nation untuk menghasilkan tampilan yang didenormalisasi dan diringkas dari total penjualan nasional. Langkah pra-penggabungan ini adalah praktik umum dalam analisis, karena menyiapkan data untuk kasus penggunaan tertentu—dalam skenario ini, untuk digunakan oleh aplikasi operasional.

Mengizinkan Snowflake Mengakses Google Cloud Storage

Agar Snowflake dapat menulis data ke bucket GCS, dua hal yang perlu dibuat adalah Volume Eksternal dan izin yang diperlukan.

• Volume Eksternal adalah objek Snowflake yang menyediakan link aman ke lokasi tertentu di bucket GCS. Objek ini tidak menyimpan data itu sendiri, tetapi menyimpan konfigurasi yang diperlukan agar Snowflake dapat mengakses penyimpanan cloud.
• Untuk keamanan, bucket penyimpanan cloud bersifat pribadi secara default. Saat Volume Eksternal dibuat, Snowflake akan membuat Akun Layanan khusus. Akun layanan ini harus diberi izin untuk membaca dari dan menulis ke bucket.

Buat database

1. Di menu samping kiri, di bagian Horizon Catalog, arahkan kursor ke Catalog, lalu klik Database Explorer
2. Setelah berada di halaman Databases, klik tombol + Database di kanan atas.
3. Beri nama db baru codelabs_retl_db

Membuat Lembar Kerja

Untuk menjalankan perintah SQL terhadap database, lembar kerja akan diperlukan.

Untuk membuat lembar kerja:

1. Di menu sebelah kiri, di bagian Bekerja dengan data, arahkan kursor ke Project, lalu klik Ruang kerja
2. Di sidebar Ruang Kerja Saya, klik tombol + Tambahkan baru, lalu pilih File SQL

Semua perintah SQL selanjutnya ke Snowflake dapat dijalankan dengan mengganti konten satu file ini. Bagian paragraf file dapat dijalankan dengan menempatkan kursor pada paragraf tersebut dan menekan Ctrl + Enter di keyboard.

Membuat Volume Eksternal

Jalankan perintah berikut di worksheet Snowflake untuk membuat volume.

Perhatikan bahwa Snowflake menggunakan awalan gcs:// untuk jalur Google Cloud Storage, bukan gs://.

Karena kode SQL berikut tidak akan dijalankan sebagai bagian dari instance shell dengan variabel lingkungan yang ditetapkan, pastikan untuk mengganti nilai nama Bucket GCS di mana pun nilai tersebut muncul

CREATE EXTERNAL VOLUME codelabs_retl_ext_vol
STORAGE_LOCATIONS = 
(
  (
    NAME = 'codelabs_retl_ext_vol'
    STORAGE_PROVIDER = 'GCS'
    STORAGE_BASE_URL = 'gcs://<Your bucket name>/snowflake_extvol'
  )
); 

Mendapatkan Akun Layanan Snowflake

DESC (describe) volume eksternal yang baru dibuat untuk mendapatkan akun layanan unik yang telah dibuat Snowflake untuk volume tersebut.

DESC EXTERNAL VOLUME codelabs_retl_ext_vol;

1. Di panel hasil, cari properti json dan temukan entri property_value yang menyimpan string JSON yang dimulai dengan "NAME":"codelabs_retl_ext_vol"
2. Temukan properti STORAGE_GCP_SERVICE_ACCOUNT dalam objek json dan salin nilainya (akan terlihat seperti alamat email). Ini adalah ID akun layanan yang memerlukan akses ke bucket GCS.
3. Simpan akun layanan ini ke dalam variabel lingkungan di instance shell Anda untuk digunakan kembali nanti

export GCP_SERVICE_ACCOUNT=<Your service account>

Catatan: Jika organisasi Google Cloud Anda menerapkan batasan iam.allowedPolicyMemberDomains, domain akun layanan ini (bagian setelah simbol @) harus ditambahkan ke daftar yang diizinkan oleh administrator keamanan.

Memberikan Izin GCS ke Snowflake

Sekarang, akun layanan Snowflake harus diberi izin untuk menulis ke bucket GCS.

gcloud storage buckets add-iam-policy-binding gs://$GCS_BUCKET_NAME \
    --member="serviceAccount:$GCP_SERVICE_ACCOUNT" \
    --role="roles/storage.objectAdmin"

gcloud storage buckets add-iam-policy-binding gs://$GCS_BUCKET_NAME \
    --member="serviceAccount:$GCP_SERVICE_ACCOUNT" \
    --role="roles/storage.legacyBucketReader"

Memverifikasi Akses di Snowflake

Kembali di worksheet Snowflake, jalankan perintah ini untuk memverifikasi bahwa Snowflake kini dapat terhubung ke bucket GCS dengan berhasil.

SELECT SYSTEM$VERIFY_EXTERNAL_VOLUME('codelabs_retl_ext_vol');

Hasilnya harus berupa objek JSON yang berisi "success":true.

Untuk mengetahui informasi selengkapnya tentang volume eksternal di Snowflake, lihat dokumentasi resmi.

Mengekspor Data Pesanan Contoh

Sekarang Anda dapat membuat tabel Iceberg di Snowflake. Perintah berikut memberi tahu Snowflake untuk menjalankan kueri dan menyimpan hasilnya di GCS menggunakan format Iceberg. File data akan berupa Parquet, dan metadata akan berupa Avro dan JSON, yang semuanya disimpan di lokasi yang ditentukan oleh codelabs_retl_ext_vol Volume Eksternal.

Buat database

1. Di menu samping kiri, di bagian Horizon Catalog, arahkan kursor ke Catalog, lalu klik Database Explorer
2. Setelah berada di halaman Databases, klik tombol + Database di kanan atas.
3. Beri nama db baru codelabs_retl_db

USE DATABASE codelabs_retl_db; 

CREATE ICEBERG TABLE REGIONAL_SALES_ICEBERG (
    NATION_NAME STRING,
    MARKET_SEGMENT STRING,
    ORDER_YEAR INTEGER,
    ORDER_PRIORITY STRING,
    TOTAL_ORDER_COUNT INTEGER,
    TOTAL_REVENUE NUMBER(24,2),
    UNIQUE_CUSTOMER_COUNT INTEGER
) 
EXTERNAL_VOLUME = 'codelabs_retl_ext_vol'
CATALOG = 'SNOWFLAKE'
BASE_LOCATION = 'regional_sales_iceberg'
AS (
    SELECT 
        n.n_name AS nation_name,
        c.c_mktsegment AS market_segment,
        YEAR(o.o_orderdate) AS order_year,
        o.o_orderpriority AS order_priority,
        COUNT(o.o_orderkey) AS total_order_count,
        ROUND(SUM(o.o_totalprice), 2) AS total_revenue,
        COUNT(DISTINCT c.c_custkey) AS unique_customer_count
    FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.orders AS o
    INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.customer AS c 
        ON o.o_custkey = c.c_custkey
    INNER JOIN SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.nation AS n
        ON c.c_nationkey = n.n_nationkey
    GROUP BY 
        n.n_name, 
        c.c_mktsegment, 
        YEAR(o.o_orderdate), 
        o.o_orderpriority
);

Untuk mengetahui informasi selengkapnya tentang cara membuat dan mengelola tabel Iceberg menggunakan Snowflake, lihat dokumentasi resmi.

Memverifikasi Data di GCP

Sekarang periksa bucket GCS. File yang dibuat Snowflake akan terlihat. Hal ini mengonfirmasi bahwa ekspor berhasil. Metadata Iceberg akan ditemukan di folder metadata dan data aktual sebagai file Parquet di folder data.

gcloud storage ls "gs://$GCS_BUCKET_NAME/snowflake_extvol/**"

Nama file yang tepat akan bervariasi, tetapi strukturnya akan terlihat seperti ini:

gs://$GCS_BUCKET_NAME/snowflake_extvol/
gs://$GCS_BUCKET_NAME/snowflake_extvol/regional_sales_iceberg.snvrAWuR/data/snow_cbsKIRmdDmo_wLg128fugxg_0_2_009.parquet
...
gs://$GCS_BUCKET_NAME/snowflake_extvol/regional_sales_iceberg.snvrAWuR/metadata/00001-62f831ff-6708-4494-94c5-c891b7ad447f.metadata.json
...

Data kini telah disalin dari Snowflake ke Google Cloud Storage dalam format Iceberg.

Saat kita memiliki daftar ini, mari simpan file metadata.json ke variabel lingkungan karena kita akan memerlukannya nanti.

export GCS_METADATA_JSON=$(gcloud storage ls "gs://$GCS_BUCKET_NAME/snowflake_extvol/**" | grep .metadata.json)

5. Mengonfigurasi Tabel Eksternal BigQuery

Setelah tabel Iceberg berada di Google Cloud Storage, langkah berikutnya adalah membuatnya dapat diakses oleh BigQuery. Hal ini dapat dilakukan dengan membuat tabel eksternal BigLake.

BigLake adalah mesin penyimpanan yang memungkinkan pembuatan tabel di BigQuery yang membaca data langsung dari sumber eksternal seperti Google Cloud Storage. Untuk lab ini, teknologi tersebut adalah teknologi utama yang memungkinkan BigQuery memahami tabel Iceberg yang baru saja diekspor tanpa perlu menyerap data.

Agar ini berfungsi, dua komponen diperlukan:

1. Koneksi Resource Cloud: Ini adalah link yang aman antara BigQuery dan GCS. Proses ini menggunakan akun layanan khusus untuk menangani autentikasi, sehingga memastikan BigQuery memiliki izin yang diperlukan untuk membaca file dari bucket GCS.
2. Definisi Tabel Eksternal: Definisi ini memberi tahu BigQuery tempat menemukan file metadata tabel Iceberg di GCS dan cara menafsirkannya.

Mengonfigurasi Koneksi ke Google Cloud Storage

Pertama, koneksi yang memungkinkan BigQuery mengakses GCS akan dibuat. Perintah ini membuat resource koneksi dalam BigQuery.

bq mk \
  --connection \
  --project_id=$GCP_PROJECT \
  --location=$GCP_REGION \
  --connection_type=CLOUD_RESOURCE \
  codelabs-retl-connection

Keberhasilan akan terlihat seperti ini:

Connection 12345678.region.codelabs-retl-connection successfully created

Informasi selengkapnya tentang koneksi Resource Cloud di BigQuery tersedia di dokumentasi Google Cloud.

Memberi otorisasi koneksi BigQuery untuk membaca data

Koneksi BigQuery baru memiliki akun layanannya sendiri yang memerlukan izin untuk membaca data dari bucket Google Cloud Storage.

1. Mendapatkan Akun Layanan Koneksi

Pertama, dapatkan ID akun layanan dari koneksi yang baru saja dibuat:

bq show \
  --location $GCP_REGION \
  --connection codelabs-retl-connection

Hasilnya akan menampilkan tabel koneksi yang cocok.

Tetapkan serviceAccountId ke variabel lingkungan untuk digunakan nanti.

export GCP_BQ_SERVICE_ACCOUNT=<Your service account email>

2. Berikan Izin

Beri otorisasi akun layanan untuk melihat data di bucket GCS dengan menjalankan perintah berikut.

gcloud storage buckets add-iam-policy-binding \
  gs://$GCS_BUCKET_NAME \
  --member serviceAccount:$GCP_BQ_SERVICE_ACCOUNT \
  --role roles/storage.objectViewer

Membuat Tabel Eksternal

Sekarang, buat tabel eksternal BigLake di BigQuery. Perintah ini tidak memindahkan data apa pun. Tindakan ini hanya membuat pointer ke data yang ada di GCS. Jalur ke salah satu file .metadata.json yang dibuat Snowflake akan diperlukan.

Ganti nilai external_table_definition dengan jalur yang sesuai ke file metadata.json Anda dalam perintah berikut.

Pastikan tabel ini dibuat di region yang sama dengan koneksi dari langkah sebelumnya.

bq mk --dataset --location=$GCP_REGION codelabs_retl

bq mk \
    --table \
    --location=$GCP_REGION \
--external_table_definition=ICEBERG=$GCS_METADATA_JSON@projects/$GCP_PROJECT/locations/$GCP_REGION/connections/codelabs-retl-connection \
    codelabs_retl.regional_sales

Memverifikasi Data di BigQuery

Tabel ini kini dapat dikueri menggunakan SQL standar, seperti tabel BigQuery lainnya. BigQuery akan menggunakan koneksi untuk membaca file Parquet dari GCS secara langsung.

bq query \
  --location=$GCP_REGION \
  --nouse_legacy_sql "SELECT * FROM \`$GCP_PROJECT.codelabs_retl.regional_sales\` LIMIT 10;"

6. Mengimpor Data dari BigQuery ke Spanner: Langkah Terakhir

Bagian akhir dan terpenting dari pipeline telah tercapai: memindahkan data dari tabel BigLake ke Spanner. Ini adalah langkah "Reverse ETL", di mana data, setelah diproses dan dikurasi di data warehouse, dimuat ke dalam sistem operasional untuk digunakan oleh aplikasi.

Spanner adalah database relasional terkelola sepenuhnya yang didistribusikan secara global. Layanan ini menawarkan konsistensi transaksional database relasional tradisional, tetapi dengan skalabilitas horizontal database NoSQL. Hal ini menjadikannya pilihan ideal untuk membangun aplikasi yang skalabel dan sangat tersedia.

Prosesnya adalah:

1. Buat skema tabel di database Spanner yang cocok dengan struktur data.
2. Jalankan kueri EXPORT DATA BigQuery untuk memuat data dari tabel BigLake langsung ke tabel Spanner.

Buat Tabel Spanner

Sebelum mentransfer data dari BigQuery, tabel tujuan harus dibuat di Spanner dengan skema yang kompatibel.

gcloud spanner databases ddl update $SPANNER_DB \
  --instance=$SPANNER_INSTANCE \
  --ddl="$(cat <<EOF
CREATE TABLE regional_sales (
    nation_name STRING(MAX),
    market_segment STRING(MAX),
    order_year INT64,
    order_priority STRING(MAX),
    total_order_count INT64,
    total_revenue NUMERIC,
    unique_customer_count INT64
) PRIMARY KEY (nation_name, market_segment, order_year, order_priority);
EOF
)"

Mengekspor Data dari BigQuery

Ini adalah langkah terakhir. Dengan data sumber yang siap di tabel BigLake BigQuery dan tabel tujuan yang dibuat di Spanner, pemindahan data yang sebenarnya sangatlah sederhana. Satu kueri SQL BigQuery akan digunakan: EXPORT DATA.

Kueri ini dirancang khusus untuk skenario seperti ini. Alat ini secara efisien mengekspor data dari tabel BigQuery (termasuk tabel eksternal seperti tabel BigLake) ke tujuan eksternal. Dalam hal ini, tujuannya adalah tabel Spanner.

bq query --location=$GCP_REGION --use_legacy_sql=false <<EOF
EXPORT DATA OPTIONS (
uri="https://spanner.googleapis.com/projects/${GCP_PROJECT}/instances/${SPANNER_INSTANCE}/databases/${SPANNER_DB}",
  format='CLOUD_SPANNER',
  spanner_options="""{ 
      "table": "regional_sales", 
      "priority": "HIGH" 
      }"""
) AS 
SELECT * FROM \`${PROJECT_ID}.codelabs_retl.regional_sales\`
EOF

Setelah kueri selesai, panel Hasil akan menampilkan "Update completed".

7. Memverifikasi data di Spanner

Selamat! Pipeline Reverse ETL lengkap telah berhasil dibuat dan dieksekusi. Langkah terakhir adalah memverifikasi bahwa data telah tiba di Spanner seperti yang diharapkan.

gcloud spanner databases execute-sql \
  --instance=$SPANNER_INSTANCE \
  $SPANNER_DB \
  --sql='SELECT * FROM regional_sales LIMIT 10'

Data sampel yang diimpor akan muncul seperti yang diminta:

nation_name  market_segment  order_year  order_priority   total_order_count  total_revenue  unique_customer_count
ALGERIA      AUTOMOBILE      1992        1-URGENT         375                59232423.66    298
ALGERIA      AUTOMOBILE      1992        2-HIGH           328                47371891.08    269
ALGERIA      AUTOMOBILE      1992        3-MEDIUM         346                52823195.87    262
ALGERIA      AUTOMOBILE      1992        4-NOT SPECIFIED  365                52935998.34    288
ALGERIA      AUTOMOBILE      1992        5-LOW            380                54920263.68    293
ALGERIA      AUTOMOBILE      1993        1-URGENT         394                63145618.78    312
ALGERIA      AUTOMOBILE      1993        2-HIGH           340                50737488.4     277
ALGERIA      AUTOMOBILE      1993        3-MEDIUM         383                55871057.46    298
ALGERIA      AUTOMOBILE      1993        4-NOT SPECIFIED  365                56424662.05    291
ALGERIA      AUTOMOBILE      1993        5-LOW            363                54673249.06    283

Kesenjangan antara dunia data analitis dan operasional telah berhasil dijembatani.

8. Membersihkan

Membersihkan Spanner

Menghapus Database dan Instance Spanner

gcloud spanner instances delete $SPANNER_INSTANCE

Membersihkan GCS

Hapus Bucket GCS yang dibuat untuk menghosting data

gcloud storage rm --recursive gs://$GCS_BUCKET_NAME

Membersihkan BigQuery

bq rm -r codelabs_retl
bq rm --connection --location=$GCP_REGION codelabs-retl-connection

Membersihkan Snowflake

Lepaskan database

1. Pada menu samping kiri, di bagian Horizon Catalog, arahkan kursor ke Catalog, lalu klik Database Explorer
2. Klik ... di sebelah kanan database CODELABS_RETL_DB untuk meluaskan opsi, lalu pilih Drop
3. Pada dialog konfirmasi yang muncul, pilih Drop Database

Menghapus buku kerja

4. Di menu sebelah kiri, di bagian Bekerja dengan data, arahkan kursor ke Project, lalu klik Ruang kerja
5. Di sidebar Ruang Kerja Saya, arahkan kursor ke berbagai file ruang kerja yang Anda gunakan untuk lab ini guna menampilkan opsi tambahan ..., lalu klik opsi tersebut
6. Pilih Hapus, lalu Hapus lagi di dialog konfirmasi yang muncul.
7. Lakukan hal ini untuk semua file ruang kerja SQL yang Anda buat untuk lab ini.

Menghapus volume eksternal

8. Di menu sebelah kiri, di bagian Horizon Catalog, arahkan kursor ke Catalog, lalu klik External Data
9. Klik di sebelah kanan CODELABS_RETL_EXT_VOL, pilih Lepaskan volume eksternal, lalu pilih Lepaskan volume eksternal lagi pada dialog konfirmasi

9. Selamat

Selamat, Anda telah menyelesaikan codelab.

Yang telah kita bahas

• Cara memuat data ke Snowflake
• Cara membuat Bucket GCS
• Cara mengekspor tabel Snowflake ke GCS dalam format CSV
• Cara menyiapkan instance Spanner
• Cara memuat Tabel CSV ke Spanner dengan Dataflow