基于MaxCompute InformationSchema进行血缘关系分析

一、需求场景分析 在实际的数据平台运营管理过程中，数据表的规模往往随着更多业务数据的接入以及数据应用的建设而逐渐增长到非常大的规模，数据管理人员往往希望能够利用元数据的分析来更好地掌握不同数据表的血缘关系，从而分析出数据的上下游依赖关系。 本文将介绍如何去根据MaxCompute InformationSchema中作业ID的输入输出表来分析出某张表的血缘关系。 二、方案设计思路 MaxCompute Information_Schema提供了访问表的作业明细数据tasks_history，该表中有作业ID、input_tables、output_tables字段记录表的上下游依赖关系。根据这三个字段统计分析出表的血缘关系 1、根据某1天的作业历史，通过获取tasks_history表里的input_tables、output_tables、作业ID字段的详细信息，然后分析统计一定时间内的各个表的上下游依赖关系。 2、根据表上下游依赖推测出血缘关系。 三、方案实现方法 参考示例一： （1）根据作业ID查询某表上下游依赖SQL处理如下：

select
t2.input_table,
t1.inst_id,
replace(replace(t1.output_tables,"[",""),"]","") as output_table
from information_schema.tasks_history t1
left join
(

select
---去除表开始和结尾的\[ \]
trans\_array(1,",",inst\_id,
replace(replace(input\_tables,"\[",""),"\]","")) as (inst\_id,input\_table)
from information\_schema.tasks\_history  where ds = 20190902 

)t2
on t1.inst_id = t2.inst_id
where (replace(replace(t1.output_tables,"[",""),"]","")) <> ""
order by t2.input_table limit 1000;

结果如下图所示：

（2）根据结果可以分析得出每张表张表的输入表输出表以及连接的作业ID，即每张表的血缘关系。 血缘关系位图如下图所示：

中间连线为作业ID，连线起始为输入表，箭头所指方向为输出表。 参考示例二： 以下方式是通过设置分区，结合DataWorks去分析血缘关系： （1）设计存储结果表Schema

CREATE TABLE IF NOT EXISTS dim_meta_tasks_history_a
(

stat\_date         STRING COMMENT '统计日期',
project\_name      STRING COMMENT '项目名称',
task\_id           STRING COMMENT '作业ID',
start\_time        STRING COMMENT '开始时间',
end\_time          STRING COMMENT '结束时间',
input\_table       STRING COMMENT '输入表',
output\_table      STRING COMMENT '输出表',
etl\_date          STRING COMMENT 'ETL运行时间'

);

（2）关键解析sql

SELECT
'${yesterday}' AS stat_date
,'project_name' AS project_name
,a.inst_id AS task_id
,start_time AS start_time
,end_time AS end_time
,a.input_table AS input_table
,a.output_table AS output_table
,GETDATE() AS etl_date
FROM (

SELECT 
    t2.input\_table    
    ,t1.inst\_id
    ,replace(replace(t1.input\_tables,"\[",""),"\]","") AS output\_table
    ,start\_time        
    ,end\_time        
FROM (
    SELECT
        \*
        ,ROW\_NUMBER() OVER(PARTITION BY output\_tables ORDER BY end\_time DESC) AS rows
    FROM information\_schema.tasks\_history
    WHERE operation\_text LIKE 'INSERT OVERWRITE TABLE%'
    AND (
        start\_time >= TO\_DATE('${yesterday}','yyyy-mm-dd')
        and
        end\_time <= DATEADD(TO\_DATE('${yesterday}','yyyy-mm-dd'),8,'hh')
        )
    AND(replace(replace(output\_tables,"\[",""),"\]",""))<>""
    AND ds = CONCAT(SUBSTR('${yesterday}',1,4),SUBSTR('${yesterday}',6,2),SUBSTR('${yesterday}',9,2))
    )t1
LEFT JOIN(
    SELECT TRANS\_ARRAY(1,",",inst\_id,replace(replace(input\_tables,"\[",""),"\]","")) AS (inst\_id,input\_table)
    FROM information\_schema.tasks\_history
    WHERE ds = CONCAT(SUBSTR('${yesterday}',1,4),SUBSTR('${yesterday}',6,2),SUBSTR('${yesterday}',9,2))
)t2
ON t1.inst\_id = t2.inst\_id
where t1.rows = 1

) a
WHERE a.input_table is not null
;

（3）任务依赖关系

（4）最终血缘关系

以上血缘关系的分析是根据自己的思路实践去完成。真实的业务场景需要大家一起去验证。所以希望大家有需要的可以根据自己的业务需求去做相应的sql修改。如果有发现处理不当的地方希望多多指教。我在做相应的调整。 欢迎加入“MaxCompute开发者社区2群”,点击链接申请加入或扫描二维码 https://h5.dingtalk.com/invite-page/index.html?bizSource=____source____&corpId=dingb682fb31ec15e09f35c2f4657eb6378f&inviterUid=E3F28CD2308408A8&encodeDeptId=0054DC2B53AFE745

上云就看云栖号：更多云资讯，上云案例，最佳实践，产品入门，访问：https://yqh.aliyun.com/

本文为阿里云原创内容，未经允许不得转载。