我想看看Sqoop

Sqoop 简介

1. Sqoop是什么

​ Sqoop（sql to hadoop）是apache旗下的一款 ”Hadoop和关系数据库之间传输数据”的工具，它可以将Mysql、Oracle数据导入到HDFS、Hive、HBase等数据存储系统，也可以从Hadoop文件系统中到处数据到关系型数据库。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ICKKbG6E-1590070520349)(Sqoop 简介.assets/1590067412280.png)]

2. Sqoop的工作机制

​ 它的工作机制是将sqoop的指定的指令转换为mapreduce程序实现的

3. 基本架构

​ sqoop在发展的过程中演进出了两种不同的架构

3.1 sqoop1

1. 版本号为1.4.x为sqoop1
2. 在架构上：sqoop1使用sqoop客户端直接提交的方式
3. 访问方式：CLI控制台方式进行访问
4. 安全性：命令或脚本中指定用户数据库名及密码

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-e8ENTO6r-1590070520351)(Sqoop 简介.assets/1590068110562.png)]

3.2 sqoop2

1. 版本号为1.99x为sqoop2

2. 在架构上：sqoop2引入了sqoop server，对connector实现了集中的管理

3. 访问方式：REST API、 JAVA API、 WEB UI以及CLI控制台方式进行访问

   [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6iS7hVli-1590070520352)(Sqoop 简介.assets/1590068088315.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-R0jEgsDy-1590070520354)(Sqoop 简介.assets/1590068136439.png)]

4. 安装部署

这个可以百度，比较简单。

5. 基本的使用

5.1 常用命令

• 命令行查看帮助文档

  sqoop list-databases --help
  

• 列出nodeX上mysql数据库中所有的数据库名称

  sqoop list-databases --connect jdbc:mysql://nodeX:3306/ --username root --password 123456
  

• 查看某一个数据库下面的所有数据表

  sqoop list-tables --connect jdbc:mysql://nodeX:3306/ --username root --password 123456
  

5.2 导入数据

5.2.1 导入数据

• 在MySQL数据库服务器中创建一个数据库userdb, 然后在创建一张表 emp，添加点测试数据到表中

• 从MySQL数据库服务器中的userdb数据库下的emp表导入HDFS上

  sqoop import --connect jdbc:mysql://nodeX:3306/userdb --username root   --password 123456 --table emp --m 1
  

• 提交之后，会运行一个MR程序，最后查看HDFS上的目录看是否有数据生成

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KS15eeHs-1590070520356)(Sqoop 简介.assets/1590068506397.png)]

5.2.2 导入数据库表数据到HDFS指定目录

• 在导入表数据到HDFS使用Sqoop导入工具，我们可以指定目标目录。

• 使用参数 ==–target-dir==来指定导出目的地，

• 使用参数==–delete-target-dir==来判断导出目录是否存在，如果存在就删掉

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-buWyVjhI-1590070520357)(Sqoop 简介.assets/1590068594092.png)]

5.2.3 导入数据库表数据到HDFS指定目录并且指定数据字段的分隔符

• 这里使用参数

  • –fields-terminated-by 分隔符

  sqoop import  --connect jdbc:mysql://node1:3306/userdb --username root --password 123456 --delete-target-dir --table emp  --target-dir /sqoop/emp1 --fields-terminated-by '#' --m 1
  

• 提交查看HDFS上的目录看是否有数据生成

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rDklB6IW-1590070520359)(Sqoop 简介.assets/1590068708722.png)]

5.2.4 导入关系表到Hive中

• (1) 将我们mysql表当中的数据直接导入到hive表中的话，需要将hive的一个叫做hive-exec-1.2.2.jar包拷贝到sqoop的lib目录下

  cp /opt/bigdata/hive/lib/hive-exec-1.2.2.jar /opt/bigdata/sqoop/lib/
  

• (2) 准备hive数据库与表

  • 在hive中创建一个数据库和表

    create database sqooptohive;
    
    create external table sqooptohive.emp_hive(id int,name string,deg string,salary double ,dept string) row format delimited fields terminated by '\001';
    

• (3) 把mysql表数据导入到hive表中

  sqoop import --connect jdbc:mysql://node1:3306/userdb --username root --password 123456 --table emp --fields-terminated-by '\001' --hive-import --hive-table sqooptohive.emp_hive --hive-overwrite  --m 1
  

• (4) 执行完成了查看hive中表的数据

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UistotYB-1590070520360)(Sqoop 简介.assets/1590068881516.png)]

5.2.5 导入数据库表数据到hive中(并自动创建hive表)

sqoop import --connect jdbc:mysql://node1:3306/userdb --username root --password 123456 --hive-database sqooptohive --hive-table emp1 --table emp --hive-import -m 1 ;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OXplBb03-1590070520361)(Sqoop 简介.assets/1590068968132.png)]

5.2.6 sql语句查找导入hdfs

sqoop import \--connect jdbc:mysql://node1:3306/userdb \--username root --password 123456 --table emp \--target-dir /sqoop/emp_where -m 1  --delete-target-dir \--where "dept = 'TP'"

5.2.7 导入数据库表数据到hive中(并自动创建hive表)

sqoop import \
--connect jdbc:mysql://node1:3306/userdb \
--username root --password 123456 \
--target-dir /sqoop/emp_sql -m 1  --delete-target-dir \
--query 'select * from emp where salary >30000 and $CONDITIONS' 

5.2.8 增量导入

在实际工作当中，数据的导入很多时候都是只需要导入增量数据即可，并不需要将表中的数据全部导入到hive或者hdfs当中去，肯定会出现重复的数据的状况，所以我们一般都是选用一些字段进行增量的导入，为了支持增量的导入，sqoop也给我们考虑到了这种情况并且支持增量的导入数据

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

• 需要添加 ==‘incremental’, ‘check-column’, 和 ‘last-value’==选项来执行增量导入。

  --incremental <mode>
  --check-column <column name>
  --last value <last check column value>
  

• 第一种增量导入实现

  • 基于递增列的增量数据导入（Append方式）

    • 注意：这里不能加上 --delete-target-dir 参数，添加就报错

    sqoop import \
    --connect jdbc:mysql://node1:3306/userdb \
    --username root \
    --password 123456 \
    --table emp \
    --incremental append \
    --check-column id \
    --last-value 1202  \
    -m 1 \
    --target-dir /sqoop/increment1 
    

• 第二种增量导入实现

  • 基于时间列的增量数据导入（LastModified方式）

    • 此方式要求原有表中有time字段，它能指定一个时间戳

      sqoop import \
      --connect jdbc:mysql://node1:3306/userdb \
      --username root \
      --password 123456  \
      --table user \
      --target-dir /sqoop/incement2 \
      --incremental lastmodified  \
      --check-column createTime  \
      --last-value '2019-09-01 10:16:00'  \
      --m 1
      

5.3 导出数据

• 将数据从HDFS把文件导出到RDBMS数据库
  • 导出前，目标表必须存在于目标数据库中。
    • 默认操作是从将文件中的数据使用INSERT语句插入到表中
    • 更新模式下，是生成UPDATE语句更新表数据

sqoop export \
--connect jdbc:mysql://node1:3306/userdb \
--username root --password 123456 \
--table fromhdfs \
--input-fields-terminated-by " " \
--export-dir /user/hive/warehouse/hive_source 

6. 其他使用

• Sqoop作业

  • 将事先定义好的数据导入导出任务按照指定流程运行

  • 语法

    sqoop job (generic-args) (job-args)
       [-- [subtool-name] (subtool-args)]
    

  • 创建作业

  sqoop job \
  --create myjob \
  -- import \
  --connect jdbc:mysql://node1:3306/userdb \
  --username root \
  --password 123456 \
  --table emp \
  --target-dir /sqoop/myjob \
  --delete-target-dir \
  --m 1
  

  • 验证作业

  sqoop job --list
  

  • 查看作业

    sqoop job --show myjob
    

  • 执行作业

    • 用于执行保存的作业

      sqoop job --exec myjob
      

    • 解决sqoop需要输入密码的问题

      • vi /opt/bigdata/sqoop/conf/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>
    

  • 删除作业

  • 用于删除保存作业

    sqoop job --delete myjob
    

7.扩展

7.1 Sqoop导入导出Null存储一致性问题

• • Hive中的Null在底层是以“\N”来存储，而MySQL中的Null在底层就是Null，为了保证数据两端的一致性。在导出数据时采用–input-null-string和–input-null-non-string两个参数。导入数据时采用–null-string和–null-non-string。

7.2 Sqoop导入时出现部分Map任务成功部分失败，解决方式：staging-table或者设置map数量为1个（不推荐）

sqoop export --connect jdbc:mysql://192.168.137.10:3306/user_behavior --username root --password 123456 --table app_cource_study_report --columns watch_video_cnt,complete_video_cnt,dt --fields-terminated-by "\t" --export-dir "/user/hive/warehouse/tmp.db/app_cource_study_analysis_${day}" --staging-table app_cource_study_report_tmp --clear-staging-table --input-null-string '\N'

7.3 Sqoop在导入数据的时候数据倾斜

sqoop 抽数的并行化主要涉及到两个参数：num-mappers：启动N个map来并行导入数据，默认4个；split-by：按照某一列来切分表的工作单元。

``