devbox
笔触狂放9
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Hive基础知识(个人总结)_hive字段类型

作者：笔触狂放9 | 2024-07-03 04:38:35

赞

踩

hive字段类型

    声明: 1. 本文为我的个人复习总结, 并非那种从零基础开始普及知识 内容详细全面, 言辞官方的文章
              2. 由于是个人总结, 所以用最精简的话语来写文章
              3. 若有错误不当之处, 请指出

一. 基础

Hive的特点:

Hive 的执行延迟比较高, 不支持直接修改, 只能进行insert owewrite覆盖

迭代式算法无法表达

Hive架构:

在这里插入图片描述

Hive和数据库比较

Hive并不是数据库, 自身没有存储数据的能力; 本质是 HDFS和MySQL里存的元数据进行表结构的映射

Hive和数据库除了拥有类似的查询语言, 再无类似之处

数据是否允许修改, 是否有索引, 数据规模方面都有差异

Hive基础命令:

hive -e “sql语句”

hive -f “sql文件路径”

对于HDFS的操作, 前缀dfs即可, 如:dfs -ls /

日志:

~/.hivehistory 用户执行的hive命令操作记录

/tmp/用户名/hive.log 运行日志

修改 hive 的 log 存放日志到/opt/module/hive/logs:

  1. 修改conf/hive-log4j2.properties.template名称
  2. hive.log.dir=/opt/module/hive/logs

参数设置:

参数的配置三种方式:

  1. 配置文件方式 hive-default.xml和hive-site.xml

  2. 命令行参数方式 启动 Hive 时，hive -hiveconf mapred.reduce.tasks=10

  3. 终端里输入命令

    ​ set 查看所有配置

    ​ set xxx 查看xxx参数的值

    ​ set xxx 1 设置xxx参数的值

优先级: 配置文件<命令行参数<终端里输入命令

注意某些系统级的参数, 例如 log4j 的设定必须用前两种方式设定, 因为那些参数的读取在会话建立以前已经完成了

坑:

  • Hive中 order by后跟的聚合字段, 必须得在select中出现

  • Hive中 子查询表必须起别名

  • hive中 join查询中的on只支持等值连接, 不支持非等值连接

    -- 不支持的写法
SELECT *  
FROM table1  
LEFT JOIN table2  
ON table1.x LIKE CONCAT('pre_' , table2.y )

-- 支持的写法
SELECT *  
FROM table1  
LEFT JOIN table2  
ON TRUE
WHERE table1.x LIKE CONCAT('pre_' , table2.y ) 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

  • substr(string A, int start, int len) 和 substring(string A, int start, int len) 用法一样
    功能：返回字符串A从下标start位置开始，长度为len的字符串，下标从1开始

二. Hive数据类型

基本数据类型:

Hive 数据类型Java 数据类型
TINYINTbyte
SMALINTshort
INTint
BIGINTlong
BOOLEANboolean
FLOATfloat
DOUBLEdouble
STRINGstring
TIMESTAMP

String 类型相当于数据库的 varchar 类型, 该类型是一个可变的字符串; 不过它不能声明其中最多能存储多少个字符, 理论上它可以存储 2GB 的字符数

集合数据类型:

  1. STRUCT struct<street:string, city:string> 字段.city

  2. MAP map<string, int> 字段[key]

  3. Array array<string> 字段[index]

例, 建表语句

create table test(  
    name string,
    address  struct<street:string, city:string>,
    children  map<string, int>,
    friends array<string>
)

-- 建表语句后的参数设置(设置导入源文件的格式)
row format delimited fields terminated by ','     -- 导入文件数据格式的 列分隔符
lines terminated by '\n';	-- 导入文件数据格式的 行分隔符
collection items terminated by '_'   -- MAP,STRUCT,ARRAY内部的数据分割符号
map keys terminated by ':'    -- MAP中的key与value的分隔符

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

插入数据示例:

zhangsan, xingfu_beijing, zhanga:18_zhangb:19, lisi_wangwu

查询语句:

select address.city, children['zhangsan1'], friends[1]

-- 得到:      beijing   18   lisi

  • 1
  • 2
  • 3

类型转化:

  1. 隐式类型转换

    TINYINT -> INT -> BIGINT

    TINYINT、SMALLINT、INT、BIGINT、FLOAT 、STRING -> DOUBLE

    TINYINT、SMALLINT、INT -> FLOAT

    BOOLEAN 类型不可以转换为其它类型

  2. 强制转换

    CAST(‘1’ AS INT) 将把字符串’1’ 转换成整数 1, 如果转换失败则返回 NULL

三. DDL 数据定义

数据库默认存放在HDFS上的 /user/hive/warehouse/*.db下

创建数据库语句模板:

create database if not exists demo01 comment '测试' location '/my/demo01' with dbproperties (createtime=20170830, aaa=bbb)

  • 1

修改数据库:

alter database demo01 set dbproperties('createtime'='20170830');

  • 1

创建表语句模板:

create external table if not exists t1(
name string
) comment 't1临时表'
partitioned by (dt date )  
clustered by (name)
sorted by name desc
into 3 buckets
row format fields terminated by '\t'
serde '序列化反序列化名称'
stored as textfile 		-- SerDe是Serialize/Deserilize的简称
location '/my/demo01/t1'
tblproperties (key=value, key=value)
as select username from user;   -- 复制现有的表结构, 并且复制数据
-- like select username from user; 复制现有的表结构, 但是不复制数据

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

字段解释说明:

  1. EXTERNAL 外部表, 只有元数据可以被删,实际数据保留(实际工作中常用)

  2. PARTITIONED BY 分区字段

  3. CLUSTERED BY 分桶字段

  4. SORTED BY 不常用, 对桶中的列排序

  5. ROW FORMAT DELIMITED

    • FIELDS TERMINATED BY ‘\t’

    • COLLECTION ITEMS TERMINATED BY ‘\t’

    • MAP KEYS TERMINATED BY ‘\t’]

    • LINES TERMINATED BY ‘\t’

  6. SerDe 是Serialize/Deserilize的简称,指定自定义的 SerDe

  7. STORED AS

    • SEQUENCEFILE（二进制序列文件）
    • TEXTFILE（文本）
    • RCFILE（列式存储格式文件）

  8. as sql查询语句 根据查询结果创建表,复制数据

  9. like sql查询语句 复制现有的表结构, 但是不复制数据

修改表:

-- 注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法, 区分大小写
alter table t1 set tblproperties('EXTERNAL'='TRUE')

  • 1
  • 2

desc formatted t1; 可以查看表类型是否为EXTERNAL

重命名表名字:

ALTER TABLE t1 RENAME TO t2

  • 1

更新列:

alter table t2 change column name myName string

  • 1

增加列:

alter table t2 add columns(password string)

  • 1

增加和替换列:

alter table t2 replace columns(username string, age int);

  • 1

四. DML数据操作

数据导入方式:

  1.  -- load data方式  只是数据存储格式校验+剪切
 load data [local] inpath '数据的 path' overwrite/into table t2 [partition(dt='2020-06-14')]

    • 1
    • 2
  2.  -- insert select方式
 insert t3 select * from t2

    • 1
    • 2
  3.  -- create table as select方式
 create table if not exists t4 as select name from t3

    • 1
    • 2
  4.  -- import方式, 注意它读取的文件是export命令导出的文件, export和import主要用于两个Hadoop平台集群之间Hive表迁移
 export table default.t4 to '/root/t4.dat'
 import table t5 from '/root/t4.dat'

    • 1
    • 2
    • 3

如果直接把数据上传到HDFS, mysql里没有该数据的元数据信息, 是感知不到这些文件的, 需要用msck repair table student修复

数据导出方式:

  1.  -- 将查询的结果格式化导出
 insert overwrite [local] directory '/root'
 ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
 select * from t2

    • 1
    • 2
    • 3
    • 4
  2.  -- Shell命令将查询的结果复写输出到本地文件
 hive -e 'select * from t2' >> /root/result.txt

    • 1
    • 2
  3.  -- Hive里输入Hadoop命令get到本地
 dfs -get '/my/demo/t2.txt' '/my/demo/t2.txt.bak' 

    • 1
    • 2
  4.  -- export命令导出
 export table default.t4 to '/root/t4.dat'

    • 1
    • 2

五. DQL查询

查询语句语法:

select (distinct) *
from student
where score>80
group by class
order by age
distribute by col_list  
sort by col_list
limit 10

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

with … as 语句:

with t1 as
select id,name
from user

  • 1
  • 2
  • 3

运算符:

  • 基于sql的正则表达式匹配: name like ‘张%’ name not like ‘张%’

  • 基于java的正则表达式匹配: name rlike ‘张%’ name regexp ‘张%’ java的正则语法更强大

支持各种join, 包括 full join

排序:

  1. order by: 全局排序, 只有一个 Reducer 所以慎用order by, 除非有过滤条件或limit之类的减少数据量
  2. sort by: 每个 Reduce 内部排序, 可以有多个Reducer

分区 distribute by:

分区是把数据分到不同的reducer上, hash%reducer数量

distribute by 常和 sort by混用; 当二者所用字段相同时, 可以使用 cluster by 简写

分区表:

  1. 分区可以避免全表扫描, 只查询指定分区内容即可

  2. 分区就是分目录, 让数据分开存放更有条理

创建分区表: 加上 partitioned by(dt string)分区字段不能是表中已有的字段, 它像是一个伪列, 可以指定多个字段形成多级分区

静态分区:

往分区表插入数据:

insert overwrite user partition(dt='2020-06-14') select id, name from....

  • 1

动态分区:

insert overwrite user partition(dt) select id, name, createTime from....
-- 最后一个字段即为动态分区字段dt

  • 1
  • 2

默认是strict模式: 要求至少一个分区为 静态分区

nonstrict 模式: 所有的分区字段都可以使用动态分区

分桶表:

分区针对的是数据的存储路径, 而分桶针对的是数据文件, 是更细粒度的数据范围划分, 一般不用它

分桶表操作需要注意的事项:

  1. reduce 的个数设置为-1, 让 Job 自行决定需要用多少个 reduce; 或者设置reduce个数>=分桶表的桶数
  2. 不要使用本地模式

可用于抽样查询: select * from student tablesample(bucket 1 out of 4 on id); 抽取一个桶进行抽样统计

六. 函数:

UDF: 进一出一

UDAF: 聚合函数

UDTF: 炸裂函数

nvl(username, “xxx”)

substring(str, index, length)

case sex when ‘女’ then 1 else 0 end

多个函数嵌套组合: sum(if(xxx))

行转列(合并列):

concat(str1, str2, …)

concat_ws(分隔符, str1, str2, …)

collect_set(col): 对某字段的值进行去重汇总, 返回 Array

列转行(炸裂行):

explode(array/map)

lateral view: 如movie和type两个字段, category是个Array类型,炸裂后行数>movie; 所以lateral view把 movie行数扩充去适配炸裂后的type行数

示例:

select
movie, category_name
from
movie_info 
lateral view explode(split(category,",")) movie_info_tmp as category_name;

  • 1
  • 2
  • 3
  • 4
  • 5

开窗函数 over( ):

类似于group by,

但group by的粒度是对整组的所有数据进行操作, 而开窗可以选取当前窗口的部分数据进行操作;

group by是一组数据里用的是同一个组, 而over是每行数据都是一个窗口

一行匹配多行时(对每一行数据进行开窗), 有点类似lateral view使之匹配

总的来说, 有聚合类开窗(sum, count), 有排序类开窗(rank)

聚合类开窗:

sum(cost) over(partition by name order by date rows between 1 preceding and current row) as total

先order by 才能rows

rows:

current row： 当前行

n preceding：相对于当前行 之前的n行数据

n following： 相对于当前行 之后的n行数据

unbounded preceding: 表示从最前面的起点开始

unbounded following: 表示到最后面的终点

  1. 所有行相加

    sum(cost) over( ) as sample1

    • 1

  2. 按name分组, 组内数据相加

    sum(cost) over(partition by name) as sample2

    • 1

  3. 按name分组, 组内数据累加

    sum(cost) over(partition by name order by orderdate) as sample3

    • 1

  4. 按name分组, 由起点到 当前行的聚合

    sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row ) as sample4

    • 1

  5. 按name分组, 当前行和前面一行做聚合

    sum(cost) over(partition by name order by orderdate rows between 1 preceding and current row) as sample5

    • 1

  6. 按name分组, 当前行和前边一行及后面一行

    sum(cost) over(partition by name order by orderdate rows between 1 preceding and 1 following ) as sample6 

    • 1

  7. 按name分组, 当前行及后面所有行

    sum(cost) over(partition by name order by orderdate rows between current row and unbounded following ) as sample7

    • 1

lag(col,n,default_val)： 往前的第n行数据, 当向上n行 为NULL时, 取默认值；

​ n如果不指定, 则为1

​ default_val如果不指定, 则为NULL

lead(col,n, default_val)： 往后的第n行数据, 当向下n行 为NULL时, 取默认值；

​ n如果不指定, 则为1

​ default_val如果不指定, 则为NULL

ntile(n)： 给数据大致分组, 比如90个数据进行大致尽量均分成n个组

案例:

  1. 查询前20%数据:

    select * from (
	select name, ntile(5) over(order by orderdate) sortedTmp 
        from business
) 
where sortedTmp = 1;

    • 1
    • 2
    • 3
    • 4
    • 5

  2. 查询在2017年4月份购买过的顾客及总人数

    select name, count(*) over ( ) 
from business
where substring(orderdate,1,7) = '2017-04' 
group by name;

    • 1
    • 2
    • 3
    • 4

  3. 查询顾客的购买明细及月购买总额

    select name, sum(cost) over(partition by month(orderdate)) 
from business;

    • 1
    • 2

  4. 查看顾客上次的购买时间

    select name,
	  lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1
from business;

    • 1
    • 2
    • 3

排序类开窗:

三种rank:

  • ROW_NUMBER( ) 根据行号顺序; 1,2,3,4,5

  • RANK( ) 并列时会重复, 总数可能不变; 像成绩排名: 1,2,2,4,5

  • DENSE_RANK( ) 并列时会重复, 总数会减少; 1,2,2,3,4

示例:

rank( ) over(partition by subject order by score desc) rp

  • 1

七. 存储格式:

存储格式:

textfile(不做处理, 行式存储)
sequence file(二进制格式, 占内存比textfile略小些)
orc(自带略微压缩, 列式存储)
parquet(自带略微压缩, 列式存储), 像.docx, 没有被7zip压缩照样自带压缩

  • 在不额外lzo或者snappy压缩时, 三者查询效率差不多(当然还是没自带压缩的textfile快), 但是存储效率ORC最高占存储空间少

  • lzo可以额外建立索引文件使之能切片, 而snappy不能切片, 但是可以人为控制文件输出的大小, 生成snappy文件时使每个snappy不超过128M之类的

  • 不使用额外压缩, 就把额外描述的压缩参数设为None

  • MR最好采用orc存储格式

  • Spark自身对parquet优化, 最好用parquet存储格式

  • orc和parquet文件存储结构都差不多, 都是先分行组, 行组内再列式存储(这一列存完了, 再存下一列)

  • ads层 hive导出到mysql时, 用的是hdfs文件不走hive, 故采用原格式textfile

八: 优化:

  1. 谓词下推

  2. 只select 必需的字段, 不用select *

  3. 尽量使用分区字段, 避免全表扫描

  4. MapJoin

    set hive.auto.convert.join=true; --默认为 true

  5. MR时, 使用ORC列式存储; Spark时使用Parquet列式存储

  6. 采用分区技术

  7. 输入端对小文件进行合并 CombineHiveInputFormat(默认)

  8. 输出端对小文件进行合并

    相关参数:

    SET hive.merge.mapfiles = true; 	-- 默认true, 在map-only任务结束时合并小文件
SET hive.merge.mapredfiles = true; 	 -- 默认false, 在map-reduce任务结束时合并小文件
SET hive.merge.size.per.task = 268435456;	     -- 默认256M
SET hive.merge.smallfiles.avgsize = 16777216; 	-- 当输出文件的平均大小小于16m该值时, 启动一个独立的map-reduce任务进行文件merge

    • 1
    • 2
    • 3
    • 4

  9. 开启map端combiner(不影响业务逻辑时)

    set hive.map.aggr=true;

  10. 压缩选择速度快的

  11. 开启JVM重用

  12. 合理设置MapTask数量

  13. 合理设置ReduceTask数量

九: 数据倾斜

由于热点数据, 导致有的Reduce分区数据量过大

解决:

  1. 处理热点数据(key)

    • key为NULL时

      • 属于异常数据就提前过滤掉
      • 不属于异常数据就拼接随机值

    • key不为NULL时

      拼接随机值

    然后进行两阶段MR聚合:

    第一次MR带着随机值聚合一部分, 即局部聚合;

    第二次MR去掉随机值进行最终聚合 即全局聚合

    所谓的随机值, 并不是UUID完全随机, 因为那样第一个MR相当于没干任何聚合的活, 第二个MR拆掉后缀随机值后照样数据倾斜;
    应该是某一个区间内的随机值(如随机值%100), 当1亿个同key的数据%100 [0,99]分区进行聚合, 第二个MR去掉后缀随机值后只需要聚合的是这100个同key的数据, 任务量就很小了

  2. 去重时选用group by, group by性能优于distinct

  3. MapJoin

    set hive.auto.convert.join=true;   -- 默认为true

    • 1

  4. 开启Map端combiner预聚合

  5. join时若关联字段数据类型不一致, 使用手动cast进行强转

    比如A表string类型的uid 去 join B表bigint类型的uid:

    若不手动把bigint强转为string, 在hive3.x里便会隐式地把string转为bigint, 可能会导致数据溢出从而返回NULL,

    若这种溢出状况较多, 便会导致了热点数据NULL

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/笔触狂放9/article/detail/781986
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号