sqoop 常用指令
一、Sqoop與MySQL
1.列出mysql資料庫中的所有資料庫
sqoop list-databases -connect jdbc:mysql://192.168.1.10:3306 -username root -password root
注意：
以下URL寫法，都可以
jdbc:mysql://192.168.1.10:3306/(推薦)
jdbc:mysql://192.168.1.10/
jdbc:mysql://192.168.1.10
jdbc:mysql://masters:3306/
jdbc:mysql://master/
jdbc:mysql://master
jdbc:mysql://localhost:3306/
jdbc:mysql://localhost/
jdbc:mysql:///
jdbc:mysql://
2.列出資料庫sqoop中的所有資料表
sqoop list-tables -connect jdbc:mysql:///sqoop -username root -password root
3.通過Sqoop執行SQL語句
sqoop eval -connect jdbc:mysql:///sqoop -username root -password root -query "select * from employee where id=5"
可以快速地使用SQL語句對關系資料庫進行操作，這可以使得在使用import這種工具進行資料導入的時候，可以預先了解相關的SQL語句是否正确，并能将結果顯示在控制台。
二、Sqoop與HDFS
1.将sqoop.employee表中的資料導入HDFS的/sqfs目錄下
sqoop import -connect jdbc:mysql://192.168.10.71:3306/t2 -username=root -password=root -table employee -m 1 -target-dir /output/1
mysql隻認ip位址
疊加
追加模式
sqoop import -connect jdbc:mysql://192.168.10.71:3306/t2 -username root -password root -table employee -m 1 -target-dir /output/3 -incremental append -check-column id -last-value "5"
最後修改模式
sqoop import -connect jdbc:mysql://192.168.1.10:3306/sqoop -username root -password root -table employee -m 1 -target-dir /sqfs -incremental lastmodified -check-column lastmodified -last-value '2016/1/5 18:00:05'
SQL語句
sqoop import -connect jdbc:mysql://192.168.1.10:3306/sqoop -username root -password root -m 1 -query 'SELECT id,birthday from employee where $CONDITIONS' -target-dir /res
注：不能添加-table參數
sqoop import-all-tables -connect jdbc:mysql://192.168.1.10:3306/sqoop -username root -paseeword root -m 1
注：不能添加-target-dir參數
導出的預設路徑是：/user/使用者名/若幹資料表名/資料檔案和_SUCCESS
所有表都有主鍵時，可以設定-m的參數大于1，否則隻能唯1
2.将HDFS上/sqfs目錄下的資料導入的sqoop.employee表中
sqoop export -connect "jdbc:mysql://192.168.11.51:3306/big1806?useUnicode=true&characterEncoding=utf-8" -username root -password root -table t2 -m 1 -export-dir /sqoop/mysql/t1
采用export插入資料的時候，如果資料已經存在了，插入會失敗，如果我們使用-update-key，它會認為每個資料都是更新，比如我們使用下面這條語句：
sqoop export -table foo -update-key id -export-dir /path/to/data -connect …
UPDATE foo SET msg='this is a test', bar=42 WHERE id=0;
UPDATE foo SET msg='some more data', bar=100 WHERE id=1;
...
這樣即使找不到它也不會報錯
-update-mode allowinsert 如果存在就更新，不存在就插入
三、Sqoop與Hive
1.将關系型資料的employee表結構複制到Hive中
sqoop create-hive-table -connect jdbc:mysql://192.168.11.51:3306/big1806 -username root -password root -table t1 -hive-table sqoop.t1 -fields-terminated-by "\0001" -lines-terminated-by "\n"注：
-hive-table emp指定在Hive中建立的表名為emp(預設資料庫default)
-hive-table sqoop.emp指定在Hive中的sqoop資料庫下建立emp表
-fields-terminated-by "\0001" 是設定每列之間的分隔符，"\0001"是ASCII碼中的1，是hive的預設行内分隔符，而sqoop的預設行内分隔符為"，"
-lines-terminated-by "\n" 設定的是每行之間的分隔符，此處為換行符，也是預設的分隔符；
2.将關系資料庫中的employee表的資料導入檔案到Hive表中
sqoop import -connect jdbc:mysql://192.168.1.10:3306/sqoop -username root -password root -table employee -hive-table sqoop.emp -m 1 -fields-terminated-by "\0001" -hive-import
注：
-fields-terminated-by "\0001" 需同建立Hive表時保持一緻
-hive-import 指定是Hive導入資料
-split-by id employee中沒有主鍵時，用于指定Mapper時的Key
追加1
sqoop import -append -connect jdbc:mysql://192.168.1.10:3306/sqoop -username root -password root -target-dir /user/hive/warehouse/sqoop.db/emp/ -fields-terminated-by "\0001" -query "select * from employee where \$CONDITIONS" -m 1
注：
可以添加-columns，-where參數，同時使用時-where參數會失效
追加2
sqoop import -append -connect jdbc:mysql://192.168.1.10:3306/sqoop -username root -password root -table employee -columns "id,name,birthday" -where "id=2" -m 1 -target-dir /user/hive/warehouse/sqoop.db/emp/ -fields-terminated-by "\0001"
注：
-target-dir /user/hive/warehouse/sqoop.db/emp 可用-hive-tablesqoop.emp -hive-import替換,但是要去掉-append參數。
在導入大對象，比如BLOB和CLOB列時需要特殊處理，小于16MB的大對象可以和别的資料一起存儲，超過這個值就存儲在_lobs的子目錄當中，它們采用的是為大對象做過優化的存儲格式，最大能存儲2^63位元組的資料，我們可以用-inline-lob-limit參數來指定每個lob檔案最大的限制是多少，如果設定為0，則大對象使用外部存儲。
3. Hive導入參數
-hive-home
-hive-import 插入資料到hive當中，使用hive的預設分隔符
-hive-overwrite 重寫插入
-create-hive-table 建表，如果表已經存在，該操作會報錯！
-hive-table 設定到hive當中的表名
-hive-drop-import-delims 導入到hive時删除\n, \r, and \0001
-hive-delims-replacement 導入到hive時用自定義的字元替換掉\n, \r, and \0001
-hive-partition-key hive分區的key
-hive-partition-value hive分區的值
-map-column-hive 類型比對，sql類型對應到hive類型
hive空值處理
sqoop會自動把NULL轉換為null處理，但是hive中預設是把\N來表示null，因為預先處理不會生效的，我們需要使用 -null-string 和-null-non-string來處理空值 把\N轉為\\N
例句：sqoop import ... -null-string '\\N' 或-null-non-string '\\N'
sqoop導入hive資料到MySql碰到hive表中列的值為null的情況：
在導入資料的過程中，如果碰到列值為null的情況，hive中為null的是以\N代替的，是以你在導入到MySql時，需要加上兩個參數：--input-null-string '\\N' --input-null-non-string '\\N'，多加一個'\'，是為轉義。如果你通過這個還不能解決字段為null的情況，還是報什麼NumberFormalt異常的話，那就是比較另類的了，沒有關系，我們還是要辦法解決。
你應該注意到每次通過sqoop導入MySql的時，都會生成一個以MySql表命名的.java檔案，然後打成JAR包，給sqoop送出給hadoop的MR來解析Hive表中的資料。那我們可以根據報的錯誤，找到對應的行，改寫該檔案，編譯，重新打包，sqoop可以通過-jar-file，--class-name組合讓我們指定運作自己的jar包中的某個class。來解析該hive表中的每行資料。腳本如下：一個完整的例子如下：
sqoop export --connect "jdbc:mysql://localhost/aaa?useUnicode=true&characterEncoding=utf-8"
--username aaa --password bbb --table table
--export-dir /hive/warehouse/table --input-fields-terminated-by '\t'
--input-null-string '\\N' --input-null-non-string '\\N'
--class-name com.chamago.sqoop.codegen.bi_weekly_sales_item
--jar-file /tmp/sqoop-chamago/bi_weekly_sales_item.jar
上面--jar-file 參數指定jar包的路徑。--class-name指定jar包中的class。
這樣就可以解決所有解析異常了。
4.将Hive中的表資料導入到mysql資料庫employee表中
sqoop export -connect "jdbc:mysql://192.168.11.51:3306/big1806?useUnicode=true&characterEncoding=utf-8" -username root -password root -table t3 -export-dir /user/hive/warehouse/sqoop.db/t1/part-m-00000 -input-fields-terminated-by '\0001'注：
在進行導入之前，mysql中sqoop資料庫中employee表必須已經提起建立好了。
jdbc:mysql://192.168.1.10:3306/sqoop中的IP位址改成localhost會報異常
指定/user/hive/warehouse/sqoop.db/emp/part-m-00000，隻加載該檔案
指定/user/hive/warehouse/sqoop.db/emp/，加載該目錄下的所有檔案
四、Sqoop與HBase
1.MySQL中的employee表中的資料導入資料到 HBase的emp表中
sqoop import -connect jdbc:mysql://192.168.1.10:3306/sqoop -username root -password root -table employee -hbase-table emp -column-family 'per data' -hbase-row-key id -m 1
注：
-hbase-table emp指定HBase的表emp
-column-family 'per data'指定列族名per data
-hbase-create-table該參數是用來建立HBase表的，但不太管用
2. 将employee表不同列的資料添加到emp表中不同列族中
先将employee表的name列中的資料到per data列族中
sqoop import -connect jdbc:mysql://192.168.1.10:3306/sqoop -username root -password root -table employee -hbase-table emp -column-family 'per data' -hbase-row-key id -m 1 -columns id,name
注：
-columns id,name指定employee表中的id、name列
-hbase-row-key id指定emp表中的行id
先将employee表的age、birthday列中的資料到pro data列族中
sqoop import -connect jdbc:mysql://192.168.1.10:3306/sqoop -username root -password root -table employee -hbase-table emp -column-family 'per data' -hbase-row-key id -m 1 -columns id,age,birthday
五、重要參數
1.轉換為對象
-map-column-java 将轉換為java資料類型
-map-column-hive 将轉換為hive資料類型
2.分隔符、轉義字元
例句：
Some string, with a comma.
Another "string with quotes"
導入指令：
$ sqoop import -fields-terminated-by , -escaped-by \\ -enclosed-by '\"' ...
處理結果：
"Some string, with a comma.","1","2","3"...
"Another \"string with quotes\"","4","5","6"...
導入指令：
$ sqoop import -optionally-enclosed-by '\"' (the rest as above)...
處理結果：
"Some string, with a comma.",1,2,3...
"Another \"string with quotes\"",4,5,6...
六、常用工具
1.sqoop job
儲存常用的作業，以便下次快速調用
-create 建立一個新的job
-delete 删除job
-exec 執行job
-show 顯示job的參數
-list列出所有的job
建立job
sqoop job -create myjob - import -connect jdbc:mysql://example.com/db ... -table mytable
列出所有job
sqoop job -list
檢視job
sqoop job -show myjob
執行job
sqoop job -exec myjob
重寫參數
sqoop job -exec myjob -username someuser -P
2. 聚合工具
sqoop-metastore、sqoop-merge
合并兩個目錄
sqoop merge -new-data newer -onto older -target-dir merged -jar-file datatypes.jar -class-name Foo -merge-key id
3. 校驗Validate
它用來比較源資料和目标資料的數量。
sqoop import --connect jdbc:mysql://db.foo.com/corp ... -table EMPLOYEES --validate
sqoop export --connect jdbc:mysql://db.example.com/foo --table bar -export-dir /results/bar_data --validate
注：
它有三個接口
Validator
Property: validator
Description: Driver for validation,
must implement org.apache.sqoop.validation.Validator
Supported values: The value has to be a fully qualified class name.
Default value: org.apache.sqoop.validation.RowCountValidator
Validation Threshold
Property:validation-threshold
Description:Drives the decision based on the validation meeting the
threshold or not. Must implement
org.apache.sqoop.validation.ValidationThreshold
Supported values:The value has to be a fully qualified class name.
Default value:org.apache.sqoop.validation.AbsoluteValidationThreshold
Validation Failure Handler
Property:validation-failurehandler
Description:Responsible for handling failures, must implement
org.apache.sqoop.validation.ValidationFailureHandler
Supported values:The value has to be a fully qualified class name.
Default value:org.apache.sqoop.validation.LogOnFailureHandler
例句：
sqoop import --connect jdbc:mysql://db.foo.com/corp --table EMPLOYEES
添加參數：
-validate -validator org.apache.sqoop.validation.RowCountValidator
或-validate -validation-threshold
或-validate -validation-failurehandler
七、配置檔案
通過配置檔案conf/sqoop-site.xml來配置常用參數
例：
property.name
property.value
如果不在這裡面配置的話，就需要像這樣寫指令
sqoop import -D property.name=property.value ...
參數：sqoop.bigdecimal.format.string
作用：大decimal是否儲存為string，如果儲存為string就是0.0000007,否則為1E7
參數：sqoop.hbase.add.row.key
作用：是否把作為rowkey的列也加到行資料當中，預設是false的
附件：
1. 亂碼問題：
MySQL與HDFS互相導入，導出的亂碼問題。
修改MySQL的編碼格式，由Latin1改為UTF-8
vi /etc/my.cnf
添加如下内容：紅色的這個 是适合老版mysql 入：mysql5.1.71
[mysqld]
default-character-set=utf8
character_set_server=utf8
init_connect='SET NAMES utf8'
[mysql]
default-character-set=utf8
[client]
default-character-set=utf8
重新開機MySQL服務，重建庫、表；
2. MySQL建表語句
建立員工表1
create table employee(
id int primary key auto_increment,
name varchar(20),
birthday date
);
測試資料：
insert into employee values('','張三','2000-01-01');
insert into employee values('','李四','2001-01-01');
insert into employee values('','王五','2002-01-01');
insert into employee values('','張小三','2003-01-01');
insert into employee values('','李小四','2004-01-01');
insert into employee values('','王小五','2005-01-01');
insert into employee values('','張大三','2006-01-01');
insert into employee values('','李大四','2007-01-01');
insert into employee values('','王大五','2008-01-01');
insert into employee values('','王二麻子','2009-01-01');
insert into employee values('','wangermazi','2010-01-01');
建立員工表2
create table em(
id int,
name varchar(20),
birthday date
);
測試資料：
insert into employee values('0','張三','2000-01-01');
insert into employee values('1','李四','2001-01-01');
insert into employee values('2','王五','2002-01-01');
insert into employee values('3','張小三','2003-01-01');
insert into employee values('4','李小四','2004-01-01');
insert into employee values('5','王小五','2005-01-01');
insert into employee values('6','張大三','2006-01-01');
insert into employee values('7','李大四','2007-01-01');
insert into employee values('8','王大五','2008-01-01');
insert into employee values('9','王二麻子','2009-01-01');
insert into employee values('10','wangermazi','2009-01-01');
3. HBase建表語句
MySQL建表語句
create table employee(
id int,
name varchar(20),
age int,
birthday date
);
測試資料
insert into employee values('0','張三','18','2003-01-01');
insert into employee values('1','李四','18','2003-01-01');
insert into employee values('2','王五','18','2003-01-01');
insert into employee values('3','wangermazi','18','2003-01-01');
創表語句
create 'emp','per data','pro data'
删表語句
disable 'emp'
drop 'emp'
查詢語句
scan ‘emp’