Redshift | Polytomic Docs

Supports:

✅ Models
✅ Model sync destination
✅ Bulk sync source
✅ Bulk sync destination

Connection

Configuration

Name	Type	Description	Required
`auth_mode`	`string`	Authentication Method How to authenticate with AWS. Defaults to Access Key and Secret. Accepted values: `access_key_and_secret`, `iam_role`	`true`
`aws_access_key_id`	`string`	AWS Access Key ID (destinations only) Access Key ID with read/write access to a bucket. More info: https://docs.polytomic.com/docs/redshift (required if `auth_mode` is “aws_access_key_and_secret”)	`false`
`aws_secret_access_key`	`string`	AWS Secret Access Key (destinations only) (required if `auth_mode` is “aws_access_key_and_secret”)	`false`
`bulk_sync_staging_schema`	`string`	Staging schema name	`false`
`database`	`string`	Database	`true`
`hostname`	`string`	Hostname	`true`
`iam_role_arn`	`string`	IAM Role ARN (required if `auth_mode` is “iam_role”)	`false`
`password`	`string`	Password	`true`
`port`	`integer`	Port	`true`
`s3_bucket_name`	`string`	S3 Bucket Name (destinations only) Name of bucket used for staging data load files	`false`
`s3_bucket_region`	`string`	S3 Bucket Region (destinations only) Region of bucket. Note: must match region of redshift server	`false`
`ssh`	`boolean`	Connect over SSH tunnel	`false`
`ssh_host`	`string`	SSH host	`false`
`ssh_port`	`integer`	SSH port	`false`
`ssh_private_key`	`string`	Private key	`false`
`ssh_user`	`string`	SSH user	`false`
`use_bulk_sync_staging_schema`	`boolean`	Use custom bulk sync staging schema	`false`
`username`	`string`	Username	`true`

Example

1 {
2   "name": "Redshift connection",
3   "type": "redshift",
4   "configuration": {
5     "auth_mode": "access_key_and_secret",
6     "aws_access_key_id": "AKIAIOSFODNN7EXAMPLE",
7     "aws_secret_access_key": "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY",
8     "bulk_sync_staging_schema": "",
9     "database": "mydb",
10     "hostname": "mycluster.us-west-2.redshift.amazonaws.com",
11     "iam_role_arn": "",
12     "password": "password",
13     "port": 5439,
14     "s3_bucket_name": "my-bucket",
15     "s3_bucket_region": "us-west-2",
16     "ssh": false,
17     "ssh_host": "bastion.example.com",
18     "ssh_port": 22,
19     "ssh_private_key": "",
20     "ssh_user": "",
21     "use_bulk_sync_staging_schema": false,
22     "username": "redshift_user"
23   }
24 }

Read-only properties

Name	Type	Description	Required
`aws_user`	`string`	User ARN	`false`
`external_id`	`string`	External ID for the IAM role	`false`

Model Sync

Source

Configuration

Name	Type	Description	Required
`query`	`string`		`false`
`schema`	`string`	Schema	`false`
`table`	`string`	Table	`false`
`view`	`string`	View	`false`

Example

1 {
2   ...
3   "configuration": {
4     "query": "SELECT * FROM sampledata.users",
5     "schema": "sampledata",
6     "table": "users",
7     "view": "active_users"
8   }
9 }

Target

Redshift connections may be used as the destination in a model sync.

All targets

Configuration

Name	Type	Description	Required
`created_column`	`string`	’Created at’ timestamp column	`false`
`preserve_table_on_resync`	`boolean`	Preserve destination table when resyncing	`false`
`updated_column`	`string`	’Updated at’ timestamp column	`false`
`write_record_timestamps`	`boolean`	Write row timestamp metadata	`false`

Example

1 {
2   ...
3   "target": {
4       "configuration": {
5         "created_column": "",
6         "preserve_table_on_resync": false,
7         "updated_column": "",
8         "write_record_timestamps": false
9     }
10   }
11 }

Bulk Sync

Destination

Configuration

Name	Type	Description	Required
`advanced`	`object`		`false`
`mirror_schemas`	`boolean`	Mirror schemas	`false`
`schema`	`string`	Output schema	`false`

Example

1 {
2   ...
3   "destination_configuration": {
4     "advanced": {
5         "empty_strings_null": false,
6         "hard_deletes": false,
7         "table_prefix": "",
8         "truncate_existing": false
9     },
10     "mirror_schemas": false,
11     "schema": "schema"
12   }
13 }

Type handling

Destination types

POLYTOMIC TYPE	REDSHIFT TYPE
`array<>`	`SUPER`
`bigint`	`BIGINT`
`boolean`	`BOOL`
`date`	`DATE`
`datetime`	`TIMESTAMP`
`decimal(precision, scale)`	`NUMERIC(precision,scale)`
`double`	`FLOAT8`
`int`	`INTEGER`
`json`	`SUPER`
`jsonarray`	`SUPER`
`number`	`NUMERIC(38,18)`
`object{}`	`SUPER`
`single`	`FLOAT4`
`smallint`	`SMALLINT`
`string`	`VARCHAR(MAX)`
`time`	`VARCHAR(255)`

Source types

REDSHIFT TYPE	POLYTOMIC TYPE
`4000`	`json`
`BIGINT`	`bigint`
`BOOL`	`boolean`
`BOOLEAN`	`boolean`
`BPCHAR`	`string`
`CHAR`	`string`
`CHARACTER`	`string`
`CHARACTER VARYING`	`string`
`DATE`	`date`
`DECIMAL`	`number`
`DECIMAL(precision, scale)`	`decimal(precision, scale)`
`DOUBLE PRECISION`	`double`
`FLOAT`	`double`
`FLOAT4`	`single`
`FLOAT8`	`double`
`INT`	`int`
`INT2`	`smallint`
`INT4`	`int`
`INT8`	`bigint`
`INTEGER`	`int`
`NCHAR`	`string`
`NUMERIC`	`number`
`NUMERIC(precision, scale)`	`decimal(precision, scale)`
`NVARCHAR`	`string`
`REAL`	`single`
`SMALLINT`	`smallint`
`STRING`	`string`
`TEXT`	`string`
`TIME`	`time`
`TIME WITH TIME ZONE`	`time`
`TIME WITHOUT TIME ZONE`	`time`
`TIMESTAMP`	`datetime`
`TIMESTAMP WITH TIME ZONE`	`datetime_tz`
`TIMESTAMP WITHOUT TIME ZONE`	`datetime`
`TIMESTAMPTZ`	`datetime_tz`
`TIMETZ`	`time`
`VARCHAR`	`string`

1	{
2	"name": "Redshift connection",
3	"type": "redshift",
4	"configuration": {
5	"auth_mode": "access_key_and_secret",
6	"aws_access_key_id": "AKIAIOSFODNN7EXAMPLE",
7	"aws_secret_access_key": "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY",
8	"bulk_sync_staging_schema": "",
9	"database": "mydb",
10	"hostname": "mycluster.us-west-2.redshift.amazonaws.com",
11	"iam_role_arn": "",
12	"password": "password",
13	"port": 5439,
14	"s3_bucket_name": "my-bucket",
15	"s3_bucket_region": "us-west-2",
16	"ssh": false,
17	"ssh_host": "bastion.example.com",
18	"ssh_port": 22,
19	"ssh_private_key": "",
20	"ssh_user": "",
21	"use_bulk_sync_staging_schema": false,
22	"username": "redshift_user"
23	}
24	}

1	{
2	...
3	"configuration": {
4	"query": "SELECT * FROM sampledata.users",
5	"schema": "sampledata",
6	"table": "users",
7	"view": "active_users"
8	}
9	}

1	{
2	...
3	"target": {
4	"configuration": {
5	"created_column": "",
6	"preserve_table_on_resync": false,
7	"updated_column": "",
8	"write_record_timestamps": false
9	}
10	}
11	}

1	{
2	...
3	"destination_configuration": {
4	"advanced": {
5	"empty_strings_null": false,
6	"hard_deletes": false,
7	"table_prefix": "",
8	"truncate_existing": false
9	},
10	"mirror_schemas": false,
11	"schema": "schema"
12	}
13	}