一、Hive

Hive Wiki

该文涉及到的所有软件包均已上传云盘，自行下载
链接：https://pan.baidu.com/s/1_fg4p-_Ch5_WAmcPbBqAvg
提取码：hi68

1.0.1、环境准备

1.0.1.0、maven安装

1.0.1.0.1、下载软件包

1.0.1.0.2、配置环境变量

1.0.1.0.3、调整maven仓库

打开$MAVEN_HOME/conf/settings.xml文件，调整maven仓库地址以及镜像地址

<settings xmlns="http://maven.apache.org/SETTINGS/1.0.0"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://maven.apache.org/SETTINGS/1.0.0
                            https://maven.apache.org/xsd/settings-1.0.0.xsd">
        <localRepository>你自己的路径</localRepository>
      <mirrors>
         <mirror>
           <id>alimaven</id>
           <name>aliyun maven</name>
           <url>http://maven.aliyun.com/nexus/content/groups/public/</url>
           <mirrorOf>central</mirrorOf>        
         </mirror>
      </mirrors>
</settings>

1.0.1.1、cywin安装

该软件安装主要是为了支持windows编译源码涉及到的基础环境包

1.0.1.1.1、下载软件包

1.0.1.1.2、安装相关编译包

需要安装cywin，gcc+相关的编译包

binutils 
gcc 
gcc-mingw 
gdb

1.0.1.2、源码包下载

https://downloads.apache.org/hive/hive-2.3.9/

1.0.1.3、JDK安装

1.0.1.3.1、下载软件包

1.0.1.3.2、配置环境变量

1.0.1.3.2.1、创建JAVA_HOME系统变量

1.0.1.3.2.2、创建CLASSPATH系统变量

1.0.1.3.2.3、追加到Path变量中

1.0.1.4、Hadoop安装

1.0.1.4.1、下载软件包

1.0.1.4.2、配置环境变量

创建HADOOP_HOME系统变量，然后将该变量追加到Path全局变量中

1.0.1.4.3、编辑配置文件

注意：下载的软件包中可能不包含下列的文件，但提供了template文件，直接重命名即可。由于该篇是属于学习自用，所以只需要配置基础核心信息即可。下列文件位于$HADOOP_HOME/etc/hadoop目录下。

1.0.1.4.3.1、修改core-site.xml文件

<configuration>
    <property>
        <name>fs.defaultFS</name>
        <value>hdfs://localhost:9000</value>
    </property>  
</configuration>

1.0.1.4.3.2、修改yarn-site.xml文件

<configuration>
<!-- Site specific YARN configuration properties -->
   <property>
        <name>yarn.nodemanager.aux-services</name>
        <value>mapreduce_shuffle</value>
    </property>
    <property>
        <name>yarn.nodemanager.aux-services.mapreduce.shuffle.class</name>
        <value>org.apache.hadoop.mapred.ShuffleHandler</value>
    </property>
</configuration>

1.0.1.4.3.3、修改mapred-site.xml文件

<configuration>
    <property>
        <name>mapreduce.framework.name</name>
        <value>yarn</value>
    </property>
</configuration>

1.0.1.4.4、初始化启动

首先初始化namenode,打开cmd执行下面的命令

$HADOOP_HOME/bin> hadoop namenode -format

当初始化完成后，启动hadoop

$HADOOP_HOME/sbin> start-all.cmd

当以下进程都启动成功后，hadoop基本环境也算是搭建成功了！

1.0.1.5、Mysql安装

1.0.1.5.1、软件包下载解压缩

官网或者直接从百度云盘中下载即可。
https://dev.mysql.com/downloads/mysql/

1.0.1.5.2、环境变量配置

1.0.1.5.2.1、创建系统环境变量MYSQL_HOME

1.0.1.5.2.2、将系统环境变量配置到Path上

1.0.1.5.2.3、生成Data文件

使用系统管理员打开CMD窗口

--执行下面命令
mysqld --initialize-insecure --user=mysql

1.0.1.5.2.4、安装MYSQL并启动服务

--执行命令
mysqld -install
--启动服务
net start MySQL

1.0.1.5.2.5、登录Mysql并修改密码

--初始登录时不需要密码，直接回车即可
mysql -u root -p
--修改root默认密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
--刷新提交
 flush privileges;

1.0.1.6、Hive安装

如果你想要深入学习Hive底层，那么源码编译是必不可少的，所以本篇将采用源码包编译安装的方式。

1.0.1.6.1、下载软件包并解压缩

1.0.1.6.2、编译

mvn clean package -Phadoop-2 -DskipTests -Pdist

注意：在编译的过程中遇到问题的可能性非常大，大部分是因为maven，可能是网络问题，也可能跟你的镜像配置也有关系。另外对于一些模块编译过程中出现Could not transfer artifact XXXX的问题，可以先把pom文件中的scope设置注释掉！

1.0.1.6.3、安装

编译完成后，可以在$HIVE_SRC_HOME/packaging/target目录下找到对应的可执行的压缩包

1.0.1.6.3.1、配置环境变量

创建HIVE_HOME系统变量，然后将该变量追加引用到Path变量中

1.0.1.6.3.2、编辑配置文件

1.0.1.6.3.2.1、编辑hive-env.sh文件

# 配置环境信息
# Set HADOOP_HOME to point to a specific hadoop install directory
export HADOOP_HOME=D:\GitCode\hadoop-2.7.2
# Hive Configuration Directory can be controlled by:
export HIVE_CONF_DIR=D:\GitCode\apache-hive-2.3.9-bin\conf
# Folder containing extra libraries required for hive compilation/execution can be controlled by:
export HIVE_AUX_JARS_PATH=D:\GitCode\apache-hive-2.3.9-bin\lib

1.0.1.6.3.2.2、编辑hive-site.xml文件
该文件中的参数稍微有些多，不过我们只改动基础的部分即可。

  <property>
    <name>hive.repl.rootdir</name>
    <value>D:\GitCode\apache-hive-2.3.9-bin\tmp_local</value>
  </property>
   <property>
    <name>hive.repl.cmrootdir</name>
    <value>D:\GitCode\apache-hive-2.3.9-bin\tmp_local</value>
  </property>
   <property>
    <name>hive.exec.local.scratchdir</name>
    <value>D:\GitCode\apache-hive-2.3.9-bin\tmp_local</value>
  </property>
  <property>
    <name>hive.downloaded.resources.dir</name>
    <value>D:\GitCode\apache-hive-2.3.9-bin\tmp_local\${hive.session.id}_resources</value>
  </property>
  <property>
    <name>hive.querylog.location</name>
    <value>D:\GitCode\apache-hive-2.3.9-bin\tmp_local</value>
  </property>
   <property>
    <name>hive.server2.logging.operation.log.location</name>
    <value>D:\GitCode\apache-hive-2.3.9-bin\tmp_local</value>
   </property>
    <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>123456</value>
  </property>
  <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true&amp;characterEncoding=UTF-8</value>
  </property>
  <property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>root</value>
  </property>
  <property>
    <name>hive.metastore.schema.verification</name>
    <value>false</value>
  </property>

1.0.1.6.3.2.3、log文件重命名
将conf文件中的几个Log文件后缀带有template移除即可

1.0.1.6.3.3、驱动包加载

本文使用mysql作为元数据存储，因此需要将JDBC的驱动包放到$HIVE_HOME/lib目录下

1.0.1.6.3.4、元数据初始化

在windows环境下，$HIVE_HOME/bin目录下并未找到cmd结尾的可执行文件，因此为了调通基础环境，可以从低版本中进行拷贝。本文涉及到的脚本和软件包都打包上传到云盘中

# 打开cmd命令行
$HIVE_HOME/bin> hive schematool -dbType mysql -initSchema  --verbose

注意：在初始化过程中，可能会有表无法创建的问题，这里手动创建即可。或者直接source sql文件。本篇文章使用的是hive-shceam-2.3.0.mysql.sql
SQL文件位置于HIVE_HOME/scripts/metastore/upgrade/mysql下：

1.0.2、服务启动

在搭建Hadoop的环节中，已经将Hadoop服务启动了，这里将Hive Metastore服务启动

hive --service metastore

1.0.2.1、服务端启动Debug模式

为了方便学习，大家可以在IDEA中打开Terminal，开启debug模式和metastore服务启动。

hive --debug

1.0.2.2、客户端断点模式

1.0.2.2.1、配置remote debug

在IDEA中配置Remote DeBug信息

1.0.2.2.2、断点

由于是在客户端进行源码追溯，所以一般会进入CliDriver类中，比如在main方法中打上断点，然后开始Debug



到这里，整个调试功能就已经实现了，大家可以在本地编写HSQL，然后根据断点深入学习Hive底层具体的执行流程，甚至自己改造源码！

1.1、流程剖析

大致流程：
1、客户端连接到HS2(HiveServer2，目前大多数通过beeline形式连接，Hive Cli模式相对较重，且直接略过授权访问元数据),建立会话
2、提交sql，通过Driver进行编译、解析、优化逻辑计划，生成物理计划
3、对物理计划进行优化，并提交到执行引擎进行计算
4、返回结果
细节流程：
1、客户端和HiveServer2建立连接，创建会话
2、提交查询或者DDL，转交到Driver进行处理
3、Driver内部会通过Parser对语句进行解析，校验语法是否正确
4、然后通过Compiler编译器对语句进行编译，生成AST Tree
5、SemanticAnalyzer会遍历AST Tree，进一步进行语义分析，这个时候会和Hive MetaStore进行通信获取Schema信息,抽象成QueryBlock，逻辑计划生成器会遍历QueryBlock，翻译成Operator（计算抽象出来的算子）生成OperatorTree,这个时候是未优化的逻辑计划
6、Optimizer会对逻辑计划进行优化，如进行谓词下推、常量值替换、列裁剪等操作，得到优化后的逻辑计划。
7、SemanticAnalyzer会对逻辑计划进行处理，通过TaskCompiler生成物理执行计划TaskTree。
8、TaskCompiler会对物理计划进行优化，然后根据底层不同的引擎进行提交执行。

1.1.1、Analyze Sql

语法：

EXPLAIN [EXTENDED|CBO|AST|DEPENDENCY|AUTHORIZATION|LOCKS|VECTORIZATION|ANALYZE] query

版本支持：
Hive0.14.0支持AUTHORIZATION；[HIVE-5961]
Hive2.3.0支持VECTORIZATION;[HIVE-11394]
Hive3.2.0支持LOCKS;[HIVE-17683]
Explain结果总共分为三个部分：
1、对应查询的抽象语法树 AST
2、每个计划阶段Stage之间的依赖关系
3、每个计划阶段的描述（可能是map/reduce,也可能是操作元数据或者文件操作）
聚合操作分析示例:

EXPLAIN FROM src INSERT OVERWRITE TABLE dest_g1 SELECT src.key, sum(substr(src.value,4)) GROUP BY src.key;

聚合操作分析输出信息：

STAGE DEPENDENCIES: --每个Stage之间的依赖关系
  Stage-1 is a root stage --Stage1是根阶段
  Stage-2 depends on stages: Stage-1 --当Stage1执行完成后才会执行Stage2
  Stage-0 depends on stages: Stage-2 --当Stage2执行完成后才会执行Stage0
 STAGE PLANS: --具体Stage信息
  Stage: Stage-1 
    Map Reduce  --MapReduce阶段
      Alias -> Map Operator Tree: --map阶段从一个特定表或者上一个map/reduce阶段结果中读取
        src --表名
            Reduce Output Operator
              key expressions:
                    expr: key
                    type: string
              sort order: +
              Map-reduce partition columns:
                    expr: rand()
                    type: double
              tag: -1
              value expressions:
                    expr: substr(value, 4)
                    type: string
      Reduce Operator Tree: --执行部分聚合
        Group By Operator
          aggregations:
                expr: sum(UDFToDouble(VALUE.0))
          keys:
                expr: KEY.0
                type: string
          mode: partial1
          File Output Operator
            compressed: false
            table:
                input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                output format: org.apache.hadoop.mapred.SequenceFileOutputFormat
                name: binary_table
  Stage: Stage-2
    Map Reduce --MapReduce阶段
      Alias -> Map Operator Tree:
        /tmp/hive-zshao/67494501/106593589.10001
          Reduce Output Operator
            key expressions:
                  expr: 0
                  type: string
            sort order: +  --列排序；+表示升序；-表示降序；一个+代表对一个列进行排序操作，如sort order:++ :代表按照两个列升序序排列
            Map-reduce partition columns:
                  expr: 0
                  type: string
            tag: -1
            value expressions:
                  expr: 1
                  type: double
      Reduce Operator Tree: --从Stage1中的部分聚合计算进行再次聚合
        Group By Operator --分组操作
          aggregations: --聚合函数信息
                expr: sum(VALUE.0)
          keys: --分组字段
                expr: KEY.0
                type: string
          mode: final --聚合模式:hash:随机聚合，final:最终聚合
          Select Operator
            expressions:
                  expr: 0
                  type: string
                  expr: 1
                  type: double
            Select Operator --选取操作
              expressions: --需要的字段名和类型
                    expr: UDFToInteger(0)
                    type: int
                    expr: 1
                    type: double
              File Output Operator --过滤操作
                compressed: false
                table:
                    input format: org.apache.hadoop.mapred.TextInputFormat
                    output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat
                    serde: org.apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe
                    name: dest_g1
  Stage: Stage-0
    Move Operator --文件操作，将从临时目录移动到结果目录下
      tables:
            replace: true
            table:
                input format: org.apache.hadoop.mapred.TextInputFormat
                output format: org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat
                serde: org.apache.hadoop.hive.serde2.dynamic_type.DynamicSerDe
                name: dest_g1

依赖分析示例:

EXPLAIN DEPENDENCY SELECT key, count(1) FROM srcpart WHERE ds IS NOT NULL GROUP BY key

依赖分析结果：

{"input_partitions":[{"partitionName":"default<at:var at:name="srcpart" />ds=2008-04-08/hr=11"},{"partitionName":"default<at:var at:name="srcpart" />ds=2008-04-08/hr=12"},{"partitionName":"default<at:var at:name="srcpart" />ds=2008-04-09/hr=11"},{"partitionName":"default<at:var at:name="srcpart" />ds=2008-04-09/hr=12"}],"input_tables":[{"tablename":"default@srcpart","tabletype":"MANAGED_TABLE"}]}

分析实际数据量示例：

explain analyze select t1.user_id,t2.visit_url from wedw_tmp.tmp_url_info t1 full join wedw_tmp.tmp_url_info t2 on t1.user_id = t2.user_id;

分析实际数量结果：

STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 depends on stages: Stage-1
STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Map Operator Tree:
          TableScan
            alias: t1
            Statistics: Num rows: 9/9 Data size: 1041 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: user_id (type: string)
              outputColumnNames: _col0
              Statistics: Num rows: 9/9 Data size: 1041 Basic stats: COMPLETE Column stats: NONE
              Reduce Output Operator
                key expressions: _col0 (type: string)
                sort order: +
                Map-reduce partition columns: _col0 (type: string)
                Statistics: Num rows: 9/9 Data size: 1041 Basic stats: COMPLETE Column stats: NONE
          TableScan
            alias: t2
            Statistics: Num rows: 9/9 Data size: 1041 Basic stats: COMPLETE Column stats: NONE
            Select Operator
              expressions: user_id (type: string), visit_url (type: string)
              outputColumnNames: _col0, _col1
              Statistics: Num rows: 9/9 Data size: 1041 Basic stats: COMPLETE Column stats: NONE
              Reduce Output Operator
                key expressions: _col0 (type: string)
                sort order: +
                Map-reduce partition columns: _col0 (type: string)
                Statistics: Num rows: 9/9 Data size: 1041 Basic stats: COMPLETE Column stats: NONE
                value expressions: _col1 (type: string)
      Reduce Operator Tree:
        Join Operator
          condition map:
               Outer Join 0 to 1
          keys:
            0 _col0 (type: string)
            1 _col0 (type: string)
          outputColumnNames: _col0, _col2
          Statistics: Num rows: 9/41 Data size: 1145 Basic stats: COMPLETE Column stats: NONE
          Select Operator
            expressions: _col0 (type: string), _col2 (type: string)
            outputColumnNames: _col0, _col1
            Statistics: Num rows: 9/41 Data size: 1145 Basic stats: COMPLETE Column stats: NONE
            File Output Operator
              compressed: false
              Statistics: Num rows: 9/41 Data size: 1145 Basic stats: COMPLETE Column stats: NONE
              table:
                  input format: org.apache.hadoop.mapred.SequenceFileInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
                  serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
  Stage: Stage-0
    Fetch Operator
      limit: -1
      Processor Tree:
        ListSink

1.1.2、Stage division（不够细致，需要例子）

Stage理解：
结合对前面讲到的Hive对查询的一系列执行流程的理解，那么在一个查询任务中会有一个或者多个Stage.每个Stage之间可能存在依赖关系。没有依赖关系的Stage可以并行执行。
Stage是Hive执行任务中的某一个阶段，那么这个阶段可能是一个MR任务，也可能是一个抽取任务，也可能是一个Map Reduce Local ,也可能是一个Limit。
何时划分Stage:
那么Stage划分的时机其实是发生在逻辑计划转化OperatorTree转化成物理计划的阶段TaskTree，按照深度优先遍历OperatorTree,再结合具体执行引擎的Compiler(MR/Tez/Spark)应用规则生成对应的Task。
Stage划分的界限决定于ReduceSinkOperator，在遇到ReduceSinkOperator之前的Operator都划分到Map阶段，同时也标识这Map阶段的结束。该ReduceSinkOperator到下一个ReduceSinkOperator阶段中间的部分划分为Reduce阶段。一个MR任务代表一个Stage（当然也包括其他非MR，如FetchTask、MoveTask、CopyTask）。
划分规则(按照MR为例子)：
R1: TS% ——>生成MapRedTask对象，确定MapWork
R2:TS%.RS —->遇到第一个ReduceSinkOperator，划分Map阶段，确定ReduceWork
R3:RS%.RS% ——>生成新的MapRedTask，切分MapRedTask。这个时候已经生成一个Job
R4:FS% ——> 连接MapRedTask和MoveTask。
R5:UNION% ——>如果所有的子查询都是map-only，则把所有的MapWork进行合并连接。
R6:UNION%.RS% —->遇到ReduceSinkOpeartor，则合并Stage，
R7:MAPJOIN%

*demo

insert ovewrite table test
select
  distinct url
from tmp.test
where date_id='2021-06-08' and length(url)>0 and url is not null
distribute by rand()
limit 10000

第一个Job发生的Map阶段：

第一个Job发生的Reduce阶段:

第二个Job发生的Map阶段

第二个Job发生的Reduce阶段

第三个Job发生的Map阶段

第三个Job发生的Reduce阶段

从sql查看具体的生成的job

create table  wedw_tmp.test as
select
 t1.user_id,count(1)
from test1 t1
left join test1  t2
on t1.user_id = t2.user_id
where t1.date_id='2021-06-08' and t2.date_id='2021-06-08'  and t1.user_id='12313' and t2.user_id='12313'
group by t1.user_id
distribute by rand()
limit 10000

1.1.3、Statistics Job

从OperatorTree生成Job的过程:
1、对输出表生成MoveTask
2、从OperatorTree中的一个根节点向下深度优先遍历
3、ReduceSinkOperator标识Map/Reduce界限，多个Job间的界限
4、遍历其他根节点，遇到JoinOpeartor合并MapReduceTask
5、生成StatTask更新元数据
6、剪断Map和Reduce之间的Operator关系
代码层面：

Utilities.getMRTasks(plan.getRootTasks()).size()
        + Utilities.getTezTasks(plan.getRootTasks()).size()
        + Utilities.getSparkTasks(plan.getRootTasks()).size();

计算生成MR Job的逻辑：
从执行计划根节点任务开始遍历，当该task属于ExecDriver实例，那么Job数+1
计算TezTask生成的Job数逻辑：
也是从执行计划根节点开始遍历，如果该task属于TezTask，那么Job数+1
计算Spark Job数的逻辑：
也是从执行计划根节点开始遍历，如果该task属于SparkTask，那么Job数+1
分析层面：
从explain可以看出一个sql语句会被划分多少个Stage,其实Stage总数就是Job数。但是有些Stage并不是MR或者Spark/Tez任务。所以要根据Operator类型来确定大概有多少个Job.

1.1.4、Only Map?

总结：即只要不发生shuffle,只是做简单的处理，如简单查或者文件移动/删除就会只有map阶段。
1、Creaet table As Select field from table [where condition]

2、select field from table [where condition] —需要设置hive.fetch.task.conversion=none

3、insert into/overwrite targettable select * from table [where condition]

4、select /+MAPJOIN(…)_/ ….

5、使用transform，只调用map script;
具体使用可参考:https://github.com/rgordon/hive-transform-example

cat>/tmp/myscript.sh
sed -r -e 's/\{(.*)\}/\1/' -e 's/"//g' -e 's/v(.)/v\100/g'
create table d (item map<string,string>);
create table s (item map<string,string>);
insert into s select map('k1','v1','k2','v2','k3','v3');
add file /tmp/myscript.sh;
select  
     str_to_map (result)
 from  
   (select  
      transform (item) using "myscript.sh" as result
    from s
    ) t

1.1.5、Only Reduce?

常规上思考MapReduce任务肯定是要有map阶段的，reduce阶段输入数据是通过map端输出数据拿来的。所以MapReduce不可能存在只有reduce的场景。
同样在hive的源码中也可以发现，ExecDriver在配置Job的时候是绑定了ExecMapper和ExecReducer，那么底层引擎是依赖于MapReduce的。

这么看来Hive也是不可能支持的。
但是在Hive的源码中存在另外一个MR框架。是hive自己实现的。

该框架内的MapReduce和hadoop底层的MR组件不一样，hive自身的MR框架，Map和Reduce是独立开的，每个阶段都是直接使用输入输出流，两者之间并没有依赖关系。
所以hive自身框架内的MR是可以支持只有reduce阶段的。可以使用transform函数来实现。

FROM (
  FROM src
   MAP value, key
 USING 'java -cp hive-contrib-${system:hive.version}.jar org.apache.hadoop.hive.contrib.mr.example.IdentityMapper'
    AS k, v
 CLUSTER BY k) map_output
  REDUCE k, v
   USING 'java -cp hive-contrib-${system:hive.version}.jar org.apache.hadoop.hive.contrib.mr.example.WordCountReduce'
   AS k, v
;

1.2、Filter PushDown Cases And Outer Join Behavior

前提:关闭优化器

set hive.auto.convert.join=false;
set hive.cbo.enable=false;

Inner Join:
1、Join On中的谓词: 左表下推、右表下推
2、Where谓词:左表下推、右表下推

-- 第一种情况：join on 谓词
select 
 t1.user_id,
 t2.user_id
from wedw_tmp.tmp_test_1 t1
join wedw_tmp.tmp_test_2 t2
on t1.user_id = t2.user_id   and t1.user_id='111' and t2.user_id='222'
--第二种情况：where谓词
select 
 t1.user_id,
 t2.user_id
from wedw_tmp.tmp_test_1 t1
join wedw_tmp.tmp_test_2 t2
on t1.user_id = t2.user_id   
where t1.user_id='111' and t2.user_id='222'

Left join:
1、Join On中的谓词: 左表不下推、右表下推(前提：关闭mapjoin优化)
2、Where谓词:左表下推、右表下推

-- 第一种情况：join on 谓词
select 
 t1.user_id,
 t2.user_id
from wedw_tmp.tmp_test_1 t1
left join wedw_tmp.tmp_test_2 t2
on t1.user_id = t2.user_id   and t1.user_id='111' and t2.user_id='222'
--第二种情况：where谓词
select 
 t1.user_id,
 t2.user_id
from wedw_tmp.tmp_test_1 t1
left join wedw_tmp.tmp_test_2 t2
on t1.user_id = t2.user_id   
where t1.user_id='111' and t2.user_id='222'

Right Join:
1、Join On中的谓词: 左表下推、右表不下推(前提：关闭mapjoin优化)
2、Where谓词:左表下推、右表下

-- 第一种情况：join on 谓词
select 
 t1.user_id,
 t2.user_id
from wedw_tmp.tmp_test_1 t1
right join wedw_tmp.tmp_test_2 t2
on t1.user_id = t2.user_id   and t1.user_id='111' and t2.user_id='222'
--第二种情况：where谓词
select 
 t1.user_id,
 t2.user_id
from wedw_tmp.tmp_test_1 t1
right join wedw_tmp.tmp_test_2 t2
on t1.user_id = t2.user_id   
where t1.user_id='111' and t2.user_id='222'

Full Join:
1、Join On中的谓词: 左表不下推、右表不下推(前提：关闭mapjoin优化)
2、Where谓词:左表不下推、右表不下推

-- 第一种情况：join on 谓词
select 
 t1.user_id,
 t2.user_id
from wedw_tmp.tmp_test_1 t1
full join wedw_tmp.tmp_test_2 t2
on t1.user_id = t2.user_id   and t1.user_id='111' and t2.user_id='222'
--第二种情况：where谓词
select 
 t1.user_id,
 t2.user_id
from wedw_tmp.tmp_test_1 t1
full join wedw_tmp.tmp_test_2 t2
on t1.user_id = t2.user_id   
where t1.user_id='111' and t2.user_id='222'

总结：

1.3、Function Cases

1.3.1、窗口函数

row_number：使用频率 ★★★★★
rank ：使用频率 ★★★★
dense_rank：使用频率 ★★★★
rank/dense_rank/row_number对比
first_value：使用频率 ★★★
last_value：使用频率 ★
lead：使用频率 ★★
lag：使用频率 ★★

1.3.2、集合相关

collect_set：使用频率 ★★★★★
collect_list：使用频率 ★★★★★
sort_array：使用频率 ★★★

1.3.3、URL相关

parse_url：使用频率 ★★★★
reflect：使用频率 ★★

1.3.4、JSON相关

get_json_object：使用频率 ★★★★★

1.3.5、列转行相关

explode：使用频率 ★★★★★

1.3.6、Cube相关

GROUPING SETS：使用频率 ★

1.3.7、字符相关

concat：使用频率 ★★★★★
concat_ws：使用频率 ★★★★★
instr：使用频率 ★★★★
length：使用频率 ★★★★★
size:使用频率 ★★★★★
trim：使用频率 ★★★★★
regexp_replace：使用频率 ★★★★★
regexp_extract：使用频率 ★★★★
substring_index：使用频率 ★★

1.3.8、条件判断

if：使用频率 ★★★★★
case when ：使用频率 ★★★★★
coalesce：使用频率 ★★★★★

1.3.9、数值相关

round：使用频率 ★★
ceil：使用频率 ★★★
floor：使用频率 ★★★
hex：使用频率 ★

1.3.10、时间相关(比较简单)

from_unxitime：使用频率 ★★★★★
unix_timestamp：使用频率 ★★★★★
to_date：使用频率 ★★★★★
year：使用频率 ★★★★★
month：使用频率 ★★★★★
day：使用频率 ★★★★★
date_add：使用频率 ★★★★★
date_sub：使用频率 ★★★★★

1.4、UDF/UDTF/UDAF

UDF：用户自定义函数
主要功能：一对一输入输出，如substr,date_add,instr，size等。

public final class Lower extends UDF {
  public Text evaluate(final Text s) {
    if (s == null) { return null; }
    return new Text(s.toString().toLowerCase());
  }
}

UDTF:拆解函数
主要功能：一对多输入输出，如explode,array,json_tuple等

限制(可以使用lateral view 替代)：
1、Select语句中不允许使用其他的表达式
2、不能嵌套，如explode(explode(adid_list))
3、不支持GROUP BY / CLUSTER BY / DISTRIBUTE BY / SORT BY
自定义：

public class GenericUDTFCount2 extends GenericUDTF {
  Integer count = Integer.valueOf(0);
  Object forwardObj[] = new Object[1];
  @Override
  public void close() throws HiveException {
    forwardObj[0] = count;
    forward(forwardObj);
    forward(forwardObj);
  }
  @Override
  public StructObjectInspector initialize(ObjectInspector[] argOIs) throws UDFArgumentException {
    ArrayList<String> fieldNames = new ArrayList<String>();
    ArrayList<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>();
    fieldNames.add("col1");
    fieldOIs.add(PrimitiveObjectInspectorFactory.javaIntObjectInspector);
    return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames,
        fieldOIs);
  }
  @Override
  public void process(Object[] args) throws HiveException {
    count = Integer.valueOf(count.intValue() + 1);
  }
}

UDAF:聚合函数
主要功能：sum,avg,min,max,collect_set
自定义函数：参考org.apache.hadoop.hive.ql.udf.generic.GenericUDAFMax
源码测试用例： ql/src/test/queries/clientpositive

1.4.1、函数帮助

注意：Hive中所有的关键词都是忽略大小写的。无论是通过Beeline还是CLI模式，都可以通过以下命令来查看Hive 函数

-- 查看所有的函数
show functions;
---查看具体某个函数的帮助手册
desc function function_name;
--查看函数示例功能
desc function extended function_name;

注意点：当hive.cache.expr.evaluation参数设置为true的时候(当然默认值就是true)，在0.12.0、0.13.0和0.13.1版本中，使用UDF嵌套到其他UDF函数中可能会有问题。具体可以见HIVE-7314

1.4.2、数学函数

注意：当传入值为NULL的时候，返回值大多数也是NULL

1.4.2.1、abs

作用：返回绝对值
select abs(-1),abs(1);

1.4.2.2、acos—>从Hive0.13.0

作用：如果 -1<=a<=1 或 NULL，则返回 a 的反余弦值。
select acos(0.1),acos(-1),acos(1),acos(-0.6)

1.4.2.3、asin—>从Hive0.13.0

作用：如果 -1<=a<=1 或 NULL，则返回 a 的反正弦。
select asin(0.1),asin(-1),asin(1),asin(-0.6),asin(null);

1.4.2.4、atan—>从Hive0.13.0

select atan(1.1),atan(0.543535)

1.4.2.5、bin

select bin(234234324234);

1.4.2.6、bround—>Hive1.3.0

select bround(2.4),bround(2.5),bround(3.5),bround(3.6554),bround(8.324,2),bround(3.234,1);

1.4.2.7、cbrt—>Hive1.2.0

select cbrt(3.34543);

1.4.2.8、ceil

select ceil(2.234),ceil(2.65),ceil(3.343);

1.4.2.9、conv

select conv(234234,10,2),conv(234234,10,16);

1.4.2.10、cos—>Hive0.13.0

select cos(23.23),cos(60),cos(90),cos(180);

1.4.2.11、degress—>Hive0.13.0

select degrees(23.23432),degrees(23.66545);

1.4.2.12、e

select e();

1.4.2.13、exp—>Hive0.13.0

select exp(23.234),exp(23.354),exp(23.65),exp(2);

1.4.2.14、factorial—>Hive1.2.0

1.4.2.15、floor

select floor(2.34),floor(2.756),floor(3.54);

1.4.2.16、greatest—>Hive1.1.0

select greatest(1,2,34,45,234,342,42324);

1.4.2.17、hex

select hex(23),hex(2);

1.4.2.18、least

select least(1,2,34,45,234,342,42324);

1.4.2.19、ln—>Hive0.13.0

select ln(10),ln(20),ln(0),ln(1);

1.4.2.20、log2—>Hive0.13.0

select log2(4),log2(6),log2(8);

1.4.2.21、log10—>Hive0.13.0

select log10(40),log10(60),log10(80),log10(100);

1.4.2.22、negative

select negative(1),negative(20.234),negative(-20);

1.4.2.23、pi

select pi();

1.4.2.24、pmod

select pmod(10,2),pmod(10,3),pmod(10,4);

1.4.2.25、positive

select positive(20),positive(10.234353);

1.4.2.26、pow

select pow(5,2),pow(4,2);

1.4.2.27、radians—>Hive0.13.0

select radians(4),radians(180),radians(360);

1.4.2.28、rand

— 可以指定种子序列，这样可以保障每次的结果都一致
select rand(),rand(5),rand(10);

1.4.2.29、round

select round(3),round(3.2),round(3.43);

1.4.2.30、shiftleft—>Hive1.2.0

1.4.2.31、shiftright—>Hive1.2.0

1.4.2.32、shiftrightunsigned—>Hive1.2.0

1.4.2.33、sign—>Hive0.13.0

select sign(23.23),sign(23),sign(-10);

1.4.2.34、sin—>Hive0.13.0

select sin(60),sin(90),sin(180),sin(270),sin(360)

1.4.2.35、sqrt—>Hive0.13.0

select sqrt(3),sqrt(4),sqrt(10);

1.4.2.36、tan—>Hive0.13.0

select tan(30),tan(90),tan(180);

1.4.2.37、unhex—>Hive0.12.0

select hex(57),unhex(57)

1.4.2.38、width_bucket—>Hive3.0.0

1.4.3、集合函数

1.4.3.1、size

select map(‘a’,1,’b’,2),size(map(‘a’,1,’b’,2)),array(1,2,3,4),size(array(1,2,3,4));

1.4.3.2、map_keys

select map(‘a’,1,’b’,2), map_keys(map(‘a’,1,’b’,2));

1.4.3.3、map_values

select map(‘a’,1,’b’,2), map_values(map(‘a’,1,’b’,2));

1.4.3.4、array_contains

select array(1,2,3,4),array_contains(array(1,2,3,4),1),array_contains(array(1,2,3,4),5);

注意：该函数中判断参数的类型要和数组元素类型保持一致，否则会查询失败
select array(1,2,3,4),array_contains(array(1,2,3,4),1),array_contains(array(1,2,3,4),’a’);

1.4.3.5、sort_array—>0.9.0

select array(4,2,3,234,234,23454,23),sort_array(array(4,2,3,234,234,23454,23));

1.4.4、类型转化函数

1.4.4.1、binary

select binary(‘a’),binary(‘2’);

1.4.4.2、cast

select cast(‘123’ as int);

1.4.5、日期函数

1.4.5.1、from_unixtime

select from_unixtime(1638602968),from_unixtime(1638602968,’yyyy-MM-dd HH:mm:SS’),from_unixtime(1638602968,’yyyy-MM-dd’);

1.4.5.2、unix_timestamp

select unix_timestamp();

1.4.5.3、to_date

select to_date(‘2021-12-04 2021-12-04 15:29:28’),to_date(‘2021-12-04 15:29:28’);

1.4.5.4、year

select year(‘2021-12-04 15:29:28’);

1.4.5.5、quarter—>Hive1.3.0

1.4.5.6、month

select month(‘2021-12-04 15:29:28’);

1.4.5.7、day

select day(‘2021-12-04 15:29:28’);

1.4.5.8、hour

select hour(‘2021-12-04 15:29:28’);

1.4.5.9、minute

select minute(‘2021-12-04 15:29:28’);

1.4.5.10、second

select second(‘2021-12-04 15:29:28’);

1.4.5.11、weekofyear

select weekofyear(‘2021-12-04 15:29:28’);

1.4.5.12、extract—>Hive2.2.0

1.4.5.13、datediff

select datediff(‘2009-03-01’, ‘2009-02-27’) ;

1.4.5.14、date_add

select date_add(‘2021-12-04 15:29:28’,1);

1.4.5.15、date_sub

select date_add(‘2021-12-04 15:29:28’,-1),date_sub(‘2021-12-04 15:29:28’,1),date_sub(‘2021-12-04 15:29:28’,-1);

1.4.5.16、from_utc_timestamp—>Hive0.8.0

select from_utc_timestamp(timestamp ‘1970-01-30 16:00:00’,’PST’) ,from_utc_timestamp(2592000.0,’PST’);

1.4.5.17、to_utc_timestamp—>Hive0.8.0

select to_utc_timestamp(2592000.0,’PST’), to_utc_timestamp(2592000000,’PST’),to_utc_timestamp(timestamp ‘1970-01-30 16:00:00’,’PST’) ;

1.4.5.18、current_date —>Hive1.2.0

select current_date();

1.4.5.19、current_timestamp —>Hive1.2.0

select current_timestamp();

1.4.5.20、add_months—>Hive1.1.0

select add_months(‘2009-08-31’, 1),add_months(‘2017-12-31 14:15:16’,3) ;

1.4.5.21、last_day —> Hive1.1.0

select last_day(‘2021-12-04’);

1.4.5.22、next_day —> Hive1.2.0

—获取下一个周二
select next_day(‘2021-12-04’, ‘TU’);

1.4.5.23、trunc —> Hive1.2.0

—返回截断为格式指定单位的日期（从 Hive 1.2.0 开始）。支持的格式：MONTH/MON/MM、YEAR/YYYY/YY。
select trunc(‘2021-12-04’, ‘MM’);

1.4.5.24、months_between—>Hive1.2.0

1.4.5.25、date_format —> Hive1.2.0

1.4.6、判断函数

1.4.6.1、if

select if(1=1,’a’,’b’),if(1=2,’a’,’b’) ;

1.4.6.2、isnull

select isnull(1),isnull(null);

1.4.6.3、isnotnull

select isnotnull(1),isnotnull(null);

1.4.6.4、nvl

select nvl(1,1),nvl(null,1);

1.4.6.5、coalesce

select coalesce(1,null,2,3,null,4,null),coalesce(null,null,null,2,3,4),coalesce(null,null,null,null);

1.4.6.6、case when

select case when 1=1 then ‘1’ else ‘b’ end ,case when 1=1 then ‘1’ end;

1.4.6.7、nullif —>Hive2.3.0

1.4.6.8、assert_true —> Hive0.8.0

select assert_true(1=1),assert_true(1=2);

1.4.7、字符串函数

1.4.7.1、ascii

select ascii(‘a’)

1.4.7.2、base64—>Hive0.12.0

select base64(cast(‘abcd’ as binary));

1.4.7.3、character_length—>Hive2.2.0

1.4.7.4、chr—>Hive1.3.0

1.4.7.5、concat

select concat(1,’1’,’2’,’a’),concat(1,’a’,null);

1.4.7.6、context_ngrams

1.4.7.7、concat_ws

—该拼接函数仅支持字符串类型或者字符串数组类型
select concat_ws(‘-‘,’1’,’2’,’3’),concat_ws(‘-‘,array(‘1’,’2’,’3’));

1.4.7.8、decode —>Hive0.12.0

select decode(cast(‘abc’ as binary),’ISO-8859-1’);

1.4.7.9、elt

—返回指定索引处的字符串
SELECT elt(2,’hello’,’world’),elt(1,’jello’,’world’);

1.4.7.10、encode —>Hive0.12.0

select encode(‘abc1233424asfsd’,’UTF-16LE’);

1.4.7.11、field

—返回指定字符串所在的索引处，如world字符串在第3个位置上
select field(‘world’,’say’,’hello’,’world’)

1.4.7.12、find_in_set

—返回 ‘abc,b,ab,c,def’ 中第一次出现的 ab ，其中 ‘abc,b,ab,c,def’ 是逗号分隔的字符串。如果任一参数为 null，则返回 null。如果第一个参数包含任何逗号，则返回 0
select find_in_set(‘ab’, ‘abc,b,ab,c,def’) ;

1.4.7.13、format_number—>Hive0.10.0

select format_number(2,1),format_number(2,2),format_number(2,3);

1.4.7.14、get_json_object

select get_json_object(‘{“a”:2,”b”:1}’,’$.a’);

1.4.7.15、in_file

1.4.7.16、instr

select instr(‘asfdsarsrsf234’,’6’),instr(‘asfdsarsrsf234’,’a’);

1.4.7.17、length

select length(‘asfdsarsrsf234’);

1.4.7.18、locate

select locate(‘a’,’asfdsarsrsf234’),locate(‘6’,’asfdsarsrsf234’);

1.4.7.19、lower

select lower(‘AWFDSFDS’);

1.4.7.20、lpad

—返回10位长度的字符串，如果不原始字符串不足10位，则用在左位填充
select lpad(‘sdfsd’,10,’‘);

1.4.7.21、ltrim

select ltrim(‘safdsf ‘),length(ltrim(‘safdsf ‘)),length(‘safdsf ‘);

1.4.7.22、ngrams

1.4.7.23、octet_length —>Hive2.2.0

1.4.7.24、parse_url

— 可以指定HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, 和 USERINFO
select parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1‘, ‘HOST’);

1.4.7.25、printf —>0.9.0

SELECT printf(“His Name is%s, Age Is %d”,”Jetty”,100);

1.4.7.26、regexp_extract

SELECT regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 2) ;

1.4.7.27、regexp_replace

select regexp_replace(“foobar”, “oo|ar”, “”);

1.4.7.28、repeat

SELECT repeat(‘a’,3);

1.4.7.29、replace—>HIve1.3.0

1.4.7.30、reverse

select reverse(“abcde”);

1.4.7.31、rpad

select rpad(‘ancd’,10,’*’);

1.4.7.32、rtrim

select rtrim(‘ safdsf ‘),length(rtrim(‘ safdsf ‘)),length(‘ safdsf ‘);

1.4.7.33、sentences

select sentences(‘Hello there! How are you?’);

1.4.7.34、space

select space(3),length(space(3));

1.4.7.35、split

select split(‘ansdasdfc’,’a’);

1.4.7.36、str_to_map

select str_to_map(‘a-1,b-2,c-3’,’,’,’-‘);

1.4.7.37、substr

select substr(‘foobar’, 4), substr(‘foobar’, 4, 1);

1.4.7.38、substring_index —>Hive1.3.0

1.4.7.39、translate—>Hive0.14.0

select translate(‘asefsda12313asdf’,’123’,’*’);

1.4.7.40、trim

select trim(‘ safdsf ‘),length(trim(‘ safdsf ‘)),length(‘ safdsf ‘);

1.4.7.41、unbase64 —>Hive0.12.0

select unbase64(‘wwewe.csdf’);

1.4.7.42、upper

select upper(‘fOoBaR’) ;

1.4.7.43、initcap—>Hive1.1.0

select initcap(‘asdfcasd’);

1.4.7.44、levenshtein —>Hive1.2.0

levenshtein(‘kitten’, ‘sitting’);

1.4.7.45、soundex —>Hive1.2.0

select soundex(‘Miller’);

支持版本	返回值类型	函数名称	功能描述
	int	ascii(string str)	返回 str 的第一个字符的数值。
Hive0.12.0	string	base64(binary bin)	将参数从二进制转换为 base 64 字符串（从 Hive 0.12.0 开始）
Hive2.2.0	int	character_length(string str)	返回 str 中包含的 UTF-8 字符数（从 Hive 2.2.0 开始）。函数 char_length 是该函数的简写。
Hive1.3.0	string	chr(bigint|double A)	返回二进制等同于 A 的 ASCII 字符（从 Hive 1.3.0 和 2.1.0 开始）。如果 A 大于 256，则结果等同于 chr(A % 256)。示例：选择 chr(88);返回“X”。
	string	concat(string|binary A, string|binary B…)	返回按顺序连接作为参数传入的字符串或字节所产生的字符串或字节。例如， concat(‘foo’, ‘bar’) 结果为 ‘foobar’。请注意，此函数可以接受任意数量的输入字符串。
	array>	context_ngrams(array>, array, int K, int pf)	给定一串“上下文”，从一组标记化的句子中返回前 k 个上下文 N-gram。
	string	concat_ws(string SEP, string A, string B…)	与上面的 concat() 类似，但使用自定义分隔符 SEP
	string	concat_ws(string SEP, array)	与上面的 concat_ws() 类似，但采用字符串数组。 （从 Hive 0.9.0 开始）
Hive0.12.0	string	decode(binary bin, string charset)	使用提供的字符集（’US-ASCII’、’ISO-8859-1’、’UTF-8’、’UTF-16BE’、’UTF-16LE’、’UTF- 16’)。如果任一参数为空，则结果也将为空。 （从 Hive 0.12.0 开始。）
	string	elt(N int,str1 string,str2 string,str3 string,…)	返回索引号处的字符串。例如 elt(2,’hello’,’world’) 返回 ‘world’。如果 N 小于 1 或大于参数数量，则返回 NULL。
Hive0.12.0	binary	encode(string src, string charset)	使用提供的字符集（’US-ASCII’、’ISO-8859-1’、’UTF-8’、’UTF-16BE’、’UTF-16LE’、’UTF- 16’)。如果任一参数为空，则结果也将为空。 （从 Hive 0.12.0 开始。）
	int	field(val T,val1 T,val2 T,val3 T,…)	返回 val1,val2,val3,… 列表中 val 的索引，如果未找到则返回 0。例如 field(‘world’,’say’,’hello’,’world’) 返回 3。支持所有原始类型，使用 str.equals(x) 比较参数。如果 val 为 NULL，则返回值为 0。
	int	find_in_set(string str, string strList)	返回 strList 中第一次出现的 str ，其中 strList 是逗号分隔的字符串。如果任一参数为 null，则返回 null。如果第一个参数包含任何逗号，则返回 0。例如， find_in_set(‘ab’, ‘abc,b,ab,c,def’) 返回 3
Hive0.10.0	string	format_number(number x, int d)	将数字 X 格式化为类似 ‘#,###,###.##’ 的格式，四舍五入到 D 位小数，并将结果作为字符串返回。如果 D 为 0，则结果没有小数点或小数部分。 （从 Hive 0.10.0 开始；在 Hive 0.14.0 中修复了浮点类型的错误，在 Hive 0.14.0 中添加了十进制类型支持）
	string	get_json_object(string json_string, string path)	根据指定的json路径从json字符串中提取json对象，并返回提取的json对象的json字符串。如果输入的 json 字符串无效，它将返回 null。注意：json 路径只能包含字符 [0-9a-z_]，即不能包含大写或特殊字符。此外，键不能以数字开头。这是由于对 Hive 列名称的限制
	boolean	in_file(string str, string filename)	如果字符串 str 在文件名中显示为整行，则返回 true。
	int	instr(string str, string substr)	返回 substr 在 str 中第一次出现的位置。如果任一参数为 null，则返回 null，如果在 str 中找不到 substr，则返回 0。请注意，这不是基于零的。 str 中的第一个字符的索引为 1
	int	length(string A)	返回字符串的长度
	int	locate(string substr, string str[, int pos])	返回位置 pos 之后 str 中第一次出现 substr 的位置。
	string	lower(string A) lcase(string A)	返回将 B 的所有字符转换为小写的字符串。例如，lower(‘fOoBaR’) 结果是 ‘foobar’
	string	lpad(string str, int len, string pad)	返回 str，用 pad 左填充，长度为 len。如果 str 比 len 长，则返回值将缩短为 len 个字符。如果填充字符串为空，则返回值为空
	string	ltrim(string A)	返回从 A 的开头（左侧）修剪空格产生的字符串。例如， ltrim(‘ foobar ‘) 结果为 ‘foobar ‘
	array>	ngrams(array>, int N, int K, int pf)	返回一组标记化的句子中的top-k N-grams，例如由sentences()UDAF返回的那些
Hive2.2.0	int	octet_length(string str)	返回以 UTF-8 编码保存字符串 str 所需的八位字节数（自 Hive 2.2.0）。请注意，octet_length(str) 可以大于 character_length(str)。
	string	parse_url(string urlString, string partToExtract [, string keyToExtract])	从 URL 返回指定的部分。 partToExtract 的有效值包括 HOST、PATH、QUERY、REF、PROTOCOL、AUTHORITY、FILE 和 USERINFO。例如， parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1‘, ‘HOST’) 返回 ‘facebook.com’。还可以通过提供键作为第三个参数来提取 QUERY 中特定键的值，例如， parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1‘, ‘QUERY’, ‘k1’) 返回 ‘v1’。
Hive0.9.0	string	printf(String format, Obj… args)	返回按照printf风格的格式字符串格式化的输入（从Hive 0.9.0开始）
	string	quote(String text)	返回带引号的字符串（包括任何单引号 HIVE-4.0.0 的转义字符)
	string	regexp_extract(string subject, string pattern, int index)	返回使用模式提取的字符串。例如，regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 2) 返回 ‘bar.’请注意，在使用预定义字符类时需要小心：使用 ‘\s’ 作为第二个参数将匹配字母 s； ‘\\s’ 是匹配空格等所必需的。’index’ 参数是 Java 正则表达式 Matcher group() 方法索引
	string	regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)	返回将 INITIAL_STRING 中与 PATTERN 中定义的 java 正则表达式语法匹配的所有子字符串替换为 REPLACEMENT 实例所产生的字符串。例如，regexp_replace(“foobar”, “oo|ar”, “”) 返回 ‘fb.’。请注意，在使用预定义字符类时需要小心：使用 ‘\s’ 作为第二个参数将匹配字母 s； ‘\\s’ 是匹配空格等所必需的。
	string	repeat(string str, int n)	重复 str n 次。
Hive1.3.0	string	replace(string A, string OLD, string NEW)	返回所有非重叠出现的OLD被替换为NEW的字符串A（从Hive 1.3.0和2.1.0起). 例如：选择 replace(“ababab”, “abab”, “Z”); 返回 “Zab”
	string	reverse(string A)	返回反转的字符串
	string	rpad(string str, int len, string pad)	返回 str，用 pad 右填充，长度为 len。如果 str 比 len 长，则返回值将缩短为 len 个字符。如果填充字符串为空，则返回值为空。
	string	rtrim(string A)	返回从 A 的末尾（右侧）修剪空格产生的字符串。例如， rtrim(‘ foobar ‘) 结果为 ‘ foobar’
	array>	sentences(string str, string lang, string locale)	将一串自然语言文本标记为单词和句子，其中每个句子在适当的句子边界处断开并作为单词数组返回。 ‘lang’ 和 ‘locale’ 是可选参数。例如，句子(‘Hello there! How are you?’) 返回 ( (“Hello”, “there”), (“How”, “are”, “you”) )
	string	space(int n)	返回一个包含 n 个空格的字符串
	array	split(string str, string pat)	在 pat 周围拆分 str （pat 是一个正则表达式）
	map	str_to_map(text[, delimiter1, delimiter2])	使用两个分隔符将文本拆分为键值对。 Delimiter1 将文本分成 K-V 对，Delimiter2 拆分每个 K-V 对。默认分隔符是“,”用于 delimiter1，“:”用于 delimiter2。
	string	substr(string|binary A, int start) substring(string|binary A, int start)	返回 A 的字节数组的子字符串或切片，从起始位置到字符串 A 的结尾。例如， substr(‘foobar’, 4) 结果为 ‘bar
	string	substr(string|binary A, int start, int len) substring(string|binary A, int start, int len)	返回 A 的字节数组的子字符串或切片，从起始位置开始，长度为 len。例如， substr(‘foobar’, 4, 1) 结果为 ‘b’
Hive1.3.0	string	substring_index(string A, string delim, int count)	在分隔符 delim 出现计数之前返回字符串 A 中的子字符串（从 Hive 1.3.0 开始））。如果 count 为正数，则返回最终分隔符左侧的所有内容（从左侧开始计数）。如果计数为负，则返回最终分隔符右侧的所有内容（从右侧开始计数）。 Substring_index 在搜索 delim 时执行区分大小写的匹配。示例：substring_index(‘www.apache.org’, ‘.’, 2) = ‘www.apache’
Hive0.14.0	string	translate(string|char|varchar input, string|char|varchar from, string|char|varchar to)	通过用 to 字符串中的相应字符替换 from 字符串中存在的字符来翻译输入字符串。这类似于 PostgreSQL 中的 translate 函数。如果此 UDF 的任何参数为 NULL，则结果也为 NULL。 （从 Hive 0.10.0 开始可用，用于字符串类型）。从 Hive 0.14.0 开始添加 Char/varchar 支持
	string	trim(string A)	返回从 A 的两端修剪空格产生的字符串。例如，trim(‘ foobar ‘) 结果为 ‘foobar’
Hive0.12.0	binary	unbase64(string str)	将参数从 base 64 字符串转换为 BINARY。 （从 Hive 0.12.0 开始。）
	string	upper(string A) ucase(string A)	返回将 A 的所有字符转换为大写的字符串。例如， upper(‘fOoBaR’) 结果为 ‘FOOBAR’
Hive1.1.0	string	initcap(string A)	返回字符串，每个单词的第一个字母大写，所有其他字母小写。单词由空格分隔。 （截至 Hive 1.1.0.)
Hive1.2.0	int	levenshtein(string A, string B)	返回两个字符串之间的 Levenshtein 距离（从 Hive 1.2.0）。例如， levenshtein(‘kitten’, ‘sitting’) 结果为 3
Hive1.2.0	string	soundex(string A)	返回字符串的 soundex 代码（从 Hive 1.2.0 开始）。例如， soundex(‘Miller’) 结果为 M460

1.4.8、掩码函数

1.4.8.1、mask—>Hive2.1.0

1.4.8.12、mask_first_n->Hive2.1.0

1.4.8.13、mask_last_n —>Hive2.1.0

1.4.8.14、mask_show_first_n—>Hive2.1.0

1.4.8.15、maskshow__last_n —>Hive2.1.0

1.4.8.16、mask_hash —>Hive2.1.0

1.4.9、杂项函数

1.4.9.1、java_method—>Hive0.9.0

SELECT java_method(“java.lang.String”, “valueOf”, 1),
java_method(“java.lang.String”, “isEmpty”),
java_method(“java.lang.Math”, “max”, 2, 3),
java_method(“java.lang.Math”, “min”, 2, 3),
java_method(“java.lang.Math”, “round”, 2.5),
java_method(“java.lang.Math”, “exp”, 1.0),
java_method(“java.lang.Math”, “floor”, 1.9)

1.4.9.2、reflect —> Hive0.7.0

SELECT reflect(“java.lang.String”, “valueOf”, 1),
reflect(“java.lang.String”, “isEmpty”),
reflect(“java.lang.Math”, “max”, 2, 3),
reflect(“java.lang.Math”, “min”, 2, 3),
reflect(“java.lang.Math”, “round”, 2.5),
reflect(“java.lang.Math”, “exp”, 1.0),
reflect(“java.lang.Math”, “floor”, 1.9)

1.4.9.3、hash —> Hive0.4

select hash(‘asfsd’);

1.4.9.4、current_user —> Hive1.2.0

select current_user();

1.4.9.5、logged_in_user —> Hive2.2.0

1.4.9.6、current_database —>Hive0.13.0

select current_database();

1.4.9.7、md5 —>Hive1.3.0

select md5(‘234’);

1.4.9.8、sha1 —> Hive1.3.0

1.4.9.9、crc32 —> Hive1.3.0

1.4.9.10、sha2—>Hive1.3.0

1.4.9.11、aes_encrypt —> Hive1.3.0

1.4.9.12、version —> Hive2.1.0

1.4.9.13、surrogate_key

1.4.10、内置UDAF函数

1.4.10.1、count

—可以发现count(id)会把id=null的值剔除掉
select count(1),count(*),count(distinct id),count(id) from test

1.4.10.2、sum

select sum(1) from test;

1.4.10.3、avg

该函数太简单了，就不给大家演示了

1.4.10.4、min

该函数太简单了，就不给大家演示了

1.4.10.5、max

该函数太简单了，就不给大家演示了

1.4.10.6、variance

1.4.10.7、var_sample

1.4.10.8、stddev_pop

1.4.10.9、covar_pop

1.4.10.10、covar_samp

1.4.10.11、corr

1.4.10.12、percentile

1.4.10.13、percentile_approx

1.4.10.14、regr_avgx —>Hive2.2.0

1.4.10.15、regr_avgy —>Hive2.2.0

1.4.10.16、regr_count —>Hive2.2.0

1.4.10.17、regr_intercept —>Hive2.2.0

1.4.10.18、regr_r2 —>Hive2.2.0

1.4.10.19、regr_slope —>Hive2.2.0

1.4.10.20、regr_sxx —>Hive2.2.0

1.4.10.21、regr_sxy —>Hive2.2.0

1.4.10.22、regr_syy —>Hive2.2.0

1.4.10.23、histogram_numeric

1.4.10.24、collect_set

select collect_set(id),count(1) from test;

1.4.10.25、collect_list —> Hive0.13.0

select collect_list(id),count(1) from test;

1.4.10.26、ntile —> Hive0.11.0

select
id,
ntile(1) over(partition by id),—分组内的数据切分为1份
ntile(2) over(partition by id),—分组内的数据切分为2份
ntile(3) over(partition by id )—分组内的数据切分为2份
from test

1.4.11、内置UDTF函数

1.4.11.1、explode

select explode(array(100,200,300));

得到的结果如下：

1.4.11.2、posexplode

select posexplode(array(‘A’,’B’,’C’));

1.4.11.3、parse_url_tuple

select parse_url_tuple(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1','HOST‘, ‘PATH’, ‘QUERY’, ‘QUERY:id’);

1.4.11.5、inline

select inline(array(struct(‘A’,10,date ‘2015-01-01’),struct(‘B’,20,date ‘2016-02-02’)));

1.4.11.6、stack

select stack(2,’A’,10,date ‘2015-01-01’,’B’,20,date ‘2016-01-01’);

1.4.11.7、json_tuple

select json_tuple(‘{“a”:1,”b”:2}’,’a’,’b’,’c’);

1.4.12、三者区别

1.4.12.1、UDF

UDF全称为User Defined Function（即用户自定义函数），UDF开发在日常工作当中是非常普遍的。我们写一段SQL，调用UDF，得到结果就算是结束了，但大家有没有想过UDF底层是怎么执行的呢？那么我们拿MR引擎为例，那UDF是在Map端执行还是在Reduce端执行的呢？说实话，我之前没想过。

既然没想过，那今天就来想一想。首先抛开在哪端执行不说，那我们知道UDF的模式是我们给一个值，然后再返回一个值。如上图所示，传一个A，返回给一个A_1；传一个B，返回给一个B_1;传一个C返回给一个C_1；
这种模式就相当于在传入的一个值上进行了一些修饰后再返回给我们，相当于是一对一的模式。梳理到这里，答案也比较清晰了，这不就是map功能吗。有些同学可能会质疑，没关系，我们explain一下就知道了。

如上图所示，只有一个fetch Operator，当然这个demo比较简单，不会走MR的。从这里也可以看出来这就是一个转换功能，但有些同学仍有疑惑，没关系，让我们来一个走MR的例子。
explain select substr(id,2),count(1) from test group by substr(id,2);

到这里总能证明UDF函数是在Map阶段执行的吧！

1.4.12.2、UDF编写

对于如何开发UDF，网上模板一大堆，这里不再叙述。下面出一个关于year函数的内部实现，供大家参考

public class UDFYear extends UDF {
  private final SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
  private final Calendar calendar = Calendar.getInstance();
  private final IntWritable result = new IntWritable();
  public UDFYear() {
  }
  public IntWritable evaluate(Text dateString) {
    if (dateString == null) {
      return null;
    }
    try {
      Date date = formatter.parse(dateString.toString());
      calendar.setTime(date);
      result.set(calendar.get(Calendar.YEAR));
      return result;
    } catch (ParseException e) {
      return null;
    }
  }
  public IntWritable evaluate(DateWritable d) {
    if (d == null) {
      return null;
    }
    calendar.setTime(d.get());
    result.set(calendar.get(Calendar.YEAR));
    return result;
  }
  public IntWritable evaluate(TimestampWritable t) {
    if (t == null) {
      return null;
    }
    calendar.setTime(t.getTimestamp());
    result.set(calendar.get(Calendar.YEAR));
    return result;
  }
}

1.4.12.3、UDAF

UDAF全称为User-defined Aggregation Function，从命名来看，这是一种聚合函数，比如像我们常用的sum、max。如下图所示，可以抽象的理解成传入多个值，最后返回给我们一个值。那么对于该类型的函数是不是一定在reduce端执行了，为什么这么说呢？你看sum函数是不是会发生shuffle，是不是在reduce端做全局聚合呢（如果你这样想也没问题，但也有问题）

我们通过explain命令来验证一下想法。我们执行如下命令：

explain select sum(id) from test

如上图所示，对于SUM类型的UDAF是在map端和reduce端都执行了，哎呦，这是怎么回事呢？我们回想一下MapReduce机制，如果我们要做全局聚合，难道要把所有的数据都拉取到reduce端吗？那reduce端压力是不是就会很大。所以有了局部聚合的这么一种优化方式。

那我们把局部聚合优化阶段给关闭后，再来看一下UDAF会在那一端执行

--关闭map端聚合
set hive.map.aggr=false;

如上图所示，当我们把局部聚合优化功能给关闭后，UDAF只会在reduce执行。

1.4.12.4、UDAF编写

如UDF一样，这里给出内置Max示例函数的实现

public class UDAFExampleMax extends UDAF {
  static public class MaxShortEvaluator implements UDAFEvaluator {
    private short mMax;
    private boolean mEmpty;
    public MaxShortEvaluator() {
      super();
      init();
    }
    public void init() {
      mMax = 0;
      mEmpty = true;
    }
    public boolean iterate(ShortWritable o) {
      if (o != null) {
        if (mEmpty) {
          mMax = o.get();
          mEmpty = false;
        } else {
          mMax = (short) Math.max(mMax, o.get());
        }
      }
      return true;
    }
    public ShortWritable terminatePartial() {
      return mEmpty ? null : new ShortWritable(mMax);
    }
    public boolean merge(ShortWritable o) {
      return iterate(o);
    }
    public ShortWritable terminate() {
      return mEmpty ? null : new ShortWritable(mMax);
    }
  }
  static public class MaxIntEvaluator implements UDAFEvaluator {
    private int mMax;
    private boolean mEmpty;
    public MaxIntEvaluator() {
      super();
      init();
    }
    public void init() {
      mMax = 0;
      mEmpty = true;
    }
    public boolean iterate(IntWritable o) {
      if (o != null) {
        if (mEmpty) {
          mMax = o.get();
          mEmpty = false;
        } else {
          mMax = Math.max(mMax, o.get());
        }
      }
      return true;
    }
    public IntWritable terminatePartial() {
      return mEmpty ? null : new IntWritable(mMax);
    }
    public boolean merge(IntWritable o) {
      return iterate(o);
    }
    public IntWritable terminate() {
      return mEmpty ? null : new IntWritable(mMax);
    }
  }
  static public class MaxLongEvaluator implements UDAFEvaluator {
    private long mMax;
    private boolean mEmpty;
    public MaxLongEvaluator() {
      super();
      init();
    }
    public void init() {
      mMax = 0;
      mEmpty = true;
    }
    public boolean iterate(LongWritable o) {
      if (o != null) {
        if (mEmpty) {
          mMax = o.get();
          mEmpty = false;
        } else {
          mMax = Math.max(mMax, o.get());
        }
      }
      return true;
    }
    public LongWritable terminatePartial() {
      return mEmpty ? null : new LongWritable(mMax);
    }
    public boolean merge(LongWritable o) {
      return iterate(o);
    }
    public LongWritable terminate() {
      return mEmpty ? null : new LongWritable(mMax);
    }
  }
  static public class MaxFloatEvaluator implements UDAFEvaluator {
    private float mMax;
    private boolean mEmpty;
    public MaxFloatEvaluator() {
      super();
      init();
    }
    public void init() {
      mMax = 0;
      mEmpty = true;
    }
    public boolean iterate(FloatWritable o) {
      if (o != null) {
        if (mEmpty) {
          mMax = o.get();
          mEmpty = false;
        } else {
          mMax = Math.max(mMax, o.get());
        }
      }
      return true;
    }
    public FloatWritable terminatePartial() {
      return mEmpty ? null : new FloatWritable(mMax);
    }
    public boolean merge(FloatWritable o) {
      return iterate(o);
    }
    public FloatWritable terminate() {
      return mEmpty ? null : new FloatWritable(mMax);
    }
  }
  static public class MaxDoubleEvaluator implements UDAFEvaluator {
    private double mMax;
    private boolean mEmpty;
    public MaxDoubleEvaluator() {
      super();
      init();
    }
    public void init() {
      mMax = 0;
      mEmpty = true;
    }
    public boolean iterate(DoubleWritable o) {
      if (o != null) {
        if (mEmpty) {
          mMax = o.get();
          mEmpty = false;
        } else {
          mMax = Math.max(mMax, o.get());
        }
      }
      return true;
    }
    public DoubleWritable terminatePartial() {
      return mEmpty ? null : new DoubleWritable(mMax);
    }
    public boolean merge(DoubleWritable o) {
      return iterate(o);
    }
    public DoubleWritable terminate() {
      return mEmpty ? null : new DoubleWritable(mMax);
    }
  }
  static public class MaxStringEvaluator implements UDAFEvaluator {
    private Text mMax;
    private boolean mEmpty;
    public MaxStringEvaluator() {
      super();
      init();
    }
    public void init() {
      mMax = null;
      mEmpty = true;
    }
    public boolean iterate(Text o) {
      if (o != null) {
        if (mEmpty) {
          mMax = new Text(o);
          mEmpty = false;
        } else if (mMax.compareTo(o) < 0) {
          mMax.set(o);
        }
      }
      return true;
    }
    public Text terminatePartial() {
      return mEmpty ? null : mMax;
    }
    public boolean merge(Text o) {
      return iterate(o);
    }
    public Text terminate() {
      return mEmpty ? null : mMax;
    }
  }
}