Apache Hive基础

Apache Hive基础

Hive优势：
提供了一个简单的优化模型
HQL类SQL语法，简化MR开发
支持在不同的计算框架上运行
支持在HDFS和HBase上临时查询数据
支持用户自定义函数、格式
成熟的JDBC和ODBC驱动程序，用于ETL和BI
稳定可靠（真实生产环境）的批处理
有庞大活跃的社区

Hive元数据管理
记录数据仓库中模型的定义
存储在关系数据库中
默认是Derby，轻量级内嵌SQL数据库，Derby非常适合测试演示，存储在.metastore_db目录中
实际生产一般储存在MySQL中，修改配置文件hive-site.xml
Hcatalog
将Hive元数据共享给其他应用程序上

Hive架构体系

Hive Interface 命令窗口模式
有两种工具：Beeline和Hive命令行（CLI）
有两种模式：命令行模式和交互模式
命令行模式

交互模式

Hive工具操作
检查Hive服务是否正常启动
使用Hive：hive
使用beeline
虚拟机的URL
jdbc:hive2://localhost:10000/default
beeline -u :jdbc:hive2://localhost:10000/default"

Hive数据类型 - 原始数据
类似于SQL数据类型

Hive数据类型 - 复杂数据类型
ARRRAY:存储的数据为相同的类型
MAP：具有相同类型的键值对
STRUCT：封装了一些字段

Hive元数据结构

数据库（database）
表的集合，HDFS中表现为一个文件夹（默认在hive.metastore.warehouse.dir属性目录下）

create database if not exists myhivebook;
use myhivebook;
show databases;
describe database default; --more details than ’show’, such as location（--is comment）
alter database myhivebook set owner user dayongd;
drop database if exists myhivebook cascade;

表（Tables）
分为内部表和外部表
内部表（管理表）
HDFS中为所属数据库目录下的子文件夹
数据完全由Hive管理，删除表(元数据)会删除数据
外部表（External Tables）
数据保存在指定位置的HDFS路径中
Hive不完全管理数据，删除表(元数据)不会删除数据

建表语句

CREATE EXTERNAL TABLE IF NOT EXISTS employee_external (--IF NOT EXISTS可选，
如果表存在，则忽略
    name string,
    work_place ARRAY<string>,
    sex_age STRUCT<sex:string,age:int>,--列出所有列和数据类型
    skills_score MAP<string,int>,
    depart_title MAP<STRING,ARRAY<STRING>>
)
COMMENT 'This is an external table'--COMMENT可选
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'--如何分隔列（字段）
COLLECTION ITEMS TERMINATED BY ','--如何分隔集合和映射
MAP KEYS TERMINATED BY ':'
STORED AS TEXTFILE--文件存储格式
LOCATION '/user/root/employee'; --数据存储路径（HDFS)

表操作 - 删除/修改表
删除表

DROP TABLE IF EXISTS employee [With PERGE];--With PERGE直接删除（可选），否则会放到 .Trash目录
TRUNCATE TABLE employee; -- 清空表数据

修改表（Alter针对元数据）

ALTER TABLE employee RENAME TO new_employee;--修改表名，常用于数据备份
ALTER TABLE c_employee SET TBLPROPERTIES ('comment'='New name, comments');
ALTER TABLE employee_internal SET SERDEPROPERTIES ('field.delim' = '$’);
ALTER TABLE c_employee SET FILEFORMAT RCFILE; -- 修正表文件格式
-- 修改表的列操作
ALTER TABLE employee_internal CHANGE old_name new_name STRING; -- 修改列名
ALTER TABLE c_employee ADD COLUMNS (work string); -- 添加列
ALTER TABLE c_employee REPLACE COLUMNS (name string); -- 替换列

Hive分区操作 - 定义分区

--建表时候定义分区
CREATE TABLE employee_partitioned(
    name string,
    work_place ARRAY<string>,
    sex_age STRUCT<sex:string,age:int>,
    skills_score MAP<string,int>,
    depart_title MAP<STRING,ARRAY<STRING>> )
PARTITIONED BY (year INT, month INT)--通过PARTITIONED BY 定义分区
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':';

静态分区操作

--ALTER TABLE 的方式添加静态分区
--ADD添加分区，DROP删除分区
ALTER TABLE employee_partitioned ADD 
PARTITION (year=2019,month=3) PARTITION (year=2019,month=4); 

ALTER TABLE employee_partitioned DROP PARTITION (year=2019, month=4);

Hive分区操作 - 动态分区
使用动态区需要设定属性`

--使用动态分区需进行设置
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.modenonstrict;

动态分区设置方法

--insert方式添加动态分区
insert into table employee_partitioned partition(year, month)
select name,array('Toronto') as work_place,
named_struct("sex","male","age",30) as sex_age,
map("python",90) as skills_score,
map("r&d", array('developer')) as depart_title,
year(start_date) as year,month(start_date) as month
from employee_hr eh ;	

分桶抽样（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;

--随机抽样基于block size
SELECT * FROM table_name TABLESAMPLE(10 PERCENT) s;
SELECT * FROM table_name TABLESAMPLE(1M) s;
SELECT * FROM table_name TABLESAMPLE(10 rows) s;

Hive视图（Views）
视图概述
通过隐藏子查询、连接和函数来简化查询的逻辑结构
虚拟表，从真实表中选取数据
只保存定义，不存储数据
如果删除或更改基础表，则查询视图将失败
视图是只读的，不能插入或装载数据
应用场景
将特定的列提供给用户，保护数据隐私
查询语句复杂的场景

Hive视图操作命令：CREATE、SHOW、DROP、ALTER

CREATE VIEW view_name AS SELECT statement; -- 创建视图
	-- 创建视图支持 CTE, ORDER BY, LIMIT, JOIN, etc.
SHOW TABLES; -- 查找视图 (SHOW VIEWS 在 hive v2.2.0之后)
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; -- 更改视图定义, 

Hive侧视图（Lateral View）
常与生成函数结合使用，将函数的输入和输出连接

--OUTER关键字：即使output为空也会生成结果
select name,work_place,loc from employee lateral view outer explode(split(null,',')) a as loc;
--支持多层级
select name,wps,skill,score from employee 
lateral view explode(work_place) work_place_single as wps
lateral view explode(skills_score) sks as skill,score;
--通常用于规范化行或解析JSON