The following instructions guide you through connecting to Redshift data sources.

Contents

Requirements

  • Admin privileges in your Sigma organization; see Account types.
  • A Redshift data warehouse
  • A configured Redshift cluster
  • SELECT grants on all relevant tables, and USAGE grants on all relevant schemas in the Redshift cluster
  • We recommend that you avoid granting excessive permissions to the account you use when connecting to your data store; for example, you do not require SYSADMIN-level access.

Limitations

Support for the function ArrayDistinct.

Configure Redshift

Follow the steps below to configure Redshift to connect to Sigma.

  1. Go to Elastic IP’s in the EC2 Console.

    company apps

  2. Create an Elastic IP Address, this is required to make an IP address publicly accessible.
  3. Click Allocate Elastic IP address

    company apps
  4. Click Allocate.
  5. Go back to your Redshift Cluster and click Actions.
  6. Click Modify publicly accessible setting.
  7. Click Enable and select the Elastic IP address you created in the Elastic IP Address dropdown.

    company apps

  8. Go to Security Groups in the EC2 Console.

    company apps

  9. On the Create security group page, name and describe the security groups. 
    Note: a VPC is an elastic network that's populated by infrastructure, platform, and application services that share common security and interconnection.

    company apps
  10. Add Sigma’s IP addresses to both the Inbound and Outbound rules.
    Note that IPs depend on the cloud instance that hosts your organization. We recommend that you get the IP addresses from Sigma's connection dialog.

    company apps
  11.  Return to your Redshift Cluster and click the Properties tab. Click Edit on the Network and security settings tab. company apps
  12. Select the VPC Security Group you created and click Save Changes.

    company apps

Data Permissions

If don’t want to use an existing Redshift user, create a new user for Sigma. If you want to leverage write access, create a schema for Sigma and grant all privileges to your user on that schema. Be sure to Grant usage on all schemas and Grant select on all tables you wish to access in Sigma

create user sigma_user password ‘123’
create schema sigma_write;
grant all privileges on schema sigma_write to sigma_user;
grant usage on schema public to sigma_user;
grant select on all tables in schema public to sigma_user;

company apps

Additional Grants

'grant all on schema' does not give the user read/write abilities against a table in Redshift, even if the ownership was transferred. For that, you can use this grant:

grant all on all tables in schema {schema name} to {user};

See related official documentation from Amazon Redshift here.

Obtain Connection Info and Establish Connection

Obtain the endpoint from the General Information section of your Redshift Cluster, this will go in the “Host” field in Sigma

company apps

Required Info:

  • Host: Endpoint from above
  • Port: 5439
  • User: Your Redshift User
  • Password: PW for above user
  • Database:
  • Enable TLS: Turn on if you wish to use TLS encryption
  • Write Schema: Schema created in Redshift in previous step

Connect to your Redshift Cluster

  1. Open your Admin Portal by selecting Administration in the user menu at the top right of your screen.
  2. Select the Connections page from the left-hand panel.

    company apps
  3. Click the Create Connection button.
  4. Name your connection.
  5. Select Redshift under warehouse Type.
    Once selected, you're prompted to define your Connection Credentials.

    company apps

  6. Enter your cluster Endpoint URL In the Host field.
    Example: cluster.abcd.us-west-1.redshift.amazonaws.com
    Note: Sigma will connect from 44.229.241.60, 104.197.169.18, 104.197.193.23, and 54.188.54.135.
  7. Enter your cluster’s port number in the Port field.
  8. Enter your Redshift username in the User field. 
  9. Enter your Redshift password in the Password field.
  10. Enter your cluster’s database name in the Database field.
  11. [optional] Enable TLS encryption on your connection. 
  12. [optional] Connect to your instance via SSH Tunnel
    If you enable the SSH Tunnel, you're prompted to enter the Tunnel host and Tunnel port.
    See Connect through SSH.
    company apps
  13. Under Connection Features, specify the following
    Connection timeout
    The time before timeout (or cancellation), in seconds, that Sigma waits for the query to return results.
    Default is 120, or 2 minutes.
    Maximum is 600, or 10 minutes.
    Use friendly names
    This switch makes column names from the data source more readable.
    For example, a database column ORDER_NUMBER appears as Order Number.
    On by default.
  14. Enable write access. The user must have ownership of the WRITE schema, to  create, update, and drop tables.
  15. Click Create in the top right to create your configuration.

Locate your Redshift Cluster Credentials

  1. Go to the AWS Management Console.
  2. In the search bar, search and select Redshift.
    You're directed to the Redshift dashboard.
  3. In the left-hand navigation panel, select Clusters.
  4. Click on the cluster you would like to connect to Sigma.
    This directs you to the cluster page where you can find the required credentials to connect to Sigma.

Related Resources


Was this page helpful?
Yes No