DQL

查询语句语法

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference       -- 从什么表查
  [WHERE where_condition]   -- 过滤
  [GROUP BY col_list]        -- 分组查询
   [HAVING col_list]          -- 分组后过滤
  [ORDER BY col_list]        -- 排序
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT number]                -- 限制输出的行数

关系运算函数

操作符	支持的数据类型	描述
A=B	基本数据类型	如果A等于B则返回true，反之返回false
A<=>B	基本数据类型	如果A和B都为null或者都不为null，则返回true，如果只有一边为null，返回false
A<>B, A!=B	基本数据类型	A或者B为null则返回null；如果A不等于B，则返回true，反之返回false
A<B	基本数据类型	A或者B为null，则返回null；如果A小于B，则返回true，反之返回false
A<=B	基本数据类型	A或者B为null，则返回null；如果A小于等于B，则返回true，反之返回false
A>B	基本数据类型	A或者B为null，则返回null；如果A大于B，则返回true，反之返回false
A>=B	基本数据类型	A或者B为null，则返回null；如果A大于等于B，则返回true，反之返回false
A [not] between B and C	基本数据类型	如果A，B或者C任一为null，则结果为null。如果A的值大于等于B而且小于或等于C，则结果为true，反之为false。如果使用not关键字则可达到相反的效果。
A is null	所有数据类型	如果A等于null，则返回true，反之返回false

逻辑运算函数

操作符	含义
and	逻辑并
or	逻辑或
not	逻辑否

聚合函数

count(*)，表示统计所有行数，包含null值；
count(某列)，表示该列一共有多少行，不包含null值；
max()，求最大值，不包含null，除非所有值都是null；
min()，求最小值，不包含null，除非所有值都是null；
sum()，求和，不包含null。
avg()，求平均值，不包含null。

Join语句

Hive支持通常的sql join语句，但是只支持等值连接，不支持非等值连接。

排序

全局排序（Order By）
- Order By：全局排序，只有一个Reduce。
  - 使用Order By子句排序
    - asc（ascend）：升序（默认）
    - desc（descend）：降序
  - Order By子句在select语句的结尾
每个Reduce内部排序（Sort By）
- Sort By：对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用Sort by。
- Sort by为每个reduce产生一个排序文件。每个Reduce内部进行排序，对全局结果集来说不是排序。
分区（Distribute By）
- Distribute By：在有些情况下，我们需要控制某个特定行应该到哪个Reducer，通常是为了进行后续的聚集操作。distribute by子句可以做这件事。distribute by类似MapReduce中partition（自定义分区），进行分区，结合sort by使用。对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。
- distribute by的分区规则是根据分区字段的hash码与reduce的个数进行相除后，余数相同的分到一个区。
- Hive要求distribute by语句要写在sort by语句之前
分区排序（Cluster By）
- 当distribute by和sort by字段相同时，可以使用cluster by方式。
- cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为asc或者desc。

函数

函数简介

Hive会将常用的逻辑封装成函数给用户进行使用，类似于Java中的函数。
- 好处：避免用户反复写逻辑，可以直接拿来使用。
- 重点：用户需要知道函数叫什么，能做什么。
- Hive提供了大量的内置函数，按照其特点可大致分为如下几类：单行函数、聚合函数、炸裂函数、窗口函数。

查看系统内置函数

show functions;

查看内置函数用法

desc function upper;

查看内置函数详细信息

desc function extended upper;

单行函数

单行函数的特点是一进一出，即输入一行，输出一行。
单行函数按照功能可分为如下几类:日期函数、字符串函数、集合函数、数学函数、流程控制函数等。

算术运算函数

运算符	描述
A+B	A和B 相加
A-B	A减去B
A*B	A和B 相乘
A/B	A除以B
A%B	A对B取余
A&B	A和B按位取与
A\|B	A和B按位取或
A^B	A和B按位取异或
~A	A按位取反

数值函数

round：四舍五入
ceil：向上取整
floor：向下取整

字符串函数

substring：截取字符串
replace ：替换
regexp_replace：正则替换
regexp：正则匹配
repeat：重复字符串
split ：字符串切割
nvl ：替换null值
concat ：拼接字符串
concat_ws：以指定分隔符拼接字符串或者字符串数组
get_json_object：解析json字符串

日期函数

unix_timestamp：返回当前或指定时间的时间戳
from_unixtime：转化UNIX时间戳（从 1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式
current_date：当前日期
current_timestamp：当前的日期加时间，并且精确的毫秒
month：获取日期中的月
day：获取日期中的日
hour：获取日期中的小时
datediff：两个日期相差的天数（结束日期减去开始日期的天数）
date_add：日期加天数
date_sub：日期减天数
date_format:将标准日期解析成指定格式字符串

流程控制函数

case when：条件判断函数
if: 条件判断，类似于Java中三元运算符

集合函数

size：集合中元素的个数
map：创建map集合
map_keys：返回map中的key
map_values: 返回map中的value
array 声明array集合
array_contains: 判断array中是否包含某个元素
sort_array：将array中的元素排序
struct声明struct中的各属性
named_struct声明struct的属性和值

高级聚合函数

普通聚合

collect_list 收集并形成list集合，结果不去重

collect_set 收集并形成set集合，结果去重

炸裂函数

定义：UDTF（Tables-Generating Functions）：接受一行数据，输出一行或多行数据

窗口函数（开窗函数）

按照功能，常用窗口可划分为如下几类：聚合函数、跨行取值函数、排名函数。

聚合函数

max：最大值。
min：最小值。
sum：求和。
avg：平均值。
count：计数。

跨行取值函数

lead和lag:获取当前行的上/下边某行、某个字段的值
first_value和last_value:获取窗口内某一列的第一个值/最后一个值

排名函数

rank,dense_rank,row_number

自定义函数

Hive自带了一些函数，比如：max/min等，但是数量有限，自己可以通过自定义UDF来方便的扩展。
当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。
根据用户自定义函数类别分为以下三种：
1. UDF（User-Defined-Function）:一进一出
2. UDAF（User-Defined Aggregation Function）:用户自定义聚合函数，多进一出.
3. UDTF（User-Defined Table-Generating Functions）:用户自定义表生成函数，一进多出。
官方文档地址
- https://cwiki.apache.org/confluence/display/Hive/HivePlugins

例子

创建一个Maven工程
导入依赖

<dependencies>
	<dependency>
		<groupId>org.apache.hive</groupId>
		<artifactId>hive-exec</artifactId>
		<version>3.1.3</version>
	</dependency>
</dependencies>

创建一个类

public class MyUDF extends GenericUDF {
    /**
     * 判断传进来的参数的类型和长度
     * 约定返回的数据类型
     * @param arguments
     * @return
     * @throws UDFArgumentException
     */
    @Override
    public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
        if (arguments.length != 1){
            throw new UDFArgumentLengthException("只有一个参数");
        }

        ObjectInspector argument = arguments[0];
        if(!argument.getCategory().equals(ObjectInspector.Category.PRIMITIVE)){
            throw new UDFArgumentTypeException(1,"i need primitive type arg");
        }

        PrimitiveObjectInspector primitiveObjectInspector = (PrimitiveObjectInspector) argument;
        if(!primitiveObjectInspector.getPrimitiveCategory().equals(PrimitiveObjectInspector.PrimitiveCategory.STRING)){
            throw new UDFArgumentException("只接受String类型的参数");
        }

        return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
    }

    /**
     * 解决具体逻辑的
     * @param arguments
     * @return
     * @throws HiveException
     */
    @Override
    public Object evaluate(DeferredObject[] arguments) throws HiveException {
        Object o = arguments[0].get();
        if(o == null){
            return 0;
        }
        return  o.toString().length();
    }

    /**
     * 用于获取执行计划的字符串
     * @param children
     * @return
     */
    @Override
    public String getDisplayString(String[] children) {
        return "";
    }
}

创建临时函数
1. 打成jar包上传到服务器/opt/module/hive/datas/myudf.jar
2. 将jar包添加到hive的classpath，临时生效
```
add jar /opt/module/hive/datas/myudf.jar;
```
3. 创建临时函数与开发好的java class关联
```
create temporary function my_len as "com.atguigu.hive.udf.MyUDF";
```
4. 在hql中使用自定义的临时函数
```
select 
    ename,
    my_len(ename) ename_len 
from emp;
```
5. 创建永久函数
  - 注意：因为add jar本身也是临时生效，所以在创建永久函数的时候，需要制定路径（并且因为元数据的原因，这个路径还得是HDFS上的路径）。
  - 注意：永久函数跟会话没有关系，创建函数的会话断了以后，其他会话也可以使用。
  - 永久函数创建的时候，在函数名之前需要自己加上库名，如果不指定库名的话，会默认把当前库的库名给加上。
  - 永久函数使用的时候，需要在指定的库里面操作，或者在其他库里面使用的话加上，库名.函数名。
```
create function my_len2 as "com.atguigu.hive.udf.MyUDF" using jar "hdfs://hadoop102:8020/udf/myudf.jar";
```

分区表

Hive中的分区就是把一张大表的数据按照业务需要分散的存储到多个目录，每个目录就称为该表的一个分区。在查询时通过where子句中的表达式选择查询所需要的分区，这样的查询效率会提高很多。

动态分区

动态分区是指向分区表insert数据时，被写往的分区不由用户指定，而是由每行数据的最后一个字段的值来动态的决定。使用动态分区，可只用一个insert语句将数据写入多个分区。
动态分区相关参数
1. 动态分区功能总开关（默认true，开启）
```
set hive.exec.dynamic.partition=true
```
2. 严格模式和非严格模式
  1. 动态分区的模式，默认strict（严格模式），要求必须指定至少一个分区为静态分区，nonstrict（非严格模式）允许所有的分区字段都使用动态分区。
```
set hive.exec.dynamic.partition.mode=nonstrict
```
  2. 一条insert语句可同时创建的最大的分区个数，默认为1000
```
set hive.exec.max.dynamic.partitions=1000
```
  3. 单个Mapper或者Reducer可同时创建的最大的分区个数，默认为100
```
set hive.exec.max.dynamic.partitions.pernode=100
```
  4. 一条insert语句可以创建的最大的文件个数，默认100000。
```
hive.exec.max.created.files=100000
```
  5. 当查询结果为空时且进行动态分区时，是否抛出异常，默认false
```
hive.error.on.empty.partition=false
```

分桶表

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分，分区针对的是数据的存储路径，分桶针对的是数据文件。
分桶表的基本原理是，首先为每行数据计算一个指定字段的数据的hash值，然后模以一个指定的分桶数，最后将取模运算结果相同的行，写入同一个文件中，这个文件就称为一个分桶（bucket）。

文件格式和压缩

为Hive表中的数据选择一个合适的文件格式，对提高查询性能的提高是十分有益的。Hive表数据的存储格式，可以选择text file、orc、parquet、sequence file等

Text File

文本文件是Hive默认使用的文件格式，文本文件中的一行内容，就对应Hive表中的一行记录

ORC

ORC（Optimized Row Columnar）file format是Hive 0.11版里引入的一种列式存储的文件格式。ORC文件能够提高Hive读写数据和处理数据的性能。

行存储的特点
- 查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快。
列存储的特点
- 因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法。
- 前文提到的text file和sequence file都是基于行存储的，orc和parquet是基于列式存储的。
原理
- 每个Orc文件由Header、Body和Tail三部分组成。
- 其中Header内容为ORC，用于表示文件类型。
- Body由1个或多个stripe组成，每个stripe一般为HDFS的块大小，每一个stripe包含多条记录，这些记录按照列进行独立存储，每个stripe里有三部分组成，分别是Index Data，Row Data，Stripe Footer。
- Index Data：一个轻量级的index，默认是为各列每隔1W行做一个索引。每个索引会记录第n万行的位置，和最近一万行的最大值和最小值等信息。
- Row Data：存的是具体的数据，按列进行存储，并对每个列进行编码，分成多个Stream来存储。
- Stripe Footer：存放的是各个Stream的位置以及各column的编码信息。
- Tail由File Footer和PostScript组成。File Footer中保存了各Stripe的其实位置、索引长度、数据长度等信息，各Column的统计信息等；PostScript记录了整个文件的压缩类型以及File Footer的长度信息等。
- 在读取ORC文件时，会先从最后一个字节读取PostScript长度，进而读取到PostScript，从里面解析到File Footer长度，进而读取FileFooter，从中解析到各个Stripe信息，再读各个Stripe，即从后往前读。

建表语句

create table orc_table
(column_specs)
stored as orc
tblproperties (property_name=property_value, ...);

参数	默认值	说明
orc.compress	ZLIB	压缩格式，可选项：NONE、ZLIB,、SNAPPY
orc.compress.size	262,144	每个压缩块的大小（ORC文件是分块压缩的）
orc.stripe.size	67,108,864	每个stripe的大小
orc.row.index.stride	10,000	索引步长（每隔多少行数据建一条索引）

Parquet

Parquet文件是Hadoop生态中的一个通用的文件格式，它也是一个列式存储的文件格式。
Parquet文件的基本结构，文件的首尾都是该文件的Magic Code，用于校验它是否是一个Parquet文件。
- 首尾中间由若干个Row Group和一个Footer（File Meta Data）组成。
- 每个Row Group包含多个Column Chunk，每个Column Chunk包含多个Page。以下是Row Group、Column Chunk和Page三个概念的说明：
- 行组（Row Group）：一个行组对应逻辑表中的若干行。
- 列块（Column Chunk）：一个行组中的一列保存在一个列块中。
- 页（Page）：一个列块的数据会划分为若干个页。
- Footer（File Meta Data）中存储了每个行组（Row Group）中的每个列快（Column Chunk）的元数据信息，元数据信息包含了该列的数据类型、该列的编码方式、该类的Data Page位置等信息。

建表语句

Create table parquet_table
(column_specs)
stored as parquet
tblproperties (property_name=property_value, ...);

参数	默认值	说明
parquet.compression	uncompressed	压缩格式，可选项：uncompressed，snappy，gzip，lzo，brotli，lz4
parquet.block.size	134217728	行组大小，通常与HDFS块大小保持一致
parquet.page.size	1048576	页大小

压缩

在Hive表中和计算过程中，保持数据的压缩，对磁盘空间的有效利用和提高查询性能都是十分有益的。
在Hive中，不同文件类型的表，声明数据压缩的方式是不同的

TextFile

若一张表的文件类型为TextFile，若需要对该表中的数据进行压缩，多数情况下，无需在建表语句做出声明。直接将压缩后的文件导入到该表即可，Hive在查询表中数据时，可自动识别其压缩格式，进行解压。
需要注意的是，在执行往表中导入数据的SQL语句时，用户需设置以下参数，来保证写入表中的数据是被压缩的。

--SQL语句的最终输出结果是否压缩
set hive.exec.compress.output=true;
--输出结果的压缩格式（以下示例为snappy）
set mapreduce.output.fileoutputformat.compress.codec =org.apache.hadoop.io.compress.SnappyCodec;

ORC

若一张表的文件类型为ORC，若需要对该表数据进行压缩，需在建表语句中声明压缩格式如下：

create table orc_table
(column_specs)
stored as orc
tblproperties ("orc.compress"="snappy");

Parquet

若一张表的文件类型为Parquet，若需要对该表数据进行压缩，需在建表语句中声明压缩格式如下：

create table orc_table
(column_specs)
stored as parquet
tblproperties ("parquet.compression"="snappy");

计算过程中使用压缩

单个MR的中间结果进行压缩

单个MR的中间结果是指Mapper输出的数据，对其进行压缩可降低shuffle阶段的网络IO，可通过以下参数进行配置：

--开启MapReduce中间数据压缩功能
set mapreduce.map.output.compress=true;
--设置MapReduce中间数据数据的压缩方式（以下示例为snappy）
set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;

单条SQL语句的中间结果进行压缩

单条SQL语句的中间结果是指，两个MR（一条SQL语句可能需要通过MR进行计算）之间的临时数据，可通过以下参数进行配置：

--是否对两个MR之间的临时数据进行压缩
set hive.exec.compress.intermediate=true;
--压缩格式（以下示例为snappy）
set hive.intermediate.compression.codec= org.apache.hadoop.io.compress.SnappyCodec;

MapReduce资源配置

MapReduce资源配置主要包括Map Task的内存和CPU核数，以及Reduce Task的内存和CPU核数。核心配置参数如下
mapreduce.map.memory.mb
- 该参数的含义是，单个Map Task申请的container容器内存大小，其默认值为1024。该值不能超出yarn.scheduler.maximum-allocation-mb和yarn.scheduler.minimum-allocation-mb规定的范围。
- 该参数需要根据不同的计算任务单独进行配置，在hive中，可直接使用如下方式为每个SQL语句单独进行配置：
```
set  mapreduce.map.memory.mb=2048;
```
mapreduce.map.cpu.vcores
- 该参数的含义是，单个Map Task申请的container容器cpu核数，其默认值为1。该值一般无需调整。
mapreduce.reduce.memory.mb
- 该参数的含义是，单个Reduce Task申请的container容器内存大小，其默认值为1024。该值同样不能超出yarn.scheduler.maximum-allocation-mb和yarn.scheduler.minimum-allocation-mb规定的范围。
- 该参数需要根据不同的计算任务单独进行配置，在hive中，可直接使用如下方式为每个SQL语句单独进行配置：
```
set  mapreduce.reduce.memory.mb=2048;
```
mapreduce.reduce.cpu.vcores
- 该参数的含义是，单个Reduce Task申请的container容器cpu核数，其默认值为1。该值一般无需调整。

Explain执行计划概述

Explain呈现的执行计划，由一系列Stage组成，这一系列Stage具有依赖关系，每个Stage对应一个MapReduce Job，或者一个文件系统操作等。
若某个Stage对应的一个MapReduce Job，其Map端和Reduce端的计算逻辑分别由Map Operator Tree和Reduce Operator Tree进行描述，Operator Tree由一系列的Operator组成，一个Operator代表在Map或Reduce阶段的一个单一的逻辑操作，例如TableScan Operator，Select Operator，Join Operator等。
常见的Operator及其作用如下：
- TableScan：表扫描操作，通常map端第一个操作肯定是表扫描操作
- Select Operator：选取操作
- Group By Operator：分组聚合操作
- Reduce Output Operator：输出到 reduce 操作
- Filter Operator：过滤操作
- Join Operator：join 操作
- File Output Operator：文件输出操作
- Fetch Operator 客户端获取数据操作

基本语法

EXPLAIN [FORMATTED | EXTENDED | DEPENDENCY] query-sql

注：FORMATTED、EXTENDED、DEPENDENCY关键字为可选项，各自作用如下
- FORMATTED：将执行计划以JSON字符串的形式输出
- EXTENDED：输出执行计划中的额外信息，通常是读写的文件名等信息
- DEPENDENCY：输出执行计划读取的表及分区