目录
6.2.3.Built-in Aggregate Functions (UDAF)实例
6.2.4.Built-in Table-Generating Functions (UDTF) 表生成函数实例
7.6.2 设置map输出和reduce输出进行合并的相关参数：
一、Hive 概述
1.1 Hive 是什么
- 由Facebook开源用于解决海量结构化日志的数据统计
- 基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射成一张表，并且提供类SQL的查询功能
- Hive仅仅是一个工具，本身不存储数据只提供一种管理方式，同时也不涉及分布式概念，就是个软件而已
- Hive本质就是MapReduce，将类SQL（HQL）转换成MapReduce程序
1.1.1 HQL转换MR流程
解释：
- Hive处理的数据存储在HDFS
- Hive分析数据底层默认实现是MapReduce[可以修改为tez或spark]
Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases. - 执行的程序运行在yarn上
- Hive相当于Hadoop的一个客户端
- Hive不是分布式
1.2 Hive 优缺点
1.2.1 优点
- 操作接口采用类SQL语法，提供快速开发的能力（简单、易上手）
- 避免去写MR，减少开发人员学习成本
- Hive的延迟比较高（因为MR延迟高），因此Hive常用于数据分析
- Hive优势在于处理大数据（数据量少真不如MySQL等）
- Hive支持用户自定义函数，可以根据自己的需求实现自己的函数
1.2.2 缺点
Hive的HQL表达能力有限（MR决定的）：
- 迭代算法无法表达
- 不适用于数据挖掘
Hive的效率比较低：
- Hive自动生成的MR作业，通常情况不够智能
- Hive调优难（只能对资源，SQL层面调优，无法深入作业底层逻辑
1.3 Hive 架构原理
- 用户接口：Client
CLI（hive shell）、JDBC/ODBC（java访问hive）、WEBUI（浏览器访问hive） - 元数据：Metastore
包括表名、表所属的数据库、表的拥有者、列/分区字段、表的类型、表数据所在的目录等（自带个derby数据库，推荐配置到MySQL） - 底层存储：HDFS
使用HDFS进行存储，使用MapReduce计算 - 驱动器：Driver
解析器（SQL Parser）：将SQL字符串转换成抽象语法树AST，并对语法树进行语法分析，如：SQL语法、表/字符是否存在 编译期（Physical Plan）：将AST编译生成逻辑执行计划 优化器（Query Optimizer）：对逻辑执行计划进行优化 执行器（Execution）：把逻辑执行计算转换成运行的物理计划，即MR/Spark
Hive通过给用户提供的一系列交互接口，接受到用户编写的SQL，使用自己的Driver结合MetaStore，将SQL指令翻译成MapReduce提交到Hadoop中执行，将执行结果输出到用户交互接口。
1.4 Hive 和传统数据库比较
Hive除了提供类似SQL语法外和传统数据库没有任何相似之处，Hive是站在数据仓库出发点而设计的。
1.4.1 数据存储位置
Hive是建立在Hadoop之上，所有的Hive数据都是存储在HDFS上；传统数据库将数据保存在本地文件系统中；因此Hive能够处理更大更多的数据
1.4.2 数据更新
Hive是针对数据仓库应用设计，因此数据一次写入多次读出，即Hive中不建议对数据进行改写操作，所有数据都是在加载的时候确定好；对于数据库通常需要进行频繁的增删查改
1.4.3 索引
Hive在加载数据过程不会对数据进行任何处理，因为数据量庞大建立索引并不划算，因此Hive访问数据中满足特定值需要暴力扫描真个数据，因此访问延迟高。由于MapReduce的引入，Hive可以并行访问数据，即便没有索引也可用于大数据量的访问；传统数据库通常针对一个或多个列建立索引，因此在访问数据是延迟低效率高，即Hive不适合实时数据分析
1.4.4 执行
Hive 的执行引擎为MR/Spark，传统数据库都有自己的执行引擎
1.4.5 可拓展性
由于Hadoop的高拓展性，因此Hive也具备很强的拓展性；传统数据库的拓展会受到一定的限制
1.4.6 数据规模
Hive可以利用MapReduce进行大规模数据的并行计算；传统数据库支持的数据规模较小
二、Hive 基础
2.1 Hive 版本的选择
建议使用 1.x 版本 hive-3.1.2
只需要在一个节点上安装（Master节点）
2.2. 步骤
-
下载
hive.apache.org -
拷贝到linux
winscp
设置共享文件夹 -
解压
tar -zxvf apache-hive-1.2.1-bin.tar.gz -C /opt/programfile/ -
配置环境变量（/etc/profile）
export HIVE_HOME=/opt/programfile/hive export PATH= $PATH:$HIVE_HOME/bin使新的配置生效：source /etc/profile
-
Hive的基本配置
改名
cp hive-env.sh.template hive-env.sh修改配置（指定hadoop的路径，因为hive基于Hadoop运行的）
HADOOP_HOME=/opt/programfile/hadoop
export HIVE_CONF_DIR=/opt/programfile/hive/conf开启集群
start-dfs.sh
start-yarn.sh配置数据仓库的存储路径（数据最终存储在HDFS上）（在HDFS上配置的路径）
注意：此路径需要具有写权限创建数据存储目录：
hdfs dfs -mkdir /tmp
hdfs dfs -mkdir -p /user/hive/warehouse修改权限：
hdfs dfs -chmod 777 /tmp
hdfs dfs -chmod 777 /user/hive/warehouse配置日志的存储位置：
日志文件默认存储位置：/tmp/zhangsan/hive.log 设置用户自定义的存储位置： 1. 创建 hive-log4j.properties cp hive-log4j.properties.template hive-log4j.properties 2. 修改参数 hive.log.dir=/opt/programfile/hive/logs 3. 重新进入hive，当前日志文件所在的路径发生的变更hive-site.xml中进行配置
配置数据仓库的位置： <property> <name>hive.metastore.warehouse.dir</name> <value>/user/hive/warehouse</value> </property> 配置当前数据库提示信息： <property> <name>hive.cli.print.current.db</name> <value>false</value> </property> 配置查询结果的字段提示信息： <property> <name>hive.cli.print.header</name> <value>true</value> </property> -
将元数据配置到MySQL中需要初始化，初始化命令（其余步骤可自行百度）：
schematool -dbType mysql -initSchemaMySQL管理：
（1）查看mysql服务是否启动
ps -ef|grep mysqld（2）启动、关闭mysql服务
service mysqld restart 重启
service mysqld start 启动
service mysqld stop 停止（3）配置文件
/etc/my.cnf（4）常见管理命令
USE 数据库名 使用数据库
SHOW DATABASES 列出 MySQL 数据库管理系统的数据库列表
SHOW TABLES 显示指定数据库的所有表
SHOW COLUMNS FROM 数据表 显示数据表的属性
SHOW INDEX FROM 数据表 显示数据表的详细索引信息
SHOW TABLE STATUS LIKE [FROM db_name] [LIKE 'pattern'] \G: 该命令将输出Mysql数据库管理系统的性能及统计信息。 -
Hive元数据存到到MySQL的参数配置
目的：hive如何能连接上mysql
步骤：
1. 驱动
mv /opt/software/mysql-connection-java-xxx.jar /opt/programfile/hive/lib/
2. 连接mysql参数配置：user password driver url
hive-default.xml.template ， hive的配置文件模板
3. 创建一个配置文件：hive-site.xml
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://192.168.175.200:3306/metastore?createDatabaseIfNotExist=true</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>root</value>
</property>
</configuration>
2.3 Hive 基本操作
- 启动hive
[root@master hive-3.2.1]# hive - 查看数据库
hive (hive)> show databases; OK database_name default hive Time taken: 0.02 seconds, Fetched: 2 row(s)hive自带一个default数据库，默认也是进这个数据库
-
切换数据库
hive (hive)> use hive; OK Time taken: 0.031 seconds -
创建表
hive (hive)> create table if not exists tbl_1(id int,name string); OK Time taken: 0.628 seconds和MySQL语法基本一致，只是Hive的数据类型和Java类似
-
查看表结构
hive (hive)> desc tbl_1; OK col_name data_type comment id int name string Time taken: 0.084 seconds, Fetched: 2 row(s) -------------------- 分隔符 ------------------- # 查看表的详细信息 hive (hive)> desc formatted tbl_1; OK col_name data_type comment # col_name data_type comment id int name string # Detailed Table Information Database: hive OwnerType: USER Owner: root CreateTime: Wed Aug 26 19:55:58 CST 2020 LastAccessTime: UNKNOWN Retention: 0 Location: hdfs://master:9000/user/hive/warehouse/hive.db/tbl_1 Table Type: MANAGED_TABLE Table Parameters: COLUMN_STATS_ACCURATE {\"BASIC_STATS\":\"true\",\"COLUMN_STATS\":{\"id\":\"true\",\"name\":\"true\"}} bucketing_version 2 numFiles 0 numRows 0 rawDataSize 0 totalSize 0 transient_lastDdlTime 1598442958 # Storage Information SerDe Library: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe InputFormat: org.apache.hadoop.mapred.TextInputFormat OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat Compressed: No Num Buckets: -1 Bucket Columns: [] Sort Columns: [] Storage Desc Params: serialization.format 1 Time taken: 0.154 seconds, Fetched: 32 row(s) -
插入数据（不要用，不要用，不要用）
hive (hive)> insert into tbl_1 values(1,'zhangsan'); ... ... ... Time taken: 84.754 seconds -
查询数据
hive (hive)> select * from tbl_1; OK tbl_1.id tbl_1.name 1 zhangsan Time taken: 0.214 seconds, Fetched: 1 row(s) -
退出hive
hive (hive)> quit; -
执行hdfs shell
hive (hive)> dfs -ls /; Found 3 items drwxr-xr-x - root supergroup 0 2020-07-21 15:57 /HBase drwx-wx-wx - root supergroup 0 2020-07-21 18:27 /tmp drwxrwxrwx - root supergroup 0 2020-07-21 18:00 /user hive (default)> dfs ls /user/hive/; -
执行linux shell
hive (hive)> !pwd; /usr/local/soft/hive-3.2.1 hive (default)> ! ls /home/zhangsan; //查看本地的文件系统 -
查看hive操作的历史记录
[hadoop@master /home/hadoop]$ cat .hivehistory
2.4 Hive 常规操作
问题：插入一条数据84秒，比较耗时。显然不现实…
hive (hive)> insert into tbl_1 values(1,'zhangsan');
...
...
...
Time taken: 84.754 seconds
为此Hive插入数据将采用最暴力最直接的方式，只需要将数据文件放到hdfs制定的路径即可。但也不是什么数据都可以，需要在创建表时指定分隔符
hive (hive)> create table if not exists tbl_2(id int,name string)
> row format delimited fields terminated by '\t';
OK
Time taken: 0.118 seconds
准备数据：
[root@master data]# cat student.txt
1 zhangsan
2 lisi
3 wangwu
4 zhaoliu
5 tianqi
[root@master data]# pwd
/usr/local/soft/hive-3.2.1/data
2.4.1 hive 插入数据方式一
加载本地数据到hive
hive (hive)> load data local inpath '/usr/local/soft/hive-3.2.1/data/student.txt' into table tbl_2;
Loading data to table hive.tbl_2
OK
Time taken: 0.311 seconds
hive (hive)> select * from tbl_2;
OK
tbl_2.id tbl_2.name
1 zhangsan
2 lisi
3 wangwu
4 zhaoliu
5 tianqi
Time taken: 0.192 seconds, Fetched: 5 row(s)
评价：方便、推荐使用
2.4.2 hive 插入数据方式二
hive管理的数据是放在hdfs，可以再配置文件指定存储路径，可以进入指定路径查看，也可以通过desc formatted 表名看到该表的存储路径
- hive在hdfs存储的根路径是在/…/warehouse/下
- 一个数据库对应一个文件夹，命名方式为数据库名.db（默认数据库除外）
- 每张表也对应一个文件夹，命名方式为表名
- 数据文件直接放在表对应的文件夹下，因此通过load方式其实底层调用的是hadoop fs -put
- 默认数据库下的表直接放在warehouse下命名方式不变
基于上述规律可以有第二种插入方式，直接通过hadoop的shell将文件put到指定的hdfs路径下即可
[root@master data]# hadoop fs -put student.txt /user/hive/warehouse/hive.db/tbl_2/student_1.txt
hive (hive)> select * from tbl_2;
OK
tbl_2.id tbl_2.name
1 zhangsan
2 lisi
3 wangwu
4 zhaoliu
5 tianqi
1 zhangsan
2 lisi
3 wangwu
4 zhaoliu
5 tianqi
Time taken: 0.396 seconds, Fetched: 10 row(s)
总结：也可以用，但是必须知道表在hdfs上的路径，所以这种方式较为受限！
看过hive数据库、表、数据在hdfs上的存储结构后，尝试再次加载一次数据，这次通过load加载hdfs上的数据
hive (hive)> load data inpath '/student.txt' into table tbl_2;
Loading data to table hive.tbl_2
OK
Time taken: 0.683 seconds
load方式加载hdfs文件不需要加local（很显然），这时候再次查看hdfs信息，此时原数据将会被删除（其实就是hadoop fs -mv）
对于多次加载相同数据文件情况，hive会将数据文件重命名后上传到hdfs指定路径，重命名格式：原文件名_copy_n.txt；和windows下同名文件处理类似。
2.5 Hive 数据类型
2.5.1 基本数据类型
| Hive数据类型 | Java数据类型 | 长度 |
|---|---|---|
| tinyint | byte | 1byte |
| smalint | short | 2byte |
| int | int | 4byte |
| bigint | long | 8byte |
| boolean | boolean | true/false |
| float | float | 单精度 |
| double | double | 双精度 |
| string | String | 字符串 |
| timestamp | ||
| bigary |
常用的基本数据类型有int、bigint、double、string且不区分大小写；boolean一般使用0/1代替以减少存储量；string使用最多，一般都是处理日志，理论上可以存储2G数据（一行）。
2.5.2 集合数据类型
| 数据类型 | 描述 | 语法实例 |
| struct | 结构体，复杂无关系数据 | struct |
| map | 字典，键值对元组集合 | map |
| array | 数组，同一类型集合 | array |
struct和map区别在于map只能存储一组一组的k-v对，且一个map中的k不能相同，struct可以存储很对组相同key不同value的数据结构，即map中每组数据的key都不相同，struct中每组数据对应位置的key都是一样的；集合数据类型允许任意层次的嵌套。
复杂数据类型：array、map、struct
Array：
create table arr_tb(name string,score Array<double>)
row format delimited fields terminated by '\t' # 设置每行中的每一个属性间的分隔符
collection items terminated by '_' # 设置一个数组类型的数据项间的分隔符
lines terminated by '\n'; # 设置多行间的分隔符
Map：
create table hash_tb(name string ,score map<string,double>)
row format delimited fields terminated by '\t'
collection items terminated by '_'
map keys terminated by ':' # map中一个数据项的kv的分隔符
lines terminated by '\n';
Struct：
create table struct_tb(name string ,score struct<java:double,bigdata:double,mysql:double>)
row format delimited fields terminated by '\t'
collection items terminated by '_'
lines terminated by '\n';
2.5.3 类型转换
Hive支持类似java的数据类型转换
隐式转换
- tinyint -> smalint -> int -> bigint -> float -> double （小的数据类型可以转换成大的数据类型 ）
- string类型只有是数字才可以转换
- 所有的数据类型类型都可以转成double类型
- boolean不能转换成任意类型
强制类型转换
hive (hive)> select cast('1' as int);
OK
_c0
1
Time taken: 0.937 seconds, Fetched: 1 row(s)
hive (hive)> select cast('a' as int);
OK
_c0
NULL
Time taken: 0.184 seconds, Fetched: 1 row(s)
2.5.4 测试集合数据类型
需要存储如下格式数据（json）
{
"name": "刘德华",
"friends": ["张学友" , "郭富城"], //列表Array
"children": { //键值Map
"jasper": 3 ,
"baby": 1 ,
}
"address": { //结构Struct
"street": "皇后大道" ,
"city": "香港"
}
}
{
"name": "汪峰",
"friends": ["章子怡" , "孙俪"],
"children": {
"诺一": 8 ,
"霓娜": 6 ,
}
"address": {
"street": "长安街" ,
"city": "北京"
}
}
将一条数据转换成一行数据，去除没必要的数据，在一条数据中，字段之间用’,‘隔开，集合元素之间用’_‘隔开，map的kv用’:'隔开，因此可以转换成如下格式
刘德华,张学友_郭富城,jasper:3_baby:1,皇后大道_香港
汪峰,章子怡_孙俪,诺一:8_霓娜:6,长安街_北京
针对上述数据创建如下表：
hive (hive)> create table tbl_3(name string,friends array<string>,childress map<string,int>,address struct<street:string,city:string>)
> row format delimited fields terminated by ','
> collection items terminated by '_'
> map keys terminated by ':';
OK
Time taken: 0.124 seconds
解释：
- row format delimited fields terminated by ','设置字段分割符
- collection items terminated by '_'设置集合元素分割符
- map keys terminated by ':'设置map键值对分隔符
- lines terminated by '\n'设置行分隔符，默认\n
导入数据测试：
hive (hive)> load data local inpath '/usr/local/soft/hive-3.2.1/data/test_collection' into table tbl_3;
Loading data to table hive.tbl_3
OK
Time taken: 0.281 seconds
hive (hive)> select * from tbl_3;
OK
tbl_3.name tbl_3.friends tbl_3.childress tbl_3.address
刘德华 ["张学友","郭富城"] {"jasper":3,"baby":1} {"street":"皇后大道","city":"香港"}
汪峰 ["章子怡","孙俪"] {"诺一":8,"霓娜":6} {"street":"长安街","city":"北京"}
Time taken: 0.176 seconds, Fetched: 2 row(s)
hive (hive)> select name,friends[0],childress['baby'],address.street from tbl_3;
OK
name _c1 _c2 street
刘德华 张学友 1 皇后大道
汪峰 章子怡 NULL 长安街
Time taken: 0.222 seconds, Fetched: 2 row(s)
三.Hive数据的DDL、DML、DQL
SQL：数据库的核心语言，采纳为操作关系型数据库的国际标准语言（ ISO ）
非关系型数据库引入此语言（ hive : HQL 额外的功能 ）
分类：
数据定义语言 ：Data definition language DDL ，create /drop /alter
数据操作语言 ：Data manipulation language DML ，update /delete /insert
数据查询语言 ：Data Query language DQL ， select
1. DDL（和数据无关）
1.1 针对数据库
语法：create database [if not exists] dbName [location path];
例如：
create database db1; // /user/hive/warehouse/
create database db1;//报错
create database if not exists db1;//避免创建的数据库已经存在的错误，使用if not exists写法
create database db2 location '/user/db2.db';//指定数据库在hdfs的存储位置
创建一个数据库，默认存储在hdfs中/user/hive/warehouse/*.db
1.1.2. 查看数据库
使用某一个数据库：use databaseName;
查看所有的数据库：show databases;
过滤显示查询的数据库：show databases like 'h*';
查看某一个数据库的详细信息：desc database [extended] dbName
1.1.3. 修改数据库
已经创建的数据库其信息都是不可以修改的，包括数据库名和数据库所在的目录位置等，这里修改数据库指的是修改数据库的dbproperties的键值对
只能修改数据库的描述信息
语法：alter database dbName set dbproperties('desc'='ceshi db');
hive (test)> alter database test set dbproperties('creator'='wj');
OK
Time taken: 0.234 seconds
1.1.4. 删除数据库
语法：
drop database [if exists] dbName
对于非空数据库，上述命令无法删除
hive (d1)> drop database d1;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database d1 is not empty. One or more tables exist.)
因此可以使用cascade进行强制删除
hive (d1)> drop database d1 cascade;
OK
Time taken: 0.231 seconds
1.2 针对数据表
标准语句
create [external] table [if not exists] table_name
[(col_name data_type [comment col_comment],...)]
[comment table_comment]
[partitioned by (col_name data_type [col_name data_type],...)]
[clustered by (col_name,col_name)]
[row format ...]
[collection items ...]
[map keys ...]
[location hdfs_path]
完整版的建表语句：
create [external] table [if not exists] tbName
(col definiton [comment 字段的描述信息] ... ... ...)
[comment 表的描述信息]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] # 创建分区
[CLUSTERED BY (col_name, col_name, ...) ] # 创建分桶表
[row format ... ... ...] # 指定表的分隔符
[location path] # 指定表的存储位置
[AS select_statement] # 创建表的方式
LIKE existing_table_or_view_name # 创建表的方式
创建表方式一：直接创建
create table tbName (col type... ...)
创建表方式二：查询建表法 , AS （复制表结构，和表中所有数据到新的表中）
create table tbName
as
select * from tbName2 ;
创建表方式三：like建表法 , LIKE （只复制表结构，不复制表中所有数据到新的表中）
create table tbName
like
tbName2;
1.2.2. 查看表
show tables; 查看当前库中的所有的表
show create table tbName; 查看某一个表的具体的建表语句（获取当前表设置的分隔符信息）
show tables in dbName; 指定查看某一个数据库中的所有的表
show tables like 'stu*' ; 模糊查询多个表
desc tbName; 查看表中的具体的字段信息
desc extended tbName ; 查看表的详情 （查看外部表）
desc formatted tbName ; 查看表的详情 （查看内部表和外部表）
1.2.3. 修改表
1. 修改表的名称
alter table tbName rename to newTbName ;
2. 修改表字段的定义
添加新的列：
alter table tbName add columns(colName type... ....)
eg(添加列):
hive (hive)> alter table tbl_emp add columns(emp_id int);
OK
Time taken: 0.147 seconds
修改列定义：change可以修改 列名称 / 列类型 / 列的位置
alert table tbName change oldColName newColName newType [first|after colName]
eg(修改列):包括修改列名，列属性
hive (hive)> alter table tbl_emp change emp_id c_emp_id string;
OK
Time taken: 0.234 seconds
表结构替换： 替换调原来的表结构
alert table tbName replace columns(colName type... ....)
eg(替换列):hive (hive)> alter table tbl_emp replace columns(s_id string,c_id string,c_grade string);
OK
Time taken: 0.157 seconds
注意：hive2.0+版本中，对类型转换进行限制
小类型 》大类型 ---> 运行
大类型 》小大类型 ---> 运行
1.2.4. 删除表
1. 删除内部表
drop table tbName;
truncate table tbName;
注意：更换mysql驱动包的版本
2. 删除外部表
方式一：
alter table tbName set tblproperties('external'='false');
drop table tbName;
方式二：
hdfs dfs -rm -r -f /user/hive/warehouse/tbName
2.DML 数据操作语言
2.1.导入数据
方式一：load方式
1.标准语法
语法：load data [local] inpath 数据源路径 into table tbName [overwrite] [partition(k=v)]
//load data [local] inpath path [overwrite] into table table_name [partition(p1=v1,...)]
说明：local表示从本地文件系统中导入数据
不加local 默认从HDFS文件系统中导入数据
情况一：数据从本地导入
本质：将数据从本地上传到HDFS的数据仓库中
情况二：数据从hdfs加载
本质：在HDFS上进行数据的移动（不是复制）
注意：数据源一定需要在hdfs上存在
参数及解释说明：
| args | explain |
|---|---|
| load data | 加载数据 |
| local | 加载本地文件，不加默认加载hdfs |
| inpath path | 加载数据文件的路径 |
| overwrite | 覆盖已有数据 |
| into table | 追加数据 |
| table_name | 具体的表名 |
| partition | 加载数据到指定分区 |
2.操作案例
见2.4 Hive常规操作
方式二：insert语句 （使用少）
语法：insert into table tbName [partition(k=v)] values(v,v,v,v)
此方式底层：将insert语句转换成MR任务执行
效率较低
insert插入分区表，真不推荐使用这个
hive (hive)> insert into tbl_6 partition(month='2020-07') values(4,'zhaoliu');
基本插入模式，根据查询的结果插入数据
hive (hive)> insert into table tbl_5 select * from tbl_1;
hive (hive)> insert overwrite table tbl_5 select * from tbl_1;方式三：as select
hive (hive)> create table tbl_7 as select * from tbl_6;
方式四：import
只能搭配export使用，见下面的export用法
补充：创建表时，直接导入HDFS上数据， location 指定数据表加载数据的路径
create table tbName(…)
location ‘hdfs path’
例如：
create table load_tb2(id int ,name string)
> row format delimited fields terminated by '@'
> location '/home/zhangsan/';
2.2.导出数据
方式一：将数据仓库中的数据表的数据导出到HDFS
export table tbName to 'hdfs path'
方式二：将数据仓库中的数据表的数据导出到本地
dfs -get hiveDataPath localPath
方式三：insert overwrite 命令
语法格式：insert overwrite [local] directory path selectExpr
说明：local表示从本地文件系统中导入数据
不加local 默认从HDFS文件系统中导入数据
path 本地或者HDFS的具体导出路径
例如：insert overwrite local directory /home/zhangsan select * from tbName
导出的数据会把导出路径下的所有文件进行覆盖，一定要写一个不存在的路径。但cat文件发现数据不友好，因此需要格式化导出数据
hive (hive)> insert overwrite local directory '/tmp/hive' row format delimited fields terminated by '\t' select * from tbl_6;
[root@master hive]# cat 000000_0
1 tzhangsan 2020-08
2 tlisi 2020-08
3 twangwu 2020-08
1 tzhangsan 2020-09
2 tlisi 2020-09
3 twangwu 2020-09
方式四：hive shell
不常用hive -e ‘sql’ > file 利用linux的重定向
方式五：export/import
先导出后导入
hive (hive)> export table tbl_1 to '/hive';
OK
Time taken: 0.117 seconds
hive (hive)> truncate table tbl_1;
OK
Time taken: 0.149 seconds
hive (hive)> select * from tbl_1;
OK
tbl_1.id tbl_1.name
Time taken: 0.141 seconds
hive (hive)> import table tbl_1 from '/hive';
Copying data from hdfs://master:9000/hive/data
Copying file: hdfs://master:9000/hive/data/000000_0
Loading data to table hive.tbl_1
OK
Time taken: 0.197 seconds
3.DQL 数据查询语言
3.1.本地模式设置
对于数据量小，学习一些操作命令可以将hive的运行模式设置成本地模式。对于小数据集可以明显的缩短时间，通过如下配置
//开启本地模式
set hive.exec.mode.local.auto=true;
//设置local mr最大输入数据量，当数据量小于这个值（默认128M）时使用local mr
set hive.exec.mode.local.auto.inputbytes.max=50000000;
//设置local mr最大输入文件数，当文件数小于这个值（默认4）时使用local mr
set hive.exec.mode.local.auto.input.files.max=10;
关闭本地模式
set hive.exec.mode.local.auto=false;
3.2. 语法
select [distinct|all] expr，expr... ...
from tbName
where whereCondition
group by colList
having havingCondition
order by colList
cluster by colList
limit num
总结：
- HQL语法不区分大小写
- HQL可以写一行也可以写多行
- 关键字不能被缩写或换行
- 字句一般分行写
- 多使用缩进提高代码可读性
3.3.算术运算符
| 运算符 | 描述 |
|---|---|
| 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按位取反 |
3.4. limit 语句
select st_id,st_name from student limit 2;
3.5. where子句
is null
is not null
between...and...
in(v1,v2,v3,... ...) | not in( v1,v2,v3,... ...)
and
or
like | rlike
% 通配符
_ 占位符
比较运算符：
| 操作符 | 支持的数据类型 | 描述 |
| A=B | 基本数据类型 | 如果A等于B则返回TRUE，反之返回FALSE |
| A!=B | 基本数据类型 | 如果A不等于B，则返回TRUE，反之返回FALSE |
| A | 基本数据类型 | 如果A小于B，则返回TRUE，反之返回FALSE |
| A<=B | 基本数据类型 | 如果A小于等于B，则返回TRUE，反之返回FALSE |
| A>B | 基本数据类型 | 如果A大于B，则返回TRUE，反之返回FALSE |
| A>=B | 基本数据类型 | 如果A大于等于B，则返回TRUE，反之返回FALSE |
| A [NOT] BETWEEN B AND C | 基本数据类型 | 如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。 |
| A IS NULL | 所有数据类型 | 如果A等于NULL，则返回TRUE，反之返回FALSE |
| A IS NOT NULL | 所有数据类型 | 如果A不等于NULL，则返回TRUE，反之返回FALSE |
| IN(数值1, 数值2) | 所有数据类型 | 等于数值1、数值2，返回TRUE |
| A [NOT] LIKE B | STRING 类型 | B是一个SQL下的简单正则表达式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。B的表达式说明如下：‘x%‘表示A必须以字母’x’开头，’%x’表示A必须以字母’x’结尾，而’%x%‘表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。如果使用NOT关键字则可达到相反的效果。 |
逻辑运算符：
| 操作符 | 含义 |
|---|---|
| AND | 逻辑并 |
| OR | 逻辑或 |
| NOT | 逻辑否 |
3.6.group by 子句
概述：
按照某些字段的值进行分组，有相同值的放置到一起
通常和聚合函数一起使用
SQL案例：
select c1 ,c2
where condition ------>map端执行
group by c1,c2 ------>reduce端执行
having condition ------>reduce端执行
案例：
1. 建表
create table order_tb(oid int ,num int ,pname string ,uid int)
row format delimited fields terminated by ',';
导入数据：
load data local inpath '/home/zhangsan/order.txt' into table order_tb;
create table user_tb(uid int ,name string ,addr string)
row format delimited fields terminated by ',';
导入数据：
load data local inpath '/home/zhangsan/user.txt' into table user_tb;
2. 注意
select 后面非聚合列，必须要出现在group by 中
可以使用as对表和列设置别名
having和where的区别：
位置，condition使用的过滤字段，针对的数据（单位：一条数据；单位：一组数据）
3. 按照用户id分组查询订单表
select o.uid from order_tb as o group by o.uid
使用分组函数|聚合函数：查询用户购买商品数量
select o.uid , count(o.num) from order_tb as o group by o.uid
使用分组函数|聚合函数：查询用户购买商品数量，显示数量大于2的信息
select o.uid , count(o.num) from order_tb as o group by o.uid having count(o.num) >2;
4. 常见的聚合参数
count()
max()
min()
sum()
avg()3.7.having
用法和mysql一样
having和where区别：
- where对表中的列发挥作用，having对查询结果的列发挥作用
- where后面不能接聚合函数，having后面可以接聚合函数
- having只能用在group by后面
3.8. order by 子句
概述
按照某些字段进行排序
现象：order by 可以使用多列进行排序（全局排序），此操作在reduce阶段进行，只有一个reduce
问题：数据量很大
全局排序：order by
asc|desc
案例
select * from order_tb order by num desc;
select * from order_tb order by num ,oid asc ; // 多字段排序3.9.sort by 排序
在mapreduce内部的排序，不是全局排序
1. 设置reduce的个数
默认 < 配置文件 < 命令行
set key=value 设置属性
set key 获取属性
set mapreduce.job.reduces=3; 设置属性值
set mapreduce.job.reduces ; 查看属性值
2. 执行排序
select * from order_tb sort by num;
3. 查看reduce中各个阶段输出的结果数据（将结果导出到本地查看）
insert overwrite local directory '/home/zhangsan/sortbyResult'
select * from order_tb sort by num desc;
总结：只对当前的分区进行内容的排序
3.10.distribute by 分区
mapreduce中的partition操作,进行分区，结果sort by使用
distribute by字句需要写在sort by之前（先分区，在排序）
案例：sql转成mr时，内部使用oid字段进行分区，使用num字段进行排序
insert overwrite local directory '/home/zhangsan/distributebyResult'
select * from order_tb distribute by oid sort by num desc;
3.11.cluster by 分区并排序
当distribute by和sort by字段一致时可以使用cluster by代替，因此distribute by兼顾分区和排序两个功能，但是cluster by排序只支持降序排序，不能指定desc或asc一句话
cluster by col <=> distribute by col sort by col
insert overwrite local directory '/home/zhangsan/clusterbyResult'
select * from cluster by num ;
注意：分区和排序的字段需要一致
不可以指定排序的方向
那么有人就问了对一个字段分区又排序的意义可在？当我们数据有很多时，分区数少但该字段类型有很多种，因此就会有很多字段进入同一个分区，在对同一个分区按照该字段排序。
3.12. join 子句（多表联结查询）（子查询）
Hive 支持内连接，外连接，左外连接，右外连接，笛卡尔连接，这和传统数据库中的概念是一致的，可以参见下图。
需要特别强调：JOIN 语句的关联条件必须用 ON 指定，不能用 WHERE 指定，否则就会先做笛卡尔积，再过滤，这会导致你得不到预期的结果 (下面的演示会有说明)。
3.12.1.数据准备
为了演示查询操作，这里需要预先创建两张表，并加载测试数据。
数据文件 emp.txt 和 dept.txt 可以从本仓库的resources 目录下载。
1.部门表
-- 建表语句
CREATE TABLE dept(
deptno INT, --部门编号
dname STRING, --部门名称
loc STRING --部门所在的城市
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
--加载数据
LOAD DATA LOCAL INPATH "/usr/file/dept.txt" OVERWRITE INTO TABLE dept;
2.员工表
-- 建表语句
CREATE TABLE emp(
empno INT, -- 员工表编号
ename STRING, -- 员工姓名
job STRING, -- 职位类型
mgr INT,
hiredate TIMESTAMP, --雇佣日期
sal DECIMAL(7,2), --工资
comm DECIMAL(7,2),
deptno INT) --部门编号
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
--加载数据
LOAD DATA LOCAL INPATH "/usr/file/emp.txt" OVERWRITE INTO TABLE emp;
3.12.2.INNER JOIN
-- 查询员工编号为 1005 的员工的详细信息
SELECT e.*,d.* FROM
emp e JOIN dept d
ON e.deptno = d.deptno
WHERE empno=1005;
--如果是三表或者更多表连接，语法如下
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)
3.12.3.LEFT OUTER JOIN
LEFT OUTER JOIN 和 LEFT JOIN 是等价的。
-- 左连接
SELECT e.*,d.*
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno = d.deptno;
3.12.4.RIGHT OUTER JOIN
--右连接
SELECT e.*,d.*
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno;
执行右连接后，由于 40 号部门下没有任何员工，所以此时员工信息为 NULL。这个查询可以很好的复述上面提到的——JOIN 语句的关联条件必须用 ON 指定，不能用 WHERE 指定。你可以把 ON 改成 WHERE，你会发现无论如何都查不出 40 号部门这条数据，因为笛卡尔运算不会有 (NULL, 40) 这种情况。
3.12.5.FULL OUTER JOIN
SELECT e.*,d.*
FROM emp e FULL OUTER JOIN dept d
ON e.deptno = d.deptno;
3.12.6.LEFT SEMI JOIN
LEFT SEMI JOIN （左半连接）是 IN/EXISTS 子查询的一种更高效的实现。
- JOIN 子句中右边的表只能在 ON 子句中设置过滤条件;
- 查询结果只包含左边表的数据，所以只能 SELECT 左表中的列。
-- 查询在纽约办公的所有员工信息 SELECT emp.* FROM emp LEFT SEMI JOIN dept ON emp.deptno = dept.deptno AND dept.loc="NEW YORK"; --上面的语句就等价于 SELECT emp.* FROM emp WHERE emp.deptno IN (SELECT deptno FROM dept WHERE loc="NEW YORK");
3.12.7.JOIN
笛卡尔积连接，这个连接日常的开发中可能很少遇到，且性能消耗比较大，基于这个原因，如果在严格模式下 (hive.mapred.mode = strict)，Hive 会阻止用户执行此操作。
SELECT * FROM emp JOIN dept;
3.12.8.相关示例
1.概述
两张表m,n之间可以按照on的条件进行连接，m中的一条记录和n表中的一条记录组成一个新的记录。
join 等值连接（内连接）：只有某个值在m和n中同时存在时，才连接。
left outer join (左外连接) ：左表中的值无论在右表中是否存在，都输出；右表中的值只有在左表中存在才输出
right outer join (右外连接) ：相反
mapjoin （map端完成连接）: 在map端进行表的连接操作，基于内存（优化的方式）
2.案例
等值连接：
select * from user_tb u join order_tb o ; // 笛卡尔积现象
select u.name,o.num,o.pname from user_tb u join order_tb o on u.uid=o.uid ; //添加过滤条件避免，笛卡尔积现象
左外连接：（和右外连接相对的）
insert into user_tb values(10004,‘xiaoliu’,‘beijing’);
select u.name,o.num,o.pname from user_tb u
left join order_tb o on u.uid=o.uid ;
注意：表和表的一次连接，代表一个MapReduce 作业job
如果sql语句中存在多个表之间的连接，会创建多个job作业，连接的顺序就是job执行的顺序
3.子查询
SQL语句的嵌套查询
select * from (select * from ...)
selct * from tbName where col = (select * from ...)
嵌套的SQL语句中，子SQL语句的结果 可以作为查询的数据源，也可以作为查询的条件
统计汽车类型的分布：
商用 n1/count
个人 n2/count
hive:
select 汽车的类型 , count() from cart group by 汽车类型
select count() from cart
select 汽车类型 ， 当前类型数量/ (select count(*) from cart ) from
(select 汽车的类型 , count(*) from cart group by 汽车类型)
3.13.小结
| 语法 | 总结 |
|---|---|
| group by | 对字段进行分区，将相同字段再进行分区，后面会接聚合操作 |
| distribute by | 仅对字段进行分区 |
| order by | 全局排序，只会起一个reduce |
| sort by | 局部排序，若设置reduce个数为1则和order by一样 |
四、表的分类（内部表、外部表、分区表）
4.1.内部表（管理表）
概述：
默认创建的所有表
当前用户对其拥有所有的操作权限（删除，修改… …）
删除内部表，数据仓库存储的数据被删除，元数据中的内容也被删除
查看表的类型：desc formatted tbName ;
Table Type: MANAGED_TABLE
删除表：
drop table tbName;
注意：删除管理表，将所有数据全部删除
4.2. 外部表
概述：
在创建表时使用external关键字创建的表是外部表
当前用户其拥有部分的操作权限（删除，修改… …）
删除外部表，数据仓库存储的数据不会被删除，元数据中的内容会被删除
适合存储被共享的数据
创建表：
使用关键字 : external
查看表的类型：desc formatted tbName ;
desc extended tbName;
desc tbName;
Table Type: EXTERNAL_TABLE
删除表： 删除的是当前表的元数据
drop table tbName;
注意：删除外部表，不会将所有数据全部删除
创建新的同名称表，数据会进行自动的管理操作
4.3. 分区表
概述：
分区表对当前数据进行划分并存储不同的分区中（ 文件夹 ）
分区表对当前的数据所在的分区进行单独的管理
提高查询效率
创建表：partitioned by
create table tbName(col type... ...)
partitioned by (pCol type) # 按照什么字段进行分区
row format delimited fields terminated by ',';
例如：创建订单表，order
create table partition_tb(id int ,name string)
partitioned by (year string) # 按照什么字段进行分区
row format delimited fields terminated by ',';
导入数据：将一个文件中的所有数据全部加载到一个分区中
load data local inpath 'asdsad' into table partition_tb
partition(k=v);
查询分区数据：
select * from tbName where k=v ; 查询 k所在分区
select * from tbName where k=v
union
select * from tbName where k =v2 ; 查询 多个分区
查看表的分区信息：
show partitions tbName;
删除分区：
alter table tbName drop parititon(k='v') [,parititon(k='v'),parititon(k='v')... ...]
添加分区：一次添加多个分区时，分区间不需要添加逗号
alter table tbName add parititon(k='v') [parititon(k='v') parititon(k='v')... ...]
多级分区表：（本质上就是多层级目录）
partitioned by(year string ,month string) //按照多个字段分区
多级分区表查询指定分区的数据：
select * from tbName where year=2019 and month=6
4.3.1 分区表的数据的上传方式
方式一：用户自定义分区目录，上传数据
hive (default)> dfs -mkdir -p /user/hive/warehouse/partition_tb2/year=2019/month=7;
hive (default)> dfs -put /home/zhangsan/partition.sql /user/hive/warehouse/partition_tb2/year=2019/month=7;
修复表上的分区的元数据
msck repair table partition_tb2;
方式二：用户自定义分区目录
hive (default)> dfs -mkdir -p /user/hive/warehouse/partition_tb2/year=2019/month=1;
hive (default)> dfs -put /home/zhangsan/partition.sql /user/hive/warehouse/partition_tb2/year=2019/month=1;
show partitions partition_tb2 ;//查看此表的分区信息
手动的给表添加用户的自定义的分区路径：
alter table partition_tb2 add partition(year='2019',month='1');
方式三：
hive (default)> dfs -mkdir -p /user/hive/warehouse/partition_tb2/year=2019/month=2;
hive (default)> load data local inpath '/home/zhangsan/partition.sql' into table partition(year='2019',month='2')
五、Hive中的分区、分桶
5.1.创建
分区：将文件切割成多个子目录
select * from partition(k=v) ;//减少数据量
案例：
创建分区表：
CREATE EXTERNAL TABLE emp_ptn(
empno INT,
ename STRING,
job STRING,
mgr INT,
hiredate TIMESTAMP,
sal DECIMAL(7,2),
comm DECIMAL(7,2)
)
PARTITIONED BY (deptno INT) -- 按照部门编号进行分区
ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
--加载数据
LOAD DATA LOCAL INPATH "/usr/file/emp.txt" OVERWRITE INTO TABLE emp_ptn PARTITION (deptno=20)
LOAD DATA LOCAL INPATH "/usr/file/emp.txt" OVERWRITE INTO TABLE emp_ptn PARTITION (deptno=30)
LOAD DATA LOCAL INPATH "/usr/file/emp.txt" OVERWRITE INTO TABLE emp_ptn PARTITION (deptno=40)
LOAD DATA LOCAL INPATH "/usr/file/emp.txt" OVERWRITE INTO TABLE emp_ptn PARTITION (deptno=50)
分区查询 (Partition Based Queries)，可以指定某个分区或者分区范围。
-- 查询分区表中部门编号在[20,40]之间的员工
SELECT emp_ptn.* FROM emp_ptn
WHERE emp_ptn.deptno >= 20 AND emp_ptn.deptno <= 40;
分桶：将文件切割成多个小文件
采集数据，按照桶采集 //减少数据量
桶中数据的join操作，效率更高 //减少数据量
原理：
MR中：按照key的hash值对reducetask个数求模
Hive中：按照分桶字段的hash值对分桶的个数求模
分桶：
方便抽样（采用数据）
提高join的查询效率
案例：
1. 创建表，设置分桶字段
create table stu_buck(id int ,name string)
clustered by(id) into 4 buckets
row format delimited fields terminated by ',';
创建普通表：
create table stu_buck2(id int ,name string)
row format delimited fields terminated by ',';
load data local inpath '/home/zhangsan/cluster.txt' into table cluster_tb2;
2. 配置属性
set hive.enforce.bucketing=true
set mapreduce.job.reduces=-1
3. 使用子查询的方式，将数据添加到cluster_tb中
insert into table stu_buck
select id,name from stu_buck2 cluster by(id);
4.查看表结构
desc formatted stu_buck;
分桶表使用clustered by（注意和分区排序区分开）且字段要是表中的字段（分区字段不能是表中字段）且分4个桶
数据的采集：
select * from tbName tablesample (bucket startNum out of sizeNum on colName)
从startNum开始抽取数据，采取bucketSize/sizeNum的数据5.2.分桶表应用
对于分桶表主要用于抽样查询中，在一个庞大的数据集中往往需要一个代表性查询结果而不是全部查询结果，因此hive通过抽样语句来实现抽样查询
tablesample(bucket x out of y on col)
col：为分桶字段
x：表示从哪个桶开始抽取
y：必须是分桶数的倍数或者因子，决定抽样的比例。如假设分桶数为4，当y=2时，抽取(4/2)=2个桶的数据；当y=4时，抽取(4/4)=1个桶的数据；当y=8时，抽取(4/8)=1/2个桶的数据
假设分桶数z：z/y>1，则最终取x，x+z/y+z/y*2…
tablesample(bucket 1 out of 2 on id)
即从第一个桶开始抽，抽取两个桶的数据，也就是抽取1，3两个桶中的数据
因此tablesample要求x <= y，因为抽取的最后一个为
x+(z/y-1)*y => x+z-y <= z => x <= y
若x > y会报FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck
六、函数
hive的函数和mysql一样分为系统内置函数和用户自定义函数，只是自定义函数和mysql将会有巨大差别
查看系统内置函数
hive (test)> show functions;查看内置函数用法
hive (test)> desc function upper;查看内置函数详细用法
hive (test)> desc function extended upper;6.1. 系统内置函数
下面列举常用的内置函数，主要介绍和mysql不同的部分（带*的）[一共216个]
| function | explanation |
|---|---|
| round | 四舍五入 |
| ceil | 向上取整 |
| floor | 向下取整 |
| rand | 取0-1随机数 |
| lower | 转小写 |
| upper | 转大写 |
| length | 返回字符串长度 |
| concat | 字符串拼接 |
| concat_ws | 指定分隔符拼接 |
| collect_set | 合并字段* |
| substr | 求子串 |
| trim | 前后去空格 |
| split | 字符串分割 |
| to_date | 字符串转日期 |
| year、month… | 从日期中提取年月日 |
| from_unixtime | 时间戳转日期* |
| unix_timestamp | 日期转时间戳* |
| case…when… | 条件函数 |
| if | 判断函数 |
| count | 求个数（聚合） |
| sum | 求总和（聚合） |
| min | 求最小值（聚合） |
| max | 求最大值（聚合） |
| avg | 求平均值（聚合） |
| explode | 膨胀函数* |
| lateral view | 拓展explode* |
| over | 开窗函数 |
hive常用函数：
常用日期函数
unix_timestamp:返回当前或指定时间的时间戳
select unix_timestamp();
select unix_timestamp("2020-10-28",'yyyy-MM-dd');
from_unixtime：将时间戳转为日期格式
select from_unixtime(1603843200);
current_date：当前日期
select current_date;
current_timestamp：当前的日期加时间
select current_timestamp;
to_date：抽取日期部分
select to_date('2020-10-28 12:12:12');
year：获取年
select year('2020-10-28 12:12:12');
month：获取月
select month('2020-10-28 12:12:12');
day：获取日
select day('2020-10-28 12:12:12');
hour：获取时
select hour('2020-10-28 12:12:12');
minute：获取分
select minute('2020-10-28 12:12:12');
second：获取秒
select second('2020-10-28 12:12:12');
weekofyear：当前时间是一年中的第几周
select weekofyear('2020-10-28 12:12:12');
dayofmonth：当前时间是一个月中的第几天
select dayofmonth('2020-10-28 12:12:12');
months_between： 两个日期间的月份
select months_between('2020-04-01','2020-10-28');
add_months：日期加减月
select add_months('2020-10-28',-3);
datediff：两个日期相差的天数
select datediff('2020-11-04','2020-10-28');
date_add：日期加天数
select date_add('2020-10-28',4);
date_sub：日期减天数
select date_sub('2020-10-28',-4);
last_day：日期的当月的最后一天
select last_day('2020-02-30');
date_format(): 格式化日期
select date_format('2020-10-28 12:12:12','yyyy/MM/dd HH:mm:ss');
常用取整函数
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);
常用字符串操作函数
upper： 转大写
select upper('low');
lower： 转小写
select lower('low');
length： 长度
select length("atguigu");
trim： 前后去空格
select trim(" atguigu ");
lpad： 向左补齐，到指定长度
select lpad('atguigu',9,'g');
rpad： 向右补齐，到指定长度
select rpad('atguigu',9,'g');
regexp_replace：使用正则表达式匹配目标字符串，匹配成功后替换！
SELECT regexp_replace('2020/10/25', '/', '-');
集合操作
size： 集合中元素的个数
select size(friends) from test3;
map_keys： 返回map中的key
select map_keys(children) from test3;
map_values: 返回map中的value
select map_values(children) from test3;
array_contains: 判断array中是否包含某个元素
select array_contains(friends,'bingbing') from test3;
sort_array： 将array中的元素排序
select sort_array(friends) from test3;
grouping_set:多维分析
6.2 用户自定义函数
当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。
官方文档地址：https://cwiki.apache.org/confluence/display/Hive/HivePlugins
hive中的自定义函数根据输入输出行数分为三种：
- 用户定义函数（user-defined function）UDF
- 用户定义聚集函数（user-defined aggregate function）UDAF
- 用户定义表生成函数（user-defined table-generating）UDTF
| 函数类型 | 描述 |
|---|---|
| UDF | 一行输入一行输出，如字符串类函数 |
| UDAF | 多行输入一行输出，如聚合函数 |
| UDTF | 一行输入多行输出，如膨胀函数 |
6.2.1.编程步骤
（1）继承org.apache.hadoop.hive.ql.UDF
（2）需要实现evaluate函数；evaluate函数支持重载；
（3）在hive的命令行窗口创建函数
a）添加jar
add jar linux_jar_path
b）创建function，
create [temporary] function [dbname.]function_name AS class_name;
（4）在hive的命令行窗口删除函数
Drop [temporary] function [if exists] [dbname.]function_name;
注意事项
（1）UDF必须要有返回类型，可以返回null，但是返回类型不能为void；
6.2.2.自定义UDF函数实例
1．创建一个Maven工程Hive
2．导入依赖
<dependencies>
<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-exec -->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>1.2.1</version>
</dependency>
</dependencies>3．创建一个类继承UDF，覆写带有一个或多个参数的evaluate方法。
package com.muzili.hive;
import org.apache.hadoop.hive.ql.exec.UDF;
public class Lower extends UDF {
public String evaluate (final String s) {
if (s == null) {
return null;
}
return s.toLowerCase();
}
}4．打成jar包上传到服务器/opt/module/jars/udf.jarBuilt-in Aggregate
Functions (UDAF)
5．将jar包添加到hive的classpath
hive (default)> add jar /opt/module/datas/udf.jar;
6．创建临时函数与开发好的java class关联
hive (default)> create temporary function mylower as "com.muzili.hive.Lower";
7．即可在hql中使用自定义的函数strip
hive (default)> select ename, mylower(ename) lowername from emp;
6.2.3.Built-in Aggregate Functions (UDAF)实例
UDAF：多对一
count、max、min、sum、avg
collect_set(col) : 返回消除重复元素的一组对象。
hive (default)> select id,name,
collect_set(hobby) from test.t_hobby
group by id,name;6.2.4.Built-in Table-Generating Functions (UDTF) 表生成函数实例
普通的UDF，如concat()，接受单个输入行并输出单个输出行。相反，表生成函数将单个输入行转换为多个输出行 (一对多)。
explode(ARRAY a) ：炸裂函数。 将数组分解为多行。返回一个单列(col)的行集，数组中的每个元素对应一行。
Lateral View Syntax 侧面图语法
lateralView: LATERAL VIEW
udtf(expression) tableAlias AS
columnAlias (',' columnAlias)*
fromClause: FROM baseTable (lateralView)*
侧视图与用户定义的表生成函数(如爆炸())一起使用。正如内置表生成函数中提到的，UDTF为每个输入行生成0或更多输出行。
横向视图首先将UDTF应用于基表的每一行，然后将结果输出行连接到输入行，从而形成具有所提供的表别名的虚拟表。
hive (default)> select explode(hobbies)
from test.t_temp;
smoking
drinking
reading
music
dance
sport
hive (default)> select id,name,tt.hobby
from test.t_temp
LATERAL VIEW explode(hobbies) tt as
hobby;七、Hive 调优
hive调优涉及到压缩和存储调优，参数调优，sql的调优，数据倾斜调优，小文件问题的调优等
7.1 数据的压缩与存储格式
1. map阶段输出数据压缩 ，在这个阶段，优先选择一个低CPU开销的算法。
set hive.exec.compress.intermediate=true
set mapred.map.output.compression.codec= org.apache.hadoop.io.compress.SnappyCodec
set mapred.map.output.compression.codec=com.hadoop.compression.lzo.LzoCodec;2. 对最终输出结果压缩
set hive.exec.compress.output=true
set mapred.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec
## 当然，也可以在hive建表时指定表的文件格式和压缩编码结论：一般选择orcfile/parquet + snappy 方式
7.2 合理利用分区分桶
分区是将表的数据在物理上分成不同的文件夹，以便于在查询时可以精准指定所要读取的分区目录，从而降低读取的数据量
分桶是将表数据按指定列的hash散列后分在了不同的文件中，将来查询时，hive可以根据分桶结构，快速定位到一行数据所在的分桶文件，从来提高读取效率
7.3 hive参数优化
// 让可以不走mapreduce任务的，就不走mapreduce任务
hive> set hive.fetch.task.conversion=more;
// 开启任务并行执行
set hive.exec.parallel=true;
// 解释：当一个sql中有多个job时候，且这多个job之间没有依赖，则可以让顺序执行变为并行执行（一般为用到union all的时候）
// 同一个sql允许并行任务的最大线程数
set hive.exec.parallel.thread.number=8;
// 设置jvm重用
// JVM重用对hive的性能具有非常大的 影响，特别是对于很难避免小文件的场景或者task特别多的场景，这类场景大多数执行时间都很短。jvm的启动过程可能会造成相当大的开销，尤其是执行的job包含有成千上万个task任务的情况。
set mapred.job.reuse.jvm.num.tasks=10;
// 合理设置reduce的数目
// 方法1：调整每个reduce所接受的数据量大小
set hive.exec.reducers.bytes.per.reducer=500000000; （500M）
// 方法2：直接设置reduce数量
set mapred.reduce.tasks = 20
// map端聚合，降低传给reduce的数据量
set hive.map.aggr=true
// 开启hive内置的数倾斜优化机制
set hive.groupby.skewindata=true7.4 sql优化
7.4.1 where条件优化
优化前（关系数据库不用考虑会自动优化）
select m.cid,u.id from order m join customer u on( m.cid =u.id ) where m.dt='20180808';优化后(where条件在map端执行而不是在reduce端执行）
select m.cid,u.id from （select * from order where dt='20180818'） m join customer u on( m.cid =u.id);7.4.2 union优化
尽量不要使用union （union 去掉重复的记录）而是使用 union all 然后在用group by 去重
7.4.3 count distinct优化
不要使用count (distinct cloumn) ,使用子查询
select count(1) from (select id from tablename group by id) tmp;7.4.4 用in 来代替join
如果需要根据一个表的字段来约束另为一个表，尽量用in来代替join。 in 要比join 快
select id,name from tb1 a join tb2 b on(a.id = b.id);
select id,name from tb1 where id in(select id from tb2);7.4.5 优化子查询
消灭子查询内的 group by 、 COUNT(DISTINCT)，MAX，MIN。可以减少job的数量。
7.4.6 join 优化
Common/shuffle/Reduce JOIN 连接发生的阶段，发生在reduce 阶段， 适用于大表 连接 大表(默认的方式)
Map join ：连接发生在map阶段 ， 适用于小表 连接 大表
大表的数据从文件中读取
小表的数据存放在内存中（hive中已经自动进行了优化，自动判断小表，然后进行缓存）
set hive.auto.convert.join=true;SMB join
Sort -Merge -Bucket Join 对大表连接大表的优化，用桶表的概念来进行优化。在一个桶内发生笛卡尔积连接（需要是两个桶表进行join）
set hive.auto.convert.sortmerge.join=true;
set hive.optimize.bucketmapjoin = true;
set hive.optimize.bucketmapjoin.sortedmerge = true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;7.5 数据倾斜
现象：任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。因为其处理的数据量和其他reduce差异过大。
原因：某个reduce的数据输入量远远大于其他reduce数据的输入量
7.5.1 sql本身导致的倾斜
1）group by
如果是在group by中产生了数据倾斜，是否可以讲group by的维度变得更细，如果没法变得更细，就可以在原分组key上添加随机数后分组聚合一次，然后对结果去掉随机数后再分组聚合
在join时，有大量为null的join key，则可以将null转成随机值，避免聚集
2）count(distinct)
情形：某特殊值过多
后果：处理此特殊值的 reduce 耗时；只有一个 reduce 任务
解决方式：count distinct 时，将值为空的情况单独处理，比如可以直接过滤空值的行，
在最后结果中加 1。如果还有其他计算，需要进行 group by，可以先将值为空的记录单独处理，再和其他计算结果进行 union。
3）不同数据类型关联产生数据倾斜
情形：比如用户表中 user_id 字段为 int，log 表中 user_id 字段既有 string 类型也有 int 类型。当按照 user_id 进行两个表的 Join 操作时。
后果：处理此特殊值的 reduce 耗时；只有一个 reduce 任务
默认的 Hash 操作会按 int 型的 id 来进行分配，这样会导致所有 string 类型 id 的记录都分配
到一个 Reducer 中。
解决方式：把数字类型转换成字符串类型
select * from users a
left outer join logs b
on a.usr_id = cast(b.user_id as string)4）mapjoin
7.5.2 业务数据本身的特性(存在热点key)
join的每路输入都比较大，且长尾是热点值导致的，可以对热点值和非热点值分别进行处理，再合并数据
7.5.3 key本身分布不均
可以在key上加随机数，或者增加reduceTask数量
开启数据倾斜时负载均衡
set hive.groupby.skewindata=true;
思想：就是先随机分发并处理，再按照 key group by 来分发处理。
操作：当选项设定为 true，生成的查询计划会有两个 MRJob。
第一个 MRJob 中，Map 的输出结果集合会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 GroupBy Key 有可能被分发到不同的Reduce 中，从而达到负载均衡的目的；
第二个 MRJob 再根据预处理的数据结果按照 GroupBy Key 分布到 Reduce 中（这个过程可以保证相同的原始 GroupBy Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作。
7.5.4 控制空值分布
将为空的 key 转变为字符串加随机数或纯随机数，将因空值而造成倾斜的数据分不到多个 Reducer。
注：对于异常值如果不需要的话，最好是提前在 where 条件里过滤掉，这样可以使计算量大大减少
7.6 合并小文件
小文件的产生有三个地方，map输入，map输出，reduce输出，小文件过多也会影响hive的分析效率：
7.6.1 设置map输入的小文件合并：
set mapred.max.split.size=256000000;
//一个节点上split的至少的大小(这个值决定了多个DataNode上的文件是否需要合并)
set mapred.min.split.size.per.node=100000000;
//一个交换机下split的至少的大小(这个值决定了多个交换机上的文件是否需要合并)
set mapred.min.split.size.per.rack=100000000;
//执行Map前进行小文件合并
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;7.6.2 设置map输出和reduce输出进行合并的相关参数：
//设置map端输出进行合并，默认为true
set hive.merge.mapfiles = true
//设置reduce端输出进行合并，默认为false
set hive.merge.mapredfiles = true
//设置合并文件的大小
set hive.merge.size.per.task = 256*1000*1000
//当输出文件的平均大小小于该值时，启动一个独立的MapReduce任务进行文件merge。
set hive.merge.smallfiles.avgsize=160000007.7 查看sql的执行计划
explain sql学会查看sql的执行计划，优化业务逻辑 ，减少job的数据量。对调优也非常重要
7.8 选择使用Tez或Spark引擎
Tez: 是基于Hadoop Yarn之上的DAG（有向无环图，Directed Acyclic Graph）计算框架。它把Ｍap/Reduce过程拆分成若干个子过程，同时可以把多个Ｍap/Reduce任务组合成一个较大的DAG任务，减少了Ｍap/Reduce之间的文件存储。同时合理组合其子过程，也可以减少任务的运行时间
设置hive.execution.engine = tez;
通过上述设置，执行的每个HIVE查询都将利用Tez
当然，也可以选择使用spark作为计算引擎
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
