- hive的三种安装模式
安装参考: - hive是一个映射工具
hive可以把sql翻译成分布式计算处理任务,hive能够把hdfs的文件映射成表 - metedata metastore hiveserver2 beeline hive客户端 之间的关系
- metadata
metadata是元数据,是描述hive的数据表和hdfs上的数据文件是怎么联系起来的。
或者说metadata就是描述数据文件映射成表的描述文件,当然还有一些事描述数据库等等的元数据。
默认是存储在内嵌的derby数据库中,可以指定到外部数据库中。
常见的数据库都可以用于存放hive的元数据mysql,oracle,postgresql,sql server,DB2 - metastore
metastore 负责存取元数据,但是它不支持多个客户端连接,多个连接需要由hiveserver2来维持。
独立启动的时候占用8083端口,启动hive客户端如果没有配置hive.metastore.uris参数会启动一个内嵌的不占用端口 - hiveserver2
维持和客户端的连接,支持多个客户端的远程连接,维持权限
hiveserver2通过hive-site.xml里面的hive.metastore.uris参数确定metastore的位置
hiveserver2默认占用10000端口,web界面端口10002 - beeline
一个可以连接hiveserver2的命令行工具 - hiveCli(已经弃用)
一个只能直接连接metastore的命令行工具
hive客户端通过hive-site.xml里面的hive.metastore.uris参数确定metastore的位置,如果没有这个参数就会启动一个内嵌的metastore。
- hive beeline的使用
- hive支持的数据类型
文档地址: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types#LanguageManualTypes-Intervals - 常见表语法
- 建表语句和指定分隔符
row format delimited 表示用限定符分割
使用定界符分割可以选择的子语句(使用的是序列化类是org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe)
- fields terminated by ’分隔符‘ 指定字段分割符,模式是 \001
- collection items terminared by ’分隔符’ 指定集合分隔符
- map keys terminated by ‘分隔符’ 指定map 分隔符
- lines terminated by char 行分隔符 默认值是
\n
- 分隔符是一个 char 表现为‘cahr’ 或者是 "\转义字符"
row format serde "serde全类名"
使用指定serde类
后面可以更上属性:with serdeproperties("xxx" = "xxx")
- hive的表没严格意义上的有主键,直接人为没有主键就行
- 复和类型的申明方式
- map 和 array类型在表中的使用
- hive数据文件存储位置
hive默认数据默认是放在 /user/hive/warehouse目录下的,由配置hive.metastore.warehouse.dir决定
使用默认数据库的时候直接在/user/hive/warehouse下面建立表名相等的目录
指定了数据库的时候,是在数据库对应的目录下面创建表
可以通过 location修改数据文件存放位置
- 查看表的定义
- 修改表的定义
- 外部表和内部表的区别,外部表被删除的时候数据会被保留,内部表被删的的时候数据会被删除。
temporary 是内部表,不写默认就是temporary
external 是外部表
内部表和外部表指的是hive对表数据文件的管理方式,和数据文件放在哪里没有关系
- 数据的分区
分区不是一个字段,但是查询的时候会把分区显示成一个字段
数据分区就是在表名目录下按照分区字段和分区字段值建立的文件夹,真实的数据放在分区文件夹下面的。
查询的时候带上分区字段,可以减少扫描范围。
- 分区数据数据静态导入和动态导入
静态导入:导入的时候人为指定分区字段,导入的数据都会去向同一个分区
动态导入:使用表里面的字段动态导入数据,导入的数据进入不同分区
创建分区表
静态导入:load data [local] inpath xxx into table xxx partition(分区xxx=‘xxx’);
动态导入:insert into/overwrite table xxx partition(xxx) select * from xxx;
- 动态分区 严格模式和非严格模式区别
严格模式要求至少一个静态分区字段
非严格分区,所有分区都是可以不指定,严格模式下不写分区关键字也能有非严格模式的效果。
- mr严格模式和非严格模式
默认是没有设置,等价于非严格模式
区别
- 严格模式要求不能全部扫描
- 严格模式要求 排序必须分页
- 严格模式要求join必须带上on
mr严格模式下例子
- 数据的分桶
分桶是按照 指定字段的hash模分桶数量,然后把数据分配到指定的bucket中
- 分区和分桶的区别
- 分区不一定依赖字段值,分区值可以是指定的固定值,分桶必须指定字段
- 分区可以无限多,分桶数量数定义表的时候定义好分桶数量的
- 分区字段在数据文件中不存在,实际是个目录固定值,分桶按是按照编号切分文件为几块,分桶字段真实存在
- where 带上分区分桶字段都可以避免全表扫描,分桶字段用于 join on 还可以桶内join(SMB join),减少匹配次数
- 分桶可以用于抽样
- 分区的数据和分区无关,可以随意移动到别的分区。分桶的数据和分桶字段有关,分桶文件名就是桶编号
- 定义表的时候分区在前,分桶在后。数据文件存放目录,分区在上,分桶在下。
- explain 执行计划 查看方式
格式:explain [formated]|[extended]|[dependency]|[authorization] querysql;
extended:可以看到更多执行计划
formatted:返回json格式的执行计划
dependency: 以json格式放回查询所以来的表和分区列表
authorization:列出需要被授权的条目,包含输入输出
explain 能够看到sql 执行时候的阶段依赖和每个阶段执行步骤 - hive的表默认是不能修改和删除的,只能插入和查询,事务表才能修改数据
- hive事务的使用
事务表可以修改和删除数据,普通表不可以
hive的事务有很多限制,不支持保存点,不支持roolback,不支持手动提交,只能自动提交。
创建事务表
创建事务表需要的条件
- 需要内部表,外部表不能申明为事务表
- 需要存储格式orc, stored as orc
- 需要表设置开启事务配置; tblproperties('transactional'='true')
- 设置事务管理器; SET hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
- 需要开启并发; SET hive.support.concurrency = true;
- 不支持load data 导入文本文件,但是可以导入 orc 格式的数据
- hive的视图
- 查看视图定义,查看视图列表
视图效果类似命名的子查询,不能提升速度,相当于查询结果集分封装
- 物化视图的使用
只有事务表才能创建物化视图
物化视图会和真实的表一样在/user/hive/warehouse下面创建一个同名目录。
物化视图不会自动更新,需要主动触发
查询原表的时候会自动触发查询物化视图,所以物化视图一定程度上可以代替索引
- 创建数据库相关的命令
- 删除表数据,清空表,删除表
- 手动修改了hdfs分区目录修复
手动添加和删除分区文件夹hive是无法识别的。存在mysql里面的元数据没变,可以通过 msck repair修复。
msck 是 metastore check 的缩写
- 使用load命令导入数据
load data [local] inpath xxx into table xxx partition(分区xxx=‘xxx’)
load 命令的 local 是 hiveserver2本地,不是最外层客户端的本地
load 本地文件是复制,load hdfs文件是mv
load data 导入文件是文件移动,所以存储格式是什么被导入的文件格式也必须是什么。
3.0 以后load 不指定分区会使用文件的最后一个字段作为分区字段(多个分区字段的时候,使用的最后几个字段作为分区字段)
load 可以导入到分区表和分桶表,并且会把数据分散开也会触发数据的重排序,感觉这里是读取数据然后走的查询插入,正常整个文件导入在hdfs上是文件移动,如果是导入分区表分桶表原数据文件依旧会存在。
- insert into 和 overwrite 的区别
overwrite 是清空表然后再插入,没有主键没有唯一标志,不存在更具id覆盖单行记录。
- 多重插入,查询一次插入多张表
- 动态分区插入数据,insert into select 可以用查询的最后一个字段作为分区字段,和 load data 类似。
- hive数据的的导出
需要注意的是 hive的导出是覆盖指定路径,如果目录里面有其他文件会被全部删除,如果是/
路径被覆盖就完蛋了
insert overwrite directory xx,只能是 overwrite 不能是into,只能是覆盖没有插入。
export 导出
- 事务表的修改是通过增量文件来实现修改和删除的
编号1,2是新增数据产生的delta数据,后面的编号是事务Id和操作Id
编号3是删除数据产生的delete_delta数据
编号4是修改数据产生的数据,相当于删除了在添加
hive事务表的会产生大量小文件,这些碎片文件可以通过合并的方式变成大文件,文件合并器位于metastore里面。
非事务表插入数据也会产生小文件 ,是文件名_copy的小文件,新的数据放在这里面 - 事务表的修改实现方案合并策略
小合并(minor compation):合并delta文件,由 hive.compactor.delta.num.threshold控制,默认是10,表示有10个delta文件时候触发
大合并 (major compaction):合并delta文件和总的数据文件,由 hive.compactor.delta.pct.threshold,默认是10,表示delta文件占比10%触发合并。
- orc数据文件可以使用 orc tools 打开
- orc格式是一种 hive推荐使用,但是有没有默认使用的存储格式
orc 是 Optimized Row Columnar的缩写,优化行列格式 - hive的常见查询
- set 参数可以直接查看属性当前值
不加属性就能直接查看属性
- order by,cluster by, distribute by sort by
order by:全部数据合并排序,只有一个reduce
cluster by:数据分桶以后,再排序,有多个reduce,分桶和排序字段相同
distribute by sort by::数据分桶以后,再排序,有多个reduce,分桶和排序字段可以不同
- union ,union distinct ,union all
union 等价于union distinct,都会区重复
union all 不会区重复,不会重排序
- 子查询和相关子查询
子查询如果用上了外层记录的值就是相关子查询,这种子查询在关系型数据库中效率很低,需要对每条外层子查询都执行一次。
mr里面感觉性能差异没有那么大
- CTE表达式,with的使用
CTE是 common table expression的缩写,通用表表达式
with 定义一个零时查询结果集,效果类似临时表。
- 6种join的区别
- inner join 内连接
- left join 左外连接
- right join 右外连接
- full outer join 全连接
- left semi join 返回交集部分,但是只返回左边表的数据,右边的不返回
- cross join 返回笛卡尔积
- join on 能写不等于
- 隐式连接
- join 的指定流表
join的时候建议小表写前面,小表写前面的时候会被hive优化成 map join ,缓存前面的表,和后面再map端做join。
也可以手动指定mapjoin
- hive的 命令行参数
详情参考:hive --help 和 hive --H
- beeline客户端命令行参数
详情参考: beeline --help
- hive的配置文件 ,和设置方式
官网-->Documentation-->Language Manual-->更多子页面-->Configuration Properties
hive属性配置文档地址: https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties conf目录下的hive-default.xml.template也有很多默认配置的描述
设置属性的三种方式:
- 属性的设置可以配置到 hive-site.xml文件里面
- 可以启动命令行的时候用 --hiveconf 制定
- 可以启动客户端以后通过set 指定
- hive的函数,和文档查看方式
查看所有函数:show functions
查看一个函数的用法:desc function [extended] 函数名
函数文档地址: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF,有关函数,运算符,复杂类型的使用(array,map,struct等)都在这里UDF,UDTF,UDAF 本来是指 用户定义普通函数,用户定义表函数,用户定义聚合函数,后来系统定义的函数也这么叫了,UDF 统代指单入单出的函数,UDAF统代指多进单出的函数,UDTF统代指一进多出的函数
值得一提的是 字符串连接符号是 || 不是+,hive 把操作符也作为函数的。 - 可以通过编写Java代码定义新的函数
- 基础 UDF实现功能打成jar包
- 上传到hs2 或者hdfs,使用add jar "hdfs地址/aaa.jar" 添加
- 注册函数: create temporary function 函数名 as 'UDF类全路径'
- hive的 select 可以不带 from
hive默认没有内置dual表,如果想用可以建立一个只有一行空数据的dual表
- explode函数的使用,它是一个UDAF
- explode 和 lateral view
explode 函数不能和普通字段一起使用,只能通过侧视图连接使用
lateral view UDTF 相当于关联了一个表,默认类似 inner join
- count(*,字段,1) 在 hive 里面 有区别
如果是count(字段) 里面 null 的字段不会被统计,count(*
),和count(1)都是统计的行,mysq之类的关系型数据库也是一样的
同理 != xx 和 = xx 也不会统计 null值。
- UDAF 配合 struct的使用
struct的排序有关的默认 都是用的第一个字段
- grouping sets ,
grouping__id
效果类似 多个维度分组,然后union all 连接起来grouping__id
是内置的,用于区分不同分组维度,注意是两条下划线
- with cube
效果类似123,12,13,23,1,2,3,空 8个维度分组
等价于 grouping sets 列出所有可能的维度
- with rollup
效果类似123,12,1,空 4个维度分组
等价于列出分组维度依次减少一个(最后一个)的所有情况
- 开窗函数
- 开窗从句
用于限定区内取值范围,如果没有order by默认是区内全部,如果有排序是区内第一行到当前行
rows和ranges,对于排序字段重复值取值不同
边界取值
- 开窗函数可以使用 排序函数 rank() dense_rank() row_number()
- 开窗 函数中可以使用 聚合函数max(),min(),avg(),count(),sum()
- ntile 分成N组,分不均优先给编号小的组,各组数据相差不超过1个
- 取区内指定行的数据, lag(取前面第几行) 和lead(取后面第几行),first_value, last_value,nth_value
- 数据抽样
随机抽样,优点随机,但是慢
数据库抽样,优点快,但是数据不随机,取的是连续的数据
tablesample( 1 rows )
tablesample( 50 percent)
tablesample( 1k )
分桶抽样,相对来说比较随机,也比较快,只用按照规则计算出需要被抽样的那块桶里面的数据就行。
tablesample( bucket x out of y on filed)
- 多字符作为分隔符的表数据处理方案
- 清洗数据(清洗成可以识别的单字符分隔符)
可以用mr清洗,数据量小可以直接用Java程序读取文件然后清洗 - 使用正则serde
- 使用自定义 inputformat 格式化 输出数据,使输入数据可以用默认的Serde解析
- 编写解析类 实现 inputformat ,或者它的子类
- 上传jar包,并且 使用add jar xxx 添加 jar包
- 创建表的时候 制定 inputform "解析类全类名"
- hive数据输入输出和序列化反序列化
数据读取和序列化房序列化是分开的,InputFormat 读取数据,OutputFormat输出数据,SerDe 序列化和反序列化数据
使用 desc formatted 表名可以看见 输出输出和系列化反序列化类
普通分隔符分隔的表( row format delimited char),表结构部分表述
SerDe Library: | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe |
InputFormat: | org.apache.hadoop.mapred.TextInputFormat |
OutputFormat: | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat |
正则分隔的表(row format serde xxx),表结构部分表述
SerDe Library: | org.apache.hadoop.hive.serde2.RegexSerDe |
InputFormat: | org.apache.hadoop.mapred.TextInputFormat |
OutputFormat: | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat |
- hive里面查询更多详情基本都是 formatted ,但是 explain 和 desc fcuntion 后面是加 extended 查看更多详情。
- url path 解析
parse_url 一次只能输出url的一段,是udf函数
parse_url_tuple 一次可以解析url的多段,它是UDTF函数,不能和普通字段一起查询,如联查需要 侧视图
- json数据的的使用
get_json_object()
一次只能获取一个key的值
json_tuple()
这是一个UDTF 一次可以获取多个
JsonSerDe
- 行转列
多行转多列
分组+case when + 极值函数
先分组把所有user_id相同的记录划为一组,这时候可以通过 case when 判断科目的值是什么科目,去除分数用科目命名,max的作用是选出需要显示的行,相当于需要case when 的默认值是 null 或者一个极小值。如果是用min 函数默认值就需要时极大值。
多行转单列
用多行转多列的方法得到多列,然后使用concat_wt ,collect_list ,collect_set等函数联合多列为单列
- 列转行
多列转多行
union all 实现列转行get
单列转多行:
使用 UDTF 函数把一行变成多行然后和原理的数据 侧视图连接
也可以用查询的时候把单列多次查询裁剪得到多列,然后使用 union all 连接
- 连续登录问题(连续两天登录就算连续登录)
已知user_id,登录日期,并且每人每天只有一条登录记录,求连续登录的用户
- join 解法(只能得到连续2天登录)
登录记录自join,连接条件是user_id相等,并且筛选左右表登录时间字段相差是1天的记录,然后user_id去重 - lead 和 over 实现查询连续N天登录的用户
按照user_id分区时间排序,然后通过lead取到下N条的记录,和当前记录登录时间延后N天做对比,相等就是需要找到的记录,然后user_id去重
- 累积求和问题
已知用户每月消费的金额,和月份信息,求截止到每月当月累计总消费
- join
自join,条件是user_id相等 ,以左表为参考,过滤掉月份大于当前月份的记录(这时候右表的金额和就是截止当当月的总消费),然后按照user_id和左表月份分组,组内右表消费金额求和就是截止到每月当月累计总消费 - sum over
按照用户分区,月份排序,开窗函数默认取值是区内第一条到当前条,求和就能得到当每月前月累计总消费
- 分区求 topN 问题
比如按照区域划分,求销量的前3
row_number over( 分区 销量排序) as rowNum 然后外层筛选rowNUm <= 3的记录 - 拉链表,目的是用于存储数据变化的过程
在拉链表中用数据的开始和结束时间记录数据的状态
新增的数据开始时间是当前时间,截止时间是一个极大值
修改的数据的新行开始时间是当前时间,截止时间是一个极大值
修改数据的旧行需要更新结束时间是当前时间,这样可以记录数据的变化过程
当同步过来一个增量表的时候,拉链表的处理方案如下(增量数据里面有一部分是新增的数据,有一部分修老数据被修改的记录)
用拉链表和传过来的增量表左关联,(右边不是空&&左边的结束时间是极大值)的记录就是修改的数据的次新记录,需要把结束时间改成当前时间。这样得到临时表已经修正了结束时间的旧记录表,用它和增量数据 union all,得到新的拉链数据。 - hive 的索引,数据更新以后需要手动重建,生成慢,3.0以后已经移除不能在使用了
感觉hive 的索引类似物化视图,重构索引的时候也是走的mr程序然后把结果保存到hdfs。
- hive支持的文件存储格式
- textFile
格式是默认值,只有textFile 才能用load data 导入文本格式 - sequenceFile
是hadoop用来存储序键值对的二进制文件格式 - parquet
是一种列式存储文件格式 - orc (Optimized Row Columnar)
需要工具查看,是hive推荐的格式,事务表必须要使用orc格式
- hive的压缩 和 mr压缩
关于压缩的配置
压缩减少了网络io,但是会增加解压文件的cpu占用,压缩主要在三个地方
- 输入文件压缩,这是输入数据前,压缩,mr只能控制解压
- map,map向reduce 阶段传达数据的时候压缩数据,减少io
- reduce,输出结果的时候减少磁盘占用
- 小文件优化方式,合并小文件
如果输入文件是小文件可以用CombineHiveInputFormat合并的
执行过程有关小文件合并的参数默认值
- orc存储格式的 行组索引的使用
orc存储格式的表可以使用行组索引,也叫最大值最小值索引,通过判断查询值是否处于最大值最小值范围内快速筛选数据。
使用行组索引需要 orc存储格式和 orc.create.index"="true - stored as orc
- tblpropertyies("orc.create.index"="true")
- orc存储格式 布隆过滤器使用
- orc存储格式还有布隆过滤器来体改查询效率,不命中的数据直接跳过。
使用布隆过滤器需要orc存储格式和指定过滤字段 - stored as orc
- tblpropertyies("orc.bloom.filter.columns"="字段名1,字段名2")
- orc 存储格式的 矢量查询
hive默认查询执行引擎是一次处理一条数据,使用orc格式以后默认使用矢量化查询,一次处理一批数据(1024条)
- mr优化,job的 本地模式执行
使用本地模式需要满足4个条件
- 输入文件数小于等于4个:hive.exec.mode.local.auto.input.files.max指定
- 输入数据小于128Mb,由hive.exec.mode.local.auto.inputbytes.max指定
- map task 数量小于等于4,由hive.exec.mode.local.auto.tasks.max指定
- reduce task 小于等于1
- jvm重用
hive3.0已经弃用,效果类似flink的 算子链合并,不是特别消耗资源的子任务可以放到一个节点执行,减少计算资源的创建和销毁 - 并行执行,并行执行线程数
有些job是可以多并行执行的,比如union的子查询
默认是没有开启并行执行的
- 并行和并发的区别
并行一般是描述发起方,比如一个cpu核心可以做到并行(多线程轮换跑多个任务),并发一般描述的提供服务的方式,支持并发及支持同时多个客户端操作。
并行不一定需要并发支持,并行的任务可以是轮换的,并没有要求同一时间有多个任务在执行。
并发一般来说是有并行的,同一时时刻处理多个客户端段请求,需要有并行的处理流程。 - join 优化
hive会自动选择走 map join 还是 reduce join
- map端 join,(有小表的情况hive优先选着这种方式)
- reduce端 join 都是大表的情况
- 可以通过bucket优化 reduce join,对表分桶分桶数相等,或者成倍数,join on字段是分桶字段(bucket 排序更好),可以提高reduce join 效率,reduce阶段是bucket 内的数据join
- 执行过程优化
关联优化
- 优化器选择和分析器使用
- RBO ( rule basic optimise) 基于规则优化器,在没有提前执行分析器的情况下的默认选择
- CBO (cost basic optimise) 基于代价优化器,CBO的执行效率更好,但是需要提前分析才能有效果,,需要配合分析器使用
- 谓词下推
PPD (predicate Pushdown):将过滤表达式尽可能的提前到map阶段,以提高效率,数据越早筛选掉无效数据,查询效率越高
join 的时候条件写在 on后面是还卸载where 后面谓词下推启用情况
推荐用法
个人对谓词下推出怀疑态度,触发了谓词下推,不一定是效率更高,只是因为这种写法可以被优化,不是这种写法效率高,不触发下推可能已经足够优秀。
- 数据倾斜问题
数据倾斜主要发生在 group by 和 count( distinct )中
- 开启map端聚合,有些任务是可以直接在map端就完成一部分聚合的,这样可以减少reduce端的数据量
- 开启数据倾斜是自动负载均衡
开启以后程序会自动通过两个mr来运行
一个mr自动随机分发数据到reduce中,多个reducer做分区聚合,输出给第二个mr
第二个mr汇总前面多个reducer的结果得到最终结果
- join 也能使用 skew
skew join:如果两张大表 join的过程会发生数据倾斜,hive会把倾斜的数据单独出来执行 map端join,别的数据执行 reduce join,避免倾斜数据在 reduce端执行过长时间。
开启join skew需要开启的配置 - hive 已经不建议使用mr了,推荐使用 TEZ 计算引擎和spark引擎,优先考虑使用spark
mr的效率太低了,出于历史原因 hive 默认计算引擎还是mr,但是已经不推荐使用 - LLAP(Long live and process) 是一个 可以运行在 hive 和 datanode之间的中间程序,它可以做一些数据缓存,做一些小数据量的计算,相比直接读取dataNode效率更高,可以划分LLAP中资源为多个池,自动映射计算程序到不同的池 ,只有TEZ才支持 LLAP
- hive3.0 以后metastore可以独立部署,不依赖hive部署,这样别的软件可以在没有hive的情况下 直接和 metastore 集成,比如 spark。