Snowflake: working with multiple warehouses

Snowflake is a fantastic database for analytical storage and for reading and writing of data. Data Virtuality's Snowflake connector supports a single connection to a single database. If you need to connect to multiple databases, it is recommended you create multiple connections, one for each database.

For example, this Snowflake instance has four databases.

We currently have a single connection to the test_db database.

/* Create connection */
call SYSADMIN.createConnection(name => 'snowflake_dv', jbossCliTemplateName => 'snowflake', connectionOrResourceAdapterProperties => 'host=************************.snowflakecomputing.com,db=test_db,user-name=qa,check-valid-connection-sql="select 0",warehouse=DEMO_WH', encryptedProperties => 'password=**password**');;

/* Create data source */
call SYSADMIN.createDatasource(name => 'snowflake_dv', translator => 'snowflake', modelProperties => 'importer.catalog=TEST_DB,importer.tableTypes="TABLE,VIEW",importer.useFullSchemaName=FALSE,importer.schemaPattern=TEST_VZ,importer.defaultSchema=TEST_VZ', translatorProperties => '', encryptedModelProperties => '', encryptedTranslatorProperties => '');;

We create an additional connection to the SNOWFLAKE_SAMPLE_DATA database.

UNION ALL can be used within views to combine data across multiple data warehouses. In the next example, you will create connections to multiple Snowflake databases.

This will create a new data source called sf1 connected to warehouse TEST1

-- create Snowflake data source using Warehouse "TEST1"
EXEC SYSADMIN.createConnection(name => 'sf1', jbossCLITemplateName => 'snowflake', connectionOrResourceAdapterProperties => 'host=<your_account_name.snowflakecomputing.com,db=<database>,user-name=<user_name>,password=<user_password>,warehouse=TEST1') ;;
EXEC SYSADMIN.createDataSource(name => 'sf1', translator => 'snowflake', modelProperties => 'importer.defaultSchema=PUBLIC,importer.schemaPattern=PUBLIC,importer.useFullSchemaName=FALSE,importer.tableTypes="TABLE,VIEW"', translatorProperties => 'supportsNativeQueries=true') ;;

This will create a new data source called sf2 connected to warehouse TEST2

-- create Snowflake data source using Warehouse "TEST2"
EXEC SYSADMIN.createConnection(name => 'sf2', jbossCLITemplateName => 'snowflake', connectionOrResourceAdapterProperties => 'host=<your_account_name.snowflakecomputing.com,db=<database>,user-name=<user_name>,password=<user_password>,warehouse=TEST2') ;;
EXEC SYSADMIN.createDataSource(name => 'sf2', translator => 'snowflake', modelProperties => 'importer.defaultSchema=PUBLIC,importer.schemaPattern=PUBLIC,importer.useFullSchemaName=FALSE,importer.tableTypes="TABLE,VIEW"', translatorProperties => 'supportsNativeQueries=true') ;;

The data from sf1.tableX and sf2.tableX is combined using the UNION ALL statement

create view views.tableX as
SELECT * FROM sf1.tableX
UNION ALL
SELECT * FROM sf2.tableX;;

The following example uses the hasRole function to dynamically limit at runtime, which datasource is used based on the roles assigned to the user executing the query.

create view views.tableX_by_role as
SELECT * FROM sf1.tableX where hasRole('data', 'dv-role-1')
UNION ALL
SELECT * FROM sf2.tableX where hasRole('data', 'dv-role-2');;

For example, I do not have the role 'dv-role-1' assigned to my account. When I execute the following query it returns FALSE.

select hasRole('data', 'dv-role-1');;

For example, I have the role 'dv-role-2' assigned to my account. When I execute the following query it returns TRUE.

select hasRole('data', 'dv-role-2');;

When I execute the view select * from views.tableX_by_role, the hasRole function returns FALSE for 'dv-role-1' and TRUE for 'dv-role-2' base on the roles assigned to my account. This effectively becomes

SELECT * FROM sf1.tableX where FALSE -- no data returned
UNION ALL
SELECT * FROM sf2.tableX where TRUE -- data is returned

This effectively becomes

SELECT * FROM sf2.tableX