Snowflake is a high-performing cloud data warehouse. It can either be used to store your data in it and/or to use it as a data source.
This page describes connecting and using Snowflake as a data source. If you want to write into Snowflake, please refer to Snowflake Data Warehouse.
Type Name
snowflake
Connection Properties
Template name: snowflake
Appropriate translator name: snowflake
Properties:
host
(default:localhost
)db
(required; if absent:No database name provided
)schema
warehouse
(required; if absent:No warehouse name provided
; for multiple warehouses see theroleMapping
translator property)CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX
(default:TRUE
)application
(default:DataVirtuality_DV
)user-name
password
driver
(default:snowflake
)driver-class
(default:net.snowflake.client.jdbc.SnowflakeDriver
)jdbc-properties
(arbitrary extra properties)new-connection-sql
check-valid-connection-sql
(default:select 0
)min-pool-size
(default:2
)max-pool-size
(default:70
)cloudAgent
(default:FALSE
)
Here is an example:
CALL SYSADMIN.createConnection(
name
=>
'snowflake'
, jbossCLITemplateName =>
'snowflake'
, connectionOrResourceAdapterProperties =>
'host=<your_account_name.snowflakecomputing.com,db=<database>,user-name=<user_name>,password=<user_password>,warehouse=<warehouse>'
) ;;
CALL SYSADMIN.createDataSource(
name
=>
'snowflake'
, translator =>
'snowflake'
, modelProperties =>
'importer.defaultSchema=PUBLIC,importer.schemaPattern=PUBLIC,importer.useFullSchemaName=FALSE,importer.tableTypes="TABLE,VIEW"'
, translatorProperties =>
'supportsNativeQueries=true'
) ;;
A data source with multiple warehouses and role mapping:
CALL SYSADMIN.createConnection(
name
=>
'snowflake'
, jbossCLITemplateName =>
'snowflake'
, connectionOrResourceAdapterProperties =>
'host=<your_account_name.snowflakecomputing.com,db=<database>,user-name=<user_name>,password=<user_password>,warehouse=<warehouse>'
) ;;
CALL SYSADMIN.createDataSource(
name
=>
'snowflake'
, translator =>
'snowflake'
, modelProperties =>
'importer.defaultSchema=PUBLIC,importer.schemaPattern=PUBLIC,importer.useFullSchemaName=FALSE,importer.tableTypes="TABLE,VIEW"'
, translatorProperties =>
'supportsNativeQueries=true,roleMapping="DV_ROLE1=SF_ROLE1{WH1},DV_ROLE2=SF_ROLE2,DV_ROLE3=SF_ROLE3{WH3}'
) ;;
Translator Properties
Translator Properties Shared by All JDBC Connectors
(Properties listed in alphabetical order)
To view the full table, click the expand button in its top right corner
Name | Description | Default value |
---|---|---|
| Sets a template to convert Examples comparisonStringConversion=%s -- no conversion will be applied comparisonStringConversion= binary %s -- WHERE binary someStringExpression LIKE someOtherStringExpression comparisonStringConversion=(%s COLLATE Latin1_General_CS_AS) -- WHERE (someStringExpression COLLATE Latin1_General_CS_AS LIKE someOtherStringExpression) |
|
| Database time zone, used when fetching date, time, or timestamp values | System default time zone |
| Specific database version, used to fine-tune pushdown support | Automatically detected by the server through the data source JDBC driver, if possible |
| Only considered when |
|
| If |
|
| Maximum size of prepared insert batch |
|
| Sets a template to convert Examples OrderByStringConversion=%s -- no conversion will be applied OrderByStringConversion=( binary %s) -- ORDER BY (binary someStringExpression) OrderByStringConversion= cast ((%s) as varchar (2000) ccsid 1208) -- ORDER BY cast((someStringExpression) as varchar(2000) ccsid 1208) |
|
| If |
|
| Forces a translator to issue a Example SELECT x.* FROM table ( CALL "dwh.native" ( "request" => 'SELECT query, pid, elapsed, substring FROM svl_qlog ORDER BY STARTTIME DESC LIMIT 200' ) ) w , ARRAYTABLE( w.tuple COLUMNS query string, pid integer , elapsed string, "substring" string ) x; |
|
| If If |
|
| If If |
|
| If |
|
| if |
|
| Embeds a / comment / leading comment with session/request id in the source SQL query for informational purposes |
|
The names of the translator properties are case-sensitive.
Translator Properties Specific for Snowflake
To view the full table, click the expand button in its top right corner
Name | Description | Default value |
---|---|---|
| Value: Uploads data as files on Amazon S3 storage. Translator properties | |
Value: Uploads data as files on Azure Blob storage. Translator properties | ||
Value: Uploads data as files to the Snowflake internal stage. The value is case-insensitive. | ||
| Value: a path to a folder (relative or absolute). Specifies the folder to be used for creating temporary files instead of the system configured one. If not specified, the default OS tmp folder is used | |
| Value: a bucket name. Only for the | |
| Value: a bucket prefix. Only for the | |
| Value: boolean. Specifies if the bucket set by the |
|
| Value: a region. Only for the | |
| Only for the | |
| As Redshift and Vertica measure varchar size in bytes, not chars, and store strings in UTF-8 encoding, a char may be 1 to 4 bytes long. You can specify the percentage by which the byte size will exceed the original char size. Also, there is a special value: 65535 which makes every varchar to be of 65535 bytes long | |
| Keep temporary files after uploading |
|
| The name of the internal stage to be created or used for uploading the files. If not set, a named stage with a unique name starting with TEMPORARY_NAMED_STAGE_ will be created for uploading the files and removed when done being used by the command. | |
| For | |
| For | |
| For | |
| SAS Token used for accessing the Blob Storage from Snowflake. For | |
| Defines the role that is used to retrieve Snowflake metadata. If the property is not set it is used to cache the default role gotten from Snowflake via the | |
| Enables/disables the Snowflake roles usage |
|
| Used to map the roles defined in the CData Virtuality Server to the roles on the Snowflake side. Mandatory if the The roles must be mapped as follows: Snowflake warehouses can also be assigned via the In the case of authentication via LDAP AD roles must be mapped to Snowflake roles in the same way. LDAP role recursion is not taken into consideration when mapping Snowflake roles and warehouses, each LDAP role must be mapped individually. |
defaultRole, enableReAuth, roleMapping
translator properties available since v4.1
Mapping AD roles to Snowflake roles supported since v4.7
internal_stage
upload mode available since v4.8
Mapping warehouses to roles supported since v4.10
Data Source Properties
Data Source Properties Shared by All JDBC Connectors
(Properties listed in alphabetical order)
To view the full table, click the expand button in its top right corner
Name | Description | Default |
---|---|---|
| Replaces |
|
| Database catalogs to use. Can be used if the Only for Microsoft SQL Server and Snowflake:
| Exasol: EXA_DB All others: empty |
|
Please note that writing into a data source is only possible if this parameter is set. | Empty |
| Turns on metadata cache for a single data source even when the global option is turned off. Together with |
|
| Case-insensitive regular expression that will exclude a matching fully qualified procedure name from import | Empty |
| Comma-separated list of schemas (no | Oracle: APEX_PUBLIC_USER, DIP, FLOWS_040100, FLOWS_020100, FLOWS_FILES, MDDATA, ORACLE_OCM, SPATIAL_CSW_ADMIN_USR, SPATIAL_WFS_ADMIN_USR, XS$ NULL , BI, HR, OE, PM, IX, SH, SYS, SYSTEM, MDSYS, CTXSYS All others: empty |
| Case-insensitive regular expression that will exclude a matching fully qualified table name from import. Does not speed up metadata loading. Here are some examples: 1. Excluding all tables in the (source) schemas importer.excludeTables=(.*[.]sys[.].*|.*[.]INFORMATION_SCHEMA[.].*) 2. Excluding all tables except the ones starting with "public.br" and "public.mk" using a negative lookahead: importer.excludeTables=(?! public \.(br|mk)).* 3. Excluding "tablename11" from the list ["tablename1", "tablename11", "company", "companies"]: importer.excludeTables=.*\.(?!\btablename1\b|\bcompan).*
| Empty |
| Fetch size assigned to a resultset on loading metadata | No default value |
| If set to |
|
| If set to |
|
| If set to |
|
| If set to Please note that it is currently not possible to import procedures which use the same name for more than one parameter (e.g. same name for |
|
| Set to |
|
| If set to |
|
| If set to |
|
| Procedure(s) to import. If omitted, all procedures will be imported. | Empty |
| If set to |
|
| If set to |
|
| If set to |
|
| If set to |
|
| Schema(s) to import. If omitted or has "" value, all schemas will be imported. | Empty |
| If set to |
|
| Table(s) to import. If omitted, all tables will be imported. | Empty |
| Comma-separated list (without spaces) of table types to import. Available types depend on the DBMS. Usual format: Other typical types are | Empty |
| If set to | |
| If set to Please note that this may lead to objects with duplicate names when importing from multiple schemas, which results in an exception |
|
| If set to |
|
| If set to |
|
Escaping wildcards in importer.catalog
available since v4.0.8
Default values
and importer.catalog='EXA_DB'
importer.useCatalogName=FALSE
available since v4.4
importer.loadSourceSystemFunctions
is available since v4.6
set to importer.importProcedures
TRUE
by default for CData connector since v4.7