Mysql view ddl lineage deduction

[whossa]

Hi Open Metadata team,
Thanks for the great work and we're exploring the possibility of adopting open metadata as our central meta-repository solution.

For MySQL view DDL lineage deduction via UI, based on this https://docs.open-metadata.org/v1.0.0/connectors/ingestion/lineage, which already addresses how to create that lineage for tables of database service. I thought it was already implemented in the MySQL service connector but it looks like OpenMetadata UI could only deduct the source table on the from end but not for the join end, for example, there's such a view DDL like this

CREATE VIEW order_incomes AS
SELECT
order_id,
customer_name,
SUM(ordered_quantity * product_price) total
FROM
order_details
INNER JOIN orders USING (order_id)
INNER JOIN customers USING (customer_name)
GROUP BY order_id;

We could only get a lineage such as order_details => order_incomes but should we get a lineage such as
[order_details, orders, customers] => order_incomes?

So is this by design and users have to implement this feature themselves?

[ulixius9]

Hey @whossa, thanks for raising this. we have already identified this issue. we fetch the view ddl from mysql by running this query SHOW CREATE VIEW <view_name>.

This query returns a compiled version of the create view like this

CREATE ALGORITHM=UNDEFINED DEFINER=`sql12611281`@`%` SQL SECURITY DEFINER VIEW `sql12611281`.`vRisk_Denorm` AS select `a`.`AsOfDate` AS `AsOfDate`,`a`.`AssetID` AS `AssetID`,`a`.`AssetRate` AS `AssetRate`,`a`.`AssetRateIndex` AS `AssetRateIndex`,`a`.`AssetRateIndexBehavior` AS `AssetRateIndexBehavior`,`a`.`CostPriceSettled` AS `CostPriceSettled`,`a`.`CostPriceTraded` AS `CostPriceTraded`,`a`.`CurrencyType_Identifier` AS `CurrencyType_Identifier`,`a`.`IntRate` AS `IntRate`,`a`.`Issuer_PortfolioCompanyNameNM` AS `Issuer_PortfolioCompanyNameNM`,`a`.`MaturityDate` AS `MaturityDate`,`a`.`ParAmountSettled` AS `ParAmountSettled`,`a`.`ParAmountTraded` AS `ParAmountTraded`,`a`.`Portfolio` AS `Portfolio`,`a`.`Portfolio_EntityId` AS `Portfolio_EntityId`,`a`.`Position_Id` AS `Position_Id`,`a`.`QuantitySettled` AS `QuantitySettled`,`a`.`QuantityTraded` AS `QuantityTraded`,`a`.`SecurityID` AS `SecurityID`,`a`.`SettledvsTraded` AS `SettledvsTraded`,`a`.`UnitsSettled` AS `UnitsSettled`,`a`.`UnitsTraded` AS `UnitsTraded`,`b`.`Source System` AS `Source System`,`b`.`Portfolio_Name` AS `Portfolio_Name`,`b`.`PositionKeyID` AS `PositionKeyID`,`b`.`Name` AS `Name`,`b`.`Credit Spread` AS `Credit Spread`,`b`.`Issuer OAS` AS `Issuer OAS`,`b`.`Idiosyncratic OAS` AS `Idiosyncratic OAS`,`b`.`Probability of Default` AS `Probability of Default`,`b`.`Recovery Rate` AS `Recovery Rate`,`b`.`Weighted Average Life` AS `Weighted Average Life`,`b`.`Theta` AS `Theta`,`b`.`Effective Convexity` AS `Effective Convexity`,`b`.`Effective Duration` AS `Effective Duration`,`b`.`Delta-Adjusted Underlying PV` AS `Delta-Adjusted Underlying PV`,`b`.`Gross Exposure` AS `Gross Exposure`,`b`.`Long Exposure` AS `Long Exposure`,`b`.`Net Exposure` AS `Net Exposure`,`b`.`Short Exposure` AS `Short Exposure`,`b`.`Modified Duration` AS `Modified Duration`,`b`.`ESG_;VERNANCE_SCORE` AS `ESG_;VERNANCE_SCORE`,`b`.`SDG_01_NET_ALIGNMENT_SCORE` AS `SDG_01_NET_ALIGNMENT_SCORE`,`b`.`SDG_02_NET_ALIGNMENT_SCORE` AS `SDG_02_NET_ALIGNMENT_SCORE`,`b`.`SDG_03_NET_ALIGNMENT_SCORE` AS `SDG_03_NET_ALIGNMENT_SCORE`,`b`.`SDG_04_NET_ALIGNMENT_SCORE` AS `SDG_04_NET_ALIGNMENT_SCORE`,`b`.`SDG_05_NET_ALIGNMENT_SCORE` AS `SDG_05_NET_ALIGNMENT_SCORE`,`b`.`SDG_06_NET_ALIGNMENT_SCORE` AS `SDG_06_NET_ALIGNMENT_SCORE`,`b`.`SDG_07_NET_ALIGNMENT_SCORE` AS `SDG_07_NET_ALIGNMENT_SCORE`,`b`.`SDG_08_NET_ALIGNMENT_SCORE` AS `SDG_08_NET_ALIGNMENT_SCORE`,`b`.`SDG_09_NET_ALIGNMENT_SCORE` AS `SDG_09_NET_ALIGNMENT_SCORE`,`b`.`SDG_10_NET_ALIGNMENT_SCORE` AS `SDG_10_NET_ALIGNMENT_SCORE`,`b`.`SDG_11_NET_ALIGNMENT_SCORE` AS `SDG_11_NET_ALIGNMENT_SCORE`,`b`.`SDG_12_NET_ALIGNMENT_SCORE` AS `SDG_12_NET_ALIGNMENT_SCORE`,`b`.`SDG_13_NET_ALIGNMENT_SCORE` AS `SDG_13_NET_ALIGNMENT_SCORE`,`b`.`SDG_14_NET_ALIGNMENT_SCORE` AS `SDG_14_NET_ALIGNMENT_SCORE`,`b`.`SDG_15_NET_ALIGNMENT_SCORE` AS `SDG_15_NET_ALIGNMENT_SCORE`,`b`.`SDG_16_NET_ALIGNMENT_SCORE` AS `SDG_16_NET_ALIGNMENT_SCORE`,`b`.`SDG_17_NET_ALIGNMENT_SCORE` AS `SDG_17_NET_ALIGNMENT_SCORE`,`b`.`CONTROVERSY_SCORE` AS `CONTROVERSY_SCORE`,`b`.`SOCIAL_CONTROVERSY_SCORE` AS `SOCIAL_CONTROVERSY_SCORE`,`b`.`CUSTOMER_CONTROVERSY_SCORE` AS `CUSTOMER_CONTROVERSY_SCORE`,`b`.`ENVIRONMENT_CONTROVERSY_SCORE` AS `ENVIRONMENT_CONTROVERSY_SCORE`,`b`.`;VERNANCE_CONTROVERSY_SCORE` AS `;VERNANCE_CONTROVERSY_SCORE`,`b`.`HUMAN_RIGHTS_CONTROVERSY_SCORE` AS `HUMAN_RIGHTS_CONTROVERSY_SCORE`,`b`.`LABOR_RIGHTS_CONTROVERSY_SCORE` AS `LABOR_RIGHTS_CONTROVERSY_SCORE`,`b`.`CLIMATE_CHANGE_THEME_SCORE` AS `CLIMATE_CHANGE_THEME_SCORE`,`b`.`BUSINESS_ETHICS_THEME_SCORE` AS `BUSINESS_ETHICS_THEME_SCORE`,`b`.`CORPORATE_;V_THEME_SCORE` AS `CORPORATE_;V_THEME_SCORE`,`b`.`ENVIRONMENTAL_OPPS_THEME_SCORE` AS `ENVIRONMENTAL_OPPS_THEME_SCORE`,`b`.`HUMAN_CAPITAL_THEME_SCORE` AS `HUMAN_CAPITAL_THEME_SCORE`,`b`.`NATURAL_RES_USE_THEME_SCORE` AS `NATURAL_RES_USE_THEME_SCORE`,`b`.`WASTE_MGMT_THEME_SCORE` AS `WASTE_MGMT_THEME_SCORE`,`b`.`PRODUCT_SAFETY_THEME_SCORE` AS `PRODUCT_SAFETY_THEME_SCORE`,`b`.`SOCIAL_OPPS_THEME_SCORE` AS `SOCIAL_OPPS_THEME_SCORE`,`b`.`STAKEHOLDER_OPPOSIT_THEME_SCORE` AS `STAKEHOLDER_OPPOSIT_THEME_SCORE`,`b`.`CBN_LCT_SCORE` AS `CBN_LCT_SCORE`,`b`.`PROD_CARB_FTPRNT_SCORE` AS `PROD_CARB_FTPRNT_SCORE`,`b`.`CARBON_EMISSIONS_SCORE` AS `CARBON_EMISSIONS_SCORE`,`b`.`CARBON_EMISSIONS_GHG_MITIGATION_SCORE` AS `CARBON_EMISSIONS_GHG_MITIGATION_SCORE`,`b`.`CARBON_EMISSIONS_SCOPE_1` AS `CARBON_EMISSIONS_SCOPE_1`,`b`.`UNGC_COMPLIANCE` AS `UNGC_COMPLIANCE`,`b`.`CARBON_EMISSIONS_SCOPE_12` AS `CARBON_EMISSIONS_SCOPE_12`,`b`.`CARBON_EMISSIONS_SCOPE_2` AS `CARBON_EMISSIONS_SCOPE_2`,`b`.`CARBON_EMISSIONS_SCOPE_3` AS `CARBON_EMISSIONS_SCOPE_3`,`b`.`CARBON_EMISSIONS_SCOPE123` AS `CARBON_EMISSIONS_SCOPE123`,`b`.`ESG_SCORE` AS `ESG_SCORE`,`b`.`ESG_ENVIRONMENTAL_SCORE` AS `ESG_ENVIRONMENTAL_SCORE`,`b`.`ESG_SOCIAL_SCORE` AS `ESG_SOCIAL_SCORE`,`b`.`Security_ID` AS `Security_ID`,`b`.`_ID` AS `_ID`,`b`.`as_of_date` AS `as_of_date`,`b`.`created_by` AS `created_by`,`b`.`modified_by` AS `modified_by`,`b`.`created_on` AS `created_on`,`b`.`modified_on` AS `modified_on`,`b`.`is_active` AS `is_active`,`b`.`is_processed` AS `is_processed` from (`sql12611281`.`vPositions_denorm_data` `a` join `sql12611281`.`risk_metrics_output_data` `b` on((`a`.`Position_Id` = `b`.`PositionKeyID`)))

and the parser that we use i.e sqlfluff fails to parse the table reference and joins due to the complex bracketed structure for example in your case this would be

FROM
(((order_details  INNER JOIN orders USING (order_id)) INNER JOIN customers USING (customer_name)) GROUP BY order_id)

we are working on improving this, we already have a ticket open for this ref

[whossa]

Hi @ulixius9
Thanks for the reply, we're looking forward to this fix as this lineage is an awesome feature in open metadata and view lineage is part of this, so I could build a POC on top of this.

[harshach]

@ulixius9 can we transfer this discussion to appropriate GitHub issue