數(shù)據倉庫測試環(huán)境數(shù)據搬運方法研究

摘 要：數(shù)據量大是數(shù)據倉庫的應用的特點之一。在企業(yè)級應用中，通常需要從生產環(huán)境中搬運部分真實數(shù)據以提高軟件的開發(fā)、測試及驗收工作的質量。文中針對利用SQL Server架構的企業(yè)級的數(shù)據倉庫，對常見數(shù)據導出導入方式進行了總體介紹和比對試驗，對不同數(shù)據量下的搬運效率進行了比對，給出了從生產環(huán)境向測試環(huán)境搬運真實數(shù)據的最佳實踐。

關鍵詞：數(shù)據倉庫；軟件測試；SQL Server

中圖分類號：TP311

隨著對數(shù)據價值重視的日益提高，很多企業(yè)、科研及政府機構都建立了自己的數(shù)據倉庫和架構在此之上的決策支持系統(tǒng)。根據傳統(tǒng)數(shù)據倉庫模型，業(yè)務數(shù)據在經過ETL過程后，被加載至數(shù)據倉庫中[1]。在企業(yè)級應用中，數(shù)據倉庫的大小可能會達到TB級甚至PB級。在針對數(shù)據倉庫及在此之上的各類應用的測試過程中，特別是在驗收測試階段，為了提高用戶的接受度及測試效果，通常需要拷貝全部或者部分實際生產數(shù)據用于測試。本文從企業(yè)級實際應用出發(fā)，對基于SQL Server開發(fā)的數(shù)據倉庫類應用，探尋最佳的測試數(shù)據搬運方法。

1 數(shù)據倉庫介紹

數(shù)據倉庫（Data Warehouse）是一個面向主題的（Subject Oriented）、集成的（Integrated）、相對穩(wěn)定的（Non-Volatile）、反映歷史變化（Time Variant）的數(shù)據集合，用于支持管理決策（Decision Making Support）[2]。它的這個特性決定了數(shù)據倉庫中將保留大量的歷史數(shù)據，并定期更新。由于數(shù)據倉庫同時會囊括公司中業(yè)務、財務、人員等不同系統(tǒng)的數(shù)據，在大型企業(yè)中，數(shù)據倉庫的大小一般會達到TB級別。出于對實際數(shù)據安全性的考慮，在對應軟件的開發(fā)及測試過程中，一般不可能直接獲取生產數(shù)據的全量備份，通常是根據不同應用，選取部分表的部分數(shù)據，并對敏感信息（例如客戶個人資料）進行脫敏后，再用于開發(fā)及測試。

2 SQL Server介紹

SQL Server是微軟公司一款典型的關系型數(shù)據庫管理系統(tǒng)，以其強大的功能，簡便的操作及可靠的安全性得到了很多用戶的認可，其SSIS、SSAS、SSRS，3大組件，及其同VS良好的兼容性，為包括數(shù)據倉庫在內的整套商業(yè)智能系統(tǒng)的開發(fā)提供了十分便捷的環(huán)境[3]。

利用SQL Server開發(fā)管理的商務智能系統(tǒng)中，數(shù)據倉庫的設計多為星形和雪花模型，事實表一般情況下為明細數(shù)據，數(shù)據條數(shù)多，比如電信、銀行等行業(yè)的數(shù)據會過億條，為了多維度分析數(shù)據，事實表的字段也會設計的非常多，所以單張表的數(shù)據大小可能超過百G。在數(shù)據建設完成后，會有大量基于數(shù)據倉庫的商業(yè)智能類應用的開發(fā)，在這些應用程序的開發(fā)和測試的過程中，為提高測試準確性和驗收的直觀性，不可避免的需要從生產環(huán)境中獲取真實數(shù)據來為開發(fā)測試工作提供便利。

3 幾種常用的數(shù)據搬運方式

在企業(yè)應用中，基于數(shù)據安全性的考慮，開發(fā)環(huán)境、測試環(huán)境和生產環(huán)境一般是利用防火墻嚴格隔絕的，當開發(fā)、測試工作中需要實際數(shù)據時，一般由運維人員將實際數(shù)據的拷貝脫敏后提供給開發(fā)測試人員。

SQL Server在2008及以后版本中，提供了以下幾種數(shù)據搬運的方式。

3.1 導出為平面文件

通常導出的格式為csv或者txt，這種方式的導出過程簡單，但導出文件較大，需要利用其他工具進行壓縮，導入過程需要針對字段類型進行逐一設置。

3.2 BCP命令行方式

BCP命令是SQL Server提供的一個快捷的數(shù)據導入導出工具。使用它不需要啟動任何圖形管理工具就能以高效的方式導入導出數(shù)據。但存在同導出為平面文件類似的問題。

3.3 直接生成帶數(shù)據的sql語句

自2008版本之后，生成腳本功能增加了“編寫數(shù)據腳本”的選項，可直接生成表內數(shù)據對應的insert語句，在新的環(huán)境執(zhí)行時，可以直接創(chuàng)建同結構的表，并將原表中的數(shù)據插入進去。

3.4 整庫備份

歷代SQL Server都具有相對便捷的數(shù)據庫備份還原功能，在SQL Server 2008之后提供了壓縮備份的選項，可減小數(shù)據庫備份文件的大小，提高數(shù)據搬運的效率。

3.5 利用中間庫間接處理

在源環(huán)境中創(chuàng)建一個中間庫，通過select into語句將部分數(shù)據導入到一個這個中間庫中，然后將此中間庫搬運到目標環(huán)境中并進行還原。

3.6 數(shù)據庫間直連

SQL Server的數(shù)據導入導出向導中，提供了多種鏈接方式，例如OLE DB等，這些方式可以實現(xiàn)不同數(shù)據庫之間的直接訪問，從而直接抽取、加載數(shù)據。

3.7 程序抽取

實現(xiàn)方式多樣，可用多種語言和接口實現(xiàn)，同時可以添加特殊處理邏輯（手工數(shù)據脫敏等），但需要源和目標庫之間可以直接連通。

4 實際結果比對

通常情況下，企業(yè)級應用中，生產環(huán)境同開發(fā)、測試環(huán)境之間是嚴格隔離的，所以第四部分中后兩種方法由于數(shù)據安全性問題而無法實現(xiàn)，本文以下主要對前五種方式進行測試比對。

表1是文本、BCP命令及帶數(shù)據腳本導出方式的測試比對的結果，記錄了萬、十萬和百萬級數(shù)據量的表進行導出文件大小，導出耗時，導入耗時。

表1 文本、BCP 命令、數(shù)據腳本方式

導出導入方式數(shù)據量（條）導出文件（M）導出耗時（秒）導入耗時（秒）

文本導出 20268 13 1 報錯

BCP命令 20268 13 1 報錯

帶數(shù)據腳本 20268 41 8 30

帶數(shù)據腳本 110521 138 29 223

帶數(shù)據腳本 1195257 1169 158 2191

從結果中可以看出，文本和BCP方式導入存在報錯。文本導入及BCP命令的方式即便在源表和目標表結構相同的情況下，操作仍十分繁瑣且極易報錯，導致導入工作無法順利完成。例如文本導入的方式默認的字符類型為50的DT_STR，需要手工逐個修改，BCP命令會報多種數(shù)據類錯誤，例如無效的數(shù)據格式等，以上兩種方式在數(shù)據質量較差，數(shù)據量較大時，基本無法實施。

對生成帶數(shù)據腳本的方式，優(yōu)點是可以直接在待加載庫中建立表結構、索引等對象，操作步驟簡單。缺點是腳本文件較大，如表中所示，十萬數(shù)據量的表導出文本超過百兆，已無法直接在SQL Server客戶端直接打開，需利用sqlcmd命令行的方式進行執(zhí)行，百萬數(shù)據量的表的導出腳本已超過1G，僅執(zhí)行時間超過半小時。

在數(shù)據庫較小時，整庫備份的方式方便簡潔，大小為124G的數(shù)據庫，壓縮備份時間約為7分6秒，還原耗時為3分50秒，但是當數(shù)據較大時，整庫備份無法靈活選擇部分表的部分數(shù)據，在實際應用中存在一定的局限性。同時由于備份了整個數(shù)據庫，可能會包含一些本次開發(fā)、測試工作未用到，但是十分敏感的數(shù)據，例如用戶的個人信息，在這種情況下，不得不再對這些信息進行額外的漂白處理，增加了不必要的工作量。

表2是采用中間庫的方式導出導入結果。表中記錄了在不同數(shù)量級下，各個步驟的資源消耗，包括將數(shù)據導出到中間庫的耗時，中間庫的大小，備份中間庫的耗時，壓縮備份后的中間庫的大小以及還原備份庫的耗時。

表2 中間庫間接處理方式

數(shù)據量（條） 導入中間庫（秒）中間庫（M）備份（秒）備份庫（M）還原（秒）

20268 0.77 34 0.11 1.10 0.06

110521 1.27 106 0.29 5.65 0.23

1195257 7.24 717 1.15 29.36 1.10

11296446 78.12 6467 11.61 411.03 9.22

從結果中可以看出，這種方式在同等數(shù)據量下（萬級，十萬級），整個過程耗時約是“帶數(shù)據腳本方式”的八十分之一和兩百分之一，效率非常高，并且對其他方式無法操作的百萬，千萬級數(shù)據也有良好的效率，耗時分別在十秒級和百秒級。

這種方法缺點是步驟多，需要先將表導入到中間庫，然后進行備份還原，最后重新導入到目標庫。優(yōu)點是對大數(shù)據量的表處理速度快，SQL語句簡單靈活，可批量加條件導出，例如可以根據設計好的時間戳字段導出一段時間的數(shù)據，是對數(shù)據量超過千萬的表的最佳導出、導入途徑。

5 結束語

綜上所述，對于從防火墻隔離下的生產環(huán)境中獲取較大數(shù)據庫中部分表的部分數(shù)據，利用中間庫間接處理的方式為最優(yōu)選擇，其他方式在處理效率、靈活性等方面均存有一定的弊端。

目前數(shù)據庫工具都給出了多種數(shù)據導出導入方式，需要針對不同的應用場景選擇不同的數(shù)據搬運方式，而不同方式的方便程度，不僅僅依賴于數(shù)據庫本身提供的技術手段，同時依賴于應用程序良好的架構設計和完善的管理。

參考文獻：

[1]胡侃.基于大型數(shù)據倉庫的數(shù)據采掘[J].軟件學報，1998（01）.

[2]W.H.Inmon Building the data warehouse[M].Wiley Publishing，Inc，2005.

[3]郭鄭州，陳軍紅.SQL Server 2008完全學習手冊[M].北京：清華大學出版社，2013.

[4]（美）布萊克.軟件測試基礎[M].北京：人民郵電出版社，2013.

[5]張媛媛.測試環(huán)境管理的研究及應用[D].北京：北京郵電大學，2010（03）.

[6]蔡立志.大數(shù)據來臨，軟件測試準備好了嗎[J].軟件產業(yè)與工程，2013.

[7]（美）William E.Perry.軟件測試的有效方法[M].清華大學出版社.2008

[8]趙剛.大數(shù)據技術與應用實踐指南[M].北京：電子工業(yè)出版社.2013

作者簡介：陳學亮（1982-），男，山東人，高級測試工程師，碩士，研究方向：系統(tǒng)測試、商務智能。

作者單位：中國人壽保險股份有限公司研發(fā)中心，北京 110000