一、Hive基本概念
1.1 hive基本认识
hive是基于hadoop的一个数仓分析工具，hive可以将hdfs上存储的结构化的数据，映射成一张表，然后让用户写HQL(类SQL)来分析数据。
Hive毕竟不是数据库，但是在大数据的场景中，我们大量的数据都存储在hadoop的hdfs中，那么我们又怎样去做才能去操作hdfs中的数据呢？那就是使用：Hive。所以说，Hive就是一个操作hdfs中数据的一个客户端工具。
tel up down
1383838438 1345 1567
1383838439 5345 1567
1383838440 1241 16577
1383838441 3453 15757
1383838434 35355 1567567
按照手机号 分组，统计每个手机号的总流量
select tel,sum(up+down) from test group by tel ;
hive的本质其实就是hadoop的一个客户端，hive底层不存储任何数据，hive表的数据存在hdfs上，hive表的元数据存在关系型数据库中。
默认是derby，我们不一般不用默认的derby来存，因为derby不支持高并发。一般都会修改为mysql。
元数据：描述数据的数据。
Hive其实就是将用户写的HQL，给翻译成对应的mr模板，然后执行这些mr程序。
hive底层执行引擎其实就是MapReduce，mr运行在yarn上。
1.2 hive的优缺点
优点：操作简单，采用类sql的语法分析数据，门槛低，大大的降低了大数据分析的难度，通用性高
缺点：不够灵活，机翻粒度比较粗，调优困难。因为底层执行引擎还是mr，所以延迟较高，不能像关系型数据库那样，立马返回结果
并且底层存储是hdfs，不支持随机写，只能追加，所以hive不支持行级别的更新和删除（delete 和 update）
1.3 hive的架构原理
客户端：命令行客户端【黑窗口】，jdbc客户端【可视化工具】，和mysql一样。
数据存储：hdfs
底层执行引擎：mr
元数据库：将元数据存储在关系数据库中（MySql、Derby【默认】），元数据包括哪些库、表的属性、表的名称、表的列、分区及其属性以及表数据所在的目录等。
Dirver四个器**【驱动中的4个组件】**
解析器：将hql语句转换成AST抽象语法树，解析sql是否有误
编译器：将解析后的hql编译成逻辑执行计划，暂时不执行
优化器：对逻辑计划进行优化，调优
执行器：将优化后的逻辑计划执行，其实就是翻译成对应的mr程序，在yarn上运行
1.4 hive和关系型数据库对比
hive不是数据库，不是数据库，不是数据库
hive除了查询语言HQL跟SQL很像之外，别的跟数据库再也没有半点相似可言
具体体现在这几个方面：数据更新、数据规模、执行延迟、底层引擎、数据存储
数据更新：
数据库： 支持更新操作。
Hive: 不支持更新操作，不支持update操作，insert和drop操作也较少。
数据规模：
数据库： 数据规模比较少，理论上支持500万数据量。
Hive: 数据量比较大，数据存储在hadoop中的hdfs中，支持集群规模。
执行延迟：
数据库：支持索引。运行效率高，延迟低。
Hive: ①：底层数据没有索引，需要扫描整个表，运行效率低，延迟高。②：底层引擎是MR,MR本身延迟就高。
底层引擎：
数据库：InnoDB等。
Hive : 默认MR引擎，同时也支持Spark、tez。
数据存储：
数据库： 存储在磁盘中。
Hive: 数据存储在hdfs中，元数据存储在关系型数据库中。
二、Hive安装
2.0 安装命令
1. 安装命令
安装Hive之前，首先需要在机器上安装mysql
## 5个安装包 01_mysql-community-common-5.7.16-1.el7.x86_64.rpm 02_mysql-community-libs-5.7.16-1.el7.x86_64.rpm 03_mysql-community-libs-compat-5.7.16-1.el7.x86_64.rpm 04_mysql-community-client-5.7.16-1.el7.x86_64.rpm 05_mysql-community-server-5.7.16-1.el7.x86_64.rpm ## 驱动包 mysql-connector-java-5.1.27-bin.jarLinux机器上安装软件有两种方式：
yum安装：在线安装 ——> yum install xxxxx
rpm安装：离线安装【需要自己提供rpm安装包】
安装软件命令： rpm -ivh xxxxx
查看是否安装命令： rpm -qa | grep i xxxxxx 【i : 不区分大小写】
卸载软件命令： rpm -e xxxxx
如果卸载的软件具有依赖，但是仍然需要进行强制卸载时需要加 --nodeps
rpm -e xxxxx --nodeps
2. 安装MySql
建议使用root用户角色进行安装，如果没有root权限就使用当前用户角色进行安装
-
测试当前系统是否有mysql，如果有，则删除。
## 测试命令 [heather@hadoop01 mysql ]$ rpm -qa | grep -i -E mysql\|mariadb ## 删除命令 [heather@hadoop01 mysql ]$ rpm -qa | grep -i -E mysql\|mariadb | xargs -n1 sudo rpm -e --nodeps -
安装mysql数据库的rpm包，一定要按照先后顺序安装，否则报错。
[heather@hadoop01 mysql]$ sudo rpm -ivh 01_mysql-community-common-5.7.16-1.el7.x86_64.rpm [heather@hadoop01 mysql]$ sudo rpm -ivh 02_mysql-community-libs-5.7.16-1.el7.x86_64.rpm [heather@hadoop01 mysql]$ sudo rpm -ivh 03_mysql-community-libs-compat-5.7.16-1.el7.x86_64.rpm [heather@hadoop01 mysql]$ sudo rpm -ivh 04_mysql-community-client-5.7.16-1.el7.x86_64.rpm [heather@hadoop01 mysql]$ sudo rpm -ivh 05_mysql-community-server-5.7.16-1.el7.x86_64.rpm -
如果Linux是最小化安装的，在安装
05_mysql-community-server-5.7.16-1.el7.x86_64.rpm时可能会出 现如下错误[heather@hadoop01 mysql]$ sudo rpm -ivh 05_mysql-community-server-5.7.16-1.el7.x86_64.rpm 警告：05_mysql-community-server-5.7.16-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY 错误：依赖检测失败： libaio.so.1()(64bit) 被 05_mysql-community-server-5.7.16-1.el7.x86_64 需要 libaio.so.1(LIBAIO_0.1)(64bit) 被 05_mysql-community-server-5.7.16-1.el7.x86_64 需要 libaio.so.1(LIBAIO_0.4)(64bit) 被 05_mysql-community-server-5.7.16-1.el7.x86_64 需要 -
通过yum安装缺少的依赖,然后重新安装05_mysql-community-server-5.7.16-1.el7.x86_64 即可
[heather@hadoop01 mysql]$ sudo yum install -y libaio -
删除/etc/my.cnf文件中datadir指向的目录下的所有内容,如果有内容的情况下:
- 查看datadir的值：
datadir=/var/lib/mysql- 删除/var/lib/mysql目录下的所有内容:
[heather@hadoop01 mysql]$ cd /var/lib/mysql [heather@hadoop01 mysql]$ sudo rm -rf ./* //注意执行命令的位置 -
初始化数据库
[heather@hadoop01 mysql]$ sudo mysqld --initialize --user=mysql -
查看临时生成的root用户的密码
[heather@hadoop01 mysql]$ sudo cat /var/log/mysqld.log ## 如下内容【最后一行】： ——> oiu+fsst<2Hv就是mysql密码 A temporary password is generated for root@localhost: oiu+fsst<2Hv -
启动MySQL服务
[heather@hadoop01 mysql]$ sudo systemctl start mysqld -
登录MySQL数据库（如果报错，给密码加单引号）
[heather@hadoop01 mysql]$ mysql -uroot -p'oiu+fsst<2Hv' 或者： [heather@hadoop01 mysql]$ mysql -uroot -p Enter password: 输入临时生成的密码【oiu+fsst<2Hv】 -
设置复杂密码(由于mysql密码策略，此密码必须足够复杂)
mysql> set password=password("Qs23=zs32"); -
更改mysql密码策略
mysql> set global validate_password_length=4; mysql> set global validate_password_policy=0; -
设置新密码
mysql> set password=password("root"); -
修改mysql库下的user表中的root用户允许任意ip连接
mysql> update user set host="%" where user="root"; -
刷新
mysql> flush privileges; -
退出
mysql> quit;
2.1 hive访问
使用hive客户端之前，首先应该启动mysql服务器端 和 hive的服务器端 以及hadoop的hdfs。
1）mysql是自启动的，但是我们也可以检查一下mysql的启动状态。
-
启动mysql的服务命令：
sudo systemctl start mysqld -
检查mysql的服务状态命令：
sudo systemctl status mysqld
2）启动hadoop的hdfs
start-dfs.sh
3）hive服务器端有两个服务应该开启：metastore 、hiveserver2
[heather@hadoop01 hive]$ nohup hive --service metastore>hive_log1.txt 2>&1 &
[heather@hadoop01 hive]$ nohup hive --service hiveserver2>hive_log2.txt 2>&1 &
实际开发中最常使用的命令是：
[heather@hadoop01 hive]$ nohup hiveserver2 &
命令详解：
nohup: 放在命令开头，表示不挂起,也就是关闭终端进程也继续保持运行状态
2>&1 : 表示将错误重定向到标准输出上
&: 放在命令结尾,表示后台运行
一般会组合使用: nohup [xxx命令操作]> file 2>&1 &， 表示将xxx命令运行的
结果输出到file中，并保持命令启动的进程在后台运行。
- 实际开发中我们最常使用的命令【我们只需要启动这一个命令即可】
[heather@hadoop01 hive]$ nohup hiveserver2 &
- hive脚本访问
hive
5）通过hive自带的beeline客户端访问【】
beeline -u jdbc:hive2://hadoop01:10000 -n heather
2.2 hive交互命令
交互命令使用场景：在shell脚本里面不能人为的进入hive客户端交互写sql，所以要通过hive -e或者-f两个交互参数进行写入
1）hive -e 【因为在脚本中我们没有能力直接进入hive的客户端，但是hive -e 可以执行sql命令】
1. 先创建一个bash脚本 ——> hive_e.sh，内容如下：
#! /bin/bash
hive -e "select * from student"
2. 给hive_e.sh脚本赋予执行权限 ——> chmod +x hive_e.sh
3. 使用hive -e 命令执行hive_e.sh脚本
[heather@hadoop01 hive]$ hive_e.sh
2）hive -f 【】
1. 先创建一个sql语句文本文件 ——> stu.sql，内容如下：
select * from student;
2. 使用hive -f 命令执行sql脚本
hive -f stu.sql
2.3 日志 & JVM堆设置
1. 日志配置
修改hive/conf/hive-log4j.properties.template文件信息
property.hive.log.dir=/opt/module/hive/logs
2. JVM堆设置
修改hive/conf/hive-env.sh.template文件信息
# 此行本来是注释状态，打开注释即可。
export HADOOP_HEAPSIZE=1024
2.4 hive参数设置方式
1. 通过配置文件设置 （永久生效）
在hive的家目录下面的conf文件夹下的hive-site.xml hive-env.sh hive-log4j2.properties
2. 通过命令行参数来设置 （临时生效，只针对当前客户端连接）
hive -hiveconf 参数名=参数值
beeline -u jdbc:hive2://hadoop01:10000 -n heather -hiveconf 参数名=参数值
3. 通过set命令设置（临时生效，只针对当前客户端连接）
我们连接到hive的客户端以后，可以通过set语句来设置参数
查看所有参数设置
set;
查看单个参数的值
set 参数名;
设置单个参数的值
set 参数名=参数值;
参数设置优先级：
hive-default.xml < hive-site.xml < -hiveconf 参数名=参数值 < set 参数名=参数值
三、数据类型
3.1 基本数据类型
| HIVE | MySQL | JAVA | 长度 | 例子 |
|---|---|---|---|---|
| TINYINT | TINYINT | byte | 1byte有符号整数 | 2 |
| SMALINT | SMALINT | short | 2byte有符号整数 | 20 |
| INT | INT | int | 4byte有符号整数 | 20 |
| BIGINT | BIGINT | long | 8byte有符号整数 | 20 |
| BOOLEAN | 无 | boolean | 布尔类型，true或者false | TRUE FALSE |
| FLOAT | FLOAT | float | 单精度浮点数 | 3.14159 |
| DOUBLE | DOUBLE | double | 双精度浮点数 | 3.14159 |
| STRING【2GB】 | VARCHAR | string | 字符系列。可以指定字符集。可以使用单引号或者双引号。 | ‘now is the time’ “for all good men” |
| TIMESTAMP | TIMESTAMP | 时间类型 | ||
| BINARY | BINARY | 字节数组 |
利用基本数据类型建表测试
create table test(id int,weight double,name string,money bigint);
利用insert语句按照指定的数据类型插入一条数据
insert into test values(1001,75,"zhangsan",1000000000000);
3.2 集合数据类型
| 数据类型 | 描述 | 语法示例 |
|---|---|---|
| STRUCT | 和c语言中的struct类似，和Java中的对象类似。都可以通过“点”符号访问元素内容。例如，如果某个列的数据类型是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 |
通过以下Json数据来区分上述三种数据类型：
- Map结构比Array结构的维度多一层。
- Struct结构和Map结构类似，但是Map结构的value部分的类型一定是一致的，但是Struct结构的value部分的类型可能是一致的，也可能不一致。
- 具体的体现请详细查看下面建表的sql语句。
{
"name": "songsong",
"friends": ["bingbing" , "lili"], //列表Array,
"children": { //键值Map,
"xiao song": 18,
"xiaoxiao song": 19
}
"address": { //结构Struct,
"street": "hui long guan",
"city": "beijing",
"email": "10010"
}
}
测试数据
songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing_10010
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing_10011
在hive中建表描述上述数据
create table test(
name string,
friends array<string>,
children map<string,int>,
address struct<street:string,city:string,emial:int>
)
row format delimited
fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';
加载数据
第一种方式：
0: jdbc:hive2://hadoop01:10000> load data local inpath '/opt/module/hive-3.1.2/testData/test.txt' into table test;
第二种方式：
[heather@hadoop01 hive-3.1.2]$ hadoop fs -put /opt/module/hive-3.1.2/testData/test.txt /user/hive/warehouse/test;
查询数据
select * from test;
查出来songsong 这个人的姓名，第一个朋友，孩子xiaoxiao song的年龄，和他的邮编
select name, friends[0],children['xiaoxiao song'],address.email from test where name = "songsong";
select name, friends[1],children['xiao song'],address.street from test;
3.3 复杂数据类型的使用
1. 如何声明复杂结构体的类型
2. 如何构造复杂结构体类型的的值
3. 如何获取复杂结构体类型中的值
-
Array结构
语法：Array<数据类型> 声明复杂结构体的类型： Array<string>。 构造复杂结构体类型的值：①：array<1,2,3> ②：collect_set(id) ③：collect_list(name)。 获取复杂结构体类型中的值:array[索引下标]。 -
Map结构
语法：Map<key值类型,value值类型> 声明复杂结构体的类型： Map<string,string>。 构造复杂结构体类型的值：①：map(k1,v1,k2,v2) ②：str_to_map(text[,delimiter1,delimiter2])。 获取复杂结构体类型中的值：map[key值]。 -
Struct结构
语法：Struct<属性名1:属性名1类型,属性名2:属性名2类型,属性名3:属性名3类型> 声明复杂结构体的类型： Struct<id:int,name:string,age:int>。 构造复杂结构体类型的值：①：struct(val1,val2,val3,...) 【属性名的默认值是col1,col2,col3...】 ②：named_struct(name1,val1,name2,val2,...)。 获取复杂结构体类型中的值：struct.属性值 ——> struct.name。
3.4 Hive操作数据总结：
Hive对数据的存储是：数据文件在数据表的下一层。
相当于数据表是文件夹，而数据文件是文本文件。
通过上述的操作，我们对hive操作数据进行一下总结：
总体上来讲，Hive是操作hdfs上的数据的，步骤如下：
1). 获取数据，创建文件xxx.txt
2). 创建一张表
3). 向表中导入数据
1. load data local inpath '/数据路径' into table 表名;
2. hadoop fs -put '/数据路径' hdfs路径;
3.5 类型转换
1）隐式（自动）类型转换
（1）任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。
（2）所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。
（3）TINYINT、SMALLINT、INT都可以转换为FLOAT。
（4）BOOLEAN类型不可以转换为任何其它的类型。
2）显示（强制）类型转换
CAST( vlaue AS type)
例如：select * from cast('1' as int) + 2;
四、DDL数据定义语言
4.1 库的DDL
1 创建数据库
CREATE DATABASE [IF NOT EXISTS] database_name --指定数据库名称
[COMMENT database_comment] --指定数据库描述
[LOCATION hdfs_path] --指定创建的数据库在hdfs上存储的路径
[WITH DBPROPERTIES (property_name=property_value, ...)]; --指定库的一些属性
补充：
在创建库的时候 LOCATION hdfs_path 可以不指定，默认位置在hive_site.xml文件中进行配置。
案例实操
create database if not exists db_hive
comment "this is my first db"
with dbproperties ("dbname"="db_hive","owner"="atguigu");
create database db_hive;
--创建数据库，并指定在hdfs上的路径
create database if not exists db_hive2
location '/db_hive2';
2 查询数据库
show databases；
3 查看数据库详情
--简单查看
desc database 数据库名;
--详细查看 （详细查看可以看到库的属性信息，简单查看看不到）
desc database extended 数据库名;
4 切换数据库
use 数据库名;
5 修改数据库
只能修改数据库的属性信息，别的都无法更改。例如：库名，库的存储位置等元数据信息无法更改
alter database db_hive set dbproperties('createtime'='20200624');
6 删除数据库
注意：删除掉数据库以后，hdfs上对应的目录也会删除，谨慎操作。
--删除空数据库
drop database 数据库名;
--如果数据库不为空，可以在最后加上cascade强制删除。
drop database 数据库名 cascade;
--为了更严谨，我们可以在删除之前判断数据库是否存在。
drop database if exists 数据库名 cascade;
4.2 表的DDL
1创建表
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name --指定表名 【external 外部表/内部表】
[(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, ...) --指定分桶表的分桶字段
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] --指定分桶表桶内排序字段 指定分桶的个数
[ROW FORMAT DELIMITED] --指定hive表在hdfs上存储的原始数据的格式
[FIELDS TERMINATED BY char] --每行数据中字段的分隔符 默认值：ascII码表的第一个字符 ^A
[COLLECTION ITEMS TERMINATED BY char] --集合元素分隔符 默认值：ascII码表的第二个字符 ^B
[MAP KEYS TERMINATED BY char] --map集合中 key 和 value 的分隔符 默认值：ascII码表的第三个字符 ^C
[LINES TERMINATED BY char] --每行数据的分隔符 默认值：'\n'
[STORED AS file_format] --指定hive的数据在hdfs上存储的格式
[LOCATION hdfs_path] --指定hive数据在hdfs上存储的路径 默认值 /user/hive/warehouse/数据库名 一般情况下，创建管理表【内部表】不需要指定位置，但是创建外部表需要指定hdfs上的位置。
[TBLPROPERTIES (property_name=property_value, ...)] --指定表的属性
[AS select_statement] --按照as后面的查询语句的结果来创建表，复制表结构以及表数据
[LIKE table_name] --按照like后面的表结构来创建表，只复制表结构，不复制表数据
-------------------------------------------------------------------------------------
^A : Ctrl+v + Ctrl+A
^B : Ctrl+v + Ctrl+B
^C : Ctrl+v + Ctrl+C
如果想查看则需要使用-A | -B | -C 参数查看。
2 管理表和外部表
管理表（内部表）：hive掌控着这个数据的生命周期，如果删除一个管理表，hdfs上存储的数据也跟着一起删除。所以一般我们创建管理表时，一般不会再指定location表的存储路径，就默认放在/user/hive/warehouse下
外部表：hive不完全掌控外部表的数据的生命周期，删除外部表，只删除hive表的元数据，不会删除掉hdfs上存储的数据
内部表和外部表的创建
数据库和数据表在hdfs上都是以文件夹的形式存在，导入hdfs上的数据都是以文件的形式存在。
- 内部表的创建步骤：
先在hive的客户端创建表，此时，hdfs上同时也会创建同名的文件夹，接下来再把数据导入到hdfs上。- 外部表的创建步骤：
先在hdfs上创建文件夹，再在hive的客户端创建同名的表【指定location目录】，最后再把数据导入到hdfs上。
内部表和外部表区别
- 内部表也称之为管理表，Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。 当我们删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。
- 外部表建表的同时可以指定一个指向实际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。通常我们会把数据存储在hdfs上，当hive删除表时，hdfs上的数据不会删除，会保留下来。
3 创建管理表
create table student(
id int,
name string
)
row format delimited
fields terminated by '\t';
create table student2(
id int,name string
);
--根据AS select语句查询结构创建表,复制表结构，复制表数据
create table student3 as select * from student;
--根据like 创建表，只复制表结构，不复制表数据
create table student4 like student;
--创建表,并且自定义location位置
--路径的位置是相对于hdfs的绝对路径来设定的。
create table student6(
id int,
name string
)
row format delimited
fields terminated by '\t'
location '/student6';
查看表信息
--简单查看表信息
desc 表名;
--详细查看表信息
desc formatted 表名;
删除管理表
注意：删除管理表同时会删除hdfs上对应目录的数据，谨慎操作，数据无价。
drop table student3;
4 创建外部表
注意：外部表创建时要加external，外部表的好处就是，删除表的时候，只删除表的元数据信息，不删hdfs上存储的数据，更安全。
原始数据
dept:
10 ACCOUNTING 1700
20 RESEARCH 1800
30 SALES 1900
40 OPERATIONS 1700
emp:
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
把数据上传到hdfs中
hive (default)> dfs -mkdir -p /company/dept;
hive (default)> dfs -mkdir -p /company/emp;
hive (default)> dfs -put /opt/module/hive/datas/dept.txt /user/company/dept;
hive (default)> dfs -put /opt/module/hive/datas/emp.txt / user/company/emp;
建表语句
create external table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t'
location '/user/company/dept';
create external 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 '\t'
location '/user/company/emp';
5 外部表和内部表的转换
通过设置表属性“EXTERNAL”来控制表是外部表还是内部表
TRUE 是 外部表 FALSE 是内部表
alter table student set tblproperties('EXTERNAL'='TRUE/FALSE');
6 修改表
重名表 注意：重命名表会一起修改hdfs上对应的目录名，前提这个表必须是管理表，并且创建这个管理表时没有自定义location
ALTER TABLE table_name RENAME TO new_table_name
更新列
更新列，列名可以随意修改，列的类型只能小改大，不能大改小（遵循自动转换规则）
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
增加列
ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...)
替换列
ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
7 清空表
注意：只能truncate 管理表，外部表不能truncate。truncate的本质其实就是删除hdfs上对应路径的数据。
truncate table 表名;
五、ML数据操作
在MySql中，DML数据操作语言包括增、删、改三项操作，但是Hive中数据不支持删除和修改操作，所以Hive中的DML数据操作语言是：数据的导入和导出两种操作。
5.1 导入
5.1.1 load【不执行MR】
1 通过load命令加载数据
load data [local] inpath '数据的path' [overwrite] into table student [partition (partcol1=val1,…)];
（1）load data:表示加载数据
（2）local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表
（3）inpath:表示加载数据的路径
（4）overwrite:表示覆盖表中已有数据，否则表示追加
（5）into table:表示加载到哪张表
（6）student:表示具体的表
（7）partition:表示上传到指定分区
2 通过本地方式导入
注意：通过本地路径load，本质上其实就是将本地的文件put到hdfs对应的表目录
load data local inpath '/opt/module/hive/datas/student.txt' overwrite into table student;
3通过hdfs路径导入
注意：通过hdfs路径导入，本质是将hdfs上路径的数据剪切到对应的表的hdfs路径
load data inpath '/student.txt' into table student;
4 覆盖导入
注意:加上关键字overwrite 就是覆盖导入，不加的话，默认是追加导入
load data local inpath '/opt/module/hive/datas/student.txt' overwrite into table student;
5.1.2 insert【执行MR】
1) 基本模式插入
into是追加插入，overwrite是覆盖插入，此方式一般没人用
insert into/overwrite table student values(1018,'ss18'),(1019,'ss19');
2）通过查询结果插入
此方式用的比较多，一般都是查询原始表的数据到临时表，
注意select之前不能加as，跟创建表时as select区分开。
insert into table student2 select * from student where id < 1006;
insert overwrite table student2 select * from student where id < 1006;
注意：通过inset插入数据，数据格式和列的数量要一致才可以。
5.1.3 建表语句后+ as select【执行MR】
注意：建表语句后跟select语句时，as不能省略，跟上面的insert into刚好相反
create table student3 as select * from student;
5.1.4 建表指定location位置
需要提前把数据传到hdfs上，然后创建表的时候，指定表的位置为数据的路径
注意 location后面一定要给一个目录，不能直接给文件路径。
hive (default)> dfs -mkdir /student5;
hive (default)> dfs -put /opt/module/hive-3.1.2/testData/student.txt /student5;
create table student5(
id int,name string
)
row format delimited fields terminated by '\t'
location '/student5';
5.1.5 import导入
注意：使用import导入之前，得先使用export导出数据，并且因为export会把数据和元数据一起导出，所以我们使用import导入的时候，表不能存在，否则会元数据冲突，报错。
import table student6 from '/stu';
5.2 导出
5.2.1 insert 导出
默认格式导出
insert overwrite local directory '/opt/module/hive/datas/export/student'
select * from student;
格式化导出
insert overwrite local directory '/opt/module/hive/datas/export/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;
注意：因为insert导出，后面只能跟overwrite，所以我们给路径的时候，一定要具体，防止hive误删重要文件、
这个导出路径可以不存在，hive会帮我们创建路径
5.2.2hadoop命令导出
hadoop dfs -get /user/hive/warehouse/student/student.txt
/opt/module/hive/datas/export/student3.txt;
5.2.3hive shell 命令导出
hive -e 'select * from db_hive.student' > /opt/module/hive/datas/stu.txt
5.2.4 export 导出
注意：export导出只能导出到hdfs上，并且会将元数据和数据一起导出。
使用场景：export和import主要用于两个Hadoop平台集群之间Hive表迁移，不能直接导出的本地。
export table db_hive.student to '/stu';
六、查询DQL
本章所有的查询都以dept表和emp表为例进行操作。
本章所有的HQL操作都使用DBserver工具，为了加快查询速度，设置为本地模式
set hive.exec.mode.local.auto=true; //开启本地mr
SELECT [ALL | DISTINCT] select_expr, select_expr, ... --指定查询字段
FROM table_reference --从哪个表查询
[WHERE where_condition] --指定where过滤条件
[GROUP BY col_list] --指定分组条件
[ORDER BY col_list] --指定排序条件
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list] --hive排序四个by
]
[LIMIT number] --限制输出结果条数
6.1 基本查询
1. 基本查询
select
empno as no,
ename name,
job j
from emp;
注意：
（1）SQL 语言大小写不敏感。
（2）SQL 可以写在一行或者多行
（3）关键字不能被缩写也不能分行
（4）各子句一般要分行写。
（5）使用缩进提高语句的可读性。【缩进不能使用tab键，可以使用空格】
2. 常用的比较运算符
-
between
查询工资在500 到 1000之间的人
select * from emp where sal between 500 and 1000;select * from emp where sal >= 500 and sal <= 1000; -
in（x,y） | not in（x,y）
查询工资为800 或 1300的人
select * from emp where sal in(800,1300);select * from emp where sal=800 or sal=1300; -
is null
查询没有奖金的员工
select * from emp where comm is null;
3. like & rlike
RLIKE子句是Hive中这个功能的一个扩展，其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。
6.2 分组查询
Group by 分组查询语句通常会和聚合函数一起使用，按照一个或者多个列对结果进行分组，然后对每个组执行聚合操作
-
求出每个部门的平均工资
select deptno,avg(sal) avg_sal from emp group by deptno;
Having 语句
having与where不同点
（1）where后面不能写分组聚合函数，而having后面可以使用分组聚合函数。
（2）having只用于group by分组统计语句，对分组后的数据进行再次条件筛选过滤。
-
求平均薪水大于2000的部门
select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;
6.3 join关联查询
Hive支持通常的SQL JOIN语句，但是只支持等值连接，不支持非等值连接。
首先对emp表做一些修改：向emp表中插入一条数据，此数据与dept表不等值。
insert into emp values(7523,"HEATHER","AUTHOR",7839,"1982-5-13",2300,800,50);
1. 内连接：
（1）根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称；
select
e.empno ,
e.ename ,
d.deptno ,
d.deptno
from
emp e join dept d
on e.deptno = d.deptno ;
2. 左外连接
select
e.empno ,
e.ename ,
d.deptno ,
d.deptno
from emp e left join dept d
on e.deptno = d.deptno ;
3. 右外连接
select
e.empno ,
e.ename ,
d.deptno ,
d.deptno
from emp e right join dept d
on e.deptno = d.deptno ;
4. 满外连接（全外连接）
select
e.empno ,
e.ename ,
d.deptno ,
d.deptno
from emp e full join dept d
on e.deptno = d.deptno ;
5. 自连接
查询每位员工的员工编号、员工姓名、领导编号、领导姓名
select
e1.empno ,
e1.ename ,
e1.mgr ,
e2.ename
from emp e1 join emp e2
on e1.mgr = e2.empno ;
select
e1.empno ,
e1.ename ,
e1.mgr ,
e2.ename
from emp e1 left join emp e2
on e1.mgr = e2.empno ;
6.4 排序
1. 全局排序 order by
Order By：全局排序，只有一个Reducer，把所有数据都放在一个分区里面进行排序，这样才能保证输出的结果集是全局有序的。
ASC（ascend）: 升序（默认）
DESC（descend）: 降序
按照多个列，双重排序
--按照部门升序排序，再按照工资倒序排序
select deptno,sal,empno,ename,job from emp order by deptno asc ,sal desc ;
-- 查询员工信息，并按照一年总收入进行倒叙排序
NVL(字段,默认值) ： 对null值的判断。
select ename,job,sal,comm,sal + NVL(comm,0) sumsal from emp order by sumsal desc;
2. 单个reducer排序 sort by
注意：因为单独使用sort by的话，只能给每个分区内的数据排序，但是不能指定分区内的数据都有哪些，属于随机给数据分配分区
因此没人会单独使用sort by来排序，因为出来的结果集没有意义，会结合分区一块使用。
3. 分区 distribute by
Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by类似MR中partition（自定义分区），进行分区，结合sort by使用。
对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。
多个reduce就相当于是多个分区。
-- 设置reduce的个数
set mapreduce.job.reduces=3;
insert overwrite local directory '/opt/module/hive-3.1.2/testData/distribute_result'
select * from emp distribute by deptno sort by sal;
注意：
distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区。
Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
演示完以后mapreduce.job.reduces的值要设置回-1，否则下面分区or分桶表load跑mr的时候有可能会报错
4. 分区排序 Cluster by
当distribute by和sort by字段相同时，可以使用cluster by方式。
但是排序只能升序排，不能再指定desc和asc
select * from emp distribute by deptno sort by deptno;
--上面的sql可以简写成下面的
select * from emp cluster by deptno;
七、分区表
7.1分区表（分目录）
hive存在问题：hive里面没有索引机制，每次查询的时候，hive会暴力扫描整张表。
分区表的本质就是分目录，按照业务需求，把数据分成多个目录存储，然后查询的时候就可以通过where条件指定对应的分区
0. 数据准备
vim dept_20200401.log
10 ACCOUNTING 1700
20 RESEARCH 1800
vim dept_20200402.log
30 SALES 1900
40 OPERATIONS 1700
vim dept_20200403.log
50 TEST 2000
60 DEV 1900
1. 创建分区表语法
create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (day string) -- 指定分区字段
row format delimited
fields terminated by '\t';
分区字段属于分区表的一个伪列，数据里面并没有记录这列的值，分区字段的值体现在分区目录名上面。
2. 加载数据，一定要指定分区
load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table dept_partition partition(day='20200401');
3. 分区表查询数据
select * from dept_partition where day = '20200401';
select * from dept_partition where day='20200401' or day='20200402' or day='20200403';
-- union 是表之间的纵向拼接，join是表之间的横向拼接。
select * from dept_partition where day='20200401'
union
select * from dept_partition where day='20200402'
union
select * from dept_partition where day='20200403';
select * from dept_partition where day = '20200401' or day = '20200402';
4. 查看分区表有多少分区
show partitions dept_partition;
5. 删除分区
注意：删除分区会一起删除掉分区内的数据
alter table dept_partition drop partition (day='__HIVE_DEFAULT_PARTITION__');
--删除多个分区 注意：多个分区间必须有逗号，没有会报错
alter table dept_partition drop partition(day='20200405'),partition(day='20200406');
6. 增加分区
alter table dept_partition add partition(day='20200404') ;
--增加多个分区 注意：多个分区间不能逗号，有会报错
alter table dept_partition add partition(day='20200405') partition(day='20200406');
7. 查看分区表信息
desc formatted dept_partition;
7.2 二级分区表
1. 创建二级分区表
create table dept_partition2(
deptno int, dname string, loc string
)
partitioned by (day string, hour string)
row format delimited fields terminated by '\t';
2. 给二级分区正常加载数据
load data local inpath '/opt/module/hive/datas/dept_20200401.log'
into table dept_partition2
partition(day='20200401',hour='12');
3. 给二级分区增加分区
alter table dept_partition2 add partition(day='20200403',hour='01') partition(day='20200403',hour = '02');
4. 给二级分区删除分区
alter table dept_partition2 drop partition(day='20200403',hour='01'),partition(day='20200403',hour = '02');
5. 分区表和元数据对应三种方式
1. 先上传 再修复表
msck repair table dept_partition2;
2. 先上传数据，然后手动添加分区
-- 创建目录
dfs -mkdir -p /user/hive/warehouse/db_hive.db/dept_partition2/day=20200401/hour=14;
-- 上传数据
dfs -put /opt/module/hive/datas/dept_20200401.log /user/hive/warehouse/db_hive.db/dept_partition2/day=20200401/hour=14;
-- 添加分区
alter table dept_partition2 add partition(day='20200401',hour='14');
-- 执行查询
select * from dept_partition2 where day='20200401' and hour='14';
3. 直接load load数据的时候直接指定分区字段的值，这个时候不仅会上传数据，还会创建对应的分区，并且还会创建元数据。
-- 创建目录
dfs -mkdir -p /user/hive/warehouse/db_hive.db/dept_partition2/day=20200401/hour=15;
-- 上传数据
load data local inpath '/opt/module/hive/datas/dept_20200401.log' into table
dept_partition2 partition(day='20200401',hour='15');
-- 执行查询
select * from dept_partition2 where day='20200401' and hour='15';
7.3 动态分区调整（重点）
1. 开启动态分区参数设置
-
开启动态分区功能（默认true，开启）。
set hive.exec.dynamic.partition=true; -
设置为非严格模式（动态分区的模式，默认strict，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区。）
set hive.exec.dynamic.partition.mode=nonstrict; -
在所有执行MR的节点上，最大一共可以创建多少个动态分区。默认1000。
set hive.exec.max.dynamic.partitions=1000; -
在每个执行MR的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365，如果使用默认值100，则会报错。
set hive.exec.max.dynamic.partitions.pernode=100; -
整个MR Job中，最大可以创建多少个HDFS文件。默认100000。
set hive.exec.max.created.files=100000; -
当有空分区生成时，是否抛出异常。一般不需要设置。默认false。
set hive.error.on.empty.partition=false;
2. 实操
-
可以新创建一张表，然后向表中插入数据。
-
准备数据【向dept表中新增一个字段】
10 ACCOUNTING 1700 20200501 20 RESEARCH 1800 20200501 30 SALES 1900 20200502 40 OPERATIONS 1700 20200503 -
创建一张新表
create table newTable(deptno int, dname string, loc string) partitioned by (day int) row format delimited fields terminated by '\t'; -
上传数据。【不需要指定分区】
load data local inpath '/opt/module/hive-3.1.2/tdatas/tmp/dept2.txt' into table newtable ;
-
-
也可以向已经存在的表中插入数据。【不需要指定分区】
load data local inpath '/opt/module/hive-3.1.2/tdatas/tmp/dept2.txt' into table dept_partition ;
八、分桶表
分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。
分桶是将数据集分解成更容易管理的若干部分的另一个技术。
8.0 分区表和分桶表的区别：
分区表：分区针对的是数据的存储路径；将数据拆分存储在目录中。
分区表的字段是伪字段，一定不能是定义的表中已经存在的字段。
分桶表：分桶针对的是数据文件；将数据进一步拆分存储在文件中。
分桶表的字段必须是定义的表中已经存在的字段。
8.1 创建分桶表
-
数据准备 【student.txt】
1001 ss1 1002 ss2 1003 ss3 1004 ss4 1005 ss5 1006 ss6 1007 ss7 1008 ss8 1009 ss9 1010 ss10 1011 ss11 1012 ss12 1013 ss13 1014 ss14 1015 ss15 1016 ss16 -
创建表
create table stu_buck(id int, name string)
clustered by(id) -- 指定分桶字段
into 4 buckets -- 指定分桶的个数
row format delimited
fields terminated by '\t';
-
上传数据
向分桶表中插入数据会执行MR程序，尽可能的传递hdfs上的路径，而不是Linux本地的路径
-- 通过本地的路径上传数据 load data local inpath '/opt/module/hive-3.1.2/tdatas/tmp/student.txt' into table stu_buck; -- 通过hdfs路径上传数据 load data inpath '/student.txt' into table stu_buck; -
执行查询
select * from stu_buck; -- 以桶为单位将数据进行展示。
8.2 查看分桶表信息
desc formatted stu_buck;
九、函数（重点）
9.0 内置函数
show functions;
查看函数具体用法
desc function extended upper;
9.1 空字段赋值函数 nvl、coalesce
1. nvl函数
nvl 函数中可以传入两个参数，对参数类型没有要求，如果第一个参数不为null时，返回第一个参数，如果第一个参数为null时，返回第二个参数。【将null值转换成我们想要的值】
select sal,comm,sal+ NVL(comm,0) money from emp;
-- 如果员工的奖金为null，返回当前员工的领导编号
select ename,job,sal,mgr,comm,NVL(comm,mgr) from emp;
2. coalesce函数
coalesce 函数可以传入多个参数，参数的类型必须一致，返回的是这些参数中第一个不为null的值。
select coalesce(10,20,-999);
select coalesce(null,20,-999);
select coalesce(null,null,-999);
9.2 CASE WHEN
case when then else end : 和java中的switch 选择结构一样。
准备数据
悟空 A 男
大海 A 男
宋宋 B 男
凤姐 A 女
婷姐 B 女
婷婷 B 女
创建表
create table emp_sex(
name string,
dept_id string,
sex string)
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/hive-3.1.2/tdatas/emp_sex.txt' into table emp_sex;
期望结果 ：求出不同部门男女各多少人
dept_Id 男 女
A 2 1
B 1 2
分析过程
第一步：先求出每个部门有多少人
select
dept_id,
sum(1)
from emp_sex
group by dept_id ;
第二步：求出每个部门男女各多少人
select
dept_id,
sum(case sex when '男' then 1 else 0 end) man,
sum(case sex when '女' then 1 else 0 end) woman
from emp_sex
group by dept_id ;
9.3 行转列 - 行变少
一列多行转一列一行
行转列 ：行数据聚合操作
直观体验：
原始 数据 →→ 转换后 数据 【姓名】name 【工资】salary →→ 【工资】salary 【姓名】name 不二 1000 →→ 1000 不二，李四，赵六，王八 张三 2000 →→ 2000 张三，王五，田七，杨九 李四 1000 →→ 王五 2000 →→ 赵六 1000 →→ 田七 2000 →→ 王八 1000 →→ 杨九 2000 →→ 要求掌握：concat() 、concat_ws() 、collect_list() 、collect_set() 函数
1. 拼接函数
concat(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;
select e.empno,e.ename,e.job,concat(e.empno,'-',e.ename,'-',e.job) from emp e;
concat_ws(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数作为参数间的分隔符。
分隔符可以是与参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
select e.empno,e.ename,e.job,concat_ws('-',cast(e.empno as string),e.ename,e.job) from emp e;
注意：
CONCAT_WS must be “string or array”
如果多个字段的类型不一致，借助于cast(字段 as 类型) 进行类型的转换
2. 行转列函数
collect_set : 去重
collect_list : 不去重
collect_set(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段；如遇到特殊情况，可进行类型的转换。
select concat_ws('-',collect_set(cast(deptno as string))) from emp;
collect_list(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行不去重汇总，产生array类型字段；如遇到特殊情况，可进行类型的转换。
select concat_ws('-',collect_list(cast(deptno as string))) from emp;
原始数据
vim constellation.txt
孙悟空 白羊座 A
大海 射手座 A
宋宋 白羊座 B
猪八戒 白羊座 A
凤姐 射手座 A
苍老师 白羊座 B
创建hive表并导入数据
create table person_info(
name string,
constellation string,
blood_type string)
row format delimited fields terminated by "\t";
load data local inpath "/opt/module/hive-3.1.2/tdatas/tmp/constellation.txt" into table person_info;
需求 ：把星座和血型一样的人归类到一起
射手座,A 大海|凤姐
白羊座,A 孙悟空|猪八戒
白羊座,B 宋宋|苍老师
实现：
select
t.cb,
concat_ws('|',collect_set(name))
from
(
select
name,concat(constellation,',',blood_type) cb
from person_info
) t
group by t.cb;
9.4 列转行 - 行变多
一列一行转一列多行
1. 切割函数
Split(str, separator)：将字符串按照后面的分隔符切割，转换成字符array
select split("tom,jetty,alicy,jems",",");
-- 结果如下
["tom","jetty","alicy","jems"]
2. 列转行函数
EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
explode()函数的参数的类型只能是一个array数组或者map集合，不能是字符串。
简言之，explode()函数的参数类型只能是hive中的array类型和map类型。其他格式中的数组类型和键值对map类型此函数不可用。在使用时，尤其要注意json格式的数据，json格式中的数据，无论是对象，还是数组在hive中统一都是以string格式的字符串存在的。所以在面对json中的数组时，我们不能使用此函数进行炸裂，只能自定义udtf函数进行处理。
当类型不满足条件时，我们可以先使用split()函数对字符串进行切分，切分后就成了数组。但是，一般情况下，此函数单独使用完不成功能，还要结合侧写一起使用。lateral view 。
-- 原始数据
friends中的数据：["bingbing","lili"]
-- 使用炸裂函数
select EXPLODE(friends) from test;
-- 炸裂后的结果数据：
bingbing
lili
LATERAL VIEW
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
lateral view首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。
原始数据
| movie | category |
|---|---|
| 《疑犯追踪》 | 悬疑,动作,科幻,剧情 |
| 《Lie to me》 | 悬疑,警匪,动作,心理,剧情 |
| 《战狼2》 | 战争,动作,灾难 |
需求
《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
《Lie to me》 警匪
《Lie to me》 动作
《Lie to me》 心理
《Lie to me》 剧情
《战狼2》 战争
《战狼2》 动作
《战狼2》 灾难
准备数据 ：
vim movie.txt
《疑犯追踪》 悬疑,动作,科幻,剧情
《Lie to me》 悬疑,警匪,动作,心理,剧情
《战狼2》 战争,动作,灾难
创建hive表并导入数据
create table movie_info(
movie string,
category string)
row format delimited fields terminated by "\t";
load data local inpath "/opt/module/hive-3.1.2/tdatas/tmp/movie.txt" into table movie_info;
实现 第一步，炸开类型
select
explode(split(category,','))
from movie_info;
实现第二步：尝试使用join进行连接，但是不行，没有办法写join条件，造成了笛卡尔积
SELECT
t1.movie,
t2.category_name
from
(
select movie from movie_info
) t1
left join
(
select
explode(split(category,',')) category_name
from movie_info
) t2;
实现第三步：
我们的需求是·想让炸开后的临时表和炸开之前表所在行的其他字段进行join
hive帮我们实现了这个需求 lateral view （侧写视图）
SELECT
movie,
tmp.category_list
FROM
movie_info
lateral view explode(split(category,",")) tmp as category_list;
9.5 窗口函数
窗口函数=函数+窗口
窗口：函数在运算时，我们可以指定函数运算的数据范围
注意：开窗函数的执行顺序是最靠后的，开窗后的别名不能在where等条件中使用，应该使用子查询完成。
9.5.1 定义
窗口函数属于sql中比较高级的函数
mysql从8.0版本才支持窗口函数，5.6 、5.7都没有窗口函数
oracle 里面一直支持窗口函数
hive也支持窗口函数
相关函数
over()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。
over函数的参数——> window窗口字句。
CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：无边界
UNBOUNDED PRECEDING 前无边界，表示从前面的起点，
UNBOUNDED FOLLOWING后无边界，表示到后面的终点
以下函数才是窗口函数
1. 窗口函数：
lead(col,n, default_val)：往后第n行数据。 col 列名，n 往后第几行，默认为1 默认值 默认null
lag(col,n,default_val)：往前第n行数据 。 col 列名，n 往前第几行，默认为1 默认值 默认null
first_value：在当前窗口下的第一个值。FIRST_VALUE(col,true/false) 如果设置为true，则跳过空值。
last_value： 在当前窗口下的最后一个值。LAST_VALUE(col,true/false) 如果设置为true，则跳过空值。
2. 标准聚合函数：
- COUNT
- SUM
- MIN
- MAX
- AVG
3. 分析排名函数
所有的排序函数都不支持window窗口字句。
只需要在窗口中写分区(partition by)、排序(order by)。
- RANK ：排序相同时会重复（考虑并列，会跳号），总数不会变。
- ROW_NUMBER ：排序相同时会重复（考虑并列，不跳号），总数会减少。
- DENSE_RANK ： 根据顺序进行计算（不考虑并列，不跳号，就是行号）。
- NTILE ：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。【详情见下：需求6】
9.5.2 语法
窗口函数 over([partition by 字段] [order by 字段] [ 窗口语句])
partition by 给查出来的结果集按照某个字段分区，分区以后，开窗的大小最大不会超过分区数据的大小
一旦分区之后，我们必须在单个分区内指定窗口。
order by 给分区内的数据按照某个字段排序
9.5.3 窗口语句
(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING
9.5.4 两种特殊情况
①：当指定ORDER BY缺少WINDOW子句时，WINDOW规范默认为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。
②：如果同时缺少ORDER BY和WINDOW子句，则WINDOW规范默认为ROW BETWEENUND UNBOUNDED PRECEDING和UNBOUNDED FOLLOWING。此种情况其实就是聚合函数。
③：如果同时缺少ORDER BY和WINDOW子句，则WINDOW规范默认为ROW BETWEENUND UNBOUNDED PRECEDING和CURRENT ROW。此种情况其实就是first_value 和 last_value。
以下函数在over（）里面只能分区和排序，不能自定义窗口大小了，也就是不能再写window字句
排序分析函数 都不能写 例如： Rank, NTile, DenseRank, CumeDist, PercentRank.
Lead 和 Lag不能写
9.5.5 窗口需求
（1）查询在2017年4月份购买过的顾客及总人数
（2）查询顾客的购买明细及月购买总额
（3）上述的场景, 将每个顾客的cost按照日期进行累加
（4）查询顾客购买明细以及上次的购买时间和下次购买时间
（5）查询顾客每个月第一次的购买时间 和 每个月的最后一次购买时间
（6）查询前20%时间的订单信息
数据准备
vim business.txt
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94
创建hive表并导入数据
create table business(
name string,
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
load data local inpath "/opt/module/hive/datas/business.txt" into table business;
需求1、 查询在2017年4月份购买过的顾客及总人数
select
name,
count(1) over(rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING)
from business
where year(orderdate) =2017 and month(orderdate) =4
group by name;
由于窗口语句有三种特殊情况，我们这种刚好符合第二种，因此可以省略掉窗口语句
select
name,
count(1) over()
from business
where year(orderdate) =2017 and month(orderdate) =4
group by name;
需求2、 查询顾客的购买明细及月购买总额
SELECT
name,
orderdate,
cost,
sum(cost) over(partition by name,month(orderdate))
from business;
需求3、上述的场景, 将每个顾客的cost按照日期进行累加
select
name,
orderdate,
cost,
-- 将每个顾客的cost按照日期进行累加
sum(cost) over(partition by name order by orderdate) cost1,
-- 将每个顾客的cost按照日期进行累加
sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) cost2,
-- 将每个顾客的cost按照日期累加上一天，当天和下一天【共计3天】的总和
sum(cost) over(partition by name order by orderdate rows between 1 preceding and 1 following) cost3,
-- 将每个顾客的cost按照日期累加上一天和当天【共计2天】的总和
sum(cost) over(partition by name order by orderdate rows between 1 preceding and current row) cost4,
-- 将每个顾客的cost按照日期累加从当天到最后一天的总和
sum(cost) over(partition by name order by orderdate rows between current row and unbounded following) cost5
from business;
需求4、 查询顾客购买明细以及上次的购买时间和下次购买时间
-- 方式1-----------
select
name,
orderdate,
cost,
LAG(orderdate) over(partition by name order by orderdate) prev_time,
LEAD(orderdate) over(partition by name order by orderdate) next_time
from business;
-- 方式2-----------
select
name,
orderdate,
cost,
LAG(orderdate,1,'无') over(partition by name order by orderdate) prev_time,
LEAD(orderdate,1,'无') over(partition by name order by orderdate) next_time
from business;
需求5、 查询顾客每个月第一次的购买时间 和 每个月的最后一次购买时间
注意：LAST_VALUE和FIRST_VALUE 需要自定义windows字句，否则出现错误
select
name,
orderdate,
cost,
FIRST_VALUE(orderdate)
over(partition by name,month(orderdate) order by orderdate rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) first_time,
LAST_VALUE(orderdate)
over(partition by name,month(orderdate) order by orderdate rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) last_time
from business;
需求6、 查询前20%时间的订单信息
select
t1.*
FROM
(
select
name,
orderdate,
cost,
ntile(5) over(order by orderdate ) nsort
from business
) t1
where t1.nsort = 1;
9.6 排名行数
- RANK ：排序相同时会重复（考虑并列，会跳号），总数不会变。
- ROW_NUMBER ：排序相同时会重复（考虑并列，不跳号），总数会减少。
- DENSE_RANK ： 根据顺序进行计算（不考虑并列，不跳号，就是行号）。
准备数据
vim score.txt
孙悟空 语文 87
孙悟空 数学 95
孙悟空 英语 68
大海 语文 94
大海 数学 56
大海 英语 84
宋宋 语文 64
宋宋 数学 86
宋宋 英语 84
婷婷 语文 65
婷婷 数学 85
婷婷 英语 78
创建hive表并上传数据
create table score(
name string,
subject string,
score int)
row format delimited fields terminated by "\t";
load data local inpath '/opt/module/hive/datas/score.txt' into table score;
需求1：计算每门学科成绩排名
SELECT
name,
subject,
score,
RANK() over(partition by subject order by score desc) rk,
DENSE_RANK() over(partition by subject order by score desc) drk,
ROW_NUMBER() over(partition by subject order by score desc) rown
FROM score ;
需求1 效果展示图
需求2：求出每门学科前三名的学生
SELECT
t.*
FROM
(
SELECT
name,
subject,
score,
RANK() over(partition by subject order by score desc) rk
FROM score
) t
where t.rk <= 3;
9.7 其他函数
1. 日期函数
unix_timestamp:返回当前或指定时间的时间戳
select unix_timestamp(); select unix_timestamp('2008-08-08 08:08:08');
from_unixtime：将时间戳转为日期格式
select from_unixtime(1218182888);
current_date：当前日期
select current_date();
current_timestamp：当前的日期加时间
select current_timestamp();
to_date：抽取日期部分
select to_date('2008-08-08 08:08:08'); select to_date(current_timestamp());
year：获取年 select year(current_timestamp());
month：获取月 select month(current_timestamp());
day：获取日 select DAY(current_timestamp());
hour：获取时 select HOUR(current_timestamp());
minute：获取分 select minute(current_timestamp());
second：获取秒 select SECOND(current_timestamp());
weekofyear：当前时间是一年中的第几周
select weekofyear(current_timestamp()); select weekofyear('2020-01-08');
dayofmonth：当前时间是一个月中的第几天
select dayofmonth(current_timestamp()); select dayofmonth('2020-01-08');
months_between： 两个日期间的月份
select months_between('2020-07-29','2020-06-28');
add_months：日期加减月
select add_months('2020-06-28',1);
datediff：两个日期相差的天数
select datediff('2019-03-01','2019-02-01');
select datediff('2020-03-01','2020-02-01');
date_add：日期加天数
select date_add('2019-02-28',1); select date_add('2020-02-28',1);
date_sub：日期减天数
select date_sub('2019-03-01',1); select date_sub('2020-03-01',1);
next_day(date1, day_of_week)【这个地方的day代表是周几的意思】 :返回第一个晚于start_date的日期。
-- 下周一
select next_day(`current_date`(),'mo');
-- 本周一
select date_add(next_day(`current_date`(),'mo'),-7);
-- 本周日
select next_day(`current_date`(),'su');
last_day：日期的当月的最后一天
select last_day('2020-02-28'); select last_day('2019-02-28');
date_format() ：格式化日期 【日期格式：'yyyy-MM-dd hh:mm:ss' 】
select date_format('2008-08-08 08:08:08','yyyy-MM-dd hh:mm:ss');
2. 取整函数
round： 四舍五入 select round(4.5);
ceil： 向上取整 select ceil(4.5);
floor： 向下取整 select floor(4.5);
3. 字符串函数
upper： 转大写 select upper('abcDEFg');
lower： 转小写 select lower('abcDEFg');
length： 长度 select length('abcDEFg');
trim： 前后去空格
select length(' abcDEFg '); select length(trim(' abcDEFg '));
lpad： 向左补齐，到指定长度 select lpad('abc',11,'*');
rpad： 向右补齐，到指定长度 select rpad('abc',11,'*');
substring: 剪切字符串
select substring('abcdefg',1,3);
select rpad(substring('13843838438',1,3),11,'*');
regexp_replace：
SELECT regexp_replace('100-200', '(\\d+)', 'num');
select regexp_replace('abc d e f',' ','');
使用正则表达式匹配目标字符串，匹配成功后替换！
4. 集合函数
size： 集合中元素的个数
map_keys： 返回map中的key
map_values: 返回map中的value
select size(friends),map_keys(children),map_values(children) from person;
array_contains: 判断array中是否包含某个元素
select array_contains(friends,'lili') from person;
sort_array： 将array中的元素排序
select sort_array(split('1,3,4,5,2,6,9',','));
select sort_array(split('a,d,g,b,c,f,e',','));
总结
几个关键字总结
1 建表 ：PARTITIONED BY(分区表) 、 CLUSTERED BY（分桶表）
2 查询：ORDER BY(全局排序) 、 SORT BY(区内排序)
DITRIBUTE BY（分区）、CLUSTER BY（分区排序）3 窗口函数：PARTITION BY(对数据分区) ORDER BY（排序）
十、自定义udf
自定义函数的分类
根据用户自定义函数类别分为以下三种：
（1）UDF（User-Defined-Function）
一进一出
类似于upper()
（2）UDAF（User-Defined Aggregation Function）
用户自定义聚合函数，多进一出
类似于：count/max/min
（3）UDTF（User-Defined Table-Generating Functions）
用户自定义表生成函数，一进多出
如lateral view explode()
自定义函数的步骤
1. 继承Hive提供的类
-
org.apache.hadoop.hive.ql.udf.generic.GenericUDF
-
org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
2. 实现类中的3个抽象方法
-
initialize
- 初始化方法，此方法处理3个业务
- 约束函数传入的参数的个数
- 约束函数传入的参数的类型
- 约束函数返回值的类型的检查器
- 初始化方法，此方法处理3个业务
-
evaluate
- 函数逻辑处理的核心方法
- 逻辑处理的结果返回，这个返回值的类型是通过初始化方法的最后一步进行约束的。
- 函数逻辑处理的核心方法
-
getDisplayString
- 返回一个显示的字符串，一般情况下用不上。
代码示例：
public class MyLength extends GenericUDF {
/*
TODO 初始化方法： 有3个作用
- 约束函数传入的参数的个数
- 约束函数传入的参数的类型
- 约束函数返回值的类型
*/
@Override
public ObjectInspector initialize(ObjectInspector[] objectInspectors) throws UDFArgumentException {
//1. 约束函数传入的参数的个数
if (objectInspectors.length != 1) {
throw new UDFArgumentLengthException("参数个数必须是1个！");
}
//2. 约束函数传入的参数的类型
if (!objectInspectors[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)) {
throw new UDFArgumentTypeException(0, "类型不匹配，请传入基本数据类型的值");
}
//3. 约束函数返回值的类型
return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
}
// 逻辑处理核心方法
@Override
public Object evaluate(DeferredObject[] deferredObjects) throws HiveException {
// 获取参数
Object obj = deferredObjects[0].get();
// 转换为String类型
String str = obj.toString();
int len = str.length();
// 之所以在此处可以直接返回int类型的值，就是因为在initialize初始化方法中对返回值的类型做了约束。
return len;
}
//返回一个显示的字符串，这个方法用不上。
@Override
public String getDisplayString(String[] strings) {
return "";
}
}
3. 在hive的命令行窗口创建函数
1. 创建临时函数
把程序打成jar包【myudf.jar】并把jar包上传到Linux系统的/opt/module/hive-3.1.2/tdatas目录下
添加jar包的类路径给hive，注意是临时生效
add jar /opt/module/hive-3.1.2/tdatas/myudf.jar;
创建临时函数
create temporary function my_len as "com.heather.hive.MyLength";
通过命令查询，函数是否创建成功
使用函数
SELECT my_len("abcd"); -- 4
SELECT my_len(TRUE); -- 4
SELECT my_len(12345); -- 5
SELECT my_len("abcd",123); -- 报错【SQL 错误 [10015] [21000]: Error while compiling statement: FAILED: SemanticException [Error 10015]: Line 1:7 Arguments length mismatch '123': 参数个数必须是1个！】
SELECT my_len(split("a,b,c,d",",")); -- 报错【SQL 错误 [10016] [42000]: Error while compiling statement: FAILED: SemanticException [Error 10016]: Line 2:14 Argument type mismatch '","': 类型不匹配，请传入基本数据类型的值】
删除临时函数
drop temporary function my_len;
注意：临时函数只跟会话有关系，只要会话不断，在当前会话下，任意一个库都可以使用。其他会话全都不能使用。
2. 创建永久函数
注意：因为永久函数是永久生效的，我们推出当前会话以后，其他会话也要使用永久函数，因此我们就不能简单的使用add jar来添加hive的类路径了
第一种方式 - auxlib
在$HIVE_HOME下面创建 auxlib 目录
[heather@hadoop01 hive-3.1.2]$ mkdir auxlib
将jar包上传到$HIVE_HOME/auxlib下，然后重启hive
创建永久函数，在default库下创建永久函数
hive (default) > create function my_len2 as "com.heather.hive.MyLength";
使用函数
注意：永久函数在创建时会以库为前缀名，如果是操作当前库，可以直接使用；
如果操作的数据库不是当前库，则在使用时需要在函数名前 + 库名。
在default库中，不需要加库名
SELECT my_len2("abcd");
SELECT my_len2(12345);
SELECT my_len2(true);
SELECT my_len2("abcd",123); -- 报错【SQL 错误 [10015] [21000]: Error while compiling statement: FAILED: SemanticException [Error 10015]: Line 1:7 Arguments length mismatch '123': 参数个数必须是1个！】
SELECT my_len2(split("a,b,c,d",",")); -- 报错【SQL 错误 [10016] [42000]: Error while compiling statement: FAILED: SemanticException [Error 10016]: Line 2:14 Argument type mismatch '","': 类型不匹配，请传入基本数据类型的值】
在db_hive库中，需要加库名
SELECT default.my_len2("abcd");
SELECT default.my_len2(12345);
SELECT default.my_len2(true);
SELECT default.my_len2("abcd",123); -- 报错【SQL 错误 [10015] [21000]: Error while compiling statement: FAILED: SemanticException [Error 10015]: Line 1:7 Arguments length mismatch '123': 参数个数必须是1个！】
SELECT default.my_len2(split("a,b,c,d",",")); -- 报错【SQL 错误 [10016] [42000]: Error while compiling statement: FAILED: SemanticException [Error 10016]: Line 2:14 Argument type mismatch '","': 类型不匹配，请传入基本数据类型的值】
第二种方式 - hdfs
注意：此时要使用USING JAR的方式来添加函数的jar包类路径，并且这个路径必须是hdfs路径；
使用规则和第一种方式一样，当前库使用不需要 + 库名，其他库使用需要 + 库名前缀。
create function my_len2 as "com.heather.hive.MyLength" USING JAR 'hdfs://hadoop01:9820/hivejar/myudf.jar';
删除永久函数
drop function my_len2;
自定义永久函数的注意事项
永久函数创建的时候，在函数名之前需要自己加上库名，如果不指定库名的话，会默认把当前库的库名给加上。
然后使用永久函数的时候，需要在指定的库里面操作,或者在其他库里面使用的话得加上 库名.函数名
十一、压缩和存储
11. 1 压缩
11.1.1 MR程序中的压缩回顾
| 压缩格式 | 算法 | 文件扩展名 | 是否可切分 | 优点 | 缺点 |
|---|---|---|---|---|---|
| DEFLATE | DEFLATE | .deflate | 否 | ||
| Gzip | DEFLATE | .gz | 否 | 压缩率比较高 | 不支持 Split；压缩/解压速度一般 |
| bzip2 | bzip2 | .bz2 | 是 | 压缩率高；支持 Split | 压缩/解压速度慢 |
| LZO | LZO | .lzo | 是 | 压缩/解压速度比较快；支持 Split | 压缩率一般；想支持切片需要额外创建索引 |
| Snappy | Snappy | .snappy | 否 | 压缩和解压缩速度快 | 不支持 Split；压缩率一般 |
压缩方式的选择重点考虑：
压缩/解压缩速度、压缩率（压缩后存储大小）、压缩后是否可以支持切片
压缩场景选择：
压缩可以在 MapReduce 作用的任意阶段启用
Map阶段就是shuffle阶段的压缩，我们一般选择Snappy格式，就是因为快！！！
11.1.2 Hive中的压缩
因为Hive就是Hadoop的客户端工具，所以在开启Hive端的压缩之前，应该首先把MR程序中的压缩打开。
1. 开启Map输出阶段压缩（MR引擎）
开启map输出阶段压缩可以减少job中map和Reduce task间数据传输量。具体配置如下：
-
开启hive中间传输数据压缩功能
hive (default)>set hive.exec.compress.intermediate=true; -
开启mapreduce中map输出压缩功能
hive (default)>set mapreduce.map.output.compress=true; -
设置mapreduce中map输出数据的压缩方式
hive (default)>set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec; -
执行查询语句
hive (default)> select count(ename) name from emp;
2. 开启Reduce输出阶段压缩
当Hive将输出写入到表中时，输出内容同样可以进行压缩。属性hive.exec.compress.output控制着这个功能。用户可能需要保持默认设置文件中的默认值false，这样默认的输出就是非压缩的纯文本文件了。用户可以通过在查询语句或执行脚本中设置这个值为true，来开启输出结果压缩功能。
-
开启hive最终输出数据压缩功能
hive (default)>set hive.exec.compress.output=true; -
开启mapreduce最终输出数据压缩
hive (default)>set mapreduce.output.fileoutputformat.compress=true; -
设置mapreduce最终数据输出压缩方式
hive (default)> set mapreduce.output.fileoutputformat.compress.codec =org.apache.hadoop.io.compress.SnappyCodec; -
设置mapreduce最终数据输出压缩为块压缩
hive (default)> set mapreduce.output.fileoutputformat.compress.codec =org.apache.hadoop.io.compress.SnappyCodec; -
测试一下输出结果是否是压缩文件
hive (default)> insert overwrite local directory '/opt/module/hive/datas/compress/' select * from emp distribute by deptno sort by empno desc;
11.2 存储
Hive支持的存储数据的格式主要有：TEXTFILE 、SEQUENCEFILE、ORC、PARQUET。【了解即可】
TEXTFILE和SEQUENCEFILE的存储格式都是基于行存储的；
ORC和PARQUET是基于列式存储的。
具体的行存和列存的区别，请详细查看HBase笔记。
1. TextFile格式
默认格式，数据不做压缩，磁盘开销大，数据解析开销大。可结合Gzip、Bzip2使用，但使用Gzip这种方式，hive不会对数据进行切分，从而无法对数据进行并行操作。
2. Orc格式
Orc (Optimized Row Columnar)是Hive 0.11版里引入的新的存储格式。
如下图所示可以看到每个Orc文件由1个或多个stripe组成，每个stripe一般为HDFS的块大小**【HDFS的块大小默认为128M，stripe的默认大小为256M】**，每一个stripe包含多条记录，这些记录按照列进行独立存储，对应到Parquet中的row group的概念。每个Stripe里有三部分组成，分别是Index Data，Row Data，Stripe Footer
1）Index Data：一个轻量级的index，默认是每隔1W行做一个索引。这里做的索引应该只是记录某行的各字段在Row Data中的offset。
2）Row Data：存的是具体的数据，先取部分行，然后对这些行按列进行存储。对每个列进行了编码，分成多个Stream来存储。
3）Stripe Footer：存的是各个Stream的类型，长度等信息。
每个文件有一个File Footer，这里面存的是每个Stripe的行数，每个Column的数据类型信息等；每个文件的尾部是一个PostScript，这里面记录了整个文件的压缩类型以及FileFooter的长度信息等。在读取文件时，会seek到文件尾部读PostScript，从里面解析到File Footer长度，再读FileFooter，从里面解析到各个Stripe信息，再读各个Stripe，即从后往前读。
3. Parquet格式
Parquet文件是以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的数据和元数据，因此Parquet格式文件是自解析的。
（1）行组(Row Group)：每一个行组包含一定的行数，在一个HDFS文件中至少存储一个行组，类似于orc的stripe的概念。
（2）列块(Column Chunk)：在一个行组中每一列保存在一个列块中，行组中的所有列连续的存储在这个行组文件中。一个列块中的值都是相同类型的，不同的列块可能使用不同的算法进行压缩。
（3）页(Page)：每一个列块划分为多个页，一个页是最小的编码的单位，在同一个列块的不同页可能使用不同的编码方式。
通常情况下，在存储Parquet数据的时候会按照Block大小设置行组的大小，由于一般情况下每一个Mapper任务处理数据的最小单位是一个Block，这样可以把每一个行组由一个Mapper任务处理，增大任务执行并行度。Parquet文件的格式。
主流文件存储格式对比
存储文件的对比
ORC > Parquet > textFile
存储文件的查询速度总结
查询速度相近。
11.3 压缩和存储结合
首先，要清楚的知道压缩格式都有哪些，存储的格式有哪些；
再者，还要明白两者结合使用时 数据表如何创建
1. 创建一个ZLIB压缩的ORC存储方式
向Linux系统中上传数据 —— 文本格式的数据
创建表
create table log_orc_zlib(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as orc -- 指定存储格式【列数存储】
tblproperties("orc.compress"="ZLIB"); -- 指定压缩格式
插入数据
load data local inpath 。。。 这种方式插入数据会失败，报错！！！
inset into table log_orc_zlib select * from textFile格式表名; 这种格式是正确的！！！
原因：Linux系统上的文件是文本格式的，但是创建的表是orc列式存储格式的，格式不匹配导致的！！！
2. 创建一个SNAPPY压缩的ORC存储方式
创建表
create table log_orc_snappy(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as orc -- 指定存储格式【列数存储】
tblproperties("orc.compress"="SNAPPY"); -- 指定压缩格式
插入数据
和上面一个道理，使用inset into 方式
3. 创建一个SNAPPY压缩的parquet存储方式
create table log_parquet_snappy(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by '\t'
stored as parquet -- 指定存储格式【列数存储】
tblproperties("parquet.compression"="SNAPPY"); -- 指定压缩格式
插入数据
和上面一个道理，使用inset into 方式
11.4 开发总结
在数仓项目中，
如果底层使用的是Spark，那么会采用 Parquest + Snappy(Spark运行在内存中，不需要切片)；
如果底层使用的是Hive，那么会采用ORC + LZO(支持切片) 。
十二、Hive调优
12.1 Fetch抓取
Fetch抓取是指，Hive中对某些情况的查询可以不必使用MapReduce计算。例如：SELECT * FROM emp;在这种情况下，Hive可以简单地读取emp对应的存储目录下的文件，然后输出查询结果到控制台。
在hive-default.xml.template文件中hive.fetch.task.conversion默认是more，老版本hive默认是minimal，该属性修改为more以后，在全局查找、字段查找、limit查找等都不走mapreduce。
<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.
<!-- 关闭Fetch抓取功能 -->
0. none : disable hive.fetch.task.conversion
<!-- 按照分区进行过滤时不走MR -->
1. minimal : SELECT STAR, FILTER on partition columns, LIMIT only
2. more : SELECT, FILTER, LIMIT only (support TABLESAMPLE and virtual columns)
</description>
</property>
12.2 Local模式
大多数的Hadoop Job是需要Hadoop提供的完整的可扩展性来处理大数据集的。不过，有时Hive的输入数据量是非常小的。在这种情况下，为查询触发执行任务消耗的时间可能会比实际job的执行时间要多的多。对于大多数这种情况，Hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间可以明显被缩短。
用户可以通过设置hive.exec.mode.local.auto的值为true，来让Hive在适当的时候自动启动这个优化。
set hive.exec.mode.local.auto=true; //开启本地mr
//设置local mr的最大输入数据量，当输入数据量小于这个值时采用local mr的方式，默认为134217728，即128M
set hive.exec.mode.local.auto.inputbytes.max=50000000;
//设置local mr的最大输入文件个数，当输入文件个数小于这个值时采用local mr的方式，默认为4
set hive.exec.mode.local.auto.input.files.max=10;
12.3 表优化
1. 小表、大表join
将key相对分散，并且数据量小的表放在join的左边，这样可以有效减少内存溢出错误发生的几率；再进一步，可以使用map join让小的维度表（1000条以下的记录条数）先进内存。在map端完成reduce。
实际测试发现：新版的hive已经对小表JOIN大表和大表JOIN小表进行了优化。小表放在左边和右边已经没有明显区别。
2. 大表 join 大表
1. 空key过滤
有时join超时是因为某些key对应的数据太多，而相同key对应的数据都会发送到相同的reducer上，从而导致内存不够。此时我们应该仔细分析这些异常的key，很多情况下，这些key对应的数据是异常数据，我们需要在SQL语句中进行过滤。例如key对应的字段为空，操作如下：
2. 空key转换
有时虽然某个key为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join的结果中，此时我们可以表a中key为空的字段赋一个随机的值，使得数据随机均匀地分布到不同的reducer上。
- 如果key为空，可以使用nvl()函数转换。
- 如果key不为空，可以自定义函数转换。
3. MapJoin
如果不指定MapJoin或者不符合MapJoin的条件，那么Hive解析器会将Join操作转换成Common Join，即：在Reduce阶段完成join。容易发生数据倾斜。可以用MapJoin把小表全部加载到内存在map端进行join，避免reducer处理。
-
开启MapJoin参数设置
-
设置自动选择Mapjoin
set hive.auto.convert.join = true; 默认为true -
大表小表的阈值设置（默认25M以下认为是小表）
set hive.mapjoin.smalltable.filesize=25000000;
-
-
MapJoin工作机制
4. Group By
默认情况下，Map阶段同一Key数据分发给一个reduce，当一个key数据过大时就倾斜了。
并不是所有的聚合操作都需要在Reduce端完成，很多聚合操作都可以先在Map端进行部分聚合，最后在Reduce端得出最终结果。
开启Map端聚合参数设置
-
是否在Map端进行聚合，默认为True
set hive.map.aggr = true -
在Map端进行聚合操作的条目数目
set hive.groupby.mapaggr.checkinterval = 100000 -
有数据倾斜的时候进行负载均衡（默认是false）
set hive.groupby.skewindata = true
| 说明： |
|---|
| 当选项设定为 true，生成的查询计划会有两个MR Job。第一个MR Job中，Map的输出结果会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的Group By Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；第二个MR Job再根据预处理的数据结果按照Group By Key分布到Reduce中（这个过程可以保证相同的Group By Key被分布到同一个Reduce中），最后完成最终的聚合操作。 |
5. Count(Distinct) 去重统计
数据量小的时候无所谓，数据量大的情况下，由于COUNT DISTINCT操作需要用一个Reduce Task来完成，这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换,但是需要注意group by造成的数据倾斜问题。
所以说，distinct 去重操作时慎重使用。
在做去重操作时，应该先分组。
使用步骤
-
设置reduce个数
set mapreduce.job.reduces = 5; -
使用distinct查询
select count(distinct deptno) from emp; -
使用group by 分组查询
select count(t.deptno) from ( select deptno from emp group by deptno ) t;
6. 笛卡尔积
尽量避免笛卡尔积，join的时候不加on条件，或者无效的on条件，Hive只能使用1个reducer来完成笛卡尔积。
7. 行列过滤
列处理：在SELECT中，只拿需要的列，如果有，尽量使用分区过滤，少用SELECT *。
行处理：在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在Where后面，那么就会先全表关联，之后再过滤。 这种操作称之为 “谓词下推”
谓词：
谓词：返回bool值即true和false的函数，或是隐式转换为bool的函数 |
|---|
如SQL中的谓词主要有 LKIE、BETWEEN、IS NULL、IS NOT NULL、IN、EXISTS。 |
如Spark中的input.filter(x=> x >= 5) |
谓词下推：
| 谓词下推的基本思想：↓↓↓ |
|---|
| 将过滤表达式尽可能移动至靠近数据源的位置，以使真正执行时能直接跳过无关的数据。 |
简单实力说明：
-
不使用谓词下推 ——> 先关联两张表，再用where条件过滤
select o.id from bigtable b join bigtable o on o.id = b.id where o.id <= 10; -
使用谓词下推 ——> 通过子查询后，再关联表
select b.id from bigtable b join (select id from bigtable where id <= 10 ) o on b.id = o.id;
8. 合理设置Map及Reduce数
1）通常情况下，作业会通过input的目录产生一个或者多个map任务。
主要的决定因素有：input的文件总个数，input的文件大小，集群设置的文件块大小。
2）是不是map数越多越好？
答案是否定的。如果一个任务有很多小文件（远远小于块大小128m），则每个小文件也会被当做一个块，用一个map任务来完成，而一个map任务启动和初始化的时间远远大于逻辑处理的时间，就会造成很大的资源浪费。而且，同时可执行的map数是受限的。
3）是不是保证每个map处理接近128m的文件块，就高枕无忧了？
答案也是不一定。比如有一个127m的文件，正常会用一个map去完成，但这个文件只有一个或者两个小字段，却有几千万的记录，如果map处理的逻辑比较复杂，用一个map任务去做，肯定也比较耗时。
针对上面的问题2和3，我们需要采取两种方式来解决：即减少map数和增加map数；
1. 复杂文件增加Map数
当input的文件都很大，任务逻辑复杂，map执行非常慢的时候，可以考虑增加Map数，来使得每个map处理的数据量减少，从而提高任务的执行效率。
增加map的方法为：根据
computeSliteSize(Math.max(minSize,Math.min(maxSize,blocksize)))=blocksize=128M公式，调整maxSize最大值。让maxSize最大值低于blocksize就可以增加map的个数。
-
执行查询
select count(*) from emp; Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1 -
设置最大切片值为100个字节
set mapreduce.input.fileinputformat.split.maxsize=100; select count(*) from emp; Hadoop job information for Stage-1: number of mappers: 6; number of reducers: 1
2. 小文件进行合并
1）在map执行前合并小文件，减少map数：CombineHiveInputFormat具有对小文件进行合并的功能（系统默认的格式）。HiveInputFormat没有对小文件合并功能。
ps : 在新版本的Hive 3版本中，下面的配置项都是默认值，不用配置。
set hive.input.format= org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
2）在Map-Reduce的任务结束时合并小文件的设置：
-
在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;
3. 合理设置Reduce数
1）调整reduce个数方法一
-
每个Reduce处理的数据量默认是256MB
hive.exec.reducers.bytes.per.reducer=256000000 -
每个任务最大的reduce数，默认为1009
hive.exec.reducers.max=1009 -
计算reducer数的公式
N=min(参数2，总输入数据量/参数1)
2）调整reduce个数方法二
在hadoop的mapred-default.xml文件中修改
设置每个job的Reduce个数
set mapreduce.job.reduces = 15;
3）reduce个数并不是越多越好
- 过多的启动和初始化reduce也会消耗时间和资源；
- 另外，有多少个reduce，就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题；
**总结：**在设置reduce个数的时候也需要考虑这两个原则：处理大数据量利用合适的reduce数；使单个reduce任务处理数据量大小要合适；
9. 并行执行
Hive会将一个查询转化成一个或者多个阶段。这样的阶段可以是MapReduce阶段、抽样阶段、合并阶段、limit阶段。或者Hive执行过程中可能需要的其他阶段。默认情况下，Hive一次只会执行一个阶段。不过，某个特定的job可能包含众多的阶段，而这些阶段可能并非完全互相依赖的，也就是说有些阶段是可以并行执行的，这样可能使得整个job的执行时间缩短。不过，如果有更多的阶段可以并行执行，那么job可能就越快完成。
通过设置参数hive.exec.parallel值为true，就可以开启并发执行。不过，在共享集群中，需要注意下，如果job中并行阶段增多，那么集群利用率就会增加。
set hive.exec.parallel=true; //打开任务并行执行，默认为false
set hive.exec.parallel.thread.number=16;//同一个sql允许最大并行度，默认为8。
当然，得是在系统资源比较空闲的时候才有优势，否则，没资源，并行也起不来。
10. 安全模式
Hive可以通过设置防止一些危险操作
-
分区表不使用分区过滤
将hive.strict.checks.no.partition.filter设置为true时，对于分区表，除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行。换句话说，就是用户不允许扫描所有分区。进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表。 -
使用order by没有limit过滤
将hive.strict.checks.orderby.no.limit设置为true时，对于使用了order by语句的查询，要求必须使用limit语句。因为order by为了执行排序过程会将所有的结果数据分发到同一个Reducer中进行处理，强制要求用户增加这个LIMIT语句可以防止Reducer额外执行很长一段时间。 -
笛卡尔积
将hive.strict.checks.cartesian.product设置为true时，会限制笛卡尔积的查询。对关系型数据库非常了解的用户可能期望在 执行JOIN查询的时候不使用ON语句而是使用where语句，这样关系数据库的执行优化器就可以高效地将WHERE语句转化成那个ON语句。不幸的是，Hive并不会执行这种优化，因此，如果表足够大，那么这个查询就会出现不可控的情况。
小试牛刀
需求描述
统计硅谷影音视频网站的常规指标，各种TopN指标：
– 统计视频观看数Top10
– 统计视频类别热度Top10（类别热度：类别下的总视频数）
– 统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数
– 统计视频观看数Top50所关联视频的所属类别Rank
– 统计每个类别中的视频热度Top10,以Music为例（视频热度：视频观看数）
– 统计每个类别视频观看数Top10
– 统计上传视频最多的用户Top10以及他们上传的视频观看次数在前20的视频
数据结构
- 视频表
| 字段 | 备注 | 详细描述 |
|---|---|---|
| videoId | 视频唯一id（String） | 11位字符串 |
| uploader | 视频上传者（String） | 上传视频的用户名String |
| age | 视频年龄（int） | 视频在平台上的整数天 |
| category | 视频类别（Array） | 上传视频指定的视频分类 |
| length | 视频长度（Int） | 整形数字标识的视频长度 |
| views | 观看次数（Int） | 视频被浏览的次数 |
| rate | 视频评分（Double） | 满分5分 |
| Ratings | 流量（Int） | 视频的流量，整型数字 |
| conments | 评论数（Int） | 一个视频的整数评论数 |
| relatedId | 相关视频id（Array） | 相关视频的id，最多20个 |
- 用户表
| 字段 | 备注 | 字段类型 |
|---|---|---|
| uploader | 上传者用户名 | string |
| videos | 上传视频数 | int |
| friends | 朋友数量 | int |
准备工作
-
需要准备的表
-
创建外部数据表：gulivideo_ori，gulivideo_user_ori 【原始表】
-
创建最终表：gulivideo_orc，gulivideo_user_orc
-
-
创建外部数据表
- 上传原始数据到HDFS
[heather@hadoop01 tdatas] pwd /opt/module/hive-3.1.2/tdatas [heather@hadoop01 tdatas] hadoop fs -mkdir -p /gulivideo/video [heather@hadoop01 tdatas] hadoop fs -mkdir -p /gulivideo/user [heather@hadoop01 tdatas] hadoop fs -put gulivideo/user/user.txt /gulivideo/user [heather@hadoop01 tdatas] hadoop fs -put gulivideo/video/*.txt /gulivideo/video- 创建外部数据表：gulivideo_ori
create external table gulivideo_ori( videoId string, uploader string, age int, category array<string>, length int, views int, rate float, ratings int, comments int, relatedId array<string>) row format delimited fields terminated by "\t" collection items terminated by "&" stored as textfile location '/gulivideo/video';- 创建外部数据表: gulivideo_user_ori
create external table gulivideo_user_ori( uploader string, videos int, friends int) row format delimited fields terminated by "\t" stored as textfile location '/gulivideo/user'; -
创建orc存储格式带snappy压缩的管理表
- gulivideo_orc
create table gulivideo_orc(
videoId string,
uploader string,
age int,
category array<string>,
length int,
views int,
rate float,
ratings int,
comments int,
relatedId array<string>)
stored as orc
tblproperties("orc.compress"="SNAPPY");
- gulivideo_user_orc
create table gulivideo_user_orc(
uploader string,
videos int,
friends int)
row format delimited
fields terminated by "\t"
stored as orc
tblproperties("orc.compress"="SNAPPY");
- 向表中插入数据，通过查询两个外部表的方式，insert into 。
insert into table gulivideo_orc select * from gulivideo_ori;
insert into table gulivideo_user_orc select * from gulivideo_user_ori;
业务实现
统计硅谷影音视频网站的常规指标，各种TopN指标
需求1 ：
统计视频观看数Top10
select
t.*
from
(
select
videoid ,
uploader ,
`views` ,
RANK () over(order by `views` desc) rk
from gulivideo_orc
) t
where t.rk <= 10;
需求2 ：
统计视频类别热度Top10（类别热度：类别下的总视频数）
-- 先把类别字段炸开
select
videoid ,
category ,
tmp.category_name
from gulivideo_orc
lateral view explode(category) tmp as category_name;
-- 1. 简单实现
select
t.category_name,
count(t.videoid ) cate_hot
from
(
select
videoid ,
category ,
tmp.category_name
from gulivideo_orc
lateral view explode(category) tmp as category_name
) t
group by t.category_name
order by cate_hot desc
limit 10;
-- 2. 使用开窗函数实现
-- 步骤1：
select
t.category_name,
count(t.videoid) cate_hot,
rank() over(order by count(t.videoid) desc ) rk
from
(
select
videoid ,
category ,
tmp.category_name
from gulivideo_orc
lateral view explode(category) tmp as category_name
) t
group by t.category_name;
-- 步骤2：
SELECT
t1.*
FROM
(
select
t.category_name,
count(t.videoid) cate_hot,
rank() over(order by count(t.videoid) desc ) rk
from
(
select
videoid ,
category ,
tmp.category_name
from gulivideo_orc
lateral view explode(category) tmp as category_name
) t
group by t.category_name
) t1
where t1.rk <= 10;
需求3 ：
统计出视频观看数最高的20个视频的所属类别以及类别包含Top20视频的个数
select * from gulivideo_orc ;
-- 3.1 先统计出观看数最高的20个视频的所属类别
SELECT
videoid ,
category ,
`views`
FROM gulivideo_orc
order by `views` DESC
LIMIT 20;
-- 3.2 炸开视频类别
SELECT
t.videoid ,
tmp.cate_name
FROM
(
SELECT
videoid ,
category ,
`views`
FROM gulivideo_orc
order by `views` DESC
LIMIT 20
) t
LATERAL VIEW explode(t.category) tmp as cate_name;
-- 3.3 根据类型分组，获取每个类别中的视频个数
SELECT
t1.cate_name,
COUNT(videoid) cate_video_count
FROM
(
SELECT
t.videoid ,
tmp.cate_name
FROM
(
SELECT
videoid ,
category ,
`views`
FROM gulivideo_orc
order by `views` DESC
LIMIT 20
) t
LATERAL VIEW explode(t.category) tmp as cate_name
) t1
group by t1.cate_name;
-- 3.4 需求扩展：把结果进行排序
SELECT
t1.cate_name,
COUNT(videoid) cate_video_count,
RANK () OVER (order by COUNT(videoid) DESC ) rk
FROM
(
SELECT
t.videoid ,
tmp.cate_name
FROM
(
SELECT
videoid ,
category ,
`views`
FROM gulivideo_orc
order by `views` DESC
LIMIT 20
) t
LATERAL VIEW explode(t.category) tmp as cate_name
) t1
group by t1.cate_name;
需求4 ：
统计视频观看数Top50所关联视频的所属类别Rank
-- 4. 统计视频观看数Top50所关联视频的所属类别Rank
SELECT * FROM gulivideo_orc ;
-- -----------------第一种方式-----------------
--4.1 求出视频观看数Top50的视频的信息,重点是求出这50个视频的关联视频
SELECT
videoid ,
`views` ,
relatedid
FROM gulivideo_orc
order by videoid
LIMIT 50;
--4.2 炸开上述结果中的关联视频id
SELECT
t.videoid,
tmp.new_relatedid
FROM
(
SELECT
videoid ,
`views` ,
relatedid
FROM gulivideo_orc
order by videoid
LIMIT 50
) t
lateral view explode(t.relatedid) tmp as new_relatedid;
--4.3 利用上述结果中的new_relatedid和视频表中的videoid进行join,求出每个new_relatedid的类别
SELECT
t2.videoid,
t2.category
FROM
(
SELECT
t.videoid,
tmp.new_relatedid
FROM
(
SELECT
videoid ,
`views` ,
relatedid
FROM gulivideo_orc
order by videoid
LIMIT 50
) t
lateral view explode(t.relatedid) tmp as new_relatedid
) t1
join gulivideo_orc t2
on t1.new_relatedid = t2.videoid;
-- 4.4 炸开上述结果中的category
SELECT
t3.videoid,
tmp2.cate_name
FROM
(
SELECT
t2.videoid,
t2.category
FROM
(
SELECT
t.videoid,
tmp.new_relatedid
FROM
(
SELECT
videoid ,
`views` ,
relatedid
FROM gulivideo_orc
order by videoid
LIMIT 50
) t
lateral view explode(t.relatedid) tmp as new_relatedid
) t1
join gulivideo_orc t2
on t1.new_relatedid = t2.videoid
) t3
lateral view explode(t3.category) tmp2 as cate_name;
--4.5 对上述结果按照炸开的类别category_name分组,然后统计每个分组下有多少个视频，并排序
SELECT
t4.cate_name,
COUNT(t4.videoid) cate_video_count,
RANK() over(order by COUNT(t4.videoid) DESC ) rk
FROM
(
SELECT
t3.videoid,
tmp2.cate_name
FROM
(
SELECT
t2.videoid,
t2.category
FROM
(
SELECT
t.videoid,
tmp.new_relatedid
FROM
(
SELECT
videoid ,
`views` ,
relatedid
FROM gulivideo_orc
order by videoid
LIMIT 50
) t
lateral view explode(t.relatedid) tmp as new_relatedid
) t1
join gulivideo_orc t2
on t1.new_relatedid = t2.videoid
) t3
lateral view explode(t3.category) tmp2 as cate_name
) t4
group by t4.cate_name;
-- ----------------第二种方式--------------------
--4.5 对上述结果按照炸开的类别category_name分组,然后统计每个分组下有多少个视频
SELECT
t4.cate_name,
COUNT(t4.videoid) cate_video_count
FROM
(
SELECT
t3.videoid,
tmp2.cate_name
FROM
(
SELECT
t2.videoid,
t2.category
FROM
(
SELECT
t.videoid,
tmp.new_relatedid
FROM
(
SELECT
videoid ,
`views` ,
relatedid
FROM gulivideo_orc
order by videoid
LIMIT 50
) t
lateral view explode(t.relatedid) tmp as new_relatedid
) t1
join gulivideo_orc t2
on t1.new_relatedid = t2.videoid
) t3
lateral view explode(t3.category) tmp2 as cate_name
) t4
group by t4.cate_name;
--4.5 对上述结果按照炸开的类别category_name分组,然后统计每个分组下有多少个视频，并排序
SELECT
t5.cate_name ,
t5.cate_video_count cate_count,
RANK () over (order by cate_count DESC ) rk
FROM
(
SELECT
t4.cate_name,
COUNT(t4.videoid) cate_video_count
FROM
(
SELECT
t3.videoid,
tmp2.cate_name
FROM
(
SELECT
t2.videoid,
t2.category
FROM
(
SELECT
t.videoid,
tmp.new_relatedid
FROM
(
SELECT
videoid ,
`views` ,
relatedid
FROM gulivideo_orc
order by videoid
LIMIT 50
) t
lateral view explode(t.relatedid) tmp as new_relatedid
) t1
join gulivideo_orc t2
on t1.new_relatedid = t2.videoid
) t3
lateral view explode(t3.category) tmp2 as cate_name
) t4
group by t4.cate_name
) t5;
需求5 ：
统计每个类别中的视频热度Top10,以Music为例（视频热度：视频观看数）
SELECT
videoid ,
`views` ,
tmp.cate_name
FROM gulivideo_orc
lateral view explode(category) tmp as cate_name
where tmp.cate_name = 'Music'
order by `views` DESC
LIMIT 10;
需求6 ：
统计每个类别视频观看数Top10
-- 6.1 炸开类别
SELECT
tmp.cate_name,
videoid ,
`views`
FROM gulivideo_orc
lateral view explode(category) tmp as cate_name;
-- 6.2 按照炸裂开的类别 category_name 分区,按照观看数倒序排序,编号
SELECT
t.cate_name,
t.videoid,
t.views,
RANK () over(partition by cate_name order by `views` DESC ) rk
FROM
(
SELECT
tmp.cate_name,
videoid ,
`views`
FROM gulivideo_orc
lateral view explode(category) tmp as cate_name
) t ;
-- 6.3 写层子查询,按照上述结果中的rk过滤
SELECT
t2.cate_name,
t2.videoid,
t2.views,
t2.rk
FROM
(
SELECT
t.cate_name,
t.videoid,
t.views,
RANK () over(partition by cate_name order by `views` DESC ) rk
FROM
(
SELECT
tmp.cate_name,
videoid ,
`views`
FROM gulivideo_orc
lateral view explode(category) tmp as cate_name
) t
) t2
where t2.rk <= 10;
需求7 ：
统计上传视频最多的用户Top10以及他们上传的视频观看次数在前20的视频
此需求的后半段“他们上传的视频观看次数在前20的视频”，说的含糊不清，闪烁其词，因此我们有下面三种约定，每种约定的sql写法都不一致，大家要好好理解一下。
约定1：取Top10中所有人上传的视频的前20
思路：
（1）去用户表gulivideo_user_orc求出上传视频最多的10个用户
（2）关联gulivideo_orc表，求出这10个用户上传的所有的视频，按照观看数取前20
--7.1 去用户表求出上传视频个数最多的前10个用户
SELECT
uploader,
videos
from gulivideo_user_orc
order by videos DESC
limit 10;
--7.2 用上述结果中的uploader 跟 视频表的uploader进行join,求出这十个大牛上传的所有的视频
SELECT
t2.uploader,
t2.`views`
FROM
(
SELECT
uploader,
videos
from gulivideo_user_orc
order by videos DESC
limit 10
) t1
JOIN gulivideo_orc t2
on t1.uploader = t2.uploader
order by t2.`views` DESC
limit 20;
约定2：取Top10中每个人上传的视频的前20
思路：
（1）去用户表gulivideo_user_orc求出上传视频最多的10个用户
（2）关联gulivideo_orc表，求出这10个用户上传的所有的视频id，视频观看次数，还要按照uploader分 区，views倒序排序，求出每个uploder的上传的视频的观看排名rk
（3）在第二步的结果上，按照rk进行where过滤，求出rk<=20的数据
--7.1 去用户表求出上传视频个数最多的前10个用户
SELECT
uploader,
videos
from gulivideo_user_orc
order by videos DESC
limit 10;
--7.2 用上述结果中的uploader 跟 视频表的uploader进行join,求出这十个大牛上传的所有的视频
SELECT
t2.uploader,
t2.`views`
FROM
(
SELECT
uploader,
videos
from gulivideo_user_orc
order by videos DESC
limit 10
) t1
JOIN gulivideo_orc t2
on t1.uploader = t2.uploader
order by t2.`views` DESC
limit 20;
-- 7.3 用rank函数,对上述结果中的uploader,按照`views` 倒序排序,编号
SELECT
t3.uploader,
t3.`views`,
RANK() OVER(partition by t3.uploader order by t3.`views` DESC ) rk
FROM
(
SELECT
t2.uploader,
t2.`views`
FROM
(
SELECT
uploader,
videos
from gulivideo_user_orc
order by videos DESC
limit 10
) t1
JOIN gulivideo_orc t2
on t1.uploader = t2.uploader
order by t2.`views` DESC
limit 20
) t3 ;
-- 7.4 对上述结果,利用rk过滤
SELECT
t4.*
FROM
(
SELECT
t3.uploader,
t3.`views`,
RANK() OVER(partition by t3.uploader order by t3.`views` DESC ) rk
FROM
(
SELECT
t2.uploader,
t2.`views`
FROM
(
SELECT
uploader,
videos
from gulivideo_user_orc
order by videos DESC
limit 10
) t1
JOIN gulivideo_orc t2
on t1.uploader = t2.uploader
) t3
) t4
where t4.rk <= 20;
约定3：Top10用户上传的所有视频，有哪些视频是在所有视频观看次数前20的视频。
思路：
（1）去用户表gulivideo_user_orc求出上传视频最多的10个用户
（2）关联gulivideo_orc表，求出这10个用户上传的所有的视频id，视频观看次数
（3）在第二步的结果上，与视频表观看次数前20的数据进行内连接，求出Top10用户上传的视频有哪些是观看次数前20的视频
--7.1 去用户表求出上传视频个数最多的前10个用户
SELECT
uploader,
videos
from gulivideo_user_orc
order by videos DESC
limit 10;
--7.2 用上述结果中的uploader 跟 视频表的uploader进行join,求出这十个大牛上传的所有的视频
SELECT
t2.videoid,
t2.uploader,
t2.`views`
FROM
(
SELECT
uploader,
videos
from gulivideo_user_orc
order by videos DESC
limit 10
) t1
JOIN gulivideo_orc t2
on t1.uploader = t2.uploader;
--7.3 利用上述结果的videoid和视频表视频观看数前20进行join
SELECT
t4.videoid,
t4.views
FROM
(
SELECT
t2.videoid,
t2.uploader,
t2.`views`
FROM
(
SELECT
uploader,
videos
from gulivideo_user_orc
order by videos DESC
limit 10
) t1
JOIN gulivideo_orc t2
on t1.uploader = t2.uploader
) t3
JOIN
(
SELECT
videoid ,
`views`
FROM gulivideo_orc
order by `views`
LIMIT 20
) t4
on t3.videoid = t4.videoid;
蚂蚁森林
数据准备：
user_low_carbon.txt
u_001 2017/1/1 10
u_001 2017/1/2 150
u_001 2017/1/2 110
u_001 2017/1/2 10
u_001 2017/1/4 50
u_001 2017/1/4 10
u_001 2017/1/6 45
u_001 2017/1/6 90
u_002 2017/1/1 10
u_002 2017/1/2 150
u_002 2017/1/2 70
u_002 2017/1/3 30
u_002 2017/1/3 80
u_002 2017/1/4 150
u_002 2017/1/5 101
u_002 2017/1/6 68
u_003 2017/1/1 20
u_003 2017/1/2 10
u_003 2017/1/2 150
u_003 2017/1/3 160
u_003 2017/1/4 20
u_003 2017/1/5 120
u_003 2017/1/6 20
u_003 2017/1/7 10
u_003 2017/1/7 110
u_004 2017/1/1 110
u_004 2017/1/2 20
u_004 2017/1/2 50
u_004 2017/1/3 120
u_004 2017/1/4 30
u_004 2017/1/5 60
u_004 2017/1/6 120
u_004 2017/1/7 10
u_004 2017/1/7 120
u_005 2017/1/1 80
u_005 2017/1/2 50
u_005 2017/1/2 80
u_005 2017/1/3 180
u_005 2017/1/4 180
u_005 2017/1/4 10
u_005 2017/1/5 80
u_005 2017/1/6 280
u_005 2017/1/7 80
u_005 2017/1/7 80
u_006 2017/1/1 40
u_006 2017/1/2 40
u_006 2017/1/2 140
u_006 2017/1/3 210
u_006 2017/1/3 10
u_006 2017/1/4 40
u_006 2017/1/5 40
u_006 2017/1/6 20
u_006 2017/1/7 50
u_006 2017/1/7 240
u_007 2017/1/1 130
u_007 2017/1/2 30
u_007 2017/1/2 330
u_007 2017/1/3 30
u_007 2017/1/4 530
u_007 2017/1/5 30
u_007 2017/1/6 230
u_007 2017/1/7 130
u_007 2017/1/7 30
u_008 2017/1/1 160
u_008 2017/1/2 60
u_008 2017/1/2 60
u_008 2017/1/3 60
u_008 2017/1/4 260
u_008 2017/1/5 360
u_008 2017/1/6 160
u_008 2017/1/7 60
u_008 2017/1/7 60
u_009 2017/1/1 70
u_009 2017/1/2 70
u_009 2017/1/2 70
u_009 2017/1/3 170
u_009 2017/1/4 270
u_009 2017/1/5 70
u_009 2017/1/6 70
u_009 2017/1/7 70
u_009 2017/1/7 70
u_010 2017/1/1 90
u_010 2017/1/2 90
u_010 2017/1/2 90
u_010 2017/1/3 90
u_010 2017/1/4 90
u_010 2017/1/4 80
u_010 2017/1/5 90
u_010 2017/1/5 90
u_010 2017/1/6 190
u_010 2017/1/7 90
u_010 2017/1/7 90
u_011 2017/1/1 110
u_011 2017/1/2 100
u_011 2017/1/2 100
u_011 2017/1/3 120
u_011 2017/1/4 100
u_011 2017/1/5 100
u_011 2017/1/6 100
u_011 2017/1/7 130
u_011 2017/1/7 100
u_012 2017/1/1 10
u_012 2017/1/2 120
u_012 2017/1/2 10
u_012 2017/1/3 10
u_012 2017/1/4 50
u_012 2017/1/5 10
u_012 2017/1/6 20
u_012 2017/1/7 10
u_012 2017/1/7 10
u_013 2017/1/1 50
u_013 2017/1/2 150
u_013 2017/1/2 50
u_013 2017/1/3 150
u_013 2017/1/4 550
u_013 2017/1/5 350
u_013 2017/1/6 50
u_013 2017/1/7 20
u_013 2017/1/7 60
u_014 2017/1/1 220
u_014 2017/1/2 120
u_014 2017/1/2 20
u_014 2017/1/3 20
u_014 2017/1/4 20
u_014 2017/1/5 250
u_014 2017/1/6 120
u_014 2017/1/7 270
u_014 2017/1/7 20
u_015 2017/1/1 10
u_015 2017/1/2 20
u_015 2017/1/2 10
u_015 2017/1/3 10
u_015 2017/1/4 20
u_015 2017/1/5 70
u_015 2017/1/6 10
u_015 2017/1/7 80
u_015 2017/1/7 60
plant_carbon.txt
p001 梭梭树 17
p002 沙柳 19
p003 樟子树 146
p004 胡杨 215
上传数据到Linux系统指定目录下：
/opt/module/hive/datas/
创建数据表
create table user_low_carbon(user_id String,data_dt String,low_carbon int) row format delimited fields terminated by '\t';
create table plant_carbon(plant_id string,plant_name String,low_carbon int) row format delimited fields terminated by '\t';
加载数据：
load data local inpath "/opt/module/hive-3.1.2/tdatas/user_low_carbon.txt" into table user_low_carbon;
load data local inpath "/opt/module/hive-3.1.2/tdatas/plant_carbon.txt" into table plant_carbon;
需求1：
1.蚂蚁森林植物申领统计
问题：假设2017年1月1日开始记录低碳数据（user_low_carbon），假设2017年10月1日之前满足申领条件的用户都申领了一颗p004-胡杨，
剩余的能量全部用来领取“p002-沙柳” 。
统计在10月1日累计申领“p002-沙柳” 排名前10的用户信息；以及他比后一名多领了几颗沙柳。
得到的统计结果如下表样式：
user_id plant_count less_count(比后一名多领了几颗沙柳)
u_101 1000 100
u_088 900 400
u_103 500 …
sql实现：
-- 1. 转换日期格式
select
user_id ,
date_format(REPLACE (data_dt,'/','-'),'yyyy-MM-dd') data_dt ,
low_carbon
from user_low_carbon;
-- 2. 对数据进行筛选，10月份之前的
SELECT
t.user_id,
sum(low_carbon ) sum_low_carbon
FROM
(
select
user_id ,
date_format(REPLACE (data_dt,'/','-'),'yyyy-MM-dd') data_dt ,
low_carbon
from user_low_carbon
) t
where YEAR (data_dt) = 2017 and MONTH (data_dt) <= 10
group by user_id
order by sum_low_carbon DESC
LIMIT 11;
-- 3. 求沙柳的数量
SELECT
t1.user_id,
FLOOR ((t1.sum_low_carbon - (SELECT low_carbon FROM plant_carbon where plant_name = '胡杨')) / (SELECT low_carbon FROM plant_carbon where plant_name = '沙柳')) plant_count
FROM
(
SELECT
t.user_id,
sum(low_carbon ) sum_low_carbon
FROM
(
select
user_id ,
date_format(REPLACE (data_dt,'/','-'),'yyyy-MM-dd') data_dt ,
low_carbon
from user_low_carbon
) t
where YEAR (data_dt) = 2017 and MONTH (data_dt) <= 10
group by user_id
order by sum_low_carbon DESC
LIMIT 11
) t1 ;
-- 4.使用开窗函数求相邻沙柳数量
SELECT
t2.user_id,
t2.plant_count,
lead (plant_count,1,0) over(order by plant_count DESC ) lead_count
FROM
(
SELECT
t1.user_id,
FLOOR ((t1.sum_low_carbon - (SELECT low_carbon FROM plant_carbon where plant_name = '胡杨')) / (SELECT low_carbon FROM plant_carbon where plant_name = '沙柳')) plant_count
FROM
(
SELECT
t.user_id,
sum(low_carbon ) sum_low_carbon
FROM
(
select
user_id ,
date_format(REPLACE (data_dt,'/','-'),'yyyy-MM-dd') data_dt ,
low_carbon
from user_low_carbon
) t
where YEAR (data_dt) = 2017 and MONTH (data_dt) <= 10
group by user_id
order by sum_low_carbon DESC
LIMIT 11
) t1
) t2 ;
--5 最终结果
SELECT
t3.user_id,
t3.plant_count,
(t3.plant_count - t3.lead_count) less_count
FROM
(
SELECT
t2.user_id,
t2.plant_count,
lead (plant_count,1,0) over(order by plant_count DESC ) lead_count
FROM
(
SELECT
t1.user_id,
FLOOR ((t1.sum_low_carbon - (SELECT low_carbon FROM plant_carbon where plant_name = '胡杨')) / (SELECT low_carbon FROM plant_carbon where plant_name = '沙柳')) plant_count
FROM
(
SELECT
t.user_id,
sum(low_carbon ) sum_low_carbon
FROM
(
select
user_id ,
date_format(REPLACE (data_dt,'/','-'),'yyyy-MM-dd') data_dt ,
low_carbon
from user_low_carbon
) t
where YEAR (data_dt) = 2017 and MONTH (data_dt) <= 10
group by user_id
order by sum_low_carbon DESC
LIMIT 11
) t1
) t2
) t3;
需求2：
蚂蚁森林低碳用户排名分析
问题：查询user_low_carbon表中每日流水记录，条件为：
用户在2017年，连续三天（或以上）的天数里，
每天减少碳排放（low_carbon）都超过100g的用户低碳流水。
需要查询返回满足以上条件的user_low_carbon表中的记录流水。
例如用户u_002符合条件的记录如下，因为2017/1/2~2017/1/5连续四天的碳排放量之和都大于等于100g：
user_id data_dt low_carbon
u_002 2017/1/2 150
u_002 2017/1/2 70
u_002 2017/1/3 30
u_002 2017/1/3 80
u_002 2017/1/4 150
u_002 2017/1/5 101
sql实现：
-- 1. 转换日期格式
SELECT
user_id ,
date_format(REPLACE (data_dt,'/','-'),'yyyy-MM-dd') data_dt ,
low_carbon
FROM user_low_carbon ;
-- 2. 筛选出每人每日能力大于100g的数据
SELECT
t.user_id,
t.data_dt,
sum(low_carbon ) sum_carbon
FROM
(
SELECT
user_id ,
date_format(REPLACE (data_dt,'/','-'),'yyyy-MM-dd') data_dt ,
low_carbon
FROM user_low_carbon
) t
group by t.user_id,t.data_dt
having sum_carbon > 100;
--3. 按照用户分区，按照data_dt排序，然后求个row_number排序
SELECT
t1.user_id,
t1.data_dt,
t1.sum_carbon,
RANK () over(partition by t1.user_id order by t1.data_dt ) rk
FROM
(
SELECT
t.user_id,
t.data_dt,
sum(low_carbon ) sum_carbon
FROM
(
SELECT
user_id ,
date_format(REPLACE (data_dt,'/','-'),'yyyy-MM-dd') data_dt ,
low_carbon
FROM user_low_carbon
) t
group by t.user_id,t.data_dt
having sum_carbon > 100
) t1 ;
-- 4. 用data_id减去rn递增序列，求出差值date_diff
SELECT
t2.user_id,
t2.data_dt,
t2.sum_carbon,
t2.rk,
date_sub(data_dt ,rk) date_diff
FROM
(
SELECT
t1.user_id,
t1.data_dt,
t1.sum_carbon,
RANK () over(partition by t1.user_id order by t1.data_dt ) rk
FROM
(
SELECT
t.user_id,
t.data_dt,
sum(low_carbon ) sum_carbon
FROM
(
SELECT
user_id ,
date_format(REPLACE (data_dt,'/','-'),'yyyy-MM-dd') data_dt ,
low_carbon
FROM user_low_carbon
) t
group by t.user_id,t.data_dt
having sum_carbon > 100
) t1
) t2 ;
-- 5. 按照user_id和data_diff分区，计算出每个用户下data_diff相同的个数
SELECT
t3.user_id,
t3.data_dt,
t3.sum_carbon,
t3.date_diff,
count(*) over(partition by t3.user_id,t3.date_diff ) date_count
FROM
(
SELECT
t2.user_id,
t2.data_dt,
t2.sum_carbon,
t2.rk,
date_sub(data_dt ,rk) date_diff
FROM
(
SELECT
t1.user_id,
t1.data_dt,
t1.sum_carbon,
RANK () over(partition by t1.user_id order by t1.data_dt ) rk
FROM
(
SELECT
t.user_id,
t.data_dt,
sum(low_carbon ) sum_carbon
FROM
(
SELECT
user_id ,
date_format(REPLACE (data_dt,'/','-'),'yyyy-MM-dd') data_dt ,
low_carbon
FROM user_low_carbon
) t
group by t.user_id,t.data_dt
having sum_carbon > 100
) t1
) t2
) t3;
-- 6. 按照diff_count过滤，求出diff_count大于等于3的数据
SELECT
t4.user_id,
t4.data_dt,
t4.sum_carbon,
t4.date_diff,
t4.date_count
FROM
(
SELECT
t3.user_id,
t3.data_dt,
t3.sum_carbon,
t3.date_diff,
count(*) over(partition by t3.user_id,t3.date_diff ) date_count
FROM
(
SELECT
t2.user_id,
t2.data_dt,
t2.sum_carbon,
t2.rk,
date_sub(data_dt ,rk) date_diff
FROM
(
SELECT
t1.user_id,
t1.data_dt,
t1.sum_carbon,
RANK () over(partition by t1.user_id order by t1.data_dt ) rk
FROM
(
SELECT
t.user_id,
t.data_dt,
sum(low_carbon ) sum_carbon
FROM
(
SELECT
user_id ,
date_format(REPLACE (data_dt,'/','-'),'yyyy-MM-dd') data_dt ,
low_carbon
FROM user_low_carbon
) t
group by t.user_id,t.data_dt
having sum_carbon > 100
) t1
) t2
) t3
) t4
where t4.date_count >= 3;
-- 7. 在上述结果之上和处理好日期格式的临时表join，求出每个用户的能量获取明细，关联条件user_id和data_dt
-- 因为题目中要求最终的结果是原始数据的流水
SELECT
t6.user_id,
t6.data_dt,
t6.low_carbon
FROM
(
SELECT
t4.user_id,
t4.data_dt,
t4.sum_carbon,
t4.date_diff,
t4.date_count
FROM
(
SELECT
t3.user_id,
t3.data_dt,
t3.sum_carbon,
t3.date_diff,
count(*) over(partition by t3.user_id,t3.date_diff ) date_count
FROM
(
SELECT
t2.user_id,
t2.data_dt,
t2.sum_carbon,
t2.rk,
date_sub(data_dt ,rk) date_diff
FROM
(
SELECT
t1.user_id,
t1.data_dt,
t1.sum_carbon,
RANK () over(partition by t1.user_id order by t1.data_dt ) rk
FROM
(
SELECT
t.user_id,
t.data_dt,
sum(low_carbon ) sum_carbon
FROM
(
SELECT
user_id ,
date_format(REPLACE (data_dt,'/','-'),'yyyy-MM-dd') data_dt ,
low_carbon
FROM user_low_carbon
) t
group by t.user_id,t.data_dt
having sum_carbon > 100
) t1
) t2
) t3
) t4
where t4.date_count >= 3
) t5
join
(
SELECT
user_id ,
date_format(REPLACE (data_dt,'/','-'),'yyyy-MM-dd') data_dt ,
low_carbon
FROM user_low_carbon
) t6
on t5.user_id = t6.user_id and t5.data_dt = t6.data_dt;
常见错误及解决方案
0） 如果更换Tez引擎后，执行任务卡住，可以尝试调节容量调度器的资源调度策略
将$HADOOP_HOME/etc/hadoop/capacity-scheduler.xml文件中的
<property>
<name>yarn.scheduler.capacity.maximum-am-resource-percent</name>
<value>0.1</value>
<description>
Maximum percent of resources in the cluster which can be used to run
application masters i.e. controls number of concurrent running
applications.
</description>
</property>
改成
<property>
<name>yarn.scheduler.capacity.maximum-am-resource-percent</name>
<value>1</value>
<description>
Maximum percent of resources in the cluster which can be used to run
application masters i.e. controls number of concurrent running
applications.
</description>
</property>
1）连接不上mysql数据库
- 导错驱动包，应该把mysql-connector-java-5.1.27-bin.jar导入/opt/module/hive/lib的不是这个包。错把mysql-connector-java-5.1.27.tar.gz导入hive/lib包下。
- 修改user表中的主机名称没有都修改为%，而是修改为localhost
2）hive默认的输入格式处理是CombineHiveInputFormat，会对小文件进行合并。
hive (default)> set hive.input.format;
hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat
可以采用HiveInputFormat就会根据分区数输出相应的文件。
hive (default)> set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
**3）**不能执行mapreduce程序
可能是hadoop的yarn没开启。
**4）**启动mysql服务时，报MySQL server PID file could not be found! 异常。
在/var/lock/subsys/mysql路径下创建hadoop102.pid，并在文件中添加内容：4396
**5）**报service mysql status MySQL is not running, but lock file (/var/lock/subsys/mysql[失败])异常。
解决方案：在/var/lib/mysql 目录下创建： -rw-rw----. 1 mysql mysql 5 12月 22 16:41 hadoop102.pid 文件，并修改权限为 777。
**6）**JVM堆内存溢出（hive集群运行模式）
描述：java.lang.OutOfMemoryError: Java heap space
解决：在yarn-site.xml中加入如下代码
<property>
<name>yarn.scheduler.maximum-allocation-mb</name>
<value>2048</value>
</property>
<property>
<name>yarn.scheduler.minimum-allocation-mb</name>
<value>2048</value>
</property>
<property>
<name>yarn.nodemanager.vmem-pmem-ratio</name>
<value>2.1</value>
</property>
<property>
<name>mapred.child.java.opts</name>
<value>-Xmx1024m</value>
</property>
**7）**JVM堆内存溢出（hive本地运行模式）
描述：在启用hive本地模式后，hive.log报错java.lang.OutOfMemoryError: Java heap space
解决方案1（临时）：
在hive客户端临时设置io.sort.mb和mapreduce.task.io.sort.mb两个参数的值为10
0: jdbc:hive2://hadoop102:10000> set io.sort.mb;
+-----------------+
| set |
+-----------------+
| io.sort.mb=100 |
+-----------------+
1 row selected (0.008 seconds)
0: jdbc:hive2://hadoop102:10000> set mapreduce.task.io.sort.mb;
+--------------------------------+
| set |
+--------------------------------+
| mapreduce.task.io.sort.mb=100 |
+--------------------------------+
1 row selected (0.008 seconds)
0: jdbc:hive2://hadoop102:10000> set io.sort.mb = 10;
No rows affected (0.005 seconds)
0: jdbc:hive2://hadoop102:10000> set mapreduce.task.io.sort.mb = 10;
No rows affected (0.004 seconds)
解决方案2（永久生效）：
在$HIVE_HOME/conf下添加hive-env.sh
[atguigu@hadoop102 conf]$ pwd
/opt/module/hive/conf
[atguigu@hadoop102 conf]$ cp hive-env.sh.template hive-env.sh
然后将其中的参数 export HADOOP_HEAPSIZE=1024的注释放开，然后重启hive。
**8）**虚拟内存限制
在yarn-site.xml中添加如下配置:
<property>
<name>yarn.nodemanager.vmem-check-enabled</name>
<value>false</value>
</property>
t
FROM
(
SELECT
t2.user_id,
t2.data_dt,
t2.sum_carbon,
t2.rk,
date_sub(data_dt ,rk) date_diff
FROM
(
SELECT
t1.user_id,
t1.data_dt,
t1.sum_carbon,
RANK () over(partition by t1.user_id order by t1.data_dt ) rk
FROM
(
SELECT
t.user_id,
t.data_dt,
sum(low_carbon ) sum_carbon
FROM
(
SELECT
user_id ,
date_format(REPLACE (data_dt,’/’,’-’),‘yyyy-MM-dd’) data_dt ,
low_carbon
FROM user_low_carbon
) t
group by t.user_id,t.data_dt
having sum_carbon > 100
) t1
) t2
) t3
) t4
where t4.date_count >= 3
) t5
join
(
SELECT
user_id ,
date_format(REPLACE (data_dt,’/’,’-’),‘yyyy-MM-dd’) data_dt ,
low_carbon
FROM user_low_carbon
) t6
on t5.user_id = t6.user_id and t5.data_dt = t6.data_dt;
# 常见错误及解决方案
**0）** 如果更换Tez引擎后，执行任务卡住，可以尝试调节容量调度器的资源调度策略
> 将$HADOOP_HOME/etc/hadoop/capacity-scheduler.xml文件中的
```xml
<property>
<name>yarn.scheduler.capacity.maximum-am-resource-percent</name>
<value>0.1</value>
<description>
Maximum percent of resources in the cluster which can be used to run
application masters i.e. controls number of concurrent running
applications.
</description>
</property>
改成
<property>
<name>yarn.scheduler.capacity.maximum-am-resource-percent</name>
<value>1</value>
<description>
Maximum percent of resources in the cluster which can be used to run
application masters i.e. controls number of concurrent running
applications.
</description>
</property>
1）连接不上mysql数据库
- 导错驱动包，应该把mysql-connector-java-5.1.27-bin.jar导入/opt/module/hive/lib的不是这个包。错把mysql-connector-java-5.1.27.tar.gz导入hive/lib包下。
- 修改user表中的主机名称没有都修改为%，而是修改为localhost
2）hive默认的输入格式处理是CombineHiveInputFormat，会对小文件进行合并。
hive (default)> set hive.input.format;
hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat
可以采用HiveInputFormat就会根据分区数输出相应的文件。
hive (default)> set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
**3）**不能执行mapreduce程序
可能是hadoop的yarn没开启。
**4）**启动mysql服务时，报MySQL server PID file could not be found! 异常。
在/var/lock/subsys/mysql路径下创建hadoop102.pid，并在文件中添加内容：4396
**5）**报service mysql status MySQL is not running, but lock file (/var/lock/subsys/mysql[失败])异常。
解决方案：在/var/lib/mysql 目录下创建： -rw-rw----. 1 mysql mysql 5 12月 22 16:41 hadoop102.pid 文件，并修改权限为 777。
**6）**JVM堆内存溢出（hive集群运行模式）
描述：java.lang.OutOfMemoryError: Java heap space
解决：在yarn-site.xml中加入如下代码
<property>
<name>yarn.scheduler.maximum-allocation-mb</name>
<value>2048</value>
</property>
<property>
<name>yarn.scheduler.minimum-allocation-mb</name>
<value>2048</value>
</property>
<property>
<name>yarn.nodemanager.vmem-pmem-ratio</name>
<value>2.1</value>
</property>
<property>
<name>mapred.child.java.opts</name>
<value>-Xmx1024m</value>
</property>
**7）**JVM堆内存溢出（hive本地运行模式）
描述：在启用hive本地模式后，hive.log报错java.lang.OutOfMemoryError: Java heap space
解决方案1（临时）：
在hive客户端临时设置io.sort.mb和mapreduce.task.io.sort.mb两个参数的值为10
0: jdbc:hive2://hadoop102:10000> set io.sort.mb;
+-----------------+
| set |
+-----------------+
| io.sort.mb=100 |
+-----------------+
1 row selected (0.008 seconds)
0: jdbc:hive2://hadoop102:10000> set mapreduce.task.io.sort.mb;
+--------------------------------+
| set |
+--------------------------------+
| mapreduce.task.io.sort.mb=100 |
+--------------------------------+
1 row selected (0.008 seconds)
0: jdbc:hive2://hadoop102:10000> set io.sort.mb = 10;
No rows affected (0.005 seconds)
0: jdbc:hive2://hadoop102:10000> set mapreduce.task.io.sort.mb = 10;
No rows affected (0.004 seconds)
解决方案2（永久生效）：
在$HIVE_HOME/conf下添加hive-env.sh
[atguigu@hadoop102 conf]$ pwd
/opt/module/hive/conf
[atguigu@hadoop102 conf]$ cp hive-env.sh.template hive-env.sh
然后将其中的参数 export HADOOP_HEAPSIZE=1024的注释放开，然后重启hive。
**8）**虚拟内存限制
在yarn-site.xml中添加如下配置:
<property>
<name>yarn.nodemanager.vmem-check-enabled</name>
<value>false</value>
</property>
扫一扫
1053
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
