Support for Stored Procedures as another entity under Database Schema

[harshach]

Is your feature request related to a problem? Please describe.
Stored Procedures important entity in a database, many users are expressed interest in capturing metadata about stored procedures and potentially lineage

Describe the solution you'd like
We will be adding Stored Procedure as another entity with a parent container as the Database Schema.
There will be APIs under /api/v1/storedProcedures to capture the metadata about Stored Procedures

• Backend APIs for Stored Procedures
• Sample Data for Stored Procedures
• UI support for Stored Procedures
• Search support for Stored Procedures
• Ingestion Support
• Missing aggregation in search
• cypress for storedProcedure and data models

Related issues

• Allow stored procedures, user defined functions, user defined table types as table types #12167

[pmbrull]

Ingestion

For Snowflake:

• Overview
• List Procedures
• Possible solution to getting the procedure lineage based on the procedure execution query ID

For Redshift:

• Overview
• List Procedures (via query) and get details by name.
• Possible solution to getting procedure executions

For BigQuery:

• Overview. They are a subset of Routines, which include Stored Procedures, UDFs and Table Functions, so we might be able to generally treat Routines.
• List Procedures and get their definition. More docs
• TODO: Check how we might be able to get the executions. Maybe use the JOBS_* tables?

For Postgres:

[asprdnv]

Hi, will we able to build pipelines and lineage based on stored procedures?

We call procedures in MSSQL Jobs – usually 1 step = 1 procedure

[pmbrull]

hi @asprdnv Stored Procedures are ingested under the Database Schema that they live in. Not sure if we'll have enough room to handle MSSQL for 1.2.0. If not, we'll add them in 1.2.1

[chuqbach]

Hi, how about Oracle Stored Procedure? Do you have any plan for it?

[nqvuong1998]

Hi @pmbrull , Do you have any plan to implement Stored Procedure for Oracle?

[ayush-shah]

Hello @nqvuong1998 Yes, that's is on the roadmap and we plan to bring it in the upcoming release.

[ayush-shah]

@nqvuong1998 you can track the fix for the same from here: #14641
and the issue for it: #14630