Hive-数据仓库

数据仓库

概念

数据仓库是一个面向主题的，集成的，相对稳定的。反应历史变化的数据集合，用于支持决策
主题：把不同数据库和用户相关的数据抽取在一起
集成：进行合并
相对稳定：不是实时的（因为数据量太大，影响网站运行效率），一般按天对数据进行抽取，合并
反应历史变化：用来统计，进行管理决策

传统数据库面临的挑战（不足）

1.无法满足快速增长的海量数据的存储需求
2.无法有效处理不同类型的数据
3.计算和处理能力不足

hive

官网：http://hive.apache.org/

简介

Hive是基于Hadoop的数据仓库，他提供工具用来ETL ，可以存储，查询，分析在hadoop上的大规模的数据。

Hive可以将结构化的数据文件映射成一张数据库表，使用类sql语句将其转换成MR作业进行运行。

学习成本低，非常适合熟悉sql的用户。

也允许熟悉MR的人自定义MR

why

为什么有良好的可扩展性：hdfs可以很简单的奴役新的datanode节点

为什么可以看成是用户的编程接口：hive只是将输入进来的类sql语句转换成MR，它并没有存储和处理数据的过程

为什么Hive采用批处理处理数据：因为底层是MR

为什么Hive只适合处理静态的数据：因为底层MR只是处理离线数据

Hive和Hadoop中其他组件的关系

Hive和传统数据库的区别

1.Hive和关系型数据库的存储文件的系统不同
hive存储文件的系统是hdfs。
关系型数据库是将文件存到本地。
2.hive的计算模型是MR，关系型数据库是自身的计算模型。
3.hive不适合用于小数据的存储，因为他的存储是hdfs，hdfs的namenode存放目录，当有很多小文件的时候目录就会很大。
4.hive的实时性差因为MR是离线处理系统
5.hive的扩展性会比传统数据库强的多，因为hdfs可以很容易的奴役新的数据节点
6.Hive不支持时间或行级更新，因为hdfs是一次存入多次读取，hdfs不支持修改

总结

Hive是由Facebook开源用于解决庞大的结构化日志的数据统计。
hive可以将结构化的数据文件映射为一张表，提供类sql查询功能
本质：是将HQL转化成MR程序
hive存储数据在hdfs，计算在MR，执行在Yarn

优点

1.简单易上手
2.Hive执行延迟高，常用于数据分析，对实时性要求不高的场景
3.hive优势在于处理大量的数据
4.hive支持自定义函数

缺点

1.hive的hql的表达能力有限
1）迭代式算法无法表达
2）数据挖掘方面不擅长
2.hive的效率比较低
1）hive生成的MR通常不够智能化
2）调优比较难，粒度较粗

hive和hdoop的关系

hive 的架构

提交：CLI,JDBC,ODBC
元数据：存放在hdfs的哪个目录。。。。

hive的安装

hive有三种运行模式：单机，伪分布式，完全分布式

安装mysql

1.将mysql的安装包上传到linux上
2.删除原有的mysql
rpm -qa | grep mysql
rpm -e --nodeps mysql
3.解压mysql安装包
tar -xzvf 安装包名
4.将mysql移动到/usr/local路径下
mv /home/hduser/software/mysql /usr/local
5.增加用户组mysql
sudo groupadd mysql
6.在mysql用户组中创建新的用户mysql
sudo useradd -g mysql mysql
7.修改目录拥有者
sudo chown -R mysql:mysql mysql
8.增加环境变量：
在/etc/profile最下方增加

export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin

9.初始化mysql
在mysql的目录下
sudo scripts/mysql_install_db --user=mysql
10.启动mysql
sudo ./support-files/mysql.server start
11.查看是否启动
ps -ef|grep mysql
12.进入mysql
mysql -u root
13.给root加密码
use mysql;
update user set password=PASSWORD(‘admin’) where user=‘root’;
flush privileges;
exit;
14.修改编码方式
1）查看mysql编码方式 status
2）如果发现有的编码方式是latin1需要修改
3）修改配置文件
sudo vim my.cnf
在[mysqld]下添加：
character-set-server=utf8
4）重启mysql
sudo ./support-files/mysql.server restart
再次进入mysql
status，看编码全是utf8就OK

安装hive

1.首先将hive安装包上传到linux系统中
2.解压hive安装包
3.配置hive环境变量
在/etc/profile最下方增加

export HIVE_HOME=/home/hduser/software/hive-1.2.1
export PATH=$PATH:$HIVE_HOME/bin

4.配置hive-site.xml文件
1）首先将 hive-env.sh.template更名为hive-env.sh
2）在hive-env.sh中修改，添加

HADOOP_HOME=/home/hduser/software/hadoop-2.7.2
HADOOP_CONF_DIR=/home/hduser/software/hadoop-2.7.2/etc
export HIVE_CONF_DIR=/home/hduser/software/hive-1.2.1/con
export HIVE_AUX_JARS_PATH=/home/hduser/software/hive-1.2.1/lib

5.启动hive
hive（hive在哪个目录下执行，哪个目录下就会生成一个元文件 metastore_db）

将元数据存到mysql中

将mysql-connection-java.jar上传到hive的lib文件夹中
在hive的conf文件夹中创建hive-site.xml文件
在其中配置：

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
	<property>
	  <name>javax.jdo.option.ConnectionURL</name>
	  <value>jdbc:mysql://testmachine:3306/metastore?createDatabaseIfNotExist=true</value>
	  <description>JDBC connect string for a JDBC metastore</description>
	</property>

	<property>
	  <name>javax.jdo.option.ConnectionDriverName</name>
	  <value>com.mysql.jdbc.Driver</value>
	  <description>Driver class name for a JDBC metastore</description>
	</property>

	<property>
	  <name>javax.jdo.option.ConnectionUserName</name>
	  <value>root</value>
	  <description>username to use against metastore database</description>
	</property>

	<property>
	  <name>javax.jdo.option.ConnectionPassword</name>
	  <value>admin</value>
	  <description>password to use against metastore database</description>
	</property>
</configuration>

之后启动mysql，启动hadoop，最后启动hive

HiveJDBC访问

1.首先启动 hiveserver2
2.在另一个连接中启动beeline
3.在beeline中输入
!connect jdbc:hive2://linux8:10000
username hduser
password 回车

Hive常用交互命令

1.-e 不进入hive交互窗口执行命令
hive -e “show databases;”
2. -f 执行文件脚本中的sql命令
hive -f ./text

其他命令

1.退出hive窗口
exit；先提交数据在退出
quit；直接退出
2.在hive窗口中查看hdfs文件系统
dfs -ls
3.在hive窗口中查看本地文件系统
！ ls ./
4.查看hive中输入的历史命令
cat .hivehistory

hive常见属性配置

1.修改default数据仓库的原始位置，
在hive-default.xml.template中有

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

将其复制到hive-site.xml中，之后改value
2.显示当前数据库，查询表头信息配置
在hive-site.xml中添加配置

<property>
	<name>hive.cli.print.header</name>
	<value>true</value>
</property>

<property>
	<name>hive.cli.print.current.db</name>
	<value>true</value>
</property>

hive-hql常用命令

1.查看所有数据库名

show databases;

2.创建数据库

create database pgdb;

3.查看数据库的属性

desc database extended pgdb;

4.删除数据库

drop database pgdb
drop database pgdb cascade(级联删)

5.创建一张以\t为分隔的表

create table tb1(id int,name string) row format delimited fields terminated by '\t';

6.查看表结构

desc tb1；

7.修改表名

alter table tb1 rename to tb2;

8.增加字段

alter table tb2 add columns(age int);

在创建表的时候可能会有错误：

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:For direct MetaStore DB connections, we don't support retries at the client level.)

解决方法：进入mysql，把初始数据库的字符集改成latin1；

ALTER DATABASE metastore CHARACTER SET latin1

就OK了
9.克隆表（备份）

create table stu2 as select id,name from stu;

10.复制表结构

create table stu3 like stu2

11.添加数据
本地数据： load data local inpath '路径' into table 表名
hdfs上的数据：load data inpath '路径' into table 表名
12.删除包保留表结构

truncate table tb1;

13.模糊查询

show databases like 'pg*';

14.加注释

alter table tb1 add columns(name string comment 'user name');

15.避免要创建的数据库已存在的错误

create databases if not exists pgdb;

16.查看表的详细数据

desc formatted stu；

管理表（内部表）

1.hive对表和数据的生命周期进行全程的管理
2.在创建表的时候不特别指定就是创建管理表
3.删除表的时候，里面的数据也会被删掉
4.添加数据的时候会将数据放入表目录下

外部表

1.hive不管理数据，只管理元数据
元数据：metastore中的数据
数据：表目录中的数据
2.删除表的时候只删除元数据，不删除数据
3.创建外部表

create external table stu（id int）row delimited fields terminated by '\073'(\073表示;) location '路径'；

分区表

1.创建分区表
1)

create table par_tb(name string,nation string) partitioned by(gender string) row format delimited fields terminated by ',';

2)`

create table par_th1 like par_tb;

2.通过分区表创建普通表

create table par_pp as select name,nation,gender from par_th1；

(有数据)
3.向分区表中静态添加数据
1)

load data local inpath '路径' into table par_th1 partition(gender='boy');

4.向单分区中动态添加数据

insert overwrite table par_stu1 partition(gender) select name,nation,gender from par_dum_stu;

注：par_dum_stu:有数据的无分区表
par_stu1：无数据的有分区表
5.向有多分区的表中动态添加数据

insert overwrite table par_stu1 partition(gender='women',year) select name,nation,year from par_stu2;

insert overwrite table par_stu1 partition(gender,year) select name,nation,year from par_stu2;

partition动态添加多分区数据的时候，
可以是第一个分区静态添加，第二个分区动态添加
可以是两个分区都是动态
6.增加分区

alter table par_tb1 add partition(year='1092') 

注：增加的分区必须是已有的分区字段,且这个字段的分区不能存在
7.删除分区

alter table par_tb1 drop partition(year='1021')

8.查看分区详情

 show partitions 表名;

9.手动加载数据
1） 首先在hdfs的表所在的文件夹中添加文件夹

 dfs -mkdir -p ‘路径’;

在文件夹中上传数据

 dfs -put ‘路径’ ;

刷新数据库

msck repair table 表名;

2）首先在hdfs的表所在的文件夹中添加文件夹

 dfs -mkdir -p ‘路径’

在文件夹中上传数据

 dfs -put ‘路径’ 

创建–声明分区

 alter table 表名 add partition(year='1000');

3)首先在hdfs的表所在的文件夹中添加文件夹

 dfs -mkdir -p ‘路径’

load 加载数据

总结：

分区的目的是为了更高效的查找数据
当按照分区字段去查找数据的时候，会直接去分区的文件夹中取数据，所以会更快

数据导入–将数据导入hdfs

load data local inpath ‘路径’ overwrite into table student partition(gender='men')

2.插入一条数据

insert into table student partition(mouth='202004') value('1004',wangwu);

3.根据单表查询结果

insert overwrite table student partition(mouth='202004')
select id,name from student where mouth='202004'

4.插入多条数据

from student
insert overwrite table student partition(mouth='202004')
select id,name where mouth='202004'
insert overwrite table student partition(mouth='202005')
select id,name where mouth='202005'

5.创建表时通过Location指定加载路径

create table stu3(id string,name string) row format delimited fields terminated by '\t' location 'hdfs路径'`

之后将数据手动上传到此目录中
6.import导入到本地
注：只有用export导出的数据才能用import导入

import tablestudent from ‘hdfs路径’

数据导出–将数据导入本地

1.将查询的结果导出到本地

insert overwrite local directory ‘本地路径’ select * from student

2.将查询结果格式化导出到本地

insert overwrite local directory '本地路径' row format delimited fields terminated by '\t' collection items terminated by '\n' select * from student

3.将结果导出到hdfs

insert overwrite  directory '本地路径' row format delimited fields terminated by '\t' collection items terminated by '\n' select * from student

4.hadoop命令导出

dfs -get hdfs路径 本地路径

5.hive shell命令导出

bin/hive -e 'select * from default.student;'>本地路径

6.export 导出到hdfs

export table default.student to ‘本地路径’

HQL语法

1.和sql一样，不区分大小写
2.语句可以换行，但是关键字不能拆开写

列别名

select saraly as saralyy from emp

后面的是别名

select saraly saralyy from emp

算数运算符

比较运算符

在where子句中使用

like，rlike

select * from emp where saraly like ‘2%’ 查询以2开头的字段
select * from emp where saraly like ‘[2]’查询字段中有2的字段

between and

select * from emp where saraly between 1000 and 5000

between后面的数不能比and后面的数大

limit子句

select * from emp limit 5 显示前5行

聚合函数

count

计数
count（1）显示有几行
count（*）显示有几行

max min

求这一列的最大值最小值

select max(saraly) from emp

select min(saraly) from emp

sum

求和

select sum(saraly) total from emp

avg

求平均值

select avg(saraly) total from emp

分组–group by

分组查询的关键字：每个
分组就是将分组字段中相同的值分成一组进行操作
分组查询显示的字段只能是分组字段或者是聚合函数
查询每个部门的平均工资：

select dep,avg(saraly) from emp group by dep

having

常和group by 连用
group by需要在having的前面
查询薪水大于2000元的部门的平均工资

select dep,avg(saraly) avg_saraly from emp group by dep having avg_saraly>2000

表连接

内连接

内连接就是将连接字段相匹配的字段全部输出

select * from emp e join dept d on e.dept_on=d.dept_on

左外连接

在join前的表是左表，在join后的表是右表，左连接就是把左表当做主表，将每行数据和右表的所有数据进行匹配，匹配成功就输出，如果没有与之匹配的项就将次表数据的填成null

select * from emp e left join dept d on e.dept_on=d.dept_on

右外连接

select * from emp e right join dept d on e.dept_on=d.dept_on

满外连接

满外连接就是将左连接和有连接的值合并

select * from emp e full join dept d on e.dept_on=d.dept_on

排序

Order by

全局排序：只起一个reducetask
写在select 语句后
select * from emp order by saraly

sort by

改变reducetask的数量：set mapreduce.job.reduces=3;
区内有序：每个reduce task里的数据分别有序
select * from emp sort by saraly

distribute by

相当于MR中的partition，但是区内无序，需要和sort by配合使用。
select * from emp distribute by emp_no sort by emp_on;

cluster by

当distribute by和sort by 的值相同的时候 可以直接用
cluster by 代替
select * from emp cluster by emp_no

分桶

分桶表数据存储

分区是针对数据的存储路径，分桶是针对的数据文件。
分桶是将数据集分解成更容易管理的若干部分的技术

分桶表建立

create table  stu_buck(id int,name string) clustered by(id)
into 4 buckets
row format delimited fields terminated by '\t';

查看详细表结构

desc formatted stu_buck;

在向分桶表插入数据的时候需要设置一个属性：

set hive.enforce.bucketing=true;
set hive.mapreduce.job.reudces=-1;(默认)

分桶抽样查询

select * from stu_buck tablesample(bucket 1 out of 4 on id);

语法结构是：
tablesample(bucket x out of y)
解释： 例如分桶个数n 为4，当x等于1，y等于4的时候效果就是在第一个桶取一桶的数据
取多少数据：n/y
在哪开始取：x
y只能取n的整数倍，或者n的因子
x不可以比y大

数据块抽样

这个是基于行数，按照数据块百分比进行抽样

select * from stu tablesample(10 percent);

这句的意思就是在这个桶中取10%的数据

注：

hive函数

行转列查询

集合数据类型：
struct：可存不同的数据类型
map：以键值的方式存储
array：只能存储一种数据类型
例：

 create table person_info(name string,friends array<string>,children map<string,int>,address struct<street:string,city:string>) row format delimited fields terminated by ',' collection items terminated by '_' map keys terminated by ':' lines terminated by '\n';

array和map在声明数据类型的时候需要在尖括号中声明
collection items terminated by 是声明每个array和map中每项的分隔符（必须写）
map keys terminated by 是声明map中键值对的分隔符

查询集合数据类型

查询array

通过字段名[下角标]查询

select friends[1] from person_info where name='zhangfei';

查询map

通过字段名[key值]

select children['cao zhi'] from person_info where name='caocao';

查询struct

select adress.street from person_info where name='zhangfei';

函数类型

udf 一进一出 例：转换大小写的函数
udaf 多进一出 例：max，min，avg
udtf 一进多出 例：explode

合并函数（聚合函数）

只能合并string类型的字段

concat

这个函数是将字符连接在一起

select concat('hello','world');

concat_ws

这个函数是concat的进阶版，第一个字符代表分隔符

select concat(',','hello','world');

collect_set

这个函数的作用就是去重之后将去重后的数据整合在一个array中

select collect_set(字段名) from 表名;

炸裂函数

explode

此函数是将hive中复杂的类型拆开

select explode(字段名) from 表名

lateral view

将炸裂开的字段映射成一个视图并且和原表的数据的关联依然存在

select  movie,movie_type  from movie lateral view explode(字段名) table_tmp as movie_type;

nvl

判断是否为空
select nvl（字段名，默认值）from 表名

分析函数

分析函数又叫窗口函数，主要是用来数据统计分析的
hive 的分析函数包括 LEAD,LAG,FIRST_VALUE,LAST_VALUE,RANK,ROW_NUMBER,PERCENT_RANK,CUBE,ROLLUP

查看分析函数

show function;
详细查看某个分析函数：desc function substr；

over

over(),将所有行放入窗口
窗口：进行处理的行
指定分析函数工作的数据窗口大小，其中可以用partition by 字段 进行分组，可以用order by 字段名 进行排序

partition by

partition by 的作用就是将窗口的大小控制为每个分组的大小。
例：购买过的顾客及总人数

select * ,sum(cost) over(partition by name)  from bussiness 

order by

order by作用就是排序，将窗口的大小从第一行开始加，一直加到最后

select *,sum(cost) over(order by order_date) from bussiness;

current row

当前行

n preceding

往前n行
select * ,sum(cost)

n following

往后n行

unbounded

起点

unbounded preceding

表示起点

unbounded following

表示终点

LAG(col，n)

往前n行
例：查询用户上一次的购买时间

select * lag(order_data,1) over( partition by name order by order_data) from bussiness;

LEAD(col,n)

往后n行
例：查询用户下一次的购买时间

select * lead(order_data,1) over( partition by name order by order_data) from bussiness;

ntile（n）

把有序分区中的行分发到指定数据的组中各个组有编号，编号从一开始，对于每一行，ntile返回此行所属的组 。
例：查询前20%的数据

select * from (select * ntile(5) over(order  by order_date) group_id from bussiness) t where group_id=1;

rank()

排序的时候，当遇到两个值相等的时候序号会重复，总数不变
select * rank() over(partition by name order by score)

dense_rank()

排序的时候，当遇到两个值相等的时候序号会重复，总数会变少

row_number()

会把行号变成序号

自定义函数

首先在idea的maven项目中的pom.xml文件下，加载jar包

<dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-exec</artifactId>
            <version>3.1.2</version>
</dependency>

之后创建一个类让其继承UTF类
写一个public方法

public  String evaluate(String a){
        return "Hello"+a;
    }

之后打包
上传到linux上
打开hive
增加jar包

add jar 路径名

注册函数

create temporary function 自定义函数名 as "主类路径";