天天看点
返回

hive怎么处理过滤掉满足多个多个条件的记录_Hive基础面试题总结

 点击关注上方“知了小巷”，

设为“置顶或星标”，第一时间送达干货。

1. 描述一下Hive动态分区和分桶使用场景和使用方法

  1. 分区

按照数据表的某列或某些列分为多个分区，分区从形式上可以理解为文件夹，比如我们要收集某个大型网站的日志数据，一个网站每天的日志数据存在同一张表上，由于每天会生成大量的日志，导致数据表的内容巨大，在查询时进行全表扫描耗费的资源非常多。那其实这个情况下，我们可以按照日期对数据表进行分区，不同日期的数据存放在不同的分区，在查询时只要指定分区字段的值就可以直接从该分区查找。分区是以字段的形式在表结构中存在，通过describe table命令可以查看到字段存在，但是该字段不存放实际的数据内容，仅仅是分区的标识。

  • (1) 静态分区
  • (2) 动态分区
  • (3) 静态分区和动态分区的区别

静态分区与动态分区的主要区别在于静态分区是手动指定，而动态分区是通过数据来进行判断。详细来说：

静态分区：

  • 静态分区是在编译期间指定的指定分区名。
  • 支持load和insert两种插入方式。
  • 适用于分区数少，分区名可以明确的数据。

动态分区：

  • 根据分区字段的实际值，动态进行分区
  • 是在SQL执行的时候进行分区
  • 需要先将动态分区设置打开
  • 只能用insert方式
  • 通过普通表选出的字段包含分区字段，分区字段放置在最后，多个分区字段按照分区顺序放置。
  1. 分桶

分桶是相对分区进行耕细粒度的划分。分桶将整个数据内容按照某列属性值得到hash值进行分区，如果按照name属性分为3个桶，就是对name属性值的hash值对3取模，按照取模结果对数据分桶。如取模结果为0的数据记录存放到一个文件，取模为1的数据存放到一个文件，取模为2的数据存放到一个文件。

对于每一个表(table)或者分区，Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是针对某一列进行桶的组织。Hive采用对列值进行哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。把表(或者分区)组织成桶(Bucket)有两个理由：

  • (1) 获得更高的查询处理效率

    桶为表加上了额外的结构，Hive在处理有些查询时能够利用这个结构。具体而言，连接两个在(包含连接列的)相同列上划分了桶的表，可以使用Map端连接(Map-Side join)高效的实现。比如join操作，对于join操作的两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行join操作就可以，可以大大减少join的数据量。

  • (2) 使取样(samping)更高效

    在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

2. Hive是怎么集成HBase的？

  1. 首先我们需要将HBase的客户端jar拷贝入Hive lib目录下。
  2. 修改hive/conf下的hive-site.xml配置文件，在最后添加如下属性：
  1. 启动Hive，创建管理表hbase_table_1，指定数据存储在HBase表中。 类似下面的方式：
  1. 往Hive表hbase_table_1表中插入数据。

CDH版本不需要额外操作，相同CDH版本下的软件已经做过整合。重点是HBaseStorageHandler。

3. Hive查询的时候on和where有什么区别？

左右关联时：

  • 条件不为主表条件时，放在on和where后面一样。
  • 条件为主表条件时，放在on后面，结果为主表全量，放在where后面为主表条件筛选过后的全量。

sql1： 如果是left join在on上写主表a的条件不会生效，全表扫描。sql2： 如果是left join在on上写副表b的条件会生效，但是语义与写到where条件不同。sql3： 如果是inner join在on上写主表a、副表b的条件都会生效。sql4： 建议写SQL，大部分的语义都是先过滤数据然后再join，所以在不了解join on+ 条件的情况下，条件尽量别写在on后，直接写到where后就OK了。

on中的条件是在join时执行，where中的条件，是在join结束后对结果集进行执行过滤。

4. Hive里面的left join时怎么执行的？

不考虑where条件下，left join会把左表所有数据查询出来，on及其后面的条件仅仅会影响右表的数据(符合就显示，不符合全部为null)；

在join阶段，where子句的条件都不会被使用，仅在join阶段完成以后，where子句条件才会被使用，它将从匹配阶段产生的数据中检索过滤。

所以左连接关注的是左边的主表数据，不应该把on后面的从表中的条件加到where后，这样会影响原有主表中的数据。where后面：是先连接生成临时查询结果，然后再筛选。on后面：先根据条件过滤筛选，再连接生成临时查询结果。

对于条件在on加个and还是用子查询，查询结果是一模一样的，至于如何使用。需要分情况， 用子查询的话会多一个MapTask，但是如果利用这个子查询能过滤很多数据的话，用子查询还是比较建议的，因为不会加载太多的数据到内存中，如果过滤数据不多的情况下，建议使用on后面加and条件。

5. Hive内部表、外部表、分区表

  1. 内部表、管理表(MANAGE TABLE)
  • 与数据库中的Table在概念上是类似的。
  • 每一个Table在Hive中都有一个相应的目录存储数据。
  • 所有的Table数据(不包括External Table)都保存在这个目录中。
  • 删除表时，元数据与数据都会被删除
  1. 外部表
  • 指向已经在HDFS中存在的数据，可以创建Partition。
  • 它和内部表在元数据的组织上是相同的，而实际数据的存储则有较大的差异。
  • 外部表只有一个过程，加载数据和创建表同时完成，并不会移动到数据库目录中，只是与外部数据建立一个连接。当删除一个外部表时，仅删除连接和元数据。
  1. 分区表
  • Partition对应于数据库的Partition列的聚集索引
  • 在Hive中，表中的一个Partition对应于表下的一个目录，所有的Partition的数据都存储在对应的目录中。

6. Hive和MySQL的区别，大数据为什么不用MySQL做大规模数据存储和数据处理？

Hive是基于Hadoop构建的一套数据仓库分析系统，它提供了丰富的SQL查询方式来分析存储在Hadoop分布式文件系统中的数据：可以将结构化数据文件映射为一张数据库表，并提供完整的SQL查询功能；可以将SQL语句转换为MapReduce任务运行，通过自己的SQL查询分析需要的内容，这套SQL简称Hive SQL，使得不熟悉MapReduce开发人员可以方便地利用SQL语言-查询、汇总和分析数据。而MapReduce开发人员可以把自己写的Mapper和Reducer作为插件来支持Hive做更复杂的数据分析。

它与关系型数据库的SQL略有不同，但支持了绝大多数的语句如DDL、DML以及常见的聚合函数、连接查询、条件查询。它还提供了一系列的工具进行数据提取转化加载，用来存储、查询和分析存储在Hadoop中的大规模数据集，并支持UDF(User-Defined Function)、UDAF(User-Defined AggregateFunction)和UDTF(User-Defined Table-Generating Function)，也可以实现对map和reduce函数的定制，为数据操作提供了良好的伸缩性和可扩展性。

Hive不适合用于联机事务处理(OLTP)，也不提供实时查询功能。它最适合应用在基于大量不可变数据的批处理作业。Hive的特点包括：可伸缩(在Hadoop的集群上动态添加设备)、可扩展、容错、输入格式的松散耦合。

Hive构建在基于静态批处理的Hadoop之上，Hadoop通常都有较高的延迟并且在作业提交和调度的时候需要大量的开销。因此，Hive并不能在大规模数据集上实现低延迟快速的查询。例如，Hive在几百MB的数据集上执行查询一般有分钟级的时间延迟。

因此，Hive并不适合那些需要高实时性的应用，例如，联机事务处理(OLTP)。Hive查询操作过程严格遵守Hadoop MapReduce的作业执行模型，Hive将用户的HiveQL语句通过解释器转换为MapReduce作业提交到Hadoop集群上，Hadoop监控作业执行过程，然后返回作业执行结果给用户。Hive并非为联机事务处理而设计，Hive并不提供实时的查询和基于行级的数据更新操作。Hive的最佳使用场景是大数据集的批处理作业，例如，网络日志分析。

总结：

  • 查询语言不同，Hive是hql语言，MySQL是SQL语句
  • 数据存储位置不通，Hive是把数据存储在HDFS上，而MySQL数据是存储在自己的系统中
  • 数据格式，Hive数据格式可以用户自定义，MySQL有自己的系统定义格式
  • 延迟性，Hive延迟较高，而MySQL延迟较低
  • 数据规模，Hive存储的数据量超级大，而MySQL只是存储一些少量的业务数据
  • 底层执行原理：Hive底层默认使用的是MapReduce，而MySQL是Executor执行器

7. 简述Hive调优

【Hive企业级调优】

  1. 设置本地模式
  2. 并行执行
  3. JVM重用
  4. 严格模式
  5. 合理设置map和reduce的数量
  6. Fetch抓取
  7. 防止数据倾斜参数的开启，会生成两个MR job
  8. explain执行计划，通过执行计划来调节SQL语句

8. Hive数据倾斜原因和处理

  1. 产生原因
  • (1) K分布不均匀
  • (2) 业务数据本身的特性
  • (3) 建表时考虑不周
  • (4) 某些SQL语句本身就有数据倾斜，例如：大表join小表：其中小表key比较集中，分发到某一个或几个Reduce上的数据远高于平均值。大表join大表：但是分桶的判断字段0值或空值过多，这些空值都由一个Reduce处理，非常慢。group by：group by维度过小，某个值的数量过多，处理这个值的Reduce非常耗时。Count Distinct：某特殊值过多，处理此特殊值的Reduce耗时。
  1. 解决方案

参数调节

Map端部分聚合，相当于Combiner。

有数据倾斜的时候进行负载均衡，当选项设定为true，生成的查询计划会有两个MRJob。第一个MRJob中，Map的输出结果会随机分不到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的Group By Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；第二个MRJob再根据预处理的数据结果按照Group By Key分布到Reduce中(这个过程可以保证相同的Group By Key被分不到同一个Reduce中)，最后完成最终的聚合操作。

SQL调整

  • 如何join：关于驱动表的选取，选用join key分布最均匀的表作为驱动表，做好列裁剪和filter操作，以达到两表做join的时候，数据量相对变小的效果。
  • 大小表join：使用map join让小的维度表(1000条以下的记录条数)先进内存。在Map端完成Reduce操作。
  • 大表join大表：把空值的Key变成一个字符串加上随机数，把倾斜的数据分到不同的Reduce上，由于null值关联不上，处理后并不影响最终结果。
  • count distinct大量相同特殊值，将值为空的情况单独处理，如果是计算count distinct，可以不用处理，直接过滤，在最后结果中加1。如果还有其他计算，需要进行group by，可以先将值为空的记录单独处理，再和其他计算结果进行union all。
  • group by维度过小：采用sum() group by的方式来替换count(distinct)完成计算。
  • 特殊情况预处理：在业务逻辑优化效果不大的情况下，有些时候是可以将倾斜的数据单独拿出来处理。最后union回去。

9. Hive的自定义函数实现了什么接口什么方法？

Hive自定义函数包括三种：UDF UDAF UDTF。

  1. UDF：一进一出，继承了UDF类，并重写evaluate方法。
  2. UDAF：聚合函数，多进一出。如count max min
  3. UDTF：一进多出，如lateral view explore

10. HiveSQL如何查询A表中B表不存的数据？

11. 如何控制Hive中Mapper和Reducer的个数？

  1. 控制Hive任务中的map数

    通常情况下，作业会通过input的目录产生一个或者多个map任务。

    主要的决定因素有：input的文件总个数，input的文件大小，集群设置的文件块大小(目前为128MB，可以在Hive中通过set dfs.block.size;命令查看到，该参数不能自定义修改)

    举例：假设input目录下有1个文件a，大小为780MB，那么Hadoop会将该文件a分割成7个块(6个128MB的块和1个12MB的块)，从而产生7个map数。假设input目录下有3个文a, b, c大小分别为10M、20M、130M，那么Hadoop会分割成4个块(10M、20M、128M、2M)，从而产生4个map数，即，如果文件大于块大小(128MB)，那么会拆分，如果小于块大小，则把该文件当成一个块。

  • 问题1  是不是map数越多越好？

    并不是越多越好。如果一个任务有很多小文件(远远小于块大小128MB)，则每个小文件也会被当做一个块，用一个map任务来完成，而一个map任务启动和初始化的时间远远大于逻辑处理的时间，就会造成很大的资源浪费。而且，同时可执行的map数是受限的。

  • 问题2 是不是保证每个map处理接近128MB的文件块，就高枕无忧？

    答案是不一定，比如有一个127MB的文件，正常会用一个map去完成，但这个文件只有一个或者两个小字段，却有几千万的记录，如果map处理的逻辑比较复杂，用一个map任务去做，肯定也比较耗时。

针对上面的两个问题，需要分别减少map数和增加map数来解决：

  • (1) 通过合并小文件，减少map数？假设一个SQL：

该任务的inputdir /xxxx/p_data/p_data-etl/pt/pt=20200915公有194个文件，其中很多是远远小于128MB的小文件，总大小9GB，正常执行会用194个map任务。Map总共消耗的计算资源：SLOTS_MILLIS_MAPS=623020 我通过以下方法来在map执行前合并小文件，减少map数：

在执行count语句，用了74个map任务，map消耗的计算资源：SLOTS_MILLIS_MAPS=333500，对于这个简单的SQL任务，执行时间上可能差不多，但节省了一半的计算资源。

其中，100000000标识100MB，CombineHiveInputFormat是用来表示在执行前对小文件进行合并，大于文件块大小128MB的，按照128M来分割，小于128MB大于100MB的，按照100M来分割，把哪些小于100MB的(包括小文件和分割大文件剩下的)，进行合并，最终生成了74个块。

  • (2) 如何适当的增加map个数？当input的文件都很大，任务逻辑复杂，map执行非常慢的时候，可以考虑增加map数，来使得每个map处理的数据量减少，从而提高任务的执行效率。假设有这样一个任务：

如果表a只有一个文件，大小为120MB，但包含几千万的记录，如果用1个map去完成这个任务，肯定是比较耗时的，这种情况下，要考虑将一个文件合理的拆分成多个，这样就可以用多个map任务去完成。

这样会将a表的记录，随机的分散到包含10个文件的a_1表中，再用a_1表代替a表，则会用10个map任务来完成。每个map任务处理大于12MB(几百万记录)的数据，效率肯定会好很多。

map数量是要根据实际情况：使得大数据量利用合适的map数，使得单个map任务处理合适的数据量。

  1. 控制Hive任务中的reduce数

Hive自己如何确定reduce数：reduce个数的设定极大影响任务执行效率，不指定reduce个数的情况下，Hive会猜测确定一个reduce个数，

计算reducer数的公式很简单N=min(参数2，总的输入数据量/参数1)；

如果reduce的输入(map的输出)总大小不超过1G，那么只会有一个reduce任务

调整reduce个数方法一

调整hive.exec.reducers.bytes.per.reducer参数的值：

调整reduce个数方法二

reduce个数也不是越多越好

和map数量一样，启动和初始化reduce也会消耗时间和资源；另外，有多少个reduce，就会有多少个输出文件，如果生产了很多小文件，而且这些小文件是作为下一个任务的输入，则也会出现小文件过多的问题。

什么情况下只有一个reduce？

有时候，不管数据量有多大，也不管有没有设置调整reduce个数的参数，任务中一直都只有一个reduce任务；其实只有一个reduce任务的情况，除了数据量小于hive.exec.reducers.bytes.per.reducer参数值之外，还有以下原因：

  • (1) 没有group by的聚合计算

    比如直接写成

  • (2) order by 全局排序
  • (3) 有笛卡尔积 这些操作只能找办法来变通和避免，没有其他好的办法，Hadoop不得不用一个reduce去完成；同样，在设置reduce个数的时候需要考虑两个原则：大数据量利用合适的reduce数，单个reduce任务处理合适的数据量。

12. Hive中的Order, Sort, Cluster, and Distribute By

  1. order byHive中的order by与传统的SQL语言中的order by作用是一样的，会对查询的结果做一次全局排序，所以，在Hive中使用order by，所有数据都会到同一个reducer进行处理。大量数据的时候，这是一个非常耗时的操作。如果制定了参数hive.mapred.mode=strict(默认值是nonstrict)，这时就必须指定limit来限制输出条数，因为所有数据都会在同一个Reducer端进行处理，数据量大的情况下可能出不来结果，所以在strict模式下必须指定输出的条数。

  2. sort by

    Hive中指定了sort by，那么在每个Reducer端都会做排序，也就是说保证了局部有序(每个reducer出来的数据都是有序的，但是不能保证所有的数据是有序的，除非只有一个reducer)，好处是：执行了局部排序之后可以为接下去的全局排序提高不少的效率(再做一次归并)。

  3. distribute by和sort by一起使用

    distribute by是控制map的输出在reducer是如何划分的，比如有一张店铺表store，mid是所属商户，money是商户的盈利，name是店铺名字。HiveSQL语句：

所有mid相同的数据会被发送到同一个reducer去处理，因为指定了distribute by mid，统计出每个商户中各个商店的盈利的排序(排序是全局有序的，相同商户是在同一个reducer处理)。distribute by要在sort by之前。

  1. cluster by

    cluster by的功能就是distribute by和sort by相结合，功能是一样的。

被cluster by指定的列只能是降序，不能指定asc和desc。

13. Hive中的split、coalesce以及collect list函数的用法？

  1. split将字符串转化为数组，即split('a,b,c,d') ==> ["a","b","c","d"]
  2. coalesce(T v1, T v2, ...)返回参数中的第一个非空值：如果所有值都为null，则返回null。如果某个默认值为null，想要返回的不是null，而是0或者其他值，可以使用coalesce函数：
  1. collect_list将分组中的某列转换为一个数组返回，不去重(列转行函数)。
  1. collect_set也是将分组中的某列转为一个数组返回，但是去重。

14. 描述一下数据库中的null，null在Hive底层是如何存储的？

  1. null与任何值运算的结果都是null，可以使用is null、is not null函数指定其值为null情况下的取值。
  2. null在Hive底层默认使用'\N'来存储，可以通过alter table test set SERDEPROPERTIES('serialization.null.format'='a')来修改。
  3. 查询出t1表中存在但是t2表中不存在的id相对应的t1表的所有字段。

15. Hive中有哪些保存元数据的方式及其特点

  • 内嵌模式，数据库derby，也是基于Java、JDBC和SQL标准
  • 本地模式，MySQL数据库，数据存储模式可以自己设置，持久化好，查看方便。
  • 远程模式，用于非Java客户端访问元数据库，在服务端启动MetaStoreServer，客户端利用Thrift协议通过MetaStoreServer访问元数据库。将MetaStore分离出来独立成一个Hive服务(可以部署多个)，可以避免认证信息泄露等。

16. 生产环境中为什么建议使用外部表？

  1. 因为外部表不会加载数据到Hive，减少数据传输、数据还能共享。
  2. Hive不会修改数据，所以无需担心数据的损坏。
  3. 删除表时，只删除表结构、不删除数据。

往期推荐：

MapReduce和YARN基础面试题总结

HDFS基础面试题总结

建设数据中台到底有什么⽤？

数据中台从哪⾥来，要到哪⾥去？

数据中台在⽹易电商业务的最佳实践

知了小巷

长按识别二维码，一键关注

hive怎么处理过滤掉满足多个多个条件的记录_Hive基础面试题总结
hive怎么处理过滤掉满足多个多个条件的记录_Hive基础面试题总结
hive怎么处理过滤掉满足多个多个条件的记录
上一篇: 压缩JS，提高代码执行速度
下一篇: 一文搞懂Q-Q plot图的含义

继续阅读