什么是Hive?
- 基于Hadoop的数据仓库解决方案
- 将结构化的数据文件映射为数据库表
- 提供类sq|的查询语言HQL (Hive Query Language)
- Hive让更多的人使用Hadoop
- Hive成为Apache顶级项目
- Hive始于 2007年的Facebook
- 官网: hive.apache.org
Hive的优势和特点
- 提供了一个简单的优化模型
- HQL类SQL语法,简化MR开发
- 支持在不同的计算框架.上运行
- 支持在HDFS和HBase.上临时查询数据
- 支持用户自定义函数、格式、宏
- 成熟的JDBC和ODBC驱动程序,用于ETL和BI
- 稳定可靠(真实生产环境)的批处理
- 有庞大活跃的社区
Hive元数据管理
记录数据仓库中模型的定义、各层级间的映射关系
存储在关系数据库中
- 默认Derby, 轻量级内嵌SQL数据库
- Derby非常适合测试和演示
- 存储在 metastore.db目录中
- 实际生产-般存储在MySQL中
- 修改配置文件hive-site.xml
HCatalog - 将Hive元数据共享给其他应用程序
mysql数据库元数据:数据库表结构
元数据归自己管
hive将要存储的数据交给hdfs,hive将元数据(默认)存在自带的Derby数据库,生产过程中元数据(表信息)存在mysql中,hive负责管理
Hive体系架构
Hive Interface - 命令窗口模式
- 有两种工具:Beeline和Hive命令行(CLI)
- 有两种模式:命令行模式和交互模式
- 命令行模式:
交互模式
Hive工具操作
使用Hive:
- hive
使用beeline: - 虚拟机里的URL
- jdbc:hive2://localhost:10000/default
- beeline -u “jdbc:hive2://localhost:10000/default”
Hive数据类型 - 原始类型
- 类似于SQL数据类型
Hive数据类型 - 复杂数据类型
- ARRAY:存储的数据为相同类型
- MAP:具有相同类型的键值对
- STRUCT:封装了一组字段
Hive元数据结构
数据库(Database)
- 表的集合,HDFS中表现为一个文件夹
- 默认在hive.metastore.warehouse.dir属性目录下
- 如果没有指定数据库,默认使用default数据库
create database if not exists mydemo;
use mydemo;
show databases;
describe database default; --more details than ’show’, such as location
alter database mydemo set owner user dayongd;
drop database if exists mydemo cascade;
数据表(Tables)
- 分为内部表和外部表
内部表(管理表) - HDFS中为所属数据库目录下的子文件夹
- 数据完全由Hive管理,删除表(元数据)会删除数据
外部表(External Tables) - 数据保存在指定位置的HDFS路径中
- Hive不完全管理数据,删除表(元数据)不会删除数据
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)
Hive建表高阶语句 - CTAS and WITH
CTAS – as select方式建表
CREATE TABLE ctas_employee as SELECT * FROM employee;
- CTAS不能创建partition, external, bucket table
CTE (CTAS with Common Table Expression)
CREATE TABLE cte_employee AS
WITH
r1 AS (SELECT name FROM r2 WHERE name = 'Michael'),
r2 AS (SELECT name FROM employee WHERE sex_age.sex= 'Male'),
r3 AS (SELECT name FROM employee WHERE sex_age.sex= 'Female')
SELECT * FROM r1 UNION ALL SELECT * FROM r3;
Like
CREATE TABLE employee_like LIKE employee;
创建临时表
临时表是应用程序自动管理在复杂查询期间生成的中间数据的方法
- 表只对当前session有效,session退出后自动删除
- 表空间位于/tmp/hive-<user_name>(安全考虑)
- 如果创建的临时表表名已存在,实际用的是临时表
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 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分区(Partitions)
分区主要用于提高性能
- 分区列的值将表划分为segments(文件夹)
- 查询时使用“分区”列和常规列类似
- 查询时Hive自动过滤掉不用于提高性能的分区
分为静态分区和动态分区
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 employee_partitioned ADD -- ALTER TABLE的方式添加静态分区,ADD添加分区, DROP删除分区
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 into table employee_partitioned partition(year, month)
select name,array('Toronto') as work_place,
named_struct("sex","male","age",30) as sex_age,-- insert方式添加动态分区
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 ;
分桶(Buckets )
分桶对应于HDFS中的文件
- 更高的查询处理效率
- 使抽样(sampling)更高效
- 根据“桶列”的哈希函数将数据进行分桶
分桶只有动态分桶 - SET hive.enforce.bucketing = true;
定义分桶 - CLUSTERED BY (employee_id) INTO 2 BUCKETS
- 分桶的列是表中已有的列
- 分桶数最好是2的n次方
必须使用INSERT方式加载数据
分桶抽样(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
数仓分层:
- ods层 原始数据层,数仓准备区,提供原始数据
- dwd层 明细数据层,为DW层提供来源明细数据(建模,外部表),近源层数据
- dws层 服务数据层 ,以dwd为基础,进行轻度汇总
- ads层 数据应用层, 叫法繁多(app层,dal层,dm层)