將 Microsoft SQL Server 資料載入 BigQuery

預覽

這項功能適用《服務專屬條款》中「一般服務條款」一節的《正式發布前產品條款》。正式發布前功能是依「原樣」提供，支援服務可能受限。 詳情請參閱推出階段說明。

注意：如要取得這項功能的支援或提供意見回饋，請傳送電子郵件至 [e-mail]。

您可以使用 Microsoft SQL Server 連接器的 BigQuery 資料移轉服務，將資料從 Microsoft SQL Server 載入 BigQuery。Microsoft SQL Server 連接器支援從地端部署環境和其他雲端供應商 (例如 Cloud SQL、Amazon Web Services (AWS) 或 Microsoft Azure) 中代管的 Microsoft SQL Server 執行個體載入資料。透過 BigQuery 資料移轉服務，您可以建立隨選和週期性資料移轉工作，將 Microsoft SQL Server 執行個體中的資料移轉至 BigQuery。

限制

Microsoft SQL Server 資料移轉工作有下列限制：

• Microsoft SQL Server 資料庫的連線數有上限。因此，同時傳輸至單一 Microsoft SQL Server 資料庫的執行次數也有限制。請確認並行傳輸工作數量少於 Microsoft SQL Server 資料庫支援的並行連線數量上限。
• 為避免資料遺失，部分 Microsoft SQL Server 資料類型可能會對應至 BigQuery 中的 STRING 類型。舉例來說，Microsoft SQL Server 中未定義精確度和比例的特定數值型別，可能會對應至 BigQuery 中的 STRING。詳情請參閱「資料類型對應」。

增量移轉限制

Microsoft SQL Server 增量轉移作業有下列限制：

• 浮水印欄只能選擇 TIMESTAMP 欄。
• 只有含有有效浮水印欄的資產，才支援增量擷取。
• 浮水印資料欄中的值必須單調遞增。
• 增量轉移作業無法同步處理來源資料表中的刪除作業。
• 單一轉移設定只能支援增量或完整擷取。
• 第一次執行增量擷取後，就無法更新 asset 清單中的物件。
• 首次執行增量擷取後，就無法在轉移設定中變更寫入模式。
• 第一次執行遞增式擷取後，就無法變更時間戳記欄或主鍵。
• 目的地 BigQuery 資料表會使用提供的主鍵分群，並受分群資料表限制約束。
• 首次將現有轉移設定更新為增量擷取模式時，更新後的第一次資料轉移作業會轉移資料來源中的所有可用資料。後續的增量資料轉移作業只會轉移資料來源中的新資料列和更新資料列。
• 建議您在浮水印資料欄上建立索引。這個連接器會使用遞增轉移中的篩選器，因此為這些資料欄建立索引可提升效能。
• 進行增量轉移時，必須使用更新後的資料類型對應。

資料擷取選項

下節提供設定 Microsoft SQL Server 資料移轉時的資料擷取選項相關資訊。

傳輸層安全標準 (TLS) 設定

Microsoft SQL Server 連接器支援傳輸層安全標準 (TLS) 設定，可加密傳輸至 BigQuery 的資料。Microsoft SQL Server 連接器支援下列 TLS 設定：

• 「加密資料，並驗證 CA 和主機名稱」模式。這個模式會使用 TCPS 通訊協定透過 TLS 完整驗證伺服器。這項功能會加密所有傳輸中的資料，並驗證資料庫伺服器的憑證是否由信任的憑證授權單位 (CA) 簽署。這個模式也會檢查您連線的主機名稱，是否與伺服器憑證中的一般名稱 (CN) 或主體別名 (SAN) 完全相符。這個模式可防止攻擊者使用其他網域的有效憑證，冒充資料庫伺服器。

  如果主機名稱與憑證 CN 或 SAN 不符，連線就會失敗。您必須設定 DNS 解析，使其與憑證相符，或使用其他安全模式。使用這個模式可獲得最安全的防護，避免中間人 (PITM) 攻擊。

• 「加密資料，但僅驗證 CA」模式。這個模式會透過 TCPS 通訊協定使用 TLS 加密所有資料，並驗證伺服器的憑證是否由用戶端信任的 CA 簽署。不過，這個模式不會驗證伺服器的主機名稱。只要憑證有效且由可信任的 CA 發行，這個模式就能成功連線，無論憑證中的主機名稱是否與您連線的主機名稱相符。

  如果您想確保連線至憑證由信任的 CA 簽署的伺服器，但主機名稱無法驗證，或您無法控管主機名稱設定，請使用這個模式。

• 「僅加密」模式。這個模式會加密用戶端與伺服器之間傳輸的所有資料。不會執行任何憑證或主機名稱驗證。

  這個模式會保護傳輸中的資料，提供一定程度的安全性，但可能容易受到中間人攻擊。

  如果您需要確保所有資料都經過加密，但無法或不想驗證伺服器的身分，請使用這個模式。使用私人虛擬私有雲時，建議採用這個模式。

• 「不加密或驗證」模式。這個模式不會加密任何資料，也不會執行任何憑證或主機名稱驗證。所有資料都會以純文字形式傳送。

  我們不建議在處理機密資料的環境中使用這個模式。建議您只在安全無虞的獨立網路中，將這個模式用於測試。

信任的伺服器憑證 (PEM)

如果您使用「加密資料，並驗證 CA 和主機名稱」模式或「加密資料，並驗證 CA」模式，也可以提供一或多個 PEM 編碼的憑證。在某些情況下，BigQuery 資料移轉服務需要驗證資料庫伺服器的身分，才能建立 TLS 連線，這時就需要這些憑證：

• 如果您使用貴機構內部私有 CA 簽署的憑證，或是自行簽署的憑證，則必須提供完整的憑證鏈結或單一自行簽署憑證。如果是透過代管雲端服務供應商 (例如 Amazon Relational Database Service (RDS)) 的內部 CA 發行的憑證，則必須執行這項操作。
• 如果資料庫伺服器憑證是由公開 CA 簽署 (例如 Let's Encrypt、DigiCert 或 GlobalSign)，您就不需要提供憑證。BigQuery 資料移轉服務已預先安裝並信任這些公開 CA 的根憑證。

您可以在轉移設定的「信任的 PEM 憑證」欄位中指定 PEM 編碼憑證，但須符合下列規定：

• 憑證必須是有效的 PEM 編碼憑證鏈結。
• 憑證必須完全正確。如果鏈結中缺少任何憑證或內容有誤，TLS 連線就會失敗。
• 如果是單一憑證，您可以提供資料庫伺服器中的單一自行簽署憑證。
• 如為私人 CA 核發的完整憑證鏈結，您必須提供完整的信任鏈結。這包括資料庫伺服器的憑證，以及任何中繼和根 CA 憑證。

完整或累加轉移

設定 Microsoft SQL Server 移轉作業時，您可以在移轉設定中選取「完整」或「增量」寫入偏好設定，指定資料載入 BigQuery 的方式。預先發布版支援增量轉移。

注意： 如要要求提供意見回饋或支援增量轉移，請傳送電子郵件至 [e-mail]。

您可以設定完整資料移轉，在每次資料移轉時，轉移 Microsoft SQL Server 資料集的所有資料。

或者，您也可以設定增量資料移轉作業 (搶先版)，只移轉上次資料移轉後變更的資料，而不是在每次資料移轉時載入整個資料集。如果為資料移轉作業選取「增量」Incremental，則必須指定「附加」Append或「插入或更新」Upsert寫入模式，定義在增量資料移轉期間，資料如何寫入 BigQuery。以下各節說明可用的寫入模式。

附加寫入模式

附加寫入模式只會將新資料列插入目的地資料表。這個選項會嚴格附加移轉的資料，不會檢查現有記錄，因此這個模式可能會導致目的地資料表中的資料重複。

選取附加模式時，必須選取浮水印欄。Microsoft SQL Server 連接器必須使用浮水印資料欄，才能追蹤來源資料表中的變更。

如果是 Microsoft SQL Server 轉移作業，建議選取只在建立記錄時更新的資料欄，後續更新不會變更該資料欄。例如「CREATED_AT」欄。

Upsert 寫入模式

新增或更新寫入模式會檢查主鍵，以更新資料列或在目的地資料表中插入新資料列。您可以指定主鍵，讓 Microsoft SQL Server 連接器判斷需要進行哪些變更，才能讓目的地資料表與來源資料表保持同步。如果在資料移轉期間，目標 BigQuery 資料表中存在指定的主鍵，Microsoft SQL Server 連接器就會使用來源資料表中的新資料更新該列。如果資料轉移期間沒有主鍵，Microsoft SQL Server 連接器就會插入新列。

選取「新增或更新」模式時，必須選取浮水印欄和主鍵：

• Microsoft SQL Server 連接器必須有水位線資料欄，才能追蹤來源資料表中的變更。
  • 選取每次修改資料列時都會更新的水印資料欄。建議使用與 UPDATED_AT 或 LAST_MODIFIED 欄類似的資料欄。

• 主鍵可以是資料表上的一或多個資料欄，Microsoft SQL Server 連接器會根據這些資料欄判斷是否需要插入或更新資料列。

  選取包含非空值的資料欄，這些值在資料表的所有資料列中都是不重複的。建議您使用包含系統產生 ID、專屬參照代碼 (例如自動遞增 ID) 或不可變動的時間序列 ID 的資料欄。

  為避免資料遺失或損毀，您選取的主鍵資料欄必須具有不重複的值。如果您對所選主鍵欄的唯一性有疑慮，建議改用附加寫入模式。

增量擷取行為

在資料來源中變更資料表結構定義時，這些資料表的增量資料移轉作業會以以下方式反映在 BigQuery 中：

資料來源異動	增量擷取行為
新增資料欄	目的地 BigQuery 資料表會新增資料欄。 這個資料欄的所有先前記錄都會有空值。
刪除資料欄	刪除的資料欄仍會保留在目的地 BigQuery 資料表中。系統會在新項目中填入空值。
變更資料欄中的資料類型	連接器僅支援 ALTER COLUMN DDL 陳述式支援的資料類型轉換。如果轉換成其他資料類型，資料轉移作業就會失敗。 如果遇到任何問題，建議建立新的轉移設定。
重新命名資料欄	原始資料欄會保留在目的地 BigQuery 資料表中，而目的地資料表會新增一個名稱更新的資料欄。

事前準備

如要排定 Microsoft SQL Server 資料移轉作業，請務必先符合下列必要條件。

Microsoft SQL Server 必要條件

您必須在 Microsoft SQL Server 資料庫中建立使用者帳戶。詳情請參閱「建立可登入的使用者」。

BigQuery 必要條件

• 確認您已完成啟用 BigQuery 資料移轉服務的一切必要動作。
• 請建立 BigQuery 資料集來儲存您的資料。

必要的角色

如要取得建立 Microsoft SQL Server 資料移轉作業所需的權限，請要求管理員在專案中授予您 BigQuery 管理員 (roles/bigquery.admin) IAM 角色。如要進一步瞭解如何授予角色，請參閱「管理專案、資料夾和組織的存取權」。

這個預先定義的角色具備建立 Microsoft SQL Server 資料移轉作業所需的權限。如要查看確切的必要權限，請展開「Required permissions」(必要權限) 部分：

所需權限

如要建立 Microsoft SQL Server 資料移轉作業，必須具備下列權限：

• bigquery.transfers.update
• bigquery.datasets.get

您或許還可透過自訂角色或其他預先定義的角色取得這些權限。

網路設定

如果 Microsoft SQL Server 資料庫連線沒有可用的公開 IP 位址，您必須設定特定網路設定。詳情請參閱下列章節：

• 設定與執行個體的連線 Google Cloud
• 設定與 AWS 的連線
• 設定與 Azure 的連線

設定 Microsoft SQL Server 資料移轉作業

選取下列選項之一：

控制台

1. 前往「資料轉移」頁面。

   前往「資料轉移」頁面

2. 按一下 「建立轉移作業」。

3. 在「Source type」(來源類型) 部分，「Source」(來源) 請選取「Microsoft SQL Server」。

4. 在「Data source details」(資料來源詳細資料) 部分執行下列操作：

   • 在「Network attachment」(網路連結) 部分選取現有的網路連結，或是點選「Create Network Attachment」(建立網路連結)。
   • 在「Host」(主機) 部分，輸入 Microsoft SQL Server 資料庫的主機名稱或 IP 位址。
   • 在「Port number」(通訊埠編號) 中，輸入 Microsoft SQL Server 資料庫的通訊埠編號。
   • 在「Database name」(資料庫名稱) 部分，輸入 Microsoft SQL Server 資料庫的名稱。
   • 在「Username」(使用者名稱) 欄位中，輸入啟動 Microsoft SQL Server 資料庫連線的 Microsoft SQL Server 使用者名稱。
   • 在「密碼」欄位中，輸入啟動 Microsoft SQL Server 資料庫連線的 Microsoft SQL Server 使用者密碼。
   • 在「TLS Mode」(TLS 模式) 中，從選單選取一個選項。如要進一步瞭解 TLS 模式，請參閱「TLS 設定」。
   • 在「Trusted PEM Certificate」(信任的 PEM 憑證) 欄位中，輸入核發資料庫伺服器 TLS 憑證的憑證授權單位 (CA) 公開憑證。詳情請參閱「信任的伺服器憑證 (PEM)」一文。
   • 在「Enable legacy mapping」(啟用舊版對應) 部分，選取「true」(預設)，即可使用舊版資料類型對應。選取「false」即可使用更新後的資料類型對應。如果您要進行增量轉移，這個值必須為 false。如要進一步瞭解資料類型對應更新，請參閱「2027 年 3 月 16 日」。 資料庫伺服器。
   • 在「Ingestion type」(擷取類型) 部分，選取「Full」(完整) 或「Incremental」(增量)。
     • 如果選取「Incremental」(增量) (預先發布版)，請在「Write mode」(寫入模式) 中選取「Append」(附加) 或「Upsert」(新增或更新)。如要進一步瞭解不同的寫入模式，請參閱「完整或增量轉移」一節的說明。
   • 在「Microsoft SQL Server objects to transfer」(要移轉的 Microsoft SQL Server 物件)，瀏覽 Microsoft SQL Server 資料表，或手動輸入移轉所需的資料表名稱。

5. 在「Destination settings」(目的地設定) 部分，「Dataset」(資料集) 請選取您為了儲存資料而建立的資料集，或按一下「建立新的資料集」，然後建立一個做為目的地資料集。

6. 在「Transfer config name」(轉移設定名稱) 部分，「Display name」(顯示名稱) 請輸入移轉作業的名稱。移轉作業名稱可以是任意值，日後需要修改移轉作業時能夠據此識別。

7. 在「Schedule options」(排程選項) 部分執行下列操作：

   • 選取重複頻率。如果選取「小時」、「天數」(預設)、「週數」或「月數」選項，則必須一併指定頻率。你也可以選取「Custom」(自訂) 選項，建立專屬的重複頻率。如果選取「On-demand」(隨選)，這項資料移轉作業會在您手動觸發後執行。
   • 視情況選取「立即開始」或「在所設時間開始執行」選項，並提供開始日期和執行時間。

8. 選用：在「Notification options」(通知選項) 專區，執行下列操作：

   • 如要啟用電子郵件通知，請點選「電子郵件通知」切換按鈕，將其設為開啟。啟用這個選項之後，若移轉失敗，移轉作業管理員就會收到電子郵件通知。
   • 如要為移轉作業設定 Pub/Sub 執行通知，請點選「Pub/Sub notifications」(Pub/Sub 通知) 切換按鈕，將其設為開啟。您可以選取主題名稱，或點選「Create a topic」(建立主題) 來建立主題。

9. 選用：在「Advanced options」(進階選項) 部分，選取這次轉移作業的加密類型。您可以選取 Google-owned and Google-managed encryption key或客戶擁有的 Cloud Key Management Service 金鑰。如要進一步瞭解加密金鑰，請參閱「客戶自行管理的加密金鑰 (CMEK)」。

10. 按一下 [儲存]。

bq

輸入 bq mk 指令，並加上移轉建立標記 --transfer_config：

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

更改下列內容：

• PROJECT_ID (選用)：您的 Google Cloud 專案 ID。 如未提供 --project_id 標記指定特定專案，系統會使用預設專案。
• DATA_SOURCE：資料來源，即 sqlserver。
• DISPLAY_NAME：資料移轉設定的顯示名稱。移轉作業名稱可以是任意值，日後需要修改移轉作業時，能夠據此識別即可。
• DATASET：資料移轉設定的目標資料集。

• PARAMETERS：已建立移轉設定的 JSON 格式參數。例如：--params='{"param":"param_value"}'。以下是 Microsoft SQL Server 轉移作業的參數：

  • connector.networkAttachment (選用)：要連線至 Microsoft SQL Server 資料庫的網路附件名稱。
  • connector.database：Microsoft SQL Server 資料庫的名稱。
  • connector.endpoint.host：資料庫的主機名稱或 IP 位址。
  • connector.endpoint.port：資料庫的通訊埠編號。
  • connector.authentication.username：資料庫使用者的使用者名稱。
  • connector.authentication.password：資料庫使用者的密碼。
  • connector.legacyMapping：設為 true (預設)，即可使用舊版資料類型對應。如要使用更新後的資料類型對應，請將這個值設為 false。如果您要進行增量轉移，這個值必須是 false。如要進一步瞭解資料類型對應更新，請參閱「2027 年 3 月 16 日」。
  • connector.tls.mode：指定要用於這項轉移作業的 TLS 設定：
    • ENCRYPT_VERIFY_CA_AND_HOST 加密資料，並驗證 CA 和主機名稱
    • ENCRYPT_VERIFY_CA 加密資料，且僅驗證 CA
    • ENCRYPT_VERIFY_NONE 僅適用於資料加密
    • DISABLE，因為沒有加密或驗證
  • connector.tls.trustedServerCertificate：(選用) 提供一或多個 PEM 編碼的憑證。只有在 connector.tls.mode 的值為 ENCRYPT_VERIFY_CA_AND_HOST 或 ENCRYPT_VERIFY_CA 時，才需要提供這項屬性。
  • ingestionType：指定 full 或 incremental。預先發布版支援增量轉移。詳情請參閱「完整或增量移轉」。
  • writeMode：指定 WRITE_MODE_APPEND 或 WRITE_MODE_UPSERT。
  • watermarkColumns：將資料表中的資料欄指定為浮水印資料欄。如要進行增量轉移，這個欄位為必填。
  • primaryKeys：將資料表中的資料欄指定為主鍵。 如要進行增量轉移，這個欄位為必填。
  • assets：要從 Microsoft SQL Server 資料庫轉移的 Microsoft SQL Server 資料表名稱清單。

舉例來說，下列指令會建立名為 My Transfer 的 Microsoft SQL Server 轉移作業：

bq mk \
    --transfer_config
    --target_dataset=mydataset
    --data_source=sqlserver
    --display_name='My Transfer'
    --params='{"assets":["DB1/DEPARTMENT","DB1/EMPLOYEES"],
        "connector.authentication.username": "User1",
        "connector.authentication.password":"ABC12345",
        "connector.database":"DB1",
        "connector.endpoint.host":"192.168.0.1",
        "connector.endpoint.port":"1520",
        "connector.networkAttachment":"projects/dev-project1/regions/us-central1/networkattachments/na1",
        "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"}'

在增量轉移期間指定多項資產時，watermarkColumns 和 primaryKeys 欄位的值會對應至 assets 欄位中的值位置。在下列範例中，dep_id 對應於資料表 DB1/DEPARTMENT，而 report_by 和 report_title 則對應於資料表 DB1/EMPLOYEES。

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

儲存移轉設定後，Microsoft SQL Server 連接器會根據排程選項，自動觸發移轉作業。每次執行移轉作業時，Microsoft SQL Server 連接器都會將 Microsoft SQL Server 中的所有可用資料移轉至 BigQuery。

如要在正常時間表以外手動執行資料轉移作業，可以啟動回填作業。

資料類型對應

注意： 2027 年 3 月 16 日，Microsoft SQL Server 連接器將更新部分資料類型對應，如下表所示。詳情請參閱「2027 年 3 月 16 日」。

下表列出 Microsoft SQL Server 資料類型對應的 BigQuery 資料類型：

Microsoft SQL Server 資料型別	BigQuery 資料類型	更新的 BigQuery 資料類型
tinyint	INTEGER
smallint	INTEGER
int	INTEGER
bigint	BIGNUMERIC
bit	BOOLEAN
decimal	BIGNUMERIC
numeric	NUMERIC
money	BIGNUMERIC
smallmoney	BIGNUMERIC
float	FLOAT
real	FLOAT
date	DATE
time	TIME
datetime2	TIMESTAMP	DATETIME
datetimeoffset	TIMESTAMP
datetime	TIMESTAMP	DATETIME
smalldatetime	TIMESTAMP	DATETIME
char	STRING
varchar	STRING
text	STRING
nchar	STRING
nvarchar	STRING
ntext	STRING
binary	BYTES
varbinary	BYTES
image	BYTES
geography	STRING
geometry	STRING
hierarchyid	BYTES
rowversion	BYTES
sql_variant	BYTES
uniqueidentifier	STRING
xml	STRING
json	STRING
vector	STRING

json 和 vector 資料類型僅支援 Azure。

在 Azure SQL 資料庫和 Azure SQL 受控執行個體中，如果設定為採用「永遠保持最新」的更新政策，則支援 JSON 資料類型。如果 Azure SQL 受控執行個體設定了 Microsoft SQL Server 2022 更新政策，則不支援 JSON 資料型別。

Microsoft SQL Server 會將 JSON 儲存為 NVARCHAR(MAX)，而非 JSON 類型。建議您使用 CHECK (ISJSON(json_col) = 1) 進行驗證，並使用 JSON_VALUE() 進行查詢。

Microsoft SQL Server 不支援 vector 資料類型的向量。建議您將向量儲存為 NVARCHAR(MAX) 中的 JSON 陣列，並使用 JSON_VALUE() 擷取向量，然後手動計算 FLOAT 相似度。

疑難排解

如要排解資料移轉問題，請參閱「Microsoft SQL Server 移轉問題」。

定價

這項功能處於預覽階段時，將 Microsoft SQL Server 資料移轉至 BigQuery 不會產生費用。

後續步驟

• 請參閱 BigQuery 資料移轉服務總覽。
• 瞭解如何管理移轉作業，包括取得移轉設定資訊、列出移轉設定，以及查看移轉作業的執行記錄。
• 瞭解如何使用 BigQuery Omni 作業載入資料。