基于Sqoop 的異構(gòu)環(huán)境數(shù)據(jù)遷移方法研究

◎王正迅

引言

基于傳統(tǒng)關(guān)系型數(shù)據(jù)庫的穩(wěn)定性，目前還有很多企業(yè)將數(shù)據(jù)存儲在關(guān)系型數(shù)據(jù)庫中，但是關(guān)系型數(shù)據(jù)庫的數(shù)據(jù)模型較簡單，不適合表達復雜的數(shù)據(jù)關(guān)系，在處理大量數(shù)據(jù)、半結(jié)構(gòu)化和非結(jié)構(gòu)化數(shù)據(jù)，以及系統(tǒng)容錯和系統(tǒng)擴展性方面受到了一定的限制，Hadoop 下的系列工具則有較大優(yōu)勢，早期由于工具的缺乏，Hadoop 集群與傳統(tǒng)數(shù)據(jù)庫之間的數(shù)據(jù)傳輸非常困難。基于這些方面的考慮，需要一個能在傳統(tǒng)關(guān)系型數(shù)據(jù)庫和Hadoop 之間進行數(shù)據(jù)遷移的工具，Sqoop 應(yīng)運而生，Apache 提供的Sqoop 工具，能實現(xiàn)自動化數(shù)據(jù)遷移，依托于數(shù)據(jù)庫相關(guān)的schema 描述信息，遷移的過程則使用MapReduce（后面都簡寫為MR）來進行。Sqoop 作為一個跨平臺抽取和輸出數(shù)據(jù)的工具，在關(guān)系型數(shù)據(jù)庫（MySQL、O-ralce 等）和大數(shù)據(jù)平臺（HDFS、Hive、HBase）之間常用。作為ETL 過程中重要的一環(huán)，加載作業(yè)的性能也是需要關(guān)注和優(yōu)化的。本文將主要闡明如何在異構(gòu)環(huán)境中使用Sqoop 方法進行數(shù)據(jù)遷移。

一、認識Sqoop

Sqoop 是一款用于在Hadoop 和關(guān)系型數(shù)據(jù)庫之間高效遷移大批量數(shù)據(jù)的開源工具，類似于其他ETL 工具，Sqoop 使用元數(shù)據(jù)模型來判斷數(shù)據(jù)類型，并在數(shù)據(jù)從數(shù)據(jù)源轉(zhuǎn)移到Hadoop 時確保傳輸安全的數(shù)據(jù)處理，專為大數(shù)據(jù)批量傳輸設(shè)計，能夠分割數(shù)據(jù)集并創(chuàng)建Maptask 任務(wù)來處理每個區(qū)塊。以RDBMS 和HDFS 之間數(shù)據(jù)傳輸為例，Sqoop 借助于MR 導入和導出數(shù)據(jù)，用戶可以輕松地以命令行模式從RDBMS 如MySQL 或 Oracle 中導入數(shù)據(jù)到 HDFS 中，通過 Hadoop 的MR 模型計算完之后，將結(jié)果導回RDBMS，Sqoop 能夠自動完成整個過程中的大部分，并提供容錯和并行化操作。

二、Sqoop 工作機制

Sqoop 本質(zhì)就是遷移數(shù)據(jù)，用戶在使用Sqoop 在異構(gòu)環(huán)境間遷移數(shù)據(jù)時，Sqoop Client 提供了CLI 和瀏覽器兩種方式提交請求，然后Sqoop Server 收到請求后，授權(quán)MR 執(zhí)行。這個過程它高度依賴Hadoop 并行導入數(shù)據(jù)，充分利用了MR 的并行特點，以批處理的方式加快數(shù)據(jù)的傳輸，同時也借助MR 實現(xiàn)了容錯。

1.從關(guān)系型數(shù)據(jù)庫導入數(shù)據(jù)到hadoop 上。

Sqoop 把關(guān)系型數(shù)據(jù)庫（以mysql 為例）的數(shù)據(jù)導人到HDFS 中，主要分為兩步：一是得到元數(shù)據(jù)（mysql 數(shù)據(jù)庫中的數(shù)據(jù)），二是提交Map。在這個過程中，sqoop 會通過jdbc來獲取需要的數(shù)據(jù)庫的元數(shù)據(jù)信息，例如：導入的表的列名，數(shù)據(jù)類型。這些數(shù)據(jù)庫的數(shù)據(jù)類型會被映射成為java 的數(shù)據(jù)類型，根據(jù)這些信息，sqoop 會生成一個與表名相同的類，用來完成序列化工作，最后使用Java 類進行反序列化，MR并行寫數(shù)據(jù)到Hadoop 中，從而保存表中的每一行記錄。在導入數(shù)據(jù)時，如果不想取出全部數(shù)據(jù)，可以通過類似于where 的語句進行限制。

圖1 Sqoop 數(shù)據(jù)導入機制

2.從Hadoop 上導出數(shù)據(jù)到關(guān)系型數(shù)據(jù)庫。

Sqoop 的導出通常是將 HDFS、HBase、Hive 中的數(shù)據(jù)導出到關(guān)系型數(shù)據(jù)庫中，關(guān)系型數(shù)據(jù)庫中的表必須提前創(chuàng)建好。底層方面，同樣是通過jdbc 讀取HDFS/HBase/Hive 數(shù)據(jù)，生成Java 類（這個類主要作用是解析文本中的數(shù)據(jù)），用于序列化，最后export 程序啟動，通過Java 類反序列化，同時啟動多個Map 將相應(yīng)值插入表中。

圖2 Sqoop 數(shù)據(jù)導出機制

三、Sqoop 數(shù)據(jù)遷移方法介紹

1.Sqoop 數(shù)據(jù)導入。

數(shù)據(jù)導入分全量導入和增量導入。

（1）全量導入數(shù)據(jù)。全量數(shù)據(jù)導入就是一次性將所有需要導入的數(shù)據(jù)，從關(guān)系型數(shù)據(jù)庫一次性地導入到HDFS 中（也可以是HBase、Hive 等）。全量導入形式使用場景為一次性離線分析場景。用sqoop import 命令，具體如下：

（2）導入數(shù)據(jù)庫中的部分數(shù)據(jù)。導入部分數(shù)據(jù)可以在行與列的選取上添加參數(shù)完成，列選取上添加一個--columns參數(shù)，指定數(shù)據(jù)庫中需要導入的列，如添加--columns id，name，age，sex；行選取上添加 --where 參數(shù)，增加 where 條件篩選滿足條件的行，如--where "age >= 20" ；還可以使--query 參數(shù)查詢篩選需要導入的數(shù)據(jù)，同時實現(xiàn)行、列的選取，如 --query"select id，name，age，sex from t_user_info where age>=20 and$CONDITIONS"。

（3）增量導入數(shù)據(jù)。在實際生產(chǎn)環(huán)境中，系統(tǒng)可能會定期從與業(yè)務(wù)相關(guān)的關(guān)系型數(shù)據(jù)庫向Hadoop 導入數(shù)據(jù)，導入數(shù)據(jù)倉庫后進行后續(xù)離線分析。數(shù)據(jù)量比較大，有的前期數(shù)據(jù)已經(jīng)被用于項目分析了，我們此時不可能再將所有數(shù)據(jù)重新導一遍，此時我們就需要增量數(shù)據(jù)導入這一模式了。增量數(shù)據(jù)導入分兩種，一是基于遞增列的增量數(shù)據(jù)導入（Append方式）。二是基于時間列的增量數(shù)據(jù)導入（LastModified 方式）。在--incremental 參數(shù)后通過指定Append 方式或LastModified 方式。

2.Sqoop 數(shù)據(jù)導出。

export 是HDFS 里的文件導出到關(guān)系型數(shù)據(jù)庫的工具，不能直接從hive、hbase 導出數(shù)據(jù)。如果要把hive 表數(shù)據(jù)導出到關(guān)系型數(shù)據(jù)庫，需先把hive 表通過查詢寫入到一個暫存表，臨時用文本格式，然后再從該暫存表目錄里導出數(shù)據(jù)。

執(zhí)行數(shù)據(jù)導出前，數(shù)據(jù)庫中必須已經(jīng)存在要導入的目標表，默認操作是從將文件中的數(shù)據(jù)使用INSERT 語句插入到表中，也可選擇更新模式（Sqoop 將生成UPDATE 替換數(shù)據(jù)庫中現(xiàn)有記錄的語句）或調(diào)用模式（Sqoop 將為每條記錄創(chuàng)建一個存儲過程調(diào)用）。

默認情況下，sqoop export 將每行輸入記錄轉(zhuǎn)換成一條INSERT 語句，添加到目標數(shù)據(jù)庫表中。如果數(shù)據(jù)庫中的表具有約束條件（例如，其值必須唯一的主鍵列）并且已有數(shù)據(jù)存在，則必須注意避免插入違反這些約束條件的記錄。如果INSERT 語句失敗，導出過程將失敗。此模式主要用于將記錄導出到可以接收這些結(jié)果的空表中。通常用于全表數(shù)據(jù)導出。使用如下命令可完成：

更新模式導出，僅僅更新已存在的數(shù)據(jù)記錄，不會插入新記錄，該模式用于更新源表與目標表中數(shù)據(jù)的不一致，即在不一致時，將源表中的數(shù)據(jù)遷移至目標表（如MySQL、Oracle 等的目標表中），這種不一致是指一條記錄中存在的不一致，比如HDFS 表和MySQL 中都有一個id=1 的記錄，但是其中一個字段的取值不同，則該模式會將這種忽視差異。對于“你有我無”的記錄則不做處理，通過指定update-key 并在—update-mode 參數(shù)后指定是updateonly 模式。調(diào)用模式導出，會更新已存在的數(shù)據(jù)記錄，同時插入一個新記錄，實質(zhì)上是插入一個update+insert 的操作，同樣是通過指定update-key 并在—update-mode 參數(shù)后指定是allowinsert模式。

四、Sqoop 常見問題及解決辦法

Sqoop 作為一種重要的數(shù)據(jù)遷移工具，在使用過程中需要遵守數(shù)據(jù)庫約束、數(shù)據(jù)庫連接機制，考慮空值、并行度、分隔符等原因?qū)е碌膫鬏斄袛?shù)和表的列數(shù)不一致等問題。

1.空值問題。

空值問題常見于Hive 與MySQL 數(shù)據(jù)遷移過程中發(fā)生。Hive 中的 Null 在底層是以“N”來存儲，而 MySQL 中的 Null在底層就是Null，這就導致了兩邊進行數(shù)據(jù)遷移時存儲不一致問題，Sqoop 要求在數(shù)據(jù)遷移的時候嚴格保證兩端的數(shù)據(jù)格式、數(shù)據(jù)類型一致，否則會帶來異常。

為了保證數(shù)據(jù)兩端的一致性，數(shù)據(jù)遷移的過程中遇到null-string，null-non-string 數(shù)據(jù)都轉(zhuǎn)化成指定的類型，通常指定成"N"。依賴自身參數(shù)在導入數(shù)據(jù)時采用--null-string“\N”和--null-non-string“\N”，在導出數(shù)據(jù)時采用--input-null-string“\N”和 --input-null-non-string“\N”兩個參數(shù)，在使用這些參數(shù)過程中，需要正確地將值N 轉(zhuǎn)義到\N。

2.數(shù)據(jù)一致性問題。

（1）任務(wù)失敗導致數(shù)據(jù)不一致。由于Sqoop 將導出過程分解為多個事務(wù)，因此失敗的導出作業(yè)可能會導致將部分數(shù)據(jù)提交到數(shù)據(jù)庫。在某些情況下，這可能會導致后續(xù)作業(yè)由于插入沖突而失敗，或者在其他情況下導致重復數(shù)據(jù)。如這樣一個場景：export 到 Mysql 時，使用 6個 Map 任務(wù)，過程中有3個任務(wù)失敗，那此時MySQL 中存儲了另外三個Map任務(wù)導入的數(shù)據(jù)，此時會生成一個不完整的報表數(shù)據(jù)。繼續(xù)調(diào)試問題并最終將全部數(shù)據(jù)正確的導入MySQL，會再次生成一個報表數(shù)據(jù)，而這個報表數(shù)據(jù)與之前的報表數(shù)據(jù)是不一致，這在生產(chǎn)環(huán)境是不允許的。這種情況下，可以通過--staging-table 參數(shù)指定一個staging 表來克服這個問題，指定的這個staging 表在單個事務(wù)中，暫存數(shù)據(jù)，等到事務(wù)完全處理完畢再移動到目標表。為了使用暫存功能，必須在運行導出作業(yè)之前創(chuàng)建暫存表，該表必須在結(jié)構(gòu)上與目標表相同，這個表應(yīng)該在導出作業(yè)運行之前為空，所以需要--clear-staging-table 這個參數(shù)配合起來使用。

（2）分隔符問題導致數(shù)據(jù)不一致。Sqoop 默認字段與字段之間是用“，”分隔開，Hive 默認的列分隔符是 ^A（001），行與行之間的分隔符是“ ”，當然，在創(chuàng)建這些表（包括MySQL表）的時候也可以自己指定分隔符。在數(shù)據(jù)遷移過程中，由于分隔符的不一致會導致數(shù)據(jù)遷移失敗，由于導入的數(shù)據(jù)中有' '，hive 會認為一行已經(jīng)結(jié)束，后面的數(shù)據(jù)被分割成下一行，也會導致數(shù)據(jù)不一致。這時可以使用--lines-terminated-by和--fields-terminated-by 這兩個參數(shù)來自定義行分隔符和列分隔符進行解決。但是hive 只支持' '作為行分隔符，所以在關(guān)系型數(shù)據(jù)庫與Hive 進行數(shù)據(jù)遷移時，還需要加上--hive-delims-replacement或 --hive-drop-import-delims 這兩個參數(shù)把導入數(shù)據(jù)中包含的hive 默認的分隔符去掉（這兩個參數(shù)項不能同時使用）。

--hive-delims-replacement ：是將分隔符中的/0x01 和 /r/n 替換成

--hive-drop-import-delims：將分隔符中的/0x01 和/r/n去掉

3.數(shù)據(jù)傾斜問題。

在生產(chǎn)環(huán)境中，由于數(shù)據(jù)量巨大，數(shù)據(jù)結(jié)構(gòu)復雜，Sqoop導入數(shù)據(jù)報內(nèi)存溢出以及抽數(shù)時間過長，日志顯示有個別的reduce 執(zhí)行時間過長，卡在99%那個位置，例如有25個Map 中有24個Map 是在20 秒內(nèi)執(zhí)行完成，只有1個Map用了6 分多鐘，這種Map 分布不均勻，就是數(shù)據(jù)傾斜現(xiàn)象。導致數(shù)據(jù)傾斜的原因有可能是數(shù)據(jù)本身就不均勻，或是分隔符問題，或是數(shù)據(jù)類型不一致等。這時需要設(shè)置--split-by、--num-Mappers 和--split-Mappers 這三個參數(shù)。

在import 時，指定--split-by 參數(shù)，Sqoop 根據(jù)不同的split-by 參數(shù)值來進行切分，然后將切分出來的區(qū)域分配到不同Map 中。每個Map 中再處理數(shù)據(jù)庫中獲取的一行一行的值，寫入到HDFS 中。split-by 根據(jù)不同的參數(shù)類型有不同的切分方法，最好使用較簡單的int 型。

通過設(shè)置Map 的個數(shù)來提高吞吐量，-num-Mappers后面設(shè)置的Maptask 數(shù)目大于1 的話，那么-split-by 后面必須跟字段，因為-num-Mappers 后面要是1 的話，那么-split-Mappers 后面跟不跟字段都沒有意義，因為，他只會啟動一個Maptask 進行數(shù)據(jù)處理。一般來說數(shù)據(jù)量與Map 的數(shù)量是相關(guān)的，一般建議在500w 以下使4個Map 即可，如果數(shù)據(jù)量在500w 以上可以使用8個Map，Map 數(shù)量太多會對數(shù)據(jù)庫增加運壓力，造成其他場景使？性能降低。在使用并行度的時候需要了解主鍵的分布情況是否是有必要的。

五、結(jié)語

隨著大數(shù)據(jù)、云計算、物聯(lián)網(wǎng)的不斷發(fā)展，信息系統(tǒng)產(chǎn)生的數(shù)據(jù)規(guī)模與日俱增，以Hadoop 平臺為代表的海量數(shù)據(jù)處理平臺通過對海量數(shù)據(jù)進行并行處理成為一種有效的解決方案，基于Sqoop 實現(xiàn)的在關(guān)系型數(shù)據(jù)庫與Hadoop 平臺之間進行數(shù)據(jù)遷移，它可以高效、可靠地完成數(shù)據(jù)傳輸任務(wù)，是數(shù)據(jù)分析處理及挖掘前的重要一環(huán)。本文從Sqoop 工作機制、遷移方法介紹、Sqoop 常見問題及解決辦法等方面進行分析，解決了Sqoop 使用過程中的簡單問題，在實際使用過程中，還需要結(jié)合項目實際需求對Sqoop 做更進一步的優(yōu)化。