sqoop使用文檔

1、1 簡介Sqoop是一個用來將Hadoop和關(guān)系型數(shù)據(jù)庫中的數(shù)據(jù)相互轉(zhuǎn)移的工具，可以將一個關(guān)系型數(shù)據(jù)庫（例如 ： MySQL ,Oracle ,Postgres等）中的數(shù)據(jù)導(dǎo)進到Hadoop的HDFS中，也可以將HDFS的數(shù)據(jù)導(dǎo)進到關(guān)系型數(shù)據(jù)庫中。Sqoop已在CDH集群中配置完成。集群中使用的是sqoop2.關(guān)于sqoop1和sqoop2的區(qū)別如下：1.1 Sqoop2和sqoop1的區(qū)別Sqoop1架構(gòu)圖：Sqoop2架構(gòu)圖：1.2 使用#指定列$ sqoop import -connect jdbc:mysql:/ -table EMPLOYEES -columns employee_i

2、d,first_name,last_name,job_title#使用8個線程$ sqoop import -connect jdbc:mysql:/ -table EMPLOYEES -m 8#快速模式$ sqoop import -connect jdbc:mysql:/ -table EMPLOYEES -direct#使用sequencefile作為存儲方式$ sqoop import -connect jdbc:mysql:/ -table EMPLOYEES -class-name com.foocorp.Employee -as-sequencefile#分隔符$ sqoop i

3、mport -connect jdbc:mysql:/ -table EMPLOYEES -fields-terminated-by t -lines-terminated-by n -optionally-enclosed-by #導(dǎo)入到hive$ sqoop import -connect jdbc:mysql:/ -table EMPLOYEES -hive-import#條件過濾$ sqoop import -connect jdbc:mysql:/ -table EMPLOYEES -where start_date 2010-01-01#用dept_id作為分個字段$ sqoop

4、import -connect jdbc:mysql:/ -table EMPLOYEES -split-by dept_id#追加導(dǎo)入$ sqoop import -connect jdbc:mysql:/ -table sometable -where id 100000 -target-dir /incremental_dataset append-華麗的分割線-將mysql中的數(shù)據(jù)導(dǎo)入hdfs文件系統(tǒng)中sqoop import -connect jdbc:mysql:/:3306/mysql -username root -password root -table

5、 help_topic -fields-terminated-by t -m 2 -target-dir /user/mysql/topic2/$today -default-character-set=utf-8 將oracle中的數(shù)據(jù)導(dǎo)入hdfs文件系統(tǒng)中sqoop import -connect jdbc:oracle:thin:13:1521:orcl -username oracle -password oracle -table recmail -columns nid,ssubject,srecusers,ssendusers,scc,scontent -f

6、ields-terminated-by t -m 1 -target-dir /user/oracle/email/$today使用query關(guān)聯(lián)查詢sqoop import -connect jdbc:mysql:/:3306/test -username root -password root -query SELECT u.id,,u.age,s.score FROM user u join score s on u.id=s.uid WHERE $CONDITIONS -fields-terminated-by t -m 1 -target-dir /

7、user/mysql/topic3/$today生成對應(yīng)的表的java代碼sqoop codegen -connect jdbc:mysql:/:3306/mysql -username root -password root -table help_topicsqoop mysql hive（int 類型為null）sqoop import -connect jdbc:mysql:/:3306/test -username root -password root -table user -m 1 -hive-import -create-hive-

8、table -hive-table user_mysql -target-dir /user/hive/warehouse/user -enclosed-by -fields-terminated-by , -escaped-by sqoop oracle hivesqoop import -connect jdbc:oracle:thin:13:1521:orcl -username oracle -password oracle -table RECMAIL -columns NID,SSUBJECT,SRECUSERS,SSENDUSERS,SCC,SCONTENT

9、 -m 1 -hive-import -create-hive-table -hive-table email_oracle2 -target-dir /user/hive/warehouse/email3 -enclosed-by -fields-terminated-by , -escaped-by ps:oracle中的表明和字段名必須大寫，否則會找不到。導(dǎo)入hive中不支持增量導(dǎo)入Sqoop mysql hbasesqoop import -connect jdbc:mysql:/:3306/test -username root -password root -

10、table user -m 1 -hbase-create-table -hbase-table user -column-family userinfo -hbase-row-key id append 表示增量導(dǎo)入如果hbase中已存在表和列族，則不需要使用-hbase-create-table命令sqoop import -connect jdbc:oracle:thin:13:1521:ORCL -username oracle -password oracle -table RECMAIL -columns NID,SRECUSERS,SSENDUSERS,SC

11、C,SBCC -m 1 -hbase-table email -column-family users -hbase-row-key NID appendsqoop import -connect jdbc:oracle:thin:11:1521:ORCL -username oracle -password oracle -table RECMAIL -columns NID,SSUBJECT,SCONTENT -m 1 -hbase-table email -column-family message -hbase-row-key NID -append2 附錄：Sq

12、oop中文手冊Sqoop在我們的實際工作中有一些應(yīng)用，為了對Sqoop的使用有個更全面的了解，于是我花了一些時間將Sqoop的使用整理成了一份中文手冊，我自認還是非常全面的，覆蓋到了Sqoop所有的方面。雖然對每一個用法，我都是盡可能地先經(jīng)過測試然后再得出結(jié)論，但可能難免會有謬誤，不過總的來說希望能對使用Sqoop的朋友有點幫助吧。概述本文檔主要對SQOOP的使用進行了說明，參考內(nèi)容主要來自于Cloudera SQOOP的官方文檔。為了用中文更清楚明白地描述各參數(shù)的使用含義，本文檔幾乎所有參數(shù)使用說明都經(jīng)過了我的實際驗證而得到。2.1 codegen將關(guān)系數(shù)據(jù)庫表映射為一個java文件、jav

13、a class類、以及相關(guān)的jar包，作用主要是兩方面：1、 將數(shù)據(jù)庫表映射為一個Java文件，在該Java文件中對應(yīng)有表的各個字段。2、 生成的Jar和class文件在metastore功能使用時會用到?；A(chǔ)語句：sqoop codegen connect jdbc:mysql:/localhost:3306/hive username root password 123456 table TBLS2參數(shù)說明bindir 指定生成的java文件、編譯成的class文件及將生成文件打包為JAR的JAR包文件輸出路徑class-name 設(shè)定生成的Java文件指定的名稱outdir 生成的jav

14、a文件存放路徑package-name包名，如nic，則會生成cn和cnnic兩級目錄，生成的文件（如java文件）就存放在cnnic目錄里input-null-non-string在生成的java文件中，可以將null字符串設(shè)為想要設(shè)定的值（比如空字符串）input-null-string同上，設(shè)定時，最好與上面的屬性一起設(shè)置，且設(shè)置同樣的值（比如空字符串等等）。map-column-java數(shù)據(jù)庫字段在生成的java文件中會映射為各種屬性，且默認的數(shù)據(jù)類型與數(shù)據(jù)庫類型保持對應(yīng)，比如數(shù)據(jù)庫中某字段的類型為bigint，則在Java文件中的數(shù)據(jù)類型為long型，通過這個屬性，可以改變數(shù)據(jù)庫字段

15、在java中映射的數(shù)據(jù)類型，格式如：map-column-java DB_ID=String,id=Integernull-non-string在生成的java文件中，比如TBL_ID=null?”null”:”，通過這個屬性設(shè)置可以將null字符串設(shè)置為其它值如ddd，TBL_ID=null?”ddd”:”null-string同上，使用的時候最好和上面的屬性一起用，且設(shè)置為相同的值table 對應(yīng)關(guān)系數(shù)據(jù)庫的表名，生成的java文件中的各屬性與該表的各字段一一對應(yīng)。2.2 create-hive-table生成與關(guān)系數(shù)據(jù)庫表的表結(jié)構(gòu)對應(yīng)的HIVE表基礎(chǔ)語句：sqoop create-hiv

16、e-table connect jdbc:mysql:/localhost:3306/hive -username root -password 123456 table TBLS hive-table h_tbls2參數(shù)說明hive-home Hive的安裝目錄，可以通過該參數(shù)覆蓋掉默認的hive目錄hive-overwrite覆蓋掉在hive表中已經(jīng)存在的數(shù)據(jù)create-hive-table默認是false,如果目標(biāo)表已經(jīng)存在了，那么創(chuàng)建任務(wù)會失敗hive-table后面接要創(chuàng)建的hive表table指定關(guān)系數(shù)據(jù)庫表名2.3 eval可以快速地使用SQL語句對關(guān)系數(shù)據(jù)庫進行操作，這可以使

17、得在使用import這種工具進行數(shù)據(jù)導(dǎo)入的時候，可以預(yù)先了解相關(guān)的SQL語句是否正確，并能將結(jié)果顯示在控制臺。查詢示例：sqoop eval connect jdbc:mysql:/localhost:3306/hive -username root -password 123456 -query “SELECT * FROM tbls LIMIT 10”數(shù)據(jù)插入示例：sqoop eval connect jdbc:mysql:/localhost:3306/hive -username root -password 123456 -e “INSERT INTO TBLS2VALUES(100

18、,1375170308,1,0,hadoop,0,1,guest,MANAGED_TABLE,abc,ddd)”-e、-query這兩個參數(shù)經(jīng)過測試，比如后面分別接查詢和插入SQL語句，皆可運行無誤，如上。5. export從hdfs中導(dǎo)數(shù)據(jù)到關(guān)系數(shù)據(jù)庫中sqoop export connect jdbc:mysql:/localhost:3306/hive username root password123456 table TBLS2 export-dir sqoop/test參數(shù)說明direct快速模式，利用了數(shù)據(jù)庫的導(dǎo)入工具，如mysql的mysqlimport，可以比jdbc連接的方

19、式更為高效的將數(shù)據(jù)導(dǎo)入到關(guān)系數(shù)據(jù)庫中。export-dir 存放數(shù)據(jù)的HDFS的源目錄-m,num-mappers 啟動N個map來并行導(dǎo)入數(shù)據(jù)，默認是4個，最好不要將數(shù)字設(shè)置為高于集群的最大Map數(shù)table 要導(dǎo)入到的關(guān)系數(shù)據(jù)庫表update-key 后面接條件列名，通過該參數(shù)，可以將關(guān)系數(shù)據(jù)庫中已經(jīng)存在的數(shù)據(jù)進行更新操作，類似于關(guān)系數(shù)據(jù)庫中的update操作update-mode 更新模式，有兩個值updateonly和默認的allowinsert，該參數(shù)只能是在關(guān)系數(shù)據(jù)表里不存在要導(dǎo)入的記錄時才能使用，比如要導(dǎo)入的hdfs中有一條id=1的記錄，如果在表里已經(jīng)有一條記錄id=2，那么更

20、新會失敗。input-null-string 可選參數(shù)，如果沒有指定，則字符串null將被使用input-null-non-string 可選參數(shù)，如果沒有指定，則字符串null將被使用staging-table 該參數(shù)是用來保證在數(shù)據(jù)導(dǎo)入關(guān)系數(shù)據(jù)庫表的過程中事務(wù)安全性的，因為在導(dǎo)入的過程中可能會有多個事務(wù)，那么一個事務(wù)失敗會影響到其它事務(wù)，比如導(dǎo)入的數(shù)據(jù)會出現(xiàn)錯誤或出現(xiàn)重復(fù)的記錄等等情況，那么通過該參數(shù)可以避免這種情況。創(chuàng)建一個與導(dǎo)入目標(biāo)表同樣的數(shù)據(jù)結(jié)構(gòu)，保留該表為空在運行數(shù)據(jù)導(dǎo)入前，所有事務(wù)會將結(jié)果先存放在該表中，然后最后由該表通過一次事務(wù)將結(jié)果寫入到目標(biāo)表中。clear-staging-

21、table如果該staging-table非空，則通過該參數(shù)可以在運行導(dǎo)入前清除staging-table里的數(shù)據(jù)。batch該模式用于執(zhí)行基本語句（暫時還不太清楚含義）2.4 import將數(shù)據(jù)庫表的數(shù)據(jù)導(dǎo)入到hive中，如果在hive中沒有對應(yīng)的表，則自動生成與數(shù)據(jù)庫表名相同的表。sqoop import connect jdbc:mysql:/localhost:3306/hive username root password123456 table user split-by id hive-importsplit-by指定數(shù)據(jù)庫表中的主鍵字段名，在這里為id。參數(shù)說明append將數(shù)

22、據(jù)追加到hdfs中已經(jīng)存在的dataset中。使用該參數(shù)，sqoop將把數(shù)據(jù)先導(dǎo)入到一個臨時目錄中，然后重新給文件命名到一個正式的目錄中，以避免和該目錄中已存在的文件重名。as-avrodatafile將數(shù)據(jù)導(dǎo)入到一個Avro數(shù)據(jù)文件中as-sequencefile將數(shù)據(jù)導(dǎo)入到一個sequence文件中as-textfile將數(shù)據(jù)導(dǎo)入到一個普通文本文件中，生成該文本文件后，可以在hive中通過sql語句查詢出結(jié)果。boundary-query 邊界查詢，也就是在導(dǎo)入前先通過SQL查詢得到一個結(jié)果集，然后導(dǎo)入的數(shù)據(jù)就是該結(jié)果集內(nèi)的數(shù)據(jù)，格式如：boundary-query select id,c

23、reationdate from person where id = 3，表示導(dǎo)入的數(shù)據(jù)為id=3的記錄，或者select min(), max() from ，注意查詢的字段中不能有數(shù)據(jù)類型為字符串的字段，否則會報錯：java.sql.SQLException: Invalid value forgetLong()目前問題原因還未知columns指定要導(dǎo)入的字段值，格式如：columns id,usernamedirect直接導(dǎo)入模式，使用的是關(guān)系數(shù)據(jù)庫自帶的導(dǎo)入導(dǎo)出工具。官網(wǎng)上是說這樣導(dǎo)入會更快direct-split-size在使用上面direct直接導(dǎo)入的基礎(chǔ)上，對導(dǎo)入的流按字節(jié)數(shù)分塊

24、，特別是使用直連模式從PostgreSQL導(dǎo)入數(shù)據(jù)的時候，可以將一個到達設(shè)定大小的文件分為幾個獨立的文件。inline-lob-limit設(shè)定大對象數(shù)據(jù)類型的最大值-m,num-mappers啟動N個map來并行導(dǎo)入數(shù)據(jù)，默認是4個，最好不要將數(shù)字設(shè)置為高于集群的節(jié)點數(shù)query，-e從查詢結(jié)果中導(dǎo)入數(shù)據(jù)，該參數(shù)使用時必須指定target-dir、hive-table，在查詢語句中一定要有where條件且在where條件中需要包含$CONDITIONS，示例：query select * from person where $CONDITIONS target-dir/user/hive/wa

25、rehouse/person hive-table personsplit-by表的列名，用來切分工作單元，一般后面跟主鍵IDtable 關(guān)系數(shù)據(jù)庫表名，數(shù)據(jù)從該表中獲取target-dir 指定hdfs路徑warehouse-dir 與target-dir不能同時使用，指定數(shù)據(jù)導(dǎo)入的存放目錄，適用于hdfs導(dǎo)入，不適合導(dǎo)入hive目錄where從關(guān)系數(shù)據(jù)庫導(dǎo)入數(shù)據(jù)時的查詢條件，示例：where id = 2-z,compress壓縮參數(shù)，默認情況下數(shù)據(jù)是沒被壓縮的，通過該參數(shù)可以使用gzip壓縮算法對數(shù)據(jù)進行壓縮，適用于SequenceFile, text文本文件, 和Avro文件compr

26、ession-codecHadoop壓縮編碼，默認是gzipnull-string 可選參數(shù)，如果沒有指定，則字符串null將被使用null-non-string可選參數(shù)，如果沒有指定，則字符串null將被使用增量導(dǎo)入?yún)?shù)說明check-column (col)用來作為判斷的列名，如idincremental (mode)append：追加，比如對大于last-value指定的值之后的記錄進行追加導(dǎo)入。lastmodified：最后的修改時間，追加last-value指定的日期之后的記錄last-value (value)指定自從上次導(dǎo)入后列的最大值（大于該指定的值），也可以自己設(shè)定某一值對i

27、ncremental參數(shù)，如果是以日期作為追加導(dǎo)入的依據(jù)，則使用lastmodified，否則就使用append值。2.5 import-all-tables將數(shù)據(jù)庫里的所有表導(dǎo)入到HDFS中，每個表在hdfs中都對應(yīng)一個獨立的目錄。sqoop import-all-tables connect jdbc:mysql:/localhost:3306/testsqoop import-all-tables connect jdbc:mysql:/localhost:3306/test hive-import參數(shù)說明as-avrodatafile同import參數(shù)as-sequencefile同i

28、mport參數(shù)as-textfile同import參數(shù)direct同import參數(shù)direct-split-size 同import參數(shù)inline-lob-limit 同import參數(shù)-m,num-mappers 同import參數(shù)warehouse-dir 同import參數(shù)-z,compress同import參數(shù)compression-codec同import參數(shù)2.6 job用來生成一個sqoop的任務(wù)，生成后，該任務(wù)并不執(zhí)行，除非使用命令執(zhí)行該任務(wù)。sqoop job參數(shù)說明create 生成一個job，示例如：sqoop job create myjob import conn

29、ectjdbc:mysql:/localhost:3306/test tablepersondelete 刪除一個jobsqoop job delete myjobexec 執(zhí)行一個jobsqoop job exec myjobhelp顯示幫助說明list顯示所有的jobsqoop job listmeta-connect 用來連接metastore服務(wù)，示例如：meta-connect jdbc:hsqldb:hsql:/localhost:16000/sqoopshow 顯示一個job的各種參數(shù)sqoop job show myjobverbose打印命令運行時的詳細信息2.7 list-

30、databases打印出關(guān)系數(shù)據(jù)庫所有的數(shù)據(jù)庫名sqoop list-databases connect jdbc:mysql:/localhost:3306/ -username root -password 1234562.8 list-tables打印出關(guān)系數(shù)據(jù)庫某一數(shù)據(jù)庫的所有表名sqoop list-tables connect jdbc:mysql:/localhost:3306/zihou -username root -password 1234562.9 merge將HDFS中不同目錄下面的數(shù)據(jù)合在一起，并存放在指定的目錄中，示例如：sqoop merge new-data

31、/test/p1/person onto /test/p2/person target-dir /test/merged jar-file /opt/data/sqoop/person/Person.jar class-name Person merge-key id其中，class-name所指定的class名是對應(yīng)于Person.jar中的Person類，而Person.jar是通過Codegen生成的參數(shù)說明new-data Hdfs中存放數(shù)據(jù)的一個目錄，該目錄中的數(shù)據(jù)是希望在合并后能優(yōu)先保留的，原則上一般是存放越新數(shù)據(jù)的目錄就對應(yīng)這個參數(shù)。onto Hdfs中存放數(shù)據(jù)的一個目錄，該目錄

32、中的數(shù)據(jù)是希望在合并后能被更新數(shù)據(jù)替換掉的，原則上一般是存放越舊數(shù)據(jù)的目錄就對應(yīng)這個參數(shù)。merge-key 合并鍵，一般是主鍵IDjar-file 合并時引入的jar包，該jar包是通過Codegen工具生成的jar包class-name 對應(yīng)的表名或?qū)ο竺?，該class類是包含在jar包中的。target-dir 合并后的數(shù)據(jù)在HDFS里的存放目錄2.10 metastore記錄sqoop job的元數(shù)據(jù)信息，如果不啟動metastore實例，則默認的元數(shù)據(jù)存儲目錄為：/.sqoop，如果要更改存儲目錄，可以在配置文件sqoop-site.xml中進行更改。metastore實例啟動：sq

33、oop metastore參數(shù)說明shutdown關(guān)閉一個運行的metastore實例2.11 version顯示sqoop版本信息語句：sqoop version2.12 help打印sqoop幫助信息語句：sqoop help2.13 公共參數(shù)2.13.1 Hive參數(shù)參數(shù)說明hive-delims-replacement 用自定義的字符串替換掉數(shù)據(jù)中的n, r, and 01等字符hive-drop-import-delims在導(dǎo)入數(shù)據(jù)到hive中時，去掉數(shù)據(jù)中n,r和01這樣的字符map-column-hive 生成hive表時，可以更改生成字段的數(shù)據(jù)類型，格式如：map-column

34、-hiveTBL_ID=String,LAST_ACCESS_TIME=stringhive-partition-key創(chuàng)建分區(qū)，后面直接跟分區(qū)名即可，創(chuàng)建完畢后，通過describe 表名可以看到分區(qū)名，默認為string型hive-partition-value該值是在導(dǎo)入數(shù)據(jù)到hive中時，與hive-partition-key設(shè)定的key對應(yīng)的value值。hive-home Hive的安裝目錄，可以通過該參數(shù)覆蓋掉默認的hive目錄hive-import將數(shù)據(jù)從關(guān)系數(shù)據(jù)庫中導(dǎo)入到hive表中hive-overwrite覆蓋掉在hive表中已經(jīng)存在的數(shù)據(jù)create-hive-tabl

35、e默認是false,如果目標(biāo)表已經(jīng)存在了，那么創(chuàng)建任務(wù)會失敗hive-table后面接要創(chuàng)建的hive表table指定關(guān)系數(shù)據(jù)庫表名2.13.2 數(shù)據(jù)庫連接參數(shù)參數(shù)說明connect Jdcb連接url，示例如：connect jdbc:mysql:/localhost:3306/hiveconnection-manager 指定要使用的連接管理類driver 數(shù)據(jù)庫驅(qū)動類hadoop-home Hadoop根目錄help打印幫助信息-P從控制端讀取密碼password Jdbc url中的數(shù)據(jù)庫連接密碼username Jdbc url中的數(shù)據(jù)庫連接用戶名verbose在控制臺打印出詳細信息

36、connection-param-file 一個記錄著數(shù)據(jù)庫連接參數(shù)的文件2.13.3 文件輸出參數(shù)用于import場景。示例如：sqoop import connect jdbc:mysql:/localhost:3306/test username root P table person split-by id check-column id incremental append last-value 1 enclosed-by ”escaped-by # fields-terminated-by .參數(shù)說明enclosed-by 給字段值前后加上指定的字符，比如雙引號，示例：enclosed-by ”，顯示例子：”3,”jimsss”,”dd”escaped-by 給雙引號作轉(zhuǎn)義處理，如字段值為”測試”，經(jīng)過escaped-by 處理后，在hdfs中的顯示值為：”測試”，對單引號無效fields-terminated-by 設(shè)定每個字段是以什么符號作為結(jié)束的，