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

大数据学习路线图

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

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

版权声明:版权归厦门大学数据库实验室所有,请勿用于商业用途;未经授权,其他网站请勿转载

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

所需知识储备

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

训练技能

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

任务清单

  1. Hive预操作
  2. 使用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预测回头客行为