将之前的项目做一下复盘。
一、项目背景
Adventure Works Cycles是AdventureWorks样本数据库所虚构的公司,该公司生产和销售金属和复合材料自行车在全国各个市场。销售方式主要有两种,前期主要是分销商模式,但是2018年公司实现财政收入目标后,2019就开始通过公司自有网站获取线上商户进一步扩大市场。
1 客户类型
Adventure Works Cycle这家公司的客户主要有两种:
个体:这些客户购买商品是通过网上零售店铺
商店: 这些是从Adventure Works Cycles销售代表处购买转售产品的零售店或批发店。
2 产品介绍
这家公司主要有下面四个产品线:
• Adventure Works Cycles 生产的自行车
• 自行车部件,例如车轮,踏板或制动组件
• 从供应商处购买的自行车服装,用于转售给Adventure Works Cycles的客户。
• 从供应商处购买的自行车配件,用于转售给Adventure Works Cycles客户。
3 项目目标
通过现有数据监控商品的线上销售情况,并且获取最新的商品销售趋势,以及区域分布情况,为公司的制造和销售提供指导性建议,以增加公司的收益。
二、探索数据罗列分析指标
1.数据字典
从mysql数据库中共有3张表,分别是订单明细表、每日新增用户表、日期维度表,具体字段内容如下:
2.指标体系
以销售情况为主题,给老板、员工提供公司业务情况的基本信息
分析维度:
1 时间维度——年、季度、月、周、日
2 地区维度——销售区域、省份、城市
3 产品维度——产品类别、产品子类
分析指标:
1 总销售额
2 交易客户总数
3 客单价
4 目标金额
5 目标订单量
6 每日销售金额环比
7 当日/昨日/当月/当季/当年/ 总金额/订单量/客单价的同期比
三、 利用python对数据进行加工聚合
1. 每日环比表
2.当日维度表
3.时间_地区_产品聚合表
四、索引优化
1.索引介绍
索引本质上是一种数据结构。
索引的优点
- 1、提高检索效率,降低IO成本。
- 2、通过索引,对无序的数据进行排序,降低排序成本。比如在字典中,要查找“mysql”这个单词,有了索引,我们可以直接定位到m字母,然后往下找到y字母,再找到剩下的sql。如果没有索引,那么你可能需要把所有单词都看一遍直到能找到你想要的。
索引的缺点:
- 1、创建索引和维护索引要耗费时间,并且随着数据量的增加所耗费的时间也会增加。
- 2、索引也需要占空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间。
- 3、当对表中的数据进行增加、删除、修改时,索引也需要动态的维护,降低了数据的维护速度。
2.索引的类型
- 普通索引
- 唯一索引
- 主键索引
- 一种特殊的唯一索引,一个表中只有一个主键,不允许有空值,通常在建表的时候同时创建主键索引
- 组合索引
- 多个字段上创建的索引,遵循最佳左前缀匹配原则
3.索引的创建与删除
普通索引
(1) 直接创建索引
CREATE INDEX index_name ON table(column(length))
(2)修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
(3)查看索引
show index from table
(4)删除索引
DROP INDEX index_name ON table
唯一索引
CREATE UNIQUE INDEX index_name ON table(column(length))
alter table table_name ADD unique index name_index(name);
主键索引
ALTER TABLE customer add PRIMARY KEY customer(customer_no);
删除建主键索引:
ALTER TABLE customer drop PRIMARY KEY ;
修改建主键索引:
必须先删除掉(drop)原索引, 再新建(add)索引
组合索引
CREATE INDEX index_name ON customer(customer_no,customer_name);
或者
alter table tb add index dept_name_index(dept,name);
4.不适合创建索引
- 表记录太少
- 索引的选择性较低,索引的选择性是指不重复的索引值(Cardinality)与表记录数比值
- 经常增删改的表或字段
- where条件里用不到的字段
5.索引失效
- like以%开头
- 字符串不加单引号
- 包含is null 和 is not null
- where子句里对索引使用<>或!=
- where条件中有or,因为如果俩个字段中有一个没有索引的话,引擎会放弃索引而产生全表扫描
- 如果查询条件中含有函数或表达式,则MySQL不会为这列使用索引
- 对于联合索引来说,如果存在范围查询,比如between,>,<等条件时,会造成后面的索引字段失效。
6.常见优化手段
- 用select 字段名 代替 select * 增加了使用覆盖索引的可能性
- 使用or时没有用到索引,可以改为union或union all
- 使用in不能用到索引,可以改为exists,且exists后面跟的是大表
- 对于联合索引要遵循最左前缀原则
7.添加索引
有一种与索引选择性有关的索引优化策略叫做前缀索引,就是用列的前缀代替整个列作为索引key,当前缀长度合适时,可以做到既使得前缀索引的选择性接近全列索引,同时因为索引key变短而减少了索引文件的大小和维护开销。比如where条件当中增加 ‘2019-1’,那么索引会直接排除掉[‘2020’,‘2018’,‘2021’]等这些年份结果,直接匹配前缀相符合的内容。
CREATE INDEX index_date ON dw_order_by_day_jelly(create_date(7));
SELECT * FROM dw_order_by_day_jelly WHERE create_date='2019-02-11';
EXPLAIN SELECT * FROM dw_order_by_day_jelly WHERE create_date='2019-02-11';
五、定时任务更新
1.运行定时任务,用schedule模块
2.linux 挂在后台
部分示例:
import schedule
import time
import datetime
import os
import requests
def job1():
"""
dw_dim_df 时间维度表
"""
print('Job1:每天6:00执行一次')
print('Job1-startTime:%s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
os.system(
"/home/anaconda3/bin/python3 /home/frog005/adventure_jelly/create_dim_date.py >> /home/frog005/adventure_jelly/jelly_logs/create_dim_date_schedule.log 2>&1 &")
time.sleep(20)
print('Job1-endTime:%s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
print('------------------------------------------------------------------------')
if __name__ == '__main__':
schedule.every().day.at('06:00').do(job1)
while True:
schedule.run_pending() # 运行所有可以运行的任务
time.sleep(10)
print("wait", datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))
说明:
在linux中0 1 2是一个文件描述符,0表示,1表示,2表示,
名称 | 代码 | 操作符 |
---|---|---|
标准输入(stdin) | 0 | <或<< |
标准输出(stdout) | 1 | >,>>,1>或1>> |
标准错误输出(stderr) | 2 | 2>或2>> |
- 2>&1是将标准错误重定向到标准输出
- &:是指在后台运行,当用户退出时,命令也跟着退出
nohup 是no hang up的缩写,就是不挂断的意思,在退出账户时或关闭终端时继续运行进程。
nohup python3 schedule_job_test.py > schedule_job_test.log 2>&1 &
查看后台运行的程序
ps aux | grep schedule_job_test.py
查看日志输出情况
cat schedule_job_test.log | tail -10
六、连接powei bi 部署展示
七、hive数据仓库满足大数据需求
数据仓库是一个面向主题的、集成的、随时间变化的、但信息本身相对稳定的数据集合,用于支持管理决策。
hive是基于hadoop的一个数据仓库工具,可以将结构化的数据映射为一张数据表,并提供类sql查询功能。
hive的本质是将sql语句转换为MapReduce任务运行
(1) hive和关系数据库区别:
Hive | RDBMS | |
---|---|---|
查询语言 | hql | sql |
数据存储位置 | HDFS | Local Fs |
数据执行 | MapReduce | Executor |
执行延迟 | 高 | 低 |
索引 | 0.8版本支持简单索引 | 支持复杂索引 |
数据更新 | 覆盖追加 | 行级别更新删除 |
数据规模 | 大 | 小 |
应用查询 | 海量离线查询 | 实时查询 |
(2) MapReduce概述
其核心思想是“分而治之”,适用于大规模数据处理的场景。
- Map负责“分”,即把复杂的任务分解成若干个“简单的任务”来并行处理,可以拆分的前提是这些小任务可以并行计算,彼此之间独立。
- Reduce负责“合”,即对map阶段的结果进行汇总。
Map-Reduce执行流程:
1.设置InputFormat类,在HDFS文件系统中读取要计算的数据,用split将数据切分,转换成Key-Value(K1和V1)对,其中Key是每一行行首字母的偏移量,Value是每一行数据,输出给Map
2.根据实际业务需求自定义Map逻辑,将第一步的结果转换成另外的Key-Value(K2和V2)对
3.输出给Shuffle
- (1)partition :按照一定的规则对输出的Key-Value对进行分区
- (2)sort:对不同分区的数据按照相同的Key排序
- (3)combiner:在Map端进行局部汇总
4.编写Reduce函数实现自己的逻辑,对输入的Key-Value进行处理,转为新的Key-Value(K3和V3)输出
5.设置OutputFormat处理并保存Rdeuce输出的Key-Value数据。
(3)Sqoop抽取mysql数据导入hive(sqoop_dim_date_df.sh)
- 步骤1:Sqoop与数据库Server通信,获取数据库表的元数据信息;
- 步骤2:Sqoop启动一个Map-Only的MR作业,利用元数据信息并行将数据写入Hadoop。
- 特点:可以指定hdfs路径,指定关系数据库的表,字段,连接数(不压垮数据库),可以导入多个表,支持增量导入(手动指定起始id、事件,或自动记录上次结束位置,自动完成增量导入)
hive -e "drop table if exists jelly.dim_date_df"
sqoop import \
--hive-import \
--connect "jdbc:mysql://106.12.180.xxx:3306/数据库名称?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&tinyInt1isBit=false&dontTrackOpenResources=true&defaultFetchSize=50000&useCursorFetch=true" \
--driver com.mysql.jdbc.Driver \
--username xxxx \ ## 连接mysql的用户名
--password xxxx \ ## 连接mysql的密码
--query \
"select * from dim_date_df where "'$CONDITIONS'" " \
--fetch-size 50000 \ ## 一次从数据库读取 n 个实例,即n条数据
--hive-table jelly.dim_date_df \ ## 创建dim_date_df表(默认也会自动创建表)
--hive-drop-import-delims \ ## 在导入数据到hive时,去掉数据中的\r\n\013\010这样的字符
--delete-target-dir \ ## 如果目标文件已存在就把它删除
--target-dir /user/hadoop/sqoop/dim_date_df \ ## 指定的目录下面并没有数据文件,数据文件被放在了hive的默认/user/hadoop/sqoop/dim_date_df下面
-m 1 ## 迁移过程使用1个map(开启一个线程)
sqoop_ods_customer.sh和sqoop_ods_sales_orders.sh与上述方式相同,实现dim_date_df,ods_customer,ods_sales_orders三张表中的数据导入到hive数据库中。
(4) 数据统计分析与聚合处理
(4.1) 数据统计分析满足日常需求
比如计算每个用户截止到每月为止的最大交易金额和累计到该月的总交易金额
with temp as (select customer_key,substr(create_date,1,7) as month,sum(unit_price) as uamount
from ods_sales_orders
group by customer_key,substr(create_date,1,7))
select customer_key,month,uamount,
max(uamount) over(partition by customer_key order by month) as current_max,
sum(uamount) over(partition by customer_key order by month) as current_sum
from temp limit 20;
(4.2)数据聚合
根据实际业务需要自主分析一些分析指标,接着在hive数据库中来做数据聚合表。
下面是shell脚本的代码是做聚合的HIVESQL语句,利用dw_order_by_day聚合而成的当日维度表,包括当日,昨日,本月,本季度等同比数据。
hive -e "drop table if exists ods.dw_amount_diff_jelly"
hive -e"
CREATE TABLE ods.dw_amount_diff_jelly(
d_amount double,
d_order double,
d_avg double
)
"
hive -e "
insert into ods.dw_amount_diff_jelly
select
a.d_amount,
a.d_order,
a.d_avg
from(
select
((sum(a.sum_amount) / sum(b.sum_amount)) -1) as d_amount,
((sum(a.order_count) / sum(b.order_count)) -1) as d_order,
((sum(a.sum_amount) / sum(a.order_count))/ (sum(b.sum_amount) /sum(b.order_count)) -1) as d_avg
from
(select *,add_months(from_unixtime(unix_timestamp(create_date, 'yyyy-MM-dd'), 'yyyy-MM-dd'), -12) as before_lastyear_date
from ods.dw_order_by_day_jelly
where
is_today =1 or
is_yesterday =1 or
is_current_month=1 or
is_current_quarter=1)
as a
left join ods.dw_order_by_day_jelly as b
on a.before_lastyear_date=b.create_date
group by
a.is_today,
a.is_yesterday,
a.is_current_month,
a.is_current_quarter
# 上述代码计算的是今日/昨日/当月/当季对应的同比增长
(5)Sqoop从hive导出数据到mysql
首先在mysql数据库建立要导出的表;
CREATE TABLE `dw_amount_diff` (
`today_diff` double DEFAULT NULL,
`yesterday_diff` double DEFAULT NULL,
`month_diff` double DEFAULT NULL,
`quarter_diff` double DEFAULT NULL,
`year_diff` double DEFAULT NULL,
`flag` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
开始进行sqoop从hive数据库抽取数据到mysql数据库;
sqoop export \
--connect "jdbc:mysql://106.15.xxx.232:3306/数据库名称" \
--username ####\ ##数据库账号
--password #### \##数据库密码
--table dw_amount_diff \ ##mysql数据库建好的表
--export-dir /user/hive/warehouse/ods.db/dw_amount_diff \ #hive数据库数据路径
--input-null-string "\\\\N" \
--input-null-non-string "\\\\N" \
--input-fields-terminated-by "\001" \
--input-lines-terminated-by "\\n" \
-m 1