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/profile2、修改配置文件
1、进入hive配置文件所在目录
cd /usr/local/soft/hive-3.1.2/conf2、创建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.jar4、初始化hive元数据库
# 2、初始化hive的元数据(表结构)到mysql中
schematool -dbType mysql -initSchema5、启动hive元数据服务（占用一个窗口不可操作）
hive --service metastore6、进入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.bak8、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.sql2、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]; 默认只能删除空数据库，强制删除加关键字cascade4、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/warehouse2、复杂数据类型
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/warehouse3、查看表信息
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)、con5.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;其他概念
事务
可以但是不推荐使用
谓语下推
数据倾斜
一个任务数据过多，另外的任务数据过少。
1793
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
