笔触狂放9

这个屌丝很懒，什么也没留下！

热门标签

Hive保姆级教程(万字长文，建议收藏)_hive搭建保姆

作者：笔触狂放9 | 2024-04-09 03:16:02

踩

hive搭建保姆

Apache Hive

1 Hive 的介绍
2 Hive 的设计原理
3 Hive 的架构图
4 Hive 的手动安装部署与配置
5 Hive 和 HDFS 之间的存储关系
6 Hive 数据库元数据表信息
7 Hive 中的 DDL 和 DML
- 7.1 创建库
- 7.2 删除库
- 7.3 创建表
- 7.4 加载数据
- 7.5 向表中写入其他表的内容
- 7.6 把数据从 Hive 中写入到本地文件系统
8 Hive 中表的分类
- 8.1 查看表的类型
- 8.2 管理表
- 8.3 外部表
- 8.4 管理表和外部表的区别
- 8.5 分区表
- - 8.5.1 手动分区
  - 8.5.2 自动分区
- 8.6 分桶表
9 Hive 中的特殊 JOIN
- 9.1 Map JOIN
- - 9.1.1 Map JOIN 方式一
  - 9.1.2 Map JOIN 方式二
- 9.2 SMB JOIN
10 Hive 中的排序
11 Hive 中的 SQL 脚本
- 11.1 示例
12 Hive 中的特殊数据类型
- 12.1 ARRAY
- 12.2 MAP
13 Hive 中使用正则表达式
14 Hive 的函数
- 14.1 Hive 的函数分类
- 14.2 Hive 的字符串函数
- 14.3 Hive的日期函数
- 14.4 lateral view 侧视图
- 14.5 explode函数
- 14.6 特殊函数
- 14.7 窗口函数和分析函数
- 14.8 CASE WHEN 用法
- - 14.8.1 第一种用法
  - 14.8.2 第二种用法
- 14.9 行列转换
15 Hive 的调优
- 15.1 fetch task
- 15.2 严格模式
- 15.3 本地模式
- 15.4 JVM重用
- 15.5 推测执行
- 15.6 压缩
- 15.7 Map端聚合
- 15.8 并行计算
- 15.9 SQL 优化
- 15.10 数据存储格式
- 15.11 数据倾斜
- - 15.11.1 Mapreduce 的解决方案
  - 15.11.2 Hive 的解决方案
16 数仓设计
- 16.1 数据仓库
- 16.2 分层设计
- 16.3 建模设计
17 Hive 的 maven 相关依赖

1 Hive 的介绍

Hive 是 Facebook 公司设计的一款专门为数据分析师提供的使用 SQL 可以操作 Hadoop 的基于 Hadoop 之上的数据中间件，相当于是 Hadoop 的一个客户端。Hive 有两大功能，存储和计算，存储是将表映射成 HDFS 上的文件，计算是将 SQL 语句转换成 Mapreduce 代码运行在 YARN 集群上，在当前的大数据环境下， Mapreduce 运行的太慢了，所以当前 HIVE 的主要应用场景是数据仓库的构建。

2 Hive 的设计原理

将 HDFS 文件通过 metadata 元数据映射成表的数据，将 SQL 转换为 Mapreduce 程序来操作 Hadoop 。

3 Hive 的架构图

Hive的架构图

客户端 ：负责与用户交互，让用户开发 SQL，并返回结果显示给用户。
服务端 ：
- 连接器 ：管理客户端访问的连接。
- 解析器 ：通过访问 Hive 的元数据对 SQL 的词法、语法进行校验，例如检查库、表、字段是否都存在。
- 逻辑计划 ：将 SQL 语句中的参数进行解析，赋值给底层的 Mapreduce 模板。
- 优化器 ：根据 Mapreduce 的使用方式，自动选择最优的方式来进行实现。
- 物理计划 ：形成一个完整的 Mapreduce 程序。
- 执行器 ：将 Mapreduce 程序提交给 YARN 集群运行。
元数据
- 负责存储表与文件之间的映射关系。
- 负责存储所有表的信息，数据库、字段、分隔符等。
- MetaStore ：元数据管理服务，负责 Hive 和数据库进行交互。

4 Hive 的手动安装部署与配置

  ##解压
  tar -zxvf hive-1.1.0-cdh5.14.0.tar.gz -C /usr/local
  ##创建软连接
  ln -s /usr/local/hive-1.1.0-cdh5.14.0 /usr/local/hive
  ##配置系统环境变量
  vim /etc/profile
  ##HIVE HOME
  export HIVE_HOME=/usr/local/hive
  export PATH=$PATH:$HIVE_HOME/bin
  ##刷新环境变量
  source /etc/profile
  ##修改环境变量
  cd /usr/local/hive/conf
  vim hive-env.sh
  export HADOOP_HOME=/usr/local/hadoop-x.x.x-cdhx.x.x
  export HIVE_CONF_DIR=/usr/local/hive/conf
  ##配置元数据存储在关系型数据库中
  vim hive-site.xml
  <!--配置将元数据存储在MySQL中,如果是其他数据库的话，配置对应的jdbc连接和Driver驱动-->
  	<property>
  		<name>javax.jdo.option.ConnectionURL</name>
  		<value>jdbc:mysql://v6:3306/hive?createDatabaseIfNotExist=true</value>
  	</property>
  	<property>
  		<name>javax.jdo.option.ConnectionDriverName</name>
  		<value>com.mysql.jdbc.Driver</value>
  	</property>
  	<property>
  		<name>javax.jdo.option.ConnectionUserName</name>
  		<value>root</value>
  	</property>
  	<property>
  		<name>javax.jdo.option.ConnectionPassword</name>
  		<value>123456</value>
  	</property>
  	<!--配置统一的元数据管理服务MetaStore-->
  	<property>
  		<name>hive.metastore.uris</name>
  		<value>thrift://v3:9083</value>
  	</property>
  	<!--显示当前的数据库名称-->
  	<property>
  		<name>hive.cli.print.current.db</name>
  		<value>true</value>
      </property>
  	<!--显示SQL结果的每一列的名称-->
  	<property>
  		<name>hive.cli.print.header</name>
  		<value>true</value>
  	</property>
  ##并且添加对应的驱动包到HIVE的lib目录下
  mv mysql-connector-java-5.1.38.jar /usr/local/hive/lib
  ##修改Hive的日志存储位置
  mkdir /usr/local/hive/logs
  cd /usr/local/hive/conf
  mv hive-log4j.properties.template hive-log4j.properties
  vim hive-log4j.properties
  
  hive.log.threshold=ALL
  #指定日志级别
  hive.root.logger=INFO,DRFA  
  #指定日志存储位置
  hive.log.dir=/usr/local/hive/logs
  hive.log.file=hive.log
  
  ##启动HIVE
  ##1. 先启动Hadoop的HDFS和YARN
  ##2.在Hadoop中创建HIVE的目录
  hdfs dfs -mkdir /tmp
  hdfs dfs -chmod g+w /tmp
  hdfs dfs -mkdir -p /user/hive/warehouse
  hdfs dfs -chmod g+w /user/hive/warehouse
  
  ##在HIVE2.1版本及以上在第一次启动HIVE的时候还要初始化元数据
  cd /usr/local/hive/bin
  ./schematool -dbType mysql -initSchema
  
  ##3.必须先启动MetaStore服务
  cd /usr/local/hive/bin
  ./hive --service metastore &
  
  #检测是否真正启动成功
  netstat -atunlp | grep 9083
  
  ##启动HIVE的服务端
  ./hiveserver2 &
  
  #检测是否真正启动成功
  netstat -atunlp | grep 10000
  
  ##最后就可以启动HIVE的客户端了通过beeline
  ./beeline -u jdbc:hive2://v5:10000 -n root -p 123456
  
  #退出客户端
  !quit
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95

5 Hive 和 HDFS 之间的存储关系

Hive 的数据在 HDFS 上的位置：hive.metastore.warehouse.dir=/user/hive/warehouse，Hive 中创建的所有数据库都会在该目录下创建对应一个目录，自动以 dbname.db 命名。这个目录也作为 default 数据库的目录。Hive 中的所有数据库，都会对应一个 HDFS 的目录。Hive 中所有的表，都会默认对应一个 HDFS 的目录，目录在数据库目录的下面。Hive 表中的数据，默认在表的目录下面。

6 Hive 数据库元数据表信息

DBS ：记录了 Hive 中所有数据库的信息。
TBLS ：记录了 Hive 中所有表的信息。
SDS ：记录了所有表与 HDFS 的映射关系。

7 Hive 中的 DDL 和 DML

7.1 创建库

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT '库详细信息']
[LOCATION HDFS_PATH];
-- LOCATION：所有 Hive 中的数据库默认会有一个 HDFS 对应的目录，如果不存在会自动在 /user/hive/warehouse 目录下创建，该选项可以自定义，手动指定某个 HDFS 目录作为数据库目录。
1
2
3
4

7.2 删除库

DROP DATABASE [IF EXISTS] database_name [CASCADE];
-- CASCADE：强制删除，不加CASCADE只能删除空数据库。
1
2

7.3 创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] TAB_NAME(
   COLUMN1 TYPE1,
   COLUMN2 TYPE2
      ...
)
[PARTITIONED BY COLUMN] -- 按照那个字段进行分区
[CLUSTERED BY COLUMN INTO N BUCKETS ] -- 按照那个字段进行分桶
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' -- 指定列分隔符 默认为\001
ROW FORMAT DELIMITED LINES TERMINATED BY '\n' -- 指定行的分隔符 默认为\n
[STORED AS FILE_FORMAT] -- 指定表中数据文件的类型，默认为textfile，可选orc/parquet等
[LOCATION HDFS_PATH] -- 手动指定表所在的HDFS目录
-- 注意：Hive中的SQL语句不能包含制表符
-- EXTERNAL : 声明外部表的关键字
  
-- 将SQL语句的结果保存为一张不存在的表
CREATE TABLE [IF NOT EXISTS] TB_NAME AS SELECT ...
-- 复制一个已存在的表的表结构，不复制数据，构建一张新的表
CREATE TABLE [IF NOT EXISTS] TB_NAME LIKE EXISTS_TB_NAME;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

7.4 加载数据

LOAD DATA [LOCAL] INPATH '' [OVERWRITE] INTO TABLE TB_NAME;
-- LOCAL:表示加载本地数据，原理实际上是将本地文件上传到了表的目录下。
-- 不加LOCAL：表示加载HDFS数据，原理实际上将HDFS上的文件移动到表的目录下。
-- OVERWRITE：会覆盖表中已存在的数据。
1
2
3
4

7.5 向表中写入其他表的内容

-- 方式一
INSERT OVERWRITE/INTO TABLE TB_NAME SELECT ...;
-- OVERWRITE：插入并覆盖
-- INTO：追加
1
2
3
4

7.6 把数据从 Hive 中写入到本地文件系统

-- 方式二
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 [ROW FORMAT] SELECT ... FROM ...
-- LOCAL：表示写入本地文件系统，不加LOCAL表示写入HDFS文件系统。
-- ROW FORMAT
   -- row format delimited fields terminated by '\001' -- 列分隔符
   -- row format delimited lines terminated by '\n' -- 行分隔符
1
2
3
4
5
6

8 Hive 中表的分类

8.1 查看表的类型

  desc formatted tb_name;
1

8.2 管理表

默认的表的创建类型。

8.3 外部表

在创建表的时候可以通过 EXTERNAL 关键字来创建一张外部表。

8.4 管理表和外部表的区别

1. 管理表在删除数据的时候表结构元数据以及 HDFS 的表目录下面的数据都被删除了。
2. 外部表在删除数据的时候只删除了表结构以及当前表对应的元数据，表的目录以及数据都还存在。

8.5 分区表

在 Hive 中，由于数据是增量的，如果把所有的数据存储在一个文件中，不利于数据的检索查询，于是出现了分区的概念，分区有两种实现方式，分区后每个分区表对应一个目录，表的最后一级目录是分区目录。分区表主要是为了优化底层 Mapreduce 的输入。

8.5.1 手动分区

原始数据就是分区存在的，按照时间或者数据的特征。

-- 语法，一级分区
CREATE TABLE TB_NAME(
  COLUMN1 STRING,
  COLUMN2 STRING,
  COLUMN3 STRING,
  ....
)
PARTITIONED BY (COLUMN4 TYPE)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

-- 向分区表中导入数据
LOAD DATA [LOCAL] INPATH '' INTO TABLE TB_NAME PARTITION(COLUMN4 = '...');

-- 语法，多级分区
CREATE TABLE TB_NAME(
  COLUMN1 STRING,
  COLUMN2 STRING,
  COLUMN3 STRING,
  ....
)
PARTITIONED BY (COLUMN4 TYPE,COLUMN5 TYPE)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\n';
-- 向分区表中导入数据
LOAD DATA [LOCAL] INPATH '' INTO TABLE TB_NAME PARTITION (COLUMN4='...',COLUMN5 = '...');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

注意：手动分区的分区字段不存在于数据文件中，但是在创建分区表后，表的字段个数算上分区字段。

8.5.2 自动分区

原始数据不是分区存在的，这样可以在导入数据的时候进行分区，写入分区表中。

-- 语法 示例
-- 开启自动分区的参数
SET hive.exec.dynamic.partition.mode = nonstrict;
CREATE TABLE TB_NAME(
  COLUMN1 STRING,
  COLUMN2 STRING,
  COLUMN3 STRING,
  ....
)
PARTITIONED BY (COLUMN4 TYPE)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
-- 导入数据
INSERT INTO TABLE TB_NAME PARTITION(COLUMN4) SELECT *,COLUMN4 FROM TB_NAME;
1
2
3
4
5
6
7
8
9
10
11
12
13

注意：自动分区的分区字段存在于源数据中，并且分区字段要是最后一个字段。

8.6 分桶表

在 Hive 中，两张表进行关联查询的时候，如果两张表的数据量都非常大，在这两个表进行 Join 的时候，会产生笛卡尔积，在 Hive 中是严禁产生笛卡尔积的 Join 的，分桶主要应用场景是两个大表进行 Join 去笛卡尔积的SMB Join。Hive中的分桶就是 Mapreduce 的分区，分桶的规则就是 Hash 取余规则。

-- 分桶的操作示例
CREATE TABLE TB_NAME1(
  COLUMN1 TYPE1,
  COLUMN2 TYPE2,
  COLUMN3 TYPE3
  ...
)
CLUSTERED BY (CLOUMN3) INTO 3 BUCKETS
-- 给分桶表中添加数据  
-- CLOUMN4这个字段存在于TB_NAME2表中，并且和TB_NAME1的CLOUMN3字段内容相同
INSERT OVERWRITE/INTO TABLE TB_NAME1 SELECT * FROM TB_NAME2 CLUSTER BY (CLOUMN4);
1
2
3
4
5
6
7
8
9
10
11

9 Hive 中的特殊 JOIN

9.1 Map JOIN

Hive中小表JOIN大表用到的JOIN ，小表的大小阈值为128M，可以认为这是一张小表，配置的参数为：

set hive.mapjoin.smalltable.filesize = 25123456;
1

Hive 中程序走 Map JOIN 有两种方式：

9.1.1 Map JOIN 方式一

在 Hive 中可以配置参数，Hive 会自动判断是否走 Map JOIN ,参数为：

set hive.auto.convert.join = true; -- 默认为true
1

9.1.2 Map JOIN 方式二

可以强制程序走 Map JOIN，示例如下

SELECT /*Map(A)*/ * FROM TB_NAME A LEFT JOIN TB_NAME2 B ON A.COLUMN = B.COLUMN
1

9.2 SMB JOIN

是两张桶表之间的 JOIN ，并且这两张桶表的桶个数要为整数倍，在 Hive 中如果表的数据量很大并且和其他表还有关联关系，建议把表设置为桶表。
设置相关的参数:

set hive.auto.convert.sortmerge.join = true;
set hive.optimize.bucketmapjoin = true;
set hive.optimize.bucketmapjoin.sortedmerge = true;
1
2
3

10 Hive 中的排序

ORDER BY ：全局排序：只能用于 reduce 只有一个的排序。
SORT BY ：局部排序，每个 reduce 内部的排序。
DISTRIBUTE BY ：类似于分桶表，指定 SQL 转换为 Mapreduce 后按照那个字段进行分区，也就是表的分区字段，一般和 SORT BY 配合使用。
CLUSTER BY ：相当于 SORT BY + DISTRIBUTE BY，如果 SORT BY 和 DISTRIBUTE BY 指定的字段是同一个字段，则可以使用 CLUSTER BY 代替，但是排序只能是倒序。

reduce 相关的参数设置

set hive.exec.reducers.bytes.per.reducer = ：设置每个reduce最多处理多少数据
set hive.exec.reducers.max = :设置reduce的最大个数
set mapreduce.job.reduces = :设置reduceTask的数量
1
2
3

11 Hive 中的 SQL 脚本

cd /usr/local/hive/bin
./hive 
-d/--hivevar/--hiveconf:# 用于自定义一个Hive的变量
--database:# 指定访问的数据库名称
-e:# 执行一条SQL语句
-f:# 执行一个SQL文件
-S:# 显示比较少的日志
-v:# 显示详细的日志
1
2
3
4
5
6
7
8

11.1 示例

./hive --database db_name -e "SELECT * FROM TB_NAME"
./hive --database db_name -S -d tb_name = $tbname -f /root/hive.sql  
SELECT * FROM ${hiveconf:tb_name}
1
2
3

12 Hive 中的特殊数据类型

12.1 ARRAY

-- 示例
CREATE TABLE TB_NAME(
  COLUMN1 TYPE,
  COLUMN2 array<String>
  ...
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' -- 列分隔符
COLLECTION ITEMS TERMINATED BY ',';  -- 集合每个元素之间的分隔符
-- 数据样式 1	1,2,3,4,5,6
LOAD DATA [LOCAL] INPATH '文件位置' INTO TABLE TB_NAME;

-- 查询方式
SELECT SIZE(COLUMN2) AS NUMB,COLUMN FROM TB_NAME;
SELECT COLUMN ,COLUMN2[0],COLUMN2[1] FROM TB_NAME;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

12.2 MAP

-- 示例
CREATE TABLE TB_NAME(
  COLUMN1 TYPE,
  COLUMN2 map<string,string>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY '-' MAP KEYS TERMINATED BY ':';
-- COLLECTION ITEMS TERMINATED BY '-'  KEY-VALUE对之间的分隔符
-- MAP KEYS TERMINATED BY ':'  KEY和VALUE之间的分隔符
-- 数据样式 1	唱歌：非常喜欢-跳舞:一般-游泳:不喜欢
LOAD DATA [LOCAL] INPATH '文件位置' INTO TABLE TB_NAME;

-- 查询方式
SELECT COLUMN1 ,SIZE(COLUMN2) AS NUMB FROM TB_NAME
SELECT COLUMN1,COLUMN2["唱歌"] AS TEMP FROM TB_NAME;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

13 Hive 中使用正则表达式

对于列的分隔符不固定的数据，在数据加载到 Hive 表的时候可以使用正则校验，示例

CREATE TABLE TB_NAME(
  COLUMN TYPE,
  COLUMN1 TYPE
  ...
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES(
"input.regex" = "(...) (...)"
)
-- ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' 通过正则匹配每一个字段
-- input.regex 指定每个字段的正则表达式
1
2
3
4
5
6
7
8
9
10
11

14 Hive 的函数

show functions;-- 查看所有的内置函数
desc function extended func_name; -- 查看某个内置函数的用法
1
2

14.1 Hive 的函数分类

14.1.1 UDF

普通函数，一对一的函数，一进一出。

实现一个自己创建的UDF函数步骤:

开发一个类并继承 GenericUDF 这个类
重写这个抽象类的三个方法
在该方法中实现对应的逻辑，在实现对应逻辑的过程中对于数据类型尽量使用 Hadoop 的序列化类型。

public class GenericUDFArray extends GenericUDF {
  private transient Converter[] converters;
  private transient ArrayList<Object> ret = new ArrayList<Object>();

  @Override
  public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {

    GenericUDFUtils.ReturnObjectInspectorResolver returnOIResolver = new GenericUDFUtils.ReturnObjectInspectorResolver(true);

    for (int i = 0; i < arguments.length; i++) {
      if (!returnOIResolver.update(arguments[i])) {
        throw new UDFArgumentTypeException(i, "Argument type \""
            + arguments[i].getTypeName()
            + "\" is different from preceding arguments. "
            + "Previous type was \"" + arguments[i - 1].getTypeName() + "\"");
      }
    }

    converters = new Converter[arguments.length];

    ObjectInspector returnOI =
        returnOIResolver.get(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
    for (int i = 0; i < arguments.length; i++) {
      converters[i] = ObjectInspectorConverters.getConverter(arguments[i],
          returnOI);
    }

    return ObjectInspectorFactory.getStandardListObjectInspector(returnOI);
  }

  @Override
  public Object evaluate(DeferredObject[] arguments) throws HiveException {
    ret.clear();
    for (int i = 0; i < arguments.length; i++) {
      ret.add(converters[i].convert(arguments[i].get()));
    }
    return ret;

  }

  @Override
  public String getDisplayString(String[] children) {
    return getStandardDisplayString("array", children, ",");
  }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

14.1.2 UDAF

聚合函数，多进一出。

实现一个自己创建的 UDAF 函数步骤

开发一个类，继承 AbstractGenericUDAFResolver
重写方法 getEvaluator

@Description(name = "collect_set", value = "_FUNC_(x) - Returns a set of objects with duplicate elements eliminated")
public class GenericUDAFCollectSet extends AbstractGenericUDAFResolver {

  static final Log LOG = LogFactory.getLog(GenericUDAFCollectSet.class.getName());

  public GenericUDAFCollectSet() {
  }

  @Override
  public GenericUDAFEvaluator getEvaluator(TypeInfo[] parameters)
      throws SemanticException {
    if (parameters.length != 1) {
      throw new UDFArgumentTypeException(parameters.length - 1,
          "Exactly one argument is expected.");
    }
    switch (parameters[0].getCategory()) {
      case PRIMITIVE:
      case STRUCT:
      case MAP:
      case LIST:
        break;
      default:
        throw new UDFArgumentTypeException(0,
            "Only primitive, struct, list or map type arguments are accepted but "
                + parameters[0].getTypeName() + " was passed as parameter 1.");
    }
    return new GenericUDAFMkCollectionEvaluator(BufferType.SET);
  }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

14.1.3 UDTF

表生成函数，一进多出。

实现一个自己创建的 UDTF 函数步骤

定义一个类继承 GenericUDTF 类
实现如下方法
1. initialize：初始化的方法
2. process：真正实现转换的方法
3. close：关闭资源的方法

注意：自定义 UDTF 函数只能在 SELECT 语句中使用，不能跟其他字段放在一起使用，不能嵌套使用，不能与 group by /sort by/ distribute by/cluster by 一起使用，只能与 order by 一起使用。

public class GenericUDTFStack extends GenericUDTF {
  @Override
  public void close() throws HiveException {
  }

  private transient List<ObjectInspector> argOIs = new ArrayList<ObjectInspector>();
  private transient Object[] forwardObj = null;
  private transient ArrayList<ReturnObjectInspectorResolver> returnOIResolvers =
      new ArrayList<ReturnObjectInspectorResolver>();
  IntWritable numRows = null;
  Integer numCols = null;

  @Override
  public StructObjectInspector initialize(ObjectInspector[] args)
      throws UDFArgumentException {
    if (args.length < 2)  {
      throw new UDFArgumentException("STACK() expects at least two arguments.");
    }
    if (!(args[0] instanceof WritableConstantIntObjectInspector)) {
      throw new UDFArgumentException(
          "The first argument to STACK() must be a constant integer (got " +
          args[0].getTypeName() + " instead).");
    }
    numRows =
        ((WritableConstantIntObjectInspector)args[0]).getWritableConstantValue();

    if (numRows == null || numRows.get() < 1) {
      throw new UDFArgumentException(
          "STACK() expects its first argument to be >= 1.");
    }

    // Divide and round up.
    numCols = (args.length - 1 + numRows.get() - 1) / numRows.get();

    for (int jj = 0; jj < numCols; ++jj) {
      returnOIResolvers.add(new ReturnObjectInspectorResolver());
      for (int ii = 0; ii < numRows.get(); ++ii) {
        int index = ii * numCols + jj + 1;
        if (index < args.length && 
            !returnOIResolvers.get(jj).update(args[index])) {
          throw new UDFArgumentException(
              "Argument " + (jj + 1) + "'s type (" +
              args[jj + 1].getTypeName() + ") should be equal to argument " +
              index + "'s type (" + args[index].getTypeName() + ")");
        }
      }
    }

    forwardObj = new Object[numCols];
    for (int ii = 0; ii < args.length; ++ii) {
      argOIs.add(args[ii]);
    }

    ArrayList<String> fieldNames = new ArrayList<String>();
    ArrayList<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>();
    for (int ii = 0; ii < numCols; ++ii) {
      fieldNames.add("col" + ii);
      fieldOIs.add(returnOIResolvers.get(ii).get());
    }

    return ObjectInspectorFactory.getStandardStructObjectInspector(
        fieldNames, fieldOIs);
  }

  @Override
  public void process(Object[] args)
      throws HiveException, UDFArgumentException {
    for (int ii = 0; ii < numRows.get(); ++ii) {
      for (int jj = 0; jj < numCols; ++jj) {
        int index = ii * numCols + jj + 1;
        if (index < args.length) {
          forwardObj[jj] = 
            returnOIResolvers.get(jj).convertIfNecessary(args[index], argOIs.get(index));
        } else {
          forwardObj[ii] = null;
        }
      }
      forward(forwardObj);
    }
  }

  @Override
  public String toString() {
    return "stack";
  }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

14.1.4 创建临时函数

-- 1. 把项目打好包后上传到 Hive 的 lib 目录下
CREATE TEMPORARY FUNCTION functionName AS className
-- functionName：函数名
-- className：类全限定名 
1
2
3
4

14.1.5 创建永久函数

-- 1. 把打好的 jar 包上传的 HDFS 上面
CREATE TEMPORARY FUNCTION functionName AS className USING JAR hdfsPath;
-- functionName：函数名                        
-- className：类全限定名
-- hdfsPath：jar包hdfs路径
1
2
3
4
5

14.2 Hive 的字符串函数

14.2.1 substring/substr截取字符串函数

-- 示例:
SELECT substr('Facebook', 5) FROM src LIMIT 1;
'book'
SELECT substr('Facebook', -5) FROM src LIMIT 1;
'ebook'
SELECT substr('Facebook', 5, 1) FROM src LIMIT 1;   
'b'
SELECT substring('Facebook', 5) FROM src LIMIT 1;
'book'
SELECT substring('Facebook', -5) FROM src LIMIT 1;
'ebook'
SELECT substring('Facebook', 5, 1) FROM src LIMIT 1;   
'b'
1
2
3
4
5
6
7
8
9
10
11
12
13

14.2.2 coalesce非空查找函数

coalesce(expression,value1,value2……)
coalesce() 函数的第一个参数 expression 为待检测的表达式，而其后的参数个数不定。coalesce() 函数将会返回包括 expression 在内的所有参数中的第一个非空表达式。如果 expression 不为空值则返回 expression；否则判断 value1 是否是空值，如果 value1 不为空值则返回 value1；否则判断 value2 是否是空值，如果 value2 不为空值则返回 value2；……以此类推，如果所有的表达式都为空值，则返回 NULL。

14.2.3 if判断函数

if(expr1,a,b)函数的第一个参数是一个表达式，如果满足条件则返回a，否则返回b。

14.2.4 split字符串分割函数

SELECT split('oneAtwoBthreeC', '[ABC]') FROM src LIMIT 1;
["one", "two", "three"] 
1
2

14.2.5 instr查找字符串函数

SELECT instr('Facebook', 'boo') FROM src LIMIT 1;
5
1
2

14.2.6 lower转换小写函数

SELECT lower('Facebook') FROM src LIMIT 1;
'facebook' 
1
2

14.2.7 upper转换大写函数

SELECT lower('facebook') FROM src LIMIT 1;
'FACEBOOK' 
1
2

14.2.8 reverse反转函数

SELECT reverse('Facebook') FROM src LIMIT 1;
'koobecaF'
1
2

14.2.9 regexp_replace正则替换函数

SELECT regexp_replace('100-200', '(\d+)', 'num') FROM src LIMIT 1;
'num-num'
1
2

14.2.10 regexp_extract正则表达式解析函数

-- 语法:regexp_extract(string subject,string pattern,int index)
-- 返回值:string
-- 说明:将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。
    -- 第一参数：要处理的字段
    -- 第二参数: 需要匹配的正则表达式
    -- 第三个参数:
       -- 0：是显示与之匹配的整个字符串
       -- 1：是显示第一个括号里面的
       -- 2：是显示第二个括号里面的字段
SELECT regexp_extract('100-200', '(\d+)-(\d+)', 1) FROM src LIMIT 1;
'100'
1
2
3
4
5
6
7
8
9
10
11

14.2.11 concat/concat_ws字符串拼接函数

SELECT concat('abc', 'def') FROM src LIMIT 1;
'abcdef'
SELECT concat_ws('.', 'www', array('facebook', 'com')) FROM src LIMIT 1;
'www.facebook.com'
1
2
3
4

14.2.12 parse_url

-- 语法：parse_url(str,partToExtract) partToExtract的选项包含[HOST、PATH、QUERY、REF、PROTOCOL、FILE、AUTHORITY、USERINFO]
select parse_url(‘http://facebook.com/path/p1.php?query=1’, ‘PROTOCOL’) from dual; 
http
select parse_url(‘http://facebook.com/path/p1.php?query=1’, ‘HOST’) from dual;
facebook.com
select parse_url(‘http://facebook.com/path/p1.php?query=1’, ‘REF’) from dual;
空
select parse_url(‘http://facebook.com/path/p1.php?query=1’, ‘PATH’) from dual;
/path/p1.php
select parse_url(‘http://facebook.com/path/p1.php?query=1’, ‘QUERY’) from dual;
空
select parse_url(‘http://facebook.com/path/p1.php?query=1’, ‘FILE’) from dual;
/path/p1.php?query=1
select parse_url(‘http://facebook.com/path/p1.php?query=1’, ‘AUTHORITY’) from dual;
facebook.com
select parse_url(‘http://facebook.com/path/p1.php?query=1’, ‘USERINFO’) from dual;
空
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

14.2.13 parse_url_tuple

-- UDTF函数 解析url字符串一般用来解析IP域名等信息
SELECT parse_url_tuple("https://xuexi.boxuegu.com/video.html?courseId=1708","HOST","PATH","QUERY","QUERY:courseId") as(HOST,PATH,QUERY,QUERY_NAME);   
+--------------------+--------------+----------------+-------------+--+
|        host        |     path     |     query      | query_name  |
+--------------------+--------------+----------------+-------------+--+
| xuexi.boxuegu.com  | /video.html  | courseId=1708  | 1708        |
+--------------------+--------------+----------------+-------------+--+
1
2
3
4
5
6
7

14.3 Hive的日期函数

14.3.1 year

-- 获取日期格式中的年
SELECT year('2009-07-30') FROM src LIMIT 1;
2009
1
2
3

14.3.2 month

-- 获取日期格式中的月
SELECT month('2009-07-30') FROM src LIMIT 1;
7
1
2
3

14.3.3 day

-- 获取日期格式中的天
SELECT day('2009-07-30') FROM src LIMIT 1;
30
1
2
3

14.3.4 hour

SELECT hour('2009-07-30 12:58:59') FROM src LIMIT 1;
12
SELECT hour('12:58:59') FROM src LIMIT 1;
12
1
2
3
4

14.3.5 to_date

SELECT to_date('2009-07-30 04:17:52') FROM src LIMIT 1;
'2009-07-30'
1
2

14.3.6 minute

SELECT minute('2009-07-30 12:58:59') FROM src LIMIT 1;
58
SELECT minute('12:58:59') FROM src LIMIT 1;
58
1
2
3
4

14.3.7 second

SELECT second('2009-07-30 12:58:59') FROM src LIMIT 1;
59
SELECT second('12:58:59') FROM src LIMIT 1;
59
1
2
3
4

14.3.8 date_add

SELECT date_add('2009-07-30', 1) FROM src LIMIT 1;
'2009-07-31'
1
2

14.3.9 date_sub

SELECT date_sub('2009-07-30', 1) FROM src LIMIT 1;
'2009-07-29'
1
2

14.3.10 unix_timestamp

-- 将标准日期转换为时间戳
select unix_timestamp('2018-12-05 01:10:00','yyyy-MM-dd HH:mm:ss');
+-------------+--+
|     _c0     |
+-------------+--+
| 1543943400  |
+-------------+--+
select unix_timestamp('2018-12-05','yyyy-MM-dd');
+-------------+--+
|     _c0     |
+-------------+--+
| 1543939200  |
+-------------+--+
1
2
3
4
5
6
7
8
9
10
11
12
13

14.3.11 from_unixtime

-- 将时间戳转换为标准格式
SELECT from_unixtime(0, 'yyyy-MM-dd HH:mm:ss')
'1970-01-01 00:00:00'
1
2
3

14.3.12 datediff

-- 日期比较函数
SELECT datediff('2009-07-28', '2009-07-29')
-1
SELECT datediff('2009-07-30', '2009-07-29')、
1
1
2
3
4
5

14.3.13 weekofyear

-- 日期转周函数
SELECT weekofyear('2008-02-20') FROM src LIMIT 1;
8
SELECT weekofyear('1980-12-31 12:59:59') FROM src LIMIT 1;
1
1
2
3
4
5

14.4 lateral view 侧视图

一般与 split、explode、parse_url_tople 等 UDTF 函数连用，它可以将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

LATERAL VIEW UDTF(args) AS VIEW_NAME AS COLUMN1,COLUMN2; 
1

14.5 explode函数

函数类别：UDTF

功能：将一个 array 或者 map 进行展开，对于 array 来说，会将集合的每一个元素作为一行，对于 map 来说，会将每一对 keyvalue 变为一行，key 为一列，value 为一列。一般情况下与侧视图 lateral view 连用。

-- 示例ARRAY
CREATE TABLE TB_ARRAY(
NAME STRING,
ID ARRAY<STRING>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '/t'
COLLECTION ITEMS TERMINATED BY ',';

SELECT * FROM TB_ARRAY;
+----------------+-------------------------+--+
| tb_array.name  |       tb_array.id       |
+----------------+-------------------------+--+
| zhangsan       | ["1","2","3","4","5"]   |
| lisi           | ["6","7","8","9","10"]  |
+----------------+-------------------------+--+
SELECT EXPLODE(ID) FROM TB_ARRAY;
+------+--+
| col  |
+------+--+
| 1    |
| 2    |
| 3    |
| 4    |
| 5    |
| 6    |
| 7    |
| 8    |
| 9    |
| 10   |
+------+--+

SELECT A.NAME,B.ID FROM TB_ARRAY A LATERAL VIEW EXPLODE(ID) B AS ID;

+-----------+-------+--+
|  a.name   | b.id  |
+-----------+-------+--+
| zhangsan  | 1     |
| zhangsan  | 2     |
| zhangsan  | 3     |
| zhangsan  | 4     |
| zhangsan  | 5     |
| lisi      | 6     |
| lisi      | 7     |
| lisi      | 8     |
| lisi      | 9     |
| lisi      | 10    |
+-----------+-------+--+

-- 示例Map
CREATE TABLE TB_MAP(
ID STRING,
HOBBY MAP<STRING,STRING>
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':';

SELECT * FROM TB_MAP;
+------------+----------------------------------------+--+
| tb_map.id  |              tb_map.hobby              |
+------------+----------------------------------------+--+
| 1          | {"唱歌":"非常喜欢","跳舞":"不喜欢","打游戏":"非常喜欢"}  |
| 2          | {"游泳":"非常喜欢","跳绳":"不喜欢"}               |
+------------+----------------------------------------+--+

SELECT EXPLODE(HOBBY) FORM TB_MAP;
+------+--------+--+
| key  | value  |
+------+--------+--+
| 唱歌   | 非常喜欢   |
| 跳舞   | 不喜欢    |
| 打游戏  | 非常喜欢   |
| 游泳   | 非常喜欢   |
| 跳绳   | 不喜欢    |
+------+--------+--+

SELECT A.ID,B.* FROM TB_MAP A LATERAL VIEW EXPLODE(HOBBY) B AS KEY,VALUE;
+-------+--------+----------+--+
| a.id  | b.key  | b.value  |
+-------+--------+----------+--+
| 1     | 唱歌     | 非常喜欢     |
| 1     | 跳舞     | 不喜欢      |
| 1     | 打游戏    | 非常喜欢     |
| 2     | 游泳     | 非常喜欢     |
| 2     | 跳绳     | 不喜欢      |
+-------+--------+----------+--+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86

14.6 特殊函数

14.6.1 reflect

-- 功能：可以通过实例化对象调用 Java 类的方法，也可以直接调用 Java 中静态类的方法。
-- 要求：该类的方法必须返回一个基础类型或者 Hive 能识别的序列化类型。
-- 用法：reflect（classname , method , args）
select reflect(“java.util.UUID”,“randomUUID”);
select reflect("java.lang.Math","max",3,2);
1
2
3
4
5

14.6.2 Json 处理

Join 处理 Hive 中的数据有两种方式

14.6.2.1 使用 Hive 中的内置函数对 Json 进行处理

-- get_json_object：从 Json 数据中获取 Json 字段
-- json_tuple：UDTF 函数，解析 Json 数据
vim /export/datas/hivedata.json
{"id": 1701439105,"ids": [2154137571,3889177061],"total_number": 493}
{"id": 1701439106,"ids": [2154137571,3889177061],"total_number": 494}

create table tb_json_test1(
json string
);
load data local inpath '/export/datas/hivedata.json' into table tb_json_test1;

select
get_json_object(t.json,'$.id') as id,
get_json_object(t.json,'$.total_number') as total_number
from
tb_json_test1 t;
+------------+---------------+
|    id      | total_number  |
+------------+---------------+
| 1701439105 |      493      |
| 1701439106 |      494      |
+------------+---------------+
select 
t2.*
from
tb_json_test1 t1
lateral view
json_tuple(t1.json,'id','total_number') t2 as c1,c2;
+------------+---------+
|    c1      |   c2    |
+------------+---------+
| 1701439105 |   493   |
| 1701439106 |   494   |
+------------+---------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

14.6.2.2 通过 Json 的解析工具类直接加载数据解析

先将 Json 的解析类 json-serde-1.3.7-jar-with-dependencies.jar 放入 Hive 的 lib 目录下
重新启动 Hive 的服务端才可以生效。

create table tb_json_test2 (
id string,
ids array<string>,
total_number int
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
STORED AS TEXTFILE;

-- 注意：表中字段名称必须与json数据中字段名称一致
load data local inpath '/export/datas/hivedata.json' into table tb_json_test2;
1
2
3
4
5
6
7
8
9
10

14.7 窗口函数和分析函数

示例表信息

SELECT * FROM TB_DEPT;
+-------------+---------------+----------------+---------------+--+
| tb_dept.id  | tb_dept.name  | tb_dept.price  | tb_dept.type  |
+-------------+---------------+----------------+---------------+--+
| 7369        | SMITH         | 1000.0         | 20            |
| 7566        | JONES         | 2975.0         | 20            |
| 7698        | BLAK          | 2850.0         | 30            |
| 7782        | CLARK         | 6000.0         | 10            |
| 7788        | SCOTT         | 3000.0         | 20            |
| 7839        | KING          | 5000.0         | 10            |
| 7876        | ADAMS         | 1100.0         | 20            |
| 7900        | JAMES         | 950.0          | 30            |
| 7902        | FORD          | 3000.0         | 20            |
| 7934        | MILLER        | 1300.0         | 10            |
+-------------+---------------+----------------+---------------+--+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

14.7.1 窗口函数

-- 语法
函数名(args) OVER(PARTITION BY  COLUMN ORDER BY  COLUMN DESC)
PARTITION BY ：-- 按照某一列进行分区，这一列的值相同的行会放到一起
ORDER BY ：-- 分区内部按照某一列进行排序
1
2
3
4

-- 手动指定窗口
  -- preceding：往前 例如：数字 preceding
  -- following：往后 例如：数字 following
  -- current row ：当前行
  -- unbounded : 起点
  -- unbounded preceding :表示从前面的起点,分区的第一行
  -- unbounded following :表示到后面的终点，分区的最后一行

rows between 起始位置 and 结束位置
rows between unbounded preceding and current row ：-- 从第一行到当前行，做分区也做排序的默认窗口
rows between unbounded preceding and unbounded following ：-- 从第一行到最后一行，只做分区不做排序的默认窗口
1
2
3
4
5
6
7
8
9
10
11

14.7.1.1 位置窗口

14.7.1.1.1 FIRST_VALUE

-- 取窗口中第一个值。
SELECT *,
FIRST_VALUE(ID) OVER(PARTITION BY TYPE ORDER BY PRICE DESC) AS ID_ FROM TB_DEPT;
+-------+---------+---------+-------+-------+--+
|  id   |  name   |  price  | type  |  id_  |
+-------+---------+---------+-------+-------+--+
| 7782  | CLARK   | 6000.0  | 10    | 7782  |
| 7839  | KING    | 5000.0  | 10    | 7782  |
| 7934  | MILLER  | 1300.0  | 10    | 7782  |
| 7902  | FORD    | 3000.0  | 20    | 7902  |
| 7788  | SCOTT   | 3000.0  | 20    | 7902  |
| 7566  | JONES   | 2975.0  | 20    | 7902  |
| 7876  | ADAMS   | 1100.0  | 20    | 7902  |
| 7369  | SMITH   | 1000.0  | 20    | 7902  |
| 7698  | BLAK    | 2850.0  | 30    | 7698  |
| 7900  | JAMES   | 950.0   | 30    | 7698  |
+-------+---------+---------+-------+-------+--+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

14.7.1.1.2 LAST_VALUE

-- 取窗口中最后一个值。
-- 分区又排序的话，默认的窗口大小为开始到当前节点
SELECT *,
LAST_VALUE(ID) OVER(PARTITION BY TYPE ORDER BY PRICE DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS ID_ FROM TB_DEPT;
+-------+---------+---------+-------+-------+--+
|  id   |  name   |  price  | type  |  id_  |
+-------+---------+---------+-------+-------+--+
| 7782  | CLARK   | 6000.0  | 10    | 7934  |
| 7839  | KING    | 5000.0  | 10    | 7934  |
| 7934  | MILLER  | 1300.0  | 10    | 7934  |
| 7902  | FORD    | 3000.0  | 20    | 7369  |
| 7788  | SCOTT   | 3000.0  | 20    | 7369  |
| 7566  | JONES   | 2975.0  | 20    | 7369  |
| 7876  | ADAMS   | 1100.0  | 20    | 7369  |
| 7369  | SMITH   | 1000.0  | 20    | 7369  |
| 7698  | BLAK    | 2850.0  | 30    | 7900  |
| 7900  | JAMES   | 950.0   | 30    | 7900  |
+-------+---------+---------+-------+-------+--+
-- 如果不指定窗口的大小的话，查询出来的结果不正确
SELECT *,LAST_VALUE(ID) OVER (PARTITION BY TYPE ORDER BY PRICE DESC) AS ID_ FROM TB_DEPT;
+-------+---------+---------+-------+-------+--+
|  id   |  name   |  price  | type  |  id_  |
+-------+---------+---------+-------+-------+--+
| 7782  | CLARK   | 6000.0  | 10    | 7782  |
| 7839  | KING    | 5000.0  | 10    | 7839  |
| 7934  | MILLER  | 1300.0  | 10    | 7934  |
| 7902  | FORD    | 3000.0  | 20    | 7902  |
| 7788  | SCOTT   | 3000.0  | 20    | 7788  |
| 7566  | JONES   | 2975.0  | 20    | 7566  |
| 7876  | ADAMS   | 1100.0  | 20    | 7876  |
| 7369  | SMITH   | 1000.0  | 20    | 7369  |
| 7698  | BLAK    | 2850.0  | 30    | 7698  |
| 7900  | JAMES   | 950.0   | 30    | 7900  |
+-------+---------+---------+-------+-------+--+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

14.7.1.1.3 LEAD

-- 向后偏移，LEAD(COLUMN,偏移量,默认值)。
SELECT *,LEAD(PRICE,1,0) OVER(PARTITION BY TYPE ORDER BY PRICE DESC) AS LEAD_PRICE FROM TB_DEPT;
+-------------+---------------+----------------+---------------+-------------+--+
| tb_dept.id  | tb_dept.name  | tb_dept.price  | tb_dept.type  | lead_price  |
+-------------+---------------+----------------+---------------+-------------+--+
| 7782        | CLARK         | 6000.0         | 10            | 5000.0      |
| 7839        | KING          | 5000.0         | 10            | 1300.0      |
| 7934        | MILLER        | 1300.0         | 10            | 0.0         |
| 7902        | FORD          | 3000.0         | 20            | 3000.0      |
| 7788        | SCOTT         | 3000.0         | 20            | 2975.0      |
| 7566        | JONES         | 2975.0         | 20            | 1100.0      |
| 7876        | ADAMS         | 1100.0         | 20            | 1000.0      |
| 7369        | SMITH         | 1000.0         | 20            | 0.0         |
| 7698        | BLAK          | 2850.0         | 30            | 950.0       |
| 7900        | JAMES         | 950.0          | 30            | 0.0         |
+-------------+---------------+----------------+---------------+-------------+--+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

14.7.1.1.4 LAG

-- 向前偏移，LAG(COLUMN,偏移量,默认值)。
SELECT * ,LAG(PRICE,1,0) OVER (PARTITION BY TYPE ORDER BY PRICE DESC) AS LAG_PRICE FROM TB_DEPT;
+-------------+---------------+----------------+---------------+------------+--+
| tb_dept.id  | tb_dept.name  | tb_dept.price  | tb_dept.type  | lag_price  |
+-------------+---------------+----------------+---------------+------------+--+
| 7782        | CLARK         | 6000.0         | 10            | 0.0        |
| 7839        | KING          | 5000.0         | 10            | 6000.0     |
| 7934        | MILLER        | 1300.0         | 10            | 5000.0     |
| 7902        | FORD          | 3000.0         | 20            | 0.0        |
| 7788        | SCOTT         | 3000.0         | 20            | 3000.0     |
| 7566        | JONES         | 2975.0         | 20            | 3000.0     |
| 7876        | ADAMS         | 1100.0         | 20            | 2975.0     |
| 7369        | SMITH         | 1000.0         | 20            | 1100.0     |
| 7698        | BLAK          | 2850.0         | 30            | 0.0        |
| 7900        | JAMES         | 950.0          | 30            | 2850.0     |
+-------------+---------------+----------------+---------------+------------+--+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

14.7.1.2 聚合窗口

14.7.1.2.1 MAX

-- 取窗口中的最大值。
SELECT *,MAX(PRICE) OVER (PARTITION BY TYPE ORDER BY PRICE DESC) AS MAX_PRICE FROM TB_DEPT;
+-------------+---------------+----------------+---------------+------------+--+
| tb_dept.id  | tb_dept.name  | tb_dept.price  | tb_dept.type  | max_price  |
+-------------+---------------+----------------+---------------+------------+--+
| 7782        | CLARK         | 6000.0         | 10            | 6000.0     |
| 7839        | KING          | 5000.0         | 10            | 6000.0     |
| 7934        | MILLER        | 1300.0         | 10            | 6000.0     |
| 7902        | FORD          | 3000.0         | 20            | 3000.0     |
| 7788        | SCOTT         | 3000.0         | 20            | 3000.0     |
| 7566        | JONES         | 2975.0         | 20            | 3000.0     |
| 7876        | ADAMS         | 1100.0         | 20            | 3000.0     |
| 7369        | SMITH         | 1000.0         | 20            | 3000.0     |
| 7698        | BLAK          | 2850.0         | 30            | 2850.0     |
| 7900        | JAMES         | 950.0          | 30            | 2850.0     |
+-------------+---------------+----------------+---------------+------------+--+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

14.7.1.2.2 MIN

-- 取窗口中的最小值。
SELECT *,MIN(PRICE) OVER (PARTITION BY TYPE ORDER BY PRICE ASC) AS MIN_PRICE FROM TB_DEPT;
+-------------+---------------+----------------+---------------+------------+--+
| tb_dept.id  | tb_dept.name  | tb_dept.price  | tb_dept.type  | min_price  |
+-------------+---------------+----------------+---------------+------------+--+
| 7934        | MILLER        | 1300.0         | 10            | 1300.0     |
| 7839        | KING          | 5000.0         | 10            | 1300.0     |
| 7782        | CLARK         | 6000.0         | 10            | 1300.0     |
| 7369        | SMITH         | 1000.0         | 20            | 1000.0     |
| 7876        | ADAMS         | 1100.0         | 20            | 1000.0     |
| 7566        | JONES         | 2975.0         | 20            | 1000.0     |
| 7902        | FORD          | 3000.0         | 20            | 1000.0     |
| 7788        | SCOTT         | 3000.0         | 20            | 1000.0     |
| 7900        | JAMES         | 950.0          | 30            | 950.0      |
| 7698        | BLAK          | 2850.0         | 30            | 950.0      |
+-------------+---------------+----------------+---------------+------------+--+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

14.7.1.2.3 SUM

-- 取窗口中的数据总和。
SELECT *,SUM(PRICE) OVER (PARTITION BY TYPE ORDER BY PRICE ASC) AS MIN_PRICE FROM TB_DEPT;
+-------------+---------------+----------------+---------------+------------+--+
| tb_dept.id  | tb_dept.name  | tb_dept.price  | tb_dept.type  | min_price  |
+-------------+---------------+----------------+---------------+------------+--+
| 7934        | MILLER        | 1300.0         | 10            | 1300.0     |
| 7839        | KING          | 5000.0         | 10            | 6300.0     |
| 7782        | CLARK         | 6000.0         | 10            | 12300.0    |
| 7369        | SMITH         | 1000.0         | 20            | 1000.0     |
| 7876        | ADAMS         | 1100.0         | 20            | 2100.0     |
| 7566        | JONES         | 2975.0         | 20            | 5075.0     |
| 7902        | FORD          | 3000.0         | 20            | 11075.0    |
| 7788        | SCOTT         | 3000.0         | 20            | 11075.0    |
| 7900        | JAMES         | 950.0          | 30            | 950.0      |
| 7698        | BLAK          | 2850.0         | 30            | 3800.0     |
+-------------+---------------+----------------+---------------+------------+--+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

14.7.1.2.4 COUNT

-- 求窗口中的数据条数。 
SELECT *,COUNT(PRICE) OVER (PARTITION BY TYPE ORDER BY PRICE ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MIN_PRICE FROM TB_DEPT;
+-------------+---------------+----------------+---------------+------------+--+
| tb_dept.id  | tb_dept.name  | tb_dept.price  | tb_dept.type  | min_price  |
+-------------+---------------+----------------+---------------+------------+--+
| 7934        | MILLER        | 1300.0         | 10            | 3          |
| 7839        | KING          | 5000.0         | 10            | 3          |
| 7782        | CLARK         | 6000.0         | 10            | 3          |
| 7369        | SMITH         | 1000.0         | 20            | 5          |
| 7876        | ADAMS         | 1100.0         | 20            | 5          |
| 7566        | JONES         | 2975.0         | 20            | 5          |
| 7902        | FORD          | 3000.0         | 20            | 5          |
| 7788        | SCOTT         | 3000.0         | 20            | 5          |
| 7900        | JAMES         | 950.0          | 30            | 2          |
| 7698        | BLAK          | 2850.0         | 30            | 2          |
+-------------+---------------+----------------+---------------+------------+--+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

14.7.1.2.5 AVG

-- 求窗口中的平均值。
SELECT *,AVG(PRICE) OVER (PARTITION BY TYPE ORDER BY PRICE ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MIN_PRICE FROM TB_DEPT;
+-------------+---------------+----------------+---------------+---------------------+--+
| tb_dept.id  | tb_dept.name  | tb_dept.price  | tb_dept.type  |      min_price      |
+-------------+---------------+----------------+---------------+---------------------+--+
| 7934        | MILLER        | 1300.0         | 10            | 4100.0              |
| 7839        | KING          | 5000.0         | 10            | 4100.0              |
| 7782        | CLARK         | 6000.0         | 10            | 4100.0              |
| 7369        | SMITH         | 1000.0         | 20            | 2215.0              |
| 7876        | ADAMS         | 1100.0         | 20            | 2215.0              |
| 7566        | JONES         | 2975.0         | 20            | 2215.0              |
| 7902        | FORD          | 3000.0         | 20            | 2215.0              |
| 7788        | SCOTT         | 3000.0         | 20            | 2215.0              |
| 7900        | JAMES         | 950.0          | 30            | 1900.0              |
| 7698        | BLAK          | 2850.0         | 30            | 1900.0              |
+-------------+---------------+----------------+---------------+---------------------+--+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

14.7.2 分析函数

-- 语法
函数名() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
1
2

14.7.2.1 ROW_NUMBER

-- 给每个分区内部编号
SELECT * ,ROW_NUMBER() OVER(PARTITION BY TYPE ORDER BY PRICE DESC) AS CN FROM TB_DEPT;
+-------------+---------------+----------------+---------------+-----+--+
| tb_dept.id  | tb_dept.name  | tb_dept.price  | tb_dept.type  | cn  |
+-------------+---------------+----------------+---------------+-----+--+
| 7782        | CLARK         | 6000.0         | 10            | 1   |
| 7839        | KING          | 5000.0         | 10            | 2   |
| 7934        | MILLER        | 1300.0         | 10            | 3   |
| 7902        | FORD          | 3000.0         | 20            | 1   |
| 7788        | SCOTT         | 3000.0         | 20            | 2   |
| 7566        | JONES         | 2975.0         | 20            | 3   |
| 7876        | ADAMS         | 1100.0         | 20            | 4   |
| 7369        | SMITH         | 1000.0         | 20            | 5   |
| 7698        | BLAK          | 2850.0         | 30            | 1   |
| 7900        | JAMES         | 950.0          | 30            | 2   |
+-------------+---------------+----------------+---------------+-----+--+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

14.7.2.2 RANK

-- 用于实现分区内排名编号[会留空位]
SELECT * ,RANK() OVER(PARTITION BY TYPE ORDER BY PRICE DESC) AS CN FROM TB_DEPT;
+-------------+---------------+----------------+---------------+-----+--+
| tb_dept.id  | tb_dept.name  | tb_dept.price  | tb_dept.type  | cn  |
+-------------+---------------+----------------+---------------+-----+--+
| 7782        | CLARK         | 6000.0         | 10            | 1   |
| 7839        | KING          | 5000.0         | 10            | 2   |
| 7934        | MILLER        | 1300.0         | 10            | 3   |
| 7902        | FORD          | 3000.0         | 20            | 1   |
| 7788        | SCOTT         | 3000.0         | 20            | 1   |
| 7566        | JONES         | 2975.0         | 20            | 3   |
| 7876        | ADAMS         | 1100.0         | 20            | 4   |
| 7369        | SMITH         | 1000.0         | 20            | 5   |
| 7698        | BLAK          | 2850.0         | 30            | 1   |
| 7900        | JAMES         | 950.0          | 30            | 2   |
+-------------+---------------+----------------+---------------+-----+--+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

14.7.2.3 DENSE_RANK

-- 用于实现分区内排名编号[不留空位]
SELECT * ,DENSE_RANK() OVER(PARTITION BY TYPE ORDER BY PRICE DESC) AS CN FROM TB_DEPT;
+-------------+---------------+----------------+---------------+-----+--+
| tb_dept.id  | tb_dept.name  | tb_dept.price  | tb_dept.type  | cn  |
+-------------+---------------+----------------+---------------+-----+--+
| 7782        | CLARK         | 6000.0         | 10            | 1   |
| 7839        | KING          | 5000.0         | 10            | 2   |
| 7934        | MILLER        | 1300.0         | 10            | 3   |
| 7902        | FORD          | 3000.0         | 20            | 1   |
| 7788        | SCOTT         | 3000.0         | 20            | 1   |
| 7566        | JONES         | 2975.0         | 20            | 2   |
| 7876        | ADAMS         | 1100.0         | 20            | 3   |
| 7369        | SMITH         | 1000.0         | 20            | 4   |
| 7698        | BLAK          | 2850.0         | 30            | 1   |
| 7900        | JAMES         | 950.0          | 30            | 2   |
+-------------+---------------+----------------+---------------+-----+--+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

14.7.2.4 ROW_NUMBER , RANK 以及 DENSE_RANK 的区别

这三个分析函数都是实现分区内排名编号
ROW_NUMBER:给每个分区排序，如果排序的数据重复,源数据从后往前排列排序
RANK:给每个分区排序,如果排序的数据重复，则重复的数据的排名一样并且留有空位
DENSE_RANK:给每个分区排序,如果排序的数据重复，则重复的数据的排名一样并且不留空位

14.7.2.5 NTILE

-- NTILE：对分区内部再次进行均分，通常用作求数据的百分比，百分率等。NTILE() 括号里面的数字表示均分几等份
SELECT * ,NTILE(2) OVER(PARTITION BY TYPE ORDER BY PRICE DESC) AS CN FROM TB_DEPT;
+-------------+---------------+----------------+---------------+-----+--+
| tb_dept.id  | tb_dept.name  | tb_dept.price  | tb_dept.type  | cn  |
+-------------+---------------+----------------+---------------+-----+--+
| 7782        | CLARK         | 6000.0         | 10            | 1   |
| 7839        | KING          | 5000.0         | 10            | 1   |
| 7934        | MILLER        | 1300.0         | 10            | 2   |
| 7902        | FORD          | 3000.0         | 20            | 1   |
| 7788        | SCOTT         | 3000.0         | 20            | 1   |
| 7566        | JONES         | 2975.0         | 20            | 1   |
| 7876        | ADAMS         | 1100.0         | 20            | 2   |
| 7369        | SMITH         | 1000.0         | 20            | 2   |
| 7698        | BLAK          | 2850.0         | 30            | 1   |
| 7900        | JAMES         | 950.0          | 30            | 2   |
+-------------+---------------+----------------+---------------+-----+--+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

14.8 CASE WHEN 用法

14.8.1 第一种用法

CASE COLUMN 
WHEN VALUE1 THEN RS1 
WHEN VALUE2 THEN RS2 
WHEN VALUE3 THEN RS3 
ELSE REN 
END
1
2
3
4
5
6

14.8.2 第二种用法

CASE
WHEN COLUMN = VALUE THEN RS1 
WHEN COLUMN = VALUE2 THEN RS2 
WHEN COLUMN = VALUE3 THEN RS3 
ELSE RSN 
END
1
2
3
4
5
6

14.9 行列转换

14.9.1 行转列

14.9.1.1 多行转多列

CREATE TABLE ROW2TOCOL2(
  COLUMN1 STRING,
  COLUMN2 STRING,
  COLUMN3 INT
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

SELECT * FROM ROW2TOCOL2;
+---------------------+---------------------+---------------------+--+
| row2tocol2.column1  | row2tocol2.column2  | row2tocol2.column3  |
+---------------------+---------------------+---------------------+--+
| a                   | c                   | 1                   |
| a                   | d                   | 2                   |
| a                   | e                   | 3                   |
| b                   | c                   | 4                   |
| b                   | d                   | 5                   |
| b                   | e                   | 6                   |
+---------------------+---------------------+---------------------+--+
SELECT COLUMN1,
MAX(CASE COLUMN2 WHEN 'c' THEN COLUMN3 ELSE 0 END) AS C,
MAX(CASE COLUMN2 WHEN 'd' THEN COLUMN3 ELSE 0 END) AS D,
MAX(CASE COLUMN2 WHEN 'e' THEN COLUMN3 ELSE 0 END) AS E
FROM ROW2TOCOL2 GROUP BY COLUMN1;
+----------+----+----+----+--+
| column1  | c  | d  | e  |
+----------+----+----+----+--+
| a        | 1  | 2  | 3  |
| b        | 4  | 5  | 6  |
+----------+----+----+----+--+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

14.9.1.2 多行转单列

CREATE TABLE ROW2TOCOL(
  COLUMN1 STRING,
  COLUMN2 STRING,
  COLUMN3 INT
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

SELECT * FROM ROW2TOCOL;
+--------------------+--------------------+--------------------+--+
| row2tocol.column1  | row2tocol.column2  | row2tocol.column3  |
+--------------------+--------------------+--------------------+--+
| a                  | b                  | 1                  |
| a                  | b                  | 2                  |
| a                  | b                  | 3                  |
| c                  | d                  | 4                  |
| c                  | d                  | 5                  |
| c                  | d                  | 6                  |
+--------------------+--------------------+--------------------+--+

SELECT COLUMN1, COLUMN2, 
CONCAT_WS(',', COLLECT_SET(CAST(COLUMN3 AS STRING))) AS COLUMN3 FROM 
ROW2TOCOL GROUP BY COLUMN1, COLUMN2;
+----------+----------+----------+--+
| column1  | column2  | column3  |
+----------+----------+----------+--+
| a        | b        | 1,2,3    |
| c        | d        | 4,5,6    |
+----------+----------+----------+--+
-- COLLECT_SET :将某一列的所有元素放在一个不重复的数组中
-- COLLECT_LIST :将某一列的所有元素放在一个可重复的数组中
-- CONCAT :不能指定结果中每个元素的分隔符,只要有一个元素为null,结果就是null
-- CONCAT_WS('分隔符',str...):可以指定结果中每个元素的分隔符，只要有一个元素不为null，结果就不为null
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

14.9.2 列转行

14.9.2.1 多列转多行

CREATE TABLE COL2TOROW2(
  COLUMN1 STRING,
  COLUMN2 STRING,
  COLUMN3 STRING,
  COLUMN4 STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

SELECT * FROM COL2TOROW2;
+---------------------+---------------------+---------------------+---------------------+--+
| col2torow2.column1  | col2torow2.column2  | col2torow2.column3  | col2torow2.column4  |
+---------------------+---------------------+---------------------+---------------------+--+
| a                   | 1                   | 2                   | 3                   |
| b                   | 4                   | 5                   | 6                   |
+---------------------+---------------------+---------------------+---------------------+--+
SELECT COLUMN1, 'c' as COLUMN2, COLUMN2 as COLUMN3 from COL2TOROW2
UNION ALL
SELECT COLUMN1, 'd' as COLUMN2, COLUMN3 as COLUMN3 from COL2TOROW2
UNION ALL
SELECT COLUMN1, 'e' as COLUMN2, COLUMN4 as COLUMN3 from COL2TOROW2;
+--------------+--------------+--------------+--+
| _u1.column1  | _u1.column2  | _u1.column3  |
+--------------+--------------+--------------+--+
| a            | c            | 1            |
| a            | d            | 2            |
| a            | e            | 3            |
| b            | c            | 4            |
| b            | d            | 5            |
| b            | e            | 6            |
+--------------+--------------+--------------+--+
UNION：-- 实现SQL语句行的拼接
UNION ：-- 要求字段个数是一致的
UNION ALL：-- 要求字段个数和类型都是一致的
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

14.9.2.2 单列转多行

CREATE TABLE COL1TOROW2(
  A STRING,
  B STRING,
  C STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'

SELECT * FROM COL1TOROW2;
+---------------+---------------+---------------+--+
| col1torow2.a  | col1torow2.b  | col1torow2.c  |
+---------------+---------------+---------------+--+
| a             | b             | 1,2,3         |
| c             | d             | 4,5,6         |
+---------------+---------------+---------------+--+
SELECT A.a,A.b,B.c FROM COL1TOROW2 A LATERAL VIEW EXPLODE(SPLIT(A.c,",")) B AS c
+------+------+------+--+
| a.a  | a.b  | b.c  |
+------+------+------+--+
| a    | b    | 1    |
| a    | b    | 2    |
| a    | b    | 3    |
| c    | d    | 4    |
| c    | d    | 5    |
| c    | d    | 6    |
+------+------+------+--+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

15 Hive 的调优

15.1 fetch task

<!--有的 SQL 不走 Mapreduce,不走 Mapreduce 的 SQL 走了 fetch task ,更快。这个由如下的参数决定-->
<property>
    <name>hive.fetch.task.conversion</name>
    <value>minimal</value>
</property>
<!--value的值有两个：
1.minimal：select * 和对分区表的分区过滤不走 Mapreduce
2.more ：简单的 select [不包含聚合、分组、排序等等]、字段过滤、limit 都不走 Mapreduce
在工作中建议改为 more
-->
1
2
3
4
5
6
7
8
9
10

15.2 严格模式

<property>
    <name>hive.mapred.mode</name>
    <value>nonstrict</value><!--非严格模式-->
    <value>strict</value><!--严格模式-->
</property>
<!-- 默认为非严格模式，在工作中要设定为严格模式，严格模式可以避免一些程序的故障，更加严格代码规范 -->
     <!-- 笛卡尔积：Join 时不指定关联字段会产生笛卡尔积 -->
     <!-- 在分区表进行查询时不指定分区过滤 -->
     <!-- 将 bigint 类型与字符串或者 double 类型进行比较 -->
     <!-- 如果做了全局排序不使用 limit 进行输出 -->
1
2
3
4
5
6
7
8
9
10

15.3 本地模式

<!--- 如果一个SQL语句处理的数据量不是很大，不需要提交给yarn取运行，直接在当前机器上完成计算-->
<property>
    <name>hive.exec.mode.local.auto</name>
    <value>false</value><!--不开启-->
    <value>true</value><!--开启-->
</property>
<!-- 当程序开启了本地模式，以下情况就不会提交给 yarn 去运行，直接在本地做计算-->
   <!-- 处理的数据不超过 128M -->
   <!-- 该 SQL 对应的 MapTask 不超过 4 个 -->
   <!-- 该 SQL 对应的 ReduceTask 不超过 1 个 -->
1
2
3
4
5
6
7
8
9
10

15.4 JVM重用

<!--当我们执行一个 Mapreduce 程序的时候，每一个 Task 进程都会启动一个 JVM,当进程执行完毕，JVM 销毁。
可以通过设置这个参数，来达到 JVM 重用的特性，例如 value 修改为10，则表示 10 个 Task 在一个 JVM 中运行，当运行结束后，
JVM 进行销毁-->	
<property>
    <name>mapreduce.job.jvm.numtasks</name>
    <value>10</value>
</property>
1
2
3
4
5
6
7

15.5 推测执行

<!--推测执行：当程序运行 Task 任务时，如果某个任务一直没有运行，其他 Task 都在等待这个 Task，如果开启了推测执行的话，程序会在启动一个 Task 运行这个任务，这两个 Task 那个先完成任务另外一个会被 kill 掉-->

<!--开启 mapTask 的推测执行-->
<property>
    <name>mapreduce.map.speculative</name>
    <value>true</value>
</property>

<!--开启reduceTask的推测执行-->
<property>
    <name>mapreduce.reduce.speculative</name>
    <value>true</value>
</property>
<!-- 不建议一定开启 -->
     <!-- 如果因为环境问题导致的故障，环境问题一直存在，每次产生的新的Task都会失败。最终导致多个Task全部阻塞 -->
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

15.6 压缩

15.6.1 优点

1. 减少存储磁盘空间，降低单节点的磁盘 IO。
2. 由于压缩后的数据占用的带宽更少，因此可以加快数据在 Hadoop 集群流动的速度，减少网络传输带宽。

15.6.2 缺点

需要花费额外的时间用 CPU 做压缩和解压缩计算。

15.6.3 压缩分析

1. 数据在进入 MapTask 任务前可以进行压缩，然后进行解压处理。
2. 数据在 MapTask 任务处理完成后的输出可以压缩，这样可以节省网络I/O，因为 ReduceTask 通常和 MapTask 不在一个机器上。
3. 数据在 ReduceTask 中处理完后可以进行压缩存储在 HDFS 上，可以降低磁盘占用量。

15.6.4 压缩设置

<!-- Hive 中的压缩就是 Mapreduce 中的压缩，所以先在 mapred-site.xml 文件中配置 Mapreduce 的压缩 -->
<!-- 配置 Mapreduce 的 Map 端压缩以及压缩算法-->
<property>
    <name>mapreduce.map.output.compress</name>
    <value>true</value>
</property>
<property>
    <name>mapreduce.map.output.compress.codec</name>
    <value>org.apache.hadoop.io.compress.SnappyCodec</value>
</property>
<!-- 配置 Mapreduce 的 Reudce 端压缩以及压缩算法-->
<property>
    <name>mapreduce.output.fileoutputformat.compress</name>
    <value>true</value>
</property>
<property>
    <name>mapreduce.output.fileoutputformat.compress.codec</name>
    <value>org.apache.hadoop.io.compress.SnappyCodec</value>
</property>
<!--设置 Mapreduce 最终数据输出压缩为块压缩-->
<property>
    <name>mapreduce.output.fileoutputformat.compress.type</name>
    <value>BLOCK</value>
</property>

<!--然后在 Hive 中配置中间结果压缩-->
<property>
    <name>hive.exec.compress.intermediate</name>
    <value>true</value>
</property>
<!--配置 Hive 最终输出数据压缩功能-->
<property>
    <name>hive.exec.compress.output</name>
    <value>true</value>
</property>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

15.7 Map端聚合

<!--就是 Mapreduce 底层的 Map 端的 Combiner-->
<property>
    <name>hive.map.aggr</name>
    <value>true</value><!-- 默认为true，在底层的 Mapreduce 程序执行时会开启 Combiner-->
</property>
<property>
    <name>hive.groupby.mapaggr.checkinterval</name>
    <value>100000</value><!--在Map端进行聚合操作的条目数目-->
</property>
1
2
3
4
5
6
7
8
9

15.8 并行计算

<!-- Hive 执行 SQL 默认使用的是顺序执行，可以开启并行计算来执行 SQL 语句 -->
<property>
    <name>hive.exec.parallel</name>
    <value>true</value>
</property>
<!--代表一次 SQL 计算中最大允许同时执行的 Job 个数，默认为8个Job-->
<property>
    <name>hive.exec.parallel.thread.number</name>
    <value>8</value>
</property>
1
2
3
4
5
6
7
8
9
10

15.9 SQL 优化

15.9.1 分区裁剪

如果做了分区表，在对表进行分析处理时，尽量加上分区字段的过滤。先过滤，后处理。

15.9.2 列裁剪

如果处理数据时，先将不需要用到的列过滤掉，然后再处理。先过滤，后处理。

15.9.3 count(distinct)

在 count 时，如果结果需要做去重，一定先 distinct ，然后再聚合。先过滤，后处理，不然会导致数据倾斜。distinct 在 Hive 的底层就是分组。

15.9.4 表优化

1. 分区表：尽量根据业务需求将表作分区表，方便以后对分区数据进行处理。
2. 分桶表：如果后期的需求中，会出现大数据的join，推荐创建分桶表。

15.10 数据存储格式

创建表的时候，表的存储格式分为好多种，以下为常用的存储类型：

1. TEXTFILE：行式存储，默认表的存储格式，普通的文本类型，不支持块压缩，磁盘开销大。
2. RCFILE：RCFile是一种行列存储相结合的存储方式。
3. ORC：数据按行分块，每块按照列存储，压缩快。快速列存取，效率比RCFILE高，是RCFILE的改良版本。
4. PARQUET：能够很好的压缩，有很好的查询性能，支持有限的模式演进。但是写速度通常比较慢。这种文件格式主要是用在 Impala 上面。
注意：PARQUET 数据存储类型可以和文件压缩一块使用 PARQUET + SNAPPY。ORC 一般不建议和文件压缩一块使用，组合使用可能更耗时间，单独的 ORC 存储格式已经有很高的压缩比了。

15.10.1 行式存储

15.10.1.1 优点

相关的数据是保存在一起，比较符合面向对象的思维，因为一行数据就是一条记录。这种存储格式比较方便进行 INSERT / UPDATE 操作。

15.10.1.2 缺点

1. 如果查询只涉及某几个列，它会把整行数据都读取出来，不能跳过不必要的列读取。当然数据比较少，一般没啥问题，如果数据量比较大就比较影响性能。
2. 由于每一行中，列的数据类型不一致，导致不容易获得一个极高的压缩比，也就是空间利用率不高。
3. 不是所有的列都适合作为索引。

15.10.2 列式存储

15.10.2.1 优点

1. 查询时，只有涉及到的列才会被查询，不会把所有列都查询出来，即可以跳过不必要的列查询。
2. 高效的压缩率，不仅节省储存空间也节省计算内存和 CPU。
3. 任何列都可以作为索引。

15.10.2.2缺点

1. INSERT / UPDATE 很麻烦或者不方便。
2. 不适合扫描小量的数据。

15.11 数据倾斜

只要是涉及到分布式计算，都存在数据倾斜的问题。在分布式计算中，将大的任务拆分为多个小的任务，但是由于拆分的规则，任务分配不平衡，这就会产生数据倾斜，产生的原因有两种，一种数据本身就是倾斜的，另一种为由于分配规则不适合导致的数据倾斜。以下列出常用的解决方案。

15.11.1 Mapreduce 的解决方案

1. 开启 Map 端的聚合 Combiner。
2. 弃用系统默认的 Hash 规则，改用随机分区或者其他的分区规则。
3. 如果数据本身就是倾斜的，可以通过添加随机数来将数据转换为不是倾斜的数据。

15.11.2 Hive 的解决方案

1. 如果是 Join 产生的数据倾斜，可以使用 MapJoin，强制数据量小的一方为小表。尽量先去重，做数据裁剪，对 Join 的字段去重后再 Join。将不会被 Join 上的数据先过滤，再进行Join。
2. 使用参数优化进行 Map 端的 Combiner。
3. 使用参数进行负载均衡。
4. 调整 MapTask 和 ReduceTask 的个数。

<!--当选项设定为 true，生成的查询计划会有两个 MRJob。第一个 MRJob 会进行随机分区，进行第一次聚合,第二个 MRJob 会进行 Hash 分区，做整体聚合-->
<property>
    <name>hive.groupby.skewindata</name>
    <value>true</value>
</property>
1
2
3
4
5

16 数仓设计

16.1 数据仓库

数据仓库是一个更加集成的、统一化的面向主题的数据存储模型。本身自己不产生数据，也不消费数据。只存储数据，数据是随着时间变化的，只有增量，不产生更新、删除等操作。数据仓库的目标为实现整个公司所有数据的统一化，标准化的数据管理、数据共享。

16.1.1 数据仓库的特点

1. 面向主题：整个数据仓库中的数据有多少种应用，就有多少种主题。只要确定数据仓库中的数据和主题的关系即可。
2. 集成化的：所有数据源的数据全部进入数据仓库中。
3. 变化的：数据会不断增加，常见的是T+1模式，每天导入昨天的数据进入数据仓库。只导入增量的数据，数据不会更新，如果数据更新了，增量导入更新的数据。
4. 非易失的：数据仓库中的数据一般只进行增量更新。

16.1.2 数据表的分类

1. 增量表：只有每天增量的数据，以后每一次导入都是增量导入。
2. 全量表：全量导入的数据，第一次导入都是全量导入。
3. 拉链表：保存所有的历史数据，查询时可以根据条件只查询最新的数据。

16.1.3 数据仓库和数据库的区别

1. 数据库：面向单一业务的，OLTP联机事务处理，设计主要体现在功能，性能的衡量。
2. 数据仓库：面向主题的，OLAP联机分析处理，主要实现复杂的分析，主要用于运营决策。

16.2 分层设计

所有不同的数据源数据进入数据仓库，经过统一的数据转换，得到最终想要的数据。

16.2.1 常见的分层架构图

16.2.2 分层实现

16.2.2.1 ODS层（操作数据层）

1. 所有的不同数据源的数据在经过预处理以后入库的,第一层就是 ODS 层。
2. 最原始的数据，只经过了简单的 ETL 处理，变成了结构化的数据。
3. 原始表：将原始数据文件经过了一些简单的过滤得到了一张表结构的数据。
4. 设计表或者库名称一般以 ods_ 开头。

16.2.2.2 DWD层（详细数据层）

1. 将 ODS 层的数据进一步的转化，实现转换、补全构建宽表。
2. 补全一些维度的字段：时间维度，地域维度。
3. 设计表或者库名称一般以 dwd_ 开头。

16.2.2.3 DWS层（汇总数据层）

1. 将 DWD 层中构建的宽表，根据每个主题中的业务拆分不同的分析模型。
2. 后期在主题中实现分析应用。
3. 从 DWD 层的宽表中提取各个分析需要用到的字段构建分析模型表。
4. 设计表或者库名称一般以 dws_ 开头。

16.2.2.4 ADS层（数据应用层）

1. 在 DWS 中实现应用得到结果表。
2. 设计表或者库名称一般以 ads_ 开头

16.2.2.5 常见的一些辅助层

1. DIM：维度层，存储维度表
2. TMP：临时层，存放临时表

16.3 建模设计

解决数据库和表到底如何进行设计，采用什么样的建模模型。

16.3.1 ER模型

关系实体模型，传统的网站或者基于数据库的设计一般都用ER模型。

16.3.2 维度模型

基于不同维度来描述一件事情，大数据分析主要使用的建模模型。通过不同维度来看待一件事情的好坏。每一个维度就是一张表，例如时间维度、地域维度、平台维度、终端维度、板块维度、来源维度等等，维度模型中常见的设计模式有：

16.3.2.1 雪花模型

子维度通过外键关联父维度。

16.3.2.1.1 结构

16.3.2.1.2 存在的问题

如果想描述一个事实，维度中引用了多级外键，在数据检索时非常的慢，需要逐级检索。

16.3.2.2 星型模型

所有的维度直接关联事实表，不存在子维度关联父维度。

16.3.2.2.1 结构

16.3.2.2.2 特点

数据存储时允许亢余，用空间换时间。

16.3.2.3 星座模型

基于星型模型中的多个事实表可以共用相同的维度表。

16.3.2.3.1 结构

17 Hive 的 maven 相关依赖

  <properties>
       <hadoop.version>2.6.0-cdh5.14.0</hadoop.version>
       <mysql.version>5.1.38</mysql.version>
       <hive.version>1.1.0-cdh5.14.0</hive.version>
   </properties>
 
   <dependencies>
       <!-- Hadoop Client 依赖 -->
       <dependency>
           <groupId>org.apache.hadoop</groupId>
           <artifactId>hadoop-common</artifactId>
           <version>${hadoop.version}</version>
       </dependency>
       <dependency>
           <groupId>org.apache.hadoop</groupId>
           <artifactId>hadoop-client</artifactId>
           <version>${hadoop.version}</version>
       </dependency>
       <dependency>
           <groupId>org.apache.hadoop</groupId>
           <artifactId>hadoop-hdfs</artifactId>
           <version>${hadoop.version}</version>
       </dependency>
       <!-- MySQL Client 依赖 -->
       <dependency>
           <groupId>mysql</groupId>
           <artifactId>mysql-connector-java</artifactId>
           <version>${mysql.version}</version>
       </dependency>
       <!-- Hive依赖 -->
       <dependency>
           <groupId>org.apache.hive</groupId>
           <artifactId>hive-exec</artifactId>
           <version>${hive.version}</version>
       </dependency>
       <dependency>
           <groupId>org.apache.hive</groupId>
           <artifactId>hive-common</artifactId>
           <version>${hive.version}</version>
       </dependency>
       <dependency>
           <groupId>org.apache.hive</groupId>
           <artifactId>hive-cli</artifactId>
           <version>${hive.version}</version>
       </dependency>
       <dependency>
           <groupId>org.apache.hive</groupId>
           <artifactId>hive-jdbc</artifactId>
           <version>${hive.version}</version>
       </dependency>
   </dependencies>
   <build>
       <plugins>
           <plugin>
               <groupId>org.apache.maven.plugins</groupId>
               <artifactId>maven-compiler-plugin</artifactId>
               <version>3.0</version>
               <configuration>
                   <source>1.8</source>
                   <target>1.8</target>
                   <encoding>UTF-8</encoding>
               </configuration>
           </plugin>
       </plugins>
   </build>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/笔触狂放9/article/detail/389932