Apache Sqoop介绍、工作机制
-
Sqoop介绍
sqoop是apache旗下一款“Hadoop和关系数据库服务器之间传送数据”的工具。 导入数据：MySQL，Oracle导入数据到Hadoop的HDFS、HIVE、HBASE等数据存储系统； 导出数据：从Hadoop的HDFS、HIVE中导出数据到关系数据库mysql等。
-
Sqoop工作机制
Sqoop工作机制是将导入或导出命令翻译成mapreduce程序来实现。 在翻译出的mapreduce中主要是对inputformat和outputformat进行定制。
-
sqoop安装
你的hive安装在哪一台主机，你的sqoop就要装在哪一台主机，该操作是以node1为例 1、在node1中，上传sqoop的安装包到/export/software目录 2、在node1中，解压sqoop安装包到/export/server目录 tar -xvf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz -C /export/server/ 3、在node1中，对解压后的sqoop目录进行重命名 cd /export/server/ mv sqoop-1.4.7.bin__hadoop-2.6.0 sqoop-1.4.7 4、在node1中，修改sqoop的配置文件， cd /export/server/sqoop-1.4.7/conf mv sqoop-env-template.sh sqoop-env.sh 修改sqoop-env.sh 文件，设置以下内容 export HADOOP_COMMON_HOME=/export/server/hadoop-3.3.0 export HADOOP_MAPRED_HOME=/export/server/hadoop-3.3.0 export HIVE_HOME=/export/server/hive-3.1.2 5、在node1中，加入mysql的jdbc驱动包和hive的执行包 cp /export/server/hive-3.1.2/lib/mysql-connector-java-5.1.32-bin.jar /export/server/sqoop-1.4.7/lib/ cp /export/servers/hive-3.1.2/lib/hive-exec-3.1.2.jar /export/servers/sqoop-1.4.7/lib/ cp /export/server/hive-3.1.2/hcatalog/share/hcatalog/hive-hcatalog-core-3.1.2.jar /export/server/sqoop-1.4.7/lib/ 6、在node1，node2、node3中，配置环境变量 vim /etc/profile 添加以下内容 export SQOOP_HOME=/export/server/sqoop-1.4.7 export PATH=:$SQOOP_HOME/bin:$PATH # HCatelog export HCAT_HOME=/export/server/hive-3.1.2/hcatalog export hive_dependency=$HIVE_HOME/conf:$HIVE_HOME/lib/*:$HIVE_HOME/hcatalog/share/hcatalog/hive-hcatalog-core-3.1.2.jar 添加完之后一定要保存退出，执行以下命令 source /etc/profile 7、在node1中，测试sqoop sqoop list-databases \ --connect jdbc:mysql://node1:3306/ \ --username root --password 123456
-
sqoop测试
#测试你的sqoop是否能查看MySQL中所有的数据库 sqoop list-databases \ --connect jdbc:mysql://hadoop01:3306/ \ --username root \ --password 123456
增量数据、全量数据
-
全量数据（Full data）
就是全部数据，所有数据。如对于表来说，就是表中的所有数据。
-
增量数据（Incremental data）
就是上次操作之后至今产生的新数据。
-
数据子集
也叫做部分数据。整体当中的一部分。
Sqoop数据导入至HDFS
-
测试数据准备
-
全量导入MySQL数据到HDFS
sqoop import \ --connect jdbc:mysql://hadoop01:3306/userdb \ --table emp \ --username root \ --password 123456 \ --target-dir /sqoop/result1 \ --delete-target-dir \ --m 1 # --table emp 要导入mysql中的userdb数据库的emp表数据 # --target-dir /sqoop/result1 表示要导入到hdfs的目标路径 # --delete-target-dir 如果目标目录存在，则删除 # --m 1 使用一个Map线程来导入 导入HDFS之后，默认分隔符是逗号
-
指定分隔符
sqoop import \ --connect jdbc:mysql://192.168.88.80:3306/userdb \ --username root \ --password 123456 \ --target-dir /sqoop/result2 \ --delete-target-dir \ --fields-terminated-by '\t' \ --table emp \ --m 1 # --fields-terminated-by '\t' 指定HDFS上文件的分隔符是'\t'
-
指定任务并行度（maptask个数）
sqoop import \ --connect jdbc:mysql://192.168.88.80:3306/userdb \ --username root \ --password 123456 \ --target-dir /sqoop/result3 \ --delete-target-dir \ --fields-terminated-by '\t' \ --split-by id \ --table emp \ --m 2 #如果你要指定多个maptask来完成数据的导入，也就是--m参数的值不是1，则必须添加一个参数--split-by #该参数用来指定你原表的数据如何分配给多个线程来实现导入 #--split-by id 内部原理是获取id的最小值和id的最大值，进行平均划分 SELECT MIN(`id`), MAX(`id`) FROM `emp` #maptask1要导入的数据 1201 gopal manager 50000 TP 1202 manisha Proof reader 50000 TP #maptask2要导入的数据 1203 khalil php dev 30000 AC 1204 prasanth php dev 30000 AC 1205 kranthi admin 20000 TP sqoop import \ -Dorg.apache.sqoop.splitter.allow_text_splitter=true \ --connect jdbc:mysql://192.168.88.80:3306/userdb \ --username root \ --password 123456 \ --target-dir /sqoop/result3 \ --delete-target-dir \ --fields-terminated-by '\t' \ --split-by name \ --table emp \ --m 2 #小结：split-by后边的字段必须是数字类型，或者是数字字符串类型（‘123’） 如果是数字类型，则不需要额外添加内容 如果是数字字符串类型，则需要添加以下内容 sqoop import \ -Dorg.apache.sqoop.splitter.allow_text_splitter=true \ --connect jdbc:mysql://192.168.88.80:3306/userdb \ --username root \ --password 123456 \ --target-dir /sqoop/result3 \ --delete-target-dir \ --fields-terminated-by '\t' \ --split-by name \ --table emp \ --m 2
Sqoop数据导入至Hive
-
测试准备
-- Hive中创建测试使用的数据库 drop database if exists test cascade ; create database if not exists test;
-
方式1-先复制mysql的表结构到Hive，然后再导入数据
1、先复制表结构到hive中再导入数据，将关系型数据的表结构复制到hive中 sqoop create-hive-table \ --connect jdbc:mysql://hadoop01:3306/userdb \ --table emp_add \ --username root \ --password 123456 \ --hive-table test.emp_add_sp 其中： --table emp_add为mysql中的数据库userdb中的表。 --hive-table emp_add_sp 为hive中新建的表名称。 复制表结构默认分隔符是'\001' 2、从关系数据库导入文件到hive中 sqoop import \ --connect jdbc:mysql://hadoop01:3306/userdb \ --username root \ --password 123456 \ --table emp_add \ --hive-table test.emp_add_sp \ --hive-import \ --m 1
-
方式2：直接导入数据（建表 + 导入数据）
-- 1、使用hive默认分隔符 '\001' sqoop import \ --connect jdbc:mysql://192.168.88.80:3306/userdb \ --table emp_conn \ --username root \ --password 123456 \ --hive-import \ --hive-database test \ --m 1 #从MySQL的userdb数据库的emp_conn表导入到hive的test数据库的emp_conn表 #如果多次导入，则会进行数据追加 #如果要覆盖操作，需要加参数: --hive-overwrite -- 2、使用指定分隔符 '\0' sqoop import \ --connect jdbc:mysql://192.168.88.80:3306/userdb \ --username root \ --password 123456 \ --table emp_conn \ --hive-import \ --hive-database test \ --fields-terminated-by '\t' \ --m 1 帮助手册: sqoop help sqoop help import
Sqoop数据导入至Hive--HCatalog API
-
sqoop API 原生方式
所谓sqoop原生的方式指的是sqoop自带的参数完成的数据导入。
但是有什么不好的地方呢？请看下面案例
-- 手动在hive中建一张表 create table test.emp_hive ( id int, name string, deg string, salary int, dept string ) row format delimited fields terminated by '\t' stored as orc; --注意，这里指定了表的文件存储格式为ORC。 --从存储效率来说，ORC格式胜于默认的textfile格式。
sqoop import \ --connect jdbc:mysql://hadoop01:3306/userdb \ --username root \ --password 123456 \ --table emp \ --fields-terminated-by '\t' \ --hive-database test \ --hive-table emp_hive \ -m 1
执行之后，可以发现虽然针对表emp_hive的sqoop任务成功，但是==Hive表中却没有数据==。
-
==HCatalog== API方式
Apache HCatalog是基于Apache Hadoop之上的数据表和存储管理服务。
包括：
-
提供一个共享的模式和数据类型的机制。
-
抽象出表，使用户不必关心他们的数据怎么存储，底层什么格式。
-
提供可操作的跨数据处理工具，如Pig，MapReduce，Streaming，和Hive。
sqoop的官网也做了相关的描述说明，使用HCatalog支持ORC等数据格式。
sqoop import \ --connect jdbc:mysql://192.168.88.80:3306/userdb \ --username root \ --password 123456 \ --table emp \ --fields-terminated-by '\t' \ --hcatalog-database test \ --hcatalog-table emp_hive \ -m 1
可以发现数据导入成功，并且底层是使用ORC格式存储的。
-
-
sqoop原生API和 HCatalog区别
#数据格式支持（这是实际中使用HCatalog的主要原因，否则还是原生的灵活一些） Sqoop方式支持的数据格式较少; HCatalog支持的数据格式多，包括RCFile, ORCFile, CSV, JSON和SequenceFile等格式。 #数据覆盖 Sqoop方式允许数据覆盖，HCatalog不允许数据覆盖，每次都只是追加。 #字段名匹配 Sqoop方式比较随意，不要求源表和目标表字段相同(字段名称和个数都可以不相同)，它抽取的方式是将字段按顺序插入，比如目标表有3个字段，源表有一个字段，它会将数据插入到Hive表的第一个字段，其余字段为NULL。 但是HCatalog不同，源表和目标表字段名需要相同，字段个数可以不相等，如果字段名不同，抽取数据的时候会报NullPointerException错误。HCatalog抽取数据时，会将字段对应到相同字段名的字段上，哪怕字段个数不相等。行存储和列存储
1、行存储 1）表数据在硬盘上以行为单位，一行的数据是连续存储在一起 ，select * from A 查询效率高。 2）行存储代表：TextFile、SequenceFile create table test.emp_hive ( id int, name string, deg string, salary int, dept string ) row format delimited fields terminated by '\t' stored as textfile; 2、列存储 1）表数据在硬盘上以列为单位，一列的数据是连续存储在一起 ，select 字段 from A 查询效率高。 2）列存储代表：ORC、Parquet create table test.emp_hive ( id int, name string, deg string, salary int, dept string ) row format delimited fields terminated by '\t' stored as orc; 3、ORC格式插入数据的步骤 1)准备数据：log.dat 18.2M 2)创建普通表，存储格式是TEXTFILE create temporary table log_text ( track_time string, url string, session_id string, referer string, ip string, end_user_id string, city_id string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS TEXTFILE ; 3)给普通表插入数据 load data local inpath '/root/log.data' into table log_text; 4)创建ORC存储的表 create table log_orc( track_time string, url string, session_id string, referer string, ip string, end_user_id string, city_id string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' STORED AS orc ; 5)从普通表查询数据插入到orc表 insert into table log_orc select * from log_text;
知识点07：Sqoop数据导入--条件部分导入
在实际开发中，有时候我们从RDBMS（MySQL）中导入数据时，不需要将数据全部导入，而只需要导入满足条件的数据，则需要进行条件导入
-
query查询
sqoop import \ --connect jdbc:mysql://192.168.88.80:3306/userdb \ --username root \ --password 123456 \ --target-dir /sqoop/result5 \ --query 'select id,name,deg from emp WHERE id>1203 and $CONDITIONS' \ --delete-target-dir \ --fields-terminated-by '\001' \ --m 1
使用sql query语句来进行查找时，==不能加参数--table==；
并且==必须要添加where条件==；
并且where条件后面==必须带一个$CONDITIONS==这个字符串；
并且这个sql语句==必须用单引号==，不能用双引号。
Sqoop数据导入--增量导入
1、全量导入，表所有数据全部导入 2、条件导入，只要满足查询条件的就导入 3、增量导入，之前已经导入过一次，下一次只导入新增加或者修改的数据 方式1-使用sqoop自带的参数实现增量导入 方式2-使用用户自定义条件来实现增量导入（使用该方式比较多） 增量导入的难点： 因为你之前已经导入多一次，下一次导入时一定要判断哪些数据是已经导入过的，则不要导入，哪些数据是新增加的或者修改的，则需要导入
-
方式一：sqoop自带参数实现
设计思路：对某一列值进行判断，只要大于上一次的值就会导入。
所谓的增量实现，肯定需要一个判断的依据，上次到哪里了，这次从哪里开始。
--check-column <column> Source column to check for incremental change --incremental <import-type> Define an incremental import of type 'append' or 'lastmodified' --last-value <value> Last imported value in the incremental check column
-
==append==模式
-
要求：必须有一列自增的值，按照==自增的int值==进行判断
-
特点：只能导入增加的数据，无法导入更新的数据
#第一次，全量导入-首先执行以下指令先将我们之前的数据导入 sqoop import \ --connect jdbc:mysql://192.168.88.80:3306/userdb \ --username root \ --password 123456 \ --target-dir /sqoop/appendresult \ --table emp \ --m 1 #查看生成的数据文件，发现数据已经导入到hdfs中. #模拟新增加数据，然后在mysql的emp中插入2条数据: insert into `userdb`.`emp` (`id`, `name`, `deg`, `salary`, `dept`) values ('1206', 'allen', 'admin', '30000', 'tp'); insert into `userdb`.`emp` (`id`, `name`, `deg`, `salary`, `dept`) values ('1207', 'woon', 'admin', '40000', 'tp'); #第二次导入，执行如下的指令，实现增量的导入: sqoop import \ --connect jdbc:mysql://192.168.88.80:3306/userdb \ --username root \ --password 123456 \ --table emp --m 1 \ --target-dir /sqoop/appendresult \ --incremental append \ --check-column id \ --last-value 1205 ####如果想实现sqoop自动维护增量记录 可以使用sqoop job作业来实现 21/10/09 15:03:37 INFO tool.ImportTool: --incremental append 21/10/09 15:03:37 INFO tool.ImportTool: --check-column id 21/10/09 15:03:37 INFO tool.ImportTool: --last-value 1207 21/10/09 15:03:37 INFO tool.ImportTool: (Consider saving this with 'sqoop job --create')并且还可以结合sqoop job作业，实现sqoop自动记录维护last-value值，详细可以参考课程资料。
-
-
lastmodifield模式
-
要求：==必须包含动态时间变化这一列==，按照数据变化的时间进行判断
-
特点：既导入新增的数据也导入更新的数据
# 首先我们要在mysql中创建一个customer表，指定一个时间戳字段 create table userdb.customertest( id int,name varchar(20), last_mod timestamp default current_timestamp on update current_timestamp ); #此处的时间戳设置为在数据的产生和更新时都会发生改变. #插入如下记录: insert into userdb.customertest(id,name) values(1,'neil'); insert into userdb.customertest(id,name) values(2,'jack'); insert into userdb.customertest(id,name) values(3,'martin'); insert into userdb.customertest(id,name) values(4,'tony'); insert into userdb.customertest(id,name) values(5,'eric'); #第一次全量导入：此时执行sqoop指令将数据导入hdfs: sqoop import \ --connect jdbc:mysql://192.168.88.80:3306/userdb \ --username root \ --password 123456 \ --target-dir /sqoop/lastmodifiedresult \ --table customertest --m 1 #再次插入一条数据进入customertest表 insert into userdb.customertest(id,name) values(6,'james'); #更新一条已有的数据，这条数据的时间戳会更新为我们更新数据时的系统时间. update userdb.customertest set name = 'NEIL' where id = 1; #第二次导入：执行如下指令，把id字段作为merge-key: sqoop import \ --connect jdbc:mysql://192.168.88.80:3306/userdb \ --username root \ --password 123456 \ --table customertest \ --target-dir /sqoop/lastmodifiedresult \ --incremental lastmodified \ --check-column last_mod \ --last-value '2022-10-08 15:40:27' \ --m 1 \ --merge-key id #解释： --incremental lastmodified 第二次导入数据和第一次导入的数据合并，重新执行MR，将MR执行的 覆盖原来的数据 --last-value "2022-06-10 10:59:36" 把大于等于这个日期的数据导入 --merge-key id 如果第一次导入的数据和第二次导入的数据id相同，则合并 #由于merge-key这种模式是进行了一次完整的mapreduce操作， #因此最终我们在lastmodifiedresult文件夹下可以发现id=1的name已经得到修改，同时新增了id=6的数据 -
-
-
方式二：用户条件过滤实现(不用敲)
-
通过where对字段进行过滤
-- 导入从某一个时间点的数据 sqoop import \ --connect jdbc:mysql://192.168.88.80:3306/userdb \ --username root \ --password 123456 \ --query "select * from customertest where last_mod >'2022-06-10 10:59:36' and \$CONDITIONS" \ --fields-terminated-by '\001' \ --hcatalog-database test \ --hcatalog-table customertest \ -m 1 -- 导入上一天数据，指定时间区间 sqoop import \ --connect jdbc:mysql://192.168.88.80:3306/userdb \ --username root \ --password 123456 \ --query "select * from customertest where last_mod >= '2022-09-12 00:00:00' and last_mod <= '2022-09-12 23:59:59' and \$CONDITIONS" \ --fields-terminated-by '\001' \ --hcatalog-database test \ --hcatalog-table customertest \ -m 1 sqoop import \ --connect jdbc:mysql://192.168.88.80:3306/userdb \ --username root \ --password 123456 \ --query "select * from emp where id>1203 and \$CONDITIONS" \ --fields-terminated-by '\001' \ --hcatalog-database test \ --hcatalog-table emp_hive \ -m 1
-
Sqoop数据导出
sqoop导出操作最大的特点是，==目标表需要自己手动提前创建==。
1、在大数据中，数据的导出一般发生在大数据分析的最后一个阶段 2、将分析后的指标导入一些通用的数据库系统中，用于进一步使用 3、导入到MySQL时，需要在MySQL中提前创建表
-
全量数据导出
#step1:MySQL中建表 mysql> use userdb; mysql> create table employee ( id int primary key, name varchar(20), deg varchar(20), salary int, dept varchar(10)); #step2:从HDFS导出数据到MySQL sqoop export \ --connect jdbc:mysql://192.168.88.80:3306/userdb \ --username root \ --password 123456 \ --table employee \ --export-dir /sqoop/result1/ #解释：将HDFS/sqoop/result1/目录下的文件内容，导出到mysql中userdb数据库下的employee表 #step3:从Hive导出数据到MySQL #首先清空MySQL表数据 truncate table employee; sqoop export \ --connect "jdbc:mysql://192.168.88.80:3306/userdb? useUnicode=true&characterEncoding=utf-8" \ --username root \ --password 123456 \ --table employee \ --hcatalog-database test \ --hcatalog-table emp_hive \ --input-fields-terminated-by '\t' \ -m 1 #解释： 将Hive中test数据库下的emp_hive表导出到MySQL的userdb数据库中employee表 #--input-fields-terminated-by '\t' ：表示sqoop去hive的表目录下读取文件时，使用'\t'对文件进行切割，如果hive文件分隔符是'\001'，则该参数不用指定 #注意，如果Hive中的表底层是使用ORC格式存储的，那么必须使用hcatalog API进行操作。
-
增量数据导出
-
updateonly：只增量导出更新的数据
-
allowerinsert：既导出更新的数据，也导出新增的数据
-
updateonly模式
#在HDFS文件系统中/sqoop/updateonly/目录的下创建一个文件updateonly_1.txt hadoop fs -mkdir -p /sqoop/updateonly/ hadoop fs -put updateonly_1.txt /sqoop/updateonly/ 1201,gopal,manager,50000 1202,manisha,preader,50000 1203,kalil,php dev,30000 #手动创建mysql中的目标表 CREATE TABLE userdb.updateonly ( id INT NOT NULL PRIMARY KEY, name VARCHAR(20), deg VARCHAR(20), salary INT ); #先执行全部导出操作： sqoop export \ --connect jdbc:mysql://192.168.88.80:3306/userdb \ --username root \ --password 123456 \ --table updateonly \ --export-dir /sqoop/updateonly/updateonly_1.txt #新增一个文件updateonly_2.txt：修改了前三条数据并且新增了一条记录 1201,gopal,manager,1212 1202,manisha,preader,1313 1203,kalil,php dev,1414 1204,allen,java,1515 hadoop fs -put updateonly_2.txt /sqoop/updateonly/ #执行更新导出： sqoop export \ --connect jdbc:mysql://192.168.88.80:3306/userdb \ --username root \ --password 123456 \ --table updateonly \ --export-dir /sqoop/updateonly/updateonly_2.txt \ --update-key id \ --update-mode updateonly #解释： --update-key id 根据id这列来判断id两次导出的数据是否是同一条数据，如果是则更新 --update-mode updateonly 导出时，只导出第一次和第二次的id都有的数据，进行更新，不会导出HDFS中新增加的数据
-
allowinsert模式
#手动创建mysql中的目标表 CREATE TABLE userdb.allowinsert ( id INT NOT NULL PRIMARY KEY, name VARCHAR(20), deg VARCHAR(20), salary INT); #先执行全部导出操作 sqoop export \ --connect jdbc:mysql://192.168.88.80:3306/userdb \ --username root \ --password 123456 \ --table allowinsert \ --export-dir /sqoop/updateonly/updateonly_1.txt #执行更新导出 sqoop export \ --connect jdbc:mysql://192.168.88.80:3306/userdb \ --username root --password 123456 \ --table allowinsert \ --export-dir /sqoop/updateonly/updateonly_2.txt \ --update-key id \ --update-mode allowinsert
-
# 博学谷IT 技术支持
216
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
