Hive数据仓库

概念

数据库

1. 业务应用
2. 操作性处理
3. 联机事务处理（OLTP）
4. 面向交易
5. 存放的是实时数据（在线数据）
6. 数据库设计遵循三大范式，尽量避免冗余

数据仓库

1. 面向数据分析
2. 依照分析需求、分析维度、分析指标进行设计
3. 存放的数据都是历史数据
4. 联机分析处理（OLAP）

Hive数据仓库

概念

1. Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射成一张数据库表，并提供类SQL查询功能。
2. Hive其实就是一个SQL解析引擎，它将SQL语句转译成M/R JOB然后在Hadoop上执行，以达到快速开发的目的。
3. 它的表其实就是一个Hadoop的目录/文件。
4. 不存储和计算数据。
5. 为大数据批量处理而生。

体系结构

Hive各模块组成

Hive运行机制

Hive安装模式

1. 本地模式
   只允许一个用户，元数据存储在derby数据库中，在一台服务器中
2. 单用户模式
   只允许一个用户，元数据存储在单独mysql服务器中
3. 多用户模式
   可以有多个用户，中间有一个元数据服务器

Hive环境搭建

1. Hadoop环境
2. 安装mysql数据库:
   1）yum install mysql-server
   2）启动mysql服务 :
   ①临时启动：chkconfig --> service mysqld start --> service mysqld status(显示进程id) --> ss -nal (显示端口号)
   ②永久启动：chkconfig mysqld on --> chkconfig mysqld --list (查看是否启动) --> mysql (进入mysql,首次没有密码) --> use mysql; --> show tables;
   3）设置开机启动
   4）修改权限:
   接着上面 --> select user,host,password from user; --> GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘root’ WITH GRANT OPTION; --> flush privileges; --> select user,host,password from user;
   5）windows中的navicat连接测试（密码root）
   
   连接测试成功后 --> 创建navicat连接 --> delete from user where host!=’%’; --> flush privileges --> exit; --> mysql --> mysql -u root -p --> root(密码) --> exit;
3. Hive安装
   ①上传hive所需的文件到node01根目录下，使用winSCP
   ②将这两个文件传给node02：scp apache-hive-2.0.1-bin.tar.gz mysql-connector-java-5.1.39.jar node02:pwd
   ③在node02解压：tar -zxvf apache-hive-2.0.1-bin.tar.gz -C /opt/hpe/
   ④给刚解压的文件夹改名：mv apache-hive-2.0.1-bin/ hive-2.0.1
   ⑤cd hive-2.0.1/ --> pwd (复制hive的路径) --> vi /etc/profile --> 加上export HIVE_HOME=/opt/hpe/hive-2.0.1一行，并在PATH最后加上:$HIVE_HOME/bin
   ⑥cd $HIVE_HOME --> cd conf --> cp hive-default.xml.template hive-site.xml --> vi hive-site.xml --> 从下一行鼠标停住，按下shift+:进入命令模式，输入 . , $-1d回车（删除之间的所有行)
   ⑦配置hive-site.xml

<property>  
  <name>hive.metastore.warehouse.dir</name>  
  <value>/user/hive_remote/warehouse</value>  
</property>  
<property>  
  <name>hive.metastore.local</name>  
  <value>true</value>  
</property>  
<property>  
  <name>javax.jdo.option.ConnectionURL</name>  
  <value>jdbc:mysql://node01/hive_remote?createDatabaseIfNotExist=true</value>  
</property>  
<property>  
  <name>javax.jdo.option.ConnectionDriverName</name>  
  <value>com.mysql.jdbc.Driver</value>  
</property>  
<property>  
  <name>javax.jdo.option.ConnectionUserName</name>  
  <value>root</value>  
</property>   
<property>  
  <name>javax.jdo.option.ConnectionPassword</name>  
  <value>root</value>  
</property>

⑧将MySQL驱动包放在hive的lib目录下即可：
先定位到hive的lib下 --> cp /root/mysql-connector-java-5.1.39.jar ./
⑨Hive安装初始化：schematool -initSchema -dbType mysql
⑩启动hive：在目录/opt/hpe/hive-2.0.1/bin下输入hive --> quit;(关闭hive)

Hive单用户模式操作

1. 启动hadoop集群
2. hive (node02中启动hive) --> show databases; --> create table tbl0(id int,age int); --> desc tbl0;（查看表信息或详细查看表信息：desc formatted tbl0;）–> insert into tbl0 values(1,9);此时点击web的node03:8088可以查看到在执行mapreduce作业。

Hive多用户模式操作

节点	任务
node01	mysql
node03	MetaStoreServer
node04	Hive Client

1. 实现node01~04全部免密钥：实现一个节点免密钥，再分发覆盖即可。scp authorized_keys node01:pwd
2. 从node02分发hive文件夹到node03~04:scp apache-hive-2.0.1-bin.tar.gz node03:pwd
3. node03~04解压刚才的hive文件并改名：tar -zxvf apache-hive-2.0.1-bin.tar.gz -C /opt/hpe/ --> mv apache-hive-2.0.1-bin/ hive-2.0.1
4. 将node02的配置文件profile分发到node03~04 : scp /etc/profile node03:/etc/
5. node03中：cd $HIVE_HOME --> cd conf --> cp hive-default.xml.template hive-site.xml --> vi hive-site.xml

<property>  
  <name>hive.metastore.warehouse.dir</name>  
  <value>/user/hive/warehouse</value>  
</property>  

<property>  
  <name>javax.jdo.option.ConnectionURL</name>  
  <value>jdbc:mysql://192.168.228.11:3306/hive?createDatabaseIfNotExist=true</value>  
</property>  

<property>  
  <name>javax.jdo.option.ConnectionDriverName</name>  
  <value>com.mysql.jdbc.Driver</value>  
</property> 
    
<property>  
  <name>javax.jdo.option.ConnectionUserName</name>  
  <value>root</value>  
</property>  

<property>  
  <name>javax.jdo.option.ConnectionPassword</name>  
  <value>root</value>  
</property>

–> 和上面一样将MySQL驱动jar存放到hive的lib下 --> Hive安装初始化（hive服务端）：schematool -initSchema -dbType mysqllin
7. 启动hive服务：hive --service metastore&（加上&可以让命令在后台执行） 服务端口号9083 --> 新建表，添加数据等尝试
8. node04上 --> cp 得到 hive-site.xml --> vi hive-site.xml --> 删除之间的内容，并添加保存

<property>  
  <name>hive.metastore.warehouse.dir</name>  
  <value>/user/hive/warehouse</value>  
</property>  
   
<property>  
  <name>hive.metastore.local</name>  
  <value>false</value>  
</property>  
  
<property>  
  <name>hive.metastore.uris</name>  
  <value>thrift://node03:9083</value>  
</property>

10. node04启动hive：hive --> 新建表tbl、插入数据1、查询数据尝试 --> 再打开一个node04的连接 --> hdfs dfs -ls /user/hive/warehouse/tbl --> hdfs dfs -cat /user/hive/warehouse/tbl/* --> 在/opt/hpe下将客户端配置分发给node01一份 : scp -r hive-2.0.1/ node01:pwd --> 在node01的/etc/profile文件中添加export HIVE_HOME=/opt/hpe/hive-2.0.1 并在PATH中添加相应bin目录 --> 启动node01的hive:hive --> show tables;查询得到的表是一样的（多用户）

Hive数据类型

Hive实操案例

1. 编写创建表的语句（在notepad++里）：

人员表
id,姓名,爱好,地址

1,小明1,lol-book-movie,jining:hpe-shanghai:pudong
2,小明2,lol-book-movie,jining:hpe-shanghai:pudong
3,小明3,lol-book-movie,jining:hpe-shanghai:pudong
4,小明4,lol-book-movie,jining:hpe-shanghai:pudong
5,小明5,lol-book-movie,jining:hpe-shanghai:pudong
6,小明6,lol-book-movie,jining:hpe-shanghai:pudong
7,小明7,lol-book-movie,jining:hpe-shanghai:pudong
8,小明8,lol-book-movie,jining:hpe-shanghai:pudong

-- 创建内部表
CREATE TABLE psn0(
id int,
name string,
likes ARRAY<string>,
address MAP<string,string>)
ROW FORMAT
DELIMITED FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':';

2. 将创建内部表的语句赋值粘贴到node04的hive>下
3. 第二个node04回到家目录 cd --> vi data1 --> 粘贴上面1~8小明的数据记录保存 --> 将数据导入select * from psn0;查看

-- 导入数据
LOAD DATA LOCAL INPATH '/root/data1' INTO TO TABLE psn0;

4. node04创建外部表

-- 创建外部表
CREATE EXTERNAL TABLE psn1(
id int,
name string,
likes ARRAY<string>,
address MAP<string,string>)
ROW FORMAT
DELIMITED FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':'
LOCATION '/psn1';

5. 导入数据

-- 导入数据
LOAD DATA LOCAL INPATH '/root/data1' INTO TABLE psn1;

6. 创建分区表

-- 创建分区表
PARTITIONED BY (字段名，数据类型 可以接很多字段)

CREATE TABLE psn2(
id int,
name string,
likes ARRAY<string>,
address MAP<string,string>)
PARTITIONED BY (age int)
ROW FORMAT
DELIMITED FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':';

7. 将创建分区表语句粘贴到node04的hive下，导入数据

-- 导入数据
-- 如果目标表是分区表，需要使用partition来指定分区
-- 这里的age=10就是一个目录，下面就存放的导入的数据
LOAD DATA LOCAL INPATH '/root/data1' INTO TABLE psn2 partition (age=10);

–> select * from psn2 where age=10;(数据量很大的时候效率就提高了)
8. 创建两个分区字段的分区表

CREATE TABLE psn3(
id int,
name string,
likes ARRAY<string>,
address MAP<string,string>)
PARTITIONED BY (sex string,age int)
ROW FORMAT
DELIMITED FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':';

操作步骤和上面一个分区分区表类似。
9. 删除、增加分区表：

-- 增加分区
alter table psn3 add partition(sex='boy',age=10);
-- 删除分区
alter table psn3 drop partition(sex='boy',age=10);

10. 克隆创建表

-- create table like
-- 创建表的时候，带过来结构
create table psn4 like psn2;
-- create table as select
-- 创建表的时候，带过来结果和数据
create table newtable as select id,name from psn2;

Hive表

一、内部表 MANAGED_TABLE
删除内部表的时候，不仅会删除元数据，真实数据也会被删除：drop table psn0;
二、外部表 EXTERNAL_TABLE
删除外部表的时候，仅仅会删除元数据，真实数据不会被删除：drop table psn1;

计算掉话率

公式：掉话率=总的掉话时间/总的通话时间

步骤

1. 创建表 cell_monitor,创建结果表 drop_monitor
2. 导入数据 load data
3. 统计分析取出掉话率最高的前10个基站信息

操作

1. 创建表

create table cell_monitor(
record_time string,
imei string,
cell string,
ph_num int,
call_num int,
drop_num int,
duration int,
drop_rate DOUBLE,
net_type string,
erl string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

create table cell_drop_monitor(
imei string,
total_call_num int,
total_drop_num int,
d_rate DOUBLE
) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

2. winSCP将数据文件上传到node04的/root/下 --> 改名为cdr.csv --> load data local inpath ‘/root/cdr.csv’ into table cell_monitor;
3. node04 hive中

-- 找出掉线率最高的基站
from cell_monitor cm 
insert overwrite table cell_drop_monitor  
select cm.imei , sum(cm.duration), sum(cm.drop_num), sum(cm.drop_num)/sum(cm.duration) d_rate 
group by cm.imei 
sort by d_rate desc;

4. select * from cell_drop_monitor limit 10; --> quit;

补充

1. Linux中esc模式按u是撤销
2. 不是所有的hive数据库语句执行都会转换为mapreduce作业
3. 查看进程号命令：ss -naltp
4. 导入数据的时候
   1）从Linux集群导入：LOAD DATA LOCAL INPATH ‘/root/data1’ INTO TABLE psn1;
   该模式原Linux数据文件依然存在
   2）从hdfs分布式文件系统导入：LOAD DATA INPATH ‘/root/data1’ INTO TABLE psn1;
   该模式原分布式数据文件不存在
5. load适合批量导入数据，from适合统计分析