?什么是Hive
1) hive简介
Hive:由Facebook开源用于解决海量结构化日志的数据统计工具。
Hive是基于Hadoop的一个数据仓库工具,可以将结构化的数据文件映射为一张表,并提供类SQL查询功能。
2) Hive本质:将HQL转化成MapReduce程序。
Hive架构原理驱动器:Driver
(1)解析器(SQL Parser):语法分析,比如表是否存在、字段是否存在、SQL语义是否有误。
(2)编译器(Physical Plan):编译生成逻辑执行计划。
(3)优化器(Query Optimizer):对逻辑执行计划进行优化,有时候对人为写的不合理的地方进行简化,减少步骤。
(4)执行器(Execution):把逻辑执行计划转换成可以运行的物理计划。对于Hive来说,就是MR/Spark。
另外hive压根不是数据库,hive除了语言类似以外,存储和计算都是用的hadoop的,但是Mysql却用的是自己的,拥有自己的体系。
hive安装按步骤来就行,但是hive默认使用的元数据库为derby,单线程的不与其他客户端共享数据,需要将hive元数据地址改为mysql。
Hive数据类型和Java类似,隐式转换规则如下:
TINYINT,SMALINT——>INT——>BIGINT——>FLOAT——>DOUBLE
STRING——>DOUBLE
BOOLEAN不参与转换。
可以使用CAST操作显示进行数据类型转换,如CAST('X' AS INT)。
Hive语法1)DDL语法
1.创建表
(1)CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXISTS 选项来忽略这个异常。
(2)EXTERNAL关键字可以让用户创建一个外部表,在建表的同时可以指定一个指向实际数据的路径(LOCATION),在删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据。
(3)COMMENT:为表和列添加注释。
(4)PARTITIONED BY创建分区表
(5)CLUSTERED BY创建分桶表
(6)SORTED BY不常用,对桶中的一个或多个列另外排序
(7)ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
??????? [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
?? | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED,将会使用自带的SerDe。在建表的时候,用户还需要为表指定列,用户在指定表的列的同时也会指定自定义的SerDe,Hive通过SerDe确定表的具体的列的数据。
SerDe是Serialize/Deserilize的简称, hive使用Serde进行行对象的序列与反序列化。
(8)STORED AS指定存储文件类型
常用的存储文件类型:SEQUENCEFILE(二进制序列文件)、TEXTFILE(文本)、RCFILE(列式存储格式文件)
如果文件数据是纯文本,可以使用STORED AS TEXTFILE。如果数据需要压缩,使用 STORED ?AS SEQUENCEFILE。
(9)LOCATION :指定表在HDFS上的存储位置。
(10)AS:后跟查询语句,根据查询结果创建表。
(11)LIKE允许用户复制现有的表结构,但是不复制数据。
2.修改表
(1)更新列
更新列,列名可以随意修改,列的类型只能小改大,不能大改小(遵循自动转换规则)
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment]
(2)增加和替换列
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
注:ADD是代表新增一字段,字段位置在所有列后面(partition列前),REPLACE则是表示替换表中所有字段,REPLACE使用的时候,字段的类型要跟之前的类型对应上,数量可以减少或者增加,其实就是包含了更新列,增加列,删除列的功能。
2)DML语法 2.向表中装载数据(Load)hive> load data [local] inpath '数据的path' [overwrite] into table student [partition (partcol1=val1,…)];
(1)load data:表示加载数据
(2)local:表示从本地加载数据到hive表;否则从HDFS加载数据到hive表
(3)inpath:表示加载数据的路径
(4)overwrite:表示覆盖表中已有数据,否则表示追加
(5)into table:表示加载到哪张表
(6)student:表示具体的表
(7)partition:表示上传到指定分区
import导入Insert导出Export导出到HDFS上
3)查询语法hive查询语句执行顺序:
from->join on->where->group by->聚合函数->having->select->distinct->order by->limit
From? 从xxxx 表中查询
Join on 需要横向连接多张表 where 过滤xxx
Group by 分组 将一些相同分到一起我们聚合 聚合函数 算什么
Having 分组后过滤
Select 挑选选择
Distinct 去重
Order by 排序
Limit 翻页
分区(Distribute By)
distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后,余数相同的分到一个区。因此官方写的随机进入实际上是伪随机进入各个分区。
另外网上很多资料上写的hive都不能自定义分区器,实际上是可以的,在老的版本mapred包里。
自定义类实现org.apache.hadoop.mapred.Partitioner(必须为这个,Hive中使用的是老的API)接口
3)分区表和分桶表 1. 分区表1.1分区表的使用
分区表实际上就是对应一个HDFS文件系统上的独立的文件夹,该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录,把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区,这样的查询效率会提高很多。
例如:
加载数据
load data local inpath '/opt/module/hive/datas/dept_20220401.log' into table dept_partition partition(day='20220401');
查看分区表有哪些
show partitions dept_partition;
同时创建多个分区(分区之间不能有逗号)
alter table dept_partition add partition(day='20220405') partition(day='20200406');
同时删除多个分区(分区之间必须有逗号)
hive (default)> alter table dept_partition drop partition (day='20220404'), partition(day='20200405');
吐槽一下,这点hive感觉很不人性化啊,同样功能语句删除和创建居然不一样,我不李姐。
加载数据到二级分区表中,跟一级一样再往后分就完了。
load data local inpath '/opt/module/hive/datas/dept_20220401.log' into table
dept_partition2 partition(day='20220401', hour='12');
1.2把数据直接上传到分区目录上,让分区表和数据产生关联的三种方式1)方式一:上传数据后修复
上传数据
hive (default)> dfs -mkdir -p
?/user/hive/warehouse/db_hive.db/dept_partition2/day=20200401/hour=13;
hive (default)> dfs -put /opt/module/hive/datas/dept_20200401.log? /user/hive/warehouse/db_hive.db/dept_partition2/day=20200401/hour=13;
查询数据(查询不到刚上传的数据)
hive (default)> select * from dept_partition2 where day='20200401' and hour='13';
执行修复命令
hive> msck repair table dept_partition2;
再次查询数据
hive (default)> select * from dept_partition2 where day='20200401' and hour='13';
2)方式二:上传数据后添加分区
3)方式三:创建文件夹后load数据到分区
方式二三就没必要说了,手动添加你懂的,咱不要,直接用修复。
1.3动态分区调整关系型数据库中,对分区表Insert数据时候,数据库自动会根据分区字段的值,将数据插入到相应的分区中,Hive中也提供了类似的机制,即动态分区(Dynamic Partition),只不过,使用Hive的动态分区,需要进行相应的配置。
(1)开启动态分区功能(默认true,开启)
hive.exec.dynamic.partition=true
动态分区如果数据只在一个datanode上,可能会报错,就是说只有当mapreduce跑在对应的那个有数据的节点上才能成功,理论上会按就近原则,在数据当前节点上运行,但有一定概率走其他没数据的datanode。有点像薛定谔的猫一样,程序运行才知道会不会报错。
(2)设置为非严格模式(动态分区的模式,默认strict,表示必须指定至少一个分区为静态分区,nonstrict模式表示允许所有的分区字段都可以使用动态分区。)
hive.exec.dynamic.partition.mode=nonstrict
严格模式这玩意太鸡肋了,开不开感觉没啥区别。
2.分桶表创建分桶表
create table stu_buck(id int, name string)
clustered by(id)
into 4 buckets
row format delimited fields terminated by '\t';
分桶规则:Hive的分桶采用对分桶字段的值进行哈希,然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
4)函数 1.系统内置函数很多289个,没必要记 2.常用内置函数可以记一下 4.2.1 空字段赋值函数说明
NVL:给值为NULL的数据赋值,它的格式是NVL( value,default_value)。它的功能是如果value为NULL,则NVL函数返回default_value的值,否则返回value的值,如果两个参数都为NULL ,则返回NULL。
4.2.2 CASE WHEN THEN ELSE END跟if else差不多例如:case sex when '男' then 1 else 0 end
4.2.3 行转列CONCAT(string A/col, string B/col…):返回输入字符串连接后的结果,支持任意个输入字符串;
CONCAT_WS(separator, str1, str2,...):它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL,返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;
注意:CONCAT_WS must be "string or array<string>"
COLLECT_SET(col):函数只接受基本数据类型,它的主要作用是将某字段的值进行去重汇总,产生array类型字段。
COLLECT_LIST(col):函数只接受基本数据类型,它的主要作用是将某字段的值进行不去重汇总,产生array类型字段。?
4.2.4 列转行(一列转多行)Split(str, separator):将字符串按照后面的分隔符切割,转换成字符array。
EXPLODE(col):将hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW
用法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释:lateral view用于和split, explode等UDTF一起使用,它能够将一行数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。
lateral view首先为原始表的每行调用UDTF,UDTF会把一行拆分成一或者多行,lateral view再把结果组合,产生一个支持别名表的虚拟表。
4.2.5 窗口函数(开窗函数)OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化。
CURRENT ROW:当前行
n PRECEDING:往前n行数据
n FOLLOWING:往后n行数据
UNBOUNDED:无边界
UNBOUNDED PRECEDING 前无边界,表示从前面的起点,
UNBOUNDED FOLLOWING后无边界,表示到后面的终点
LAG(col,n,default_val):往前第n行数据
LEAD(col,n, default_val):往后第n行数据
FIRST_VALUE (col,true/false):当前窗口下的第一个值,第二个参数为true,跳过空值
LAST_VALUE (col,true/false):当前窗口下的最后一个值,第二个参数为true,跳过空值
NTILE(n):把有序窗口的行分发到指定数据的组中,各个组有编号,编号从1开始,对于每一行,NTILE返回此行所属的组的编号。注意:n必须为int类型。
4.2.6 RankRANK() 排序相同时会重复,总数不会变
DENSE_RANK() 排序相同时会重复,总数会减少
ROW_NUMBER() 会根据顺序计算
4.3 自定义函数根据用户自定义函数类别分为以下三种:
(1)UDF(User-Defined-Function)
???????? 一进一出
(2)UDAF(User-Defined Aggregation Function)
???????? 用户自定义聚合函数,多进一出
???????? 类似于:count/max/min
(3)UDTF(User-Defined Table-Generating Functions)
???????? 用户自定义表生成函数,一进多出
???????? 如lateral view explode()
编程步骤:
(1)继承Hive提供的类
???????????????? org.apache.hadoop.hive.ql.udf.generic.GenericUDF?
?????????????????? org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
(2)实现类中的抽象方法
(3)在hive的命令行窗口创建函数
添加jar
add jar linux_jar_path
创建function
create [temporary] function [dbname.]function_name AS class_name;
(4)在hive的命令行窗口删除函数
drop [temporary] function [if exists] [dbname.]function_name;
?5)压缩和存储Hive支持的存储数据的格式主要有:TEXTFILE?、SEQUENCEFILE、ORC、PARQUET。
1)行存储的特点
查询满足条件的一整行数据的时候,列存储则需要去每个聚集的字段找到对应的每个列的值,行存储只需要找到其中一个值,其余的值都在相邻地方,所以此时行存储查询的速度更快。
2)列存储的特点
因为每个字段的数据聚集存储,在查询只需要少数几个字段的时候,能大大减少读取的数据量;每个字段的数据类型一定是相同的,列式存储可以针对性的设计更好的设计压缩算法。
TEXTFILE和SEQUENCEFILE的存储格式都是基于行存储的;
ORC和PARQUET是基于列式存储的。
6)企业级调优 6.1Fetch抓取Fetch抓取是指,Hive中对某些情况的查询可以不必使用MapReduce计算。例如:SELECT * FROM emp;在这种情况下,Hive可以简单地读取emp对应的存储目录下的文件,然后输出查询结果到控制台。
在hive-default.xml.template文件中hive.fetch.task.conversion默认是more,老版本hive默认是minimal,该属性修改为more以后,在全局查找、字段查找、limit查找等都不走mapreduce?。
6.2本地模式大多数的Hadoop Job是需要Hadoop提供的完整的可扩展性来处理大数据集的。不过,有时Hive的输入数据量是非常小的。在这种情况下,为查询触发执行任务消耗的时间可能会比实际job的执行时间要多的多。对于大多数这种情况,Hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集,执行时间可以明显被缩短。
用户可以通过设置hive.exec.mode.local.auto的值为true,来让Hive在适当的时候自动启动这个优化。
set hive.exec.mode.local.auto=true;? //开启本地mr
//设置local mr的最大输入数据量,当输入数据量小于这个值时采用local? mr的方式,默认为134217728,即128M
set hive.exec.mode.local.auto.inputbytes.max=50000000;
//设置local mr的最大输入文件个数,当输入文件个数小于这个值时采用local mr的方式,默认为4
set hive.exec.mode.local.auto.input.files.max=10;
6.3 表的优化小表、大表Join
将key相对分散,并且数据量小的表放在join的左边,这样可以有效减少内存溢出错误发生的几率;再进一步,可以使用map join让小的维度表(1000条以下的记录条数)先进内存。在map端完成reduce。
?大表Join大表空KEY过滤
有时join超时是因为某些key对应的数据太多,而相同key对应的数据都会发送到相同的reducer上,从而导致内存不够。此时我们应该仔细分析这些异常的key,很多情况下,这些key对应的数据是异常数据,我们需要在SQL语句中进行过滤。例如key对应的字段为空
空key转换
有时虽然某个key为空对应的数据很多,但是相应的数据不是异常数据,必须要包含在join的结果中,此时我们可以表a中key为空的字段赋一个随机的值,使得数据随机均匀地分不到不同的reducer上。
MapJoin如果不指定MapJoin或者不符合MapJoin的条件,那么Hive解析器会将Join操作转换成Common Join,即:在Reduce阶段完成join。容易发生数据倾斜。可以用MapJoin把小表全部加载到内存在map端进行join,避免reducer处理。
Group By默认情况下,Map阶段同一Key数据分发给一个reduce,当一个key数据过大时就倾斜了。
并不是所有的聚合操作都需要在Reduce端完成,很多聚合操作都可以先在Map端进行部分聚合,最后在Reduce端得出最终结果。
?Count(Distinct) 去重统计数据量小的时候无所谓,数据量大的情况下,由于COUNT DISTINCT操作需要用一个Reduce Task来完成,这一个Reduce需要处理的数据量太大,就会导致整个Job很难完成,一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换,但是需要注意group by造成的数据倾斜问题(比如count,可以先count group之后的数据,再sum)。
笛卡尔积尽量避免笛卡尔积,join的时候不加on条件,或者无效的on条件,Hive只能使用1个reducer来完成笛卡尔积。
行列过滤列处理:在SELECT中,只拿需要的列,如果有,尽量使用分区过滤,少用SELECT *。
行处理:在分区剪裁中,当使用外关联时,如果将副表的过滤条件写在Where后面,那么就会先全表关联,之后再过滤。
分区分桶 合理设置Map及Reduce数 并行执行通过设置参数hive.exec.parallel值为true,就可以开启并发执行。不过,在共享集群中,需要注意下,如果job中并行阶段增多,那么集群利用率就会增加。
严格模式Hive可以通过设置防止一些危险操作:
JVM重用 压缩 执行计划(Explain)EXPLAIN [EXTENDED | DEPENDENCY | AUTHORIZATION] query
1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,会注明原创字样,如未注明都非原创,如有侵权请联系删除!;3.作者投稿可能会经我们编辑修改或补充;4.本站不提供任何储存功能只提供收集或者投稿人的网盘链接。 |