ETL đảo ngược từ Databricks sang Spanner bằng CSV

1. Tạo quy trình ETL đảo ngược từ Databricks sang Spanner bằng GCS và Dataflow

Giới thiệu

Trong lớp học lập trình này, bạn sẽ tạo một quy trình ETL đảo ngược từ Databricks đến Spanner bằng cách sử dụng các tệp CSV được lưu trữ trong Google Cloud Storage. Theo truyền thống, các quy trình ETL (Trích xuất, Biến đổi, Tải) sẽ di chuyển dữ liệu từ cơ sở dữ liệu hoạt động vào một kho dữ liệu như Databricks để phân tích. Quy trình Reverse ETL làm ngược lại: quy trình này di chuyển dữ liệu đã được xử lý và tuyển chọn từ kho dữ liệu trở lại các hệ thống vận hành, nơi dữ liệu có thể hỗ trợ các ứng dụng, cung cấp các tính năng cho người dùng hoặc được dùng để đưa ra quyết định theo thời gian thực.

Mục tiêu là di chuyển một tập dữ liệu mẫu từ bảng Databricks vào Spanner, một cơ sở dữ liệu quan hệ được phân phối trên toàn cầu, lý tưởng cho các ứng dụng có tính sẵn sàng cao.

Để đạt được điều này, Google Cloud Storage (GCS) và Dataflow được dùng làm các bước trung gian. Sau đây là thông tin chi tiết về dòng dữ liệu và lý do đằng sau cấu trúc này:

  1. Databricks đến Google Cloud Storage (GCS) ở định dạng CSV:
  • Bước đầu tiên là lấy dữ liệu từ Databricks ở định dạng mở, phổ biến. Xuất sang CSV là một phương pháp phổ biến và đơn giản để tạo tệp dữ liệu di động. Các tệp này sẽ được dàn xếp trong GCS, cung cấp một giải pháp lưu trữ đối tượng có khả năng mở rộng và bền bỉ.
  1. GCS đến Spanner (thông qua Dataflow):
  • Thay vì viết một tập lệnh tuỳ chỉnh để đọc từ GCS và ghi vào Spanner, Google Dataflow được dùng làm dịch vụ xử lý dữ liệu được quản lý hoàn toàn. Dataflow cung cấp các mẫu dựng sẵn dành riêng cho loại tác vụ này. Việc sử dụng mẫu "GCS Text to Cloud Spanner" (Văn bản GCS sang Cloud Spanner) cho phép nhập dữ liệu song song với thông lượng cao mà không cần viết bất kỳ mã xử lý dữ liệu nào, giúp tiết kiệm đáng kể thời gian phát triển.

Kiến thức bạn sẽ học được

  • Cách tải dữ liệu vào Databricks
  • Cách tạo một Nhóm lưu trữ GCS
  • Cách xuất bảng Databricks sang GCS ở định dạng CSV
  • Cách thiết lập một phiên bản Spanner
  • Cách tải Bảng CSV lên Spanner bằng Dataflow

2. Thiết lập, yêu cầu và hạn chế

Điều kiện tiên quyết

  • Một tài khoản Databricks có quyền tạo cụm và cài đặt thư viện. Tài khoản dùng thử miễn phí không đủ cho phòng thí nghiệm này.
  • Một tài khoản Google Cloud đã bật API Spanner, Cloud Storage và Dataflow.
  • Quyền truy cập vào Google Cloud Console thông qua trình duyệt web.
  • Một cửa sổ dòng lệnh đã cài đặt Google Cloud CLI.
  • Nếu tổ chức của bạn trên Google Cloud đã bật chính sách iam.allowedPolicyMemberDomains, thì quản trị viên có thể cần cấp một trường hợp ngoại lệ để cho phép tài khoản dịch vụ từ các miền bên ngoài. Chúng ta sẽ đề cập đến vấn đề này trong một bước sau (nếu có).

Quyền IAM của Google Cloud Platform

Tài khoản Google sẽ cần có các quyền sau để thực hiện tất cả các bước trong lớp học lập trình này.

Tài khoản dịch vụ

iam.serviceAccountKeys.create

Cho phép tạo Tài khoản dịch vụ.

Spanner

spanner.instances.create

Cho phép tạo một phiên bản Spanner mới.

spanner.databases.create

Cho phép chạy các câu lệnh DDL để tạo

spanner.databases.updateDdl

Cho phép chạy câu lệnh DDL để tạo bảng trong cơ sở dữ liệu.

Google Cloud Storage

storage.buckets.create

Cho phép tạo một vùng lưu trữ GCS mới để lưu trữ các tệp Parquet đã xuất.

storage.objects.create

Cho phép ghi các tệp Parquet đã xuất vào bộ chứa GCS.

storage.objects.get

Cho phép BigQuery đọc các tệp Parquet trong vùng lưu trữ GCS.

storage.objects.list

Cho phép BigQuery liệt kê các tệp Parquet trong vùng lưu trữ GCS.

Dataflow

Dataflow.workitems.lease

Cho phép yêu cầu các mục công việc từ Dataflow.

Dataflow.workitems.sendMessage

Cho phép worker Dataflow gửi thông báo trở lại dịch vụ Dataflow.

Logging.logEntries.create

Cho phép các worker Dataflow ghi các mục nhật ký vào Google Cloud Logging.

Để thuận tiện, bạn có thể sử dụng các vai trò được xác định trước có chứa những quyền này.

roles/resourcemanager.projectIamAdmin

roles/iam.serviceAccountKeyAdmin

roles/spanner.instanceAdmin

roles/spanner.databaseAdmin

roles/storage.admin

roles/dataflow.serviceAgent

roles/dataflow.worker

roles/dataflow.serviceAgent

Các điểm hạn chế

Bạn cần lưu ý đến sự khác biệt về kiểu dữ liệu khi di chuyển dữ liệu giữa các hệ thống.

  • Databricks sang CSV: Khi xuất, các kiểu dữ liệu Databricks sẽ được chuyển đổi thành các biểu thị văn bản tiêu chuẩn.
  • CSV sang Spanner: Khi nhập, bạn cần đảm bảo rằng các kiểu dữ liệu Spanner đích tương thích với các kiểu biểu thị chuỗi trong tệp CSV. Lớp học lập trình này hướng dẫn bạn thực hiện một nhóm ánh xạ kiểu phổ biến.

Thiết lập các thuộc tính có thể dùng lại

Bạn sẽ cần một số giá trị nhiều lần trong suốt quá trình thực hành này. Để đơn giản hoá việc này, chúng ta sẽ đặt các giá trị này thành các biến shell để sử dụng sau.

  • GCP_REGION – Khu vực cụ thể nơi các tài nguyên GCP sẽ được đặt. Bạn có thể xem danh sách các khu vực tại đây.
  • GCP_PROJECT – Mã dự án GCP cần sử dụng.
  • GCP_BUCKET_NAME – Tên của Nhóm GCS sẽ được tạo và nơi lưu trữ các tệp dữ liệu.
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>

Databricks

Đối với phòng thí nghiệm này, một tài khoản Databricks được lưu trữ trên GCP để cho phép xác định vị trí dữ liệu bên ngoài trong GCS.

Google Cloud

Bài thực hành này yêu cầu bạn phải có một dự án trên Google Cloud.

Dự án trên Google Cloud

Dự án là đơn vị tổ chức cơ bản trong Google Cloud. Nếu quản trị viên đã cung cấp một khoá để sử dụng, bạn có thể bỏ qua bước này.

Bạn có thể tạo một dự án bằng CLI như sau:

gcloud projects create $GCP_PROJECT
gcloud config set project $GCP_PROJECT

Tìm hiểu thêm về cách tạo và quản lý dự án tại đây.

Thiết lập Spanner

Để bắt đầu sử dụng Spanner, bạn cần cung cấp một phiên bản và một cơ sở dữ liệu. Bạn có thể xem thông tin chi tiết về cách định cấu hình và tạo một phiên bản Spanner tại đây.

Tạo đối tượng

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

Tạo Database

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

3. Tạo bộ chứa Google Cloud Storage

Google Cloud Storage (GCS) sẽ được dùng để tạm thời lưu trữ các tệp dữ liệu CSV do Snowflake tạo trước khi được nhập vào Spanner.

Tạo vùng chứa

Sử dụng lệnh sau để tạo một nhóm lưu trữ ở một khu vực cụ thể.

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

Xác minh việc tạo vùng chứa

Sau khi lệnh đó thành công, hãy kiểm tra kết quả bằng cách liệt kê tất cả các vùng chứa. Nhóm mới sẽ xuất hiện trong danh sách kết quả. Tham chiếu đến nhóm thường xuất hiện với tiền tố gs:// ở trước tên nhóm.

gcloud storage ls | grep gs://$GCS_BUCKET_NAME

Kiểm thử quyền ghi

Bước này giúp đảm bảo rằng môi trường cục bộ được xác thực đúng cách và có các quyền cần thiết để ghi tệp vào bộ chứa mới tạo.

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

Xác minh tệp đã tải lên

Liệt kê các đối tượng trong vùng chứa. Đường dẫn đầy đủ của tệp vừa tải lên sẽ xuất hiện.

gcloud storage ls gs://$GCS_BUCKET_NAME

Bạn sẽ thấy kết quả sau đây:

gs://$GCS_BUCKET_NAME/hello.txt

Để xem nội dung của một đối tượng trong một nhóm, bạn có thể sử dụng gcloud storage cat.

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

Nội dung của tệp phải hiển thị:

Hello, GCS

Dọn dẹp tệp kiểm thử

Bộ chứa Cloud Storage hiện đã được thiết lập. Giờ đây, bạn có thể xoá tệp kiểm thử tạm thời.

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

Kết quả đầu ra sẽ xác nhận việc xoá:

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

4. Xuất từ Databricks sang GCS

Giờ đây, môi trường Databricks sẽ được định cấu hình để kết nối an toàn với GCS và xuất dữ liệu.

Tạo thông tin đăng nhập

  1. Trên trình đơn bên trái, hãy nhấp vào Danh mục
  2. Nhấp vào Dữ liệu bên ngoài nếu có ở đầu trang danh mục. Nếu không, hãy nhấp vào trình đơn thả xuống Kết nối rồi nhấp vào Thông tin đăng nhập
  3. Chuyển sang thẻ Credentials (Thông tin đăng nhập) nếu bạn chưa chuyển.
  4. Nhấp vào Tạo thông tin đăng nhập
  5. Chọn GCP Service Account cho Loại thông tin đăng nhập
  6. Nhập codelabs-retl-credentials cho Tên thông tin đăng nhập
  7. Nhấp vào Tạo
  8. Sao chép email tài khoản dịch vụ từ hộp thoại rồi nhấp vào Xong

Đặt tài khoản dịch vụ này thành một biến môi trường trong phiên bản shell để sử dụng lại:

export GCP_SERVICE_ACCOUNT=<Your service account>

Cấp quyền GCS cho Databricks

Giờ đây, bạn phải cấp cho tài khoản dịch vụ Snowflake quyền ghi vào bộ chứa 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"

Tạo vị trí bên ngoài

  1. Quay lại trang Thông tin đăng nhập bằng cách sử dụng đường dẫn ở đầu trang
  2. Chuyển sang thẻ Vị trí bên ngoài
  3. Nhấp vào Tạo vị trí bên ngoài
  4. Đặt Tên vị trí bên ngoài thành codelabs-retl-gcs
  5. Giữ Loại bộ nhớ là GCP
  6. Đặt đường dẫn đến nhóm của bạn thành URL
  7. Đặt Storage Credential (Thông tin xác thực bộ nhớ) thành codelabs-retl-credentials
  8. Nhấp vào Tạo
  9. Trên trang xác nhận. Nhấp vào Tạo

Tạo danh mục và giản đồ

  1. Trên trình đơn bên trái, hãy nhấp vào Danh mục
  2. Nhấp vào Tạo, rồi nhấp vào Tạo danh mục
  3. Đặt Tên danh mục thành retl_tpch_project
  4. Đặt Type (Loại) thành Standard
  5. Chọn codelabs-retl-gcs làm vị trí bên ngoài
  6. Nhấp vào Tạo
  7. Nhấp vào retl_tpch_project trong danh sách Danh mục
  8. Nhấp vào Tạo giản đồ
  9. Đặt Tên giản đồ thành tpch_data
  10. Chọn Vị trí lưu trữ là codelabs-retl-gcs
  11. Nhấp vào Tạo

Xuất dữ liệu dưới dạng tệp CSV

Giờ đây, dữ liệu đã sẵn sàng để xuất. Tập dữ liệu mẫu TPC-H sẽ được dùng để xác định bảng mới của chúng ta. Bảng này sẽ được lưu trữ bên ngoài dưới dạng CSV.

Trước tiên, hãy sao chép dữ liệu mẫu vào một bảng mới trong không gian làm việc. Để làm như vậy, bạn cần chạy mã SQL từ một truy vấn.

  1. Trên trình đơn bên trái trong mục SQL, hãy nhấp vào Truy vấn
  2. Nhấp vào nút Tạo truy vấn
  3. Bên cạnh nút Run (Chạy), hãy đặt Workspace (Không gian làm việc) thành retl_tpch_project
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;

Xác minh dữ liệu trong GCS

Kiểm tra vùng lưu trữ GCS để xem các tệp mà Databricks đã tạo.

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

Bạn sẽ thấy một hoặc nhiều tệp .csv, cùng với _SUCCESS và các tệp nhật ký.

5. Tải dữ liệu vào Spanner bằng Dataflow

Một mẫu Dataflow do Google cung cấp sẽ được dùng để nhập dữ liệu CSV từ GCS vào Spanner.

Tạo bảng Spanner

Trước tiên, hãy tạo bảng đích trong Spanner. Giản đồ cần tương thích với dữ liệu trong tệp 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
)"

Tạo tệp kê khai Dataflow

Mẫu Dataflow yêu cầu một tệp "manifest". Đây là một tệp JSON cho biết mẫu nơi tìm các tệp dữ liệu nguồn và bảng Spanner nào cần tải các tệp đó vào.

Xác định và tải một tệp regional_sales_manifest.json mới lên bộ chứa 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

Bật Dataflow API

Trước khi sử dụng Dataflow, bạn cần bật tính năng này. Làm như vậy với

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

Tạo và chạy tác vụ Dataflow

Giờ thì bạn đã có thể chạy công việc nhập. Lệnh này khởi chạy một công việc Dataflow bằng cách sử dụng mẫu GCS_Text_to_Cloud_Spanner.

Lệnh này dài và có nhiều tham số. Sau đây là thông tin chi tiết:

  • --gcs-location: Đường dẫn đến mẫu được tạo sẵn trên GCS.
  • --region: Khu vực mà tác vụ Dataflow sẽ chạy.
  • --parameters: Danh sách các cặp khoá-giá trị dành riêng cho mẫu:
  • instanceId, databaseId: Cơ sở dữ liệu và phiên bản Spanner mục tiêu.
  • importManifest: Đường dẫn GCS đến tệp kê khai vừa tạo.
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='\'

Bạn có thể kiểm tra trạng thái của lệnh Dataflow bằng lệnh sau

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

Bạn sẽ cần khoảng 5 phút để hoàn thành công việc này.

Xác minh dữ liệu trong Spanner

Sau khi công việc Dataflow thành công, hãy xác minh rằng dữ liệu đã được tải vào Spanner.

Trước tiên, hãy kiểm tra số hàng, số hàng phải là 4375

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

Tiếp theo, hãy truy vấn một vài hàng để kiểm tra dữ liệu.

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

Dữ liệu được nhập từ bảng Databricks sẽ xuất hiện.

6. Dọn dẹp

Dọn dẹp Spanner

Xoá Cơ sở dữ liệu và Đối tượng Spanner

gcloud spanner instances delete $SPANNER_INSTANCE

Dọn dẹp GCS

Xoá Nhóm GCS đã tạo để lưu trữ dữ liệu

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

Dọn dẹp Databricks

Xoá danh mục/lược đồ/bảng

  1. Đăng nhập vào phiên bản Databricks
  2. Nhấp vào biểu tượng 20bae9c2c9097306.png trong trình đơn bên trái
  3. Chọn retl_tpch_project đã tạo trước đó trong danh sách danh mục

fc566eb3fddd7477.png

  1. Trong danh sách Lược đồ, hãy chọn tpch_data mà bạn đã tạo
  2. Chọn regional_sales_csv đã tạo trước đó trong danh sách bảng
  3. Mở rộng các lựa chọn về bảng bằng cách nhấp vào biểu tượng df6dbe6356f141c6.png rồi chọn Xoá
  4. Nhấp vào Xoá trên hộp thoại xác nhận để xoá bảng
  5. Sau khi xoá bảng, bạn sẽ được đưa trở lại trang giản đồ
  6. Mở rộng các lựa chọn về giản đồ bằng cách nhấp vào df6dbe6356f141c6.png rồi chọn Xoá
  7. Nhấp vào Xoá trên hộp thoại xác nhận để xoá Lược đồ
  8. Sau khi xoá giản đồ, bạn sẽ được đưa trở lại trang danh mục
  9. Lặp lại các bước từ 4 đến 11 để xoá giản đồ default (nếu có).
  10. Trên trang danh mục, hãy mở rộng các lựa chọn về danh mục bằng cách nhấp vào biểu tượng df6dbe6356f141c6.png rồi chọn Xoá
  11. Nhấp vào Xoá trên hộp thoại xác nhận để xoá danh mục

Xoá vị trí / thông tin đăng nhập của dữ liệu bên ngoài

  1. Trên màn hình Danh mục, hãy nhấp vào biểu tượng 32d5a94ae444cd8e.png
  2. Nếu không thấy biểu tượng External Data, bạn có thể thấy biểu tượng External Location trong trình đơn thả xuống Connect.
  3. Nhấp vào vị trí dữ liệu bên ngoài retl-gcs-location mà bạn đã tạo trước đó
  4. Trên trang vị trí bên ngoài, hãy mở rộng các lựa chọn vị trí bằng cách nhấp vào df6dbe6356f141c6.png rồi chọn Delete
  5. Nhấp vào Xoá trên hộp thoại xác nhận để xoá vị trí bên ngoài
  6. Nhấp vào biểu tượng e03562324c0ba85e.png
  7. Nhấp vào retl-gcs-credential đã được tạo trước đó
  8. Trên trang thông tin xác thực, hãy mở rộng các lựa chọn thông tin xác thực bằng cách nhấp vào df6dbe6356f141c6.png rồi chọn Delete
  9. Nhấp vào Xoá trên hộp thoại xác nhận để xoá thông tin đăng nhập.

7. Xin chúc mừng

Chúc mừng bạn đã hoàn thành lớp học lập trình này.

Nội dung đã đề cập

  • Cách tải dữ liệu vào Databricks
  • Cách tạo một Nhóm lưu trữ GCS
  • Cách xuất bảng Databricks sang GCS ở định dạng CSV
  • Cách thiết lập một phiên bản Spanner
  • Cách tải Bảng CSV lên Spanner bằng Dataflow