Hadhoop生态（Hive）

一、数据仓库

1. 数据仓库的基本概念

数据仓库，英文名称为Data Warehouse，可简写为DW或DWH。数据仓库的目的是构建面向分析的集成化数据环境，为企业提供决策支持（Decision Support）。 它出于分析性报告和决策支持目的而创建。

数据仓库本身并不“生产”任何数据，同时自身也不需要“消费”任何的数据，数据来源于外部，并且开放给外部应用，这也是为什么叫“仓库”，而不叫“工厂”的原因。

企业中一般先有数据库，然后有数据仓库，可以没有数据仓库，但是不能没有数据库。

数据仓库不是大型的数据库，只是一个数据分析的平台。

2. 数据仓库的主要特征

数据仓库是面向主题的（Subject-Oriented ）、 集成的（Integrated）、 非易失的（Non-Volatile）和时变的（Time-Variant ）数据集合，用以支持管理决策 。

2.1. 面向主题

主题是一个抽象的概念，是较高层次上企业信息系统中的数据综合、归类并进行分析利用的抽象。在逻辑意义上，它是对应企业中某一宏观分析领域所涉及的分析对象。

操作型处理（传统数据）对数据的划分并不适用于决策分析。而基于主题组织的数据则不同，它们被划分为各自独立的领域，每个领域有各自的逻辑内涵但互不交叉，在抽象层次上对数据进行完整、一致和准确的描述。一些主题相关的数据通常分布在多个操作型系统中。

2.2. 集成性

数仓不是生成数据的平台，其数据来自于各个不同的数据源。

当我们确定主题之后，就需要把和主题相关的数据从各个数据源集成过来。

因为同一个主题的数据可能来自不同的数据源,它们之间会存在着差异(异构数据);字段名不同、单位不统一、编码不统;
因此，在集成的过程中需要进行ETL(Extract抽取、Transform转换、Load加载)

2.3. 非易失性（不可更新性）

数仓上面的数据几乎没有修改操作，都是分析的操作。

数据仓库的数据反映的是一段相当长的时间内历史数据的内容，因此，数据经加工和集成进入数据仓库后是极少更新的，通常只需要定期的加载和更新。

2.4. 时变性

数据仓库的数据需要更新，以适应决策的需要。

3. 数据仓库与数据库区别

数据库与数据仓库的区别实际讲的是OLTP与OLAP的区别。

操作型处理，叫联机事务处理OLTP（On-Line Transaction Processing，），也可以称面向交易的处理系统，它是针对具体业务在数据库联机的日常操作。 常对少数记录进行查询、修改。用户较为关心操作的响应时间、数据的安全性、完整性和并发支持的用户数等问题。传统的数据库系统作为数据管理的主要手段，通主要用于操作型处理。

分析型处理，叫联机分析处理OLAP（On-Line ==Analytical == Processing）一般针对某些主题的历史数据进行分析，支持管理决策。

首先要明白，数据仓库的出现，并不是要取代数据库。

• 数据库是面向事务的设计，数据仓库是面向主题设计的。
• 数据库一般存储业务数据，数据仓库存储的一般是历史数据。
• 数据库设计是尽量避免冗余，一般针对某一业务应用进行设计，比如一张简单的User表，记录用户名、密码等简单数据即可，符合业务应用，但是不符合分析。数据仓库在设计是有意引入冗余，依照分析需求，分析维度、分析指标进行设计。
• 数据库是为捕获数据而设计，数据仓库是为分析数据而设计。

数据仓库，是在数据库已经大量存在的情况下，为了进一步挖掘数据资源、为了决策需要而产生的，它决不是所谓的“大型数据库”。

4. 数据仓库分层架构

按照数据流入流出的过程，数据仓库架构可分为三层——源数据、数据仓库、数据应用。

为什么要对数据仓库分层？
用空间换时间，通过大量的预处理来提升应用系统的用户体验（效率），因此数据仓库会存在大量冗余的数据；不分层的话，如果源业务系统的业务规则发生变化将会影响整个数据清洗过程，工作量巨大。

5. ETL、ELT

数据仓库从各数据源获取数据及在数据仓库内的数据转换和流动都可以认为是ETL（抽取Extract, 转化Transform , 装载Load）的过程。
但是在实际操作中将数据加载到仓库却产生了两种不同做法：ETL和ELT。

5.1. ETL

首先从数据源池中提取数据，这些数据源通常是事务性数据库。数据保存在临时暂存数据库中（ODS）。然后执行转换操作，将数据结构化并转换为适合目标数据仓库系统的形式。然后将结构化数据加载到仓库中，以备分析。

5.1. ETL

使用ELT，数据在从数据源中提取后立即加载。没有专门的临时数据库（ODS），这意味着数据会立即加载到单一的集中存储库中。数据在数据仓库系统中进行转换，以便与商业智能工具（BI 工具）一起使用。大数据时代数仓这个特点很明显。

二、Apache Hive

1. Hive 简介

Hive 是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供类SQL查询功能。

本质是将SQL转换为MapReduce程序。

主要用途：用来做离线数据分析，比直接用MapReduce开发效率更高。

为什么使用Hive:

1. 直接使用Hadoop MapReduce处理数据所面临的问题：
   人员学习成本太高
   MapReduce 实现复杂查询逻辑开发难度太大
2. 使用Hive ：
   操作接口采用类SQL语法，提供快速开发的能力
   避免了去写MapReduce，减少开发人员的学习成本
   功能扩展很方便

2. Hive 架构

2.1. Hive 架构图

2.2. Hive 组件

用户接口：包括 CLI、JDBC/ODBC、WebGUI。其中，CLI(command line interface)为 shell 命令行；JDBC/ODBC 是 Hive 的 JAVA 实现，与传统数据库JDBC 类似；WebGUI是通过浏览器访问Hive。

元数据存储：通常是存储在关系数据库如 mysql/derby中。Hive 将元数据存储在数据库中。Hive 中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等。

解释器、编译器、优化器、执行器:完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。生成的查询计划存储在 HDFS 中，并在随后有 MapReduce 调用执行。

2.3. Hive 与Hadoop 的关系

Hive 利用HDFS 存储数据，利用MapReduce查询分析数据。

3. Hive与传统数据库对比

hive 用于海量数据的离线数据分析。

hive 具有sql 数据库的外表，但应用场景完全不同，hive只适合用来做批量数据统计分析。

更直观的对比请看下面这幅图：

4. Hive 安装部署

Hive 安装前需要安装好 JDK 和 Hadoop。配置好环境变量。如果需要使用mysql 来存储元数据，则需要mysql也安装好。

4.1. metadata 、metastore

Metadata 即元数据。元数据包含用Hive创建的database、table、表的字段等元信息。元数据存储在关系型数据库中。如hive内置的Derby、第三方如 MySQL 等。

Metastore 即元数据服务，作用是：客户端连接metastore服务，metastore再去连接MySQL数据库来存取元数据。有了metastore服务，就可以有多个客户端同时连接，而且这些客户端不需要知道MySQL数据库的用户名和密码，只需要连接metastore 服务即可。

4.2. metadata 、metastore

metastore服务配置有3种模式：内嵌模式、本地模式、远程模式。区分3种配置方式的关键是弄清楚两个问题：

• Metastore服务是否需要单独配置、单独启动？
• Metadata是存储在内置的derby中，还是第三方RDBMS,比如Mysql。
  
  这里我们使用企业推荐模式–远程模式部署。

4.3. metastore 的启动方式

nohup /export/server/apache-hive-3.1.2-bin/bin/hive --service metastore & 

5. Hive Client、Beeline Client

5.1. 第一代客户端Hive Client

在hive安装包的bin目录下，有hive提供的第一代客户端 bin/hive。使用该客户端可以访问hive的metastore服务。从而达到操作hive的目的。

如果需要在其他机器上通过该客户端访问hive metastore服务，只需要在该机器的hive-site.xml配置中添加metastore服务地址即可。
scp 安装包到另一个机器上，比如node3：

scp -r /export/server/apache-hive-3.1.2-bin/ node3:/export/server/

使用下面的命令启动hive的客户端：

/export/server/apache-hive-3.1.2-bin/bin/hive

注意：第一代客户端已经不推荐使用了。

5.2. 第二代客户端Hive Beeline Client

hive 经过发展，推出了第二代客户端beeline，但是beeline客户端不是直接访问metastore服务的，而是需要单独启动hiveserver2服务。

在hive运行的服务器上，首先启动metastore服务，然后启动hiveserver2服务。

nohup /export/server/apache-hive-3.1.2-bin/bin/hive --service metastore &
nohup /export/server/apache-hive-3.1.2-bin/bin/hive --service hiveserver2 &

在node3上使用beeline客户端进行连接访问。

/export/server/apache-hive-3.1.2-bin/bin/beeline 
beeline> ! connect jdbc:hive2://node1:10000
Enter username for jdbc:hive2://node1:10000: root
Enter password for jdbc:hive2://node1:10000: *******

三、 HQL数据定义语言（DDL）概述

1. DDL语法的作用

数据定义语言 (Data Definition Language, DDL)，是 SQL 语言集中对数据库内部的对象结构进行创建，删除，修改等的操作语言，这些数据库对象包括database（schema）、 table、view、index 等。核心语法由== CREATE、ALTER 与 DROP==三个所组成。DDL并不涉及表内部数据的操作。

2. Hive中DDL使用

Hive SQL（HQL）与 SQL 的语法大同小异，基本上是相通的，学过SQL的使用者可以无痛使用Hive SQL。只不过在学习HQL语法的时候，特别要注意Hive自己特有的语法知识点，比如partition相关的DDL操作。

基于Hive的设计、使用特点，HQL中create语法（尤其create table）将是学习掌握 DDL 语法的重中之重。可以说建表是否成功直接影响数据文件是否映射成功，进而影响后续是否可以基于SQL分析数据。

3. Hive DDL 建表基础

3.1. 完整建表语法树

• 蓝色字体是建表语法的关键字，用于指定某些功能。
• [] 中括号的语法表示可选。
• | 表示使用的时候，左右语法二选一。
• 建表语句中的语法顺序要和上述语法规则保持一致。

3.2. Hive数据类型详解

3.2.1. 整体概述

Hive 中的数据类型指的是Hive表中的列字段类型。Hive数据类型整体分为两个类别：原生数据类型（primitive data type）和复杂数据类型（complex data type）。

• 原生数据类型包括：数值类型、时间类型、字符串类型、杂项数据类型；
• 复杂数据类型包括：array数组、map映射、struct结构、union联合体。

关于Hive的数据类型，需要注意：

1. 英文字母大小写不敏感；
2. 除SQL数据类型外，还支持Java数据类型，比如：string；
3. int和string是使用最多的，大多数函数都支持；
4. 复杂数据类型的使用通常需要和分隔符指定语法配合使用。
5. 如果定义的数据类型和文件不一致，hive会尝试隐式转换，但是不保证成功。

3.2.2. 原生数据类型

Hive支持的原生数据类型如下图所示：

其中标注的数据类型是使用较多的，详细的描述请查询语法手册：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types


案例：文件archer.txt 中记录了手游《王者荣耀》射手的相关信息，内容如下所示，其中字段之间分隔符为制表符\t,要求在Hive中建表映射成功该文件。

字段之间的分隔符是制表符，需要使用row format语法进行指定。

--创建数据库并切换使用 
use zmx01; 

 --ddl create table 
create table t_archer( 
    id int comment "ID", 
    name string comment "英雄名称", 
    hp_max int comment "最大生命", 
    mp_max int comment "最大法力", 
    attack_max int comment "最高物攻", 
    defense_max int comment "最大物防", 
    attack_range string comment "攻击范围", 
    role_main string comment "主要定位", 
    role_assist string comment "次要定位" 
) comment "王者荣耀射手信息" 
row format delimited fields terminated by "\t"; 

建表成功之后，在Hive的默认存储路径下就生成了表对应的文件夹，把archer.txt文件上传到对应的表文件夹下。

hadoop fs -put archer.txt  /user/hive/warehouse/zmx01.db/t_archer

执行查询操作，可以看出数据已经映射成功。

0: jdbc:hive2://node1:10000> select * from t_archer;

Hive这种能力是不是比mysql一条一条insert插入数据方便多了

3.2.3. 复杂数据类型

Hive支持的复杂数据类型如下图所示：

其中标注的数据类型是使用较多的，详细的描述请查询语法手册：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types

案例：文件hot_hero_skin_price.txt中记录了手游《王者荣耀》热门英雄的相关皮肤价格信息，内容如下,要求在Hive中建表映射成功该文件。

分析一下：前3个字段原生数据类型、最后一个字段复杂类型map。需要指定字段之间分隔符、集合元素之间分隔符、map kv之间分隔符。

create table t_hot_hero_skin_price( 
    id int, 
    name string, 
    win_rate int, 
    skin_price map<string,int> 
) 
row format delimited 
fields terminated by ',' 
collection items terminated by '-' 
map keys terminated by ':' ; 

建表成功后，把hot_hero_skin_price.txt文件上传到对应的表文件夹下。

hadoop fs -put hot_hero_skin_price.txt /user/hive/warehouse/honor_of_kings.db/t_hot_hero_skin_price

执行查询操作，可以看出数据已经映射成功。

select * from t_hot_hero_skin_price

如果最后一个字段以String类型来定义，那么后续就无法通过键值对来查询。

select skin_price['至尊宝']from t_hot_hero_skin_price limit 1;

3.2.4. 数据类型隐式、显示转换

与SQL类似，HQL支持隐式和显式类型转换。
原生类型从窄类型到宽类型的转换称为隐式转换，反之，则不允许。
下表描述了类型之间允许的隐式转换：

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types

显式类型转换使用CAST函数。

例如，CAST（'100’as INT）会将100字符串转换为100整数值。 如果强制转换失败，例如CAST（'INT’as INT），该函数返回NULL。

3.3. Hive读写文件机制

3.3.1. SerDe 是什么

SerDe 是 Serializer、Deserializer 的简称，目的是用于序列化和反序列化。序列化是对象转化为字节码的过程；而反序列化是字节码转换为对象的过程。

Hive 使用SerDe（和FileFormat）读取和写入行对象。

需要注意的是，“ key”部分在读取时会被忽略，而在写入时key始终是常数。基本上行对象存储在“value”中。

可以通过desc formatted tablename 查看表的相关SerDe信息。默认如下：

3.3.2. Hive 读写文件流程

Hive 读取文件机制：首先调用InputFormat（默认 TextInputFormat），返回一条一条kv键值对记录（默认是一行对应一条记录）。然后调用SerDe（默认LazySimpleSerDe）的 Deserializer，将一条记录中的value根据分隔符切分为各个字段。

Hive 写文件机制：将 Row 写入文件时，首先调用 SerDe（默认LazySimpleSerDe ）的 Serializer 将对象转换成字节序列，然后调用OutputFormat 将数据写入HDFS文件中。

3.3.3. SerDe相关语法

在Hive的建表语句中，和SerDe相关的语法为：



其中ROW FORMAT是语法关键字，DELIMITED和SERDE二选其一。

如果使用delimited表示使用默认的LazySimpleSerDe类来处理数据。如果数据文件格式比较特殊可以使用ROW FORMAT SERDE serde_name指定其他的Serde类来处理数据,甚至支持用户自定义SerDe类。

3.3.4. LazySimpleSerDe分隔符指定

LazySimpleSerDe是Hive默认的序列化类，包含4种子语法，分别用于指定字段之间、集合元素之间、map映射 kv之间、换行的分隔符号。在建表的时候可以根据数据的特点灵活搭配使用。

3.3.5. 默认分隔符

hive 建表时如果没有 row format 语法。此时字段之间默认的分割符是==‘\001’==，是一种特殊的字符，使用的是ascii编码的值，键盘是打不出来的。

在实际工作中，最喜欢的就是\001分隔符，我们需要有意识的把数据之间的分隔符指定为\001。

4. Hive DDL建表高阶

4.1. Hive内、外部表

4.1.1. 什么是内部表

内部表（Internal table）也称为被 Hive 拥有和管理的托管表（Managed table）。默认情况下创建的表就是内部表，Hive拥有该表的结构和文件。换句话说，Hive完全管理表（元数据和数据）的生命周期，类似于RDBMS中的表。

当您删除内部表时，它会删除数据以及表的元数据。

create table student( 
num int, 
name string, 
sex string, 
age int, 
dept string)  
row format delimited  
fields terminated by ','; 

可以使用

describe formatted zmx01.student;

来获取表的描述信息，从中可以看出表的类型。

4.1.2. 什么是外部表

外部表（External table）中的数据不是Hive拥有或管理的，只管理表元数据的生命周期。要创建一个外部表，需要使用EXTERNAL语法关键字。
删除外部表只会删除元数据，而不会删除实际数据。在Hive外部仍然可以访问实际数据。

而且外部表更为方便的是可以搭配location语法指定数据的路径。

create external table student_ext( 
num int, 
name string, 
sex string, 
age int, 
dept string) 
row format delimited 
fields terminated by ',' 
location '/stu'; 

location：重新指定文件的存储位置，默认为使用的database。
可以使用

describe formatted zmx01.student;

来获取表的描述信息，从中可以看出表的类型。

4.1.3. 内部表、外部表差异

无论内部表还是外部表，Hive都在Hive Metastore中管理表定义及其分区信息。删除内部表会从Metastore中删除表元数据，还会从HDFS中删除其所有数据/文件。

删除外部表，只会从Metastore中删除表的元数据，并保持HDFS位置中的实际数据不变。

4.1.4. 如何选择内部表、外部表

当需要通过Hive完全管理控制表的整个生命周期时，请使用内部表。

当文件已经存在或位于远程位置时，请使用外部表，因为即使删除表，文件也会被保留。

4.2. Hive分区表(优化)

4.2.1. 分区表的引入、产生背景

现有6份数据文件，分别记录了《王者荣耀》中6种位置的英雄相关信息。现要求通过建立一张表t_all_hero，把6份文件同时映射加载。

create table t_all_hero( 
id int, 
name string, 
hp_max int, 
mp_max int, 
attack_max int, 
defense_max int, 
attack_range string, 
role_main string, 
role_assist string 
) 
row format delimited 
fields terminated by "\t"; 

加载数据文件到HDFS指定路径下：

现要求查询role_main 主要定位是射手并且 hp_max 最大生命大于 6000 的有几个，sql语句如下：

select count(*) from t_all_hero where role_main="archer" and hp_max >6000; 

思考一下：where语句的背后需要进行全表扫描才能过滤出结果，对于hive来说需要扫描表下面的每一个文件。如果数据文件特别多的话，效率很慢也没必要。本需求中，只需要扫描archer.txt文件即可，如何优化可以加快查询，减少全表扫描呢？

4.2.2. 分区表的概念、创建

当Hive表对应的数据量大、文件多时，为了避免查询时全表扫描数据，Hive支持根据用户指定的字段进行分区，分区的字段可以是日期、地域、种类等具有标识意义的字段。比如把一整年的数据根据月份划分12个月（12个分区），后续就可以查询指定月份分区的数据，尽可能避免了全表扫描查询。

分区表建表语法：

CREATE TABLE table_name (column1 data_type, column2 data_type) PARTITIONED BY (partition1 data_type, partition2 data_type,….); 

针对《王者荣耀》英雄数据，重新创建一张分区表t_all_hero_part，以role角色作为分区字段。

create table t_all_hero_part( 
id int, 
name string, 
hp_max int, 
mp_max int, 
attack_max int, 
defense_max int, 
attack_range string, 
role_main string, 
role_assist string 
) partitioned by (role string) 
row format delimited 
fields terminated by "\t"; 

需要注意：分区字段不能是表中已经存在的字段，因为分区字段最终也会以虚拟字段的形式显示在表结构上。

最后一个role就是分区字段

4.2.3. 分区表数据加载–静态分区

所谓静态分区指的是分区的字段值是由用户在加载数据的时候手动指定的。

语法如下：

load data [local] inpath ' ' into table tablename partition(分区字段='分区值'...);

Local 表示数据是位于本地文件系统还是HDFS文件系统。关于load语句后续详细展开讲解。

首先要确保文件都在Hive服务器所在的本地文件系统上

然后，在远程连接的客户端上：

load data local inpath '/root/hivedata/archer.txt' into table t_all_hero_part partition(role='sheshou'); 
load data local inpath '/root/hivedata/assassin.txt' into table t_all_hero_part partition(role='cike'); 
load data local inpath '/root/hivedata/mage.txt' into table t_all_hero_part partition(role='fashi'); 
load data local inpath '/root/hivedata/support.txt' into table t_all_hero_part partition(role='fuzhu'); 
load data local inpath '/root/hivedata/tank.txt' into table t_all_hero_part partition(role='tanke'); 
load data local inpath '/root/hivedata/warrior.txt' into table t_all_hero_part partition(role='zhanshi'); 

查询一下是否成功

select * from t_all_hero_part;

但是分区值是手动写入的，有风险。并且效率不高

4.2.4. 分区表数据加载–动态分区

往hive 分区表中插入加载数据时，如果需要创建的分区很多，则需要复制粘贴修改很多sql去执行，效率低。因为hive是批处理系统，所以hive提供了一个动态分区功能，其可以基于查询参数的位置去推断分区的名称，从而建立分区。

所谓动态分区指的是分区的字段值是基于查询结果自动推断出来的。核心语法就是insert+select。
插入的数据来自于后面的查询结果，要求查询的的字段类型，顺序，个数要和待插入的表保持一致

启用hive动态分区，需要在hive会话中设置两个参数(默认开启)：

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

第一个参数表示开启动态分区功能，第二个参数指定动态分区的模式。分为nonstick 非严格模式和strict严格模式。strict严格模式要求至少有一个分区为静态分区。

创建一张新的分区表t_all_hero_part_dynamic

create table t_all_hero_part_dynamic( 
id int, 
name string, 
hp_max int, 
mp_max int, 
attack_max int, 
defense_max int, 
attack_range string, 
role_main string, 
role_assist string 
) partitioned by (role string) 
row format delimited 
fields terminated by "\t"; 

执行动态分区插入：

insert into table t_all_hero_part_dynamic partition(role) select tmp.*,tmp.role_main from t_all_hero tmp; 

动态分区插入时，分区值是根据查询返回字段位置自动推断的。

4.2.5. 多重分区表

通过建表语句中关于分区的相关语法可以发现，Hive 支持多个分区字段：PARTITIONED BY (partition1 data_type, partition2 data_type,….)。

多重分区下，分区之间是一种递进关系，可以理解为在前一个分区的基础上继续分区。从HDFS的角度来看就是文件夹下继续划分子文件夹。比如：把全国人口数据首先根据省进行分区，然后根据市进行划分，如果你需要甚至可以继续根据区县再划分，此时就是3分区表。

4.2.6. 分区表的本质

外表上看起来分区表好像没多大变化，只不过多了一个分区字段。实际上在底层管理数据的方式发生了改变。这里直接去HDFS查看区别。

非分区表：



分区表：

分区的概念提供了一种将Hive表数据分离为多个文件/目录的方法。不同分区对应着不同的文件夹，同一分区的数据存储在同一个文件夹下。只需要根据分区值找到对应的文件夹，扫描本分区下的文件即可，避免全表数据扫描。


分区表的使用重点在于：
一、建表时根据业务场景设置合适的分区字段。比如日期、地域、类别等；
二、查询的时候尽量先使用where进行分区过滤，查询指定分区的数据，避免全表扫描。


分区表的注意事项:

• 分区表不是建表的必要语法规则，是一种优化手段表，可选；
• 分区字段不能是表中已有的字段，不能重复；
• 分区字段是虚拟字段，其数据并不存储在底层的文件中；
• 分区字段值的确定来自于用户价值数据手动指定（静态分区）或者根据查询结果位置自动推断（动态分区）；
• Hive 支持多重分区，也就是说在分区的基础上继续分区，划分更加细粒度

4.3. Hive分桶表

4.3.1. 分桶表的概念

分桶表也叫做桶表，源自建表语法中bucket单词。是一种用于优化查询而设计的表类型。该功能可以让数据分解为若干个部分易于管理。

在分桶时，我们要指定根据哪个字段将数据分为几桶（几个部分）。默认规则是：Bucket number = hash_function(bucketing_column) mod num_buckets。
可以发现桶编号相同的数据会被分到同一个桶当中。hash_function取决于分桶字段bucketing_column 的类型：

• 如果是int类型，hash_function(int) == int;
• 如果是其他类型，比如bigint,string 或者复杂数据类型，hash_function
  比较棘手，将是从该类型派生的某个数字，比如hashcode值。

4.3.2. 分桶表的语法

其中CLUSTERED BY (col_name)表示根据哪个字段进行分；
INTO N BUCKETS 表示分为几桶（也就是几个部分）。
需要注意的是，分桶的字段必须是表中已经存在的字段。

4.3.3. 分桶表的创建

现有美国2021-1-28号，各个县county的新冠疫情累计案例信息，包括确
诊病例和死亡病例，数据格式如下所示：

根据state州把数据分为5桶，建表语句如下：

create table t_usa_covid19_bucket( 
    count_date string, 
    county string, 
    state string, 
    fips int, 
    cases int, 
    deaths int) 
clustered by (state) into 5 buckets; 

在创建分桶表时，还可以指定分桶内的数据排序规则：

create table t_usa_covid19_bucket_sort( 
    count_date string, 
    county string, 
    state string, 
    fips int, 
    cases int, 
    deaths int) 
clustered by (state) sorted by (cases desc) into 5 buckets; 

加载数据：
首先还是要创建一张普通的表

CREATE TABLE itcast.t_usa_covid19( 
count_date string, 
county string, 
state string, 
fips int, 
cases int, 
deaths int) 
row format delimited fields terminated by ",";

然后通过insert+select将数据插入分桶表

insert into t_usa_covid19_bucket select * from t_usa_covid19; 

到HDFS上查看t_usa_covid19_bucket 底层数据结构可以发现，数据被分为了5个部分。

4.3.4. 分桶表的使用好处

和非分桶表相比，分桶表的使用好处有以下几点：

1. 基于分桶字段查询时，减少全表扫描
2. JOIN 时可以提高MR程序效率，减少笛卡尔积数量
3. 分桶表数据进行抽样

5. Hive DDL 其他语法

在实际应用中，如果建表有问题，通常直接drop删除重新创建加载数据，时间成本极低。

5.1. Database|schema（数据库） DDL操作

5.1.1. Create database

Hive 中DATABASE 的概念和RDBMS中类似，我们称之为数据库。在Hive中， DATABASE 和 SCHEMA 是可互换的，使用DATABASE或SCHEMA都可以。

• COMMENT：数据库的注释说明语句
• LOCATION：指定数据库在HDFS存储位置，默认/user/hive/warehouse
• WITH DBPROPERTIES：用于指定一些数据库的属性配置。

注意：使用location指定路径的时候，最好是一个新创建的空文件夹。

5.1.2. Describe database

Hive中的DESCRIBE DATABASE语句用于显示Hive中数据库的名称，其注释（如果已设置）及其在文件系统上的位置等信息。

EXTENDED：用于显示更多信息。

5.1.3. Use database

Hive中的USE DATABASE语句用于选择特定的数据库,切换当前会话使用哪一个数据库进行操作。

5.1.4. Drop database

Hive 中的DROP DATABASE 语句用于删除（删除）数据库。
默认行为是 RESTRICT，这意味着仅在数据库为空时才删除它。要删除带有表的数据库，我们可以使用CASCADE。

5.1.5. Alter database

Hive中的ALTER DATABASE语句用于更改与Hive中的数据库关联的元数据。

5.2. Table（表）DDL操作

5.2.1. Describe table

Hive 中的DESCRIBE table 语句用于显示Hive中表的元数据信息。

如果指定了EXTENDED关键字，则它将以Thrift序列化形式显示表的所有元数据。如果指定了FORMATTED关键字，则它将以表格格式显示元数据。

5.2.2. Drop table

DROP TABLE 删除该表的元数据和数据。如果已配置垃圾桶（且未指定PURGE），则该表对应的数据实际上将移动到.Trash/Current 目录，而元数据完全丢失。删除EXTERNAL 表时，该表中的数据不会从文件系统中删除，只删除元数据。

如果指定了PURGE，则表数据不会进入.Trash/Current目录，跳过垃圾桶直接被删除。因此如果DROP失败，则无法挽回该表数据。

5.2.3. Drop table

从表中删除所有行。可以简单理解为清空表的所有数据但是保留表的元数据结构。如果HDFS启用了垃圾桶，数据将被丢进垃圾桶，否则将被删除。

5.2.4. Alter table (最好是直接删除表重建)

--1、更改表名 
ALTER TABLE table_name RENAME TO new_table_name; 

--2、更改表属性 
ALTER TABLE table_name SET TBLPROPERTIES (property_name = 
property_value, ... ); --更改表注释 
ALTER TABLE student SET TBLPROPERTIES ('comment' = "new 
comment for student table"); 

--3、更改SerDe属性 
ALTER TABLE table_name SET SERDE serde_class_name [WITH 
SERDEPROPERTIES (property_name = property_value, ... )]; 
ALTER TABLE table_name [PARTITION partition_spec] SET 
SERDEPROPERTIES serde_properties; 
ALTER TABLE table_name SET SERDEPROPERTIES ('field.delim' = 
','); --移除SerDe属性 
ALTER TABLE table_name [PARTITION partition_spec] UNSET 
SERDEPROPERTIES (property_name, ... ); 
 
 --4、更改表的文件存储格式 该操作仅更改表元数据。现有数据的任何转换都必须
在Hive之外进行。 
ALTER TABLE table_name  SET FILEFORMAT file_format; 

--5、更改表的存储位置路径 
ALTER TABLE table_name SET LOCATION "new location"; 

 --6、更改列名称/类型/位置/注释 
CREATE TABLE test_change (a int, b int, c int); 
// First change column a's name to a1. 
ALTER TABLE test_change CHANGE a a1 INT; 
// Next change column a1's name to a2, its data type to 
string, and put it after column b. 
ALTER TABLE test_change CHANGE a1 a2 STRING AFTER b; 
// The new table's structure is:  b int, a2 string, c int.
// Then change column c's name to c1, and put it as the first 
column. 
ALTER TABLE test_change CHANGE c c1 INT FIRST; 
// The new table's structure is:  c1 int, b int, a2 string. 
// Add a comment to column a1 
ALTER TABLE test_change CHANGE a1 a1 INT COMMENT 'this is 
column a1'; 
 
 --7、添加/替换列 --使用ADD COLUMNS，您可以将新列添加到现有列的末尾但在分区列之前。 --REPLACE COLUMNS 将删除所有现有列，并添加新的列集。 
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name 
data_type,...); 

5.3. Partition（分区）DDL操作

5.3.1. Add partition

分区值仅在为字符串时才应加引号。位置必须是数据文件所在的目录。
ADD PARTITION会更改表元数据，但不会加载数据。如果分区位置中不存在数据，查询将不会返回任何结果。

如果手动添加分区分区

hadoop fs -mkdir /user/hive/warehouse/tithema_dt_t_user_province/province=XM
hadoop fs -put students.txt /user/hive/warehouse/tithema_dt_t_user_province/province=XM

数据创建后的文件夹，并且手动进行划分到分区中绕开了，hive 无识别到分区。

5.3.2. rename partition

5.3.3. delete partition

可以使用ALTER TABLE DROP PARTITION删除表的分区。这将删除该分区的数据和元数据。

5.3.4. msck partition

Hive 将每个表的分区列表信息存储在其 metastore 中。但是，如果将新分区直接添加到HDFS（例如通过使用hadoop fs -put命令）或从HDFS中直接删除分区文件夹，则除非用户ALTER TABLE table_name ADD/DROP PARTITION 在每个新添加的分区上运行命令，否则metastore（也就是Hive）将不会意识到分区信息的这些更改。
但是，用户可以使用修复表选项运行metastore check命令。

MSC 命令的默认选项是“添加分区”。使用此选项，它将把HDFS上存在但元存储中不存在的所有分区添加到元存储中。DROP PARTITIONS 选项将从已经从HDFS 中删除的 metastore 中删除分区信息。SYNC PARTITIONS 选项等效于调用ADD 和DROP PARTITIONS。

5.3.5. alter partition

6. Hive Show显示语法

Show相关的语句提供了一种查询Hive metastore的方法。可以帮助用户查询相关信息。

--1、显示所有数据库 SCHEMAS和DATABASES的用法 功能一样 
show databases; 
show schemas; 

 --2、显示当前数据库所有表/视图/物化视图/分区/索引 
show tables; 
SHOW TABLES [IN database_name]; --指定某个数据库 

 --3、显示当前数据库下所有视图 
Show Views;  -- 虚拟表
SHOW VIEWS 'test_*'; -- show all views that start with "test_" 
SHOW VIEWS FROM test1; -- show views from database test1 
SHOW VIEWS [IN/FROM database_name]; 

 --4、显示当前数据库下所有物化视图 
SHOW MATERIALIZED VIEWS [IN/FROM database_name]; 

 --5、显示表分区信息，分区按字母顺序列出，不是分区表执行该语句会报错 
show partitions table_name; 

 --6、显示表/分区的扩展信息 
SHOW TABLE EXTENDED [IN|FROM database_name] LIKE table_name; 
show table extended like student; 

 --7、显示表的属性信息 
SHOW TBLPROPERTIES table_name; 
show tblproperties student; 

 --8、显示表、视图的创建语句 
SHOW CREATE TABLE ([db_name.]table_name|view_name); 
show create table student; 

 --9、显示表中的所有列，包括分区列。 
SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) db_name]; 
show columns  in student; 

 --10、显示当前支持的所有自定义和内置的函数
show functions; 
 
 -11、Describe desc --查看表信息 
desc extended table_name; --查看表信息（格式化美观） 
desc formatted table_name; --查看数据库相关信息 
describe database database_name; 

四、Hive 数据操纵语言（DML）

1. Load 加载数据

功能：load加载操作时将数据文件移动到Hive表对应的位置的纯复制/移动操作。

Hive官方推荐使用Load命令将数据加载到表中。

在将数据load加载到表中时，Hive不会进行任何转换。

load data inpath ' ' into table tab_name;

• filepath：filepath表示的待移动数据的路径，可以引用一个文件（在这种情况下，Hive将文件移动到表中），也可以是一个目录（在这种情况下，Hive将把该目录中的所有文件移动到表中）。
• LOCAL：如果指定了LOCAL， load命令将在本地文件系统中查找文件路径。如果指定了相对路径，它将相对于用户的当前工作目录进行解释。用户也可以为本地文件指定完整的URI，例如：file:///user/hive/project/data1。这里的本地文件系统指的是Hiveserver2服务所在机器的本地Linux文件系统
• OVERWRITE ：如果使用了OVERWRITE关键字，则目标表（或者分区）中的内容会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。

-------练习:Load Data From Local FS or HDFS------ --step1:建表 --建表student_local 用于演示从本地加载数据 
create table student_local(num int,name string,sex string,age 
int,dept string) row format delimited fields terminated by ','; --建表student_HDFS  用于演示从HDFS加载数据 
create external table student_HDFS(num int,name string,sex 
string,age int,dept string) row format delimited fields 
terminated by ','; --建表student_HDFS_p 用于演示从HDFS加载数据到分区表 
create table student_HDFS_p(num int,name string,sex string,age 
int,dept string) partitioned by(country string) row format 
delimited fields terminated by ','; 
 --建议使用beeline客户端 可以显示出加载过程日志信息 --step2:加载数据 -- 从本地加载数据  数据位于HS2（node1）本地文件系统  本质是hadoop fs 
put上传操作 
LOAD DATA LOCAL INPATH '/root/hivedata/students.txt' INTO TABLE 
student_local; 
 --从HDFS加载数据  数据位于HDFS文件系统根目录下  本质是hadoop fs -mv 移
动操作 --先把数据上传到HDFS上  hadoop fs -put /root/hivedata/students.txt 
/ 
LOAD DATA INPATH '/students.txt' INTO TABLE student_HDFS; 
 ----从HDFS加载数据到分区表中并制定分区  数据位于HDFS文件系统根目录下 --先把数据上传到HDFS上 hadoop fs -put /root/hivedata/students.txt 
/ 
LOAD DATA INPATH '/students.txt' INTO TABLE student_HDFS_p 
partition(country ="CHina"); 

2、Insert插入数据

在MySQL这样的RDBMS中，通常是insert+values的方式来向表插入数据，并且速度很快。这也是RDBMS中插入数据的核心方式。

在Hive中，你会发现执行过程非常非常慢，底层是使用MapReduce把数据写入HDFS的。

但是并不意味着insert语法在Hive中没有使用地位了，通常在Hive中我们使用insert+select语句。即插入表的数据来自于后续select查询语句返回的结果。

2.1. insert + select

Hive中insert主要是结合select查询语句使用，将查询结果插入到表中。
例如：

INSERT OVERWRITE将覆盖表或分区中的任何现有数据。
需要保证查询结果列的数目和需要插入数据表格的列数目一致。
如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换失败的数据将会为NULL。

2.2. multiple inserts多重插入

multiple inserts可以翻译成为多次插入，多重插入，核心是：一次扫描，多次插入。其功能也体现出来了就是减少扫描的次数。

-----------multiple inserts---------------------- --当前库下已有一张表student 
select * from student; --创建两张新表 
create table student_insert1(sno int); 
create table student_insert2(sname string); --多重插入 
from student 
insert overwrite table student_insert1 
select num 
insert overwrite table student_insert2 
select name; 

2.3. dynamic partition insert动态分区插入

--动态分区插入
--1、首先设置动态分区模式为非严格模式 默认已经开启了动态分区功能 
set hive.exec.dynamic.partition = true; 
set hive.exec.dynamic.partition.mode = nonstrict; 

--2、当前库下已有一张表student 
select * from student; 

--3、创建分区表 以sdept作为分区字段 
--注意：分区字段名不能和表中的字段名重复。 
create table student_partition(Sno int,Sname string,Sex 
string,Sage int) partitioned by(Sdept string); 

 --4、执行动态分区插入操作 
insert into table student_partition partition(Sdept) 
select Sno,Sname,Sex,Sage,Sdept from student; --其中，Sno,Sname,Sex,Sage作为表的字段内容插入表中 --Sdept作为分区字段值 

最终执行结果如下，可以发现实现了自动分区：

3、insert + directory导出数据

Hive支持将select查询的结果导出成文件存放在文件系统中。语法格式如下：

--标准语法: 
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 
    [ROW FORMAT row_format] [STORED AS file_format] (Note: Only 
available starting with Hive 0.11.0) 
SELECT ... FROM ... 
 --Hive extension (multiple inserts): 
FROM from_statement 
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 
select_statement1 
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 
select_statement2] ... 
 --row_format 
: DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]]
[COLLECTION ITEMS TERMINATED BY char] 
[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] 

注意，导出操作是一个OVERWRITE覆盖操作。慎重。

目录可以是完整的URI。如果未指定scheme或Authority，则Hive将使用hadoop配置变量fs.default.name中的方案和Authority，该变量指定Namenode URI。
如果使用LOCAL关键字，则Hive会将数据写入本地文件系统上的目录。
写入文件系统的数据被序列化为文本，列之间用^ A隔开，行之间用换行符隔开。如果任何列都不是原始类型，那么这些列将序列化为JSON格式。也可以在导出的时候指定分隔符换行符和文件格式。

--当前库下已有一张表student 
select * from student; 

--1、导出查询结果到HDFS指定目录下 
insert overwrite directory '/tmp/hive_export/e1' select * from 
student;

 --2、导出时指定分隔符和文件存储格式 
insert overwrite directory '/tmp/hive_export/e2' row format 
delimited fields terminated by ',' 
stored as orc 
select * from student;

 --3、导出数据到本地文件系统指定目录下 
insert overwrite local directory '/root/hive_export/e1' select * from student; 

五、Hive 数据查询语言（DQL）

1. 基础查询

1.1. 语法树

---------select语法树------------
[WITH CommonTableExpression (, CommonTableExpression)*]
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT [offset,] rows];

table_reference指示查询的输入。它可以是普通物理表，视图，join查询结果或子查询结果。

表名和列名不区分大小写。

1.2. 案例：美国Covid-19新冠select查询

--step1:创建普通表t_usa_covid19
drop table if exists t_usa_covid19;
CREATE TABLE t_usa_covid19(
       count_date string,
       county string,
       state string,
       fips int,
       cases int,
       deaths int)
row format delimited fields terminated by ",";

--将源数据load加载到t_usa_covid19表对应的路径下
load data local inpath '/root/hivedata/us-covid19-counties.dat' into table t_usa_covid19;

--step2:创建一张分区表 基于count_date日期,state州进行分区
CREATE TABLE if not exists t_usa_covid19_p(
     county string,
     fips int,
     cases int,
     deaths int)
partitioned by(count_date string,state string)
row format delimited fields terminated by ",";

--step3:使用动态分区插入将数据导入t_usa_covid19_p中
set hive.exec.dynamic.partition.mode = nonstrict;

insert into table t_usa_covid19_p partition (count_date,state)
select county,fips,cases,deaths,count_date,state from t_usa_covid19;

1.3. select_expr

每个select_expr表示您要检索的列。必须至少有一个 select_expr。

--查询所有字段或者指定字段
select * from t_usa_covid19_p;
select county, cases, deaths from t_usa_covid19_p; -- 列裁剪

--查询匹配正则表达式的所有字段
SET hive.support.quoted.identifiers = none; --反引号不在解释为其他含义，被解释为正则表达式
select `^c.*` from t_usa_covid19_p;

--查询当前数据库
select current_database(); --省去from关键字

--查询使用函数
select count(county) from t_usa_covid19_p;

1.4. ALL 、DISTINCT

--返回所有匹配的行
select state from t_usa_covid19_p;
--相当于
select all state from t_usa_covid19_p;
--返回所有匹配的行 去除重复的结果
select distinct state from t_usa_covid19_p;
--多个字段distinct 整体去重
select  county,state from t_usa_covid19_p;
select distinct county,state from t_usa_covid19_p;
select distinct sex from student;

1.5. WHERE

WHERE条件是一个布尔表达式。在WHERE表达式中，您可以使用Hive支持的任何函数和运算符，但聚合函数除外。

原因：聚合函数要使用它的前提是结果集已经确定，而where子句还处于“确定”结果集的过程中，因而不能使用聚合函数。

select * from t_usa_covid19_p where 1 > 2;  -- 1 > 2 返回false
select * from t_usa_covid19_p where 1 = 1;  -- 1 = 1 返回true
--where条件中使用函数 找出州名字母长度超过10位的有哪些
select * from t_usa_covid19_p where length(state) >10 ;
--where子句支持子查询
SELECT *
FROM A
WHERE A.a IN (SELECT foo FROM B);

可以用having代替聚合函数的查询

--可以使用Having实现
select state,count(deaths)
from t_usa_covid19_p  group by state
having count(deaths) > 100;

1.6. 分区查询、分区裁剪

通常，SELECT查询将扫描整个表（所谓的全表扫描）。如果使用PARTITIONED BY子句创建的分区表，则在查询时可以指定分区查询，减少全表扫描，也叫做分区裁剪。

所谓分区裁剪指的是：对分区表进行查询时，会检查WHERE子句或JOIN中的ON子句中是否存在对分区字段的过滤，如果存在，则仅访问查询符合条件的分区，即裁剪掉没必要访问的分区。

--4、分区查询、分区裁剪
--找出来自加州，累计死亡人数大于1000的县 state字段就是分区字段 进行分区裁剪 避免全表扫描
select * from t_usa_covid19_p where state ="California" and deaths > 1000;
--多分区裁剪
select * from t_usa_covid19_p where count_date = "2021-01-28" and state ="California" and deaths > 1000;

1.7. GROUP BY

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。需要注意的是，出现在GROUP BY中select_expr的字段：要么是GROUP BY分组的字段；要么是被聚合函数应用的字段。原因很简单，避免出现一个字段多个值的歧义。

--5、GROUP BY
--根据state州进行分组
--SemanticException:Expression not in GROUP BY key 'deaths'
--deaths不是分组字段 报错
--state是分组字段 可以直接出现在select_expr中
select state,deaths
from t_usa_covid19_p where count_date = "2021-01-28" group by state;

--被聚合函数应用
select state,sum(deaths)
from t_usa_covid19_p where count_date = "2021-01-28" group by state;

1.8. HAVING

HAVING子句可以让我们筛选分组后的各组数据,并且可以在Having中使用聚合函数，因为此时where，group by已经执行结束，结果集已经确定。

--6、having
--统计死亡病例数大于10000的州
--where语句中不能使用聚合函数 语法报错
select state,sum(deaths)
from t_usa_covid19_p where count_date = "2021-01-28" and sum(deaths) >10000 group by state;

--先where分组前过滤（此处是分区裁剪），再进行group by分组， 分组后每个分组结果集确定 再使用having过滤
select state,sum(deaths)
from t_usa_covid19_p
where count_date = "2021-01-28"
group by state
having sum(deaths) > 10000;

--这样写更好 即在group by的时候聚合函数已经作用得出结果 having直接引用结果过滤 不需要再单独计算一次了
select state,sum(deaths) as cnts
from t_usa_covid19_p
where count_date = "2021-01-28"
group by state
having cnts> 10000;

1.9. LIMIT

LIMIT子句可用于约束SELECT语句返回的行数。

LIMIT接受一个或两个数字参数，这两个参数都必须是非负整数常量。

第一个参数指定要返回的第一行的偏移量（从 Hive 2.0.0开始），第二个参数指定要返回的最大行数。当给出单个参数时，它代表最大行数，并且偏移量默认为0。

--7、limit
--没有限制返回2021.1.28 加州的所有记录
select * from t_usa_covid19_p
where count_date = "2021-01-28"
and state ="California";

--返回结果集的前5条
select * from t_usa_covid19_p
where count_date = "2021-01-28"
  and state ="California"
limit 5;

--返回结果集从第1行开始 共3行
select * from t_usa_covid19_p
where count_date = "2021-01-28"
and state ="California"
limit 2,3; --注意 第一个参数偏移量是从0开始的

1.9. Hive SQL查询执行顺序

from>where>group（含聚合）>having>order>select

2. 高级查询

2.1. SORT/ORDER/CLUSTER/DISTRIBUTE BY

2.1.1. ORDER BY

Hive SQL中的ORDER BY语法类似于SQL语言中的ORDER BY语法。会对输出的结果进行全局排序，因此底层使用MapReduce引擎执行的时候，只会有一个reducetask执行。也因此，如果输出的行数太大，会导致需要很长的时间才能完成全局排序。

---1、order by
--根据字段进行排序
select * from t_usa_covid19_p
where count_date = "2021-01-28"
and state ="California"
order by deaths ; --默认asc, nulls first 也可以手动指定nulls last

select * from t_usa_covid19_p
where count_date = "2021-01-28"
and state ="California"
order by deaths desc; --指定desc nulls last

--强烈建议将LIMIT与ORDER BY一起使用。避免数据集行数过大
--当hive.mapred.mode设置为strict严格模式时，使用不带LIMIT的ORDER BY时会引发异常。
select * from t_usa_covid19_p
where count_date = "2021-01-28"
  and state ="California"
order by deaths desc
limit 3;

2.1.2. CLUSTER BY

Hive SQL中的CLUSTER BY语法可以指定根据后面的字段将数据分组，每组内再根据这个字段正序排序（不允许指定排序规则），概况起来就是：根据同一个字段，分且排序。

分为几组取决于reduce task的个数。下面在Hive beeline客户端中针对student表进行演示。

--2、cluster by
select * from student;
--不指定reduce task个数
--日志显示：Number of reduce tasks not specified. Estimated from input data size: 1
select * from student cluster by num;

--手动设置reduce task个数
set mapreduce.job.reduces =2;
select * from student cluster by num;

执行结果如下：分为两个部分，每个部分内正序排序。

假如说，现在想法如下：把学生表数据根据性别分为两个部分，每个分组内根据年龄的倒序排序。你会发现CLUSTER BY无法完成了。而order by更不能在这里使用，因为它是全局排序，一旦使用order by，编译期间就会强制把reduce task个数设置为1。无法满足分组的需求。

2.1.3. DISTRIBUTE BY + SORT BY

如果说CLUSTER BY的功能是分且排序（同一个字段），那么DISTRIBUTE BY +SORT BY就相当于把cluster by的功能一分为二：DISTRIBUTE BY负责分，SORT BY负责分组内排序，并且可以是不同的字段。如果DISTRIBUTE BY +SORT BY的字段一样，可以得出下列结论：
CLUSTER BY=DISTRIBUTE BY +SORT BY（字段一样）

--3、distribute by +sort by
--案例：把学生表数据根据性别分为两个部分，每个分组内根据年龄的倒序排序。
--错误
select * from student cluster by sex order by age desc;
select * from student cluster by sex sort by age desc;

--正确
select * from student distribute by sex sort by age desc;

--下面两个语句执行结果一样
select * from student distribute by num sort by num;
select * from student cluster by num;

2.2. Union联合查询

UNION用于将来自多个SELECT语句的结果合并为一个结果集。语法如下：

select_statement UNION [ALL | DISTINCT] select_statement UNION [ALL | DISTINCT] select_statement ...;

使用DISTINCT关键字与只使用UNION默认值效果一样，都会删除重复行。

使用ALL关键字，不会删除重复行，结果集包括所有SELECT语句的匹配行（包括重复行）。

1.2.0之前的Hive版本仅支持UNION ALL，在这种情况下不会消除重复的行。

每个select_statement返回的列的数量和名称必须相同。

--使用DISTINCT关键字与使用UNION默认值效果一样，都会删除重复行。
select num,name from student_local
UNION
select num,name from student_hdfs;
--和上面一样
select num,name from student_local
UNION DISTINCT
select num,name from student_hdfs;

--使用ALL关键字会保留重复行。
select num,name from student_local
UNION ALL
select num,name from student_hdfs limit 2;

--如果要将ORDER BY，SORT BY，CLUSTER BY，DISTRIBUTE BY或LIMIT应用于单个SELECT
--请将子句放在括住SELECT的括号内
SELECT num,name FROM (select num,name from student_local LIMIT 2)  subq1
UNION
SELECT num,name FROM (select num,name from student_hdfs LIMIT 3) subq2;

--如果要将ORDER BY，SORT BY，CLUSTER BY，DISTRIBUTE BY或LIMIT子句应用于整个UNION结果
--请将ORDER BY，SORT BY，CLUSTER BY，DISTRIBUTE BY或LIMIT放在最后一个之后。
select num,name from student_local
UNION
select num,name from student_hdfs
order by num desc;

2.3. Common Table Expressions（CTE）

公用表表达式（CTE）是一个临时结果集，该结果集是从WITH子句中指定的简单查询派生而来的，该查询紧接在SELECT或INSERT关键字之前。

CTE仅在单个语句的执行范围内定义。一个或多个CTE可以在Hive SELECT，INSERT， CREATE TABLE AS SELECT或CREATE VIEW AS SELECT语句中使用。

--select语句中的CTE
with q1 as (select num,name,age from student where num = 95002)
select *
from q1;

-- from风格
with q1 as (select num,name,age from student where num = 95002)
from q1
select *;

-- chaining CTEs 链式
with q1 as ( select * from student where num = 95002),
     q2 as ( select num,name,age from q1)
select * from (select num from q2) a;


-- union
with q1 as (select * from student where num = 95002),
     q2 as (select * from student where num = 95004)
select * from q1 union all select * from q2;


-- ctas
create table s2 as
with q1 as ( select * from student where num = 95002)
select * from q1;

-- view
create view v1 as
with q1 as ( select * from student where num = 95002)
select * from q1;

select * from v1;

3. join 连接查询

根据数据库的三范式设计要求和日常工作习惯来说，我们通常不会设计一张大表把所有类型的数据都放在一起，而是不同类型的数据设计不同的表存储。在这种情况下，有时需要基于多张表查询才能得到最终完整的结果，SQL中join语法的出现是用于根据两个或多个表中的列之间的关系，从这些表中共同组合查询数据，因此有时为了得到完整的结果，我们就需要执行 join。

Hive作为面向分析的数据仓库软件，为了更好的支持数据分析的功能丰富，也实现了join的语法，整体上来看和RDBMS中的join语法类似，只不过在某些点有自己的特色。需要特别注意。

3.1. Hive join语法

在Hive中，当下版本3.1.2总共支持6种join语法。分别是：
inner join（内连接）、left join（左连接）、right join（右连接）、full outer join（全外连接）、left semi join（左半开连接）、cross join（交叉连接，也叫做笛卡尔乘积）。

join_table:
    table_reference [INNER] JOIN table_factor [join_condition]
  | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
  | table_reference LEFT SEMI JOIN table_reference join_condition
  | table_reference CROSS JOIN table_reference [join_condition] (as of Hive 0.10)

join_condition:
    ON expression
-------------------
--隐式联接表示法
SELECT *
FROM table1 t1, table2 t2, table3 t3
WHERE t1.id = t2.id AND t2.id = t3.id AND t1.zipcode = '02535';

--支持非等值连接
SELECT a.* FROM a JOIN b ON (a.id = b.id)
SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)
SELECT a.* FROM a LEFT OUTER JOIN b ON (a.id <> b.id)
-------------------

3.2. inner join

内连接，只有进行连接的两个表中都存在与连接条件相匹配的数据才会被留下来。

--1、inner join
select e.id,e.name,e_a.city,e_a.street
from employee e inner join employee_address e_a
on e.id =e_a.id;
--等价于 inner join=join
select e.id,e.name,e_a.city,e_a.street
from employee e join employee_address e_a
on e.id =e_a.id;

--等价于 隐式连接表示法
select e.id,e.name,e_a.city,e_a.street
from employee e , employee_address e_a
where e.id =e_a.id;

3.3. left join

eft join中文叫做是左外连接(Left Outer Jion)或者左连接，其中outer可以省略，left outer join是早期的写法。

通俗解释：join时以左表的全部数据为准，右边与之关联；左表数据全部返回，右表关联上的显示返回，关联不上的显示null返回。

--2、left join
select e.id,e.name,e_conn.phno,e_conn.email
from employee e left join employee_connection e_conn
on e.id =e_conn.id;

--等价于 left outer join
select e.id,e.name,e_conn.phno,e_conn.email
from employee e left outer join  employee_connection e_conn
on e.id =e_conn.id;

3.4. right join

right join中文叫做是右外连接(Right Outer Jion)或者右连接，其中outer可以省略。

通俗解释：join时以右表的全部数据为准，左边与之关联；右表数据全部返回，左表关联上的显示返回，关联不上的显示null返回。

--3、right join
select e.id,e.name,e_conn.phno,e_conn.email
from employee e right join employee_connection e_conn
on e.id =e_conn.id;

--等价于 right outer join
select e.id,e.name,e_conn.phno,e_conn.email
from employee e right outer join employee_connection e_conn
on e.id =e_conn.id;

3.5. full outer join

full outer join 等价 full join ,中文叫做全外连接或者外连接。

包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行 在功能上，它等价于对这两个数据集合分别进行左外连接和右外连接，然后再使用消去重复行的操作将上述两个结果集合并为一个结果集。

--4、full outer join
select e.id,e.name,e_a.city,e_a.street
from employee e full outer join employee_address e_a
on e.id =e_a.id;
--等价于
select e.id,e.name,e_a.city,e_a.street
from employee e full  join employee_address e_a
on e.id =e_a.id;

3.6. left semi join

左半开连接（LEFT SEMI JOIN）会返回左边表的记录，前提是其记录对于右边的表满足ON语句中的判定条件。

从效果上来看有点像inner join之后只返回左表的结果。

--5、left semi join
select *
from employee e left semi join employee_address e_addr
on e.id =e_addr.id;

--相当于 inner join,但是只返回左表全部数据， 只不过效率高一些
select e.*
from employee e inner join employee_address e_addr
on e.id =e_addr.id;

3.7. left cross join

交叉连接cross join，将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积。对于大表来说，cross join慎用。

在HiveSQL语法中，cross join 后面可以跟where子句进行过滤，或者on条件过滤。

--6、cross join
--下列A、B、C 执行结果相同，但是效率不一样：
--A:
select a.*,b.* from employee a,employee_address b where a.id=b.id;
--B:
select * from employee a cross join employee_address b on a.id=b.id;
select * from employee a cross join employee_address b where a.id=b.id;
--C:
select * from employee a inner join employee_address b on a.id=b.id;

--一般不建议使用方法A和B，因为如果有WHERE子句的话，往往会先生成两个表行数乘积的行的数据表然后才根据WHERE条件从中选择。
--因此，如果两个需要求交集的表太大，将会非常非常慢，不建议使用。

3.8. Hive join使用注意事项

总体来说，随着Hive的版本发展，join语法的功能也愈加丰富。当下我们课程使用的是3.1.2版本，有以下几点需要注意：

1. 允许使用复杂的联接表达式

   SELECT a.* FROM a JOIN b ON (a.id = b.id)
   SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)
   SELECT a.* FROM a LEFT OUTER JOIN b ON (a.id <> b.id)
   

2. 同一查询中可以连接2个以上的表

   SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
   

3. 如果每个表在联接子句中使用相同的列，则Hive将多个表上的联接转换为单个MR作业

   SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)
   --由于联接中仅涉及b的key1列，因此被转换为1个MR作业来执行
   SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
   --会转换为两个MR作业，因为在第一个连接条件中使用了b中的key1列，而在第二个连接条件中使用了b中的key2列。
   -- 第一个map / reduce作业将a与b联接在一起，然后将结果与c联接到第二个map / reduce作业中。
   

4. join时的最后一个表会通过reducer流式传输，并在其中缓冲之前的其他表，因此，将大表放置在最后有助于减少reducer阶段缓存数据所需要的内存

   SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)
   --由于联接中仅涉及b的key1列，因此被转换为1个MR作业来执行，并且表a和b的键的特定值的值被缓冲在reducer的内存中。然后，对于从c中检索的每一行，将使用缓冲的行来计算联接。
   SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
   --计算涉及两个MR作业。其中的第一个将a与b连接起来，并缓冲a的值，同时在reducer中流式传输b的值。
   -- 在第二个MR作业中，将缓冲第一个连接的结果，同时将c的值通过reducer流式传输。
   

5. join在WHERE条件之前进行。

   SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)
   --a,b,c三个表都在一个MR作业中联接，并且表b和c的键的特定值的值被缓冲在reducer的内存中。
   -- 然后，对于从a中检索到的每一行，将使用缓冲的行来计算联接。如果省略STREAMTABLE提示，则Hive将流式传输最右边的表。
   

6. 如果除一个要连接的表之外的所有表都很小，则可以将其作为仅map作业执行

   SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a JOIN b ON a.key = b.key
   --不需要reducer。对于A的每个Mapper，B都会被完全读取。限制是不能执行FULL / RIGHT OUTER JOIN b。
   

还有一些其他相关的使用注意事项，可以参考官方：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins

六、Hive 参数配置

6.1. CLIs and Commands客户端和命令

6.1.1. Hive CLI

$HIVE_HOME/bin/hive是一个shellUtil,通常称之为hive的第一代客户端或者旧客户端，主要功能有两个：
一：用于以交互式或批处理模式运行Hive查询，注意，此时作为客户端，需要并且能够访问的是Hive metastore服务，而不是hiveserver2服务。
二：用于hive相关服务的启动，比如metastore服务。

1. Batch Mode 批处理模式：
   

   #-e 
   $HIVE_HOME/bin/hive -e 'show databases' 
   
   #-f 
   cd ~
   #编辑一个sql文件 里面写上合法正确的sql语句 
   vim hive.sql 
   show databases; 
   #执行 从客户端所在机器的本地磁盘加载文件 
   $HIVE_HOME/bin/hive -f /root/hive.sql 
   #也可以从其他文件系统加载sql文件执行 
   $HIVE_HOME/bin/hive -f hdfs://<namenode>:<port>/hive-script.sql 
   $HIVE_HOME/bin/hive -f s3://mys3bucket/s3-script.sql 
   
   #使用静默模式将数据从查询中转储到文件中
    $HIVE_HOME/bin/hive -S -e 'select * from itheima.student' > a.txt 
   

   当使用==-e或-f==选项运行$ HIVE_HOME / bin / hive时，它将以批处理模式执行SQL命令。所谓的批处理可以理解为一次性执行，执行完毕退出。

   -f调用sql文件执行的方式就是企业中hive生产环境主流的调用方式。

2. Interactive Shell 交互式模式：
   所谓交互式模式可以理解为客户端和hive服务一直保持连接，除非手动退出客户端。

   /export/server/hive/bin/hive 
   hive> show databases; 
   OK 
   default 
   itcast 
   itheima 
   Time taken: 0.028 seconds, Fetched: 3 row(s) 
   hive> use itcast; 
   OK 
   Time taken: 0.027 seconds 
   hive> exit; 
   

3. 启动服务、修改配置
   远程模式部署方式下，hive metastore服务需要单独配置手动启动，此时就可以使用Hive CLI来进行相关服务的启动，hiveserver2服务类似。

   #--service 
   $HIVE_HOME/bin/hive --service metastore 
   $HIVE_HOME/bin/hive --service hiveserver2 
   #--hiveconf 
   $HIVE_HOME/bin/hive --hiveconf hive.root.logger=DEBUG,console 
   

6.1.2. Beeline CLI

$HIVE_HOME/bin/beeline被称之为第二代客户端或者新客户端，是一个JDBC客户端，是官方强烈推荐使用的Hive命令行工具，和第一代客户端相比，性能加强安全性提高。Beeline在嵌入式模式和远程模式下均可工作。

远程模式下beeline通过Thrift连接到单独的HiveServer2服务上，这也是官方推荐在生产环境中使用的模式。

[root@node3 ~]# /export/server/hive/bin/beeline  
Beeline version 3.1.2 by Apache Hive 
beeline> ! connect jdbc:hive2://node1:10000 
Connecting to jdbc:hive2://node1:10000 
Enter username for jdbc:hive2://node1:10000: root 
Enter password for jdbc:hive2://node1:10000:  
Connected to: Apache Hive (version 3.1.2) 
Driver: Hive JDBC (version 3.1.2) 
Transaction isolation: TRANSACTION_REPEATABLE_READ 
0: jdbc:hive2://node1:10000> 

beeline支持的参数非常多，可以通过官方文档进行查询 ：
https://cwiki.apache.org/confluence/display/Hive/HiveServer2+Clients#HiveServer2Clients-Beeline%E2%80%93NewCommandLineShell

6.2. Configuration Properties 配置属性

6.2.1. 配置属性概述

Hive作为一款复杂的数据仓库软件，除了一些默认的属性行为之外，还支持用户配置属性进行修改，使得在某些场景下满足用户的需求。

作为用户我们需要掌握两件事：
一是：Hive有哪些属性支持修改，修改了有什么功能；
二是：Hive支持哪种方式进行修改，修改是临时生效还是永久生效的。

Hive配置属性的规范列表是在HiveConf.Java类中管理的，因此请参考该HiveConf.java文件，以获取Hive当前使用的发行版中可用的配置属性的完整列表。从Hive 0.14.0开始，会从HiveConf.java类中直接生成配置模板文件hive-default.xml.template，它是当前版本配置及其默认值的可靠来源。

详细的配置参数大全可以参考Hive官网配置参数，在页面使用ctrl+f进行搜索。
https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties

6.2.2. 修改配置属性方式

1. hive-site.xml配置文件：
   在$HIVE_HOME/conf路径下，可以添加一个hive-site.xml文件，把需要定义修改的配置属性添加进去，这个配置文件会影响到这个Hive安装包的任何一种服务启动、客户端使用方式，可以理解为是Hive的全局配置。

2. hiveconf命令行参数 ：
   hiveconf是一个命令行的参数，用于在使用Hive CLI或者Beeline CLI的时候指定配置参数。这种方式的配置在整个的会话session中有效，会话结束，失效。
   比如在启动hive服务的时候，为了更好的查看启动详情，可以通过hiveconf参数修改日志级别：

   $HIVE_HOME/bin/hive --hiveconf hive.root.logger=DEBUG,console 
   

3. set命令：
   在Hive CLI或Beeline中使用set命令为set命令之后的所有SQL语句设置配置参数，这个也是会话级别的。
   这种方式也是用户日常开发中使用最多的一种配置参数方式。因为Hive倡导一种：谁需要、谁配置、谁使用的一种思想，避免你的属性修改影响其他用户的修改。

4. 服务器特定的配置文件：
   您可以设置特定metastore的配置值hivemetastore-site.xml中，并在HiveServer2特定的配置值hiveserver2-site.xml中。

总结：配置文件的优先顺序如下，后面的优先级越高：
hive-site.xml-> hivemetastore-site.xml-> hiveserver2-site.xml->’ -hiveconf’命令行参数

七、Hive 内置运算符

官方链接：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

也可以使用课程附件中的中文版本运算符函数说明文档进行查看。

--显示所有的函数和运算符 
show functions; --查看运算符或者函数的使用说明 
describe function +; --使用extended 可以查看更加详细的使用说明 
describe function extended +; 

从Hive 0.13.0开始，select查询语句FROM关键字是可选的（例如SELECT 1+1）。因此可以使用这种方式来练习测试内置的运算符、函数的功能。

除此之外，还可以通过创建一张虚表dual来满足于测试需求。

--1、创建表dual
create table dual(id string);
--2、加载一个文件dual.txt到dual表中
--dual.txt只有一行内容：内容为一个空格
load data local inpath '/root/hivedata/dual.txt' into table dual;
--3、在select查询语句中使用dual表完成运算符、函数功能测试
select 1+1 from dual;

7.1. 关系运算符

关系运算符是二元运算符，执行的是两个操作数的比较运算。每个关系运算符都返回boolean类型结果（TRUE或FALSE）。

•等值比较: = 、== 
•不等值比较: <> 、!= 
•小于比较: < 
•小于等于比较: <= 
•大于比较: > 
•大于等于比较: >= 
•空值判断: IS NULL  
•非空判断: IS NOT NULL 
•LIKE比较: LIKE 
•JAVA的LIKE操作: RLIKE 
•REGEXP操作: REGEXP --正则表达式

--is null空值判断
select 1 from dual where 'itcast' is null;

--is not null 非空值判断
select 1 from dual where 'itcast' is not null;

--like比较： _表示任意单个字符 %表示任意数量字符
--否定比较： NOT A like B
select 1 from dual where 'itcast' like 'it_';
select 1 from dual where 'itcast' like 'it%';
select 1 from dual where  'itcast' not like 'hadoo_';
select 1 from dual where  not 'itcast' like 'hadoo_';

--rlike：确定字符串是否匹配正则表达式，是REGEXP_LIKE()的同义词。
select 1 from dual where 'itcast' rlike '^i.*t$';
select 1 from dual where '123456' rlike '^\\d+$';  --判断是否全为数字
select 1 from dual where '123456aa' rlike '^\\d+$';

--regexp：功能与rlike相同 用于判断字符串是否匹配正则表达式
select 1 from dual where 'itcast' regexp '^i.*t$';

7.2. 算术运算符

算术运算符操作数必须是数值类型。 分为一元运算符和二元运算符; 一元运算符,只有一个操作数; 二元运算符有两个操作数,运算符在两个操作数之间。

•加法操作: + 
•减法操作: - 
•乘法操作: * 
•除法操作: / 
•取整操作: div 
•取余操作: % 
•位与操作: & 
•位或操作: | 
•位异或操作: ^ 
•位取反操作: ~ 

--取整操作: div  给出将A除以B所得的整数部分。例如17 div 3得出5。
select 17 div 3;

--取余操作: %  也叫做取模mod  A除以B所得的余数部分
select 17 % 3;

--位与操作: &  A和B按位进行与操作的结果。 与表示两个都为1则结果为1
select 4 & 8 from dual;  --4转换二进制：0100 8转换二进制：1000
select 6 & 4 from dual;  --4转换二进制：0100 6转换二进制：0110

--位或操作: |  A和B按位进行或操作的结果  或表示有一个为1则结果为1
select 4 | 8 from dual;
select 6 | 4 from dual;

--位异或操作: ^ A和B按位进行异或操作的结果 异或表示两者的值不同,则结果为1
select 4 ^ 8 from dual;
select 6 ^ 4 from dual;

7.3. 逻辑运算符

•与操作: A AND B 
•或操作: A OR B 
•非操作: NOT A 、!A 
•在:A IN (val1, val2, ...) 
•不在:A NOT IN (val1, val2, ...) 
•逻辑是否存在: [NOT] EXISTS (subquery) 

--与操作: A AND B   如果A和B均为TRUE，则为TRUE，否则为FALSE。如果A或B为NULL，则为NULL。
select 1 from dual where 3>1 and 2>1;
--或操作: A OR B   如果A或B或两者均为TRUE，则为TRUE，否则为FALSE。
select 1 from dual where 3>1 or 2!=2;
--非操作: NOT A 、!A   如果A为FALSE，则为TRUE；如果A为NULL，则为NULL。否则为FALSE。
select 1 from dual where not 2>1;
select 1 from dual where !2=1;
--在:A IN (val1, val2, ...)  如果A等于任何值，则为TRUE。
select 1 from dual where 11  in(11,22,33);
--不在:A NOT IN (val1, val2, ...) 如果A不等于任何值，则为TRUE
select 1 from dual where 11 not in(22,33,44);

八、Hive 内置函数

可以使用show functions查看当下版本支持的函数，并且可以通过

describe function extended funcname

来查看函数的使用方式和方法。

官方文档地址：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

8.1. String Functions 字符串函数

•字符串长度函数：length 
•字符串反转函数：reverse 
•字符串连接函数：concat 
•带分隔符字符串连接函数：concat_ws 
•字符串截取函数：substr,substring 
•字符串转大写函数：upper,ucase 
•字符串转小写函数：lower,lcase 
•去空格函数：trim 
•左边去空格函数：ltrim 
•右边去空格函数：rtrim 
•正则表达式替换函数：regexp_replace 
•正则表达式解析函数：regexp_extract 
•URL解析函数：parse_url 
•json解析函数：get_json_object 
•空格字符串函数：space 
•重复字符串函数：repeat 
•首字符ascii函数：ascii 
•左补足函数：lpad 
•右补足函数：rpad 
•分割字符串函数: split 
•集合查找函数: find_in_set 

select concat("angela","baby");
--带分隔符字符串连接函数：concat_ws(separator, [string | array(string)]+)
select concat_ws('.', 'www', array('itcast', 'cn'));
--字符串截取函数：substr(str, pos[, len]) 或者  substring(str, pos[, len])
select substr("angelababy",-2); --pos是从1开始的索引，如果为负数则倒着数
select substr("angelababy",2,2);
--正则表达式替换函数：regexp_replace(str, regexp, rep)
select regexp_replace('100-200', '(\\d+)', 'num');
--正则表达式解析函数：regexp_extract(str, regexp[, idx]) 提取正则匹配到的指定组内容
select regexp_extract('100-200', '(\\d+)-(\\d+)', 2);
--URL解析函数：parse_url 注意要想一次解析出多个 可以使用parse_url_tuple这个UDTF函数
select parse_url('http://www.itcast.cn/path/p1.php?query=1', 'HOST');
--分割字符串函数: split(str, regex)
select split('apache hive', '\\s+');
--json解析函数：get_json_object(json_txt, path)
--$表示json对象
select get_json_object('[{"website":"www.itcast.cn","name":"allenwoon"}, {"website":"cloud.itcast.com","name":"carbondata 中文文档"}]', '$.[1].website');

--字符串长度函数：length(str | binary)
select length("angelababy");
--字符串反转函数：reverse
select reverse("angelababy");
--字符串连接函数：concat(str1, str2, ... strN)
--字符串转大写函数：upper,ucase
select upper("angelababy");
select ucase("angelababy");
--字符串转小写函数：lower,lcase
select lower("ANGELABABY");
select lcase("ANGELABABY");
--去空格函数：trim 去除左右两边的空格
select trim(" angelababy ");
--左边去空格函数：ltrim
select ltrim(" angelababy ");
--右边去空格函数：rtrim
select rtrim(" angelababy ");
--空格字符串函数：space(n) 返回指定个数空格
select space(4);
--重复字符串函数：repeat(str, n) 重复str字符串n次
select repeat("angela",2);
--首字符ascii函数：ascii
select ascii("angela");  --a对应ASCII 97
--左补足函数：lpad
select lpad('hi', 5, '??');  --???hi
select lpad('hi', 1, '??');  --h
--右补足函数：rpad
select rpad('hi', 5, '??');
--集合查找函数: find_in_set(str,str_array)
select find_in_set('a','abc,b,ab,c,def');

8.2. Date Functions 日期函数

主要针对时间、日期数据类型进行操作，比如下面这些：

•获取当前日期: current_date 
•获取当前时间戳: current_timestamp 
•UNIX时间戳转日期函数: from_unixtime 
•获取当前UNIX时间戳函数: unix_timestamp 
•日期转UNIX时间戳函数: unix_timestamp 
•指定格式日期转UNIX时间戳函数: unix_timestamp 
•抽取日期函数: to_date 
•日期转年函数: year 
•日期转月函数: month 
•日期转天函数: day 
•日期转小时函数: hour 
•日期转分钟函数: minute 
•日期转秒函数: second 
•日期转周函数: weekofyear 
•日期比较函数: datediff 
•日期增加函数: date_add 
•日期减少函数: date_sub 

--获取当前日期: current_date
select current_date();
--获取当前时间戳: current_timestamp
--同一查询中对current_timestamp的所有调用均返回相同的值。
select current_timestamp();
--获取当前UNIX时间戳函数: unix_timestamp
select unix_timestamp();
--日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp("2011-12-07 13:01:03");
--指定格式日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss');
--UNIX时间戳转日期函数: from_unixtime
select from_unixtime(1618238391);
select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss');
--日期比较函数: datediff  日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
select datediff('2012-12-08','2012-05-09');
--日期增加函数: date_add
select date_add('2012-02-28',10);
--日期减少函数: date_sub
select date_sub('2012-01-1',10);


--抽取日期函数: to_date
select to_date('2009-07-30 04:17:52');
--日期转年函数: year
select year('2009-07-30 04:17:52');
--日期转月函数: month
select month('2009-07-30 04:17:52');
--日期转天函数: day
select day('2009-07-30 04:17:52');
--日期转小时函数: hour
select hour('2009-07-30 04:17:52');
--日期转分钟函数: minute
select minute('2009-07-30 04:17:52');
--日期转秒函数: second
select second('2009-07-30 04:17:52');
--日期转周函数: weekofyear 返回指定日期所示年份第几周
select weekofyear('2009-07-30 04:17:52');

8.3. Mathematical Functions 数学函数

主要针对数值类型的数据进行数学计算，比如下面这些：

•取整函数: round 
•指定精度取整函数: round 
•向下取整函数: floor 
•向上取整函数: ceil 
•取随机数函数: rand 
•二进制函数: bin 
•进制转换函数: conv 
•绝对值函数: abs 

--取整函数: round  返回double类型的整数值部分 （遵循四舍五入）
select round(3.1415926);
--指定精度取整函数: round(double a, int d) 返回指定精度d的double类型
select round(3.1415926,4);
--向下取整函数: floor
select floor(3.1415926);
select floor(-3.1415926);
--向上取整函数: ceil
select ceil(3.1415926);
select ceil(-3.1415926);
--取随机数函数: rand 每次执行都不一样 返回一个0到1范围内的随机数
select rand();
--指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列
select rand(3);

8.4. Collection Functions 集合函数

主要针对集合这样的复杂数据类型进行操作，比如下面这些：

•集合元素size函数: size(Map<K.V>) size(Array<T>) 
•取map集合keys函数: map_keys(Map<K.V>) 
•取map集合values函数: map_values(Map<K.V>) 
•判断数组是否包含指定元素: array_contains(Array<T>, value) 
•数组排序函数:sort_array(Array<T>) 

-------Collection Functions 集合函数--------------
--集合元素size函数: size(Map<K.V>) size(Array<T>)
select size(`array`(11,22,33));
select size(`map`("id",10086,"name","zhangsan","age",18));

--取map集合keys函数: map_keys(Map<K.V>)
select map_keys(`map`("id",10086,"name","zhangsan","age",18));

--取map集合values函数: map_values(Map<K.V>)
select map_values(`map`("id",10086,"name","zhangsan","age",18));

--判断数组是否包含指定元素: array_contains(Array<T>, value)
select array_contains(`array`(11,22,33),11);
select array_contains(`array`(11,22,33),66);

--数组排序函数:sort_array(Array<T>)
select sort_array(`array`(12,2,32));

8.5. Conditional Functions 条件函数

主要用于条件判断、逻辑判断转换这样的场合，比如：

•if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull) 
•空判断函数: isnull( a ) 
•非空判断函数: isnotnull ( a ) 
•空值转换函数: nvl(T value, T default_value) 
•非空查找函数: COALESCE(T v1, T v2, ...) 
•条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END 
•nullif( a, b ): 如果a = b，则返回NULL；否则返回NULL。否则返回一个 
•assert_true: 如果'condition'不为真，则引发异常，否则返回null 

--if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
select if(1=2,100,200);
select if(sex ='男','M','W') from student limit 3;

--空判断函数: isnull( a )
select isnull("allen");
select isnull(null);

--非空判断函数: isnotnull ( a )
select isnotnull("allen");
select isnotnull(null);

--空值转换函数: nvl(T value, T default_value)
select nvl("allen","itcast");
select nvl(null,"itcast");

--非空查找函数: COALESCE(T v1, T v2, ...)
--返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL
select COALESCE(null,11,22,33);
select COALESCE(null,null,null,33);
select COALESCE(null,null,null);

--条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;
select case sex when '男' then 'male' else 'female' end from student limit 3;

8.6. Type Conversion Functions 类型转换函数

主要用于显式的数据类型转换，有下面两种函数：

--任意数据类型之间转换:cast
select cast(12.14 as bigint);
select cast(12.14 as string);
select cast("hello" as int);

8.7. Data Masking Functions 数据脱敏函数

主要完成对数据脱敏转换功能，屏蔽原始数据，主要如下：

•mask 
•mask_first_n(string str[, int n] 
•mask_last_n(string str[, int n]) 
•mask_show_first_n(string str[, int n]) 
•mask_show_last_n(string str[, int n]) 
•mask_hash(string|char|varchar str) 

--mask
--将查询回的数据，大写字母转换为X，小写字母转换为x，数字转换为n。
select mask("abc123DEF");
select mask("abc123DEF",'-','.','^'); --自定义替换的字母

--mask_first_n(string str[, int n]
--对前n个进行脱敏替换
select mask_first_n("abc123DEF",4);

--mask_last_n(string str[, int n])
select mask_last_n("abc123DEF",4);

--mask_show_first_n(string str[, int n])
--除了前n个字符，其余进行掩码处理
select mask_show_first_n("abc123DEF",4);

--mask_show_last_n(string str[, int n])
select mask_show_last_n("abc123DEF",4);

--mask_hash(string|char|varchar str)
--返回字符串的hash编码。
select mask_hash("abc123DEF");

8.7. Data Masking Functions 数据脱敏函数

•hive调用java方法: java_method(class, method[, arg1[, arg2..]]) 
•反射函数: reflect(class, method[, arg1[, arg2..]]) 
•取哈希值函数:hash 
•current_user()、logged_in_user()、current_database()、version() 
•SHA-1加密: sha1(string/binary) 
•SHA-2家族算法加密：sha2(string/binary, int)  (SHA-224, SHA-256, SHA-384, 
SHA-512) 
•crc32加密: 
•MD5加密: md5(string/binary) 

--如果你要调用的java方法所在的jar包不是hive自带的 可以使用add jar添加进来
--hive调用java方法: java_method(class, method[, arg1[, arg2..]])
select java_method("java.lang.Math","max",11,22);

--反射函数: reflect(class, method[, arg1[, arg2..]])
select reflect("java.lang.Math","max",11,22);

--取哈希值函数:hash
select hash("allen");

--current_user()、logged_in_user()、current_database()、version()

--SHA-1加密: sha1(string/binary)
select sha1("allen");

--SHA-2家族算法加密：sha2(string/binary, int)  (SHA-224, SHA-256, SHA-384, SHA-512)
select sha2("allen",224);
select sha2("allen",512);

--crc32加密:
select crc32("allen");

--MD5加密: md5(string/binary)
select md5("allen");

九、Hive 函数高阶

9.1. UDTF之explode函数

对于UDTF表生成函数，很多人难以理解什么叫做输入一行，输出多行。

为什么叫做表生成？能够产生表吗？下面我们就来学习Hive当做内置的一个非常著名的UDTF函数，名字叫做explode函数，中文戏称之为“爆炸函数”，可以炸开数据。

explode函数接收map或者array类型的数据作为参数，然后把参数中的每个元素炸开变成一行数据。一个元素一行。这样的效果正好满足于输入一行输出多行。

explode(array)将array列表里的每个元素生成一行；
explode(map)将map里的每一对元素作为一行，其中key为一列，value为一列；

一般情况下，explode函数可以直接使用即可，也可以根据需要结合lateral view侧视图使用。


下面做一个案例：NBA总冠军球队名单
有一份数据《The_NBA_Championship.txt》，关于部分年份的NBA总冠军球队名单：

需求：使用Hive建表映射成功数据，对数据拆分，要求拆分之后数据如下所示：

并且最好根据年份的倒序进行排序。

--step1:建表
create table the_nba_championship(
           team_name string,
           champion_year array<string>
) row format delimited
fields terminated by ','
collection items terminated by '|';

--step2:加载数据文件到表中
load data local inpath '/root/hivedata/The_NBA_Championship.txt' into table the_nba_championship;

--step3:验证
select *
from the_nba_championship;

下面使用explode函数：

--step4:使用explode函数对champion_year进行拆分 俗称炸开
select explode(champion_year) from the_nba_championship;

--想法是正确的 sql执行确实错误的
select team_name,explode(champion_year) from the_nba_championship;

在select条件中，如果只有explode函数表达式，程序执行是没有任何问题的； 但是如果在select条件中，包含explode和其他字段，就会报错。
如果数据不是map或者array如何使用explode函数呢？
想方设法使用split、subsrt、regex_replace等函数组合使用，把数据变成array或者map。

explode语法限制原因

1. explode函数属于UDTF函数，即表生成函数；
2. explode函数执行返回的结果可以理解为一张虚拟的表，其数据来源于源表；
3. 在select中只查询源表数据没有问题，只查询explode生成的虚拟表数据也没问题
4. 但是不能在只查询源表的时候，既想返回源表字段又想返回explode生成的虚拟表字段
5. 通俗点讲，有两张表，不能只查询一张表但是返回分别属于两张表的字段；
6. 从SQL层面上来说应该对两张表进行关联查询
7. Hive专门提供了语法lateral View侧视图，专门用于搭配explode这样的UDTF函数，以满足上述需要。

select a.team_name,b.year
from the_nba_championship a lateral view explode(champion_year) b as year
order by b.year desc;

9.2. Lateral View 侧视图

Lateral View是一种特殊的语法，主要用于搭配UDTF类型功能的函数一起使用，用于解决UDTF函数的一些查询限制的问题。

侧视图的原理是将UDTF的结果构建成一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表。这样就避免了UDTF的使用限制问题。使用lateral view时也可以对UDTF产生的记录设置字段名称，产生的字段可以用于group by、order by 、limit等语句中，不需要再单独嵌套一层子查询。

一般只要使用UDTF，就会固定搭配lateral view使用。

官方链接：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LateralView

--lateral view侧视图基本语法如下
select …… from tabelA lateral view UDTF(xxx) 别名 as col1,col2,col3……;

继续完成上一节的案例：

select a.team_name ,b.year
from the_nba_championship a lateral view explode(champion_year) b as year;

--根据年份倒序排序
select a.team_name ,b.year
from the_nba_championship a lateral view explode(champion_year) b as year
order by b.year desc;

--统计每个球队获取总冠军的次数 并且根据倒序排序
select a.team_name ,count(*) as nums
from the_nba_championship a lateral view explode(champion_year) b as year
group by a.team_name
order by nums desc;

lateral view相当于join，就是把explode生成的虚拟表与原表匹配起来。

9.3. 行列转换应用与实现

9.3.1. 工作应用场景

实际工作场景中经常需要实现对于Hive中的表进行行列转换操作，例如统计得到每个小时不同维度下的UV、PV、IP的个数，而现在为了构建可视化报表，得到每个小时的UV、PV的线图，观察访问趋势，我们需要构建如下的表结构：

在Hive中，我们可以通过函数来实现各种复杂的行列转换。

9.3.2. 行转列：多行转单列

concat

功能：用于实现字符串拼接，不可指定分隔符

concat(element1,element2,element3……) 

特点：如果任意一个元素为null，结果就为null

concat_ws

功能：用于实现字符串拼接，可以指定分隔符

concat_ws(SplitChar，element1，element2……)

特点：任意一个元素不为null，结果就不为null

collect_list

功能：用于将一列中的多行合并为一行，不进行去重

collect_list（colName） 

concat_set

功能：用于将一列中的多行合并为一行，并进行去重

collect_set（colName） 

实现目标

--建表
create table row2col2(
                         col1 string,
                         col2 string,
                         col3 int
)row format delimited fields terminated by '\t';

--加载数据到表中
load data local inpath '/root/hivedata/r2c2.txt' into table row2col2;

select
    col1,
    col2,
    concat_ws(',', collect_list(cast(col3 as string))) as col3
from
    row2col2
group by
    col1, col2;

注意col3是int类型，需要用cast函数转换成字符串类型再拼接

9.3.3. 列转行：单列转多行

explode

功能：用于将一个集合或者数组中的每个元素展开，将每个元素变成一行

explode( Map | Array) 

实现目标

--创建表
create table col2row2(
                         col1 string,
                         col2 string,
                         col3 string
)row format delimited fields terminated by '\t';

--加载数据
load data local inpath '/root/hivedata/c2r2.txt' into table col2row2;

--SQL最终实现
select
    col1,
    col2,
    lv.col3 as col3
from
    col2row2
        lateral view
            explode(split(col3, ',')) lv as col3;

因为col3是string类型，那么我们可以通过split函数来切割将col3变成一个集合类型。

9.4. JSON数据处理

9.4.1. 应用场景

JSON数据格式是数据存储及数据处理中最常见的结构化数据格式之一，很多场景下公司都会将数据以JSON格式存储在HDFS中，当构建数据仓库时，需要对JSON格式的数据进行处理和分析，那么就需要在Hive中对JSON格式的数据进行解析读取。

例如，当前我们JSON格式的数据如下：

每条数据都以JSON形式存在，每条数据中都包含4个字段，分别为设备名称【device】、设备类型【deviceType】、信号强度【signal】和信号发送时间【time】，现在我们需要将这四个字段解析出来，在Hive表中以每一列的形式存储，最终得到以下Hive表：

9.4.2. 处理方式

Hive中为了实现JSON格式的数据解析，提供了两种解析JSON数据的方式，在实际工作场景下，可以根据不同数据，不同的需求来选择合适的方式对JSON格式数据进行处理。

• 方式一：使用JSON函数进行处理 ：

  Hive中提供了两个专门用于解析JSON字符串的函数：get_json_object、json_tuple，这两个函数都可以实现将JSON数据中的每个字段独立解析出来，构建成表。

• 方式二：使用Hive内置的JSON Serde加载数据

  Hive中除了提供JSON的解析函数以外，还提供了一种专门用于加载JSON文件的Serde来实现对JSON文件中数据的解析，在创建表时指定Serde，加载文件到表中，会自动解析为对应的表格式。
  
  

9.4.3. JSON 函数：get_json_object

功能：用于解析JSON字符串，可以从JSON字符串中返回指定的某个对象列的值。

get_json_object(json_txt, path) - Extract a json object from path

• 第一个参数：指定要解析的JSON字符串
• 第二个参数：指定要返回的字段，通过$.columnName的方式来指定path

特点：每次只能返回JSON对象中一列的值

create table tb_json_test1 (
    json string
);

--加载数据
load data local inpath '/root/hivedata/device.json' into table tb_json_test1;

select
    --获取设备名称
    get_json_object(json,"$.device") as device,
    --获取设备类型
    get_json_object(json,"$.deviceType") as deviceType,
    --获取设备信号强度
    get_json_object(json,"$.signal") as signal,
    --获取时间
    get_json_object(json,"$.time") as stime
from tb_json_test1;

9.4.4. JSON 函数：JSON 函数：json_tuple

功能：用于实现JSON字符串的解析，可以通过指定多个参数来解析JSON返回多列的值

json_tuple(jsonStr, p1, p2, ..., pn)  
like get_json_object, but it takes multiple names and return a 
tuple

• 第一个参数：指定要解析的JSON字符串
• 第二个参数：指定要返回的第1个字段
• ……
• 第N+1个参数：指定要返回的第N个字段

特点 ：

• 功能类似于get_json_object，但是可以调用一次返回多列的值。属于UDTF类型函数
• 返回的每一列都是字符串类型
• 一般搭配lateral view使用

--单独使用
select
    --解析所有字段
    json_tuple(json,"device","deviceType","signal","time") as (device,deviceType,signal,stime)
from tb_json_test1;

--搭配侧视图使用
select
    json,device,deviceType,signal,stime
from tb_json_test1
         lateral view json_tuple(json,"device","deviceType","signal","time") b
         as device,deviceType,signal,stime;

9.4.5. JSON 函数：JSON 函数：json_tuple

功能 ：上述解析JSON的过程中是将数据作为一个JSON字符串加载到表中，再通过JSON解析函数对JSON字符串进行解析，灵活性比较高，但是对于如果整个文件就是一个JSON文件，在使用起来就相对比较麻烦。Hive中为了简化对于JSON文件的处理，内置了一种专门用于解析JSON文件的Serde解析器，在创建表时，只要指定使用JSONSerde解析表的文件，就会自动将JSON文件中的每一列进行解析。

--创建表
create table tb_json_test2 (
                               device string,
                               deviceType string,
                               signal double,
                               `time` string
)
    ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
    STORED AS TEXTFILE;

load data local inpath '/root/hivedata/device.json' into table tb_json_test2;

十、窗口函数

10.1. 窗口函数概述

窗口函数（Window functions）是一种SQL函数，非常适合于数据分析，因此也叫做OLAP函数，其最大特点是：输入值是从SELECT语句的结果集中的一行或多行的“窗口”中获取的。你也可以理解为窗口有大有小（行有多有少）。

通过OVER子句，窗口函数与其他SQL函数有所区别。如果函数具有OVER子句，则它是窗口函数。如果它缺少OVER子句，则它是一个普通的聚合函数。

普通分组聚合：

窗口聚合：

10.2. 窗口函数语法

-------窗口函数语法树
Function(arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_expression>])

--其中Function(arg1,..., argn) 可以是下面分类中的任意一个
    --聚合函数：比如sum max avg等
    --排序函数：比如rank row_number等
    --分析函数：比如lead lag first_value等

--OVER [PARTITION BY <...>] 类似于group by 用于指定分组  每个分组你可以把它叫做窗口
--如果没有PARTITION BY 那么整张表的所有行就是一组

--[ORDER BY <....>]  用于指定每个分组内的数据排序规则 支持ASC、DESC

--[<window_expression>] 用于指定每个窗口中 操作的数据范围 默认是窗口中所有行

10.3. 案例：网站用户页面浏览次数分析

在网站访问中，经常使用cookie来标识不同的用户身份，通过cookie可以追踪不同用户的页面访问情况，有下面两份数据：

在Hive中创建两张表表，把数据加载进去用于窗口分析。

---建表并且加载数据
create table website_pv_info(
   cookieid string,
   createtime string,   --day
   pv int
) row format delimited
fields terminated by ',';

create table website_url_info (
    cookieid string,
    createtime string,  --访问时间
    url string       --访问页面
) row format delimited
fields terminated by ',';


load data local inpath '/root/hivedata/website_pv_info.txt' into table website_pv_info;
load data local inpath '/root/hivedata/website_url_info.txt' into table website_url_info;

使用窗口聚合函数

-----窗口聚合函数的使用-----------
--1、求出每个用户总pv数  sum+group by普通常规聚合操作
select cookieid,sum(pv) as total_pv from website_pv_info group by cookieid;

--2、sum+窗口函数 总共有四种用法 注意是整体聚合 还是累积聚合
--sum(...) over( )对表所有行求和
--sum(...) over( order by ... ) 连续累积求和
--sum(...) over( partition by... ) 同组内所行求和
--sum(...) over( partition by... order by ... ) 在每个分组内，连续累积求和

--需求：求出网站总的pv数 所有用户所有访问加起来
--sum(...) over( )对表所有行求和
select cookieid,createtime,pv,
       sum(pv) over() as total_pv  --注意这里窗口函数是没有partition by 也就是没有分组  全表所有行
from website_pv_info;

--需求：求出每个用户总pv数
--sum(...) over( partition by... )，同组内所行求和
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid) as total_pv
from website_pv_info;

--需求：求出每个用户截止到当天，累积的总pv数
--sum(...) over( partition by... order by ... )，在每个分组内，连续累积求和
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime) as current_total_pv
from website_pv_info;

10.4. Window expression窗口表达式

我们知道，在sum(…) over( partition by… order by … )语法完整的情况下，进行的累积聚合操作，默认累积聚合行为是：从第一行聚合到当前行。

Window expression窗口表达式给我们提供了一种控制行范围的能力，比如向前2行，向后3行。
语法如下：

键字是rows between，包括下面这几个选项 
- preceding：往前 
- - following：往后 
- - current row：当前行
- - unbounded：边界 
- - unbounded preceding 表示从前面的起点 
- - unbounded following：表示到后面的终点 

---窗口表达式
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime) as pv1  --默认从第一行到当前行
from website_pv_info;
--第一行到当前行
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2
from website_pv_info;

--向前3行至当前行
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4
from website_pv_info;

--向前3行 向后1行
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5
from website_pv_info;

--当前行至最后一行
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6
from website_pv_info;

--第一行到最后一行 也就是分组内的所有行
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding  and unbounded following) as pv6
from website_pv_info;

10.4. 窗口排序函数

窗口排序函数用于给每个分组内的数据打上排序的标号。注意窗口排序函数不支持窗口表达式。

总共有4个函数需要掌握：

• row_number：在每个分组中，为每行分配一个从1开始的唯一序列号，递增，不考虑重复；
• rank: 在每个分组中，为每行分配一个从1开始的序列号，考虑重复，挤占后续位置；
• dense_rank: 在每个分组中，为每行分配一个从1开始的序列号，考虑重复，不挤占后续位置；
• ntile函数，其功能为：将每个分组内的数据分为指定的若干个桶里（分为若干个部分），并且为每一个桶分配一个桶编号。 如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。（topN问题）

-----窗口排序函数
SELECT
    cookieid,
    createtime,
    pv,
    RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
    DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
    ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3
FROM website_pv_info
WHERE cookieid = 'cookie1';

--需求：找出每个用户访问pv最多的Top3 重复并列的不考虑
SELECT * from
(SELECT
    cookieid,
    createtime,
    pv,
    ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS seq
FROM website_pv_info) tmp where tmp.seq <4;

--把每个分组内的数据分为3桶
SELECT
    cookieid,
    createtime,
    pv,
    NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2
FROM website_pv_info
ORDER BY cookieid,createtime;

--需求：统计每个用户pv数最多的前3分之1天。
--理解：将数据根据cookieid分 根据pv倒序排序 排序之后分为3个部分 取第一部分
SELECT * from
(SELECT
     cookieid,
     createtime,
     pv,
     NTILE(3) OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn
 FROM website_pv_info) tmp where rn =1;

10.4. 窗口分析函数(连续问题)

• LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
  第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）；

• LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
  第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）；

• FIRST_VALUE 取分组内排序后，截止到当前行，第一个值；

• LAST_VALUE 取分组内排序后，截止到当前行，最后一个值；

--LAG
SELECT cookieid,
       createtime,
       url,
       ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
       LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,
       LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time
FROM website_url_info;


--LEAD
SELECT cookieid,
       createtime,
       url,
       ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
       LEAD(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time,
       LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time
FROM website_url_info;

--FIRST_VALUE
SELECT cookieid,
       createtime,
       url,
       ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
       FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1
FROM website_url_info;

--LAST_VALUE
SELECT cookieid,
       createtime,
       url,
       ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
       LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1
FROM website_url_info;

十一、Hive 数据压缩与存储格式

11.1. Hive数据压缩

优点：

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

缺点：

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

首先说明 mapreduce 哪些过程可以设置压缩：需要分析处理的数据在进入map 前可以压缩，然后解压处理，map处理完成后的输出可以压缩，这样可以减少网络I/O(reduce 通常和 map 不在同一节点上)，reduce 拷贝压缩的数据后进行解压，处理完成后可以压缩存储在hdfs上，以减少磁盘占用量。

MapReduce可以在两个阶段进行数据压缩：

• map的输出
  • 减少shuffle的数据量 提高shuffle时网络IO的效率
• reduce的输出
  • 减小输出文件的大小 降低磁盘的存储空间

11.1.1. Hadoop中支持的压缩算法

推荐Snappy算法。

11.1.2. Hive的压缩设置

• 开启hive中间传输数据压缩功能
  1. 开启hive中间传输数据压缩功能

  set hive.exec.compress.intermediate=true; 
  

  2. 开启mapreduce中map输出压缩功能

  set mapreduce.map.output.compress=true; 
  

  3. 设置mapreduce中map输出数据的压缩方式

  set mapreduce.map.output.compress.codec= org.apache.hadoop.io.compress.SnappyCodec; 
  

• 开启Reduce输出阶段压缩
  1. 开启hive最终输出数据压缩功能

  set hive.exec.compress.output=true; 
  

  2. 开启mapreduce最终输出数据压缩

  set mapreduce.output.fileoutputformat.compress=true; 
  

  3. 设置mapreduce最终数据输出压缩方式

  set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec; 
  

  4. 设置mapreduce最终数据输出压缩为块压缩

  set mapreduce.output.fileoutputformat.compress.type=BLOCK; 
  

设置完毕以后，只有当HiveSQL底层通过MapReduce程序执行才会涉及压缩。
比如

select * from table

就不会涉及压缩操作

但是

-- ctas语句
create table xx as select * from table

就涉及压缩操作

11.2. Hive的数据存储格式

11.2.1. 列式存储和行式存储

逻辑表中的数据，最终需要落到磁盘上，以文件的形式存储，有两种常见的存储形式。行式存储和列式存储。

1. 行存储：

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

   缺点：
   如果查询只涉及某几个列，它会把整行数据都读取出来，不能跳过不必要的列读取。当然数据比较少，一般没啥问题，如果数据量比较大就比较影响性能

2. 列式存储：

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

   缺点：
   INSERT/UPDATE 很麻烦或者不方便；
   不适合扫描小量的数据

Hive 支持的存储数的格式主要有：TEXTFILE（行式存储） 、SEQUENCEFILE(行式存储)、ORC（列式存储）、PARQUET（列式存储）。

11.2.2. TEXTFILE格式

默认格式，数据不做压缩，磁盘开销大，数据解析开销大。可结合Gzip、Bzip2使用(系统自动检查，执行查询时自动解压)，但使用这种方式，hive不会对数据
进行切分，从而无法对数据进行并行操作。

11.2.3. ORC格式

ORC 的全称是(Optimized Row Columnar)，ORC 文件格式是一种Hadoop生态圈中的列式存储格式，它的产生早在2013年初，最初产生自Apache Hive，用于降低Hadoop数据存储空间和加速Hive查询速度。它并不是一个单纯的列式存储格式，仍然是首先根据行组分割整个表，在每一个行组内进行按列存储。

一个orc文件可以分为若干个Stripe，一个stripe可以分为三个部分：
indexData：某些列的索引数据
rowData :真正的数据存储
StripFooter：stripe 的元数据信息

优点如下：

• ORC 是列式存储，有多种文件压缩方式，并且有着很高的压缩比。
• 文件是可切分（Split）的。因此，在 Hive 中使用 ORC 作为表的文件存储格式，不仅节省HDFS存储资源，查询任务的输入数据量减少，使用的MapTask也就减少了。
• ORC 可以支持复杂的数据结构（比如Map等）。
• ORC 文件也是以二进制方式存储的，所以是不可以直接读取，ORC文件也是自解析的。

11.2.3. PARQUET格式

Parquet 是面向分析型业务的列式存储格式，由 Twitter 和 Cloudera 合作开发，2015年5月从Apache的孵化器里毕业成为Apache顶级项目。

Parquet 文件是以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的数据和元数据，因此Parquet格式文件是自解析的。

通常情况下，在存储Parquet数据的时候会按照Block大小设置行组的大小，由于一般情况下每一个 Mapper 任务处理数据的最小单位是一个 Block，这样可以把每一个行组由一个Mapper任务处理，增大任务执行并行度。

11.2.5. 文件格式存储对比

从存储文件的压缩比和查询速度两个角度对比。

1. textfile

   create table log_text (
   track_time string,
   url string,
   session_id string,
   referer string,
   ip string,
   end_user_id string,
   city_id string
   )
   ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
   STORED AS TEXTFILE ; -- 不写默认也是textfile
   
   load data local inpath '/root/hivedata/log.data' into table log_text ;
   

   查看文件大小：
   
   
   

2. ORC

   create table log_orc(
   track_time string,
   url string,
   session_id string,
   referer string,
   ip string,
   end_user_id string,
   city_id string
   )
   ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
   STORED AS orc ;
   
   insert into table log_orc select * from log_text ;
   -- 为什么不用load? 因为load是纯复制移动操作，不会调整文件格式
   

   查看表中数据大小：
   

3. PARQUET

   create table log_parquet(
   track_time string,
   url string,
   session_id string,
   referer string,
   ip string,
   end_user_id string,
   city_id string
   )
   ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
   STORED AS PARQUET ;
   
   insert into table log_parquet select * from log_text ;
   

   

存储文件的压缩比总结：
ORC > Parquet > textFile

在实际中，可以根据需求选择不同的文件格式并且搭配不同的压缩算法。可以达到更好的效果。

结论：推荐使用ORC + Snappy压缩

create table log_orc_snappy( 
track_time string, 
url string, 
session_id string, 
referer string, 
ip string, 
end_user_id string, 
city_id string 
) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
STORED AS orc tblproperties ("orc.compress"="SNAPPY"); 

11.2.6. 存储文件查询速度对比

可以针对三张表，使用sql统计表数据个数。查看执行时间。

十二、Hive 调优

12.1. Fetch抓取机制

Hive 中对某些情况的查询可以不必使用MapReduce计算。例如：SELECT * FROM employees;在这种情况下，Hive 可以简单地读取 employee 对应的存储目录下的文件，然后输出查询结果到控制台。

在执行sql的时候，能不走MapReduce程序就尽量不走MapReduce程序。

在hive-default.xml.template 文件中 hive.fetch.task.conversion 默认是more，老版本hive默认是minimal，该属性修改为more以后，在全局查找、字段查找、limit查找等都不走mapreduce。

把hive.fetch.task.conversion 设置成 none，然后执行查询语句，都会执行mapreduce 程序。

set hive.fetch.task.conversion=none; 

把hive.fetch.task.conversion 设置成 more，然后执行查询语句，就不会执行mapreduce程序。

set hive.fetch.task.conversion=more; 

12.2. mapreduce 本地模式

mapreduce 程序除了可以提交到 yarn 执行之外，还可以使用本地模拟环境运行，此时就不是分布式执行的程序，但是针对小文件小数据处理特别有效果。

用户可以通过设置hive.exec.mode.local.auto 的值为true，来让Hive在适当的时候自动启动这个优化。

set hive.exec.mode.local.auto = true

hive 自动根据下面三个条件判断是否启动本地模式，要同时满足：

如果针对Hive的调优仍然不满足，还是效率低，尝试使用Spark计算引擎。

12.3. join 查询的优化

多个表关联时，最好分拆成小段sql分段执行，避免一个大sql（无法控制中间Job）。

底层还是MapReduce的join优化。

12.3.1. map side join

如果不指定 MapJoin 或者不符合 MapJoin 的条件，那么 Hive 解析器会将Join 操作转换成Common Join，即：在Reduce阶段完成join。容易发生数据倾斜。可以用MapJoin 把小表全部加载到内存在 map 端进行join，避免 reducer处理。

Hive会自动尝试选择map端的join提高join的效率，省区shuffle的过程。

开启mapjoin参数设置： 
（1）设置自动选择mapjoin 
set hive.auto.convert.join = true; --默认为 true 
（2）大表小表的阈值设置： 
set hive.mapjoin.smalltable.filesize= 25000000; 

12.3.2. 大表join大表

有时join超时是因为某些key对应的数据太多，而相同key对应的数据都会发送到相同的reducer上，从而导致内存不够。

大表join达标本身数据就十分具体，如果join的字段存在null空值，该如何处理？

1. 空key过滤

   不过滤查询：

   SELECT a.* FROM nullidtable a JOIN ori b ON a.id = b.id; 
   结果： 
   No rows affected (152.135 seconds) 
   

   过滤查询：

   SELECT a.* FROM (SELECT * FROM nullidtable WHERE id IS NOT NULL ) a JOIN ori b ON a.id = 
   b.id; 
   结果：
   No rows affected (141.585 seconds) 
   

2. 空key转换
   有时虽然某个key为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join的结果中，此时我们可以表a中key为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的reducer上。

SELECT a.* 
FROM nullidtable a 
LEFT JOIN ori b ON CASE WHEN a.id IS NULL THEN 'hive' ELSE a.id END = b.id; 
No rows affected (41.668 seconds) 

结果：这样的后果就是所有为null值的id全部都变成了相同的字符串，及其容易造成数据的倾斜（所有的key相同，相同key的数据会到同一个reduce当中去）。

为了解决这种情况，我们可以通过hive的rand函数，随记的给每一个为空的id赋上一个随机值，这样就不会造成数据倾斜。

SELECT a.* 
FROM nullidtable a 
LEFT JOIN ori b ON CASE WHEN a.id IS NULL THEN concat('hive', rand()) ELSE a.id END = b.id;

12.3.3. 大小表、小大表join

在当下的hive 版本中，大表join小表或者小表join大表，就算是关闭map
端join 的情况下，基本上没有区别了（hive为了解决数据倾斜的问题，会自动进
行过滤）。

12.4. group by 数据倾斜优化—map端聚合

默认情况下，当进行group by的时候，Map阶段同一Key数据分发给一个reduce，当一个key数据过大时就倾斜了。

但并不是所有的聚合操作都需要在Reduce端完成，很多聚合操作都可以先在Map端进行部分聚合，最后在Reduce端得出最终结果。

（1）是否在Map端进行聚合，默认为True 
set hive.map.aggr = true; 
（2）在Map端进行聚合操作的条目数目 
set hive.groupby.mapaggr.checkinterval = 100000; 
（3）有数据倾斜的时候进行负载均衡（默认是false） 
set hive.groupby.skewindata = true; 

Q：在hive中数据倾斜开启负载均衡之后，底层的执行机制是什么样？

• S1：启动一个mapreduce程序，将倾斜的资源随机发送到各个reduce中，进行打散，每个reduce进行聚合都是局部聚合

• S2：在启动第二个reduce程序，将局部聚合的结果来进行最终的聚合
  
  
  

12.5. MapReduce引擎并行度调整

12.5.1. maptask 个数调整

如果是在mapreduce中maptask是通过切片逻辑机制决定的。

但是在hive中，影响因素很多，比如切片逻辑机制，文件是否背压缩，压缩之后是否支持切割。

因此在hive中国，调整maptask的个数，直接去HDFS调整文件的大小和个数，效率比较高。

如果小文件多，就合并；如果大文件多，就调整block size。

12.5.2. reducetask 个数调整

（1）每个Reduce处理的数据量默认是256MB 
hive.exec.reducers.bytes.per.reducer=256123456 
（2）每个任务最大的reduce数，默认为1009 
hive.exec.reducers.max=1009 
（3）mapreduce.job.reduces 
该值默认为-1，由hive自己根据任务情况进行判断。 

reduce 个数并不是越多越好 ：

1. 过多的启动和初始化reduce也会消耗时间和资源；
2. 另外，有多少个reduce，就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题；

在设置reduce 个数的时候也需要考虑这两个原则：处理大数据量利用合适的reduce 数；使单个reduce任务处理数据量大小要合适。

12.6. 执行计划—explain（了解）

通过执行计划可以看出hive接下来是如何打算执行这天sql的
语法：

explain [extended]+ sql语句

12.7. 并行执行机制

前提是stage之间没有依赖。

Hive 会将一个查询转化成一个或者多个阶段。这样的阶段可以是MapReduce阶段、抽样阶段、合并阶段、limit阶段。或者Hive执行过程中可能需要的其他阶段。

默认情况下，Hive 一次只会执行一个阶段。不过，某个特定的job 可能包含众多的阶段，而这些阶段可能并非完全互相依赖的，也就是说有些阶段是可以并行执行的，这样可能使得整个job的执行时间缩短。不过，如果有更多的阶段可以并行执行，那么job可能就越快完成。

通过设置参数hive.exec.parallel值为true，就可以开启并发执行。不过，在共享集群中，需要注意下，如果job中并行阶段增多，那么集群利用率就会增加。

set hive.exec.parallel=true;             / /打开任务并行执行
set hive.exec.parallel.thread.number=16;  //同一个 sql 允许最大并行度，默认为8。

当然，得是在系统资源比较空闲的时候才有优势，否则，没资源，并行也起不来。

12.8. 严格模式

注意：不要与动态分区的严格模式搞混淆。

Hive 提供了一个严格模式，可以防止用户执行那些可能意向不到的不好的影响的查询。

通过设置属性hive.mapred.mode值为默认是非严格模式nonstrict 。开启严格模式需要修改hive.mapred.mode值为strict，开启严格模式可以禁止3种类型的查询。

1. 对于分区表，除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行。换句话说，就是用户不允许扫描所有分区。进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表。

2. 对于使用了order by语句的查询，要求必须使用limit语句。因为order by 为了执行排序过程会将所有的结果数据分发到同一个Reducer 中进行处理，强制要求用户增加这个LIMIT语句可以防止Reducer额外执行很长一段时间。

3. 限制笛卡尔积的查询。对关系型数据库非常了解的用户可能期望在执行JOIN 查询的时候不使用ON语句而是使用where语句，这样关系数据库的执行优化器就可以高效地将WHERE语句转化成那个ON语句。不幸的是，Hive并不会执行这种优化，因此，如果表足够大，那么这个查询就会出现不可控的情况。

12.9. 推测执行机制

Hadoop采用了推测执行（Speculative Execution）机
制，它根据一定的法则了推测执行（Speculative Execution）机制，并为这样的任务启动一个备份任务，让该任务与原始任务同时处理同一份数据，并最终选用最先成功运行完成任务的计算结果作为最终结果。