Redshift (2023-04-25) — Polytomic Docs

Connection

Configuration

NAME	TYPE	DESCRIPTION	REQUIRED	READONLY
hostname	string		true	false
username	string		true	false
password	string		true	false
database	string		true	false
port	integer		true	false
ssh	boolean		false	false
ssh_user	string		false	false
ssh_host	string		false	false
ssh_port	integer		false	false
ssh_private_key	string		false	false
aws_access_key_id	string	Access Key ID with read/write access to a bucket. More info: https://docs.polytomic.com/docs/redshift	false	false
aws_secret_access_key	string		false	false
aws_user	string		false	true
s3_bucket_name	string	Name of bucket used for staging data load files	false	false
s3_bucket_region	string	Region of bucket. Note: must match region of redshift server	false	false

Example

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

Model Sync

Source

Configuration

NAME	TYPE	DESCRIPTION	REQUIRED	READONLY
query	string		false	false
schema	string	Schema	false	false
table	string	Table	false	false
view	string	View	false	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

Configuration

NAME	TYPE	DESCRIPTION	REQUIRED	READONLY
preserve_table_on_resync	boolean	Preserve destination table when resyncing	false	false
write_record_timestamps	boolean	Write row timestamp metadata	false	false
created_column	string	’Created at’ timestamp column	false	false
updated_column	string	’Updated at’ timestamp column	false	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	READONLY
advanced	object		false	false
schema	string	Output schema	false	false
mirror_schemas	boolean	Mirror schemas	false	false

Example

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

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`
`TEXT`	`string`
`TIME`	`time`
`TIME WITH TIME ZONE`	`time`
`TIME WITHOUT TIME ZONE`	`time`
`TIMESTAMP`	`datetime`
`TIMESTAMP WITH TIME ZONE`	`datetime`
`TIMESTAMP WITHOUT TIME ZONE`	`datetime`
`TIMESTAMPTZ`	`datetime`
`TIMETZ`	`time`
`VARCHAR`	`string`

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

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	"hard_deletes": false,
6	"table_prefix": "",
7	"truncate_existing": false
8	},
9	"mirror_schemas": false,
10	"schema": "schema"
11	}
12	}