Sqoop基础 （二）导入，导出，创建job

全量将mysql数据导入到HDFS中。

bin/sqoop import \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 密码 \
--delete-target-dir \
--target-dir /sqoopresult \
--table emp --m 1

其中--target-dir 可以用来指定导出数据存放至HDFS的目录。

可以看出它会在 HDFS 上默认用逗号,分隔 emp 表的数据和字段。可以通过

--fields-terminated-by '\t'

来指定分隔符。

bin/sqoop import \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 密码 \
--delete-target-dir \
--fields-terminated-by '\t'
--target-dir /sqoopresult \
--table emp --m 1

如果表的数据比较大，可以启动并行多个maptask执行导入，如果表没有主键，请根据某个字段进行切分

bin/sqoop import \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 密码 \
--delete-target-dir \
--fields-terminated-by '\t'
--target-dir /sqoopresult \
--split-by id
--table emp --m 2

导入数据到hive

bin/sqoop import \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 密码 \
--table emp_conn \
--hive-import \
--m 1 \
--hive-database test;

--hive-database test 导入数据到hive的databases中test数据库。

导入表数据子集(where 过滤)

--where 可以指定从关系数据库导入数据时的查询条件,它执行在数据库服务器相应的 SQL 查询，并将结果存储在 HDFS 的目标目录。

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

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

注意事项：

• 使用 query sql 语句来进行查找不能加参数--table ;
• 并且必须要添加 where 条件;
• 并且 where 条件后面必须带一个$CONDITIONS 这个字符串;
• 并且这个 sql 语句必须用单引号，不能用双引号;

bin/sqoop import \
--connect jdbc:mysql://node-1:3306/userdb \
--username root \
--password 密码 \
--target-dir /wherequery12 \
--query 'select id,name,deg from emp WHERE
--split-by id \
--fields-terminated-by '\t' \
id>1203 and $CONDITIONS' \
--m 2

sqoop 命令中，--split-by id 通常配合-m 10 参数使用。用于指定根据哪个字段进行划分并启动多少个 maptask。

增量导入

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

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

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

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

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

1. Append 模式增量导入

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

--check-column id
--last-value 1205

这两行的意思就是检查id列，定义上一次更新到哪里，我们就从这个数的下一个开始导入。

Lastmodified 模式增量导入

这种模式的增量导入一般都是带有时间戳形式的。

所以使用 incremental 的方式进行增量的导入:

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

但是，这种形式的导入会将last-value "2019-05-28 18:42:06"的这条信息重复的导入，为了避免数据的重复导入，我们应该稍微修改这条数据，保证大于等于时间为2019-05-28 18:42:06的数据。比如我们可以将18：42：06改为18:42:07。

Lastmodified 模式:append、merge-key

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

bin/sqoop import \
--connect jdbc:mysql://node-1:3306/userdb \
--username root \
--password hadoop \
--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 id可以将表中修改的值和增量的值同时赋值并且更新到一张表中，这里不同于--append是将增量数据更新到新的一张表中。这种相对于更加的合理。

Sqoop 导出

将数据从 Hadoop 生态体系导出到 RDBMS 数据库导出前，目标表必须存在于目标数据库中。

export 有三种模式：

• 默认操作是从将文件中的数据使用 INSERT 语句插入到表中。
• 更新模式：Sqoop 将生成 UPDATE 替换数据库中现有记录的语句。
• 调用模式：Sqoop 将为每条记录创建一个存储过程调用。

默认模式导出 HDFS 数据到 mysql

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

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

准备 HDFS 数据

在 HDFS 文件系统中“/input/sqoop”目录的下创建一个文件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

手动创建 mysql 中的目标表

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

执行导出命令

bin/sqoop export \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 88116142 \
--table employee \
--export-dir /input/sqoop

相关配置参数

指定文件中的分隔符

--input-fields-terminated-by '\t'

指定每行的字段名称

选择列并控制它们的排序。当导出数据文件和目标表字段列顺序完全一 致的时候可以不写。否则以逗号为间隔选择和排列各个列。没有被包含在columns 后面列名或字段要么具备默认值，要么就允许插入空值。否则数据 库会拒绝接受 sqoop 导出的数据，导致 Sqoop 作业失败

--columns

bin/sqoop export \
--connect jdbc:mysql://node03:3306/userdb \
--username root \
--password 88116142 \
--table employee \
--columns id,salary,dev,
--export-dir /input/sqoop

导出目录
在执行导出的时候，必须指定这个参数，同时需要具 备--table 或--call 参数两者之一，--table 是指的导出数据库当中对应的表，--call 是指的某个存储过程。

--export-dir

指定空
如果没有指定第一个参数，对于字符串类型的列来说，“NULL”这个字符 串就回被翻译成空值，如果没有使用第二个参数，无论是“NULL”字符串还 是说空字符串也好，对于非字符串类型的字段来说，这两个类型的空串都会 被翻译成空值。比如：

--input-null-string --input-null-non-string

--input-null-string "\\N" --input-null-non-string "\\N"

更新导出（updateonly 模式）

参数说明

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

--updatemod，指定 updateonly（默认模式），仅仅更新已存在的数据记录， 不会插入新纪录。

举例：

bin/sqoop export \
--connect jdbc:mysql://node-1:3306/userdb \
--username root --password hadoop \
--table updateonly \
--export-dir /updateonly_2/ \
--update-key id \
--update-mode updateonly

更新导出（allowinsert 模式）

参数说明

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

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

bin/sqoop export \
--connect jdbc:mysql://node-1:3306/userdb \
--username root --password hadoop \
--table allowinsert \
--export-dir /allowinsert_2/ \
--update-key id \
--update-mode allowinsert

这种导入不仅会更新已经更新的数据，还会导入没有的数据，比updateonly更加全面

Sqoop job 作业

创建 job

在这里，我们创建一个名为leonjob，这可以从 RDBMS 表的数据导入到HDFS 作业。

bin/sqoop job --create leonjob -- import --connect jdbc:mysql://node03:3306/userdb \
--username root \
--password hadoop \
--target-dir /sqoopresult333 \
--table emp --m 1

注意 import 前要有空格

验证 job

bin/sqoop job --list

检查 job

bin/sqoop job --show leonjob

执行 job

bin/sqoop job --exec leonjob

免密执行 job

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

然后新建一个.pwd结尾的配置文件，写入密码

echo -n "密码" > leon.pwd
hdfs dfs -mkdir -p /input/sqoop/pwd/
hdfs dfs -put leon.pwd /input/sqoop/pwd
hdfs dfs -chmod 400 /input/sqoop/pwd/leon.pwd

并且检查 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>

创建job

bin/sqoop job --create leonjob1 -- import --connect jdbc:mysql://cdh-1:3306/userdb \
--username root \
--password-file /input/sqoop/pwd/leon.pwd \
--target-dir /sqoopresult333 \
--table emp --m 1

执行job

bin/sqoop job --exec leonjob1