【SQLFlow】SQLFlow分析mysql语句

使用SQLFlow分析一段mysql的SQL脚本，得其中的表以及字段间的数据血缘关系data lineage。

分析的SQL：

CREATE TABLE tmp.tmp_a_supp_achievement_an_mom_001 AS
  SELECT a1.dim_day_txdate,
         a.a_pin,
         Sum(Coalesce(b.amount, 0)) AS total_amount
         , Sum(Coalesce(c.refund_amt, 0)) AS refund_amt
         , Sum(os_prcp_amt) os_prcp_amt
  FROM (SELECT dim_day_txdate 
        FROM dmv.dim_day
        WHERE dim_day_txdate>=concat(cast(Year('2018-05-15')-1 AS string),'-', substring('2018-05-15', 6, 2), '-01')
          AND dim_day_txdate<='2018-05-15' )a1
  JOIN (SELECT DISTINCT a_pin, product_type
        FROM dwd.dwd_as_qy_cust_account_s_d
        WHERE dt ='2018-05-15' AND product_type='20288' )a
  LEFT OUTER JOIN (SELECT substring(tx_time, 1, 10) AS time, sum(order_amt) AS amount, a_pin
                    FROM dwd.dwd_actv_as_qy_iou_receipt_s_d
                    WHERE a_order_type='20096' AND a_pin NOT IN ('vep_test', 'VOPVSP测试')
                      AND dt='2018-05-15'
                    GROUP BY substring(tx_time, 1, 10), a_pin )b
              ON cast(a.a_pin AS string)=cast(b.a_pin AS string) AND a1.dim_day_txdate=b.time
  LEFT OUTER JOIN ( SELECT substring(refund_time, 1, 10) AS refund_time, a_pin, sum(refund_amt)AS refund_amt
                    FROM dwd.dwd_as_qy_iou_refund_s_d
                    WHERE refund_status='20090' AND dt='2018-05-15' AND a_order_no <> '12467657248'
                      AND a_refund_no <> '1610230919767139947'
                    GROUP BY substring(refund_time, 1, 10), a_pin )c
              ON cast(a.a_pin AS string)=cast(c.a_pin AS string) AND a1.dim_day_txdate=c.refund_time
  LEFT OUTER JOIN (SELECT dt, a_pin, sum(os_prcp_amt) AS os_prcp_amt
                    FROM dwd.dwd_as_qy_cycle_detail_s_d
                    WHERE dt>=concat(substr('2018-05-15', 1, 7), '-01') AND dt<='2018-05-15'
                    GROUP BY dt, a_pin)e
              ON cast(a.jd_pin AS string)=cast(e.a_pin AS string) AND a1.dim_day_txdate=e.dt
  GROUP BY a1.dim_day_txdate, a.a_pin;

使用SQLFlow进行分析

并且可以进行模糊搜索，使用非常方便。

总结

以上就是使用SQLFlow对mysql的SQL继续分析得到表，以及字段间的数据血缘关系data lineage的案例。