快餐项目4--sgg

一 数仓库开发之ODS层(创建事实表)

ODS层设计的要点如下:

• ODS层的表结构设计依托于从业务系统同步过来的数据结构.
• ODS层要保存全部历史数据,顾其压缩格式应选择压缩比较高的gzip.
• ODS层表名要命名规范:ods_表名_单分区增量全量表示(inc,full)

  ODS层的数据是通过Maxwell或者dataX同步到hdfs上的.

构建ODS层本质上就是将datax和maxwell采集到hdfs上的数据写入到hive的表中,由于不同的同步方式将数据保存在hdfs上的数据格式不同,所以创建hive表的时候,需要根据不同的类型数据去构建相应的表.相当于给该数据建表.

数据采集回顾:

数据采集有两种方法,全量采集和增量采集,他们会把数据以不同的形式(特定分隔符分割,或者json文件)

 全量数据是使用datax将数据从mysql数据库导入到hdfs上的,他在hdfs上保存的形式是文本形式,如下图:

所以在构建全量表的时候,要根据对应的数据类型来构建表.

所以 在ods层建表相当于根据对应的数据去建表.

# 全量表建表语句
create external table ods_product_categroy_full(
id bigint,
create_time string,
updata_time string,
description string,
name string) 
partition by (dt string)
row format delimited fields terminated by "\t"  # 之前使用datax导入数据的时候是"\t"分割
# mysql表里可能会有null值,但是datax同步过来的时候,存在hdfs上是空,所以这里设置将数据导入到hive的时候转换回null.

null defined as ''
LOCATION '/warehouse/fast_food/ods/ods_shop_full'
tblproperties ('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');  # 使用gzip压缩.

增量数据使用的是maxwell将数据同步到kafka上的,在hdfs上保存的格式是json形式,如下图所示.

1.json类型的数据建表,要用到hive的复杂数据类型.

• 数组:array<元素类型>

create table if not exists t2(
person string,
friends array<string>  # 创建数组对象 array<元素1,元素2>
)
row format delimited 
fields terminated by '|'  # friend字段和person字段以|分割.
collection items terminated by ',';   # friend中的数组元素以,分割

• map对象:map<k的类型,v的类型>

create table if not exists t3(
person string,
address map<string, string>
)
row format delimited 
fields terminated by '|'   # 字段之间用|分割
collection items terminated by ','  # map元素之间用,分割
map keys terminated by ':';   # k,v之间用:分割


tom|province:guangdong,city:shenzhen
jerry|province:jiangsu,city:nanjing

• struct对象:struct<属性名:属性类型....>

create table if not exists t4(
person string,
info struct<age:int, city:string>  # 创建struct对象 struct<属性名1,属性名2>
)
row format delimited 
fields terminated by '|'
collection items terminated by ',';  # info内部多个元素之间以,分割.

json数据建表:

给上图的json建表

# 增量表建表
# json建表的字段只需要一级属性:id,name,dept,xq,其中dept里边的id,name不是一级字段.
create external table persion_inc(
id bigint,
name string,
dept struct<id:string,name:string>
xq array<string>
)
# 这里不需要指定分割符,因为json数据里边没有分隔符
partition by ('dt' string)
sorted as jsonfile
LOCATION '/warehouse/fast_food/ods/ods_payment_inc'
tblpropertites('compression.codec'='org.apache.hadoop.io.compress.GzipCodec');

load data inpath '/test.txt' overwrite into table persion_inc; # 上传数据.

二 数仓开发之DIM层(创建维度表--1全量快照表|拉链表)

DIM层的设计要点:

• DIM层的设计依据是维度建模理论,该层存储维度模型的维度表.(在设计事实表时,已经确定了与每个事实表相关的维度,理论上每个相关维度均需对应一张维度表.)
• DIM的数据存储格式为orc列式存储+snappy压缩.(gzip的速度比较慢)
• DIM层表的命名规范为dim_表名_全量表或者拉链表标识(full/zip)

全量表按日期分区,那么每个分区都会保存一份全量数据,因为全量表是每天同步一次.

拉链表没有冗余数据

 怎样创建维度表?创建几张维度表?

根据任务总线矩阵来定.

这里创建6个维度表. 

顾客:数据量大,改变频率不高(这里指顾客的信息)-->拉链表

注意,顾客表在ods层上就是增量表.

1.首先先在dim目录下创建拉链表。

DROP TABLE IF EXISTS dim_customer_zip;
CREATE EXTERNAL TABLE IF NOT EXISTS dim_customer_zip
(
    `id`           STRING COMMENT '顾客ID',
    `phone_number` STRING COMMENT '手机号',
    `username`     STRING COMMENT '用户名',
    `start_date`   STRING COMMENT '起始日期',
    `end_date`     STRING COMMENT '结束日期'
) COMMENT '顾客维度表'
    PARTITIONED BY (`dt` STRING COMMENT '分区')
    STORED AS ORC
    LOCATION '/warehouse/fast_food/dim/dim_customer_zip'
    TBLPROPERTIES ('orc.compress' = 'snappy');

• start_date和end_date是拉链表的日期字段.
• 使用orc列式存储(使用该方式存储数据,不可以使用load方式进行导入数据,只有filetext存储才可以使用load方式导入数据)
• 拉链表为了方便查询,设计两个分区:一个是9999-12-31分区(存放所有用户最新状态数据)),一个是普通日期分区.(存放下一日失效的数据,当日还在生效,下一日改了不生效)
• 注意:首日要同步mysql表的所有数据,mysql表中的数据是最新数据,所以首日同步的数据应该放在9999-12-31分区.

2.将ods层的数据载入到拉链表里。

insert into|overwrite orc_table partition by (dt='9999-12-31)
select ... from ...where...