《淘宝双11数据分析与预测课程案例—步骤三：将数据从Hive导入到MySQL》

开发团队：厦门大学数据库实验室联系人：林子雨老师 ziyulin@xmu.edu.cn

本教程介绍大数据课程实验案例“淘宝双11数据分析与预测”的第三个步骤，将数据从Hive导入到MySQL。从数据导入到MySQL是为了后续数据可视化，服务端读取MySQL中的数据，渲染到前端ECharts页面。在实践本步骤之前，请先完成该实验案例的第一个步骤——本地数据集上传到数据仓库Hive，和第二个步骤——Hive数据分析。这里假设你已经完成了前面的这两个步骤。

所需知识储备

数据仓库Hive概念与基本原理、关系数据库概念与基本原理、SQL语句

训练技能

数据仓库Hive的基本操作、关系数据库MySQL的基本操作、Sqoop工具的使用方法

任务清单

Hive预操作
使用Sqoop将数据从Hive导入MySQL

一、准备工作

本教程需要安装Hive、MySQL和Sqoop。在前面的第一个步骤中，我们在安装Hive的时候就已经一起安装了MySQL（因为我们采用MySQL来存储Hive的元数据），所以，现在你只需要再安装Sqoop。
（1）请参考厦大数据库实验室博客Ubuntu安装Sqoop，完成Sqoop的安装。本教程下载的是sqoop-1.4.6.bin__hadoop-2.0.4-alpha.tar.gz，安装目录是“/usr/local/sqoop”。虽然这个sqoop是为hadoop2.0.4版本开发的，本教程Hadoop版本是2.7.1，但是，依然可以顺利使用。

二、Hive预操作

如果你还没有启动Hive，请首先启动Hive。
请登录Linux系统（本教程统一采用hadoop用户名登录系统），然后，打开一个终端（可以按快捷键Ctrl+Alt+T）。
本教程中，Hadoop的安装目录是“/usr/local/hadoop”，Hive的安装目录是“/usr/local/hive”。
因为需要借助于MySQL保存Hive的元数据，所以，请首先启动MySQL数据库，请在终端中输入下面命令：

service mysql start  # 可以在Linux的任何目录下执行该命令

由于Hive是基于Hadoop的数据仓库，使用HiveQL语言撰写的查询语句，最终都会被Hive自动解析成MapReduce任务由Hadoop去具体执行，因此，需要启动Hadoop，然后再启动Hive。

请执行下面命令启动Hadoop（如果你已经启动了Hadoop就不用再次启动了）：

cd /usr/local/hadoop
./sbin/start-all.sh

然后，执行jps命令看一下当前运行的进程：

jps

如果出现下面这些进程，说明Hadoop启动成功了。

3765 NodeManager
3639 ResourceManager
3800 Jps
3261 DataNode
3134 NameNode
3471 SecondaryNameNode

下面，继续执行下面命令启动进入Hive：

cd /usr/local/hive
./bin/hive   #启动Hive

通过上述过程，我们就完成了MySQL、Hadoop和Hive三者的启动。
启动成功以后，就进入了“hive>”命令提示符状态，可以输入类似SQL语句的HiveQL语句。

然后，在“hive>”命令提示符状态下执行下面命令：

1、创建临时表inner_user_log和inner_user_info

hive> create table dbtaobao.inner_user_log(user_id INT,item_id INT,cat_id INT,merchant_id INT,brand_id INT,month STRING,day STRING,action INT,age_range INT,gender INT,province STRING) COMMENT 'Welcome to XMU dblab! Now create inner table inner_user_log ' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;

这个命令执行完以后，Hive会自动在HDFS文件系统中创建对应的数据文件“/user/hive/warehouse/dbtaobao.db/inner_user_log”。

2、将user_log表中的数据插入到inner_user_log,
在[大数据案例-步骤一:本地数据集上传到数据仓库Hive(待续)]中，我们已经在Hive中的dbtaobao数据库中创建了一个外部表user_log。下面把dbtaobao.user_log数据插入到dbtaobao.inner_user_log表中，命令如下：

hive> INSERT OVERWRITE TABLE dbtaobao.inner_user_log select * from dbtaobao.user_log;

请执行下面命令查询上面的插入命令是否成功执行：

hive> select * from inner_user_log limit 10;

三、使用Sqoop将数据从Hive导入MySQL

1、启动Hadoop集群、MySQL服务
前面我们已经启动了Hadoop集群和MySQL服务。这里请确认已经按照前面操作启动成功。

2、将前面生成的临时表数据从Hive导入到 MySQL 中，包含如下四个步骤。
(1)登录 MySQL
请在Linux系统中新建一个终端，执行下面命令：

mysql –u root –p

为了简化操作，本教程直接使用root用户登录MySQL数据库，但是，在实际应用中，建议在MySQL中再另外创建一个用户。
执行上面命令以后，就进入了“mysql>”命令提示符状态。
(2)创建数据库

mysql> show databases; #显示所有数据库
mysql> create database dbtaobao; #创建dbtaobao数据库
mysql> use dbtaobao; #使用数据库

注意：请使用下面命令查看数据库的编码：

mysql> show variables like "char%";

会显示类似下面的结果：

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

请确认当前编码为utf8，否则无法导入中文，请参考Ubuntu安装MySQL及常用操作修改编码。
下面是笔者电脑上修改了编码格式后的结果：

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

(3)创建表
下面在MySQL的数据库dbtaobao中创建一个新表user_log，并设置其编码为utf-8：

mysql> CREATE TABLE `dbtaobao`.`user_log` (`user_id` varchar(20),`item_id` varchar(20),`cat_id` varchar(20),`merchant_id` varchar(20),`brand_id` varchar(20), `month` varchar(6),`day` varchar(6),`action` varchar(6),`age_range` varchar(6),`gender` varchar(6),`province` varchar(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

提示：语句中的引号是反引号`，不是单引号’。需要注意的是，sqoop抓数据的时候会把类型转为string类型，所以mysql设计字段的时候，设置为varchar。
创建成功后，输入下面命令退出MySQL：

mysql> exit;

(4)导入数据(执行时间：20秒左右)
注意，刚才已经退出MySQL，回到了Shell命令提示符状态。下面就可以执行数据导入操作，

cd /usr/local/sqoop
bin/sqoop export --connect jdbc:mysql://localhost:3306/dbtaobao --username root --password root --table user_log --export-dir '/user/hive/warehouse/dbtaobao.db/inner_user_log' --fields-terminated-by ',';

字段解释：

./bin/sqoop export ##表示数据从 hive 复制到 mysql 中
--connect jdbc:mysql://localhost:3306/dbtaobao
--username root #mysql登陆用户名
--password root #登录密码
--table user_log #mysql 中的表，即将被导入的表名称
--export-dir '/user/hive/warehouse/dbtaobao.db/user_log ' #hive 中被导出的文件
--fields-terminated-by ',' #Hive 中被导出的文件字段的分隔符

3、查看MySQL中user_log或user_info表中的数据
下面需要再次启动MySQL，进入“mysql>”命令提示符状态：

mysql -u root -p

会提示你输入MySQL的root用户的密码，本教程中安装的MySQL数据库的root用户的密码是hadoop。
然后执行下面命令查询user_action表中的数据：

mysql> use dbtaobao;
mysql> select * from user_log limit 10;

会得到类似下面的查询结果：

+---------+---------+--------+-------------+----------+-------+------+--------+-----------+--------+-----------+
| user_id | item_id | cat_id | merchant_id | brand_id | month | day  | action | age_range | gender | province  |
+---------+---------+--------+-------------+----------+-------+------+--------+-----------+--------+-----------+
| 414196  | 1109106 | 1188   | 3518        | 4805     | 11    | 11   | 0      | 4         | 0      | 宁夏      |
| 414196  | 380046  | 4      | 231         | 6065     | 11    | 11   | 0      | 5         | 2      | 陕西      |
| 414196  | 1109106 | 1188   | 3518        | 4805     | 11    | 11   | 0      | 7         | 0      | 山西      |
| 414196  | 1109106 | 1188   | 3518        | 4805     | 11    | 11   | 0      | 6         | 0      | 河南      |
| 414196  | 1109106 | 1188   | 3518        | 763      | 11    | 11   | 2      | 2         | 0      | 四川      |
| 414196  | 944554  | 1432   | 323         | 320      | 11    | 11   | 2      | 7         | 2      | 青海      |
| 414196  | 1110009 | 1188   | 298         | 7907     | 11    | 11   | 2      | 3         | 1      | 澳门      |
| 414196  | 146482  | 513    | 2157        | 6539     | 11    | 11   | 0      | 1         | 0      | 上海市    |
| 414196  | 944554  | 1432   | 323         | 320      | 11    | 11   | 0      | 2         | 1      | 宁夏      |
| 414196  | 1109106 | 1188   | 3518        | 4805     | 11    | 11   | 0      | 7         | 0      | 新疆      |
+---------+---------+--------+-------------+----------+-------+------+--------+-----------+--------+-----------+
10 rows in set (0.03 sec)

从Hive导入数据到MySQL中，成功！

到这里，第三个步骤的实验内容顺利结束，请继续访问第四个步骤《步骤四:利用Spark预测回头客行为》