1.Hive原理介绍
2.Hive Sql
- 2.1 DDL（Data Defination Language）数据库建库语言
  - 2.1.1 建表说明
create table :创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。
1.建表输入文件是ORC，输出文件是ORC
2.指定数据文件存放目录(默认的数据库目录下表目录下存放)
3.指定文件内容分隔符

— 建表方式 —
create table table_name as select id,name,sex from table_name1；
- 2.1.2 分区表
- 2.1.3 分桶表
2.2 DML（Data Manipulation Language）数据库操作语言
插入数据
// 普通插入
insert into table values (),();
// 加载文件
load data inpath ‘hdfs://file_path’ [overwrite] into table table_name;
加了overwrite是覆盖原有的文件
// 插入数据
insert [overwrite] into table_name select fields … from table_name1;
#删除数据
truncate table table_name
#没有delete和update
# 多表插入
from table_name
insert into table_name1 select id,name,sex where come_date=’2021-05-01’
insert into table_name2 select id,name,sex,addr where come_date=’2021-05-11’
insert into table_name3 select id,name,sex where come_date=’2021-05-20’
- 2.3 DQL（Data Query Language）数据库查询语言
- 2.4 字段类型
3.Hive 函数
- 3.1系统函数
- 3.2自定义函数 :
4. 数据仓库分层和建模
5.Hive 分析函数
6. Hive Sql优化
7. Hive数据倾斜问题
8.Hive参数

1.Hive原理介绍

1.1Hive介绍

Hive大数据仓库解决方案，基于Hadoop平台搭建的数据仓库
英文名称为Data Warehouse，可简写为DW。
面向分析的存储系统(面向数据分析的存储系统）
数据库与数据仓库的区别？
数据库-mysql/oracle

面向事务性操作
响应及时
数据量不大

数据仓库-hive /oracle

面向分析操作
存储历史数据
数据量大
响应不及时

Hive解决什么问题 Oracle — 传统数据仓库搭建
在Haddop项目中，HDFS解决了文件分布式存储的问题，MapReducce解决了数据处理分布式计算的问题，Hbase解决了数据的存储和检索。
但是要对HDFS上的文件或者Hbase上的表进行查询的时候，要手工写一堆的MapReduce，这只能由懂MapReduce的程序员操作，对于业务人员或数据科学家，非常不方便。因为他们已经习惯了通过SQL跟RDBMS（关系数据库管理系统）打交道，因此如果有sql查询文件和数据就显得很有必要，这就是hive要满足的需求。

Hive是一个SQL解析引擎，将SQL语句转译成MR Job，然后再Hadoop平台上运行，达到快速开发的目的。
Hive本身不存储数据，它完全依赖HDFS和MapReduce。
Hive的内容是读多写少，不支持对数据的改写和删除

Hive — 解决海量数据分布式存储问题，基于Hadoop平台搭建的数据仓库
— 表面 SQL
— 计算计算引擎 MR Tez Spark
— 资源 Yarn StandLone
— 存储 HDFS

1.2Hive原理
Hive原理详解：https://blog.csdn.net/ForgetThatNight/article/details/79632364?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522162246709116780265416331%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=162246709116780265416331&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduend~default-2-79632364.first_rank_v2_pc_rank_v29&utm_term=hive%E5%8E%9F%E7%90%86&spm=1018.2226.3001.4187

Hive的基本工作，简单提炼一下来表述，就是将结构化的数据映射为一张数据库表，并提供完整的SQL查询功能，可以将SQL语句转换为MapReduce任务进行运行。

1.2.1 分层
1. 用户接口
  CLI JDBC|Beeline Hive Web
  2. Hive
  HiveServer2 — 提供客户端认证服务
  Hive MetaStore — 元数据可以部署为MYSQL或嵌入式数据库Derby
  Hive Driver —
  SQL解析器
  SQL优化器
  SQL执行器
  3. Hadoop Core
  计算 mr tez spark
  资源 yarn mesos alone local
  存储 hdfs
  2. 分析hive-site.xml
  metastore.xxx:
  表存放的HDFS目录地址 /warehouse/tablespace/managed/hive/
  元数据存放的地址 MYSQL jdbc:mysql://master/hive

1.2.2 工作原理

接收SQL语句；
进行词法分析和语法分析；
进行语义分析；
生成逻辑计划，得到算子树；
逻辑计划优化。对算子树进行优化，包括剪枝和谓词下推等；
物理计划生成。将逻辑计划生产出包含由MapReduce任务组成的DAG的物理计划；
物理计划执行。将DAG发送到Hadoop集群进行执行；
将查询结果返回。

1.2.3 Hive的用户接口
Hive CLI（Hive命令行）：客户端可以直接在命令行模式下进行操作。通过命令行，用户可以定义表、执行查询等。如果没有指定其他服务，这个就是默认的服务。
HWI（Hive Web接口）：Hive提供了更直接的Web界面，可以执行查询语句和其他命令，这样可以不用登陆到集群中的某台机器上使用CLI来进行查询。
Hive Thrift（即Hive-server）：用于监听来自于其他进程的Thrift连接的一个守护进程。Thrift客户端目前支持C++/Java/Python等语言。
1.2.4 Hive创建表和处理数据的操作
（1）Hive创建表的过程
第一步：解析用户提交的Hive语句，进行解析，分解为表、字段、分区等Hive对象；
第二步：根据解析到的信息构建对应的表、字段、分区等对象。
（2）Hive元数据的三种存储模式
模式一：单用户模式；
模式二：多用户模式；
模式三：远程服务器模式。
Hive中的数据，分为表数据和元数据。表数据是Hive表格（Table）中具有的数据，而元数据是用来存储表的名字、表的列、表分区及其属性以及表的数据所在目录等。
1.3 Hive安装
Ambari -> Add Service -> Choose Service
官方网站Apache安装
1. 下载Hive二进制包
2. 解压
3. 配置HIVE_HOME到环境变量
4. HADOOP_HOME配置到hive-env.sh
5. 为Hive创建HDFS目录
hdfs dfs -mkdir /tmp
hdfs dfs -mkdir /user/hive/warehouse
hdfs dfs -chmod g+w /tmp
hdfs dfs -chmod g+w /user/hive/warehouse
6. cd hive-xxx
7. ./beeline
2.Hive Sql
参考资料：https://www.cnblogs.com/HondaHsu/p/4346354.html 文档 03Hive基础应用

操作分类	具体操作	sql备注
DDL	•建表 •删除表 •修改表结构 •创建/删除视图 •创建数据库 •显示命令	Create/Drop/Alter Database Create/Drop/Truncate Table Alter Table/Partition/Column Create/Drop/Alter View Create/Drop Index Create/Drop Function Show Describe
DML	•数据插入(insert,load)	load data…into table insert overwrite table
DQL	•数据查询(select)

2.1 DDL（Data Defination Language）数据库建库语言

2.1.1 建表说明

元数据: 描述数据的数据
表分类：主要分内表和外表
EXTERNAL关键字
可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和实体数据会被一起删除，而外部表只删除元数据，不删除实体数据。

元数据：描述数据的数据
实体数据：真正承载真实数据的内容
内表还是外表的对比说明
- 概念
  - External修饰的表称为外表
  - 没有external修饰的表称为内表
- 特点特征
  - 内表：元数据和实体数据都归Hive自身管理，一删除则全部删除。
  - 外表：元数据归Hive管理，实体数据归HDFS管理，删除表的话，只会删除元数据，不会删除实体数据。
- 应用场景
  - 如果表自身数据是可以自循环、自生成的，则使用内表。
  - 如果表自身是外部导入的，即不可实现自循环自生成的，则使用外表。
- 代码实现
  - Create external table….则生成外表
  - Create table …..则生成内表
ROW FORMAT 设置行数据分割格式

LIKE
允许用户复制现有的表结构，但是不复制数据。
STORED AS
如果表类型是外部表，则文件类型是由外部表地应源数据格式来决定。
如果表类型是内部表，则由Hive数据仓库支持的文件存储类型来决定。一般多见设置的rc或是orc，现在最多是的ORC。
存储格式区别：https://www.cnblogs.com/sx66/p/12039248.html

textfile 存储空间消耗比较大，并且压缩的text 无法分割和合并查询的效率最低,可以直接存储，加载数据的速度最高；
sequencefile 存储空间消耗最大,压缩的文件可以分割和合并查询效率高，需要通过text文件转化来加载；
orcfile, rcfile存储空间最小，查询的效率最高，需要通过text文件转化来加载，加载的速度最低；
parquet格式是列式存储，有很好的压缩性能和表扫描功能；

结论：
1.在压缩存储时间上，除Sequencefile外基本相差无几，空间换时间，压缩存储率提高了，时间也可能变长。
2.数据压缩比例上ORC最优，相比textfile节省了50倍磁盘空间，parquet压缩性能也较好。
3.SQL查询速度而言，ORC与parquet性能较好，远超其余存储格式。
综合上述各种性能指标，建议工作中原始日志写入hive的存储格式都采用ORC或者parquet格式，这和目前主流的做法一致。
[

](https://blog.csdn.net/onway_goahead/article/details/107665024)

create table :创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。
1.建表输入文件是ORC，输出文件是ORC
2.指定数据文件存放目录(默认的数据库目录下表目录下存放)
3.指定文件内容分隔符

— 建表方式 —
create table table_name as select id,name,sex from table_name1；

    create table stu_2 stored as textfile as select * from stu_1;<br />    #注意：1. 使用这两种方式建表，默认会把数据和结构拷贝过去<br />           2. 默认不复制过去表结构中的文件类型和表类型<br />           3. 可以指定文件类型<br />    <br />    -- 普通表 --    <br />create table table_name(<br />        field_name type_name comment '',<br />        field_name type_name comment '',<br />        field_name type_name comment ''<br />    )<br />    row format delimited<br />    fields terminated by '\t'<br />    lines terminated by '\n'             导入数据表的数据格式是：字段之间是tab键分割，行之间是断行。<br />    stored as textfile | orcfile<br />    location hdfs_path

分区分桶参考资料：https://www.cnblogs.com/wasaier/p/14451069.html

分区：数据本身具备明显的水平可分隔的特性，比如时间字段、国家、地区、城市等字段。
分桶：数据本身具备明显的垂直可分隔的特性，比如名字查询、ID查询等。

2.1.2 分区表
在Hive Select查询中一般会扫描整个表内容，会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据，因此建表时引入了partition概念。分区表指的是在创建表时指定的partition的分区空间。
Hive可以对数据按照某列或者某些列进行分区管理，所谓分区我们可以拿下面的例子进行解释。
当前互联网应用每天都要存储大量的日志文件，几G、几十G甚至更大都是有可能。存储日志，其中必然有个属性是日志产生的日期。在产生分区时，就可以按照日志产生的日期列进行划分。把每一天的日志当作一个分区。
将数据组织成分区，主要可以提高数据的查询速度。至于用户存储的每一条记录到底放到哪个分区，由用户决定。即用户在加载数据的时候必须显示的指定该部分数据放到哪个分区。
Partition Information 查看分区信息

— 分区表 —
```
create table table_name(
      field_name type_name comment '',
      field_name type_name comment '',
      field_name type_name comment ''
  )
  partitioned by (come_date string)
  row format delimited
  fields terminated by '\t'
  lines terminated by '\n'
  stored as textfile | orcfile
  location hdfs_path
```
多个分区：partitioned by (come_date1 string,come_date2 string)

—- 静态分区 —-
insert into stu_partiton partition(come_date=’2021-05-01’)
select id,sname,sex from stu where come_date = ‘2021-05-01’;
insert into stu_partiton partition(come_date=’2021-05-02’)
select id,sname,sex from stu where come_date = ‘2021-05-02’;
insert into stu_partiton partition(come_date=’2021-05-03’)
select id,sname,sex from stu where come_date = ‘2021-05-03’;
上面演示了用insert 插入到分区表，如果再插入分区 ‘201902’ 数据，需要再写一条 insert 语句。
而动态分区可以直接使用一条 insert 语句完成。
—- 动态分区 —-
# 非严格模式
set hive.exec.dynamic.partition.mode=nonstrict;先设置严格模式
insert into stu_partiton partition(come_date) select id,sname,sex from stu;

总结：静态分区(SP)列:在涉及多个分区列的 DML/DDL 中，这些列的值在编译时已知(由用户给出)。
动态分区(DP)列:在执行时才知道其值的列。

2.1.3 分桶表

对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。（桶有点类似mr中的分区）Hive也是针对某一列进行桶的组织。

Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
比如有三个桶，来的数据x就进行x%3，结果就为0，1，2中的一个，放入相应的桶。
查询的的时候比如select * from table where id=1 在分桶表中执行，分桶表就会把1做hash进行1%3，看看是那个桶的，然后去这个桶里面找。
基本可以这么说分区表是粗粒度的划分，桶在细粒度的划分。当数据量比较大，我们需要更快的完成
任务，多个map和reduce进程是唯一的选择。

但是如果输入文件是一个的话，map任务只能启动一个。此时bucket table是个很好的选择，通过指定CLUSTERED的字段，将文件通过hash打散成多个小文件。
- 把表（或者分区）组织成桶（Bucket）有两个理由：
  - 获得更高的查询处理效率。大表在JOIN的时候，效率低下。如果对两个表先分别按id分桶，那么相同id都会归入一个桶。那么此时再进行JOIN的时候是按照桶来JOIN的，那么大大减少了JOIN的数量。
  - 使取样（sampling）更高效。因为在处理大规模的数据集时，在开发、测试阶段将所有的数据全部处理一遍可能不太现实，这时抽样就必不可少。

注意：分桶之前要设置分桶开关以及reduce数量使其与分桶数量一致！
— 分桶表 —
create table table_name(
field_name1 type_name comment ‘’,
field_name2 type_name comment ‘’,
field_name3 type_name comment ‘’
)
clustered by field_name1 into 4 buckets
row format delimited
fields terminated by ‘\t’
lines terminated by ‘\n’
stored as textfile | orcfile
location hdfs_path

#drop table
drop table stu;
#alter table 增加一个字段
alter table stu add columns (addr string);

2.2 DML（Data Manipulation Language）数据库操作语言

插入数据
// 普通插入
insert into table values (),();
// 加载文件
load data inpath ‘hdfs://file_path’ [overwrite] into table table_name;
加了overwrite是覆盖原有的文件
// 插入数据
insert [overwrite] into table_name select fields … from table_name1;
#删除数据
truncate table table_name
#没有delete和update
# 多表插入
from table_name
insert into table_name1 select id,name,sex where come_date=’2021-05-01’
insert into table_name2 select id,name,sex,addr where come_date=’2021-05-11’
insert into table_name3 select id,name,sex where come_date=’2021-05-20’

2.3 DQL（Data Query Language）数据库查询语言

    Hive Sql执行顺序 | Mysql执行顺序<br />        (1)mysql语句执行顺序：<br />            from... where ...group by ... having .... select ... order by ... limit ...<br />        (2) hive语句执行顺序：<br />            from ... where ... select ... group by ... having ... order by ... limit ...

UNION 和 UNION ALL的区别?
UNION只会查询到两个表中不同的数据，相同的部分不会被查出
UNION ALL会把两个表的所有数据都查询出

2.4 字段类型

复合类型 : struct、map、array
1. struct — class
terminated 之间用分割.

create table stu_struct(id int, info struct<name:string, age:string>)
row format delimited
fields terminated by '\t'
collection items terminated by ','
stored as textfile;

insert into stu_struct values(1, named_struct('name','zs','sex','F'));
    select id, info.name, info.sex from stu_struct;

    load data inpath 'hdfs://' overwrite into table stu_struct;

2. array  -- [ ]

create table stu_arr(id int, hobbits array<string>)
row format delimited
fields terminated by '\t'
collection items terminated by ','
stored as textfile;

    insert into stu_arr values(1, array('basketball','football','pingpong'));
    select id, hobbits[0], size(hobbits) from stu_arr;

    load data inpath 'hdfs://' overwrite into table stu_arr;

3. map -- <k,v>

create table stu_map(id int, info map<string,int>)
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
stored as textfile;

    insert into stu_map values (1, map('yuwen',89,'shuxue',90));
    select id, info['yuwen'], info['shuxue'] from stu_map;

    load data inpath 'hdfs://' overwrite into table stu_map;

补充 :
select from table
cluster by … == distribute by … + sort by …
性能优于
select from table
order by …

3.Hive 函数

查看所有系统函数：show functions

函数分类：

3.1系统函数

字符串相关函数、日期相关函数、聚合函数、其他常用函数
split、concat、replace、concat_ws、concat_set、substr、length
date_diff、toDate、date_add、year、month、day、hour
case … when、if、coalesce、explode
sum、count、avg、max、min

3.2自定义函数 :

UDF - 1:1 用户定义（普通）函数，只对单行数值产生作用；
1. 继承UDF类
2. 编写evaluate()方法
UDAF - n:1 用户定义聚合函数，可对多行数据产生作用；等同与SQL中常用的SUM()，AVG()，也是聚合函数；
1. 继承UDAF类
2. 实现内部接口UDAFEvauator
3. 编写init、iterate、terminatePartial、merge、terminate
UDTF - 1:n 用户定义表生成函数，用来解决输入一行输出多行；

            1. explode + lateral view

3.2.1. UDF(user defined function)

· 背景
系统内置函数无法解决所有的实际业务问题，需要开发者自己编写函数实现自身的业务实现诉求。
应用场景非常多，面临的业务不同导致个性化实现很多，故udf很需要。
· 意义
函数扩展得到解决，极大丰富了可定制化的业务需求。
· IO要求-要解决的问题
in:out=1:1,只能输入一条记录当中的数据，同时返回一条处理结果。
属于最常见的自定义函数，像cos,sin,substring,instr等均是如此要求。
· 实现步骤(Java创建自定义UDF类)

自定义一个java类
继承UDF类
约定俗成的重写evaluate方法
打包类所在项目成一个all-in-one的jar包并上传到hive所在机器
在hive中执行add jar操作，将jar加载到classpath中。
在hive中创建模板函数，使得后边可以使用该函数名称调用实际的udf函数
hive sql中像调用系统函数一样使用udf函数

案例：
1. 新建Maven2. 添加hive依赖到pom

org.apache.hive
hive-cli
1.2.1

3. 开发udf
public class MaskUDF extends UDF {
// 方法名称必须是evaluate，返回值类型不能是void，函数可以重载
public String evaluate(String str, int n, String replace){
if(str == null || str.trim().length() == 0){
return null;
}

        if(str.length() >= n){<br />                return str.substring(0, n) + replace;<br />            }<br />            return str;<br />        }<br />    }

打包上传
mvn install
rz xxx.jar
hdfs dfs -put xxx.jar hdfs_path
5. 加载到hive(session级别)
add jar hdfs://cluster0.hadoop:8020/tmp/mask.jar;
6. 关联hive函数
create temporary function my_mask as ‘cn.com.hive.udf.MaskUDF’;
7. 使用
select my_mask(“hello world”, 4, “*“)

3.2.2. UDAF（user defined aggregation function）
· 自定义udaf函数self_count，实现系统udaf count的功能
in:out=n:1,即输入N条数据，返回一条处理结果，即列转行。
最常见的系统聚合函数，如count,sum,avg,max等

· 实现步骤

自定义一个java类
继承UDAF类
内部定义一个静态类，实现UDAFEvaluator接口
实现方法init,iterate,terminatePartial,merge,terminate共5个方法.

· 在hive中执行add jar操作，将jar加载到classpath中。
· 在hive中创建模板函数，使得后边可以使用该函数名称调用实际的udf函数
· hive sql中像调用系统函数一样使用udaf函数
· 代码实现
· 布署步骤 跟udf完全一致
· 加载jar包、声明函数、使用函数 跟udf完全一致
· 测试运行 与count一样，使用前边定义的临时udaf函数。

—- map —-1. new Evaluator() mr - mapper
2. init() | cnt = 0 mr - mapper - 初始化变量
3. iterate() mr - mapper - map()
4. terminatePartial() mr - mapper - combiner - output

—- reduce —-
1. new Evaluator() mr - reducer
2. init() | cnt = 0 mr - reducer - int cnt = 0
3. merge() mr - reducer - reduce()
4. terminate() mr - reducer - output

—- UDAF模板 —-
1. public Evaluator(){}
2. private int cnt;
3. public init(){}
4. public boolean iterate(){}
5. public int terminatePartial(){}
6. public boolean merge(){}
7. public int terminate(){}

3.2.3. UDTF(User-Defined Table-Generating Functions)

解决一行输入多行输出，即1:n，即行转列应用
往往被lateral view explode+udf等替代实现，比直接用udtf会更简单、直接、更灵活一些
本节由学员自学实现，如何用lateral view explode+udf替代udtf实现

4. 数据仓库分层和建模

数据仓库(Data Warehouse)是一个面向主题的(subject oriented)、集成的(integrated)、相对稳定的(non-volatile)、反应历史变化(time variant)的数据集合，用于支持管理决策(decision making support)。
数据仓库的主要工作就是ETL，即是英文 Extract-Transform-Load 的缩写，用来描述数据从来源端经过抽取(extract)、转换(transform)、装载(load)、至目的端的过程。
目的是将企业中的分散、零乱、标准不统一的数据整合到一起，为企业的决策提供分析的依据。

4.1 数据仓库架构

架构原则：先水平分层，再垂直分主题域。
数据架构分三层
源数据落地区(SDF:Source Data File)
数据仓库层(DW:Data WareHouse)
数据集市层(DM:Data Market)
数据仓库层进一步细分为三层
源数据层(DWB):存放原始数据
细节数据层(DWD)：对原始数据（ODS）进行清洗（去空，脏数据，超过极限的数据），维度退化，脱敏等。
汇总数据层(DWS)：数据汇总

提问：源数据落地层和源数据层有什么区别？
源数据落地层是文件，源数据层是表，属于Hive管理，是从源数据落地层把数据加载过来的。
Hive - 图6

4.2 数据仓库建模

数据仓库的建模方法有很多，每一种建模方法则代表哲学上的一个观点，代表了一种归纳，概括世界的一种方法。
目前的构建方法主要有三种：
范式建模法
维度建模法
实体建模法

4.2.1 范式建模

范式的定义：符合某一种级别的关系模式集合，表示一个关系内部各属性之间的联系的合理化程度。通俗地讲，范式可以理解为一张数据表的表结构，符合的标准级别、规范和要求。
1）优点
采用范式，可以降低数据的冗余性。

为什么要降低数据冗余性？
十几年前，磁盘很贵，为了减少磁盘存储。
以前没有分布式系统，都是单机，只能增加磁盘，磁盘个数也是有限的
一次修改，需要修改多个表，很难保证数据一致性

3）缺点
范式的缺点是获取数据时，需要通过Join拼接出最后的数据。
4）分类
目前业界范式有：第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)、第五范式(5NF)。
函数依赖：

完全函数依赖：共同决定，任何单独一个推测不出来。
部分函数依赖：只依赖于其中一个，一半
传递函数依赖： a->b->c(c不能得到a)

4.2.1.1 第一范式:属性不可切割

1NF核心原则：属性不可切割； 商品| 数量可切割

4.2.1.2 第二范式:完全函数依赖

2NF核心原则：不能存在部分函数依赖
联合主键(学号, 课名)，但姓名并不完全依赖于（学号，课名）；**变成完全函数依赖即可

4.2.1.3 第三范式：不能传递函数依赖

3NF不能存在传递函数依赖
学号->系名->系主任，但系主任不能推出学号；
把它拆开两张表

特别说明：
范式建模优点
从关系型数据库的角度出发，结合了业务系统的数据模型，能够比较方便的实现数据仓库的建模。
范式建模缺点
其建模方法限定在关系型数据库之上，在有些时候(需要冗余的时候)反而限制了整个数据仓库模型的灵活性，性能等，特别是考虑到数据仓库的底层数据向数据集市的数据进行汇总时，需要灵活调整才能达到要求。
使用建议：当不需要冗余设计提高易用性和计算效率时，可以采用这种模式。（常见的即为web项目开发中）

4.2.2 维度建模

4.2.2.1维度表

维度表：一般是对事实的描述信息。每一张维表对应现实世界中的一个对象或者概念。例如：用户、商品、日期、地区等。
维表的特征：

维表的范围很宽（具有多个属性、列比较多）
跟事实表相比，行数相对较小：通常< 10万条
内容相对固定：编码表

在维度建模的基础上又分为三种模型：星型模型、雪花模型、星座模型。

4.2.2.2 事实表

事实表中的每行数据代表一个业务事件（下单、支付、退款、评价等）。“事实”这个术语表示的是业务事件的度量值（可统计次数、个数、金额等）。
维度表：时间、用户、商品、商家。事实表：250块钱、一瓶
每一个事实表的行包括：具有可加性的数值型的度量值、与维表相连接的外键、通常具有两个和两个以上的外键、外键之间表示维表之间多对多的关系。
事实表的特征：

非常的大
内容相对的窄：列数较少（主要是外键id和度量值）
经常发生变化，每天会新增加很多。
4.2.2.3维度建模模型

1.星型模型：(快)
只有1层，数据表中只有1个维度表；星型模式的核心是一个大的中心表（事实表），一组小的附属表（维表）。
2.雪花模型：(灵活)
多级多个维度表，比较靠近3NF； 雪花模式是星型模式的扩展，其中某些维表被规范化，进一步分解到附加表（维表）中。
3.星座模型：（可能是雪花也可能是星型）
数据仓库由多个主题构成，包含多个事实表，而维表是公共的，可以共享，这种模式可以看做星型模式的汇集，因而称作星系模式或者事实星座模式。

关于维度建模法的总结说明：

广泛被使用的原因：
- 在于针对各个维作了大量的预处理，如按照维进行预先的统计、分类、排序等，能够极大的提升数据仓库的处理能力。
维度建模优点
- 由于其可以有必要合理的冗余和其它范式建模的严格限制，相对于针对3NF 的建模方法，星型模式在性能上占据明显的优势。
- 维度建模非常直观，紧紧围绕着业务模型，可以直观的反映出业务模型中的业务问题。不需要经过特别的抽象处理，即可以完成维度建模。
维度建模缺点
- 由于在构建星型模式之前需要进行大量的数据预处理，会带来大量的数据处理工作。
- 业务发生变化后，往往需要更新维度的预处理。
- 存储和处理过程中，数据冗余量较大
- 依靠维度建模的话，其维度必然会且维护成本增大，不能保证数据来源的一致性和准确性，而且在数据仓库的底层，不是特别适用于维度建模的方法。
使用建议：
- 在数据架构设计中的细节数据层、汇总数据层、数据集市层等需要提升计算性能的时候，均可以使用，也是建模过程中逻辑建模阶段最常用的方法之一。（常用于数据仓库模型设计）
  4.2.3 实体建模
  不做分析
  4.3 数据分析
  4.3.1 OLTP
全称：on-line transaction processing。中文意思：联机事务处理
- 其是数据库的主要应用，主要是执行基本日常的事务处理，如数据库记录的增删查改。
- 比如在支付定或银行的一笔交易记录，就是一个典型的事务。
主要特点
- 实时性要求高，操作完后立刻要能看到结果。
- 数据量不是很大，生产库上的数据量一般不会太大，而且会及时做相应的数据处理与转移。
- 交易一般是确定的，比如银行存取款的金额肯定是确定的，所以OLTP是对确定性的数据进行存取高并发，并且要求满足ACID原则。比如两人同时操作一个银行卡账户，比如大型的购物网站秒杀活动时上万的QPS请求。
总结
- 主要是指关系数据库中的增删查改，也是我们最常用操作，此为数据库的基础。
  4.3.2 OLAP
  · 全称：On-Line Analytical Processing，中文意思为：联机分析处理
  · 其是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。典型的应用就是复杂的动态的报表系统。
  · 主要特点
  实时性要求不是很高，比如最常见的应用就是天级更新数据，然后出对应的数据报表。
  数据量大，因为OLAP支持的是动态查询，所以用户也需要通过将很多数据的统计后才能得到想要知道的信息，例如时间序列分析等等，所以处理的数据量很大;
  n OLAP系统的重点是通过数据提供决策支持，所以查询一般都是动态，自定义的。所以在OLAP中，维度的概念特别重要。一般会将用户所有关心的维度数据，存入对应数据平台。
  · 总结
  其是数据仓库的核心部件。
  所谓数据仓库是对于大量已经由OLTP形成的数据的一种分析型的数据库，用于处理商业智能(BI)、决策支持等重要的决策信息。
  数据仓库是在数据库应用到一定程序之后而对历史数据的加工与分析，读取较多，更新较少。
  OLTP发展到一定阶段后产生的OLAP。

5.Hive 分析函数

1.count计数

count(*) 所有值不全为NULL时，加1操作
count(1) 不管有没有值，只要有这条记录，值就加1
count(col) col列里面的值为null，值不会加1，这个列里面的值不为NULL，才加1

2. SQL排名函数问题：
rank:1,1,3
row_number:123456**
dense_rank:1,1,2
参考：**https://www.cnblogs.com/huxinga/p/7688376.html

3.order by和sort by区别？
order by是全局排序，需要reduce操作，且只有一个reduce，与配置无关。数据量很大时，慎用。
sort by是组内排序，确保每个reduce上面输出的数据有序。如果只有一个reduce时，和order by作用一样。
4.distribute by与group by 的区别？
都是按key值划分数据都使用reduce操作
**唯一不同的是，distribute by只是单纯的分散数据，
distribute by col – 按照col列把数据分散到不同的reduce。
Sort排序 sort by col – 按照col列把数据排序
而group by把相同key的数据聚集到一起，后续必须是聚合操作。
distribute by sort by可以结合桶表使用，给桶中的数据排序。
```
select col1,col2 from M
distribute by col1
sort by col1 asc,col2 desc
```
从表中读取数据，执行where条件。设置reduce数为3，以distribute by列的值作为key,其他列值作为value，然后把数据根据key值传到不同的reduce，然后按sort by字段进行排序。
参考：https://www.cnblogs.com/huxinga/p/7688376.html
5. Hive join

inner join(内连接，只有进行连接的两个表中都存在与连接条件相匹配的数据才会被留下来)
Left join：inner join + a保留b为空
Right join:inner join+ a为空b保留
Full join:inner join+Left join+Right join

LEFT，RIGHT和FULL OUTER关键字用于处理join中空记录的情况

6.Hive 行转列/列转行

https://www.cnblogs.com/blogyuhan/p/9274784.html

https://blog.csdn.net/u010003835/article/details/106604698?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522162402223616780274118567%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=162402223616780274118567&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduend~default-2-106604698.first_rank_v2_pc_rank_v29&utm_term=hive%E8%A1%8C%E4%B8%93%E5%88%97%E5%88%97%E8%BD%AC%E8%A1%8C&spm=1018.2226.3001.4187

7.sql执行顺序

    Hive Sql执行顺序 | Mysql执行顺序<br />        (1)mysql语句执行顺序：<br />            from... where ...group by ... having .... select ... order by ... limit ...<br />        (2) hive语句执行顺序：<br />            from ... where ... select ... group by ... having ... order by ... limit ...

6. Hive Sql优化

https://www.cnblogs.com/smartloli/p/4356660.html
1. 列裁剪
尽量需要哪些字段就select哪些字段，不用写select
2.分区裁剪：
在查询的过程中减少不必要的分区，即尽量指定分区
3. 谓词前置（ join前过滤掉不必要的数据）
实例 : select a., b. from a join b on a.col1 = b.col1 where a.col1 > 20and b.col2 > 40
优化 :
select aa., bb. from
(select from a where a.col1 > 20) aa inner join
(select from b where b.col2 > 40) bb on aa.col1 = bb.col1
4. map join
map join 在map端把小表加载到内存中，然后读取大表，和内存中的小表完成连接操作。其中使用了分布式缓存技术。
map join的使用场景： 1、关联操作中有一张表非常小 2、不等值的链接操作
优点不消耗集群的reduce资源（reduce相对紧缺）。减少了reduce操作，加快程序执行。降低网络负载。
*缺点占用部分内存，所以加载到内存中的表不能过大，因为每个计算节点都会加载一次。生成较多的小文件。

https://www.cnblogs.com/MOBIN/p/5702580.html

5. 数据倾斜优化器
例如 aaa 这个 key,map 时随机在 aaa 后面加上 1,2,3,4 这四个数字之一，把 key 先分成四组，先进行一次运算，之后再恢复 key 进行最终运算。
在map端产生的倾斜万能贴解决不了如128m 128m 30g不可切分
由于hash不均衡导致的reduce端倾斜可以解决
倾斜的原理是很多相同的key用同一个reduce处理，导致处理的任务过大
解决方法：
set hive.groupby.skewindata=true
set hive.skewjoin.key=100000;

利用hive的优化机制减少job数
尽可能在多表关联的时候，使用相同的key进行关联，减少job数
尽量多用批量插入multi-insert 而不是写两个insert
from a
insert overwrite table tmp1
select … where 条件1
insert overwrite table tmp2
select … where 条件2
8. 善于使用union all
多个表的数据合并成一个表，hive不支持union
union all必须满足如下要求 字段名字一样字段类型一样字段个数一样子表不能有别名如果需要从合并之后的表中查询数据，那么合并的表必须要有别名
不同表的union all相当于multi inputs，同一表的union all相当于map一次输出多条
避免笛卡尔积：关联的时候一定要写关联条件

10. left semi join代替in
select a.key,a.value from a left semi join b on a.key=b.key
注意: select只能有a表字段

11. 使用动态分区
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

12. 尽量避免使用distinct
尽量使用group by代替distince去重
select distinct key from a
select key from a group by key

13. 排序优化
order by : 实现全局排序，一个reduce实现，由于不能并发执行，所以效率低
sort by : 实现部分有序，单个reduce的输出结果是有序的，效率高
通常sort by与distribute by一起使用 : distribute by key1 sort by key2
cluster by key1 == distribute by key1 sort by key1

使用explain dependency查看sql实际扫描多少分区、使用explain查看sql执行计划

15. 分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
连接两个在（包含连接列）相同列上划分了桶的表，可以使用Map端连接（Map side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了捅操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大减少JOIN的数据量。对于map端连接的情况，两个表以相同方式划分桶。处理左边表内某个桶的mapper知道右边表内相匹配的行在对应的桶内。因此，mapper只需要获取那个桶（这只是右边表内存储数据的·小部分）即可进行连接。这一优化方法并不一定要求两个表必须桶的个数相同，两个表的桶个数是倍数关系也可以。

16. group by优化
set hive.group.skewindata=true; 如果是group by过程出现倾斜，应该设置为true
set hive.groupby.mapaggr.checkinterval=100000; 这个是group的键对应的记录条数超过这个值则会进行优化
17. count distinct优化
—优化前1
select count(distinct id) from tablename;
—优化后1
select count(1) from (select distinct id from tablename) tmp;
select count(1) from (select id from tablename group by id) tmp;
—优化前2
select a, sum(b), count(distinct c), count(distinct d) from test group by a;
—优化后2
select a, sum(b) as b, count(c) as c, count(d) as d
from(
select a,0 as b, c, null as d from test group by a,c
union all
select a,0 as b, null as c, d from test group by a, d
union all
select a,b,null as c, null as d from test
)tmp1 group by a;
18. 并行化执行
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=8;
19. job合并输入小文件
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat
合并文件数由mapred.max.split.size限制的大小决定
20. job合并输出小文件
set hive.merge.smallfiles.avgsize=256000000;当输出文件平均大小小于该值，启动新job合并文件
set hive.merge.size.per.task=64000000;合并之后的文件大小
21. JVM重利用
set mapred.job.reuse.jvm.num.tasks=20;
22. 压缩数据
set hive.exec.compress.intermediate=true;
set hive.intermediate.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;
set hive.intermediate.compression.type=BLOCK;
—- hive查询最终的输出也可以压缩 —-
set hive.exec.compress.output=true;
set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec;
set mapred.output.compression.type=BLOCK;
23. Hive Map端优化
blocksize | file num | file size | file type
23.1 map端聚合
set hive.map.aggr=true; // combiner
23.2 推测执行
mapred.map.tasks.speculative.execution
24.
join 操作中，使用 map join 在 map 端就先进行 join ，免得到reduce 时卡住。

总结：
1. 减少输入数据量
减少读取的列数、减少读取的分区、谓语前置 …
2. 减少数据倾斜
根源 : 减少key分配到reduce不均匀
3. 增加并行度
4. …..

技巧：
1. 临时表
create temporary table tt1 as select from t1 where name = ‘1’
create temporary table tt2 as select from t2 where name = ‘2’
select from tt1 join tt2 on tt1.id = tt2.id
优化：
with t1 as (select from t1 where name = ‘1’),
t2 as (select from t2 where name = ‘2’)
select from t1 join t2 on t1.id = t2.id

2. 增强group - with cube | with rollup | grouping sets ()

3. 行转列
lateral view + explode()
explode作用：处理map结构的字段，将数组转换成多行
https://blog.csdn.net/panfelix/article/details/107146795?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522162486793116780269823255%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=162486793116780269823255&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~first_rank_v2~rank_v29-3-107146795.first_rank_v2_pc_rank_v29&utm_term=lateral+view+%2B+explode%28%29&spm=1018.2226.3001.4187
4. 列转行
concat_ws | collect_set

5. 窗口函数
sum() | count() | max() | min() | avg() + over(partition by fields order by fields)
lag(field, n) | lead(field, n) + over(partition by fields order by fields)
ntile(n) + over(partition by fields order by fields)
last_value | first_value

7. Hive数据倾斜问题

https://blog.csdn.net/u010670689/article/details/42920917

7.1 问题

如何将数据均匀的分配到各个reduce中，就是解决数据倾斜的根本所在

7.2 操作

join            一个表较小, key集中<br />    join            大表与大表, 但是分桶判断字段0或null过多<br />    group by        group by 维度过小, 某值数量过多<br />    count distinct    特殊值过多

7.3 原因

1. key分布不均匀<br />    2. 业务数据存在特性 -- null过多<br />    3. 建表不周全<br />4.某些sql语句本身就存在数据倾斜x

简单来说数据倾斜就是数据的key 的分化严重不均，造成一部分数据很多，一部分数据很少的局面。
举个 word count 的入门例子，它的map 阶段就是形成（“aaa”,1）的形式，然后在reduce 阶段进行 value 相加，得出 “aaa” 出现的次数。若进行 word count 的文本有100G，其中 80G 全部是 “aaa” 剩下 20G 是其余单词，那就会形成 80G 的数据量交给一个 reduce 进行相加，其余 20G 根据 key 不同分散到不同 reduce 进行相加的情况。如此就造成了数据倾斜，临床反应就是 reduce 跑到 99%然后一直在原地等着那80G 的reduce 跑完。
简化了的 shuffle 图就是这样。
这样就能清楚看到，数据经过 map后，由于不同key 的数据量分布不均，在shuffle 阶段中通过 partition 将相同的 key 的数据打上发往同一个 reducer 的标记，然后开始 spill （溢写）写入磁盘，最后merge成最终map阶段输出文件。
如此一来 80G 的 aaa 将发往同一个 reducer ，由此就可以知道 reduce 最后 1% 的工作在等什么了。

7.4 表现

任务进度长时间维持在99%(或100%)，查看任务监控页面，发现只有少量(1个或几个)reduce子任务未完成。<br />    因为其处理的数据量和其他reduce差异过大。<br />    单一reduce的记录数与平均记录数差异过大，通常可能达到3倍甚至更多。 <br />    最长时长远大于平均时长。

7.5 解决方案

map端聚合
hive.map.aggr = true 在map中会做部分聚集操作，相当于Combiner，效率更高但需要更多的内存。
2. 数据倾斜的时候进行负载均衡
hive.groupby.skewindata=true 数据倾斜时负载均衡

详解：当选项设定为true，生成的查询计划会有两个MRJob。第一个MRJob 中，经过哈希处理，Map的输出结果集合会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的GroupBy Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；第二个MRJob再根据预处理的数据结果按照GroupBy Key分布到Reduce中（这个过程可以保证相同的GroupBy Key被分布到同一个Reduce中），最后完成最终的聚合操作。
上述两个方案使用频繁。起到至关重要的作用的其实是第二个参数的设置，它使计算变成了两个mapreduce，先在第一个中在 shuffle 过程 partition 时随机给 key 打标记，使每个key 随机均匀分布到各个 reduce 上计算，但是这样只能完成部分计算，因为相同key没有分配到相同reduce上，所以需要第二次的mapreduce,这次就回归正常 shuffle,但是数据分布不均匀的问题在第一次mapreduce已经有了很大的改善，因此基本解决数据倾斜。

原文链接：https://blog.csdn.net/chyeers/article/details/78320778
3. 如何join
关于驱动表的选取，选用join key分布最均匀的表作为驱动表
列裁剪和filter操作，以达到两表做join的时候，数据量相对变小的效果
4. 大小join
使用map join让小的维度表(1000条以下的记录条数)先进内存
5. 大表join大表
把空值的key变成一个字符串加上随机数，把倾斜的数据分到不同的reduce上，由于null值关联不上，处理后并不影响最终结果。
6. count distinct大量相同特殊值
count distinct时，将值为空的情况单独处理，如果是计算count distinct，可以不用处理，直接过滤，在最后
结果中加1。如果还有其他计算，需要进行group by，可以先将值为空的记录单独处理，再和其他计算结果进
行union。
7. group by维度过小
采用sum() + group by的方式来替换count(distinct)完成计算
8. 特殊情况特殊处理
在业务逻辑优化效果的不大情况下，有些时候是可以将倾斜的数据单独拿出来处理。最后union回去
9. 空值产生的数据倾斜
解决方法1： user_id为空的不参与关联
select from log a join users b on a.user_id is not null and a.user_id = b.user_id
union all
select from log a where a.user_id is null;
解决方法2 ：赋与空值分新的key值
select from log a left outer join users b
on case when a.user_id is null then concat(‘hive’,rand() ) else a.user_id end = b.user_id;
10. 不同数据类型关联产生数据倾斜
解决方法 : select from users a left outer join logs b on a.usr_id = cast(b.user_id as string)
11. 小表不小不大，怎么用map join解决倾斜问题
select from log a left outer join users b on a.user_id = b.user_id;
解决方法 : select from log a left outer join (
select c., d. from ( select distinct user_id from log ) c
join users d
on c.user_id = d.user_id
) x
on a.user_id = b.user_id;

8.Hive参数

参考文档05 Hive参数深入浅出
05. Hive参数深入浅出.docx

Hive

1.Hive原理介绍

1.1Hive介绍

1.2Hive原理

1.2.1 分层

1.2.2 工作原理

1.2.3 Hive的用户接口

1.2.4 Hive创建表和处理数据的操作

1.3 Hive安装

2.Hive Sql