使用sqoop将hive数据库导入至mysql

前言：本文由实现此博客过程中遇到的问题及解决办法整理而成。博客：淘宝双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

报错反思

1. 在遇到报错时，没能保留过程，导致报错解决后，无法对比反思报错原因

2. 解决报错时，需要找到报错的核心部分，如：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)

Linux系统中文件行末尾出现^M的原因及解决办法 - WayneLiu123 - 博客园 (cnblogs.com)