离线数仓ODS层准备

TTXS123456789ABC

已于 2024-09-15 21:14:35 修改

阅读量819

点赞数 19

分类专栏： XM2-离线数仓-金融零售文章标签：数据仓库

于 2024-09-15 20:56:54 首次发布

本文链接：https://blog.youkuaiyun.com/qq_45972323/article/details/136383247

版权

离线数仓ODS层设计-Operational Data Store

ODS层设计要点
ODS层-日志表-json表

ODS层设计要点

（1）ODS层的表结构设计依托于从业务系统同步过来的数据结构。
（2）ODS层要保存全部历史数据，故其压缩格式应选择压缩比较高的，此处选择gzip。
（3）ODS层表名的命名规范为：ods_表名_单分区增量全量标识（inc/full）。
电商数据主要分为两类：db（业务）和 log（日志）
在这里插入图片描述
查看数据
[atguigu@hadoop102 ~]$ hadoop fs -cat /origin_data/gmall/log/topic_log/2020-06-14/* | zcat

[atguigu@hadoop102 ~]$ hadoop fs -cat /origin_data/gmall/db/activity_info_full/2020-06-14/* | zcat

ODS层-日志表-json表

链接: hive 语法
链接: hive 建表语法
在这里插入图片描述
搜 json 相关内容

# 根据SERDE分隔字符 
# 声明文件格式为 TEXTFILE文本格式 （默认也是TEXTFILE文本格式 ）
CREATE TABLE my_table(a string, b bigint, ...)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS TEXTFILE;

SERDE

在这里插入图片描述
每一个hive语句都会有 SERDE INPUTFORMAT OUTPUTFORMAT ，只不过已经简化了。

hive导入json表


//根据json的类型，对应的设计表，key的名，value的类型 要对应。根据对应的key值找，字段的顺序不重要。
//location '/person';   导入/person 根目录下的person文件
--{
   "id":1001,"name":"zhangsan"}
CREATE TABLE person(id int,name string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
location '/person';

在这里插入图片描述

要在hadoop102里面load，如果在datagrip里面load它的导入是在本地（windows）里面导入的。
启动hive，load 文件person离得json字符。

[atguigu@hadoop102 ~]$ hive
which: no hbase in (/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/opt/module/jdk/bin:/opt/module/hadoop/bin:/opt/module/hadoop/sbin:/opt/module/kafka/bin:/opt/module/hive/bin:/opt/module/spark/bin:/home/atguigu/.local/bin:/home/atguigu/bin)
Hive Session ID = 6934327b-c3f1-4797-9de4-8b02cd6537a6

Logging initialized using configuration in file:/opt/module/hive/conf/hive-log4j2.properties Async: true
Hive Session ID = e04a92c0-c7c5-445e-8eda-04b57b016d86
hive (default)> use gmall;
OK
Time taken: 0.042 seconds
hive (gmall)> load data local inpath '/home/atguigu/person' into table person;
Loading data to table gmall.person
OK
Time taken: 0.628 seconds
hive (gmall)>

hdfs上的根目录可以找到person文件。
在datagrip里面链接hadoop102的hive，查看导入的数据。

（hive表和json表字段不一致解决方案）

修改内容
将字段名 name 改为 name1
在这里插入图片描述
重新load 文件person

hive (gmall)> load data local inpath '/home/atguigu/person' overwrite into table person;
Loading data to table gmall.person
OK
Time taken: 0.192 seconds
hive (gmall)>

重新查询person表
在这里插入图片描述
字段有就插入，没有就直接null填充。

复杂数据类型

hive 中有简单数据类型、基本数据类型、复杂数据类型。
没有嵌套结构的用基本数据类型
嵌套结构的用复杂数据类型(array map struct) 存储
在这里插入图片描述
链接: hive UDFs

在这里插入图片描述
复杂类型构造器

udf: 一进一出
udaf：多进一出
udtf：一进多出 (一和多指的是行)
hive读数据是一行一行读取的，所以保存的json必须是一行一行完整的。

array ：
1.定义：array < string >
2.取值：arr[0]
3.构造(如何通过一个select语句得到array):
array(val1, val2, …)–udf
split()–udf 字符串拆成数组
collect_set()–udaf

map : 结构中所有的数据类型都要是一样的。
1.定义: map< string ,bigint>
2.取值：map[key]
3.构造:
map(key1, value1, key2, value2, …)
str_to_map(text[, delimiter1, delimiter2]) 字符串拆成map

struct结构体：多个字段，每个字段都有他的字段名和字段类型。
1.定义: struct< id:int ,name:string>
2.取值：struct.id
3.构造:
struct(val1, val2, val3, …) Struct field names will be col1, col2, …
named_struct(name1, val1, name2, val2, …)

嵌套数据类型：array < struct< id:int ,name:string> >

{
   "id":1001, "name":"zhangsan",
"hobby":["reading","coding","smoking"],
"address":{
   "country":"China","city":"Beijing"}
}


drop table if exists person;
CREATE TABLE person(
    id int,
    name string,
    hobby array<string>,
    address struct<country:string,city:string>
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
location '/person';

select * from person;
select hobby[0] from person;
select
    address.country
from person;

日志表-建表语句

创建的日志表，要兼容页面日志和启动日志。如何设计？
在这里插入图片描述
6个页面日志字段 + 启动日志多余的1个字段start = 7个字段
加载页面日志时，启动日志对应的字段为空。
加载启动日志时，页面日志对应的字段为空。

日志表

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/';

在这里插入图片描述

创建日志表


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/';

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