前言：本文由实现此博客过程中遇到的问题及解决办法整理而成。博客：淘宝双11数据分析与预测课程案例-步骤三:将数据从Hive导入到MySQL_厦大数据库实验室
操作前准备
数据
数据放在/usr/local/data/comment.csv
数据来源于：Amazon Reviews: Unlocked Mobile Phones | Kaggle
大数据开发环境
| 软件 | 版本 |
|---|---|
| hadoop | 2.7 |
| mysql | 5.7 |
| hive | 2.1.0 |
| sqoop | 1.4.6 |
未知hive2.1.0和sqoop1.4.7是否有兼容性问题，推荐使用sqoop1.4.6
数据预处理
删除第一行表头
# 1d表示删除第1行，同理，3d表示删除第3行，nd表示删除第n行
cd /usr/local/data
sed -i '1d' comment.csv
head -n 5 comment.csv
查看是否存在多余字符或空行
使用vim查看文件内容以及是否有多余的无关符号。虽然编辑器gedit也可查看文件内容，但是不能显示所有字符，故推荐使用vim
-
若每行行尾存在字符
^M，可能出现的原因是：该文件在导入Linux系统前在Windows系统下编辑过解决办法：通过字符替换，将
^M换掉具体操作：利用
vim打开文件，然后输入vim命令%s/^M$//g，进行全文件查找替换。注意：
^M不是键盘中的符号直接输入，而是使用快捷键，^使用ctrl+v来输入，M使用ctrl+m来输入
导入过程
启动hadoop
cd /usr/local/java/hadoop/hadoop-2.7.1
./sbin/start-dfs.sh
在HDFS中创建目录
hadoop fs -ls /
hadoop fs -mkdir -p /data/amazon
将文件上传至HDFS中
hadoop fs -put /usr/local/data/comment.csv /data/amazon
查看文件内容
hadoop fs -cat /data/amazon/comment.csv | head -5
启动mysql
service mysql start
启动hive
cd /usr/local/java/hive-2.1.0
./bin/hive
选择amazon数据库
hive> use amazon;
创建hive外部表
# 用 /data/amazon 创建 amazon数据库中 goods 表
hive> CREATE EXTERNAL TABLE amazon.goods(product_name STRING,brand_name STRING,price INT,rating INT,reviews STRING,votes INT) COMMENT 'Welcome to shandong. Now create amazon!' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE LOCATION '/data/amazon';
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','：以,结尾的行格式分隔字段
分隔符号的选择：在HDFS(localhost:hdfs:50070)中下载文件，使用vim查看文件分隔符以及是否有多余无关符号
查询hive表中的数据
hive> select * from goods limit 10;
hive> exit;
hive数据库中表行尾$
查看文件分隔符
hive -e "select * from amazon.goods limit 10" >> res
head -n 2 res | cat -A
以下为输出结果，分隔符号为^I
"""CLEAR CLEAN ESN"" Sprint EPIC 4G Galaxy SPH-D700*FRONT CAMERA*ANDROID*SLIDER*QWERTY KEYBOARD*TOUCH SCREEN"^ISamsung^I199^I4^I"nice phone^INULL$
"""CLEAR CLEAN ESN"" Sprint EPIC 4G Galaxy SPH-D700*FRONT CAMERA*ANDROID*SLIDER*QWERTY KEYBOARD*TOUCH SCREEN"^ISamsung^I199^I5^IVery pleased^I0$
创建临时表 inner_goods
Hive会自动在HDFS文件系统(/hive/warehouse/amazon.db/inner_goods)中创建对应的数据文件
hive> create table amazon.inner_goods(product_name STRING,brand_name STRING,price INT,rating INT,reviews STRING,votes INT) COMMENT 'Welcome to shandong. Now create inner table inner_goods ' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;
将amazon外部表中的数据插入到 inner_goods
hive> INSERT OVERWRITE TABLE amazon.inner_goods select * from amazon.goods;
查看插入命令是否成功执行
hive> select * from inner_goods limit 10;
登录mysql
mysql -u root -p
mysql> show databases; #显示所有数据库
mysql> create database amazon; #创建dbtaobao数据库
mysql> use amazon; #使用数据库
查看数据库的编码
mysql> show variables like "char%";
设置| character_set_server | utf8
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
在[mysqld]下添加一行character_set_server=utf8
重启MySQL服务。service mysql restart
登陆MySQL，并查看MySQL目前设置的编码。show variables like "char%";
在amazon数据库中创建表amazon
mysql> CREATE TABLE `amazon`.`amazon` (`product_name` varchar (1500), `brand_name` varchar(300), `price` float, `rating` int, `reviews` varchar(20000), `votes` int) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mysql> exit;
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535.
实际操作中发现
VARCHAR最大限制21845个字符
# 导入数据
cd /usr/local/java/sqoop-1.4.6
bin/sqoop export --connect jdbc:mysql://localhost:3306/amazon --username your_username --password your_password --table amazon --export-dir '/hive/warehouse/amazon.db/inner_goods' --fields-terminated-by ',' --input-null-non-string '\\N';
# jdbc:mysql://localhost:3306/amazon amazon为mysql的数据库名
# --username your_username --password sdufewsp 登录mysql的用户名和密码
# --table amazon 从amazon表中导出数据到sqoop
# --export-dir '/hive/warehouse/amazon.db/inner_goods' HDFS中的导出目录
# --fields-terminated-by ',' 分隔符
# --input-null-non-string 忽略表数据中的空值
java.lang.RuntimeException: Can't parse input data: '\N'，For input string: "\N"
主要是因为hive 中的表数据为空导致的，添加一个参数忽略空：--input-null-non-string '\\N'
下载mysql-workbench
通过Mysql Workbrench可视化查看表内容
sudo apt-get install mysql-workbench
报错反思
-
在遇到报错时，没能保留过程，导致报错解决后，无法对比反思报错原因
-
解决报错时，需要找到报错的核心部分，如：
caused by……、error
参考文章：
【SQOOP】 sqoop java.lang.RuntimeException: Can‘t parse input data: ‘\N‘，For input string: “\N“_Zsigner的博客-CSDN博客
Sqoop工具从hive导出数据到MySQL踩坑记 - 简书 (jianshu.com)文章来源：https://www.toymoban.com/news/detail-446110.html
Linux系统中文件行末尾出现^M的原因及解决办法 - WayneLiu123 - 博客园 (cnblogs.com)文章来源地址https://www.toymoban.com/news/detail-446110.html
到了这里，关于使用sqoop将hive数据库导入至mysql的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
