目录
9.3.1 distribute by + sort by 组合
9.3.2 partition by +order by组合
一.Hive的简介
1.1 出现的原因
facebook团队要管理和分析大量的结构化的日志文件，因为写mapreduce非常麻烦,所以该团队就开发出来一个框架，叫hive.1.2 hive是什么
hive是一款运行在hadoop上的数据仓库管理工具，可以将具有结构化的数据文件映射成一张数据表，使用类似sql的方式来对这种文件进行读、写、管理(包括元数据)等。这种类sql的语言，称之为Hive SQL,简称HQL。 实际上hive底层是mapreduce或者是spark或者是tez.
如果是mapreduce，则是将hql翻译成对应的mapreduce程序。1.3 Hive的优缺点
一、优点
1. 学习成本低
2. 扩展性好
3. 适合做离线分析(OLAP)
4. 容错性好(某一个节点宕机，hive依然可以完成)
5. 延展性好(用户可以自定义hive函数)
6. hive的元数据统一管理。
二、缺点：
1. hive的表达能力有限(特别复杂的算法难以实现)
3. hive的效率低(调优不方便，速度慢)二、hive的框架原理(嗯点)
2.1 hive的框架结构
1. 用户接口层: 就是供用户链接hive的接口，有三类。
- shell命令行接口
- jdbc/odbc接口
- webui接口
2. Thrift server：hive的可选组件(第三方组件)，供编程语言链接hive时使用。
3. metaStore：元数据库
用来存储hive的元数据的，元数据包括库名，表名，表字段，字段类型，表数据所在的目录等。
注意：hive在工作时，会访问元数据库，可能是读取元数据，也可能是更改元数据。
4. Driver: hive的驱动
- 解析器： 解析hql语法
- 编译器： 将没有问题的hql翻译成mr程序
- 优化器： 对翻译好的mr程序进行部分优化(比如去掉不必要的列等)
- 执行器： 提交mr程序job。2.2 hive的运行原理
1. 用户提交hql语句给Driver.
2. Driver的解析器校验hql,然后传给编译器，由编译器翻译成mr执行计划。
3. 编译器读取元数据库里的元数据
4. 编译器获取元数据后，再次完善mr执行计划，比如设置输入路径等。
5. 将完善后的mr执行计划交由driver。此时，整个解析和翻译过程结束。
6. driver将执行计划交给执行引擎
7. 执行引擎提交作业到yarn上，运行mr程序
8. 运算结果交由执行引擎。
9.执行引擎获取数据后，交给driver
10.driver将结果传递给用户。2.3 hive和hadoop的关系
hive就像是在hadoop上的一个壳子。 hive要处理的数据存储在hdfs/hbase上。hive的计算基于mapreduce/spark/tez2.4 Hive与关系型数据库的比较
- mysql用自己的存储存储引擎，hive使用的hdfs来存储。
- mysql使用自己的执行引擎，而hive使用的是mapreduce来执行。
- mysql使用环境几乎没有限制，hive是基于hadoop的。
- mysql的低延迟，hive是高延迟。
- mysql的handle的数据量较小，而hive的能handle数据量较大。
- mysql的可扩展性较低，而hive的扩展性较高。
- mysql的数据存储格式要求严格，而hive对数据格式不做严格要求。
- mysql可以允许局部数据插入、更新、删除等，而hive不支持局部数据的操作。三、hive的安装模式
3.1 内嵌模式的简介(忽略)
hive的元数据存储在自带的derby数据库中，就是内嵌模式。缺点，只支持单session。3.2 本地模式
3.2.1 简介
元数据库是关系型数据库mysql,hive的相关进程都在同一台机器上，与mysql在哪一台机器上无关。
注意：hive在启动客户端时，会内置的自动启动一个元数据服务项。3.2.2 安装步骤
准备工作. 安装好mysql(任意一台机器即可)步骤1：上传，解压，更名，配置环境变量
步骤2： 修改hive-env.sh
如果不存在,就用hive.env.sh.template复制一个
export HIVE_CONF_DIR=/usr/local/hive/conf
export JAVA_HOME=/usr/local/jdk
export HADOOP_HOME=/usr/local/hadoop
export HIVE_AUX_JARS_PATH=/usr/local/hive/lib步骤3： 修改hive-site.xml
hive2.1.1中默认是没有hive-site.xml,可以把conf/hive-default.xml.template拷贝过来使用
<!--配置mysql的连接字符串-->
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://xxx03:3306/hive?createDatabaseIfNotExist=true</value>
</property>
<!--配置mysql的连接驱动-->
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<!--配置登录mysql的用户-->
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<!--配置登录mysql的密码，注意，注意，注意，要用自己的远程密码-->
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>@Mmforu45</value>
</property>
<!-- 该参数主要指定Hive的数据存储目录 -->
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
</property>
<!-- 该参数主要指定Hive的临时文件存储目录 -->
<property>
<name>hive.exec.scratchdir</name>
<value>/tmp/hive</value>
</property>
<!-- 一些临时文件夹的配置 -->
<property>
<name>hive.exec.local.scratchdir</name>
<value>/usr/local/hive/iotmp</value>
</property>
<property>
<name>hive.downloaded.resources.dir</name>
<value>/usr/local/hive/iotmp/${hive.session.id}_resources</value>
</property>
<property>
<name>hive.querylog.location</name>
<value>/usr/local/hive/iotmp/root</value>
</property>
<property>
<name>hive.server2.logging.operation.log.location</name>
<value>/usr/local/hive/iotmp/root/operation_logs</value>
</property>
<!-- 是否显示当前数据库名-->
<property>
<name>hive.cli.print.current.db</name>
<value>true</value>
</property>
<!-- 该参数指定翻译出来的mr程序是使用yarn调度工具，还是本地OS调度工具,true表示local运行mr-->
<property>
<name>hive.exec.mode.local.auto</name>
<value>true</value>
</property>步骤4： 导入mysql的驱动包到lib目录下
步骤5：初始化元数据库
]# schematool -initSchema -dbType mysql步骤6： 使用hive指令运行客户端即可
]# hive3.3 远程模式
3.3.1 概念
hive的服务项进程必须单独启动，服务项有两个，一个是hiveserver2,一个是metastore。 启动哪一个都可以，注意启动hiveserver2时，其本质还是在内部启动了metastore3.3.2 hive的服务端的配置
<!--配置mysql的连接字符串-->
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://xxx03:3306/hive?createDatabaseIfNotExist=true</value>
</property>
<!--配置mysql的连接驱动-->
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<!--配置登录mysql的用户-->
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
</property>
<!--配置登录mysql的密码，注意，注意，注意，要用自己的远程密码-->
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>@Mmforu45</value>
</property>
<!-- 该参数主要指定Hive的数据存储目录 -->
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
</property>
<!-- 该参数主要指定Hive的临时文件存储目录 -->
<property>
<name>hive.exec.scratchdir</name>
<value>/tmp/hive</value>
</property>
<!-- 一些临时文件夹的配置 -->
<property>
<name>hive.exec.local.scratchdir</name>
<value>/usr/local/hive/iotmp</value>
</property>
<property>
<name>hive.downloaded.resources.dir</name>
<value>/usr/local/hive/iotmp/${hive.session.id}_resources</value>
</property>
<property>
<name>hive.querylog.location</name>
<value>/usr/local/hive/iotmp/root</value>
</property>
<property>
<name>hive.server2.logging.operation.log.location</name>
<value>/usr/local/hive/iotmp/root/operation_logs</value>
</property>
<!-- 是否显示当前数据库名-->
<property>
<name>hive.cli.print.current.db</name>
<value>true</value>
</property>
<!-- 该参数指定翻译出来的mr程序是使用yarn调度工具，还是本地OS调度工具,true表示local运行mr-->
<property>
<name>hive.exec.mode.local.auto</name>
<value>true</value>
</property>注意：如果使用远程模式连接服务项。那么必须修改hadoop的core-site.xml文件，追加以下两个属性
<property>
<name>hadoop.proxyuser.root.hosts</name>
<value>*</value>
</property>
<property>
<name>hadoop.proxyuser.root.groups</name>
<value>*</value>
</property>要scp到其他节点上，还要重启HDFS，使配置生效。
3.3.3 服务项的启动操作
启动hiveserver2:
方法1：
直接调用hiveserver2。会进入监听状态不退出。
方法2：
hive --service hiveserver2 & 进入后台启动
方法3：
hive --service hiveserver2 2>&1 >/dev/null &; #信息送入黑洞。
启动metastore：
方法1：
- hive --service metastore &
方法2：
- hive --service metastore 2>&1 >/dev/null &; #信息送入黑洞。3.3.4 客户端连接hiveserver2
如果启动的是hiveserver2服务项，必须使用beeline工具连接。
方式1：
step1. beeline 回车
step2. !connect jdbc:hive2://ip:10000 回车
step3. 输入用户名 回车
step4. 输入密码 回车
方法2(直连)：
1. beeline -u jdbc:hive2://ip:10000 -n 用户名
解析:
hive2，是hive的协议名称
ip: hiveserver2服务所在的主机IP。
10000，是hiveserver2的端口号3.3.5 客户端连接metastore
想要连接metastore服务的客户端必须配置如下属性和属性值
<property>
<name>hive.metastore.uris</name>
<value>thrift://ip:9083</value>
</property>
解析：thrift:是协议名称
ip为metastore服务所在的主机ip地址
9083是默认端口号配置完，直接使用hive指令即可
四、HIVE的库和表操作
命名规范
1. 名字可以由字母，数字等构成，数字不能开头
2. 不区分大小写
3. 不能使用关键字，不要使用特殊符号4.1 库操作
1）库的创建语法
create database dbName;
create database if not exists dbName;
create database [if not exists] dbName comment '备注信息';注意： hive的库的本质，是在hdfs上的一个目录，目录名为dbName.db
2) 库的查看语法
查看所有数据库：
show databases;
查看某一个数据库的信息
desc database dbName
desc database extended dbName;
显示创建语法
show create database dbName;3) 库的切换语法
use dbName4）库的删除语法
drop if database dbName; 只能删除没有表的空库
drop database dbName cascade; 可以强制删除有表的库4.2 表操作
1）常用的字段类型
int
float
double
string
varchar
char
date
timestamp2）表的创建语法（重点）
create table tableName(
fieldName1 Type,
fieldName1 Type,
....
);
create table dbName.tableName(
fieldName1 Type,
fieldName1 Type,
.....
);
create table [if not exists] tableName(
fieldName1 Type,
fiedlName2 Type comment '备注',
.......
)
comment 'this is my table' --表备注
row format delimited --规定输入规则,以及切分规则
fields terminated by '\t' --列分隔符，默认值是^A
lines terminated by '\n' --行分隔符，也是默认值
stored as textfile; --指定表对应的文件是什么存储格式，textfile，也是默认值注意：hive的表的本质，是在库目录下的子目录，目录名为表名。
3）表的查看语法
查看库里的所有的表
show tables;
查看某一个表的结构
desc tableName;
desc extended tableName;
查看建表语句
show create table tableName4）表的结构修改语法
- 修改表名
alter table oldTableName rename to newTableName;
- 修改列名、修改列类型是同一个语法
alter table tableName change column oldColumnName newColumnName [Type|newType]
注意：在修改类型时，不能将大类型转成小类型，可以使用小类型转向大类型。
- 修改列的位置: 注意，2.x版本后，必须是相同类型进行移动位置。
alter table tableName change column colName colName colType after colName1;
alter table tableName change column colName colName colType first;
- 增加字段：add columns
alter table tableName add columns (sex int,...);
- 删除字段：replace columns #注意，2.x版本后，注意类型的问题，替换操作，其实涉及到位置的移动问题。
alter table tableName replace columns(
id int,
name int,
size int,
pic string
);
注意：实际上是保留小括号内的字段。5）表的删除语法
drop table [if exists] tableName;4.3 数据的导入（重点）
方式1）使用hdfs上的上传指令
在知道表的位置的基础上，使用hdfs dfs -put 本地文件 表路径方式2）使用hive命令上的load指令
load data [local] inpath '具体路径' [overwrite] into table tableName;注意： 实际上hive的load指令底层使用的就是hdfs的上传或移动。
方式3）基于别的表进行查询插入
insert into tablename select .... from tableName1 [where conditions]
扩展内容：向多张表中插入数据的语法
from tableName1
insert into tableName2 select * where 条件
insert into tableName3 select * where 条件
.....方式4）表的克隆
-- 克隆表极其表数据
create table [if not exists] tablename as select .... from ....;
create table [if not exists] tablename like tablename1 location 'tablename1的路径'
-- 只克隆表结构
create table [if not exists] tablename like tablename1;4.4 案例演示
create table flow(
id string COMMENT 'this is id column',
phonenumber string,
mac string,
ip string,
url string,
urltype string,
uppacket int,
downpacket int,
upflow int,
downflow int,
issuccess int
)
row format delimited
fields terminated by "\t"
lines terminated by "\n"
stored as textfile;需求：统计每个手机的总上行，总下行，总流量
select phonenumber,sum(upflow),sum(downflow),sum(upflow+downflow)
from flow
group by phonenumber;4.5 数据的导出
4.5.1 将hive的表的数据导出到目录下
-- 可以导出到本地文件系统的目录下
insert overwrite local directory '本地目录' select .... from tablename;
-- 可以导出到hdfs系统的目录下
insert overwrite directory 'hdfs目录' select .... from tablename;注意：导出的数据文件里字段没有分开，使用的是默认分隔符"^A";
可以自定义分隔符，语法如下：
insert overwrite [local] directory '目录' row format delimited fields terminated by '分隔符'
select .... from tablename;4.5.2 将hive的表的数据导入到指定文件中
[root@xxx01 ~]# hive -e e 'DQL' >> '文件路径'
注意：默认分隔符为制表符‘\t’五、hive的shell技巧(熟悉)
在配置文件中，配置显示当前数据库和启用mr的本地运行模式
<property>
<name>hive.cli.print.current.db</name>
<value>true</value>
</property>
<property>
<name>hive.exec.mode.local.auto</name>
<value>true</value>
</property>
其他内容，参考文档六、表类型的详解（重点）
6.1 表的分类
表根据用途来划分为两种类型，一种是管理表(也叫内部表)，一种是外部表。
管理表
-- 也叫内部表
-- 表的数据存在属性${hive.metastore.warehouse.dir}指定的目录下的相应的库下的与表名相同的子目录下
-- 普通的创建语法创建出来的就是管理表外部表
-- 建表时必须使用关键字"external"
create external table tablename (.....) .... [location .....]
-- 可以使用location关键字来指定外部表的数据的具体位置，如果不使用该关键字，和管理表的位置相似。6.2 表类型的转换
内部表转成外部表
alter table tablename set tblproperties('EXTERNAL'='TRUE');外部表转成内部表
alter table tablename set tblproperties('EXTERNAL'='FALSE');注意事项：
属性external必须大写。true必须大写。false大小写都行
6.3 两种表的区别
1. 建表期间
-外部表必须使用关键字external，内部表正常创建，没有该关键字
-外部表可以使用location关键字指定数据的具体位置，内部表只能在库目录下。
2. 用途
- 外部表通常用于存储永久性数据文件。换句话说，就是不能随便删除的表要使用外部表。数据仓库底层一般都是外部表
- 内部表一般用于测试数据
3. 删除表时
- 删除内部表时，mysql中的元数据以及hdfs上的原始数据都会被删除。
- 删除外部表时，mysql中的元数据会被删除，但是hdfs上的原始数据不会被删除。注意：建表时会在hdfs上产生目录以及在mysql中存储元数据。
七、Hive的基本查询语句
7.1 查询原则：
1. 尽量不使用子查询、尽量不使用in 或者not in (可以使用 [not] exists替代)
2. 尽量避免join连接查询，但是通常避免不了
3. 查询永远是小表驱动大表（小表作为驱动表）
--注意：内连接时，默认是左表是驱动表，因此左表一定要是小表。
-- 外连接看需求而定。 7.2 在hive(2.1.1版本)中的注意事项：
1. 列别名可以提前应用到having子句中
2. 子查询如果在where子句中作为条件，那么不能使用等号，必须使用in，not in 换句话说，即使子查询返回的是唯一的一个值，也是集合形式。
3. 表名问题：
如果涉及到嵌套或者是子查询，表名相同，那么必须起表别名。
4. 分组查询时，select子句中除了分组字段和聚合函数之外，不能有普通的字段存在。
使用count()函数时的执行效率问题：
当表中有主键字段时，count(主键字段) 效率最高
没有主键字段时，count(1)效率最高
5. limit后支持两个参数
语法格式： limit [index,] lines;
index:表示行的下标，从0开始
lines:表示要查询的行数。7.3 exists的用法
exists: 作用： 用于判断主查询中是否有满足条件的记录，如果有就返回。如果没有，就过滤掉。子查询的特点时： 如果有满足条件，就会selec后指定的数据，至少一条。相当于true.即存在
select * from emp A where exists(select 1 from emp t1 where A.mgr=t1.empno)
not exists:
select * from emp A where not exists(select 1 from emp t1 where A.mgr=t1.empno)
注意：子查询中的条件只能使用等号。7.4 left semi join
和exists的原理是一样的
用于判断左表中是否有满足关联条件的数据，如果有就显示，没有就过滤掉。不显示右表的数据
select * from emp A left semi join emp M on A.mgr = M.empno;
hive --database sz2103 --hivevar tname=emp -e 'select deptno,count(1) from ${hivevar:tname} group by deptno';7.5 hive的日志
位置：/tmp/root/下
特点：每天一个日志。
日志名： hive.log
当hql语句执行异常时，可以查看该日志。7.6 hql的运行方式
方式1：在hive的命令行上运行
本地模式下连接hive
远程模式下使用beeline连接hiveserver2服务
源码模式下使用hive(9083)连接metastore服务方式2：运行hql脚本文件
步骤1： 将hql语句封装到文件中
步骤2： 在linux命令行上使用hive指令和-f参数来运行该hql脚本文件
[root@xxx01 ~]# mkdir hql
[root@xxx01 ~]# vim hql/query1.hql
use mydb2;
select * from u1 left semi join u2 on u1.id = u2.id;
[root@xxx01 ~]# hive -f hql/query1.hql方式3：直接在linux命令行上运行hql
第一种：
[root@xxx01 ~]# hive -e 'select * from mydb2.u1';
第二种：
[root@xxx01 ~]# hive --database mydb2 -e 'select * from u1';
第三种：声明变量法
[root@xxx01 ~]# hive --database mydb2 --hivevar tablename=emp --hiveconf deptno=10 -e 'select * from ${hivevar:tablename} where deptno=${hiveconf:deptno}'; 八、HIVE的数据类型
hive的数据类型分两种，一种是基本数据类型，一种是复杂数据类型
8.1 hive的基本数据类型
1. 整型有： int bigint smallint tinyint
2. 浮点型： double,float, 精确类型(decimal)
3. 布尔型： boolean
4. 字符串类型：string varchar(length),char(length),
5. 日期类型：date,timestamp案例演示：
-- 数据如下：
233 12 342523 455345345 30000 60000 nihao helloworld 2017-06-02
126 13 342526 455345346 80000 100000 true helloworld1 2017-06-02 11:41:30--建表语句：
create table if not exists datatype1(
id1 tinyint,
id2 smallint,
id3 int,
id4 bigint,
slary float,
comm double,
isok boolean,
content binary,
dt timestamp
)
row format delimited
fields terminated by '\t'
;
--导入数据
load data local inpath './data/datatype.txt' into table datatype1;
特点总结：
1. 文件里的数据映射成表的字段值时，如果超出范围，或者类型不匹配，则映射成null。 timestamp类型必须是yyyy-MM-dd HH:mm:ss的格式才能映射成功。
2. 隐式转换
- 在做运算时，小范围类型都可以自动转为大范围类型做运算8.2 复杂类型之Array类型
8.2.1 建表语法
create table [if not exists] tablename(
.......
fieldName array<元素类型>
.......
)8.2.2 案例演示
1）数据准备
zhangsan 78,89,92,96
lisi 67,75,83,94
王五 23,122）建表语句
-- 错误示范，不指定元素分隔符。
create table scores(
name string,
scores array<int>
)
row format delimited fields terminated by '\t' lines terminated by '\n';
-- 结论：array字段映射失败，没有数据，只有一个null元素
-- 正确写法，应该使用collection items terminated by ''指定数组的元素分隔符
drop table if exists scores;
create table scores(
name string,
scores array<int>
)
row format delimited fields terminated by '\t'
collection items terminated by ','
lines terminated by '\n';3）导入数据
load data local inpath 'data/scores.txt' into table scores;4）array类型的查询
下标从0开始，如果下标书写时越界，不会报错，只会返回null。练习1：统计每个人的每科的成绩
select name,scores[0] math,scores[1] chinese,scores[2] english,scores[3] physic from scores;
结论：下标越界，返回null.练习2：统计每个人的总成绩 （注意null值的处理）
select name,scores[0]+scores[1]+nvl(scores[2],0)+nvl(scores[3],0) totalScore from scores;练习3：统计每个人考试的科目数。(可以借助size函数，来统计array的元素个数）
select name,size(scores) from scores;练习4：统计考了3科以上的学生信息
select * from scores where size(scores)>=3;练习5：统计第二科成绩小于第一科成绩的学生信息
select * from scores where scores[1]<scores[0];8.3 展开函数explode的应用
通过练习2，可以发现，如果有多列，那么在计算和的时候，可能会很繁琐。如果能将array的元素变成多行显示。然后使用分组查询，来统计和，就简单多了。因此来研究一下array元素的展开，需要使用展开函数explode
案例需求：
zhangsan 78,89,92,96
lisi 67,75,83,94
王五 23,12
将上述效果转成下面的效果，更方便统计每个人的总成绩。
zhangsan 78
zhangsan 89
zhangsan 92
zhangsan 96
lisi 67
lisi 75
lisi 83
lisi 94
王五 23
王五 128.3.1 简介
- explode：
展开函数(UDTF函数中的一种),作用是：接受一个数据行，然后返回产生多个数据行
- lateral view:虚拟表。
会将UDTF函数生成的结果放到一个虚拟表中，然后这个虚拟表会和输入行进行join来达到数据聚合的目的8.3.2 案例演示
1. 使用展开函数，只查询array字段，看效果
hive> select explode(scores) from scores;
OK
78
89
92
96
67
75
83
94
23
12
2. 利用虚拟表与其他字段进行关联。看效果
语法格式：
....from 原表名 lateral view explode(展开字段) 虚拟表别名 as 展开字段的列别名.
hive> select name,score from scores lateral view explode(scores) t1 as score;
OK
zhangsan 78
zhangsan 89
zhangsan 92
zhangsan 96
lisi 67
lisi 75
lisi 83
lisi 94
王五 23
王五 12
3. 利用展开效果，统计每个人的总成绩
select name,sum(score) from scores lateral view explode(scores) t1 as score group by name;
注意：from中的虚拟表就已经是关联结果了，所有没有必须在嵌套一层，直接分组查询即可。8.4 搜集函数的应用
搜集函数有两种，一种是collect_list，一种是collect_set。
collect_list的特点：是有序，可重复
collect_set的特点：是无序，不可重复8.4.1 案例演示：
1）案例需求：
向array字段中动态加载数据，不能直接load加载，而是insert。2）数据准备，也就是准备一张表
hive> create table score_1 as select name,score from scores lateral view explode(scores) t1 as score;
hive> select * from score_1;
OK
zhangsan 78
zhangsan 89
zhangsan 92
zhangsan 96
lisi 67
lisi 75
lisi 83
lisi 94
王五 23
王五 123）借助函数收集数据，将每个人的成绩搜集起来
select name,collect_list(score) score_arr from score_1 group by name;
结果如下：
lisi [67,75,83,94]
zhangsan [78,89,92,96]
王五 [23,12]
注意：搜集函数的返回值就是一个数组。4）动态插入到一个有数组字段的新表中
-- 创建新表
create table scores_new(
name string,
scores array<int>
)
row format delimited fields terminated by '\t'
collection items terminated by ','
lines terminated by '\n';
-- 动态导入数据
insert into scores_new select name,collect_list(score) score_arr from score_1 group by name;8.5 复杂类型之Map类型
8.5.1 建表语法
create table tableName(
.......
colName map<T,T>
......
)8.5.2 案例演示1
1）数据准备
zhangsan chinese:90,math:87,english:63,nature:76
lisi chinese:60,math:30,english:78,nature:0
wangwu chinese:89,math:252）建表语句
create table scores_map(
name string,
scores map<string,int>
)
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';3）导入数据
load data local inpath './data/map1.txt' into table scores_map;4）map类型的查询
访问map的元素的写法：
字段名["key"]练习1）查询每个人的数学成绩
select name,scores["math"] from scores_map;练习2）查询语文成绩大于70的学生信息
select * from scores_map where scores["chinese"]>70;练习3）查询每个人的总成绩
select name,scores["chinese"]+scores["math"]+scores["english"]+scores["nature"] from scores_map;
上述结果，不正确，因为没有处理null值。
select name,scores["chinese"]+scores["math"]+nvl(scores["english"],0)+nvl(scores["nature"],0) from scores_map;练习4）使用展开函数来实现练习3的需求
-- 展开效果如下：
hive> select explode(scores) from scores_map;
OK
chinese 90
math 87
english 63
nature 76
chinese 60
math 30
english 78
nature 0
chinese 89
math 25
--借助虚拟表进行展开
hive> select name,subject,score from scores_map lateral view explode(scores) t1 as subject,score;
zhangsan chinese 90
zhangsan math 87
zhangsan english 63
zhangsan nature 76
lisi chinese 60
lisi math 30
lisi english 78
lisi nature 0
wangwu chinese 89
wangwu math 25
-- 直接查询每个人的总成绩
select name,sum(score) from scores_map lateral view explode(scores) t1 as subject,score group by name;8.5.2 案例演示2
1）案例需求：
将数据动态写入含有map字段的表中2）数据准备
hive> create table map_1 as select name,subject,score from scores_map lateral view explode(scores) t1 as subject,score;
hive> select * from map_1;
OK
zhangsan chinese 90
zhangsan math 87
zhangsan english 63
zhangsan nature 76
lisi chinese 60
lisi math 30
lisi english 78
lisi nature 0
wangwu chinese 89
wangwu math 253）步骤如下
第一步：将字段subject和score拼接到一起
select name,concat(subject,":",score) from map_1;
第二步：将所有属于同一个人的数据组合在一起
select name,collect_set(concat(subject,":",score)) from map_1 group by name;
第三步: 将数组的元素用逗号拼接成字符串，借助concat_ws函数
concat_ws(sep,array):
第一个参数是拼接符号，第二个参数是数组。作用就是用拼接符号将数组元素拼接成字符串
select name,concat_ws(",",collect_set(concat(subject,":",score))) from map_1 group by name;
第四步：将拼接后的字符串转成map类型
str_to_map(string,delimiter1,delimiter2):
参数string: 表示要转换的字符串
参数delimiter1：指定map的元素分隔符
参数delimiter2：指定map的key和value的分隔符
select name,str_to_map(concat_ws(",",collect_set(concat(subject,":",score))),",",":")from map_1 group by name;
第五步：直接使用克隆表的方式，将数据存储到新表中
create table map_new as select name,str_to_map(concat_ws(",",collect_set(concat(subject,":",score))),",",":") scores from map_1 group by name; 提前建表，使用insert into动态插入，注意类型转换的问题
drop table scores_map2;
create table scores_map2(
name string,
scores map<string,string>
)
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n';
hive (sz2103)> insert into scores_map2 select name, str_to_map(concat_ws(",",collect_list(concat(subject,":",score))),",",":") from map_10 group by name;8.6 复杂类型之struct类型
8.6.1 建表语法
create table tablename(
......
fieldName struct<subName1:Type,subName2:Type,......>
......
)8.6.2 案例演示
1）数据准备
zhangsan 90,87,63,76
lisi 60,30,78,0
wangwu 89,25,81,92）建表语句
create table struct_1(
name string,
scores struct<math:int,chinese:int,english:int,nature:int>
)
row format delimited
fields terminated by '\t'
collection items terminated by ',';3) 导入数据
load data local inpath './data/struct1.txt' into table struct_1;4）struct类型的查询
练习1：查询每个人的数学和英语成绩
select name,scores.math,scores.english from struct_1;练习2：查询英语成绩大于70分的学生信息
select * from struct_1 where scores.english>70;8.7 综合练习
数据如下：
-- 主管信息表如下：
manager(uid uname belong tax addr)
-- 数据如下：
1 xdd ll,lw,lg,lc,lz wx:600,gongjijin:1200,shebao:450 北京,西城,中南海
2 lkq lw,lg,lc,lz,lq wx:1000,gongjijin:600,shebao:320 河北,石家庄,中山路
3 zs lw,lg,lc wx:2000,gongjijin:300,shebao:10 江西,南昌,八一大道1）建表语句
create table manager(
uid int,
uname string,
belong array<string>,
tax map<string,int>,
addr struct<province:string,city:string,street:string>
)
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':';2）导入数据
load data local inpath './data/manager.txt' into table manager;3）练习：查询下属个数大于4个，公积金小于1200，省份在河北的数据
select *
from manager where size(belong)>4 and tax["gongjijin"]<1200 and addr.province="河北"; 扩展：默认分隔符的输入规则
默认分隔符：^A 对应输入方式:ctrl+V ctrl+A 九、HIVE的函数（重点）
hive的函数分为两种类型，一种是内置函数，一种是用户自定义函数。
如何查看函数：
show functions：查看hive中所有的函数
desc function funcName: 查看指定函数的用法9.1 常用的内置函数
常用的内置函数，可以分为时间日期函数、数学函数，字符串函数、高级函数
9.1.1 常用的日期时间函数
当前系统时间函数：
current_date()、
current_timestamp()、
unix_timestamp()
练习：
hive> select current_date(),current_timestamp(),unix_timestamp();
日期转时间戳函数：
unix_timestamp()
练习：
select unix_timestamp("1970-1-1 00:00:00","yyyy-MM-dd HH:mm:ss");
-28800
注意：当我们查询东八区的凌晨时，返回的是本初子午线的时间点。那个时候正好是69年12月31号下午4点整。
东八区为8点时，本初子午线才是凌晨。
时间戳转日期函数：
from_unixtime
练习：
select from_unixtime(28800,"yyyy-MM-dd HH:mm:ss");
注意：第一个参数是本初子午线距离1970年1月1日的时间秒数。
计算时间差函数：
datediff()
months_between()
练习
select datediff(current_date(),'2020-9-6'); :返回的是第一个参数减去第二个参数的天数
select months_bettween(current_date(),'2020-9-6');
:返回的是相差的月数，会有小数。
日期时间分量函数：
year()、
month()、
day()、
hour()、
minute()、
second()
日期定位函数：
last_day()、next_day()
练习：
select last_day(current_date()) :返回指定日期所在月的最后一天的日期
select next_day(current_date(),'mon'):返回指定日期后的第一个周几的具体日期
日期加减函数：
date_add()、
date_sub()、
add_months()
练习：
select date_add(current_date(),-1);
select date_sub(current_date(),1);
select add_months(current_date(),1);
字符串转日期：
to_date(express)
练习：
select to_date("2008-8-8") : 注意，只能使用日期的默认格式yyyy-MM-dd的字符串。
日期转字符串(格式化)函数：
date_format
练习
select date_format(current_date(),'yyyy/MM/dd');
日期截断函数：
select trunc(current_date(),"YYYY"); 截取到年份，表示后面的日期分量都回到最小值。9.1.2 常用的字符串函数
lower/lcase--（转小写）
select lower('ABC');
upper/ucase--（转大写）
select upper('abc');
length--（字符串长度，字符数）
select length('abc');
concat--（字符串拼接）
select concat("A", 'B');
concat_ws --（指定分隔符）
select concat_ws('-','a' ,'b','c');
substr--（求子串）
select substr('abcde',3);
split(str,regex)--切分字符串，返回数组。
select split("a-b-c-d-e-f","-");
大小写的函数练习
hive> select lower("AbcDef"),upper("AbcDef"),lcase("A"),ucase("a");
hive> select length("no zuo no die"),concat("my teacher"," is canglaoshi"),concat_ws("-","the teacher of all over the world","is","boduoyelaoshi");
hive> select substr("helloworld",4); 返回的结果为：loworld
注意：下标从1开始，表示从第4个开始截取
select substr("helloworld",4,2); 第三个参数表示截取的长度。9.1.3 常用的数学函数
常用的数学统计函数，必须配合分组查询。
max()
min()
avg()
sum()
count()
四舍五入函数：round():
向上取整函数：ceil() 返回的是大于参数的最小整数
向下取整函数：floor()， 返回的是小于参数的最大整数
随机函数：rand() , 返回的是[0,1)之间的任意一个小数。
select round(0.618,2),ceil(0.618),floor(-0.618),rand();9.1.4 其他函数
nvl(value,default value):如果value为null,则使用default value，否则使用本身value.
if(p1,p2,p3)
case when then ....when ...then.. else... end
isnull()
isnotnull()
coalesce(col1,col2,col3...)返回第一个不为空的9.2 高级函数之窗口函数over
先来看一下这个需求：求每个部门的员工信息以及部门的平均工资。在mysql中如何实现呢
需要两个sql语句来完成
第一个：查询员工信息
select * from emp;
第二个：查询部门的平均工资
select deptno,avg(ifnull(sal,0)) from emp group by deptno;
合并一下：
select e.*,t.avg_sal
from (select deptno,avg(ifnull(sal,0)) avg_sal from emp group by deptno) t join emp e
on t.deptno = e.deptno;
另外一种写法，但是效率低下。
select e.*,(select avg(ifnull(sal,0)) from emp where deptno = a.deptno) avg_sal from emp a;我们也可以使用窗口函数，更方便的做到如上查询，那么什么是窗口函数呢
9.2.1 窗口函数的介绍
-1) 窗口函数over()又名开窗函数，属于分析函数的一种。
-2) 是一种用于解决复杂报表统计需求的函数。
-3) 窗口函数常用于计算基于组的某种值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。
简单的说窗口函数对每条详细记录开一个窗口,进行聚合统计的查询
换句话说：窗口中有多少条，就返回多少条
-4) 开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。
-5) 窗口函数一般不单独使用，要配合其他函数一起使用。
-6) 窗口函数内也可以分组和排序9.2.2 案例演示
数据准备
zsan,2019-10-11,10
zsan,2019-10-12,20
lisi,2019-10-12,30
zsan,2019-10-13,30
lisi,2019-10-13,20
wangwu,2019-10-13,10
wangwu,2019-10-14,20
saml,2018-01-01,10
saml,2018-01-08,55
tony,2018-01-07,50
saml,2018-01-05,46
tony,2018-01-04,29
tony,2018-01-02,15
saml,2018-02-03,23
mart,2018-04-13,94
saml,2018-04-06,42
mart,2018-04-11,75
mart,2018-04-09,68
mart,2018-04-08,62
neil,2018-05-10,12
neil,2018-06-12,80创建order表:
drop table t_order;
create table if not exists t_order
(
name string,
orderdate string,
cost int
) row format delimited fields terminated by ',';加载数据:
load data local inpath "./data/order1.txt" into table t_order;练习1：查询每个人的信息以及总记录数。
不使用开窗函数的写法如下：
select a.*,b.num from t_order a join (select count(*) num from t_order) b;使用开窗函数的写法如下：
select *,count(1) over() from t_order;注意：over()，如果括号里没有内容，则窗口的数据量是整张表。
9.3 over()函数的其他用法
9.3.1 distribute by + sort by 组合
位置：在over函数的小阔号
写法：可以单独使用，也可以一起组合使用
如：
over(distribute by colName)
over(sort by colName)
over(distribute by colName sort by colName [asc|desc])
作用：
distribute by colName：用于指定分组字段，表示按照指定字段分组，那么每一组对应一个窗口,
如果没有，则表示整张表为一组
sort by colName: 用于排序，如果没有distribute by组合，表示整张表为一
组，进行排序，如果有则组内进行排序练习：使用窗口函数，统计每个人的总花销
select *,sum(cost) over(distribute by name) from t_order;练习：查询每个人的详情以及总花销，按照name降序
select *,sum(cost) over(sort by name desc) from t_order;练习：查询每个人的总花销，并且按照花销降序
select *,sum(cost) over(distribute by name sort by cost desc) from t_order;9.3.2 partition by +order by组合
位置：还是在over小括号里
写法：可以单独使用，也可以一起组合使用
如
over(partition by colName)
over(order by colName)
over(partition by colName order by colName [asc|desc])
作用：与 distribute by + sort by 组合效果一模一样。
练习：查询每个人的总花销，最大的花销数，最小的花销，平均花销，以及花销次数以及详情
select *,
sum(cost) over(partition by name),
max(cost) over(partition by name),
min(cost) over(partition by name),
avg(nvl(cost,0)) over(partition by name),
count(1) over(partition by name),
sum(cost) over() `商场营业额`
from t_order;9.4 window子句的作用
window子句用来更细粒度的管理窗口大小的
current row： 当前行
preceding: 向前
following: 向后
unbounded preceding: 从起点
unbounded following: 到终点
select name,orderdate,cost,
sum(cost) over() as sample1,--所有行相加
sum(cost) over(partition by name) as sample2,-- 按name分组，组内数据相加
sum(cost) over(partition by name order by orderdate) as sample3,-- 按name分组，组内数据累加
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,-- 与sample3一样，由起点到当前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, -- 当前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,-- 当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 -- 当前行及后面所有行
from t_order;
练习： 统计每个人的详细信息，以及最近三天的花销总额
select *,sum(cost) over(partition by name order by orderdate rows between 2 preceding and current row) from t_order;9.5 排名函数
有三个排名函数，分别是row_number()、dense_rank()、rank()这三个，他们的排名效果不同。情况如下：
效果一： 分数有相同，名次是连续不重复的， 对应的函数是row_number()
分数 名次
100 1
99 2
99 3
98 4
效果二： 分数相同，名次有重复，是间断的 对应的函数是rank()
分数 名次
100 1
99 2
99 2
98 4
效果三： 分数有相同，名次重复不间断 应的函数是dense_rank()
分数 名次
100 1
99 2
99 2
98 3这三个排名函数，不能单独使用，也必须配合over函数一起使用。
练习1：显示全校排名名次
select *, row_number() over(sort by score desc) rk1,rank() over(sort by score desc) rk2,dense_rank() over(sort by score desc) rk3
from stu_score;练习2：查询每个班级的排名情况，效果：分数相同的话，名次一样，不间断
select * ,dense_rank() over(distribute by classno sort by score desc) from stu_score;练习3：取每个班级的前三名，dense_rank()。
select *
from
(select * ,dense_rank() over(distribute by classno sort by score desc) rk from stu_score) t
where rk<=3;9.6 其他序列函数
1）ntile函数
也是一个强大的分析函数，可以看成是：它把有序的数据集合 "平均分配" 到 "指定数量"（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。练习：
select *,ntile(3) over(sort by score desc) from stu_score;
select *,ntile(3) over(distribute by classno sort by score desc) from stu_score; 2）lag()和lead()函数
lag(colName,precedingNum[,defaultValue])
:向前取第precedingNum行的该colName的值，如果没有，使用defaultValue
lead(colName,followingNum[,defaultValue])
:向后取第followingNum行的该colName的值，如果没有，使用defaultValue练习1：列出每个顾客的当次消费以及上一次的消费额度
select *,lag(cost,1) over(partition by name order by orderdate) from t_order;练习2：列出每个顾客的当次消费以及下2次的消费额度，如果下2次没有消费，设置为0
select *,lead(cost,2,0) over(partition by name order by orderdate) from t_order;练习3： 求5分钟连续点击100次的用户
dt id url
........
2019-08-22 19:00:01,1,www.baidu.com
2019-08-22 19:01:01,1,www.baidu.com
2019-08-22 19:02:01,1,www.baidu.com
2019-08-22 19:03:01,1,www.baidu.com
.......
思路： 每个用户分组并按照时间升序，并显示详情，然后向前取第100条，与当前条的时间做差值。如果差值<300秒，则为5分钟之后连续点击超过100次。3）first_value和last_value函数
first_value(colName) 取分组内排序后，截止到当前行，第一个值
last_value(colName) 分组内排序后，截止到当前行，最后一个值
练习
select *,first_value(cost) over(partition by name order by orderdate) from t_order;
select *,last_value(cost) over(partition by name order by orderdate) from t_order;练习2：取每一组中的第一个值和最后一个值
select *,
first_value(cost) over(partition by name order by orderdate) firstV,
first_value(cost) over(partition by name order by orderdate desc) lastV from t_order;9.7 自定义函数
如果hive的内置函数不能满足我们的查询需求，那么就可以使用hive的api来自定义一些hive的函数。自定义函数分三类：
一、UDF(用户自定义函数)
特点：一进一出，一对一的形式
二、UDAF(用户自定义聚合函数)
特点：多进一出，多对一的形式，类似max,sum等函数
三、UDTF(用户自定义表生成函数)
特点：一进多出，一对多的形式，类似explode函数9.7.1 UDF的应用
1）准备工作和注意事项
在pom.xml,加入以下maven的依赖包
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>2.1.1</version>
</dependency>定义UDF函数要注意下面几点:
1. 继承org.apache.hadoop.hive.ql.exec.UDF
2. 重写evaluate()，这个方法不是由接口定义的,因为它可接受的参数的个数,数据类型都是不确定的。
当在hive的hql里应用自定义函数时，Hive会根据向函数中传入的参数个数以及类型,查看对应的自定义类中能否找到和函数调用相匹配的evaluate()方法案例演示1：
写一个UDF, 将小写字母转成大写字母。代码如下：
package com.qf.hive.udf;
import org.apache.hadoop.hive.ql.exec.UDF;
/**
* 第一步：继承UDF类型
* 第二步：提供一个名为evaluate的方法
* 返回值和参数可以自定义
*/
public class MyUpperCase extends UDF {
public String evaluate(String value){
if(value==null||value.length()==0){
return null;
}
return value.toUpperCase();
}
}使用第一种方式临时加载（只针对于当前session有效）
步骤1：将写好的程序打包并上传到linux上，然后使用hive的add jar指定加载到classpath里
hive> add jar /root/hive-1.0-SNAPSHOT.jar;
步骤2：创建临时函数
hive> create temporary function myUpper as 'com.qf.hive.udf.FirstUDF';
步骤3. 查看我们创建的自定义函数，
hive> show functions;
步骤4.在hive中使用函数进行功能测试
hive> select myupper('a');
步骤5. 如何删除自定义函数？在删除一个自定义函数的时候一定要确定该函数没有调用
hive> drop temporary function if exists myupper;案例演示2
把生日转换成年纪代码如下：
package com.qf.hive.udf;
import org.apache.commons.lang.StringUtils;
import org.apache.hadoop.hive.ql.exec.UDF;
import java.util.Calendar;
public class Birthday2Age extends UDF {
public int evaluate(String birth) {
// 1、判断参数
if (StringUtils.isEmpty(birth)) {
return -1;
}
// 拆分生日，获取年月日
String[] birthdays = birth.split("-");
// 得到生日年月日
int birthYear = Integer.parseInt(birthdays[0]);
int birthMonth = Integer.parseInt(birthdays[1]);
int birthDay = Integer.parseInt(birthdays[2]);
// 获取当前时间
Calendar calendar = Calendar.getInstance();
int nowYear = calendar.get(Calendar.YEAR);
int nowMonth = calendar.get(Calendar.MONTH) + 1;
int nowDay = calendar.get(Calendar.DAY_OF_MONTH);
// 计算年龄
int age = nowYear - birthYear;
// 判断月份和日期
if (nowMonth < birthMonth) {
age -= 1;
} else if (nowMonth == birthMonth && nowDay < birthDay) {
age -= 1;
}
return age;
}
public static void main(String[] args) {
System.out.println(new Birthday2Age().evaluate("1980-09-8"));
}
}使用第二种方式加载自定义函数并测试
第二种方式：使用hive指令，在启动时加载配置文件形式(只针对当前会话生效)
步骤1：上传打包好的程序到linux
步骤2：创建一个配置文件/root/hive-init.conf,书写以下内容
add jar /root/hive-1.0-SNAPSHOT.jar;
create temporary function birthdayToAge as 'com.qf.hive.udf.Birthday2Age';
步骤3：退出hive客户端，重新进入hive
hive - i /root/hive-init.conf
步骤4：检查是否存在
show functions
步骤5：测试应用
select birthdaytoage("1986-11-08");案例演示3
根据key获取value1) 数据准备：
sex=1&hight=180&weight=100&sal=2000000
输入:sal
输出:20000002) 实现思路：
1. 将这种输入字符串转换成json格式的字符串{sex:1,hight:180,weight:100,sal:2000000}
2. 构建一个json的解析器
3. 根据key获取value3）代码如下
package com.qf.hive.udf;
import org.apache.commons.lang.StringUtils;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.json.JSONException;
import org.json.JSONObject;
public class Key2Value extends UDF {
public String evaluate(String str, String key) throws JSONException {
// 1、判断参数
if (StringUtils.isEmpty(str) || StringUtils.isEmpty(key)) {
return null;
}
// 将str转换成json格式
// sex=1&hight=180&weight=130&sal=28000
// {'sex':1,'hight':180,'weight':130,'sal':28000}
String s1 = str.replace("&", ",");
String s2 = s1.replace("=", ":");
String s3 = "{" + s2 + "}";
// 使用尽送对象解析json串
JSONObject jo = new JSONObject(s3);
return jo.get(key).toString();
}
public static void main(String[] args) throws JSONException {
System.out.println(new Key2Value().evaluate("sex=1&hight=180&weight=130&sal=28000&faceId=189", "faceId")
);
}
}4）使用第三种方式来加载自定义函数
1、将编写好的自定义函数程序上传到linux
2、在hive的安装目录下的bin目录中创建一个名为.hiverc的文件并输入以下内容
add jar /root/hive-1.0-SNAPSHOT.jar;
create temporary function getvaluebykey as 'com.qf.hive.udf.Key2Value';
3、重新启动hive客户端
4、查看是否存在
5、测试运行
select getvaluebykey("uid=1001&name=zangs&age=23&girlfriend=canglaoshi","girlfriend");案例演示4：
正则表达式解析日志代码如下：
package com.qf.hive.udf;
import org.apache.commons.lang.StringUtils;
import org.apache.hadoop.hive.ql.exec.UDF;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class LogParser extends UDF {
public String evaluate(String log) throws ParseException, Exception {
// 1
if (StringUtils.isEmpty(log)) {
return null;
}
// 220.181.108.151 - - [31/Jan/2012:00:02:32 +0800] \"GET
// /home.php?mod=space&uid=158&do=album&view=me&from=space HTTP/1.1\" 200 8784
// \"-\" \"Mozilla/5.0 (compatible; Baiduspider/2.0;
// +http://www.baidu.com/search/spider.html)\"
// 220.181.108.151 20120131 120232 GET
// /home.php?mod=space&uid=158&do=album&view=me&from=space HTTP 200 Mozilla
// 定义一个正则表达式
String reg = "^([0-9.]+\\d+) - - \\[(.* \\+\\d+)\\] .+(GET|POST) (.+) (HTTP)\\S+ (\\d+) .+\\\"(\\w+).+$";
// 获取一个模式匹配器
Pattern pattern = Pattern.compile(reg);
// 匹配结果
Matcher matcher = pattern.matcher(log);
//
StringBuffer sb = new StringBuffer();
// 判断数据是否匹配上
if (matcher.find()) {
// 先获取匹配的段数
int count = matcher.groupCount();
// 循环获取每段的内容，并且将内容拼接起来
for (int i = 1; i <= count; i++) {
// 判断字段是否是时间字段，如果是，则做时间格式转换
if (i == 2) {
// 定义一个时间格式来解析当前时间
Date d = new SimpleDateFormat("dd/MMM/yyyy:HH:mm:ss Z", Locale.ENGLISH).parse(matcher.group(i));
// 定义输出的时间格式
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd hhmmss");
// 将时间格式转换成输出格式，并输出
sb.append(sdf.format(d) + "\t");
} else {
sb.append(matcher.group(i) + "\t");
}
}
}
return sb.toString();
}
public static void main(String[] args) throws Exception {
System.out.println(new LogParser().evaluate(
"220.181.108.151 - - [31/Jan/2012:00:02:32 +0800] \\\"GET /home.php?mod=space&uid=158&do=album&view=me&from=space HTTP/1.1\\\" 200 8784 \\\"-\\\" \\\"Mozilla/5.0 (compatible; Baiduspider/2.0; +http://www.baidu.com/search/spider.html)\\\""));
}
}准备数据：
数据文件名access.log
create table log100(
log string
);
load data local inpath './data/access.log' into table log100;使用第一种临时加载的方式测试自定义函数
...
create temporary function logparser as 'com.qf.hive.udf.LogParser'
..
select logparser(log) from log100;案例演示5：
Json数据解析UDF开发1） 数据准备：
有原始json数据如下：
{"movie":"1193","rate":"5","datetime":"978300760","uid":"1"}
{"movie":"661","rate":"3","datetime":"978302109","uid":"1"}
{"movie":"914","rate":"3","datetime":"978301968","uid":"1"}
{"movie":"3408","rate":"4","datetime":"978300275","uid":"1"}
{"movie":"2355","rate":"5","datetime":"978824291","uid":"1"}
{"movie":"1197","rate":"3","datetime":"978302268","uid":"1"}
{"movie":"1287","rate":"5","datetime":"978302039","uid":"1"}
参考数据文件：rating.json
最终形成hive表中这样的数据
movie rate datetime uid
1197 3 978302268 12） 将原始数据导入到hive库，先创建一个单字段的表
create table if not exists movie_json
(json string);
load data local inpath './data/rating.json' into table movie_json;代码如下：
RateBean
package com.qf.hive.udf;
import org.apache.hadoop.io.WritableComparable;
import java.io.DataInput;
import java.io.DataOutput;
import java.io.IOException;
public class RateBean implements WritableComparable<RateBean> {
private int uid;
private int movie;
private int rate;
private String datetime;
public RateBean(){}
public RateBean(int uid, int movie, int rate, String datetime) {
this.uid = uid;
this.movie = movie;
this.rate = rate;
this.datetime = datetime;
}
public int getUid() {
return uid;
}
public void setUid(int uid) {
this.uid = uid;
}
public int getMovie() {
return movie;
}
public void setMovie(int movie) {
this.movie = movie;
}
public int getRate() {
return rate;
}
public void setRate(int rate) {
this.rate = rate;
}
public String getDatetime() {
return datetime;
}
public void setDatetime(String datetime) {
this.datetime = datetime;
}
/**
* 因为想要将该类型作为v2, 进入reduce后，该类型的对象放入集合中，进行排序，
* 所以使用rate进行降序
* @param o
* @return
*/
@Override
public int compareTo(RateBean o) {
return o.getRate()-this.rate;
}
@Override
public void write(DataOutput out) throws IOException {
out.writeInt(uid);
out.writeInt(movie);
out.writeInt(rate);
out.writeUTF(datetime);
}
@Override
public void readFields(DataInput in) throws IOException {
uid = in.readInt();
movie = in.readInt();
rate = in.readInt();
datetime = in.readUTF();
}
public String toString(){
return movie+"\t"+rate+"\t"+datetime+"\t"+uid;
}
}
package com.qf.hive.udf;
import com.google.common.base.Strings;
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.log4j.Logger;
import org.codehaus.jackson.map.ObjectMapper;
import java.io.IOException;
public class JsonParser extends UDF {
private Logger logger = Logger.getLogger(JsonParser.class);
public String evaluate(String json) {
//判断传入参数
if(Strings.isNullOrEmpty(json)){
return null;
}
ObjectMapper objectMapper = new ObjectMapper();
try {
RateBean bean = objectMapper.readValue(json, RateBean.class);
return bean.toString();
} catch (IOException e) {
logger.error("解析json串失败！！",e);
}
return null;
}
public static void main(String[] args) {
System.out.println(new JsonParser().evaluate("{\"movie\":\"1193\",\"rate\":\"5\",\"datetime\":\"978300760\",\"uid\":\"1\"}"));
}
}使用临时加载的方式加载自定义函数，并进行测试
....
create temporary function jsonParser as 'com.qf.hive.udf.JsonParser'
....
select jsonparser(json) from movie_json limit 100;将解析的数据存入到一张新表中（直接克隆一张新表进行存储数据）
drop table movie_rate;
create table if not exists movie_rate
as
select
split(jsonParser(json),'\t')[0] as movie,
split(jsonParser(json),'\t')[1] as rate,
split(jsonParser(json),'\t')[2] as times,
split(jsonParser(json),'\t')[3] as uid
from movie_json;
drop table movie_rate;
create table if not exists movie_rate
as
select
split(getjson(json),'\t')[0] as movie,
split(getjson(json),'\t')[1] as rate,
split(getjson(json),'\t')[2] as times,
split(getjson(json),'\t')[3] as uid
from movie_json;案例演示6
内置的json解析函数：
select get_json_object(json,"$.movie") movie,
get_json_object(json,"$.rate") rate,
get_json_object(json,"$.datetime") datetime,
get_json_object(json,"$.uid") uid
from movie_json;十、分区表的讲解（重点）
10.1 分区表的简介
1）为什么要引入分区这个概念
因为hive本身在执行DQL语句时，通常都是全表扫描；而有的时候，我们只需要对表的某一个部分做查询，这样的话，就会降低效率。因此引入partition概念，将表的数据存存储在不同的子目录下，可以在查询时，指定子目录进行查询，从而提高查询效率2）分区的本质
就是在表的目录下维护多个子目录，用来存储数据。 子目录的名字 字段名=字段值3）如何分区
create table tablename(
.....
.....
)
partitioned by(colName type[comment ''],.......);4）分区字段的名字
通常都是年、月、日、星期、地域名等这些名字，但是，还是要按照业务需要而定。5）分区的注意事项
1. 分区字段名不区分大小写，但是值区分大小写。字段名不支持中文，字段值也不支持中文
CITY="SHANGHAI" city="shanghai"
2. 分区字段是一个伪字段，在表的结构中存在，但是可以像普通字段一样，正常使用。
3. 分区目录下可以有子分区目录。10.2 分区的案例演示
10.2.1 一级分区表的创建
1)建表语句
create table if not exists emp_part1(
empno int comment '员工编号',
ename string,
job string,
mgr int,
hiredate date,
sal double,
comm double,
deptno int
)
partitioned by (year string)
row format delimited
fields terminated by ',';2)数据导入
load data local inpath '/root/emp.txt' overwrite into table emp_part1 partition(year="2021");
load data local inpath '/root/emp.txt'overwrite into table emp_part1 partition(YEAR="2020"); --大小写不敏感
注意：分区字段的值不要使用中文。3）查询
desc emp_part1
select * from emp_part1
select * from emp_part1 where year=2020;10.2.2 二级分区表的创建
1）建表语句
create table if not exists emp_part2(
empno int comment '员工编号',
ename string,
job string,
mgr int,
hiredate date,
sal double,
comm double,
deptno int
)
partitioned by (year string,month string)
row format delimited
fields terminated by ',';2)加载数据
load data local inpath '/root/emp.txt' overwrite into table emp_part2 partition(year="2020",month="01");
load data local inpath '/root/emp.txt' overwrite into table emp_part2 partition(YEAR="2020",month="02");
load data local inpath '/root/emp.txt' overwrite into table emp_part2 partition(year="2021",month="01");
load data local inpath '/root/emp.txt' overwrite into table emp_part2 partition(YEAR="2021",month="02"); 3)查询
desc emp_part2;
select * from emp_part2;
select * from emp_part2 where year="2020" and month="02";10.2.3 三级分区表的创建
1）建表语句
create table if not exists emp_part3(
empno int,
ename string,
job string,
mgr int,
hiredate date,
sal double,
comm double,
deptno int
)
partitioned by (year string,month string,city string)
row format delimited
fields terminated by ',';2）导入数据
load data local inpath '/root/emp.txt' overwrite into table emp_part3 partition(year="2021",month="07",city="shenzhen");
load data local inpath '/root/emp.txt' overwrite into table emp_part3 partition(year="2021",month="07",city="beijing");
load data local inpath '/root/emp.txt' overwrite into table emp_part3 partition(year="2021",month="08",city="beijing");3)查询
desc emp_part3;
select * from emp_part3;
select * from emp_part3 where city="beijing";10.3 分区的CRUD
c：create 增
r:read 查
u:update 改
d:delete 删10.3.1 分区的查看
查看指定表中有哪些分区名
show partitions tablename;
原理：查询元数据库中的元数据，10.3.2 分区的增加
语法1：增加一个空分区，本质是向元数据添加分区元数据以及在hdfs上维护新分区目录
alter table tablename add partition(colname type,......);
语法2：增加多个空分区
alter table tablename add partition(colname type,......)
partition(colname type,......);
语法3：增加一个分区，并指定该分区映射的目录,
alter table tablename add partition(colname type,......) location '路径';
语法4：增加多个分区，并指定分区映射的目录,
alter table tablename add partition(colname type,......) location '路径'partition(colname type,......) location '路径';练习
语法2的练习
alter table emp_part3 add partition(year="2021",month="09",city="shenzhen")
partition(year="2021",month="09",city="beijing")
语法3的练习：
alter table emp_part3 add partition(year="2021",month="09",city="changchun") location '/output';10.3.3 分区的修改
注意：
1.分区的修改指的是修改分区的映射路径，而不是分区名
2.有的版本在修改路径时，比如hive1.2.1版本，需要从hdfs://开始写。
2.1.1版本不需要
语法1：修改一个分区的映射路径
alter table tablename partition(colName=value,......) set location 'newPath';练习
[root@xxx01 ~]# hdfs dfs -mkdir -p /emp/01/
[root@xxx01 ~]# hdfs dfs -cp /output/emp.txt /emp/01/
语法1的练习
alter table emp_part3 partition(year="2021",month="09",city="beijing") set location '/emp/01';
注意：如果修改了新的路径，那么在select时，一定查询的是新路径下的数据，但是如果在加载数据时，使用了overwrite关键字，就会出现映射到默认路径下的修改。如果没有overwrite关键字，加载的数据则上传到新目录下。
alter table emp_part3 partition(year="2021",month="08",city="beijing") set location '/emp/01',
partition(year="2021",month="08",city="shenzhen") set location '/emp/02';10.3.4 分区的删除
语法1：删除单个分区
alter table tablename drop partition(colname=value,.....);
语法1：删除多个分区
alter table tablename drop partition(colname=value,.....),partition(colname=value,.....);练习：
alter table emp_part3 drop partition(year="2021",month="09",city="beijing");删除分区的特点：
元数据库的元数据被删除，分区对应的目录也被删除。 10.4 分区类型讲解
10.4.1 分类的类型分类和常用属性
类型分类,分三类
1. 静态分区： 分区名是由load形式加载数据时产生的新分区，就是静态分区，静态分区名是手写的。
2. 动态分区： 使用insert形式加载数据，产生的新分区，就是动态分区，动态分区名是由数据决定的。
3. 混合分区： 静态和动态都有常用属性
hive.exec.dynamic.partition=true|false，是否支持动态分区操作,true表示支持
hive.exec.dynamic.partition.mode=strict/nonstrict: 严格模式/非严格模式
严格模式： 分区名必须要有一个静态值
非严格模式： 可以不用指定静态值
hive.exec.max.dynamic.partitions=1000: 总共允许创建的动态分区的最大数量
hive.exec.max.dynamic.partitions.pernode=100:in each mapper/reducer node10.4.2 动态分区的创建
1）数据准备
95001,李勇,男,20,CS,2017-8-31
95002,刘晨,女,19,IS,2017-8-31
95003,王敏,女,22,MA,2017-8-31
95004,张立,男,19,IS,2017-8-31
95005,刘刚,男,18,MA,2018-8-31
95006,孙庆,男,23,CS,2018-8-31
95007,易思玲,女,19,MA,2018-8-31
95008,李娜,女,18,CS,2018-8-31
95009,梦圆圆,女,18,MA,2018-8-31
95010,孔小涛,男,19,CS,2017-8-31
95011,包小柏,男,18,MA,2019-8-31
95012,孙花,女,20,CS,2017-8-31
95013,冯伟,男,21,CS,2019-8-31
95014,王小丽,女,19,CS,2017-8-31
95015,王君,男,18,MA,2019-8-31
95016,钱国,男,21,MA,2019-8-31
95017,王风娟,女,18,IS,2019-8-31
95018,王一,女,19,IS,2019-8-31
95019,邢小丽,女,19,IS,2018-8-31
95020,赵钱,男,21,IS,2019-8-31
95021,周二,男,17,MA,2018-8-31
95022,郑明,男,20,MA,2018-8-31
创建一个普通表，用来存储上面的数据
create table student(
sid int,
name string,
gender string,
age int,
academy string
dt string
)
row format delimited
fields terminated by ','
;
load data local inpath './data/student.txt' into table student; 2）创建分区表
create table student_part(
sid int,
name string,
gender string,
age int,
academy string
)
partitioned by (dt string)
row format delimited fields terminated by ','
;3）加载数据
注意：如果想要动态分区名，不能使用load加载，应该使用insert 动态加载数据
写法1：
insert into student_part partition(dt) select * from student;
写法2：
insert into student_part partition(dt) select sid,name,gender,age,academy,dt from student;
注意：
1.动态导入数据时，在严格模式下，必须至少有一个静态分区值。非严格模式下，可以没有静态分区值。
2.动态分区加载数据时，分区字段要放在select子句中的后面。4）查询
desc student_part;
show partitions student_part;
select * from student_part where dt="2019-8-31" and academy="CS";10.4.3 混合分区的创建
1）数据准备
create table userinfo(
id int,
name string,
year string,
month string,
day string
)
row format delimited fields terminated by ','
;
数据如下：
1,廉德枫,2019,06,25
2,刘浩(小),2019,06,25
3,王鑫,2019,06,25
5,张三,2019,06,26
6,张小三,2019,06,26
7,王小四,2019,06,27
8,夏流,2019,06,27
load data local inpath './data/userinfo.txt' into table userinfo;2）创建分区表
create table userinfo_part(
id int,
name string
)
partitioned by (year string,month string,day string)
row format delimited
fields terminated by '\t'
lines terminated by '\n'
stored as textfile;3）加载数据
insert into userinfo_part partition(year="2019",month,day)
select id,name,month,day from userinfo;4)查询
show partitions userinfo_part;
select * from userinfo_part where day="26";10.5 分区的总结
1. 分区字段是表外字段，是一个伪字段，但是可以进行查询过滤
2. 动态分区尽量少用，因为insert会产生mr程序，从而占用较多的平台资源(比如namenode,resourcemanager两个进程)
3. 分区的修改会涉及到元数据和hdfs目录的修改
4. 如果使用动态分区，需要注意严格模式下，至少要使用一个静态分区值。十一.分桶的概念(嗯点)
11.1 分桶的简介
表的分区，可能会出现，某些分区的数据过多，某些分区的数据较少，当对数据过多的分区进行查询时，相对而言，效率也会较低。因此又引入了分桶的概念。分桶的本质是对分区内的数据进行划分成多个数据文件进行存储，从而可以指定桶进行查询，提高效率。
分桶是比分区更细粒度的管理表数据。语法：
create table tablename(
.....
)
....
clustered by(colname,......) sorted by (colname [asc|desc]) into num buckets
....原理：
与MapReduce中的HashPartitioner的原理一模一样
- MapReduce：使用key的hash值对reduce的数量进行取模(取余)
- hive：使用分桶字段的hash值对分桶的数量进行取模(取余)。针对某一列进行分桶存储。每一条记录都是通过分桶字段的值的hash对分桶个数取余，然后确定放入哪个桶。11.2分桶的案例演示
1）数据准备
create table student1(
sno int,
name string,
sex string,
age int,
academy string
)
row format delimited
fields terminated by ','
;
95001,李勇,男,20,CS
95002,刘晨,女,19,IS
95003,王敏,女,22,MA
95004,张立,男,19,IS
95005,刘刚,男,18,MA
95006,孙庆,男,23,CS
95007,易思玲,女,19,MA
95008,李娜,女,18,CS
95009,梦圆圆,女,18,MA
95010,孔小涛,男,19,CS
95011,包小柏,男,18,MA
95012,孙花,女,20,CS
95013,冯伟,男,21,CS
95014,王小丽,女,19,CS
95015,王君,男,18,MA
95016,钱国,男,21,MA
95017,王风娟,女,18,IS
95018,王一,女,19,IS
95019,邢小丽,女,19,IS
95020,赵钱,男,21,IS
95021,周二,男,17,MA
95022,郑明,男,20,MA
load data local inpath './data/student.txt' into table student1;2）创建分桶表
create table student_bucket(
sno int,
name string,
sex string,
age int,
academy string
)
clustered by (sno) sorted by (age desc) into 4 buckets
row format delimited
fields terminated by ','
;3）加载数据
注意：分桶表加载数据时，也不能使用load，而是动态加载insert
方式1：使用insert into
insert into table student_bucket select * from student1 distribute by(sno) sort by (age desc);
;
方式2：使用 insert overwrite
insert overwrite table student_bucket select * from student1
distribute by(sno) sort by (age desc);4）注意事项：
1. 加载数据不能使用load, 必须使用insert,才会有分桶效果
2. 2.1.1版本的hive设置了强制分桶策略，修改reduceTask的数量没有意义
set mapreduce.job.reduces=4;
3.如果数据量比较大，我们可以使用MR的本地模式：
set hive.exec.mode.local.auto=true;
4.强行分桶设置：set hive.enforce.bucketing=true; 默认是false
5.强行排序设置：set hive.enforce.sorting=true;11.3 分桶的查询
1）分桶语法：
select ....from tablename tablesample(bucket x out of y on colname);
解析：
x表示要查询的第几桶 从1开始
y表示该表的总的桶数。 x不能大于y.
2.1.1版本的y可以自定义。2）整张表的查询语法
select * from student_bucket;
select * from student_bucket tablesample(bucket 1 out of 1);3）查询某一个桶的数据
查询4桶中的第一桶
select * from student_bucket tablesample(bucket 1 out of 4 on sno);
查询4桶中的第一桶和第三桶
select * from student_bucket tablesample(bucket 1 out of 4 on sno)
union
select * from student_bucket tablesample(bucket 3 out of 4 on sno);
20 %4 0 %2 0
19 3 1
18 2 0
17 1 1
16 0 0
15 3 1
14 2 0
简写：
select * from student_bucket tablesample(bucket 1 out of 2 on sno);
查询4桶中的第二桶和第四桶
select * from student_bucket tablesample(bucket 2 out of 2 on sno);
查询5桶中的第三桶
select * from student_bucket tablesample(bucket 3 out of 5 on sno);4）其他查询方式
查询三行数据
select * from student_bucket limit 3;
select * from student_bucket tablesample(3 rows);
查询百分比的数据
select * from student_bucket tablesample(13 percent);大小的百分比所占的那一行。
查询固定大小的数据
select * from student_bucket tablesample(68b); 单位（K,KB,MB,GB...）
固定大小所占的那一行。
随机抽三行数据
select * from student_bucket order by rand() limit 3;11.4 分区和分桶的总结
--1. 分区和分桶在建表时，关键字上都带ed
(1)分区的是partitioned by(colname type,.....)
(2)分桶的是clustered by (colname,....) sorted by(colname asc|desc)into num bucket
--2. 动态导入数据时，
(1)分区的是partition by(colname="static value",colname)
(2)分桶的是distribute by(colname) sort by(colname)
(3)当建表时分桶的字段和排序字段一致，并且是升序时，可以使用cluster by(colname)
insert into student_bucket select * from student1 cluster by (sno);
--3. 本质区别：
（1）分区的本质是分多个子目录来管理表数据
（2）分桶的本质是将目录中的大文件划分为多个小文件（桶文件）来管理数据
（3）两者都是优化手段，但是分桶比分区更细粒度。
（4）分区是表外字段，分桶是表内字段。十二、hive的序列化机制（熟悉）
12.1 SerDe的简介
- SerDe是“Serializer and Deserializer”的简称。
- Hive使用SerDe（和FileFormat）来读/写表的Row对象。
- HDFS文件-> InputFileFormat -> <key，value> -> Deserializer -> Row对象
- Row对象->Serializer -> <key，value> -> OutputFileFormat -> HDFS文件
注意，“key”部分在读取时会被忽略，而在写入时始终是常数。基本上Row对象存储在“值”中。
注意，org.apache.hadoop.hive.serde是一个过时的SerDe库。使用最新版本的org.apache.hadoop.hive.serde2。
可参考官网：https://cwiki.apache.org/confluence/display/Hive/DeveloperGuide#DeveloperGuide-HiveSerDe12.2 hive常用的内置SerDe库
- csv: 逗号分隔值
- tsv: tab分隔值
- json: json格式的数据
- regexp: 数据需要复合正则表达式12.3 LazySimpleSerDe的简介
默认的字段分隔符：^A
默认的行分隔符：\n
注意：hive的行分隔符只能是\n
也可以在建表语句上使用fields terminated by '\001'; <------使用^A的ascii码来指定分隔符
数据样本
1001^Azs^A23
1002^Alis^A24
建表
create table if not exists csv1(
uid int,
uname string,
age int
); -- 格式都没有指定，默认使用的就是LazySimpleSerde,记录分隔符是\n，列分隔符是^A
导入数据
load data local inpath './data/csv1.txt' overwrite into table csv1;
验证是否切分成三个字段
select * from csv1;
create table if not exists csv2(
uid int,
uname string,
age int
)
row format delimited
fields terminated by '\001';12.4 OpenCSVSerDe的应用
-- CSV 是Comma-Separated Values的简写，逗号分隔值，也可以称之字符分隔值
-- CSV 对应的具体SerDe类型是：org.apache.hadoop.hive.serde2.OpenCSVSerde.class
-- 默认列分隔值是逗号
-- 默认行分隔值是\n
-- 常用的三个属性：
1. 默认转义字符(DEFAULT_ESCAPE_CHARACTER): \ <--反斜线
2. 默认引用字符(DEFAULT_QUOTE_CHARACTER): " <--双引号
3. 默认分隔符(DEFAULT_SEPARATOR): ， <-逗号
-- 案例演示：使用CSVSerDe
-- 1. 不修改默认列的分隔符建表
drop table csv2;
create table if not exists csv2(
uid int,
uname string,
age int
)
row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
;
-- 2. 导入数据
1001,zhangsan,23
1002,lisi,24
1003,wangwu,25
1004,,26
load data local inpath './data/csv2.txt' into table csv2;
--案例演示：修改默认列分隔符的建表形式：
create table csv3(
uid int,
uname string,
age int
)
row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
with serdeproperties(
"separatorChar"="7",
"qutoeChar"="'",
"escapeChar"="\\")
stored as textfile
;
--导入数据：
10017zhangsan723
10027lisi724
10037wangwu725
10047726
load data local inpath './data/csv3.txt' overwrite into table csv3;12.5 JsonSerDe的应用
--1. hive本身没有JsonSerDe类型
--2. 可以使用自己编写的，也可以使用第三方的JsonSerDe类型
json-serde-1.3.8-jar-with-dependencies.jar
--3. 在使用JsonSerDe时，应该将jar包加载到classpath下
（1）可以使用add jar临时加载
（2）可以将jar包放入到hive的lib目录下
-- 数据如下：是一个json格式的数据，
{"uid":"1","uname":"gaoyuanyuan","age":"18"}
{"uid":"2","uname":"zhaoyouting","age":"42"}
{"uname":"fanbingbing","uid":"3","age":"42"}
load data local inpath './data/json1.txt' overwrite into table json1;
-- 为上述数据文件，创建表，使用JsonSerDe类型
drop table json1;
create table json1(
uid int,
uname string,
age int
)
row format serde 'org.openx.data.jsonserde.JsonSerDe';JSON的应用场景：复杂类型的使用
--1. 原始数据：
zs math:100,98,76 chinese:98,96,100 english:100,99,90
ls math:60,80,70 chinese:90,66,91 english:50,51,70
-------------------------------------------
create table t1(
uname string,
math map<string,array<int>>,
....
)
row format delimited
fields terminated by ' '
map keys terminiated by ':'
collection items terminated by','
----上述的表语句需要执行map的元素分隔符，还要指定数组的元素分隔符，都用到了collection.因此一个collection无法指定多个分割符。这种情况，只能换思路，使用比较合适的serde去导入数据(jsonSerde最适合)
-- 清洗数据：
可以使用编程语言转成下面json字符串
{"uname":"zs","score":{"math":[100,98,76],"chinese":[98,96,100],"english":[100,99,90]}}
{"uname":"ls","score":{"math":[60,80,70],"chinese":[90,66,91],"english":[50,51,70]}}
--建表：
create table if not exists complex(
uname string,
score map<String,array<int>>
)
row format serde 'org.openx.data.jsonserde.JsonSerDe'
stored as textfile
;
--导入数据
load data local inpath './data/json2.txt' into table complex;12.6 RegexSerDe的应用
-- 为什么要提供RegexSerde
因为其他的SerDe都不支持多字符作为分隔符，因此提供了正则SerDe.
-- 正则SerDe对应的具体类型：org.apache.hadoop.hive.serde2.RegexSerDe
--案例演示：
01||zhangsan||23
02||lisi||24
drop table t_regex;
create table if not exists t_regex(
id string,
uname string,
age int
)
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
with serdeproperties(
'input.regex'='(.*)\\|\\|(.*)\\|\\|(.*)',
'output.format.string'='%1$s %2$s %3$s'
)
stored as textfile
;
load data local inpath './data/regex.txt' into table t_regex;十三、hive表的存储格式（熟悉）
13.1 文件存储格式说明
hvie的文件存储格式指的是在写建表语句时，stored as "" 子句指定的格式，会在hdfs上使用相应的数据存储格式存储数据。
对应的属性：
--1. 默认的存储格式属性设置
<property>
<name>hive.default.fileformat</name>
<value>TextFile</value>
<description>定义hive建表的默认存储格式，可以是textfile,sequencefile,rcfile,orc,parquet之一，在建表期间可以使用stored as进行覆盖该属性的值</description>
</property>
--2. 内部表（管理表的）默认存储格式，当设置为none时，使用hive.default.fileformat对应的值。
<property>
<name>hive.default.fileformat.managed</name>
<value>none</value>
</property>
--3. 常用的存储格式分两大类，分别是纯文本文件和二进制文件
（1）纯文本文件指的是TextFile，也是默认的
（2）二进制存储文件有：sequenceFile,rcfile,orc,parquet压缩工具和文件的组织结构的区别
压缩工具指的是一些解压/解压缩软件，各对应一种压缩算法。比如gzip，bzip2,lzo.......
文件组织结构，指的是存储格式，比如行存储格式，列存储格式。行列混合存储格式。
行存储格式：指的是将数据按照行进行存储行与行之间要有分隔符的。
列存储格式：同一列的数据聚合到一起存储。在生产环境中：
一般要使用文件存储格式+压缩工具配合使用，更好的减少磁盘IO13.2 常用的存储格式案例演示：
参考文档十四、hive的索引和视图（自己小了解下）
十五、hive的压缩机制（自己小了解下）
十六、hive的优化（自己小了解下）
select * from rate; ---hive fetch
select * from rate limit 10,100;
hive在翻译成mr程序时，可能会有多个job进行并发或者时串行。
一个job一定对应一个mr程序。
8 stage --- 6 job
stage-0: fetch
stage-root: 可能会被翻译成mr程序。
job对应一个阶段，整个作业的阶段数量>=job数据
hive.limit.row.max.size=100000： 普通的limit操作时，每行的字节的最大数量不能超过默认值
hive.limit.optimize.limit.file=10
hive.limit.optimize.enable=false (如果limit较多时建议开启) hive.limit.optimize.fetch.max=50000 普通的limit操作时，最多fetch的行数(=-=,收!!!!总算把这几天所学给补上了,针对于hive的sql写法,大家可以百度上查找HIVE50道挑战题和HIVE场景题进行练习,难度适中,对sql的掌握还是有很多的提升,这债总算还完了哈,小白周末准备将这些天所学再看一遍巩固巩固,理论上的内容偏多,理解的去记忆会很有帮助,还有一方面注重培养下自己的逻辑能力,代码说到底重的还是逻辑,你只要逻辑通就没有做不出的需求,嗯就到这了,小白得趴一会,手废啦~~~)
扫一扫
5548
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
