Overview
Integrate.io uses the COPY query to sync data from S3 to Redshift using UPSERTS in periodic batches. The data stored temporarily in S3 is removed after it has been synced to Redshift.
Requirements
SUPERUSER privileges to create sync user and create schema if not exists.
Create sync user
Let's start by creating a sync user for Integrate.io CDC. Run the following query,
CREATE USER flydata PASSWORD '<your_password>';
Grant database privileges
The sync user needs CREATE and TEMPORARY privileges to create the schema and temporary tables.
Specify the database that you'd like the data to be synced to and run the following query to grant the privileges to the user on that database,
GRANT CREATE ON DATABASE <database_name> TO flydata;
GRANT TEMPORARY ON DATABASE <database_name> TO flydata;
Grant schema privileges
The sync user needs USAGE and CREATE privileges to create the tables in the schema.
Specify the schema that you'd like the data to be synced to and run the following query to grant the privileges to the user on that schema,
CREATE SCHEMA IF NOT EXISTS <schema_name>;
GRANT USAGE ON SCHEMA <schema_name> TO flydata;
GRANT CREATE ON SCHEMA <schema_name> TO flydata;
Note: If you want to store your logs in your own S3 bucket when syncing from your source to redshift, refer to this guide.