Connecting Amazon Redshift copies your Simplesat responses into a table in your Redshift database and keeps that table current as new responses arrive and existing ones change. It covers every survey in your workspace.

Before you start

The integration is available on every Simplesat plan. To connect it and switch it on, you need the Manage integrations permission. Owner and Admin have it by default.

In AWS, you need:

A Redshift Serverless workgroup or a provisioned cluster, with a database for the table.
An IAM user with an access key, for the integration to connect as.

Simplesat runs its statements through the Amazon Redshift Data API using that access key, so your workgroup or cluster doesn't need to accept outside connections.

The IAM user needs these permissions:

redshift-data:ExecuteStatement, redshift-data:DescribeStatement and redshift-data:GetStatementResult
On Redshift Serverless, redshift-serverless:GetCredentials. Statements run as a database user named after the IAM user, for example IAM:simplesat.
On a provisioned cluster, redshift:GetClusterCredentials. Statements run as the database user you enter in the settings.

The database user the statements run as needs to create a table in the schema the integration uses (see The table), and to select, insert and update in it.

Integration security, permissions and data flow covers what a connected tool can read and write.

Connect Amazon Redshift

In AWS IAM, create a user for Simplesat, or pick an existing one, and give it the permissions above.
Under the user's Security credentials, create an access key. Copy both the access key ID and the secret access key now, because AWS shows the secret only once.
In Simplesat, go to the Integrations page, open Amazon Redshift and click Authenticate.
Fill in AWS Access Key with the access key ID, AWS Region with the region your workgroup or cluster is in, for example us-west-1, and AWS Secret Key with the secret access key. Click Authenticate.
When the page shows Authenticated with Amazon Redshift, click Activate integration on Sync feedback data to Amazon Redshift.
Fill in its settings and click Save.

Setting	What to enter
Database	The Redshift database for your feedback. Defaults to `dev`
Table name	The table's name. Defaults to `simplesat feedback`. If no table has that name, the integration creates it

The other settings depend on how you run Redshift. On Redshift Serverless, enter the workgroup's name. On a provisioned cluster, enter the cluster's identifier and the database user the statements run as.

What lands in Redshift and when

The table

The integration creates the table in your Database, in the first schema on the database user's search path. That's public unless you've changed it. There's no schema setting, but you can change the search path (see the FAQ below).

Responses reach the integration at least five minutes after they arrive in Simplesat. The first one to reach it after you activate the integration creates the table. Then every existing response in your workspace is copied in, from every survey. If you have a lot of history, that takes a while.

If you change Table name to a name that doesn't exist yet, the next response creates that table and copies every response into it.

When rows are added and updated

Each response has one row, matched on id. A new response adds a row. When a response changes, for example because the customer answers another question later or a rating is edited, its row is updated. Only active responses are sent. Withheld, hidden and spam responses aren't.

Tagging a response in Simplesat doesn't update its row. The tag reaches Redshift the next time the response itself changes.

What each row holds

Column	Type	What it holds
`id`	BIGINT	The response ID. The table's primary key
`survey`	SUPER	The survey's ID and name
`created`, `modified`	TIMESTAMP	When the response was created and last changed, in UTC
`ip_address`	VARCHAR(45)	The respondent's IP address
`ticket`	SUPER	The ticket's ID in Simplesat and in your helpdesk, its subject and its ticket fields
`customer`	SUPER	The customer's IDs, name, email, company, tags and attributes
`answers`	SUPER	Each answer: the question, the rating or choice, the comment, the sentiment, and the follow-up question and answer
`tags`	SUPER	The response's tags
`source`	VARCHAR(255)	Where the response came from

The table has no column for team members.

Rows copied in when the table is created hold every answer in answers until their response changes. Rows written after that, for new and changed responses, hold only one answer.

Replace the access key or change the cluster

An access key works until it's deactivated or deleted in IAM. When it stops working, every sync fails, and the page still shows Authenticated with Amazon Redshift. To replace it:

In IAM, create a new access key for the user.
On the Amazon Redshift page in Simplesat, click Revoke access.
Click Authenticate and enter the new access key ID, secret access key and region.
Click Activate integration on Sync feedback data to Amazon Redshift. Your settings are still there, but the integration stays off until you do this, and responses that arrive while it's off aren't added later.
Deactivate the old access key in IAM.

To move to a different workgroup, cluster or database, change the settings and click Save. To connect a different AWS account, follow the steps in this section with an access key from that account. Its IAM user needs the permissions in Before you start.

Troubleshooting

Nothing has reached my Redshift table since a certain date

Start with the access key. If it was deactivated or deleted in IAM, every sync fails while the page still shows Authenticated with Amazon Redshift. Create a new key. Then, on the Amazon Redshift page, click Revoke access, click Authenticate and enter the new key, and click Activate integration.

If the key is fine, check that:

The IAM user still has the permissions in Before you start.
The workgroup or cluster still exists.
The database user can still write to the table.

If none of that explains it, contact us. Responses that arrived while syncs were failing aren't added later.

The table hasn't appeared in my database

Responses reach the integration at least five minutes after they arrive, and the first one after you activate it creates the table. If that's had time to happen, check that:

Database and Table name are filled in and saved, along with the workgroup's name on Redshift Serverless, or the cluster's identifier and the database user on a provisioned cluster.
The IAM user has the permissions in Before you start, including redshift-serverless:GetCredentials on Serverless or redshift:GetClusterCredentials on a provisioned cluster.
The database user can create tables in the schema.
You're looking in the first schema on the database user's search path, usually public.

My Redshift rows are missing answers

Rows copied in when the table was created hold every answer until their response changes. Rows written after that, for new and changed responses, hold only one answer.

Tags I add in Simplesat don't show up in Redshift

Tagging a response doesn't update its row. The tag goes over the next time the response itself changes.

FAQ

Can I choose the schema the table goes in?

There's no schema setting. The table goes in the first schema on the database user's search path. To use another schema, put it first in that user's search path. For a database user named simplesat on a provisioned cluster:

ALTER USER simplesat SET search_path TO feedback, public

On Redshift Serverless, the database user's name has a colon in it, so put it in double quotes:

ALTER USER "IAM:simplesat" SET search_path TO feedback, public

The change applies from the user's next session.

Does the integration create the database or the table?

It creates the table if it doesn't exist. The database, and the workgroup or cluster, have to exist already.

How do I read the SUPER columns?

Use dot and bracket notation with a table alias. This returns each customer's email and the first answer's rating or choice:

SELECT f.customer.email, f.answers[0].choice FROM "simplesat feedback" f

The default table name has a space in it, so put it in double quotes.

Which responses go into the table when it's created?

Every existing response in your workspace, from every survey. After that, new and changed responses are added or updated as they arrive.

Amazon Redshift integrations