Snowflake Connection Configuration: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
No edit summary
Line 15: Line 15:
# Select the created database, and click the '''Schema''' button to create a new schema. Define '''Name''' ''QPRPA'', and click the '''Create''' button.
# Select the created database, and click the '''Schema''' button to create a new schema. Define '''Name''' ''QPRPA'', and click the '''Create''' button.
# Select the created schema, and in the '''Privileges''' section, click the '''Privilege''' button. For '''Role''', select the ''QPRPA'' role. For '''Privileges''', select '''CREATE VIEW''', '''CREATE TABLE''', '''CREATE FILE FORMAT''' and '''USAGE'''. Click the '''Grant Privileges''' button.
# Select the created schema, and in the '''Privileges''' section, click the '''Privilege''' button. For '''Role''', select the ''QPRPA'' role. For '''Privileges''', select '''CREATE VIEW''', '''CREATE TABLE''', '''CREATE FILE FORMAT''' and '''USAGE'''. Click the '''Grant Privileges''' button.
# Running Snowflake warehouses consumes credits in the Snowflake account, and thus it's a good practice to set resource monitoring to control the credits used. In '''Compute''' > '''Resource Monitors''', click the '''Resource Monitor''' button, to create a new resources monitor. Choose a suitable monitor settings for your needs.


== Set Snowflake ODBC connection ==
== Set Snowflake ODBC connection ==

Revision as of 13:46, 28 January 2022

This page describes how to configure the Snowflake account and QPR ProcessAnalyzer for Snowflake connection.

Snowflake account configuration

These instructions provide an example how to configure the Snowflake account. Configurations can also be done differently based on other requirements, such as other usage of the same Snowflake account. These instructions are based on the minimum privilege principle.

  1. Create a new Snowflake account in the Snowflake site: https://www.snowflake.com/ (or use an existing account). The account is created to the selected cloud platform and site, so consider a location that is close to the QPR ProcessAnalyzer hosting site.
  2. Go to https://app.snowflake.com and login in to your Snowflake account.
  3. In Account > Roles, click the Role button to create a new role. Define Name QPRPA, and click Create Role.
  4. In Account > Users, click the User button to create a new user. Define User Name QPRPA and define a strong password. Check also that the Force user to change password on first time login is disabled. Click the Create User button.
  5. Select the created user, and click the Grant Role button. In the Role to grant list, select QPRPA, and click Grant.
  6. In Compute > Warehouses, click the Warehouse button to create a new warehouse. Define Name QPRPA, and select a suitable Size for the warehouse. In the Advanced Warehouse Options, check that Auto Resume and Auto Suspend are enabled. Set the Suspend After time based on your performance requirements (for example 5 minutes is a good initial value). Click Create Warehouse.
  7. Select the created warehouse and in the Privileges section and click the Privilege button. For Role, select the QPRPA role. For Privileges, select MONITOR, OPERATE and USAGE. Click the Grant Privileges button.
  8. In Data > Databases, click the Database button to create a new database. Define Name QPRPA, and click the Create button.
  9. Select the created database, and in the Privileges section, click the Privilege button. For Role, select the QPRPA role. For Privileges, select USAGE. Click Grant Privileges.
  10. Select the created database, and click the Schema button to create a new schema. Define Name QPRPA, and click the Create button.
  11. Select the created schema, and in the Privileges section, click the Privilege button. For Role, select the QPRPA role. For Privileges, select CREATE VIEW, CREATE TABLE, CREATE FILE FORMAT and USAGE. Click the Grant Privileges button.
  12. Running Snowflake warehouses consumes credits in the Snowflake account, and thus it's a good practice to set resource monitoring to control the credits used. In Compute > Resource Monitors, click the Resource Monitor button, to create a new resources monitor. Choose a suitable monitor settings for your needs.

Set Snowflake ODBC connection

To set the Snowflake ODBC connection for QPR ProcessAnalyzer, following steps are needed:

  1. Install Snowflake ODBC driver in the machine running QPR ProcessAnalyzer Server. More information about the ODBC driver installation: https://docs.snowflake.com/en/user-guide/odbc.html
  2. Configure Snowflake ODBC connection string to QPR ProcessAnalyzer configuration table. When configuring Snowflake as instructed above, the following kind of connection string can be used:
Driver={SnowflakeDSIIDriver};Server=<account_identifier>.snowflakecomputing.com;Database=QPRPA;Schema=QPRPA;Warehouse=QPRPA;Role=QPRPA;uid=QPRPA;pwd=<password>

Replace <password> with the actual password of the QPRPA user. In addition, replace <account_identifier> with the account identifier of your Snowflake account. More information about Snowflake account identifiers: https://docs.snowflake.com/en/user-guide/admin-account-identifier.html.

More information about Snowflake ODBC connection string: https://docs.snowflake.com/en/user-guide/odbc-parameters.html