Connecting to Snowflake
  • 08 Aug 2022
  • 5 Minutes to read
  • Dark
    Light

Connecting to Snowflake

  • Dark
    Light

Note:

This topic describes how to import data into Sisense's ElastiCube, and how to use Live Connect.

Sisense enables easy and quick access to databases, tables and views contained within Snowflake.

Snowflake Connector Differences for ElastiCube and Live

You can import your data into a Sisense ElastiCube, or connect your data to a Sisense Live model using Live Connect.

The ElastiCube connector and Live connector dialogs have the same fields, but there are some differences:

  • ElastiCube connector dialog - All of the parameters must be entered directly into the relevant fields.
  • Live connector dialog - In addition to manually entering parameter values, it also allows the use of user parameters for the Connection String, User Name, Password, Dynamic Schema, and Database.

For more information about user parameters (which are defined in the Sisense Admin console) and the Dynamic Schema functionality, please refer to Managing Live Dynamic Connections.

Snowflake Connection String

To connect to your Snowflake database you need to provide a connection string that identifies which Snowflake warehouse you are connecting to.

For example:
jdbc:snowflake://<account_name>.snowflakecomputing.com/?warehouse=<warehouse_name>
snowflake://my-account.snowflakecomputing.com/?warehouse=DEMO_WH

The credentials you will provide in the Snowflake connector dialog must be relevant for the warehouse that you have included in the connection string.

To create a connection string, see JDBC Driver Connection String.

Credentials

For authentication, you can provide user credentials or use Key-Pair credentials.

To use Key-Pair credentials, store your Key Pair here: /opt/sisense/storage/<any sub-folder>

For more information, see Using Key Pair Authentication.

Importing Into a Sisense ElastiCube

Importing Into a Sisense ElastiCube

To import Snowflake data:

1. In the Data page, open an ElastiCube or click  to create a new ElastiCube.

2. In the Model Editor, click  the Add Data dialog box is displayed.

3. In the Add Data dialog box, select Snowflake.
8-5snowflakethumb0300.png

4. In Connection String, enter your connection string to your Snowflake database.

5. Enter your Snowflake user name in the User Name field.

6. Enter your Snowflake password in the Password field.

8-5snowflake-connection-string.png
OR
Select Use Key Pair Authentication, and enter:
  • Path to private key: Where you stored the Private Key on your computer.
  • Private key passphrase: The password for your Private Key.
  • Account: The unique name of your Snowflake account within your organization.

7. Ignore the Use Dynamic Schema option. This option is only valid when configuring a Live connection.

8. (Optional) In Additional Parameters, enter additional configuration options by appending key-value pairs to the connection string (e.g., Timeout=60;). For a full list of connection string options, see the Configuring the JDBC Driver documentation for Snowflake.

9. (Optional) In Database enter the name of the database that you will use. This option lets you "pre-select" a database so you don't have to chose from a long list of databases in the Select Database tab.

10. (Optional) In Schema enter a schema name if you only want to get tables from a specific schema in the Select Table tab.

11. Click Next to go to the Select Database and Select Table tabs.

If you have not entered a (optional) Database or (optional) Schema then all tables and views associated with your Snowflake connection string are displayed. If you have entered a either a database name or schema name, it will be pre-selected on the Select Database or Select Table tab. If you provided both a database name and a schema name the Select Table tab will be opened directly to the target schema so you can select the tables for your data model.

8-5snowflake-previewthumb0300.png

12. Select the relevant tables or views from the list that you want to work with. Click the preview icon next to the relevant table or view to see a preview of the data inside it.

13. (Optional) Click + for the Add Table Query option (at the bottom of the schemas and tables list) to create a table using a custom SQL query. See Importing Data with Custom Queries for more information.

14. Selecting the Import Relationships toggle at the bottom of the screen to use the existing relationships between tables in the source.

15. Click Done to add your selections.

Out-of-Memory Issues

When building an ElastiCube with this connector, you might receive an "out of memory" error. To add more memory, see Troubleshooting Memory Issues

Connecting Data to a Sisense Live Model

Connecting Data to a Sisense Live Model

1. In the Data page, open a live model or click 8-5livebutton1.png to create a new live model.

2. In the Model Editor, click8-5databutton4.png the Add Data dialog box is displayed.

3. In the Add Data dialog box, select Snowflake.

8-5snowflakethumb03001.png

Snowflake Connector Dialog

This is the Snowflake connector dialog for connecting data to a Live model. It has the same fields as the ElastiCube connector dialog, but the fields with 3 dot menus can be used with user parameters that have been defined in the Sisense Admin console.


Note:

User parameters (which are defined in the User Parameters area of the Sisense Admin console) may be used for these Snowflake live model connection settings:

  • Connection String
  • User Name
  • Password
  • Dynamic Schema
  • Database

For more information about user parameters and the Dynamic Schema functionality, please refer to Managing Live Dynamic Connections.

4. In Connection String, enter your connection string to your Snowflake database.

5. Enter your Snowflake user name in the User Name field.

6. Enter your Snowflake password in the Password field.

OR

Select Use Key Pair Authentication, and enter:

  • Path to private key: Where you stored the Private Key on your computer.
  • Private key passphrase: The password for your Private Key.
  • Account: The unique name of your Snowflake account within your organization.

7. (Optional) Select the Use Dynamic Schema option to automatically select the schema defined for your user in the User Parameters area of the Sisense Admin console.

8. (Optional) In Additional Parameters, enter additional configuration options by appending key-value pairs to the connection string (e.g., Timeout=60;). For a full list of connection string options, see the Configuring the JDBC Driver documentation for Snowflake.

9. (Optional) In Database enter a database name manually or use the selector to select a User Parameter for the database name. This option lets you "pre-select" a database so you don't have to chose from a long list of databases in the Select Database tab.

10. (Optional) In Schema enter a schema name if you only want to get tables from a specific schema in the Select Table tab.

11. Click Next to go to the Select Database and Select Table tabs.

If you have not entered a (optional) Database or (optional) Schema then all tables and views associated with your Snowflake connection string are displayed. If you have entered a either a database name or schema name, it will be pre-selected on the Select Database or Select Table tab. If you provided both a database name and a schema name the Select Table tab will be opened directly to the target schema so you can select the tables for your data model.

8-5snowflake-previewthumb03001.png

12. Select the relevant tables or views from the list that you want to work with. Click the preview icon next to the relevant table or view to see a preview of the data inside it.

13. (Optional) Click + for the Add Table Query option (at the bottom of the schemas and tables list) to create a table using a custom SQL query. See Importing Data with Custom Queries for more information.

14. Selecting the Import Relationships toggle at the bottom of the screen to use the existing relationships between tables in the source.

15. Click Done to add your selections.

Limitations

  • The timestamp_ltz data type is not supported. Use timestamp_tz instead.

Was this article helpful?