该文为个人学习笔记,仅供参考。 更多内容关注本人Halo^0博客

Hive介绍

概述

hive是基于Hadoop的一个数据仓库^1工具,由Facebook开源,用于解决海量结构化的数据统计项目。

Hive本质

  • Hive表及数据本质上是HDFS上的目录和文件。
  • 将SQL(HQL^2)语句转化成MapReduce程序。
  • Hive表的元数据存储在mysql数据库中

Hive体系结构

hive体系结构

  • Hive的数据存储基于Hadoop HDFS

  • Hive没有专门的存储数据格式

  • 存储结构主要包括:数据库、文件、表、视图、索引。

  • 用户接口:Client

    • CLI(hive shell)、JDBC/ODBC(java访问hive),WEBUI(浏览器访问hive)
  • 元数据:Metastore

    • 元数据包括:表名、表所属的数据库(默认是default)、表的拥有者、列/分区字段、表的类型(是否是外部表)、表的数据所在目录等;
    • 默认存储在自带的derby数据库中,推荐使用采用MySQL
  • Hadoop

    • 使用HDFS进行存储,使用MapReduce进行计算
  • 驱动器:Driver

    • 包含:解析器、编译器、优化器、执行

    Hive安装部署

Hive安装模式

  • 内嵌模式:元数据保存在derby中,只允许一个会话。
  • 本地模式:使用本地mysql替代derby存储元数据
  • 远程模式:登录使用远程mysql替代derby存储元数据

Hive部署(本地模式)

详见另一篇博文:Hive本地模式部署

Hive基本使用

启动hive及使用(确保已经配置环境变量)

$ hive

基本命令(类sql)

--# 查看所有数据库
hive> show databases;
--# 创建数据库
hive> create database db_name;
--# 删除数据库
hive> drop database db_name;
--# 使用数据库
hive> use db_name;
--# 查看数据库
hive> show tables;
--# 创建表并规定字段分隔符为\t
hive> create table user(
        > id int comment 'id of user',
        > name string comment 'name of user',
        > age int comment 'age of user'
        > )comment 'demo table'
        > row format delimited fields terminated by '\t';
--# 查看表结构
hive> desc tb_name;
--# 查看表详细信息
hive> desc formatted tb_name;
--# 查询表
hive> select * from tb_name;
--# 删除表
hive> drop table tb_name;
--# 清空表
truncate table tb_name;

加载数据

导入数据时,注意分隔符问题,避免出现null值

  • 从linux本地加载数据
    hive> load data local inpath '/home/hadoop/user_info.data' into table user_info;
    
  • 从HDFS文件系统加载数据
    hive> load data inpath '/input/user_info.data' into table user;
    

命令行交互

  • hive与linux交互

    hive> #!命令;
    hive> !pwd;
    
  • hive与HDFS交互

    由于在hive-env.sh中配置了hadoop环境变量,所以在hive中可以直接操作hdfs。

    hive> dfs -ls /;
    

显示表名及列名

  • 临时生效

    hive> set hive.cli.print.header=true;      #列名
    hive> set hive.cli.print.current.db=true;  #表名
    hive> reset;    #重置
    
  • 修改配置文件,永久生效。

    vi conf/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表类型

内、外表

内部表

没有external修饰,表数据保存在Hive默认的路径下,数据完全由Hive管理,内部表删除时会将元数据信息和表数据同时删除

外部表

有external修饰,表数据保存在HDFS上,该位置由用户指定。外部表在删除表的时候只会删除表的元数据(metadata)信息不会删除表数据(data)

使用场景

  1. 希望做数据备份并且不经常改变的数据,存放在外部表可以减少失误操作
  2. 数据清洗转换后的中间结果,可以存放在内部表,因为Hive对内部表支持的功能比较全面,方便管理
  3. 处理完成的数据由于需要共享,可以存储在外部表,这样能够防止失误操作,增加数据的安全性

分区表(重点内容)

创建分区表

create table emp_part(
empno int,
empname string,
empjob string,
mgrno int,
birthday string,
salary float,
bonus float,
deptno  int
)
partitioned by (province string)  #选择分区字段
row format delimited fields terminated by '\t';

向分区表加载数据

load data local inpath '/home/user01/emp.txt' into table emp_part partition (province='CHICAGO');

分区表操作

查看分区
show partitions emp_part;
添加分区
alter table emp_part add partition (province='shanghai');
删除分区
alter table emp_part drop partition (province='shanghai');

向分区添加数据

load data local inpath '本地路径' into table emp_part partition (province='shanghai');

查询分区数据

select * from emp_part where province='henan';

二级分区

创建二级分区
create table emp_second(
id int ,
name string,
job string,
salary float,
dept int
)
partitioned by (day string,hour string)
row format delimited fields terminated by '\t';
添加分区
alter table emp_second add partition (day='20180125',hour='16');
删除分区
alter table emp_second drop partition (day='20180125');
添加数据的时候指定分区(没有该分区会创建)
load data local inpath '/home/hadoop/emp.log' into table emp_second partition (day='20180125',hour='17');

hive脚本

语法

语法:hive 参数

​ -e 执行sql

​ -f 执行hql文件

​ -s 静默执行

导入、导出

导入方式总结

  • 本地导入
  • hdfs导入
  • 覆盖
  • 追加
  • 将查询结果创建并导入到表中
  • 创建表时加载数据
  • sqoop导入

导出方式总结

  • hive脚本

    #!/bin/bash
    hive -e "use test_db;select * from emp_p" > /home/hadoop/result.txt
    
  • 导出到本地

    insert overwrite local directory '/home/hadoop/data'  row format delimited fields terminated by '^'  select * from emp_p;
    
  • 导出到hdfs

    hive > insert overwrite directory '/data' select * from emp_p;
    

HQL

select
	[distinct|all]
	{*|tab.*|tab.c as alias [,...]}
from tab [as alias]
[join| left|right|full join tab  on ...]
[where ]
[group by ]
[having ]
[order by desc|asc]
[limit n]

表连接

1) 积
	select * from dept, emp;
	select * from emp, dept where emp.did=dept.did;
2) join
	select t1.eid, t1.ename, t1.salary,t2.did ,t2.dname from emp t1 join dept t2 on t1.did=t2.did;		
3) 外连查询
	left join
		select eid,ename, salary,t2.did, t2.dname from emp t1 left join dept t2 on t1.did = t2.did;
	right join
		select eid,ename, salary,t2.did, t2.dname from emp t1 right join dept t2 on t1.did = t2.did;
4) 全连接
	select eid,ename, salary,t2.did, t2.dname from emp t1 full join dept t2 on t1.did = t2.did;

hql四种排序

1 Order by

​ 全局排序Order By (对所有的数据进行排序)

select * from emp_part order by salary;

​ 设置reduce个数为3,也只有一个文件 ​ set mapreduce.job.reduces=3;

2 sort by

​ 内部排序 (每个reduce内部进行排序 ) ​ 分区是在reduce函数之前完成的 ​ # 默认reduce个数为1, 这种情况下和order by一样

hive>set mapreduce.job.reduces=3;
hive>insert overwrite local directory '/home/hadoop/result'  select * from emp_part sort by salary;
3 distribute by

​ 分区排序(通过distribute by设置分区 ,使用 sort by设置分区内排序) ​

set mapreduce.job.reduces=3;
# 这里使用部分分区,薪资排序
insert overwrite local directory '/home/hadoop/result'  select * from emp_part distribute by deptno sort by salary;
4 Cluster By

(distribute by 和sort by条件相同时 使用cluster by)

连接方式

配置

​ 修改hive-site.xml

<property>
  <name>hive.server2.long.polling.timeout</name>
  <value>5000</value>
</property>

<property>
  <name>hive.server2.thrift.port</name>
  <value>10000</value>
</property>

<property>
  <name>hive.server2.thrift.bind.host</name>
  <value>linux01</value>
</property>

启动服务
$ bin/hiveserver2 &
或
$bin/hive --service hiveserver2 &
连接
$ bin/beeline
beeline>!connect jdbc:hive2://linux01:10000
输入mysql的用户名
输入mysql密码
mysql数据库中创建一个普通用户
1)创建用户
 CREATE USER 'hadoop'@'主机名' IDENTIFIED BY '123456';
2)授权访问(hive的存储元数据的数据库)
GRANT ALL ON metastore.* TO 'hadoop'@'主机名' IDENTIFIED BY '123456';  
 GRANT ALL ON metastore.* TO 'hadoop'@'%' IDENTIFIED BY '123456';
3)刷新授权
flush privileges;

hive函数

Hive系统函数

hive中的UDF

  • 用户自定义函数
  • 类型:
    • UDF 一进一出
    • UDAF 多进一出
    • UDTF 一进多出 行列转换

编写UDF:

​ 编写UDF必须继承UDF

​ 必须至少实现一个evaluale方法

​ 必须要有返回类型,可以是null

​ 建议使用hadoop序列化类型

需求:日期转换

​ 31/Aug/2015:00:04:37 +0800 --> 2015-08-31 00:04:37

实现步骤

​ 1) 自定义类实现UDF类

​ 2) 打包不要指定主类

​ 3) 添加到hive中

hive (test_db)>add jar jar包位置;

hive (test_db)> CREATE TEMPORARY FUNCTION 函数名 as 'UDF实现类的全限定名';    

hive (test_db)> show functions;

窗口函数

1. over语句

准备测试数据

hive (db_analogs)> create database ts;
hive (db_analogs)> use ts;
hive (ts)> create table testscore(gender string,satscore int, idnum int) row format delimited fields terminated by ',';
hive (ts)> load data local inpath '/opt/datas/TESTSCORES.csv' into table testscore;

OVER with standard aggregates: COUNT、SUM、MIN/MAX、 AVG

需求1:

按照性别分组,satscore分数排序(降序),最后一列显示所在分组中的最高分

Female  1000    37070397        1590
Female  970     60714297        1590
Female  910     30834797        1590
Male    1600    39196697        1600
Male    1360    44327297        1600
Male    1340    55983497        1600

答案sql:

hive (ts)>  select gender,satscore,idnum,max(satscore) over(partition by gender order by satscore desc) maxs  from testscore;

注 意:

partition by 是分组用的

2 分析函数

要求 topN

​ 按照性别分组,satscore排序(降序),最后一列显示在分组中的名次

需求1:

分数相同名次不同,名次后面根据行数增长

Female  1590    23573597        1
Female  1520    40177297        2
Female  1520    73461797        3
Female  1490    9589297 4
Female  1390    99108497        5
Female  1380    23048597        6  # 分数相同
Female  1380    81994397        7  # 分数相同

需求2:

分数相同名次相同,名次后面根据行数增长

Female  1590    23573597        1
Female  1520    40177297        2
Female  1520    73461797        2
Female  1490    9589297 4
Female  1390    99108497        5
Female  1380    23048597        6  #分数相同
Female  1380    81994397        6  # 分数相同

需求3:

分数相同名次相同,名次连续增长

Female  1590    23573597        1
Female  1520    40177297        2
Female  1520    73461797        2
Female  1490    9589297 3
Female  1390    99108497        4
Female  1380    23048597        5
Female  1380    81994397        5
SQL
sql1
hive (ts)> select gender,satscore,idnum,row_number() over(partition by gender order by satscore desc) maxs  from testscore;

sql2
select gender,satscore,idnum,rank() over(partition by gender order by satscore desc) maxs  from testscore;

sql3
select gender,satscore,idnum,dense_rank() over(partition by gender order by satscore desc) maxs  from testscore;

# 当有order by,而没有指定窗口子句时,窗口子句默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(从起点到当前行的范围)

# 当order by和窗口子句都没有时,窗口子句默认ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING(从起点到后面的终点)

UNBOUNDED PRECEDING	顶(第一行)
UNBOUNDED FOLLOWING 底行(最后一行)
1 PRECEDING	
1 FOLLOWING
CURRENT ROW

窗口对比

 select gender,satscore,idnum,sum(satscore) over(partition by gender order by satscore desc RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sums  from testscore;
select gender,satscore,idnum,sum(satscore) over(partition by gender order by satscore desc RANGE BETWEEN UNBOUNDED PRECEDING AND unbounded following) sums  from testscore;
1) LAG

落后值(上n个值),在不指定落后个数的情况下,默认为落后一个值(数据从上向下显示,落后即当前值之前显示的值)

场景: 分析用户页面浏览顺序

sql

hive (ts)> select gender,satscore,idnum, lag(satscore) over(partition by gender order by satscore desc) as lastvalue from testscore;

要求

gender  satscore        idnum   lastvalue
Female  1590    23573597        NULL  # 此处为null,可以为其指定默认值
Female  1520    40177297        1590  # 显示当前satscore的上一条记录的值
Female  1520    73461797        1520  # 显示当前satscore的上一条记录的值
Female  1490    9589297 1520
Female  1390    99108497        1490
2) LEAD

与LAG相反(下n搁置),用法同理,前面的值(领先值),默认为领先一个值(数据从上向下显示,领先即当前值之后显示的值)

sql

hive (ts)> select gender,satscore,idnum, lead(satscore, 1, 0) over(partition by gender order by satscore desc) as nextvalue from testscore;

结果

gender  satscore        idnum   nextvalue
...
Female  1060    59149297        1060
Female  1060    46028397        1000
Female  1000    37070397        970
Female  970     60714297        910
Female  910     30834797        0