sqoop的基本使用

提示: 因为格式问题, 里面的所有的"–"需要自行更改格式为"--"

介绍:
sqoop是一种hadoop和 rdbms之间的数据传输的工具 , 其实就是关系形数据库和hdfs的传输转换.

工作机制:
Sqoop 工作机制是将导入或导出命令翻译成 mapreduce 程序来实现。在翻译出的 mapreduce 中主要是对 inputformat 和 outputformat 进行定制。

sqoop安装与部署

下载安装包: 目前最稳定版 1.4.6版本

进行解压之后,为了容易记录名称,可以更改一下名称

mv sqoop-1.4.6.bin__hadoop-2.0.4-alpha/ sqoop

进入到conf里, 将模板配置进行更名,然后修改

mv sqoop-env-template.sh sqoop-env.sh
vim sqoop-env.sh
更改需要使用的配置信息路径

为了验证能够正常的使用,(mysql测试)

• 先将mysql的驱动包安装到 sqoop的lib下

测试:(查询显示mysql的数据库)

bin/sqoop list-databases
–connect jdbc:mysql://hadoop203:3306/
–username root --password 0000

如果能正常显示库名, 那就表示可以了!!!

全量导入数据:(导入到hdfs上)😃

bin/sqoop import \
–connect jdbc:mysql://hadoop203:3306/userdb
–username root
–password 0000
–delete-target-dir
–target-dir /sqoop/sqoopresult1
–table emp
–m 1

• –m 可以指定map的并行度,当数据过大时, 默认采用该表的主键进行切片,如果没有主键则会报错, 需要指定对特定字段进行切片--split-by 字段名
• --fields-terminated-by '\t'可以指定数据之间的分隔符,默认是","号
• --delete-target-dir : 如果文件存在,则会先删除在导入

子集过滤:

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

也可以使用--where "id = '1002'"这个参数来进行过滤

使用query过滤限制:

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

从MySQL导入到hive

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

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

bin/sqoop create-hive-table
–connect jdbc:mysql://node-1:3306/sqoopdb
–table emp_add
–username root
–password hadoop
–hive-table test.emp_add_sp

其中：

–table emp_add 为 mysql 中的数据库 sqoopdb 中的表。
–hive-table emp_add_sp 为 hive 中新建的表名称。

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

bin/sqoop import
–connect jdbc:mysql://node-1:3306/sqoopdb
–username root
–password hadoop
–table emp_add
–hive-table test.emp_add_sp
–hive-import
–m 1

方式二:直接导入数据和复制表结构

bin/sqoop import
–connect jdbc:mysql://node-1:3306/userdb
–username root
–password hadoop
–table emp_conn
–hive-import \ --m 1
–hive-database test;

错误:Hive exited with status 64 未解决

增量数据的导入:

参数介绍:
–check-column (col)

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

–incremental (mode)

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

–last-value (value)

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

mysql创建时间戳表:

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

无论是新数据,还是对原数据进行修改都会更新时间戳

示例:

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

== 注意点:==

• 增量数据的导入

• 所谓的增量数据指的是上次至今中间新增加的数据

• sqoop支持两种模式的增量导入

  • append追加 根据数值类型字段进行追加导入 大于指定的last-value
  • lastmodified 根据时间戳类型字段进行追加 大于等于指定的last-value
    • 注意在lastmodified 模式下 还分为两种情形：append和merge-key

• 关于lastmodified 中的两种模式：

  • append 只会追加增量数据到一个新的文件中 并且会产生数据的重复问题

    因为默认是从指定的last-value 大于等于其值的数据开始导入

  • merge-key 把增量的数据合并到一个文件中 除了追加增量数据之外 如果之前的数据有变化修改 也可以进行修改操作 底层相当于进行了一次完整的mr作业。数据不会重复。

–merge-key id # 对id进行去重

---

数据的导出:

前提:RDBMS 数据库中表存在

bin/sqoop export
–connect jdbc:mysql://hadoop203:3306/userdb \
–username root
–password 0000
–table employee \ #导出到的表
–export-dir /export #导出的元数据路径

相关参数配置:
--input-fields-terminated-by '\t': 指定文件中的分隔符,默认是 ","号
--columns :如果数据的列和 MySQL中列不对应,需要指定column的顺序
--export-dir: 导出的数据的路径
--table : 导出到的表的名字
--input-null-string "\\N" : 如果字符串数据为空, 转换成空值,而不是NULL
--input-null-non-string "\\N":如果非字符串数据为空, 转换成空值,而不是NULL
--update-mode 模式: 修改导出的模式

---

导出的三种模式:

1. insert:　默认就是这个模式，将数据直接使用insert 插入进来
2. updateonly : 根据--update-key id进行修改,只对修改的数据有效,新插入的数据无效
3. allowinsert: 既修改又插入,对新数据进行插入,对旧数据进行修改

---

job作业

创建作业:

bin/sqoop job --create itcastjob1 -- import 
--connect jdbc:mysql:/hadoop202:3306/userdb \
--username root \
--password 0000 \
--target-dir /sqoop \
--table emp --m 1

注意import前要有空格

显示作业

bin/sqoop job --list

检查作业

bin/sqoop job --show myjob

执行作业:

bin/sqoop job --exec myjob
注: 没有配置免密job的话是要进行输入mysql密码的

job免密登录
1.先在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>

2.sqoop规定密码文件必须存放在HDFS上，并且权限必须是400。

echo -n “0000” > itcastmysql.pwd
hadoop fs -mkdir -p /input/sqoop/pwd/ # hdfs上创建固定文件夹
hadoop fs -put itcastmysql.pwd /input/sqoop/pwd/ #进行上传
hadoop fs -chmod 400 /input/sqoop/pwd/itcastmysql.pwd #修改权限为400

如果password文件格式错误会有如下提示：
ERROR manager.SqlManager: Error executing statement: java.sql.SQLException: Access denied for user ‘root’@‘spark220’ (using password: YES)
或者
ERROR tool.ImportTool: Encountered IOException running import job: java.io.IOException: No columns to generate for ClassWriter at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1652)