天天看点

hive怎么处理过滤掉满足多个多个条件的记录_Hive的经典面试题

很久没有发文章了,今天发表一下Hive的总结,如果那里有不足的欢迎指正,顺便再提一个问题(数仓建模中:细化程度越高,粒度级就越小,相反,细化程度越低,粒度级就越大,这个说法能打个比方比喻出来吗?) 必会的函数:
select:选择
having,where:筛选
sum,max,min,count+group by:聚合
sort by,oder by:排序-row number-分组排序
distinct:去重-某种情况下group by也可以做去重
join,left join,rigth join,union表连接
case...when end:条件
substr,concat,decimal(a,b):字符
data_add,last_day,next_day:日期
percentile:去百分比
           
union和union all的区别:
union和union all度是对数据集进行合并,但union可以进行去重,且结果可以排序,union all不可以
           
leftjoin和right join的区别:
left join以左表为基准,向右表匹配符合规则的字段,不符合为null。
           
HIVE和数据库的比较:
总的来说,hive和数据库除开拥有类SQL的HQL语言,其他并无相似之处:
存储位置不同,hive的底层是HDFS,数据库是本地。
hive的数据规模大,但延迟高,数据库延迟低,但数据规模小.
           
Hive的外部表和内部表:
内部表又被称为管理表,删除管理表的时候,会把它所有的目录都给删除掉,元数据和原始数据。
而删除外部表的时候,只会删除元数据,原始数据不会被删除.
           
Hive四个BY的区别:
sort by:分区内有序。
order by:全局有序,只有一个reducer。
distinct by:类似于mr的parttion,可以分区。
cluster by:在distinct by和sort by有相同字段的时候可以用到,具有两者的能力,且能排序,只能升序。 
           
窗口函数:
rank():生成数据项在分组里面的排名,如果有名次相等,则会留下空位.
dense_rank():与rank一样,但不会留下空位.
row_number:进行排序,且给定序号.
over():指定数据分析函数工作时窗口的大小,但窗口也许会随着当前行的变化而变化。
lag:往前多少行.
lead:往后多少行.
curent row:当前行.
           
Hive分区分桶:
分区就是一种对表数据逻辑上的划分,且不保存数据,使用的是表外字段,它只是存储在HDFS的一个层级。
分桶就是当分区或者表中的数据越来越多的时候,需要进行的更细粒度的划分。
           
自定义UDF和UDTF:
使用UDF解析公共字段,使用UDTF解决事件字段。
步骤:
1.继承UDF,重写evaluate方法。
2.继承GenericUDTF,重写它的三个方法。
           
数仓建模:
企业拥有海量的数据,这些数据对于企业的运作是非常有用的,但是对于商业战略决策和目标制定的作用甚微,关系型数据库很难将这些数据转换成企业真正需要的决策信息,所以我们需要对企业中各类数据进行汇集,清洗,管理,找出战略决策信息,这就shi需要建立的数据仓库。
数据仓库是面向主题的、集成的(非简单的数据堆积)、相对稳定的、反应历史变化的数据集合,数仓中的数据是有组织 有结构的存储数据集合,用于对管理决策过程的支持。
面向主题:主题是指使用数据仓库进行决策时所关心的重点方面,每个主题都对应一个相应的分析领域,一个主题通常与多个信息系统相关。 
数据集成:数据仓库中的数据是在对原有分散的数据库数据抽取、清理的基础上经过系统加工、汇总和整理得到的,必须消除源数据中的不一致性,以保证数据仓库内的信息是关于整个企业的一致的全局信息,这个过程中会有ETL操作,以保证数据的一致性、完整性、 有效性、精确性。
相对稳定:数据仓库的数据主要供企业决策分析之用,所涉及的数据操作主要是数据查询,一旦某个数据进入数据仓库以后,一般情况下将被长期保留,也就是数据仓库中一般有大量的查询操作,基本没有修改和删除操作,通常只需要定期的加载、刷新。
           
数仓建模又分为维度建模和ER建模,维度建模主要源自数据集市,主要面向分析场景,维度建模以分析决策的需求出发构建模型,构建的数据模型为分析需求服务,因此它重点解决用户如何更快的完成分析需求,同时还有较好的大规模复杂查询的响应性能。它与实体-关系(ER)建模有很大的区别,实体-关系建模是面向应用,遵循第三范式,以消除数据冗余为目标的设计技术。
维度建模是面向分析,为了提高查询性能可以增加数据冗余,反规范化的设计技术。将数据仓库中的表划分为事实表、维度表两种类型。 

事实表:发生在现实世界中的操作型事件,其中所产生的可度量数值,存储在事实表中。
维度表:维度表包含了维度的每个成员的特定名称,维度成员的名称称为 “ 属 性 ”
           
数仓建模:
1.选择业务过程:不管你做什么项目或者公司有海量数据需要去解决什么事,都会有一个业务流程,比如说保险,投保,支付收费,保单变更,这就是事实
2.声明粒度:粒度指的是数据仓库中数据单元的详细程度和级别,数据越详细,粒度就越小,级别就越低,数据综合度高,粒度就越大,级别就越高,数据粒度分为四个级别:早期级别,当前细节级,轻度综合级,高度综合级,源数据经过综合后进入当前细节级,并根据具体需要进一步综合,就会进入轻度综合级或者高度综合级,数据老化后进入早期细节级,粒度越高表示细节程度越低,综合程度越大,表示细节能力下降。
比如说数据,每一天过来的数据和每一周的数据粒度是不一样的,一般是按照最小粒度来进行统计。
3.确定维度
4.确定事实
           
保险数据仓库设计:
保险业务流程:一般可分为"投保"-"核保"-"收费"-"生效"-"保单变更"-"退保"或者"理赔"-"退付费"-"保单到期"-"合同结束"
维度:业务流程中需要定义一些维度。 
投保:保单号,日期,地域,客户,
客户信息:"客户基本信息和理赔基本信息"-"客户主题和理赔主题"-"承保主题"-"承保信息"
客户属性表:
主题名:客户-承保-理赔
主题编码:保单号
属性:
客户:"保单号,年龄,住址,工作单位,健康情况,职业"
承保:"保单号,被保险人员名称,生效日期,终止日期,保额"
理赔:"保单号,案件号,理赔金额,理赔原因,费用金额"
           
三大范式和星星模型,雪花模型:
三大范式:原子性和唯一性,非主键字段不能相互依赖,不存在传递依赖星星模型:当所有的维度表都由连接键连接到事实表时,结构图如星星一样,这种分析模型就是星型模型,星型架构是一种非正规化的结构,它多维数据集的每一个维度都直接与事实表相连接,不存在渐变维度,所以数据有一定的冗余,
雪花模型:当有一个或多个维表没有直接连接到事实表上,而是通过其他维度表连接到事实表上时, 其结构图就像雪花连接在一起,这种分析模型就是雪花模型。

星型模型和雪花模型主要区别就是对维度表的拆分,对于雪花模型,维度表的涉及更加规范,一般符合三范式设计;而星型模型,一般采用降维的操作,维度表设计不符合三范式设计,反规范化,利用冗余牺牲空间来避免模型过于复杂,提高易用性和分析效率。 
星型模型因为数据的冗余所以很多统计查询不需要做外部的连接,因此一般情况下效率 比雪花型模型要高。星型结构不用考虑很多正规化的因素,设计与实现都比较简单。 
雪花型模型由于去除了冗余,有些统计就需要通过表的联接才能产生,所以效率不一定有星型模型高。正规化也是一种比较复杂的过程,相应的数据库结构设计、数据的ETL、以及后期的维护都要复杂一些。因此在冗余可以接受的前提下,数仓构建实际运用中星型模型 使用更多,也更有效率。 
d 此外,在数据仓库中星座模型也使用比较多,当多个事实表共用多张维度表时,就构成了星座模型.
           
Hive的优化:
1.mapjoin
例:如果不指定Mapjoin或者不符合Mapjoin的条件,那么HIve的解析器则会将join操作转换成common join操作,在reducer端进行join,会造成数据倾斜,所以需要把表数据加载到内存在map端进行join,避免使用Reducer。
2.行列过滤
列处理:尽量使用分区过滤,少使用select *,在select中只拿需要的列
行处理:在分区剪裁中,如果使用外关联,把过滤条件写在where后,那么就会先全表关联在过滤。
3.采用分区分桶。
4.合理设置map,reducer数。
5.小文件进行合并,在map端采用combine操作。
           
Hive解决数据倾斜的方法:
1.采用Group By或者count(distinct)
两者的作用都在于在自己维度过小的时候和特殊值过多的时候。
2.mapjoin
3.处理空值
4.不同的数据类型关联也容易造成数据倾斜,比如说a表字段id是int类型,但是在log表中,既有int又有string类型,解决办法就是把int转成string。
           
HiveSQl的优化:
1.尽量尽早的进行过滤。
2.执行Join操作时,小表放在左边,否则会引起大量的内存和磁盘消耗。
3.尽量的原子化操作,避免一个sql里面复杂的逻辑,可以使用中间表来处理。
4.还有要注意写语句的时候用到如join,group这类的容易造成数据倾斜,需要增大reducer大小和调整group建对应的记录条数。
           
Hive的索引和机制:
Hive索引是为了提高HIve表指定列的查询速度,同样的,创建创建索引也需要消耗额外的资源和更多的磁盘去存储索引。
在查询索引列的值时,会生成一个mr job,根据对索引列的过滤条件,从索引表中过滤出索引列的值对应的hdfs文件路径及偏移量,输出到hdfs上的一个文件中,然后根据这些文件中的hdfs路径和偏移量,筛选原始input文件,生成新的split,作为整个job的split,这样就达到不用全表扫描的目的。
索引的优点就在于可以避免全表扫描和资源浪费、可以加快group by得查询计算速度。