Connector for Snowflake — Features and settings

Page content

Connector features

The following functionality is available when you access data with the connector for Snowflake.


Feature	Supported?
Load methods	Import (in-memory) External (in-database) Push compute (only available for users with Spotfire Industry Pro) On-demand
Custom queries	Yes
Stored procedures	No
Custom connection properties	Yes
Single sign-on with identity provider	Yes
Authoring in web client	Yes
Included in Spotfire distribution on Linux	Yes

Data source properties

The following are the supported data source properties that you can configure when you create a data connection with the connector for Oracle.


Option	Description
Server	The hostname of the server where your data is located. A Snowflake server contains multiple databases, which contain schemas, which in turn contain tables. `<account_identifier>.snowflakecomputing.com`
Authentication method	The authentication method to use when logging into the database. Choose between: Identity provider (OAuth2) Use an identity provider, such as Azure, Okta or Keycloak, to log in. When you open the data connection, a web browser window opens, and you log in with the identity provider's login procedure. Username and password (with MFA token caching) Log in with username and password and have the ODBC driver cache the access token received after you approve the request on your multi-factor authentication (MFA) device. The cached token is valid for up to 4 hours. If you are using an analysis for longer than 4 hours, a new request is sent to your MFA device whenever Spotfire sends a query to Snowflake. If you do not approve the request in time, or if many queries are sent from Spotfire, there is a risk you will get locked out of Snowflake temporarily. Note: By default, you cannot use this authentication method in the Spotfire web client. When you log in to a Snowflake data connection with token caching in a web client, the driver caches the token in the credentials manager on the computer running the Spotfire Web Player service. Anyone with access to the shared computer can access this token. To enable this authentication method for use in the web client, use the `AllowMfaTokenCachingOnWebPlayer` preference in Administration Manager. Username and password Log in with your Snowflake username and password. If multi-factor authentication (MFA) is required, you must approve every query that is sent from Spotfire. Okta Snowflake offers native authentication through Okta. You authenticate using your username, password, and the URL endpoint. Key-pair authentication Log in with key-pair authentication using the private key that you generated in Snowflake, and the username. If the private key is encrypted, you must also enter a password. Key-pair authentication is useful if you use a service account for automated use cases, such as Automation Services and Scheduled Updates.
Identity provider (OAuth2)	[Only applicable for Identity provider (OAuth2) authentication.] Select the identity provider you want to use for logging in to the data source. The options available in the drop-down menu are the identity providers you have added to the OAuth2IdentityProviders preference.
Scopes	[Only applicable for Identity provider (OAuth2) authentication.] Scopes determine what permissions Spotfire requests on your behalf when you log in to the data source. Default Use the default scopes that you have specified for your identity provider in the `OAuth2IdentityProviders` preference. Custom Enter scopes manually in the text box. Separate values with a space. `Scope_1 Scope_2`
Okta URL	[Only applicable for Okta authentication.] Enter the URL of the Okta instance you wish to connect to. `https://<okta_account_name>.okta.com`
Private key	[Only applicable for Key-pair authentication.] To connect to Snowflake using key-pair authentication, enter your private key. Refer to the Snowflake documentation if you need help with generating the key. Encrypted private key example If you use an encrypted private key, enter the username and password in the corresponding fields. `-----BEGIN ENCRYPTED PRIVATE KEY----- MIIFHDBOBgkqhkiG9w0BBQ0wQTApBgkqhkiG9w0BBQwwHAQIfXyJisrSjXkCAggA wlfisPs50Apinjw9tq3cLVdUdItife3xFSdAyGv38vXuY+4+ooj21su3I2VtoY3rK LH9PC54g5dLBvbGizZlFNQ== -----END ENCRYPTED PRIVATE KEY-----` Unencrypted private key example If you use an unencrypted private key, enter the username and leave the password field empty. `-----BEGIN PRIVATE KEY----- MIIFHDBOBgkqhkiG9w0BBQ0wQTApBgkqhkiG9w0BBQwwHAQIfXyJisrSjXkCAggA wlfisPs50Apinjw9tq3cLVer5fsxoWMFSdAyGv38vXuY+4+ooj21su3I2VtoY3rK LH9PC54g5dLBvbGizZlFNQ== -----END PRIVATE KEY-----`
Connection timeout (s)	Specify the maximum number of seconds allowed for a connection to be established. The default value is 300. Note: If you set the connection timeout to zero, it will be interpreted as no timeout. That is, there will be no upper limit for trying to connect. This is generally not recommended.
Command timeout (s)	Specify the maximum number of seconds allowed for a command to be executed. The default value is 0. Note: If you set the command timeout to zero, it will be interpreted as no timeout. That is, there will be no upper limit for trying to execute the command. This is generally not recommended.
Warehouse	[The warehouse option is available when you have connected successfully.] A warehouse is a collection of servers which determines how much compute power will be used for your queries. If no warehouse is specified, the default warehouse specified in Snowflake will be used.
Role	[The role option is available when you have connected successfully.] Controls the primary role in Snowflake. The primary role, together with the secondary roles, determines which database views a user can access. The secondary roles is set to ALL if the preference ForceSecondaryRolesToAll is set to true in your environment. Selecting a role is optional. The role is saved in the connection data source.
Allow the load method 'Push compute'	Determine whether to allow the load method 'Push compute' when the connection is used. With this load method, the data is kept in the source system, and any calculations and joins are performed by Snowflake. Everybody that use an analysis with push-compute data must review the consent dialog and decide whether they allow any additional data to be uploaded to their user account on Databricks. Note: Only users with the Spotfire Industry Pro license are allowed to add and configure analyses that use push-compute data. If this option is cleared, nobody will see the Push compute option in the source view when trying to add data from the connection. Read more about the push compute option in Load methods and Push-compute data. Also note that data functions that use push compute, should be created specifically for the system of interest. See Using a specific calculation engine for data functions for more details.

Custom properties for Snowflake connection data sources

The following is the default list of driver settings that are allowed as custom properties in Snowflake connection data sources. To learn how to change the allowed custom properties, see Controlling what properties are allowed.

Default allowed custom properties

login_timeout, maxHttpRetries, network_timeout, no_proxy, odbc_use_standard_timestamp_columnsize, passcodeInPassword, proxy, query_timeout, retryTimeout, SecondaryRoles, tracing

Note: The SecondaryRoles custom property only takes effect if the connectors preference ForceSecondaryRolesToAll is set to false. The secondary roles must be set to ALL to use push-compute data.

Supported data types when using data from the connector in Spotfire

When you are setting up a connection to an external data source, Spotfire needs to map the data types in the data source to data types in Spotfire. The following are the data types that the Snowflake connector supports.


Database data type	Spotfire data type
BINARY	Binary
BOOLEAN	Boolean
DATE	Date
TIMESTAMP	DateTime
NUMBER(x,y)	Currency/Decimal
FLOAT	Real/Double
CHAR	String
VARCHAR	String
TIME	Time

Supported data types when using push compute upload from Spotfire

When you are using the push compute load method to upload data from Spotfire to an external system, the system must map the data types in Spotfire to data types in the data source. The following are the data types that the Snowflake connector supports.


Spotfire data type	Database data type
Boolean	BOOLEAN
Date	DATE
DateTime	TIMESTAMP_NTZ()
Currency/Decimal	NUMBER(38,18)
Real/Double	FLOAT
SingleReal/Float	FLOAT
Integer	NUMBER(38,0)
LongInteger/Long	NUMBER(38,0)
String	VARCHAR(16777216)
Time	TIME(9)

Supported functions

Supported functions are the functions that you can use when you work with in-database data tables, for example for calculated columns and custom expressions.

Note: Some supported functions might not be possible to use with your database. This depends on what functions are available in the database, which often differs between database versions and types.

The following are the functions that the Snowflake connector supports.


Function type	Functions supported
Date and Time	Add_Months, Convert_Timezone, CURDATE, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, CURTIME, Date_From_Parts, Date_Part, Date_Trunc, DateAdd, DateDiff, DAYNAME, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, HOUR, Last_Day, MINUTE, MONTH, MonthName, Next_Day, Previous_Day, QUARTER, SECOND, Time_From_Parts, Time_Slice, Timestamp_From_Parts, WEEK, YEAR
Conversion	COALESCE, NULLIF, To_Char, Try_To_Boolean, Try_To_Date, Try_To_Double, Try_To_Number, Try_To_Time, Try_To_Timestamp
Math	ABS, ACOS, Acosh, ASIN, Asinh, ATAN, ATAN2, Atanh, Cbrt, CEILING, COS, Cosh, COT, DEGREES, EXP, Factorial, FLOOR, Haversine, Ln, LOG, MOD, PI, POWER, RADIANS, RAND, ROUND, SIGN, SIN, Sinh, SQRT, Square, TAN, Tanh, TRUNCATE Note: In Spotfire, the input for the function `Factorial` must be an integer expression in the range 0-27. This differs from the range in Snowflake.
Operators	+, -, *, /
Statistical	Avg, Count, Listagg, Max, Median, Min, Stddev, Stddev_Pop, Stddev_Samp, Sum, UniqueCount, Var_Pop, Var_Samp, Variance, Variance_Pop, Variance_Samp
Text	ASCII, BIT_LENGTH, CHAR, CHAR_LENGTH, CHARACTER_LENGTH, CharIndex, CONCAT, Contains, EditDistance, EndsWith, ILike, InitCap, INSERT, LCASE, LEFT, LENGTH, LikeAny, LOCATE, LOWER, LTRIM, OCTET_LENGTH, POSITION, RegExp_Count, RegExp_Instr, RegExp_Replace, RegExp_Substr, REPEAT, REPLACE, RIGHT, RLike, RTRIM, SPACE, StartsWith, Substr, SUBSTRING, Trim, UCASE, UPPER