小白学数据仓库日记day4——sqoop

最新推荐文章于 2022-12-08 14:23:12 发布

兰翎翡竹

最新推荐文章于 2022-12-08 14:23:12 发布

阅读量504

点赞数 1

本文链接：https://blog.csdn.net/qq_42515611/article/details/119010203

版权

Sqoop
Sqoop是什么?
SQL+HADOOP=SQOOP
完成MySQL到HADOOP之间的数据传输
Sqoop现状
已经停止维护分为SQOOP 1 和SQOOP 2 建议使用sqoop 1.4.7
停止维护原因分析
1.足够的成熟
2.基于MapReduce太老了,替换方案datax canel

兼容性较好的版本组合
Hadoop 2.7+ Hive 1.2.1 +HBase 1.x +sqoop 1.4.7
SQOOP主要命令
import 导入
export 导出
job 减少重复代码

Import 详解
通用参数
如下:
Argument               Description
==--connect==          指定JDBC连接字符串
--connection-manager   指定连接管理类
--driver               指定连接的驱动程序
-P                     从控制台读入密码(可以防止密码显示中控制台)
==--password==         指定访问数据库的密码
==--username==         指定访问数据库的用户名

连接数据库
[root@qianfeng01 ~]sqoop import --connect jdbc:mysql://qianfeng01:3306/sqoop --username root --password 123456
列出所有数据库
[root@qianfeng01 ~] sqoop list-databases --connect jdbc:mysql://hadoop01:3306 --username root --password 123456;
列出数据库中所有表
[root@qianfeng01 ~] sqoop list-tables --connect jdbc:mysql://hadoop01:3306/sqoop --username root --password 123456;

Import的控制参数
Argument
Description
--append
通过追加的方式导入到HDFS
--as-avrodatafile
导入为 Avro Data 文件格式
--as-sequencefile
导入为 SequenceFiles文件格式
--as-textfile
导入为文本格式 (默认值)
--as-parquetfile
导入为 Parquet 文件格式
--columns
指定要导入的列
--delete-target-dir
如果目标文件夹存在,则删除
--fetch-size
一次从数据库读取的数量大小
-m,--num-mappers
n 用来指定map tasks的数量,用来做并行导入
-e,--query
指定要查询的SQL语句
--split-by
用来指定分片的列
--table
需要导入的表名
--target-dir
HDFS 的目标文件夹
--where
用来指定导入数据的where条件
-z,--compress
是否要压缩
--compression-codec
使用Hadoop压缩 (默认是 gzip)

案例：

数据准备
在本地mysql数据库中新建一个sqoop数据库数据如下：


CREATE TABLE emp(
    empno        INT primary key,
    ename        VARCHAR(50),
    job        VARCHAR(50),
    mgr        INT,
    hiredate    DATE,
    sal        DECIMAL(7,2),
    comm        decimal(7,2),
    deptno        INT
) ;
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);

导入到hdfs

sqoop的典型导入都是把关系数据库中的表导入到HDFS中,使用--table参数可以指定具体的表导入到hdfs,譬如用 --table emp,默认情况下是全部字段导入.如下：
[root@sh01 ~]# bin/sqoop import --connect jdbc:mysql://hadoop01:3306/sqoop \
--username root --password 123456 \
--table emp \
#下面填写自己的ip和端口
--target-dir hdfs://hadoop01:8020/sqoopdata/emp
--delete-target-dir
查看结果
[root@sh01 ~]# hdfs dfs -cat /sqoopdata/emp/par*
指定列导入
[root@sh01 ~]sqoop import --connect jdbc:mysql://hadoop00:3306/test \
--username root --password 123456 \
--table emp \
--columns 'empno,mgr' \
--target-dir /sqoopdata/emp \
--delete-target-dir
指定条件导入
[root@sh01 ~]sqoop import --connect jdbc:mysql://hadoop01:3306/sqoop \
--username root --password 123456 \
--table emp \
--columns 'empno,mgr' \
--where 'empno>7800' \
##也可以不写端口和ip
--target-dir /sqoopdata/emp3 \
--delete-target-dir
指定Sql导入
上面的可以通过表,字段,条件进行导入,但是还不够灵活,其实sqoop还可以通过自定义的sql来进行导入,可以通过--query 参数来进行导入,这样就最大化的用到了Sql的灵活性.如下:
[root@sh01 ~]sqoop import --connect jdbc:mysql://hadoop00:3306/test \
--username root --password 123456 \
--query 'select empno,mgr,job from emp WHERE empno>7800  and $CONDITIONS' \
--target-dir /sqoopdata/emp \
--delete-target-dir \
--split-by empno \
-m 1
$CONDITIONS作用用于后面动态拼接SQL条件完成split
注意：
1.sql后必须跟上 and $CONDITIONS
2.如果m>1必须指定--split-by primary-key-column
3.在sqoop中尽量使用单引号
[root@sh01 ~]sqoop import --connect jdbc:mysql://hadoop00:3306/test \
--username root --password 123456 \
--query 'select empno,mgr,job from emp WHERE empno>7800  and $CONDITIONS' \
--target-dir /sqoopdata/emp \
--delete-target-dir \
--split-by empno \
-m 2

导入到hive

在导入Hive之前先要配置Hadoop的Classpath才可以,否则会报类找不到错误,在/etc/profile末尾添加如下配置:
[root@sh01 ~]vi /etc/profile
export HADOOP_CLASSPATH=$HADOOP_CLASSPATH:$HIVE_HOME/lib/*
#刷新配置
[root@sh01 ~]source /etc/profile

具体的参数如下:

Argument              Description
--hive-home           覆盖环境配置中的$HIVE_HOME,默认可以不配置
--hive-import         指定导入数据到Hive中
--hive-overwrite     覆盖当前已有的数据
--create-hive-table   是否创建hive表,如果已经存在,则会失败
--hive-table          设置要导入的Hive中的表名

注意：hive的版本不同可能会造成失败建议使用1.2.1版本的hive

[root@sh01 ~]sqoop import --connect jdbc:mysql://hadoop00:3306/test \
--username root \
--password 123456 \
--table emp \
--delete-target-dir \
--hive-import \
--hive-overwrite \
-m 1

分区表
静态分区
sqoop import --connect jdbc:mysql://hadoop01:3306/sqoop \
--username root --password 123456 \
--query 'select * from emp where deptno=10 and $CONDITIONS' \
--target-dir /sqoopdata/emp/deptno=10 \
--delete-target-dir \
--split-by empno \
-m 1;

CREATE external TABLE `emp`(
  `empno` int,
  `ename` string,
  `job` string,
  `mgr` int,
  `hiredate` string,
  `sal` double,
  `comm` double)
PARTITIONED by (deptno int)
ROW FORMAT DELIMITED
  FIELDS TERMINATED BY ','
  LINES TERMINATED BY '\n'
LOCATION
  'hdfs://hadoop01/sqoopdata/emp'
  
创建外部表 load data inpath 或者表已经存在,通过增加分区alter table add partition(dt=xxx) location hdfspath
alter table emp add partition(deptno=10) location 'hdfs://hadoop01/sqoopdata/emp/deptno=10'

sqoop import --connect jdbc:mysql://hadoop01:3306/sqoop \
--username root --password 123456 \
--query 'select * from emp where deptno=20 and $CONDITIONS' \
--target-dir /sqoopdata/emp/deptno=20 \
--delete-target-dir \
--split-by empno \
-m 1;
alter table emp add partition(deptno=20) location 'hdfs://hadoop01/sqoopdata/emp/deptno=10'

导入到hbase中


[root@sh01 ~]start-hbase.sh
hbase shell中创建表：
create 'mysql2hbase','info'
sqoop import  --connect jdbc:mysql://hadoop00:3306/test \
--username root \
--password 123456 \
--table emp \
--hbase-table mysql2hbase \
--column-family info \
--hbase-create-table \
--hbase-row-key empno \
-m 1 \
hbase(main):010:0> scan 'mysql2hbase',{COLUMNS=>['info:deptno','info:ename'],LIMIT=>4}

增量导入


1.手动增量
--假设昨天全量同步过
[root@sh01]sqoop import --connect jdbc:mysql://hadoop00:3306/test --username root --password 123456 --table emp --target-dir /sqoopdata/2102/emp -m 1;

--mysql今天新增1个用户
INSERT INTO emp values(8000,'SMITH2','CLERK2',7902,'2020-12-17',800,NULL,20);

--增量更新 你需要记住上次最大的id是多少
sqoop import --connect jdbc:mysql://hadoop00:3306/test \
--username root --password 123456 \
--query 'select empno,mgr,job from emp WHERE empno>7934  and $CONDITIONS' \
--target-dir /sqoopdata/2102/0722/emp \
--delete-target-dir \
--split-by empno \
-m 1
2.incremental 半自动
--mysql新增1个用户
INSERT INTO emp values(8001,'SMITH3','CLERK3',7902,'2020-12-17',800,NULL,20);
# 第二次导入
[root@sh01]sqoop import --connect jdbc:mysql://hadoop00:3306/test \
--username root --password 123456 \
--table emp \
--target-dir /sqoopdata/2102/emp-incremental \
--split-by empno \
-m 1 \
--check-column empno \
--incremental append \
--last-value 8000 \
--fields-terminated-by '\t' \
--null-string '\\N' \
--null-non-string '0'

sqoop export

场景将:数据仓库中dws层结果导出到MySQL供java服务程序查询使用


在mysql中创建表
use test;
CREATE TABLE `u2` (
  `id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `u3` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) default NULL,
  `age` int(11) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 通过console输入一些数据到hdfs文件中
hdfs dfs -put - /sqoopdata/2102/u2/part-m-0
100,20
101,19
102,18
输入完成后按ctrl+D

[root@sh01~]sqoop export --connect jdbc:mysql://hadoop00:3306/test \
--username root \
--password 123456 \
--table u2 \
--driver com.mysql.jdbc.Driver \
--export-dir '/sqoopdata/2102/u2/*' \
-m 1

sqoop job

本地元数据存储

[root@sh01~]sqoop job --create exportjob1  -- export --connect jdbc:mysql://hadoop00:3306/test \
--username root \
--password 123456 \
--table u2 \
--driver com.mysql.jdbc.Driver \
--export-dir '/sqoopdata/2102/u2/*' \
-m 1

sqoop job --list
sqoop job --exec exportjob1

兰翎翡竹

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
4
评论
小白学数据仓库日记day4——sqoop

SqoopSqoop是什么?SQL+HADOOP=SQOOP完成MySQL到HADOOP之间的数据传输Sqoop现状已经停止维护分为SQOOP 1 和SQOOP 2 建议使用sqoop 1.4.7停止维护原因分析1.足够的成熟2.基于MapReduce太老了,替换方案datax canel兼容性较好的版本组合Hadoop 2.7+ Hive 1.2.1 +HBase 1.x +sqoop 1.4.7SQOOP主要命令import 导入export 导出job 减少重复代码.
复制链接

扫一扫