Extending the functionality of WhereScape
In order to integrate virtual warehouse usage into WhereScape RED, we need to enhance the tool using templates and extended properties. The aim of the following instructions is to allow an extended property to create a virtual warehouse for each table object. This is then retrieved from the table objects' loading templates and a USE-WAREHOUSE command sent to Snowflake at the beginning of each procedure.
1. Adding an extended property
First, you need to create a new extended property via Tools > Extended Properties > Maintain Extended Properties > New. Be sure to select "Other" under databases. Under Object Types, select the types of object you wish to enable for subsequent customized warehouse usage.
2. Review and maintain the extended property
The second step is to manually assign a value (i.e. a virtual warehouse name) to the extended property. To do this, navigate from an object via Properties > Extended Properties to the corresponding overview page and then enter a value in the extended property you created earlier. In our example, a virtual warehouse with the name WH_HUBS is assigned to the hub h_categories.
Tip: For additional automation, you can generate and automatically enter the virtual warehouse names in 3D via Model Conversion Rules, which eliminates the need for manual maintenance.
3. Modify the templates responsible for loading
The third step involves modifying the templates responsible for loading the object types you selected in step 1. The following code retrieves the virtual warehouse name for an object and sends a USE-WAREHOUSE command to Snowflake. This should, of course, be inserted at the beginning of the relevant template to ensure that all subsequent commands are executed by the appropriate virtual warehouse.
If no virtual warehouse name is selected, the warehouse stored in the ODBC connection is used by default for all commands contained in the procedure.
4. Regenerating the procedures
In the final step, the procedures of an object are regenerated via Properties > Update Procedure > Regenerate to allow the code added to the template to be used.
Note: You can check whether the procedure has been successful by inspecting the history tab in Snowflake. If you have initiated a procedure in WhereScape, you should find the USE-WAREHOUSE command there. Also, any subsequent commands issued by the procedure should be executed by the virtual warehouse defined in the extended property rather than by the virtual warehouse stored in the ODBC connection.