日常数据需求讲解1-数据表存储优化

1.数据表存储优化

背景

当前有这样一张网关日志表，30+字段，每天存储515tb，每小时全量5tb，要花费近600块/天，需要去进行存储优化，全量714亿。

思路

第一步评估影响

看看下游有谁在用（血缘），怎么用（下游脚本读了多少天的数据），有没有报表等服务在用，如果出了问题能不能复现回滚，业务方查询分区最多多少天，元数据表则从之前做好的dwd去取，如果没有则需要问平台侧要元数据信息自己加工。

CREATE TABLE `xx`.`dwd_meta_table_detail_df`(
  `cata_log` string COMMENT '集群or数据源', 
  `db_name` string COMMENT '库名', 
  `table_name` string COMMENT '表名', 
  `join_name` string COMMENT '关联用name', 
  `layer` string COMMENT '分层', 
  `creator` string COMMENT '表创建人id', 
  `creator_name` string COMMENT '表创建人姓名', 
  `comments` string COMMENT '表描述', 
  `table_update_time` string COMMENT '表同步时间', 
  `tbl_type` string COMMENT '内部/外部/视图', 
  `tbl_inputformat` string COMMENT '表的inputformat', 
  `tbl_outputformat` string COMMENT '表的outputformat', 
  `tbl_loc` string COMMENT '表的存储位置', 
  `tbl_owner` string COMMENT '表的负责人：可选值为项目/用户', 
  `file_total_fromfile` bigint COMMENT '文件总数: 计算方法为根据表所在的路径的文件信息统计得到', 
  `size_total_fromfile` double COMMENT '存储量:这里是逻辑存储量，未考虑副本，单位为MB；计算方法为根据表所在的路径的文件信息统计得到', 
  `file_add_fromfile` bigint COMMENT '新增文件数:计算方法为昨天的文件总数-前天的文件总数', 
  `size_add_fromfile` double COMMENT '（根据文件）新增存储量:单位为MB， 计算方法为昨天的文件规模-前天的文件规模', 
  `open_total` bigint COMMENT '文件打开次数：计算方法为从底层取open_add_all字段', 
  `audit_total` bigint COMMENT '所有操作的执行次数：计算方法为从底层去audit_add_all字段', 
  `open_lasttime` string COMMENT '文件的最后打开时间：计算方法为底层取open_lasttime_max字段', 
  `audit_lasttime` string COMMENT '文件的最后操作时间：计算方法为底层取audit_lasttime_max字段', 
  `tbl_create_time` string COMMENT '创建时间', 
  `tbl_creator` string COMMENT '文件的创建人', 
  `partition_tbl` string COMMENT '是否是分区表', 
  `tbl_ref_num` bigint COMMENT 'job，query引用的job个数（注意开发模式和任务模式的job算两个）', 
  `tbl_visit_num` bigint COMMENT 'job,query的访问次数', 
  `account_id` string COMMENT '项目ID', 
  `catalog_id` string COMMENT 'catalog_id', 
  `catalog_name` string COMMENT 'catalog_name', 
  `catalog_type` string COMMENT 'catalog_type', 
  `refer_count` bigint COMMENT '引用次数(元数据中心)', 
  `read_count` bigint COMMENT '读取次数(元数据中心)', 
  `storage_type` string COMMENT '存储格式', 
  `lzo_compressed` string COMMENT '是否是lzo压缩', 
  `impala_sync` string COMMENT 'impala 同步情况', 
  `last_modified_time` bigint COMMENT '变更时间', 
  `transient_last_ddl_time` bigint COMMENT '表变更时间', 
  `lifecycle` string COMMENT '表生命周期', 
  `partition_lifecycle` string COMMENT '分区生命周期', 
  `themedomain` string COMMENT '主题域', 
  `domainlevel` string COMMENT '表分层', 
  `changetimes` bigint COMMENT '修改次数', 
  `cpu_cost` double COMMENT 'cpu消耗', 
  `memory_cost` double COMMENT '内存消耗', 
  `file_average_size` double COMMENT '文件平均大小', 
  `file_add_fromfile_30` bigint COMMENT '30天新增文件数', 
  `size_add_fromfile_30` double COMMENT '30天新增存储量', 
  `open_total_30` bigint COMMENT '30天文件打开数', 
  `cpu_budget` double COMMENT 'cpu 元', 
  `memory_budget` double COMMENT 'memory 元', 
  `storage_budget` double COMMENT 'storage 元', 
  `offline` string COMMENT '是否下线', 
  `unvistied` string COMMENT '30天无访问', 
  `tbl_owner_email` string COMMENT '表owner email', 
  `changetimes_30` bigint COMMENT '', 
  `file_open_num_lastscancycle_sum` bigint COMMENT '近扫描周期内文件累计打开次数', 
  `tbl_ref_num_lastscancycle_average` double COMMENT '近扫描周期内表平均引用JOB次数', 
  `tbl_visit_num_lastscancycle_sum` bigint COMMENT '近扫描周期内表累计被访问次数', 
  `file_open_num` bigint COMMENT '当天的文件打开次数', 
  `themedomainsw` string COMMENT '主题域（英文）', 
  `domainlevelsw` string COMMENT '表分层（英文）', 
  `tbl_write_num` bigint COMMENT '当天表write次数', 
  `tbl_write_num_all` bigint COMMENT '累计的表write次数', 
  `tbl_write_num_lastscancycle_sum` bigint COMMENT '最近一个扫描周期累计的表write次数', 
  `offline_level` bigint COMMENT '表推荐下线级别：0:不推荐 1:弱推荐 2:强推荐', 
  `hot_reserve` string COMMENT '数据温热保留时间', 
  `delete_dir` string COMMENT '数据处理策略', 
  `last_scan_cycle_new_storage_size` double COMMENT '最近一个扫描周期的表新增存储量', 
  `last_scan_cycle_new_file_num` bigint COMMENT '最近一个扫描周期的表新增文件数', 
  `tbl_last_scan_cycle_change_num` bigint COMMENT '最近一个扫描周期的表修改次数', 
  `reference_tbl_num` bigint COMMENT '被表引用次数', 
  `non_standard_path_num` bigint COMMENT '不规范路径的分区数量', 
  `has_lifecycle` string COMMENT '是否已设置生命周期', 
  `has_owner` string COMMENT '是否有负责人', 
  `warm_size_total_from_file` double COMMENT '温集群文件大小', 
  `par_num` bigint COMMENT '分区数量', 
  `little_file_count` bigint COMMENT '小文件数量', 
  `little_file_par_num` bigint COMMENT '小文件分区数量', 
  `offline_whitelist` string COMMENT '是否加入推荐下线白名单', 
  `lifecycle_whitelist` string COMMENT '是否加入生命周期永久保存', 
  `lifecycle_ticket_status` bigint COMMENT '生命周期永久保存审批状态，0：正常，1：审批中', 
  `offline_ticket_status` bigint COMMENT '下线白名单审批状态，0：正常，1：审批中', 
  `cold_reserve` string COMMENT '冷备保留天数', 
  `pg_id` bigint COMMENT '项目组id', 
  `file_merge_status` bigint COMMENT '小文件合并状态: 0：为开启，1：开启', 
  `implicit_type` string COMMENT '隐式类型，hiveOnKudu,hiveOnHbase,hiveOnArctic...', 
  `zorder_columns` string COMMENT '表z-order属性关联的字段', 
  `reserve_par_num` string COMMENT '生命周期保留分区数')
  
 
 
 CREATE TABLE `xx`.`dwd_meta_table_lineage_detail_df`(
  `join_name` string COMMENT '关联用表名', 
  `table_layer` string COMMENT '表分层', 
  `relation_table_id` string COMMENT '血缘表id', 
  `relation_type` bigint COMMENT '血缘类型：-1-上游 1-下游', 
  `relation_cata_log` string COMMENT '血缘集群or数据源', 
  `relation_db_name` string COMMENT '血缘库名', 
  `relation_table_name` string COMMENT '血缘表名', 
  `relation_layer` string COMMENT '血缘分层', 
  `lineage_update_time` string COMMENT '血缘同步时间', 
  `relation_join_name` string COMMENT '血缘表关联用表名，库名.表名')

第二步设计思路及前置评估

假设下游就一个节点，且每日就是把明细数据抽取到下游表，我们发现这张表是一个hf的表，也就是说每小时调度取所有数据（这里忽略数据源），看创建时间是跑了30+分钟，消耗资源，我们点击api下游发现也没报表使用，跟业务方沟通他们也只是用来存储30天数据用于数据排查，同时期望能看到当天的数据，因此当天调度没办法变动。

操作

简单修改办法：将3天分区生命周期变为1天生命周期，同时将下游脚本改为读取上游表最新分区。3天存储降为1天，节省400块/天

ALTER TABLE atest.table_test2 SET TBLPROPERTIES ('LIFECYCLE'='1')


下游表改造
取t+1数据

insert overwrite table xxxx.xxxx partition(pt='${bizdate}')
select xxxx
      ,xxxx
from atest.table_test2
where substr(时间,1,10)<'${bizdate_0}'
and pt='${bizdate_0}'
and hr='02'

长期修改办法

步骤一：走增量同步由于是日志数据，因此不需要考虑到数据回刷，同时一小时调度也浪费资源，还是log数据直接接入中间没处理，因此通过flink cdc3 订阅binlog将数据导入kafka，同时使用flink将数据跑入离线表中，历史数据则放入一个单独分区中存放，超过30天的数据则放入habse等，或者这张数据表保留近1-2年数据开到730分区。

图为flink cdc3 conf/mysql2xxx.yaml截图

实时把明细数据导入离线

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

下游操作

--因为可回滚数据所以将下游设置为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}'

#数据人的面试交流地##牛客创作赏金赛##数据人offer决赛圈怎么选##Java##数据分析#