使用BCP + Polybase 實現本地數據遷移到Azure DB

来源:https://www.cnblogs.com/huwz/archive/2022/10/17/bcpmigratedata.html
-Advertisement-
Play Games

使用BCP + Polybase 實現本地數據遷移到Azure DB 一、背景 最近因為要做一些實驗的緣故， 需要在Azure DB上準備一些帶數據的資料庫。 AdventureWorks2019 和AdventureWorksDW2019就挺合適的，官網上能提供這兩個資料庫的備份文件。 在我將其成 ...


使用BCP + Polybase 實現本地數據遷移到Azure DB

 

一、背景

最近因為要做一些實驗的緣故， 需要在Azure DB上準備一些帶數據的資料庫。 AdventureWorks2019 和AdventureWorksDW2019就挺合適的，官網上能提供這兩個資料庫的備份文件。 在我將其成功還原到了本地SQL 實例中， 但是怎麼把數據遷移到Azure DB上有點犯難了。

雖然辦法有很多，比如可用採用資料庫遷移工具。但我的目標是能儘量的自動化，因此更希望以腳本的方式來實現。在這個目標前提下， 成功實現了數據的上雲遷移。 我的這個方式不一定是最好的，但效率上還是挺不錯的，而且在數據遷移的過程中也趟了一些坑。因此在此記錄過程中踩過的一些問題，有需要的可以借鑒。

 

二、過程

以下為具體實施過程的記錄。

 

 

Step 1: BCP 導出數據為文件。

-- ===============

-- bcp 批量導出數據

-- ===============

select concat('bcp AdventureWorksDW2019.'

, a.name,'.' , b.name

, ' out ', a.name, '.', b.name, '.txt -c -t"|" -T -S. -C65001') as c

from sys.schemas a

     inner join sys.objects b on a.schema_id = b.schema_id

        where (b.name like 'dim%' or b.name like 'fact%') and b.type='u'

        order by a.name, b.name

-- 示例

bcp AdventureWorksDW2019.dbo.DimAccount out dbo.DimAccount.txt -c -t"|" -T -S. -C65001

 

1)     上面SQL的結果是形成多個BCP的命令， 將AdventureWorksDW2019的DIM表和FACT表的數據導出到獨立txt文件的命令。

2)     沒有文件頭行， 也就是沒有表頭；如果需要表頭，只能用Query的方式Union All一行出來。不過在我的實驗中，可不用要求導出表頭; 而是直接使用raw表的schema。 題外一句，這麼用，可能不太適合使用ADF的場景。

3)     沒有Quated； 因此如果使用逗號,分隔， 一定要註意現有的內容是否也會包含逗號；如果包含了， 則需要改成其他分隔符， 在此我使用的是豎線”|”作為分隔符；

4)     Code page = 65001 （UTF8 Formated）。大部分情況都不需要指定Code Page, 但是我在導出DimProduct表的是時候出現了亂碼問題， 指定Code Page = 65001解決。

5)     類型問題， 比如binary， image， geometry ， 這些事實上都不會造成困擾。

Step 2: 上傳數據文件到Azure Blob

1)     可用使用azcopy 工具將文件上傳，可用查閱官網示例 azcopy | Microsoft Learn

2)     也可以手動。本例中我是手動在Portal上上傳到路徑src_data/adventureworks2019dw/bcp 下的

Step3: 資料庫上新建 External Data Source

1)     參考文檔 CREATE EXTERNAL DATA SOURCE (Transact-SQL) - SQL Server | Microsoft Learn

2)     已手動建立【azblobadvtwksblob】

Step 4: 在資料庫上建立raw 表

1)     建raw表目的是為了能讓數據更好的從blob中通過polybase技術複製到SQL DB中來。

2)     創建raw schema

CREATE SCHEMA raw

3)     1:1 創建raw表

select concat('SELECT top 0 * INTO raw.', a.name,'_', b.name

            , ' FROM ', a.name , '.', b.name

            , '; TRUNCATE table raw.',  a.name,'_', b.name )

from sys.schemas a

     inner join sys.objects b on a.schema_id = b.schema_id

    where (b.name like 'dim%' or b.name like 'fact%')

           and b.type='u' and a.name='dbo'

    order by a.name, b.name

 

-- 示例

SELECT top 0 * INTO raw.dbo_DimAccount FROM dbo.DimAccount; TRUNCATE table raw.dbo_DimAccount

SELECT top 0 * INTO raw.dbo_DimCurrency FROM dbo.DimCurrency; TRUNCATE table raw.dbo_DimCurrency

 

NOTES: 這樣做， 可用減少手動一個個建表的工作量。確保raw表是空表，無數據。

為什麼要建raw層的表？ 目的就是為了減少因為DB本身的約束導致的數據導入失敗，比如外鍵、視圖綁定、類型問題等，因為raw表沒有外鍵、Identity、引用等約束條件，所以複製數據的時候不容易產生問題。數據都進入DB了， 再查詢起來就比較方便， 也就更容易找到問題所在。數據在Blob上，理論上也有工具可以查詢，但是從效率上還是SQL更高。

在本例中 ，由於是同構的資料庫，所以不會出現類型相容的問題。 但是如果有必要， 可用將raw表的所有的欄位類型都改為nvarchar(N)的，N取決於業務欄位具體的長度。這樣在上數的過程中更不容易出現類型轉換不相容導致的問題。具體的類型轉換，將在” 從raw表查詢數據插入到目標表”步驟中隱式實現也可以。

Step 5: 將數據導入到raw表

1） 使用PolyBase技術，將外部數據源的數據接入到raw表中

-- ===============

-- 從外部文件載入數據

-- ===============

declare @root nvarchar(400) = 'src-data/adventureworks2019dw/bcp/'

SELECT CONCAT('TRUNCATE TABLE raw.', a.name,'_', b.name ,';BULK INSERT raw.',   a.name,'_', b.name, ' FROM ''' , @root,  a.name,'.', b.name,'.txt'' WITH (DATA_SOURCE = ''azblobadvtwksblob'', FIRSTROW =1,FIELDTERMINATOR = ''|'' );')

from sys.schemas a

     inner join sys.objects b on a.schema_id = b.schema_id

    where (b.name like 'dim%' or b.name like 'fact%') and b.type='u' and a.name='dbo'

-- 示例

TRUNCATE TABLE raw.dbo_DimAccount;

BULK INSERT raw.dbo_DimAccount

FROM 'src-data/adventureworks2019dw/bcp/dbo.DimAccount.txt'

WITH (DATA_SOURCE = 'azblobadvtwksblob', FIRSTROW =1,FIELDTERMINATOR = '|' );

 

TRUNCATE TABLE raw.dbo_DimCurrency;

BULK INSERT raw.dbo_DimCurrency

FROM 'src-data/adventureworks2019dw/bcp/dbo.DimCurrency.txt'

WITH (DATA_SOURCE = 'azblobadvtwksblob', FIRSTROW =1,FIELDTERMINATOR = '|' );

 

3)     如前所述， 如有必要加上CODEPAGE=‘65001’ （UTF8 Format）

參考 BULK INSERT (Transact-SQL) - SQL Server | Microsoft Learn

Step 6: 去除數據目標表的限制

1） 備份資料庫的DACPAC（即備份表結構的DDL）

2） 刪除外鍵約束

 

SELECT concat('ALTER TABLE ',

          SCHEMA_NAME(a.schema_id), '.', object_name(a.parent_object_id)

       , ' drop constraint ' , a.name ,';')

FROM SYS.foreign_keys A

WHERE TYPE='F'

and   (object_name(a.parent_object_id) like 'dim%' or object_name(a.parent_object_id) like 'fact%')  and schema_name(a.schema_id)='dbo'

order by SCHEMA_NAME(a.schema_id), object_name(a.parent_object_id)

 

 

3） 刪除視圖， 因為視圖有的會和表做schema binding

select concat('drop view ', schema_name(a.schema_id) , '.', a.name )

from sys.views a

    where (A.name like 'dim%' or A.name like 'fact%')  and schema_name(a.schema_id)='dbo'

 

 

Step 7: 從raw表查詢數據插入到目標表

1） 如有必要，清空目標表

2） 判斷identity 列；如果表有Identity列，則需要Set IDENTITY_INSERT ON， 並使用明確列選擇；數據入表完成後，再Set IDENTITY_INSERT OFF

-- ===============

-- 從raw導入數據

-- ===============

; with c1 as

(

SELECT

   isIdentity = sum(case when b.is_identity = 1 then 1 else 0 end) ,

   schemaName = SCHEMA_NAME(a.schema_id),

   tableName = a.name,

   columnNames  = STRING_AGG( '['+ b.name + ']', ',')

FROM SYS.objects a

      left join sys.columns b on a.object_id = b.object_id 

WHERE a.type='u'

     and   (A.name like 'dim%' or A.name like 'fact%')  and schema_name(a.schema_id)='dbo'

      and b.is_computed = 0

group by a.schema_id, a.name

)

SELECT

 pk_identity_prefix = case when (isIdentity >0  ) then concat( ' SET IDENTITY_INSERT ' , schemaName, '.',tableName, ' On;') else '' end

    ,

   load_script=concat('TRUNCATE TABLE ', schemaName, '.',tableName

   , '; INSERT INTO ' ,schemaName, '.',tableName , '(',columnNames,')'

   ,  ' SELECT ',  columnNames

          ,' FROM raw.',schemaName, '_',tableName,' ;')

   ,

 pk_identity_prefix = case when (isIdentity >0  ) then concat( ' SET IDENTITY_INSERT ' , schemaName, '.',tableName, ' OFF;') else '' end

FROM c1

order by  schemaName,  tableName

 

--打開IDENTITY_INSERT

SET IDENTITY_INSERT dbo.DimAccount On;

--清空

TRUNCATE TABLE dbo.DimAccount;

--插入

INSERT INTO dbo.DimAccount([AccountKey],[ParentAccountKey],[AccountCodeAlternateKey],[ParentAccountCodeAlternateKey],[AccountDescription],[AccountType],[Operator],[CustomMembers],[ValueType],[CustomMemberOptions])

SELECT [AccountKey],[ParentAccountKey],[AccountCodeAlternateKey],[ParentAccountCodeAlternateKey],[AccountDescription],[AccountType],[Operator],[CustomMembers],[ValueType],[CustomMemberOptions] FROM raw.dbo_DimAccount ;

--還原

SET IDENTITY_INSERT dbo.DimAccount OFF;

 

 

Step 8: 還原外鍵、視圖等

1)     使用SQLPACKAGE工具，利用資料庫的DACPAC，對資料庫進行還原。

2)     或者使用Visual Studio的Database Project 對資料庫進行還原，本例就是用的這個方式。

 

附

1） BCP 工具的地址：

通常使用Visual Studio 的CMD可用自動找到， 如果沒有，可用試試類似路徑：

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe

2） BCP的命令幫助

usage: bcp {dbtable | query} {in | out | queryout | format} datafile

  [-m maxerrors]            [-f formatfile]          [-e errfile]

  [-F firstrow]             [-L lastrow]             [-b batchsize]

  [-n native type]          [-c character type]      [-w wide character type]

  [-N keep non-text native] [-V file format version] [-q quoted identifier]

  [-C code page specifier]  [-t field terminator]    [-r row terminator]

  [-i inputfile]            [-o outfile]             [-a packetsize]

  [-S server name]          [-U username]            [-P password]

  [-T trusted connection]   [-v version]             [-R regional enable]

  [-k keep null values]     [-E keep identity values][-G Azure Active Directory Authentication]

  [-h "load hints"]         [-x generate xml format file]

  [-d database name]        [-K application intent]  [-l login timeout]

 

強調一點的是， 如果是直接導出表的數據， 用指令”out”; 如果是導出查詢的數據， 用指令”queryout”。

3） AzCopy 工具的介紹：

參考： azcopy | Microsoft Learn

4） SQLPACKAGE工具的介紹：

參考：SqlPackage.exe - SQL Server | Microsoft Learn

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 騰訊雲簡單使用
    前面簡單介紹了騰訊雲的由來和定義，本編介紹騰訊的簡單使用（我這個展示的是把最近寫的vs項目部署到雲服務中）： 1、首先根據前面的定義註冊個騰訊雲賬戶，然後購買個雲伺服器，當然首次註冊的都有免費體驗資格，我這裡用到的是免費體驗輕量式伺服器： 進入購買然後開通就會顯示在你的輕量伺服器上，會根據你選的wi ...
  • 樹莓派Zero 2 W（ubuntu-22.04）通過.NET6和libusb操作USB讀寫
    有這個想法的初衷 喜歡電子和DIY硬體的朋友對稚暉君應該都不陌生，他定期都會分享一些自己做的好玩的硬體，他之前做了一個ElectronBot桌面機器人我就很感興趣，所以就自己也做了一個。 起初我只是自己開發了一個叫電子腦殼的上位機軟體，大家在之前的博客里應該也有見到，是個用WinUI（WASDK）開 ...
  • 學習ASP.NET Core Blazor編程系列七——新增圖書
    一、 先決條件 1.Azure Repos Git/Git和項目上傳 把本地的Net Core項目上傳至Azure Repos Git/Git 2.Docker Registry Service Connection/Azure subscription和Azure Container Regist ...
  • keepalived實現nginx負載均衡機高可用
    keepalived實現nginx負載均衡機高可用 環境說明： | 系統 | 主機名 | IP | 服務 | | | | | | | centos8 | master | 192.168.111.141 | nginxkeepalived | | centos8 | backup | 192.168 ...
  • 華為雲GaussDB資料庫榮獲國際CC EAL4+級別認證
    摘要：近日，華為雲GaussDB企業級分散式資料庫內核正式通過了全球知名獨立認證機構歐洲SGS Brightsight實驗室的安全評估，獲得全球權威信息技術安全性評估標準CC EAL4+級別認證。 本文分享自華為雲社區《中國首個，我們拿下了！業界最高級別！華為雲GaussDB資料庫榮獲國際CC EA ...
  • 數倉性能調優：如何進行函數下推
    摘要：本文主要描述下函數在滿足特征的前提下可以把函數屬性定義為下推屬性。 本文分享自華為雲社區《GaussDB(DWS)性能調優：函數下推》，作者：譡里個檔 。 DWS作為MPP架構的數倉產品，其性能優勢主要在分散式計算上。預設情況下，DWS為了保證結果的正確性，自定義函數預設屬性是不下推的，這會導 ...
  • 資料庫選型思考(PostgreSQL,MySQL,Oracle)
    資料庫選型是一件很大的事情，也是一件很頭疼的事情。 很多企業並沒有資料庫的選型標準，或者並不瞭解業務需要什麼樣的資料庫。 很多企業的資料庫是開發說了算，熟悉什麼就用什麼，很多選型失誤，導致後期非常尷尬的局面。 那麼資料庫選型要註意什麼呢？ 列舉一些例子，取自如下文檔 ...
  • 從0到1設計通用數據大屏搭建平臺
    基於可視化搭建的方式來實現通用數據大屏搭建的解決方案，通過對平臺能力的開發來講解可視化搭建的核心功能實現，幫助有需要的同學瞭解可視化搭建的整體架構設計流程。 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類