Version: 8.7

Configure AWS & Qrvey for Snowflake

The steps below detail the required configuration to access Snowflake clusters for loading data into Qrvey for data analysis.

The changes allow Qrvey to access the cluster and also enable it to export data to S3 to be accessed by Qrvey.

Steps

Create a KMS Key
1. Open the AWS console.
2. Use the Qrvey deployment’s AWS account.
3. Create a new key.
4. Select the Asymmetric option.
5. Select Sign and verify.
6. Select Key spec RSA_2048.
7. Click Next.
8. Set Alias to “QRVEY_DEPLOYMENT-snowflake-kms”.
9. Click Next.
10. For “Key administrators”, select one or more AWS users to be the administrators.
11. Click Finish.
12. Open the details for the new key.
13. Select the Public key tab.
14. Copy the KMS public key, it will be used later. Copy without the opening and closing lines, or newlines.
15. Note the new KMS key’s ARN.
Create a Policy to give Qrvey permissions to the Snowflake cluster.
- a. Continue in the Qrvey AWS account.
- b. Open AWS Identity and Access Management (IAM).
- c. Go to Policies, and Create Policy.
- Set the following permissions for the Policy in the JSON tab:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
              "s3:PutObject",
              "s3:GetObject",
              "s3:GetObjectVersion",
              "s3:DeleteObject",
              "s3:DeleteObjectVersion"
            ],
            "Resource": "arn:aws:s3:::QRVEY_DEPLOYMENT-dataload-drdatacommons/sql_export/*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
            "Resource": "arn:aws:s3:::QRVEY_DEPLOYMENT-dataload-drdatacommons",
            "Condition": {
                "StringLike": {
                    "s3:prefix": [
                        "sql_export/*"
                    ]
                }
            }
        }
    ]
}

d. Click Next: Tags.
e. Click Next: Review.
f. Name the new Policy. For example: “SnowflakeDataAccessPolicy”

Create a new Role for the Policy, enabling access from Snowflake to S3.
- Open AWS Identity and Access Management (IAM).
- Go to Roles, then Create Role.
Configure Snowflake.
- a. Open the Snowflake console.
  - Create a Snowflake user.
  - Assign the user a ROLE that allows the user to read data.
- b. Create a Storage Integration in the Snowflake cluster.
- c. From Snowflake, run the CREATE STORAGE INTEGRATION command, replacing QRVEY_DEPLOYMENT and EXPORT_TO_S3_ARN.

CREATE STORAGE INTEGRATION QrveyExportToS3_QRVEY_DEPLOYMENT
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = S3
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'EXPORT_TO_S3_ARN'
  STORAGE_ALLOWED_LOCATIONS = ('s3://QRVEY_DEPLOYMENT-dataload-drdatacommons/sql_export/')

d. Grant permission to Snowflake user role, as follows:

GRANT USAGE ON integration QrveyExportToS3_QRVEY_DEPLOYMENT to role SNOWFLAKE_USER_ROLE;

e. Set the Trust Relationship between the Snowflake and the AWS account.
- Run this query:

DESC INTEGRATION QrveyExportToS3_QRVEY_DEPLOYMENT;

snowflake1

f. From the query response note the values forSTORAGE_AWS_IAM_USER_ARN STORAGE_AWS_EXTERNAL_ID
g. Return to the IAM console, and open the role created above.
h. Click the Trust Relationship tab.
i. Click Edit.
j. Set the trust relationship following this example, replacing the values from the query above.

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "",
      "Effect": "Allow",
      "Principal": {
        "AWS": "STORAGE_AWS_IAM_USER_ARN"
      },
      "Action": "sts:AssumeRole",
      "Condition": {
        "StringEquals": {
          "sts:ExternalId": "STORAGE_AWS_EXTERNAL_ID"
        }
      }
    }
  ]
}

k. Update the Snowflake user account to include the KMS key created above.
```
ALTER USER SNOWFLAKE_USER SET rsa_public_key='KMS_PUBLIC_KEY'
```

Create a new Secret that points to the Snowflake cluster.
- Open AWS Secrets Manager.
- Create a new Secret.
- Select Other type of secret.
- Select the Plaintext tab.
- Set the key/value pairs with the following example. Specify the Snowflake user, password, account, the KMS ARN, and the storageIntegration.

{
  "user": "XXXX",
  "password": "****",
  "account": "acountid.region",
  "kmsARNId": "KMS_KEY_ARN",
  "storageIntegration": "QrveyExportToS3_QRVEY_DEPLOYMENT"
}

f. Click Next.
g. Enter a Secret name. For example SnowflakeClusterSecret.
h. Click Next.
i. Click Store.
j. Note this Secret’s ARN string for later use.

Set permissions for the Qrvey Database lambda.
- Open AWS Identity and Access Management (IAM).
- Click Roles
- Search for the lambdas role. It contains these strings: “DBDatasourcePumpFunction” and “elastic-view-function-role”. There are Roles for each Qrvey deployment. Select the role applying to the current Qrvey deployment.
- Click Add inline policy.
- Click the JSON tab.
- Paste the Sample Inline Policy for the lambdas shown below, replacing the appropriate ARN.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "getSecretValue",
            "Effect": "Allow",
            "Action": [
                "secretsmanager:GetSecretValue"
            ],
            "Resource": [
                "<SECRET_ARN>"
            ]
        },
        {
            "Sid": "getPublicKey",
            "Effect": "Allow",
            "Action": [
                "kms:GetPublicKey",
                "kms:Sign"
            ],
            "Resource": [
                "KMS_KEY_ARN"
            ]
        }
    ]
}

Click Review policy.
Name the policy. Example: SnowflakeDataAccessAssumeRole
Click Save, and see that the new policy has been added.

Open the Qrvey application.
- Click Datasets.
- ClickNew Connections.
- Create a new Connection for Snowflake.
- Enter the Secret ARN created above.
- Test the new Connection.

Steps​

Steps