open-metadata
MS SQL procedures - lineage not being pickup and process
Affected module Ingestion Framework
Describe the bug MS SQL procedures - lineage not being pickup and process
To Reproduce create two tables namely (test1,test2) and 1 storedprocedure "myproc" with simple logic as
create PROCEDURE myproc AS BEGIN insert into test2 select * from test1 END GO
Expected behavior
- run metadata ingestion.
- run the "exec myproc" in any SQL client tool
- run lineage ingestion
- test1 and test2 lineage should build in UI
Version:
- OS: window
- Python version: 3.8.10
- OpenMetadata version: 1.4.7
- OpenMetadata Ingestion package version: 1.4.7.1
Additional context The storedprocedure lineage not being calculated is because the sql server log the "exec myproc" as "create procedure..." in the dm_exec_sql_text table and this was being ignored/filtered by the openmetadata SQL default filtering query.
as we can see from the SQL query below.
SELECT TOP 1000
db.NAME database_name,
t.text query_text,
s.last_execution_time start_time,
DATEADD(s, s.total_elapsed_time/1000, s.last_execution_time) end_time,
s.total_elapsed_time/1000 duration,
NULL schema_name,
NULL query_type,
NULL user_name,
NULL aborted FROM sys.dm_exec_cached_plans AS p
INNER JOIN sys.dm_exec_query_stats AS s ON p.plan_handle = s.plan_handle
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
INNER JOIN sys.databases db ON db.database_id = t.dbid
WHERE s.last_execution_time
between '2024-08-25 00:00:00' and '2024-08-27 00:00:00'
AND t.text NOT LIKE '/* {"app": "OpenMetadata", %%} /%%'
AND t.text NOT LIKE '/ {"app": "dbt", %%} */%%'
AND p.objtype != 'Prepared'
AND (lower(t.text) LIKE '%%select%%into%%' OR lower(t.text) LIKE '%%insert%%into%%select%%' OR lower(t.text) LIKE '%%update%%' OR lower(t.text) LIKE '%%merge%%')
AND lower(t.text) NOT LIKE '%%create%%procedure%%'
AND lower(t.text) NOT LIKE '%%create%%function%%'
AND lower(t.text) NOT LIKE '%%declare%%'
ORDER BY s.last_execution_time DESC
when we triggered the "lineage ingestion", the query run in procedure is being omitted, i.e. "insert into test2 select * from test1"
because openmetadata SQL logic is to ignore the "create procedure..." when building lineage.
Same bug here using OpenMetadata in production environment. Version: 1.4.8
This issue has been fixed by this PR https://github.com/open-metadata/OpenMetadata/pull/14586
we are now including the create procedure and declare statements.
