柚子快報(bào)激活碼778899分享：數(shù)據(jù)倉庫 Hive數(shù)倉模型

http://yzkb.51969.com/

Hive數(shù)倉模型

1、hive數(shù)據(jù)分層

ODS層——原始數(shù)據(jù)

來自業(yè)務(wù)庫、埋點(diǎn)日志、消息隊(duì)列等

業(yè)務(wù)庫：sqoop定時(shí)抽取數(shù)據(jù)；實(shí)時(shí)方面考慮使用canal監(jiān)聽mysql的binlog日志，實(shí)時(shí)接入即可埋點(diǎn)日志：日志一般是以文件的形式保存，可以選擇使用flume來定時(shí)同步；可以使用spark streaming或者Flink、Kafka來實(shí)時(shí)接入消息隊(duì)列：來自ActiveMQ、Kafka的數(shù)據(jù)等

DWD層——數(shù)據(jù)細(xì)節(jié)層

進(jìn)行數(shù)據(jù)清洗和規(guī)范化操作；去空、臟數(shù)據(jù)，離群值等，數(shù)據(jù)清洗。

DWM層——數(shù)據(jù)中間層【中間表】

在DWD的基礎(chǔ)上進(jìn)行輕微聚合操作，算出相應(yīng)的統(tǒng)計(jì)指標(biāo)

DWS——數(shù)據(jù)服務(wù)層

該層是基于DWM上的基礎(chǔ)數(shù)據(jù)，整合匯總成分析某一個(gè)主題域的數(shù)據(jù)服務(wù)層，一般是寬表，用于提供后續(xù)的業(yè)務(wù)查詢，OLAP分析，數(shù)據(jù)分發(fā)等。

ADS層——數(shù)據(jù)應(yīng)用層

該層主要是提供給數(shù)據(jù)產(chǎn)品和數(shù)據(jù)分析使用的數(shù)據(jù)，一般會(huì)存放在ES、Redis、PostgreSql等系統(tǒng)中供線上系統(tǒng)使用；也可能存放在hive或者Druid中，供數(shù)據(jù)分析和數(shù)據(jù)挖掘使用，比如常用的數(shù)據(jù)報(bào)表就是存在這里的。

事實(shí)表：系統(tǒng)日志、銷售記錄等，事實(shí)記錄的表的記錄會(huì)不斷增長。 維度表：與事實(shí)表對(duì)應(yīng)的一種 表，例如日期表、地點(diǎn)表。

2、數(shù)倉過程

HIve是什么

Hive是Hadoop生態(tài)的數(shù)據(jù)倉庫工具；Hive將存儲(chǔ)在HDFS上的文件映射為關(guān)系表；通過解析開發(fā)者提交的SQL語句，將SQL轉(zhuǎn)換成MR任務(wù)，提交到Hadoop執(zhí)行；Hive提供了命令行客戶端和JDBC。

一、采集日志 Flume

# 數(shù)據(jù)輸出打印到logger控制臺(tái)

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

二、數(shù)據(jù)清洗

2.1 建立源數(shù)據(jù)表ODS[單列]

-- 創(chuàng)建ods層 單列

-- 創(chuàng)建表

create external table meituan

(

line string

)

-- 默認(rèn)路徑 user/hive/warehouse

location 'hdfs://xgh0791:8020/flume/events/2024-05-06';

2.2 建立數(shù)據(jù)明細(xì)層DWD層[多列]

-- 創(chuàng)建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, --店鋪評(píng)分 9

month_sales int, --月銷量 11

avg_price string, --人均價(jià)格 12

min_price string, --起送費(fèi) 13

menu_categories string, --菜單種類 22

updated date --更新的時(shí)間 24

);

-- 插入數(shù)據(jù)

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數(shù)據(jù)中間層【多表】

該層是在DWD層的數(shù)據(jù)基礎(chǔ)上，對(duì)數(shù)據(jù)做一些輕微的聚合操作，生成一些列的中間結(jié)果表，提升公共指標(biāo)的復(fù)用性，減少重復(fù)加工的工作。

2.4 DWS 數(shù)據(jù)服務(wù)層【寬表層】

-- 創(chuàng)建dws層

-- 查詢每個(gè)省份的餐館月銷量最高的餐館

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層 數(shù)據(jù)應(yīng)用層

該層主要是提供給數(shù)據(jù)產(chǎn)品和數(shù)據(jù)分析使用的數(shù)據(jù)，一般會(huì)存放在ES、Redis、PostgreSql等系統(tǒng)中供線上系統(tǒng)使用；也可能存放在hive或者Druid中，供數(shù)據(jù)分析和數(shù)據(jù)挖掘使用，比如常用的數(shù)據(jù)報(bào)表就是存在這里的。

3、Hive函數(shù)

-- 查看函數(shù)的詳細(xì)用法(包含案例)

desc function extended substr;

常用的函數(shù)

空字段賦值 case when then else end 行轉(zhuǎn)列 列轉(zhuǎn)行 窗口函數(shù)（開窗函數(shù)）

OVER()：指定分析函數(shù)工作的數(shù)據(jù)窗口大小，這個(gè)數(shù)據(jù)窗口大小可能會(huì)隨著行的變而變化。

CURRENT ROW：當(dāng)前行

n PRECEDING：往前n行數(shù)據(jù)

n FOLLOWING：往后n行數(shù)據(jù)

UNBOUNDED：無邊界

UNBOUNDED PRECEDING 前無邊界，表示從前面的起點(diǎn)，

UNBOUNDED FOLLOWING后無邊界，表示到后面的終點(diǎn)

LAG(col,n,default_val)：往前第n行數(shù)據(jù)

LEAD(col,n, default_val)：往后第n行數(shù)據(jù)

FIRST_VALUE (col,true/false)：當(dāng)前窗口下的第一個(gè)值，第二個(gè)參數(shù)為true，跳過空值

LAST_VALUE (col,true/false)：當(dāng)前窗口下的最后一個(gè)值，第二個(gè)參數(shù)為true，跳過空值

NTILE(n)：把有序窗口的行分發(fā)到指定數(shù)據(jù)的組中，各個(gè)組有編號(hào)，編號(hào)從1開始，對(duì)于每一行，NTILE返回此行所屬的組的編號(hào)。注意：n必須為int類型。

4、Hive類型

基本類型

Hive數(shù)據(jù)類型Java數(shù)據(jù)類型長度例子TINYINTbyte1byte有符號(hào)整數(shù)20SMALLINTshort2byte有符號(hào)整數(shù)20INTint4byte有符號(hào)整數(shù)20BIGINTlong8byte有符號(hào)整數(shù)20BOOLEANboolean布爾類型，true或者falseTRUE FALSEFLOATfloat單精度浮點(diǎn)數(shù)3.14159DOUBLEdouble雙精度浮點(diǎn)數(shù)3.14159STRINGstring字符系列?？梢灾付ㄗ址??？梢允褂脝我?hào)或者雙引號(hào)?！畁ow is the time’ “for all good men”TIMESTAMP-時(shí)間類型-BINARY-字節(jié)數(shù)組-

對(duì)于Hive的String類型相當(dāng)于數(shù)據(jù)庫的varchar類型，該類型是一個(gè)可變的字符串，不過它不能聲明其中最多能存儲(chǔ)多少個(gè)字符，理論上它可以存儲(chǔ)2GB的字符數(shù)。

集合類型

數(shù)據(jù)類型描述語法示例STRUCT和c語言中的struct類似，都可以通過“點(diǎn)”符號(hào)訪問元素內(nèi)容。例如，如果某個(gè)列的數(shù)據(jù)類型是STRUCT{first STRING, last STRING},那么第1個(gè)元素可以通過字段.first來引用。struct()MAPMAP是一組鍵-值對(duì)元組集合，使用數(shù)組表示法可以訪問數(shù)據(jù)。例如，如果某個(gè)列的數(shù)據(jù)類型是MAP，其中鍵->值對(duì)是’first’->’John’和’last’->’Doe’，那么可以通過字段名[‘last’]獲取最后一個(gè)元素map()ARRAY數(shù)組是一組具有相同類型和名稱的變量的集合。這些變量稱為數(shù)組的元素，每個(gè)數(shù)組元素都有一個(gè)編號(hào)，編號(hào)從零開始。例如，數(shù)組值為[‘John’, ‘Doe’]，那么第2個(gè)元素可以通過數(shù)組名[1]進(jìn)行引用。Array()

Hive有三種復(fù)雜數(shù)據(jù)類型ARRAY、MAP 和 STRUCT。ARRAY和MAP與Java中的Array和Map類似，而STRUCT與C語言中的Struct類似，它封裝了一個(gè)命名字段集合，復(fù)雜數(shù)據(jù)類型允許任意層次的嵌套。

類型轉(zhuǎn)換

Hive的原子數(shù)據(jù)類型是可以進(jìn)行隱式轉(zhuǎn)換的，類似于Java的類型轉(zhuǎn)換，例如某表達(dá)式使用INT類型，TINYINT會(huì)自動(dòng)轉(zhuǎn)換為INT類型，但是Hive不會(huì)進(jìn)行反向轉(zhuǎn)化，例如，某表達(dá)式使用TINYINT類型，INT不會(huì)自動(dòng)轉(zhuǎn)換為TINYINT類型，它會(huì)返回錯(cuò)誤，除非使用CAST 操作。

隱式類型轉(zhuǎn)換規(guī)則如下。任何整數(shù)類型都可以隱式地轉(zhuǎn)換為一個(gè)范圍更廣的類型，如TINYINT可以轉(zhuǎn)換成INT，INT可以轉(zhuǎn)換成BIGINT。所有整數(shù)類型、FLOAT和STRING類型都可以隱式地轉(zhuǎn)換成DOUBLE。TINYINT、SMALLINT、INT都可以轉(zhuǎn)換為FLOAT。BOOLEAN類型不可以轉(zhuǎn)換為任何其它的類型?？梢允褂肅AST操作顯示進(jìn)行數(shù)據(jù)類型轉(zhuǎn)換，例如CAST(‘1’ AS INT)將把字符串’1’ 轉(zhuǎn)換成整數(shù)1；如果強(qiáng)制類型轉(zhuǎn)換失敗，如執(zhí)行CAST(‘X’ AS INT)，表達(dá)式返回空值 NULL。

柚子快報(bào)激活碼778899分享：數(shù)據(jù)倉庫 Hive數(shù)倉模型

http://yzkb.51969.com/

參考文章