数据迁移工具——Sqoop_将数据湖数据导入大数据联机查询服务(kva)可以使用什么导数工具

Sqoop概述

1. Sqoop是一款开源的工具，主要用于在Hadoop(Hive)与传统的数据库（mysql、postgresql等）间进行数据的传递。
2. Sqoop可以将关系型数据库（MySQL ,Oracle,Postgres等）中的数据导入到HDFS中，也可以将HDFS的数据导进到关系型数据库中。
3. Sqoop将导入或导出命令转换为 MapReduce 程序来实现。翻译出的 MapReduce 中主要是对 inputformat 和outputformat 进行定制。

Sqoop导入导出

1. Sqoop导入是指：从关系型数据库向大数据集群（HDFS、HIVE、HBASE）传输数据；使用import关键字；
2. Sqoop导出是指：从大数据集群向关系型数据库传输数据；使用export关键字；

Sqoop导入数据

Sqoop导入：MySQL到HDFS

1. 导入全部数据：

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

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

2. 导入查询到的数据

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

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

3. 导入指定的列

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

   1. columns中如果涉及到多列，用逗号分隔，不能添加空格

4. 导入查询数据(使用关键字)

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

5. 启动多个Map Task导入数据：

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

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

   2. 如果 MySQL 中的表有主键，指定 Map Task 的个数就行

   3. 如果 MySQL 中的表没有主键，要使用 split-by 指定分区字段

   4. 如果分区字段是字符类型，使用 sqoop 命令的时候要添加：-Dorg.apache.sqoop.splitter.allow_text_splitter=true。即

      sqoop import -Dorg.apache.sqoop.splitter.allow_text_splitter=true \
      --connect jdbc:mysql://liunx123:3306/sqoop \
      ... ...
      1
      2
      3

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

6. 从MySQL 到 Hive

   sqoop import \
   --connect jdbc:mysql://linux123:3306/sqoop \
   --username hive \
   --password 12345678 \
   --table goodtbl \
   --hive-import \
   --create-hive-table \
   --fields-terminated-by "\t" \
   --hive-overwrite \
   --hive-table mydb.goodtbl \
   -m 1
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11

   1. hive-import。必须参数，指定导入hive
   2. hive-database。Hive库名(默认值default)
   3. hive-table。Hive表名
   4. fields-terminated-by。Hive字段分隔符
   5. hive-overwrite。覆盖中已经存在的数据
   6. create-hive-table。创建好 hive 表，但是表可能存在错误。不建议使用这个参数，建议提前建好表

Sqoop导出数据

1. 导出数据:Hive/HDFS到RDBMS

   sqoop export \
   --connect jdbc:mysql://linux123:3306/sqoop \
   --username hive \
   --password 12345678 \
   --table goodtbl2 \
   --num-mappers 1 \
   --export-dir /user/hive/warehouse/mydb.db/goodtbl \
   --input-fields-terminated-by "\t"
   1
   2
   3
   4
   5
   6
   7
   8

Sqoop增量数据导入

变化数据捕获（CDC）

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

常见的4种CDC方法(前三种是侵入式的)

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

Sqoop增量导入数据的方式

1. 基于递增列的增量数据导入（Append方式）
2. 基于时间列的数据增量导入（LastModified方式）

Append方式

1. 将数据导入Hive

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

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

Sqoop执行job

1. 执行数据增量导入有两种实现方式：

   1. 每次手工配置last-value，手工调度
   2. 使用job，给定初始last-value，定时任务每天定时调度

2. 创建口令文件

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

3. 创建 sqoop job

   sqoop job --create myjob1 -- import \
   --connect jdbc:mysql://linux123:3306/sqoop?useSSL=false \
   --username hive \
   --password-file /sqoop/pwd/sqoopPWD.pwd \
   --table goodtbl \
   --incremental append \
   --hive-import \
   --hive-table mydb.goodtbl \
   --check-column serialNumber \
   --last-value 0 \
   -m 1
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11

4. 查看已创建的job:sqoop job --list

5. 查看job详细运行是参数:sqoop job --show myjob1

6. 执行job：sqoop job --exec myjob1

7. 删除job: sqoop job --delete myjob1

job的实现原理

1. job执行完成后，会把当前check-column的最大值记录到meta中，下次再调起时把此值赋给last-value。

2. 默认情况下元数据保存在 ~/.sqoop/,其中，metastore.db.script 文件记录了对last-value的更新操作：

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