Snowflake Connection Configuration: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
No edit summary
No edit summary
Line 1: Line 1:
This page describes how to configure the [[#Snowflake account configuration|Snowflake account]] and [[#Set Snowflake ODBC connection string|QPR ProcessAnalyzer for the Snowflake connection]].
This page describes how to configure the [[#Snowflake account configuration|Snowflake account]] and [[#Set Snowflake ODBC connection string|QPR ProcessAnalyzer for Snowflake connection]].


== Snowflake account configuration ==
== Snowflake account configuration ==
These instructions provide an example how to configure Snowflake. The configuration can also be done differently based on needs and other usage of the same Snowflake account.
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.


# Create a new account to Snowflake in the Snowflake site: https://www.snowflake.com/ (or use existing account). The account is created to a certain cloud and site, so consider a location that is close to the QPR ProcessAnalyzer hosting site.
# 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.
# In '''Account''' > '''Roles''', click '''Role''' button to create a new role. Define '''Name''' QPRPA.
# Go to https://app.snowflake.com and login in to your Snowflake account.
# In '''Account''' > '''Users''', click '''User''' button to create a new user. Define '''User Name''' QPRPA and define a strong password. Check that the '''Force user to change password on first time login''' is disabled.
# In '''Account''' > '''Roles''', click the '''Role''' button to create a new role. Define '''Name''' ''QPRPA'', and click '''Create Role'''.
# Select the created users, and click '''Grant to User'''.
# 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.
# In '''Compute''' > '''Warehouses''', click '''Warehouse''' button to create a new warehouse. Define '''Name''' QPRPA, and select 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).
# Select the created user, and click the '''Grant to User''' button.
# Select the created warehouse and in the '''Privileges''' section, select '''Privilege''' button. To '''Role''' select the QPRPA role and select '''Privileges''' select '''MONITOR''', '''OPERATE''' and '''USAGE'''.
# In '''Compute''' > '''Warehouses''', click the '''Warehouse''' button to create a new warehouse. Define '''Name''' ''QPRPA'', and select 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).
# In '''Data''' > '''Databases''', click '''Database''' button to create a new database. Define '''Name''' QPRPA. To '''Role''', select the QPRPA role. To '''Privileges''', select '''USAGE'''.
# Select the created warehouse and in the '''Privileges''' section, select '''Privilege''' button. To '''Role''' select the ''QPRPA'' role and select '''Privileges''' select '''MONITOR''', '''OPERATE''' and '''USAGE'''.
# Select the created database, and in the '''Privileges''' section, click '''Privilege'''.  
# In '''Data''' > '''Databases''', click the '''Database''' button to create a new database. Define '''Name''' ''QPRPA''. To '''Role''', select the ''QPRPA'' role. To '''Privileges''', select '''USAGE'''.
# Select the created database, and click '''Schema'''. Define '''Name''' QPRPA.
# Select the created database, and in the '''Privileges''' section, click the '''Privilege''' button.  
# Select the created schema, and in the '''Privileges''' section, click '''Privilege'''. To '''Role''', select the QPRPA role. To '''Privileges''', select '''CREATE VIEW''', '''CREATE TABLE''', '''CREATE FILE FORMAT''', '''USAGE'''.
# Select the created database, and click the '''Schema'''. Define '''Name''' ''QPRPA''.
# Select the created schema, and in the '''Privileges''' section, click the '''Privilege''' button. To '''Role''', select the ''QPRPA'' role. To '''Privileges''', select '''CREATE VIEW''', '''CREATE TABLE''', '''CREATE FILE FORMAT''', '''USAGE'''.


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

Revision as of 23:54, 27 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 to User button.
  6. In Compute > Warehouses, click the Warehouse button to create a new warehouse. Define Name QPRPA, and select 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).
  7. Select the created warehouse and in the Privileges section, select Privilege button. To Role select the QPRPA role and select Privileges select MONITOR, OPERATE and USAGE.
  8. In Data > Databases, click the Database button to create a new database. Define Name QPRPA. To Role, select the QPRPA role. To Privileges, select USAGE.
  9. Select the created database, and in the Privileges section, click the Privilege button.
  10. Select the created database, and click the Schema. Define Name QPRPA.
  11. Select the created schema, and in the Privileges section, click the Privilege button. To Role, select the QPRPA role. To Privileges, select CREATE VIEW, CREATE TABLE, CREATE FILE FORMAT, USAGE.

Set Snowflake ODBC connection string

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 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

Retrieved from "https://wiki.onqpr.com/pa/index.php?title=Snowflake_Connection_Configuration&oldid=19830"