Connect AWS Redshift

Suggest Edits

1. Whitelist Bigeye IP Address

For Bigeye to connect to your Redshift database, you’ll need to allow our inbound IP address ( 35.163.65.120 ) in your Cluster’s Security Group settings:

  1. Log in to the Redshift Management Console.
  2. Select Clusters from the left sidebar then select the cluster you want to connect to Bigeye.
  3. From the Cluster Details page, navigate to the Properties tab and scroll to the Network and Security section.
  4. Under VPC Security Groups, click the Security Group you want to use to allow communication from Bigeye.
  5. Click the Inbound Rules tab at the bottom of the page then click Edit.
  6. Click Add Rule to add a new Inbound Rule.
    a. Set the Type to Redshift.
    b. Adjust the Port, if needed.
    c. Enter the following into the Source field: 35.163.65.120
  7. Click Save.
1600

2. Ensure that the Redshift cluster is publicly accessible

  1. From the AWS Management Console, navigate to the Amazon Redshift console.
  2. On the navigation menu, choose CLUSTERS.
  3. Select the cluster that you want to modify.
  4. Choose Actions.
  5. Choose Modify Publicly accessible setting.
  6. Choose Enable.
  7. Choose Save changes.
372

3. Create Dedicated User for Bigeye

You should create a new user and grant SELECT permissions to the tables the user (i.e., Bigeye) will access.

To create a new user, go back to the Redshift console and connect the query editor to your data source and run the query below, replacing <bigeye_user> and <password> with your new username and password combination:

📘

The password must contain at least one uppercase, one lowercase, and one numeric character.

CREATE USER <bigeye_user> WITH PASSWORD '<password>' SYSLOG ACCESS UNRESTRICTED;
GRANT SELECT ON svv_table_info to <username>`;

To grant select permissions on all tables in a schema, run the query below, replacing <schema_name> with the name of your schema and <bigeye_user> with the user you created above.

GRANT USAGE ON SCHEMA <schema_name> TO <bigeye_user>;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <bigeye_user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO <bigeye_user>;

If you’d like to grant SELECT permissions on a table-by-table basis, you can run the query below to generate the queries to GRANT SELECT ON each table in a schema. You’ll then need to exclude any grant statements for tables that you don’t want the user to access.

📘

The AWS Redshift query editor will not run multi-statement queries; it’s recommended you use SQL Workbench to connect to Redshift and run multiple queries at one time

SELECT 'GRANT SELECT ON '||schemaname||'."'||tablename||'" TO <username>;' FROM pg_tables WHERE schemaname IN ('<schema>') ORDER BY schemaname, tablename;

Copy the output of this command and paste it into SQL workbench, but make sure to remove any lines for tables that you do not want the user to access.

4. Gather Redshift connection details

You'll want to gather the following connection details from Redshift:

  • Endpoint: The endpoint of your Redshift cluster database

    Go to the Properties tab of your Redshift Cluster Details page.

    Under the Connection details section, copy the Endpoint up to but not including the colon (:).

807

  • Database Port: default port number for Redshift is 5439

    From your Redshift Cluster Details page, you can find the port number under the Database configurations section of the Properties tab.

  • Username: username of the Redshift read-only user you created previously

  • User Password: password of the Redshift read-only user you created

  • Database Name: name of your Redshift database

    You can also find your Database Name under the Database configurations section of the Properties tab.

5. Add Redshift as data source in Bigeye

From the Catalog page click Add source. On the modal that appears, select Redshift.

1266


To connect Redshift, you will need to input the following information:

  • Alias - Enter any string, this is how your source will appear in the Bigeye Catalog
  • Vendor - Redshift
  • Host - endpoint from step 3
  • Port - port from step 3
  • Database name - database name from step 3
  • Username - Username from step 3
  • Password - User password from step 3

Once filled out, the form should look something like this

1328

6. Test connection

This step will query your warehouse to ensure that the user you provided has the permissions necessary to read data and run all Bigeye metric types. If an error is returned, return to step 2 above to ensure the user is permissioned correctly in snowflake.

7. Add metrics

If test connection is successful, you will have the option to deploy Metadata Metrics on your schemas. We recommend broad coverage of these metrics across your warehouse to detect the most common data quality issues.

Once you’ve added your source, Bigeye will start profiling your data to recommend autometrics. It can take up to 24 hours for this process to complete. Proceed to the Getting Started guide for next steps.

Updated 6 months ago