Hive

作者: 王龙江_3c83 | 来源:发表于2019-04-06 09:39 被阅读0次

1. 简介

Hive是一款基于Hadoop的数据仓库工具，设计初衷就是使不擅长编程的数据分析人员也能参与到大数据中来，常用功能是数据提取转化加载（ETL）和数据分析。其使用存储在HDFS中的数据，可以将结构化的数据文件映射为一张数据库表，并将SQL语句转换为MapReduce任务（目前不止MapReduce，还包括Spark和TEZ）执行查询。

2. 架构

Hive架构如图11所示：由客户端提供查询语句，提交给Hive，Hive再交给Driver处理（Compiler先编译，编译时要从元数据库中获取元数据信息，生成逻辑计划；生成物理计划；由优化器进行优化；执行器提交任务，同时还需要提取元数据信息关联具体的数据，这些元数据信息送到HDFS的数据节点），并将结果返回或写入HDFS。

image.png

3. 特点

3.1 可扩展性

Hive 可以自由的扩展集群的规模，Hive 本身只是将 HSQL 解析为 MapReduce 任务，数据存储在 HDFS，所以其扩展性取决与 Hadoop 的扩展性。

3.2 支持自定义函数

Hive 支持自定义函数，用户可以根据自己的需求来实现自己的函数。

3.3 高容错性

可以保障即使有节点出现问题，SQL 语句仍可完成执行。由于 Hive 本身只是将 HSQL 解析为 MapReduce 任务，其容错性取决于底层的文件系统HDFS，和计算引擎 MapReduce，而HDFS 和 MapReduce 定位是运行在通用硬件上，因此容错性可以保障。

4. 语法

4.1 DDL

4.1.1 数据库操作

4.1.1.1 创建

create database if not exists hive;
# 同 mysql

4.1.1.2 查询数据库列表

show databases;
# 同 mysql

4.1.1.3 删除数据库

drop database if exists hive;
# 同 mysql
drop database if exists hive cascade;
# 删除带表的数据库

4.1.1.4 描述数据库

desc database hive;
# 同 mysql

4.1.1.5 查看当前使用的数据库

select current_database();

4.1.1.6 切换数据库

use hive;
# 同 mysql

4.1.2 表操作

4.1.2.1 创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name

　　[(col_name data_type [COMMENT col_comment], ...)]

　　[COMMENT table_comment]

　　[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]

　　[CLUSTERED BY (col_name, col_name, ...)

　　　　[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]

　　[ROW FORMAT row_format]

　　[STORED AS file_format]

　　[LOCATION hdfs_path]

EXTERNAL:指定创建外部表。
COMMENT：可以为表和字段添加描述。
PARTITIONED BY：指定分区字段（支持多属性分区）。
CLUSTERED BY: 指定分桶字段，分桶和分区的区别：分桶字段在文件中中，分区字段不在文件中。
SORTED BY：指定桶内排序字段。
ROW FORMAT：指定分割符。
STORED AS ：文件类型。
LOCATION：表路径。

4.1.2.1.1 创建内部表

创建表

create table student(id int, name string, sex string, age int,department string) row format delimited fields terminated by ",";
* 导入数据

load data INPath '/Users/dreamaker/Downloads/apache-hive-2.3.4-bin/data/tableA' into table tableA;

查询数据

select a.id,a.name,a.grade,a.dept,b.id,b.name from tableA a left join tableB b on a.dept=b.id and a.grade>80;

4.1.2.1.2 创建外部表

在创建内部表语法基础上，需使用 EXTERNAL 关键字，并指定 location 。

4.1.2.1.3 创建分区表

在内部表（或外部表）的基础上，指定 PARTITIONED BY 属性
使用 alter 命令添加分区。

alter table tablename add partition(key=value);

在 load data 目录的基础上，添加 partition(key=value)命令。

load data local inpath "/home/hadoop/student.txt" into table student_ptn partition(city="beijing");

动态分区
创建分区表

create table student_ptn_age(id int,name string,sex string,department string) partitioned by (age int);

在插入数据时指定按照查询结果中的某个属性分区

insert overwrite table student_ptn_age partition(age) select id,name,sex,department，age from student_ptn;

4.1.2.1.4 创建分桶表

在普通表的基础上，使用CLUSTERED BY (col_name, col_name, ...)[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS命令。

4.1.2.1.5 使用 CTAS 创建表

create table student_ctas as select * from student where id < 95012;

4.1.2.1.6 复制表结构

create table stu like student;

4.1.2.2 查看表

查看表基本信息

desc tablename;

查看表详细信息

desc extended tablename;

格式化表详细信息

desc formatted tablename;

查看表分区信息

show partitions tablename;

查看表的详细建表语句

show create table tablename;

4.1.2.3 列举表

列举当前数据库中的所有表

show tables;

列举指定数据库中的所有表

show tables in hive;

模式匹配当前数据库中的所有表

show tables like 'stu*';

4.1.2.4 修改表

4.1.2.4.1 修改表名

alter table oldname rename to newname;

4.1.2.4.2 添加列

alter table tablename add colums (filed,datatype);

4.1.2.4.3 修改一列的定义

alter table tablename change old_columname  new_columname datatype;

4.1.2.4.4 替换所有字段

alter table tablename replaces columns (columname,datatype...);

4.1.2.4.5 添加分区

前文创建分区表中已演示，支持批量添加分区。

4.1.2.4.6 修改分区的存储目录

alter table student_ptn partition (city='beijing') set location '/student_ptn_beijing';

4.1.2.4.7 删除分区

alter table student_ptn drop partition (city='beijing');

4.1.2.5 删除表

drop table tablename;

4.1.2.6 清空表

truncate table tablename;

4.1.3 视图操作

4.1.3.1 创建

create view view_cdt as select * from cdt;

4.1.3.2 查看视图列表

show views

4.1.3.3 描述视图

desc view_cdt;

4.1.3.4 查询视图

select * from view_cdt;

4.1.3.5 删除视图

drop view if exists view_cdt;

4.2 数据类型和存储格式

4.2.1 基本数据类型

布尔、整形、浮点型、字符型、字符串、字节数组、时间戳、日期

4.2.2 复合类型

类型	功能
array	有序的的同类型的集合
map	key-value,key必须为原始类型，value可以任意类型
struct
uniontype

create table cdt(
id int, 
name string, 
work_location array<string>, 
piaofang map<string,bigint>, 
address struct<location:string,zipcode:int,phone:string,value:int>) 
row format delimited 
fields terminated by "\t" 
collection items terminated by "," 
map keys terminated by ":" 
lines terminated by "\n";

4.2.3 存储格式

数据格式
textfile	默认格式，存储方式为行存储。数据不做压缩，磁盘开销大，数据解析开销大。
SequenceFile	是Hadoop API提供的一种二进制文件支持，其具有使用方便、可分割、可压缩的特点。支持三种压缩选择：NONE, RECORD, BLOCK。 Record压缩率低，一般建议使用BLOCK压缩。
RCFile	一种行列存储相结合的存储方式。
ORCFile	数据按照行分块，每个块按照列存储，其中每个块都存储有一个索引。hive给出的新格式，属于RCFILE的升级版,性能有大幅度提升,而且数据可以压缩存储,压缩快快速列存取。
Parquet	Parquet也是一种行式存储，同时具有很好的压缩性能；同时可以减少大量的表扫描和反序列化的时间。

4.3 函数

4.3.1 内置函数

4.3.1.1 数学函数

取整、随机数、数学运算

4.2.1.2 集合函数

size

4.2.1.3 类型转换

4.2.1.4 日期函数

4.2.1.5 条件函数

select case field1 when value1 then field2 else 0 as field3
case field1 when value2 then field2 else 0 as field4
# case 数等于 field1 不同的 value 数。

4.2.1.6 字符函数

4.2.1.7 聚合函数

4.2.1.8 表生成函数

4.2.1.9 窗口函数

partition by:指定分组属性。
order by:指定排序属性。
聚合函数：max()、min()、sum、avg()、rank()、ntile(n)、row_number()、dense_rank()

select max() over (partition by field1 [order by field2] [rows between unbounded/n preceding and current row/unbounded/n following])

preceding: 窗口左边界，可指定为 current row。
following: 窗口右边界，可指定为 current row。

4.2.2 查看内置函数列表

show functions

4.2.1.2 描述函数

desc function [extended]substr;

4.2.2 自定义函数 UDF

4.2.2 自定义函数

4.3 DQL

4.1 Join

关键字	功能
Join
Left Outer Join	返回左边表的所有行。
Right Outer Join	返回右边表的所有行。
Full Outer Join	包含两个表的所有行。

4.4 实战

4.4.1

查询每个用户每月的 ucount 之和。

insert into table tmp_access select uname,umonth,sum(ucount) from t_access t group by t.uname,t.umonth;

查询每个用户每个月的最高 umount，和总 umount。

select name,mon,num,max(num) over (partition by name order by mon)  as max, sum(num) over (partition by name order by mon) as sum from tmp_access;

4.4.2 查询数学成绩高于其他成绩的学生

create view score_rank as select sid,course,rank() over (partition by sid order by score desc) as rn1 from course;

select * from score_rank where rn1=1 and course='shuxue';

4.4.3 查询每年的最高温度和具体哪一天

create view weather_rank as select substr(data,1,8) `date`,rank() over (partition by substr(data,1,4) order by substr(data,9,2) desc) as rn1 from weather;

select * from weather_rank where rn1=1;

4.4.4 查询学生是否选择了某门课程

select id,case course when 'a' then 1 else 0 as a,
when 'b' then 1 else 0 as b,
when 'c' then 1 else 0 as c,
when 'd' then 1 else 0 as d,
when 'e' then 1 else 0 as e,
when 'f' then 1 else 0 as f;

select id,max(a),max(b),max(c),max(d),max(e),max(f) group by id;

4.4.5 求月销售额和累计到当月的总销售额

窗口函数

5. 数据仓库理论