#15799: Snowflake schema tree does not show Procedures.

alonsomendez reported 2020-06-24T13:05:50Z · last modified 2021-04-21T17:16:22Z

Snowflake schema tree does not show Procedures.

Dev	vaibhavjain
QA	AmarAccolite

Priority	Minor
Complexity	Unknown

Component	DB - Snowflake
Version	22.0

Stored Procedures were recently added to the Snowflake platforms. Aqua Data Studio needs to support stored procedure objects.

ADS areas that need to change:

Schema Tree - object nodes, model nodes

Build object extraction queries

Layout - object nodes, model nodes - DB2ProceduresNode as an example

Database

Schema

Procedures

Dependencies

Parameters

Detail Window - HiveColumnsModel as an example.

Scripting - Add Procedures

Define script syntax, system catalog dependencies and keywords

Schema browser "Script object to" Layout - object nodes->popup - DB2TablesNode.constructPopupMenu as an example. Define the functions in the popup.

CREATE

DROP

EXECUTE

GRANT

REVOKE

Functional areas are Schema script generator, Script object to Query Analyzer Schema Compare, Visual editing and preview

Build keyword extraction queries

Create extracts for scripts

Build script syntax for each object - ScriptTable, ScriptView as examples

Schema script generator - See ScriptGeneratorDialog, ScriptGeneratorWorker

Visual Editing

Create, Alter, Drop and Object Properties - See TableDialog

Define the object parameters

Build keyword extraction queries

Create menus for object parameters

Define and build preview scripting - See scripting above.

Query Analyzer Window

Needs to be able to execute the Stored Procedure.

Results Window

Specialized query result sets - See PlatResultsFormatPanel, QueryThread, AFScriptContext and ShellScriptContext

Schema Compare

Add new schema objects to the selection - See SchemaObject

As listed in scripting section, will need to have all listed objects ddl scripted.

Object Search

Build search extracts for all procedure objects. See FindFrame and ObjectSearchctx

AquaScript

Support for new procedures for Schema Compare and Database Schema and Data Exporter.- CoreMetadata.java, ExportDatabaseToCSVTemplate.vm , SchemaCompareTemplate.vm

Test projects in studio

6 attachments

SnowflakeParameters.png

2021-03-09T18:22:58Z

196 KB
DB2Parameters.png

2021-03-09T18:26:19Z

105 KB
sp1.png

2021-03-09T19:50:25Z

106 KB
sp2.png

2021-03-09T19:50:34Z

106 KB
sp3.png

2021-03-09T21:22:14Z

116 KB
SnowflakeRevoke.png

2021-03-16T15:25:02Z

137 KB

All Comments (20) Change History

shreypasari 2021-02-09T11:57:07Z

Dependencies Node in Snowflake

Hi @Asif/ @Tom

We are implementing the Procedure Dependency in Snowflake.

As per implementation of other database servers there is reference/dependency information in information_schema for all objects i.e. table, view, procedures, etc.

MySQL:

DB2:

PostgreSql:

In the case of Snowflake there is no such information and the available API call “GET_OBJECT_REFERENCES” is only for tables and views.

The other possible solution is the perform string function on the procedure definition to get information about the references and dependency for a procedure.

Please let us know your thoughts on this. Should we hide the dependency tab totally or should we use a string manipulation approach?

Thanks

tomconrad 2021-02-09T18:25:29Z

Hi Shrey,

Lets hide the dependency tab since we also don't have it for other objects.

Thanks,
Tom

shreypasari 2021-02-10T08:00:33Z · (edited)

Alter Procedure

Hi @Asif/ @Tom

We are implementing the Alter Procedure Script in Snowflake.

For other database servers the Alter Script is done in two possible ways: 1. Adding Alter comment before the Procedure Definition i.e. same command as create procedure.2. Providing all possible options for Alter Procedure

In the case of Snowflake, Alter Procedure provides the following possible scripting options:

Please let us know your thoughts on this. Should we hide the script alter option for snowflake or which give all possible options to the user to alter which are available to us in snowflake?

Thanks

tomconrad 2021-02-10T16:53:30Z

Hi Shrey,

Since those are the only options, let's use the #2 option and provide all options.

Thanks,

Tom

abdullah 2021-02-11T03:33:29Z

I agree with Tom. Show all the options.

Thanks
Asif

abdullah 2021-02-23T17:37:09Z

Revision #58506
Committed by krishna.agarwal Today 14:07

Overall changes look OK. Can you address the following:

-- public final class ExtractParameter

Cant we just split the parameter based on space, " " here?

	AFParameter aParameter = new AFParameter(param.substring(0, param.indexOf(" ")).trim());
	aParameter._dataType = param.substring(param.indexOf(" ") + 1).trim();

Thanks
Asif

Revision #58506
Committed by krishna.agarwal Today 14:07

Overall changes look OK. Can you address the following:

-- public final class ExtractParameter

Cant we just split the parameter based on space, " " here?

	AFParameter aParameter = new AFParameter(param.substring(0, param.indexOf(" ")).trim());
	aParameter._dataType = param.substring(param.indexOf(" ") + 1).trim();

Thanks
Asif

shreypasari 2021-03-02T05:16:21Z

Revision #58526

As per Tom's comments in the mail, all the extraction queries are updated with qualifying the object names.

As well as scripting objects are also updated to work as per the Script Options selected for Snowflake Server.

Thanks

tomconrad 2021-03-02T14:57:20Z

Thanks Team!!!

MounikaSure09 2021-03-03T11:28:38Z · (edited)

Hi Tom/Asif,

we have created stored procedure with optional parameters like strict/execute as owner, after the procedure is created those optional parameters are not shown in properties window or either in preview SQL tab. Because of this when we generate schema script of the procedure optional parameters are not being included. Is this expected?

procedure we used

create or replace procedure stproc1(FLOAT_PARAM1 FLOAT)

returns string

language javascript

strict

execute as owner

var sql_command =

"INSERT INTO stproc_test_table1 (num_col1) VALUES (" + FLOAT_PARAM1 + ")";

try {

snowflake.execute (

{sqlText: sql_command}

);

return "Succeeded."; // Return a success/error indicator.

}

catch (err) {

return "Failed: " + err; // Return a success/error indicator.

}

;

tomconrad 2021-03-03T21:19:48Z

Hi Mounika,

You are correct. All parameters in the create statement should be available in scripting assuming they can be extracted.

Hi Vaibhav,

Please have a look at this and if the parameters are available from the extract, please include them in the scripting.

Thanks,
Tom

shreypasari 2021-03-08T16:42:37Z

Revision #58534

As per Mounika's comment the optional variables are now visible in scripting options for Snowflake.

tomconrad 2021-03-09T21:23:31Z · (edited)

Hi Vaibhav,

I don't think the F5:Detail window is working correctly when I select parameters in Snowflake. See here, It should probably behave similar to DB2. Please have a look.

For some reason, not all sp's are getting previewed using script generator. See here and here.

We can get rid of Create Procedure in Editor for Snowflake...

Thanks,
Tom

shreypasari 2021-03-10T09:43:26Z · (edited)

Hi Tom,

In case of Snowflake, we only get the parameter Name and data type from the available information for the procedure. So the detailed window for snowflake Parameters can have two columns namely a) Name b) DataType

Preview tab is working fine after the code committed yesterday and the "Create Procedure in Editor" will be removed in the next commit.

For the optional parameters
"STRICT" is synonym to "RETURNS NULL ON NULL INPUT" so DDL for stored procedure in Snowflake provides us the second text along with all the other optional parameters default setting.

Thanks

tomconrad 2021-03-17T13:48:34Z · (edited)

Hi Shrey,

Name and data type are fine for the detail window.

The Script Object as Revoke to the QA window doesn't seem to be working. Please have a look at that.

Thanks,

Tom

shreypasari 2021-03-17T08:36:58Z

Hi Tom,

The specified changes are made in the revision #58588

Thanks

MounikaSure09 2021-03-23T04:25:22Z

Verified on build Aqua Data Studio 22.0.0-dev-26-no-ofsc Build #: 58607

Windows 10

testrail link:https://idera.testrail.net/index.php?/runs/view/4167&group_by=cases:section_id&group_order=asc&group_id=28023

divya123garg1 2021-03-23T08:52:45Z

Verified on build Aqua Data Studio 22.0.0-dev-26-no-ofsc .

platform : Mac catalina (10.15)

testrail link:

https://idera.testrail.net/index.php?/runs/view/4168&group_by=cases:section_id&group_order=asc&group_id=28023

shubham.tripathi 2021-03-26T13:43:08Z

QA Verified:Snowflake schema tree does not show Procedures.

On build Aqua Data Studio 22.0.0-dev-26-no-ofsc and its working as expected.

platform : Ubuntu

testrail link: ADS 22.0 test suite - Ubuntu - TestRail

KarunaAccolite 2021-03-30T11:40:04Z

Moving to automation team for further action on this

AmarAccolite 2021-04-21T17:16:14Z

Automation verified. Please find the test run below.

https://idera.testrail.net/index.php?/runs/view/4381

Search Tips

Aqua Data Studio / nhilam

Snowflake schema tree does not show Procedures.

6 attachments

Issue #15799

Completion

Search Tips

Aqua Data Studio / nhilam

Title

Snowflake schema tree does not show Procedures.

6 attachments

Issue #15799

Completion