ETL terbalik dari Databricks ke Spanner menggunakan CSV

1. Membangun pipeline ETL terbalik dari Databricks ke Spanner menggunakan GCS dan Dataflow

Pengantar

Dalam codelab ini, Anda akan membuat pipeline Reverse ETL dari Databricks ke Spanner menggunakan file CSV yang disimpan di Google Cloud Storage. Biasanya, pipeline ETL (Ekstrak, Transformasi, Muat) memindahkan data dari database operasional ke data warehouse seperti Databricks untuk analisis. Pipeline ETL Terbalik melakukan hal sebaliknya: pipeline ini memindahkan data yang telah diproses dan dikurasi dari data warehouse kembali ke sistem operasional tempat data tersebut dapat mendukung aplikasi, menayangkan fitur yang terlihat oleh pengguna, atau digunakan untuk pengambilan keputusan real-time.

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

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

1. Databricks ke Google Cloud Storage (GCS) dalam Format CSV:

• Langkah pertama adalah mengeluarkan data dari Databricks dalam format terbuka dan universal. Mengekspor ke CSV adalah metode umum dan mudah untuk membuat file data portabel. File ini akan di-staging di GCS, yang menyediakan solusi penyimpanan objek yang skalabel dan tahan lama.

2. GCS ke Spanner (melalui Dataflow):

• Alih-alih menulis skrip kustom untuk membaca dari GCS dan menulis ke Spanner, Google Dataflow digunakan, yaitu layanan pemrosesan data yang terkelola sepenuhnya. Dataflow menyediakan template siap pakai khusus untuk jenis tugas ini. Dengan menggunakan template "GCS Text to Cloud Spanner", Anda dapat melakukan impor data paralel dengan throughput tinggi tanpa menulis kode pemrosesan data apa pun, sehingga menghemat waktu pengembangan yang signifikan.

Yang akan Anda pelajari

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

2. Penyiapan, Persyaratan & Batasan

Prasyarat

• Akun Databricks dengan izin untuk membuat cluster dan menginstal library. Akun uji coba gratis tidak cukup untuk lab ini.
• Akun Google Cloud dengan API Spanner, Cloud Storage, dan Dataflow diaktifkan.
• Akses ke Konsol Google Cloud melalui browser web.
• Terminal dengan Google Cloud CLI terinstal.
• 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.

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

Batasan

Penting untuk mengetahui perbedaan jenis data saat memindahkan data antar-sistem.

• Databricks ke CSV: Saat diekspor, jenis data Databricks dikonversi menjadi representasi teks standar.
• CSV ke Spanner: Saat mengimpor, Anda harus memastikan bahwa jenis data Spanner target kompatibel dengan representasi string dalam file CSV. Lab ini memandu Anda melalui serangkaian pemetaan jenis umum.

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.

Databricks

Untuk lab ini, akun Databricks dihosting di GCP untuk memungkinkan penentuan lokasi data eksternal di GCS.

Jika instance Databricks Anda berada di penyedia cloud lain, data harus di-staging terlebih dahulu di solusi penyimpanan penyedia cloud tersebut, lalu dipindahkan ke GCS secara terpisah.

Google Cloud

Lab ini memerlukan project Google Cloud.

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 sementara file data CSV yang dihasilkan oleh Snowflake sebelum diimpor ke Spanner.

Buat bucket

Gunakan perintah berikut untuk membuat bucket penyimpanan di region tertentu.

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 Databricks ke GCS

Sekarang, lingkungan Databricks akan dikonfigurasi agar terhubung dengan aman ke GCS dan mengekspor data.

Buat Kredensial

1. Di menu sisi kiri, klik Katalog
2. Klik Data Eksternal jika tersedia di bagian atas halaman katalog. Jika tidak, klik dropdown Connect, lalu klik Credentials.
3. Beralihlah ke tab Credentials jika Anda belum membukanya.
4. Klik Create Credentials.
5. Pilih GCP Service Account untuk Credential Type
6. Masukkan codelabs-retl-credentials untuk Nama Kredensial
7. Klik Buat
8. Salin email akun layanan dari kotak dialog, lalu klik Selesai

Tetapkan akun layanan ini ke variabel lingkungan di instance shell Anda untuk digunakan kembali:

export GCP_SERVICE_ACCOUNT=<Your service account>

Memberikan Izin GCS ke Databricks

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"

Membuat Lokasi Eksternal

6. Buka kembali halaman Credentials menggunakan breadcrumb di bagian atas halaman
7. Beralih ke tab Lokasi Eksternal
8. Klik Buat lokasi eksternal
9. Tetapkan External Location Name ke codelabs-retl-gcs
10. Tetapkan Jenis Penyimpanan sebagai GCP
11. Tetapkan jalur bucket Anda ke URL
12. Setel Storage Credential ke codelabs-retl-credentials
13. Klik Buat
14. Di konfirmasi. Klik Buat

Jika konfirmasi menunjukkan bahwa verifikasi File Events Resource Provision Gagal, lanjutkan dan klik Buat Paksa, karena hal itu tidak akan memengaruhi codelab ini.

Membuat Katalog dan Skema

1. Di menu sisi kiri, klik Katalog
2. Klik Buat, lalu Buat katalog
3. Tetapkan Nama Katalog ke retl_tpch_project
4. Tetapkan Type ke Standard
5. Pilih codelabs-retl-gcs sebagai lokasi eksternal
6. Klik Buat
7. Klik retl_tpch_project dari daftar Katalog
8. Klik Buat skema
9. Tetapkan Schema name ke tpch_data
10. Pilih Lokasi penyimpanan menjadi codelabs-retl-gcs
11. Klik Buat

Mengekspor Data sebagai CSV

Sekarang data siap diekspor. Set data TPC-H contoh akan digunakan untuk menentukan tabel baru yang akan disimpan secara eksternal sebagai CSV.

Pertama, salin contoh data ke dalam tabel baru di ruang kerja. Untuk melakukannya, kode SQL harus dijalankan dari kueri.

1. Di menu sebelah kiri di bagian SQL, klik Queries
2. Klik tombol Buat kueri
3. Di samping tombol Run, setel Workspace ke retl_tpch_project

Karena kode berikut tidak akan dijalankan di shell dengan variabel lingkungan yang ditentukan sebelumnya untuk nama bucket GCS Anda, pastikan untuk mengganti nilai Your bucket name dengan nama bucket Anda sendiri.

CREATE TABLE retl_tpch_project.tpch_data.regional_sales_csv
USING CSV
LOCATION 'gs://<Your bucket name>/regional_sales_csv'
OPTIONS (
  header "false",
  delimiter ","
)
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 samples.tpch.orders AS o
INNER JOIN samples.tpch.customer AS c
    ON o.o_custkey = c.c_custkey
INNER JOIN samples.tpch.nation AS n
    ON c.c_nationkey = n.n_nationkey
GROUP BY 1, 2, 3, 4;

Memverifikasi Data di GCS

Periksa bucket GCS untuk melihat file yang dibuat Databricks.

gcloud storage ls gs://$GCS_BUCKET_NAME/regional_sales_csv/

Satu atau beberapa file .csv akan terlihat, bersama dengan file _SUCCESS dan log.

5. Memuat Data ke Spanner dengan Dataflow

Template Dataflow yang disediakan Google akan digunakan untuk mengimpor data CSV dari GCS ke Spanner.

Buat Tabel Spanner

Pertama, buat tabel tujuan di Spanner. Skema harus kompatibel dengan data dalam file CSV.

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
)"

Buat Manifes Dataflow

Template Dataflow memerlukan file "manifest". Ini adalah file JSON yang memberi tahu template tempat menemukan file data sumber dan tabel Spanner yang akan dimuat.

Tentukan dan upload regional_sales_manifest.json baru ke bucket GCS:

cat <<EOF | gcloud storage cp - gs://$GCS_BUCKET_NAME/regional_sales_manifest.json 
{ 
  "tables": [
    {
       "table_name": "regional_sales", 
       "file_patterns": [ 
         "gs://$GCS_BUCKET_NAME/regional_sales_csv/*.csv"
       ] 
    } 
  ] 
} 
EOF

Aktifkan Dataflow API

Sebelum menggunakan Dataflow, Anda harus mengaktifkannya terlebih dahulu. Lakukan dengan

gcloud services enable dataflow.googleapis.com --project=$GCP_PROJECT

Membuat dan Menjalankan Tugas Dataflow

Tugas impor kini siap dijalankan. Perintah ini meluncurkan tugas Dataflow menggunakan template GCS_Text_to_Cloud_Spanner.

Perintahnya panjang dan memiliki beberapa parameter. Berikut perinciannya:

• --gcs-location: Jalur ke template bawaan di GCS.
• --region: Region tempat tugas Dataflow akan berjalan.
• --parameters: Daftar pasangan nilai kunci khusus untuk template:
• instanceId, databaseId: Instance dan database Spanner target.
• importManifest: Jalur GCS ke file manifes yang baru saja dibuat.

gcloud dataflow jobs run spanner-import-from-gcs \
  --gcs-location=gs://dataflow-templates/latest/GCS_Text_to_Cloud_Spanner \
  --region=$GCP_REGION \
  --staging-location=gs://$GCS_BUCKET_NAME/staging \
  --parameters \
instanceId=$SPANNER_INSTANCE,\
databaseId=$SPANNER_DB,\
importManifest=gs://$GCS_BUCKET_NAME/regional_sales_manifest.json,escape='\'

Status tugas Dataflow dapat diperiksa dengan perintah berikut

gcloud dataflow jobs list \
    --filter="name:spanner-import-from-gcs" \
    --region="$GCP_REGION" \
    --sort-by="~creationTime" \
    --limit=1

Tugas ini akan memerlukan waktu sekitar 5 menit untuk diselesaikan.

Memverifikasi data di Spanner

Setelah tugas Dataflow berhasil, verifikasi bahwa data telah dimuat ke Spanner.

Pertama, periksa jumlah baris, seharusnya 4375

gcloud spanner databases execute-sql $SPANNER_DB \
--instance=$SPANNER_INSTANCE \
--sql='SELECT COUNT(*) FROM regional_sales;'

Selanjutnya, buat kueri beberapa baris untuk memeriksa data.

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

Data yang diimpor dari tabel Databricks akan terlihat.

6. 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 Databricks

Menghapus Katalog/Skema/Tabel

1. Login ke instance Databricks Anda
2. Klik dari menu sebelah kiri
3. Pilih retl_tpch_project yang dibuat sebelumnya dari daftar katalog

4. Dalam daftar Skema, pilih tpch_data yang dibuat
5. Pilih regional_sales_csv yang dibuat sebelumnya dari daftar tabel
6. Luaskan opsi tabel dengan mengklik , lalu pilih Hapus
7. Klik Hapus pada dialog konfirmasi untuk menghapus tabel
8. Setelah tabel dihapus, Anda akan diarahkan kembali ke halaman skema
9. Luaskan opsi skema dengan mengklik , lalu pilih Hapus
10. Klik Hapus di dialog konfirmasi untuk menghapus Skema
11. Setelah skema dihapus, Anda akan diarahkan kembali ke halaman katalog
12. Ikuti langkah 4 - 11 lagi untuk menghapus skema default jika ada.
13. Dari halaman katalog, luaskan opsi katalog dengan mengklik , lalu pilih Hapus.
14. Klik Hapus pada dialog konfirmasi untuk menghapus katalog

Menghapus Lokasi / Kredensial Data Eksternal

15. Dari layar Katalog, klik
16. Jika Anda tidak melihat opsi External Data, Anda mungkin menemukan External Location yang tercantum di menu dropdown Connect.
17. Klik lokasi data eksternal retl-gcs-location yang dibuat sebelumnya
18. Dari halaman lokasi eksternal, luaskan opsi lokasi dengan mengklik , lalu pilih Delete
19. Klik Hapus pada dialog konfirmasi untuk menghapus lokasi eksternal
20. Klik
21. Klik retl-gcs-credential yang telah dibuat sebelumnya
22. Dari halaman kredensial, luaskan opsi kredensial dengan mengklik , lalu pilih Delete
23. Klik Hapus pada dialog konfirmasi untuk menghapus kredensial.

7. Selamat

Selamat, Anda telah menyelesaikan codelab.

Yang telah kita bahas

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