[筆記]使用 Bulk Insert 將 Xml 資料匯入 MSSQL

  • 2495
  • 0
  • SQL
  • 2016-04-30

這是抓取OpenData遇到的一個狀況,小喵要抓取氣象資料,放入資料庫,方便後續的查詢與應用,但是撞了兩個雷:雷一:資料有重複。雷二:維護多資料庫時間長,為了這兩個雷,於是有了這一篇~~最後,小喵透過元件的方式,透過Bulk Insert的方式,將資料整批的放入資料庫中,大大的節省了資料 Insert 的時間,詳細如何,讓我們繼續看下去~

緣起:

為了讓系統可以很容易的參考氣象的相關資料,所以小喵預計要寫一個程式,每日定時的抓取Open Data,並將資料轉入資料庫,好讓其他的資料如果要參考天氣的狀況,可以有個簡單的方式可以關聯查詢。連到中央氣象局,抓取OpenData轉成物件,這個部分還蠻單純,也沒啥問題,只是資料稍微偏大,一次大約做個5分鐘內,資料量大約是7~8萬筆,就可以轉換完成,但,接著要放入資料庫時,就開始撞牆踩雷了~

雷雷相連

雷一:資料量大

其實這也還好,一次組合好 7~8萬筆的 Insert 的語法,再一次ExecuteNoneQuery,這樣應該就還好吧~但是這個再搭配第二個雷,就是噩夢的開始~

雷二:資料有重複

資料量大,再遇到資料重複,那麼,我要

  1. 產生SQL與法之前,就先用LINQ在物件放入集合前,判斷是否有重複,將重複性的資料跳掉
  2. 一筆一筆新增,然後在新增前先下SQL與法查詢是否有資料

方式1.其實是不好的,一開始還好,但隨著筆數不斷的增加,他其實是跑黑圈來逐筆比對因此到後來越來越慢→失敗
方式2.也沒快到哪裡去,迴圈中不斷的Select...,Insert....資料庫來來回回多趟

於是小喵心想,那麼,如果我用另外一個暫時的資料表(Tmp),把Key拿掉,先讓資料不必管Key來將資料放入,接著,才透過 Insert ...Select ... Group By,將重複性的資料跳掉,放入真正想要存放的資料表,這樣,我只要迴圈產生8萬條Insert語法,再一次執行SQL,這樣應該會好些吧

(PS.由於小喵最近電腦壞掉,目前用一台舊的電腦,速度慢的狀況更是顯而易見~)

於是,小喵這樣做後,執行的時間,從原來的約80分鐘,減少到50分鐘左右,但,這樣依舊太久,無論哪個方式,至少一次的8萬筆回圈避不掉,所以小喵又開始再思考,還有什麼方式是可以一次大量匯入資料的?

Bulk Insert for MS SQL

Bulk Insert可以大量快速的轉入文字型的資料,於是小喵尋找相關的說明文件,找到了這一篇

https://msdn.microsoft.com/zh-tw/library/ms191184(v=sql.120).aspx

小喵思考,目前已經組出來的是物件集合List (Of XXXX),如果可以將物件集合轉為XML檔案,並且存起來那麼應該可以加快速度吧~

有了方向,就開始動手,首先,先將物件轉為XML(請參考:[筆記] Object 物件(集合) XML 互轉 公用程式)並存在檔案系統中,這部分還好,很快的,已經處理好這段。

接著,研究如何用Bulk Insert 來將資料轉移到資料庫,細看了一下目前找到的文章所提供的範例:

USE AdventureWorks2012;
GO
DELETE myTestFormatFiles;
GO
BULK INSERT myTestFormatFiles 
   FROM 'C:\myTestFormatFiles-c.Dat' 
   WITH (FORMATFILE = 'C:\myTestFormatFiles.Fmt');
GO
SELECT * FROM myTestFormatFiles;
GO

發現,問題在【C:\xxxxx】,如果要用SQL語法執行這一段,那麼,就必須把xml檔案,產生或複製到SQL Server所在的主機上,才能這樣玩。但,這樣似乎不太好。

於是,小喵又找了找是否有其他方式,可以透過元件來執行 Bulk Insert,小喵找到了以下這篇:

如何使用 XML 大量載入元件將 XML 匯入 SQL Server

這篇是使用VBScript來運作,使用的是【SQLXMLBulkLoad.SQLXMLBulkLoad】這樣的一個元件,因此,如果可以找到此元件,加入參考,那樣大約就可以搞定在App Server上,直接運作存檔、匯入,不必把xml檔案搞到SQL Server上執行。

接著,就來看這部分如何處理~

透過元件執行 Bulk Insert 處理 xml 檔案

首先,元件【SQLXMLBulkLoad.SQLXMLBulkLoad】必須安裝【SqlXml 4.0】,可以到以下的網址進行下載安裝:

SqlXml 4.0 Service Pack 1 (SP1)

加入參考

VBScript使用的方式是屬於Late Binding的方式,在Visual Studio中,小喵撰寫WCF,當然希望可以用Early Binding的方式,所以,首先要將元件加入參考,請選擇【COM】 元件,找到【Microsoft XML Bulkload for SQL Server 4.0 Type Library】

Imports NameSpace

接著,要Imports(VB.NET) / using (C#) 命名空間【SQLXMLBULKLOADLib】

Connection String

使用SqlXml有個地方要特別的注意,他的Connection String與我們熟知的SqlClient方式不同,他主要是用OLEDB的方式,建置的方法,可以參考以下的步驟:

  1. 開啟檔案總管,在硬碟的任意位置(例如:D:\Temp)→右鍵→新增→文字文件
  2. 檔名任意,附檔名改為【UDL】
  3. 雙擊剛新增的檔案
  4. 點選【提供者】→選擇【Microsoft OLE DB Provider for SQL Server】→下一步
  5. 分別輸入【伺服器名稱】、【登錄資訊】、【資料庫】→之後按下【測試連線】,如果沒問題,按下【確定】
  6. 用【記事本】開啟剛剛的UDL檔
  7. 最後一行,即為OLEBD的Connection String設定

Code

相關程式碼的部分,還算單純,大致如下:

Dim objBL As New SQLXMLBulkLoad4
Try
	Dim FilePath As String = "D:\Tmp"
	Dim XmlFileName As String = "Weather20160503.xml"
	Dim ConnStr2 As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=NorthWind;Data Source=.\SQLExpress"
	objBL.ConnectionString = ConnStr2
	objBL.ErrorLogFile = FilePath & "\error.log"
	objBL.Execute(FilePath & "\WeatherMapping.xml", FilePath & "\" & XmlFileName)

Catch ex As Exception
	Throw New Exception(ex.Message)
Finally
	objBL = Nothing

End Try

這裡面,需要有另外一個檔案(xml),用來定義XML檔與資料表的Table欄位的對照,這邊就不細講,詳細可以參考VBScript那一篇

結果

實際執行結果,效果十分驚人,原本需要超過1小時,或者 50幾分的動作,在20秒內,就全部完成了資料匯入SQL的部分。大致上的時間比較,如果下表表示:

方式 時間
LINQ to Object 判斷是否重複 > 60 分
SQL 逐筆 Insert, 比對是否重複 > 60 分
SQL 一次產生 8萬筆, Insert到無Key Table
再Insert ... Select ...Group 
約 50 分
BULK Insert到無KeyTable
再Insert ... Select ... Group  
約 0.4 分(20秒)

 

末記

大量的匯入資料,SQL提供了高效率的 BULK Insert 的方式,而本篇則是針對 Xml 的檔案格式,透過SqlXml提供的元件來進行,小喵特別筆記下來,也提供大家參考。

 

 

 

 

 

 

 


以下是簽名:


Microsoft MVP
Visual Studio and Development Technologies
(2005~2019/6)