Configuring Database connectors - Snowflake

Studio enables you to use the database connectors to connect to your relational databases like MySQL, PostgreSQL and Microsoft SQL, MongoDB, Snowflake, Redshift and so on.

Snowflake has been gaining recognition for its powerful cloud-based data platform. It is an efficient warehouse-as-a-cloud-service (SaaS for DB) that requires no hardware or software installation. It is based on a new SQL database engine that enables faster, easier and flexible data storage, processing, and analytics. Studio provides the Snowflake connector that allows you to integrate with SQL database for your data storage and processing needs using your Custom apps.

Configuring a Snowflake Connector

To add third party DB connectors, under Studio > Connectors, click (+) Connector and select the Snowflake connector from the list.

To configure the connector, enter a Name and set the connection string or the configuration parameters.

Assuming that you have a Snowflake account you need to keep a few things in mind. You first need to Whitelist DronaHQ Studio.

Use the following commands in the worksheets

  1. Set the Admin privileges to set your user role as securityadmin

    Use role securityadmin

  2. Create a network policy and whitelist DronaHQ IP

    CREATE NETWORK POLICY DHQ_WHITELIST ALLOWED_IP_LIST = ( ‘Your IP’, ‘DronaHQ IP’ , … )

  3. Set the network policy -

    Alter account Set network_policy = DHQ_WHITELIST

  4. Check policy data

    Desc network policy DHQ_WHITELIST

If you are creating a new policy, make sure your own IP is included in the whitelisting array of IPs else add DronaHQ IP in your existing policy Get Account name.

You can can get the Account name from your Snowflake database url at https://.snowflakecomputing.com

For example: - https://dronahq-test-account.snowflakecomputing.com
where account name would be dronahq-test-account.

If you are using AWS then it could be something like : https://dronahq-test-account.us-east-2.aws.snowflakecomputing.com where the account name would be dronahq-test-account.us-east-2.aws

If you are using Azure then it can be something like: https://dronahq-test-account.west-us-2.azure.snowflakecomputing.com where account name would be dronahq-test-account.west-us-2.azure

You can provide the respective connection parameters required, namely Account Name, Database name and few optional fields like Database Schema, Username and Password, Database warehouse name, and User Role.

Then enable further action for Whitelisting IP.

Once these configurations are done, you need to Test the connection. If the authentication is successful you would get the response accordingly. You can now click Save.

Adding queries to DB connector

Once the Connector is configured it would now be available for use within your Studio Apps as required. You need to create or add a query using the Add Query option available to fetch, add, update or delete data using the connection. Here you can add your queries - simple as well as complex ones.

You can provide a name to the query. Add your query and click Run or press Ctrl + enter to run the query. The rows returned from the table are shown in the Response section. You can also add dynamic values. You simply need to put it into double Curly brackets. The Test value that you entered would be considered for fetching data.

You can now view the queries that you saved for use in your apps later under your specific connector under Custom Database connectors.

Using Snowflake connector

Fetch Data and apply pagination

Now here consider an example where we want to find the author names from the table. Let us first fetch data and apply pagination using the Snowflake Connector. So click bto go to the Query Editor. Here you would add the query with the OFFSET clause.

Run the query to view the results. You can add a dynamic variable if you want to define the OFFSET and LIMIT clause value to specify the number of rows and the page number to be shown for pagination. Save the query. Your connector is now configured.

Now whenever you want to make use of these Database connectors in your Apps, under Studio Console, click Connectors > + Add. From the list of connectors select Custom Database Connectors and select your ready connector and click Continue. Now from the connector select the query for pagination and click Continue. Connect your authenticated account.

Based on the query selected, you would have the connector fields that would be used in the BINDAPI formula. Add the connector name and other details and click Finish.

In this example form, to bind the fetched data to the Tablegrid control, select the Connector from the list of connectors available. You can see in the Custom Formula the BINDAPI formula which you can customize further if you want to format how your data would be displayed in the Tablegrid. You can also view the SQL query being used using Show query. If you want to hide the query for some reason, click Hide Query. Beyond the configured queries you feel the need a few more queries you can use the Add Query option.

Add Trips / Insert

Let us now see how to add a Trip with the Insert query. Click Add Query and open the query editor and add the Insert statement with the values for the different fields and then run the query.

When the record is added you can view it with the select statement. Optionally you can add dynamic values using the variables in the insert statement. These values can be added using a form designed to add records.

If you are using the dynamic values you need to configure and link the controls to the variables added to the Insert statement. Further, you need to add an action flow to trigger the Server-side action that calls the insert query in the connector that you have already configured.

Using the DB Connector for Snowflake is similar to any other SQLs like PostgreSQL, MySQL and so on. You can make use of the Queries to fetch data as well bind data such that you can undertake the insert, update, delete actions as well. You need to ensure the syntax which would vary a bit for different SQL systems.