8.数据仓库之ODS层搭建

最新推荐文章于 2024-05-07 20:03:23 发布

Mmj666

最新推荐文章于 2024-05-07 20:03:23 发布

阅读量1.4k

点赞数

分类专栏：东软睿购跨境电商数仓项目文章标签：数据仓库

本文链接：https://blog.csdn.net/Mmj666/article/details/125785538

版权

本文详细介绍了数据仓库ODS层的搭建过程，包括日志表和业务表的设计，如日志表建表语句、装载脚本，以及全量和增量表的构建。ODS层保留全部历史数据，采用gzip压缩。每日通过脚本自动装载数据，确保数据仓库的实时更新。

摘要由CSDN通过智能技术生成

数据仓库之ODS层搭建

我们本项目中对数据仓库每层的搭建主要分为两部分，第一部分是确定都有哪些表，第二部分是确定数据装载的方式。

我们在进行ODS层搭建时，需要明确以下几点：

1）ODS层的表结构设计依托于从业务系统同步过来的数据结构。

2）ODS层要保存全部历史数据，故其压缩格式应选择压缩比较高的，此处选择gzip。

3）ODS层表名的命名规范为：ods_表名_单分区增量全量标识（inc/full）。

我们在进行数据同步时，同步到的用户行为日志数据当中是json字符串格式；增量表是使用Maxwell进行同步的，也是json字符串格式；全量表使用的是DataX同步的，同步到的数据是tsv格式的。因此，我们在进行ODS层表结构设计时，需要考虑到这一点。

1.日志表设计

我们一共有两种方案，第一种方案是在建表的时候只有一个字段，一行存放的是一个json字符串，我们获取对应数据时，可以通过get_json_object()函数，从该字符串中取出对应的字段的信息；第二种方案是我们直接构建一张json表，这种表中的字段和json字符串当中的字段一一对应，这样就可以方便我们之后使用数据了。我们最终选择的方案是构建json表

1.1日志表建表语句

我们去官网查看建json表的语法：
在这里插入图片描述

我们结合行为数据中json的格式，最终构建的日志表建表语句如下所示：

DROP TABLE IF EXISTS ods_log_inc;
CREATE EXTERNAL TABLE ods_log_inc
(
    `common`   STRUCT<ar :STRING,ba :STRING,ch :STRING,is_new :STRING,md :STRING,MID :STRING,os :STRING,uid :STRING,vc
                      :STRING> COMMENT '公共信息',
    `page`     STRUCT<during_time :STRING,item :STRING,item_type :STRING,last_page_id :STRING,page_id
                      :STRING,source_type :STRING> COMMENT '页面信息',
    `actions`  ARRAY<STRUCT<action_id:STRING,item:STRING,item_type:STRING,ts:BIGINT>> COMMENT '动作信息',
    `displays` ARRAY<STRUCT<display_type :STRING,item :STRING,item_type :STRING,`order` :STRING,pos_id
                            :STRING>> COMMENT '曝光信息',
    `start`    STRUCT<entry :STRING,loading_time :BIGINT,open_ad_id :BIGINT,open_ad_ms :BIGINT,open_ad_skip_ms
                      :BIGINT> COMMENT '启动信息',
    `err`      STRUCT<error_code:BIGINT,msg:STRING> COMMENT '错误信息',
    `ts`       BIGINT  COMMENT '时间戳'
) COMMENT '活动信息表'
    PARTITIONED BY (`dt` STRING)
    ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
    LOCATION '/warehouse/gmall/ods/ods_log_inc/';

我们可以看到上述建表语句中没有写压缩格式，但是我们在传输到HDFS上时使用的是gzip压缩格式，这是因为hive可以自动识别出我们的gzip压缩格式。
在这里插入图片描述

如下图所示，我们建表成功：
在这里插入图片描述

1.2日志表装载语句

我们接下来进行日志表装载语句的编写，我们将hdfs上的数据装载到对应日期分区的表当中：

--数据装载
load data inpath '/origin_data/gmall/log/topic_log/2022-05-01' into table ods_log_inc partition(dt='2022-05-01');

但是该条语句只能将2022-05-01日的数据装载进表当中,因此我们需要编写一个每日装载脚本，用于每天装载日志表数据：

（1）在hadoop102的/home/hadoop/bin目录下创建hdfs_to_ods_log.sh

[root@hadoop102 bin]$ vim hdfs_to_ods_log.sh

（2）我们在该脚本中编写如下内容：

#!/bin/bash

# 定义变量方便修改
APP=gmall

# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$1" ] ;then
   do_date=$1
else
   do_date=`date -d "-1 day" +%F`
fi

echo ================== 日志日期为 $do_date ==================
sql="
load data inpath '/origin_data/$APP/log/topic_log/$do_date' into table ${APP}.ods_log_inc partition(dt='$do_date');
"
hive -e "$sql"

（3）给该脚本增加权限

[root@hadoop102 bin]$ chmod +x hdfs_to_ods_log.sh

（4）我们每天只需要执行该脚本，即可完成日志数据的装载，下图是装载2022/5/1的数据

[root@hadoop102 bin]$ hdfs_to_ods_log.sh 2022-05-01

在这里插入图片描述

2.业务表设计

我们全量表的设计应当与Mysql业务系统当中对应表的字段保持一致，另外，我们需要设置分区，每天将数据装载到对应的分区当中。

对于增量表的设计，我们首先查看增量表中数据的格式：

在这里插入图片描述

我们可以看到，由于我们使用的是Maxwell进行同步，因此我们同步过来的数据是以json字符串的形式存储的，我们对于增量表，也是建立json表。我们最终留下的字段有type，ts以及data。

2.1活动信息表（全量表）设计

活动信息表的建表语句如下所示：

DROP TABLE IF EXISTS ods_activity_info_full;
CREATE EXTERNAL TABLE ods_activity_info_full
(
    `id`            STRING COMMENT '活动id',
    `activity_name` STRING COMMENT '活动名称',
    `activity_type` STRING COMMENT '活动类型',
    `activity_desc` STRING COMMENT '活动描述',
    `start_time`    STRING COMMENT '开始时间',
    `end_time`      STRING COMMENT '结束时间',
    `create_time`   STRING COMMENT '创建时间'
) COMMENT '活动信息表'
    PARTITIONED BY (`dt` STRING)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    NULL DEFINED AS ''
    LOCATION '/warehouse/gmall/ods/ods_activity_info_full/';

我们将hive表中null值的存储格式设置为’‘空字符串。因为我们使用DataX将数据从mysql导到HDFS上时，DataX会将Mysql当中的空值存储成’'空字符串形式。我们为了保证hive可以正确识别空值，因此我们在此处将Hive的NULL定义为空字符串。

（Hive中默认空值格式：‘\N’，Mysql中默认空值存储格式：null，datax会把null值存为’'）

2.2活动规则表（全量表）设计

DROP TABLE IF EXISTS ods_activity_rule_full;
CREATE EXTERNAL TABLE ods_activity_rule_full
(
    `id`               STRING COMMENT '编号',
    `activity_id`      STRING COMMENT '类型',
    `activity_type`    STRING COMMENT '活动类型',
    `condition_amount` DECIMAL(16, 2) COMMENT '满减金额',
    `condition_num`    BIGINT COMMENT '满减件数',
    `benefit_amount`   DECIMAL(16, 2) COMMENT '优惠金额',
    `benefit_discount` DECIMAL(16, 2) COMMENT '优惠折扣',
    `benefit_level`    STRING COMMENT '优惠级别'
) COMMENT '活动规则表'
    PARTITIONED BY (`dt` STRING)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    NULL DEFINED AS ''
    LOCATION '/warehouse/gmall/ods/ods_activity_rule_full/';

2.3一级品类表（全量表）设计

DROP TABLE IF EXISTS ods_base_category1_full;
CREATE EXTERNAL TABLE ods_base_category1_full
(
    `id`   STRING COMMENT '编号',
    `name` STRING COMMENT '分类名称'
) COMMENT '一级品类表'
    PARTITIONED BY (`dt` STRING)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    NULL DEFINED AS ''
    LOCATION '/warehouse/gmall/ods/ods_base_category1_full/';

2.4二级品类表（全量表）设计

DROP TABLE IF EXISTS ods_base_category2_full;
CREATE EXTERNAL TABLE ods_base_category2_full
(
    `id`           STRING COMMENT '编号',
    `name`         STRING COMMENT '二级分类名称',
    `category1_id` STRING COMMENT '一级分类编号'
) COMMENT '二级品类表'
    PARTITIONED BY (`dt` STRING)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    NULL DEFINED AS ''
    LOCATION '/warehouse/gmall/ods/ods_base_category2_full/';

2.5三级品类表（全量表）设计

DROP TABLE IF EXISTS ods_base_category3_full;
CREATE EXTERNAL TABLE ods_base_category3_full
(
    `id`           STRING COMMENT '编号',
    `name`         STRING COMMENT '三级分类名称',
    `category2_id` STRING COMMENT '二级分类编号'
) COMMENT '三级品类表'
    PARTITIONED BY (`dt` STRING)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY