一、sqoop--基本使用

一、sqoop概述

1.1 簡介

? sqoop用于在hadoop（hdfs、hive）和關系型數據庫等結構化數據存儲之間相關導數據的場景。Sqoop于2012年3月孵化出來，現在是一個頂級的Apache項目。
請注意，1.99.7與1.4.6不兼容，且特征不完整，它并不打算用于生產部署。

1.2 基本原理

將導入或導出命令翻譯成mapreduce程序來實現。
在翻譯出的mapreduce中主要是對inputformat和outputformat進行定制。

二、部署sqoop

首先得準備好hadoop和java環境，這里不重復說。
這里使用 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/

修改配置文件：

進入sqoop的解壓路徑，進入conf目錄
mv sqoop-env-template.sh sqoop-env.sh

修改內容：
#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

準備需要連接的關系型數據庫的連接驅動，這里使用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

接著配置環境變量：

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

驗證sqoop：

sqoop help

三、sqoop簡單使用案例

在詳細說明sqoop的用法之前，先來點簡單案例熟悉下

3.1 導入數據

? 在Sqoop中，“導入”概念指：從非大數據集群（RDBMS）向大數據集群（HDFS，HIVE，HBASE等）中傳輸數據，叫做：導入，即使用import關鍵字。

3.1.1 RDBMS到HDFS

先在mysql中準備些數據：

$ 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');

導入全部數據：

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

查詢導入：

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" \     寫入到hdfs文件中的字段分隔符
--query 'select name,sex from staff where id <=3 and $CONDITIONS;' 執行的sql查詢語句

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

導入指定列：

$ 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 \                       導入指定列
--table staff

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

使用sqoop關鍵字篩選查詢導入數據：

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"                        這個其實相當于select中的where語句

這個方式其實就相當于--query方式進行查詢導入

尖叫提示：在Sqoop中可以使用sqoop import -D property.name=property.value這樣的方式加入執行任務的參數，多個參數用空格隔開。

3.1.2 RDBMS到hive

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

尖叫提示1：從MYSQL到Hive，本質是從MYSQL => HDFS => load To Hive
尖叫提示2：hive表不存在時，自動創建。表存在默認就會自動覆蓋數據

3.2 導出數據

? 在Sqoop中，“導出”概念指：從大數據集群（HDFS，HIVE，HBASE等）向非大數據集群（RDBMS）中傳輸數據，叫做：導出，即使用export關鍵字。

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

hive導入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中如果表不存在，不會自動創建。表不存在會報錯 
尖叫提示2：數據是追加的，不是覆蓋

3.3 腳本打包

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

vi ./job_HDFS2RDBMS.opt
#以下命令是從staff_hive中追加導入到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"

格式必須驗證按照上面的寫，選項和參數各自獨立一行

執行腳本：

sqoop --options-file job_HDFS2RDBMS.opt

四、sqoop常用參數

sqoop的用法其實是以 sqoop subcommand options 的形式的，有很多子命令，選項也有公有選項以及獨有選項，下面看看每個子命令的用法

4.1 數據庫連接參數

4.2 import公用參數

4.3 export公用參數

4.4hive的公用參數

4.5 import--導入到hadoop

? 將關系型數據庫中的數據導入到HDFS（包括Hive，HBase）中，如果導入的是Hive，那么當Hive中沒有對應表時，則自動創建。導入數據時，默認是追加的方式。

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

增量導入：
有幾個關鍵參數

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指定的字段中，下一次導入的開始的行的id的值

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

lastmodified模式：

先在mysql中建表并插入幾條數據：
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');
先導入一部分數據：
$ 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
再增量導入一部分數據:
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方式導入數據要指定增量數據是要--append（追加）還是要--merge-key COLUMN（合并）
尖叫提示2：在Hive中，如果不指定輸出路徑，可以去看以下兩個目錄
1.  /user/root（此為用戶名）追加模式中會將文件輸出到這里
如果需要導入到hive中的表時，需要—target-dir 指定輸出目錄到hive的目錄
2.  /user/hive/warehouse  個人配置的目錄，普通模式下會輸出到這里
尖叫提示3：last-value指定的值是會包含于增量導入的數據中
注意：lastmodified模式下，如果不指定輸出hdfs路徑，就是輸出到/user/root/TABLE_NAME/ 下，不會再 /user/hive/warehouse/ 下，這點要注意

Last-modified和append的區別：

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

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

4.6 export導出數據到mysql

從HDFS（包括Hive和HBase）中將數據導出到關系型數據庫中。

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

4.7 codegen--數據打包成jar

將關系型數據庫中的表映射為一個Java類，在該類中有各列對應的各個字段。

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，但是不執行。
默認會在/tmp/sqoop-root/compile/[JOB_ID]/ 生成對應的jar包

常用參數：

4.8 create-hive-table創建hive表

用于單獨創建hive表

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

4.9 eval--在命令行下執行sql語句

? 可以快速的使用SQL語句對關系型數據庫進行操作，經常用于在import數據之前，了解一下SQL語句是否正確，數據是否正常，并可以將結果顯示在控制臺。

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

4.10 import-all-tables導入所有表

可以將RDBMS中的所有表導入到HDFS中，每一個表都對應一個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任務

用來生成一個sqoop任務，生成后不會立即執行，需要手動執行。

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

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

執行job
$ bin/sqoop job \
--exec myjob

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

4.12 list-databases和list-tables

顯示mysql庫：

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中不同目錄下面的數據合并在一起并放入指定目錄中
數據環境：注意:以下數據自己手動改成\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

尖叫提示：上邊數據的列之間的分隔符應該為\t，行與行之間的分割符為\n，如果直接復制，請檢查。

開始合并

創建JavaBean：將數據以及任務打包成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"

開始合并：注：是hdfs路徑，運行上面的jar包.記得將上面的數據文件上傳到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
結果：
1 AAA MALE
2 BBB MALE
3 CCC MALE
4 DDD MALE
6 DDD FEMALE

參數：