一、sqoop--基本使用

一、sqoop概述

1.1 簡(jiǎn)介

? sqoop用于在hadoop（hdfs、hive）和關(guān)系型數(shù)據(jù)庫(kù)等結(jié)構(gòu)化數(shù)據(jù)存儲(chǔ)之間相關(guān)導(dǎo)數(shù)據(jù)的場(chǎng)景。Sqoop于2012年3月孵化出來(lái)，現(xiàn)在是一個(gè)頂級(jí)的Apache項(xiàng)目。
請(qǐng)注意，1.99.7與1.4.6不兼容，且特征不完整，它并不打算用于生產(chǎn)部署。

1.2 基本原理

將導(dǎo)入或?qū)С雒罘g成mapreduce程序來(lái)實(shí)現(xiàn)。
在翻譯出的mapreduce中主要是對(duì)inputformat和outputformat進(jìn)行定制。

二、部署sqoop

首先得準(zhǔn)備好hadoop和java環(huán)境，這里不重復(fù)說(shuō)。
這里使用 sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz。
解壓程序：

tar -zxvf sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz -C /opt/modules/

修改配置文件：

進(jìn)入sqoop的解壓路徑，進(jìn)入conf目錄
mv sqoop-env-template.sh sqoop-env.sh

修改內(nèi)容：
#export HADOOP_COMMON_HOME=
export HADOOP_COMMON_HOME=/opt/modules/hadoop-2.8.4

#Set path to where hadoop-*-core.jar is available
#export HADOOP_MAPRED_HOME=
export HADOOP_MAPRED_HOME=/opt/modules/hadoop-2.8.4

#Set the path to where bin/hive is available
#export HIVE_HOME=
export HIVE_HOME=/opt/modules/hive-1.2.1-bin

#Set the path for where zookeper config dir is
#export ZOOCFGDIR=
export ZOOCFGDIR=/opt/modules/zookeeper-3.4.10/conf

準(zhǔn)備需要連接的關(guān)系型數(shù)據(jù)庫(kù)的連接驅(qū)動(dòng)，這里使用mysql，所以下載 mysql-connector-java-5.1.27-bin.jar，然后

cp -a mysql-connector-java-5.1.27-bin.jar /opt/module/sqoop-1.4.6.bin__hadoop-2.0.4-alpha/lib

接著配置環(huán)境變量：

vim /etc/profile.d/sqoop.sh
#!/bin/bash
export SQOOP_HOME=/opt/modules/sqoop-1.4.6.bin__hadoop-2.0.4-alpha
export PATH=$PATH:${SQOOP_HOME}/bin

驗(yàn)證sqoop：

sqoop help

三、sqoop簡(jiǎn)單使用案例

在詳細(xì)說(shuō)明sqoop的用法之前，先來(lái)點(diǎn)簡(jiǎn)單案例熟悉下

3.1 導(dǎo)入數(shù)據(jù)

? 在Sqoop中，“導(dǎo)入”概念指：從非大數(shù)據(jù)集群（RDBMS）向大數(shù)據(jù)集群（HDFS，HIVE，HBASE等）中傳輸數(shù)據(jù)，叫做：導(dǎo)入，即使用import關(guān)鍵字。

3.1.1 RDBMS到HDFS

先在mysql中準(zhǔn)備些數(shù)據(jù)：

$ mysql -uroot -p000000
mysql> create database company;
mysql> create table company.staff(id int(4) primary key not null auto_increment, name varchar(255), sex varchar(255));
mysql> insert into company.staff(name, sex) values('Thomas', 'Male');
mysql> insert into company.staff(name, sex) values('Catalina', 'FeMale');

導(dǎo)入全部數(shù)據(jù)：

sqoop import \
--connect jdbc:mysql://bigdata121:3306/company \   jdbc的連接串
--username root \                                  用戶名
--password 000000 \                                密碼
--table staff \                                    數(shù)據(jù)源的表
--target-dir /user/company \                       hdfs指定目錄
--delete-target-dir \                              目標(biāo)目錄存在就刪除
--num-mappers 1 \                                  map數(shù)目
--fields-terminated-by "\t"                        輸出到hdfs的分隔符

查詢導(dǎo)入：

sqoop import \
--connect jdbc:mysql://bigdata121:3306/company \
--username root \
--password 000000 \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t" \     寫(xiě)入到hdfs文件中的字段分隔符
--query 'select name,sex from staff where id <=3 and $CONDITIONS;' 執(zhí)行的sql查詢語(yǔ)句

尖叫提示1：must contain '$CONDITIONS' in WHERE clause.即where語(yǔ)句的最后面一定要加上 and $CONDITIONS
尖叫提示2：如果query后使用的是雙引號(hào)，則$CONDITIONS前必須加轉(zhuǎn)義符，防止shell識(shí)別為自己的變量。

導(dǎo)入指定列：

$ bin/sqoop import \
--connect jdbc:mysql://bigdata113:3306/company \
--username root \
--password 000000 \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t" \
--columns id,sex \                       導(dǎo)入指定列
--table staff

尖叫提示：columns中如果涉及到多列，用逗號(hào)分隔，分隔時(shí)不要添加空格
像這種只是簡(jiǎn)單對(duì)列進(jìn)行篩選的，用上面的方式就好，如果需要進(jìn)行where之類的條件篩選，那么還是使用查詢導(dǎo)入

使用sqoop關(guān)鍵字篩選查詢導(dǎo)入數(shù)據(jù)：

sqoop import \
--connect jdbc:mysql://bigdata113:3306/company \
--username root \
--password 000000 \
--target-dir /user/company \
--delete-target-dir \
--num-mappers 1 \
--fields-terminated-by "\t" \
--table staff \
--where "id=2"                        這個(gè)其實(shí)相當(dāng)于select中的where語(yǔ)句

這個(gè)方式其實(shí)就相當(dāng)于--query方式進(jìn)行查詢導(dǎo)入

尖叫提示：在Sqoop中可以使用sqoop import -D property.name=property.value這樣的方式加入執(zhí)行任務(wù)的參數(shù)，多個(gè)參數(shù)用空格隔開(kāi)。

3.1.2 RDBMS到hive

sqoop import \
--connect jdbc:mysql://bigdata113:3306/Andy \
--username root \
--password 000000 \
--table aca \
--num-mappers 1 \
--hive-import \                           指定導(dǎo)入到hive中
--fields-terminated-by "\t" \             指定字段分隔符
--hive-overwrite \                        覆蓋導(dǎo)入
--hive-table staff_hive                   指定導(dǎo)入到hive中的表

尖叫提示1：從MYSQL到Hive，本質(zhì)是從MYSQL => HDFS => load To Hive
尖叫提示2：hive表不存在時(shí)，自動(dòng)創(chuàng)建。表存在默認(rèn)就會(huì)自動(dòng)覆蓋數(shù)據(jù)

3.2 導(dǎo)出數(shù)據(jù)

? 在Sqoop中，“導(dǎo)出”概念指：從大數(shù)據(jù)集群（HDFS，HIVE，HBASE等）向非大數(shù)據(jù)集群（RDBMS）中傳輸數(shù)據(jù)，叫做：導(dǎo)出，即使用export關(guān)鍵字。

mysql創(chuàng)建aca表
create table abc(id int,name VARCHAR(5));

hive導(dǎo)入mysql
sqoop export \
--connect jdbc:mysql://bigdata113:3306/test\
--username root \
--password 000000 \
--export-dir /user/hive/warehouse/staff_hive \
--table abc \
--num-mappers 1 \
--input-fields-terminated-by "\t"

尖叫提示1：Mysql中如果表不存在，不會(huì)自動(dòng)創(chuàng)建。表不存在會(huì)報(bào)錯(cuò) 
尖叫提示2：數(shù)據(jù)是追加的，不是覆蓋

3.3 腳本打包

使用opt格式文件打包sqoop命令，文件后綴必須是.opt

vi ./job_HDFS2RDBMS.opt
#以下命令是從staff_hive中追加導(dǎo)入到mysql的aca表中
# 格式基本上是 option /n value

export
--connect
jdbc:mysql://bigdata113:3306/Andy
--username
root
--password
000000
--table
aca
--num-mappers
1
--export-dir
/user/hive/warehouse/staff_hive
--input-fields-terminated-by
"\t"

格式必須驗(yàn)證按照上面的寫(xiě)，選項(xiàng)和參數(shù)各自獨(dú)立一行

執(zhí)行腳本：

sqoop --options-file job_HDFS2RDBMS.opt

四、sqoop常用參數(shù)

sqoop的用法其實(shí)是以 sqoop subcommand options 的形式的，有很多子命令，選項(xiàng)也有公有選項(xiàng)以及獨(dú)有選項(xiàng)，下面看看每個(gè)子命令的用法

4.1 數(shù)據(jù)庫(kù)連接參數(shù)

4.2 import公用參數(shù)

4.3 export公用參數(shù)

4.4hive的公用參數(shù)

4.5 import--導(dǎo)入到hadoop

? 將關(guān)系型數(shù)據(jù)庫(kù)中的數(shù)據(jù)導(dǎo)入到HDFS（包括Hive，HBase）中，如果導(dǎo)入的是Hive，那么當(dāng)Hive中沒(méi)有對(duì)應(yīng)表時(shí)，則自動(dòng)創(chuàng)建。導(dǎo)入數(shù)據(jù)時(shí)，默認(rèn)是追加的方式。

導(dǎo)入到hive中
sqoop import \
--connect jdbc:mysql://bigdata113:3306/Andy \
--username root \
--password 000000 \
--table access \
--hive-import \
--fields-terminated-by "\t"

增量導(dǎo)入：
有幾個(gè)關(guān)鍵參數(shù)

append模式：

sqoop import \
--connect jdbc:mysql://bigdata113:3306/Andy \
--username root \
--password 000000 \
--table aca \
--num-mappers 1 \
--fields-terminated-by "\t" \
--target-dir /user/hive/warehouse/staff_hive \
--check-column id \
--incremental append \
--last-value 10     在check-column指定的字段中，下一次導(dǎo)入的開(kāi)始的行的id的值

尖叫提示1：append不能與--hive-等參數(shù)同時(shí)使用
尖叫提示2：如果 --last-value N , N > MYSQL中最大行數(shù)，則HDFS會(huì)創(chuàng)建一個(gè)空文件。如果N<=0 , 那么就是所有數(shù)據(jù)

lastmodified模式：

先在mysql中建表并插入幾條數(shù)據(jù)：
mysql> create table company.staff_timestamp(id int(4), name varchar(255), sex varchar(255), last_modified timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
mysql> insert into company.staff_timestamp (id, name, sex) values(1, 'AAA', 'female');
mysql> insert into company.staff_timestamp (id, name, sex) values(2, 'BBB', 'female');
先導(dǎo)入一部分?jǐn)?shù)據(jù)：
$ bin/sqoop import \
--connect jdbc:mysql://bigdata113:3306/company \
--username root \
--password 000000 \
--table staff_timestamp \
--delete-target-dir \
--hive-import \
--fields-terminated-by "\t" \
--m 1
再增量導(dǎo)入一部分?jǐn)?shù)據(jù):
mysql> insert into company.staff_timestamp (id, name, sex) values(3, 'CCC', 'female');
$ bin/sqoop import \
--connect jdbc:mysql://bigdata113:3306/company \
--username root \
--password 000000 \
--table staff_timestamp \
--check-column last_modified \
--incremental lastmodified \   指定為lastmodified模式
--m 1 \
--last-value "2019-05-17 09:50:12" \
--append    指定為追加模式

尖叫提示1：使用lastmodified方式導(dǎo)入數(shù)據(jù)要指定增量數(shù)據(jù)是要--append（追加）還是要--merge-key COLUMN（合并）
尖叫提示2：在Hive中，如果不指定輸出路徑，可以去看以下兩個(gè)目錄
1.  /user/root（此為用戶名）追加模式中會(huì)將文件輸出到這里
如果需要導(dǎo)入到hive中的表時(shí)，需要—target-dir 指定輸出目錄到hive的目錄
2.  /user/hive/warehouse  個(gè)人配置的目錄，普通模式下會(huì)輸出到這里
尖叫提示3：last-value指定的值是會(huì)包含于增量導(dǎo)入的數(shù)據(jù)中
注意：lastmodified模式下，如果不指定輸出hdfs路徑，就是輸出到/user/root/TABLE_NAME/ 下，不會(huì)再 /user/hive/warehouse/ 下，這點(diǎn)要注意

Last-modified和append的區(qū)別：

1、append，在導(dǎo)入的新數(shù)據(jù)ID值是連續(xù)時(shí)采用，對(duì)數(shù)據(jù)進(jìn)行附加 加不加–last-value的區(qū)別在于：數(shù)據(jù)是否冗余，如果不加，則會(huì)導(dǎo)入源表中的所有數(shù)據(jù)導(dǎo)致數(shù)據(jù)冗余。加了就表示將大于 last-value值的行導(dǎo)入，小于的就不導(dǎo)入。而且作為 –check-column的字段必須是可比較的類型，字符是不行的，通常是數(shù)字，比如表的主鍵id

2、lastmodified，在源表中有數(shù)據(jù)更新的時(shí)候使用，檢查列就必須是一個(gè)時(shí)間戳或日期類型的字段，更新完之后，last-value會(huì)被設(shè)置為執(zhí)行增量導(dǎo)入時(shí)的當(dāng)前系統(tǒng)時(shí)間，當(dāng)使用–incremental lastmodified模式進(jìn)行導(dǎo)入且導(dǎo)入目錄已存在時(shí)，需要使用–merge-key或–append ，--merge-key會(huì)將所有的行根據(jù)指定的key進(jìn)行合并
導(dǎo)入>=last-value的值。--incremental lastmodified --check-column created --last-value '2012-02-01 11:0:00'
就是只導(dǎo)入修改時(shí)間 比'2012-02-01 11:0:00'更大的數(shù)據(jù)，如果存在就覆蓋。
--append  就是將指定時(shí)間之后的數(shù)據(jù)追加，不會(huì)做覆蓋

4.6 export導(dǎo)出數(shù)據(jù)到mysql

從HDFS（包括Hive和HBase）中將數(shù)據(jù)導(dǎo)出到關(guān)系型數(shù)據(jù)庫(kù)中。

從hdfs導(dǎo)出到mysql
sqoop export \
--connect jdbc:mysql://bigdata113:3306/Andy \
--username root \
--password 000000 \
--export-dir /user/hive/warehouse/staff_hive \    hdfs導(dǎo)出路徑
--table aca \
--num-mappers 1 \
--input-fields-terminated-by "\t"

4.7 codegen--數(shù)據(jù)打包成jar

將關(guān)系型數(shù)據(jù)庫(kù)中的表映射為一個(gè)Java類，在該類中有各列對(duì)應(yīng)的各個(gè)字段。

sqoop codegen \
--connect jdbc:mysql://bigdata113:3306/company \
--username root \
--password 000000 \
--table staff \
--bindir /opt/Desktop/staff \    
--class-name Staff \
--fields-terminated-by "\t"

用法和import類似，只是把import換成codegen，打包成jar，但是不執(zhí)行。
默認(rèn)會(huì)在/tmp/sqoop-root/compile/[JOB_ID]/ 生成對(duì)應(yīng)的jar包

常用參數(shù)：

4.8 create-hive-table創(chuàng)建hive表

用于單獨(dú)創(chuàng)建hive表

sqoop create-hive-table \
--connect jdbc:mysql://bigdata113:3306/company \
--username root \
--password 000000 \
--table staff \
--hive-table hive_staff1     指定創(chuàng)建的表名

4.9 eval--在命令行下執(zhí)行sql語(yǔ)句

? 可以快速的使用SQL語(yǔ)句對(duì)關(guān)系型數(shù)據(jù)庫(kù)進(jìn)行操作，經(jīng)常用于在import數(shù)據(jù)之前，了解一下SQL語(yǔ)句是否正確，數(shù)據(jù)是否正常，并可以將結(jié)果顯示在控制臺(tái)。

sqoop eval \
--connect jdbc:mysql://bigdata113:3306/company \
--username root \
--password 000000 \
--query "SELECT * FROM staff"             查詢語(yǔ)句

4.10 import-all-tables導(dǎo)入所有表

可以將RDBMS中的所有表導(dǎo)入到HDFS中，每一個(gè)表都對(duì)應(yīng)一個(gè)HDFS目錄

bin/sqoop import-all-tables \
--connect jdbc:mysql://bigdata113:3306/company \
--username root \
--password 000000 \
--hive-import \
--fields-terminated-by "\t"

4.11 job生成sqoop任務(wù)

用來(lái)生成一個(gè)sqoop任務(wù)，生成后不會(huì)立即執(zhí)行，需要手動(dòng)執(zhí)行。

生成job
$ bin/sqoop job \
 --create myjob -- import-all-tables \
 --connect jdbc:mysql://bigdata113:3306/company \
 --username root \
 --password 000000

顯示job
$ bin/sqoop job \
--list

執(zhí)行job
$ bin/sqoop job \
--exec myjob

尖叫提示：注意import-all-tables和它左邊的--之間有一個(gè)空格
尖叫提示：如果需要連接metastore，則--meta-connect 
執(zhí)行的結(jié)果在HDFS：/user/root/ 目錄中，即導(dǎo)出所有表到/user/root中

4.12 list-databases和list-tables

顯示mysql庫(kù)：

sqoop list-databases \
--connect jdbc:mysql://bigdata113:3306/ \
--username root \
--password 000000

顯示mysql表：

sqoop list-tables \
--connect jdbc:mysql://bigdata113:3306/company \
--username root \
--password 000000

4.13 merge合并hdfs文件

將HDFS中不同目錄下面的數(shù)據(jù)合并在一起并放入指定目錄中
數(shù)據(jù)環(huán)境：注意:以下數(shù)據(jù)自己手動(dòng)改成\t
new_staff
1 AAA male
2 BBB male
3 CCC male
4 DDD male

old_staff
1 AAA female
2 CCC female
3 BBB female
6 DDD female

尖叫提示：上邊數(shù)據(jù)的列之間的分隔符應(yīng)該為\t，行與行之間的分割符為\n，如果直接復(fù)制，請(qǐng)檢查。

開(kāi)始合并

創(chuàng)建JavaBean：將數(shù)據(jù)以及任務(wù)打包成jar包
$ bin/sqoop codegen \
--connect jdbc:mysql://bigdata113:3306/company \
--username root \
--password 000000 \
--table staff \
--bindir /opt/Desktop/staff \
--class-name Staff \
--fields-terminated-by "\t"

開(kāi)始合并：注：是hdfs路徑，運(yùn)行上面的jar包.記得將上面的數(shù)據(jù)文件上傳到hdfs
$ bin/sqoop merge \
--new-data /test/new/ \
--onto /test/old/ \
--target-dir /test/merged \
--jar-file /opt/Desktop/staff/Staff.jar \
--class-name Staff \
--merge-key id
結(jié)果：
1 AAA MALE
2 BBB MALE
3 CCC MALE
4 DDD MALE
6 DDD FEMALE

參數(shù)：