ETL工程师面试题

项目问题：
1.每天抽取数据10+怎么抽取

抽取每天超过10TB的数据可能需要一些高效的数据抽取策略和工具。以下是一些建议：

1. **增量抽取：** 仅抽取发生变化的数据，而不是每次都抽取整个数据集。这可以通过记录上次抽取的时间戳或者标记数据变化的方式实现。增量抽取可以大幅减少数据传输和处理的开销。

2. **并行处理：** 使用并行处理技术，将数据分成多个部分同时进行抽取。这可以通过并行计算框架（如Apache Spark）来实现，提高整体抽取性能。

3. **分布式存储：** 如果数据分布在多个地方，考虑使用分布式存储系统，例如Hadoop分布式文件系统（HDFS）或云存储服务。这有助于提高数据读取速度。

4. **压缩和编码：** 在传输数据之前，可以使用压缩算法对数据进行压缩。这样可以减少网络带宽的使用，并加快数据传输速度。

5. **合理选择ETL工具：** 使用专业的ETL工具，如Apache NiFi、Talend、Informatica等，可以简化数据抽取的流程，并提供一些优化性能的选项。

6. **数据库优化：** 如果数据源是数据库，确保数据库的索引和统计信息是最新的，以优化数据查询性能。

7. **使用增强型硬件：** 考虑使用高性能的硬件，例如SSD存储、高速网络等，以加速数据抽取和传输过程。

8. **定时任务调度：** 使用定时任务调度系统，确保数据抽取过程在合适的时机执行，避免对业务产生影响。

9. **数据分区：** 如果可能，将数据分区存储，这样可以只抽取需要的分区，而不是整个数据集。

10. **监控和优化：** 实施监控系统，及时发现和解决抽取过程中的问题，并根据实际情况不断优化抽取策略。

以上这些建议是在处理大规模数据抽取时可以考虑的方法，具体的实现方式会受到你的具体业务需求、数据源类型以及技术栈的影响。

2.一般怎么把数据进行一些码值转换
数据的码值转换通常涉及以下几种情况：

数据的码值转换是数据处理中常见的任务之一，通常涉及以下几种情况：

1. **映射表转换：** 创建一个映射表，将原始数据的某个字段值映射为另一个字段值。这可以通过数据库表、配置文件或代码中的映射关系实现。例如，将性别字段中的 "M" 映射为 "男"，"F" 映射为 "女"。

2. **条件转换：** 根据某个条件对数据进行转换。这可以通过编写条件语句或者使用函数来实现。例如，根据销售额的大小将客户分为高、中、低三个级别。

3. **数值范围映射：** 将数值字段的范围映射为预定义的标签。例如，将年龄字段划分为不同的年龄段，如 "0-18岁"、"19-30岁"、"31-50岁" 等。

4. **日期转换：** 将日期字段转换为特定的格式，或者提取日期中的特定信息，如年份、月份、星期等。

5. **文本处理：** 对文本字段进行处理，例如将全部大写转换为小写，去除空格或标点符号，或者执行其他文本清理操作。

6. **枚举类型转换：** 将数据字段的值映射为预定义的枚举类型。这在处理分类信息时很常见，例如将产品类型映射为枚举值。

7. **缺失值处理：** 将缺失值转换为特定的占位符或者使用某种默认值进行填充。

8. **加密和脱敏：** 在一些敏感数据场景下，对数据进行加密或者脱敏处理，以保护隐私。

在实际操作中，可以使用ETL工具、编程语言（如Python、Java）或者SQL语句来实现这些转换操作。选择合适的方法取决于数据量、处理复杂度以及所使用的技术栈。无论采用何种方式，保持清晰的文档和注释，以及进行测试，都是良好的实践，可以确保数据转换的准确性和可维护性。

3.用kettle调用存过

Kettle（又称为Pentaho Data Integration）是一款开源的ETL（Extract, Transform, Load）工具，用于处理和转换数据。在Kettle中，你可以使用"Table Input"步骤或"Execute SQL script"步骤来调用存储过程。下面是使用Kettle调用存储过程的基本步骤：

### 方法一：使用"Table Input"步骤

1. 打开Spoon（Kettle的图形用户界面）。

2. 在图形界面中，找到"Table Input"步骤，拖动该步骤至转换画布。

3. 配置"Table Input"步骤的连接信息，指定连接数据库的相关参数。

4. 在"SQL"选项卡中，输入调用存储过程的SQL语句。例如：

    ```sql
    CALL your_stored_procedure_name(?, ?, ...);
    ```

    注意：确保将存储过程的参数适当地填充在括号中。

5. 如果存储过程有输出参数，你可以在"Output"选项卡中添加相应的字段。

6. 运行转换，查看结果。

### 方法二：使用"Execute SQL script"步骤

1. 打开Spoon。

2. 在图形界面中，找到"Execute SQL script"步骤，拖动该步骤至转换画布。

3. 配置"Execute SQL script"步骤的连接信息，指定连接数据库的相关参数。

4. 在"SQL"选项卡中，输入调用存储过程的SQL语句。例如：

    ```sql
    CALL your_stored_procedure_name(?, ?, ...);
    ```

    注意：确保将存储过程的参数适当地填充在括号中。

5. 如果存储过程有输出参数，你可以在"Output"选项卡中添加相应的字段。

6. 运行转换，查看结果。

在以上步骤中，你需要根据实际情况替换`your_stored_procedure_name`为实际存储过程的名称，并根据存储过程的参数设置相应的占位符。同时，确保Kettle连接到了正确的数据库，并具有执行存储过程的权限。

注意：使用存储过程时，可能需要考虑数据库类型（MySQL、PostgreSQL、Oracle等）的差异，以及存储过程的具体语法规则。

4.数据你们公司数据量有多大

 增量数据10万+  全量数据10亿+

5.数据仓库的架构（数据仓库的分层）分三层

我们将数据模型分为三层：数据运营层（ ODS ）、数据仓库层（DW）和数据应用层（APP）：
ODS层存放的是接入的原始数据，DW层是存放我们要重点设计的数据仓库中间层数据，APP是面向业务定制的应用数据。

一、数据运营层：ODS（Operational Data Store） “面向主题的”数据运营层，也叫ODS层，是最接近数据源中数据的一层，数据源中的数据，经过抽取、洗净、传输，也就说传说中的 ETL 之后，装入本层。本层的数据，总体上大多是按照源头业务系统的分类方式而分类的。
一般来讲，为了考虑后续可能需要追溯数据问题，因此对于这一层就不建议做过多的数据清洗工作，原封不动地接入原始数据即可，至于数据的去噪、去重、异常值处理等过程可以放在后面的DWD层来做。
二、数据仓库层：DW（Data Warehouse） 数据仓库层是我们在做数据仓库时要核心设计的一层，在这里，从 ODS 层中获得的数据按照主题建立各种数据模型。DW层又细分为 DWD（Data Warehouse Detail）层、DWM（Data WareHouse Middle）层和DWS（Data WareHouse Servce）层。
1. 数据明细层：DWD（Data Warehouse Detail）
该层一般保持和ODS层一样的数据粒度，并且提供一定的数据质量保证。同时，为了提高数据明细层的易用性，该层会采用一些维度退化手法，将维度退化至事实表中，减少事实表和维表的关联。
另外，在该层也会做一部分的数据聚合，将相同主题的数据汇集到一张表中，提高数据的可用性，后文会举例说明。
2. 数据中间层：DWM（Data WareHouse Middle）
该层会在DWD层的数据基础上，对数据做轻度的聚合操作，生成一系列的中间表，提升公共指标的复用性，减少重复加工。
直观来讲，就是对通用的核心维度进行聚合操作，算出相应的统计指标。
3. 数据服务层：DWS（Data WareHouse Servce）
又称数据集市或宽表。按照业务划分，如流量、订单、用户等，生成字段比较多的宽表，用于提供后续的业务查询，OLAP分析，数据分发等。
一般来讲，该层的数据表会相对比较少，一张表会涵盖比较多的业务内容，由于其字段较多，因此一般也会称该层的表为宽表。
在实际计算中，如果直接从DWD或者ODS计算出宽表的统计指标，会存在计算量太大并且维度太少的问题，因此一般的做法是，在DWM层先计算出多个小的中间表，然后再拼接成一张DWS的宽表。由于宽和窄的界限不易界定，也可以去掉DWM这一层，只留DWS层，将所有的数据在放在DWS亦可。
三、数据应用层：APP（Application） 在这里，主要是提供给数据产品和数据分析使用的数据，一般会存放在 ES、PostgreSql、Redis等系统中供线上系统使用，也可能会存在 Hive 或者 Druid 中供数据分析和数据挖掘使用。比如我们经常说的报表数据，一般就放在这里。
四、维表层（Dimension） 最后补充一个维表层，维表层主要包含两部分数据：
高基数维度数据：一般是用户资料表、商品资料表类似的资料表。数据量可能是千万级或者上亿级别。
低基数维度数据：一般是配置表，比如枚举值对应的中文含义，或者日期维表。数据量可能是个位数或者几千几万。

  ods：把业务系统的数据抽取到数据仓库里（财务系统 核心业务系统 、客户关系系统 、人力资源管理系统）
  dw：ods层数据清洗转换之后存放到dw层（去除后续不需要的字段 去重 去空格、码值转换 统一字段类型）
  App：dw层的数据根据业务需求的指标进行存储过程的编写，用作后续分析使用（编写分析报告 制作报表）

6.组内人员构成：
组长 etl 开发 需求 数据分析 bi

7.在工作中写过那些表
资产负债表、利润表、现金流量表、固定资产明细表等

8、遇到的比较复杂的存过（难点）

 根据客户忠诚度来分级，首先根据需求得到与银行忠诚度的相应算法
根据指标名、指标值、分值、权重等计算出一个得分，然后将表中的数据进行分析筛选得到需要的字段，
这些字段分布在不同的表中，通过表连接将这些表连接在一起，用case when
进行指标分级，然后将查询出来的结果集子查询，最后通过各指标的权重进行计算得到每个用户的最终分值

9.MySql 死锁怎么解决？

转自：http://blog.csdn.net/mchdba/article/details/38313881

之前也遇到一次，今天又遇到了这个问题，所以这次必须解决，
网上找到这篇文章帮了大忙，方便以后复习。这篇文章的解决办法对于我的情况是有效的。

我的具体情况是：使用RobotFramework测试时，本来可以通过的一个case报错了，报错为：InternalError: (1205, u'Lock wait timeout exceeded; try restarting transaction。网上找了很多也没解决问题，还是这篇文章简单有效。
2016-10-12更新：找到问题所在了，就是线上开发环境和开本地环境同时跑AT，结果因为争抢数据库资源导致数据库死锁。解决方法其实可以简化为两步：1是查出锁死的数据库线程SELECT trx_mysql_thread_id FROM information_schema.INNODB_TRX;；2是将查出的线程杀死 kill 。

前言：朋友咨询我说执行简单的update语句失效，症状如下：
mysql> update order_info set province_id=15 ,city_id= 1667 where order_from=10 and order_out_sn='1407261241xxxx';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

QQ远程过去，开始check

1，查看数据库的隔离级别：
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

mysql>

2，去查看先当前库的线程情况：
mysql> show processlist;
+----------+-----------------+-------------------+-----------------+-------------+---------+-------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+-----------------+-------------------+-----------------+-------------+---------+-------------------------+-----------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 9635385 | Waiting on empty queue | NULL |
| 9930577 | business_web | 192.168.1.21:45503 | business_db | Sleep | 153 | | NULL |
| 9945825 | business_web | 192.168.1.25:49518 | business_db | Sleep | 43 | | NULL |
| 9946322 | business_web | 192.168.1.23:44721 | business_db | Sleep | 153 | | NULL |
| 9960167 | business_web | 192.168.3.28:2409 | business_db | Sleep | 93 | | NULL |
| 9964484 | business_web | 192.168.1.21:24280 | business_db | Sleep | 7 | | NULL |
| 9972499 | business_web | 192.168.3.28:35752 | business_db | Sleep | 13 | | NULL |
| 10000117 | business_web | 192.168.3.28:9149 | business_db | Sleep | 6 | | NULL |
| 10002523 | business_web | 192.168.3.29:42872 | business_db | Sleep | 6 | | NULL |
| 10007545 | business_web | 192.168.1.21:51379 | business_db | Sleep | 155 | | NULL |
......
+----------+-----------------+-------------------+-----------------+-------------+---------+-------------------------+-----------------------+

没有看到正在执行的慢SQL记录线程，再去查看innodb的事务表INNODB_TRX，看下里面是否有正在锁定的事务线程，看看ID是否在show full processlist里面的sleep线程中，如果是，就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了，我们需要手动kill掉。

mysql> SELECT * FROM information_schema.INNODB_TRX;
*************************** 1. row ***************************
trx_id: 20866
trx_state: LOCK WAIT
trx_started: 2014-07-31 10:42:35
trx_requested_lock_id: 20866:617:3:3
trx_wait_started: 2014-07-30 10:42:35
trx_weight: 2
trx_mysql_thread_id: 9930577
trx_query: delete from dltask where id=1
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
trx_is_read_only: 0
trx_autocommit_non_locking: 0

3，看到有这条9930577的sql，kill掉，执行kill 9930577;
mysql> kill 9930577;
Query OK, 0 rows affected (0.00 sec)

mysql>

然后再去查询INNODB_TRX表，就没有阻塞的事务sleep线程存在了，如下所示：
mysql> SELECT * FROM INNODB_TRX;
Empty set (0.00 sec)

ERROR:
No query specified

mysql>
再去执行update语句，就能正常执行了，如下所示：
mysql> update order_info set province_id=15 ,city_id= 1667 where order_from=10 and order_out_sn='1407261241xxxx';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql>

4，总结分析
表数据量也不大，按照普通的情况来说，简单的update应该不会造成阻塞的，mysql都是autocommit，不会出现update卡住的情况，去查看下autocommit的值。
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)

mysql>

看到亮闪闪的0，这个设置导致原来的update语句如果没有commit的话，你再重新执行update语句，就会等待锁定，当等待时间过长的时候，就会报ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction的错误。
所以赶紧commit刚才执行的update语句，之后 set global autocommit=1;

10、数据库sql常见优化方法

下面我们就聊一聊sql优化的一些常见方法：

1）尽量不要用select * from table，除非需要返回数据库表的全部字段，否则不要返回用不到的任何字段。因为select * 会导致全表扫描，效率比较低。

2）where子句及order by涉及的列尽量建索引，不一定要全部建索引，依业务情形而定。对于多条where子句都用到的列，建议建索引。索引并不是越多越好，索引固然可以提高相应的select的效率，但同时也降低了insert及update 的效率。

3） 尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将会放弃使用索引而进行全表扫描。 对于不等于这种情况，可考虑改为范围查询解决。

4）尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，引擎将放弃使用索引而进行全表扫描，如：

select id from person_info where age=10 or name= '张三'；

可以这样查询：

select id from person_info where age = 10
union all
select id from person_info where name= '张三'

5）尽量避免在 where 子句中对字段进行 null 值判断，因为空判断将导致全表扫描，而不是索引扫描。 对于空判断这种情况，可以考虑对这个列创建数据库默认值。如：

//nu11判断将会导致全表扫描
select * from person info where name is null;
//可以考虑在经常需要nul1值判断的列，设为默认值，例如空字符串
select * from person info where name = '';

6）in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from person_info where age in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from person_info where age between 1 and 3

很多时候用 exists 代替 in 是一个好的选择：

select age from a where age in(select age from b)

用下面的语句替换：

select age from a where exists(select age from b where b.age=a.age)；

7）尽量避免左右模糊查询，这样会导致索引失效，进而全表查询，如：select id from person_info  where name like ‘%abc%’，可以使用右侧模糊查询，这样是可以索引查找的，如：select id from person_info  where name like ‘abc%’；

8）如果在 where 子句中使用参数或对字段进行表达式操作，也会导致全表扫描，如：

select id from person_info  where age/2 = 10

应改为: select id from person_info   where age= 10*2；

9）应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。函数、算术运算或其他表达式运算通常将导致全表扫描， 对于这种情况，可以考虑冗余部分数据到表中。

10）在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

11）update 语句，如果只更改1、2个字段，不要update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。

12）对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。

13）select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。可以改为select count(id) from table。

14）尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

15）尽可能的使用varchar代替char，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

sql语句尽量用大写 select句中避免使用* 减少访问数据库的次数 Where条件筛选一般在索引列
带有union、minus、intersect的sql语句都可以用其他方式重写 尽量多使用commit 优化group
by可以通过将不需要的记录在group by之前过滤掉 用where子句替换having子句 用索引提高效率 用>=替代>
避免在索引列上使用not,避免在索引列进行计算

11.说一下表 分区

表分区是一种数据库管理技术，用于将大表按照一定的规则划分成更小的、更易管理的子集，
每个子集被称为分区。表分区通常能够提高查询性能、简化数据维护和管理，以及改善数据存储的效率。以下是一些表分区的常见概念和用法：

1. **范围分区（Range Partitioning）：**
   - 按照列的范围值进行划分。例如，按照日期范围将数据划分为每个月或每年一个分区。

2. **列表分区（List Partitioning）：**
   - 按照列的离散值进行划分。例如，按照国家或地区将数据划分为不同的分区。

3. **哈希分区（Hash Partitioning）：**
   - 使用哈希函数将数据均匀地分布到多个分区中，通常用于均匀分散数据负载的情况。

4. **散列子分区（Subpartitioning）：**
   - 在每个分区内再进行进一步的分区，通常是在范围、列表、或哈希分区的基础上。

5. **复合分区（Composite Partitioning）：**
   - 结合多种分区策略，例如先按照范围分区，然后在每个范围内按照列表分区。

6. **分区键（Partition Key）：**
   - 用于指定分区的列或列集合。分区键的选择是关键的，它直接影响到数据的分布和查询性能。

7. **分区表维护：**
   - 支持动态增加和删除分区，使得表的维护更加灵活。这对于大型表的数据管理和维护提供了更好的控制。

8. **分区索引（Partitioned Index）：**
   - 与分区表一起使用的索引，以提高对分区表的查询性能。

表分区的优势在于可以减少查询时需要扫描的数据量，提高查询效率，并且简化数据的维护和管理。然而，表分区并不是适用于所有情况的解决方案，需要根据具体的业务需求和数据库使用情况来决定是否使用分区表。在使用表分区时，确保选择适当的分区策略和分区键，以充分发挥分区的优势。

12. 11g 19c 9.0

13.表空间 5个
系统表空间：主要包括数据字典、pl\sql程序单元以及所有方案对象的定义
sysaux表空间：作为system表空间的辅助表空间
临时表空间：用于暂存一些复杂的sql查询语句运行时产生的临时数据
撤销表空间：由oracle自动分配管理，一个实例在一个时刻只能使用一个undo表空间
用户表空间：用来存放用户数据
永久性表空间：一般保存表、视图、过程和索引等的数据。
临时性表空间：只用于保存系统中短期活动的数据。
撤销表空间：用来帮助回退未提交的事务数据。
14.linux常见命令

  grep 正则 awk  sed (vim+grep)  cat  find  mkdir  rmdir
 查看磁盘空间cpu或者内存使用情况： df  du
 常用的linux操作系统：redhat 红帽 hp-unix
 linux操作系统中的常用命令
ls、cd、pwd、mkdir、rmdir、cp、rm、mv、cat、tac、nl、more、less、head、tail、df、du、seq、chmod、echo、wc、sort、su、find、zip、unzip、gzip、ps、grep、sed、awk等
 查看前100行后100行 head -100 tail -100

15.常用的表有哪些,数据量有多大

 资产表、客户表、现金流水表、流水表  50+

16.经常使用的字段

 客户编码、客户类别、客户名称、证件类型、证件号码、客户性别、开卡时间、电话号码

17.对客户怎么分层

  根据客户开户日期做细分,为客户打账龄属性标签,<=3个月的为新客户,>3个月<12个月为待开发客户,>36个月的为老客户,其余的为熟悉客户

18.kettle经常使用的

 输入输出、获取系统信息、维度查询/更新、字段选择、增加常量、排序记录、增加序列、19.什么是数据抽取
  从源数据系统把数据抽取到ods层或者dw层中

20.如何数据清洗

把不需要的、不符合规范的进行处理(空值处理、校验准确性、规范格式、数据转码、数据标准)

21.转换加载

数据刷新,就是把每天抽取过来的数据更新到通过模型设计好的表中,如事实表、维度表、汇总表等,更新这些表的存过都是开发好的,个人认为这些合并增删改的过程都叫加载

22.索引用在什么地方

一般加在经常检索的字段的列

23.索引分类

 B树索引：一般用
 位图索引：在列中值高度重复时用

24.索引优缺点

  索引优点：加快索引速度、加速表之间的连接、分组排序是减少查询时间、唯一索引保证每一行的唯一性
  索引缺点：占用物理空间,对表进行增删改合并时索引也跟着动态维护,降低效率

25.索引失效

有or会失效,对索引字段进行计算,not in,null,not null 

26.使用索引一定会增加效率嘛

  不一定

27.维度建模中的两种表模型

事实表：指分析主题所有对应的表或者需求所有对应的表或者指标计算字段所在表
   特点：一般是由外键(其他表主键)的聚集的表
 维度表：在对事实表根据各个维度进行统计分析的时候,可能需要关联上其他的表,此时其他的表一般称为维度表
    在一些特殊的情况下，有一些表既是当前的事实表,又是其他主题的维度表

28.常见的建模方式

三范式建模
以业务为导向,在建表时,表应该有一个主键，尽可能避免数据的冗余情况发生
维度建模
以分析为导向,构建表时，是要能够满足分析的要求,能够让目标分析更加简单,在利于分析的要求下,允许数据出现一定的冗余

29.数仓发展的三种发展模型

  星型模型：只有一个事实表,也就是只有一个分析的主题,有多个维度表、多个维度表之间没有任何关联
     什么时期容易产生：中期
   雪花模型：只有一个事实表,也就是只有分析的主题,有多个维度表,维度表可以接着关联其他维度表
       什么时期容易产生：数仓出现了畸形的情况下,有可能产生模型,这种模型下,非常不便于维护和分析,在实际使用尽量避免这种模型出现
星座模型：有多个事实表,也就是有多个分析的主题,有多个维度表,在条件复合的情况下,多个事实表之间的维度可以进行公用
   多个星型模型构成的星座模型
 星型模型和雪花模型的区别：星型模型是事实表直接连接维度表,雪花是直接或间接连接

30.主键索引和唯一索引的区别

  相同点：都属于实体完整性约束
  不同点：唯一性索引所在的列允许空值,但是主键约束所在的列不允许空值
          可以把唯一性约束放在一个或者多个列上,这些列或列组合必须有唯一性,但唯一性约束所在的列并不是表的主键列
          唯一性约束强制在指定的列上创建一个唯一性索引,在默认情况下,创建唯一性的非聚簇索引,但也可以指定所创建的索引是聚簇索引
          建立主键的目的是让外键来引用
          一个表最多只有一个主键，但可以有很多唯一键

31.触发器

DML触发器、替代触发器、系统触发器、DDL触发器、登录触发器
        触发器是一种特殊的存储过程,在插入删除或修改特定表中的数据是并发执行的,
               比数据库本身标准的功能有更精细更复杂的数据空值能力
               可以驾驭数据库中的数据或时间限制用户的操作
               可以跟踪用户对数据库的操作
               实现复杂的数据完整性规则
               能同步实时的复制表中的数据

32.存储过程中的异常处理

  exception when 异常名1 then...;when 异常名2 then...;end;
预定义异常：有名字,有异常描述
非预定义异常：没名字,有描述
自定义异常：想要什么名字自己起

33.游标

  静态游标：显示游标和隐式游标
  动态游标：强类型和弱类型
  主要应用在数据批量更新或删除

34.存储过程和函数的区别

函数有return,存过没有
存过可以调用函数,函数不可以调用存过
存过用来实现某种操作或者业务,函数用来实现某种功能
DML一般用存过
如果返回值超过一个,一般用存过

35.存储过程和触发器的区别

  1.存储过程时已经创建并存储在数据库中的SQL语句，可以重复使用，而触发器是一种特殊的不是又用户直接调用的存储过程创建触发器时,会定义在针对特定表或列进行特定类型的数据修改时触发
   2.用户可以直接调用或执行存储过程,但是无法直接调用或执行触发器
   3.存储过程可以采用输入参数,而触发器不能将参数作为输入我们不能将参数作为输入传递给触发器
    4.存储过程可以返回零或N个值,触发器无法返回值
    5.可以在存储过程中使用事务,触发器内不允许进行事务处理
    6.存储过程通常用于执行用户指定的任务,触发器通常用于审计工作

36.常用的SQL函数：

  sum、count、substr、sum()over()、rank()over()、row_number()over()

37.锁表

  造成锁表的原因:一个程序执行了对一个表的INSERT，并且还未COMMIT提交，另一个程序也对同一个表进行INSERT，则此时就会发生资源正在忙的异常
  解除锁表的方法：查看被锁的表是由哪个用户造成的死锁，然后查看简洁的进程查出锁定表的SID，最后杀掉进程SID
  减少锁表概率的方法：减少INSERT,UPDATE,DELETE语句到COMMIT之间的时间，具体可以把批量执行改为单个执行、优化SQL自身的非执行速度

38.视图

视图和表不同，视图是已经编译好的SQL语句，没有实际的物理记录，只是逻辑概念上的存在。
   表可以及时修改而视图只能修改创建视图的语句，如果表时内容的话视图就是窗口。可以合并分离的数据创建分区视图，更安全。
   视图的建立和删除只影响视图本身，不会影响对应的基本表，工作中我们一般部直接修改视图
 优缺点：使用视图可以指定用户数据，聚焦特定的数据，简单化数据操作，使基表中的数据有一定的安全性，可以合并分立数据，创建分区视图。      
       但是视图性能差，修改有限制

39.数据备份：

exp imp

40.oracle的job

  job有定时执行的功能，可以在指定的时间点或每天的某个时间点自行执行任务。
                       而且oracle重新启动后，job会继续运行，不用重新启动。
                       最重要的字段就是job这个值就是我们操作job的id号，
					   what 操作存储过程的名称，
 					   next_date 执行的时间，
					   interval执行间隔

41.拉链表

  拉链表是一种数据模型，主要是针对数据仓库设计中表存储数据的方式而定义的，
顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，一直到当前状态的所有变化的信息。
拉链表可以避免按每一天存储所有记录造成的海量存储问题，同时也是处理缓慢变化数据（SCD2）的一种常见方式。

42.报表的相关知识
展现报表：所谓展现报表就是将数据库里的数据取出来以一定的样式展现出来，不改变数据库里的数据的报表。展现报表根据取数方式的不同，又可分为普通报表和行式报表。普通展现报表一次取出所需的全部数据，可以做分组、求和等运算，因此在设计时尽量控制取出数据的数量，只取出必要的数据，在硬件能够承受的条件下运行，避免发生内存溢出。行式报表是采取sql分页查询的方式，因此，当数据量较大的时候，可以采用行式报表。
行式报表主要用于处理大数量的数据展现，它每次都只从数据库中调取本页的数据，当点击下一页的时候，会重新调取下一页的数据，这样就解决了内存溢出的问题。由于行式报表每次只取出了一部分数据来展现，因而没法做分组，求和，求平均值等运算。
填报报表：填报报表顾名思义就是进行数据的填写和上报，因此它不仅能从数据库取数，还能进行数据的录入和修改。
43.linux相关知识
常见的命令 查看磁盘空间 cpu或者内存使用情况 查看文件前100后100行
cat查看cpu个数 du查看文件磁盘空间 top查看cpu free查看内存
44.表连接
内连接和外连接，外连接分为左外连接，右外连接，全外连接
内连接是显示量表共有的数据
左外连接是显示量表共有的和左表独有的数据，右表用空补齐
右外连接是显示两表共有的数据和右表独有的数据，
左表用空补齐 全外连接是显示量表独有和共有的数据
45.drop、truncate和delete的区别
DROP：属于DDL，不能回滚，不能加WHERE条件，表内容和表结构一起删除
TRUNCATE：属于DDL，不能回滚，不能加WHERE条件，只删除表内容
DELETE：属于DML，可以回滚，可以加WHERE条件，根据WHERE删除部分内容
速度上来说DROP>TRUNCATE>DELETE
46.ddl dml：
DDL数据定义语言：CREATE 创建；ALTER 修改；DROP 删除；TRUNCATE 清空
DML数据操作语言：INSERT INTO 插入；UPDATE SET 更新；DELETE FROM 删除；MERGE INTO
47.大数据：hadoop
1.大 多 准确（数据质量有保证）
2.解决的问题：大量的数据存储和数据计算
48.数据的类型：
1.半结构化数据：数据有一定的结构 但是结构不固定
json xml
2.结构化数据：固定的字段 字段类型一定（数据库里的表）
3.非结构化数据：没有固定的结构（文本文件 视频 音频 图片）
49.大数据行业内分析的基本步骤：
1.明确分析的目的和基本思路
2.收集数据（web页面 手机app（埋点） 业务系统 ）
3.数据的处理（java）
4.数据分析处理（SQL hiveSQL hql）
5.数据的应用：数据可视化分析
6.数据分析报告的产出：数据分析人员
50.hadoop：Apache使用java语言编写的开源免费的软件 是一个开发和运行处理大规模数据的平台
hadoop的三大核心：
hdfs：分布式存储数据的框架 可以实现海量数据的存储（磁盘）
yarn：是负责资源调度的平台（cpu 内存）
MapReduce：分布式计算的框架
hadoop的创始人：道格卡廷 Doug cutting 卡大爷
hadoop的特性：扩容能力
成本低：构建成本低 花销少
效率高：存储数据和处理数据的效率
可靠性：数据不容易丢失
51.hadoop生态圈：
hdfs：分布式存储数据的框架 可以实现海量数据的存储（磁盘）
yarn：是负责资源调度的平台（cpu 内存）
MapReduce：分布式计算的框架
hive：基于hadoop分布式存储的数据仓库 提供SQL查询的数据操作
sqoop：hadoop生态圈里的etl工具（RDBMS–>hdfs）
oozie：工作流调度框架
zookeeper：分布式协调服务的组件
cdh：提供了统一的可视化管理界面 可以实现大数据组件的自动部署和配置 并且调优和安装都非常便捷
多用户管理来控制权限，稳定性高
缺点：占用内存 对linux有一定的了解 对hadoop的Apache版本有一定的安装和调优经验
52.hdfs：基本概念
分布式文件系统：跨多台服务器来完成数据存储的工作
能够存储海量的数据（tb pb 。。）并且为用户提供一个统一的资源访问接口 让用户感觉到只是在访问一个简单的文件系统

hdfs适用的场景：
	1.存储非常大的文件 需要高吞吐量 对延迟没有要求
	2.一次性写入多次读取：数据一旦存储之后 不需要对数据进行更高 后期只是查询
hdfs不适用的场景:
	1.低延迟的数据访问
	2.不适合存储大量小文件
	3.需要对数据进行多次修改

53.hive:
基于hadoop的数据仓库工具
主要用于数据的离线分析
本质：就是把SQL转化为mr语言的工具
为什么要用hive
直接使用hadoop开发比较繁琐 用户需要对mr直接进行操作，学习成本高
hive提供了类似于SQL的功能 用户只要编写SQL即可查询出相关的数据 学习成本低 因为大多数程序员都会SQL
54.oozie：是一个开源的工作流调度引擎框架
工作流：业务过程的部分或者整体在计算机应用环境下的自动化
1.业务过程可以被拆解
2.业务流程之间存在依赖关系
3.业务流程是一个周期性的工作
oozie三种工作流：
workflow：最基础的工作流任务，不支持定时，不支持批量处理（一次性执行多个工作流）
coordinator（计划）：对workflow进行再包装，让他支持定时周期执行
bundle：对coordinator进行再度包装在能定时执行任务的情况下实现批量处理
oozie的工作流配置：基于一种图形DAG（有向无环图）
55.sqoop：是Apache下的一款用于hadoop生态圈与关系型数据库之间进行数据导入导出的工具

工作机制：将导入导出的命令转化成mr来执行

56.sqoop和其他常见etl工具的区别
kettle nifi sqoop
1.kettle虽然功能完善，但是当处理大量数据的时候瓶颈问题比较突出 不适合大数据项目
2.nifi功能强大，且支持大数据量操作，但是它独立于hadoop集群，需要独立服务器来支撑
上手门槛高 学习难度大 用人成本高
3.sqoop专为关系型数据库和hadoop之间的etl而生，支持海量数据，操作门槛低
57.数据仓库：
特点：
面向主题
数据集成
非易失
时变
数据仓库系统执行流程
1、确定分析所依赖的源数据。
2、通过ETL将源数据采集到数据仓库。
3、数据按照数据仓库提供的主题结构进行存储。
4、根据各部门的业务分析要求创建数据集市（数据仓库的子集）。
5、决策分析、报表等应用系统从数据仓库查询数据、分析数据。
6、用户通过应用系统查询分析结果、报表
58.ETL（Extra, Transfer, Load）包括数据抽取、数据转换、数据装载三个过程。
1、抽取
​ 数据抽取是从各各业务系统、外部系统等源数据处采集源数据。
2、转换
​ 采集过来的源数据如果要存储到数据仓库需要按照一定的数据格式对源数据进行转换，常见的转换方式有数据类型转换、格式转换、缺失值补充、数据综合等。
3、装载
​ 转换后的数据就可以存储到数据仓库中，这个过程要装载。数据装载通常是按一定的频率进行的，比如每天装载当天的订单数据、每星期装载客户信息等。
59.数据仓库：是用于企业整体分析的数据集合，比如分为：销售主题、客户主题、产品主题等。
数据集市：是用于部门分析的数据集合，从范围上来讲它属于数据仓库的子集，比如： 销售部门的数据集市只有销售主题。
数据仓库和数据集市具有什么区别？
1、范围的区别
数据仓库是针对企业整体分析数据的集合。
数据集市是针对部门级别分析的数据集合。
2、数据粒度不同
数据仓库通常包括粒度较细的数据明细。
数据集市则会在数据仓库的基础上进行数据聚合，这些聚合后的数据就会直接用于部门业务分析。
60.维度与指标
指标：衡量事务发展的标准，也叫度量，如价格，销量等；指标可以求和、求平均值等计算
维度：事务的特征，如颜色、区域、时间等，可以根据不同的维度来对指标进行分析对比。比如根据区域维度来分析不同区域的产品销量，根据时间来分析每个月产品的销量，同一个产品销量指标从不同的维度分析会得出不同的结果
总结：维度就是编写SQL过程中用到的分组字段和条件筛选字段
61.维度建模：
维度表和事实表:
维度表：维度是指观察数据的角度，一般是一个名词，比如对于销售金额这个事实，我们可以从销售时间、销售产品、销售店铺、购买顾客等多个维度来观察分析
高基数维度数据：一般是用户资料表、商品资料表类似的资料表。数据量可能是千万级或者上亿级别。
低基数维度数据：一般是配置表，比如枚举值对应的中文含义，或者日期维表、地理维表等。数据量可能是个位数或者几千条几万条。
事实表：事实表记录了特定事件的数字化信息,一般由数值型数字和指向维度表的外键组成

62.数仓分层：
好处：1.清晰数据结构：每一个数据分层都有它的作用域和职责，在使用表的时候能更方便地定位和理解。
2.复杂问题简单化：将一个复杂的任务分解成多个步骤来完成，每一层解决特定的问题。
3.便于维护：当数据出现问题之后，可以不用修复所有的数据，只需要从有问题的步骤开始修复。
4.减少重复开发：规范数据分层，开发一些通用的中间层数据，能够减少重复开发的工作量。
5.高性能：数据仓库的构建将大大缩短获取信息的时间，数据仓库作为数据的集合，所有的信息都可以从数据仓库直接获取，尤其对于海量数据的关联查询和复杂查询，所以数据仓库分层有利于实现复杂的统计需求，提高数据统计的效率。
简单理解数仓更层次的作用
ODS：存放接入的原始数据
DW：存放重点设计的数据仓库中间层数据
APP：面向业务定制的应用数据
63.hive的分区操作
分区字段的选择
1.选择的字段可以把数据均匀的分成多个区域
2.确定好的分区字段与后续查询需求所使用的字段能够对应上
3.选择分区字段后，尽量避免大量分区数据产生
分区方式：
静态分区：导入数据时需要手动指定分区
应用场景：导入数据时 向一个分区导入（适合单个分区导入数据）
例子：load data inpath ‘user/hive/xxx.txt’ into table partition(分区字段=值)
insert into table partition(分区字段=值)

动态分区：导入数据时，系统可以动态判断目标分区
应用场景：适合一次性导入多个分区数据
例子：insert into partition(分区字段)
注意：select语句的查询结果最后的字段必须是分区字段 并且按顺序列出
64.构建每一层需要用到的表
外部表：external,删除表的时候 数据不会被删除
数据不归我们独自管理 数据除了我们自己使用 其他部门也要用到这个表
内部表：删除表的时候 数据也会同时被删除
数据完全归我们自己管理 采用内部表

企业发展的痛点：
数据量大：hdfs大数据的存储容器
数据分散：sqoop大数据的etl工具
统计分析难度大：hive进行数据分析
项目的大致流程：
1.进行数据预处理 建立数据中心
2.数据分析处理 重中之重 大量的分析SQL
3.数据的展示 BI可视化分析
项目的需求：共计五大模块
1.访问和咨询用户看板
2.意向用户看板
3.报名用户看板
4.有效线索看板
5.学生出勤看板
项目的架构：
1.通过sqoop将数据从不同数据源导入到hdfs里
2.将hdfs数据在hive中映射成表
3.在hive中进行数据清洗分析
4.将分析结构导出到MySQL中
5.通过可视化工具进行报表展示
所用到的技术：hadoop hive+sqoop+oozie+dhfs+BI工具
管理平台：CDH

在线教育行业近几年发展迅猛导致数据量激增，传统关系型数据库无法满足日常分析需求
所以采用大数据技术来解决当前面临的问题
我们的项目是基于CDH实现的 在这里面 我们用到了 hive sqoop oozie hdfs xxBI工具去做实施
实施过程中有五大模块 分别....... 在这里 我主要负责 xxx模块的开发 	

咨询量与访问量的统计操作
1.全量
1.1：数据采集MySQL—>ods层的表
1.2：从ods层采集数据到dwd层
1.3：dwd–>dws层
1.4：dwd层数据导出到MySQL
2.增量
2.1：数据采集MySQL—>ods层的表
2.2：从ods层采集数据到dwd层
2.3：dwd–>dws层
2.4：dwd层数据导出到MySQL
每一步脚本如何编写（处理当前日期前一天的数据）

数据仓库的分层：
ods层：与原表的数据保持一致
dw：需要考虑的是如何把零散的数据 一步一步细化
dwd：明细层 根据分析的主题 把与主题相关的表结合在一起形成一张新表，并且只保留后续分析要用的字段
ods—>dwd
insert into table
select
…
from a,b

		dwm：中间表 主要是将一些多个维度统计出来形成一张中间表
			 对不同维度的进一步分析
		dws：业务层 对不同维度的指标进行计算

	访问量表设计：
		维度：小时 天 月 季度 年  区域 来源  搜索来源  访问页面 访问量
		需要考虑 如何存储以上相关的数据

研究数据如何存储
行存储的特点： 查询满足条件的一整行（所有列）数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，
行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快。
列存储的特点： 因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；
每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法。
数据格式：采用orcfile的格式
压缩方式：在ODS数据源层，因为数据量较大，可以采用orcfile+ZLIB的方式，以节省磁盘空间；
而在计算的过程中（DWD、DWM、DWS、APP），为了不影响执行的速度，可以浪费一点磁盘空间，采用orcfile+SNAPPY的方式，提升hive的执行速度。
存储空间足够的情况下，推荐采用SNAPPY压缩。
T+1：T数据产生的时间 1：数据分析的时间 T+1：数据产生之后需要留在下一天进行分析