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) 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. Selecting a warehouse is optional.
Role	[The role option is available when you have connected successfully.] A role determines which database views a user can access. Selecting a role is optional. The role is saved in the connection data source.

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

Supported data types

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
VARBINARY	Binary
BOOLEAN	Boolean
DATE	Date
TIMESTAMP	DateTime
NUMERIC	Currency/Decimal
DECIMAL	Currency/Decimal
FLOAT	Real/Double
DOUBLE	Real/Double
REAL	SingleReal/Float
INTEGER	Integer
BIGINT	LongInteger/Long
CHAR	String
VARCHAR	String
TIME	Time

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