Hive数据仓库

概念

数据库

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

数据仓库

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

Hive数据仓库

概念

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

体系结构

Hive各模块组成

Hive运行机制

Hive安装模式

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

Hive环境搭建

Hadoop环境安装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;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单用户模式操作

启动hadoop集群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多用户模式操作

节点任务node01mysqlnode03MetaStoreServernode04Hive Client 实现node01~04全部免密钥：实现一个节点免密钥，再分发覆盖即可。scp authorized_keys node01:pwd从node02分发hive文件夹到node03~04:scp apache-hive-2.0.1-bin.tar.gz node03:pwdnode03~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将node02的配置文件profile分发到node03~04 : scp /etc/profile node03:/etc/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> 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实操案例

编写创建表的语句（在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 ':'; 将创建内部表的语句赋值粘贴到node04的hive>下第二个node04回到家目录 cd --> vi data1 --> 粘贴上面1~8小明的数据记录保存 --> 将数据导入select * from psn0;查看 -- 导入数据 LOAD DATA LOCAL INPATH '/root/data1' INTO TO TABLE psn0; 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'; 导入数据 -- 导入数据 LOAD DATA LOCAL INPATH '/root/data1' INTO TABLE psn1; 创建分区表 -- 创建分区表 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 ':'; 将创建分区表语句粘贴到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); 克隆创建表 -- 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;

计算掉话率

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

步骤

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

操作

创建表 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; winSCP将数据文件上传到node04的/root/下 --> 改名为cdr.csv --> load data local inpath ‘/root/cdr.csv’ into table cell_monitor;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; select * from cell_drop_monitor limit 10; --> quit;

补充

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