大数据数据仓库，Sqoop–学习笔记

数据仓库介绍

1. 数据仓库概念

数据仓库概念创始人在《建立数据仓库》一书中对数据仓库的定义是：数据仓库（Data Warehouse）是一个面向主题的（Subject Oriented）、数据集成的（Integrated）、相对稳定(非易失)的（Non-Volatile）、反映历史变化(时变)（Time Variant）的数据集合，用于支持管理决策(Decision Making Support)。
数据仓库是决策支持系统（dss）的结构化数据环境，如下图，决策支持系统基于数据仓库进行联机分析处理 ( OLAP ) 。常用的技术有，HDFS、HBase、Hive、SparkSql等。

1. 数据采集, 将源数据采集到数据仓库
2. 基于数据仓库进行数据分析
3. 生成报表

2，OLTP和OLAP区别

OLTP（On-Line Transaction Processing）即联机事务处理，也称为面向交易的处理过程，其基本特征是前台接收的用户数据可以立即传送到计算中心进行处理，并在很短的时间内给出处理结果，是对用户操作快速响应的方式之一，比如ERP系统，CRM系统，互联网电商系统等，这类系统的特点是事务操作频繁，数据量小。
OLAP（On-Line Analytical Processing）即联机分析处理，有时也称为决策支持系统（DSS），支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。这类系统的特点是没有事务性操作，主要是查询操作，数据量大。

数据仓库的特点

1， 面向主题

数据库应用是以业务流程来划分应用程序和数据库，比如ERP（Enterprise Resource Planning）包括：进销存系统、人力资源管理系统、财务管理系统、仓库管理系统等，进销存系统管理了进货、销售、存储等业务流程，人力资源系统管理了员工的信息、待遇等相关信息。
数据仓库是以数据分析需求来对数据进行组织划分若干主题，比如销售主题、员工主题、产品主题，主题是一个抽象的概念，可以理解为相关数据的分类、目录等，通过销售主题可以进行销售相关的分析，如年度销量排行、月度订单量统计等。
总之，主题是以分析需求为导向来组织数据，数据库应用系统是以业务流程为导向来组织数据，注意：主题中的数据是跨应用系统的。

2， 数据集成

主题中的数据是跨应用系统的，也就是说数据是分散在各各应用系统，比如销售数据在进销存系统中有，财务系统中也有，为了进行销售分析需要将销售数据进行集成，集成在销售主题中，就可以从销售主题来进行数据分析。

3，非易失

数据库应用系统是根据业务需求进行数据处理和存储，而数据仓库是根据数据分析需求来进行数据存储，数据仓库中的数据用于查询和分析，为了保证数据分析的准确性和稳定性，数据仓库中的数据一般是很少更新的，会将历史快照保存下来。

4， 时变

数据仓库中的数据存储的是历史数据，历史数据是随时间变化的，比如历年的销售数据都会存储到数据仓库中，即使数据仓库中的数据很少更新，但也不能保证没有变化，如下需求：
1）会不断添加新数据
每年的销售数据会逐渐添加到数据仓库。
2）删除过期数据
数据仓库中的数据会保存很长的时间（5–10年），但也有过期时间，到过期 时间会删除过期 数据。
3）对历史明细数据进行聚合
为了方便数据分析，根据分析需求会将比较细粒度的数据进行数据聚合存储，这也是时变的一种表现，比如：为了方便统计年度销售额会将销售记录按月进行统计，统计年度销售额时只需要针对月度销售结果进行统计即可。

维度分析

1. 维度分析介绍
   对数据进行分析通常采取维度分析，比如：用户提出分析课程访问量的指标，为了满足不同的分析需求可以从时间维度分析课程访问量，分析每天、每小时的课程访问量；也可以从课程维度来分析课程访问量，分析每个课程、每个课程分类的访问量。
2. 指标与维度
   要进行维度分析需要先理解两个术语：指标和维度。
   指标是衡量事务发展的标准，也叫度量，如价格，销量等；指标可以求和、求平均值等计算。
   指标分为绝对数值和相对数值，绝对数值反映具体的大小和多少，如价格、销量、分数等；相对数值反映一定的程度，如及格率、购买率、涨幅等。
   维度是事务的特征，如颜色、区域、时间等，可以根据不同的维度来对指标进行分析对比。比如根据区域维度来分析不同区域的产品销量，根据时间来分析每个月产品的销量，同一个产品销量指标从不同的维度分析会得出不同的结果。
   维度分为定性维度和定量维度两种，定性维度就是字符类型的特征，比如区域维度包括全国各省份；定量维度就是数值类型的特征，如价格区间、销量区间等，如价格区间维度分为0–100、100-1000两个区间，可以按价格区间维度来对指标进行分析，说到这里，其实指标是可以转成维度的，所转成的维度就是定量维度。
   用具体的指标数值, 来度量不同的维度。x轴和y轴的关系。

1，识别维度案例

在日常生活中，我们从不同的角度看待事务会有不同的体会，数据分析也如此，比如：一个在线教育的平台，作为运营方会关注按时间段分析课程的访问量，作为教育机构则关注单个课程的访问量，都是课程访问量指标根据不同的维度去分析得到结果不同，这就是维度分析。
比如：按时间分析课程访问量，时间维度是课程访问量的分析依据，时间维度和业务中的课程访问量是对应的，下表列出了课程访问量明细记录：

2，维度分层与分级

通常在分析结果中首先看到的是一个总数，比如全年课程购买量，然后会详细去看每个季度、每个月的课程购买量，全年、季度、月这些属于时间维度的一个层次，年、季度、月是这个层次的三个级别；再比如按地区分析课程购买量，全国、省、市、县属于地区维度的一个层次，层次中共有四个级别。
相当于将维度进行细分。细分两层，则维度包含一个层次，多个级别。 细分三层，则维度包含多个层次，多个级别。
每个维度至少有一个层次且该层次至少有一个级别。下边将课程访问的各各维度定义层次和级别

3，下钻与上卷

维度中有不同的层次，每个层次可以有多个级别，这样就可以根据多个维护层次和级别进行分析，可以灵活获取高级别的汇总信息，获取低级别的明细信息。
把获取高级别的汇总信息的过程叫上卷，把获取低级别的明细信息的过程叫下钻
课程访问量分析，时间维度有四个级别，分别是年、月、天、小时，现在我们某个级别分析每天的课程访问量，比如按天分析课程访问量，此时我们可以按小时下钻分析，得出一天内每小时的课程访问量，也可以按月上卷，得到月度的课程访问量。
下钻维度：
天、小时
上卷维度：
年、月

数仓建模

1. 概述

数据仓库建模的方法常用的有两种：三范式建模法、维度建模法，三范式建模法主要是应用于传统的企业级数据仓库，这类数据仓库通常使用关系型数据库实现，是由Inmon提出的，应用于自顶向下的数据仓库架构; 维度数据模型就是基于维度分析来创建模型，是由Kimball提出，应用于自下向上的数据仓库架构。本课程采用维度建模的方法。
维度建模，简称DM（Dimensional modeling），数据仓库大师Kimball的观点：维度数据模型是一种趋向于支持最终用户对数据仓库进行查询的设计技术，是围绕性能和易理解性构建的。维度模型是按照用户看待或分析数据的角度来组织数据。
维度建模的两个核心概念：事实表和维度表。

2. 事实表

事实表记录了特定事件的数字化信息，一般由数值型数字和指向维度表的外键组成。
事实表的设计依赖于业务系统，事实表的数据就是业务系统的指标数据。数据分析的实质就是基于事实表开展的计算操作。

3. 维度表

维度是指观察数据的角度，一般是一个名词，比如对于销售金额这个事实，我们可以从销售时间、销售产品、销售店铺、购买顾客等多个维度来观察分析。
维度表的记录数比事实表少，但是每条记录可能会包含很多字段。

4. 常见的建模方法

1， 星型模型
是一种多维的数据关系。一个事实表为中心，多个维度表环绕周围。
一个星型模型中可以有一个或多个事实表，每个事实表可以引用任意数量的维度表。
星型模型将业务流程分为事实和维度。事实是对业务的度量，是定量的数据，比如价格、销售数量、距离、速度、质量等。维度是对事实数据属性的描述，比如日期、产品、客户、地理位置等。
2 雪花模型
当有一个或多个维表没有直接连接到事实表上，而是通过其他维表连接到事实表上时，就像多个雪花连接在一起，故称雪花模型。雪花模型是对星型模型的扩展，它对星型模型的维表进一步层次化，原有的各维表可能被扩展为小的事实表，形成一些局部的 “层次 ” 区域，这些被分解的表都连接到主维度表而不是事实表。
层次化的影响
层次化的过程是将维度表中重复度比较高的字段组成一个新表，所以层次化不可避免增加了表的数量，减少了数据的存储空间，提高了数据更新的效率。但是查询时就需要连接更多的表。
总结，雪花模型中，一个维度被规范化成多个关联的表，星型模型中，每个维度由一个单一的维度表所表示。
3，星座模型
多个事实表，多个维度表，事实表之间可以相互关联，维度表之间一般通过事实表进行关联，特殊情况下维度表也可以互相关联。

渐变维（SCD）

1，什么是渐变维

维度可以根据变化剧烈程度主要分为无变化维度和变化维度。例如一个人的相关信息，身份证号、姓名和性别等信息数据属于不变的部分；而婚姻状态、工作经历、工作单位和培训经历等属于可能会变化的字段。
大多数维度数据随时间的迁移是缓慢变化的。比如增加了新的产品，或者产品的ID号码修改了，或者产品增加了一个新的属性，此时，维度表就会被修改或者增加新的记录行。这样，在设计维度和使用维度的过程中，就要考虑到缓慢变化维度数据的处理。
缓慢渐变维，即维度中的属性可能会随着时间发生改变，比如包含用户住址Address的DimCustomer维度，用户的住址可能会发生改变，进而影响业务统计精度，DimCustomer维度就是缓慢渐变维（SCD）。

2，解决问题的方法

1，SCD1（缓慢渐变类型1）
通过更新维度记录直接覆盖已存在的值。不维护记录的历史。一般用于修改错误的数据，即历史数据就是错误数据，除此没有他用。
在数据仓库中，我们可以保持业务数据和数据仓库中的数据始终处于一致。可以在 Customer 维度中使用来自业务数据库中的 Business Key – CustomerID 来追踪业务数据的变化，一旦发生变化那么就将旧的业务数据覆盖重写。
DW 中的记录根据业务数据库中的 CustomerID 获取了最新的 City 信息，直接更新到 DW 中。
2，SCD2（缓慢渐变类型2）
在源数据发生变化时，给维度记录建立一个新的“版本”记录，从而维护维度历史。SCD2不删除、不修改已存在的数据。SCD2也叫拉链表。
在数据仓库中有很多需求场景会对历史数据进行汇总和分析，因此会尽可能的维护来自业务系统中的历史数据，使系统能够真正捕获到这种历史数据的变化。
3，SCD3（缓慢渐变类型3）
实际上SCD1 and 2 可以满足大多数需求了，但是仍然有其它的解决方案，比如说 SCD3。 SCD3希望只维护更少的历史记录。
比如说把要维护的历史字段新增一列，然后每次只更新 Current Column 和 Previous Column。这样，只保存了最近两次的历史记录，历史数据都在同一行数据中。但是如果要维护的字段比较多，就比较麻烦，因为要更多的 Current 和 Previous 字段。所以 SCD3 用的还是没有 SCD1 和 SCD2 那么普遍。它只适用于数据的存储空间不足并且用户接受有限历史数据的情况。

数据仓库分层

1，数据分层的好处

1.清晰数据结构：每一个数据分层都有它的作用域和职责，在使用表的时候能更方便地定位和理解。
2.复杂问题简单化：将一个复杂的任务分解成多个步骤来完成，每一层解决特定的问题。
3.便于维护：当数据出现问题之后，可以不用修复所有的数据，只需要从有问题的步骤开始修复。
4.减少重复开发：规范数据分层，开发一些通用的中间层数据，能够减少重复开发的工作量。
5.高性能：数据仓库的构建将大大缩短获取信息的时间，数据仓库作为数据的集合，所有的信息都可以从数据仓库直接获取，尤其对于海量数据的关联查询和复杂查询，所以数据仓库分层有利于实现复杂的统计需求，提高数据统计的效率。
数据分层
1 ODS数据源
此层数据无任何更改，直接沿用外围系统数据结构和数据，不对外开放；为临时存储层，是接口数据的临时存储区域，为后一步的数据处理做准备。
.2 DW数据仓库
2.1 DWD明细层
明细层DWD（Data Warehouse Detail）：存储明细数据，此数据是最细粒度的事实数据。该层一般保持和ODS层一样的数据粒度，并且提供一定的数据质量保证。
2.2 DWB基础数据层
数据降维后生成的明细宽表，作为中间数据使用。一般只保留一段周期内的有效数据。(一般是一年）
2.3 DWS服务数据层
按照主题划分的日统计宽表，基于DWB上的基础数据，整合汇总成分析某一个主题域的服务数据。
3 DM数据集市
数据集市层，主要职责是建设宽表模型、汇总表模型，比如用户主题宽表、销售主题宽表等。主要作用是支撑数据分析查询以及支持应用所需数据。
4 RPT报表应用
同ADS层、APP层。
根据报表、专题分析的需求而计算生成的个性化数据。

Sqoop

1， Sqoop介绍

Sqoop是Apache下的顶级项目，用来将Hadoop和关系型数据库中的数据相互转移，可以将一个关系型数据库（例如：MySQL，Oracle，PostgreSQL等）中的数据导入到Hadoop的HDFS中，也可以将HDFS的数据导出到关系型数据库中。目前在各个公司应用广泛，且发展前景比较乐观。其特点在于：
1)专门为Hadoop而生，随Hadoop版本更新支持程度好，且原本即是从CDH版本孵化出来的开源项目，支持CDH的各个版本号。
2)它支持多种关系型数据库，比如mysql、oracle、postgresql等。
3)可以高效、可控的利用资源。
4)可以自动的完成数据映射和转换。
5)大部分企业还在使用sqoop1版本，sqoop1能满足公司的基本需求。
6)自带的辅助工具比较丰富，如sqoop–import、sqoop–list–databases、sqoop–list–tables等。
导入数据：MySQL，Oracle导入数据到Hadoop的HDFS、HIVE、HBASE等数据存储系统；
导出数据：从Hadoop的HDFS、HIVE中导出数据到关系数据库mysql等

2,为什么选择Sqoop

我们常用的ETL工具有Sqoop、Kettle、Nifi。
知行教育大数据平台，ETL的数据量较大，但是数据来源的类型简单（mysql）：
1.Kettle虽然功能较完善，但当处理大数据量的时候瓶颈问题比较突出，不适合此项目；
2.NiFi的功能强大，且支持大数据量操作，但NiFi集群是独立于Hadoop集群的，需要独立的服务器来支撑，强大也就意味着有上手门槛，学习难度大，用人成本高；
3.Sqoop专为关系型数据库和Hadoop之间的ETL而生，支持海量数据，符合项目的需求，且操作简单门槛低，因此选择Sqoop作为ETL工具。

3,sqoop1与sqoop2架构对比

1,Sqoop1以Client客户端的形式存在和运行。没有任务时是没有进程存在的。
2,sqoop2是以B/S服务器的形式去运行的，始终会有Server服务端进程在运行。

4. Sqoop抽取的两种方式

对于Mysql数据的采集，通常使用Sqoop来进行。
通过Sqoop将关系型数据库数据到Hive有两种方式，一种是原生Sqoop API，一种是使用HCatalog API。两种方式略有不同。
HCatalog方式与Sqoop方式的参数基本都是相同，只是个别不一样，都是可以实现Sqoop将数据抽取到Hive。
2,区别
①数据格式支持
Sqoop方式支持的数据格式较少，HCatalog支持的数据格式多，包括RCFile, ORCFile, CSV, JSON和SequenceFile等格式。
②数据覆盖
Sqoop方式允许数据覆盖，HCatalog不允许数据覆盖，每次都只是追加。
③字段名
Sqoop方式比较随意，不要求源表和目标表字段相同(字段名称和个数都可以不相同)，它抽取的方式是将字段按顺序插入，比如目标表有3个字段，源表有一个字段，它会将数据插入到Hive表的第一个字段，其余字段为NULL。但是HCatalog不同，源表和目标表字段名需要相同，字段个数可以不相等，如果字段名不同，抽取数据的时候会报NullPointerException错误。HCatalog抽取数据时，会将字段对应到相同字段名的字段上，哪怕字段个数不相等。

5，Sqoop方式

sqoop import 
--hive-import 
--connect 'jdbc:mysql://localhost:3306/test' 
--username 'root' 
--password '123456789' 
--query " select order_no from driver_action where  $CONDITIONS" 
--hive-database test 
--hive-table driver_action 
--hive-partition-key pt 
--hive-partition-value 20190901 
--null-string '' 
--null-non-string '' 
--num-mappers 1 
--target-dir /tmp/test 
--delete-target-dir

6, HCatalog方式

sqoop import 
--connect jdbc:mysql://localhost:3306/test
--username 'root' 
--password 'root' 
--query "SELECT order_no FROM driver_action  WHERE $CONDITIONS" 
--hcatalog-database test 
--hcatalog-table driver_action 
--hcatalog-partition-keys pt 
--hcatalog-partition-values 20200104 
--hcatalog-storage-stanza 'stored as orcfile tblproperties ("orc.compress"="SNAPPY")' 
--num-mappers 1

sqoop import 
--connect jdbc:mysql://localhost:3306/test
--username 'root' 
--password 'root' 
--query "SELECT order_no_src as order_no_target  FROM driver_action WHERE $CONDITIONS" 
--hcatalog-database test 
--hcatalog-table driver_action 
--hcatalog-partition-keys pt 
--hcatalog-partition-values 20200104 
--hcatalog-storage-stanza 'stored as orc tblproperties ("orc.compress"="SNAPPY")' 
--num-mappers 1

项目选型

因为项目采用的是ORC File文件格式，sqoop原始方式并不支持，因此使用HCatalog方式来进行数据的导入导出。
1,Sqoop的数据导入
“导入工具”导入单个表从RDBMS到HDFS。表中的每一行被视为HDFS的记录。所有记录都存储为文本文件的文本数据（或者Avro、sequence文件等二进制数据）

2,基础命令

列出所有数据库
/usr/bin/sqoop help

命令行查看帮助
/usr/bin/sqoop list-databases –help

列出主机所有的数据库
/usr/bin/sqoop list-databases —connect jdbc:mysql://192.168.88.80:3306/ —username root –password 123456

查看某一个数据库下面的所有数据表
/usr/bin/sqoop list–tables —connect jdbc:mysql://192.168.88.80:3306/hive —username root –password 123456

3，导入数据库表数据到HDFS

/usr/bin/sqoop import --connect jdbc:mysql://192.168.88.80:3306/test --password 123456 --username root --table emp --m 1
注意，mysql地址必须为服务器IP，不能是localhost或者机器名。

4，导入到HDFS指定目录

在导入表数据到HDFS时，使用Sqoop导入工具，我们可以指定目标目录。
使用参数 --target-dir来指定导出目的地，
使用参数--delete-target-dir来判断导出目录是否已存在，如果存在就删掉
/usr/bin/sqoop import  --connect jdbc:mysql://192.168.88.80:3306/test --username root --password 123456 --delete-target-dir --table emp  --target-dir /sqoop/emp --m 1
查看导出的数据
hdfs dfs -text /sqoop/emp/part-m-00000

5， 导入到hdfs指定目录并指定字段之间的分隔符

/usr/bin/sqoop import  --connect jdbc:mysql://192.168.88.80:3306/test --username root --password 123456 --delete-target-dir --table emp  --target-dir /sqoop/emp2 --m 1 --fields-terminated-by 't'
查看文件内容
hdfs dfs -text /sqoop/emp2/part-m-00000

6，导入关系表到HIVE

/usr/bin/sqoop import 
--connect jdbc:mysql://192.168.88.80:3306/test 
--username root 
--password 123456 
--table emp 
--fields-terminated-by 't' 
--hcatalog-database sqooptohive 
--hcatalog-table emp_hive 
-m 1

7，条件部分导入

where语句

/usr/bin/sqoop import 
--connect jdbc:mysql://192.168.88.80:3306/test 
--username root --password 123456 --table emp_add 
--target-dir /sqoop/emp_add -m 1  --delete-target-dir 
--where "city = 'sec-bad'"

query语句

/usr/bin/sqoop import 
--connect jdbc:mysql://192.168.88.80:3306/test --username root --password 123456 
--delete-target-dir -m 1 
--query 'select phno from emp_conn where 1=1 and  $CONDITIONS' 
--target-dir /sqoop/emp_conn

8， 增量导入数据到Hive表

/usr/bin/sqoop import 
--connect jdbc:mysql://192.168.88.80:3306/test --username root --password 123456 
--query "select * from emp where id>1203 and  $CONDITIONS" 
--fields-terminated-by 't' 
--hcatalog-database sqooptohive 
--hcatalog-table emp_hive 
-m 1

Sqoop数据导出

通过export来实现数据的导出，将hive的数据导出到mysql当中去

/usr/bin/sqoop export 
--connect jdbc:mysql://192.168.88.80:3306/test --username root --password 123456 
--table emp_out 
--hcatalog-database sqooptohive 
--hcatalog-table emp_hive 
-m 1

代码007普通

打赏 收藏 海报 链接