數(shù)倉（六）從0到1簡單搭建數(shù)倉ODS層(埋點(diǎn)日志 + 業(yè)務(wù)數(shù)據(jù))

最近工作一直忙著，報(bào)名參加了上海地區(qū)觀安杯CTF的比賽。第一次參加比預(yù)期好，拿了銀行行業(yè)分行二等獎(jiǎng)（主要是團(tuán)隊(duì)給力?。?。

此外還在搞DAMA中國CDGA考證的事情。9月5日考試發(fā)揮正常，感覺應(yīng)該是PASS可以拿到證書，數(shù)據(jù)治理證書我感覺最近幾年會很火爆！就像十年前的項(xiàng)目管理證書PMP。數(shù)據(jù)管理，數(shù)據(jù)治理方向必定火爆！這次9月成績北京、廣州、深圳早一天出成績，一些大佬特別是彭友會的已經(jīng)發(fā)喜報(bào)了！

可惜上海今天中午才能出結(jié)果！中午吃飯的時(shí)候郵件推送消息顯示81分！成功get證書，也是預(yù)料之中吧！

我們在操作數(shù)倉表、數(shù)據(jù)等需要使用一些工具，這里推薦使用JetBrains的DataGrip工具。

1、配置Data Source界面

• 添加數(shù)據(jù)源，選擇Apache Hive

• 配置相關(guān)信息

  
  

  
  

  
  

  
  

• 先啟動hiveServer2服務(wù)

  
  

  
  

       在做測試連接TestConnection前，先啟動hiveServer2服務(wù)。

注意：并且有4個(gè)hive session id 出現(xiàn)才點(diǎn)擊“TestConnection”按鈕。否則出現(xiàn)connectied failure

• 下載相應(yīng)的驅(qū)動（自動下載）

2、解決用戶訪問拒絕問題

這個(gè)報(bào)錯(cuò)界面，困擾了我一些時(shí)間，遠(yuǎn)程訪問被拒絕，原因在于hive 和hdfs以及l(fā)inux之間的權(quán)限問題。

解決辦法如下：

• 配置hdfs的core-site.xml文件，配置用戶；

 <property>
          <name>hadoop.proxyuser.root.hosts</name>
          <value>*</value>
 </property>
 <property>
          <name>hadoop.proxyuser.root.groups</name>
          <value>*</value>
</property>

測試后發(fā)現(xiàn)還是不行，報(bào)錯(cuò)依然是訪問拒絕！qiusheng和root用戶都不行

• 思考配置hive的hive-site.xml文件

<property>
    <name>hive.server2.enable.doAs</name>
    <value>FALSE</value>
    <description>
        Setting this property to true will have HiveServer2 execute
        Hive operations as the user making the calls to it.
    </description>
</property> 

<property>
     <name>dfs.permissions.enabled</name>
     <value>true</value>
     <description>
            If "true", enable permission checking in HDFS.
            If "false", permission checking is turned off,
            but all other behavior is unchanged.
            Switching from one parameter value to the other does not change the                mode,owner or group of files or directories.
  </description>
</property>

• 重啟Hadoop集群

  修改配置后需要重啟hadoop。

• 測試連接

  顯示測試成功！說明主要問題在于linux用戶訪問HDFS權(quán)限問題。

3、查看hive倉庫里面的數(shù)據(jù)庫和表以及內(nèi)容

發(fā)現(xiàn)student里面已經(jīng)有id和name字段了

當(dāng)然，也可以通過beeline來通過SQL查詢。

首先我們回顧一下ODS層的主要作用和特點(diǎn)：

• 又叫“貼源層”，這層保持?jǐn)?shù)據(jù)原貌不做任何修改，保留歷史數(shù)據(jù)，儲存起到備份數(shù)據(jù)的作用。
• 數(shù)據(jù)一般采用lzo、Snappy、parquet等壓縮格式，減少磁盤存儲空間（例如：原始數(shù)據(jù) 10G，根據(jù)算法可以壓縮到 1G 左 右）。
• 創(chuàng)建分區(qū)表，防止后續(xù)的全表掃描，減少集群資源訪問數(shù)倉的壓力，一般按天存儲在數(shù)倉中。

1、創(chuàng)建前端埋點(diǎn)日志ods_log表

前端埋點(diǎn)日志信息都是JSON格式形式主要包括兩方面：

（1）啟動日志；（2）事件日志：

我們把前端整個(gè)1條記錄埋點(diǎn)日志，當(dāng)一個(gè)字符串來處理，傳入到hive數(shù)據(jù)庫。

創(chuàng)建HQL語句如下：

在DataGrip里面執(zhí)行

drop table if exists ods_log;create external table ods_log(line string)partitioned by (dt string)Stored as    inputformat 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'    outputformat 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'Location '/warehouse/gmail/ods/ods_log';

添加lzo索引

還需要在hive文件上，添加lzo索引，要不然無法支持切片操作。

具體做法是通過hadoop自帶的jar包在hadoop集群命令行里面執(zhí)行：

hadoop jar /opt/module/hadoop-3.1.4/share/hadoop/common/hadoop-lzo-0.4.20.jar   com.hadoop.compression.lzo.DistributedLzoIndexer   -Dmapreduce.job.queuename=hive   /warehouse/gmail/ods/ods_log/dt=2021-05-01

2、執(zhí)行完，觀察Browser Director

發(fā)現(xiàn)已經(jīng)多了lzo.index索引

3、加載讀取HDFS數(shù)據(jù)到hive

load data inpath '/origin_data/gmall/log/topic_log/2021-05-01'    into table ods_log partition (dt='2021-05-01');

加載到hive，路徑是

/warehouse/gmail/ods/ods_log/dt=2021-05-01

load做的是剪切的操作！

4、查看hive庫數(shù)據(jù)是否已經(jīng)加載成功？

這樣我們就完成了ODS層前端埋點(diǎn)日志的處理。

現(xiàn)在我們來處理ODS層業(yè)務(wù)數(shù)據(jù)，先需要回顧一下ODS層當(dāng)時(shí)建表的表結(jié)構(gòu)關(guān)系。

1、業(yè)務(wù)表邏輯結(jié)構(gòu)關(guān)系

其中有顏色填充的代表：事實(shí)表；

其他：代表維度表；

2、HDFS文件對應(yīng)hive表結(jié)構(gòu)關(guān)系

源業(yè)務(wù)系統(tǒng)javaweb項(xiàng)目中的數(shù)據(jù)存儲在mysql里面，通過sqoop采集到HDFS對應(yīng)的文件，需要在hive數(shù)倉里面設(shè)計(jì)外部表與其一一對應(yīng)；

（1）考慮到分區(qū)partitioned by 時(shí)間

（2）考慮到lzo壓縮，并且需要lzo壓縮支持切片的話，必須要添加lzo索引

（3）mysql數(shù)據(jù)庫的表通過sqoop采集到HDFS，用的是\t作為分割，那數(shù)倉里面ODS層也需要\t作為分割；

3、創(chuàng)建ODS層業(yè)務(wù)表

這里我們業(yè)務(wù)系統(tǒng)javaweb項(xiàng)目一共有23張表，數(shù)倉hive我們這里只演示創(chuàng)建三張代表性的表。因?yàn)?3張創(chuàng)建表的結(jié)構(gòu)大都一樣，這里只有3種數(shù)據(jù)同步策略的方式。

• 訂單表ods_order_info

  表數(shù)據(jù)同步更新策略：增量

drop table if exists ods_order_info;create external table ods_order_info (    `id` string COMMENT '訂單號',    `final_total_amount` decimal(16,2) COMMENT '訂單金額',    `order_status` string COMMENT '訂單狀態(tài)',    `user_id` string COMMENT '用戶id',    `out_trade_no` string COMMENT '支付流水號',    `create_time` string COMMENT '創(chuàng)建時(shí)間',    `operate_time` string COMMENT '操作時(shí)間',    `province_id` string COMMENT '省份ID',    `benefit_reduce_amount` decimal(16,2) COMMENT '優(yōu)惠金額',    `original_total_amount` decimal(16,2)  COMMENT '原價(jià)金額',    `feight_fee` decimal(16,2)  COMMENT '運(yùn)費(fèi)') COMMENT '訂單表'PARTITIONED BY (`dt` string) -- 按照時(shí)間創(chuàng)建分區(qū)row format delimited fields terminated by '\t' -- 指定分割符為\t STORED AS -- 指定存儲方式，讀數(shù)據(jù)采用LzoTextInputFormat；輸出數(shù)據(jù)采用TextOutputFormat  INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'  OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'location '/warehouse/gmail/ods/ods_order_info/'; -- 指定數(shù)據(jù)在hdfs上的存儲位置

• SKU商品表ods_sku_info表

  數(shù)據(jù)同步更新策略：全量

drop table if exists ods_sku_info;create external table ods_sku_info(     `id` string COMMENT 'skuId',    `spu_id` string   COMMENT 'spuid',     `price` decimal(16,2) COMMENT '價(jià)格',    `sku_name` string COMMENT '商品名稱',    `sku_desc` string COMMENT '商品描述',    `weight` string COMMENT '重量',    `tm_id` string COMMENT '品牌id',    `category3_id` string COMMENT '品類id',    `create_time` string COMMENT '創(chuàng)建時(shí)間') COMMENT 'SKU商品表'PARTITIONED BY (`dt` string)row format delimited fields terminated by '\t'STORED AS  INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'  OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'location '/warehouse/gmail/ods/ods_sku_info/';

• 省份表ods_base_province

  數(shù)據(jù)同步更新策略：特殊一次性全部加載

  不做分區(qū)partitioned BY

drop table if exists ods_base_province;create external table ods_base_province (    `id`   bigint COMMENT '編號',    `name`        string COMMENT '省份名稱',    `region_id`    string COMMENT '地區(qū)ID',    `area_code`    string COMMENT '地區(qū)編碼',    `iso_code` string COMMENT 'iso編碼,superset可視化使用')  COMMENT '省份表'row format delimited fields terminated by '\t'STORED AS  INPUTFORMAT 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'  OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'location '/warehouse/gmail/ods/ods_base_province/';

4、加載數(shù)據(jù)

load data   inpath '/origin_data/gmall/db/order_info/XXXX-XX-XX'   OVERWRITE into table gmail.ods_order_info partition(dt='XXXX-XX-XX');

顯示如下：

然后把其他20張表也類似這樣操作，我們就完成了整個(gè)ODS層的數(shù)據(jù)處理。