Redshift Data Destination

Follow
Avatar
Gadi Eliashiv
Updated

Set up a data destination to export data from Singular automatically to a Redshift database.

Note: Data destinations are an enterprise feature (learn more).

 

Setup Instructions

1. Create a Redshift User

Create a user with the following command:

CREATE USER singular WITH PASSWORD '<password>';

Grant it the following permissions:

GRANT CREATE ON DATABASE "<enter database name here>" TO "singular";
GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO singular;
GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO singular;

Note:

  • The first permission allows Singular to create new schemas in the provided database. It does not allow Singular to read or change other schemas or tables in the database.
  • The second and third permissions allow Singular to verify the structure of the tables that Singular will update.

2. Whitelist Singular Server IPs

If you are using a Security Group and only allow certain IPs to connect to your Redshift instance, you need to add Singular’s servers to the whitelisted IPs in your security group:

  • 54.183.135.179/32
  • 54.183.113.72/32
  • 13.52.189.144/32

To whitelist Singular server IPs:

  1. Go to your Redshift clusters list and select the cluster you want us to use.

  2. In the cluster settings, select the VPC security group you want to edit:

    step2.png

  3. Go to the Inbound tab and click Edit.

  4. Create three rules with the IPs provided above:

    step4.png

3. Make Your Redshift Server Accessible

Since Singular servers are located outside of your internal VPC, you have to make your Redshift server publicly accessible:

  1. In your Redshift clusters list, select your relevant cluster.

  2. Select the Properties tab:

    mceclip0.png

  3. Under "Network and Security", make sure the "Publicly Accessible" setting is set to "Yes":

4. Add a Redshift Data Destination

To add a Redshift data destination in Singular:

  1. In your Singular account, go to Settings > Data Destinations and click Add a new destination.
  2. Type in either "Redshift Destination" (to export aggregated marketing data) or "Redshift User-Level Destination" (to export user-level data).

  3. In the window that opens, fill in the relevant details:

    mceclip1.png

    Field Description
    Username The username and password you created in Step 1. 
    Password
    Hostname

    The hostname of your Redshift instance.

    Note: The hostname should not include the port number. E.g., instead of  "myredshift.xxx.amazonaws.com:1234", enter "myredshift.xxx.amazonaws.com". The port number goes in the next field.
    Port The port number of your Redshift instance.
    Database Name The database that you granted Singular permissions to.
    Schema Name Enter a name for the schema that Singular will create and write to. Default: "singular".
    Table Name Enter a name for the table that Singular will create and write to. Default: "marketing_data".
    Data Schema The schema of the data loaded into the destination. For your schema options, see Data Destinations: Aggregated Marketing Data Schemas and Data Destinations: User-Level Data Schemas.