一、Sqoop介绍

• Sqoop介绍(数据采集工具)

sqoop是apache旗下一款“Hadoop和关系数据库服务器之间传送数据”的工具。
导入数据：MySQL，Oracle导入数据到Hadoop的HDFS、HIVE、HBASE等数据存储系统；
导出数据：从Hadoop的HDFS、HIVE中导出数据到关系数据库mysql等。

二、Sqoop工作机制

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

• 为什么转化为mr任务后再导入导出

hdfs 是以文件的形式导入导出数据, 但是hdfs中文件内容的格式和其他关系型数据库不一致
如果我们想将其转换为合适的数据格式,需要在内存中进行转换
我们可以在hdfs中使用mr进行转换也可使用sqoop读取后再内存中 转换
mr处理大规模数据的能力更强于hdfs,兼容性更好,所以我们将数据转换为mr任务后导入或导出数据

三、Sqoop常用相关语法

• 测试

# 查看sqoop的帮助命令
sqoop help
# 使用sqoop 查看mysql中全部的数据库
# 参数1 --connect 设置连接路径
# 参数2 --username 用户名
# 参数3 --password
sqoop  list-databases --connect jdbc:mysql://hadoop01:3306 --username root --password 123456

# 如果数据过长我们会进行换行, 但是换行位置需要加\ 这个符号叫做未完待续符
sqoop  list-databases \
--connect jdbc:mysql://hadoop01:3306 \
--username root \
--password 123456

# 注意: 在\后方不能添加任何字符包括注释和空格

# 扩展: 如何获取指定库中所有的表呢?
sqoop  list-tables \
--connect jdbc:mysql://hadoop01:3306/hue \
--username root \
--password 123456

• 使用Sqoop将MySQL数据导入至HDFS

  • 方式一：不指定导入位置

    sqoop import \
    --connect jdbc:mysql://hadoop01:3306/userdb \
    --username root \
    --password 123456 \
    --table emp
    

    生成一个默认文件位置,在/user/用户名/表名目录下存放数据
    

  • 方式二：指定位置

    sqoop import \
    --connect jdbc:mysql://hadoop01:3306/userdb \
    --username root \
    --password 123456 \
    --delete-target-dir \
    --table emp \
    --target-dir /test/emp2
    

  • 方式三：设置字段之间的分割符

    sqoop中默认的分隔符是逗号

    sqoop import \
    --connect jdbc:mysql://hadoop01:3306/userdb \
    --username root \
    --password 123456 \
    --delete-target-dir \
    --table emp \
    --target-dir /test/emp2 \
    --fields-terminated-by '|'
    

  • 方式四：将所有内容放到一个文件中

    sqoop import \
    --connect jdbc:mysql://hadoop01:3306/userdb \
    --username root \
    --password 123456 \
    --delete-target-dir \
    --table emp \
    --target-dir /test/emp2 \
    --fields-terminated-by '|' \
    -m 1
    

• 使用Sqoop将MySQL数据导入至Hive

  sqoop import \
  --connect jdbc:mysql://hadoop01:3306/userdb \
  --username root \
  --password 123456 \
  --table emp_add \
  --hcatalog-database db_1 \
  --hcatalog-table emp_add \
  --fields-terminated-by '\t' \
  -m 1
  

  注意: 由于hive表的存储格式为orc,所以无法使用sqoop的原生导入方式,必须使用hcatalog

  hcatalog 方式导入数据可以导入任意压缩或存储格式的数据
  而使用原生方式只能使用textfile形式进行导入所以在开发中hcatalog是通用方法.

• sqoop中的where条件过滤

  • 以 emp为例, 在这个表中, 新增一条数据, 尝试将这一条数据导入到HDFS中

    INSERT INTO  userdb.emp VALUES(1206,'zhangsan','bigdata dev','30000','TP');
    

  • 通过sqoop实现条件插入导入

    sqoop import \
    --connect jdbc:mysql://hadoop01:3306/userdb \
    --username root \
    --password 123456 \
    --delete-target-dir \
    --table emp \
    --where 'id > 1205' \
    --target-dir /test/emp3 \
    --fields-terminated-by '|' \
    -m 1
    

  注意: where筛选只能进行行过滤,不能进行列过滤

• 使用sql查询向hdfs中导入数据(重要)

  • 以 emp为例, 在这个表中, 新增一条数据, 尝试将这一条数据导入到HDFS中

    INSERT INTO  userdb.emp VALUES(1207,'lisi','bigdata dev','50000','TP');
    

  • 使用sqoop条件导入数据: 采用 SQL形式

    sqoop import \
    --connect jdbc:mysql://hadoop01:3306/userdb \
     --username root \
     --password 123456 \
     --query "select id,name,deg from userdb.emp where id > 1206 and \$CONDITIONS;" \
     --target-dir '/test/emp5' \
     --delete-target-dir \
     --fields-terminated-by '\t' \
      -m 1
    

    1- 当使用 --query 方式的时候, 不允许在使用 --table, 因为 SQL中已经明确需要导入那个表的数据
    2- 当使用 --query 方式的时候, 编写的SQL语句必须添加where条件, 条件最后必须要跟$CONDITIONS, 如果使用双引号包裹SQL, $前面必须加一个 \ 进行转义操作, 当如果没有条件的时候,建议写成: where 1=1 and $CONDITIONS

• 使用sql查询向hive中导入数据(重要)

  • 先在mysql中, 新增一条数据:

       INSERT INTO  userdb.emp_add VALUES(1206,'412Q','aoc','sec-bad');
    

  • 使用sqoop完成导入操作

    sqoop import \
    --connect jdbc:mysql://hadoop01:3306/userdb \
     --username root \
    --password 123456 \
    --query "select * from userdb.emp_add where id=1206 and \$CONDITIONS;" \
    --hcatalog-database db_1 \
     --hcatalog-table emp_add \
     --fields-terminated-by '\t' \
      -m 1
    

    注意: 导入hive中不会覆盖原有表数据,而是进行了追加.在表目录中增加了一个新的数据文件.
    将数据导入到hdfs中和导入到hive中有什么区别?

    1. 导入hdfs中我们导入的是一个文本文件,而导入到hive表中,可以根据表的存储格式和压缩格式进行转换.
    2. 导入hdfs中需要我们自己手动指定文件存放路径,而导入到hive中会通过hive的库名,表名从元数据中提取出文件存放路径.
    3. 将数据导入到分区表中还可以通过动态分区的方式自动进行分区,在元数据中添加分区信息.

• 使用Sqoop将Hive数据导出至MySQL(全量数据导出)

  • 步骤一: 在MySQL中创建目标表

     CREATE TABLE userdb.tb_emp_add_hive2mysql (
    	id INT,
    	hno VARCHAR(20),
    	street VARCHAR(20),
    	city VARCHAR(20)
    );
    

  • 步骤二: 执行sqoop导出操作

    sqoop export \
    --connect jdbc:mysql://hadoop01:3306/userdb \
    --username root \
    --password 123456 \
    --table tb_emp_add_hive2mysql \
    --fields-terminated-by  '\t' \
    --hcatalog-database 'db_1' \
    --hcatalog-table 'emp_add' \
    -m 1
    

注意: 在执行数据导出的时候,必须先创建目标表,然后才能触发执行导出