1、数据仓库
数据仓库是一个面向主题的、集成的、相对稳定的、反映历史变化的数据集合,用于支持管理中的决策制定。
2、hive-3.1.2的安装
1、上传解压配置环境变量
# 1、解压
tar -xvf apache-hive-3.1.2-bin.tar.gz.gz
# 2、重命名
mv apache-hive-3.1.2-bin hive-3.1.2
# 3、配置环境变量
vim /etc/profile
# 4、在最后增加配置
export HIVE_HOME=/usr/local/soft/hive-3.1.2
export PATH=$PATH:$HIVE_HOME/bin
# 5、使环境变量剩下
source /etc/profile
2、修改配置文件
1、进入hive配置文件所在目录
cd /usr/local/soft/hive-3.1.2/conf
2、创建hive-site.xml配置文件
vim 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.cj.jdbc.Driver</value>
</property>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://master:3306/hive?useSSL=false&createDatabaseIfNotExist=true&characterEncoding=utf8&useUnicode=true</value>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>123456</value>
</property>
<property>
<name>hive.metastore.uris</name>
<value>thrift://master:9083</value>
</property>
<property>
<name>hive.server2.enable.doAs </name>
<value>false</value>
</property>
<property>
<name>hive.querylog.location</name>
<value/>
</property>
(同上)
<property>
<name>hive.exec.local.scratchdir</name>
<value/>
</property>
(同上)
<property>
<name>hive.downloaded.resources.dir</name>
<value/>
</property>
</configuration>
3、创建log4j.properties配置文件(hive conf目录下)
# 将日志级别改成WARN,避免执行sql出现很多日志
log4j.rootLogger=WARN,CA
log4j.appender.CA=org.apache.log4j.ConsoleAppender
log4j.appender.CA.layout=org.apache.log4j.PatternLayout
log4j.appender.CA.layout.ConversionPattern=%-4r [%t] %-5p %c %x - %m%n%
3、上传mysql驱动(hive的lib目录下)
# 将nysql驱动包上传到hive的lib目录下
mysql-connector-java-8.0.29.jar
4、初始化hive元数据库
# 2、初始化hive的元数据(表结构)到mysql中
schematool -dbType mysql -initSchema
5、启动hive元数据服务(占用一个窗口不可操作)
hive --service metastore
6、进入hive命令行
# 进入hive
hive
# 测试
# 1、创建表
CREATE EXTERNAL TABLE IF NOT EXISTS student(
id string ,
`name` string ,
age string ,
gender string ,
clazz string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
location '/data/student';
# 2、执行sql查询数据
select clazz,count(1) as num from student group by clazz;
7、解决 log4j 兼容性问题
警告信息,不影响使用
LF4J: Class path contains multiple SLF4J bindings.
cd /usr/local/soft/hive-3.1.2/lib
# hive 与 Hadoop 在运行时会出现 log4j 兼容性问题,这是因为 hive 的 log4j 版本与 Hadoop 的产生了冲突,我们这里将 hive 的 log4j 设置为备份。
mv log4j-slf4j-impl-2.10.0.jar log4j-slf4j-impl-2.10.0.jar.bak
8、hive日志
默认情况下,hive出错的日志会存放在/tmp/root/hive.log下面,其中root为使用hive的用户名称。
还有一个日志在hive/conf/hive-log4j.properties 文件中记录了Hive日志的存储情况
3、Hive基本概念
面试题:什么是hive?
1、hive是数据仓库建模的工具之一。
2、可以向hive传入一条交互式的sql,在海量数据中查询分析得到结果的平台。
Hive的特点:
1、可扩展性:Hive可以自由的扩展集群的规模,一般情况下不需要重启服务
2、延申性:Hive支持自定义函数,用户可以根据自己的需求来实现自己的函数
3、容错:即使节点出现错误,SQL仍然可以完成执行
Hive的优缺点:
优点:
1、操作接口采用类sql语法,提供快速开发的能力(简单、容易上手)
2、避免了去写MapReduce,减少开发人员的学习成本
3、Hive的延迟性比较高,因此Hive常用于数据分析,适用于对实时性要求不高的场合
4、Hive 优势在于处理大数据,对于处理小数据没有优势,因为 Hive 的执行延迟比较高。(不断地开关JVM虚拟机)
5、Hive 支持用户自定义函数,用户可以根据自己的需求来实现自己的函数。
6、集群可自由扩展并且具有良好的容错性,节点出现问题SQL仍可以完成执行
缺点:
1、Hive的HQL表达能力有限
(1)迭代式算法无法表达 (反复调用,mr之间独立,只有一个map一个reduce,反复开关)
(2)数据挖掘方面不擅长
2、Hive 的效率比较低
(1)Hive 自动生成的 MapReduce 作业,通常情况下不够智能化
(2)Hive 调优比较困难,粒度较粗 (hql根据模板转成mapreduce,不能像自己编写mapreduce一样精细,无法控制在map处理数据还是在reduce处理数据)
Hive和传统数据库对比
Hive架构
Driver(面试题:sql语句是如何转化成MR任务的?)
元数据存储在数据库中,默认存在自带的derby数据库(单用户局限性)中,推荐使用Mysql进行存储。
1) 解析器(SQL Parser):将SQL字符串转换成抽象语法树AST,这一步一般都用第三方工具库完 成,比如ANTLR;对AST进行语法分析,比如表是否存在、字段是否存在、SQL语义是否有误。
2) 编译器(Physical Plan):将AST编译生成逻辑执行计划。
3) 优化器(Query Optimizer):对逻辑执行计划进行优化。
4) 执行器(Execution):把逻辑执行计划转换成可以运行的物理计划。对于Hive来说,就是 MR/Spark。
解析 => 编译 => 优化 => 执行
4、Hive的使用
1、hive的三种交互方式
1)第一种交互方式
shell交互Hive,用命令hive启动一个hive的shell命令行,在命令行中输入sql或者命令来和Hive交互。
服务端启动metastore服务(后台启动):nohup hive --service metastore &
进入命令:hive
退出命令行:quit;
2)第二种交互方式(推荐)
Hive启动为一个服务器,对外提供服务,其他机器可以通过客户端通过协议连接到服务器,来完成访问操作,这是生产环境用法最多的
服务端启动hiveserver2服务:
nohup hive --service metastore &
nohup hiveserver2 &
需要稍等一下,启动服务需要时间:
进入命令:1)先执行: beeline ,再执行: !connect jdbc:hive2://master:10000
2)或者直接执行: beeline -u jdbc:hive2://master:10000 -n root
退出命令行:!exit
注:这里应该会显示拒绝连接错误
需要在 hadoop 的 core-site.xml 配置文件中增加
<!--该参数表示可以通过httpfs接口hdfs的ip地址限制-->
<property>
<name>hadoop.proxyuser.root.hosts</name>
<value>*</value>
</property>
<!--通过httpfs接口访问的用户获得的群组身份-->
<property>
<name>hadoop.proxyuser.root.groups</name>
<value>*</value>
</property>
并将hadoop的jline-0.9.94.jar的jar替换成hive的版本。
cp /usr/local/soft/hive-3.1.2/lib/jline-2.12.jar /usr/local/soft/hadoop-3.1.1/share/hadoop/yarn/lib/
然后重启所有服务,并等待两分钟后连接即可
3)第三种交互方式
使用 –e 参数来直接执行hql的语句
bin/hive -e "show databases;"
使用 –f 参数通过指定文本文件来执行hql的语句特点:执行完sql后,回到linux命令行。
vim hive.sql
use myhive;
select * from test;
hive -f hive.sql
2、Hive元数据
Hive元数据库中一些重要的表结构及用途,方便Impala、SparkSQL、Hive等组件访问元数据库的理解。
1、存储Hive版本的元数据表(VERSION),该表比较简单,但很重要,如果这个表出现问题,根本进不来Hive-Cli。比如该表不存在,当启动Hive-Cli的时候,就会报错“Table 'hive.version' doesn't exist”
2、Hive数据库相关的元数据表(DBS、DATABASE_PARAMS)
DBS:该表存储Hive中所有数据库的基本信息。
DATABASE_PARAMS:该表存储数据库的相关参数。
3、Hive表和视图相关的元数据表
主要有TBLS、TABLE_PARAMS、TBL_PRIVS,这三张表通过TBL_ID关联。
TBLS:该表中存储Hive表,视图,索引表的基本信息。
TABLE_PARAMS:该表存储表/视图的属性信息。
TBL_PRIVS:该表存储表/视图的授权信息。
4、Hive文件存储信息相关的元数据表
主要涉及SDS、SD_PARAMS、SERDES、SERDE_PARAMS,由于HDFS支持的文件格式很多,而建Hive表时候也可以指定各种文件格式,Hive在将HQL解析成MapReduce时候,需要知道去哪里,使用哪种格式去读写HDFS文件,而这些信息就保存在这几张表中。
SDS:该表保存文件存储的基本信息,如INPUT_FORMAT、OUTPUT_FORMAT、是否压缩等。
TBLS表中的SD_ID与该表关联,可以获取Hive表的存储信息。
SD_PARAMS: 该表存储Hive存储的属性信息。
SERDES:该表存储序列化使用的类信息。
SERDE_PARAMS:该表存储序列化的一些属性、格式信息,比如:行、列分隔符。
5、Hive表字段相关的元数据表
主要涉及COLUMNS_V2:该表存储表对应的字段信息。
最重要:
DBS: 存储数据库信息。
TBLS: 存储Hive表,视图,索引表的基本信息。
SDS:该表保存文件存储的基本信息。
3、数据库操作
1、创建数据库
同mysql;
//创建数据库和指定数据库存储位置
create database [if not exists] 数据库名 location '存储路径';
2、修改数据库
alter database 数据库名 set dbproperties('createime'='时间');
3、查看数据库信息
同MySQL
//可以通过like进行过滤
show databases like 'str'; *代表所有,_代表任意一个字符
4、删除数据库
drop database 数据库名 [cascade]; 默认只能删除空数据库,强制删除加关键字cascade
4、hive的数据类型(大小写不敏感)
基础数据类型
类型 | Java数据类型 | 描述 |
TINYINT | byte | 8位有符号整型。取值范围:-128~127。 |
SMALLINT | short | 16位有符号整型。取值范围:-32768~32767。 |
INT | int | 32位有符号整型。取值范围:-2 31 ~2 31 -1。 |
BIGINT | long | 64位有符号整型。取值范围:-2 63 +1~2 63 -1。 |
BINARY | 二进制数据类型,目前长度限制为8MB。 | |
FLOAT | float | 32位二进制浮点型。 |
DOUBLE | double | 64位二进制浮点型。 |
DECIMAL(precision,scale) | 10进制精确数字类型。precision:表示最多可以表示多少位的数字。取值范围:1 | |
VARCHAR(n) | 变长字符类型,n为长度。取值范围:1~65535。 | |
CHAR(n) | 固定长度字符类型,n为长度。最大取值255。长度不足则会填充空格,但空格不参与比较。 | |
STRING | string | 字符串类型,目前长度限制为8MB。 |
DATE | 日期类型,格式为yyyy-mm-dd。取值范围:0000-01-01~9999-12-31。 | |
DATETIME | 日期时间类型。取值范围:0000-01-01 00:00:00.000~9999-12-31 23.59:59.999,精确到毫秒。 | |
TIMESTAMP | 与时区无关的时间戳类型。取值范围:0000-01-01 00:00:00.000000000~9999-12-31 23.59:59.999999999,精确到纳秒。说明 对于部分时区相关的函数,例如cast( as string),要求TIMESTAMP按照与当前时区相符的方式来展现。 | |
BOOLEAN | boolean | BOOLEAN类型。取值:True、False。 |
复杂的数据类型
类型 | 定义方法 | 构造方法 |
ARRAY | array``array> | array(1, 2, 3)``array(array(1, 2), array(3, 4)) |
MAP | map``map> | map(“k1”, “v1”, “k2”, “v2”)``map(1S, array(‘a’, ‘b’), 2S, array(‘x’, ‘y’)) |
STRUCT | structstruct, field3:map> named_struct(‘x’, 1, ‘y’, 2)named_struct(‘field1’, 100L, ‘field2’, array(1, 2), ‘field3’, map(1, 100, 2, 200)) |
注:常用 bigInt, decimal(总位数,小数位数), string, timeStamp, boolean。
如果文件数据类型和hive表中的类型不同,会隐式转换(不保证成功)不成功返回值为 null
5、hive表操作
注:因为hive是为了进行数据分析,所以部分增删改操作不支持,不要深究!
1、hive的存储格式
TextFile: 正常的文本格式,是Hive默认文件存储格式
RCFile: 第一个列文件格式。能够很好的压缩和快速的查询性能
ORCFile: 是一种列式文件存储格式,有着很高的压缩比
Parquet: 支持嵌套结构
SequenceFile: 一种二进制文件
Avro: 一种用于支持数据密集型的二进制文件格式
结论:
ORCFILE存储文件读操作效率最高
耗时比较:ORC<Parquet<RC<Text
ORCFILE存储文件占用空间少,压缩效率高
占用空间:ORC<Parquet<RC<Text
注:一般就用TextFile或ORCFile。
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 [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
字段解释说明:
- CREATE TABLE
创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXISTS 选项来忽略这个异常。
- EXTERNAL
关键字可以让用户创建一个外部表,在建表的同时指定一个指向实际数据的路径(LOCATION)
创建内部表时,会将数据移动到数据仓库指向的路径(默认位置);
创建外部表时,仅记录数据所在的路径,不对数据的位置做任何改变。在
删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据。
- COMMENT:
为表和列添加注释。
- PARTITIONED BY
创建分区表
- CLUSTERED BY
创建分桶表
- SORTED BY
不常用
- ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
用户在建表的时候可以自定义SerDe或者使用自带的SerDe。
如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED,将会使用自带的SerDe。
在建表的时候,用户还需要为表指定列,用户在指定表的列的同时也会指定自定义的SerDe,Hive通过SerDe确定表的具体的列的数据。
SerDe是Serialize/Deserilize的简称,目的是用于序列化和反序列化。
- STORED AS指定存储文件类型
常用的存储文件类型:SEQUENCEFILE(二进制序列文件)、TEXTFILE(文本)、RCFILE(列式存储格式文件)
如果文件数据是纯文本,可以使用STORED AS TEXTFILE。
如果数据需要压缩,使用 STORED AS SEQUENCEFILE。
- LOCATION :
指定表在HDFS上的存储位置。
- LIKE
允许用户复制现有的表结构,但是不复制数据。
常用建表语句
1、基本数据类型
create table 表名(
字段名 数据类型 comment '注释',
......
)comment '表注释'
row format delimited
fields terminated by '分隔符' #必须指定,默认是\001
lines terminated by '行分隔符' #默认为\n
stored as 存储格式 #默认TextFile
location '存储路径'; #默认/user/hive/warehouse
2、复杂数据类型
create table 表名(
字段名1 array<数据类型> comment '注释',
字段名2 map<key数据类型, value数据类型>,
字段名3 struct<成员变量1:数据类型, 成员变量2:数据类型...>
......
)
row format delimited
fields terminated by '分隔符'
collection items terminated by '分隔符' #list元素和struct元素之间的分隔符
map keys terminated by '分隔符' #map key和value之间的分隔符
lines terminated by '列分隔符'
stored as 存储格式 #默认TextFile
location '存储路径'; #默认/user/hive/warehouse
3、查看表信息
show tables; #查看所有表
show tables like 'u*'; #like查看表
desc t_person; #查看表字段类型
desc formatted t_person; #查看表详细信息
4、加载数据
1、直接把数据上传到表存储数据的目录下
2、使用 load data inpath 命令
1、数据在hadoop上
load data inpath '文件路径名' into table 表名;
2、数据在Linux上
load data local inpath '文件路径名' into table 表名;
3、覆盖加载数据overwrite(默认为追加)
load data inpath '文件路径名' overwrite into table 表名;
3、创建表时写入其他表数据
create table 表名 as sql语句;
4、插入别的表数据
insert into table 表名 sql语句; #没有as关键字
5、修改表(错了推荐直接删除表重建)
//添加字段add
alter table 表名 add columns (列名 数据类型);
//修改字段
alter table 表名 change 旧列名 新列名 数据类型;
//重命名
alter table 表名 rename to 新表名
//修改表存储位置和文件存储格式
alter table 表名 set location '路径名';
alter table 表名 set fileformat 存储格式;
6、删除表(drop table 表名)
7、内部表和外部表
面试题:内部表和外部表的区别?如何创建外部表?工作中使用外部表
内部表在删表时会连同Hadoop上的数据一并删除,外部表不会。建表时,默认创建内部表;加上 external 关键字即可创建外部表。
8、导出数据
1、导出查询结果数据到Linux
insert overwrite local directory '导出目标目录路径' sql语句;
2、按照指定的格式导出查询结果
insert overwrite local directory '导出目标目录路径'
row format delimited
fields terminated by '分隔符'
collection items terminated by '分隔符'
map keys terminated by '分隔符'
lines terminated by '分隔符'
sql语句;
3、将查询结果导出到Hadoop HDFS中
把 local 关键字去掉(待确认)
4、直接把表数据存放的文件拷贝出来
5、将表结构和表数据同时备份
将数据导出到HDFS
//创建存放数据的目录
hdfs dfs -mkdir -p /shujia/bigdata17/copy
//导出查询结果的数据
export table 表名 to '导出路径';
export table t_person to '/shujia/bigdata17/copy';
恢复表结构和数据
import from '/shujia/bigdata17';
注意:时间不同步,会导致导入导出失败
5、IDEA操作hive
1、导入依赖
<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc -->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>3.1.2</version>
</dependency>
2、其余同mysql
1、注册 HiveDriver 驱动
2、创建连接对象
3、创建查询对象
4、执行sql语句
5、处理返回结果
6、关闭资源
public static void main(String[] args) throws Exception {
//1、注册 HiveDriver 驱动
Class.forName("org.apache.hive.jdbc.HiveDriver");
//2、创建连接对象
Connection conn = DriverManager.getConnection("jdbc:hive2://master:10000/shujia");
//3、创建查询对象
Statement stat = conn.createStatement();
//4、执行sql语句
ResultSet rs = stat.executeQuery("select * from students2 limit 10");
//5、处理返回结果
while (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
int age = rs.getInt(3);
String gender = rs.getString(4);
String clazz = rs.getString(5);
System.out.println(id + "," + name + "," + age + "," + gender + "," + clazz);
}
//6、关闭资源
rs.close();
stat.close();
conn.close();
}
6、常用函数
大部分同mysql
分类(三类)
-- UDF 进一出一
-- UDAF 进多出一
-- collect_set()和collect_list()都是对多列转成一行,区别就是list里面可重复而set里面是去重的
-- concat_ws(':',collect_set(type)) ':' 表示你合并后用什么分隔,collect_set(stage)表示要合并表中的那一列数据
select 字段名,concat_ws(':',collect_set(列名)) as 别名 from 表名 group by id;
-- UDTF 进一出多
-- explode 可以将一组数组的数据变成一列表
select explode(split(列名,"数据的分隔符")) from 表名;
-- lateral view 表生成函数,可以将explode的数据生成一个列表
select id,name,列名 from 表1,lateral view explode(split(表1.列名,"数据的分隔符"))新列名 as 别列名;
常用函数
#正则匹配:regexp
select * from 表名 where 字段 regexp '正则表达式';
数学函数:
#非空查找函数:返回第一个不为null的参数
select coalesce(参数1,参数2,参数3......);
#四舍五入:round
select round(数值,保留的小数位);
select round(3.5555555,3);返回3.556
#随机数:rand
select rand();
#幂运算: pow
select pow(2,5);返回2的5次方
#平方根函数:sqrt
select sqrt(100);返回10
#绝对值: abs
select abs(-1);返回1
时间函数
#获取当前时间:
current_date();current_timestamp();
#时间戳转日期:from_unixtime
select from_unixtime(时间戳,'转换格式');
select from_unixtime(12999000,'yyyy-MM-dd HH-mm-ss');
#获取当前时间戳:
日期转时间戳:unix_timestamp
select unix_timestamp();-- 获取当前时间戳 注:方法要加小括号
select unix_timestamp('标准格式的时间');-- 日期转时间戳
#日期相减:datadiff
datadiff(string enddata,string startdata);前减后。标准格式的字符串
#字符串转日期:date_format
date_format(str,'yyyy-MM-dd HH-mm-ss');
#带分隔符连接:concat_ws
select concat_ws(',',a,b,c....);返回a,b,c....
#替换
regexp_replace(str,str_old,str_new);将str中的str_old替换为str_new
#获取复杂类型数据长度:size
size(map/array);
#类型转换: cast
cast(数据 as 新类型); 将数据转换为新数据类型
#字符串还可以用 || 拼接
select 'a' || 'b'; 返回'ab'
explode函数: 扁平化
对 array 或 map 元素进行扁平化
侧视图 lateral view。配合explode和UDTF使用,帮助连接生成表和其他字段(看作join)
select 字段 from 表名 lateral view explode(字段) 表别名 as 列别名;
#后面还可以分组排序等
collect_set()和collect_list():都是对列转成行,区别就是list里面可重复而set里面是去重的
collect_set(字段)
collect_list(字段):把字段保存到一个数组中
7、开窗函数
1、聚合开窗函数
sum(求和)
min(最小)
max(最大)
avg(平均值)
count(计数)
lag(获取当前行上一行的数据)
示例:
--
select name,subject,score,sum(score) over() as sumover from t_fraction;
+-------+----------+--------+----------+
| name | subject | score | sumover |
+-------+----------+--------+----------+
| 唐玄奘 | 英语 | 23 | 321 |
| 唐玄奘 | 数学 | 81 | 321 |
| 唐玄奘 | 语文 | 21 | 321 |
| 沙悟净 | 英语 | 31 | 321 |
| 沙悟净 | 数学 | 12 | 321 |
| 沙悟净 | 语文 | 22 | 321 |
| 猪八戒 | 英语 | 11 | 321 |
| 猪八戒 | 数学 | 73 | 321 |
| 猪八戒 | 语文 | 10 | 321 |
| 孙悟空 | 英语 | 15 | 321 |
| 孙悟空 | 数学 | 12 | 321 |
| 孙悟空 | 语文 | 10 | 321 |
+-------+----------+--------+----------+
select name,subject,score,
sum(score) over() as sum1,
sum(score) over(partition by subject) as sum2,
sum(score) over(partition by subject order by score) as sum3,
-- 由起点到当前行的窗口聚合,和sum3一样
sum(score) over(partition by subject order by score rows between unbounded preceding and current row) as sum4,
-- 当前行和前面一行的窗口聚合
sum(score) over(partition by subject order by score rows between 1 preceding and current row) as sum5,
-- 当前行的前面一行到后面一行的窗口聚合 前一行+当前行+后一行
sum(score) over(partition by subject order by score rows between 1 preceding and 1 following) as sum6,
-- 当前行与后一行之和
sum(score) over(partition by subject order by score rows between current row and 1 following) as sum6,
-- 当前和后面所有的行
sum(score) over(partition by subject order by score rows between current row and unbounded following) as sum7
from t_fraction;
rows:行
unbounded preceding:起点
unbounded following:终点
n preceding:前 n 行
n following:后 n 行
current row:当前行
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+
| name | subject | score | sum1 | sum2 | sum3 | sum4 | sum5 | sum6 | sum7 |
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+
| 孙悟空 | 数学 | 12 | 359 | 185 | 12 | 12 | 12 | 31 | 185 |
| 沙悟净 | 数学 | 19 | 359 | 185 | 31 | 31 | 31 | 104 | 173 |
| 猪八戒 | 数学 | 73 | 359 | 185 | 104 | 104 | 92 | 173 | 154 |
| 唐玄奘 | 数学 | 81 | 359 | 185 | 185 | 185 | 154 | 154 | 81 |
| 猪八戒 | 英语 | 11 | 359 | 80 | 11 | 11 | 11 | 26 | 80 |
| 孙悟空 | 英语 | 15 | 359 | 80 | 26 | 26 | 26 | 49 | 69 |
| 唐玄奘 | 英语 | 23 | 359 | 80 | 49 | 49 | 38 | 69 | 54 |
| 沙悟净 | 英语 | 31 | 359 | 80 | 80 | 80 | 54 | 54 | 31 |
| 孙悟空 | 语文 | 10 | 359 | 94 | 10 | 10 | 10 | 31 | 94 |
| 唐玄奘 | 语文 | 21 | 359 | 94 | 31 | 31 | 31 | 53 | 84 |
| 沙悟净 | 语文 | 22 | 359 | 94 | 53 | 53 | 43 | 84 | 63 |
| 猪八戒 | 语文 | 41 | 359 | 94 | 94 | 94 | 63 | 63 | 41 |
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+
2、排序开窗函数(重点)
面试题:你知道开窗函数吗?请说一说有哪些排序开窗函数?RANK() ,DENSE_RANK() ,ROW_NUMBER()有什么区别?
- RANK() 排序相同时会重复,总数不会变
- DENSE_RANK() 排序相同时会重复,总数会减少
- ROW_NUMBER() 会根据顺序计算
- PERCENT_RANK()计算给定行的百分比排名。可以用来计算超过了百分之多少的人(当前行的rank值-1)/(分组内的总行数-1)
8、自定义函数
1、UDF函数
1、导入依赖
<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc -->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>3.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-exec -->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>3.1.2</version>
</dependency>
<!-- 需要带依赖打包 -->
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-assembly-plugin</artifactId>
<version>3.3.0</version>
<configuration>
<descriptorRefs>
<!-- 打包出来的带依赖jar包名称 -->
<descriptorRef>jar-with-dependencies</descriptorRef>
</descriptorRefs>
</configuration>
<!--下面是为了使用 mvn package命令,如果不加则使用mvn assembly-->
<executions>
<execution>
<id>make-assemble</id>
<phase>package</phase>
<goals>
<goal>single</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
2、实现方式(两种)
方法一:自定义一个类,继承 UDF;重写 evaluate 方法。
不需要写 main 方法。示例:
public class MyUDFDemo1 extends UDF {
//传进去一个字符串类型的值,在值的后面拼接一个$符号
public String evaluate(String obj){ //参数为处理的数据
return obj+"$"; //通过return返回结果
}
//需求:1000以下的 +500;1000~2000 +1000;2000以上 +1500
public int evaluate(int sal){ //参数为处理的数据
//通过return返回结果
if(sal<=1000){
return sal+500;
}else if(sal<=2000){
return sal+1000;
}else {
return sal+1500;
}
}
}
方法二:重写一个类继承 GenericUDF 方法,并实现 initialize,evaluate,getDisplayString 方法
public class MyUDFDemo3 extends GenericUDF {
String output = "";
/*
initialize方法做的是初始化,主要是设置输出的类型
通过 PrimitiveObjectInspectorFactory 静态方法去获取返回的数据类型
*/
@Override
public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
output = "";
return PrimitiveObjectInspectorFactory.javaStringObjectInspector;
}
/*
evaluate: 编写主要逻辑
*/
@Override
public Object evaluate(DeferredObject[] arguments) throws HiveException {
//DeferredObject[] arguments 会保存传入的数据,保存在数组中。
if(arguments.length==1){
//因为这是UDF函数,所以一般只有arguments[0]才有数据。并通过get方法获取数据
String input = arguments[0].get().toString();
output = "数加: "+input+" 666";
}
return output;
}
//写方法用法和注释的
@Override
public String getDisplayString(String[] children) {
return "这是自己编写的自定义函数UDF";
}
}
3、添加 jar 包到 hive 环境中去
1、临时生效
- 打成jar包并上传至Linux虚拟机
- 在 hive 中,使用 add jar 路径将 jar 包作为资源添加到 hive 环境中
add jar /usr/local/soft/bigdata19/hive-bigdata19-1.0-SNAPSHOT.jar;
- 使用 jar 包资源注册一个临时函数,fxxx1是你的函数名,'MyUDF'是主类名(全路径名)
create temporary function fxxx1 as 'com.xxx.MyUDF';
- 使用函数名处理数据
select fxxx1(字段) from 表名;
检查函数是否创建成功
show functions;
测试功能
select toUp('abcdef');
删除函数
drop temporary function if exists 函数名;
2、永久生效
将jar上传HDFS:
hadoop fs -put hadoop-mapreduce-1.0-SNAPSHOT.jar /jar/
在hive命令行中创建永久函数:
create function 函数名 as '主类全路径名' using jar 'hdfs:HDFS上jar包的路径';
示例:
create function bfy_fun as 'com.shujia.udfdemo.HiveTest' using jar 'hdfs:/shujia/bigdata19/jar/hive-udf.jar';
退出hive,再进入,执行测试:
删除永久函数,并检查:
drop function 函数名;
2、UDTF
1、导入依赖
同上
2、实现方法
重写一个类继承 GenericUDTF 方法,实现 initialize,process,close 三个方法。
public class MyUDTFDemo1 extends GenericUDTF {
//指定输出的列的个数名字。以及类型(固定写法)
@Override
public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
//M1001#xiaohu#S324231212,ltb#M1002#S2543412432,S21312312412#M1003#kzw
//1001 xiaohu 324231212
//创建一个List集合存储列的名字
ArrayList<String> colNames = new ArrayList<>();
//创建一个集合存储每一列对象的类型,将每种类型封装成对象
ArrayList<ObjectInspector> colTypes = new ArrayList<>();
//将列的名字添加到集合以及列的数据类型添加到集合
colNames.add("id");
colTypes.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
colNames.add("name");
colTypes.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
colNames.add("cardId");
colTypes.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
//将列名和列的数据类型组合一起
return ObjectInspectorFactory.getStandardStructObjectInspector(colNames,colTypes);
}
//在这里就编写实际的处理逻辑,传进来的每一列数据都会被封装成一个数组,如果只有一列数据,就去索引0就好了
@Override
public void process(Object[] args) throws HiveException {
//M1001#xiaohu#S324231212,ltb#M1002#S2543412432,S21312312412#M1003#kzw
//1001 xiaohu 324231212
String line = String.valueOf(args[0]);
//按照逗号分割得到每一个用户信息
String[] userInfos = line.split(",");
//动态初始化一个数组
String[] strings = new String[3];
//外层for循环得到的是每一个学生
for (String userInfo : userInfos) {
//再根据#分割得到学号,姓名,身份证号
String[] infos = userInfo.split("#");
for (String info : infos) {
if(info.startsWith("M")){
//如果是以M开头,就是学号的信息,将其信息赋值给数组中的第一个元素
strings[0] = info.substring(1);
}else if(info.startsWith("S")){
//如果是以S开头,就是身份证号的信息,将其信息赋值给数组中的第三个元素
strings[2] = info.substring(1);
}else {
//如果既不是S开头也不是M开头,表示是姓名,将其信息赋值给数组中的第二个元素
strings[1] = info;
}
}
//我们通过观察发现,使用内部提供的forward方法将处理的每一行写出
//将我们的每一行封装成一个数组进行返回
forward(strings);
}
}
//一般用于关闭资源,没有资源需要关闭可以不写
@Override
public void close() throws HiveException {}
}
3、添加 jar 包到 hive 环境中去
同上
3、UTAF
//TODO待补充
9、hive分区
1、Hive分区
在大数据中,最常见的一种思想就是分治,我们可以把大的文件切割划分成一个个的小的文件,这样每次操作一个个小的文件就会很容易了,同样的道理,在hive当中也是支持这种思想的,就是我们可以把大的数据,按照每天或者每小时切分成一个个小的文件,这样去操作小的文件就会容易很多了。
假如现在我们公司一天产生3亿的数据量,那么为了方便管理和查询,就做以下的事情。
1)建立分区(可按照日期,部门等等具体业务分区)
2)分门别类的管理
1.2 静态分区(SP)
静态分区(SP)static partition–partition by (字段 类型)(手动地指定分区文件夹的名字
借助于物理的文件夹分区,实现快速检索的目的。
一般对于查询比较频繁的列设置为分区列。
分区查询的时候直接把对应分区中所有数据放到对应的文件夹中。
创建单分区表语法:
CREATE TABLE IF NOT EXISTS t_student (
sno int,
sname string
) partitioned by(grade int) -- 分区字段不需要写在表字段中,单独写出来
row format delimited fields terminated by ',';
-- 分区的字段不要和表的字段相同。相同会报错error10035
1,xiaohu01,1
2,xiaohu02,1
3,xiaohu03,1
4,xiaohu04,1
5,xiaohu05,1
6,xiaohu06,2
7,xiaohu07,2
8,xiaohu08,2
9,xiaohu09,3
10,xiaohu10,3
11,xiaohu11,3
12,xiaohu12,3
13,xiaohu13,3
14,xiaohu14,3
15,xiaohu15,3
16,xiaohu16,4
17,xiaohu17,4
18,xiaohu18,4
19,xiaohu19,4
20,xiaohu20,4
21,xiaohu21,4
-- 载入数据。将相应年级一次导入
-- 静态分区需要用load data导入数据,最后加上分区值,会自动创建相应文件夹。如:partition(grade=1)
load data local inpath '/usr/local/soft/bigdata19/hivedata/student_1.txt' into table t_student partition(grade=1);
静态多分区表语法:
CREATE TABLE IF NOT EXISTS t_teacher (
tno int,
tname string
) partitioned by(grade int,clazz int) -- 注意字段顺序,先表中数据,然后分区字段
row format delimited fields terminated by ',';
--注意:前后两个分区的关系为父子关系,也就是grade文件夹下面有多个clazz子文件夹。
1,xiaoge01,1,1
2,xiaoge02,1,1
3,xiaoge03,1,2
4,xiaoge04,1,2
5,xiaoge05,1,3
6,xiaoge06,1,3
7,xiaoge07,2,1
8,xiaoge08,2,1
9,xiaoge09,2,2
--载入数据
load data local inpath '/usr/local/soft/bigdata25/teacher11.txt' into table t_teacher partition(grade=1,clazz=1);
分区表查询
select * from t_student where grade = 1;
// 全表扫描,不推荐,效率低
select count(*) from students_pt1;
// 使用where条件进行分区裁剪,避免了全表扫描,效率高
select count(*) from students_pt1 where grade = 1;
// 也可以在where条件中使用非等值判断
select count(*) from students_pt1 where grade<3 1 and grade>=1;
查看分区
show partitions t_student;
添加分区
alter table t_student add partition (grade=5);
alter table t_student add partition (grade=5) location '指定数据文件的路径';
删除分区
alter table t_student drop partition (grade=5);
1.3 动态分区(DP)
- 动态分区(DP)dynamic partition
- 静态分区与动态分区的
主要区别在于静态分区是手动指定,而动态分区是通过数据来进行判断。
- 详细来说,静态分区的列是在编译时期通过用户传递来决定的;
动态分区只有在SQL执行时才能决定。
开启动态分区首先要在hive会话中设置如下的参数
# 表示开启动态分区(hive2.3以后默认开启)
hive> set hive.exec.dynamic.partition=true;
# 表示动态分区模式:strict(需要配合静态分区一起使用)、nostrict
# strict: insert into table students_pt partition(dt='anhui',pt) select ......,pt from students;
hive> set hive.exec.dynamic.partition.mode=nonstrict;
===================以下是可选参数======================
# 表示支持的最大的分区数量为1000,可以根据业务自己调整
hive> set hive.exec.max.dynamic.partitions.pernode=1000;
其余的参数详细配置如下
设置为true表示开启动态分区的功能(2.3以前默认为false)
--hive.exec.dynamic.partition=true;
设置为nonstrict,表示允许所有分区都是动态的(默认为strict)
-- hive.exec.dynamic.partition.mode=nonstrict;
每个mapper或reducer可以创建的最大动态分区个数(默认为100)
比如:源数据中包含了一年的数据,即day字段有365个值,那么该参数就需要设置成大于365,如果使用默认值100,则会报错
--hive.exec.max.dynamic.partition.pernode=100;
一个动态分区创建可以创建的最大动态分区个数(默认值1000)
--hive.exec.max.dynamic.partitions=1000;
全局可以创建的最大文件个数(默认值100000)
--hive.exec.max.created.files=100000;
当有空分区产生时,是否抛出异常(默认false)
-- hive.error.on.empty.partition=false;
- 案例1: 动态插入学生年级班级信息
--创建分区表
CREATE TABLE IF NOT EXISTS t_student_d (
sno int,
sname string
) partitioned by (grade int,clazz int)
row format delimited fields terminated by ',';
-- 创建外部表
-- 注:动态分区必须借助一个辅助表,利用insert table命令将外部表查询结果添加进表中
CREATE EXTERNAL TABLE IF NOT EXISTS t_student_e (
sno int,
sname string,
grade int,
clazz int
)
row format delimited fields terminated by ',';
数据:
1,xiaohu01,1,1
2,xiaohu02,1,1
3,xiaohu03,1,1
4,xiaohu04,1,2
5,xiaohu05,1,2
6,xiaohu06,2,3
7,xiaohu07,2,3
8,xiaohu08,2,3
9,xiaohu09,3,3
10,xiaohu10,3,3
11,xiaohu11,3,3
12,xiaohu12,3,4
13,xiaohu13,3,4
14,xiaohu14,3,4
15,xiaohu15,3,4
16,xiaohu16,4,4
17,xiaohu17,4,4
18,xiaohu18,4,5
19,xiaohu19,4,5
20,xiaohu20,4,5
21,xiaohu21,4,5
如果静态分区的话,我们插入数据必须指定分区的值。如果想要插入多个班级的数据,我要写很多SQL并且执行24次很麻烦。而且静态分区有可能会产生数据错误问题
-- 会报错
insert overwrite table t_student_d partition (grade=1) select * from t_student_e where grade=1;
如果使用动态分区,动态分区会根据select的结果自动判断数据应该load到哪儿分区去。
-- 利用insert overwrite table语句,并且要指定分区字段
insert overwrite table t_student_d partition (grade,clazz) select * from t_student_e;
优点:不用手动指定了,自动会对数据进行分区缺点:可能会出现数据倾斜
2、Hive分桶
2.1 业务场景
分区和分桶的区别?
数据分桶的适用场景:
分区提供了一个隔离数据和优化查询的便利方式,不过并非所有的数据都可形成合理的分区,尤其是需要确定合适大小的分区划分方式
不合理的数据分区划分方式可能导致有的分区数据过多,而某些分区没有什么数据的尴尬情况
分桶是将数据集分解为更容易管理的若干部分的另一种技术。
分桶就是将数据按照字段进行划分,可以将数据按照字段划分到多个文件当中去。
2.2 数据分桶原理
- Hive采用对列值哈希,然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
- bucket num = hash_function(bucketing_column) mod num_buckets
- 列的值做哈希取余 决定数据应该存储到哪个桶
2.3 数据分桶优势
方便抽样
使取样(sampling)更高效。在处理大规模数据集时,在开发和修改查询的阶段,如果能在数据集的一小部分数据上试运行查询,会带来很多方便
提高join查询效率
获得更高的查询处理效率。桶为表加上了额外的结构,Hive 在处理有些查询时能利用这个结构。具体而言,连接两个在(包含连接列的)相同列上划分了桶的表,可以使用 Map 端连接 (Map-side join)高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列,如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以,可以大大较少JOIN的数据量。
2.4 分桶实战
首先,分区和分桶是两个不同的概念,很多资料上说需要先分区在分桶,其实不然,分区是对数据进行划分,而分桶是对文件进行划分。
当我们的分区之后,最后的文件还是很大怎么办,就引入了分桶的概念。将这个比较大的文件再分成若干个小文件进行存储,我们再去查询的时候,在这个小范围的文件中查询就会快很多。
对于hive中的每一张表、分区都可以进一步的进行分桶。
当然,分桶不是说将文件随机进行切分存储,而是有规律的进行存储。在看完下面的例子后进行解释,现在干巴巴的解释也不太好理解。它是由列的哈希值除以桶的个数来决定每条数据划分在哪个桶中。
创建顺序和分区一样,创建的方式不一样。
首先我们需要开启分桶的支持
--(依然十分重要,不然无法进行分桶操作!!!!)
set hive.enforce.bucketing=true;
数据准备(id,name,age)
1,tom,11
2,cat,22
3,dog,33
4,hive,44
5,hbase,55
6,mr,66
7,alice,77
8,scala,88
创建一个普通的表(注:和动态分区一样,把辅助表的查询结果添加到表中)
create table psn31
(
id int,
name string,
age int
)
row format delimited
fields terminated by ',';
将数据load到这张表中
load data local inpath '文件在Linux上的绝对路径' into table psn31;
创建分桶表
create table psn_bucket
(
id int,
name string,
age int
)
clustered by(age) into 4 buckets
row format delimited
fields terminated by ',';
将数据insert到表psn_bucket中(注意:这里和分区表插入数据有所区别,分区表需要select 和指定分区,而分桶则不需要)
-- 分桶使用insert into命令
insert into psn_bucket select * from psn31;
在HDFS上查看数据
查询数据我们在linux中使用Hadoop的命令查看一下(与我们猜想的顺序一致)
hadoop fs -cat /user/hive/warehouse/bigdata17.db/psn_bucket/*
这里设置的桶的个数是4 数据按照 年龄%4 进行放桶(文件)
11%4 == 3 -----> 000003_0
22%4 == 2 -----> 000002_0
33%4 == 1 -----> 000001_0
44%4 == 0 -----> 000000_0
...以此类推
在Hive进行查询
-- tablesample是抽样语句,语法:TABLESAMPLE(BUCKET x OUT OF y)
-- 分桶语句中的分母表示的是数据将会被散列的桶的个数,分子表示将会选择的桶的个数。
-- x表示从哪个bucket开始抽取。
-- 例如,table总bucket数为32,tablesample(bucket 2 out of 2)
-- 表示总共抽取(2/2=)1个bucket的数据,分别为第2个bucket和第(2+2=)4个bucket的数据
-- y必须是table总bucket数的倍数或者因子。hive根据y的大小,决定抽样的比例。
-- 例如,table总共分了4份,当y=2时,抽取(4/2=)2个bucket的数据,当y=8时,抽取(4/8=)1/2个bucket的数据
select * from psn_bucket tablesample(bucket 2 out of 2);
select * from psn_bucket tablesample(bucket 3 out of 2);
随机取值(设置因子,桶的个数/因子)
这里就是取2号桶和4号桶,取2个
select * from psn_bucket tablesample(bucket 2 out of 4);
随机取值(设置因子,桶的个数/因子)
这里就是取2号桶,取一个
select * from psn_bucket tablesample(bucket 2 out of 8);
随机取值(设置倍数,倍数/桶的个数)
这里就是取2号桶 1/2个数据
取出来是一条数据
3、Hive JDBC
启动hiveserver2
hive --service hiveserver2 &
或者
hiveserver2 &
新建maven项目并添加两个依赖
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
<version>2.7.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc -->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>1.2.1</version>
</dependency>
编写JDBC代码
import java.sql.*;
public class HiveJDBC {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("org.apache.hive.jdbc.HiveDriver");
Connection conn = DriverManager.getConnection("jdbc:hive2://master:10000/bigdata17");
Statement stat = conn.createStatement();
ResultSet rs = stat.executeQuery("select * from students limit 10");
while (rs.next()) {
int id = rs.getInt(1);
String name = rs.getString(2);
int age = rs.getInt(3);
String gender = rs.getString(4);
String clazz = rs.getString(5);
System.out.println(id + "," + name + "," + age + "," + gender + "," + clazz);
}
rs.close();
stat.close();
conn.close();
}
}
4、Hive查询语法(DQL)
大部分同mysql
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT [offset,] rows]
4.1 全局排序
- order by 会对输入做全局排序,因此只有一个reducer
,会导致当输入规模较大时,需要较长的计算时间
- 使用 order by子句排序 :ASC(ascend)升序(默认)| DESC(descend)降序
- order by放在select语句的结尾
select * from 表名 order by 字段名1[,别名2...];
4.2 局部排序(对reduce内部做排序)
- sort by 不是全局排序,其在数据进入reducer前完成排序
- 如果用sort by进行排序,并且设置mapred.reduce.tasks>1,则sort by 只保证每个reducer的输出有序,
不保证全局有序。asc,desc
- 设置reduce个数
set mapreduce.job.reduces=3;
set mapred.reduce.tasks=3;
- 查看reduce个数
set mapreduce.job.reduce;
- 排序
select * from 表名 sort by 字段名[,字段名...];
4.3 分区排序
distribute by(字段)根据指定的字段将数据分到不同的reducer,且分发算法是hash散列。类似MR中partition,进行分区,结合sort by使用。(注意:distribute by 要在sort by之前)对于distrbute by 进行测试,一定要多分配reduce进行处理,否则无法看到distribute by的效果。设置reduce个数
set mapreduce.job.reduce=7;
- 排序
select * from 表名 distribute by 字段名[,字段名...];
4.4 分区并排序
- cluster by(字段)除了具有Distribute by的功能外,还会对该字段进行排序
- cluster by = distribute by + sort by 只能默认升序,不能使用倒序
select * from 表名 sort cluster by 字段名[,字段名...];
select * from 表名 distribute by 字段名[,字段名...] sort by 字段名[,字段名...];
5、Hive内置函数
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
-- 1.查看系统自带函数
show functions;
-- 2.显示自带的函数的用法
desc function upper;
-- 3.详细显示自带的函数的用法
desc function extended upper;
5.1 内置函数分类
关系操作符:包括 = 、 <> 、 <= 、>=等
算数操作符:包括 + 、 - 、 *、/等
逻辑操作符:包括AND 、 && 、 OR 、 || 等
复杂类型构造函数:包括map、struct、create_union等
复杂类型操作符:包括A[n]、Map[key]、S.x
数学操作符:包括ln(double a)、sqrt(double a)等
集合操作符:包括size(Array)、sort_array(Array)等
类型转换函数: binary(string|binary)、cast(expr as )
日期函数:包括from_unixtime(bigint unixtime[, string format])、unix_timestamp()等
条件函数:包括if(boolean testCondition, T valueTrue, T valueFalseOrNull)等
字符串函数:包括acat(string|binary A, string|binary B…)等
其他:xpath、get_json_objectscii(string str)、con
5.2 UDTF hive中特殊的一个功能(进一出多)
-- UDF 进一出一
-- UDAF 进多出一
-- collect_set()和collect_list()都是对多列转成一行,区别就是list里面可重复而set里面是去重的
-- concat_ws(':',collect_set(type)) ':' 表示你合并后用什么分隔,collect_set(stage)表示要合并表中的那一列数据
select 字段名,concat_ws(':',collect_set(列名)) as 别名 from 表名 group by id;
-- UDTF 进一出多
-- explode 可以将一组数组的数据变成一列表
select explode(split(列名,"数据的分隔符")) from 表名;
-- lateral view 表生成函数,可以将explode的数据生成一个列表
select id,name,列名 from 表1,lateral view explode(split(表1.列名,"数据的分隔符"))新列名 as 别列名;
UDTF 进一出多
-- 创建数据库表
create table t_movie1(
id int,
name string,
types string
)
row format delimited fields terminated by ','
lines terminated by '\n';
-- 电影数据 movie1.txt
-- 加载数据到数据库 load data inpath '/shujia/movie1.txt' into table t_movie1;
1,余罪,剧情-动作-犯罪
2,木乃伊,动作-冒险-剧情
3,勇敢的心,动作-传记-剧情-历史-战争
4,大话西游,剧情-动作-爱情-武侠-古装
5,霍比特人,动作-奇幻-冒险
-- explode 可以将一组数组的数据变成一列表
select explode(split(types,"-")) from t_movie1;
-- lateral view 表生成函数,可以将explode的数据生成一个列表
select id,name,type from t_movie1 lateral view explode(split(types,"-")) typetable as type;
-- 创建数据库表
create table t_movie2(
id int,
name string,
type string
)
row format delimited fields terminated by ','
lines terminated by '\n';
-- 电影数据 movie2.txt
-- 加载数据到数据库 load data inpath '/shujia/movie2.txt' into table t_movie2;
1,余罪,剧情
1,余罪,动作
1,余罪,犯罪
2,木乃伊,动作
2,木乃伊,冒险
2,木乃伊,剧情
3,勇敢的心,动作
3,勇敢的心,传记
3,勇敢的心,剧情
3,勇敢的心,历史
3,勇敢的心,战争
4,大话西游,剧情
4,大话西游,动作
4,大话西游,爱情
4,大话西游,武侠
4,大话西游,古装
5,霍比特人,动作
5,霍比特人,奇幻
5,霍比特人,冒险
1,余罪,剧情
1,余罪,动作
1,余罪,犯罪
id,name,["剧情","动作","犯罪"]
1,余罪,"剧情-动作-犯罪"
-- collect_set()和collect_list()都是对列转成行,区别就是list里面可重复而set里面是去重的
-- concat_ws(':',collect_set(type)) 扁平化复原; ':' 表示你合并后用什么分隔,collect_set(stage)表示要合并表中的那一列数据
select id,name,concat_ws('-',collect_set(type)) as types from t_movie2 group by id,name;
5.3 WordCount案例
数据准备
hello,world
hello,bigdata,spark
like,life
bigdata,good,hive,spark
建表
create table wc
(
line string
)
row format delimited fields terminated by ','
导入数据
load data local inpath '/usr/local/soft/data/wc1.txt' into table wc;
步骤1:先对一行数据进行切分
select split(line,',') from wc;
步骤2:将行转列
select explode(split(line,',')) from wc;
步骤3:将相同的进行分组统计
select w.word,count(*) from (select explode(split(line,',')) as word from wc) w group by w.word;
其他概念
事务
可以但是不推荐使用
谓语下推
数据倾斜
一个任务数据过多,另外的任务数据过少。