大数据得Hive面试题

2. Hive和数据库比较

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

• 数据存储位置

  • Hive存储在HDFS上，数据库将数据保存在块设备或者本地文件系统中

• 数据更新

  • Hive默认不支持update，delete操作，需要开始事务配置。一般场景不建议使用跟新。mysql支持更新删除操作。如果在hive中需要update。可以insert into 新表 select 字段1，字段2，if（更新条件，返回，否则返回）from 旧表

• 执行延迟

  • Hive执行延迟较高，数据库的执行延迟较低。这个是有条件的，在数据规模较小时，在数据规模大到超过数据库的处理能力的时候，Hive的并行计算显然能体现出优势。

• 数据规模

  • Hive支持很大规模的数据计算，数据库可以支持的数据规模较小。

• SQL语法区别： 1 hive不支持where后面跟子查询。 2 hive支持建表分区操作，mysql不支持。 3 group by。hive中sql，select 中的维度字段，必须出现在group by 后面。mysql语法可以不用。 省份，城市，确诊病例 select pro,city,sum(quezhen) s from t group by pro,city 4 sort by,distribute by,group by 5 mapjoin ,mysql没有。

3. 内部表和外部表

内部表和外部表的区别在于元数据和原始数据

• 删除数据时：

  • 内部表：元数据和原始数据全部删除

  • 外部表：只删除元数据

• 在公司生产环境下，什么时候创建内部表，什么时候创建外部表？

  • 在公司中绝大多数场景都是创建外部表

  • 自己使用的临时表，才会创建内部表

4. 4个By的区别

在生产环境中order by用的较少，容易导致OOM

在生产环境中sort by+distribute by用的多

• order by：全局排序，只有一个reduce

• sort by：分区内有序

• distribute by：类似于MR中partition，进行分区，结合sort by使用

• cluster by：当distribute by和sort by字段相同的时候，可以使用cluster by方式。cluster by除了具有distribute by的功能外还兼具了sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。

5.系统函数

• date_add、date_sub函数（加减日期）

• next_day函数（周指标相关）

• date_format函数（根据格式整理日期）

• last_day函数（求当月最后一天日期）

• collect_set函数

• get_json_object解析json函数

• NVL(表达式1，表达式2)

  • 如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值。

6.自定义UDF、UDTF函数

• 在项目中是否自定义过UDF、UDTF函数，以及用他们处理了什么问题，及自定义步骤？

  • 用UDF函数解析公共字段，用UDTF函数解析事件字段

  • 自定义UDF：继承UDF，重写evaluate方法

  • 自定义UDTF：继承自GenericUDTF，重写3个方法：initialize（自定义输出的列名和类型），process（将结果返回forward(result)），close

• 为什么要定义UDF、UDTF？

  • 因为自定义函数，可以自己埋点Log打印日志，出错或者数据异常，方便调试

  • 有些需求SQL无法直接处理，可以使用代码辅助解决

7.窗口函数

• Rank

  • Rank()排序相同时会重复，总数不会变

    • rank() over(partiition by regionX order by nameX desc) as tn

    • 1 93;2 90;2 90;4 89 排名不是连续的，相同的分数是同名次，前 100 名只有 100 个

  • dense_rank()排序相同时会重复，总数会减少

    • dense_rank() over()

    • 1 93;2 90;2 90;3 89 排名是连续的，相同的分数是同名次，前 100 名可能多于 100 个

  • row_number()会根据顺序计算

    • row_number() over(partition by regionX order by nameX desc) as tn

    • 1 93;2 90;3 90 排名是连续的，相同的分数会有排名先后，前 100 名只有 100 个

• over()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化

  • current row：当前行

  • n preceding：往前n行数据

  • n following：往后n行数据

  • UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点

  • lag(col,n)：往前第n行数据

  • lead(col,n)：往后第n行数据

  • NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

• 手写TopN

  # 例如 统计班级前3名
  select  tt1.id
          ,tt1.name
          ,tt1.clazz
          ,tt1.sum_score
          ,tt1.rn
  from(
      select  t1.id
              ,t1.name
              ,t1.clazz
              ,t2.sum_score
              ,row_number() over(partition by clazz order by t2.sum_score desc) as rn
      from students t1
      left join(
          select  id
                  ,sum(score) as sum_score
          from score
          group by id
      ) t2 on t1.id = t2.id
  ) tt1 where tt1.rn<=3；

8.Hive优化

• MapJoin

  • 如果不指定MapJoin或者不符合MapJoin的条件，那么Hive解析器会将Join操作转换成Common Join，即在reduce阶段完成join。容易发生数据倾斜，可以用MapJoin把小表全部加载到内存在map端进行join，避免reduce处理。

• 行列过滤

  • 列处理：在select中，只拿需要的列，如果有，尽量使用分区过滤，少用select *

  • 行处理：在分区裁剪中，当使用外关联时，如果将副表的过滤条件写在where后面，那么就会先全表关联，之后在过滤。

• 列式存储

• 采用分区技术 -避免全表扫描，提高查询效率。一般数据量比较大的表，要建分区，一般使用日期作为分区字段。

• 合理设置Map数

  • mapred.min.split.size：指的是数据的最小分割单元大小

  • mapred.max.split.size：指的是数据的最大分割单元大小

  • 通过调整max可以起到调整map数的作用，减小max可以增大map数，增大max可以减少map数

  • 需要提醒的是，直接调整mapred.map.tasks这个参数是没有效果的。

• 合理设置Reduce数

  reduce个数并不是越多越好

  • 过的的启动和初始化reduce也会消耗时间和资源

  • 另外有多少个reduce就会输出多少个文件，如果生成了很多个小文件，那么如果这些小文件作为下一次任务的输入，则也会出现小文件过多的问题

  • 在设置reduce个数的时候也需要考虑这两个原则；处理大数据量利用合适的reduce数，使单个的reduce任务处理数据量大小要合适

• 小文件如何产生的？

  • 动态分区插入数据，产生大量的小文件，从而导致map数量剧增

  • reduce数量越多，小文件也越多（reduce的个数和输出文件是对应的)

  • 数据源本身就包含大量的小文件。

• 小文件解决方案

  1. 在Map执行前合并小文件，减少Map数

     • CombineHiveInputFormat具有对小文件进行合并的功能（系统默认的格式）。HiveInputFormat没有对小文件合并功能。

  2. merge

     • 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

  3. 开启JVM重用

     • set mapreduce.job.jvm.numtasks=10

• 开启map端combiner（在不影响最终业务逻辑）

  • set hive.map.aggr=true;

• 压缩（选择快的）

  设置map端输出、中间结果压缩（不完全是解决数据倾斜的问题，但是减少了IO读写和网络传输，能提高很多效率）

  • set hive.exec.compress.intermediate=true --启用中间数据压缩

  • set mapreduce.map.output.compress=true --启用最终数据压缩

  • set mapreduce.map.outout.compress.codec=…; --设置压缩方式

• 采用tez引擎或者spark引擎

9.Hive解决数据倾斜方法

• 数据倾斜的本质原因 key值分布不均匀，key重复的比较多。一般在group by，join，distinct容易发生倾斜。

• 数据倾斜长什么样

• 怎么产生的数据倾斜

  1. 不同数据类型关联产生数据倾斜

     • 情形：比如用户表中user_id字段为int，log表中user_id字段既有string类型也有int类型。当按照user_id进行两个表的Join操作时。

     • 后果：处理此特殊值的reduce耗时；只有一个reduce任务。默认的Hash操作会按int型的id来进行分配，这样会导致所有string类型id的记录都分配到一个Reducer中。

     • 解决方案：把数据类型转换成字符串类型

       • select * from users a
         left outer join logs b
         on a.usr_id = cast(b.user_id as string)

  2. 控制空值分布

     在生产环境经常会用大量空值数据进入到一个reduce中去，导致数据倾斜

     • 解决方案：自定义分区，将为空的key转变为字符串加随机数或纯随机数，将因空值而造成倾斜的数据分不到多个Reducer。

     • 注意：对于异常值如果不需要的话，最好是提前在where条件里过滤掉，这样可以使计算量大大减少

• 解决数据倾斜的方法

  1. group by

     group by优于distinct group

     • 采用sum() group by 的方式来替换count(distinct) 完成计算

  2. mapjoin

  3. 开启数据倾斜时负载均衡

     思想：先随机分发并处理，再按照key group by来分发处理

     操作：当选项设置为true，生成的查询计划会有两个MRJob

     第一个MRJob中，map的输出结果集合会随机分布到reduce中，每个reduce在做部分聚合操作，并输出结果，这样的处理结果是相同的group by key有可能被分发到不同的reduce中，从而达到负载均衡的目的

     第二个MRJob在根据预处理的数据结果按照group by key分不到reduce中（这个可以保证相同的原始group by key被分布到同一个reduce中），最后完成最终的聚合操作。

     • set hive.groupby.skewindata=true;

10.Hive里边字段的分隔符用的是什么？为什么用\t？有遇到过字段里边有\t的情况吗，怎么处理的？

• hive 默认的字段分隔符为ascii码的控制符\001（^A）,建表的时候用fields terminated by '\001'。注意：如果采用\t或者\001等为分隔符，需要要求前端埋点和javaEE后台传递过来的数据必须不能出现该分隔符，通过代码规范约束。一旦传输过来的数据含有分隔符，需要在前一级数据中转义或者替换（ETL）。

11.Tez引擎优点

• Tez可以将多个有依赖的作业转换为一个作业，这样只需写一次HDFS，且中间节点较少，从而大大提升作业的计算性能

• MR、Tez、Spark计算引擎的区别：

  • MR引擎：多job串联，基于磁盘，落盘的地方比较多，虽然慢，但一定能跑出结果，一般处理周、月、年指标

  • Spark引擎：虽然在shuffle过程中也落盘，但是并不是所有的算子都需要shuffle，尤其是多算子过程，中间过程不落盘，而且有DAG有向无环图，兼顾了可靠性和效率。一般处理天指标。

  • Tez引擎：完全基于内存，如果数据量特别大，请慎重，容易OOM。一般用于快速出结果，数据量比较小的场景。

12.MySQL元数据备份

• MySQL之元数据备份（项目中遇到的问题）

  • 元数据备份

    • 如数据损坏，可能整个集群无法运行，至少要保证每日零点之后备份到其它服务器两个副本

  • MySQL utf8超过字节数问题

    • MySQL的utf8编码最多存储3个字节，当数据中存在表情号、特色符号时会占用超过3个字节数的字节，那么会出现错误 Incorrect string value: '\xF0\x9F\x91\x91\xE5\xB0...'

    • 解决办法：将utf8修改为utf8mb4

      • 首先修改库的基字符集和数据库排序规则

      • 再使用 SHOW VARIABLES LIKE '%char%'; 命令查看参数

      • 确保character_set_client、character_set_server、character_set_connection、character_set_database这几个参数的值为utf8mb4如果不是使用set命令进行修改，如set character_set_server = utf8mb4;

13.Union与Union all区别

• union会将联合的结果集去重，效果较union all差

• union all 不会对结果集去重，所以效率高

14.数据清洗怎么做的？怎么用spark做数据清洗

数据清洗的目的是为了保证数据质量，包括数据的完整性、唯一性、一致性、合法性和权威性。数据清洗的结果是对各种脏数据进行对应的处理方式，从而得到标准的、干净的、连续的数据，提供给数据统计和数据挖掘使用。

• 解决数据的完整性问题

  • 通过其他信息补全

  • 通过前后数据补全

  • 如果实在无法补全，虽然可惜，但是还是要剔除掉进行统计。如果后续其他分析还需要这没必要删除。

• 解决数据唯一性问题

  • 根据主键进行去除，去除重复数据

  • 制定一系列规则，保证根据某种规则下只保存一条数据。

• 解决数据权威性的问题

  • 选择最权威的数据作为统计和挖掘

• 解决合法性的问题

  • 设定判定规则，通过特定的规则来判断字段或者值来确定数据是否需要被清洗

15.Hive分区分桶的区别

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

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

• 分桶对数据的处理比分区更加的细化，分区针对的是数据的存储路径，分桶针对的是数据文件

• 分桶是按照hash值进行切分的，相对来说比较公平，分区是按照列的值划分，容易造成数据倾斜

• 分桶、分区不干扰，分区表可以划分为分桶表

16.Hive的执行顺序

• from --> join on --> where --> group by --> 聚合函数 -->having -->select -->distinct--> order by

17.Hive和HBase的区别和联系

• 联系

  • HBase和Hive都是架构在hadoop之上的，都是用HDFS作为底层存储

• 区别

  • Hive是建立在Hadoop之上为了减少MapReduce jobs编写工作的批处理系统，HBase是为了支持弥补Hadoop对实时操作的缺陷的项目 。总的来说，hive是适用于离线数据的批处理，hbase是适用于实时数据的处理。

  • Hive本身不存储和计算数据，它完全依赖于HDFS存储数据和MapReduce处理数据，Hive中的表纯逻辑。

  • hbase是物理表，不是逻辑表，提供一个超大的内存hash表，搜索引擎通过它来存储索引，方便查询操作。

  • 由于HDFS的不可随机读写，hive是不支持随机写操作，而hbase支持随机写入操作。

  • HBase只支持简单的键查询，不支持复杂的条件查询

18.Spark on Hive和Hive on Spark的区别

• Spark on Hive

  顾名思义即将Spark构建在Hive之上，Spark需要用到Hive

  • 就是通过Spark SQL加载Hive的配置文件，获取到Hive的metastore信息，进而获得metastore，但底层运行的还是Spark RDD

  • Spark SQL获取到metastore之后就可以取访问Hive表的数据

  • 接下来就可以通过Spark SQL来操作Hive表中存储的数据

• Hive on Spark

  顾名思义即将Hive构建在Spark之上，Hive需要用到Spark

  • Hive的底层默认计算引擎从MapReduce改为Spark

19.Hive数据格式和压缩格式

行存储的特点：查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中的一个值，其余的值都在相邻的地方，因此此时行存储查询的速度快

列存储的特点：因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量，每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法

• 数据格式

  • textFile

    • 行存储，这种格式使得Hive不会对数据进行拆分，导致无法对数据进行并行操作

  • orcFile

    • 这个格式可以提高hive读、写、处理数据的原因

    • 这个格式的hive数据是按照行存储分块、每个块又按照列存储

    • 这个格式的每个块默认大小为256MB，块大了之后是的orc格式可以支持索引

  • sequenceFile

    • 二进制文件、行存储

• 存储和压缩

  • zlib：压缩率比较高、但是压缩行能一般

  • snappy：压缩速度快、压缩率合理、压缩率比zlib格式要低

  • gzip：压缩率比较高，而且压缩速度也比较快

20. 连续登陆问题

在电商、物流和银行可能经常会遇到这样的需求：统计用户连续交易的总额、连续登陆天数、连续登陆开始和结束时间、间隔天数等

• 建表语句

create table deal_tb(
    id string
    ,datestr string
    ,amount string
)row format delimited fields terminated by ',';

• 计算逻辑

注意每个用户每天可能会有多条交易记录

• 先按用户和日期分组求和，使每个用户每天只有一条数据

 select  id
         ,datestr
         ,sum(amount) as sum_amount
 from deal_tb
 group by id,datestr

• 根据用户ID分组按日期排序，将日期和分组序号相减得到连续登陆的开始日期，如果开始日期相同说明连续登陆

 select  tt1.id
         ,tt1.datestr
         ,tt1.sum_amount
         ,date_sub(tt1.datestr,rn) as grp
 from(
     select  t1.id
             ,t1.datestr
             ,t1.sum_amount
             ,row_number() over(partition by id order by datestr) as rn
     from(
        select  id
                 ,datestr
                 ,sum(amount) as sum_amount
        from deal_tb
        group by id,datestr
     ) t1
 ) tt1

• 统计用户连续交易的总额、连续登陆天数、连续登陆开始和结束时间、间隔天数

select  ttt1.id
        ,ttt1.grp
        ,round(sum(ttt1.sum_amount),2) as sc_sum_amount
        ,count(1) as sc_days
        ,min(ttt1.datestr) as sc_start_date
        ,max(ttt1.datestr) as sc_end_date
        ,datediff(ttt1.grp,lag(ttt1.grp,1) over(partition by ttt1.id order by ttt1.grp)) as iv_days
from(
    select  tt1.id
            ,tt1.datestr
            ,tt1.sum_amount
            ,date_sub(tt1.datestr,rn) as grp
    from(
        select  t1.id
                ,t1.datestr
                ,t1.sum_amount
                ,row_number() over(partition by id order by datestr) as rn
        from(
            select  id
                    ，datestr
                    ,sum(amount) as sum_amount
            from deal_tb
            group by id,datestr
        ) t1
    ) tt1
) ttt1
group by ttt1.id,ttt1.grp；

• 精简版

select  t1.id
        ,t1.grp
        ,round(sum(t1.sum_amount),3) as total_amount -- 连续交易总额
        ,count(1) as total_days -- 连续登录天数
        ,min(datestr) as start_date -- 连续登录开始的时间
        ,max(datestr) as end_date -- 连续登录结束的时间
        ,datediff(t1.grp,lag(t1.grp,1) over(partition by t1.id order by t1.grp)) as interval_days -- 间隔天数
from (
    select  id
            ，datestr
            ,round(sum(amount),3) as sum_amount
            ,date_sub(datestr,row_number() over(partition by id order by datestr)) as grp
    from deal_tb
    group by id,datestr
) t1 group by t1.id,t1.grp；

• 结果

1   2019-02-07  13600.233    3   2019-02-08  2019-02-10 NULL
1   2019-02-08  2991.6500    5   2019-02-12  2019-02-16  1
1   2019-02-09  1510.88      2   2019-02-18  2019-02-19  1
1   2019-02-10  537.711      1   2019-02-21  2019-02-21  1
2   2019-02-07  13600.233    3   2019-02-08  2019-02-10 NULL
2   2019-02-08  3026.6499    4   2019-02-12  2019-02-15  1
2   2019-02-10  1510.88      2   2019-02-18  2019-02-19  2
2   2019-02-11  537.711      1   2019-02-21  2019-02-21  1
3   2019-02-07  13600.233    3   2019-02-08  2019-02-10 NULL
3   2019-02-08  2730.044     5   2019-02-12  2019-02-16  1
3   2019-02-09  1510.88      2   2019-02-18  2019-02-19  1
3   2019-02-10  537.711      1   2019-02-21  2019-02-21  1