数仓项目流程简单梳理

数仓搭建简易案例

一、源数据构建

此处为模拟方法，实际生产中不会用到

1、通过Java和Python生成模拟日志

JAVA：创建文件名；根据初始和截止日期算出要写多少个文件；for循环内用流写文件；写出；关闭流

• 使用RandomAccessFile流，随机读写；
• 使用Calendar和Date日期类配合使用，解决"日期+1"的问题；
• 注意RandomAccessFile写出时的换行和中文乱码；

Python：和java思路一致

• with open as
• json.dump
  参考文章：使用python、Java简单模拟日志输出

2、使用Excel模拟维度字段

方方格子、Char()、rand()函数

3、使用pymysql直接往MySQL里写数据

把行数据写在元组里，装到list里然后调用executemany方法传入sql和list

一次写入过多时，会造成内存溢出，pymysql是一次性写入然后commit

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
import datetime
import random

db = pymysql.connect(host="192.168.221.205",user="root",password="root",database="myshops")
cursor = db.cursor()

data_orderitems=list()
for i in range(1000):
    info = (i,i%4+1,1+random.randint(1,5000),random.randint(1,10))
    data_orderitems.append(info)

def insert_orderitems():
    tm = datetime.datetime.now()
    sql="insert into orderitems values (%s,%s,%s,%s)"
    cursor.executemany(sql, data_orderitems)
    db.commit()
    print(datetime.datetime.now()-tm)

if __name__ == '__main__':
    insert_orderitems()

4、使用jdbc往MySQL里写数据

单例模式，写一个实体类，创建对象，加到list里。

使用addbatch方法，在url上添加rewriteBatchedStatements=true参数，设置autocommit为false，然后每addbatch达到一定数量时手动提交一次，此方法效率更高，因为从一次insert提交变为自定义的几万条提交一次
参考文章：JDBC单例模式-insert海量数据时的效率优化

二、使用flume从Tomcat读取数据到hdfs上

source：spoolDir，指定文件夹目录

channel：file，需要设置检查点和文件存储目录

sink：hdfs，文件输出路径，可顺便使用时间正则自动生成；设置文件前缀；是否使用时间戳；事务批处理大小；文件类型；创建新文件的条件设置，大小、时间、数量三种模式；指定block副本数量。

执行flume命令：flume-ng

参考文章：flume日志收集

三、构建ODS层数据：创表脚本，导入脚本

1、写创建ods层表的脚本，然后另一个Sqoop脚本写执行hive脚本的命令以及从MySQL导入到Hive表的脚本

参考文章：实现MySQL数据全量迁移至Hive的简单脚本

2、使用pyspark将数据进行格式转换，初步清洗

• SparkSession.build.master("").appName().enableHiveSupport.getOrCreatr
• get_json_object()、json_tuple、when、instr
• saveAsTable

参考文章：pyspark读取hive表解析json日志并写入hive表的简单案例

四、DWD层构建

基于ods层数据，创建常见维度表，分区表，存储格式为orc高度压缩

ODS层数据是近源层，基本上没做过大的格式或数据转换（一般仅对数据进行格式上的转换，比如说对json、URL等格式的日志文件，提取出列数据），因此在DWD层，我们会把数据做一定的整理和优化，并加入一些常用维度，比如说维度、地域等

• 使用shell脚本生成日期维度表

参考文章：DWD层构建思路

五、根据需求，构建DWS层

日活、登陆情况、10日均线等，然可以用create as建表导入到DWS层

此层用hive窗口函数会比较多

--dws
drop database if exists dws_myshops cascade;
create database dws_myshops;
use dws_myshops;

-- 创建用户每日消费情况的轻聚合表
create table dws_userconsume as
select cd.fdate,cd.userid,nvl(cp.ordernum,0) ordernum,nvl(cp.cprice,0) cprice
from (
select u.userid,dt.* 
from (
select userid 
from dwd_myshops.dwd_orders 
group by userid
)u
cross join dwd_myshops.dwd_dates dt
)cd
left join (
select c.userid,count(distinct c.orderno) ordernum,c.orderdate,sum(c.saleprice) cprice 
from (
select o.orderdate,o.userid,o.orderstatus,g.goodid,o.orderno,(o.buynum*g.price) saleprice
from dwd_myshops.dwd_orders o 
inner join dwd_myshops.dwd_goods g on o.goodid=g.goodid
)c
group by c.userid,c.orderdate
) cp on cd.userid=cp.userid and cp.orderdate=cd.fdate;

-- 用户在15天内的消费情况
select fdate,userid,ordernum,cprice,
avg(cprice) over(distribute by userid sort by fdate rows between 14 preceding and current row) avgper15
from dws_myshops.dws_userconsume
where fdate>='2020-11-01';

六、把DWS层数据导入到MySQL

sqoop export的方式

create database mydemo;
use mydemo;
drop table if exists userconsume;
create table userconsume(
fdate varchar(50),
userid varchar(50),
ordernum varchar(50),
cprice varchar(50)
);

sqoop export \
--connect jdbc:mysql://192.168.221.200:3306/mydemo \
--username root \
--password kb10 \
--table userconsume \
--input-fields-terminated-by '\001' \
--fields-terminated-by ',' \
--export-dir /opt/software/hadoop/hive110/warehouse/dws_myshops.db/dws_userconsume