Sqoop最佳實踐

一、什么是Sqoop

Sqoop是一個在結(jié)構(gòu)化數(shù)據(jù)和Hadoop之間進(jìn)行批量數(shù)據(jù)遷移的工具戳粒,結(jié)構(gòu)化數(shù)據(jù)可以是Mysql昼接、Oracle等RDBMS莱衩。Sqoop底層用MapReduce程序?qū)崿F(xiàn)抽取、轉(zhuǎn)換趋艘、加載疲恢,MapReduce天生的特性保證了并行化和高容錯率,而且相比Kettle等傳統(tǒng)ETL工具瓷胧,任務(wù)跑在Hadoop集群上显拳,減少了ETL服務(wù)器資源的使用情況。在特定場景下搓萧,抽取過程會有很大的性能提升杂数。

? ? 如果要用Sqoop宛畦,必須正確安裝并配置Hadoop,因依賴于本地的hadoop環(huán)境啟動MR程序揍移;mysql次和、oracle等數(shù)據(jù)庫的JDBC驅(qū)動也要放到Sqoop的lib目錄下。本文針對的是Sqoop1那伐,不涉及到Sqoop2踏施,兩者有大區(qū)別,感興趣的讀者可以看下官網(wǎng)說明罕邀。

二畅形、import

????import是數(shù)據(jù)從RDBMS導(dǎo)入到Hadoop的工具

????2.1、split

????Sqoop并行化是啟多個map task實現(xiàn)的燃少,-m(或--num-mappers)參數(shù)指定map task數(shù)束亏,默認(rèn)是四個。并行度不是設(shè)置的越大越好阵具,map task的啟動和銷毀都會消耗資源碍遍,而且過多的數(shù)據(jù)庫連接對數(shù)據(jù)庫本身也會造成壓力。在并行操作里阳液,首先要解決輸入數(shù)據(jù)是以什么方式負(fù)債均衡到多個map的怕敬,即怎么保證每個map處理的數(shù)據(jù)量大致相同且數(shù)據(jù)不重復(fù)。--split-by指定了split column帘皿,在執(zhí)行并行操作時(多個map task)东跪,Sqoop需要知道以什么列split數(shù)據(jù),其思想是:

????1鹰溜、先查出split column的最小值和最大值

????2虽填、然后根據(jù)map task數(shù)對(max-min)之間的數(shù)據(jù)進(jìn)行均勻的范圍切分

例如id作為split column,其最小值是0、最大值1000曹动,如果設(shè)置4個map數(shù)斋日,每個map task執(zhí)行的查詢語句類似于:SELECT * FROM sometable WHERE id >= lo AND id < hi,每個task里(lo,hi)的值分別是 (0, 250), (250, 500), (500, 750), and (750, 1001)墓陈。

????Sqoop不能在多列字段上進(jìn)行拆分恶守,如果沒有索引或者有組合鍵,必須顯示設(shè)置splitting column贡必;默認(rèn)的主鍵作為split column兔港,如果表里沒有主鍵或者沒有指定--split-by,就要設(shè)置num-mappers 1或者--autoreset-to-one-mapper仔拟,這樣就只會啟動一個task衫樊。

? ? 從上面的分析過程可以看到Sqoop以理想化方式根據(jù)split column將數(shù)據(jù)切分成多個范圍,如果split鍵的值不是均勻分布利花,每個任務(wù)分配的數(shù)據(jù)量可能相差很大橡伞、導(dǎo)致數(shù)據(jù)傾斜盒揉。

? ? 2.2、參數(shù)

--driver:指定JDBC驅(qū)動兑徘,默認(rèn)Mysql

--table:指定查詢的表

--columns:指定從源數(shù)據(jù)庫導(dǎo)入的列刚盈。當(dāng)沒有設(shè)置--table參數(shù),就默認(rèn)查詢表中所有字段挂脑,實現(xiàn)方式是在數(shù)據(jù)庫執(zhí)行一個查詢語句藕漱,就可得到每個字段及其對應(yīng)的類型.

--where:查詢條件.如果設(shè)置了table參數(shù),就以table崭闲、columns肋联、where三個參數(shù)拼接成的SQL查詢數(shù)據(jù)

--query:自定義查詢SQL,語句要有$CONDITIONS關(guān)鍵字刁俭,作用是動態(tài)替換橄仍,當(dāng)獲取默認(rèn)boundary query時,$CONDITIONS會替換成(1=1)牍戚;獲取查詢的數(shù)據(jù)列和其對應(yīng)的字段類型時$CONDITIONS會替換成(1=0)侮繁。table和query不能同時設(shè)置

--boundary-query:指定split的sql,如果沒有設(shè)置如孝,且有--table參數(shù)宪哩,生成的split sql是根據(jù)table、where條件拼出來的第晰。

如果設(shè)置了--query參數(shù)锁孟,split sql是基于query sql的子查詢:

?需要特別注意的是,有的數(shù)據(jù)庫對子查詢沒有進(jìn)行優(yōu)化(如Mysql)茁瘦,查詢性能會很低品抽,這就要自定義boundary-query,提高查詢效率甜熔。

? ? 2.3桑包、HDFS

數(shù)據(jù)直接導(dǎo)入到HDFS,按行讀取并寫入到HDFS文件纺非,源表里的每一行數(shù)據(jù)在HDFS里作為單獨(dú)記錄,記錄可以是文本格式(每行一個記錄)或Avro赘方、SequenceFile二進(jìn)制格式烧颖。導(dǎo)入過程可以并行,因此可能生成多文件窄陡。

--append:生成的文件追加到目標(biāo)目錄里

--delete-target-dir:如果目標(biāo)目錄已經(jīng)存在炕淮,會先把目錄刪掉,類似overwrite

? 執(zhí)行上面的命令后跳夭,可以看到詳細(xì)的日志:輸入數(shù)據(jù)是怎么split的涂圆、mapreduce執(zhí)行進(jìn)度们镜、mapreduce的URL等

????2.4、Hive

--hive-import參數(shù)指定數(shù)據(jù)導(dǎo)入到hive表:

--target-dir:需要指定該參數(shù)润歉,數(shù)據(jù)首先寫入到該目錄模狭,過程和直接導(dǎo)入HDFS是一樣

--hive-drop-import-delims:刪除string字段內(nèi)的特殊字符,如果Hive使用這些字符作為分隔符踩衩,hive的字段會解析錯誤嚼鹉、出現(xiàn)錯位的情況。它的內(nèi)部是用正則表達(dá)式替換的方式把\n, \r, \01替換成""

--null-string/--null-non-string:指定空字段的值驱富。Sqoop默認(rèn)空數(shù)據(jù)存的是“NULL"字符串锚赤,但hive把空解析成\N,因此當(dāng)文件存儲的空是默認(rèn)的"NULL"字符串褐鸥,hive就不能正常讀取文件中的空值了

數(shù)據(jù)import到hive表的過程:完成源數(shù)據(jù)寫入到hdfs后线脚,就執(zhí)行LOAD DATA INPATH命令把target-dir里的文件LOAD到hive表:

? ??2.5、Hbase

? --hbase-table指定數(shù)據(jù)直接導(dǎo)入到Hbase表而不是HDFS叫榕,對于每個輸入行都會轉(zhuǎn)換成HBase的put操作浑侥,每行的key取自輸入的列,值轉(zhuǎn)換成string翠霍、UTF-8格式存儲在單元格里锭吨;所有的列都在同一列簇下,不能指定多個個列簇寒匙。

然后通過Hbase shell查看表數(shù)據(jù)量零如、數(shù)據(jù),

參數(shù)詳細(xì)說明:

--hbase-create-table:當(dāng)HTable不存在或列簇不存在锄弱,Sqoop根據(jù)HBase的默認(rèn)配置自動新建表考蕾;如果沒有指定該參數(shù),就會報異常

--hbase-row-key:指定row key使用的列会宪。默認(rèn)是split-by作為row key肖卧,如果沒有指定,會把源表的主鍵作為row key;如果row key是多個列組成的掸鹅,多個列必須用逗號隔開塞帐,生成的row key是由用下劃線隔開(`ID`_`RUN_ID`)的字段組合

--column-family:指定列簇名,所有的輸出列都在同一列簇下? ?

三巍沙、export

? ?export是HDFS里的文件導(dǎo)出到RDBMS的工具葵姥,不能從hive、hbase導(dǎo)出數(shù)據(jù)句携,且HDFS文件只能是文本格式榔幸。如果要把hive表數(shù)據(jù)導(dǎo)出到RDBMS,可以先把hive表通過查詢寫入到一個臨時表,臨時用文本格式削咆,然后再從該臨時表目錄里export數(shù)據(jù)牍疏。

? ?3.1、task數(shù)

Sqoop從HDFS目錄里讀取文件拨齐,所以啟動的map task數(shù)依賴于-m參數(shù)鳞陨、文件大小、文件數(shù)量奏黑、塊大小等炊邦,可以參考這篇文章

? ?3.2、插入/更新

? ?默認(rèn)情況下Sqoop在數(shù)據(jù)導(dǎo)出到RDBMS時熟史,每行記錄都轉(zhuǎn)換成數(shù)據(jù)庫的INSERT語句馁害,但也支持插入/更新模式,即根據(jù)一定規(guī)則判斷蹂匹,如果是新記錄用INSERT語句碘菜,否則就UPDATE,設(shè)置--update-mode allowinsert參數(shù)啟用該功能限寞,插入/更新操作依賴于目標(biāo)數(shù)據(jù)庫忍啸。

????對于Mysql,使用INSERT INTO … ON DUPLICATE KEY UPDATE語法履植,用戶不能指定列來判斷是插入還是更新计雌,而是依賴于表的唯一約束,Mysql在插入數(shù)據(jù)時玫霎,如果是因唯一約束引起的錯誤凿滤,就更新數(shù)據(jù)行。Sqoop會忽略--update-key參數(shù)庶近,但要至少指定一個有效列翁脆,才能啟用更新模式

? ??3.3、參數(shù)

? ?--columns:指定插入目標(biāo)數(shù)據(jù)庫的字段鼻种,sqoop直接讀取hdfs文件并把記錄解析成多個字段反番,此時解析后的記錄是沒有字段名的,是通過位置和columns列表對應(yīng)的叉钥,數(shù)據(jù)庫插入的sql類似于:insert into _table (c1,c2...) value(v1,v2...)

?--export-dir:指定HDFS輸入文件的目錄

?--input-fields-terminated-by:字段之間分隔符

?--input-lines-terminated-by:行分隔符

四罢缸、問題及優(yōu)化

????4.1、Hive不支持的數(shù)據(jù)類型

?關(guān)系型數(shù)據(jù)庫的字段類型和hive的字段類型還是有差別的投队,所以Sqoop有一個映射關(guān)系枫疆,把RDBMS中的類型映射到Hive類型。在create hive表時蛾洛,會根據(jù)RDBMS類型和hive類型進(jìn)行映射、進(jìn)而設(shè)置hive表字段類型,如果沒有匹配到轧膘,就會報異常钞螟,如VARBINARY:

????其解決方案有三種:

????????1、在--query參數(shù)內(nèi)顯示對字段進(jìn)行轉(zhuǎn)換谎碍,如VARBINARY轉(zhuǎn)換成VARCHAR鳞滨,而Sqoop會默認(rèn)的把VARCHAR轉(zhuǎn)換成Hive的STRING.

????????2、增加--map-column-hive參數(shù)蟆淀,顯示把字段映射到Hive指定的類型

? ? ? ? 3拯啦、修改HiveTypes類,使Sqoop支持對特定類型(如:VARBINARY)的映射熔任,這種方案相對以上兩種可以一勞永逸褒链,但要重編譯sqoop源碼

類型映射邏輯如下:

4.2、Java不支持的類型

? ? Sqoop創(chuàng)建ORM對象疑苔,數(shù)據(jù)庫中的字段映射到Java屬性甫匹,用于讀取數(shù)據(jù)庫ResultSet對象并解析字段,需要把數(shù)據(jù)庫的類型映射到j(luò)ava類型惦费,如果沒有映射到兵迅,就會報錯。解決方案也有三種:

????????1薪贫、在--query參數(shù)內(nèi)顯示對字段進(jìn)行轉(zhuǎn)換

????????2恍箭、設(shè)置--map-column-java參數(shù)

? ? ? ? 3、修改ConnManager類

映射邏輯代碼:

4.3瞧省、特殊字符

?當(dāng)\t特殊字符導(dǎo)入到hive后扯夭,hive字段可能解析出錯。解決方法是修改FieldFormatter類臀突,使Sqoop可以刪除或替換掉字段數(shù)據(jù)中包含\t的特殊字符:

4.4勉抓、字段錯位

? ? 使用--query和--columns參數(shù)時,如果columns設(shè)置的列順序和query列順序不同候学,會有個疑惑是import后的字段和實際字段的值不一樣藕筋,這是因為從數(shù)據(jù)庫查詢的ResultSet對象序列化到實體對象時,column的值是根據(jù)索引取的梳码。

例如readInteger的代碼:

如果要改為columns的字段值是根據(jù)字段名取而不是根據(jù)索引位置取隐圾,可以更改一下幾個地方的代碼:ClassWriter、JdbcWritableBridge

本文首發(fā)于公眾號:data之道

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末掰茶,一起剝皮案震驚了整個濱河市暇藏,隨后出現(xiàn)的幾起案子,更是在濱河造成了極大的恐慌濒蒋,老刑警劉巖盐碱,帶你破解...
    沈念sama閱讀 211,743評論 6 492
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件把兔,死亡現(xiàn)場離奇詭異,居然都是意外死亡瓮顽,警方通過查閱死者的電腦和手機(jī)县好,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,296評論 3 385
  • 文/潘曉璐 我一進(jìn)店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來暖混,“玉大人缕贡,你說我怎么就攤上這事〖鸩ィ” “怎么了晾咪?”我有些...
    開封第一講書人閱讀 157,285評論 0 348
  • 文/不壞的土叔 我叫張陵,是天一觀的道長贮配。 經(jīng)常有香客問我谍倦,道長,這世上最難降的妖魔是什么牧嫉? 我笑而不...
    開封第一講書人閱讀 56,485評論 1 283
  • 正文 為了忘掉前任剂跟,我火速辦了婚禮,結(jié)果婚禮上酣藻,老公的妹妹穿的比我還像新娘曹洽。我一直安慰自己,他們只是感情好辽剧,可當(dāng)我...
    茶點(diǎn)故事閱讀 65,581評論 6 386
  • 文/花漫 我一把揭開白布送淆。 她就那樣靜靜地躺著,像睡著了一般怕轿。 火紅的嫁衣襯著肌膚如雪偷崩。 梳的紋絲不亂的頭發(fā)上,一...
    開封第一講書人閱讀 49,821評論 1 290
  • 那天撞羽,我揣著相機(jī)與錄音阐斜,去河邊找鬼。 笑死诀紊,一個胖子當(dāng)著我的面吹牛谒出,可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播邻奠,決...
    沈念sama閱讀 38,960評論 3 408
  • 文/蒼蘭香墨 我猛地睜開眼笤喳,長吁一口氣:“原來是場噩夢啊……” “哼!你這毒婦竟也來了碌宴?” 一聲冷哼從身側(cè)響起杀狡,我...
    開封第一講書人閱讀 37,719評論 0 266
  • 序言:老撾萬榮一對情侶失蹤,失蹤者是張志新(化名)和其女友劉穎贰镣,沒想到半個月后呜象,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體膳凝,經(jīng)...
    沈念sama閱讀 44,186評論 1 303
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,516評論 2 327
  • 正文 我和宋清朗相戀三年恭陡,在試婚紗的時候發(fā)現(xiàn)自己被綠了鸠项。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點(diǎn)故事閱讀 38,650評論 1 340
  • 序言:一個原本活蹦亂跳的男人離奇死亡子姜,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出楼入,到底是詐尸還是另有隱情哥捕,我是刑警寧澤,帶...
    沈念sama閱讀 34,329評論 4 330
  • 正文 年R本政府宣布嘉熊,位于F島的核電站遥赚,受9級特大地震影響,放射性物質(zhì)發(fā)生泄漏阐肤。R本人自食惡果不足惜凫佛,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,936評論 3 313
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望孕惜。 院中可真熱鬧愧薛,春花似錦、人聲如沸衫画。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,757評論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽削罩。三九已至瞄勾,卻和暖如春,著一層夾襖步出監(jiān)牢的瞬間弥激,已是汗流浹背进陡。 一陣腳步聲響...
    開封第一講書人閱讀 31,991評論 1 266
  • 我被黑心中介騙來泰國打工, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留微服,地道東北人趾疚。 一個月前我還...
    沈念sama閱讀 46,370評論 2 360
  • 正文 我出身青樓,卻偏偏與公主長得像职辨,于是被迫代替她去往敵國和親盗蟆。 傳聞我的和親對象是個殘疾皇子,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 43,527評論 2 349

推薦閱讀更多精彩內(nèi)容