Sqoop的基本操作

sqoop help

Available commands:
  codegen            Generate code to interact with database records
  create-hive-table  Import a table definition into Hive
  eval               Evaluate a SQL statement and display the results
  export             Export an HDFS directory to a database table
  help               List available commands
  import             Import a table from a database to HDFS
  import-all-tables  Import tables from a database to HDFS
  import-mainframe   Import datasets from a mainframe server to HDFS
  job                Work with saved jobs
  list-databases     List available databases on a server
  list-tables        List available tables in a database
  merge              Merge results of incremental imports
  metastore          Run a standalone Sqoop metastore
  version            Display version information

导入

1.全量导入mysql表数据到HDFS

bin/sqoop import \
--connect jdbc:mysql://node01:3306/test \
--username root \
--password 123456 \
--delete-target-dir \
--target-dir /sqoopresult \
--table emp --m 1

其中：
\：为linux系统中的换行符
bin/sqoop import：导入数据的固定写法
选项：
--connect：目标数据库地址
--username：访问数据库的用户名
--password：用户名密码
--delete-target-dir：当目标目录存在时删除
--target-dir：目标目录
--table：为mysql中的表
--m：maptask数量

2.全表导入mysql表数据到HIVE

方式1：先复制表结构到hive中再导入数据

将关系型数据库的表结构复制到hive中：

bin/sqoop create-hive-table \
--connect jdbc:mysql://node01:3306/test \
--table emp_add \
--username root \
--password 123456 \
--hive-table test.emp_add_sp
其中：
 --table：指定mysql中的表  
 --hive-table：指定在hive中新建的表名

从关系数据库导入文件到hive中

bin/sqoop import \
--connect jdbc:mysql://node02:3306/test \
--username root \
--password 123456 \
--table emp \
--hive-table myhive.hive_emp \
--hive-import \
--m 1

其中：
--hive-table：指定hive目标表
--hive-import：将表导入Hive中

方式2：直接复制表结构数据到hive中

导入机制：将数据导入到临时目录下并建表,再将数据移动到表对应的数据目录下

bin/sqoop import \
--connect jdbc:mysql://node01:3306/test \
--username root \
--password 123456 \
--table emp \
--hive-import \
--m 1 \
--hive-database myhive;
 
 其中：
 --hive-database：目标数据库

3.导入表数据子集

bin/sqoop import \
--connect jdbc:mysql://node01:3306/test \
--username root \
--password 123456 \
--where "city ='sec-bad'" \
--target-dir /wherequery \
--table emp_add --m 1

其中：
--where：可以指定从关系数据库导入数据时的查询条件，它执行在数据库服务器相应的SQL查询，并将结果存储在HDFS的目标目录。
city ='sec-bad'：查询字段为city，字段值为sec—bad的数据

4.导入表数据子集(query查询)

bin/sqoop import \
--connect jdbc:mysql://node01:3306/test \
--username root \
--password 123456 \
--target-dir /wherequery2 \
--query 'select id,name,deg from emp WHERE  id>1203 and $CONDITIONS' \
--split-by id \
--fields-terminated-by '\t' \
--m 2

其中w：
1.使用query sql语句来进行查找不能加参数--table;
2.并且必须要添加where条件;
3.并且where条件后面要加and $CONDITIONS;
4.并且这个sql语句必须用单引号，不能用双引号;
5.sqoop命令中，--split-by id通常配合-m 10参数使用。用于指定根据哪个字段进行划分并启动多少个maptask。

5.增量导入

在实际工作当中，数据的导入，很多时候都是只需要导入增量数据即可，并不需要将表中的数据每次都全部导入到hive或者hdfs当中去，这样会造成数据重复的问题。因此一般都是选用一些字段进行增量的导入， sqoop支持增量的导入数据。

增量导入是仅导入新添加的表中的行的技术。

其中：
–check-column (col)：
用来指定一些列，这些列在增量导入时用来检查这些数据是否作为增量数据进行导入，和关系型数据库中的自增字段及时间戳类似。
注：注意:这些被指定的列的类型不能使任意字符类型，如char、varchar等类型都是不可以的，同时-- check-column可以去指定多个列。

–incremental (mode)：
append：追加，比如对大于last-value指定的值之后的记录进行追加导入。
lastmodified：最后的修改时间，追加last-value指定的日期之后的记录

–last-value (value)：
指定自从上次导入后列的最大值（大于该指定的值），也可以自己设定某一值

a.Append模式增量导入

执行以下指令先将我们之前的数据导入：

bin/sqoop import \
--connect jdbc:mysql://node01:3306/test \
--username root \
--password 123456 \
--target-dir /appendresult \
--table emp --m 1

使用hadoop fs -cat查看生成的数据文件，发现数据已经导入到hdfs中。
然后在mysql的emp中插入2条增量数据:

insert into `emp` (`id`, `name`, `deg`, `salary`, `dept`) values ('1206', 'allen', 'admin', '30000', 'tp');
insert into `emp` (`id`, `name`, `deg`, `salary`, `dept`) values ('1207', 'woon', 'admin', '40000', 'tp');

执行如下的指令，实现增量的导入:

bin/sqoop import \
--connect jdbc:mysql://node01:3306/test \
--username root  --password 123456 \
--table emp --m 1 \
--target-dir /appendresult \
--incremental append \
--check-column id \
--last-value  1205

其中：
--incremental append \
--check-column id \
--last-value  1205
三者同时使用：指定id=1205为指标，将id大于1205的行数据导入到/appendresult目录中

b.Lastmodified模式增量导入

首先创建一个customer表，指定一个时间戳字段：

create table customertest(id int,name varchar(20),last_mod timestamp default current_timestamp on update current_timestamp);

注：此处的时间戳设置为在数据的产生和更新时都会发生改变

分别插入如下记录:

insert into customertest(id,name) values(1,'neil');
insert into customertest(id,name) values(2,'jack');
insert into customertest(id,name) values(3,'martin');
insert into customertest(id,name) values(4,'tony');
insert into customertest(id,name) values(5,'eric');

执行sqoop指令将数据全部导入hdfs:

bin/sqoop import \
--connect jdbc:mysql://node01:3306/test \
--username root \
--password 123456 \
--target-dir /lastmodifiedresult \
--table customertest --m 1

bin/sqoop import \
--connect jdbc:mysql://node01:3306/test \
--username root \
--password 123456 \
--table customertest \
--target-dir /lastmodifiedresult \
--check-column last_mod \
--incremental lastmodified \
--last-value "2019-05-28 18:42:06" \
--m 1 \
--append

注：lastmodified模式去处理增量时，会将大于等于last-value值的数据当做增量插入。

c.Lastmodified模式:append、merge-key

使用lastmodified模式进行增量处理要指定增量数据是以append模式(附加)还是merge-key(合并)模式添加

下面演示使用merge-by的模式进行增量更新,我们去更新 id为1的name字段:

update customertest set name = 'Neil' where id = 1;

更新之后，这条数据的时间戳会更新为更新数据时的系统时间.
执行如下指令，把id字段作为merge-key:

bin/sqoop import \
--connect jdbc:mysql://node01:3306/test \
--username root \
--password 123456 \
--table customertest \
--target-dir /lastmodifiedresult \
--check-column last_mod \
--incremental lastmodified \
--last-value "2019-05-28 18:42:06" \
--m 1 \
--merge-key id

由于merge-key模式是进行了一次完整的mapreduce操作，
因此最终我们在lastmodifiedresult文件夹下可以看到生成的为part-r-00000这样的文件，会发现id=1的name已经得到修改，同时新增了id=6的数据。

导出

1．默认模式导出HDFS数据到mysql

默认情况下，sqoop export将每行输入记录转换成一条INSERT语句，添加到目标数据库表中。如果数据库中的表具有约束条件（例如，其值必须唯一的主键列）并且已有数据存在，则必须注意避免插入违反这些约束条件的记录。如果INSERT语句失败，导出过程将失败。此模式主要用于将记录导出到可以接收这些结果的空表中。通常用于全表数据导出。

导出时可以是将Hive表中的全部记录或者HDFS数据（可以是全部字段也可以部分字段）导出到Mysql目标表。

a. 在HDFS文件系统中“/emp/”目录的下创建一个文件emp_data.txt：

1201,gopal,manager,50000,TP
1202,manisha,preader,50000,TP
1203,kalil,php dev,30000,AC
1204,prasanth,php dev,30000,AC
1205,kranthi,admin,20000,TP
1206,satishp,grpdes,20000,GR

b.手动创建mysql中的目标表

CREATE TABLE employee (
   id INT NOT NULL PRIMARY KEY,
   name VARCHAR(20), 
   deg VARCHAR(20),
   salary INT,
   dept VARCHAR(10));

c.执行导出命令

bin/sqoop export \
--connect jdbc:mysql://node01:3306/test \
--username root \
--password 123456 \
--table employee \
--export-dir /emp/emp_data

注：
--export-dir：导出文本数据的目录

2.更新导出(updateonly)

a.准备HDFS数据

在HDFS “/updateonly_1/”目录的下创建一个文件updateonly_1.txt：
1201,gopal,manager,50000
1202,manisha,preader,50000
1203,kalil,php dev,30000

b.手动创建mysql中的目标表

USE test;
CREATE TABLE updateonly ( 
   id INT NOT NULL PRIMARY KEY, 
   name VARCHAR(20), 
   deg VARCHAR(20),
   salary INT);

c.先执行全部导出操作

bin/sqoop export \
--connect jdbc:mysql://node01:3306/test \
--username root \
--password 123456 \
--table updateonly \
--export-dir /updateonly_1/

d.查看此时mysql中的数据

e.新增一个文件

updateonly_2.txt。修改了前三条数据并且新增了一条记录。上传至/updateonly_2/目录下：
1201,gopal,manager,1212
1202,manisha,preader,1313
1203,kalil,php dev,1414
1204,allen,java,1515

f.执行更新导出

bin/sqoop export \
--connect jdbc:mysql://node01:3306/test \
--username root \
--password 123456 \
--table updateonly \
--export-dir /updateonly_2/ \
--update-key id \
--update-mode updateonly

注：
-- update-key：更新标识，即根据某个字段进行更新，例如id，可以指定多个更新标识的字段，多个字段之间用逗号分隔。
--update-mode：指定updateonly（默认模式），仅仅更新已存在的数据记录，不会插入新纪录。

g.查看最终结果

3.更新导出（allowinsert模式）

a.准备HDFS数据

在HDFS “/allowinsert_1/”目录的下创建一个文件allowinsert_1.txt：
1201,gopal,manager,50000
1202,manisha,preader,50000
1203,kalil,php dev,30000

b.手动创建mysql中的目标表

USE userdb;
 CREATE TABLE allowinsert ( 
   id INT NOT NULL PRIMARY KEY, 
   name VARCHAR(20), 
   deg VARCHAR(20),
   salary INT);

c.先执行全部导出操作

bin/sqoop export \
--connect jdbc:mysql://node01:3306/test \
--username root \
--password 123456 \
--table allowinsert \
--export-dir /allowinsert_1/

d.查看此时mysql中的数据

e.新增一个文件

allowinsert_2.txt。修改了前三条数据并且新增了一条记录。上传至/ allowinsert_2/目录下：
1201,gopal,manager,1212
1202,manisha,preader,1313
1203,kalil,php dev,1414
1204,allen,java,1515

f.执行更新导出

bin/sqoop export \
--connect jdbc:mysql://node01:3306/test \
--username root \
--password 123456 \
--table allowinsert \
--export-dir /allowinsert_2/ \
--update-key id \
--update-mode allowinsert

-- update-key：更新标识，即根据某个字段进行更新，例如id，可以指定多个更新标识的字段，多个字段之间用逗号分隔。

-- updatemod：指定allowinsert，更新已存在的数据记录，同时插入新纪录。实质上是一个insert & update的操作。

g.查看最终结果

Sqoop job作业

1.创建job

创建一个名为czxyjob的作业,该作业可以从RDBMS表的数据导入到HDFS：

bin/sqoop job \
--create czxyjob \
-- import \
--connect jdbc:mysql://node02:3306/test \
--username root \
--password 123456 \
--target-dir /sqoopresult333 \
--table emp \
--m 1

2.验证job

–list 参数是用来验证保存的作业。
下面的命令用来验证保存Sqoop作业的列表：

bin/sqoop job --list

3.检查job

–show 参数用于检查或验证特定的工作，及其详细信息。
以下命令和样本输出用来验证一个名为czxyjob的作业：

bin/sqoop job --show czxyjob

4.执行job

–exec 参数选项用于执行保存的作业。
下面的命令用于执行保存的作业称为czxyjob：

bin/sqoop job --exec czxyjob

5.免密执行job

sqoop在创建job时，使用–password-file参数，可以避免输入mysql密码，如果使用–password将出现警告，并且每次都要手动输入密码才能执行job，sqoop规定密码文件必须存放在HDFS上，并且权限必须是400。
并且检查sqoop的sqoop-site.xml是否存在如下配置：

<property>
    <name>sqoop.metastore.client.record.password</name>
    <value>true</value>
    <description>If true, allow saved passwords in the metastore.
    </description>
</property>

1.将密码保存在绝对路径为/input/sqoop/pwd/czxymysql.pwd目录中并上传到HDFS中：

内容：
123456

2.创建job实现免密执行job

bin/sqoop job \
--create czxyjob1 \
-- import \
--connect jdbc:mysql://node01:3306/test \
--username root \
--password-file /input/sqoop/pwd/czxymysql.pwd \
--target-dir /sqoopresult333 \
--table emp \
--m 1