Hive数仓模型

Bruce_xgh

1204人浏览 · 2024-05-09 14:11:48

Bruce_xgh · 2024-05-09 14:11:48 发布

Hive数仓模型

1、hive数据分层

ODS层——原始数据

来自业务库、埋点日志、消息队列等

业务库：sqoop定时抽取数据；实时方面考虑使用canal监听mysql的binlog日志，实时接入即可
埋点日志：日志一般是以文件的形式保存，可以选择使用flume来定时同步；可以使用spark streaming或者Flink、Kafka来实时接入
消息队列：来自ActiveMQ、Kafka的数据等

DWD层——数据细节层

进行数据清洗和规范化操作；去空、脏数据，离群值等，数据清洗。

DWM层——数据中间层【中间表】

在DWD的基础上进行轻微聚合操作，算出相应的统计指标

DWS——数据服务层

该层是基于DWM上的基础数据，整合汇总成分析某一个主题域的数据服务层，一般是宽表，用于提供后续的业务查询，OLAP分析，数据分发等。

ADS层——数据应用层

该层主要是提供给数据产品和数据分析使用的数据，一般会存放在ES、Redis、PostgreSql等系统中供线上系统使用；也可能存放在hive或者Druid中，供数据分析和数据挖掘使用，比如常用的数据报表就是存在这里的。

事实表：系统日志、销售记录等，事实记录的表的记录会不断增长。
维度表：与事实表对应的一种表，例如日期表、地点表。

2、数仓过程

HIve是什么

Hive是Hadoop生态的数据仓库工具；Hive将存储在HDFS上的文件映射为关系表；通过解析开发者提交的SQL语句，将SQL转换成MR任务，提交到Hadoop执行；Hive提供了命令行客户端和JDBC。

一、采集日志 Flume

# 数据输出打印到logger控制台
a1.sinks.k1.type = hdfs
a1.sinks.k1.hdfs.path = hdfs://xgh0791:8020/flume/events/%Y-%m-%d
a1.sinks.k1.hdfs.useLocalTimeStamp = true

a1.sinks.k1.hdfs.fileType = DataStream

a1.sinks.k1.hdfs.writeFormat = Text
# 流速
a1.sinks.k1.hdfs.rollInterval = 100

a1.sinks.k1.hdfs.rollSize = 1340000000
# Bind the source and sink to the channel
a1.sinks.k1.hdfs.rollCount = 0
a1.sinks.k1.hdfs.filePrefix = shopping_data

a1.sinks.k1.hdfs.fileSuffix = .txt
a1.channels.c1.type = memory
a1.channels.c1.capacity = 1000

a1.channels.c1.transactionCapacity = 100
# Bind the source and sink to the channel
a1.sources.r1.channels = c1
a1.sinks.k1.channel = c1

二、数据清洗

2.1 建立源数据表ODS[单列]

-- 创建ods层 单列
-- 创建表
create external table meituan
(
    line string
)
    -- 默认路径 user/hive/warehouse
    location 'hdfs://xgh0791:8020/flume/events/2024-05-06';

2.2 建立数据明细层DWD层[多列]

-- 创建dwd层
-- 表的格式化  字段
create external table if not exists dp_dwd
(
    id              int,    --店铺id 0
    province        STRING, --省份   1
    city            STRING, --城市   2
    name            STRING, --店铺名字 3
    address         STRING, --店铺地址 4
    phone           STRING, --电话    5
    category        STRING, --食物的类型 8
    score           STRING, --店铺评分   9
    month_sales     int,    --月销量   11
    avg_price       string, --人均价格 12
    min_price       string, --起送费   13
    menu_categories string, --菜单种类  22
    updated         date    --更新的时间 24
);

-- 插入数据
insert overwrite table dp_dwd
select arr[0]                                                         id,
       arr[1]                                                         province,
       arr[2]                                                         city,
       arr[3]                                                         name,
       arr[4]                                                         address,
       arr[5]                                                         phone,
       arr[8]                                                         category,
       arr[9]                                                         score,
       cast(regexp_replace(substr(arr[11], 3), '[^0-9]+', '') as int) month_sales,
       arr[12]                                                        avg_price,
       arr[13]                                                        min_price,
       arr[22]                                                        menu_categories,
       arr[24]                                                        updated
from (select split(line, "\t") arr from meituan) t;

-- 删除原第一行
alter table dp_dwd set tblproperties ('skip.header.line.count'='1');

2.3 DWM数据中间层【多表】

该层是在DWD层的数据基础上，对数据做一些轻微的聚合操作，生成一些列的中间结果表，提升公共指标的复用性，减少重复加工的工作。

2.4 DWS 数据服务层【宽表层】

-- 创建dws层
-- 查询每个省份的餐馆月销量最高的餐馆
SELECT province,
       name,
       month_sales
FROM (SELECT province,
             name,
             month_sales,
             RANK() OVER (PARTITION BY province ORDER BY month_sales DESC) as rank
      FROM dp_dwd) ranked
WHERE rank = 1;

2.5 ADS层数据应用层

该层主要是提供给数据产品和数据分析使用的数据，一般会存放在ES、Redis、PostgreSql等系统中供线上系统使用；也可能存放在hive或者Druid中，供数据分析和数据挖掘使用，比如常用的数据报表就是存在这里的。

3、Hive函数

-- 查看函数的详细用法(包含案例)
desc function extended substr;

常用的函数

空字段赋值
case when then else end
行转列
列转行
窗口函数（开窗函数）

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。

CURRENT ROW：当前行

n PRECEDING：往前n行数据

n FOLLOWING：往后n行数据

UNBOUNDED：无边界

UNBOUNDED PRECEDING 前无边界，表示从前面的起点，

UNBOUNDED FOLLOWING后无边界，表示到后面的终点

LAG(col,n,default_val)：往前第n行数据

LEAD(col,n, default_val)：往后第n行数据

FIRST_VALUE (col,true/false)：当前窗口下的第一个值，第二个参数为true，跳过空值

LAST_VALUE (col,true/false)：当前窗口下的最后一个值，第二个参数为true，跳过空值

NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

4、Hive类型

基本类型

Hive数据类型	Java数据类型	长度	例子
TINYINT	byte	1byte有符号整数	20
SMALLINT	short	2byte有符号整数	20
INT	int	4byte有符号整数	20
BIGINT	long	8byte有符号整数	20
BOOLEAN	boolean	布尔类型，true或者false	TRUE FALSE
FLOAT	float	单精度浮点数	3.14159
DOUBLE	double	双精度浮点数	3.14159
STRING	string	字符系列。可以指定字符集。可以使用单引号或者双引号。	‘now is the time’ “for all good men”
TIMESTAMP	-	时间类型	-
BINARY	-	字节数组	-

对于Hive的String类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数。

集合类型

数据类型	描述	语法示例
STRUCT	和c语言中的struct类似，都可以通过“点”符号访问元素内容。例如，如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段.first来引用。	struct()
MAP	MAP是一组键-值对元组集合，使用数组表示法可以访问数据。例如，如果某个列的数据类型是MAP，其中键->值对是’first’->’John’和’last’->’Doe’，那么可以通过字段名[‘last’]获取最后一个元素	map()
ARRAY	数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素，每个数组元素都有一个编号，编号从零开始。例如，数组值为[‘John’, ‘Doe’]，那么第2个元素可以通过数组名[1]进行引用。	Array()

Hive有三种复杂数据类型ARRAY、MAP 和 STRUCT。ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。

类型转换

Hive的原子数据类型是可以进行隐式转换的，类似于Java的类型转换，例如某表达式使用INT类型，TINYINT会自动转换为INT类型，但是Hive不会进行反向转化，例如，某表达式使用TINYINT类型，INT不会自动转换为TINYINT类型，它会返回错误，除非使用CAST 操作。
1. 隐式类型转换规则如下。
2. 任何整数类型都可以隐式地转换为一个范围更广的类型，如TINYINT可以转换成INT，INT可以转换成BIGINT。
3. 所有整数类型、FLOAT和STRING类型都可以隐式地转换成DOUBLE。
4. TINYINT、SMALLINT、INT都可以转换为FLOAT。
5. BOOLEAN类型不可以转换为任何其它的类型。
6. 可以使用CAST操作显示进行数据类型转换，例如CAST(‘1’ AS INT)将把字符串’1’ 转换成整数1；如果强制类型转换失败，如执行CAST(‘X’ AS INT)，表达式返回空值 NULL。