数据开发面试总结

又到了金三银四的季节，但是今年格外冷清。当年瞧不上的外包，今年高攀不起，市场好凉，数开数分能干点啥呢，害…今年可能真的是“事未必月在楼头”。不管怎样，该找工作还得找工作，那就自己总结总结吧。

一、常用数据库有哪些

mysql 开源,小型关系型数据库管理系统
orcale 收费、安全性高，适合做大型数据库，支持多种系统平台
sqlserver 安全性高，真正的客户机/服务器体系结构

二、传统数据库与HIVE的区别

1、查询语言：传统的数据库使用的是sql语句，hive使用的是类SQL语句，降低学习和开发成本;
2、存储位置：数据库存储在块设备上或者本地文件，hive存储在HDFS 之上
3、数据格式：数据库有固定的数据格式，hive没有固定的数据格式，主要确定好列分隔符，行分隔符，及读取文件方式（textFILE，SequenceFile，RC或者ORC，Parquet）
4、执行：数据库有自己的计算引擎，hive的计算引擎是MapReduce
5、数据更新：数据库经常读写操作，hive经常读多写少，不支持改写和添加
6、延迟性：hive查询需要全扫描，所以延迟性高；数据库延迟性低，但是在查询大数据是，hive相比数据库优势很明显
7、可扩展性：数据库由于ACID语义（ACID语义描述：原子性（Atomicity）：一个操作或是全部完成，或是全部不完成，不会结束在中间某个环节。一致性（Consistency）：从操作开始至结束的期间，数据对象的完整性没有被破坏。隔离性（Isolation）：操作独立于其他并发操作完成。持久性（Durability）：操作处理结束后，对数据的修改将永久有效，即使出现系统故障，该修改也不会丢失。）的严格限制，扩展行非常有限。目前最先进的并行数据库 Oracle在理论上的扩展能力也只有 100台左右，hive建立在Hadoop之上，因此可扩展性一致，可扩展性高
8、数据量：数据库存储的量小，hive存储的数据量较大，一张表经常是TB以上
9.索引：hive是MapReduce的计算引擎，可以并行访问数据，不能添加索引，即使没有索引，但是在查询数据量很大的时候，hive的优势更为明显数据库通常为了提高查询效率会针对一个或者几个列建立索引。

三、Hive文件存储格式

TextFile： 默认格式，数据不做压缩，磁盘开销大，数据解析开销大，建表是不指定格式为这个格式，导入数据时会直接把文件拷贝到HDFS上不进行处理
SequenceFile ：是Hadoop API 提供的一种二进制文件支持，其具有使用方便、可分割可压缩的特点
RC和ORC： 行列存储结合，数据按行分块，要保证同一个record在一个块上，避免读一个记录需要度多个block,块数据列式存储，有利于数据压缩和快速的列存取。ORC可压数据按行分块 每块按照列存储 ，压缩快 快速列存取，效率比rcfile高,是rcfile的改良版本，相比RC能够更好的压缩，能够更快的查询，但还是不支持模式演进。
Parquet： 能够很好的压缩，有很好的查询性能，支持有限的模式演进。但是写速度通常比较慢。这中文件格式主要是用在Cloudera Impala上面的。

四、MapReduce的工作原理

整体流程大致分为5步
1、分片、格式化数据源
输入map阶段的数据源，必须经过分片化和格式化操作
分片操作：指的是将源文件划分为大小相等的小数据块（Hadoop 2.x 中默认 128MB），也就是分片（split），Hadoop会为每一个分片构建一个Map任务，并由该任务运行自定义的map()函数，从而处理分片里的每一条记录。
格式化操作：将划分好的分片（split）格式化为键值对<key,value>形式的数据，其中key代表偏移量，value代表每一行内容。
2、执行MapTask
每个Map任务都有一个内存缓冲区（缓冲区大小100MB），输入的分片（split）数据经过Map任务处理后的中间结果会写入内存缓冲区中。
如果写入的数据达到内存缓冲区的阈值（80MB），会启动一个线程将内存中的溢出数据写入磁盘，同时不影响Map中间结果继续写入缓冲区。
在溢写过程中，MapReduce框架会对key进行排序，如果中间结果比较大，会形成多个溢写文件，最后的缓冲区数据也会全部溢写入磁盘形成一个溢写文件，如果是多个溢写文件，则最后合并所有的溢写文件为一个溢写文件。
3、执行shuffle过程
MapReduce工作过程中，Map阶段处理的数据如何传递给Reduce阶段，这是MapReduce框架中关键的一个过程，这个过程叫shuffle。
shuffle会将Map Task输出的处理结果数据分发给Reduce Task,并在分发过程中，对数据按key进行分区和排序。
4、执行Reduce Task
输入Reduce Task的数据流是<Key,{value list}>形式，用户可以自定义reduce()方法进行逻辑处理，最终以<Key,value>的形式输出。
5、写入文件
MapReduce框架会自动把Reduce Task生成的<key,value>传入OutputFormat的write的方法，实现文件的写入操作。

五、Sql优化

1、避免全表扫描 （1）select查询的时候不要用* ，列出需要的字段即可；
（2）对where条件等号左边的查询字段避免使用表达式处理和函数操作，否则会导致不走索引，全表扫描
（3）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0
（4）应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
（5）where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20 可以这样查询：select id from t where num=10 union all select id from t where num=20
（6）in 和 not in 也要慎用，否则会导致全表扫描，select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了：select id from t where num between 1 and 3
（7）like “%abc%” 这种形式会索引会失效导致全表扫面，like "%abc"就不会
（8）在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
2、用union all 代替union，union有去重操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟
3、小表驱动大表
4、用exist代替in,
5、建立适当的索引
6、不用count(distinct)去重,select count(distinct name) from user;(效率较低)select count(1) from ( select name from user group by name)(效率更高)
7、join的数量尽量少
8、选择合适的字段类型和长度
9、提升group by 的效率
10、尽量使用inner join，避免left join

六、关于索引的知识，索引的优缺点

优点
创建唯一索引，可保证数据库里每行数据的唯一性；
可以提高检索速度；
帮助服务器避免排序和临时表；
将随机IO变成顺序IO ；
加快分组和排序；
缺点
索引本身是表，因此会占存储空间；
索引的创建和维护需要时间成本；
构建索引会降低表的修改操作的效率，在修改数据的同时还需要修改索引表；

七、Mysql分区方式

range分区：根据范围分区
list分区：根据值列表，将数据储存在不同的分区
hash分区：hash算法分区
key分区：hash算法分区（mysql自带）
columns分区
子分区：对分区在进行分区

八、Hive的分区

hive中支持两种类型的分区：
静态分区SP（static partition）
动态分区DP（dynamic partition）

九、hive数仓架构，分层模式

ods层 原始数据层，不做任何操作
dwd层 明细数据层，根据主题拆分的明细数据
dws层 维度轻度汇总的主题数据
ads层 多维度聚合的指标和标签
dim层 存放维度数据以及其他配置，转换类的数据

十、常见的数据模型

1、星型模型
星型模型主要是维表和事实表，以事实表为中心，所有维度直接关联在事实表上，呈星型分布。
2、雪花模型
在星型模型的基础上，维度表上有关联了其他维度表。这种模式维度成本高，性能也比较差，所以一般不建议使用。尤其是基于Hadoop体系构建数仓，减少join就是减少shuffle，性能差距会很大。
星型模型可以理解为，一个事实表关联多个维度表，雪花模型可以理解为一个事实表关联多个维度表，维度表再关联维度表。
3、星座模型
是对星型模型的延伸扩展，多张事实表共享维度表。星座模型是很多数仓的常态，因为很多数仓都是多个事实表的。所以星座模型只反映是否有多个事实表，他们之前是否共享一些维度表。
4、范式模型
第一范式：解决冗余数据组
第二范式：解决部分依赖现象
第三范式：解决传递性函数依赖现象

十一、数据治理

数据治理体系包括数据质量，元数据管理，主数据管理、数据资产管理，数据安全和数据标准。
1）数据质量好坏的衡量标准：完整性，准确性，一致性和及时性。
完整性：数据记录和信息是否完整，是否存在缺失。措施：按时间周期监控数据记录数和数据存储大小
准确性：数据汇总记录的信息和数据是否准确，是否存在异常或者错误 措施：同上
一致性：多个业务数仓间的公共数据，必须在各个数仓中保持一致 措施：对比各个表的汇总结果
及时性：数据能及时产出和预警 措施：告警
2）元数据就是描述数据的数据
元数据包括业务元数据和技术元数据。可以帮助数据分析人员清楚了解企业的数据，它们存储在哪里，如何抽取、清理、维护这类数据，即数据血缘。
帮助构建业务知识体系，确立数据业务含义可解释性；
提升数据整合和朔源能力，血缘关系可维护；
建立数据质量稽核体系，分类管理监控；
3）主数据管理
企业主数据指企业内一致并共享的业务主体数据，理解为各专业公司和业务系统间共享的数据。如公司的员工、客户数据，机构信息，供应商信息等，这些数据具有权威性和全局性，可归约至公司的企业资产。
主数据管理要遵循如下几点：
管理和监管各组织机构，子公司，部门对主数据的访问，制定访问规范和管理原则
定期进行数据评估，判断既定目标的完善制度；
组织相关人员和机构，统一完善主数据建设
提供技术和业务流程支持，全集团集中统筹。
4）数据资产管理
在构建企业资产时一般会考虑不同角度，业务角度和技术角度，组后合并，输出统一的数据资产，并向外提供统一的数据资产查询服务。
5）数据安全
数据安全是企业数据建设不可少的一环，需定时对数据进行 核查，敏感数据加密，访问权限控制，确保数据能被安全的使用。
6）数据标准
在组织内制定一套数据规范 ，保障内外部使用和交换一致性和准确性的规范约束，通过统一规范，消除二义性。

十二、数据倾斜

1、key-value值分布不均；
join连接，group by分组，distinct去重
2、机器配置不合理；
当机器配置与所需数据量不匹配的时候，总会出现部分需要处理的key值数量机器处理能力的情况，由此造成Reduce进程卡顿。
3、业务数据自身特性；
除了技术上的原因，业务本身的特性也会导致数据分布不均的情况，如不同品牌门店的数据量，订单量等。
4、小文件过多，笛卡尔积造成的数据膨胀。

十三、内部表和外部表的区别

外部表在建表的时候会有external的关键字的修饰，内部表不会。
hive内部表管理的是元数据和表里面的实际数据，在删除表时不仅会删除表，也会删除表里面的数据。
外部表只管理元数据，表的实际数据和元数据只是做了一个映射关系，可以借助元数据查询到表里的实际数据。当drop表时，只会删除表的元数据，表的实际数据仍然会存放在hdfs的路径中。

十四、hdfs文件加载到hive表的方法

1.当存储路径与表所在路径不一致时，可以load进去，如果表中字段类型不对应，该字段的值将会为空
load data inpath ‘hdfs://hadoop/tmp/123.txt’ overwrite into table order_123 partition(dt = ‘20230101’);

2.当存储路径与表所在路径一致时，可以采用添加分区的方式
alter table order_123 add partition (dt=‘20230101’) location ‘hdfs://warehouse/data/ods/release/dt=20230101’

十五、指标体系的建设

指标体系的建立是进行数据分析和数据挖掘的前提和基础，也是数据仓库模型设计的基础。
指标是由 业务线，维度，时间周期，修饰词，原子指标，度量六个方面组成。 指标体系包括指标名称，指标定义，指标编码，指标统计口径，指标sql逻辑等五个方面组成## 十七、SQL开窗函数

使用开窗函数可有效提升sql运行效率
开窗函数分为排序性开窗和聚合性开窗
函数的基本语法为：开窗函数()over(partition 列名 order by 列名 )
partition ：需要分区的列（可不使用）
order by ：对分区内排序

排序开窗函数

函数	意义
row_number() over(partition by col1 order by col2)	相同的值排名相同，序号从1到n连续
rank() over(partition by col1 order by col2)	若有并列的名称，会占用下一个名次，序号不连续，如两个排名3，则不存在排名4
dense_rank() over(partition by col1 order by col2)	若有并列的排名，不会占用下一名次，序号连续，如两个排名3，下一个排名继续是4
ntile(n) over(partition by col1 order by col2)	将每个分区内排序后的结果均分成N个桶，排序对应的数字为桶号。如果不能平均分配，则较小桶号的桶分配额外的行，并且各个桶中能放的数据条数最多相差1

聚合开窗函数
sql的所有的聚合函数都可用作开窗函数：max() 、min()、sum()…
相比开窗函数返回table一列的值，聚合开窗函数返回一个值。

十八、拉链表

记录一个事物从开始一直到当前状态的所有变化的信息。
我们可以使用这张表拿到最新的当天的数据以及之前的历史数据；
既能反应数据的历史状态，也能最大限度的节省存储空间。
实现方式：主要考虑实现过程中数据重跑以及数据仓库和大数据平台的差异。体现在记录的生效日期和失效日期。使用原始数据表和新数据表两表进行join，如果新表和原始数据表的不符，或在原始表中不存在，即为新增或变化，将这部分的数据追加到原始表中。

十九、hdfs文件加载进hive表的两种方式

1.当存储路径与表所在的路径不一致时，可以load进去，如果 表中字段类型不对应，该字段的值将会为空
load data inpath ‘hdfs://hadoop01:9000/tmp/sales_info.txt’ overwrite into table sales_info partition(dt = ‘20191215’);
2.当存储路径与表所在路径一致时，可以采用添加分区的方式
alter table ods_nshop.ods_01_sales add partition (dt=‘20201215’) location ‘hdfs://master:9000/data/ods/release/dt=20201215’

二十、mysql对于千万级的大表要如何优化

1.添加索引
2.分库分表
3.查询优化
4.设置缓存
5.大数据平台