Syncing from Redshift

To use Redshift as a source, you only need to supply read-access to the database.

1. In Polytomic, go to Connections → Add Connection → Redshift.

2. Enter host and credential details

Note: The AWS Access Key/Secret Key and Bucket configuration are only required if you plan to use Redshift as a destination. If you only plan to only sync from Redshift, you can leave these fields blank.

Syncing to Redshift

If you wish to write to Redshift, there are two requirements:

1. A user with write permission to the table/schemas that you want to load data into
2. An S3 bucket in the same region as your Redshift server and an Access Key/Secret Key with read/write access to the bucket. Polytomic will use these credentials to:

• Write intermediary load files to the bucket
• Instruct Redshift to load from the same bucket

Files are removed as each load job completes - the bucket is only used as a staging area inside your own AWS account as we load files into your Redshift cluster. While Polytomic will make an effort to clean up intermediary files, it is wise to set a file retention policy on the bucket to periodically delete or archive files.

Once you have set up a user and an S3 bucket with an Access Key/Secret Key pair, enter those details in the connection configuration above.

Permissions

Depending on what features you decide to use with Polytomic, different permissions may be required. The easiest thing to do is to create a database specific to Polytomic, with full permissions.

Basic permissions

The basic permissions cover what Polytomic needs to have a healthy connection and to sync from Redshift:

• USAGE on any schemas that you expect to use with Polytomic
  • e.g. GRANT USAGE ON SCHEMA my_schema TO polytomic;
• SELECT on any tables/views that you intend to read from Polytomic
  • e.g. GRANT SELECT ON ALL TABLES IN SCHEMA my_schema TO polytomic;
  • e.g. GRANT SELECT ON ALL VIEWS IN SCHEMA my_schema TO polytomic;

Write permissions: model syncs

For writing to Redshift from model syncs (e.g. writing the results of custom SQL transformations), you should also grant the following permissions to the Redshift Polytomic user:

• TEMP.
• INSERT, DELETE, UPDATE on any tables that you intend to write into (including future tables, if applicable).
• TRUNCATE for replace-mode syncs and full resyncs.
• ALTER , to support automatic column-addition to tables.
• CREATE on tables, if you'd like to write a model sync's results to a new table.
• CREATE on schemas, if you'd like to write a model sync's results to a new table.

Write permissions: bulk syncs

Polytomic requires full ownership over a database to write into, as Polytomic will fully manage the schemas and tables it writes to.

In addition to all of the above permissions, you should grant the following permission to the Polytomic user:

• DROP

S3

If you are using Redshift as a destination, you must provide an S3 bucket to use. Polytomic will use this bucket as a staging area; temporarily loading data into it before copying to Redshift data .

Here's a sample access policy for S3:

{
	"Statement": [
		{
			"Action": [
				"s3:*Object",
				"s3:ListBucket"
			],
			"Effect": "Allow",
			"Resource": [
				"arn:aws:s3:::your-bucket-name",
				"arn:aws:s3:::your-bucket-name/*"
			],
			"Sid": "PolytomicS3BucketAccessRedshift"
		}
	],
	"Version": "2012-10-17"
}