第一部分 Sqoop概述

Sqoop是一款开源的工具，主要用于在Hadoop(Hive)与传统的数据库(mysql、 postgresql等)间进行数据的传递。可以将关系型数据库(MySQL,Oracle ,Postgres 等)中的数据导入到HDFS中，也可以将HDFS的数据导进到关系型数据库中。
Sqoop项目开始于2009年，最早是作为Hadoop的一个第三方模块存在，后来为了让使用者能够快速部署，也为了让开发人员能够更快速的迭代开发，Sqoop独立成为一个 Apache 项目。
image.png
将导入或导出命令转换为 MapReduce 程序来实现。翻译出的 MapReduce 中主要是对 inputformat 和outputformat 进行定制。

第二部分 安装配置

Sqoop 官网:http://sqoop.apache.org/
Sqoop下载地址:http://www.apache.org/dyn/closer.lua/sqoop/
1、下载、上传并解压
将下载的安装包 sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz 上传到虚拟机中;
解压缩软件包;

  1. tar zxvf sqoop-1.4.7.bin_hadoop-2.6.0.tar.gz
  2. mv sqoop-1.4.7.bin_hadoop-2.6.0/ ../servers/sqoop-1.4.7/

2、增加环境变量，并使其生效

  1. vi /etc/profile
  3. # 增加以下内容
  4. export SQOOP_HOME=/opt/lagou/servers/sqoop-1.4.7 
  5. export PATH=$PATH:$SQOOP_HOME/bin
  7. source /etc/profile

3、创建、修改配置文件

  1. # 配置文件位置 $SQOOP_HOME/conf;要修改的配置文件为 sqoop-env.sh 
  2. cp sqoop-env-template.sh sqoop-env.sh
  3. vi sqoop-env.sh
  4. # 在文件最后增加以下内容
  5. export HADOOP_COMMON_HOME=/opt/lagou/servers/hadoop-2.9.2 
  6. export HADOOP_MAPRED_HOME=/opt/lagou/servers/hadoop-2.9.2 
  7. export HIVE_HOME=/opt/lagou/servers/hive-2.3.7

4、拷贝JDBC驱动程序

  1. # 拷贝jdbc驱动到sqoop的lib目录下(备注:建立软链接也可以)
  2. ln -s /opt/lagou/servers/hive-2.3.7/lib/mysql-connector-java-5.1.46.jar /opt/lagou/servers/sqoop-1.4.7/lib/

5、拷贝 jar
将 $HIVE_HOME/lib 下的 hive-common-2.3.7.jar，拷贝到 $SQOOP_HOME/lib目录下。
如不拷贝在MySQL往Hive导数据的时候将会出现错误:ClassNotFoundException: org.apache.hadoop.hive.conf.HiveConf

  1. # 硬拷贝 和 建立软链接都可以，选择一个执行即可。下面是硬拷贝
  2. cp $HIVE_HOME/lib/hive-common-2.3.7.jar $SQOOP_HOME/lib/
  3. # 建立软链接
  4. ln -s /opt/lagou/servers/hive-2.3.7/lib/hive-common-2.3.7.jar /opt/lagou/servers/sqoop-1.4.7/lib/hive-common-2.3.7.jar

将 $HADOOP_HOME/share/hadoop/tools/lib/json-20170516.jar 拷贝到 $SQOOP_HOME/lib/ 目录下;
否则在创建sqoop job时会报: java.lang.NoClassDefFoundError: org/json/JSONObject

  1. cp $HADOOP_HOME/share/hadoop/tools/lib/json-20170516.jar $SQOOP_HOME/lib/

6、安装验证

  1. sqoop version 
  2. 省略了警告 ... ...
  3. 20/06/19 10:37:24 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
  4. Sqoop 1.4.7
  5. git commit id 2328971411f57f0cb683dfb79d19d4d19d185dd8
  6. Compiled by maugli on Thu Dec 21 15:59:58 STD 2017
  8. # 测试Sqoop是否能够成功连接数据库
  9. sqoop list-databases --connect jdbc:mysql://linux123:3306/?useSSL=false --username hive --password 12345678
  10. ... ...
  11. information_schema
  12. hivemetadata
  13. mysql
  14. performance_schema
  15. sys

第三部分 应用案例

在Sqoop中

  • 导入是指:从关系型数据库向大数据集群(HDFS、HIVE、HBASE)传输数据; 使用import关键字;
  • 导出是指:从 大数据集群向关系型数据库传输数据;使用export关键字;

测试数据脚本

  1. -- 用于在 Mysql 中生成测试数据 
  2. CREATE DATABASE sqoop;
  3. use sqoop;
  5. CREATE TABLE sqoop.goodtbl(
  6.   gname varchar(50),
  7.   serialNumber int,
  8.   price int,
  9.   stock_number int,
  10.   create_time date);
  11. DROP FUNCTION IF EXISTS `rand_string`;
  12. DROP PROCEDURE IF EXISTS `batchInsertTestData`;
  14. -- 替换语句默认的执行符号，将;替换成 // 
  15. DELIMITER //
  16. CREATE FUNCTION `rand_string` (n INT) RETURNS VARCHAR(255)
  17. CHARSET 'utf8'
  18. BEGIN
  19.     DECLARE char_str varchar(200) DEFAULT '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  20.     DECLARE return_str varchar(255) DEFAULT '';
  21.     DECLARE i INT DEFAULT 0;
  22.     WHILE i < n DO
  23.         SET return_str = concat(return_str, substring(char_str,FLOOR(1 + RAND()*36), 1));
  24.         SET i = i+1;
  25.     END WHILE;
  26.     RETURN return_str;
  27. END
  28. //
  29. -- 第一个参数表示:序号从几开始;第二个参数表示:插入多少条记录 
  30. CREATE PROCEDURE `batchInsertTestData` (m INT, n INT) BEGIN
  31.     DECLARE i INT DEFAULT 0;
  32.     WHILE i < n DO
  33.         insert into goodtbl (gname, serialNumber, price,stock_number, create_time)
  34.         values (rand_string(6), i+m, ROUND(RAND()*100),FLOOR(RAND()*100), now());
  35.         SET i = i+1;
  36.     END WHILE;
  37. END //
  38. delimiter ;
  40. -- 调用存储过程生成测试数据
  41. call batchInsertTestData(1, 100);

以下案例需要启动:HDFS、YARN、MySQL 对应的服务

第 1 节 导入数据

MySQL 到 HDFS

1、导入全部数据

  1. sqoop import \
  2. --connect jdbc:mysql://linux:3306/sqoop \
  3. --username hive \
  4. --password 12345678 \
  5. --table goodtbl \
  6. --target-dir /root/lagou \
  7. --delete-target-dir \
  8. --num-mappers 1 \
  9. --fields-terminated-by "\t"

备注:

  • target-dir:将数据导入 HDFS 的路径;
  • delete-target-dir:如果目标文件夹在 HDFS 上已经存在，那么再次运行就会报错。可以使用—delete-target-dir来先删除目录。也可以使用 append 参数，表示追加数据;
  • num-mappers:启动多少个Map Task;默认启动4个Map Task;也可以写成 -m 1
  • fields-terminated-by:HDFS文件中数据的分隔符;

2、导入查询数据

  1. sqoop import \
  2. --connect jdbc:mysql://linux123:3306/sqoop \
  3. --username hive \
  4. --password 12345678 \
  5. --target-dir /root/lagou \
  6. --append \
  7. -m 1 \
  8. --fields-terminated-by "\t" \
  9. --query 'select gname, serialNumber, price, stock_number,create_time from goodtbl where price>88 and $CONDITIONS;'

备注:

  • 查询语句的where子句中必须包含 ‘$CONDITIONS’
  • 如果query后使用的是双引号，则$CONDITIONS前必须加转移符，防止shell识别为自己的变量

3、导入指定的列

  1. sqoop import \
  2. --connect jdbc:mysql://linux123:3306/sqoop \
  3. --username hive \
  4. --password 12345678 \
  5. --target-dir /root/lagou \
  6. --delete-target-dir \
  7. --num-mappers 1 \
  8. --fields-terminated-by "\t" \
  9. --columns gname,serialNumber,price \
  10. --table goodtbl

备注:columns中如果涉及到多列，用逗号分隔，不能添加空格
4、导入查询数据(使用关键字)

  1. sqoop import \
  2. --connect jdbc:mysql://linux123:3306/sqoop \
  3. --username hive \
  4. --password 12345678 \
  5. --target-dir /root/lagou \
  6. --delete-target-dir \
  7. -m 1 \
  8. --fields-terminated-by "\t" \
  9. --table goodtbl \
  10. --where "price>=68"

5、启动多个Map Task导入数据
在 goodtbl 中增加数据:call batchInsertTestData(1000000);

  1. sqoop import \
  2. --connect jdbc:mysql://linux123:3306/sqoop \
  3. --username hive \
  4. --password 12345678 \
  5. --target-dir /root/lagou \
  6. --delete-target-dir \
  7. --fields-terminated-by "\t" \
  8. --table goodtbl \
  9. --split-by gname
  11. # 给 goodtbl 表增加主键
  12. alter table goodtbl add primary key(serialNumber);

备注:

  • 使用多个 Map Task 进行数据导入时，sqoop 要对每个Task的数据进行分区

    • 如果 MySQL 中的表有主键，指定 Map Task 的个数就行
    • 如果 MySQL 中的表有主键，要使用 split-by 指定分区字段
    • 如果分区字段是字符类型，使用 sqoop 命令的时候要添加:-Dorg.apache.sqoop.splitter.allow_text_splitter=true。即
      1. sqoop import -Dorg.apache.sqoop.splitter.allow_text_splitter=true \
      2. --connect jdbc:mysql://liunx:3306/sqoop \
      3. ... ...

  • 查询语句的where子句中的 ‘$CONDITIONS’ ，也是为了做数据分区使用的，即使只有1个Map Task

    MySQL 到 Hive

    在 hive 中创建表:

    1. CREATE TABLE mydb.goodtbl(
    2. gname string,
    3. serialNumber int,
    4. price int,
    5. stock_number int,
    6. create_time date);
    1. sqoop import \
    2. --connect jdbc:mysql://linux123:3306/sqoop \
    3. --username hive \
    4. --password 12345678 \
    5. --table goodtbl \
    6. --hive-import \
    7. --create-hive-table \
    8. --fields-terminated-by "\t" \
    9. --hive-overwrite \
    10. --hive-table mydb.goodtbl \
    11. -m 1

    参数说明:

  • hive-import。必须参数，指定导入

  • hive hive-database。Hive库名(缺省值default)
  • hive-table。Hive表名
  • fields-terminated-by。Hive字段分隔符
  • hive-overwrite。覆盖中已经存在的数据

  • create-hive-table。创建好 hive 表，但是表可能存在错误。不建议使用这个参数，建议提前建好表

    第 2 节 导出数据

    进入大数据平台导入:import
    离开大数据平台导出:export
    备注:MySQL表需要提前创建

    1. # 提前创建表
    2. CREATE TABLE sqoop.goodtbl2(
    3. gname varchar(50),
    4. serialNumber int,
    5. price int,
    6. stock_number int,
    7. create_time date
    8. );

    第 3 节 增量数据导入

    变化数据捕获(CDC)

    前面都是执行的全量数据导入。如果数据量很小，则采取完全源数据抽取;如果源数据量很大，则需要抽取发生变化的数据，这种数据抽取模式叫做变化数据捕获，简称CDC(Change Data Capture)。
    CDC大体分为两种:侵入式和非侵入式。侵入式指CDC操作会给源系统带来性能影响，只要CDC操作以任何一种方式对源数据库执行了SQL操作，就认为是侵入式的。
    常用的4种CDC方法是(前三种是侵入式的):

  • 基于时间戳的CDC。抽取过程可以根据某些属性列来判断哪些数据是增量的，最常见的属性列有以下两种:

    • 时间戳:最好有两个列，一个插入时间戳，表示何时创建，一个更新时间戳，表示最后一次更新的时间;
    • 序列:大多数数据库都提供自增功能，表中的列定义成自增的，很容易地根据该列识别新插入的数据;
    • 这种方法是最简单且常用的，但是有如下缺点:
      • 不能记录删除记录的操作
      • 无法识别多次更新
      • 不具有实时能力
  • 基于触发器的CDC。当执行INSERT、UPDATE、DELETE这些SQL语句时，激活数据库里的触发器，使用触发器可捕获变更的数据，并把数据保存在中间临时表里。然后这些变更数据再从临时表取出。大多数场合下，不允许向操作型数据库里添加触发器，且这种方法会降低系统性能，基本不会被采用;
  • 基于快照的CDC。 可以通过比较源表和快照表来获得数据变化。基于快照的CDC可以检测到插入、更新和删除的数据，这是相对于基于时间戳的CDC方案的优点。其缺点是需要大量存储空间来保存快照。
  • 基于日志的CDC。最复杂的和没有侵入性的CDC方法是基于日志的方式。数据库会把每个插入、更新、删除操作记录到日志里。解析日志文件，就可以获取相关信息。每个关系型数据库日志格式不一致，没有通用的产品。阿里巴巴的canal可以完成MySQL日志文件解析。

增量导入数据分为两种方式:

  • 基于递增列的增量数据导入(Append方式)
  • 基于时间列的数据增量导入(LastModified方式)

Append方式
1、准备初始数据

  1. -- 删除 MySQL 表中的全部数据 
  2. truncate table sqoop.goodtbl;
  3. -- 删除 Hive 表中的全部数据 
  4. truncate table mydb.goodtbl;
  5. -- 向MySQL的表中插入100条数据
  6. call batchInsertTestData(1, 100);

2、将数据导入hive

  1. sqoop import \
  2. --connect jdbc:mysql://linux123:3306/sqoop \
  3. --username hive --password 12345678 \
  4. --table goodtbl \
  5. --incremental append \
  6. --hive-import \
  7. --fields-terminated-by "\t" \
  8. --hive-table mydb.goodtbl \
  9. --check-column serialNumber \
  10. --last-value 0 \
  11. -m 1

参数说明:

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

3、检查hive表中是否有数据，有多少条数据
4、再向MySQL中加入1000条数据，编号从200开始

  1. call batchInsertTestData(200, 1000);

5、再次执行增量导入，将数据从 MySQL 导入 Hive 中;此时要将 last-value 改为 100

  1. sqoop import \
  2. --connect jdbc:mysql://linux123:3306/sqoop \
  3. --username hive --password 12345678 \
  4. --table goodtbl \
  5. --incremental append \
  6. --hive-import \
  7. --fields-terminated-by "\t" \
  8. --hive-table mydb.goodtbl \
  9. --check-column serialNumber \
  10. --last-value 100 \
  11. -m 1

6、再检查hive表中是否有数据，有多少条数据

第 4 节 执行 job

执行数据增量导入有两种实现方式:
1. 每次手工配置last-value，手工调度
2. 使用job，给定初始last-value，定时任务每天定时调度
很明显方式2更简便。
1、创建口令文件

  1. echo -n "12345678" > sqoopPWD.pwd
  2. hdfs dfs -mkdir -p /sqoop/pwd
  3. hdfs dfs -put sqoopPWD.pwd /sqoop/pwd
  4. hdfs dfs -chmod 400 /sqoop/pwd/sqoopPWD.pwd
  5. # 可以在 sqoop 的 job 中增加: 
  6. --password-file /sqoop/pwd/sqoopPWD.pwd

2、创建sqoop job

  1. # 创建 sqoop job
  2. sqoop job --create myjob1 -- import \
  3. --connect jdbc:mysql://linux123:3306/sqoop?useSSL=false \ --username hive \
  4. --password-file /sqoop/pwd/sqoopPWD.pwd \
  5. --table goodtbl \
  6. --incremental append \
  7. --hive-import \
  8. --hive-table mydb.goodtbl \
  9. --check-column serialNumber \
  10. --last-value 0 \
  11. -m 1
  13. # 查看已创建的job 
  14. sqoop job --list
  16. # 查看job详细运行是参数 
  17. sqoop job --show myjob1
  19. # 执行job
  20. sqoop job --exec myjob1
  22. # 删除job
  23. sqoop job --delete myjob1

3、执行job

  1. sqoop job -exec myjob1

4、查看数据

实现原理:
因为job执行完成后，会把当前check-column的最大值记录到meta中，下次再调起时把此值赋给last-value。
缺省情况下元数据保存在 ~/.sqoop/
其中，metastore.db.script 文件记录了对last-value的更新操作:

  1. cat metastore.db.script | grep incremental.last.value

第四部分 常用命令及参数

第1节 常用命令

image.png

第2节 常用参数

所谓公用参数，就是大多数命令都支持的参数。

公用参数 — 数据库连接

image.png

公用参数 — import

image.png

公用参数 — export

image.png

公用参数 — hive

image.png

import参数

image.png
image.png

export参数

image.png