使用decode函数来处理

Q：介绍一下最近的项目 / 最近项目的工作内容

回答思路：

让你介绍最近的项目不是让你说你们项目是做什么的，而是想知道你在项目中的工作内容或者你在工作中如何使用ET。L工具或者 可视。化工具的。

参考回答：

①

我最近的项目是给XX银。行做的一个关于xx业务的 驾驶。舱项目。这个项目是他们作为甲方跟我们公司合作开发的项目，我在这个项目做了差不多一年多一共做了10多个版本，主要负责 集市层 的开发工作，报表发开相关的工作 / 主要负责数仓层。

平时主要工作内容就是，每个版本根据产品经理给我们的需求进行需求分析，然后根据需求在集市层创建目标表，然后根据需求开发存储过程实现从数仓层的事实表和维度表中同步数据，如果是比较复杂的计算。开发完成以后我会对自己的代码进行自测，就是UT测试（单元测试），测试通过等到转测的时候，提交测试验证，然后再测试过程中修改测试发现的BUG，跟测试沟通发现的问题一直到缺陷单闭环。全部需求测试完成以后，我们一般在周末上线，之后就进入下个版本的开发。

②

项目：XX银。行驾。驶舱项目

我最近做的项目是XX银行的驾驶舱项目，大概做了一年半左右，是去到（根据自己的项目查询一个地址，比如：广州天河黄埔大道）那边的支行做的。我主要负责dw层的数据同步部分。

从整体上来看，我拿到版本需求后，先根据需求看一下指标有哪些，我主要负责的任务是什么，然后在产品经理组织需求交接会议的时候，认真去听，了解清楚指标的明细和取数逻辑。然后我会用思维导图把取数逻辑画出来，维护数据字典，然后再去建立事实表和维度表，写SP从ods层同步数据到目标表里，方便dm层的同事取数，日常晨会上跟老大汇报工作进度、遇到的问题、今天的计划等，保证能在规定的时间内完成开发。

开发完成后，先自己造些数据测试一下代码跑的逻辑，是否与需求一致，有问题就改SQL，没问题就上传到SVN上等代码评审；

评审后转测，关注自己名下的问题单，根据缺陷库里的缺陷描述清单分析和修复BUG，修复后再提交测试验证，一直循环，到问题闭环。

最后就是等待上线了。

举个例子，就我负责的是资产质量模块里的不良贷款率这个指标任务，贷款分为5类，分别是正常贷款、关注贷款、次级贷款、可疑贷款、损失贷款，后面三类就是不良贷款的组成，也是我要重点处理的数据。

根据需求，我要从ods层的整合库（ZHK）不同的表同步数据到我的目标表，

例如我要做FXK_FAT_XYKDKXX(分析库_事实表：信用卡不良贷款信息）；

我就要从ods的ZHK_QDT_（整合库_清单表_信用卡交易明细表），（整合库_清单表_信用卡账户信息拓展表）等表中同步数据，信用卡号设置主键约束，主要字段有信用卡号码、客户账号ID、贷款日期、贷款总金额、换款日期、已还本金、逾期金额、逾期时长等，然后根据逾期失常判断不良等级等。

DIM_（维度表前缀）

维度表主要有：

区域维度：广州各个区域的不良贷款率

客户维度：不同年龄段的客户

信用维度：不同信用评分的不良贷款率

指标：

商业银。行资产质量维.度：

五级贷。款分类：正常贷。款、关注贷。款、不良贷。款（次级贷。款、可疑贷。款、损失贷。款）

不良贷。款率= 不良贷。款余额 / 各项贷。款余额

=（次级贷款余额+可疑贷款余额+损失贷款余额） / 各项贷款余额

不良贷。款率、贷。款准备率与拨备覆盖率是商业银行资产质量的三个基本指标。其中，

不良贷。款率= 贷。款拨备率 / 拨备覆盖率。

贷.款拨备率（又称拨贷比）：贷款损失准备与各项贷款的比值

贷。款拨备率= 贷款减值准备 / 各项贷款余额

贷。款减值准备计提= 1%*各项贷。款余额+2%*关注类贷。款+25%*次级类贷。款+50%*可疑类贷。款+100%*损失类贷。款+特别准备

贷。款拨备率的监管标准为2.50%

拨备覆盖率= 贷。款减值准备 / 不良贷。款

拨备覆盖率的监管标准为150%

不良贷。款生成率= （本期新增不良贷。款余额+本期不良贷。款核销） / 总贷。款余额

Q：往一个大表中插入数据 怎么提高效率（性能）？

优先回答，往一张大表中插入数据，表中有索引会严重影响插入的性能，可以查完数据以后再创建索。引，

如果已经有索。引了，可以让索。引先失效。

（可以在表上直接更改索引的状态让其失效或者通过命令

drop index 索。引名； 或者

1、alter table xxxxx move tablespase xxxx 命令后，索引就会失效。

2、alter index index_name unusable ,命令使索引失效。）

Q：数据 口.径 是什么

这个是描述维度的粗细的一种表达方式。

跟粒度差不多，比如统计口径为：月

时间口。径/时间粒度 从大到小 年 季度 月 周 天 ， 时分秒

项目问题/BI理论

Q：什么是数仓

数。据仓库（data ware.house）是一个面向主题的（subject oriented）、集成的（integrated）、相对稳定的（nun-volatile）、反映历。史变化（time variant）的数据集合，用于支持管理决策（decision making support）

数。仓的特点：

• 面向主题的：经过ETL抽数、清洗、转换加载后，数据按不同主题存放在同一个库中，梳理归类；

• 集成的：不同来源的数据的集合

• 相对稳定的：不会人为改变任何数据，只同步。如果源系统出故障了，数据仓库的数据依然存在

• 反映历史变化：源系统数据库一般只保存几个月，定期删除，，数据仓库可保存几年后压缩在硬盘里

Q：什么是 映。射？

在ETL过程中 源表和目标表的对应关系就是一种映。射关系

在逻辑模型中表和表之间的关系 也叫做映。射

mapping = 映。射

Q：什么是代理主键 和业务主键

代理主键是纯数字为了唯一记录数据的变量情况，而业务主键是基于业务系统可能是数字也可能是字符主要是为了确保唯一的记录

比如订单编号对应不同的状态，那么 订单编号就是业务主键，无论订单状态如何变化，订单编号都是不变的。

在数仓中为了记录订单每次变化的情况，我们可以通过代理主键（可以是自从生产的一个序列的序号纯数字的）来记录每次状态的变化情况。

这一点跟业务系统的业务主键不一样，通（常）代理主键还作为事实表的外键，对事实表的字段分类（维度，度量）可以做到比较清晰。

Q：数据仓库分为几层，主要是干嘛的？

我们项目是传统数仓：主要分为三层，贴源层，数仓层，集市层。

三层的工作内容：

ODS层（贴源层/可操作数据层），主要是做数据的抽取，转换和同步，通过调度工具（CTM）按照每天/每月，定期从源系统中将数据抽取到ODS层。

DW层（数据仓库层），主要是从ODS层同步数据到数据仓库，根据业务的需要对事实表和维度表进行建模（星型或雪花模型）。

DM层（数据集市层），主要是为报表展示提供数据支撑，会从DW层同步数据到DM层，同时对各项指标提供数据或者通过函数计算得到报表需要的数据指标。

三层的解释：

ODS是贴源层，主要是做数据的收集汇总，通过ETL工具来实现，数据抽取，转换，同步。

ODS是数据仓库准备区，为DWD层提供基础原始数据，可减少对业务系统的影响。

建模原则：建模方式及原则：从业务系统增量抽取、保留时间由业务需求决定、可分表进行周期存储，数据不做清洗转换与业务系统数据模型保持一致、按主题逻辑划分。

DW层，是数据仓库，主要是从ODS数据库中，取出我们需要的数据按照事实和维度进行建模，比如星型模型和雪花模型。

建模方式及原则：数据模型与ODS层一致，不做清洗转换处理、为支持数据重跑可额外增加数据业务日期字段、可按年月日进行分表、用增量ODS层数据和前一天DW相关表进行merge处理。

DM层，数据集市，主要是做数据分析和报表数据展示。

根据DW层数据按照各种维度或多种维度组合把需要查询的一些事实字段进行汇总统计并作为单独的列进行存储，（宽表）满足一些特定查询、数据挖掘应用；

建模方式及原则：尽量减少数据访问时计算，优化检索；维度建模，星型模型；事实拉宽，度量预先计算；分表存储。

Q：有哪几种模型，他们有什么区别？

星型模型：所有的维度表都能直接跟事实表关联，存在冗余数据，一般来说性能会更好

雪花模型：一个或多个维度表没有直接跟事实表关联，需要通过其他维度表才能关联到事实表，去除了冗余数据，因为跟维度表要关联多次，所以效率不一定有星型模型好。

两种数据模型的优缺点：

星型模型因为数据的冗余所以很多统计查询不需要做外部的连接，因此一般情况下效率比雪花型模型要高。星型结构不用考虑很多正规化的因素，设计与实现都比较简单。

雪花型模型由于去除了冗余，有些统计就需要通过表的连接才能产生，所以效率不一定有星型模型高。正规化也是一种比较复杂的过程，相应的数据库结构设计、数据的ETL、以及后期的维护都要复杂一些。因此在冗余可以接受的前提下，实际运用中星型模型使用更多，也更有效率。

Q：项目流程/ 开发流程？

整体开发流程主要从需求阶段---> 设计阶段 ---> 开发阶段 ---> 测试阶段 ---> 上线阶段

• 需求阶段，产品经理给我们交接完需求，项目经理会根据需求给我们分配开发任务

• 设计阶段，我们会根据需求完成，表结构的设计，整理出数据字典

• 开发阶段，我们会根据自己负责的开发任务进行开发，每天项目经理会跟我们开晨会，过一下每个人的开发进度，开发完成以后，我们会自己做UT测试（单元测试），转测之前我们项目经理会组织我们进行代码评审，评审通过以后提交测试验证。

• 测试阶段，主要是改BUG，测试发现问题我们修复，然后跟踪缺陷闭环

• 上线阶段，参与上线支持，项目组的全体成员都参与，上线当天有负责生产环境部署的同事进行版本上线的工作，有问题我们就及时定位解决，没问题就在公司休息，等上线结束。

Q：OLTP 和 OLAP

OLTP：联机事务处理 。Online Transaction Processing

需要实时处理大量请求，而每次处请求的数据量都是很小的。OLTP是传统的关系型数据库的主要应用，主要是基本的日常的事务处理，例如银行交易。

性能好坏的重要指标：响应时间与请求处理并发数。

OLAP：联机分析处理。 Online Analytical Processing

可以简单地理解为在海量数据中得出统计/综合信息，是数据仓库的主要应用。做OLAP应用的数据库，数据量通常量非常大。和OLTP不同，OLAP应用的并发处理量是很低的，所以基本不用考虑并发问题，而在处理数据量方面，OLAP每次操作所需要处理的数据量通常都是非常大的，这点也和OLTP相反。

性能好坏的重要指标：查询大量数据的速度。

由于OLTP和OLAP是两个不同应用方向，所以在优化数据库时应采取不同的优化策略。

Q：维表和事实表都是什么？什么是指标？怎么来区分？

1、事实表就是你要关注的内容；

2、维度表就是你观察事务的角度？是从哪个角度去观察这个内容的。

比如商品的销量，是从地区这个角度观察商品销量的。事实表就是销量表，维度表就是地区表。

指标是需要去计算的，可以是销售量或者销售额的同比或者环比之类的，叫做指标。

Q：主题域是什么？

主题域通常是联系较为紧密的数据主题的集合。可以根据业务的关注点，将这些数据主体划分到不同的主题域。主题域的确定必须由最终用户和数据仓库的设计人员共同完成。

Q：元数据是什么

元数据的定义

数据仓库的元数据是关于数据仓库中数据的数据。它的作用类似于数据库管理系统的数据字典，保存了逻辑数据结构、文件、地址和索引等信息。

广义上讲，在数据仓库中，元数据描述了数据仓库内数据的结构和建立方法的数据。

元数据是数据仓库管理系统的重要组成部分，元数据管理器是企业级数据仓库中的关键组件，贯穿数据仓库构建的整个过程，直接影响着数据仓库的构建、使用和维护

Q：概念模型，逻辑模型，物理模型

概念模型：概念模型就是在了解了用户的需求，用户的业务领域工作情况以后，经过分析和总结，提炼出来的用以描写叙述用户业务需求的一些概念的东西。如销售业务中的“客户”和“订单”，还有就是“商品”，“业务员”。

逻辑模型（关系模型）：逻辑模型就是要将概念模型详细化。要实现概念模型所描写叙述的东西，需要哪些详细的功能和处理哪些详细的信息。这就到了需求分析的细化阶段。

还以销售业务为例：“客户”信息基本上要包含：单位名称，联系人，联系电话，地址等属性；“商品”信息基本上要包含：名称，类型，规格，单位价格等属性；“订单”信息基本上要包含：日期和时间属性。而且“订单”要与“客户”，“业务员”和“商品”明细关联。

系统需要建立几个数据表：业务员信息表，客户信息表，商品信息表，订单表。

系统要包含几个功能，业务员信息维护，客户信息维护，商品信息维护，建立销售订单。

以上这些均属于建立逻辑模型，这些说明仅仅表名系统要实现什么，但如何实现，用什么工具实现还没有讲，后者属于物理模型范围。

物理模型：编写详细的SQL脚本在数据库server上将数据库建立起来。当中包含业务员信息表，客户信息表，商品信息表，订单表。包含：业务员信息维护，客户信息维护，商品信息维护，建立销售订单等功能，并用工具将每个表创建出来。

总结：这三个过程，就是实现一个软件系统的三个关键的步骤，是一个从抽象到详细的一个不断细化完好的分析，设计和开发的过程，结合powerdesigner来根据需要分析、设计、到数据库表就是整个理解的过程。

Q：会计中的三大报表是什么？

会计的三大报表是指《资产负债表》，《利润表》，《现金流量表》。

资产负债表反映企业报表日财务状况，损益表反映企业会计期间的盈利情况，现金流量表反映企业会计期间的经营、投资、筹资现金流情况。

三张报表以一个三维立体式展现一家公司的财务状况，多角度对同一经济实体的资产质量和经营业绩作报告。

从三大报表的时间属性上看，损益表、现金流量表属于期间报表，反映的是某一段时期内企业的经营业绩，资产负债表是期末报表，反映的是报表制作时企业的资产状况。

从相互作用上看一个经营期间损益表、现金流量表改变资产负债表结构，但长期而言，资产质量对企业盈利能力起到决定性作用，这又是资产负债表决定损益表、现金流量表。

从某种意义上将，资产负债表是静态报表，而损益表、现金流量表是动态报表。表现为在一段时期内如何改变资产负债表，有点像资产负债表提供一个平台。

Q：oracle数据的备份和恢复

oracle中的备份分为两大类：逻辑备份和物理备份。其中物理备份又分为两类：冷备份和热备份。

逻辑备份是指利用exp命令进行备份。利用该命令进行备份，简单易行，也不影响正常的数据库操作。因此，经常被作为日常备份的手段。exp命令可以添加多个参数选项，以实现不同的导出策略。

1、冷备份/ 恢复 冷备份是指在数据库关闭的状态下，备份所有的数据库文件。这些文件包括：所有数据文件、所有控制文件、所有联机redo log文件和init.ora文件（可选）。

2、热备份/恢复 数据库的热备份是指对处于启动状态下的数据库进行备份。热备份一个数据库，首先要保证数据库运行于归档模式，然后备份表空间的数据文件，最后备份控制文件。

Q：项目上线的时候需要注意些什么

主要是几个方面：代码检查，整理上线脚本清单，及时定位和解决生产环境的BUG，事后做好总结

1、从代码管理的方面来说，我们上线之前会进行代码的检查，确保提交的代码是最新的，并且没有遗漏。

2、上线开始前，我们会提前到公司准备上线，跟生产环境的环境管理员做好沟通。

3、上线过程中，发现了报错或者其他的情况，是我负责的，我会及时定位并修复。如果不是我负责的问题，其他同事遇到的问题，我也会主动协助他们进行问题的定位。

4、最后无论上线过程中是否有自己负责的BUG，都应该总结经验，好的方法或者思路继续保持，有风险的做法要尽量避免。

Q：数据仓库是用什么方式来保存历史数据的？

类似的问题：

数据仓库是通过什么方式来反映历史变化的？

要如何设计一张表来反应历史变化？

回答滴露：

只要涉及到保存历史数据，拉链表，缓慢变化维这样的关键词，那么问的都是如何保留历史数据 或者如何通过拉链表来保存缓慢变化的历史数据。

回答：

1、数仓保留历史数据通常是在DW层 或者是HDW层（H-history）创建拉链表来保存历史数据的。

1、1 数仓中看数据的历史变化，是通过缓慢变化维的方式来记录数据的变化过程，每次更新数据都会通过update 进行逻辑删除，然后将最新的数据插入到拉链表中，并将历史数据更新成无效状态，将最新数据更新成有效状态。

1、2 首先看需求，要保留多少历史数据，如果是只保留最近的数据，那么在目标表中需要创建一个标记字段记录和区分最新的数据和最近的一批数据。

如果要保留所有的历史数据，那么我们会使用缓慢变化维的技术通过拉链表来实现。

缓慢变化维：

维度建模的数据仓库中，有一个概念叫slowly changing dimensions，中文一般翻译成“缓慢变化维”，经常被简写为SCD。缓慢变化维的提出是因为在现实世界中，维度的属性并不是静态的，它会随着时间的流逝发生缓慢的变化。这种随时间发生变化的维度我们一般称之为缓慢变化维，并且把处理维度表的历史变化信息的问题成为处理缓慢变化维的问题，有时也简称为处理SCD的问题。

处理缓慢变化维的方法通常分为三种方式：

第一种方式是直接覆盖原值。这样处理，最容易实现，但是没有保留历史数据，无法分析历史变化信息。第一种方式通常简称为“TYPE1”

第二种方式是添加维度行。这样处理，需要代理键的支持。实现方式是当天有维度属性发生变化时，生成一条新的维度记录，主键是新分配的代理键，通过自然键可以和原维度记录保持关联。第二种方式通常简称为“TYPR2”

第三种方式是添加属性列。这种处理的实现方式是对于需要分析历史信息的属性添加一列，来记录该属性变化前的值，而本属性字段使用TYPE1来直接覆盖。这种方式的优点是可以同时分析当前及前一次变化的属性值，缺点是只保留了最后一次变化信息。第三种方式通常简称为“TYPE3”

在实际建模中，我们可以联合使用三种方式，也可以对一个维度表中的不同属性使用不同的方式，这些，都需要根据实际情况来决定，但目的都是一样的，就是能够支持方便的分析历史变化情况。

项目细节问题：

1、你们项目组维度表多少张，数据量大概多少？

我负责部分的维度表有客户，产品，账户，币种，渠道和机构等，客户大概有200多万。

2、你在工作中遇到过哪些报错，什么原因导致的？

存储过程里遇到的异常：主键冲突；内存溢出；除数为零；程序视图打开一个已经打开的右表；系统等待某一个资源，时间超时

3、查两千万的数据得多久？

这个要看取数规则，限制条件少，关联表少的时候很快，几秒钟就查询出来了，条件复杂，关联条件多的话，最多跑半个小时

4、你们表最大的数据有多少条？

借记卡交易明细数据很大，差不多2个忆。

5、每天增量同步的表有哪些表，增量数据有哪些？

一般事实表都是增量的，借记卡开户信息表、贷记卡合同信息表、交易信息表、存款信息表等，每天同步新开户、新合同、新的交易流水、新存款等。

6、几十亿的数据你们怎么同步？每天更新的数据量有多大？

增量同步，对于交易明细每天有几百万的增量，这个比较大。

7、整个项目每个周期大概要出多少张报表。

十几张，包括新增的和修改的需求

8、取数部分是按什么取的，怎么保证抓的数据尽量少又不漏？

（时间戳是使用数字签名技术产生的数据，签名的对象包括了原始文件信息、签名参数、签名时间等信息）

时间戳来取的，我们是取最近7天的数据作为增量。

（华为的做法）我们是根据会计期进行取数并将数据同步到下游目标的，一个会计期的时间是1个月

9、大表的数据有多大，性能这块儿怎么样？报表一个月额大概会做多少张？

大表的话就是历史表了，上亿了，性能可以，因为我们会做多层汇总，不同粒度会放到不同汇总表里；一个月十几张，包括新增和修改的，因为是一个月一个版本。

10、项目数据的来源是什么？需求是怎么来的，拿到需求以后怎么做的？

数据来源于业务系统，包括个人贷款系统、公司贷款系统、票据系统、总账系统、基金理财系统等，需求是客户先提到需求分析师那里，然后需求分析师会对需求进行拆解和澄清，确认取数逻辑，然后我们会开会讨论如何实现，包括可行性分析，设计好模型表，然后我们进行开发的。

11、存储过程写的多吗，一个版本写多少个，一个有多少行，写过最复杂的存储过程是什么呢，存储过程中怎么记录日志，找出异常？

嗯，我主要就是写存储过程，一个版本大概要写2、3个，每个7、8百行，还有一些对历史存储过程修改的，最复杂的是统计卡交易明细，。因为粒度比较细，设计表很多，还要对交易类型和机构进行过滤等等，写了1000行左右。存储过程每个步骤都会记录日志表，也会记录异常）

12、说说项目中做过的指标有哪些？用到的表有哪些？

客户余额、日均余额统计表：用到了客户交易明细和客户信息、币种、机构、贷款合同等

如存款账户交易月汇总指标，即根据存款账户不同的交易类型（交易码）来统计交易笔数和交易金额：客户交易明细和客户信息、币种、机构。

13、设计模型的时候有没有发生变更的情况，为什么发生变更？划分主题的方法论是什么，怎么保证划分主题是正确的，怎么保证来了几个需求之后不会发生很大的改变？之前的DM层有哪些模型，具体的业务是什么？

主要分析数据仓库的数据如何满足集市的需要，即确定需要数据仓库的哪些表数据，如果数据仓库没有，则需要数据仓库进行数据补充。先分析数据应用系统的需求，再提炼数据集市的数据接口需求。

1、各机构的客户数，可以按支行、分行、总行进行主城统计、

2、根据维度组合（卡、产品、机构、客户、币种）来统计交易笔数和交易金额

3、客户日均余额

4、贷款通过率

5、等。。。

14、DM层如何保证数据质量，如何保证数据是正确的？

首先在开发阶段，我会仔细分析并理解需求，按照指标计算的公式完成进行开发。在这个阶段，如果我认为需求有问题或者我对需求有疑问会跟我们产品经理进行沟通好确认；

开发以后，先进行单元测试，单元测试的时候我会先按照计算公式进行手工计算，然后通过代码再算一遍，对比两次计算结果，如果计算结果一致说明没有问题；

之后我们还有一个代码评审的环节，可以进一步确认我们对需求的理解是否正确。

进入测试阶段以后我们团队的测试会进行充分的测试，对于他们发现的问题我会分析问题并解决，然后对发现的有效缺陷进行分析，看看是否在其他地方也有类似的问题，避免测试漏测也可以提高我的代码质量。

上线以后，对于我们团队的BUG，我会进行分析/ 我会带领团队的开发一起对上线后的问题进行分析，总结经验，避免以后再次发生类似的BUG。

15、怎么设计表的，怎么设计mapping的，表是谁设计的，DW层提供给报表那边的表设计关联吗？

这个主要是设计人员设计的，设计好目标表的字段以及来源于哪些表，DW表会向上关联汇总做成DM表然后提供给报表端。

代码技术问题

Q：分析函数有哪些？

常用的分析函数如下所列：

row_number() over(partition by … order by …)

rank() over(partition by … order by …)

dense_rank() over(partition by … order by …)

count() over(partition by … order by …)

max() over(partition by … order by …)

min() over(partition by … order by …)

sum() over(partition by … order by …)

avg() over(partition by … order by …)

first_value() over(partition by … order by …)

last_value() over(partition by … order by …)

lag() over(partition by … order by …)

lead() over(partition by … order by …)

Q：什么是内关联，外关联？

内连接：内连接也叫连接，是最早的一种连接。还可以被称为普通连接或者自然连接，内连接是从结果表中删除与其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。

左连接（左外连接）以左边的表作为主表，主表的记录全部显示，右边的表是从表，从表表的关联字段跟主表相同的就显示，不相同的则自动补null。

右连接（右外连接）跟左关联相反，以右边的表作为主表，左边的表作为从表，主表信息全部展示，从表只显示跟主表关联字段相同的数据，其余的自动补null。

全外连接（FULL JOIN 或 FULL OUTER JOIN）完整外部连接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。

Q：存储过程出现异常怎么处理？

在PLSQL中可以使用EXCEPTION，在EXCEPTION中把过程数据记录到错误日志表，然后对主过程的事

务做回滚，并且针对异常我们会记录异常的日志，方便后续的问题分析。

或

或

或

Q：Hadoop的存储原理是什么样的

HDFS（Hadoop distributed file system，分布式文件系统）

HDFS采用了主从（master/ slave）结构模型，一个HDFS集群是由一个namenode和若干个DataNode组成的。其中namenode作为主服务器，管理文件系统的命名空间和客户端对文件的访问操作；集群中的DataNode管理存储的数据。

namenode（管理节点）作为master服务，它负责管理文件系统的命名空间和客户端对文件的访问。namenode会保存文件系统的具体信息，包括文件信息、文件被分割成具体block块的信息、以及每一个block块归属的DataNode的信息。对于整个集群来说，HDFS通过namenode对用户提供了一个单一的命名空间。

DataNode（数据节点）作为slave服务，在集群中可以存在多个。通常每一个DataNode都对应于一个物理节点。DataNode负责管理节点上他们拥有的存储，它将存储划分为多个block块，管理block块信息，同时周期性的将其所有的block块信息发送给namenode。

Q：truncate在存储过程中能直接使用吗？

不能，要使用动态SQL，在存储过程中的begin 和 end中间写：

begin

execute immediate ‘truncate table’ 目标表;

end;

Q：你知道的行转列的函数或者方法

oracle 11G以后支持 行列互换pivot() 和 unpivot() 函数。

还可以使用一下方法进行行列转换：

1、用分析函数lead /lag；

2、用decode；

3、部分关联（查询表中的部分数据，然后通过关联的方式查出结果）；

4、集合运算union all 来实现行列转换；

Q：使用left join 时，on and 和 on where 条件的区别

1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where 条件是在临时表生成好后，再对临时表进行过滤的条件，这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉，on后的条件用来生成左右表关联的临时表，where后的条件对临时表中的记录进行过滤。

Q：A表是主表，80万数据，B表是扩展表，100万数据，这两张表相同主键是学生编号，根据这个场景，判断是否存在异常？什么原因早上数据重复。

回答思路：既然问了是否存在异常，那么多数情况肯定是有异常，接下来分析为何会出现这种数据量变多的情况（这种情况叫做数据发散）

参考思路：

面试官您刚才说了A表作为主表 数据量是80万，我的理解是这样的，通常外关联才有主表和从表之分，如果是外关联，那么A表作为主表的数据肯定是全部取的，但是关联以后的结果集比如B的数据量变成了100万，很明显数据量多了20万，这种明显是数据发散了。

同乘数据发散的原因可能是关联条件有问题，比如少写了某个关联字段导致查询出来的结果集变大了，所以我认为应该重新检查和分析一下A表和其他表的关联条件是否遗留了某个关联字段。

此外还有一种可能，当然这种可能性很小，也不能排除就是A表的数据是不是刚开始就有一部分重复的，比如原来是80万，但是因为没有按照3NF原则在表中创建主键，结果更新的时候插入了部分重复记录导致结果集发散，这种情况可以先对主表数据按照主键字段去重，然后在表中创建主键 或者 对关联字段创建唯一索引，通过唯一约束来避免此类问题。

Q：有一个job前一天执行成功了，第二天再执行失败了，请你分析失败的原因？

1、第一天成功了，第二天失败，失败的原因/ 结果没有说清楚，所以需要跟面试官确认。

2、如果面试官不解释，那么我们至少要分析2中可能，一种报错，另外一种是不报错，但是没有结果。

报错，查日志，分析错误原因

不报错，看调度任务是否执行，日志中如果有开始，但是没有完成时间，说明一直在等待，这时候需要查询一下是否锁表。

参考回答：

如果第一天成功，第二天失败，有2中可能，一种是报错，如果报错的话，我们需要先看日志，如果报主键冲突，说明更新的SQL逻辑有问题，我们可以看下代码，更改原来的同步逻辑即可。

第二种如果不报错，发现脚本一直处于执行状态，可能是锁表了，我会查询一下锁表的进程，然后杀死该进程，问题就解决了。

alter system kill session ‘sid,serial#’;

Q:去重的方式，表中id不同但是其他的数据都一样，如何实现？

找出我们判断是否重复的列（一列或者多列），然后按照这列分组，找出重复记录（count(*)>=2 ）然后删除重复记录。

如果说伪列rowid一定说一下我之前用的是oracle数据库，将之前也要跟他确认一下 你们用的是什么数据库。

找出重复的记录

select * from table_1 where t.要去重的列 in (select 要去重的列 from table_1 group by 要去重的列 having count(*) >=2 )

删除

delete from table_1 t where t.要去重的列 in (select 要去重的列 from table_1 group by 要去重的列 having count(*) >=2 );

伪列去重是通过物理地址来去重，先查出物理地址唯一的记录，然后重复记录就是rowid not in （唯一记录的结果集）

delete fron 表 where rowid not in （唯一记录的结果集）；

Q：存储过程的异常处理 怎么插入日志？

异常处理时通过exception 进行判断

语法：

exception

when others then

rollback; --1回滚当前事务

v_ErrorText := sqlcode || SQLERRM || ‘存储过程[Proc_Update_ID_NO] 执行失败！’ --2 捕获异常编码 和异常信息

RAISE_APPLICATION_ERROR (-20003,v_ErrorText) ;

将异常信息v_ErrorText记录异常日志。

在exception的异常判断后面then之后调用一下 记录异常日志的存储过程。

记录异常日志以后，我们可以插入日志表分析PLSQL执行错误的问题。

Q：一个大表里面有很多维度，每个维度的评分要大于60分？

比如一个表中有姓名，地址，性别等等，我们如何实现这些维度的分数大于60分。

两种方式：

1、关联事实和维度表，然后对这个结果集的维度列进行case when 的判断，如果不确定已经有 分数了，那么我们需要写条件判断。

2、通过视图查出所有积分都大于60的数据 然后用IN（视图中满足条件数据）

3、如果有分数了，我们通过分组聚合的方式过滤出维度的最低分数> 60 and 维度的数量 = 全部维度的数量。

Q：oracle 9i 和11G 12C的区别？

i 是Internet 的意思，表名当时是Internet互联网盛行的年代。

g是grid， 网格运算。为了迎合分布式计算而推出的版本。

c是cloud，云计算的意思。

还有每次数据库版本升级会新出一些新的函数。

Q：oracle如何分页？

可以通过伪列rownum

查出某几行的数据。

比如rownum rn，

rn <= 100 这个就是查前100行，相当于第一页

或者rn between 101 and 200 这个相当于第二页 101~200行。

Q：电商订单状态变化如何保存所有的状态和 最后成交的订单记录？

针对这种历史记录要保存统一都是拉链表，也叫做缓慢变化维。

在拉链表中最新记录有一个标记字段比如1 有效 0无效，他们用update 进行逻辑删除。

如果要删除所有无效的记录，那么可以delete 进行物理删除 加上条件where 标记字段=0；

Q：什么是映射？

在ETL过程中 源表和 目标表的对应关系就是一种映射关系。

在逻辑模型中表和表中间的关系也叫做映射。

mapping = 映射。

Q：动态视图是什么，有用过吗？

动态视图属于数据字典的一种，很像普通的数据库表，但不允许修改，大多数只能特权用户和dba用户查询，名字一般用v$开头。

锁表之后可以查动态视图看锁表的进程。

工具问题

ETL相关

Q：什么是ETL

ETL，是英文Extract-Transform-Load的缩写，用来描述将数据从来源端经过抽取（extract）、转换（transform）、加载（load）至目的端的过程。ETL一词较常用在数据仓库，但其对象并不限于数据仓库。

E_JOB >> T_JOB >> L_JOB ;

Q：kettle抽数一般遇到什么问题，你们是怎么解决的？

遇到过几个，比如：

Kettle在不同的数据库抽取数据时，有时会出现中文乱码问题

解决方法：如果数据库设置正确仍然存在中文乱码，则可能是因为有的客户端数据库默认的编码集不是UTF-8，我们只需要在输出时对输出的数据库进行编码配置

如果还不行，可以打开options，添加参数characterEncoding，设置值为gbk/ UTF8。

Q：datastage常用的控件有哪些

COPY STAGE（复制）

filter stage（过滤）

funnel stage（合并）

transformer stage 数据转换操作

sort stage （排序）

lookup stage（关联）

join stage（关联）

aggregator stage（聚合）

remove duplicates stage（去重）

Q：怎么用DS设计job？

打开datastage

1、建一个数据库mydb，建Schema schema_ytao ,建表AA，schema为schema_ytao，只有一列ID（int），插入数据1,2,3

2、在数据库sample和mydb中分别建表BB，schema分别为db2admin和schema_ytao

3、新建一个server job（猜测：因为server job，所以后面的db组件都不需要制定server ip或server name）

4、拖两个db2 api组件，一个用作输入dc in，一个用作输出 dc out

5、拖一个transfer组件

6、连接。点击link，左击dc in，靠中间一点，不要放掉鼠标，向右拉开到transfer组件，这样才能出来一条线，这个地方试了很久。同样的方法连接transfer和dc out。

Q：datastage都有哪些控件

transformer stage在datastage中是一个重要的，功能强大的组件，在ETL过程中，它承担“T”（transformer 即数据的转化）的责任。

transformer stage在datastage job 中可实现的功能

1、字段转换

2、字段逻辑运算及判断

3、数据复制

4、数据过滤

5、字段关联

Q：如果抽来的数据有重复的怎么解决？

在kettle的核心组件中，有一个字段选择，里面有一个取出重复记录（unique rows （hashset））的控件，在抽数的时候 可以通过这个控件对数据进行去重。

具体操作为：创建几个核心对象，分别是输入->表输入，将数据根据字段排序，数据去重，将去重好的数据输出到另一个表中，这里需要注意，数据去重前必须要经过根据相关字段排序；

Q：ETL脚本开发好以后，怎么运维？

我们会定期进行巡检，一般2周做一次检查，检查ETL脚本跑数是否正常，是否有报错，分析脚本跑数的时间是否越跑越慢，这种情况下需要分析脚本和目标表，比如目标表是否后来建了索引 导致更新表的时候越来越慢，索引会影响更新的效率。如果是这种情况 可以先删除目标表的索引，然后更新完以后再重新创建，这样可以提高跑数的效率。

Q：一张特别大的表，几千万的表，怎么通过ETL工具同步？

在ETL工具中我们插入数据的时候 可以设置批量提交 比如10万条记录提交一次，而不是一次性提交。另外如果特别慢的话 还要考虑是否锁表了，比如在对目标表进行更新的时候，转换的状态一直不变，可能是目标表产生了排它锁，导致无法更新，这个要具体分析一下。

Q：数据同步以后，源系统的表结构发生了改变，比如源系统的表增加了字段，你的kettle脚本是否会报错？

题目意思实际是：你的kettle脚本已经开发好了，这时候源表的列增加了，你的脚本是否会报错？

应该说不会，因为我们抽数的时候对源表和目标表的每个字段都做过映射，所以源系统的表增加了字段，我们的脚本是不会报错的。

但是如果这些新增的列也需要同步到数仓，那我们会对ODS层表的结构进行变更，然后修改kettle的脚本，然后重新跑数。

报表相关

Q：做报表开发，如何确保你拿到的数据是准确的？

我们都是根据需求来验证的，如果是计算的话，都有计算公式，我会自己先算一遍，然后通过调用函数对比计算结果。

如果是存储过程的话，我会根据需求中的取值逻辑和判断，验证一下取值是否正确。

Q：你们用的报表工具是收费的吗？多少钱？

这方面我不太清楚，都是公司统一采购的，我主要负责使用报表工具完成需求开发，价格方面我没有关注。

Q：做出来的报表是以什么文件或者形式发给客户，客户又是通过什么方式查看报表的呢？

首先要看是什么报表，每张报表有URL连接的，如果是帆软或者Cognos是有客户端的，以web形式展现的，客户有登录账号密码的，我们也有，但是权限不一样，我们相当于系统管理员，是可以操作报表系统的，我们把报表文件，帆软是.cpt 文件格式，上传到服务器，然后我们自己把文件放到对应的菜单目录下，自己运行下看是正常可以展示的，那么客户端那边也可以看到了。

Q：有一张表下午五点才出数据，依赖的表早上十点就已经跑完了，是什么原因导致表这么慢出数据？

类似的问题：从数仓同步数据本来是早上9点出结果的，结果一直到下午4点才出结果，怎么找问题的原因。

思路：

第一，跑数的时间大大超过预计的时间，极大可能是性能问题。

第二，除了性能问题之外可能是调度问题。

第三，定位问题不能靠主观想象，一定要检查日志，通过日志分析原因。

参考回答：

1、前置依赖的表上午10点就跑完了，但是目标表的JOB一直到下午5点才出结果。首先我会看一下日志。

先看调度日志，再次检查一遍是否是所有的前置job都跑完了，排除因某一个前置job没跑完，之前同事检查的时候没发现，导致目标表的job一直等待。

排除上面的可能以后，再分析是否是性能问题，检查监控日志，看下跑数的时间，如果确实是目标表更新很慢，再进一步分析慢的原因。

比如检查是否是在目标表上建了过多的索引导致更新变慢。

另外我们平时也要定期（每周或者每2周对之前的跑数job进行检查）检查job是不是越来越慢，可能是因为数据量过大导致的。

数据量变大导致的性能问题可以通过创建索引或者通过分区表的方式进行优化。

在分析问题的过程中我们要关注SQL的执行计划，必要的时候也可以使用优化器进行SQL优化。

1、1 回答思路同上，因为这个问题中面试官没有说前置job是否跑完了，所以我们必须去检查一下前置依赖的job是否都跑完了，什么时候跑完的，然后再进一步分析是否是更新目标表的性能很慢。

Q：帆软和tableau的区别？

两者各有千秋，帆软是OLTP联机事务处理产品，定位于企业数据采集和处理软件，满足企业个性化报表需求，同事具备强悍的列印和填报功能，但是数据分析能力较弱。tableau是OLAP联机分析处理产品，具有很强的数据分析能力，能够灵活的在前端进行数据分析操作，无需编写编程吗，劣势是无法满足企业复杂报表，列印等需求。

性能优化

Q:Oracle怎么进行性能优化

Oracle性能优化三板斧，索引优化，分区表优化，执行计划（hints优化器优化）；

索引优化：注意索引是否失效，这一般是比较烂的SQL语句引起的问题。

经常过滤的字段需要创建索引，但是一张表的索引数最多不要超过5个，否则可能影响性能。

Q：源表数据过亿了怎么建分区

对于数据量过大的表，可以考虑先对数据先分表，再通过分区表存储，以提高查询的性能；

分表：对亿级数据的表可以通过定时任务，使用存储过程执行动态SQL实现每年创建一张表，先对数据进行分表存储。比如：每年的数据存在一张表中，一年内的数据可以通过分区表进行存储。

分区：年表创建过后，查询就是查询年表中的数据，可是虽然分表了，但是年表中的数据量仍然很大，查询速度虽然有提升，但并不能满足用户的要求。便考虑到分表再分区，即将历史数据以不同的年表来存储区，在年表中按月分区。

总的来说，分区的主要目的还是避免了全表扫描，从而提升查询速度。

特别要注意的是索引会降低insert的效率。如果要更新首先避免全量插入，其次 插入数据之前最好现在plsql工具中将目标表的索引置为失效状态，更新完成以后再让索引生效。

对于上亿的表肯定要创建分区，可以在分区中使用局部索引来优化查询某个分区时的性能。

Q：oracle的关联查询执行原理？

多表连接的三种方式：

HASH JOIN：散列连接、优化器使用两个表中较小的表（通常是小一点的那个表或数据源）利用连接键（join key）在内存中建立散列表，将列数据存储到hash列表中，然后扫描较大的表，同样对join key 进行hash 后探测散列表，找出与散列表匹配的行。

SORT MERGE JOIN：排序合并连接、是先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据。到另一个排序表中做匹配。

NESTED LOOP：嵌套循环连接，nested loops 工作方式是循环从一张表中读取数据（驱动表outer table），然后访问另一张表（被查找表inner table，通常有索引）驱动表中的每一行与inner 表中的相应记录join。类似一个嵌套的循环。

Q:有两张很的表关联，怎么做才能关联性能好

select 子句中避免使用*

使用decode函数来减少处理时间

用truncate替代delete

尽量多使用commit

用where子句替换having子句

使用表的别名

用exists替代in，用not exists替代not in

用索引提高效率，避免在索引列上使用not，通常，避免在索引列上做计算。

用>= 替代 >

用union 替换or（适用于索引列）

用in来替换or

避免在索引列上使用is null 和is not null

用where 替代order by

优化group by ，将不需要的记录在group by之前过滤掉

Q:什么是执行计划？怎么用？

执行计划是一条查询语句在oracle 中的执行过程或访问路径的描述。

在SQL窗口执行完一条select语句后按F5 即可查看刚刚执行的这条查询语句的执行计划。

执行计划的常用列字段解释：

基数（rows）：oracle估计的当前操作的返回结果集行数

字节（bytes）：执行该步骤后返回的字节数

耗费（cost）、CPU耗费：oracle估计的该步骤的执行成本，用于说明SQL执行的代价，理论上越小越好（该值可能与实际有出入）

时间（time）：oracle估计的当前操作所需的时间。

执行顺序：

根据operation缩进来判断，缩进最多的最先执行；（缩进相同时，最上面的最先执行）

同一级如果某个动作没有子ID就最先执行

同一级的动作执行时遵循最上最右先执行的原则。

SQL执行顺序即为：

index range scan -> table access by global index rowid -> index unique scan ->

table access by index rowid -> nested loops outer -> sort group by ->

select statement, goal =all rows.

表访问的几种方式（非全部）

table access full （全表扫描）：查询出该表所有数据，获取的数据执行where 语句。

table access by rowid（通过rowid的表存取）：通过rowid 获取表数据

table access by index scan（索引扫描）

索引扫描又分五种：

index unique scan（索引唯一扫描）：查询条件中包含主键，因为主键是有唯一性约束的特殊索引。

index range scan（索引范围扫描）

index full scan（索引全扫描）：进行全索引扫描时，查询出的数据都必须从索引中可以直接得到，且查询条件不包含索引前导列。

index fast full scan（索引快速扫描）扫描索引中的所有的数据块，与index full scan 类似，但是一个显著的区别是它不对查询出的数据进行排序（即数据不是以排序顺序被返回）。

index skip scan（索引跳跃扫描）

Q：三种关联机制（执行计划中的关联机制）的优先级？

如果是说哪种最好，回答没有最好的，根据不同的情况我们可以指定走某种关联机制

基本顺序排列组合优于嵌套 优于哈希，但是我们在优化的时候会先查询执行计划。

我们看执行计划主要看什么？和看执行计划的目的？：

1 表的关联机制

2 看扫描方式（全表 或者 索引）

3 消耗（跟数据量 还有1,2）

4 查看执行计划的目的是为了我们后续去更改执行计划。（在select 或insert语句后面加 hints ,/*+ 命令 */）;

Q:性能优化，如果有10张表，关联以后 会有性能的问题，问你怎么优化？

可以通过中间表进行优化。

比如A1~A10 ，直接关联有性能问题，我们可以分别将 A1~A5 关联成一张A1_5的中间表 ，A6~A10 生成第二个 A6_10 的中间表

然后再把2个中间表进行关联。

跟视图不一样，视图不存储数据，而中间表会存储数据。

Q：一个PLSQL脚本很慢如何分析到底哪里慢？

通过日志记录每步的耗时，分析到底哪一段最慢。

然后找到最慢的SQL部分 看到底是查询还是 更新慢。

比如查询慢，看select 后面是否调用了其他的自定义函数，注释函数 再查性能是否提升，如果调了自定义函数之后变慢，则需要优化自定函数的性能。

如果仍然很慢，这时候要分析数据量的变化 和执行计划。

主要看数据量的变化情况，是否突然数据增量比较快，导致数据量暴增；还可以通过日志看，脚本执行的效率是否是越来越慢，又分为数据量变的越来越大 或者有人改过代码。

1、数量多了 之前的同步方式 可能需要修改。

2、如果有人为了优化性能 建了更多的索引导致 插入数据越来越慢 这时候 需要平衡 查询 和更新的性能。有可能需要 增加服务器配置。

Q：索引失效有哪些？怎么看执行计划，执行计划里有什么？

索引失效的原因：

索引列进行运算；

针对索引使用函数；

对索引列使用隐式转换；

用！=和null判断：like‘%_’百分号在前；

看执行计划：执行一条SQL语句之后，按F5可以查询该SQL语句的执行计划。

执行计划主要关注：

基数（当前操作返回结果集的行数）。

字节（执行该步骤后返回的字节数）。

CPU耗费（执行该步骤的执行成本）。

时间（当前操作所需的时间）。

Q：用过哪些调度工具：control_m, airflow

Q : 索引种类：1、唯一索引：当某列任意两行的值都不相同（主键一样，非空且唯一）

建立主键则自动建立唯一索引。

2、组合索引：当2个或多个列经常一起出现在where中。

3、位图索引

4、基于函数的索引。

Q：如何看hints是否被调用？hints原理

查看执行计划有个PX字段代表；

在绝大多数情况下执行计划会选择正确的优化器，减轻了DBA的负担。但有时它也聪明反被聪明误，选择了很差的执行计划，使某个语句的执行变得奇慢无比。此时就需要DBA进行人为的干预，告诉优化器使用我们指定的存取路径或连接类型生成执行计划，从而使语句高效的运行。

Q：有两张很大的表关联，怎么做才能关联性能好？

1、首先要建立适当的索引。SQL在索引字段不要加函数，保证索引起效。如果是复合索引注意在SQL的顺序。如果已经存在索引，建议你先重建索引，因为大数据表的索引维护到了一个阶段就是乱的，一般建议重建。建立好的一般可以获得几十倍的速度提升。

2、最大数据量的表放在最前，最小的表放在最后面。SQL是从最后面开始反向解析的。

3、其次是要把最有效缩小范围的条件放在SQL末尾去。尤其是主键或者索引字段的条件。

4、为两个表设计合理的表分区，然后分别对应关联两个表的分区数据，再用union all 把各个连接结果叠加起来。

Q：大表和小表连接如何进行优化？

将小表作为驱动表，大表作为匹配表。关联字段建索引

Q：oracle内部执行机制？

1、语法分析，分析语句的语法是否符合规范，衡量语句中各表达式的意义。

2、语义分析，检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。

3、视图转换，将涉及视图的查询语句转换为相应的对基表查询语句。

4、表达式转换，将复杂的SQL表达式转换为较简单的等效连接表达式

5、选择优化器，不同的优化器一般产生不同的“执行计划”

6、选择连接方式，oracle有三种连接方式，对多表连接oracle可选择适当的连接方式。

7、选择连接顺序，对多表连接oracle选择哪一对表先连接，选择这两表中哪个表作为源数据表。

8、选择数据的搜索路径，根据以上条件选择合适的数据搜索路径，如果选用全表搜索还是利用索引或是其他的方式。

9、运行“执行计划”

~~~~大数据相关

Q：linux命令你知道哪些？

我在之前项目上有到的命令主要是切换目录cd，新建目录mkdir，查看文件vi这些，因为我在项目上主要做的是开发，内容主要是把开发好的脚本放到目录下，工作就完成了，对于一些其他的命令，因为不是做系统维护的，所以接触的比较少，现在也在了解一下。

Q：Hadoop是什么？

我说下我的理解

首先Hadoop就是一个大的存储，未来会代替传统数据库存储了，它的优势就是HDFS，分布式文件存储，分布式就是把数据存储在多台机器上，也就是集群，文件存储都是以文件的方式存储的，这样可以解决数据量大，数据种类复杂的存储了，

因为都存成文件了，然后要想统计计算Hadoop里面的数据要用到MapReduce，是个Java框架，然后我觉得挺难的，

后来就了解到有hive这个组件，hive是基于Hadoop的一个数据仓库工具，可以将hdfs上面结构化的数据文件映射为一张数据库表，并提供类sql查询功能，然后这个sql我也了解了一下，传统数据库上能使用的，基本在hive里都能使用，

后面会有一些差异，最近也在看这块，比如如何创建表啊，要用到分隔符啊之类的，我觉得后面就是在数仓里面把传统数据库换成hive这种，把存储方式换一下，但是开发流程好像一样，也要在hive里进行分层，然后建设数据仓库这样，进行数据的统计分析，开发的时候要配合shell脚本使用。

Q：Hadoop hive 数仓的基本原理？

首先hive利用hdfs存储数据，也就是数据是存在Hadoop上的，书写的SQL语句最终会转化成MapReduce程序执行。hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供类sql查询功能。

hive运行原理：

hive利用hdfs存储数据，利用MapReduce查询数据，书写的SQL语句会转化成MapReduce程序执行。运行的时候会产生一系列的job，然后运行job，多个机器并行运行。

Q：hive和数据库有什么区别？

hive和数据库除了拥有类似的查询语言，也就是SQL，再无类似之处。最重要的就是数据存储位置，

hive存储在hdfs。数据库将数据保存在块设备或者本地文件系统中。

然后是对于数据的更新，hive中不建议对数据的改写。而数据库中的数据通常是需要经常进行修改的，

hive执行延迟较高，也就是查询会很慢。数据库的执行延迟较低。当然，这个是有条件的，即数据规模较小，当数据规模大到超过数据库的处理能力的时候，hive的并行计算显然能体现出优势。hive支持很大规模的数据计算；数据库可以支持的数据规模较小，这个只是相对来说的。

Q：大数据环境搭建是谁负责的？

对于Hadoop系统的搭建，是由我们的平台工程师负责搭建，还有一些参数的调整，

还有对系统发生的一些错误的处理，也是由技术组长负责解决。

我在项目主要负责利用hql编写数据同步的过程，封装成shell脚本，然后提交脚本，完成我的工作。

Q：一些其他大数据组件你还知道哪些？

sqoop，spark，flink等扩展，如果了解就可以简单说下认识，如果不了解，就不要继续探讨

Q：内部表和外部表的区别？

hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。

在删除表的时候，内部表的元数据（表名、表结构）和数据（也就是那个目录）会被一起剔除，而外部表只删除元数据，不删除数据（目录）。

Q：大数据里面表是怎么分区分桶的？分区和分桶是怎么理解的？

1）分区

庞大的数据集可能需要耗费大量的时间去处理。在许多场景下，可以通过分区的方法减少每一次扫描总数据量，这种做法可以显著地改善性能。数据会依照单个或多个列进行分区，通常按照时间、地域或者是商业维度进行分区。为了达到性能表现的一致性，对不同列的划分应该让数据尽可能均匀分布。最好的情况下每分区的划分条件总是能够对应where语句的部分查询条件。

hive的分区使用hdfs的子目录功能实现。每一个子目录包含了分区对应的列名和每一列的值。但是由于hdfs并不支持大量的子目录，这也给分区的使用带来了限制。我们有必要对表中的分区数量进行预估，从而避免因为分区数量过大带来一系列问题。

hive查询通常使用分区的列作为查询条件。这样的做法可以指定MapReduce任务在hdfs中指定的子目录下完成扫描的工作。hdfs的文件目录结构可以像索引一样高效利用。

分区：分区就是分文件夹，建表的时候指定分区字段，partitioned by 字段，插入数据时指定分区字段和值，也可以启用动态分区参数后，使用动态分区，使用源表的字段的值作为分区。一般使用日期作为分区，结果是在hdfs上 一个分区一个文件夹。

2）分桶

在分区数量过于庞大以至于可能导致文件系统崩溃时，我们就需要使用分桶来解决问题了。

分区中的数据可以被进一步拆分成桶，不同于分区对列直接进行拆分，桶往往使用列的哈希值对数据打散。并分发到各个不同的桶中从而完成数据的分桶过程。

注意，hive使用对分桶所用的值进行hash，并用hash结果除以桶的个数做取余运算的方式来分桶，保证了每个桶中都有数据，但每个桶中的数据条数不一定相等。

哈希函数的选择依赖于桶操作所针对的列的数据类型。除了数据采样，桶操作也可以用来实现高效的map端连接操作。

在数据量足够大的情况下，分桶比分区，有更高的查询效率。

与分区的区别。分区表针对的是目录，也就是存储路径，分桶表，则针对的是文件，粒度更细。

分区表是对应hdfs上的目录，桶表对应是目录里的文件。

分桶：分桶就是分文件，在建表时指定分桶的字段和数量，语法如下

clustered by（‘字段1’，‘字段2’） into 字段数量（100） buckets

插入数据时，hive对上述的字段进行hash运算之后，对分桶的数量取余数，决定数据保存到哪个文件。使用时需要设定reduce数量和桶数量一致。

如下

1.让hive强制分桶，自动按照分桶表的bucket进行分桶（推荐）

set hive.enforce.bucketing = true;

2.手动指定reduce数量

set mapreduce.job.reduces = num;

Q：分桶的作用是什么？

进行表格连接的查询加速；加快表格的抽样查询。

Q：分桶的数量你是如何决定的？

总的文件大小/ (block size *2 ) = 分桶数量

Q：hive SQL 中4 个by 的区别？

1、sort by ：分区内排序；

2、order by：全局排序，只有一个reducer，单机处理，效率最低，一般不会使用；

3、distribute by 类似mr中partition，进行分区，结合sort by使用。

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

Q：如何查询文件的大小

Hadoop fs -du -h 文件路径下的每个文件的大小

Hadoop fs -du -h -s 文件路径的总文件大小

Q：什么是实时，什么是离线数据

hive Hadoop 是离线数据 简称 批，当天分析前一天的数据，即 T+1

实时数据是分析实时产生的数据，简称流，一般是间隔5分钟，即分析的是5分钟之前产生的数据。实时数据一般是使用 flink，spark-streaming 处理

Q：有没有海量数据处理的经验

项目中使用hive处理 千万/亿级别的数据，数据使用hivesql 开发脚本和SQL 优化

Q：发生数据倾斜怎么解决的？

核心思想：

1）减少数据量

利用分区或者分桶，不过分桶用的很少，影响数据同步的效率，还有就是列比较多的表，做个裁剪，把列变少

2）避免数据倾斜

简单的讲，数据倾斜就是我们在计算数据的时候，数据的分散度不够，由于数据分布不均匀，造成数据大量的集中到一点（一个reduce），造成数据热点，查询卡住啦。

比如hivesql里的distinct，group by ，join就会出现这个现象。

1）、一般我会用group by 替换distinct，

2）、group by的时候把数据量大的分组单独计算再用union all 合并到一起

3）、开启mapjoin

select /* + mapjoin(e) */ from mydb.emp e ;

3）使用一些语句的优化

比如使用with as 临时表啊，group by 替换 distinct啊等等

4）参数配置优化

对于group by产生倾斜的问题。

1） set hive.map.aggr = true;

开启map端的聚合，不需要reduce过程

2）set hive.groupby.skewindata = true;

有数据倾斜的时候开启负载均衡，当选项设置为true的时候，生成的查询计划会有 两个MR job，第一个MR job中，map 的输出结果会随机分布到reduce，每个reduce

做部分聚合操作，并输出结果，第二个MRjob再根据第一个map job 预处理的数据结 果按照group by key 分布到reduce中（这个过程可以保证相同的group by key被分布到 同一个reduce中），最后完成最终的聚合操作。

set mapred.reduce.tasks = 200;

3）自动开启mapjoin

hvie.auto.convert.join: 是否自动转换为mapjoin

hive.mapjoin.smalltable.filesize: 小表的最大文件大小，默认为2500 0000，即25M，可 以设置100M，小表小于100M的数据都自动启动mapjoin

4）在同一个SQL中不同的job是否可以同时运行。（默认为false）

set hive.exec.parallel = true;

5）增加同一个SQL允许并运行的最大线程数；

set hive.exec.parallel.thread.number=8;

6）设置reducer内存大小

set mapreduce.reduce.memory.mb = 4096;

set mapreduce.reduce.java.opts = -Xmx3584m;

5）使用高效的查询引擎

我们后期使用了spark-sql，提高执行效率。

6）存储格式（压缩）优化

我们还会使用ORC格式，这个对查询效率也有提升

7）根据业务逻辑对业务实现的整体进行优化

还有就是我们设置合理的分层，让数据逐步处理

Q：在hive里，如果每天有新数据进来的话，怎么去做增量更新？

事实表一般每天的数据一个分区，增量更新的根据项目需求有两种方法

1、使用新数据更新旧数据，即把需要更新数据查询出来然后和不需要更新的数据union all到一起，利用overwrite覆盖方式全表更新。

2、使用新数据代替旧数据，如果有主键，把新旧数据union all到一起使用row_number分析函数，对主键进行分区，根据生成时间或者版本等其他字段倒序排列，取row_number等于1，利用overwrite覆盖方式全表更新。

Q：你在hive里面创建分区表，那你们也是按天吗？按每天去存一份数据吗

是的，一般是按照天的数据，每天存一份，主要针对日志类型的文件数据

Q：Hadoop的读写流程

写流程：

1、客户端向namenode发起请求，需要存储数据data

2、因为namenode中是记录了所有DataNode的相关信息的，而数据最终要保存的地方就是DataNode，所以namenode会返回可用的DataNode的信息给客户端

3、将data分为1和2这两个数据块

4、客户端会将数据块存储到namenode返回给他的DataNode1中去

5、因为数据块需要存储多份，所以DataNode之间会相互传输来进行存储

6、DataNode存储完数据后，会反馈给namenode，NameNode会将对应的DataNode的相关信息进行更新

读数据

1、客户端向NameNode发起请求，需要获取名字为1的数据块

2、NameNode中保存了该数据存储的位置，将DateNode的信息返回给客户端

3、客户端就近的方式从DataNode获取数据

4、如果某个DataNode无法访问

5、从另一个DataNode中去获取数据

Q：hive运行数据的方式hive -e/-f/-hiveconf 分别是什么意思

-e：不进入hive的交互窗口执行SQL语句，hive语句是“” 双引号包围SQL语句，可以多行使用分割。

-f：执行脚本中SQL语句，把所有SQL语句写在一个SQL结尾的文件中执行。

-hiveconf：hive用户配置属性，如hive运行的参数。

Q：hive内部表和外部表的区别

1）内部表数据由hive自身管理，外部表数据由hdfs管理

2）内部表数据存储的位置是/user/hive/warehouse,外部表数据的存储位置由自己指定

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

4）对内部表的修改会将修改直接同步给元数据，而对外部表的表结构和分区进行修改，则需要修复

Q：传统数据库/ 关系型数据库/oracle/mysql和hive的区别

1、oracle使用不同用户来区分表所属，可以运行多个实例，hive的表建立在数据库下，默认default

2、数据类型不一致，如oracle使用number表示数字，hive分别int和double来表示整数和小数

3、hive建表，增加参数 存储格式stored as ，location保存的位置，分隔符

4、select语句当中，函数是不一样的。

日期函数获取年份oracle：to_char（日期，yyyy），hive：year（日期）

当前日期：oracle：sysdate，hive：current_timestamp

分割函数，oracle没有分割函数，hive有splist分割函数

oracle有伪列rowid，rownum hive没有伪列

自定义函数：oracle使用SQL开发自定义函数，hive：使用JAVA来开发。

5、存储过程：oracle有，hive没有

6、hive不支持update和delete语法，hive支持overwrite覆盖 = delete from + insert

7、oracle的数据保存在本地的电脑，hive数据保存在Hadoop分布式文件存储系统上

8、oracle建索引加快查询，hive只支持分区，不支持索引

9、hive有外部表和内部表，外部表删表，不删除数据，oracle全删。

10、hive的计算依赖于Hadoop，本身没有计算功能。oracle计算是在本机的数据库内存中计算。

11、hive处理数据规模只支持TB，PB级别，oracle支持数据量有限。

12、oracle只支持结构化的数据（行和列的数据），hive支持非结构化数据（json格式）

13、oracle只支持事物（commit，rollback），hive不支持

14、hive不支持 不等值关联

例如：oracle的关联条件：on e.sal between s.l and s.h

hive不支持但是可以变通的方式：

hive： on 1=1 where e.sal between s.l and s.h

hive 不支持子查询作为字段和过滤条件

Q：你们使用的Hadoop是什么环境什么版本的？

Hadoop开源版2.8

Hadoop cdh版本 5

Q：hive里面经常用到的函数有哪些？

聚合：sum，avg，min，max，count

单行：数字：abs，round，floor，ceil，power

字符串：substr，concat，concat_ws，replace，instr，split，translate，upper，lower，lpad，rpad，length，trim，ltrim，rtrim，get_json_object

时间日期：date，current_timestamp，unix_timestamp，from_unixtime，add_months，date_add，datediff，last_day

数据类型转换：cast

数组：array，size，collect_list，collect_set，explode

映射：map，map_keys，map_values

分析：

排名：row_number，rank，dense_rank

平移：lag，lead

逻辑：nvl，case...when

Q：表格的存储格式有哪些？

textfile

sequencefile

rcfile

orc

Q：项目这种经常用的结构有哪些？

如果表格比较小会使用textfile，中大型的表格使用orc

Q：表格的压缩格式你们用的什么

textfile gzip

ORC zlib

sequencefile block record

~~~~linux

Q：如果在当前目录下创建一个文件用什么命令？创建一个目录用什么命令？对文本修改权限？

创建一个空文件夹有3种方法。

1、使用touch文件名。

vi文件名，进入vi后按 :wq 退出。

2、echo > 文件名。

创建一个目录用mkdir命令， -p 表示递归（可选），格式是mkdir文件路径。

3、对文件修改用chmod命令，-R表示递归 格式是chmod数字（755）文件名，

7=4+2+1 表示可读，可写，可执行，5=4+1表示可读，可执行，还有其他组合，三位数第一位表示文件所有者，第二位表示组员，第三位表示其他人。

Q：如果文本里面有100行数据，其中含有关键词abc，需要把含有关键字的行找出来，有什么命令

grep abc 文件名

Q：linux脚本实现了什么功能？用了什么命令实现什么功能。

在大数据开发时使用shell脚本实现了hivesql脚本的封装和执行，包括打印日志，异常处理，和日期参数化，具体实现的命令和功能如下，

使用了hive-e 实现执行脚本

使用echo实现打印日志

使用$?实现采集上一个命令hive -e的执行结果，0表示成功，1表示失败

使用if语句来判定上一步的返回值成功还是失败

使用>>重定向把 打印的日志 写入到文件当中。

使用了$1 实现参数解析，通过阐述获取程序的执行周期

Q：简单介绍，while循环，for循环的使用场景

while循环运行前判断，如果条件成立，循环运行

while循环实例，读取文件并打印

#读取文件 while read line do echo $line #文件处理 打印文件中的每一行 done < 文件名

for 循环 设定某个数值初试条件，每次循环改变这个值，每次循环判定这个数值满足条件就运行。

for 循环实例 显示指定的文件目录的文件名

#显示目录下的文件 for file_name in $(ls 文件路径) do echo $file_name done

一般来说for的循环次数是确定的，while是不确定的，如读取文件读完才知道有多少行

Q：怎么替换一个TXT文本中的，为；

sed -i’1,$s/,/;/g’ xx.tzt

Q：怎么创建一个新的sh脚本，怎么使用

创建sh脚本使用touch或者是vim命令。

使用sh bash或者是路径加上文件名来使用。

Q：如何把10万行的数据按照每1000行切割成100个小文件。

后缀为字母：split -I 1000 -a 2 1.txt a_

或

后缀为数字：split -I 1000 -a 3 -d 1.txt a_

~~~~大数据 hive优化

Q：hive优化的思路/方案：

1、避免数据倾斜

2、合理使用分区和分桶，把数据分开存储

3、使用一些语句的优化

4、参数配置优化--主要是平台工程师或者数据组长负责配置

重点：数据倾斜，影响最大，Hadoop不怕数据量大，就怕数据倾斜

Q：hive知识点之数据倾斜

1、什么是数据倾斜？

由于数据分布不均匀，造成数据大量的集中到一点，造成数据热点

2、主要表现

任务进度长时间维持在99%或者100%的附近，查看任务监控页面，发现只有少量reduce子任务未完成，因为其处理的数据量和其他的reduce差异过大。单一reduce处理的记录数和平均记录数相差太大，通常达到好几倍之多，最长时间远大于平均时长。

3、容易数据倾斜情况

关键词	情形	后果
join	其中一个表较小，但是key集中	分发到某一个或几个reduce上的数据远高平均值
	大表与大表，但是分桶的判断字段0值或，空值过多	这些空值都由一个reduce处理，非常慢
group by	维度过小，某值的数量过多	处理某值的reduce非常耗时
count distinct	某特殊值过多	处理此特殊值的reduce耗时

A：group by不和聚集函数搭配使用的时候

B：count（distinct），在数据量大的情况下，容易数据倾斜，因为count（distinct）是按group by字段分组，按distinct字段排序

C：小表关联超大表

4、产生数据倾斜的原因

A：key分布不均匀

B：业务数据本身的特性

C：建表考虑不周全

D：某些HQL语句本身就存在数据倾斜

Q：举一个例子，你是如何解决数据倾斜的？

（1）空值产生的数据倾斜

场景说明：

在日志里，常会有信息丢失的问题，比如日志中的user_id，如果取其中的user_id和用户表的user_id相关联，就会碰到数据倾斜的问题。

解决方案1：user_id为空的不参与关联

select * from log a

join user b

on a.user_id is not null and a.user_id = b.user_id

union all

select * from log c

where c.user_id is null;

解决方案2：赋予空值新的key值

select * from log a

left join user b

on case when a.user_id is null then concat(‘hive’,rand()) else a.user_id end = b.user_id

总结2 比方法1 效率更好，不但IO少了，而且作业数也少了，方案1中，log表读了两次，join肯定是2，而方案2是1.这个优化适合无效id（比如-99，‘’，null）产生的数据倾斜，把空值的key变成一个字符串加上一个随机数，就能把造成数据倾斜的数据分到不同的reduce上解决数据倾斜的问题。

改变之处：使本身为null的所有记录不会拥挤在同一个reduceTask了，会由于有替代的随机字符串值，而分散到了多个reduceTask中了，由于null值关联不上，处理后并不影响最终结果。

（2）不同数据类型关联产生数据倾斜

场景说明

用户表中user_id字段int，log表中user_id为既有string也有int的类型，当按照两个表的user_id进行join操作的时候，默认的hash操作会按照int类型的id进行分配，这样就会导致所有的string类型的id就被分到同一个reduce当中

解决方案

把数字类型id转换成string类型的id

select * from user a

left join log b on b.user_id = cast(a.user_id as string )

（3）大小表关联查询产生数据倾斜

注意：使用map join解决小表关联大表造成的数据倾斜问题，这个方法使用的频率很高。

map join概念：将其中做连接的小表（全量数据）分发到所有maptask端进行join，从而避免了reducetask，前提要求是内存足以装下该全量数据。

以大表a和小表b为例，所有的maptask节点都装载小表b的所有数据，然后大表a的一个数据块数据比如说是a1去跟b全量数据做链接，就省去了reduce做汇总的过程。所以相对来说，在内存允许的条件下使用map join比直接使用MapReduce效率还高些，当然这只限于做join查询的时候。

在hive中，直接提供了能够在hql语句指定该次查询使用map join，map join的用法是在查询/子查询的select 关键字后面添加

/*+ MAPJOIN(tablelist)*/

提示优化器转化为map join（早期的hive版本的优化器是不能自动优化map join的）。其中tablelist可以是一个表，或以逗号连接的表的列表。tablelist中的表将会读入内存，通常应该是将小表写在这里。

mapjoin具体用法：

select /* +map join(a) */a.id aid, name, age from a join b on a.id=b.id;

select /* +map join(movies) */ a.title, b.rating

from movies a join ratings b on a.moviedid = b.movieid;

在hive0.11版本以后会自动开启map join优化，由两个参数控制：

set hive.auto.convert.join = true; // 设置mapjoin优化自动开启

set hive.mapjoin.smalltable.filesize=2500 0000 //设置小表不超过多大时开启mapjoin优化。

如果是大大表关联呢？那就大事化小，小事化了。把大表切分成小表，然后分别map join

那么如果小表不大不小，那该如何处理呢？

使用mapjoin 解决小表（记录数少）关联大表的数据倾斜问题，这个方法使用的频率非常高，但如果小表很大，大到map join会出现bug或异常，这时就需要特别的处理

场景说明

举一例：日志表和用户表做链接

select * from log a left join users b on a.user_id = b.user_id;

users表有600W+的记录，把users分发到所有的map上也是个不小的开销，而且map join不支持这么大的小表。如果用普通的join，又会碰到数据倾斜的问题。

解决方案

select /*+mapjoin(x)*/* from log a

left join (select /*+mapjoin(c)*/ d.*

from (select distinct user_id from log) c

join users d on c.user_id = d.user_id

) x

on a.user_id = x.user_id;

加入，log里user_id有上百万个，这就又回到原来map join问题。所幸，每日的会员uv不会太多，有交易的会员不会太多，有点击的会员不会太多，有佣金的会员不会太多 等。所以这个方法能解决很多场景下的数据倾斜问题。