Redshift

Seemore natively supports AWS Redshift, which allows you to integrate your Redshift with your Seemore workspace seamlessly.

💭 TL;DR

  1. Select the source Redshift

  2. Provide your credentials ✍️

  3. Set up your configuration 🗄️

  4. Schedule automatic updates 🕑

📜 Prerequisites for a Redshift integration

  1. Redshift Seemore user: In order to create a database user for Seemore's integration system execute the following command on your Redshift warehouse -

CREATE GROUP seemore;
CREATE USER seemore_user PASSWORD <password> IN GROUP seemore;

  • Replace <password> with the desired password for the seemore user.

  1. Crawling permissions: Execute the following commands in order to grant access to Seemore's user in order to fetch metadata on your Redshift warehouse -

👀 Note: You would have to run those also for any future database/schema that you create. Make sure to repeat this process for each database/schema.

GRANT USAGE ON SCHEMA <schema> TO GROUP seemore;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT SELECT ON TABLES TO seemore;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO GROUP seemore;
GRANT SELECT ON pg_catalog.svv_redshift_databases TO GROUP seemore;
GRANT SELECT ON pg_catalog.svv_all_schemas TO GROUP seemore;
GRANT SELECT ON pg_catalog.svv_table_info TO GROUP seemore;
GRANT SELECT ON pg_catalog.svv_external_tables TO GROUP seemore;
GRANT SELECT on pg_catalog.stl_ddltext to GROUP seemore;
GRANT SELECT on pg_catalog.stl_query to GROUP seemore;
GRANT SELECT on pg_catalog.svl_query_summary to GROUP seemore;
GRANT SELECT on pg_catalog.stl_connection_log to GROUP seemore;
GRANT SELECT on pg_catalog.stl_undone to GROUP seemore;
GRANT SELECT on pg_catalog.stl_insert to GROUP seemore;
GRANT SELECT on pg_catalog.svl_statementtext to GROUP seemore;
ALTER USER seemore_user SYSLOG ACCESS UNRESTRICTED;

  • Replace <schema> with the desired schema for the seemore user to crawl.

  1. IAM Authentication (Optional): If basic authentication in enough in order to access your Redshift warehouse you don't need this step, skip to providing credentials step. i. Create IAM Policy: Inorder to create an IAM policy with the necessary permissions follow the steps in the AWS Identity and Access Management User Guide. Create the policy using the following JSON:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "redshift:GetClusterCredentials"
      ],
      "Resource": [
        "arn:aws:redshift:<region>:<account_id>:dbuser:<redshift_cluster>/seemore_user",
        "arn:aws:redshift:<region>:<account_id>:dbname:<redshift_cluster>/<database>"
      ]
    }
  ]
}

  • Replace <region> with the AWS region of your Redshift instance.

  • Replace <account_id> with your account ID.

  • Replace <redshift_cluster> with your Redshift cluster identifier.

  • Replace <database> with the name of the Redshift database. ii. IAM User Authentication:

    1. Create an AWS IAM user using aws CLI -

$ aws iam create-user --user-name seemore-redshift
$ aws iam create-access-key --user-name seemore-redshift
$ aws iam attach-user-policy --user-name seemore-redshift --policy-arn <policy_arn>

⚠️ once completed, keep the generated access keys, you won't be able to view it afterwards.

🚀 A step-by-step guide to integrate Redshift with Seemore

STEP 1: Select the source

  1. Log into your Settings >> Integrations.

  2. On the home screen, click on the + button on the top right corner. You will see a dialogue box with the list of sources available on your workspace.

  3. Select "Redshift" from the list of options, and click on "Next".

STEP 2: Provide Credentials

For Basic Redshift warehouse authentication

  1. Authentication method: Select Basic.

  2. Database Username: The database user you have created, if not changed type seemore.

  3. Password: The password you chose in the prerequisites step for the database user.

For IAM Redshift warehouse authentication

  1. Authentication method: Select IAM.

  2. Database Username: The database user you have created, if not changed type seemore.

  3. IAM User Access Key ID: The AccessKeyId generated by the create-access-key.

  4. IAM Secret Access Key: The SecretAccessKey generated by the create-access-key.

  5. Region: The region of your AWS Redshift cluster.

Redshift database credentials:

  1. Host: The Redshift database host.

  2. Port: The Redshift database port.

  3. Database: One of the the Redshift databases name.

Once you have filled in the details, click on "Next".

STEP 3: Data configuration

You can filter data asset you don't want to fetch base on regex that you can provide

Congratulations! You have now integrated Seemore with your Redshift 🎉

PreviousPowerBINextRivery

Last updated