导图社区 Hive基础
- 317
- 12
- 2
- 举报
Hive基础
小白必看《Hive基础》，主要内容有原理图、linux里启动hive、beeline客户端连接、数据类型、Hive调优、 Hive数据存储格式、数据压缩。注释内含大量解释，无他唯手熟尔！
提示: 本内容由社区用户上传并分享。平台不对内容的真实性、合法性、知识产权归属及是否侵害第三方权利进行事前审核或保证。本内容可能包含受版权保护的图片、字体或其他第三方素材，使用前请自行确认授权范围。
- Hive
- Hive基础
- 相似推荐
- 大纲
Hive基础
原理图
linux里启动hive
nohup /export/server/hive-3.1.2/bin/hive --service metastore & nohup /export/server/hive-3.1.2/bin/hive --service hiveserver2 &
检测服务 jps
beeline客户端连接
[root@node1 ~]# /export/server/hive-3.1.2/bin/beeline beeline> ! connect jdbc:hive2://node1:10000 beeline> root beeline> 123456 0: jdbc:hive2://node1:10000> show databases; +----------------+ | database_name | +----------------+ | default | +----------------+ 1 row selected (1.242 seconds)
数据类型
关于Hive的数据类型，需要注意： 英文字母大小写不敏感； 除SQL数据类型外，还支持Java数据类型，比如：string； int和string是使用最多的，大多数函数都支持； 复杂数据类型的使用通常需要和分隔符指定语法配合使用。 如果定义的数据类型和文件不一致，hive会尝试隐式转换，但是不保证成功。
原生数据类型 primitive data type
数值类型
时间类型
字符串类型
杂项数据类型
复杂数据类型 complex data type
array数组
map映射
struct结构
union联合体
union联合体
Hive调优
抓取机制 fetch
把hive.fetch.task.conversion设置成none，然后执行查询语句，都会执行mapreduce程序。 set hive.fetch.task.conversion=none; select * from score; select s_score from score; select s_score from score limit 3; 把hive.fetch.task.conversion设置成more，然后执行查询语句，如下查询方式都不会执行mapreduce程序。 set hive.fetch.task.conversion=more; select * from score; select s_score from score; select s_score from score limit 3;
本地模式 mapreduce
hive自动根据下面三个条件判断是否启动本地模式： The total input size of the job is lower than: hive.exec.mode.local.auto.inputbytes.max (128MB by default) The total number of map-tasks is less than: hive.exec.mode.local.auto.tasks.max (4 by default) The total number of reduce tasks required is 1 or 0.
join 查询优化
【注意】多个表关联时，最好分拆成小段sql分段执行，避免一个大sql（无法控制中间Job）
map side join

开启mapjoin参数设置： （1）设置自动选择mapjoin set hive.auto.convert.join = true; 默认为true （2）大表小表的阈值设置： set hive.mapjoin.smalltable.filesize= 25000000;
大表 join 大表

有时join超时是因为某些key对应的数据太多，而相同key对应的数据都会发送到相同的reducer上，从而导致内存不够。 此时我们应该仔细分析这些异常的key，很多情况下，这些key对应的数据是异常数据，我们需要在SQL语句中进行过滤。
大小表、小大表 join

在当下的hive版本中，大表join小表或者小表join大表，就算是关闭map端join的情况下，基本上没有区别了（hive为了解决数据倾斜的问题，会自动进行过滤）
group by 优化：map端聚合
默认情况下，当进行group by的时候，Map阶段同一Key数据分发给一个reduce，当一个key数据过大时就倾斜了。 但并不是所有的聚合操作都需要在Reduce端完成，很多聚合操作都可以先在Map端进行部分聚合，最后在Reduce端得出最终结果。
1）是否在Map端进行聚合，默认为True set hive.map.aggr = true; 2）在Map端进行聚合操作的条目数目 set hive.groupby.mapaggr.checkinterval = 100000; 3）有数据倾斜的时候进行负载均衡（默认是false） set hive.groupby.skewindata = true;
MapReduce 引擎并行度调整
map task 个数调整
小文件场景： //每个Map最大输入大小(这个值决定了合并后文件的数量) set mapred.max.split.size=112345600; //一个节点上split的至少的大小(这个值决定了多个DataNode上的文件是否需要合并) set mapred.min.split.size.per.node=112345600; //一个交换机下split的至少的大小(这个值决定了多个交换机上的文件是否需要合并) set mapred.min.split.size.per.rack=112345600; //执行Map前进行小文件合并 set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
大文件场景： set mapreduce.job.reduces =10; create table a_1 as select * from a distribute by rand(123);
reduce task 个数调整
总共受3个参数控制： 1）每个Reduce处理的数据量默认是256MB hive.exec.reducers.bytes.per.reducer=256123456 2）每个任务最大的reduce数，默认为1009 hive.exec.reducers.max=1009 3）mapreduce.job.reduces 该值默认为-1，由hive自己根据任务情况进行判断。 因此，可以手动设置每个job的Reduce个数 set mapreduce.job.reduces = 8;
执行计划 explain
基本语法 EXPLAIN [EXTENDED | DEPENDENCY | AUTHORIZATION] query 案例实操 1）查看下面这条语句的执行计划 explain select * from course; explain select s_id ,avg(s_score) avgscore from score group by s_id; 2）查看详细执行计划 explain extended select * from course; explain extended select s_id ,avg(s_score) avgscore from score group by s_id;
并行执行机制
通过设置参数hive.exec.parallel值为true，就可以开启并发执行。不过，在共享集群中，需要注意下，如果job中并行阶段增多，那么集群利用率就会增加。 set hive.exec.parallel=true; //打开任务并行执行 set hive.exec.parallel.thread.number=16; //同一个sql允许最大并行度，默认为8。
严格模式
通过设置属性hive.mapred.mode值为默认是非严格模式nonstrict 。开启严格模式需要修改hive.mapred.mode值为strict，开启严格模式可以禁止3种类型的查询。 开启严格模式后： 1）对于分区表，除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行 2）对于使用了order by语句的查询，要求必须使用limit语句 3）限制笛卡尔积的查询
推测执行机制
hive本身也提供了配置项来控制reduce-side的推测执行： <property> <name>hive.mapred.reduce.tasks.speculative.execution</name> <value>true</value> </property>
Hive数据存储格式
储存形式
行式存储
【优点】相关的数据是保存在一起，比较符合面向对象的思维，因为一行数据就是一条记录。这种存储格式比较方便进行INSERT/UPDATE操作
【缺点】数据量比较大就比较影响性能
列式存储
【优点】索引快查询快，高效的压缩率，不仅节省储存空间也节省计算内存和CPU
【缺点】INSERT/UPDATE很麻烦或者不方便
TEXTFILE（行式存储）
默认格式，数据不做压缩，磁盘开销大，数据解析开销大
SEQUENCEFILE(行式存储)
ORC（列式存储）

极高压缩比，强烈推荐！
PARQUET（列式存储）
数据压缩

【优点】减少存储磁盘空间，降低单节点的磁盘IO，加快数据在Hadoop集群流动的速度，减少网络传输带宽
【缺点】需要花费额外的时间/CPU做压缩和解压缩计算
Hive 压缩设置
开启hive中间传输数据压缩功能
1）开启hive中间传输数据压缩功能 set hive.exec.compress.intermediate=true; 2）开启mapreduce中map输出压缩功能 set mapreduce.map.output.compress=true; 3）设置mapreduce中map输出数据的压缩方式 set mapreduce.map.output.compress.codec= org.apache.hadoop.io.compress.SnappyCodec;
开启Reduce输出阶段压缩
1）开启hive最终输出数据压缩功能 set hive.exec.compress.output=true; 2）开启mapreduce最终输出数据压缩 set mapreduce.output.fileoutputformat.compress=true; 3）设置mapreduce最终数据输出压缩方式 set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec; 4）设置mapreduce最终数据输出压缩为块压缩 set mapreduce.output.fileoutputformat.compress.type=BLOCK;
窗口函数
概述
如果函数具有OVER子句，则它是窗口函数。如果它缺少OVER子句，则它是一个普通的聚合函数
语法
使用
窗口表达式
【功能】提供一种控制窗口行范围的能力
关键字是rows between，包括下面这几个选项： - preceding：往前 - following：往后 - current row：当前行 - unbounded：边界 - unbounded preceding 表示从前面的起点 - unbounded following：表示到后面的终点
窗口排序函数
【功能】用于给每个分组内的数据打上排序的标号（窗口排序函数不支持窗口表达式）
row_number 在每个分组中，为每行分配一个从1开始的唯一序列号，递增，不考虑重复； rank 在每个分组中，为每行分配一个从1开始的序列号，考虑重复，挤占后续位置； dense_rank 在每个分组中，为每行分配一个从1开始的序列号，考虑重复，不挤占; ntile 将每个分组内的数据分为指定的若干个桶里（分为若干个部分），并且为每一个桶分配一个桶编号;
窗口分析函数
lag(col,n,default) 用于统计窗口内往上第n行值 第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）； lead(col,n,default) 用于统计窗口内往下第n行值 第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）； first_value 取分组内排序后，截止到当前行，第一个值； last_value 取分组内排序后，截止到当前行，最后一个值；
Hive函数高阶
爆炸函数 explode
【解释】explode函数接收map或者array类型的数据作为参数，然后把参数中的每个元素炸开变成一行数据。一个元素一行。这样的效果正好满足于输入一行输出多行。
【限制】不能在只查询源表的时候，既想返回源表字段又想返回explode生成的虚拟表字段。通俗点讲，有两张表，不能只查询一张表但是返回分别属于两张表的字段 【原因】explode属于UDTF，即表生成函数。explode函数执行返回的结果只是一张虚拟表
侧视图 lateral view
【原理】将UDTF的结果构建成一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表
一般只要使用UDTF，就会固定搭配lateral view使用
行转列：多行转单列
字符串拼接 concat （不可指定分隔符）
字符串拼接 concat_ws (可以指定分隔符)
将一列的多行合并成一行，不去重 collect_list
将一列的多行合并成一行，并去重 concat_set
列转行：单列转多行
爆炸函数 explode
JSON 数据处理

JSON函数 get_json_object

JSON函数 json_tuple

JSON函数 serde

Hive函数入门
显示详情
--显示所有的函数和运算符 show functions; --查看运算符或者函数的使用说明 describe function +; --使用extended 可以查看更加详细的使用说明 describe function extended +;
函数分类
内置函数
字符串函数

字符串长度函数：length 字符串反转函数：reverse 字符串连接函数：concat 带分隔符字符串连接函数：concat_ws 字符串截取函数：substr,substring 字符串转大写函数：upper,ucase 字符串转小写函数：lower,lcase 去空格函数：trim 左边去空格函数：ltrim 右边去空格函数：rtrim 正则表达式替换函数：regexp_replace 正则表达式解析函数：regexp_extract URL解析函数：parse_url json解析函数：get_json_object 空格字符串函数：space 重复字符串函数：repeat 首字符ascii函数：ascii 左补足函数：lpad 右补足函数：rpad 分割字符串函数: split 集合查找函数: find_in_set
日期函数

获取当前日期: current_date 获取当前时间戳: current_timestamp UNIX时间戳转日期函数: from_unixtime 获取当前UNIX时间戳函数: unix_timestamp 日期转UNIX时间戳函数: unix_timestamp 指定格式日期转UNIX时间戳函数: unix_timestamp 抽取日期函数: to_date 日期转年函数: year 日期转月函数: month 日期转天函数: day 日期转小时函数: hour 日期转分钟函数: minute 日期转秒函数: second 日期转周函数: weekofyear 日期比较函数: datediff 日期增加函数: date_add 日期减少函数: date_sub
数学函数

取整函数: round 指定精度取整函数: round 向下取整函数: floor 向上取整函数: ceil 取随机数函数: rand 二进制函数: bin 进制转换函数: conv 绝对值函数: abs
集合函数

集合元素size函数: size(Map<K.V>) size(Array<T>) 取map集合keys函数: map_keys(Map<K.V>) 取map集合values函数: map_values(Map<K.V>) 判断数组是否包含指定元素: array_contains(Array<T>, value) 数组排序函数:sort_array(Array<T>)
条件函数

if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull) 空判断函数: isnull( a ) 非空判断函数: isnotnull ( a ) 空值转换函数: nvl(T value, T default_value) 非空查找函数: COALESCE(T v1, T v2, ...) 条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END nullif( a, b ): 如果a = b，则返回NULL；否则返回NULL。否则返回一个 assert_true: 如果'condition'不为真，则引发异常，否则返回null
类型转换函数
任意数据类型之间转换:cast select cast(12.14 as bigint); select cast(12.14 as string);
数据脱敏函数

mask mask_first_n(string str[, int n] mask_last_n(string str[, int n]) mask_show_first_n(string str[, int n]) mask_show_last_n(string str[, int n]) mask_hash(string|char|varchar str)
其它杂项函数

hive调用java方法: java_method(class, method[, arg1[, arg2..]]) 反射函数: reflect(class, method[, arg1[, arg2..]]) 取哈希值函数:hash current_user()、logged_in_user()、current_database()、version() SHA-1加密: sha1(string/binary) SHA-2家族算法加密：sha2(string/binary, int) (SHA-224, SHA-256, SHA-384, SHA-512) crc32加密: MD5加密: md5(string/binary)
自定义函数
普通函数 UDF（一进一出）
聚合函数 UDAF（多进一出）
表生成函数 UDTF（一进多出）
搭配 lateral view 使用
Hive内置运算符
关系运算符

等值比较: = 、== 不等值比较: <> 、!= 小于比较: < 小于等于比较: <= 大于比较: > 大于等于比较: >= 空值判断: IS NULL 非空判断: IS NOT NULL LIKE比较: LIKE JAVA的LIKE操作: RLIKE REGEXP操作: REGEXP
算术运算符

加法操作: + 减法操作: - 乘法操作: * 除法操作: / 取整操作: div 取余操作: % 位与操作: & 位或操作: | 位异或操作: ^ 位取反操作: ~
逻辑运算符

与操作: A AND B 或操作: A OR B 非操作: NOT A 、!A 在:A IN (val1, val2, ...) 不在:A NOT IN (val1, val2, ...) 逻辑是否存在: [NOT] EXISTS (subquery)
Hive数据查询语言 HQL
基础查询
语法树
sqlect_qxpr
all、distinct
where
分区查询、分区裁剪
分组 group by
【注意】出现在GROUP BY中select_expr的字段：要么是GROUP BY分组的字段；要么是被聚合函数应用的字段
分组后筛选 having
SQL中增加having子句的原因，where关键字无法与聚合函数一起使用
【having与where的区别】 having是在分组后对数据进行过滤 where是在分组前对数据进行过滤 having后面可以使用聚合函数 where后面不可以使用聚合
分页 limi
Hive SQL 查询执行顺序
高阶查询
order by
会对输出的结果进行全局排序，因此底层使用MapReduce引擎执行的时候，只会有一个reducetask执行。也因此，如果输出的行数太大，会导致需要很长的时间才能完成全局排序。所以建议order by结合limit一起用。
cluster by
CLUSTER BY = DISTRIBUTE BY + SORT BY
DISTRIBUTE BY +SORT BY
总结
联合查询 Union
公共表表达式 CTE
【本质】是一个临时结果集，该结果集是从WITH子句中指定的简单查询派生而来的，该查询紧接在SELECT或INSERT关键字之前
连接查询 join
规则树
内连接 inner join

左连接 left join

右连接 right join

全外连接 full outer join

左半开连接 left semi join
左半开连接（LEFT SEMI JOIN）会返回左边表的记录，前提是其记录对于右边的表满足ON语句中的判定条件。 从效果上来看有点像inner join之后只返回左表的结果
交叉连接 cross join
将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积。对于大表来说，cross join慎用
Hive数据操作语言 DML
加载数据 Load
插入数据 Insert
insert+values（非常慢，不建议）
insert + select（建议）
多重插入
【本质】一次扫描多次插入
动态分区插入
【本质】分区的值是由后续的select查询语句的结果来动态确定的。根据查询结果自动分区
导出数据
directory
Hive数据定义语言 DDL
完整建表语法树
Hive创建的表，默认路径是/user/hive/warehouse

建表成功后，上传文件到HDFS对应的表文件夹下建立映射
hdfs dfs -put 本地文件路径 /user/hive/warehouse/库名/表名
原生数据类型 建表
复杂数据类型 建表
表的分类
内部表 managedl table
【特点】hive完全管理内部表的生命周期。删除内部表时，它会删除数据以及表的元数据
获取表的描述信息,可看出表的类型
describe formatted
外部表 external table
【特点】hive不能拥有或管理外部表，只管理外部表的元数据的生命周期。删除外部表时，只会删除元数据，不会删除HDFS中的实际数据
分区表
【注意】分区字段不能是表中已经存在的字段，因为分区字段最终会以虚拟字段的形式显示在表结构上，并不会存储在底层文件中
分区表数据加载
静态分区插入
load data [local] inpath ' ' into table tablename partition(分区字段='分区值'...);
动态分区插入
insert into table 表名 partition(分区字段) select 列名,被分区列名 from 临时表;
分区表的本质
不同分区对应着不同目录，同一分区的数据存储在同一个目录下
多重分区表
分区表如同目录是一种递进关系，可以理解为在前一个分区的基础上继续分区
分桶表
clustered by 表示根据哪个字段进行分桶
into 3 buckets 表示分为3桶
sorted by 排序可以不加
开启分桶的功能 从Hive2.0开始不再需要设置
set hive.enforce.bucketing=true
分桶表的本质
只要hash_function(bucketing_column)一样的，就一定被分到同一个桶中
分桶表的好处
JOIN时可以提高MR程序效率，减少笛卡尔积数量
方便抽样调查
Hive DDL 其它语法
库 database
显示Hive数据库信息
describe database [extended] db_name;
用
use database 库名
增
CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name [COMMENT database_comment] [LOCATION hdfs_path] [WITH DBPROPERTIES (property_name=property_value, ...)];
数据库的注释说明语句 comment
指定数据库在HDFS的位置 location
指定数据库的属性配置 with dbproperties
创建的表的默认存储路径为：/user/hive/warehouse
删
drop database [if exists] 库名 [cascade]
就算库里有表也删除 cascade
改
更改数据库属性
alter database 库名 set dbproperties (property_name=property_value, ...);
更改数据库所有者
alter database 库名 set owner [user|role] user_or_role;
更改数据库位置
alter database 库名 set location hdfs_path;
表 table
显示Hive中表的元数据信息
describe extended [db_name.]table_name;
删
删除表并跳过垃圾桶
drop table [if exists] table_name [purge];
删除表中所有行
truncate [table] table_name;
改
更改表名
ALTER TABLE table_name RENAME TO new_table_name;
更改表属性
ALTER TABLE table_name SET TBLPROPERTIES (property_name = property_value, ... );
更改表注释
ALTER TABLE student SET TBLPROPERTIES ('comment' = "new comment for student table");
更改SerDe属性
ALTER TABLE table_name SET SERDE serde_class_name [WITH SERDEPROPERTIES (property_name = property_value, ... )]; ALTER TABLE table_name [PARTITION partition_spec] SET SERDEPROPERTIES serde_properties; ALTER TABLE table_name SET SERDEPROPERTIES ('field.delim' = ',');
移除SerDe属性
ALTER TABLE table_name [PARTITION partition_spec] UNSET SERDEPROPERTIES (property_name, ... );
更改表的文件存储格式，该操作仅更改表元数据
ALTER TABLE table_name SET FILEFORMAT file_format;
更改表的存储位置路径
ALTER TABLE table_name SET LOCATION "new location";
更改列名称/类型/位置/注释
CREATE TABLE test_change (a int, b int, c int); ALTER TABLE test_change CHANGE a a1 INT; ALTER TABLE test_change CHANGE a1 a2 STRING AFTER b; ALTER TABLE test_change CHANGE c c1 INT FIRST; ALTER TABLE test_change CHANGE a1 a1 INT COMMENT 'this is column a1';
添加/替换列
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type,...);
分区 partition
增加分区 add partition
重命名分区 rename partition
删除分区 delete partition
修复分区 msck partition
如果直接从HDFS中直接删除分区文件夹或增加分区文件夹时，hive无法意识到分区信息被更改，这时候你可以使用修复表选项运行 metastore check 命令
修改分区 alter partition
Hive Show 显示语法
显示所有数据库
show databases;
显示当前数据库所有表/视图/物化视图/分区/索引
show tables; SHOW TABLES [IN database_name]; --指定某个数据库
显示当前数据库下所有视图
Show Views; SHOW VIEWS 'test_*'; -- show all views that start with "test_" SHOW VIEWS FROM test1; -- show views from database test1 SHOW VIEWS [IN/FROM database_name];
显示当前数据库下所有物化视图
SHOW MATERIALIZED VIEWS [IN/FROM database_name];
显示表分区信息，分区按字母顺序列出，不是分区表执行该语句会报错
show partitions table_name;
显示表/分区的扩展信息
SHOW TABLE EXTENDED [IN|FROM database_name] LIKE table_name; show table extended like student;
显示表的属性信息
SHOW TBLPROPERTIES table_name; show tblproperties student;
显示表、视图的创建语句
SHOW CREATE TABLE ([db_name.]table_name|view_name); show create table student;
显示表中的所有列，包括分区列
SHOW COLUMNS (FROM|IN) table_name [(FROM|IN) db_name]; show columns in student;
显示当前支持的所有自定义和内置的函数
show functions;
查看表信息
desc extended table_name;
查看表信息（格式化美观）
desc formatted table_name;
查看数据库相关信息
describe database database_name;