Skip to main content

Snowflake

In Illa, you can use the Snowflake query editor to execute SQL queries against your Snowflake data warehouse. You can connect to Snowflake by entering your Snowflake account credentials, including the account name, username, password, and warehouse name. Once you have connected to Snowflake, you can use the query editor to write and execute SQL queries to retrieve, manipulate, and analyze your data.

Create Snowflake

There are two ways to create a resource in Illa after signing into your Illa account.

  • Create in Resources

Sign into your Illa account, select **Resources** on the top of the page, and click **Create New** button.

external_resource

Select Snowflake from the database list.

action_list

Connect to the database with the required parameters described in Connection Settings below.

Click **Test Connection** to see if we can successfully connect to the database. If yes, click Save Resources, else, double check the hostname, port, username, and password is correct.

After creating a resource, the ready Snowflake will display as shown.

snow_ex_ready

  • Create in Builder

Sign into your Illa account, create a project in Illa Builder in the **App** page, and navigate to the Action List at the bottom of the page. Click **new**, then select Snowflake from the database list. Then, connect to the database with required parameters described in Connection Settings below.

Click **Test Connection** to see if we can successfully connect to the database. If yes, click Save Resources, else, double check the hostname, port, username, and password is correct.

Connection Settings

Here we need to provide information for connecting to Snowflake database.

snow_config

PropertiesRequiredDescription
NamerequiredThe name for resource when creating actions in the ILLA.
Account namerequiredunique identifier that is assigned to each Snowflake account
Warehouserequireda cluster of compute resources that are used to process queries and run analytical workloads
DatabaserequiredThe name of the database
Schemaoptionala container that holds database objects such as tables, views, and procedures
Roleoptionala named set of privileges that can be assigned to one or more users
Authenticationrequiredsecure access to your data and resources in the Snowflake data warehouse (Basic Auth or Key Pair (only encrypted private keys are supported))
Usernamerequiredthe username you wish to use when logging in to the Snowflake server.
PasswordrequiredUse this password for authentication.

Create Actions

We have created a Snowflake resource, we can add the action by selecting Snowflake from action list and choosing the Create action button.

snow_resource_list

Now we have added the Snowflake server as an action to our building page.

snow

Configure Snowflake

PropertiesDescription
SQL querysql commands to fetch (select), insert, update, delete data from database
Transformertransforming data into the style you like using JavaScript

Example usage:

  • Select
SELECT * FROM users ORDER BY lastname;
  • Insert
INSERT INTO users

(firstname, lastname, email)

VALUES

(

{{ NewUser.data.firstNameInput }},

{{ NewUser.data.lastNameInput }},

{{ NewUser.data.emailInput }}

);
  • Update
UPDATE users
SET firstname = '{{form1.updatedProfile.first}}'
WHERE id = {{ form1.updatedProfile.uid}};
  • Delete
DELETE FROM users WHERE id = {{ form1.updatedProfile.uid }};