大数据开发实习准备记录12

这是在学习大数据各种组件之后，开始系统的刷sql题和面试题的第十二天！

在牛客上选择SQL大厂面试真题系统刷题。

Sql：

1、2021年国庆在北京接单3次及以上的司机统计信息
--看清题目要求，接单了的司机也算，那么就算订单取消，那也算接过一次单
select
city,
round(avg(order_num) ,3)avg_order_num,
round(avg(income),3) avg_income
from
(select
city,
count(order_id) order_num,
sum(fare) income
from tb_get_car_record join tb_get_car_order using(order_id)
where (date(order_time) between '20211001' and '20211007') and city = '北京'
group by driver_id,city
having count(order_id) >= 3)t1
group by city2、有取消订单记录的司机平均评分
--ifnull()为空则返回第二个值  with rollup 根据每列不同的聚合函数进行总的聚合，比如该列是sum，那么就返回总体sum，如果该列是avg，那就对全体进行avg
SELECT IFNULL(driver_id, "总体") as driver_id,ROUND(avg(grade), 1) as avg_grade
FROM tb_get_car_order
WHERE driver_id in (SELECT driver_idFROM tb_get_car_orderWHERE DATE_FORMAT(order_time, "%Y-%m")='2021-10' AND ISNULL(fare)
) AND NOT ISNULL(grade)
GROUP BY driver_id
with rollup3、每个城市中评分最高的司机信息
--先不急的找出符合的司机，先把需要的信息整合最后再根据字段取得要求的数据
select city,driver_id,round(avg_grade,1) avg_grade ,round(avg_order_num,1) avg_order_num,
round(avg_mileage,3) avg_mileage
from
(select *, rank() over(partition by city order by avg_grade desc) rk from
(select city, driver_id,avg(grade) avg_grade,
count(order_id)/count(distinct date(order_time)) avg_order_num,
sum(mileage)/count(distinct date(order_time)) avg_mileage
from tb_get_car_order
join
tb_get_car_record
using(order_id)
group by city,driver_id) t1) t2
where rk =1
order by avg_order_num4、国庆期间近7日日均取消订单量
--近七日问题求解方法合集：
1、开窗函数sum()over(order by ... rows 6 preceding)
2、timestampdiff(day,date(order_time),dt) between 0 and 6  date(order_time)和dt来自两个相同的表
with t as
(select date(order_time) as dt,count(start_time) as com,
count(order_time)-count(start_time) as ucom from  tb_get_car_order
group by dt)
select * from
(select dt,round(sum(com)over(order by dt rows 6 preceding)/7,2) as fn,
round(sum(ucom)over(order by dt rows 6 preceding)/7,2) as fm
from t) as u
where dt between '2021-10-01' and '2021-10-03'
order by dtselect dt,round(finish_num/7,2) finish_num_7d,
round(cancel_num/7,2) cancel_num_7d
from
(select distinct date(order_time) dt,
(select sum(if(start_time is null,0,1))
from tb_get_car_order
where timestampdiff(day,date(order_time),dt) between 0 and 6
)  finish_num,
(select sum(if(start_time is null,1,0))
from tb_get_car_order
where timestampdiff(day,date(order_time),dt) between 0 and 6
)  cancel_num
from
tb_get_car_order t1
where
date(order_time) between '2021-10-01' and '2021-10-03')t25、工作日各时段叫车量、等待接单时间和调度时间
--count在计算时自动过滤掉null，不算在内，尽量使用avg求平均值
--date_format(event_time,'%W') not in ('Saturday','Sunday') 
--WHERE DAYOFWEEK(event_time) BETWEEN 2 AND 6
SELECT period, COUNT(1) as get_car_num,ROUND(AVG(wait_time/60), 1) as avg_wait_time,ROUND(AVG(dispatch_time/60), 1) as avg_dispatch_time
FROM (SELECT event_time,CASEWHEN HOUR(event_time) IN (7, 8) THEN '早高峰'WHEN HOUR(event_time) BETWEEN 9 AND 16 THEN '工作时间'WHEN HOUR(event_time) IN (17, 18, 19) THEN '晚高峰'ELSE '休息时间'END as period,TIMESTAMPDIFF(SECOND, event_time, end_time) as wait_time,TIMESTAMPDIFF(SECOND, order_time, start_time) as dispatch_timeFROM tb_get_car_recordJOIN tb_get_car_order USING(order_id)WHERE DAYOFWEEK(event_time) BETWEEN 2 AND 6
) as t_wait_dispatch_time
GROUP BY period
ORDER BY get_car_num;6、各城市最大同时等车人数
--典型的uv瞬时记录   
--注意uv desc 是要添加的，因为要保证+1在前-1在后，不然结果可能出错
WITH t1 AS(SELECT city,SUM(uv)OVER(PARTITION BY city ORDER BY uv_time,uv DESC) AS uv_cnt #每个城市等车瞬时UVFROM (SELECT city,event_time uv_time,1 AS uv FROM  tb_get_car_record #进入等车状态UNION ALLSELECT city,end_time uv_time,-1 AS uv FROM  tb_get_car_record WHERE order_id IS NULL #接单前取消UNION ALLSELECT city,IFNULL(start_time,finish_time) uv_time,-1 AS uv FROM tb_get_car_order LEFT JOIN tb_get_car_record USING(order_id)#接单后取消或上车)AS t WHERE DATE_FORMAT(uv_time,'%Y%m')='202110' #2021年10月)SELECT city,MAX(uv_cnt) max_wait_uv FROM t1 GROUP BY citY ORDER BY max_wait_uv,citY;#排序先按照uv升序，uv一样按照城市升序

面试题：

1. hive 内部表和外部表的区别

未被external修饰的是内部表（managed table），被external修饰的为外部表（external table）

区别：

内部表数据由Hive自身管理，外部表数据由HDFS管理；

内部表数据存储的位置是hive.metastore.warehouse.dir（默认：/user/hive/warehouse），外部表数据的存储位置由自己制定（如果没有LOCATION，Hive将在HDFS上的/user/hive/warehouse文件夹下以外部表的表名创建一个文件夹，并将属于这个表的数据存放在这里）；

删除内部表会直接删除元数据（metadata）及存储数据；删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除；

2. hive 有索引吗

Hive支持索引，但是Hive的索引与关系型数据库中的索引并不相同，比如，Hive不支持主键或者外键。

Hive索引可以建立在表中的某些列上，以提升一些操作的效率，例如减少MapReduce任务中需要读取的数据块的数量。

在可以预见到分区数据非常庞大的情况下，索引常常是优于分区的。

虽然Hive并不像事物数据库那样针对个别的行来执行查询、更新、删除等操作。它更多的用在多任务节点的场景下，快速地全表扫描大规模数据。但是在某些场景下，建立索引还是可以提高Hive表指定列的查询速度。（虽然效果差强人意）

索引适用的场景

适用于不更新的静态字段。以免总是重建索引数据。每次建立、更新数据后，都要重建索引以构建索引表。

Hive索引的机制如下：

hive在指定列上建立索引，会产生一张索引表（Hive的一张物理表），里面的字段包括，索引列的值、该值对应的HDFS文件路径、该值在文件中的偏移量;

v0.8后引入bitmap索引处理器，这个处理器适用于排重后，值较少的列（例如，某字段的取值只可能是几个枚举值）

因为索引是用空间换时间，索引列的取值过多会导致建立bitmap索引表过大。

但是，很少遇到hive用索引的。说明还是有缺陷or不合适的地方的。

3. 运维如何对hive进行调度

将hive的sql定义在脚本当中

使用azkaban或者oozie进行任务的调度

监控任务调度页面

4. ORC、Parquet等列式存储的优点

ORC和Parquet都是高性能的存储方式，这两种存储格式总会带来存储和性能上的提升

Parquet:

Parquet支持嵌套的数据模型，类似于Protocol Buffers，每一个数据模型的schema包含多个字段，每一个字段有三个属性：重复次数、数据类型和字段名。

重复次数可以是以下三种：required(只出现1次)，repeated(出现0次或多次)，optional(出现0次或1次)。每一个字段的数据类型可以分成两种：

group(复杂类型)和primitive(基本类型)。

Parquet中没有Map、Array这样的复杂数据结构，但是可以通过repeated和group组合来实现的。

由于Parquet支持的数据模型比较松散，可能一条记录中存在比较深的嵌套关系，如果为每一条记录都维护一个类似的树状结可能会占用较大的存储空间，因此Dremel论文中提出了一种高效的对于嵌套数据格式的压缩算法：Striping/Assembly算法。通过Striping/Assembly算法，parquet可以使用较少的存储空间表示复杂的嵌套格式，并且通常Repetition level和Definition level都是较小的整数值，可以通过RLE算法对其进行压缩，进一步降低存储空间。

Parquet文件是以二进制方式存储的，是不可以直接读取和修改的，Parquet文件是自解析的，文件

中包括该文件的数据和元数据。

ORC:

ORC文件是自描述的，它的元数据使用Protocol Buffers序列化，并且文件中的数据尽可能的压缩以降低存储空间的消耗。

和Parquet类似，ORC文件也是以二进制方式存储的，所以是不可以直接读取，ORC文件也是自解析的，它包含许多的元数据，这些元数据都是同构ProtoBuffer进行序列化的。

ORC会尽可能合并多个离散的区间尽可能的减少I/O次数。

ORC中使用了更加精确的索引信息，使得在读取数据时可以指定从任意一行开始读取，更细粒度的统计信息使得读取ORC文件跳过整个row group，ORC默认会对任何一块数据和索引信息使用ZLIB压缩，因此ORC文件占用的存储空间也更小。

在新版本的ORC中也加入了对Bloom Filter的支持，它可以进一步提升谓词下推的效率，在Hive 1.2.0版本以后也加入了对此的支

持。

5. 数据建模用的哪些模型？

星型模型

星形模式(Star Schema)是最常用的维度建模方式。星型模式是以事实表为中心，所有的维度表直接连接在事实表上，像星星一样。

星形模式的维度建模由一个事实表和一组维表成，且具有以下特点：

a. 维表只和事实表关联，维表之间没有关联；

b. 每个维表主键为单列，且该主键放置在事实表中，作为两边连接的外键；

c. 以事实表为核心，维表围绕核心呈星形分布；

雪花模型

雪花模式(Snowflake Schema)是对星形模式的扩展。雪花模式的维度表可以拥有其他维度表的，虽然这种模型相比星型更规范一些，但是由于这种模型不太容易理解，维护成本比较高，而且性能方面需要关联多层维表，性能也比星型模型要低。所以一般不是很常用。

星座模型

星座模式是星型模式延伸而来，星型模式是基于一张事实表的，而星座模式是基于多张事实表的，而且共享维度信息。前面介绍的两种维度建模方法都是多维表对应单事实表，但在很多时候维度空间内的事实表不止一个，而一个维表也可能被多个事实表用到。在业务发展后期，绝大部分维度建模都采用的是星座模式。

6. 为什么要对数据仓库分层？

用空间换时间，通过大量的预处理来提升应用系统的用户体验（效率），因此数据仓库会存在大量冗余的数据。

如果不分层的话，如果源业务系统的业务规则发生变化将会影响整个数据清洗过程，工作量巨大。

通过数据分层管理可以简化数据清洗的过程，因为把原来一步的工作分到了多个步骤去完成，相当于把一个复杂的工作拆成了多个简单的工作，把一个大的黑盒变成了一个白盒，每一层的处理逻辑都相对简单和容易理解，这样我们比较容易保证每一个步骤的正确性，当数据发生错误的时候，往往我们只需要局部调整某个步骤即可。

7. 使用过Hive解析JSON串吗

针对json格式的数据建表的时候没办法直接把每个字段都定义出来 通常的解决方案是先写一个mapreduce数据清洗任务，只需要map阶段就行了，对json格式的数据进行解析，把里面每个字段的值全部解析出来，拼成一行，字段值中间可以使用逗号分割，然后再基于解析之后的数据在hive中建表就可以了。

这个解决方案没有任何问题，如果硬要挑问题，那就只能鸡蛋里面挑骨头，说这个解决方案比较麻烦，还需要写MapReduce，不够优雅了。

我们开发人员有时候一定要懒，这个懒不是说什么都不敢，而是要想办法把复杂的问题简单化，这个懒可以督促你去找出最方便快捷的解决方案。

在这里我们需要提前涉及一个函数get_json_object，这个函数可以从json格式的数据中解析出指定字段 所以我的思路是这样的， 先基于原始的json数据创建一个外部分区表，表中只有一个字段，保存原始的json字符串即可，分区字段是日期和数据类型

然后再创建一个视图，视图中实现的功能就是查询前面创建的外部分区表，在查询的时候会解析json数据中的字段 这样就方便了，我们以后查询直接查视图就可以查询出我们需要的字段信息了，并且一行代码都不需要写。

8. sort by 和 order by 的区别

order by 会对输入做全局排序，因此只有一个reducer（多个reducer无法保证全局有序）只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。

sort by不是全局排序，其在数据进入reducer前完成排序.

因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1， 则sort by只保证每个reducer的输出有序，不保证全局有序。

9. 怎么排查是哪里出现了数据倾斜

实操 | Hive 数据倾斜问题定位排查及解决

1. 通过时间判断

如果某个 reduce 的时间比其他 reduce 时间长的多，如下图，大部分 task 在 1 分钟之内完成，只有 r_000000 这个 task 执行 20 多分钟了还没完成

2. 通过任务 Counter 判断

Counter 会记录整个 job 以及每个 task 的统计信息。

4. 定位 SQL 代码

1. 确定任务卡住的 stage

通过 jobname 确定 stage：

一般 Hive 默认的 jobname 名称会带上 stage 阶段，如下通过 jobname 看到任务卡住的为 Stage-4：

如果 jobname 是自定义的，那可能没法通过 jobname 判断 stage。需要借助于任务日志：

2. 确定 SQL 执行代码

确定了执行阶段，即 stage。通过执行计划，则可以判断出是执行哪段代码时出现了倾斜。还是从此图，这个 stage 中进行连接操作的表别名是 d：

10. 数据倾斜怎么解决

Hive千亿级数据倾斜解决方案（好文收藏）

1. 空值引发的数据倾斜

2. 不同数据类型引发的数据倾斜

3. 不可拆分大文件引发的数据倾斜

4. 数据膨胀引发的数据倾斜

5. 表连接时引发的数据倾斜

6. 确实无法减少数据量引发的数据倾斜

1. 过滤掉脏数据

如果大 key 是无意义的脏数据，直接过滤掉。本场景中大 key 有实际意义，不能直接过滤掉。

2. 数据预处理

数据做一下预处理（如上面例子，对 null 值赋一个随机值），尽量保证 join 的时候，同一个 key 对应的记录不要有太多。

3. 增加 reduce 个数

如果数据中出现了多个大 key，增加 reduce 个数，可以让这些大 key 落到同一个 reduce 的概率小很多。

配置 reduce 个数：

set mapred.reduce.tasks = 15;

4. 转换为 mapjoin

如果两个表 join 的时候，一个表为小表，可以用 mapjoin 做。

配置 mapjoin：

set hive.auto.convert.join = true;  是否开启自动mapjoin，默认是true

set hive.mapjoin.smalltable.filesize=100000000;   mapjoin的表size大小

5. 启用倾斜连接优化

hive 中可以设置 hive.optimize.skewjoin 将一个 join sql 分为两个 job。同时可以设置下 hive.skewjoin.key，此参数表示 join 连接的 key 的行数超过指定的行数，就认为该键是偏斜连接键，就对 join 启用倾斜连接优化。默认 key 的行数是 100000。

配置倾斜连接优化：

set hive.optimize.skewjoin=true; 启用倾斜连接优化

set hive.skewjoin.key=200000; 超过20万行就认为该键是偏斜连接键

6. 调整内存设置

适用于那些由于内存超限任务被 kill 掉的场景。通过加大内存起码能让任务跑起来，不至于被杀掉。该参数不一定会明显降低任务执行时间。

配置内存：

set mapreduce.reduce.memory.mb=5120; 设置reduce内存大小

set mapreduce.reduce.java.opts=-Xmx5000m -XX:MaxPermSize=128m;

11. hive 小文件过多怎么解决

1. 使用 hive 自带的 concatenate 命令，自动合并小文件

使用方法：

#对于非分区表
alter table A concatenate;#对于分区表
alter table B partition(day=20201224) concatenate;

2. 调整参数减少Map数量
设置map输入合并小文件的相关参数：
#执行Map前进行小文件合并
#CombineHiveInputFormat底层是 Hadoop的 CombineFileInputFormat 方法
#此方法是在mapper中将多个文件合成一个split作为输入
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; -- 默认

减少Reduce的数量
#reduce 的个数决定了输出的文件的个数，所以可以调整reduce的个数控制hive表的文件数量，
#hive中的分区函数 distribute by 正好是控制MR中partition分区的，
#然后通过设置reduce的数量，结合分区函数让数据均衡的进入每个reduce即可。

#设置reduce的数量有两种方式，第一种是直接设置reduce个数
set mapreduce.job.reduces=10;

#第二种是设置每个reduce的大小，Hive会根据数据总大小猜测确定一个reduce个数
set hive.exec.reducers.bytes.per.reducer=5120000000; -- 默认是1G，设置为5G
 

12. hive优化有哪些？

数据存储及压缩。

针对hive中表的存储格式通常有orc和parquet，压缩格式一般使用snappy。相比与textfile格式表，orc占有更少的存储。因为hive底层使用MR计算架构，数据流是hdfs到磁盘再到hdfs，而且会有很多次，所以使用orc数据格式和snappy压缩策略可以降低IO读写，还能降低网络传输量，这样在一定程度上可以节省存储，还能提升hql任务执行效率；

通过调参优化。

并行执行，调节parallel参数；

调节jvm参数，重用jvm；

设置map、reduce的参数；

开启strict mode模式；

关闭推测执行设置。

有效地减小数据集将大表拆分成子表；结合使用外部表和分区表。

SQL优化

大表对大表：尽量减少数据集，可以通过分区表，避免扫描全表或者全字段；

大表对小表：设置自动识别小表，将小表放入内存中去执行。

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！