一、Hive简明笔记

1、Hive基本概念
2、Hive安装
3、Hive数据类型
- 3.1 基本数据类型
- 3.2 集合数据类型
3.3 类型转换
4、DDL数据定义
5、DML数据操作
6、查询
7、分区表和分桶表
8、函数
9、hive优化
10、压缩和存储
- 10.1 Hadoop压缩
- 10.2 文件存储格式
  - 10.2.1 列式存储和行式存储

1、Hive基本概念

1.1 什么是Hive

hive简介

Hive：由 Facebook 开源用于解决海量结构化日志的数据统计工具。

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

Hive的本质，是将HQL转化成MapReduce程序
- Hive处理的数据存储在HDFS上
- Hive分析数据底层的实现是MapReduce
- 执行程序运行在Yarn上

1.2 Hive的优缺点

优点
- 操作接口采用类Sql语法，提供快速开发的能力
- 避免了去写MapReduce，减少开发人员学习成本
- Hive的执行延迟比较高，所有Hive常用于数据分析，对实时性要求不高的场合
- Hive的优势在于处理大数据，对于处理小数据没有优势
- Hive支持用户自定义函数
缺点
- Hive 的 HQL 表达能力有限
  - 迭代式算法无法表达
  - 数据挖掘方面不擅长，由于 MapReduce 数据处理流程的限制，效率更高的算法却无法实现
- Hive 的效率比较低
  - Hive 自动生成的 MapReduce 作业，通常情况下不够智能化
  - Hive 调优比较困难，粒度较粗

1.3 Hive架构原理

Hive 通过给用户提供的一系列交互接口，接收到用户的指令(SQL)，使用自己的 Driver，结合元数据(MetaStore)，将这些指令翻译成 MapReduce，提交到 Hadoop 中执行，最后，将执行返回的结果输出到用户交互接口。

Client：用户接口

CLI（command-line interface）、JDBC/ODBC(jdbc 访问 hive)、WEBUI（浏览器访问 hive）
MetaStore：元数据

元数据包括：表名、表所属的数据库（默认是defualt)、表的拥有者、列/分区字段、表的类型（是否是外部类）、表的数据所在目录等。
- 默认存储在自带的 derby 数据库中，推荐使用 MySQL 存储 Metastore
MapReduce：hadoop的组件
Driver：驱动器
1. 解析器（SQL Parser）
  
  将sql字符串转换成抽象语法树AST，对AST进行语法分析：判断表是否存在、字段是否存在、sql语义是否有误...
2. 编译器（Physical Plan）
  
  将AST编译生成逻辑执行计划
3. 优化器（Query Optimizer）
  
  对逻辑执行计划进行优化
4. 执行器（Execution）
  
  把逻辑执行计划转换成可以运行的物理计划，即MR/Spark

2、Hive安装

2.1 Hive安装部署

解压软件到/opt/software目录下面
```
tar -zxvf xxx -C /opt/software
```

添加环境变量

# HIVE_HOME
export HIVE_HOME=/opt/software/hive-3.1.2
export PATH=$PATH:$HIVE_HOME/bin

解决日志jar包冲突

mv $HIVE_HOME/lib/log4j-slf4j-impl2.10.0.jar $HIVE_HOME/lib/log4j-slf4j-impl-2.10.0.bak

初始化元数据库（元数据库默认为 derby）
```
schematool -dbType derby -initSchema
```
Hive 默认使用的元数据库为 derby，开启 Hive 之后就会占用元数据库，且不与其他客户端共享数据，所以需要将 Hive 的元数据地址改为 MySQL
检测当前系统是否安装过Mysql
```
rpm -qa|grep mariadb
```
卸载Mysql
```
sudo rpm -e --nodeps mariadb-libs
```

安装mysql

安装Mysql

参考：VMware workstation16 中Centos7下MySQL8.0安装过程+Navicat远程连接 - ld_nil - 博客园 (cnblogs.com)

下载及安装源

wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
sudo yum install mysql80-community-release-el7-3.noarch.rpm

/etc/yum.repos.d/mysql-community.repo中的enabled值为1，为安装，enabled值为0，为不安装

安装mysql程序

sudo yum -y install deltarpm  # 安装依赖否则报错
sudo yum install mysql-community-server

修改mysql服务器为开机启动

systemctl start mysqld # 启动mysql服务
systemctl enable mysqld
systemctl daemon-reload # 设置mysql服务开机启动
systemctl status mysqld # 查看mysql服务状态

查看mysql初始登录密码

sudo grep "temporary" /var/log/mysqld.log

修改密码

参考：

1、MySQL的validate_password插件/组件总结 - 潇湘隐者 - 博客园 (cnblogs.com)

2、ERROR 1819 (HY000): Your password does not satisfy the current policy requirements_Spring Boot-Common On With You-CSDN博客

[1]  mysql -uroot -p<获取到的初始密码>;
[2]  alter user user() identified by "EgxSeR!sF7xx";
[3]  SHOW VARIABLES LIKE 'validate_password%';
[4]  set global validate_password.check_user_name=OFF;
[5]  set global validate_password.length=1;
[6]  set global validate_password_length=1;
[7]  set global validate_password.policy=0;
[9]  set global validate_password_policy=0;
[10] alter user user() identified by "root";
[11] 完成设置

远程登录设置
1. mysql -uroot -proot
2. use mysql
3. select host,user from user; -- 查看root用户的host属性
4. root用户的host默认为localhost，需要改为%（上图是修改过的），即可允许远程登录数据库
  
  [1] update user set host='%' where user='root';
  
  [2] flush privileges;

2.2 Hive元数据配置到MySQL

2.2.1 拷贝JDBC驱动

将 MySQL 的 JDBC 驱动拷贝到 Hive 的 lib 目录下

wget https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-8.0.26.tar.gz

2.2.2 配置 Metastore 到 MySQL

在$HIVE_HOME/conf 目录下新建 hive-site.xml 文件

vim $HIVE_HOME/conf/hive-site.xml

添加如下内容：

 <?xml version="1.0"?>
   <?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
   <configuration>
   <!-- jdbc 连接的 URL -->
       <property>
           <name>javax.jdo.option.ConnectionURL</name>
           <value>jdbc:mysql://node001:3306/metastore?useUnicode=true&amp;allowPublicKeyRetrieval=true&amp;useSSL=false</value>

       </property>

   <!-- jdbc 连接的 Driver-->
       <property>
           <name>javax.jdo.option.ConnectionDriverName</name>
           <value>com.mysql.cj.jdbc.Driver</value>
       </property>

   <!-- jdbc 连接的 username-->
       <property>
           <name>javax.jdo.option.ConnectionUserName</name>
           <value>root</value>
       </property>

   <!-- jdbc 连接的 password -->
       <property>
           <name>javax.jdo.option.ConnectionPassword</name>
           <value>root</value>
       </property>

   <!-- Hive 元数据存储版本的验证 -->
       <property>
           <name>hive.metastore.schema.verification</name>
           <value>false</value>
       </property>

   <!--元数据存储授权-->
       <property>
           <name>hive.metastore.event.db.notification.api.auth</name>
           <value>false</value>
       </property>

   <!-- Hive 默认在 HDFS 的工作目录 -->
       <property>
           <name>hive.metastore.warehouse.dir</name>
           <value>/user/hive/warehouse</value>
       </property>

   <!-- 指定存储元数据要连接的地址-->
       <property>
           <name>hive.metastore.uris</name>
           <value>thrift://node001:9083</value>
       </property>

   <!-- 指定 hiveserver2 连接的 host -->
       <property>
           <name>hive.server2.thrift.bind.host</name>
           <value>node001</value>
       </property>
   <!-- 指定 hiveserver2 连接的端口号 -->
       <property>
           <name>hive.server2.thrift.port</name>
           <value>10000</value>
       </property>

   <!-- hiveserver2的高可用参数，开启此参数可以提高hiveserver2的启动速度 -->
       <property>
           <name>hive.server2.active.passive.ha.enable</name>
           <value>true</value>
       </property>

   <!-- yarn作业获取到的hiveserver2用户都为hive用户,设置成true则为实际的用户名-->
       <property>
           <name>hive.server2.enable.doAs</name>
           <value>true</value>
       </property>

   <!-- 打印当前库-->
       <property>
           <name>hive.cli.print.current.db</name>
           <value>true</value>
       </property>

   <!-- 打印表头-->
       <property>
           <name>hive.cli.print.header</name>
           <value>true</value>
       </property>
   </configuration>

在MySQL中创建元数据库

[1] mysql -uroot -proot
[2] create database metastore

初始化Hive元数据库

schematool -initSchema -dbType mysql -verbose

2.3 使用元数据服务的方式访问Hive

在hiv e-site.xml文件中添加配置信息

 <!-- 指定存储元数据要连接的地址 -->
 <property>
 	<name>hive.metastore.uris</name>
 	<value>thrift://hadoop102:9083</value>
 </property>

启动metastore

hive --service metastore  //该服务为前台阻塞服务，启动后窗口不能操作，需要打开一个新的shell窗口做别的事

启动hive
```
hive
```

2.4 使用JDBC方式访问Hive

在hive-site.xml文件中添加配置信息

<!-- 指定 hiveserver2 连接的 host -->
<property>
 	<name>hive.server2.thrift.bind.host</name>
    <value>pc001</value>
</property>
 <!-- 指定 hiveserver2 连接的端口号 -->
 <property>
 	<name>hive.server2.thrift.port</name>
 	<value>10000</value>
 </property

启动metastore，server2依赖于metastore
```
hive --service metastore
```
启动server2
```
hive --service hiveserver2
```

启动beeline客户端

beeline -u jdbc:hive2://pc001:10000 -n nuochengze

2.5 hive 服务启动脚本

说明
- nohup：放在命令行开头，表示不挂起，也就是关闭终端进程也继续保持运行状态
- /dev/null：黑洞，所有写入该文件的内容都会被自动丢弃
- 2>&1：将错误输出重定向到标准输出上
- &：放在命令结尾，表示后台运行
- 组合使用以上命令：nohup [xxx] > /dev/null 2>&1 & ，将 xxx 命令运行的结果输出到 file 中，并保持命令启动的进程在后台运行

hive服务启动脚本 xhiveservice.sh

# xhiveservice.sh

#! /bin/bash

HIVE_LOG_DIR=$HIVE_HOME/logs

if [ ! -d $HIVE_LOG_DIR ]
    then
        mkdir -p $HIVE_LOG_DIR
fi

# 检查进程是否正常运行，参数1为进程名，参数2为进程端口
function check_process(){
    # 1 通过查看进程ps，获取pid
    ps_pid=$( ps -ef 2>/dev/null | grep -v grep | grep -i $1 | awk '{print $2}' );

    # 2 通过查看网络端口，获取pid
    netstat_pid=$( netstat -nltp 2>/dev/null | grep $2 | awk '{print $7}' | cut -d/ -f1 );

    # 3 输出状态
    # echo "===>>> 当前服务$1: ps -ef获取的pid=$ps_pid  ; netstat -nltp获取的pid=$netstat_pid";

    # 4 检查获取到的pid，判断服务是否启动，成功则返回0，否则返回1
    if [[ -n "$ps_pid" ]] && [[ -n "$netstat_pid" ]] && [[ "$ps_pid"="$netstat_pid" ]]
        then
            return 0
    else
        return 1
    fi
}

function hiveserver_start(){
    check_process HiveMetaStore 9083;
    ck_hivemetastore_result=$?;
    check_process HiveServer2 10000;
    ch_hiveserver2_result=$?;
    if [[ "$ck_hivemetastore_result" -eq "0" ]] && [[ "$ch_hiveserver2_result" -eq "0" ]]
        then
            echo "===>>> HiveMetaStore 服务已存在，无需再次启动"
            echo "===>>> HiveServer2 服务已存在，无需再次启动";
    else
        # 通过服务名获取进行pid，并杀死进程
        hivemetastore_pid=$( ps -ef | grep -v grep | grep -i HiveMetaStore | awk '{print $2}' );
        eval "kill -9 $hivemetastore_pid >/dev/null 2>&1";
        # 启动HiveMetaStore获取，并将输出写入日志文件
        log_filename="$HIVE_LOG_DIR/hivemetastore_$(date +%Y_%m_%d).log";
        eval "nohup hive --service metastore >> $log_filename 2>&1 &";
        echo "===>>> HiveMetaStore 服务已启动";

        hiveserver2_pid=$( ps -ef | grep -v grep | grep -i HiveServer2 | awk '{print $2}' );
        eval "kill -9 $hiveserver2_pid > /dev/null 2>&1";
        log_filename="$HIVE_LOG_DIR/hiveserver2_$(date +%Y_%m_%d).log";
        eval "nohup hive --service hiveserver2 >> $log_filename 2>&1 &";
        echo "===>>> HiveServer2 服务已启动";
    fi
}


function hiveserver_stop(){
    check_process HiveMetaStore 9083;
    ck_hivemetastore_result=$?;
    check_process HiveServer2 10000;
    ch_hiveserver2_result=$?;
    if [[ "$ck_hivemetastore_result" -eq "1" ]] && [[ "$ch_hiveserver2_result" -eq "1" ]]
        then
            echo "===>>> HiveMetaStore 服务已关闭,无需再次关闭"
            echo "===>>> HiveServer2 服务已关闭，无需再次关闭";
    else
        # 通过服务名获取进行pid，并杀死进程
        hivemetastore_pid=$( ps -ef | grep -v grep | grep -i HiveMetaStore | awk '{print $2}' );
        eval "kill -9 $hivemetastore_pid > /dev/null 2>&1";
        echo "===>>> HiveMetaStore 服务已关闭";

        hiveserver2_pid=$( ps -ef | grep -v grep | grep -i HiveServer2 | awk '{print $2}' );
        eval "kill -9 $hiveserver2_pid > /dev/null 2>&1";
        echo "===>>> HiveServer2 服务已关闭";
    fi
}



case $1 in
"start")
    hiveserver_start;
;;
"stop")
    hiveserver_stop;
;;
"restart")
    hiveserver_stop;
    hiveserver_start;
;;
"status")
    check_process HiveMetastore 9083 >/dev/null && echo "Metastore 服务运行正常" || echo "Metastore 服务运行异常";
    check_process HiveServer2 10000 >/dev/null && echo "HiveServer2 服务运行正常" || echo "HiveServer2 服务运行异常";
;;
*)
    echo "Invalid Args!";
    echo "Usage: xhiveservice start|stop|restart|status";
;;
esac

2.6 Hive常见属性配置

2.6.1 Hive运行日志信息配置

hive的log默认存放在/tml/$(user)/hive.log目录下

修改hive的log存放在/opt/software/hive/logs

修改$HIVE_HOME/conf/hive-log4j2.properties.template 为 hive-log4j2.properties

cp $HIVE_HOME/conf/hive-log4j2.properties.template $HIVE_HOME/conf/hive-log4j2.properties

在 hive-log4j2.properties 文件中修改 log 存放位置

#property.hive.log.dir = ${sys:java.io.tmpdir}/${sys:user.name}
property.hive.log.dir = /opt/software/hive-3.1.2/logs

2.6.2 打印当前库和表头

在hive-site.xml中加入：

<!-- 打印当前库-->
    <property>
        <name>hive.cli.print.current.db</name>
        <value>true</value>
    </property>

<!-- 打印表头-->
    <property>
        <name>hive.cli.print.header</name>
        <value>true</value>
    </property>
</configuration>

2.6.3 参数配置方式

查看当前所有的配置信息：
```
hive> set;
```
查看具体的参数设置
```
hive> set mapred.reduce.tasks;
```
配置参数的三种方式
1. 配置文件方式
  - 默认配置文件为：hive-default.xml
  - 用户自定义配置文件：hive-site.xml
  用户自定义配置会覆盖默认配置。另外，Hive 也会读入 Hadoop 的配置，因为 Hive 是作为 Hadoop 的客户端启动的，Hive 的配置会覆盖 Hadoop 的配置。配置文件的设定对本机启动的所有 Hive 进程都有效
2. 命令行参数方式
  
  启动 Hive 时，可以在命令行添加-hiveconf param=value 来设定参数
```
hive -hiveconf mapred.reduce.tasks=10;
```
  - 仅对本次 hive 启动有效
3. 参数声明方式
  
  可以在 HQL 中使用 SET 关键字设定参数
```
hive (default)> set mapred.reduce.tasks=100;
```
上述三种设定方式的优先级依次递增。即配置文件<命令行参数<参数声明。注意某些系统级的参数，例如 log4j 相关的设定，必须用前两种方式设定，因为那些参数的读取在会话建立以前已经完成了。

3、Hive数据类型

3.1 基本数据类型

Hive 数据类型	Java 数据类型	长度
TINYINT	byte	1byte 有符号整数
SMALINT	short	2byte 有符号整数
INT	int	int
BIGINT	long	8byte 有符号整数
BOOLEAN	boolean	布尔类型，true 或者 false
FLOAT	float	单精度浮点数
DOUBLE	double	双精度浮点数
STRING	string	字符系列。可以指定字符集。可以使用单引号或者双引号
TIMESTAMP		时间类型
BINARY		字节数组

3.2 集合数据类型

数据类型	描述	语法示例
STRUCT	和 c 语言中的 struct 类似，都可以通过“点”符号访问元素内容。例如，如果某个列的数据类型是 STRUCT{first STRING, last STRING},那么第 1 个元素可以通过字段.first 来引用。	struct() 例如struct<street:string,city:String>
MAP	MAP 是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是 MAP，其中键 ->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取最后一个元素	map() 例如 map<string,int>
ARRAY	数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第 2 个元素可以通过数组名[1]进行引用。	Array() 例如 array<String>

Hive 有三种复杂数据类型 ARRAY、MAP 和 STRUCT。ARRAY 和 MAP 与 Java 中的 Array 和 Map 类似，而 STRUCT 与 C 语言中的 Struct 类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套

Hive 上创建测试表 test

create table test_hive(
    name string,
    friends array<string>,
    children map<string,int>,
    address struct<street:string,city:string>
)
row format delimited
fields terminated by ","   -- 列分隔符
collection items terminated by "_"   --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ":"  -- MAP 中的 key 与 value 的分隔符
lines terminated by "\n"; -- 行分隔符

3.3 类型转换

隐式类型转换规则如下
- 任何整数类型都可以隐式地转换为一个范围更广的类型，如 TINYINT 可以转换成 INT，INT 可以转换成 BIGINT
- 所有整数类型、FLOAT 和 STRING 类型都可以隐式地转换成 DOUBLE
- TINYINT、SMALLINT、INT 都可以转换为 FLOAT
- BOOLEAN 类型不可以转换为任何其它的类型
可以使用 CAST 操作显示进行数据类型转换
- CAST('1' AS INT)将把字符串'1' 转换成整数 1；如果强制类型转换失败，如执行 CAST('X' AS INT)，表达式返回空值 NULL

4、DDL数据定义

4.1 创建数据库

语法

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value,...)];

// 示例
create database if not exists test
comment "this is a test"
location "/test"
...

创建一个数据库，数据库在HDFS上的默认存储路径是/user/hive/warehouse/*.db
避免在创建数据库时发生已存在错误，增加if not exitsts判断
通过location，指定数据库在HDFS上存放的位置

4.2 查询数据库

显示数据库
```
show databases;
```

过滤显示查询的数据库

show databases like "db_test*";  --  对于包含特殊字符的情形，需要打引号

显示数据库信息
```
desc database db_test;
```
显示数据库详细信息，extended
```
desc database extended db_test
```
切换当前数据库
```
use db_test;
```

4.3 修改数据库

可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。

alter database db_test
set dbproperties('createtime'='20210929');  -- 属性值，可以自定义设置属性名

desc database extended db_test; -- 查看结果

4.4 删除数据库

删除空数据库
```
drop database db_test;
```
当删除的数据库不存在时，使用if exists判断数据库是否存在
```
drop database if exists db_test;
```
当数据库不为空，可以采用cascade命令，强制删除
```
drop database db_test cascade;
```

4.4 创建表

语法
```
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
[(col_name data_type [COMMENT col_comment],......)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [comment col_comment],......)]
[CLUSTERED BY (col_name,col_name,......)  INTO num_buckets BUCKETS] 
[SORTED BY (col_name [ASC|DESC],......) ]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value,......)]
[AS select_statement]
```
- CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常
- EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据
- COMMENT：为表和列添加注释
- PARTITIONED BY 创建分区表
- CLUSTERED BY 创建分桶表
- SORTED BY 不常用，对桶中的一个或多个列另外排序
- ROW FORMAT row_format，其中的row_format指 DELIMITED和SERDE：
```
ROW FORMAT DELIMITED
[FIELDS TERMINATED BY char]
[COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char]
[LINES TERINATED BY char]
...
```
```
ROW FORMAT SERDE
serde_name [WITH SERDEPROPERTIES (property_name=property_value,...)]
```
  用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。
  
  SerDe 是 Serialize/Deserilize 的简称， hive 使用 Serde 进行行对象的序列与反序列化
- SerDe 是 Serialize/Deserilize 的简称， hive 使用 Serde 进行行对象的序列与反序列化
  - 常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）
  - 如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE
- LOCATION ：指定表在 HDFS 上的存储位置
- AS：后跟查询语句，根据查询结果创建表
- LIKE 允许用户复制现有的表结构，但是不复制数据

4.5 内部表

定义

MANAGED_TABLE，默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive 会（或多或少地）控制着数据的生命周期
说明
- Hive 默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下
- 当我们删除一个管理表时，Hive 也会删除这个表中数据。管理表不适合和其他工具共享数据

4.6 外部表

定义

EXTERNAL_TABLE，表是外部表，所以 Hive 并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉

4.7 管理表与外部表的互相转换

查询表的类型
```
desc formatted table_test;
```
将内部表转换为外部表
```
alter table table_test set tblproperties('EXTERNAL'='TRUE');
```
注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！

4.8 修改表

重命名表

ALTER TABLE table_name RENAME TO new_table_name

增加/修改/替换列信息

更新列

ALTER TABLE table_name CHANGE [COLUMN] old_col_name new_col_name 
column_type [COMMENT col_comment] [FIRST|ALTER column_name]

增加/替换列

ALTER TABLE table_name ADD|REPLACE COLUMNS ( col_name data_type [COMMENT col_comment],......)

REPLACE是针对整个表的替换

4.9 删除表

DROP TABLE table_test;

5、DML数据操作

5.1 数据导入

5.1.1 向表中装载数据（load）

语法
```
hive>	LOAD DATA [LOCAL] INPATH "data's path" [OVERWRITE] 
	    INTO TABLE table_name [PARTITION (partcoll=vall,...)];
```
- load data：表示加载数据
- local：表示从本地加载数据到hive表，否则从HDFS加载数据到hive表
- overwrite：表示覆盖表中已有数据，否则表示追加
- into table：表示加载到哪张表
- table_name：目标表
- partition：表示上传到指定分区

5.1.2 通过查询语句向表中插入数据（insert）

根据单张表查询结果：基本模式插入
```
hive>	INSERT INTO|OVERWRITE TABLE table_name
	    SELECT column1,column2,... FROM source_table_name; 
```
- insert into：以追加数据的方式插入到表或分区，原有数据不会删除
- insert overwrite：会覆盖表中已存在的数据

根据多张表查询结果：多表（多分区）插入模式

hive>   FROM source_table_name
        INSERT INTO TABLE table_name PARTITION(partcoll=vall_1,...)
        SELECT column1,column2,...
        INSERT INTO TABLE table_name PARTITION(partcoll=vall_2,...)
        SELECT column1,column2,...

5.1.3 查询语句中创建表并加载数据（As select ）

根据查询结果创建表，查询的结果会添加到新创建的表中

hive>   CREATE TABLE IF NOT EXISTS table_name
        AS
        SELECT column1,column2,... FROM source_table_name;

5.1.4 创建表时通过location指定加载数据路径

hive>   CREATE EXTERNAL TABLE IF NOT EXISTS table_name(
            column1 data_type,...
        )
        ROW FORMAT DELIMITED
        FIELDS TERMINATED BY "\t"
        LOCATION "/data_path_in_hdfs";  -- 指定在 hdfs 上的位置

5.2 数据导出

5.2.1 导出数据（insert）

将查询的结果导出到本地

hive>   INSERT OVERWRITE LOCAL DIRECTORY 'data_path'
        SELECT * FROM source_table_name;

去掉local关键字，表示导出到HDFS

将查询的结果格式化导出到本地

hive>   INSERT OVERWRITE LOCAL DIRECTORY 'data_path'
        ROW FORMAT DELIMITED
        FIELDS TERMINATED by "\t"
        SELECT * FROM source_table_name;

去掉local关键字，表示导出到HDFS

5.2.2 Hadoop命令导出到本地

hive>   DFS -GET 'source_table_data_path' 'local_data_path'

5.2.3 HIve shell命令导出

bash>   hive -e 'SELECT * FROM source_table_name;' > data_path_file.txt;

基本语法：（hive -f/-e 执行语句或者脚本 > file）

5.3 表数据清除

hive>   TRUNCATE TABLE table_name;

Truncate 只能删除管理表，不能删除外部表中数据

5.4 Import 和 export

export 和 import 主要用于两个 Hadoop 平台集群之间 Hive 表迁移

export

hive>   EXPORT TABLE source_table_name TO "hdfs_path";

在导出路径会生成 数据+元数据信息

import
```
hive>   IMPORT TABLE table_name FROM "hdfs_path";
```
注意：需要先用 export 导出后，再将数据导入
- 不能导入有数据的已存在的表中
- 能导入已存在表中，但需要该表无数据

6、查询

语法

SELECT [ALL|DISTINCT] select_expr1,select_expr2,...
FROM table_reference
[WHERE where_condition]
[GROUP by col_list]
[HAVING having_condition]
[ORDER by col_list]
[CLUSTER BY  col_list]|[DISTRIBUTE BY col_list]|[SORT BY col_list]
[LIMIT number]

6.1 基本查询

6.1.1 数据准备

dept_data.txt

10,ACCOUNTING,1700
20,RESEARCH,1800
30,SALES,1900
40,OPERATIONS,1700

emp_data.txt

7369,SMITH,CLERK,7902,1980-12-17,800.00,20
7499,ALLEN,SALESMAN,7698,1981-2-20,1600.00,300.00,30
7521,WARD,SALESMAN,7698,1981-2-22,1250.00,500.00,30
7566,JONES,MANAGER,7839,1981-4-2,2975.00,,20
7654,MARTIN,SALESMAN,7698,1981-9-28,1250.00,1400.00,30
7698,BLAKE,MANAGER,7839,1981-5-1,2850.00,,30
7782,CLARK,MANAGER,7839,1981-6-9,2450.00,,10
7788,SCOTT,ANALYST,7566,1987-4-19,3000.00,,20
7839,KING,PRESIDENT,1981-11-17,5000.00,,10
7844,TURNER,SALESMAN,7698,1981-9-8,1500.00,0.00,30
7876,ADAMS,CLERK,7788,1987-5-23,1100.00,,20
7900,JAMES,CLERK,7698,1981-12-3,950.00,,30
7902,FORD,ANALYST,7566,1981-12-3,3000.00,,20
7934,MILLER,CLERK,7782,1982-1-23,1300.00,,10

local_data.txt
```
1700,Beijing
1800,London
1900,Tokyo
```

create table dept

create table if not exists dept(
    deptno int,
    dname string,
    loc int
)
row format delimited
fields terminated by ',';

create table emp

create table if not exists emp(
    empno int,
    ename string,
    job string,
    mgr int,
    hiredate string,
    sal double,
    comm double,
    deptno int
)
row format delimited
fields terminated by ",";

create table location

create table if not exists location(
	loc int,
	loc_name string
)
row format delimited 
fields terminated by ',';

加载数据

hive>	load data local inpath "/home/nuochengze/sublime_dir/dept_data.txt" overwrite into table dept;
hive>	load data local inpath "/home/nuochengze/sublime_dir/emp_data.txt" overwrite into table emp;

6.1.2 查询字段

hive>	select * from dept;  -- 全字段
		select * from emp; -- 全字段
		select empno,ename from emp; -- 部分字段

SQL 语言大小写不敏感
SQL 可以写在一行或者多行
关键字不能被缩写也不能分行
各子句一般要分行写
使用缩进提高语句的可读性

6.1.3 别名

紧跟列名，也可以在列名和别名之间加入关键字‘AS’

hive>	select ename name from emp;
		select ename as name from emp;

6.1.4 算术运算符

运算符	描述
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 按位取反

6.1.5 常用函数

常用函数
总行数（count）
最大值（max）
最小值（min）
总和（sum）
平均值（avg）

6.1.6 limit语句

典型的查询会返回多行数据。LIMIT 子句用于限制返回的行数

hive>	select * from emp limit 5; -- 只返回5行

6.1.8 比较运算符（Between/In/ Is Null）

操作符	支持的数据类型	描述
A=B	基本数据类型	如果 A 等于 B 则返回 TRUE，反之返回 FALSE
A<=>B	基本数据类型	如果 A 和 B 都为 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
A IS NOT NULL	所有数据类型	如果 A 不等于 NULL，则返回 TRUE，反之返回 FALSE
IN(数值 1, 数值 2)	所有数据类型	使用 IN 运算显示列表中的值
A [NOT] LIKE B	STRING 类型	B 是一个 SQL 下的简单正则表达式，也叫通配符模式，如果 A 与其匹配的话，则返回 TRUE；反之返回 FALSE。B 的表达式说明如下：‘x%’表示 A 必须以字母‘x’开头，‘%x’表示 A 必须以字母’x’结尾，而‘%x%’表示 A 包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用 NOT 关键字则可达到相反的效果
A RLIKE B, A REGEXP B	STRING 类型	B 是基于 java 的正则表达式，如果 A 与其匹配，则返回 TRUE；反之返回 FALSE。匹配使用的是 JDK 中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串 A 相匹配，而不是只需与其字符串匹配

6.1.9 Like 和 RLike

选择条件可以包含字符或数字
- % 代表零个或多个字符(任意个字符)
- _ 代表一个字符
RLIKE 子句是 Hive 中这个功能的一个扩展，其可以通过 Java 的正则表达式这个更强大的语言来指定匹配条件
```
hive>	select * from emp t where t.ename rlike '[A]'; -- 选出名字中含有A的记录
```

6.1.10 逻辑运算符（AND/OR/NOT）

操作符	含义
AND	逻辑并
OR	逻辑或
NOT	逻辑否

6.2 分组

6.2.1 Group By语句

GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作

hive>	select t.deptno,avg(t.sal)   -- 求部门的平均薪水
		from emp t
		group by t.deptno;

6.2.2 Having 语句

having 与 where 不同点
- where 后面不能写分组函数，而 having 后面可以使用分组函数
- having 只用于 group by 分组统计语句

hive>	select t.deptno,avg(t.sal)   -- 求部门的平均薪水
		from emp t
		group by t.deptno
		having avg(t.sal)>2000; -- 且大于2000

6.3 Join语句

等值连接（join）
内连接（inner join）

只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来
左外连接（left join）

左外连接：JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回
又外连接（right join）

右外连接：JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回
满外连接（full join）

满外连接：将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用 NULL 值替代
多表连接

连接 n 个表，至少需要 n-1 个连接条件。例如：连接三个表，至少需要两个连接条件
```
select a.empno,a.ename,b.dname,b.loc,c.loc_name
from emp a
left join dept b
on b.deptno = a.deptno
left join location c
on c.loc = b.loc
where b.dname is not null;
```
- hive的执行顺序：Hive 总是按照从左到右的顺序执行

6.4 笛卡尔积

笛卡尔积产生的条件
- 省略连接条件
- 连接条件无效
- 所有表中的所有行互相连接

6.5 排序

6.5.1 全局排序（order by，只有一个reduce)

order by 全局排序，只会产生一个reduce

select t.empno,t.ename,t.sal
from emp t
order by t.sal desc;

ASC（ascend）: 升序（默认）
DESC（descend）: 降序
ORDER BY 子句在 SELECT 语句的结尾

6.5.2 每个 Reduce内部排序（Sort By）

Sort By：对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排序，此时可以使用 sort by。
Sort by 为每个 reducer 产生一个排序文件。每个 Reducer 内部进行排序，对全局结果集来说不是排序
步骤
1. 设置reduce个数
```
hive>	set mapreduce.job.reduces=3;
```
2. 根据部门编号降序查看员工信息
```
hive>	select empno,ename,deptno from emp sort by deptno;
```
3. 将查询结果导入到文件中（按照部门编号降序排序）
```
hive>	insert overwrite local directory
		"./sort_by"
		select empno,ename,deptno from emp sort by deptno;
```
  - 通过查看分区数据，发现其在区内是有序的，需要搭配distribute by一起使用

6.5.3 分区（Distribute By）

Distribute By：在有些情况下，我们需要控制某个特定行应该到哪个 reducer，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by 类似 MR 中 partition （自定义分区），进行分区，结合 sort by 使用。

对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute by 的效果

步骤

设置reduce个数
```
hive> 	set mapreduce.job.reduces=3;
```

将查询结果导入到文件中

hive>	insert overwrite local directory
		"./distribute_by"
		select * from emp distribute by deptno sort by empno desc;

说明
- distribute by 的分区规则是根据分区字段的 hash 码与 reduce 的个数进行模除后，余数相同的分到一个区
- Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前

6.5.4 Cluster By

当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式
以下两种写法等价:

hive>	select * from emp cluster by deptno;
hive> 	select * from emp distribute by deptno sort by deptno;

cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是升序排序，不能指定排序规则为 ASC 或者 DESC。

7、分区表和分桶表

7.1 分区表

分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive 中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过 WHERE 子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多

7.1.1 分区表的基本操作

创表语法

CREATE TABLE table_name(column1 data_type,...)
PARTITIONED BY (column2 string...)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ",";

create table dept_partition(
    deptno int,
    dname string,
    loc string
)
partitioned by (day string)
row format delimited
fields terminated by ",";

注意：分区字段不能是表中已经存在的数据，可以将分区字段看作表的伪列。

加载数据

load data local inpath "/home/nuochengze/sublime_dir/dept1.log"
into table dept_partition partition(day='2021-09-21');

注意：分区表加载数据时，必须指定分区

查询分区表中的数据

单分区查询

hive>	select * from dept_partition where day='2021-09-21';

多分区联合查询

hive>	select * from dept_partition where day='2021-09-21'
		union 
		select * from dept_partition where day='2021-09-22'
		union all
		select * from dept_partition where day='2021-09-23';

增加分区

创建单个分区

hive>	alter table dept_partition add partition(day='2021-09-24');

创建多个分区

hive>	alter table dept_partition add partition(day='2021-09-25') partition(day='2021-09-26');

注意在同时创建多个分区时，分区之间以空格隔开

删除分区

删除单个分区

hive>	alter table dept_partition drop partition(day='2021-09-24');

删除多个分区

hive>	alter table dept_partition drop partition(day='2021-09-25'),partition(day='2021-09-26');

查看分区表中有多少个分区
```
hive>	show partitions dept_partition;
```
查看分区表结构
```
hive>	desc formatted dept_partition;
```

7.1.2 二级分区

创建二级分区表

create table dept_partition2(
    deptno int,
    dname string,
    loc string
)
partitioned by (day string,hour string)
row format delimited
fields terminated by ",";

加载数据

hive>	load data local inpath "/home/nuochengze/sublime_dir/dept1.log"
		into table dept_partition2 partition(day='2021-09-21',hour='12');

查询分区数据

hive>	select * from dept_partition2 where day='2021-09-21' and hour='13';

7.1.3 上传数据与分区表进行关联

说明：把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式

上传数据后修复

创建文件夹

hive>	dfs -mkdir -p /user/hive/warehouse/test.db/dept_partition2/day=2021-09-22/hour=13;

上传数据

hive>	dfs -put /home/nuochengze/sublime_dir/dept1.log 
		/user/hive/warehouse/test.db/dept_partition2/day=2021-09-22/hour=13;

查询上传的数据

hive>	select * from dept_partition2 where day='2021-09-22' and hour='13';  -- 未找到数据

执行修复命令

hive>	msck repair table dept_partition2;

再次查询上传的数据

hive>	select * from dept_partition2 where day='2021-09-24' and hour='13';  -- 已找到上传数据

上传数据后添加分区

创建文件夹

hive>	dfs -mkdir -p /user/hive/warehouse/test.db/dept_partition2/day=2021-09-24/hour=11;

上传数据

hive>	dfs -put /home/nuochengze/sublime_dir/dept1.log 
		/user/hive/warehouse/test.db/dept_partition2/day=2021-09-24/hour=11;

执行添加分区

hive>	alter table dept_partition2 add partition(day='2021-09-24',hour='11');

查询数据

hive>	select * from dept_partition2 where day='2021-09-24' and hour='11';  -- 已找到上传数据

创建文件夹后load数据到分区

创建文件夹

hive>	dfs -mkdir -p /user/hive/warehouse/test.db/dept_partition2/day=2021-09-25/hour=11;

上传数据

hive>	load data local inpath "/home/nuochengze/sublime_dir/dept1.log"
		into table dept_partition2 partition(day='2021-09-25',hour='11');

查询数据

hive>	select * from dept_partition2 where day='2021-09-25' and hour='11';  -- 已找到上传数据

7.1.4 动态分区的参数设置

关系型数据库中，对分区表 Insert 数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive 中也提供了类似的机制，即动态分区(Dynamic Partition)，不过在使用 Hive 的动态分区，需要进行相应的配置。

开启动态分区功能（默认 true，开启）
```
hive>	set hive.exec.dynamic.partition=true
```
设置为非严格模式（动态分区的模式，默认 strict，表示必须指定至少一个分区为静态分区，nonstrict 模式表示允许所有的分区字段都可以使用动态分区。）
```
hive> 	set hive.exec.dynamic.partition.mode=nonstrict
```
在所有执行 MR 的节点上，最大一共可以创建多少个动态分区。默认 1000
```
hive>	set hive.exec.max.dynamic.partitions=1000
```
在每个执行 MR 的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即 day 字段有 365 个值，那么该参数就需要设置成大于 365，如果使用默认值 100，则会报错
```
hive>	set hive.exec.max.dynamic.partitions.pernode=100
```
整个 MR Job 中，最大可以创建多少个 HDFS 文件。默认 100000
```
hive>	set hive.exec.max.created.files=100000
```
当有空分区生成时，是否抛出异常。一般不需要设置。默认 false
```
hive>	set hive.error.on.empty.partition=false
```

示例：

创建目标分区表

create table dept_partition3(
    deptno int,
    dname string
)
partitioned by (loc int)
row format delimited
fields terminated by ",";

设置动态分区

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

加载数据

以下两种方式等同：

hive>	load data local inpath "/home/nuochengze/sublime_dir/dept_data.txt" into table dept_partition3;

hive>	insert into table dept_partition3 partition(loc) select deptno, dname, loc from dept;

查看目标分区表的分区情况
```
hive>	show partitions dept_partition3;
```

7.2 分桶表

前提：分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分

分区针对的是数据的存储路径；分桶针对的是数据文件

创建分桶表

准备数据buck_data.txt

1001,s1
1002,s2
1003,s3
1004,s4
1005,s5
1006,s6
1007,s7
1008,s8
1009,s9
1010,s10
1011,s11
1012,s12
1013,s13
1014,s14
1015,s15
1016,s16

创建分桶表

create table stu_buck(
    id int,
    name string
)
clustered by(id) into 4 buckets
row format delimited
fields terminated by ",";

注意：分桶字段必须是表中已经存在的数据

查看表结构
```
hive>	desc formatted stu_buck;
```

导入数据到分桶表

hive>	load data local inpath "/home/nuochengze/sublime_dir/buck_data.txt" into table stu_buck;

hive>	insert into table stu_buck select * from stu_buck1;  -- insert方法导入数据

查看创建的分桶表中是否分成 4 个桶
分桶规则

根据结果可知：Hive 的分桶采用对分桶字段的值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中

分桶表操作需要注意的事项
- reduce 的个数设置为-1,让 Job 自行决定需要用多少个 reduce 或者将 reduce 的个数设置为大于等于分桶表的桶数
- 从 hdfs 中 load 数据到分桶表中，避免本地文件找不到问题
- 不要使用本地模式

7.3 抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive 可以通过对表进行抽样来满足这个需求，需要搭配分桶表使用

语法: TABLESAMPLE(BUCKET x OUT OF y)

查询表stu_buck中的数据：

hive>	select * from stu_buck tablesample(bucket 1 out of 4 on id);

注意：x 的值必须小于等于 y 的值，否则报错

8、函数

8.1 系统内置函数

查看系统自带的函数
```
hive>	show functions;
```
显示自带的函数的用法
```
hive>	desc function upper;
```
详细显示自带的函数的用法
```
hive>	desc function extended upper;
```

8.2 常用内置函数

8.2.1 空字段赋值（NVL）

函数说明

NVL：给值为 NULL 的数据赋值，它的格式是 NVL( value，default_value)。它的功能是如果 value 为 NULL，则 NVL 函数返回 default_value 的值，否则返回 value 的值，如果两个参数都为 NULL ，则返回 NULL

8.2.2 CASE WHEN THEN ELSE END

数据准备

悟空,A,男
大海,A,男
宋宋,B,男
凤姐,A,女
婷姐,B,女
婷婷,B,女

表创建及数据加载

create table emp_sex(
    name string,
    dept_id string,
    sex string
)
row format delimited
fields terminated by ",";

load data local inpath "/home/nuochengze/sublime_dir/emp_sex.txt" into table emp_sex;

case when 使用

select  t.dept_id,
        sum(case when t.sex="男" then 1 else 0 end) as man,
        sum(case when t.sex="女" then 1 else 0 end) as female
from emp_sex t
group by t.dept_id;

8.2.3 行转列

相关函数说明
- CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
- CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
  - 注意: CONCAT_WS must be "string or array
- COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生 Array 类型字段

数据准备

孙悟空,白羊座,A
大海,射手座,A
宋宋,白羊座,B
猪八戒,白羊座,A
凤姐,射手座,A
苍老师,白羊座,B

表创建及数据加载

create table if not exists person_info(
    name string,
    constellation string,
    blood_type string
)
row format delimited
fields terminated by ",";

load data local inpath "/home/nuochengze/sublime_dir/person_info.txt" into table person_info;

行转列

select b.concat_word,concat_ws("|",collect_set(b.name))
from(
    select a.name,
           concat_ws(",",a.constellation,a.blood_type) as concat_word
    from person_info a
) b
group by b.concat_word;

8.2.4 列转行

函数说明
- EXPLODE(col)：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。
- LATERAL VIEW
  - 用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
  - 解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合

数据准备

《疑犯追踪》	悬疑,动作,科幻,剧情
《Lie to me》	悬疑,警匪,动作,心理,剧情
《战狼 2》	战争,动作,灾难

表创建及数据加载

create table if not exists movie(
    movie_name string,
    category string
)
row format delimited
fields terminated by "\t";

load data local inpath  "/home/nuochengze/sublime_dir/movie.txt" overwrite into table movie;

列转行

select a.movie_name, category_name
from movie a
lateral VIEW explode(split(a.category,",")) movie_info_tmp as category_name;

列转行的数据格式还原

select b.movie_name,concat_ws(",",collect_set(b.category_name))
from (
    select a.movie_name, category_name 
    from movie a
    lateral VIEW explode(split(a.category,",")) movie_info_tmp as category_name
) b
group by b.movie_name;

8.2.5 窗口函数

相关函数说明
- over()
  
  指定分析函数工作的数据窗口大小，这个数据窗口大小随行的变化而变化
- current row：当前行
- n preceding：往前n行数据
- n following：往后n行数据
- unbounded：起点
  - unbounded preceding：表示从前面的起点
  - unbounded following：表示到后面的终点
- lag(col,n,default_val)：往前第n行数据
- lead(col,n,defalut_val)：往后第n行数据
- ntile(n)：
  
  把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号。
注意：n必须为int类型

需求

查询在 2017 年 4 月份购买过的顾客及总人数

select t.name,t.orderdate,t.cost,count(1) over() total_201704
from business t 
where substr(t.orderdate,1,7)="2017-04"

查询各顾客的购买明细及其月购买总额

select t.name,t.orderdate,t.cost,sum(cost) over(partition by name,month(t.orderdate))
from business t
-- where substr(t.orderdate,1,7)='2017-01'

将每个顾客的 cost 按照日期进行累加

rows 必须跟在 order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量

所有行相加

select t.name,t.orderdate,t.cost,
    sum(cost) over(), -- 所有行相加
from business t;

按name分组,组内数据相加

select t.name,t.orderdate,t.cost,
    sum(cost) over(partition by t.name) -- 按name分组,组内数据相加
from business t;

按name分组，组内数据累加

select t.name,t.orderdate,t.cost,
    sum(cost) over(partition by t.name order by t.orderdate) -- 按name分组，组内数据累加
from business t;

由起点到当前行的聚合

select t.name,t.orderdate,t.cost,
    sum(cost) over(partition by t.name order by t.orderdate rows between
                unbounded preceding and current row ) -- 由起点到当前行的聚合
from business t;

当前行和前面一行做聚合

select t.name,t.orderdate,t.cost,
    sum(cost) over(partition by t.name order by t.orderdate rows between
                1 preceding and current row ) -- 当前行和前面一行做聚合
from business t;

当前行和前边一行及后面一行（连续3行相加）

select t.name,t.orderdate,t.cost,
    sum(cost) over(partition by t.name order by t.orderdate rows between
                1 preceding and 1 following ) -- 当前行和前边一行及后面一行（连续3行相加）
from business t;

当前行及后面所有行

select t.name,t.orderdate,t.cost,
    sum(cost) over(partition by t.name order by t.orderdate rows between
                current row and unbounded following ) -- 当前行及后面所有行
from business t;

查看顾客上次的购买时间

select t.name,t.orderdate,t.cost,
    lag(t.orderdate,1,"Null") over(partition by t.name order by t.orderdate) as last_orderdate,
    lag(t.orderdate,1) over (partition by t.name order by t.orderdate) 
from business t;

查询前 20%时间的订单信息

select a.*
from (
select t.name,t.orderdate,t.cost, ntile(5) over(order by t.orderdate) sorted
from business t
) a
where a.sorted=1;

8.2.6 Rank

函数说明

RANK() 排序相同时会重复，总数不会变（1，2，2，4，...，n）

DENSE_RANK() 排序相同时会重复，总数会减少（1，2，2，3，...，n-1）

ROW_NUMBER() 会根据顺序计算（1，2，3，4，...，n）

8.2.7 常用函数

8.2.7.1 常用日期函数

unix_timestamp：返回当前或指定时间的时间戳

select unix_timestamp();
select unix_timestamp("2021-10-03",'yyyy-MM-dd');

from_unixtime：将时间戳转为日期格式

select from_unixtime(1633219200);
select from_unixtime(1633219200,'yyyy-MM-dd');

current_date：当前日期
```
select current_date;
```
current_timestamp：当前的日期加时间
```
select current_timestamp;
```
to_date：抽取日期部分
```
select to_date("2021-10-03 11:22:11");
```
year：获取年

month：获取月

day：获取日

hour：获取时

minute：获取分

second：获取秒
```
select year('2021-10-03 12:12:12');
```
weekofyear：当前时间是一年中的第几周
```
select weekofyear('2021-10-03 12:12:12');
```
dayofmonth：当前时间是一个月中的第几天
```
select dayofmonth('2021-10-03 12:12:12');
```

months_between：两个日期间的月份

select months_between("2021-10-03","2020-01-20");

add_months：日期加减月
```
select add_months("2021-10-03",-3);
```

datediff：两个日期相差的天数

select datediff("2021-10-03","2020-01-20");

date_add：日期加天数
```
select date_add("2021-10-03",-3);
```
date_sub：日期减天数
```
select date_sub("2021-10-03",-3);
```
last_day：日期的当月的最后一天
```
select last_day("2021-10-03");
```

date_format()：格式化日期

select date_format('2021-10-03 12:12:12','yyyy-MM-dd HH:mm:ss'); -- 24小时
select date_format('2021-10-03 12:12:12','yyyy-MM-dd hh:mm:ss'); -- 12小时

8.2.7.2 常用取整函数

round：四舍五入

select round(3.14);
select round(3.54);

ceil：向上取整
```
select ceil(3.14);
select ceil(3.54);
```

floor：向下取整

select floor(3.14);
select floor(3.54);

8.2.7.3 常用字符串操作函数

upper：转大写
```
select upper('low');
```
lower：转小写
```
select lower('UPPER');
```
length：长度
```
select length("nuochengze");
```
trim：前后去空格
```
select trim(" nuochengze ");
```
lpad：向左补齐，到指定长度
```
select lpad('n',9,'0');
```
rpad：向右补齐，到指定长度
```
select rpad('n',9,'0');
```
regexp_replace：使用正则表达式匹配目标字符串，匹配成功后替换！
```
SELECT regexp_replace('2021/10/03', '/', '-');
```

8.2.7.4 常用集合操作函数

size：集合中元素的个数
map_keys：返回map中的key
map_values：返回map中的value
array_contains：判断array中是否包含某个元素
sort_array：将array中的元素排序
grouping_set：多维分析

8.3 自定义函数

当hive提供的内置函数无法满足业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）

UDF：user-defined function：

UDF（User-Defined-Function）：一进一出，类似于length()
UDAF（User-Defined Aggregation Function）：聚集函数，多进一出，类似于 count()、sum()
UDTF（User-Defined Table-Generating Functions）：一进多出，类似于 lateral view explode()

编程步骤：

继承 Hive 提供的类

org.apache.hadoop.hive.ql.udf.generic.GenericUDF
org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;

实现类中的抽象方法
在 hive 的命令行窗口创建函数
1. 添加 jar：add jar linux_jar_path
2. 创建function：create [temporary] function [dbname.]function_name AS class_name;

在 hive 的命令行窗口删除函数

drop [temporary] function [if exists] [dbname.]function_name;

8.4 自定义UDF函数

package hive.testUDF;

import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;

/*
    自定义UDF函数，需要继承GenericUDF类
* */
public class MyStringLength extends GenericUDF{
    // 初始化
    @Override
    public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
        // 初始化参数的个数
        if(arguments.length !=1){
            throw new UDFArgumentLengthException("参数个数不对!");
        }
        // 判断参数输入的类型
        if(arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)){
            throw new UDFArgumentTypeException(0,"参数类型不对!");
        }
        // 函数本身返回值为int，需要返回int类型的鉴别器对象
        return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
    }

    // 函数的逻辑处理
    @Override
    public Object evaluate(DeferredObject[] arguments) throws HiveException {
        if(arguments[0].get() == null){
            return 0;
        }
        return arguments[0].get().toString().length();
    }

    @Override
    public String getDisplayString(String[] children) {
        return null;
    }
}

8.5 自定义UDTF函数

package hive.testUDTF;

import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;


import java.util.ArrayList;

public class MyUDTF extends GenericUDTF {
    // 创建复用集合
    private ArrayList<String> outList = new ArrayList<>();

    @Override
    public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
        // 定义输出数据的列名和类型
        ArrayList<String> fieldNames = new ArrayList<String>();
        ArrayList<ObjectInspector> fieldOIs = new ArrayList<ObjectInspector>();
        // 添加输出数据的列名和类型
        fieldNames.add("LineToWord");
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);
    }

    @Override
    public void process(Object[] args) throws HiveException {
        // 获取原始数据
        String strings = args[0].toString();
        // 获取数据出入的第二个参数
        String splitKey = args[1].toString();
        // 将原始数据按照传入的分隔符进行切分
        String[] fields = strings.split(splitKey);
        // 遍历切分后的结果，并写出
        for (String field : fields) {
            // 清空复用的集合
            outList.clear();
            // 将每个单词添加至集合
            outList.add(field);
            // 将集合内容写出
            forward(outList);
        }

    }

    @Override
    public void close() throws HiveException {

    }
}

9、hive优化

9.1 执行计划（Explain）

基本语法

EXPLAIN [EXTENDED | DEPENDENCY | AUTHORIZATION] query

9.2 Fetch抓取

默认配置

  <property>
    <name>hive.fetch.task.conversion</name>
    <value>more</value>
    <description>
      Expects one of [none, minimal, more].
      Some select queries can be converted to single FETCH task minimizing latency.
      Currently the query should be single sourced not having any subquery and should not have
      any aggregations or distincts (which incurs RS), lateral views and joins.
      0. none : disable hive.fetch.task.conversion
      1. minimal : SELECT STAR, FILTER on partition columns, LIMIT only
      2. more    : SELECT, FILTER, LIMIT only (support TABLESAMPLE and virtual columns)
    </description>
  </property>

说明

Fetch抓取是指，Hive中对某些情况的查询可以不必使用MapReduce计算

老版本的Hive默认是minimal，该属性修改为more以后，在全局查找、字段查找、limit查找等都不走mapreduce

9.3 本地模式

在hive的输入数据量非常小的情况下，通过本地模式在单机上处理所有的任务，避免集群通讯，减少时间消耗。

-- 开启本地mr
set hive.exec.mode.local.auto=true;
-- 设置 local mr 的最大输入数据量，当输入数据量小于这个值时采用 local mr 的方式，默认为 134217728，即 128M
set hive.exec.mode.local.auto.inputbytes.max=134217728;
-- 设置 local mr 的最大输入文件个数，当输入文件个数小于这个值时采用 local mr 的方式，默认为 4
set hive.exec.mode.local.auto.input.files.max=10;

9.4 表的优化

小表大表JOIN（MapJoin）

Hived对小表JOIN大表和大表JOIN小标进行了优化，小标放在左边和右边已经没有区别，但是习惯上会将小表写在左边。

-- 默认为true，自动选择 Mapjoin
set hive.auto.convert.join = true; 默认为 true
-- 大表小表的阈值设置（默认 25M 以下认为是小表）
set hive.mapjoin.smalltable.filesize = 25000000;

大表JOIN大表
- 空KEY过滤
  
  JOIN会自动将空值进行过滤
- 不空KEY过滤
  
  使用LEFT JOIN或RGIHT JOIN，为避免数据倾斜，需要将Key为空的字段赋一个随机的值，使数据随机均匀地分到不同的reduce上
```
select n.* from nullidtable n full join bigtable o on 
nvl(n.id,rand()) = o.id;
```
- SMB(Sort Merge Bucket join)
  
  创建分桶表，提高处理速度的同时，避免了数据倾斜，但是注意桶的个数不要超过可用CPU的核数
```
-- 启用分桶Join
set hive.optimize.bucketmapjoin = true;
set hive.optimize.bucketmapjoin.sortedmerge = true;
-- 设置文件输入格式
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
```
Group by

为避免在Reduce端产生数据倾斜，将聚合操作可以先在Map端进行部分聚合，最后在Reduce端得出结果
```
-- 是否在 Map 端进行聚合，默认为 True
set hive.map.aggr = true
-- 在 Map 端进行聚合操作的条目数目
set hive.groupby.mapaggr.checkinterval = 100000
-- 有数据倾斜的时候进行负载均衡（默认是 false）
set hive.groupby.skewindata = true
```
当启用均衡负载时，生成的查询计划会有两个MR Job，第一个 MR Job 中，Map 的输出结果会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中，从而达到负载均衡的目的；第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中（这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作。
Count(Distinct)去重统计

Count(Distinct)只会开启一个Reduce Task来完成这个任务，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换（可以使用多个reduce），需要注意GROUP BY造成的数据倾斜问题
笛卡尔积

尽量避免笛卡尔积，join 的时候不加 on 条件，或者无效的 on 条件，Hive 只能使用 1 个 reducer 来完成笛卡尔积
行列过滤
- 列处理：在 SELECT 中，只拿需要的列，如果有分区，尽量使用分区过滤，少用 SELECT *
- 行处理：在分区剪裁中，当使用外关联时，将副表的过滤条件写到where后面，查询执行谓词下推（如果将副表的过滤条件写在 Where 后面，那么就会先全表关联，之后再过滤）
分区
分桶

9.5 合理设置Map及Reduce数

Map阶段

增加 map 的方法：

根据 computeSliteSize(Math.max(minSize,Math.min(maxSize,blocksize)))=blocksize=128M 公式，调整 maxSize 最大值。让 maxSize 最大值低于 blocksize 就可以增加 map 的个数。
```
set mapreduce.input.fileinputformat.split.maxsize=100;
```

在 map 执行前合并小文件，减少 map 数

CombineHiveInputFormat 具有对小文件进行合并的功能（系统默认的格式）。HiveInputFormat 没有对小文件合并功能

set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

-- 在 map-only 任务结束时合并小文件，默认 true
SET hive.merge.mapfiles = true;
-- 在 map-reduce 任务结束时合并小文件，默认 false
SET hive.merge.mapredfiles = true;
-- 合并文件的大小，默认 256M
SET hive.merge.size.per.task = 268435456;
-- 当输出文件的平均大小小于该值时，启动一个独立的 map-reduce 任务进行文件 merge
SET hive.merge.smallfiles.avgsize = 16777216;

Reduce阶段
- 调整 reduce 个数方法一
  1. 每个 Reduce 处理的数据量默认是 256MB
```
set hive.exec.reducers.bytes.per.reducer=256000000;
```
  2. 每个任务最大的 reduce 数，默认为 1009
```
set hive.exec.reducers.max=1009;
```
  3. 计算 reducer 数的公式
```
N=min(参数 2，总输入数据量/参数 1)
```
- 调整reduce个数方法二
  - 在 hadoop 的 mapred-default.xml 文件中修改
  - 设置每个 job 的 Reduce 个数
```
set mapreduce.job.reduces = 15;
```

9.6 并行执行

Hive 会将一个查询转化成一个或者多个阶段。这样的阶段可以是 MapReduce 阶段、抽样阶段、合并阶段、limit 阶段。或者 Hive 执行过程中可能需要的其他阶段。默认情况下， Hive 一次只会执行一个阶段。不过，某个特定的 job 可能包含众多的阶段，而这些阶段可能并非完全互相依赖的，也就是说有些阶段是可以并行执行的，这样可能使得整个 job 的执行时间缩短。不过，如果有更多的阶段可以并行执行，那么 job 可能就越快完成。

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

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

9.7 严格模式

Hive 可以通过设置防止一些危险操作。

分区表不使用分区过滤

通常分区表都拥有非常大的数据集，而且数据增加迅速。没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表。

将 hive.strict.checks.no.partition.filter 设置为 true 时，对于分区表，除非 where 语句中含有分区字段过滤条件来限制范围，否则不允许执行
使用 order by 没有 limit 过滤

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

将 hive.strict.checks.orderby.no.limit 设置为 true 时，对于使用了 order by 语句的查询，要求必须使用 limit 语句
笛卡尔积

将 hive.strict.checks.cartesian.product 设置为 true 时，会限制笛卡尔积的查询。

9.8 JVM重用

七、Hadoop简明笔记 - Norni - 博客园 (cnblogs.com)

9.9 压缩

10、压缩和存储

10.1 Hadoop压缩

七、Hadoop简明笔记 - Norni - 博客园 (cnblogs.com)

开始Map输出阶段压缩(MR引擎)

开启 map 输出阶段压缩可以减少 job 中 map 和 Reduce task 间数据传输量。

-- 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;

10.2 文件存储格式

Hive 支持的存储数据的格式主要有：TEXTFILE 、SEQUENCEFILE、ORC、PARQUET

10.2.1 列式存储和行式存储

如图所示左边为逻辑表，右边第一个为行式存储，第二个为列式存储。

TEXTFILE（Hive默认格式）和 SEQUENCEFILE 的存储格式都是基于行存储的；
ORC 和 PARQUET 是基于列式存储的。

行存储的特点

查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快。
列存储的特点

因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法

在实际的项目开发当中，hive 表的数据存储格式一般选择：orc 或 parquet。压缩方式一般选择 snappy，lzo。

posted @ 2021-10-06 23:03 Norni 阅读(134) 评论(0) 编辑收藏举报

刷新页面返回顶部

一、Hive简明笔记

1、Hive基本概念

1.1 什么是Hive

1.2 Hive的优缺点

1.3 Hive架构原理

2、Hive安装

2.1 Hive安装部署

2.2 Hive元数据配置到MySQL

2.2.1 拷贝JDBC驱动

2.2.2 配置 Metastore 到 MySQL

2.3 使用元数据服务的方式访问Hive

2.4 使用JDBC方式访问Hive

2.5 hive 服务启动脚本

2.6 Hive常见属性配置

2.6.1 Hive运行日志信息配置

2.6.2 打印 当前库 和 表头

2.6.3 参数配置方式

3、Hive数据类型

3.1 基本数据类型

3.2 集合数据类型

3.3 类型转换

4、DDL数据定义

4.1 创建数据库

4.2 查询数据库

4.3 修改数据库

4.4 删除数据库

4.4 创建表

4.5 内部表

4.6 外部表

4.7 管理表与外部表的互相转换

4.8 修改表

4.9 删除表

5、DML数据操作

5.1 数据导入

5.1.1 向表中装载数据（load）

5.1.2 通过查询语句向表中插入数据（insert）

5.1.3 查询语句中创建表并加载数据（As select ）

5.1.4 创建表时通过location指定加载数据路径

5.2 数据导出

5.2.1 导出数据（insert）

5.2.2 Hadoop命令导出到本地

5.2.3 HIve shell命令导出

5.3 表数据清除

5.4 Import 和 export

6、查询

6.1 基本查询

6.1.1 数据准备

6.1.2 查询字段

6.1.3 别名

6.1.4 算术运算符

6.1.5 常用函数

6.1.6 limit语句

6.1.8 比较运算符（Between/In/ Is Null）

6.1.9 Like 和 RLike

6.1.10 逻辑运算符（AND/OR/NOT）

6.2 分组

6.2.1 Group By语句

6.2.2 Having 语句

6.3 Join语句

6.4 笛卡尔积

6.5 排序

6.5.1 全局排序（order by，只有一个reduce)

6.5.2 每个 Reduce内部排序（Sort By）

6.5.3 分区（Distribute By）

6.5.4 Cluster By

7、分区表和分桶表

7.1 分区表

7.1.1 分区表的基本操作

7.1.2 二级分区

7.1.3 上传数据与分区表进行关联

7.1.4 动态分区的参数设置

7.2 分桶表

7.3 抽样查询

8、函数

8.1 系统内置函数

8.2 常用内置函数

8.2.1 空字段赋值（NVL）

8.2.2 CASE WHEN THEN ELSE END

8.2.3 行转列

8.2.4 列转行

2.6.2 打印当前库和表头