数据仓库Hive

一丶什么是数据仓库：

1.基本概念

数据仓库的目的是构建面向分析的集成化数据环境，为企业提供决策支持，即：面向分析的存储系统。

2.主要特征

2.1面向主题：

数据仓库是通过一个个主题域将多个业务系统的数据加载到一起，为了各个主题进行分析而建

2.2集成性：

数据仓库会将不同的源数据库中的数据汇总到一起，数据仓库中的综合数据不能从原有的数据库系统直接得到，所以在数据进入数据仓库之前，必然要经过统一与整合。

2.3非易失性：

数据仓库只要保存过去的业务数据，不需要每一笔业务都实时更新数据仓库，而是根据商业需要每隔一段时间把一批较新的数据导入数据仓库。数据仓库中的数据一般仅执行查询操作，很少会有删除和更新，但是需要定期加载和刷新数据。

2.4时变性：

数据仓库包含各种粒度的历史数据。数据仓库中的数据可能与某个特定日期、星期、月份、季度或者年份有关。虽然数据仓库的用户不能修改数据，但并不是说数据仓库的数据是永远不变的。分析的结果只能反映过去的情况，当业务变化后，挖掘出的模式会失去时效性。因此数据仓库的数据需要定时更新，以适应决策的需要。

3.数据库与数据仓库的区别

数据库与数据仓库的区别实际讲的是 OLTP 与 OLAP 的区别。

操作型处理，叫联机事务处理 OLTP（On-Line Transaction Processing，），也可以称面向交易的处理系统，它是针对具体业务在数据库联机的日常操作。

分析型处理，叫联机分析处理 OLAP（On-Line Analytical Processing）一般针对某些主题的历史数据进行分析，支持 管理决策。

首先要明白，数据仓库的出现，并不是要取代数据库。

• 数据库是面向事务的设计，数据仓库是面向主题设计的。

• 数据库一般存储业务数据，数据仓库存储的一般是历史数据。

• 数据库设计是尽量避免冗余，一般针对某一业务应用进行设计，比如一张简单的User表，记录用户名、密码等简单数据即可，符合业务应用，但是不符合分析。数据仓库在设计是有意引入冗余，依照分析需求，分析维度、分析指标进行设计。

• 数据库是为捕获数据而设计，数据仓库是为分析数据而设计。

数据仓库，是在数据库已经大量存在的情况下，为了进一步挖掘数据资源、为了决策需要而产生的，它决不是所谓的“大型数据库”。

4.数据仓库的分层架构

按照数据流入流出的过程，数据仓库架构可以分为 三层---源数据，数据仓库，数据应用。

• 源数据层（ODS）：此层数据无任何更改，直接沿用外围系统数据结构和数据，不对外开放；为临时存储层，是接口数据的临时存储区域，为后一步的数据处理做准备。
• 数据仓库层（DW）：也成为细节层，DW层的数据应该是一致的干净的的数据，即对于对源数据系统进行了清洗后的数据
• 数据应用层（DA或App）：前端应用直接读取的数据源，直接生成的数据

5.数据仓库的元数据管理

元数据（Meta Date），主要记录数据仓库中模型的定义、各层级间的映射关系、监控数据仓库的数据状态及ETL的任务运行状态。一般会通过元数据资料库（Metadata Repository）来统一地存储和管理元数据，其主要目的是使数据仓库的设计、部署、操作和管理能达成协同和一致。

元数据是数据仓库管理系统的重要组成部分，元数据管理是企业级数据仓库中的关键组件，贯穿数据仓库构建的整个过程，直接影响着数据仓库的构建、使用和维护。

二丶Hive的基本概念

1.Hive简介

Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能。hive可以理解为一个将SQL转化为MapReduce的任务工具

为什么使用 Hive

• 采用类SQL语法去操作数据，提供快速开发的能力。

• 避免了去写MapReduce，减少开发人员的学习成本。

• 功能扩展很方便。

2.Hive架构

3.Hive与Hadoop的关系

Hive利用HDFS存储数据，利用MapReduce查询分析数据

4.Hive与传统数据库的对比

hive用于海量数据的离线数据分析

5.Hive的安装

第一步：上传并解压安装包

下载地址为： http://archive.apache.org/dist/hive/hive-2.1.1/apache-hive-2.1.1-bin.tar.gz

下载之后，将我们的安装包上传到机器的/export/softwares目录下面去

第二步：安装mysql

第三步：修改hive的配置文件

修改hive-env.sh

HADOOP_HOME=/export/servers/hadoop-2.7.5
export HIVE_CONF_DIR=/export/servers/apache-hive-2.1.1-bin/conf

修改hive-site.xml

注意，数据库的名称和密码要做出想要改变

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
      <name>javax.jdo.option.ConnectionUserName</name>
      <value>root</value>
  </property>
  <property>
      <name>javax.jdo.option.ConnectionPassword</name>
      <value>123456</value>
  </property>
  <property>
      <name>javax.jdo.option.ConnectionURL</name>
      <value>jdbc:mysql://node03:3306/hive?createDatabaseIfNotExist=true&amp;useSSL=false</value>
  </property>
  <property>
      <name>javax.jdo.option.ConnectionDriverName</name>
      <value>com.mysql.jdbc.Driver</value>
  </property>
  <property>
      <name>hive.metastore.schema.verification</name>
      <value>false</value>
  </property>
  <property>
    <name>datanucleus.schema.autoCreateAll</name>
    <value>true</value>
 </property>
 <property>
		<name>hive.server2.thrift.bind.host</name>
		<value>node03</value>
   </property>
</configuration>

第四步：添加mysql的连接驱动包到hive的lib目录下

将我们准备好的mysql-connector-java-5.1.38.jar 这个jar包直接上传到 /export/servers/apache-hive-2.1.1-bin/lib 这个目录下即可

第五步：配置hive的环境变量

sudo vim /etc/profile

export HIVE_HOME=/export/servers/apache-hive-2.1.1-bin
export PATH=:$HIVE_HOME/bin:$PATH

6.hive的交互方式

必须要先启动MySQL和hiveserver服务：

service mysqld start

后台启动hiveserver （挂起客户端不会断开）：

nohup bin/hive --service metastore  > /dev/null 2> /dev/null &

nohup bin/hive --service hiveserver2 > /dev/null 2> /dev/null & 

前台启动hiveserver

bin/hive --service metastore&

bin/hive --service hiveserver2& 

第一种交互方式： bin/hive,进入客户端执行类sql语句

第二种交互方式：使用sql语句或者sql脚本进行交互,不进入hive的客户端直接执行hive的hql语句

cd /export/servers/apache-hive-2.1.1-bin
bin/hive -e "create database if not exists mytest;"

或者将我们的hql语句写成一个sql脚本然后执行

vim hive.sql

create database if not exists mytest;
use mytest;
create table stu(id int,name string);

通过hive -f 来执行我们的sql脚本

bin/hive -f /export/servers/hive.sql

三丶Hive的基本操作

1.数据库操作

1.1创建数据库

create database if not exists myhive;
use  myhive;

1.2创建数据库并指定位置

create database myhive2 location '/myhive2';

1.3设置数据库键值对信息

create database foo with dbproperties ('owner'='itcast', 'date'='20190120');

查看数据库的键值对信息：

describe database extended foo;

修改数据库的键值对信息：

alter database foo set dbproperties ('owner'='itheima');

1.4 查看数据库更多详细信息

desc database extended  myhive2;

1.5 删除数据库

删除一个空数据库，如果数据库下面有数据表，那么就会报错

drop  database  myhive2;

强制删除数据库，包含数据库下面的表一起删除(一般禁用！！！)

drop  database  myhive  cascade;   

2.数据库表的操作

2.1创建表的语法

1. create table创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。
2. external可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
3. comment表示注释,默认不能使用中文
4. partitioned by表示使用表分区,一个表可以拥有一个或者多个分区，每一个分区单独存在一个目录下 .
5. clustered by 对于每一个表分文件， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。
6. sorted by指定排序字段和排序规则
7. row format​ 指定表文件字段分隔符
8. storted as指定表文件的存储格式, 常用格式:SEQUENCEFILE, TEXTFILE, RCFILE,如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 storted as SEQUENCEFILE。
9. location指定表文件的存储路径

2.2内部表的操作

建表入门

use myhive;
create table stu(id int,name string);
insert into stu values (1,"zhangsan");  #插入数据
select * from stu;

创建表并指定字段之间的分隔符

create  table if not exists stu2(id int ,name string) row format delimited fields terminated by '\t';

创建表并指定表文件的存放路径

create  table if not exists stu2(id int ,name string) row format delimited fields terminated by '\t' location '/user/stu2';

根据查询结果创建表

create table stu3 as select * from stu2; # 通过复制表结构和表内容创建新表

 根据已经存在的表结构创建表

create table stu4 like stu;

查询表的详细信息

desc formatted stu2;

删除表

drop table stu4;

2.3外部表操作

3.1外部表说明

外部表因为是指定其他的hdfs路径的数据加载到表当中来，所以hive表会认为自己不完全独占这份数据，所以删除hive表的时候，数据仍然存放在hdfs当中，不会删掉.

3.2内部表和外部表的使用场景

每天将收集到的网站日志定期流入HDFS文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。

操作案例

分别创建老师与学生表外部表，并向表中加载数据

创建老师表

create external table teacher (t_id string,t_name string) row format delimited fields terminated by '\t';

创建学生表

create external table student (s_id string,s_name string,s_birth string , s_sex string ) row format delimited fields terminated by '\t';

加载数据

load data local inpath '/export/servers/hivedatas/student.csv' into table student;

加载数据并覆盖已有数据

load data local inpath '/export/servers/hivedatas/student.csv' overwrite  into table student;

从hdfs文件系统向表中加载数据（需要提前将数据上传到hdfs文件系统）

cd /export/servers/hivedatas
hdfs dfs -mkdir -p /hivedatas
hdfs dfs -put techer.csv /hivedatas/
load data inpath '/hivedatas/techer.csv' into table teacher;

2.4分区表操作:

在大数据中，最常用的一种思想就是分治，我们可以把大的文件切割划分成一个个的小的文件，这样每次操作一个小的文件就会很容易了，同样的道理，在hive当中也是支持这种思想的，就是我们可以把大的数据，按照每月，或者天进行切分成一个个的小的文件,存放在不同的文件夹中.

创建分区表语法

create table score(s_id string,c_id string, s_score int) partitioned by (month string) row format delimited fields terminated by '\t';

创建一个表带多个分区

create table score2 (s_id string,c_id string, s_score int) partitioned by (year string,month string,day string) row format delimited fields terminated by '\t';

加载数据到分区表中

load data local inpath '/export/servers/hivedatas/score.csv' into table score partition (month='201806');

加载数据到多分区表中

load data local inpath '/export/servers/hivedatas/score.csv' into table score2 partition(year='2018',month='06',day='01');

多分区表联合查询(使用 union all)

select * from score where month = '201806' union all select * from score where month = '201806';

查看分区

show  partitions  score;

添加一个分区

alter table score add partition(month='201805');

删除分区

alter table score drop partition(month = '201806');

 2.5分桶表操作

分桶，就是将数据按照指定的字段进行划分到多个文件中去，分桶就是Mapreduce中的分区。

开启 Hive 的分桶功能

set hive.enforce.bucketing=true;

设置 Reduce 个数

set mapreduce.job.reduces=3;

创建分桶表

create table course (c_id string,c_name string,t_id string) clustered by(c_id) into 3 buckets row format delimited fields terminated by '\t';

3.修改表结构

重命名：

alter table old_table_name rename to new_table_name;

增加修改列信息： 

• 查询表结构

desc score5;

• 添加列

alter table score5 add columns (mycol string,mysco int);

• 更新列

alter table score5 change column mysco mysconew int;

• 删除表

drop table score5

四丶Hive的查询语法

1.关键字介绍：

• order by 会对输入做全局排序，因此只有一个reducer，会导致输入规模较大时，需要i较长的时间计算。
• sort by不是全局排序，其在数据进入reducer之前完成排序。因此，如果使用sort by排序并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。
• distribute by（字段）根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。
• cluster by 除了具有distribute by 的功能外，还会对字段进行排序

2.查询语法

选择特定列查询：

select s_id ,c_id from score;

列别名：

select s_id as myid,c_id from score;

3.常用函数

• 求总行数（count）

select count(1) from score;

• 求分数的最大值（max）

select max(s_score) from score;

• 求分数的最小值（min）

select min(s_score) from score;

• 求分数的总和（sum）

select sum(s_score) from score;

• 求分数的平均值（avg）

select avg(s_score) from score;

4.LIMIT语句

典型的查询会返回多行数据。LIMIT子句用于限制返回的行数。

select * from score limit 3;

5.WHERE语句

查询出分数大于60的数据

select * from score where s_score >60;

比较运算符

操作符	支持的数据类型	描述
A=B	基本数据类型	如果A等于B则返回TRUE，反之返回FALSE
A<=>B	基本数据类型	如果A和B都为NULL，则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL
A<>B, A!=B	基本数据类型	A或者B为NULL则返回NULL；如果A不等于B，则返回TRUE，反之返回FALSE
A<B	基本数据类型	A或者B为NULL，则返回NULL；如果A小于B，则返回TRUE，反之返回FALSE
A<=B	基本数据类型	A或者B为NULL，则返回NULL；如果A小于等于B，则返回TRUE，反之返回FALSE
A>B	基本数据类型	A或者B为NULL，则返回NULL；如果A大于B，则返回TRUE，反之返回FALSE
A>=B	基本数据类型	A或者B为NULL，则返回NULL；如果A大于等于B，则返回TRUE，反之返回FALSE
A [NOT] BETWEEN B AND C	基本数据类型	如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。
A IS NULL	所有数据类型	如果A等于NULL，则返回TRUE，反之返回FALSE
A IS NOT NULL	所有数据类型	如果A不等于NULL，则返回TRUE，反之返回FALSE
IN(数值1, 数值2)	所有数据类型	使用 IN运算显示列表中的值
A [NOT] LIKE B	STRING 类型	B是一个SQL下的简单正则表达式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%’表示A必须以字母‘x’开头，‘%x’表示A必须以字母’x’结尾，而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。
A RLIKE B, A REGEXP B	STRING	类型 B是一个正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。

6.LIKE和RLIKE

 % 代表0个或多个字符

_代表一个字符

查找s_id中含1的数据

select * from score where s_id rlike '[1]'; 

或者：

select * from socre where s_id like '%1%';

7.逻辑运算符

操作符	含义
AND	逻辑并
OR	逻辑或
NOT	逻辑否

查询s_id 不是 01和02的学生

select * from score where s_id not in ('01','02');

 8.分组

GROUP BY 语句

GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。

计算每个学生的平均分数

select s_id,avg(s_score) from score group by s_id;

HAVING语句

having与where不同点

1.where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据。

2.where后不能写分组函数，而having可以

3.having只用于group by 分组统计语句

求每个学生平均分数大于85的人

select s_id，avg(s_score) avgscore from score group by s_id having avfscore >85;

9.JOIN语句

1.等值JOIN

Hive支持通常的SQL JOIN 语句，但是只支持等值连接

查询分数对应的姓名

select s.s_id,s.s_score,stu.s_name,stu.s_birth  from score s  join student stu on s.s_id = stu.s_id;

2.表别名

合并老师和课程表

select * from teacher t join course c on t.t_id = c.t_id;

3.内连接

内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

select * from teacher t inner join course c on t.t_id=c.t_id;

4.左外连接

左外连接：JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。 查询老师对应的课程

select * from teacher t left join course c on t.t_id=c.t_id;

5.右外连接

右外连接：JOIN操作符右边表中符合WHERE子句的所有记录将会被返回。

select * from teacher t left join course c on t.t_id = c.t_id;

6.多表连接

注意：连接 n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。

多表连接查询，查询老师对应的课程，以及对应的分数，对应的学生

select * from teacher t
left join course c
on t.t_id = c.t_id
left join score s
on s.c_id = c.c_id
left join student stu
on s.s_id = stu.s_id;

10.排序

1.全局排序

1. 使用 ORDER BY 子句排序 ASC（ascend）: 升序（默认） DESC（descend）: 降序

2. ORDER BY 子句在SELECT语句的结尾。

查询学生的成绩，并按照分数降序排列

select * from student s left join score sco on s.s_id=sco.s_id order by sco.s_score asc;

2.按照别名排序 

按照分数的平均值排序

select s_id ,avg(s_score) avg from score group by s_id order by avg;

3.多个列排序

按照学生id和平均成绩进行排序

select s_id ,avg(s_score) avg from score group by s_id order by s_id, avg;

4.局部排序 Sort By

查询成绩按照成绩降序排列

select * from score sort by s_score;

将查询结果导入到文件中（按照成绩降序排列）

insert overwrite local directory '/export/servers/hivedatas/sort' select * from score sort by s_score;

5. 分区排序（DISTRIBUTE BY）

通过distribute by 进行数据的分区

insert overwrite local directory '/export/servers/hivedatas/sort' select * from score distribute by s_id sort by s_score;

6.CLUSTER BY

当distribute by和sort by字段相同时，可以使用cluster by方式。

cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是倒序排序，不能指定排序规则为ASC或者DESC。

以下两种写法等价

select * from score cluster by s_id;
select * from score distribute by s_id sort by s_id;