Skip to content

Latest commit

 

History

History
204 lines (149 loc) · 9.19 KB

aws-redshift.mdx

File metadata and controls

204 lines (149 loc) · 9.19 KB

AWS Redshift

Prerequisites

If the cluster is configured within a VPC, then Cube must have a network route to the cluster.

Setup

Manual

Add the following to a .env file in your Cube project:

Password Authentication

CUBEJS_DB_TYPE=redshift
CUBEJS_DB_HOST=my-redshift-cluster.cfbs3dkw1io8.eu-west-1.redshift.amazonaws.com
CUBEJS_DB_NAME=my_redshift_database
CUBEJS_DB_USER=<REDSHIFT_USER>
CUBEJS_DB_PASS=<REDSHIFT_PASSWORD>

IAM Authentication

For enhanced security, you can configure Cube to use IAM authentication instead of username and password. When running in AWS (EC2, ECS, EKS with IRSA), the driver can use the instance's IAM role to obtain temporary database credentials automatically.

Omit CUBEJS_DB_USER and CUBEJS_DB_PASS to enable IAM authentication:

CUBEJS_DB_TYPE=redshift
CUBEJS_DB_HOST=my-redshift-cluster.xxx.eu-west-1.redshift.amazonaws.com
CUBEJS_DB_NAME=my_redshift_database
CUBEJS_DB_SSL=true
CUBEJS_DB_REDSHIFT_AWS_REGION=eu-west-1
CUBEJS_DB_REDSHIFT_CLUSTER_IDENTIFIER=my-redshift-cluster

The driver uses the AWS SDK's default credential chain (IAM instance profile, EKS IRSA, etc.) to obtain temporary database credentials via the redshift:GetClusterCredentialsWithIAM API.

IAM Role Assumption

For cross-account access or enhanced security, you can configure Cube to assume an IAM role:

CUBEJS_DB_REDSHIFT_AWS_REGION=eu-west-1
CUBEJS_DB_REDSHIFT_CLUSTER_IDENTIFIER=my-redshift-cluster
CUBEJS_DB_REDSHIFT_ASSUME_ROLE_ARN=arn:aws:iam::123456789012:role/RedshiftAccessRole
CUBEJS_DB_REDSHIFT_ASSUME_ROLE_EXTERNAL_ID=unique-external-id

Cube Cloud

In some cases you'll need to allow connections from your Cube Cloud deployment IP address to your database. You can copy the IP address from either the Database Setup step in deployment creation, or from Settings → Configuration in your deployment.

The following fields are required when creating an AWS Redshift connection:

Cube Cloud also supports connecting to data sources within private VPCs if dedicated infrastructure is used. Check out the VPC connectivity guide for details.

Environment Variables

Environment Variable Description Possible Values Required
CUBEJS_DB_HOST The host URL for a database A valid database host URL ✅
CUBEJS_DB_PORT The port for the database connection A valid port number ❌
CUBEJS_DB_NAME The name of the database to connect to A valid database name ✅
CUBEJS_DB_USER The username used to connect to the database A valid database username ✅1
CUBEJS_DB_PASS The password used to connect to the database A valid database password ✅1
CUBEJS_DB_SSL If true, enables SSL encryption for database connections from Cube true, false ❌
CUBEJS_DB_MAX_POOL The maximum number of concurrent database connections to pool. Default is 16 A valid number ❌
CUBEJS_DB_REDSHIFT_CLUSTER_IDENTIFIER The Redshift cluster identifier. Required for IAM authentication A valid cluster identifier ❌
CUBEJS_DB_REDSHIFT_AWS_REGION The AWS region of the Redshift cluster. Required for IAM authentication A valid AWS region ❌
CUBEJS_DB_REDSHIFT_ASSUME_ROLE_ARN The ARN of the IAM role to assume for cross-account access A valid IAM role ARN ❌
CUBEJS_DB_REDSHIFT_ASSUME_ROLE_EXTERNAL_ID The external ID for the assumed role's trust policy A string ❌
CUBEJS_DB_EXPORT_BUCKET_REDSHIFT_ARN ❌
CUBEJS_CONCURRENCY The number of concurrent queries to the data source A valid number ❌

1 Required when using password-based authentication. When using IAM authentication, omit these and set CUBEJS_DB_REDSHIFT_CLUSTER_IDENTIFIER and CUBEJS_DB_REDSHIFT_AWS_REGION instead. The driver uses the AWS SDK's default credential chain (IAM instance profile, EKS IRSA, etc.) to obtain temporary database credentials.

Pre-Aggregation Feature Support

count_distinct_approx

Measures of type count_distinct_approx can not be used in pre-aggregations when using AWS Redshift as a source database.

Pre-Aggregation Build Strategies

To learn more about pre-aggregation build strategies, head here.

Feature Works with read-only mode? Is default?
Batching ❌ ✅
Export Bucket ❌ ❌

By default, AWS Redshift uses batching to build pre-aggregations.

Batching

Cube requires the Redshift user to have ownership of a schema in Redshift to support pre-aggregations. By default, the schema name is prod_pre_aggregations. It can be set using the pre_aggregations_schema configration option.

No extra configuration is required to configure batching for AWS Redshift.

Export bucket

AWS Redshift only supports using AWS S3 for export buckets.

AWS S3

For improved pre-aggregation performance with large datasets, enable export bucket functionality by configuring Cube with the following environment variables:

Ensure the AWS credentials are correctly configured in IAM to allow reads and writes to the export bucket in S3.

CUBEJS_DB_EXPORT_BUCKET_TYPE=s3
CUBEJS_DB_EXPORT_BUCKET=my.bucket.on.s3
CUBEJS_DB_EXPORT_BUCKET_AWS_KEY=<AWS_KEY>
CUBEJS_DB_EXPORT_BUCKET_AWS_SECRET=<AWS_SECRET>
CUBEJS_DB_EXPORT_BUCKET_AWS_REGION=<AWS_REGION>

SSL

To enable SSL-encrypted connections between Cube and AWS Redshift, set the CUBEJS_DB_SSL environment variable to true. For more information on how to configure custom certificates, please check out Enable SSL Connections to the Database.