Sqoop概述
- Sqoop是一款开源的工具,主要用于在Hadoop(Hive)与传统的数据库(mysql、postgresql等)间进行数据的传递。
- Sqoop可以将关系型数据库(MySQL ,Oracle,Postgres等)中的数据导入到HDFS中,也可以将HDFS的数据导进到关系型数据库中。
- Sqoop将导入或导出命令转换为 MapReduce 程序来实现。翻译出的 MapReduce 中主要是对 inputformat 和outputformat 进行定制。
Sqoop导入导出
- Sqoop导入是指:从关系型数据库向大数据集群(HDFS、HIVE、HBASE)传输数据;使用import关键字;
- Sqoop导出是指:从大数据集群向关系型数据库传输数据;使用export关键字;
Sqoop导入数据
Sqoop导入:MySQL到HDFS
-
导入全部数据:
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"
- target-dir:将数据导入 HDFS 的路径;
- delete-target-dir:如果目标文件夹在 HDFS 上已经存在,那么再次运行就会报错。可以使用–delete-target-dir来先删除目录。也可以使用 append 参数,表示追加数据;
- num-mappers:启动多少个Map Task;默认启动4个Map Task;也可以写成
-m 1
:表示只启动一个Map Task - fields-terminated-by:HDFS文件中数据的分隔符;
-
导入查询到的数据
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;'
- 查询语句的where子句中必须包含
'$CONDITIONS'
- 如果query后使用的是双引号,则
$CONDITIONS
前必须加转移符,防止shell识别为变量
- 查询语句的where子句中必须包含
-
导入指定的列
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
- columns中如果涉及到多列,用逗号分隔,不能添加空格
-
导入查询数据(使用关键字)
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"
-
启动多个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);
-
使用多个 Map Task 进行数据导入时,sqoop 要对每个Task的数据进行分区
-
如果 MySQL 中的表有主键,指定 Map Task 的个数就行
-
如果 MySQL 中的表没有主键,要使用 split-by 指定分区字段
-
如果分区字段是字符类型,使用 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 \ ... ...
-
查询语句的where子句中的 ‘$CONDITIONS’ ,也是为了做数据分区使用的,即使只有1个Map Task
-
-
从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
- hive-import。必须参数,指定导入hive
- hive-database。Hive库名(默认值default)
- hive-table。Hive表名
- fields-terminated-by。Hive字段分隔符
- hive-overwrite。覆盖中已经存在的数据
- create-hive-table。创建好 hive 表,但是表可能存在错误。不建议使用这个参数,建议提前建好表
Sqoop导出数据
-
导出数据: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"
Sqoop增量数据导入
变化数据捕获(CDC)
- :前面都是执行的全量数据导入。如果数据量很小,则采取完全源数据抽取;如果源数据量很大,则需要抽取发生变化的数据,这种数据抽取模式叫做变化数据捕获,简称CDC(Change Data Capture)。
- CDC大体分为两种:侵入式和非侵入式。
- 侵入式指CDC操作会给源系统带来性能影响,只要CDC操作以任何一种方式对源数据库执行了SQL操作,就认为是侵入式的。
常见的4种CDC方法(前三种是侵入式的)
- 第一种:基于时间戳的CDC。抽取过程可以根据某些属性列来判断哪些数据是增量的,最常见的属性列有以下两种:
- 时间戳:最好有两个列,一个插入时间戳,表示何时创建,一个更新时间戳,表示最后一次更新的时间;
- 序列:大多数数据库都提供自增功能,表中的列定义成自增的,很容易地根据该列识别新插入的数据;
- 时间戳的CDC是最简单且常用的,但是有如下缺点:
- 不能记录删除记录的操作
- 无法识别多次更新
- 不具有实时能力
- 第二种:基于触发器的CDC。当执行INSERT、UPDATE、DELETE这些SQL语句时,激活数据库里的触发器,使用触发器可捕获变更的数据,并把数据保存在中间临时表里。然后这些变更数据再从临时表取出。大多数场合下,不允许向操作型数据库里添加触发器,且这种方法会降低系统性能,基本不会被采用;
- 第三种:基于快照的CDC。 可以通过比较源表和快照表来获得数据变化。基于快照的CDC可以检测到插入、更新和删除的数据,这是相对于基于时间戳的CDC方案的优点。其缺点是需要大量存储空间来保存快照;
- 第四种:基于日志的CDC。最复杂的和没有侵入性的CDC方法是基于日志的方式。数据库会把每个插入、更新、删除操作记录到日志里。解析日志文件,就可以获取相关信息。每个关系型数据库日志格式不一致,没有通用的产品。阿里巴巴的canal可以完成MySQL日志文件解析。
Sqoop增量导入数据的方式
- 基于递增列的增量数据导入(Append方式)
- 基于时间列的数据增量导入(LastModified方式)
Append方式
-
将数据导入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
- check-column 用来指定一些列(即可以指定多个列),这些列在增量导入时用来检查这些数据是否作为增量数据进行导入,和关系型数据库中的自增字段及时间戳类似。这些被指定的列的类型不能使任意字符类型,如char、varchar等类型都不可以
- last-value 指定上一次导入中检查列指定字段最大值
Sqoop执行job
-
执行数据增量导入有两种实现方式:
- 每次手工配置last-value,手工调度
- 使用job,给定初始last-value,定时任务每天定时调度
-
创建口令文件
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
-
创建 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
-
查看已创建的job:
sqoop job --list
-
查看job详细运行是参数:
sqoop job --show myjob1
-
执行job:
sqoop job --exec myjob1
-
删除job:
sqoop job --delete myjob1
job的实现原理
-
job执行完成后,会把当前check-column的最大值记录到meta中,下次再调起时把此值赋给last-value。
-
默认情况下元数据保存在 ~/.sqoop/,其中,metastore.db.script 文件记录了对last-value的更新操作:
cat metastore.db.script |grep incremental.last.value