数据仓库
为什么学习数据仓库
数据不兼容,很难被整合
战略决策需要数据的分析
推荐系统
数据仓库(DW)
数据仓库是一个面向主题的、集成的、非易失的且随时间变化的数据集合
面向主题
主题(Subject)是在较高层次上将企业信息系统中的数据进行综合、归类和分析利用的一个抽象概念
每一个主题基本对应一个宏观的分析领域
在逻辑意义上,它是对应企业中某一宏观分析领域所涉及的分析对象
- 例如:“销售分析”就是一个分析领域,因此这个数据仓库应用的主题就是“销售分析”
提取主题(一)
一个面向事物处理的“商场”数据库系统,其数据模式如下
-
采购子系统:
订单(订单号,供应商号,总金额,日期) 订单细则(订单号,商品号,类别,单价,数量) 供应商(供应商号,供应商名,地址,电话)
-
销售子系统:
顾客(顾客号,姓名,性别,年龄,文化程度,地址,电话) 销售(员工号,顾客号,商品号,数量,单价,日期)
提取主题(二)
-
库存管理子系统:
领料单(领料单号,领料人,商品号,数量,日期) 进料单(进料单号,订单号,进料人,收料人,日期) 库存(商品号,库房号,库存量,日期) 库房(库房号,仓库管理员,地点,库存商品描述)
-
人事管理子系统:
员工(员工号,姓名,性别,年龄,文化程度,部门号) 部门(部门号,部门名称,部门主管,电话)
集成
集成性是指数据仓库中数据必须是一致的
-
数据仓库的数据是从原有的分散的多个数据库、数据文件和数据段中提取来的
-
数据来源可能既有内部数据又有外部数据
例如:F/M,0/1,A/B
集成方法
- 统一:消除不一致的现象
- 综合:对原有数据进行综合和计算
非易失
数据仓库中的数据是经过抽取而形成的分析型数据
- 不具有原始性
- 主要供企业决策分析之用
- 执行的主要是“查询”操作,一般情况下不执行“更新”操作
- 一个稳定的数据环境也有利于数据分析操作和决策的制定
随时间变化
数据仓库以维的形式对数据进行组织,时间维是数据仓库中很重要的一个维度
- 不断增加新的数据内容
- 不断删去旧的数据内容
- 更新与时间有关的综合数据
数据仓库和数据库的区别
数据库是为捕获和存储数据而设计web项目(web项目,给客户看)
数据仓库是为分析数据而设计(分析结果给管理层看,辅助决策)
数据库 | 数据仓库 | |
---|---|---|
本质 | 数据的集合 | 数据的集合 |
定位 | 事物处理OLTP | 数据分析OLAP |
面向群体 | 前端用户 | 管理人员 |
操作 | 增删改查 | 查询 |
数据粒度 | 事件记录 | 维度 |
表结构 | 3NF | 星型、雪花型 |
3NF:三大范式
第一范式:单列不可再分
第二范式:实体的属性完全依赖于主关键字
第三范式:任何非主属性不依赖于其他非主属性
OLTP和OLAP的区别
联机事务处理OLTP
-
On-Line Transaction Processing
-
OLTP是传统的关系型数据库的主要应用
主要是基本的、日常的事物处理 例如银行交易
联机分析处理OLAP
- On-Line Analytical Processing
- OLAP是数据仓库系统的主要应用,支持复杂的分析操作,侧重决策支持,并且提供直观易懂(数据可视化)的查询结果
对比属性 | OLTP | OLAP |
---|---|---|
读特性 | 每次查询只返回少量记录 | 对大量记录进行汇总 |
写特性 | 随机、低延时写入用户的输入 | 批量导入 |
使用场景 | 用户,Java EE项目 | 内部分析师,为决策提供支持 |
数据表征 | 最新数据状态 | 随时间变化的历史状态 |
数据规模 | GB | TB到PB |
数据仓库分层
- ODS:数据接入层
- DWD:数据明细层
- DWS:数据汇总层
- ADS:数据应用层
三层架构:ODS、DW、ADS
四层架构:ODS、DWD、DWS、ADS
数据仓库结构(一)
Inmon架构
数据集市:DM/ADS
数据仓库架构(二)
Kimball
数据仓库架构(三)
混合型架构
数据仓库的解决方案
数据采集
- Flume,Sqoop,Logstash,Datax
数据存储
- MySQL,HDFS,Hbase,Redis,MongoDB
数据计算
- Hive,Tez,Spark,Flink,Storm,Impala
数据可视化
- Tableau,Echarts,Superset,QuickBl,DataV
任务调度 - Oozie,Azkaban,Crontab
数据ETL
抽取(Extract)
- 从操作型数据源获取数据
转换(Transform)
- 转换数据,使之转变为适用于查询和分析的形式和结构
装载(Load)
- 将转换后的数据导入到最终的目标数据仓库
ETL工具
Oracle
- OWB和ODI
微软
- SQL Server Integration Services
SAP
- Data Integrator
IBM
- InfoSphere DataStage、Informatica
Pentaho
- Kettle
数据仓库的建模
数据仓库模型构建
- 选择业务流程
- 声明粒度
- 确认维度
- 确认事实
选择业务流程
选择业务流程
-
确认哪些业务处理流程是数据仓库应该覆盖的
例如:了解和分析一个零售店的销售情况
-
记录方式
使用纯文本 使用业务流程建模标注(BPMN)方法 使用同一建模语言(UML)
声明粒度
声明粒度
-
用于确定事实中表示的是什么
例如:一个零售店的顾客在购物小票上的一个购买条目
-
选择维度和事实前必须声明粒度
-
建议从原始粒度数据开始设计
原始记录能够满足无法预期的用户查询
-
不同的事实可以有不同的粒度
确认维度
确认维度
-
说明了事实表的数据是从哪路采集来的
-
典型的维度都是名词
例如:日期、商店、库存等
-
维度表存储了某一维度的所有相关数据
例如:日期维度应该包括年、季度、月、周、日等数据
确认事实
确认事实
-
识别数字化的度量,构成事实表的记录
-
和系统的业务用户密切相关
-
大部分事实表的度量都是数字类型的
可累加,可计算 例如:成本、数量、金额
星型模型(一)
星型模型特点
-
由事实表和维度表组成
-
一个星型模式中可以有一个或多个事实表,每个事实表引用任意数量的维度表
-
星型模式将业务流程分为事实和维度
事实包括业务的度量,是定量的数据 如销售价格、销售数量、距离、速度、重量等是事实 维度是对事实数据属性的描述 如日期、产品、客户、地理位置等是维度
星型模型(二)
优点
- 简化查询
- 简化业务报表逻辑
- 获得查询性能
- 快速聚合
- 便于向立方体提供数据
缺点
- 不能保证数据完整性
- 对于分析需求来说不够灵活
雪花模型(一)
雪花模型特点
-
一种多维模型中表的逻辑布局
-
由事实表和维度表所组成
-
将星型模式中的维度表进行规范化处理
把低基数的属性从维度表中移除并形成单独的表
-
一个维度被规范化成多个关联的表
雪花模型(二)
优点
- 一些OLAP多维数据库建模工具专为雪花模型进行了优化
- 规范化的维度属性节省存储空间
缺点
- 维度属性规范化增加了查询的连接操作和复杂度
- 不确保数据完整性
示例
数据抽取(一)
从RDB导入数据到HDFS
- 在HDFS中创建/data/retail_db
[root@hadoop100 01]# hdfs dfs -mkdir -p /data2/retail_db
#导入表到mysql中
#在家目录下创建一个文件夹01,把要创建的sql语句的txt文件放进去
#第一种方式:
[root@hadoop100 01]# mysql -uroot -pok
mysql> create database retail;
mysql> quit;
[root@hadoop100 01]# mysql -uroot -pok retail < /root/01/retail_db.sql
#第二种方式:
[root@hadoop100 01]# mysql -uroot -pok
mysql> create database retail;
mysql> use retail;
mysql> source /root/01/retail_db.sql;
mysql> show tables;
- 将customers表导入到/data/retail_db/customers文件夹下
[root@hadoop100 sqoop]# bin/sqoop import
--connect jdbc:mysql://hadoop100:3306/retail
--username root
--password ok
--table customers
--target-dir /data2/retail_db/customers
--m 3
增量导入
- 使用增量导入将订单表导入到HDFS中
[root@hadoop100 sqoop]# bin/sqoop import
--connect jdbc:mysql://hadoop100:3306/retail
--username root
--password ok
--table orders
--incremental append
--check-column order_id
-target-dir /data2/retail_db/orders
--m 3
导入数据到Hive分区
- 创建外部表
#打开hive
[root@hadoop100 sqoop]# hive
hive> create external table orders_ext(
order_id int,
order_date string,
order_customer_id int,
order_status string)
row format delimited
fields terminated by ','
lines terminated by '\n'
stored as textfile
location "/data2/retail_db/orders";
- 创建分区表
hive> create table orders_partiton(
order_id int,
order_date string,
order_customer_id int,
order_status string)
partitioned by (date string);
- 开启动态分区
hive> set hive.exec.dynamici.partition=true;
hive> set hive.exec.dynamic.partition.mode=nonstrict;
- 插入动态分区
hive> insert into orders_partiton partition(date)
select *,date(order_date) from orders_ext;
注意:如果有报错Error during job, obtaining debugging information…
FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask
- 解决办法:hive中插入动态分区报错及解决办法
再次插入动态分区
导出HDFS数据到MySQL
[root@hadoop100 sqoop]# bin/sqoop export
--connect jdbc:mysql://hadoop100:3306/sqoop
--username root
--password ok
--table orders
--export-dir /data2/retail_db/orders
--input-fields-terminated-by ','
--m 1
数据分析(一)
select及关联查询
- 将顾客表、部门表、商品表数据存入Hive
#顾客表
[root@hadoop100 sqoop]# sqoop-import
--connect jdbc:mysql://hadoop100:3306/retail
--username root
--password ok
--table customers
--hive-import
--create-hive-table
--hive-database retail_ods
--hive-table customers
--m 1
#部门表
[root@hadoop100 sqoop]# sqoop-import
--connect jdbc:mysql://hadoop100:3306/retail
--username root
--password ok
--table departments
--hive-import
--create-hive-table
--hive-database retail_ods
--hive-table departments
--m 1
#商品表
[root@hadoop100 sqoop]# sqoop-import
--connect jdbc:mysql://hadoop100:3306/retail
--username root
--password ok
--table products
--hive-import
--create-hive-table
--hive-database retail_ods
--hive-table products
--m 1
#种类表
[root@hadoop100 sqoop]# sqoop-import
--connect jdbc:mysql://hadoop100:3306/retail
--username root
--password ok
--table categories
--hive-import
--create-hive-table
--hive-database retail_ods
--hive-table categories
--m 1
- 查询顾客表中地区为“NY”所在城市为’New York’的用户
hive> select concat(customer_lname,"-",customer_fname)
from customers where customer_city="New York" and
customer_state="NY";
- 查询订单表中共有多少不同顾客下过订单
hive> select count(distinct order_customer_id) from orders;
- 查询商品表中前5个商品
select product_name from products limit 5;
- 使用关联查询获取没有订单的所有顾客
select * from customers c where not exists
(select * from orders o where order_customer_id=c.customer_id);
数据分析(二)
实现Hive数据加载及排序
- 将order_items.csv数据通过load方式加载到order_items表
hive> CREATE TABLE IF NOT EXISTS order_items (
order_item_id int,
order_item_order_id int,
order_item_product_id int,
order_item_quantity int,
order_item_subtotal float,
order_item_product_price float)
ROW FORMAT serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
with serdeproperties ("separatorChar"=",");
hive> load data local inpath '/root/retail_db-csv/order_items.csv'
into table order_items;
- 将order_items表中数据加载到order_test2表
hive> create table order_test2 like order_items;
hive> from order_items
insert into table order_test2 select *;
- 将order_items表中数据同时加载到o_01和o_02表
hive> create table o_01 like order_items;
hive> create table o_02 like order_items;
hive> from order_items
insert into table o_01 select *;
hive> from order_items
insert into table o_02 select *;
- 将order_items表中数据导出到本地以及hdfs
#导出到本地
hive> from order_items insert overwrite local directory '/root/01/order_items.txt' select * ;
#导出到hdfs
hive> from order_items insert overwrite directory '/data3/' select *;
- 统计order_items表中订单数量排行(取前10)
hive> select order_item_order_id,sum(order_item_quantity)e from
order_items group by order_item_order_id order by e desc limit 10;
数据分析(三)
使用聚合和窗口函数实现零售数据统计
- 统计order_items表中销量最多的前10个商品
hive> select order_item_product_id,sum(order_item_quantity)e from
order_items group by order_item_product_id order by e desc limit 10;
- 统计每个商品大类下的商品子类
hive> select category_department_id,category_name FROM categories;
- 根据商品子类id大小对每个商品大类下的子类进行排名使用ROW_NUMBER()函数
hive> select category_department_id,category_name,row_number()
over(partition by category_department_id order by category_id)
FROM categories;
- 统计order_items表中各订单中不同商品总数、订单总金额、订单最高/最低/平均金额
hive> select order_item_order_id,sum(order_item_quantity)
over(partition by order_item_order_id,order_item_product_id),
sum(order_item_subtotal) over(partition by order_item_order_id),
max(order_item_subtotal) over(partition by order_item_order_id),
min(order_item_subtotal) over(partition by order_item_order_id),
avg(order_item_subtotal) over(partition by order_item_order_id)
from order_items;