Hive系列（二）Hive的基本原理与操作--全的不行！！！_hive系列文章(2):hive介绍

一：Hive的基本架构原理：

1．用户接口：Client

CLI（hive shell）、JDBC/ODBC(java访问hive)、WEBUI（浏览器访问hive）

2．元数据：Metastore

元数据包括：表名、表所属的数据库（默认是default）、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等；
默认存储在自带的derby数据库中，推荐使用MySQL存储Metastore

3．Hadoop

使用HDFS进行存储，使用MapReduce进行计算。

4．驱动器：Driver

（1）解析器（SQL Parser）：将SQL字符串转换成抽象语法树AST，这一步一般都用第三方工具库完成，比如antlr；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。
（2）编译器（Physical Plan）：将AST编译生成逻辑执行计划。
（3）优化器（Query Optimizer）：对逻辑执行计划进行优化。
（4）执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是MR/Spark。

Hive通过给用户提供的一系列交互接口，接收到用户的指令(SQL)，使用自己的Driver，结合元数据(MetaStore)，将这些指令翻译成MapReduce，提交到Hadoop中执行，最后，将执行返回的结果输出到用户交互接口。

二：Hive的作用和优势：

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

  • Hive是基于Hadoop的一个数据仓库工具，将结构化的数据文件映射为数据库表。
  • 提供类sql的查询语言HQL(Hive Query Language)
  • 数据不放在hive上，放在HDFS上
  • 由Facebook开源用于解决海量结构化日志的数据统计。
  • 执行程序运行在Yarn上

  ---

• 优势：

  • 提供了简单的优化模型
  • HQL类sql语法，简化MR开发
  • 支持在HDFS和HBase上临时查询数据
  • 支持用户自定义函数，格式
  • 成熟JDBC和ODBC驱动程序，用于ETL和BI
  • 稳定可靠的批处理
  • 支持在不同计算框架运行

  ---

• 缺点：

  • Hive的执行延迟比较高，因此Hive常用于数据分析，对实时性要求不高的场合

  • 迭代式算法无法表达

  • 数据挖掘方面不擅长

  • Hive自动生成的MapReduce作业，通常情况下不够智能化

  • Hive调优比较困难，粒度较粗

三：Hive的数据类型：

四：Hive元数据结构：

元数据管理:

**元数据包括：**表名、表所属的数据库（默认是default）、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等

• 记录数据仓库中的模型定义
• 默认存储在自带的derby数据库中，推荐使用MySQL存储Metastore

五：Hive的数据库表分类:

内部表：

HDFS中为所属数据库目录下的子文件夹
数据完全由Hive管理,删除表(元数据)会删除数据，虽然存储路径在HDFS上，但由Hive自己管理。

外部表：

数据保存在指定位置的HDFS路径中
Hive不完全管理数据,删除表(元数据)不会删除数据

注意：

PS:
内部表数据存储的位置是hive.metastore.warehouse.dir（默认：/user/hive/warehouse），
外部表数据的存储位置由自己制定（如果没有LOCATION，Hive将在HDFS上的/user/hive/warehouse文件夹下以外部表的表名创建一个文件夹，并将属于这个表的数据存放在这里）

---

六：Hive基本命令:

命令语句hql结构几乎和mysql一致

• 创建数据库:

  create database mydemo;
  1

• 创建内部表:

  create table userinfos(
  	userid int,
      username string
  );
  1
  2
  3
  4

• 创建外部表:

  create external table customs(
      cust_id string,
      cust_name string,
      age int
  )
  row format delimited fields terminated by ','
  location '/data';
  1
  2
  3
  4
  5
  6
  7

• 插入表数据:

  insert into userinfos values('1','zs');
  1

• 查询表数据:

  select count(*) from userinfos;
  1

• 修改表元数据:

  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); -- 替换列改数据类型
  1
  2
  3
  4
  5
  6
  7
  8

• 使用shell命令:

  !hdfs dfs -text /opt/soft/hive110/mydemo.db/userinfos/000000_0
  1

Hive的更新和删除操作需要配置事务

---

七：Hive建表高阶语句：CTAS-WITH

CATS-as select 方式建表

create table ctas_employee as select * from employee
1

CTE(CATS 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;
1
2
3
4
5
6

hive和mysql对比

# mysql
select r.username,r.classname,r.score,r.score/l.countScore *100 
from(select classname,sum(score) countScore from scores group by classname) l 
inner join(select u.*,s.classname,s.score from userinfos u inner join scores s on u.userid=s.userid) r 
on l.classname = r.classname

# hive
with a1 as(select classname,sum(score) countScore from scores group by classname),a2 as(select u.*,s.classname,s.score from userinfos u inner join scores s on u.userid=s.userid) select a2.username,a2.classname,a2.score,(a2.score/a1.countScore*100) from a1 inner join a2 on a1.classname=a2.classname;
1
2
3
4
5
6
7
8

创建临时表

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

• 表只对当前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..
1
2
3

---

八：Hive数据分区：-partition

• 分区主要用于提高性能

  • 分区列的值将表划分为segments（文件夹）
  • 查询时使用分区列和常规列类似
  • 查询Hive自动过滤不用于提高性能的分区

• 分为静态分区和动态分区

  静态分区–相当于指定手动创建

  ALTER TABLE employee_partitioned ADD 
  PARTITION (year=2019,month=3) PARTITION (year=2019,month=4); 
  ALTER TABLE employee_partitioned DROP PARTITION (year=2019, 
  1
  2
  3

  insert into 追加
  insert overwrite into覆盖 拉链表 全量表
  1
  2

  • 添加静态分区的数据

  # 塞值
  insert into table mypart partition(gender='male') values(1,'zs');
  
  # 塞表 静态塞值的时候不需要塞分区字段名
  insert overwrite table mypart partition(gender='female')
  select userid,username from userinfos;
  
  # 如果塞的表和分区的分区字段不一致，会强行把表的分区字段变为一致
  # 就是到这个分区，这个分区的字段都变为一致。
  1
  2
  3
  4
  5
  6
  7
  8
  9

  ---

  动态分区

  • 使用动态分区需设定属性–开启动态分区

  set hive.exec.dynamic.partition=true;
  set hive.exec.dynamic.partition.mode=nonstrict;
  1
  2

  • 动态分区设置方法

  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;
  1
  2
  3
  4
  5
  6
  7

  • 设置动态分区的个数上限

  set hive.exec.max.created.files=600000;
  1

  • 加载本地数据文件到hive数据库表

   load data local inpath '/opt/wyw.xlsx' overwrite into table mydemo.customs2;
  1

  • 将一张表导入另一张表

   # 给一张表的对应分区里插入另一张表的数据，动态塞值的时候需要塞分区字段名
   insert into table mypart partition(gender) 
   select userid,username,gender from userinfos;
  1
  2
  3

  insert overwrite table userinfos partition(year,month) select userid,username,age,regexp_replace(birthday,'/','-'),gender,split(birthday,'/')[0] as year, split(birthday,'/')[1] as month from customs3;
  1

---

九：Hive数据分桶：-Buckets

分桶对应于HDFS中的文件

• 更高的查询处理效率
• 使抽样(sampling)更高效
• 根据“桶列”的哈希函数将数据进行分桶

分桶只有动态分桶

• set hive.enforce.bucketing=true;
  1

定义分桶

• # 分桶列是表中已有列
  # 分桶数是2的n次方
  # 直接分文件，不是分文件夹
  create table xxx()
  clustered by (employee_id) into 2 buckets
  1
  2
  3
  4
  5

分桶抽样（Sampling）：

• 随机抽样基于整行数据

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

• 随机抽样基于指定列

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

• 随机抽样基于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;
  
  1
  2
  3
  4

  create table customs3(userid int, username string,age int,birthday string,gender string) row format delimited fields terminated by ','
  1

索引–分区–分桶：

索引和分区最大的区别就是索引不分割数据库，分区分割数据库。

索引其实就是拿额外的存储空间换查询时间，但分区已经将整个大数据库按照分区列拆分成多个小数据库了。

分区和分桶最大的区别就是分桶随机分割数据库，分区是非随机分割数据库。

因为分桶是按照列的哈希函数进行分割的，相对比较平均；而分区是按照列的值来进行分割的，容易造成数据倾斜。

其次两者的另一个区别就是分桶是对应不同的文件（细粒度），分区是对应不同的文件夹（粗粒度）。

注意：普通表（外部表、内部表）、分区表这三个都是对应HDFS上的目录，桶表对应是目录里的文件

---

十：Hive视图操作：

视图概述

• 通过隐藏子查询、连接和函数来简化查询的逻辑结构

• 虚拟表，从真实表中选取数据

• 只保存定义，不存储数据

• 如果删除或更改基础表，则查询视图将失败

• 视图是只读的，不能插入或装载数据

应用场景

• 将特定的列提供给用户，保护数据隐私

• 查询语句复杂的场景

视图操作命令：

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; -- 更改视图定义
1
2
3
4
5
6
7
8
9

Hive侧视图（lateral view）

• 常与表生成函数结合使用，将函数的输入和输出连接

• OUTER关键字：即使output为空也会生成结果

  # split将对应字段值通过，分割然后explode把
  select name,work_place,loc 
  from employee 
  lateral view outer explode(split(null,',')) a as loc;
  1
  2
  3
  4

• 进行wordcount分割

  # 建表
  create table wordcount2(word string)
  # 导数据 world,see,me
  load data local inpath '/opt/niceday.txt' overwrite into table mydemo Loading data to table mydemo.wordcount2
  # 聚合侧视图查询
  select count(*),loc from wordcount2 lateral view outer explode(split(word,',')) a as loc group by loc;
  1
  2
  3
  4
  5
  6

• 支持多层级

  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;
  1
  2
  3
  4

• 通常用于规范化行或解析JSON

十一：Hive 导表方式：

内部表：

• 创建

  create table userinfos(
  userid int,
  username string
  );
  1
  2
  3
  4

• 直接插入数据到表

   insert into table mypart values(1,'zs');
  1

• 从表中导入数据到表

   # 给一张表的对应分区里插入另一张表的数据，动态塞值的时候需要塞分区字段名
  insert into table mypart 
  select userid,username,gender from userinfos;
  1
  2
  3

• 从本地文件导入数据到表

  load data local inpath '/opt/wyw.xlsx' overwrite into table mydemo.customs2;
  1

• 从HDFS处文件导入数据到表(会把文件剪切过去，原先得不在)

  load data inpath '/opt/wyw.xlsx' overwrite into table mydemo.customs2;
  1

外部表：

• 创建并导入本地文件，还设置了分割方式

  create external table customs(
  cust_id string,
  cust_name string,
  age int
  )
  row format delimited fields terminated by ','
  location '/data'; # HDFS导入了
  1
  2
  3
  4
  5
  6
  7

• 直接插入数据到表

  insert into table mypart values(1,'zs');
  1

• 从表中导入数据到表

  insert into table mypart 
  select userid,username,gender from userinfos;
  1
  2

• 从本地文件导入数据到表

  load data local inpath '/opt/wyw.xlsx' overwrite into table mydemo.customs2;
  1

• 从HDFS处文件导入数据到表(会把文件剪切过去，原先得不在)

  load data inpath '/opt/wyw.xlsx' overwrite into table mydemo.customs2;
  1