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 asSELECT * FROM sf1.tableXUNION ALLSELECT * 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 asSELECT * FROM sf1.tableX where hasRole('data', 'dv-role-1')UNION ALLSELECT * 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 returnedUNION ALLSELECT * FROM sf2.tableX where TRUE -- data is returned
This effectively becomes
SELECT * FROM sf2.tableX
