Page tree
Skip to end of metadata
Go to start of metadata

You can set up a data connection to connect to your Snowflake data warehouse on AWS and Azure.

Prerequisites for creating a Snowflake connection

  1. The Access role used for creating the connection must have the following permissions assigned to your Snowflake staging database.
    Grant usage on the database:
    GRANT USAGE ON DATABASE <database> TO ROLE <role>;

    Grant usage on the schema:
    GRANT USAGE ON SCHEMA <database>.public TO ROLE <role>;

    Grant create stage on the public schema:
    GRANT CREATE STAGE ON SCHEMA <database>.public TO ROLE <role>;
  2. In OAuth token-based authentication, you need an access token to access Snowflake resources and a refresh token to be used to get a new access token in case it expires.
    To be able to fetch tokens from any Snowflake server, the Kyvos application should be registered with the target Snowflake server and this configuration must be done by the Snowflake Admin user (outside of Kyvos). Once this is done, Admin gets client_id, client_secret, authorization URL, and token URL. You need these property values when creating a Kyvos connection to enable the Kyvos server to fetch tokens from the Snowflake server.
  3. If you have deployed VPC with Internet Gateway, then you must assign an elastic IP to the BI Server.

To set up or view a Snowflake warehouse connection

  1. From the Toolbox, click Setup, then Connections.
  2. From the Actions menu (  ) click Add Connection.
  3. Enter a name or select it from the Connection list.
  4. Select Warehouse from the Category List.
    There may be more than one warehouse connection.
  5. For Providers, select Snowflake, and enter provider details.
  6. Specify the server on which the master node is configured. For example, df34534.us-east-1.snowflakecomputing.com. This URL is provided by Snowflake.
  7. Enter the full name of the account provided by Snowflake.
  8. Enter the name of the virtual warehouse to use for reading data once connected to Snowflake.
  9. Enter the default name of the staging database provided with your Snowflake account.
  10. Specify the access control role to use during the Snowflake session. For example, SysAdmin.
  11. Enter the URL to access the Snowflake server. For example, jdbc:snowflake://abc-west-1.mycompany.com/.
  12. For Authentication Type, select Snowflake.
  13. Enter the User Name and Password to use.
  14. To enable the connection for raw data, click the Is Default SQL Engine checkbox to set this connection to run the default SQL engine. 
  15. Click Properties to view or set properties.

See the Provider parameters table for details.

To set up or view a Snowflake warehouse connection using Oauth authentication

  1. From the Toolbox, click Setup, then Connections.
  2. From the Actions menu ( ⋮ ) click Add Connection.
  3. Enter a name or select it from the Connection list.
  4. Select Warehouse from the Category List.
    There may be more than one warehouse connection.
  5. For Providers, select Snowflake, and enter provider details.
  6. Specify the server on which the master node is configured. For example, df34534.us-east-1.snowflakecomputing.com. This URL is provided by Snowflake.
  7. Enter the full name of the account provided by Snowflake.
  8. Enter the name of the virtual warehouse to use for reading data once connected to Snowflake.
  9. Enter the default name of the staging database provided with your Snowflake account.
  10. Specify the access control role to use during the Snowflake session. For example, SysAdmin.
  11. Enter the URL to access the Snowflake server. For example, jdbc:snowflake://abc-west-1.mycompany.com/.
  12. For Authentication type, select Oauth.
  13. Click the clipboard icon to copy the Redirect URL so that you can set it on the Snowflake server.
  14. Enter the Client ID created when you registered your client with Snowflake.
  15. Enter the Client Secret when you registered your client with Snowflake.
  16. Enter the Token URL where the token is stored.
  17. Click Fetch Tokens to get new tokens. You must provide the Client ID, Client Secret, and Token URL.
  18. Enter the Access Token which represents the authorization granted to a client by a user to access their data using a specified role.
  19. If needed, click Refresh Token. When known, the expiration date is shown. 
  20. Click Subscribe to get notifications for Refresh Token.
  21. To enable the connection for raw data, click the Is Default SQL Engine checkbox to set this connection to run the default SQL engine.
  22. Click Properties to view or set properties.

See the Provider parameters table for details.

To refresh connections, click the menu ( ⋮ ) at the top of the Connections column and select Refresh.

Configurations for building cubes using Snowflake warehouse

To use the Snowflake connection for cube building, add the following Snowflake jar files in the Kyvos default connections properties:

kyvos.connection.classpath.jar

;../lib/snowflake-jdbc-3.6.8.jar;../lib/spark-snowflake_2.11-2.4.7-spark_2.1.jar

kyvos.connection.sparkclasspath.jar

;../lib/snowflake-jdbc-3.6.8.jar;../lib/spark-snowflake_2.11-2.4.7-spark_2.1.jar

  • No labels