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

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

中圖分類號：TP311

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

1 數(shù)據(jù)倉庫介紹

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

2 SQL Server介紹

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

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

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

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

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

3.1 導(dǎo)出為平面文件

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

3.2 BCP命令行方式

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

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

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

3.4 整庫備份

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

3.5 利用中間庫間接處理

在源環(huán)境中創(chuàng)建一個(gè)中間庫，通過select into語句將部分?jǐn)?shù)據(jù)導(dǎo)入到一個(gè)這個(gè)中間庫中，然后將此中間庫搬運(yùn)到目標(biāo)環(huán)境中并進(jìn)行還原。

3.6 數(shù)據(jù)庫間直連

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

3.7 程序抽取

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

4 實(shí)際結(jié)果比對

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

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

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

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

文本導(dǎo)出 20268 13 1 報(bào)錯(cuò)

BCP命令 20268 13 1 報(bào)錯(cuò)

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

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

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

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

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

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

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

表2 中間庫間接處理方式

數(shù)據(jù)量（條） 導(dǎo)入中間庫（秒）中間庫（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

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

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

5 結(jié)束語

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

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

參考文獻(xiàn)：

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

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

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

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

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

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

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

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

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

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