Replicating data from SQL Server to BigQuery by DataFusion

Scenario

• 參考官方 Replicating data from SQL Server to BigQuery 進行建立
• 參考 Connect Private Data Fusion instance with a Private Cloud SQL instance using CloudSQL proxy 進行建立

使用 Private Instance

官方文件無提及需建立 GCE Proxy，原因為 CloudSQL 無法與 DataFusion 直連

因此需透過 GCE 的 Proxy 進行連線

圖片來源

實做方式

前置需求

啟用 API

• 點我啟用相關 API

API 啟用連結如下

https://console.cloud.google.com/flows/enableapi?apiid=datafusion.googleapis.com,bigquery.googleapis.com,storage.googleapis.com,sqladmin.googleapis.com

建立 VPC

export PRJ_ID="PROJECT_ID"
export VPC_NAME="NAME"

gcloud compute networks create ${VPC_NAME} --project=${PRJ_ID} --subnet-mode=custom --mtu=1460 --bgp-routing-mode=regional

gcloud compute networks subnets create ${VPC_NAME}-us --project=${PRJ_ID} --range=10.1.1.0/24 --stack-type=IPV4_ONLY --network=${VPC_NAME} --region=us-central1 --enable-private-ip-google-access

gcloud compute --project=${PRJ_ID} firewall-rules create ${VPC_NAME}-allow-rdp --direction=INGRESS --priority=1000 --network=${VPC_NAME} --action=ALLOW --rules=tcp:3389 --source-ranges=0.0.0.0/0

gcloud compute --project=${PRJ_ID} firewall-rules create ${VPC_NAME}-allow-internal --direction=INGRESS --priority=1000 --network=${VPC_NAME} --action=ALLOW --rules=all --source-ranges=10.0.0.0/8

建立 CloudSQL

建立 CloudSQL for MSSQL 並啟用 private ip 後，即可建立測試資料

建立測試資料

• 匯入測試資料
• 需使用 SSMS 工具進行連線至 MSSQL 主機，本範例使用 GCE 進行
• 先建立好一台 Windows Server 2022 主機
• 下載並安裝 SSMS 工具
• 開啟 CDC 同步

匯入測試資料

下載 AdventureWorks2017.bak 檔後匯入資料庫，於 CloudSQL 終點選 import

點選 BROWSE 建立新的 Bucket

輸入 Bucket 名稱

選擇 Region

使用預設，點選繼續

同樣選擇預設

點選建立

點選確認

上傳到 Bucket 後，選擇 bak 檔案

1. 選擇上傳至 bucket 的 bak 檔案
2. 輸入匯入後建立的資料庫名稱
3. 匯入

設定 Authorized network

使用 SSMS 確認

Server name 輸入 CloudSQL ip，內外部 ip 均可 Authentication 選擇 SQL Server Authentication Login 預設帳號為 sqlserver Password 為之前建立密碼

查看是否成功匯入

設定 CDC 同步

參考 Enable change data capture (CDC) 進行設定

啟動資料庫 CDC 同步功能

EXEC msdb.dbo.gcloudsql_cdc_enable_db [DB_NAME]

預期結果

啟用 CDC capture job

EXEC sys.sp_cdc_start_job @job_type = N'cleanup'

啟用資料表同步

EXEC sys.sp_cdc_enable_table
  @source_schema = N'dbo',
  @source_name = N'age',
  @role_name = N'CDC'

確認是否啟用成功

EXECUTE sys.sp_cdc_help_change_data_capture
  @source_schema = N'dbo',
  @source_name = N'age'

建立 CloudSQL Proxy VM

由於 DataFusion 無法直接連到 CloudSQL，因此必須透過建立一台 GCE 上面跑 CloudSQL Proxy 方式連線

首先建立一台 Ubuntu 20.04 VM 並依照以下流程安裝 MSSQL Client 套件

Install SQL Server command-line tools on Linux - SQL Server | Microsoft Learn

curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo apt-get install mssql-tools18 unixodbc-dev
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc
source ~/.bashrc

下載 CloudSQL Proxy

cd /opt
curl -o cloud-sql-proxy https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.7.2/cloud-sql-proxy.linux.amd64
chmod +x cloud-sql-proxy

設定開機啟動，記得置換 CloudSQL Connection name

export INSTANCE_CONNECTION_NAME="YOUR_CONNECTION_NAME"
echo "/opt/cloud-sql-proxy --address 0.0.0.0 --port 1433 ${INSTANCE_CONNECTION_NAME} --private-ip &" > /opt/start_proxy.sh
sed -i '1i #!/bin/sh' /opt/start_proxy.sh
chmod +x /opt/start_proxy.sh
echo "[Unit]
Description=start cloudsql proxy

[Service]
ExecStart=/opt/start_proxy.sh
Type=oneshot
RemainAfterExit=yes

[Install]
WantedBy=multi-user.target" > /etc/systemd/system/cloudsqlproxy.service
systemctl daemon-reload
systemctl enable cloudsqlproxy.service

使用 sqlcmd 測試

/opt/start_proxy.sh
sqlcmd -S tcp:127.0.0.1,1433 -U YOUR_USERNAME -P YOUR_PASSWORD -C

可使用以下指令查詢資料庫

select DB_NAME()
GO

輸出範例

建立 Cloud Data Fusion instance

點我建立 instance

輸入名稱，直接點選 Create 即可

點選 Add to edition

勾選 Enable Private IP

設定 VPC Peering

先至 Data Fusion 中複製必要資訊

1. 複製 Project ID
2. 複製 Region
3. 複製 Instance ID

至 VPC 中新增 PEERING

建立 PEERING

1. 輸入名稱
2. 選擇其他 PROJECT
3. 貼上上一步的 ProjectID
4. 貼上上一步步驟 2+3，組成方式為”Region”-“InstanceID”
5. 勾選 Export custom routes
6. 建立

建立完成後便會出現 PEERING

設定 instance

下載 JDBC 9.4 版本，並解壓縮後，點選 View Instance，如果使用最新版會遇到加密問題而無法連線

點選繼續

點選 Control Center

點選 + 後，再上傳 Driver 上傳

上傳 mssql-jdbc-9.4.1.jre8.jar 檔

Name 輸入 sqlserver Class name 輸入 com.microsoft.sqlserver.jdbc.SQLServerDriver

Version 維持預設即可

開始同步

回到主選單，點選 Replication

建立 replication job

輸入名稱

1. 選擇 SQL Server
2. 輸入 CloudSQL Proxy GCE 之 ip
3. 選擇前面設定的 driver
4. 輸入欲同步的資料庫名稱
5. 輸入使用者帳密

點選下一步

1. 可設定事先建立好的 Dataset 名稱
2. 選擇資料位置
3. 下一步

選擇要同步的表格後，再點選下一步

點選下一步

使用預設值，下一步

使用預設值，下一步

點選開始

啟動會需要一點時間

啟動後便可點選 Logs 查看狀況

於 SSMS 資料庫中新增資料

便可於 BQ 中查到所新增之紀錄

疑難排解

資料表無 Primary Key

沒有 Primary Key 會無法正常同步，請留意資料表需有 Primary Key

CloudSQL Proxy VM 無法連線到 CloudSQL

在 CloudSQL Proxy VM 測試連線到 CloudSQL 時發現權限不足

root@cdc-proxy:/opt# sqlcmd -S tcp:127.0.0.1,1433 -U YOUR_USERNAME -P YOUR_PASSWORD -C

2023/12/06 03:10:36 [timotion-bigquery:asia-east1:cdc-mssql] accepted connection from 127.0.0.1:56693
2023/12/06 03:10:36 [timotion-bigquery:asia-east1:cdc-mssql] failed to connect to instance: failed to get instance: Refresh error: failed to get instance metadata (connection name = "timotion-bigquery:asia-east1:cdc-mssql"): googleapi: Error 403: Request had insufficient authentication scopes.
Details:
[
  {
    "@type": "type.googleapis.com/google.rpc.ErrorInfo",
    "domain": "googleapis.com",
    "metadata": {
      "method": "google.cloud.sql.v1beta4.SqlConnectService.GetConnectSettings",
      "service": "sqladmin.googleapis.com"
    },
    "reason": "ACCESS_TOKEN_SCOPE_INSUFFICIENT"
  }
]
More details:
Reason: insufficientPermissions, Message: Insufficient Permission
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server..
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : TCP Provider: Error code 0x68.
Sqlcmd: Error: Microsoft ODBC Driver Server : Client unable to establish connection.
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Client unable to establish connection due to prelogin failure.

如果是使用預設的 GCE service account，會需要調整 access scope，先點選 VM

點選編輯

開啟 CloudSQL 後儲存

並確認是否開啟 sqladmin.googleapis.com 這個 API，如果沒開，使用 sqlcmd 會出現以下錯誤

Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server..
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : TCP Provider: Error code 0x68.
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Client unable to establish connection.
Sqlcmd: Error: Microsoft ODBC Driver 18 for SQL Server : Client unable to establish connection due to prelogin failure.

點我開啟 API