Hive学习
Hive的基本概念
1.HIve简介
Hive本质是将SQL转换为MapReduce的任务进行运算，底层由HDFS来提供数据存储，说白了hive可以理解为一个将SQL转换为MapReduce的任务的工具，甚至更近一步说hive就是一个MapReduce客户端。
什么是hive? （记住）
1、hive是数据仓库建模的工具之一。
2、可以向hive传入一条交互式的sql,在海量数据中查询分析得到结果的平台。
HIve架构图
为什么使用Hive?
如果直接使用hadoop的话，人员学习成本太高，项目要求周期太短，MapReduce实现复杂查询逻辑开发难度太大。如果使用hive的话，可以操作接口采用类SQL语法，提高开发能力，免去了写MapReduce，减少开发人员学习成本，功能扩展很方便（比如：开窗函数）。
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和mysql什么区别？（Hive就不是数据库…）
Hive应用场景
日志分析：大部分互联网公司使用hive进行日志分析，如百度、淘宝等。
统计一个网站一个时间段内的pv,uv，SKU,SPU,SKC （电商）
多维度数据分析（数据仓库）
海量结构化(关系型)数据离线分析
构建数据仓库
PV（Page View）访问量, 即页面浏览量或点击量，衡量网站用户访问的网页数量；在一定统计周期内用户每打开或刷新一个页面就记录1次，多次打开或刷新同一页面则浏览量累计。
UV（Unique Visitor）独立访客，统计1天内访问某站点的用户数(以cookie为依据);访问网站的一台电脑客户端为一个访客。可以理解成访问某网站的电脑的数量。网站判断来访电脑的身份是通过来访电脑的cookies实现的。如果更换了IP后但不清除cookies，再访问相同网站，该网站的统计中UV数是不变的。如果用户不保存cookies访问、清除了cookies或者更换设备访问，计数会加1。00:00-24:00内相同的客户端多次访问只计为1个访客。
2.分布式搭建Hive
华为云镜像站：https://mirrors.huaweicloud.com/home
1.下载文件
在华为云镜像站下载
apache-hive-3.1.2-bin.tar.gz
mysql-connector-java-8.0.29.tar.gz
本地解压ysql-connector-java-8.0.29.tar.gz，取出mysql-connector-java-8.0.29.jar备用。
2.上传解压配置环境变量
# 1、解压 (到上级目录)
tar -zxvf apache-hive-3.1.2-bin.tar.gz -C ../
# 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
3.修改配置文件
1.进入hive配置文件所在目录
cd /usr/local/soft/hive-3.1.2/conf
2、创建hive-site.xml配置文件
cp hive-default.xml.template 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配置文件
# 将日志级别改成WARN，避免执行sql出现很多日志log4j.rootLogger=WARN,CAlog4j.appender.CA=org.apache.log4j.ConsoleAppenderlog4j.appender.CA.layout=org.apache.log4j.PatternLayoutlog4j.appender.CA.layout.ConversionPattern=%-4r [%t] %-5p %c %x - %m%n%
4.上传驱动
# 将驱动包上传至hive的lib目录下
mysql-connector-java-8.0.29.jar
#lib目录中运行
cp /usr/local/soft/jars/mysql-connector-java-8.0.29.jar ./
5.初始化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/
6、初始化hive元数据库
# 2、初始化hive的元数据(表结构)到mysql中
schematool -dbType mysql -initSchema
报错：com.google.common.base.Preconditions.checkArgument(ZLjava/lang/String;Ljava/lang/Object;)V
这是因为hive内依赖的guava.jar和hadoop内的版本不一致造成的。
- hive中guava.jar位置/hive/lib/
- hadoop中guava.jar位置/hadoop/share/hadoop/common/lib/
删除版本低的，换成其中一个的高版本的！
#hadoop下的share中的hadoop中的common下的lib中运行
cp ./guava-27.0-jre.jar /usr/local/soft/hive-3.1.2/lib/
#删除/usr/local/soft/hive-3.1.2/lib下的guava-19.0.jar
rm -rf guava-19.0.jar
重新初始化元数据库：
又报错：com.ctc.wstx.exc.WstxParsingException: Illegal character entity: expansion character (code 0x8
at [row,col,system-id]: [3215,96,“file:/usr/local/soft/hive-3.1.2/conf/hive-site.xml”] 出现了违法的字符修改hive-site.xml 3215行 直接删掉（因为是描述信息）
再次初始化！！！
成功：
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
hadoop的etc/hadoop/core-site.xml
<property>
<name>hadoop.proxyuser.root.hosts</name>
<value>*</value>
</property>
<property>
<name>hadoop.proxyuser.root.groups</name>
<value>*</value>
</property>
#分发给node1 node2
scp core-site.xml node1:`pwd`
scp core-site.xml node2:`pwd`
8.启动hive元数据服务
#先启动hadoop
start-all.sh
#为了不占用一个xshell窗口
# 后台启动元数据服务
nohup hive --service metastore &
9.启动Hive
#进入Hive
hive
#使用show databases; 查看是否报错
10.常见问题
1、hadoop未启动，就开始执行hive命令
解决方案：启动hadoop
2、hadoop处于安全模式
解决方案：
1、等着，等hadoop日志和快照数据恢复完毕，自动离开安全模式
2、使用命令强制离开
3、元数据服务没开，操作不了sql语句
解决方案：nohup hive --service metastore &
3.Hive架构
形象的了解三者关系：
#### Client
> Hive允许client连接的方式有三个CLI（hive shell）、JDBC/ODBC(java访问hive)、WEBUI（浏览器访问 hive）。JDBC访问时中间件Thrift软件框架，跨语言服务开发。DDL DQL DML,整体仿写一套SQL语句。
>
> 1）client–需要下载安装包
>
> 2）JDBC/ODBC 也可以连接到Hive
> 现在主流都在倡导第二种 HiveServer2/beeline
> 做基于用户名和密码安全的一个校验
>
> 3）Web Gui
> hive给我们提供了一套简单的web页面
> 我们可以通过这套web页面访问hive 做的太简陋了
Metastore
> **元数据**包括表名、表所属的数据库（默认是default）、表的拥有者、列/分区字段、表的类型（是否是 外部表）、表的数据所在目录等。
>
> 一般需要借助于其他的数据载体（数据库）
>
> 主要用于存放数据库的建表语句等信息
>
> 推荐使用Mysql数据库存放数据
>
> 连接数据库需要提供：uri username password driver
#### 1.2.3 Driver（面试题：sql语句是如何转化成MR任务的？）
> 元数据存储在数据库中，默认存在自带的derby数据库（单用户局限性）中，推荐使用Mysql进行存储。
>
> 1） 解析器（SQL Parser）：将SQL字符串转换成抽象语法树AST（从3.x版本之后，转换成一些的stage），这一步一般都用第三方工具库完 成，比如ANTLR；对AST进行语法分析，比如表是否存在、字段是否存在、SQL语义是否有误。
>
> 2） 编译器（Physical Plan）：将AST编译（从3.x版本之后，转换成一些的stage）生成逻辑执行计划。
>
> 3） 优化器（Query Optimizer）：对逻辑执行计划进行优化。
>
> 4） 执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于Hive来说，就是 MR/Spark/flink。
#### 数据处理
> Hive的数据存储在HDFS中，计算由MapReduce完成。HDFS和MapReduce是源码级别上的整合，两者结合最佳。解释器、编译器、优化器完成HQL查询语句从词法分析、语法分析、编译、优化以及查询计划的生成。
1.Hive三种交互方式
1）第一种交互方式
shell交互Hive，用命令hive启动一个hive的shell命令行，在命令行中输入sql或者命令来和Hive交互。（正在用的 ）
服务端启动metastore服务（后台启动）：nohup hive --service metastore &
进入命令:hive
退出命令行：quit;
2）第二种交互方式
Hive启动为一个服务器，对外提供服务，其他机器可以通过客户端通过协议连接到服务器，来完成访问操作，这是生产环境用法最多的（企业里常用）
#（master）服务端启动hiveserver2服务：
nohup hive --service metastore &
nohup hiveserver2 &
#客户端执行（eg：node1）：需要稍等一下，启动服务需要时间：
#注意： 客户端中要有hive
#beeline远程连接到服务器（master）node1相当于你的电脑，此时在node1上操作（如写sql）是在hive的服务器上（hiveserver2）做操作的，由它发送给hive进行解析。
#进入命令:1)先执行：
beeline
#，再执行：
!connectjdbc:hive2://master:10000
2)或者直接执行： beeline -u jdbc:hive2://master:10000 -n root
退出命令行：!exit
3）第三种交互方式
使用 –e 参数来直接执行hql的语句
bin/hive -e "show databases;"
使用 –f 参数通过指定文本文件来执行hql的语句
特点：执行完sql后，回到linux命令行。
如有多条sql语句，可以先写成一个sql脚本，直接运行脚本。
vim hive.sql
create database bigdata30_test;
use bigdata30_test;
create table test1
(
id bigint,
name string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
show tables;
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：该表存储数据库的相关参数。
DBS：
3、Hive表和视图相关的元数据表
主要有TBLS、TABLE_PARAMS、TBL_PRIVS，这三张表通过TBL_ID关联。
TBLS:该表中存储Hive表，视图，索引表的基本信息。
TABLE_PARAMS:该表存储表/视图的属性信息。
TBL_PRIVS：该表存储表/视图的授权信息。
TBLS：
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:该表存储序列化的一些属性、格式信息，比如:行、列分隔符。
SDS：
（TextInputFormat）默认以文本形式输入输出。
SERDE_PARAMS：
5、Hive表字段相关的元数据表
主要涉及COLUMNS_V2：该表存储表对应的字段信息。
4.Hive的基本操作
1.Hive库操作
创建数据库
1）创建一个数据库，数据库在HDFS上的默认存储路径是/hive/warehouse/*.db。
create database testdb;
2）避免要创建的数据库已经存在错误，增加if not exists判断。（标准写法）
create database [if not exists] testdb;
create database if not exists testdb;
创建数据库和位置
create database if not exists bigdata30_test3 location '/bigdata30/yasuodb';
修改数据库
数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。（重点关注哪些不能改，以及为什么！！）（其实没啥用）
alter database dept set dbproperties('createtime'='20220531');
数据库详细信息
1）显示数据库（show）
show databases;
2）可以通过like进行过滤
show databases like 't*';
3）查看详情（desc）（描述）
desc database testdb;
4）切换数据库（use）
use testdb;
删除数据库（将删除的目录移动到回收站中）（删除了元数据）
1）最简写法
drop database testdb;
2）如果删除的数据库不存在，最好使用if exists判断数据库是否存在。否则会报错：FAILED: SemanticException [Error 10072]: Database does not exist: db_hive
drop database if exists testdb;
3)如果数据库不为空，使用cascade命令进行强制删除。报错信息如下FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database db_hive is not empty. One or more tables exist.)
drop database if exists testdb cascade;
2.Hive数据类型
关键的：
整型：BIGINT
浮点型： DOUBLE
字符串： STRING
时间类型：TIMESTAMP
BOOLEAN类型：BOOLEAN
| 类型 | 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 <= precision <= 38。scale：表示小数部分的位数。取值范围： 0 <= scale <= 38。如果不指定以上两个参数，则默认为decimal(10,0)。 | |
| 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<int>``array<struct<a:int, b:string>> | array(1, 2, 3)``array(array(1, 2), array(3, 4)) |
| MAP | map<string, string>``map<smallint, array<string>> | map(“k1”, “v1”, “k2”, “v2”)``map(1S, array(‘a’, ‘b’), 2S, array(‘x’, ‘y’)) |
| STRUCT | struct<x:int, y:int>struct<field1:bigint, field2:array<int>, field3:map<int, int>> named_struct(‘x’, 1, ‘y’, 2)named_struct(‘field1’, 100L, ‘field2’, array(1, 2), ‘field3’, map(1, 100, 2, 200)) |
Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。还有一个uniontype< 所有类型，所有类型… > 。
数组：array< 所有类型 >；
Map < 基本数据类型，所有数据类型 >；
struct < 名：所有类型[注释] >;
uniontype< 所有类型，所有类型… >
3.Hive表操作
Hive的存储格式:
Hive没有专门的数据文件格式,常见的有以下几种:
TEXTFILE
SEQUENCEFILE
AVRO
RCFILE
ORCFILE
PARQUET多记几个。
描述：
TextFile:
TEXTFILE 即正常的文本格式，是Hive默认文件存储格式，因为大多数情况下源数据文件都是以text文件格式保存（便于查看验数和防止乱码）。此种格式的表文件在HDFS上是明文，可用hadoop fs -cat命令查看，从HDFS上get下来后也可以直接读取。
TEXTFILE 存储文件默认每一行就是一条记录，可以指定任意的分隔符进行字段间的分割。但这个格式无压缩，需要的存储空间很大。虽然可结合Gzip、Bzip2、Snappy等使用，使用这种方式，Hive不会对数据进行切分，从而无法对数据进行并行操作。
一般只有与其他系统由数据交互的接口表采用TEXTFILE 格式，其他事实表和维度表都不建议使用。
RCFile:
Record Columnar的缩写。是Hadoop中第一个列文件格式。能够很好的压缩和快速的查询性能。通常写操作比较慢，比非列形式的文件格式需要更多的内存空间和计算量。 RCFile是一种行列存储相结合的存储方式。首先，其将数据按行分块，保证同一个record在一个块上，避免读一个记录需要读取多个block。其次，块数据`列式存储`，有利于数据压缩和快速的列存取。
ORCFile:
Hive从0.11版本开始提供了ORC的文件格式，ORC文件不仅仅是一种列式文件存储格式，最重要的是有着很高的压缩比，并且对于MapReduce来说是可切分（Split）的。因此，在Hive中使用ORC作为表的文件存储格式，不仅可以很大程度的节省HDFS存储资源，而且对数据的查询和处理性能有着非常大的提升，因为ORC较其他文件格式压缩比高，查询任务的输入数据量减少，使用的Task也就减少了。ORC能很大程度的节省存储和计算资源，但它在读写时候需要消耗额外的CPU资源来压缩和解压缩，当然这部分的CPU消耗是非常少的。
Parquet:
通常我们使用关系数据库存储结构化数据，而关系数据库中使用数据模型都是扁平式的，遇到诸如数组、Map和自定义Struct的时候就需要用户在应用层解析。但是在大数据环境下，通常数据的来源是服务端的埋点数据，很可能需要把程序中的某些对象内容作为输出的一部分，而每一个对象都可能是嵌套的，所以如果能够原生的支持这种数据，这样在查询的时候就不需要额外的解析便能获得想要的结果。Parquet的灵感来自于2010年Google发表的Dremel论文，文中介绍了一种支持嵌套结构的存储格式，并且使用了列式存储的方式提升查询性能。Parquet仅仅是一种存储格式，它是语言、平台无关的，并且不需要和任何一种数据处理框架绑定。这也是parquet相较于orc的仅有优势：支持嵌套结构。Parquet 没有太多其他可圈可点的地方,比如他不支持update操作(数据写成后不可修改),不支持ACID等.
SEQUENCEFILE:
SequenceFile是Hadoop API 提供的一种二进制文件，它将数据以<key,value>的形式序列化到文件中。这种二进制文件内部使用Hadoop 的标准的Writable 接口实现序列化和反序列化。它与Hadoop API中的MapFile 是互相兼容的。Hive 中的SequenceFile 继承自Hadoop API 的SequenceFile，不过它的key为空，使用value 存放实际的值， 这样是为了避免MR 在运行map 阶段的排序过程。SequenceFile支持三种压缩选择：NONE, RECORD, BLOCK。 Record压缩率低，一般建议使用BLOCK压缩。 SequenceFile最重要的优点就是Hadoop原生支持较好，有API，但除此之外平平无奇，实际生产中不会使用。
AVRO:
Avro是一种用于支持数据密集型的二进制文件格式。它的文件格式更为紧凑，若要读取大量数据时，Avro能够提供更好的序列化和反序列化性能。并且Avro数据文件天生是带Schema定义的，所以它不需要开发者在API 级别实现自己的Writable对象。Avro提供的机制使动态语言可以方便地处理Avro数据。最近多个Hadoop 子项目都支持Avro 数据格式，如Pig 、Hive、Flume、Sqoop和Hcatalog。
结论：ORCFILE存储文件读操作效率最高
耗时比较：ORC<Parquet<RC<Text
select * from （数据库名）.(表名); #这样不用切换数据库
创建表：
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 students1
(
id bigint,
name string,
age int,
gender string,
clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; // 必选，指定列分隔符
# 上传students.txt 测试
select * from students1 limit 10;
2.指定（HDFS）location （这种方式也比较常用）：
create table IF NOT EXISTS students2
(
id bigint,
name string,
age int,
gender string,
clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/bigdata30/input1'; // 指定Hive表的数据的存储位置，一般在数据已经上传到HDFS，想要直接使用，会指定Location，通常Locaion会跟外部表一起使用，内部表一般使用默认的location
#例（数据以students.txt为例）
create table IF NOT EXISTS students3
(
id bigint,
name string,
age int,
gender string,
clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/bigdata30/input1';
#此时 /bigdata30/input1 就代表着students3这张表
3.指定存储格式
# 显示表
show tables;
show tables like 'u*';
desc t_person;
desc formatted students; // 更加详细
create table IF NOT EXISTS students4
(
nid bigint,
name string,
age int,
gender string,
clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS ORC
LOCATION '/bigdata30/input2'; // 指定储存格式为orcfile
--------------------------------
存储格式的指定：
file_format:
: SEQUENCEFILE
| TEXTFILE -- (Default, depending on hive.default.fileformat configuration)
| RCFILE -- (Note: Available in Hive 0.6.0 and later)
| ORC -- (Note: Available in Hive 0.11.0 and later)
| PARQUET -- (Note: Available in Hive 0.13.0 and later)
| AVRO -- (Note: Available in Hive 0.14.0 and later)
| JSONFILE -- (Note: Available in Hive 4.0.0 and later)
| INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
inputFormat:RCFileInputFormat,outputFormat:RCFileOutputFormat，如果不指定，默认为textfile，注意：除textfile以外，其他的存储格式的数据都不能直接加载，需要使用从表加载的方式。
students4这张表的存储格式是ORC：
上传students.txt数据测试，报错：
原因: 上传的数据不是orc格式。
这时需要我们手动插入数据，做查询测试。
insert into students4 values(1001,'ss',18,'man','like');
发现这是一次MR，因为有读和写操作
可以查到数据 在HDFS上可以看到ORC格式的数据很小。
4.create table xxxx as select_statement(SQL语句) (这种方式比较常用)
#as 不能漏
#创建一个表来存储各个班级的人数(students1表)：
create table res1 as select clazz,count(1) as counts from students1 group by clazz;
查看res1表
5.create table xxxx like table_name 只想建表，不需要加载数据
create table students5 like students;
#把一张表的表结复制给另一张表（只有表机构没有数据） 开发过程还是老老实实写吧
复杂人员信息表创建：
create table IF NOT EXISTS t_person(
name string,
friends array<string>,
children map<string,int>,
address struct<street:string ,city:string>
)
row format delimited fields terminated by ',' -- 列与列之间的分隔符
collection items terminated by '_' -- 元素与元素之间分隔符（不止数组，集合）
map keys terminated by ':' -- Map数据类型键与值之间的分隔符
lines terminated by '\n'; -- 行与行之间的换行符
songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,beng bu_anhui
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,he fei_anhui
创建一个t_person.txt 放入上述数据，并上传至hdfs
逗号 得到每一列，下划线得到值。（复杂数据类型模版）
4.加载数据
1、使用hdfs dfs -put '本地数据' 'hive表对应的HDFS目录下'
2、使用 load data
create table IF NOT EXISTS students6
(
id bigint,
name string,
age int,
gender string,
clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
-- 将HDFS上的/bigdata30/input1目录下面的数据 移动至 students6表对应的HDFS目录下，注意是 移动、移动、移动
load data inpath '/bigdata30/input1/students.txt' into table students6;
-- 此时students6中有数据
create table IF NOT EXISTS students7
(
id bigint,
name string,
age int,
gender string,
clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
--如何将linux上的数据加载到hive中？
-- 清空表
truncate table students;
-- 加上 local 关键字 可以将Linux本地目录下的文件 上传到 hive表对应HDFS 目录下 原文件不会被删除
-- 复制复制复制！
load data local inpath '/usr/local/soft/bigdata30/students.txt' into table students7;
--students7有数据，重复执行会生成多个相同名字不同的文件
-- overwrite 覆盖加载
load data local inpath '/usr/local/soft/bigdata30/students.txt' overwrite into table students;
3、create table xxx as SQL语句
4、insert into table xxxx SQL语句 （没有as，最常用）
create table IF NOT EXISTS res2
(
id bigint,
name string,
age int,
gender string,
clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
# 将 students1表的数据插入到 res2 这是复制 不是移动 students1表中的表中的数据不会丢失
insert into table res2 select * from students1;
#覆盖插入 把into 换成 overwrite
insert overwrite table res2 select * from students1;
#统计students1中年龄大于23的学生并将数据插入res2中：
insert into res2 select * from students1 where age>23 limit 20;
insert overwrite res2 select * from students1 where age>23 limit 20;
------------------------------------
工作中的sql结果插入模板：
insert overwrite table xxx.xxx partition(xxx=${xxx})
select
xxxxx from xxx;
5.修改列
添加列（不影响原本数据 没有的直接是null）
alter table students2 add columns (education string);
更新列（不要轻易改数据类型）
alter table stduents2 change education educationnew string;
6.删除表
drop table （表名）;
7.HIve内，外部表
面试题：内部表和外部表的区别？如何创建外部表？工作中使用外部表
- hive内部表
当创建好表的时候，HDFS会在当前表所属的库中创建一个文件夹
当设置表路径的时候，如果直接指向一个已有的路径,可以直接去使用文件夹中的数据
当load数据的时候，就会将数据文件存放到表对应的文件夹中
而且数据一旦被load，就不能被修改
我们查询数据也是查询文件中的文件,这些数据最终都会存放到HDFS
当我们删除表的时候，表对应的文件夹会被删除，同时数据也会被删除
默认建表的类型就是内部表
-- 内部表
create table students_internal
(
id bigint,
name string,
age int,
gender string,
clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/bigdata30/input2';
hive> dfs -put /usr/local/soft/bigdata30/students.txt /input2/;
2.Hive外部表
外部表说明
外部表因为是指定其他的hdfs路径的数据加载到表中来，所以hive会认为自己不完全独占这份数据
删除hive表的时候，数据仍然保存在hdfs中，不会删除。
//内部表
create table students_inner
(
id bigint,
name string,
age int,
gender string,
clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/bigdata30/input3';
-- 删除后，hdfs上找不到，mysql中元数据也没了，hive中没有此表了（一删全删）
// 外部表
create external table students_external
(
id bigint,
name string,
age int,
gender string,
clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/bigdata30/input4';
#加载数据
load data local inpath '/usr/local/soft/bigdata30/students.txt' into table students_external;
#删除之后，hive中没有了，mysql中的元数据没有呢，但是HDFS上还存在。这就是外部表
# 想要恢复的话就重新执行一次建表语句
一般在公司中，使用外部表多一点，因为数据可以需要被多个程序使用，避免误删，通常外部表会结合location一起使用
外部表还可以将其他数据源中的数据 映射到 hive中，比如说：hbase，ElasticSearch…
设计外部表的初衷就是 让 表的元数据 与 数据 解耦
- 操作案例: 分别创建dept，emp，salgrade。并加载数据。备用。
创建数据文件存放的目录
cd /usr/local/soft/bigdata30/
vim dept.txt
vim emp.txt
vim salgrade.txt
- 创建dept表
CREATE EXTERNAL TABLE IF NOT EXISTS dept (
DEPTNO int,
DNAME string,
LOC string
) row format delimited fields terminated by ','
location '/bigdata30/dept';
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
- 创建emp表
CREATE EXTERNAL TABLE IF NOT EXISTS emp (
EMPNO int,
ENAME string,
JOB string,
MGR int,
HIREDATE date,
SAL int,
COMM int,
DEPTNO int
) row format delimited fields terminated by ','
location '/bigdata30/emp';
7369,SMITH,CLERK,7902,1980-12-17,800,null,20
7499,ALLEN,SALESMAN,7698,1981-02-20,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02,2975,null,20
7654,MARTIN,SALESMAN,7698,1981-09-28,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01,2850,null,30
7782,CLARK,MANAGER,7839,1981-06-09,2450,null,10
7788,SCOTT,ANALYST,7566,1987-07-13,3000,null,20
7839,KING,PRESIDENT,null,1981-11-17,5000,null,10
7844,TURNER,SALESMAN,7698,1981-09-08,1500,0,30
7876,ADAMS,CLERK,7788,1987-07-13,1100,null,20
7900,JAMES,CLERK,7698,1981-12-03,950,null,30
7902,FORD,ANALYST,7566,1981-12-03,3000,null,20
7934,MILLER,CLERK,7782,1982-01-23,1300,null,10
- 创建salgrade表
CREATE EXTERNAL TABLE IF NOT EXISTS salgrade (
GRADE int,
LOSAL int,
HISAL int
) row format delimited fields terminated by ','
location '/bigdata30/salgrade';
1,700,1200
2,1201,1400
3,1401,2000
4,2001,3000
5,3001,9999
分别加载数据
load data local inpath ‘/usr/local/soft/bigdata30/emp.txt’ into table emp;
load data local inpath ‘/usr/local/soft/bigdata30/dept.txt’ into table dept;
load data local inpath ‘/usr/local/soft/bigdata30/dept.txt’ into table salgrade;
8.Hive导出数据
将表中的数据备份
- 将查询结果存放到本地
//创建存放数据的目录
mkdir -p /usr/local/soft/bigdata30/hive_out1
//导出查询结果的数据(导出到Node01上)
insert overwrite local directory '/usr/local/soft/bigdata30/hive_out1/person_data' select * from t_person;
# 导出的结果不好看，且是MR的结果。
- 按照指定的方式将数据输出到本地
-- 创建存放数据的目录
mkdir -p /usr/local/soft/bigdata30/hive_out1
-- 导出查询结果的数据（格式好看，指定了分隔符）（都是MR结果）
insert overwrite local directory '/usr/local/soft/bigdata30/hive_out1/person'
ROW FORMAT DELIMITED fields terminated by ','
collection items terminated by '-'
map keys terminated by ':'
lines terminated by '\n'
select * from t_person;
- 将查询结果输出到HDFS
-- 创建存放数据的目录
hdfs dfs -mkdir -p /bigdata30/data
-- 导出查询结果的数据
insert overwrite local directory '/usr/local/soft/bigdata30/students_data2' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' select * from students
- 直接使用HDFS命令保存表对应的文件夹
// 使用HDFS命令拷贝文件到其他目录
hdfs dfs -cp /hive/warehouse/t_person/* /usr/local/soft/bigdata30/person
-
将表结构和数据同时备份
将数据导出到HDFS
//创建存放数据的目录 hdfs dfs -mkdir -p /bigdata30/copy //导出查询结果的数据 export table t_person to '/bigdata30/copy'; 删除表结构
drop table t_person; 恢复表结构和数据
import from '/bigdata30/copy';注意：时间不同步，会导致导入导出失败
Hive分区与排序（内置函数）
分区：静态分区（单级，多级），动态分区（单级，多级）
1.Hive分区（十分重要）
分区的目的：避免全表扫描，加快查询速度！
在大数据中，最常见的一种思想就是分治，我们可以把大的文件切割划分成一个个的小的文件，这样每次操作一个个小的文件就会很容易了，同样的道理，在hive当中也是支持这种思想的，就是我们可以把大的数据，按照每天或者每小时切分成一个个小的文件，这样去操作小的文件就会容易很多了。
- 静态分区（用的多）
静态分区（SP）static partition–partition by (字段 类型)
借助于物理的文件夹分区，实现快速检索的目的。
一般对于查询比较频繁的列设置为分区列。
分区查询的时候直接把对应分区中所有数据放到对应的文件夹中。
--单级分区
CREATE TABLE IF NOT EXISTS t_student (
sno int,
sname string
) partitioned by(grade int) -- partitioned（关键字）
row format delimited fields terminated by ',';
-- 分区的字段不要和表的字段相同。相同会报错error10035
vim grade1.txt
1,xiaohu01,1
2,xiaohu02,1
3,xiaohu03,1
4,xiaohu04,1
5,xiaohu05,1
vim grade2.txt
6,xiaohu06,2
7,xiaohu07,2
8,xiaohu08,2
vim grade3.txt
9,xiaohu09,3
10,xiaohu10,3
11,xiaohu11,3
12,xiaohu12,3
13,xiaohu13,3
14,xiaohu14,3
15,xiaohu15,3
vim grade4.txt
16,xiaohu16,4
17,xiaohu17,4
18,xiaohu18,4
19,xiaohu19,4
20,xiaohu20,4
21,xiaohu21,4
-- 载入数据
-- 将相应年级一次导入
load data local inpath '/usr/local/soft/bigdata30/grade2.txt' into table t_student partition(grade=2);
-- 其他同上
-- 演示多拷贝一行上传，分区的列的值是分区的值，不是原来的值
select * from t_student-- 用起来和之前的没什么区别
select * from t_student where grade=2; --非常快
不正常的情况：
创建一个grade5.txt
插入数据，但是更改一个人的年级为4
加载数据，查询发现：
文件数据是没有变的，年级的数据来自于文件夹。他不管文件里面具体的数据。数据放到哪，就是哪个年级
静态多级分区（一般不会超过两个）
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/bigdata30/teacher1.txt' into table t_teacher partition(grade=1,clazz=1);
-- ...
select * from t_teacher where grade=1 and clazz=2;
-- 速度很快
产生了多级分区
select * from t_student where grade = 1;
-- 全表扫描，不推荐，效率低
select count(*) from t_student;
-- 使用where条件进行分区裁剪，避免了全表扫描，效率高
select count(*) from t_student where grade = 1;
-- 也可以在where条件中使用非等值判断
select count(*) from t_student where grade<3 and grade>=1;
--查看分区
show partitions t_teacher;
-- 添加分区
alter table t_student add partition (grade=6);
alter table t_teacher add partition (grade=3,clazz=1) location '/user/hive/warehouse/bigdata30.db/t_teacher/grade=3/clazz=1';
-- 删除分区
alter table t_student drop partition (grade=5);
- 动态分区
动态分区（DP）dynamic partition
静态分区与动态分区的主要区别在于静态分区是手动指定，而动态分区是通过数据来进行判断。
详细来说，静态分区的列是在编译时期通过用户传递来决定的；动态分区只有在SQL执行时才能决定。
-- 开启动态分区 设置以下参数
# 表示开启动态分区
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;
-- hive-site.xml中修改
设置为true表示开启动态分区的功能（默认为false）
--hive.exec.dynamic.partition=true;
设置为nonstrict，表示允许所有分区都是动态的（默认为strict）
-- hive.exec.dynamic.partition.mode=nonstrict;
-- hive.exec.dynamic.partition.mode=strict;
每个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;
案例
-- 创建外部表 A表
CREATE EXTERNAL TABLE IF NOT EXISTS t_student_e (
sno int,
sname string,
grade int,
clazz int
)
row format delimited fields terminated by ','
location "/bigdata30/teachers";
-- 创建分区表 B表
CREATE TABLE IF NOT EXISTS t_student_d (
sno int,
sname string
) partitioned by (grade int,clazz int)
row format delimited fields terminated by ',';
-- 数据从 A表—>B表 B表自动分区
-- 将数据加载到A表中
load data local inpath '/usr/local/soft/bigdata30/teachers.txt' into table t_student_e;
-- 数据
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并且执行很多次，很麻烦。
而且静态分区有可能会产生数据错误问题
-- 会报错
insert overwrite table t_student_d partition (grade=1,clazz=1) select * from t_student_e where grade=1;
如果使用动态分区，动态分区会根据select的结果自动判断数据应该load到哪儿分区去。
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 ( hash(name)%n == x )
- 列的值做哈希取余 决定数据应该存储到哪个桶
2.3 数据分桶优势
方便抽样
使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便
提高join查询效率
获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。
2.4 分桶实战
首先，分区和分桶是两个不同的概念，很多资料上说需要先分区在分桶，其实不然，分区是对数据进行划分，而分桶是对文件进行划分。
当我们的分区之后，最后的文件还是很大怎么办，就引入了分桶的概念。
将这个比较大的文件再分成若干个小文件进行存储，我们再去查询的时候，在这个小范围的文件中查询就会快很多。
对于hive中的每一张表、分区都可以进一步的进行分桶。
当然，分桶不是说将文件随机进行切分存储，而是有规律的进行存储。在看完下面的例子后进行解释，现在干巴巴的解释也不太好理解。它是由列的哈希值除以桶的个数来决定每条数据划分在哪个桶中。
创建顺序和分区一样，创建的方式不一样。
不一定要先分区再分桶
# 分区和分桶的区别
1、在HDFS上的效果区别，分区产生的是一个一个子文件夹，分桶产生的是一个一个文件
2、无论是分区还是分桶，在建表的时候都要指定字段，分区使用partitioned by指定分区字段，分桶使用clustered by指定分桶字段
3、partitioned by指定分区字段的时候，字段后面需要加上类型，而且不能在建表小括号中出现。clustered by指定分桶字段的时候，字段已经出现定义过了，只需要指定字段的名字即可
4、分区字段最好选择固定类别的，分桶字段最好选择值各不相同的。
5、分桶不是必须要建立在分区之上，可以不进行分区直接分桶
首先我们需要开启分桶的支持
（依然十分重要，不然无法进行分桶操作！！！！）
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 person
(
id int,
name string,
age int
)
row format delimited
fields terminated by ',';
将数据load到这张表中
load data local inpath '/usr/local/soft/bigdata30/ft_data.txt' into table person;
创建分桶表
create table psn_bucket
(
id int,
name string,
age int
)
clustered by(age) into 4 buckets -- 以age字段进行hash取余，分4个桶，且分桶的字段可以在定义里出现
row format delimited fields terminated by ',';
将数据insert到表psn_bucket中
(注意：这里和分区表插入数据有所区别，分区表需要select 和指定分区，而分桶则不需要)
insert into psn_bucket select * from person;
4个桶对应4个reduce任务
在HDFS上查看数据（4个文件）
查询数据
我们在linux中使用Hadoop的命令查看一下（与我们猜想的顺序一致）
hadoop fs -cat /user/hive/warehouse/bigdata30.db/psn_bucket/000000_0
-- 8,scala,88 4,hive,44
-- 其余的可以依次查看，发现就是我们猜想的一样
这里设置的桶的个数是4 数据按照 年龄%4 进行放桶(文件)
11%4 == 3 -----> 000003_0
22%4 == 2 -----> 000002_0
33%4 == 1 -----> 000001_0
44%4 == 0 -----> 000000_0
…以此类推
****
3.Hive JDBC
启动hiveserver2
nohup hiveserver2 &
新建maven项目并添加依赖（备用）
-- hive的pom.xml中添加
<dependencies>
<!-- 注释：编写hadoop依赖 g-a-v坐标 -->
<!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-common -->
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-client -->
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-client</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-hdfs -->
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-hdfs</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc -->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
</dependency>
</dependencies>
-- 总项目的pom.xml
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<hadoop.version>3.1.3</hadoop.version>
<hive.version>3.1.2</hive.version>
</properties>
<dependencyManagement>
<dependencies>
<!-- 注释：编写hadoop依赖 g-a-v坐标 -->
<!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-common -->
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
<version>${hadoop.version}</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-client -->
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-client</artifactId>
<version>${hadoop.version}</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-hdfs -->
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-hdfs</artifactId>
<version>${hadoop.version}</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.49</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc -->
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>${hive.version}</version>
</dependency>
</dependencies>
</dependencyManagement>
编写JDBC代码
/*
1、注册驱动
2、创建连接对象
3、创建数据库操作对象
4、执行sql语句
5、增删改操作或者查询操作
6、分析查询结果
7、关闭连接释放资源
*/
public class HiveJDBC {
public static void main(String[] args) throws Exception{
//org.apache.hive.jdbc.HiveDriver
Class.forName("org.apache.hive.jdbc.HiveDriver");
Connection conn = DriverManager.getConnection("jdbc:hive2://master:10000/bigdata30");
PreparedStatement prep = conn.prepareStatement("select * from students1 where age>? limit 20");
prep.setInt(1,23);
ResultSet resultSet = prep.executeQuery();
while (resultSet.next()){
int id = resultSet.getInt(1);
//ctrl+alt + 下键
String name = resultSet.getString(2);
int age = resultSet.getInt(3);
String gender = resultSet.getString(4);
String clazz = resultSet.getString(5);
System.out.println("学号："+id+", 姓名："+name+", 年龄："+age+", 性别："+gender+", 班级："+clazz);
}
prep.close();
conn.close();
}
}
// 运行 查询成功
4.Hive查询语法（DQL）
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]
全局排序
- order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间
- 使用 order by子句排序 :ASC（ascend）升序（默认）| DESC（descend）降序
- order by放在select语句的结尾
- 无论设置了几个reduce ，只会使用一个reduce处理。 不要使用（数据量大的时候）
select * from 表名 order by 字段名1[，别名2...];
局部排序（对reduce内部做排序）
- sort by 不是全局排序,其在数据进入reducer前完成排序。
- 如果用sort by进行排序，并且设置mapred.reduce.tasks>1,则sort by 只保证每个reducer的输出有序，不保证全局有序。asc,desc
- 设置reduce个数
set mapreduce.job.reduces=3;
- 查看reduce个数
set mapreduce.job.reduces;
- 排序
select * from 表名 sort by 字段名[,字段名...];
一个reduce
3个reduce（内部排序 全局不排序）
分区排序
distribute by（字段）根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。
类似MR中partition,进行分区，结合sort by使用。（注意：distribute by 要在sort by之前）
对于distrbute by 进行测试，一定要多分配reduce进行处理，否则无法看到distribute by的效果。
设置reduce个数 可以升序降序
set mapreduce.job.reduces=3;
- 排序
select * from 表名 distribute by 字段名[,字段名...] sort by 字段;
分区并排序
- cluster by（字段）除了具有Distribute by的功能外。
- cluster by = distribute by + sort by 只能默认升序，不能使用倒序
select * from 表名 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 xxxx;
-- 3.详细显示自带的函数的用法
desc function extended xxx;
desc function extended upper;
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
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 别列名;
-- 创建数据库表
create table t_movie1(
id int,
name string,
types string
)
row format delimited fields terminated by ','
lines terminated by '\n';
-- 电影数据 movies.txt
-- 加载数据到数据库
load data local inpath '/usr/local/soft/bigdata30/movies.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';
-- 电影数据 movies1.txt
-- 加载数据到数据库
load data local inpath '/usr/local/soft/bigdata30/movies1.txt' into table t_movie2;
1,这个杀手不太冷,剧情
1,这个杀手不太冷,动作
1,这个杀手不太冷,犯罪
2,七武士,动作
2,七武士,冒险
2,七武士,剧情
3,勇敢的心,动作
3,勇敢的心,传记
3,勇敢的心,剧情
3,勇敢的心,历史
3,勇敢的心,战争
4,东邪西毒,剧情
4,东邪西毒,动作
4,东邪西毒,爱情
4,东邪西毒,武侠
4,东邪西毒,古装
5,霍比特人,动作
5,霍比特人,奇幻
5,霍比特人,冒险
-- collect_set()和collect_list()都是对列转成行，区别就是list里面可重复而set里面是去重的
-- concat_ws(':',collect_set(type)) ':' 表示你合并后用什么分隔，collect_set(stage)表示要合并表中的那一列数据
select id,concat_ws(':',collect_set(type)) as types from t_movie2 group by id;
3、WordCount案例
数据准备
hello,world
hello,bigdata
like,life
bigdata,good
建表
create table wc
(
line string
)
row format delimited fields terminated by '\n';
导入数据
load data local inpath '/usr/local/soft/bigdata30/words.txt' into table wc;
步骤1：先对一行数据进行切分
select split(line,',') from wc;
步骤2：将行转列
select explode(split(line,',')) from wc;
步骤3：将相同的进行分组统计
select t1.word,count(1) as counts from(select explode(split(line,',')) as word from wc) t1 group by t1.word;
4、函数学习
1、count(*)、count(1) 、count(‘字段名’) 区别
从执行结果来看
- count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL 最慢的
- count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL 最快的
- count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计 仅次于count(1)
从执行效率来看
- 如果列为主键，count(列名)效率优于count(1)
- 如果列不为主键，count(1)效率优于count(列名)
- 如果表中存在主键，count(主键列名)效率最优
- 如果表中只有一列，则count(*)效率最优
- 如果表有多列，且不存在主键，则count(1)效率优于count(*)
在工作中如果没有特殊的要求，就使用count(1)来进行计数。
hive语句的执行顺序
1.from2.join on 或 lateral view explode(需炸裂的列) tbl as 炸裂后的列名
3.where
4.group by
5.聚合函数 如Sum() avg() count(1)等
6.having 在此开始可以使用select中的别名
7.select 若包含over（）开窗函数，此时select中的内容作为窗口函数的输入，窗口中所选的数据范围也是在group by，having之后，并不是针对where后的数据进行开窗，这点要注意。需要注意开窗函数的执行顺序及时间点。
8.distinct
9.order by
10.limit（建议：今后在大数据环境中，一张表的数据量肯定十分庞大的，养成加limit的习惯）还有分页的功能
3、where 条件里不支持不等式子查询，实际上是支持 in、not in、exists、not exists( hive3.x版本是支持的 )
-- 查询薪资大于SCOTT的薪资员工信息 HIve3.X版本可以这么写
select * from emp where sal>(select sal from emp where ename='SCOTT)';
--若不是 可以这么写 制造列进行 关联
select * from(select t1.empno,t1.ename,t1.job,t1.mgr,t1.hiredate,t1.sal,t1.comm,t1.deptno,t2.sal as sc_sal from(select *,1 as tmp_id from emp) t1 join(select sal,1 as tmp_id from emp where ename='SCOTT') t2 on (t1.tmp_id=t2.tmp_id))tt1 where tt1.sal>tt1.sc_sal;
-- 结果同上
-- 列出与“SCOTT”从事相同工作的所有员工。
select t1.EMPNO
,t1.ENAME
,t1.JOB
from emp t1
where t1.ENAME != "SCOTT" and t1.job in(
select job
from emp
where ENAME = "SCOTT");
7900,JAMES,CLERK,7698,1981-12-03,950,null,30
7902,FORD,ANALYST,7566,1981-12-03,3000,null,20
select t1.EMPNO
,t1.ENAME
,t1.JOB
from emp t1
where t1.ENAME != "SCOTT" and exists(
select job
from emp t2
where ENAME = "SCOTT"
and t1.job = t2.job
);
4、hive中大小写不敏感（列名无所谓大小写）
5、在hive中，数据中如果有null字符串，加载到表中的时候会变成 null （不是字符串）
如果需要判断 null，使用 某个字段名 is null 这样的方式来判断
或者使用 nvl() 函数，不能 直接 某个字段名 == null
-- 由于comm列中存在null值 直接相加 结果为null
select sal+comm from emp;
select sal+nvl(comm,0) from emp;
6、使用explain查看SQL执行计划
面试题：hive中一条sql语句如何解析成MapReduce作业执行的？（hive的版本）
explain select t1.EMPNO
,t1.ENAME
,t1.JOB
from emp t1
where t1.ENAME != "SCOTT" and t1.job in(
select job
from emp
where ENAME = "SCOTT");
# 查看更加详细的执行计划，加上extended
explain extended select t1.EMPNO
,t1.ENAME
,t1.JOB
from emp t1
where t1.ENAME != "SCOTT" and t1.job in(
select job
from emp
where ENAME = "SCOTT");
Hive 常用函数
关系运算
// 等值比较 = == < = >
// 不等值比较 != <>
// 区间比较： select * from default.students where id between 1500100001 and 1500100010;
// 空值/非空值判断：isnull、isnotnull、nvl()、isnull()
// like、rlike、regexp用法
数值计算
取整函数(四舍五入)：round
向上取整：ceil
向下取整：floor
条件函数(主要使用场景是数据清洗的过程中使用，有些构建表的过程也是需要的)
- if： if(表达式,如果表达式成立的返回值,如果表达式不成立的返回值) （重点）
- 条件表达式?表达式1:表达式2;
select sal,if(sal<2000,'低薪',if(sal>=2000 and sal<3000,'中等','高薪')) as level from emp;
- COALESCE
select COALESCE(null,'1','2'); // 1 从左往右 依次匹配 直到非空为止
select COALESCE('1',null,'2'); // 1
- case when（重点）
select sal,case when sal<2000 then '低薪'
when sal>=2000 and sal<3000 then '中等薪资'
else '高薪' end as level
from emp;
select name
,case name when "施笑槐" then "槐ge"
when "吕金鹏" then "鹏ge"
when "单乐蕊" then "蕊jie"
else "算了不叫了"
end as nickname
from students limit 10;
注意条件的顺序
日期函数重点！！！
-- 时间戳
select from_unixtime(1717680233,'YYYY年MM月dd日 HH时mm分ss秒');
select from_unixtime(unix_timestamp(),'YYYY/MM/dd HH:mm:ss');
// '2021年01月14日' -> '2021-01-14'
select from_unixtime(unix_timestamp('2024年06月06日','yyyy年MM月dd日'),'yyyy-MM-dd');
// "04牛2021数加16强" -> "2021/04/16"
select from_unixtime(unix_timestamp("04牛2024数加11强","MM牛yyyy数加dd强"),"yyyy年MM月dd日");
字符串函数
-- 拼接
concat('123','456'); // 123456
concat('123','456',null); // NULL
select concat_ws('#','a','b','c'); // a#b#c
select concat_ws('#','a','b','c',NULL); // a#b#c 可以指定分隔符，并且会自动忽略NULL
select concat_ws("|",cast(id as string),name,cast(age as string),gender,clazz) from students limit 10; -- cast 转换 concat_ws 参与拼接的元素必须是字符串或字符串数组类型
select substring("abcdefg",1); -- abcdefg HQL中涉及到位置的时候 是从1开始计数
// '2021/01/14' -> '2021-01-14'
select concat_ws("-",substring('2021/01/14',1,4),substring('2021/01/14',6,2),substring('2021/01/14',9,2));
// 建议使用日期函数去做日期
select from_unixtime(unix_timestamp('2021/01/14','yyyy/MM/dd'),'yyyy-MM-dd'); -- 效率高
select concat_ws('-',split('2021/01/14','/'));
-- 字符串从1开始，数组从0开始
select split("abcde,fgh",","); // ["abcde","fgh"]
select split("a,b,c,d,e,f",",")[2]; // c 数组的下标依旧是从0开始
select explode(split("abcde,fgh",",")); // abcde
// fgh
// 解析json格式的数据 get_json_object
select get_json_object('{"name":"zhangsan","age":18,"score":[{"course_name":"math","score":100},{"course_name":"english","score":60}]}',"$.score[0].score"); -- 100 $.代表全文
{
"name": "zhangsan",
"age": 18,
"score": [{
"course_name": "math",
"score": 100
}, {
"course_name": "english",
"score": 60
}]
}
$.score[0].score
Hive窗口函数
Hive窗口函数
普通的聚合函数每组(Group by)只返回一个值，而开窗函数则可为窗口中的每行都返回一个值。
简单理解，就是对查询的结果多出一列，这一列可以是聚合值，也可以是排序值。
开窗函数一般就是说的是over()函数，其窗口是由一个 OVER 子句 定义的多行记录
开窗函数一般分为两类,聚合开窗函数和排序开窗函数。
-- 聚合格式
select sum(字段名) over([partition by 字段名] [ order by 字段名]) as 别名,
max(字段名) over() as 别名
from 表名;
-- 排序窗口格式
select rank() over([partition by 字段名] [ order by 字段名]) as 别名 from 表名;
注意点：
- over()函数中的分区、排序、指定窗口范围可组合使用也可以不指定，根据不同的业务需求结合使用
- over()函数中如果不指定分区，窗口大小是针对查询产生的所有数据，如果指定了分区，窗口大小是针对每个分区的数据
测试数据
-- 创建表
create table t_fraction(
name string,
subject string,
score int)
row format delimited fields terminated by ","
lines terminated by '\n';
-- 测试数据 fraction.txt
孙悟空,语文,10
孙悟空,数学,73
孙悟空,英语,15
猪八戒,语文,10
猪八戒,数学,73
猪八戒,英语,11
沙悟净,语文,22
沙悟净,数学,70
沙悟净,英语,31
唐玄奘,语文,21
唐玄奘,数学,81
唐玄奘,英语,23
-- 上传数据
load data local inpath '/usr/local/soft/bigdata30/fraction.txt' into table t_fraction;
聚合开窗函数
sum(求和)
min(最小)
max(最大)
avg(平均值)
count(计数)
lag(获取当前行上一行的数据）
select *,sum(score) over(partition by name) from t_fraction;
+------------------+---------------------+-------------------+---------------+
| t_fraction.name | t_fraction.subject | t_fraction.score | sum_window_0 |
+------------------+---------------------+-------------------+---------------+
| 唐玄奘 | 英语 | 23 | 125 |
| 唐玄奘 | 数学 | 81 | 125 |
| 唐玄奘 | 语文 | 21 | 125 |
| 孙悟空 | 英语 | 15 | 98 |
| 孙悟空 | 数学 | 73 | 98 |
| 孙悟空 | 语文 | 10 | 98 |
| 沙悟净 | 英语 | 31 | 123 |
| 沙悟净 | 数学 | 70 | 123 |
| 沙悟净 | 语文 | 22 | 123 |
| 猪八戒 | 英语 | 11 | 94 |
| 猪八戒 | 数学 | 73 | 94 |
| 猪八戒 | 语文 | 10 | 94 |
+------------------+---------------------+-------------------+---------------+
select name,subject,score,sum(score) over() as sumover from t_fraction;
+-------+----------+--------+----------+
| name | subject | score | sumover |
+-------+----------+--------+----------+
| 唐玄奘 | 英语 | 23 | 440 |
| 唐玄奘 | 数学 | 81 | 440 |
| 唐玄奘 | 语文 | 21 | 440 |
| 沙悟净 | 英语 | 31 | 440 |
| 沙悟净 | 数学 | 70 | 440 |
| 沙悟净 | 语文 | 22 | 440 |
| 猪八戒 | 英语 | 11 | 440 |
| 猪八戒 | 数学 | 73 | 440 |
| 猪八戒 | 语文 | 10 | 440 |
| 孙悟空 | 英语 | 15 | 440 |
| 孙悟空 | 数学 | 73 | 440 |
| 孙悟空 | 语文 | 10 | 440 |
+-------+----------+--------+----------+
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;
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+----
| name | subject | score | sum1 | sum2 | sum3 | sum4 | sum5 | sum6 | sum6 sum7|
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+----
| 沙悟净 | 数学 | 70 | 440 | 297 | 70 | 70 | 70 | 143 | 143 | 297
| 猪八戒 | 数学 | 73 | 440 | 297 | 216 | 143 | 143 | 216 | 146 | 227
| 孙悟空 | 数学 | 73 | 440 | 297 | 216 | 216 | 146 | 227 | 154 | 154
| 唐玄奘 | 数学 | 81 | 440 | 297 | 297 | 297 | 154 | 154 | 81 | 81
| 猪八戒 | 英语 | 11 | 440 | 80 | 11 | 11 | 11 | 26 | 26 | 80
| 孙悟空 | 英语 | 15 | 440 | 80 | 26 | 26 | 26 | 49 | 38 | 69
| 唐玄奘 | 英语 | 23 | 440 | 80 | 49 | 49 | 38 | 69 | 54 | 54
| 沙悟净 | 英语 | 31 | 440 | 80 | 80 | 80 | 54 | 54 | 31 | 31
| 猪八戒 | 语文 | 10 | 440 | 63 | 20 | 10 | 10 | 20 | 20 | 63
| 孙悟空 | 语文 | 10 | 440 | 63 | 20 | 20 | 20 | 41 | 31 | 53
| 唐玄奘 | 语文 | 21 | 440 | 63 | 41 | 41 | 31 | 53 | 43 | 43
| 沙悟净 | 语文 | 22 | 440 | 63 | 63 | 63 | 43 | 43 | 22 | 22
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+----
between and
rows：行
unbounded preceding：起点
unbounded following：终点
n preceding：前 n 行
n following：后 n 行
current row：当前行
lag 往前第n行数据
lead 往后第n行数据
rows必须跟在Order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量。
**OVER()：**指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。
**CURRENT ROW：**当前行
**n PRECEDING：**往前n行数据
**n FOLLOWING：**往后n行数据
**UNBOUNDED：**起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点
**LAG(col,n,default_val)：**往前第n行数据，col是列名，n是往上的行数，当第n行为null的时候取default_val
**LEAD(col,n, default_val)：**往后第n行数据，col是列名，n是往下的行数，当第n行为null的时候取default_val
**NTILE(n)：**把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。
cume_dist（），计算某个窗口或分区中某个值的累积分布。假定升序排序，则使用以下公式确定累积分布：
小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中，x 等于 order by 子句中指定的列的当前行中的值。
聚合开窗函数实战：
实战1：Hive用户购买明细数据分析
创建表和加载数据
name，orderdate，cost
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
建表加载数据
vim business.txt
create table business
(
name string,
orderdate string,
cost int
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
load data local inpath "/usr/local/soft/bigdata30/business.txt" into table business;
实战1需求：
需求1：查询在2017年4月份购买过的顾客及总人数
# 分析：按照日期过滤、分组count求总人数
select name,orderdate,cost,count(1) over() as counts from business where month(orderdate)='04';
+-------+-------------+-------+---------+
| name | orderdate | cost | counts |
+-------+-------------+-------+---------+
| mart | 2017-04-13 | 94 | 5 |
| mart | 2017-04-11 | 75 | 5 |
| mart | 2017-04-09 | 68 | 5 |
| mart | 2017-04-08 | 62 | 5 |
| jack | 2017-04-06 | 42 | 5 |
+-------+-------------+-------+---------+
需求2：查询顾客的购买明细及月购买总额
# 分析：按照顾客分组、sum购买金额
select *,sum(cost) over(partition by name,month(orderdate)) as month_cost from business;
+----------------+---------------------+----------------+-------------+
| business.name | business.orderdate | business.cost | month_cost |
+----------------+---------------------+----------------+-------------+
| jack | 2017-01-05 | 46 | 111 |
| jack | 2017-01-08 | 55 | 111 |
| jack | 2017-01-01 | 10 | 111 |
| jack | 2017-02-03 | 23 | 23 |
| jack | 2017-04-06 | 42 | 42 |
| mart | 2017-04-13 | 94 | 299 |
| mart | 2017-04-11 | 75 | 299 |
| mart | 2017-04-09 | 68 | 299 |
| mart | 2017-04-08 | 62 | 299 |
| neil | 2017-05-10 | 12 | 12 |
| neil | 2017-06-12 | 80 | 80 |
| tony | 2017-01-04 | 29 | 94 |
| tony | 2017-01-02 | 15 | 94 |
| tony | 2017-01-07 | 50 | 94 |
+----------------+---------------------+----------------+-------------+
需求3：上述的场景,要将cost按照日期进行累加
# 分析：按照顾客分组、日期升序排序、组内每条数据将之前的金额累加
select *,sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) from business;
+----------------+---------------------+----------------+---------------+
| business.name | business.orderdate | business.cost | sum_window_0 |
+----------------+---------------------+----------------+---------------+
| jack | 2017-01-01 | 10 | 10 |
| jack | 2017-01-05 | 46 | 56 |
| jack | 2017-01-08 | 55 | 111 |
| jack | 2017-02-03 | 23 | 134 |
| jack | 2017-04-06 | 42 | 176 |
| mart | 2017-04-08 | 62 | 62 |
| mart | 2017-04-09 | 68 | 130 |
| mart | 2017-04-11 | 75 | 205 |
| mart | 2017-04-13 | 94 | 299 |
| neil | 2017-05-10 | 12 | 12 |
| neil | 2017-06-12 | 80 | 92 |
| tony | 2017-01-02 | 15 | 15 |
| tony | 2017-01-04 | 29 | 44 |
| tony | 2017-01-07 | 50 | 94 |
需求4：查询顾客上次的购买时间
·# 分析：查询出明细数据同时获取上一条数据的购买时间(肯定需要按照顾客分组、时间升序排序)
select name,orderdate,cost,nvl(lag(orderdate,1) over(partition by name order by orderdate),'本月第一次消费') as last_time from business;
+-------+-------------+-------+-------------+
| name | orderdate | cost | last_time |
+-------+-------------+-------+-------------+
| jack | 2017-01-01 | 10 | 本月第一次消费 |
| jack | 2017-01-05 | 46 | 2017-01-01 |
| jack | 2017-01-08 | 55 | 2017-01-05 |
| jack | 2017-02-03 | 23 | 2017-01-08 |
| jack | 2017-04-06 | 42 | 2017-02-03 |
| mart | 2017-04-08 | 62 | 本月第一次消费 |
| mart | 2017-04-09 | 68 | 2017-04-08 |
| mart | 2017-04-11 | 75 | 2017-04-09 |
| mart | 2017-04-13 | 94 | 2017-04-11 |
| neil | 2017-05-10 | 12 | 本月第一次消费 |
| neil | 2017-06-12 | 80 | 2017-05-10 |
| tony | 2017-01-02 | 15 | 本月第一次消费 |
| tony | 2017-01-04 | 29 | 2017-01-02 |
| tony | 2017-01-07 | 50 | 2017-01-04 |
+-------+-------------+-------+-------------+
需求5：查询前20%时间的订单信息
分析：按照日期升序排序、取前20%的数据 5分之一
select t1.name,t1.orderdate,t1.cost from (select name,orderdate,cost,ntile(5) over(order by orderdate) as n from business) t1 where t1.n=1;
排序开窗函数(重点)
- RANK() 排序相同时会重复，总数不会变
- DENSE_RANK() 排序相同时会重复，总数会减少
- ROW_NUMBER() 会根据顺序计算
- PERCENT_RANK()计算给定行的百分比排名。可以用来计算超过了百分之多少的人(当前行的rank值-1)/(分组内的总行数-1)
select name,subject,
score,
rank() over(partition by subject order by score desc) rp, -- 跳号
dense_rank() over(partition by subject order by score desc) drp,-- 不跳
row_number() over(partition by subject order by score desc) rnp, -- 顺序排
percent_rank() over(partition by subject order by score desc) as percent_rank
from t_fraction;
+-------+----------+--------+-----+------+------+---------------------+
| name | subject | score | rp | drp | rnp | percent_rank |
+-------+----------+--------+-----+------+------+---------------------+
| 唐玄奘 | 数学 | 81 | 1 | 1 | 1 | 0.0 |
| 猪八戒 | 数学 | 73 | 2 | 2 | 2 | 0.3333333333333333 |
| 孙悟空 | 数学 | 73 | 2 | 2 | 3 | 0.3333333333333333 |
| 沙悟净 | 数学 | 70 | 4 | 3 | 4 | 1.0 |
| 沙悟净 | 英语 | 31 | 1 | 1 | 1 | 0.0 |
| 唐玄奘 | 英语 | 23 | 2 | 2 | 2 | 0.3333333333333333 |
| 孙悟空 | 英语 | 15 | 3 | 3 | 3 | 0.6666666666666666 |
| 猪八戒 | 英语 | 11 | 4 | 4 | 4 | 1.0 |
| 沙悟净 | 语文 | 22 | 1 | 1 | 1 | 0.0 |
| 唐玄奘 | 语文 | 21 | 2 | 2 | 2 | 0.3333333333333333 |
| 猪八戒 | 语文 | 10 | 3 | 3 | 3 | 0.6666666666666666 |
| 孙悟空 | 语文 | 10 | 3 | 3 | 4 | 0.6666666666666666 |
+-------+----------+--------+-----+------+------+---------------------+
实战2：Hive分析学生成绩信息
创建表语加载数据
name subject score
李毅 语文 87
李毅 数学 95
李毅 英语 68
黄仙 语文 94
黄仙 数学 56
黄仙 英语 84
小虎 语文 64
小虎 数学 86
小虎 英语 84
许文客 语文 65
许文客 数学 85
许文客 英语 78
建表加载数据
vim score.txt
create table score2
(
name string,
subject string,
score int
) row format delimited fields terminated by "\t";
load data local inpath '/usr/local/soft/bigdata30/score.txt' into table score2;
需求1：每门学科学生成绩排名(是否并列排名、空位排名三种实现)
分析：学科分组、成绩降序排序、按照成绩排名
select name,subject,score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from
score;
需求2：每门学科成绩排名top 2的学生
select t1.name,t1.subject,t1.score from (select name,subject,score,row_number() over(partition by subject order by score desc) as rn from score2) t1 where t1.rn<3;
+----------+-------------+-----------+
| t1.name | t1.subject | t1.score |
+----------+-------------+-----------+
| 李毅 | 数学 | 95 |
| 小虎 | 数学 | 86 |
| 小虎 | 英语 | 84 |
| 黄仙 | 英语 | 84 |
| 黄仙 | 语文 | 94 |
| 李毅 | 语文 | 87 |
+----------+-------------+-----------+
Hive 行转列
lateral view explode
create table testArray2(
name string,
weight array<string>
)row format delimited
fields terminated by '\t'
COLLECTION ITEMS terminated by ',';
小虎 "150","170","180"
火火 "150","180","190"
select name,col1 from testarray2 lateral view explode(weight) t1 as col1;
小虎 150
小虎 170
小虎 180
火火 150
火火 180
火火 190
select key from (select explode(map('key1',1,'key2',2,'key3',3)) as (key,value)) t;
key1
key2
key3
select name,col1,col2 from testarray2 lateral view explode(map('key1',1,'key2',2,'key3',3)) t1 as col1,col2;
小虎 key1 1
小虎 key2 2
小虎 key3 3
火火 key1 1
火火 key2 2
火火 key3 3
select name,pos,col1 from testarray2 lateral view posexplode(weight) t1 as pos,col1;
小虎 0 150
小虎 1 170
小虎 2 180
火火 0 150
火火 1 180
火火 2 190
Hive 列转行 collect_list
// testLieToLine
name col1
小虎 150
小虎 170
小虎 180
火火 150
火火 180
火火 190
create table testLieToLine(
name string,
col1 int
)row format delimited
fields terminated by '\t';
select name,collect_list(col1) from testLieToLine group by name;
// 结果
小虎 ["150","180","190"]
火火 ["150","170","180"]
select t1.name
,collect_list(t1.col1)
from (
select name
,col1
from testarray2
lateral view explode(weight) t1 as col1
) t1 group by t1.name;
Hive自定义函数UserDefineFunction
UDF：一进一出
定义UDF函数要注意下面几点:
- 继承
org.apache.hadoop.hive.ql.exec.UDF- 重写
evaluate()，这个方法不是由接口定义的,因为它可接受的参数的个数,数据类型都是不确定的。Hive会检查UDF,看能否找到和函数调用相匹配的evaluate()方法
- 创建maven项目，并加入依赖
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>1.2.1</version>
</dependency>
打包的时候可能会出现错误
Could not transfer artifact org.pentaho:pentaho-aggdesigner-algorithm:pom:5.1.5-jhyde
解决方案：
在pom文件中修改hive-exec的配置
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<exclusions>
<!--排除pentaho-aggdesigner-algorithm依赖，不将它引入-->
<exclusion>
<groupId>org.pentaho</groupId>
<artifactId>pentaho-aggdesigner-algorithm</artifactId>
</exclusion>
</exclusions>
</dependency>
- 编写代码，继承org.apache.hadoop.hive.ql.exec.UDF，实现evaluate方法，在evaluate方法中实现自己的逻辑
public class MyUDFDemo1 extends UDF {
/*todo:
* hive自定义函数（老版本的写法）
* 最简单的实现方法
* 将自定义的类变成hiveUDF函数类 继承UDF类
* 编写evaluate方法 确定参数类型和返回值类型 对应sql中的字段输入类型和结果类型
* evaluate方法可以发生重载，根据传参的字段数据类型，来决定调用哪一个方法
* 若数据类型相同，方法不同 只能重新新建一个类
* */
public String evaluate(String s) {
return "员工：" + s;
}
}
// 上传jar包 新建存放jar包目录
- 打成jar包并上传至Linux虚拟机
- 在hive shell中，使用
add jar 路径将jar包作为资源添加到hive环境中
add jar /usr/local/soft/bigdata30/jars/HIve-1.0-SNAPSHOT-jar-with-dependencies.jar;
- 使用jar包资源注册一个临时函数，fxxx1是你的函数名，'MyUDF’是主类名
create temporary function fxxx1 as 'MyUDF';
-- create temporary function yy as 'com.shujia.custom.MyUDFDemo1';
- 使用函数名处理数据
select yy(ename) from emp;
+------------+
| _c0 |
+------------+
| 员工：SMITH |
| 员工：ALLEN |
| 员工：WARD |
| 员工：JONES |
| 员工：MARTIN |
| 员工：BLAKE |
| 员工：CLARK |
| 员工：SCOTT |
| 员工：KING |
| 员工：TURNER |
| 员工：ADAMS |
| 员工：JAMES |
| 员工：FORD |
| 员工：MILLER |
+------------+
-- 注意 退出客户端后 失效
创建永久函数
将jar上传HDFS：
hadoop fs -put hive-udf-1.0.jar /bigdata30/hive_jars
在hive命令行中创建永久函数：
create function yy as 'com.shujia.custom.MyUDFDemo1' using jar 'hdfs:/bigdata30/hive_jars/hive-udf-1.0.jar';
查看函数
show functions;
使用同上
退出hive，再进入，执行测试：
函数依旧存在
删除永久函数，并检查：
Hive新版本 自定义UDF函数写法
/**
* 设计模式：
* 简单工厂模式
* 工厂模式
* 单例模式
* - 懒汉式 面试的时候说懒汉式（线程安全的问题）
* - 饿汉式 开发的时候写饿汉式
*/
public class MyGenericUDFDemo1 extends GenericUDF {
/*
这个方法主要是对自定义的UDF函数进行初始化，目的是指定调用完函数返回的值的类型
需求：传入一个字符串，返回一个新的字符串
举例：SMITH --> 数加：SMITH
*/
@Override
public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
//使用PrimitiveObjectInspectorFactory工厂类，获取String类型的ObjectInspector
return PrimitiveObjectInspectorFactory.javaStringObjectInspector;
}
/*
该方法是自定义UDF的核心方法，目的是实现自定义UDF的逻辑
是在initialize方法之后执行的
arguments将来会有多个参数，但是UDF函数只有一个参数，所以arguments[0]就是传入的第一个参数
*/
@Override
public Object evaluate(DeferredObject[] arguments) throws HiveException {
String out = "";
DeferredObject argument = arguments[0]; // 可能是ename, 也可能是sal
//DeferredObject存储的是传入函数的元素
Object o = argument.get(); // Object o = ""
//因为新版本中只有一个evaluate方法可以调用，无法重载多个
//因为hive的数据是在来自于HDFS的，读取数据的方式都是MR的方式的读取的
//这里的数据类型是hadoop中的数据类型
// Text -- String
// IntWritable -- Integer
// out = o.getClass().getName();
//instanceof 判断左边的对象是否是右边的类的对象或者类的子类对象
if (o instanceof LazyString) {
out = "员工: " + o;
} else if (o instanceof LazyInteger) {
//向上转型
//父 f = new 子();
//向下转型
//子 z = (子)f;
//向下转型，才可以使用对应真实数据类型的功能
LazyInteger i = (LazyInteger) o;
int sal = i.getWritableObject().get();
if (sal < 2000) {
out = "低薪";
} else if (sal < 3000) {
out = "中等薪资";
} else {
out = "高薪资";
}
}
return out;
}
@Override
public String getDisplayString(String[] children) {
return "这是我们自己使用新版本写法自定义的UDF函数";
}
}
函数加载方式
命令加载
这种加载只对本session有效
# 1、将项目打包上传服务器：将打好的jar包传到linux系统中。(不要打依赖)
# 进入到hive客户端,执行下面命令
hive> add jar /usr/local/soft/bigdata17/data/xiaohu/hadoop-mapreduce-1.0-SNAPSHOT.jar
# 2、创建一个临时函数名,要跟上面hive在同一个session里面：
hive> create temporary function toUP as 'com.shujia.testHiveFun.udf.FirstUDF';
3、检查函数是否创建成功
show functions;
4. 测试功能
select toUp('abcdef');
5. 删除函数
drop temporary function if exists toUp;
UDTF：一进多出
UDTF是一对多的输入输出,实现UDTF需要完成下面步骤
M1001#xiaohu#S324231212,lkd#M1002#S2543412432,S21312312412#M1003#bfy
1001 xiaohu 324231212
1002 lkd 2543412432
1003 bfy 21312312412
继承org.apache.hadoop.hive.ql.udf.generic.GenericUDTF，
重写initlizer（）、process（）、close()。
执行流程如下:UDTF首先会调用initialize方法，此方法返回UDTF的返回行的信息（返回个数，类型）。
初始化完成后，会调用process方法,真正的处理过程在process函数中，在process中，每一次forward()调用产生一行；如果产生多列可以将多个列的值放在一个数组中，然后将该数组传入到forward()函数。
最后close()方法调用，对需要清理的方法进行清理。
“key1:value1,key2:value2,key3:value3”
key1 value1
key2 value2
key3 value3
自定义UDTF代码：
package com.shujia.custom.udtf;
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.PrimitiveObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import java.util.ArrayList;
import java.util.Collections;
/*
* 实现GenericUDTF的两个方法
*
*
* */
public class MyUDTFDemo1 extends GenericUDTF {
/*
* 初始化，可以在这个方法中创建一些连接对象
指定返回的列数据类型和列名
*
* */
@Override
public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
//创建两个集合，这两个集合是一一对应的关系
//一个是列名的集合
ArrayList<String> nameList = new ArrayList<>();
// Collections.synchronizedCollection(nameList); 线程安全
nameList.add("id");
nameList.add("name");
nameList.add("card");
// 另一个是列数据类型的集合
ArrayList<ObjectInspector> typesList = new ArrayList<>();
typesList.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
typesList.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
typesList.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
// 将列名集合和列数据类型集合对应返回
return ObjectInspectorFactory.getStandardStructObjectInspector(nameList,typesList);
}
/*
* 核心方法
* 将传进的一行数据，进行处理，返回
*
*
* */
@Override
public void process(Object[] args) throws HiveException {
//创建一个数组，接收每次的一行三列数据
String[] strings = new String[3];
//args：接收传进的一行多列数据，每一列是数组的一个元素
//M1001#xiaohu#S324231212,lkd#M1002#S2543412432,S21312312412#M1003#bfy
String info = args[0].toString();
String[] infos = info.split(",");
for (String i : infos) {
String[] iInfos = i.split("#");
for (String iInfo : iInfos) {
if (iInfo.startsWith("M")) {
strings[0] = iInfo.substring(1);
}else if(iInfo.startsWith("S")){
strings[2] = iInfo.substring(1);
}else {
strings[1] = iInfo;
}
//调用收集器方法，将处理好的一行数据进行返回
// 受保护的可以调用
forward(strings);
}
}
}
// 关闭一些在initialize方法中创建的对象
@Override
public void close() throws HiveException {
}
}
打包上传jar资源:
add jar /usr/local/soft/jars/hive-1.0.jar
注册udtf函数：
create temporary function my_udtf as 'com.shujia.custom.udtf.MyUDTFDemo1';
SQL:
select my_udtf('M1001#xiaohu#S324231212,lkd#M1002#S2543412432,S21312312412#M1003#bfy');
案列二：
字段：id,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12 共13列
第一列是检测点编号 后12列的每隔两小时检测的值
转成3列：id,hours,value
呈现的效果：
例如：
a,1,2,3,4,5,6,7,8,9,10,11,12
a,0时,1
a,2时,2
a,4时,3
a,6时,4
…
建表语句
create table udtfData(
id string
,col1 string
,col2 string
,col3 string
,col4 string
,col5 string
,col6 string
,col7 string
,col8 string
,col9 string
,col10 string
,col11 string
,col12 string
)row format delimited fields terminated by ',';
vim udtfdata.txt
数据：
a,1,2,3,4,5,6,7,8,9,10,11,12
b,11,12,13,14,15,16,17,18,19,20,21,22
c,21,22,23,24,25,26,27,28,29,30,31,32
-- 加载数据
load data local inpath 'usr/local/soft/bigdata30/udtfdata.txt' into table udtfData;
编写UDTF代码：
package com.shujia.custom.udtf;
import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;
import java.util.ArrayList;
public class MyUDTFDemo2 extends GenericUDTF {
@Override
public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
//创建两个集合，这两个集合是一一对应的关系
//一个是列名的集合
ArrayList<String> nameList = new ArrayList<>();
// Collections.synchronizedCollection(nameList); 线程安全
nameList.add("id");
nameList.add("time");
nameList.add("value");
// 另一个是列数据类型的集合
ArrayList<ObjectInspector> typesList = new ArrayList<>();
typesList.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
typesList.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
typesList.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
// 将列名集合和列数据类型集合对应返回
return ObjectInspectorFactory.getStandardStructObjectInspector(nameList, typesList);
}
@Override
public void process(Object[] args) throws HiveException {
String[] infos = new String[3];
String id = args[0].toString();
infos[0] = id;
int t = 0;
for (int i = 1; i < args.length; i++) {
infos[1] = t + "时";
infos[2] = args[i].toString();
forward(infos);
t += 2;
}
}
@Override
public void close() throws HiveException {
}
}
打包上传jar资源:
add jar /usr/local/soft/jars/hive-1.0.jar
注册udtf函数：
create temporary function my_udtf as 'com.shujia.custom.udtf.MyUDTFDemo2';
SQL:
select my_udtf(*) from udtfData;
UDAF：多进一出
Hive Shell
直接进入Hive客户端
第一种：
hive -e "select * from test1.students limit 10"
第二种：
hive -f hql文件路径
将HQL写在一个文件里，再使用 -f 参数指定该文件
连续登陆问题
在电商、物流和银行可能经常会遇到这样的需求：
统计用户连续交易的总额、连续登陆天数、连续登陆开始和结束时间、间隔天数等
数据：
注意：每个用户每天可能会有多条记录
id datestr amount
1,2019-02-08,6214.23
1,2019-02-08,6247.32
1,2019-02-09,85.63
1,2019-02-09,967.36
1,2019-02-10,85.69
1,2019-02-12,769.85
1,2019-02-13,943.86
1,2019-02-14,538.42
1,2019-02-15,369.76
1,2019-02-16,369.76
1,2019-02-18,795.15
1,2019-02-19,715.65
1,2019-02-21,537.71
2,2019-02-08,6214.23
2,2019-02-08,6247.32
2,2019-02-09,85.63
2,2019-02-09,967.36
2,2019-02-10,85.69
2,2019-02-12,769.85
2,2019-02-13,943.86
2,2019-02-14,943.18
2,2019-02-15,369.76
2,2019-02-18,795.15
2,2019-02-19,715.65
2,2019-02-21,537.71
3,2019-02-08,6214.23
3,2019-02-08,6247.32
3,2019-02-09,85.63
3,2019-02-09,967.36
3,2019-02-10,85.69
3,2019-02-12,769.85
3,2019-02-13,943.86
3,2019-02-14,276.81
3,2019-02-15,369.76
3,2019-02-16,369.76
3,2019-02-18,795.15
3,2019-02-19,715.65
3,2019-02-21,537.71
建表语句
create table deal_tb(
id string
,datestr string
,amount string
)row format delimited fields terminated by ',';
-- 加载数据
计算步骤
1.将用户一天多次消费的数据进行合并
select id,datestr,sum(amount) as amount from deal_tb group by id,datestr;
2.以用户id开窗，以日期升序，开始编号
select
t1.*,
row_number() over(partition by t1.id order by t1.datestr) as rn
from (select
id,
datestr,
sum(amount) as amount
from
deal_tb
group by id,datestr) t1;
3.将日期与开窗编号相减，如果结果是一样的，那么就可以说明这几条数据是连续的
select
t2.*,
date_sub(t2.datestr,rn) as grp
from
(select
t1.*,
row_number() over(partition by t1.id order by t1.datestr) as rn
from (select
id,
datestr,
sum(amount) as amount
from
deal_tb
group by id,datestr) t1
) t2;
4.统计用户连续交易的总额、连续登陆天数、连续登陆开始和结束时间
select
t3.id as id,
min(t3.datestr) as start_time, --连续登陆开始时间
max(t3.datestr) as end_time, -- 结束时间
sum(t3.amount) as sum_amount, --连续交易的总额
count(1) as days, -- 连续登陆天数
from
(select
t2.*,
date_sub(t2.datestr,rn) as grp
from
(select
t1.*,
row_number() over(partition by t1.id order by t1.datestr) as rn
from (select
id,
datestr,
sum(amount) as amount
from
deal_tb
group by id,datestr) t1
) t2 ) t3 group by t3.id,t3.grp;
- 间隔天数（这一行的start_time 减去上一行的end_time，再减去1）
select
t4.*,
nvl(datediff(start_time,lag(end_time,1)over(partition by id order by start_time))-1,0) as jiange -- 间隔天数
from
(select
t3.id as id,
min(t3.datestr) as start_time, --连续登陆开始时间
max(t3.datestr) as end_time, -- 结束时间
sum(t3.amount) as sum_amount, --连续交易的总额
count(1) as days, -- 连续登陆天数
from
(select
t2.*,
date_sub(t2.datestr,rn) as grp
from
(select
t1.*,
row_number() over(partition by t1.id order by t1.datestr) as rn
from (select
id,
datestr,
sum(amount) as amount
from
deal_tb
group by id,datestr) t1
) t2 ) t3 group by t3.id,t3.grp) t4;
- 结果
Hive优化
1.1 hive的随机抓取策略
理论上来说，Hive中的所有sql都需要进行mapreduce，但是hive的抓取策略帮我们
省略掉了这个过程，把切片split的过程提前帮我们做了。
set hive.fetch.task.conversion=none;
(一旦进行这么设置，select字段名也是需要进行mapreduce的过程，默认是more)
Fetch抓取的模式
可以通过 set hive.fetch.task.conversion查看，有以下3种模式：
none：所有涉及hdfs的读取查询都走mapreduce任务；
mininal：在进行简单的select *，简单的过滤或涉及分区字段的过滤时走mr；
more:在mininal模式的基础上，增加了针对查询语句字段进行一些别名的计算操作。
以下HQL，mininal模式与more模式下都不会走mr任务:
SELECT
sale_ord_id,
store_id
FROM
test_table
where
dt = '2021-01-01'
limit 10;
以下HQL,mininal模式会走mr任务，more模式不会：
SELECT
sale_ord_id,
store_id,
if(store_id > 20,1,0) as store_id_new
FROM
test_table
where
dt = '2021-01-01'
limit 10;
查看怎么将一个sql转化成一个MR任务的
explain sql语句
例如：
explain select count() from stu_dy1_1;
更加详细的查看，例如：
**explain extended select count() from students2;**
当你输入一个sql语句的时候，hive会将对其关键字进行截串，截完串之后，变成
都是一些TOK开头的一些东西，然后经过这样的抽象语法树，再转成具体的查询块，
最后变成逻辑查询计划（低版本HIve）
1.2 本地运行模式
大多数的 Hadoop Job 是需要 Hadoop 提供的完整的可扩展性来处理大数据集的。不过，
有时 Hive 的输入数据量是非常小的。在这种情况下，为查询触发执行任务消耗的时间可能
会比实际 job 的执行时间要多的多。对于大多数这种情况， Hive 可以通过本地模式在单台机
器上处理所有的任务。对于小数据集，执行时间可以明显被缩短。
用户可以通过设置 hive.exec.mode.local.auto 的值为 true ，来让 Hive 在适当的时候自动
启动这个优化。
本地模式运行比集群模式块很多，33秒的任务降到2秒
更改为本地模式：
hive> set hive.exec.mode.local.auto=true
注意：
hive> set hive.exec.mode.local.auto.inputbytes.max=134217728 ---> 128M
（默认值就是128）
表示加载文件的最大值，若大于该配置仍然会以集群的方式去运行。
97万行数据，50MB
当我们开发或者测试阶段，可以去使用本地模式进行运行，默认是集群模式
但是，这里有个问题，当我们去更改为本地模式的时候，在8088的页面上就看不到
任务的执行情况了。
测试：select count(*) from emp group by deptno;
1.3 并行计算
通过设置以下参数开启并行模式（默认是false）
set hive.exec.parallel=true;
注意：hive.exec.parallel.thread.number
(一次SQl计算中允许并行执行的job个数最大值，默认是8个)
举例：
select t1.n1,t2.n2 from (select count(ename) as n1 from emp) t1,(select count(dname) as n2 from dept) t2;
注意，有时候开启并行计算运行时间并没有不开启的快，那是因为，资源的问题。
需要两套资源，资源申请会浪费点时间，最多可以并行8个，默认是8个。
所以，并行的越多，不一定是越快，因为它涉及到一个资源申请的策略。
1.4 严格模式(理解为增加一些限制)
1.什么是Hive的严格模式
hive中的一种模式,在该模式下禁止一些不好SQL的执行。
2.Hive的严格模式不允许哪些SQL执行
2.1 禁止分区表全表扫描
分区表往往数据量大,如果不加分区查询会带来巨大的资源消耗 。例如以下分区表
SELECT DISTINCT(planner_id) FROM fracture_ins WHERE planner_id=5;
报错如下:
FAILED: Error in semantic analysis: No Partition Predicate Found for Alias “fracture_ins” Table "fracture_ins
解决如下:
SELECT DISTINCT(planner_id) FROM fracture_ins WHERE planner_id=5 AND hit_date=20120101;
2.2 禁止排序不加limit
排序最终是要都进到一个Reduce中操作,防止reducer额外执行很长一段时间
SELECT * FROM fracture_ins WHERE hit_date>2012 ORDER BY planner_id;
出现如下错误
FAILED: Error in semantic analysis: line 1:56 In strict mode,limit must be specified if ORDER BY is present planner_id
解决方案就是增加一个limit关键字：
hive> SELECT * FROM fracture_ins WHERE hit_date>2012 ORDER BY planner_id LIMIT 100000;
2.3 禁止笛卡尔积
笛卡尔积是什么: A={a,b}, B={0,1,2}，则 A×B={(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}
SELECT * FROM fracture_act JOIN fracture_ads;
解决方法
SELECT * FROM fracture_act JOIN fracture_ads WHERE fracture_act.planner_id = fracture_ads.planner_id;
3.Hive的严格模式怎样开启
// 查看当前严格模式的状态
set hive.mapred.mode;
// 设置为严格模式
set hive.mapred.mode=strict;
// 设置为非严格模式
set hive.mapred.mode=nonstrict;
注意，这里的严格模式和动态分区的那个严格模式半毛钱关系没有）
通过设置以下参数开启严格模式：
set hive.mapred.mode=strict;
(默认为：nonstrict非严格模式)
查询限制：
1、对于分区表，必须添加where对于分区字段的条件过滤
2、order by 语句必须包含limit输出限制
3、限制执行笛卡尔积的查询
这些限制是帮助我们提高查询效率的。
1.5 **Hive排序(掌握distribute by和sort by) **
order by 对于查询结果做全排序，只允许有一个reduce处理
（注意：它会把我们所有的字段或者查询结果全部放在一个reduce里进行处理
当数据量较大时候，有可能reduce执行不完，所以，我们以后把这个给弃用掉）
** sort by 对于单个reduce进行排序 但是我们将每个reduce里面进行排序，没有考虑到
每个reduce之间的排序。所以我们引出下一个
** distribute by 分区排序，通常结合sort by一起使用
（distribute by column sort by column asc|desc）
cluster by 相当于distribute by + sort by (注意，虽然是两个结合，但是我们也不去用它
原因很简单，cluster by不能通过asc desc的方式指定排序方式规则)
1.6 Hive join数据倾斜
1、小表join小表 不管他
2、小表join大表 map-join
3、大表join大表 map-side
考虑会不会发生reduce,并且考虑reduce压力是否大（是否会出现某个reduce数据量庞大的情况）
join计算的时候，将小表（驱动表）放在join的左边
Map join：在Map端完成join
两种实现方式：
1、sql方式，在sql语句中添加Mapjoin标记（mapjoin hint）
语法糖
select /*+MAPJOIN(A)*/ * from A join B on (A.key=B.key);
语法：
>> select /*+MAPJOIN(smallTable)*/ smallTable.key bigTable.value from smallTable join bigTable on smallTable.key=bigTable.key;
2、自动开启mapjoin
通过修改以下配置启用自动的mapjoin：
set hive.auto.convert.join=true;
(注意：该参数为true的时候，Hive自动对左边的表统计量，如果
是小表，就加入到内存，即对小表使用Mapjoin)
相关配置参数
hive.mapjoin.smalltable.filesize;(默认25M,大表小表判断的阈值，如果表的大小小于该值则会被加载到内存中运行。)
hive.ignore,mapjoin.hint;(默认值：true;是否忽略mapjoin hint的标记)
hive.auto.convert.join.noconditionaltask;(默认值：true；将普通的join转换为mapjoin时，是否将多个mapjoin转化为一个mapjoin)
hive.auto.convert.join.noconditionaltask.size;(将多个mapjoin转化为一个mapjoin时，这个表的最大值)
3、尽可能使用相同的连接键，如果不同，多一个join就会多开启一个mapreduce，执行速度变得慢。
4、大表join大表（当两个都是大表的时候，只能发生reduce了，但是这里有两个优化策略）（面试的时候说，加分）
a: 空key过滤:
有时join超时是因为某些key对应的数据太多,而相同key对应的数据都会发送到相同的 reducer上,从而导致内存不够。
此时我们应该仔细分析这些异常的key,很多情况下,这些key对应的数据是异常数据,我们需要在SQL语句中进行过滤。
但是这个的前提条件是异常数据，但是我们一般拿到的数据都是经过ETL数据清洗过后的，一般影响不大，面试的时候可以说。
b: 空key转换:
有时虽然某个key为空对应的数据很多,但是相应的数据不是异常数据,必须要包含在join的结果中,
此时我们可以给表a中key为空的字段赋随机的值,使得数据随机均匀地分不到不同的 reducer上。(加盐)
但是我们一般拿到的数据都是经过ETL数据清洗过后的，规则数据，一般影响不大，面试的时候可以说。
5、Map-Side聚合
通过设置以下参数开启在Map端的聚合
set hive.map.aggr=true;（一定要进行开启，虽然进行了两个mapreduce，但是当数据倾斜发生的时候，很多时候会根本跑不出结果，卡死在99%或者100%，慢总比出不来结果要好）！！！！！！！
相关配置参数
hive. groupby mapaggr. checkinterval;
map端 igroup by执行聚合时处理的多少行数据(默认:10000
hive.map.aggr.hash.min.reduction;比例(若聚合之后的数据100大该0.5,map端聚合使用的内存的最大值
hive.mapaggr.hashforce.flush.memory.threshold;map端做聚合操作是has表的最大可用内容,大于该值则会触发fush
hive.groupby.skewindata-是否对 GroupBy产生的数据倾斜做优化,默认为false(十分重要！！！)
6、数据倾斜，尽可能地让我们的数据散列到不同的reduce里面去,负载均衡（Hbase中热点数据）
1.7 合并小文件
1、hadoop不适合存储小文件
2、MR不适合处理小文件
3、Hive不适合处理小文件
Hive优化
合并小文件
文件数目小,容易在文件存储端造成压力,给hdfs造成压力,影响效率
设置合并属性
是否合并map输出文件: hive.merge.mapfiles=true
是否合并reduce输出文件: hive.merge.mapredfiles=true
合并文件的大小: hive.merge.size.per.task=256*1000*1000
去重统计
数据量小的时候无所谓,数据量大的情况下,由于 COUNT DISTINCT操作需要用一个 Reduce Task来完成,
这一个 Reduce需要处理的数据量太大,就会导致整个JOb很难完成,一般 COUNT DISTINCT使用先 GROUP BY再COUNT的方式替换
1.8 控制map和reduce的数量(一般情况下我们不去动它)
控制Hive中Map以及 Reduce的数量
Map数量相关的参数
mapred.max.split.size;一个split的最大值,即每个map处理文件的最大值
mapred.min.split.size.per.node个节点上split的最小值
mapred.min.split.size.per.rack一个机架上spit的最小值
Reduce数量相关的参数
mapred.reduce.tasks;强制指定reduce任务的数量
hive.exec.reducers.bytes.per.reducer每个reduce任务处理的数据量
hive.exec.reducers.max每个任务最大的reduce数
1.9 JVM重用
当我们的小文件个数过多，task个数过多，需要申请的资源过多的时候，我们可以先申请一部分资源，全部执行完毕后再释放，
比我们申请一个释放一个要快。
通过 set mapred.job.reuse.jvm.num.tasks=n;来设置
（n为task插槽个数）
缺点：
设置开启后，task插槽会一直占用资源，无论是否有task进行，直到所有的task,
即整个job全部执行完毕后，才会释放所有的task插槽，所以我们要合理地设置这个n
(比如，我们设置申请了10个，但是现在来了6个，剩下4个插槽会在job全部执行完毕之前一直占用资源)
mapreduce叫懒加载，当执行任务需要资源的时候再去申请资源
扫一扫
923
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
