日常數(shù)據(jù)需求講解1-數(shù)據(jù)表存儲(chǔ)優(yōu)化
1.數(shù)據(jù)表存儲(chǔ)優(yōu)化
背景
當(dāng)前有這樣一張網(wǎng)關(guān)日志表，30+字段，每天存儲(chǔ)515tb，每小時(shí)全量5tb，要花費(fèi)近600塊/天，需要去進(jìn)行存儲(chǔ)優(yōu)化，全量714億。
思路
第一步評(píng)估影響
看看下游有誰(shuí)在用（血緣），怎么用（下游腳本讀了多少天的數(shù)據(jù)），有沒(méi)有報(bào)表等服務(wù)在用，如果出了問(wèn)題能不能復(fù)現(xiàn)回滾，業(yè)務(wù)方查詢分區(qū)最多多少天，元數(shù)據(jù)表則從之前做好的dwd去取，如果沒(méi)有則需要問(wèn)平臺(tái)側(cè)要元數(shù)據(jù)信息自己加工。
CREATE TABLE `xx`.`dwd_meta_table_detail_df`( `cata_log` string COMMENT '集群or數(shù)據(jù)源', `db_name` string COMMENT '庫(kù)名', `table_name` string COMMENT '表名', `join_name` string COMMENT '關(guān)聯(lián)用name', `layer` string COMMENT '分層', `creator` string COMMENT '表創(chuàng)建人id', `creator_name` string COMMENT '表創(chuàng)建人姓名', `comments` string COMMENT '表描述', `table_update_time` string COMMENT '表同步時(shí)間', `tbl_type` string COMMENT '內(nèi)部/外部/視圖', `tbl_inputformat` string COMMENT '表的inputformat', `tbl_outputformat` string COMMENT '表的outputformat', `tbl_loc` string COMMENT '表的存儲(chǔ)位置', `tbl_owner` string COMMENT '表的負(fù)責(zé)人：可選值為項(xiàng)目/用戶', `file_total_fromfile` bigint COMMENT '文件總數(shù): 計(jì)算方法為根據(jù)表所在的路徑的文件信息統(tǒng)計(jì)得到', `size_total_fromfile` double COMMENT '存儲(chǔ)量:這里是邏輯存儲(chǔ)量，未考慮副本，單位為MB；計(jì)算方法為根據(jù)表所在的路徑的文件信息統(tǒng)計(jì)得到', `file_add_fromfile` bigint COMMENT '新增文件數(shù):計(jì)算方法為昨天的文件總數(shù)-前天的文件總數(shù)', `size_add_fromfile` double COMMENT '（根據(jù)文件）新增存儲(chǔ)量:單位為MB， 計(jì)算方法為昨天的文件規(guī)模-前天的文件規(guī)模', `open_total` bigint COMMENT '文件打開(kāi)次數(shù)：計(jì)算方法為從底層取open_add_all字段', `audit_total` bigint COMMENT '所有操作的執(zhí)行次數(shù)：計(jì)算方法為從底層去audit_add_all字段', `open_lasttime` string COMMENT '文件的最后打開(kāi)時(shí)間：計(jì)算方法為底層取open_lasttime_max字段', `audit_lasttime` string COMMENT '文件的最后操作時(shí)間：計(jì)算方法為底層取audit_lasttime_max字段', `tbl_create_time` string COMMENT '創(chuàng)建時(shí)間', `tbl_creator` string COMMENT '文件的創(chuàng)建人', `partition_tbl` string COMMENT '是否是分區(qū)表', `tbl_ref_num` bigint COMMENT 'job，query引用的job個(gè)數(shù)（注意開(kāi)發(fā)模式和任務(wù)模式的job算兩個(gè)）', `tbl_visit_num` bigint COMMENT 'job,query的訪問(wèn)次數(shù)', `account_id` string COMMENT '項(xiàng)目ID', `catalog_id` string COMMENT 'catalog_id', `catalog_name` string COMMENT 'catalog_name', `catalog_type` string COMMENT 'catalog_type', `refer_count` bigint COMMENT '引用次數(shù)(元數(shù)據(jù)中心)', `read_count` bigint COMMENT '讀取次數(shù)(元數(shù)據(jù)中心)', `storage_type` string COMMENT '存儲(chǔ)格式', `lzo_compressed` string COMMENT '是否是lzo壓縮', `impala_sync` string COMMENT 'impala 同步情況', `last_modified_time` bigint COMMENT '變更時(shí)間', `transient_last_ddl_time` bigint COMMENT '表變更時(shí)間', `lifecycle` string COMMENT '表生命周期', `partition_lifecycle` string COMMENT '分區(qū)生命周期', `themedomain` string COMMENT '主題域', `domainlevel` string COMMENT '表分層', `changetimes` bigint COMMENT '修改次數(shù)', `cpu_cost` double COMMENT 'cpu消耗', `memory_cost` double COMMENT '內(nèi)存消耗', `file_average_size` double COMMENT '文件平均大小', `file_add_fromfile_30` bigint COMMENT '30天新增文件數(shù)', `size_add_fromfile_30` double COMMENT '30天新增存儲(chǔ)量', `open_total_30` bigint COMMENT '30天文件打開(kāi)數(shù)', `cpu_budget` double COMMENT 'cpu 元', `memory_budget` double COMMENT 'memory 元', `storage_budget` double COMMENT 'storage 元', `offline` string COMMENT '是否下線', `unvistied` string COMMENT '30天無(wú)訪問(wèn)', `tbl_owner_email` string COMMENT '表owner email', `changetimes_30` bigint COMMENT '', `file_open_num_lastscancycle_sum` bigint COMMENT '近掃描周期內(nèi)文件累計(jì)打開(kāi)次數(shù)', `tbl_ref_num_lastscancycle_average` double COMMENT '近掃描周期內(nèi)表平均引用JOB次數(shù)', `tbl_visit_num_lastscancycle_sum` bigint COMMENT '近掃描周期內(nèi)表累計(jì)被訪問(wèn)次數(shù)', `file_open_num` bigint COMMENT '當(dāng)天的文件打開(kāi)次數(shù)', `themedomainsw` string COMMENT '主題域（英文）', `domainlevelsw` string COMMENT '表分層（英文）', `tbl_write_num` bigint COMMENT '當(dāng)天表write次數(shù)', `tbl_write_num_all` bigint COMMENT '累計(jì)的表write次數(shù)', `tbl_write_num_lastscancycle_sum` bigint COMMENT '最近一個(gè)掃描周期累計(jì)的表write次數(shù)', `offline_level` bigint COMMENT '表推薦下線級(jí)別：0:不推薦 1:弱推薦 2:強(qiáng)推薦', `hot_reserve` string COMMENT '數(shù)據(jù)溫?zé)岜Ａ魰r(shí)間', `delete_dir` string COMMENT '數(shù)據(jù)處理策略', `last_scan_cycle_new_storage_size` double COMMENT '最近一個(gè)掃描周期的表新增存儲(chǔ)量', `last_scan_cycle_new_file_num` bigint COMMENT '最近一個(gè)掃描周期的表新增文件數(shù)', `tbl_last_scan_cycle_change_num` bigint COMMENT '最近一個(gè)掃描周期的表修改次數(shù)', `reference_tbl_num` bigint COMMENT '被表引用次數(shù)', `non_standard_path_num` bigint COMMENT '不規(guī)范路徑的分區(qū)數(shù)量', `has_lifecycle` string COMMENT '是否已設(shè)置生命周期', `has_owner` string COMMENT '是否有負(fù)責(zé)人', `warm_size_total_from_file` double COMMENT '溫集群文件大小', `par_num` bigint COMMENT '分區(qū)數(shù)量', `little_file_count` bigint COMMENT '小文件數(shù)量', `little_file_par_num` bigint COMMENT '小文件分區(qū)數(shù)量', `offline_whitelist` string COMMENT '是否加入推薦下線白名單', `lifecycle_whitelist` string COMMENT '是否加入生命周期永久保存', `lifecycle_ticket_status` bigint COMMENT '生命周期永久保存審批狀態(tài)，0：正常，1：審批中', `offline_ticket_status` bigint COMMENT '下線白名單審批狀態(tài)，0：正常，1：審批中', `cold_reserve` string COMMENT '冷備保留天數(shù)', `pg_id` bigint COMMENT '項(xiàng)目組id', `file_merge_status` bigint COMMENT '小文件合并狀態(tài): 0：為開(kāi)啟，1：開(kāi)啟', `implicit_type` string COMMENT '隱式類(lèi)型，hiveOnKudu,hiveOnHbase,hiveOnArctic...', `zorder_columns` string COMMENT '表z-order屬性關(guān)聯(lián)的字段', `reserve_par_num` string COMMENT '生命周期保留分區(qū)數(shù)') CREATE TABLE `xx`.`dwd_meta_table_lineage_detail_df`( `join_name` string COMMENT '關(guān)聯(lián)用表名', `table_layer` string COMMENT '表分層', `relation_table_id` string COMMENT '血緣表id', `relation_type` bigint COMMENT '血緣類(lèi)型：-1-上游 1-下游', `relation_cata_log` string COMMENT '血緣集群or數(shù)據(jù)源', `relation_db_name` string COMMENT '血緣庫(kù)名', `relation_table_name` string COMMENT '血緣表名', `relation_layer` string COMMENT '血緣分層', `lineage_update_time` string COMMENT '血緣同步時(shí)間', `relation_join_name` string COMMENT '血緣表關(guān)聯(lián)用表名，庫(kù)名.表名')
第二步設(shè)計(jì)思路及前置評(píng)估
假設(shè)下游就一個(gè)節(jié)點(diǎn)，且每日就是把明細(xì)數(shù)據(jù)抽取到下游表，我們發(fā)現(xiàn)這張表是一個(gè)hf的表，也就是說(shuō)每小時(shí)調(diào)度取所有數(shù)據(jù)（這里忽略數(shù)據(jù)源），看創(chuàng)建時(shí)間是跑了30+分鐘，消耗資源，我們點(diǎn)擊api下游發(fā)現(xiàn)也沒(méi)報(bào)表使用，跟業(yè)務(wù)方溝通他們也只是用來(lái)存儲(chǔ)30天數(shù)據(jù)用于數(shù)據(jù)排查，同時(shí)期望能看到當(dāng)天的數(shù)據(jù)，因此當(dāng)天調(diào)度沒(méi)辦法變動(dòng)。
操作
簡(jiǎn)單修改辦法：將3天分區(qū)生命周期變?yōu)?天生命周期，同時(shí)將下游腳本改為讀取上游表最新分區(qū)。3天存儲(chǔ)降為1天，節(jié)省400塊/天
ALTER TABLE atest.table_test2 SET TBLPROPERTIES ('LIFECYCLE'='1')
下游表改造
取t+1數(shù)據(jù)
insert overwrite table xxxx.xxxx partition(pt='${bizdate}')
select xxxx
,xxxx
from atest.table_test2
where substr(時(shí)間,1,10)<'${bizdate_0}'
and pt='${bizdate_0}'
and hr='02'
長(zhǎng)期修改辦法
步驟一：走增量同步由于是日志數(shù)據(jù)，因此不需要考慮到數(shù)據(jù)回刷，同時(shí)一小時(shí)調(diào)度也浪費(fèi)資源，還是log數(shù)據(jù)直接接入中間沒(méi)處理，因此通過(guò)flink cdc3 訂閱binlog將數(shù)據(jù)導(dǎo)入kafka，同時(shí)使用flink將數(shù)據(jù)跑入離線表中，歷史數(shù)據(jù)則放入一個(gè)單獨(dú)分區(qū)中存放，超過(guò)30天的數(shù)據(jù)則放入habse等，或者這張數(shù)據(jù)表保留近1-2年數(shù)據(jù)開(kāi)到730分區(qū)。
圖為flink cdc3 conf/mysql2xxx.yaml截圖
實(shí)時(shí)把明細(xì)數(shù)據(jù)導(dǎo)入離線
create table `ods_ri` (
`message` varchar
)
with (
'connector' = 'kafka',
'topic' = 'community_monitor_collect_result',
'properties.bootstrap.servers' = 'kafka-xxxxx:9092',
'properties.group.id' = 'GID_community_monitor_task',
'scan.topic-partition-discovery.interval' = '10s',
'format' = 'raw',
'scan.startup.mode' = 'timestamp',
'scan.startup.timestamp-millis' = '1720713600000'
);
create table `dwd_ri` (
xx varchar ,
xx varchar,
xx varchar,
xx varchar,
xx varchar,
xx varchar,
xx varchar,
xx varchar,
pt varchar ,
hr varchar
)
with (
'PATH' = 'hdfs://xxxxxxxxxx',
'connector' = 'yshoppingRisk',
'endPoint' = 'http://service.cn-hangzhou/api',
);
insert into dwd_ri
select JSON_VALUE(message,'$.xx') as xx
,JSON_VALUE(message,'$.xx') as xx
,JSON_VALUE(message,'$.xx') as xx
,JSON_VALUE(message,'$.xx') as xx
,JSON_VALUE(message,'$.xx') as xx
,JSON_VALUE(message,'$.xx') as xx
,JSON_VALUE(message,'$.xx') as xx
,JSON_VALUE(message,'$.xx') as xx
,JSON_VALUE(message,'$.xx') as xx
,DATE_FORMAT(FROM_UNIXTIME(cast(substring(json_value(message,'$.ts'),1,10) as bigint)),'yyyyMMdd') pt
,DATE_FORMAT(FROM_UNIXTIME(cast(substring(json_value(message,'$.ts'),1,10) as bigint)),'HH') hr
from ods_ri
下游操作
--因?yàn)榭苫貪L數(shù)據(jù)所以將下游設(shè)置為30天
ALTER TABLE xxxx.xxxx SET TBLPROPERTIES ('LIFECYCLE'='30')
insert overwrite table xxxx.xxxx partition(pt='${bizdate}')
select xxxx
,xxxx
from atest.table_test2
where pt='${bizdate}'
#數(shù)據(jù)人的面試交流地##牛客創(chuàng)作賞金賽##數(shù)據(jù)人offer決賽圈怎么選##Java##數(shù)據(jù)分析#
小天才公司福利 616人發(fā)布
字節(jié)跳動(dòng)一面926人在聊
查看19道真題和解析