hive的基本语法

什么是hive？

基于Hadoop的数据仓库解决方案

1.将结构化的数据文件映射为数据库表
2.提供类sql的查询语言HQL（Hive Query Language）
3.Hive让更多的人使用Hadoop

Hive的优势和特点

1.提供了一个简单的优化模型
2.HQL类SQL语法，简化MR开发
3.支持在不同的计算框架上运行
4.支持在HDFS和HBase上临时查询数据
5.支持用户自定义函数、格式
6.常用于ETL操作和BI
7.稳定可靠（真实生产环境）的批处理
8.有庞大活跃的社区

Hive的发展里程碑和主流版本

07年8月 – 始于Facebook
13年5月 – 0.11 Stinger Phase 1 ORC HiveServer2
13年10月 – 0.12.0 Stinger Phase 2 - ORC improvement
14年4月 – Hive 0.13.0 as Stinger Phase 3
14年11月 – Hive 0.14.0
15年2月 – Hive 1.0.0
15年5月 – Hive 1.2.0 （1.2.1 本系列课实验重点版本 )
16年2月 – Hive 2.0.0 (添加 HPLSQL, LLAP)
16年6月 – Hive 2.1.0

Hive元数据管理

记录数据仓库中模型的定义、各层级间的映射关系

存储在关系数据库中

1.默认Derby, 轻量级内嵌SQL数据库
Derby非常适合测试和演示
存储在.metastore_db目录中
2.实际生产一般存储在MySQL中
修改配置文件hive-site.xml

HCatalog

3.将Hive元数据共享给其他应用程序

Hive环境搭建

HIVE环境的搭建

Hive架构

Hive操作-命令行模式

1.有两种客户端工具：Beeline和Hive命令行（CLI）
2.有两种模式：命令行模式和交互模式
3.命令行模式

4.交互模式

Hive操作-客户端交互模式

1.检查Hive服务是否已经正常启动
2.使用Hive交互方式（输入hive即可）
3.使用beeline
4.需启动hiveserver2服务
nohup hive --service hiveserver2 &
输入beeline进入beeline交互模式
!connect jdbc:hive2://hadoop101:10000

Hive数据类型 - 基本数据类型

Hive数据类型 - 集合数据类型

1.ARRAY：存储的数据为相同类型
2.MAP：具有相同类型的键值对
3.STRUCT：封装了一组字段

Hive数据结构

数据表（Table)

数据表分为内部表和外部表。
内部表：
数据由Hive管理，对表进行操作后，数据会保存。HDFS中为所属数据库目录下的子文件夹。
外部表：
数据保存在指定位置的HDFS路径中，Hive不完全管理数据，对表操作后数据不会保存。

Hive建表语句

cretae (external/temporary) table if not exists example( // 创建(永久/临时)表
id int ,name string
)
row fromat delimited fields terminated by ','  //按照 ',' 分隔
stored as textfile // 存为textfile文件
location '/opt/hadoop/hive/software'; // 保存地址

Hive建表 - 分隔符

Hive中默认分隔符

1.字段：^A(\001)
2.集合：^B(\002)
3.映射：^C(\003)

Hive建表高阶语句 - CTAS and WITH

CTAS – as select方式建表

create table ctas_example as select * from example; //使用从example中获取的数据建表

注 ： CTAS不能创建partition, external, bucket table

CTE (CTAS with Common Table Expression)

create table cte_example as 
with 
r1 as (select name from r2 where name = 'Dashuaibi'),//从r2表中筛选名字为大帅逼的名字作为r1表
r2 as (select name from example where sex='male'),//筛选性别为male的名字作为r2表
r3 as (select name from example where sex='female')//筛选性别为female的名字作为r3表
select * from r1 union all select * from r3; //把r3表的内容和r1合并

like

create table example_like like example;// 创建一个表结构与example相同的表，但是是个空表。

创建临时表

临时表是应用程序自动管理在复杂查询期间生成的中间数据的方法

1.表只对当前session有效，session退出后自动删除
2.表空间位于/tmp/hive-<user_name>(安全考虑)
3.如果创建的临时表表名已存在，实际使用的表是临时表

CREATE TEMPORARY TABLE tmp_table_name1 (c1 string);
CREATE TEMPORARY TABLE tmp_table_name2 AS..
CREATE TEMPORARY TABLE tmp_table_name3 LIKE..

表操作 - 删除/修改表

删除表

DROP TABLE IF EXISTS example ;
TRUNCATE TABLE employee; -- 清空表数据

修改表

alter table example rename to new_example; //修改表名
alter table example set tblproperties("comment"="table_comment");//允许开发者定义一些自己的键值对信息
alter table example set serdeproperties  ('field.delim' = '$');//修改结束符
alter table example set fileformat rcfile; // 修改表文件格式
-----------
alter table example change old_name new_name string; //**修改列名**
alter table example add columns(work string)  // **添加列**
alter table example replace columns(name string); // **替换列**

装载数据：LOAD

LOAD用于在Hive中移动数据

load data local inpath '/opt/software/a.txt'
overwrite into table example;// 把a.txt装载进example表
-------
load data local inpath '/opt/software/a.txt'
overwrite into table example_partitioned partition(year=2020);// 把a.txt装载进example表中的year为20 的分区。
-------
//没有LOCAL关键字，表示文件位于HDFS文件系统中,执行后为直接移动数据
load data inpath '/opt/software/a.txt'
overwrite into table example;// 把a.txt移动到example表

Hive分区（Partition）

分区主要用于提高性能

1.分区列的值将表划分为一个个的文件夹
2.查询时语法使用"分区"列和常规列类似
3.查询时Hive会只从指定分区查询数据，提高查询效率

分为静态分区和动态分区

Hive分区操作 - 静态分区

创建分区表

create table example_partition(
id string,
name string ,
)
partitioned by (month string )
row format delimited fields terminated by ',';
//创建一个按照month分区，由','分隔的表。

静态分区操作

---//添加分区
alter table example_partition add partition (month='202007');
alter table example_partition add partition (month='202007'),partition(month='202006');
---//删除分区
alter table example_partition drop partition (month='202007');
alter table example_partition drop partition (month='202007'),partition(month='202006');
---//查看分区
show partitions example_partition;
---//加载数据到分区表中
load data local inpath '/opt/a.txt' into table example_partition
partition (month='202007');

Hive分区操作 - 动态分区

使用动态分区需设定属性

set hive.exec.dynamic.partition=true; 
set hive.exec.dynamic.partiiton.mode=nonstrict;

动态分区建表语句和静态分区相同

动态分区插入数据

insert into dynamic_example
partition(year,month)
select id,name,age from dynamic_example;
---//查询数据
select * from dynamic_example where year='2020';

分桶（Bucket）

分桶对应于HDFS中的文件

1.更高的查询处理效率
2.使抽样（sampling）更高效
3.一般根据"桶列"的哈希函数将数据进行分桶

分桶只有动态分桶

SET hive.enforce.bucketing = true;

定义分桶

CLUSTERED BY (employee_id) INTO 2 BUCKETS

分桶抽样（Sampling）

随机抽样基于整行数据

SELECT * FROM table_name TABLESAMPLE(BUCKET 3 OUT OF 32 ON rand()) s;

随机抽样基于指定列（使用分桶列更高效）

SELECT * FROM table_name TABLESAMPLE(BUCKET 3 OUT OF 32 ON id) s;

Hive视图（View）

视图概述

1.通过隐藏子查询、连接和函数来简化查询的逻辑结构
2.只保存定义，不存储数据
3.如果删除或更改基础表，则查询视图将失败
4.视图是只读的，不能插入或装载数据

应用场景

1.将特定的列提供给用户，保护数据隐私
2.用于查询语句复杂的场景

Hive视图操作

-- 创建视图，支持 CTE, ORDER BY, LIMIT, JOIN,等
CREATE VIEW view_name AS SELECT statement; 
-- 查找视图 (SHOW VIEWS 在 hive v2.2.0之后)
SHOW TABLES; 
-- 查看视图定义
SHOW CREATE TABLE view_name; 
-- 删除视图
DROP view_name; 
--更改视图属性
ALTER VIEW view_name SET TBLPROPERTIES ('comment' = 'This is a view');
-- 更改视图定义,
ALTER VIEW view_name AS SELECT statement;