利用元数据提高数据血缘分析的准确性

一、马哈鱼: 数据治理专家的一把利器

数据血缘属于数据治理中的一个概念,是在数据溯源的过程中找到相关数据之间的联系,它是一个逻辑概念。数据治理里经常提到的一个词就是血缘分析,血缘分析是保证数据融合的一个手段,通过血缘分析实现数据融合处理的可追溯。大数据治理分析师常常需要对各种复杂场景下的SQL语句进行溯源分析,而限于环境因素,往往只能提供SQL语句给马哈鱼进行分析处理,SQL语句的制造者往往为了简便行事,会产生一些数据库可执行但马哈鱼无法正确识别的一类语句,本文聚焦此处,为各位专家介绍马哈鱼官方对这类问题的解决方案。

马哈鱼官方入口: https://sqlflow.gudusoft.com

二、马哈鱼的Orphan Column Error

随着对马哈鱼的使用逐渐深入,你会发现在分析部分SQL 数据血缘时,会遇到马哈鱼的orphan column错误提示,如下图所示:

利用元数据提高数据血缘分析的准确性

如果您是马哈鱼的新用户,您可能会有我的SQL语句明明是正确可执行的为啥会报这个错误,这主要是因为马哈鱼目前的模式是未连接数据源状态,即仅从SQL语句进行血缘分析。orphan column error是提示正在分析的SQL语句存在‘孤儿列’,什么是孤儿列?孤儿列就是在多表join的情形下某个返回列或条件列没有指定具体所属表对象,即马哈鱼没有依据判断该列到底是来源于哪里。

示例:

SELECT c_customer_id
FROM   customer_total_return ctr1,
       store,
       customer
WHERE  ctr1.ctr_total_return > (SELECT Avg(ctr_total_return) * 1.2
                                FROM   customer_total_return ctr2
                                WHERE  ctr1.ctr_store_sk = ctr2.ctr_store_sk)
       AND s_store_sk = ctr1.ctr_store_sk
       AND s_state = 'SD'
       AND ctr1.ctr_customer_sk = c_customer_sk
ORDER  BY c_customer_id

上述语句是一个3表(customer_total_return ,store,customer)关联的简单语句,它的运算结果是返回复合条件的c_customer_id列集合。不难看出,关联条件(and s_store_sk = ctr1.ctr_store_sk and s_state = 'SD' and ctr1.ctr_customer_sk = c_customer_sk)中s_store_sk 、s_state 、c_customer_sk等三个列并没有指定来源。该语句之所以在Oracle查询分析器中没有错误,是因为查询分析器可以拿到三个表定义进行遍历对比,如果上述未指定来源表的列恰好都只属于某个表,此时查询分析器便能正常解析并执行该语句。

相反,马哈鱼只有SQL语句,而没法获取表定义,所以就会出现orphan column error。那我们应该如何解决’孤儿列’的问题呢?目前有以下两个可行方案:

1、完善SQL语句,由简变繁

SELECT ctr1.c_customer_id
FROM   customer_total_return ctr1,
       store s,
       customer c
WHERE  ctr1.ctr_total_return > (SELECT Avg(ctr_total_return) * 1.2
                                FROM   customer_total_return ctr2
                                WHERE  ctr1.ctr_store_sk = ctr2.ctr_store_sk)
       AND s.s_store_sk = ctr1.ctr_store_sk
       AND s.s_state = 'SD'
       AND ctr1.ctr_customer_sk = c.c_customer_sk
ORDER  BY c.c_customer_id

上述代码将返回列、条件列中所有未指定来源表的列进行了完善,执行马哈鱼分析后,能够成功分析:

利用元数据提高数据血缘分析的准确性

上图能够成功分析并且右侧显示窗口中已经没有错误信息。

2、为马哈鱼上传schema 文件

马哈鱼厂商为解决上述问题,为用户提供了一个可以手工上传schema DDL文件的方法来解决上述问题。

还以上述SQL语句为例,我们可以将对应的缺失列的Table DDL以文件方式上传提供给马哈鱼后,具体的table DDL定义如下:

create table customer
(
    c_customer_sk             integer               not null,
    c_customer_id             char(16)              not null,
    c_current_cdemo_sk        integer                       ,
    c_current_hdemo_sk        integer                       ,
    c_current_addr_sk         integer                       ,
    c_first_shipto_date_sk    integer                       ,
    c_first_sales_date_sk     integer                       ,
    c_salutation              char(10)                      ,
    c_first_name              char(20)                      ,
    c_last_name               char(30)                      ,
    c_preferred_cust_flag     char(1)                       ,
    c_birth_day               integer                       ,
    c_birth_month             integer                       ,
    c_birth_year              integer                       ,
    c_birth_country           varchar(20)                   ,
    c_login                   char(13)                      ,
    c_email_address           char(50)                      ,
    c_last_review_date        char(10)                      ,
    primary key (c_customer_sk)
);

create table store
(
    s_store_sk                integer               not null,
    s_store_id                char(16)              not null,
    s_rec_start_date          date                          ,
    s_rec_end_date            date                          ,
    s_closed_date_sk          integer                       ,
    s_store_name              varchar(50)                   ,
    s_number_employees        integer                       ,
    s_floor_space             integer                       ,
    s_hours                   char(20)                      ,
    s_manager                 varchar(40)                   ,
    s_market_id               integer                       ,
    s_geography_class         varchar(100)                  ,
    s_market_desc             varchar(100)                  ,
    s_market_manager          varchar(40)                   ,
    s_division_id             integer                       ,
    s_division_name           varchar(50)                   ,
    s_company_id              integer                       ,
    s_company_name            varchar(50)                   ,
    s_street_number           varchar(10)                   ,
    s_street_name             varchar(60)                   ,
    s_street_type             char(15)                      ,
    s_suite_number            char(10)                      ,
    s_city                    varchar(60)                   ,
    s_county                  varchar(30)                   ,
    s_state                   char(2)                       ,
    s_zip                     char(10)                      ,
    s_country                 varchar(20)                   ,
    s_gmt_offset              decimal(5,2)                  ,
    s_tax_precentage          decimal(5,2)                  ,
    primary key (s_store_sk)
);

由于第一张表customer_total_return所使用/返回的列均在SQL语句中显示指定,所以这里不需要额外提供它的定义信息,只需要提供其他两张表的定义,如果您的语句中存在所有表均有上述情况,则需要将所有表的定义提供给马哈鱼供分析。

实际操作如下:

利用元数据提高数据血缘分析的准确性 

关于马哈鱼官方提供的上传schema解决方案的几点补充:

  • 一个用户可以上传一个或多个schema文件,也支持打包zip格式上传,马哈鱼会自动遍历所有文件进行分析;
  • 用户可以对已上传的文件进行删除;

三、参考网站

马哈鱼官方入口:

https://www.sqlflow.cn

马哈鱼 架构文档:

https://github.com/sqlparser/sqlflow_public/blob/master/sqlflow_architecture.md

上一篇:【报告分享】2021中国媒体市场趋势-CTR洞察(附下载)


下一篇:细碎知识【eclipse 的快捷键、随机数、格式化小数、汉诺塔】