Apache Sqoop的数据导入之MySQL与Hive&Hdfs数据导入导出参数分类

最新推荐文章于 2022-08-26 18:43:54 发布

猫君之上

最新推荐文章于 2022-08-26 18:43:54 发布

阅读量495

点赞数

分类专栏： # Sqoop 文章标签： Sqoop 导入 import mysql hive

本文链接：https://blog.csdn.net/qq_33713328/article/details/88942966

版权

Sqoop 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

我们知道sqoop命令最终还是会解释为mapreduce代码执行，但是有一点值得注意的是，sqoop的数据迁移对应的只有maptask，没有reducetask，也就是说基本上不用担心数据倾斜问题了。

最核心的sqoop命令就类似hive 一样，目的是为了启动一个客户端。

1.外围指令（不涉及到数据导入导出相关的）

1) 查看当前MySQL中有哪几个数据库

list-databases

sqoop list-databases  \
--connect jdbc:mysql://mycat01:3306/  \
--username root \
--password miku;

上面 --connect用于指定连接的url，--username用于指定连接的用户名，--password用于指定连接的用户的密码。（此方式主要是针对JDBC方式连接—需要在sqoop的lib目录下放入驱动包）

结果展示如下：

[hadoop@mycat01 ~]$ sqoop list-databases --connect jdbc:mysql://mycat01:3306/ --username root --password miku;
......
19/03/29 00:36:13 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
19/03/29 00:36:13 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
19/03/29 00:36:13 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
information_schema
azkabandb
hivedb
mysql
test

考虑到安全性，你完全可以使用-P来代替--password.

sqoop list-databases --connect jdbc:mysql://mycat01:3306/ --username root --P;

其实，如果你在url后面补上数据库名，结果开始一样的，毕竟只列出数据库名。

2）查看MySQL的mysql数据库下面有哪些表

命令：

sqoop list-tables \
--connect jdbc:mysql://mycat01:3306/mysql \
--username root --P;

执行结果如下：

[hadoop@mycat01 ~]$ sqoop list-tables --connect jdbc:mysql://mycat01:3306/mysql --username root --P;
......
19/03/29 00:42:23 INFO sqoop.Sqoop: Running Sqoop version: 1.4.7
Enter password: 
19/03/29 00:42:26 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
columns_priv
db
event
........
time_zone
........
user

2.MySQL与HDFS、hive数据导入导出

1）MySQL相关参数

① 连接相关的参数

--driver 指定连接的驱动类
--connect 用于指定连接的url
--username 用于指定连接的用户名
--password 用于指定连接的用户的密码。
--direct 如果源表时MySQL可加快导入速度
--default-character-set=utf8 设置导入时编码集

② 源数据库相关参数

--table 指定导入时的源表
--columns 指定导入的列，多个使用逗号分隔

③ 过滤与切分字段

--where 指定过滤条件
--query 通过查询过滤
--split-by 按照某个字段进行物理切片，即划分工作单元的表字段，即根据某个字段进行分成多个区域去并行执行maptask,对于int类型，严格取该字段的最大值与最小值，然后将其整数平均，例如：最大值1000，最小值0，如果指定maptask数量为5，那么就划分成0-200,201-400，401-600,601-800,801-1000，假如说该字段分布不均匀，那么每一个maptask处理就不均匀了，甚至导致有的maptask几乎没有数据处理的情况，当数据量比较大的时候可能会导致数据倾斜的情况，比如说一个maptask处理3000w条数据，另一个maptask处理3条数据。
--m 指定maptask的并行数

④ 增量

--check-column 设置增量检查列
--incremental 指定增量类型 append追加模式  lastmodifed最后修改模式
--last-value 指定上次导出的最后一条记录的检查列的值

2）目标HDFS相关参数

--target-dir 指定hdfs目标目录
--fields-terminated-by  指定mysql导入hdfs的文件列之间的分割符的

--delete-target-dir  如果fetch目录存在则删除

3）目标Hive相关参数

--hive-import 导入数据到hive，并且使用hive默认的分隔符（如果没有设置的话）
--create-hive-table 创建hive目标表，如果目标表存在则导入失败
--external-table-dir hdfs路径   设置外部表的存储路径
--hive-table 目标hive表名
--hive-database 目标hive数据库名
--hive-partition-key 设置导入hive的分区字段
--hive-partition-value 设置导入到hive的分区字段值
--map-column-hive <arg>  数据源指定列到hive的数据类型
--hive-overwrite 覆盖已存在的hive表数据

4）输入分隔符

--input-fields-terminated-by <char> 指定输入源的字段分隔符  
--input-lines-terminated-by <char> 指定输入源的行分隔符 
--input-enclosed-by <char>  对字段值前后加上指定字符
--input-escaped-by <char>  对对含有转义符的字段做转义处理
--input-optionally-enclosed-by <char>  给带有单引号或双引号的字段前面加上指定字符

5）输出分隔符

--fields-terminated-by  <char>  指定输出源的属性分隔符
--lines-terminated-by  <char>   指定输出源的行分隔符
--mysql-delimiters      使用MySQL的默认分隔符设置，属性分隔符`逗号`,行分隔符`\n`
--enclosed-by  <char>  给字段值前加上指定的字符
--escaped-by   <char>   对字段值中的双引号加转义符
--input-optionally-enclosed-by <char>   对字段值中的双引号或单引号加转义符

3.测试

1）导出MySQL数据库的mysql库的user表的user,host字段到hive表

① 探索1

sqoop import --connect jdbc:mysql://mycat01:3306/mysql --username root -password miku --table user --columns user,host --hive-import --create-hive-table --hive-database mktest --m 1;

这里直接报错了：

19/03/30 03:52:48 ERROR util.SqlTypeMap: It seems like you are looking up a column that does not
19/03/30 03:52:48 ERROR util.SqlTypeMap: exist in the table. Please ensure that you've specified
19/03/30 03:52:48 ERROR util.SqlTypeMap: correct column names in Sqoop options.
19/03/30 03:52:48 ERROR tool.ImportTool: Import failed: column not found: user

说明导入失败了，问题是列名不对应（我们先不解决）。但是由于导入到hive前数据先会被fetch到hdfs，所以你会在/user/hadoop目录下看到一个user目录，即/user/hadoop/user,里面存有：

Permission	Owner	Group	Size	Last Modified	Replication	Block Size	Name
-rw-r--r--	hadoop	supergroup	0 B	2019/3/30 上午3:42:47	3	128 MB	_SUCCESS
-rw-r--r--	hadoop	supergroup	35 B	2019/3/30 上午3:42:47	3	128 MB	part-m-00000

说明数据fetch成功，但是没有在hive中创建表（数据也是对的）。可见/user/hadoop是fetch的默认目录，你再次执行上面的sqoop命令:

19/03/30 04:00:29 ERROR tool.ImportTool: Import failed: org.apache.hadoop.mapred.FileAlreadyExistsException: Output directory hdfs://mkmg/user/hadoop/user already exists

这个报错，我们不陌生，就是因为之前操作已经fetch数据到/user/hadoop/user下，你再次执行，fetch的话，因为目录存在，所以会报错。因为user这级目录是程序创建的，名称和前面源数据库的表名保持一致，注意语句中的--create-hive-table只是对hive设置的，与hdfs没半毛钱关系。

② 探索2

创建hive表

hive> create table user(name string,host string);

报错了，只好：

create table users(name string,host string);

然后是：

19/03/30 04:40:02 ERROR tool.ImportTool: Import failed: org.apache.hadoop.mapred.FileAlreadyExistsException: Output directory hdfs://mkmg/user/hadoop/user already exists

当然，这个还是因为fetch的时候已经检测到该hdfs目录，所以后面我直接用--delete-target-dir

sqoop import --connect jdbc:mysql://mycat01:3306/mysql --username root -password miku --table user --columns user,host --hive-import --create-hive-table `--delete-target-dir --hive-table users` --hive-database mktest --m 1;

当然自始至终都存在以下问题：

column user not found----- user 列没找到

既然这样那我就换种方式给字段起别名看看(columns不支持给字段起别名)

sqoop import --connect jdbc:mysql://mycat01:3306/mysql --username root -password miku --query "select user name,host from user where \$CONDITIONS" --hive-import  `--target-dir /user/hadoop/users --delete-target-dir` --hive-table users  --hive-database mktest --m 1;

终于可以了：

hive> select * from users;
OK
root	%
root	localhost
root	mycat01

当然，你不指定--target-dir的话也会报错的：(指定fetch的目录)这份数据即使你不想要，也必须加这个选项，可以使用--delete-target-dir在执行完任务时删除已存在的hdfs目录，重新fetch。

Must specify destination with --target-dir. 
Try --help for usage instructions.

我们再回头试试指定hive表名为user看看（让sqoop自动创建表）

sqoop import --connect jdbc:mysql://mycat01:3306/mysql --username root -password miku --query "select user name,host from user where \$CONDITIONS" --hive-import  --target-dir /user/hadoop/users --delete-target-dir --hive-table user  --hive-database mktest --m 1;

执行结果如下：

19/03/30 05:34:07 INFO hive.HiveImport: Loading data to table mktest.user
19/03/30 05:34:08 INFO hive.HiveImport: OK
19/03/30 05:34:08 INFO hive.HiveImport: Time taken: 1.102 seconds
19/03/30 05:34:08 INFO hive.HiveImport: Hive import complete.
19/03/30 05:34:08 INFO hive.HiveImport: Export directory is contains the _SUCCESS file only, removing the directory. # 移除hdfsfetch操作的目录

如果你觉得成功了，那就大错特错了。

hive> select * from user;
NoViableAltException(309@[])
	at org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.fromSource(HiveParser_FromClauseParser.java:1612)
	at org.apache.hadoop.hive.ql.parse.HiveParser_FromClauseParser.fromClause(HiveParser_FromClauseParser.java:1312)
	at org.apache.hadoop.hive.ql.parse.HiveParser.fromClause(HiveParser.java:41910)

到hive中查询直接报错，说白了就是表名使用了hive关键字。（然后你会发现user这个表在hive里面删除不了了）

解决方案：（注意表名大小写问题）

[hadoop@mycat01 root]$ hdfs dfs -rm -r -f /user/hadoop/hive/warehouse/mktest.db/user

查找hive元数据所在表记录（我的hive元数据存在MySQL的hivedb数据库中）

mysql> select * from DBS;  -- 找到user表所在库的id，我这里是6

mysql> select * from TBLS;  -- 找到user表所在库id为6的user表的表id，我这里是16

mysql> select * from COLUMNS_V2;  -- 找到user表表id（即CD_ID）为16的表的字段记录，我这里是有两条

开始删除记录了

delete from COLUMNS_V2 where CD_ID=16; # 删除表id为16的记录

delete from TABLE_PARAMS where TBL_ID=16; # 删除表参数表

delete from TBL_PRIVS where TBL_ID=16;

delete from TBLS where TBL_NAME='user' and DB_ID=6;

至此user表的相关数据删除完毕，此方式比较麻烦，你可以采用图形化工具连接到MySQL再删除这几张表而定记录。

我们发现的问题
1.hive建表时，无论是表名还是字段名都对关键字敏感，拿上面的为例，hive中创建`user`表时成功不了的，即便是字段中任何一个字段名是`user`都不可以的。
2.从MySQL导入过来的数据并没有直接进hive表，而是在这之前有一个fetch数据到hdfs的过程。
3.数据从MySQL到hive，目标表不存在的话，可以通过`--create-hive-table`,但是`hive-table`指定的是hive的关键字时，在hive中该表可以被创建，但是你在hive中查询该表的时候就报错了。
4.如果源数据库的表字段和目标表的字段名不一致，会直接报错的。

解决方案：

对于敏感词，可以在hive建表时换其他的名字（表名或字段名）。
因为MySQL到hive导入的时候第一步就是将数据fetch拉取到hdfs一个目录，通过--target-dir来指定，当然由于每次执行命令都会fetch，所以为了便于测试，通常使用--delete-target-dir指定如果fetch的目标目录已存在，则删除，重新fetch。当然，最后sqoop任务执行完，这个新的目录还是会被删除的。
对于第三点，需要特别注意，导入到hive的表名，字段名都不要是关键字。不然数据导入是成功不了的（任务执行结果是成功的，但是你去查询hive的结果表的时候会直接报错的）
对于MySQL与hive导入时字段不对应问题，可以通过query来解决。

于是MySQL到hive：

sqoop import 
--connect jdbc:mysql://mycat01:3306/mysql 
--username root -password miku 
--query "select user name,host from user where \$CONDITIONS" # 此方式比较灵活，可以给字段起别名等，--columns 则不行
--hive-import  
--target-dir /user/hadoop/users # 这个必须指定
--delete-target-dir  # 这个是为了在执行完删除上面 target-dir指定的目录的，可选
--create-table # 如果目标hive表不存在时会创建，存在时则会报错，没有改项时，需要在hive中手动创建这个表，是可选项
--hive-table users  # 目标hive表，必须指定，之一不能使用hive的关键字
--hive-database mktest # 目标hive数据库，默认default库，可选
--hive-overwrite  # 是否覆盖导入，没有该项的话是追加，可选项
--m 1; # 指定使用一个maptask运行，多个的时候，必须指定 split-by

2）从MySQL导入数据到hdfs（导出文件默认分隔符是逗号）

sqoop import \
--connect jdbc:mysql://mycat01:3306/mysql \
--username root -password miku \
--query "select user name,host from user where \$CONDITIONS" \
--target-dir /user/hadoop/users \
--m 1;

这个使用注意事项是：如果--target-dir指定的目录存在的话，直接报错：

19/03/30 06:55:15 ERROR tool.ImportTool: Import failed: org.apache.hadoop.mapred.FileAlreadyExistsException: Output directory hdfs://mkmg/user/hadoop/users already exists
	at org.apache.hadoop.mapreduce.lib.output.FileOutputFormat.checkOutputSpecs(FileOutputFormat.java:146)
	at org.apache.hadoop.mapreduce.JobSubmitter.checkSpecs(JobSubmitter.java:266)
	at org.apache.hadoop.mapreduce.JobSubmitter.submitJobInternal(JobSubmitter.java:139)

这里可以使用--delete-target-dir指定，表示如果存在那个hdfs目录则删除，重新fetch

3）关于`--hive-overwrite`的使用

在不使用hive-overwite时：

sqoop import --connect jdbc:mysql://mycat01:3306/mysql --username root -password miku --query "select user name,host from user where \$CONDITIONS" --hive-import  --target-dir /user/hadoop/users --delete-target-dir --hive-table users  --hive-database mktest --m 1;

结果：

hive> select * from users;
OK
root	%
root	localhost
root	mycat01

同样的语句我再执行一遍：

hive> select * from users;
OK
root	%
root	localhost
root	mycat01
root	%
root	localhost
root	mycat01

可以看到，相同记录内容追加了，但是这不是我们想要的，所以：

sqoop import --connect jdbc:mysql://mycat01:3306/mysql --username root -password miku --query "select user name,host from user where \$CONDITIONS" --hive-import  --target-dir /user/hadoop/users --delete-target-dir `--hive-overwrite` --hive-table users  --hive-database mktest --m 1;

再来看看结果：

hive> select * from users;
OK
root	%
root	localhost
root	mycat01

最后放一个Oracle到hive的：

sqoop import \
--hive-import \
--connect jdbc:oracle:thin:@//10.10.xx.xx:1521/apexsmp \
--username xxx \
--password xxx \
--query "SELECT * FROM fpss.tfp_cpfe where 1=1 AND \$CONDITIONS " \
--target-dir /user/hadoop/targetDir/ODS.T_FPSS_TFP_CPFE_EXT \
--delete-target-dir \
--split-by ID \
--hive-overwrite \
--m 5 \
--hive-table ODS.T_FPSS_TFP_CPFE_EXT;

猫君之上

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Apache Sqoop的数据导入之MySQL与Hive&Hdfs数据导入导出参数分类

我们知道sqoop命令最终还是会解释为mapreduce代码执行，但是有一点值得注意的是，sqoop的数据迁移对应的只有maptask，没有reducetask，也就是说基本上不用担心数据倾斜问题了。最核心的sqoop命令就类似hive 一样，目的是为了启动一个客户端。1.外围指令（不涉及到数据导入导出相关的）1) 查看当前MySQL中有哪几个数据库list-databasessqo...
复制链接

扫一扫