Redshift Export

Upload your Amplitude event data into your Redshift account. You can set up recurring syncs through the Amplitude UI, as well as manually start a sync of your historical data.

Limits¶

• Maximum running time for a single Redshift SQL query is 12 hours.

Setup¶

Prerequisites¶

You need admin privileges in Amplitude, and a role that allows you to enable resources in Redshift.

By default, Redshift clusters don't allow any incoming traffic. You must first allowlist Amplitude's IP addresses in the security group for your Redshift cluster. For more help, see the Redshift documentation.

From the page for the security group associated with your Redshift cluster, add an inbound rule. For more detailed instructions, see the Redshift Documentation.

To find the security group assigned to your Redshift cluster:

1. Navigate to your Redshift cluster.
2. Go to the Properties tab, and use the link for VPC security group under Network and security settings.

You need to add the following information:

• Protocol: TCP
• Port Range: The number of the open port used by the data source.
• Source: Custom IP (default). Add the correct IP addresses based on whether you're using EU or US Amplitude datacenters.
  • Amplitude US IP addresses:
    • 52.33.3.219
    • 35.162.216.242
    • 52.27.10.221
  • Amplitude EU IP addresses:
    • 3.124.22.25
    • 18.157.59.125
    • 18.192.47.195
• Description: A description for the rule.

Set up a recurring data export to Redshift¶

Creating a recurring data export is a simple, three-step process you can handle yourself. Each sync completes within five to ten minutes, can you can monitor the status of each job.

To set up a recurring export of your Amplitude data to Redshift, follow these steps:

1. In Amplitude Data, click Catalog and select the Destinations tab.
2. In the Warehouse Destinations section, click Redshift.
3. Under Export Data to Redshift, select the data you'd like to export. You can choose Export events ingested today and moving forward, Export all merged Amplitude ID, or both.
4. Review the Event table and Merge IDs table schemas and click Next.

5. In the Redshift Credentials For Amplitude section, enter the following information:

   • User: The Redshift user account that Amplitude uses to connect to your Redshift instance.
   • Password: The password associated with the user.
   • Database: The database where the data should be exported to and stored. Amplitude recommends dedicating a database for Amplitude data.
   • Cluster Host Address: This is the address of the endpoint of the cluster that hosts your database.
   • Port: The port of the Cluster Host Address.  This may only contain digits. The default value is 5439.

   Warning

   These credentials are case-sensitive, so keep that in mind.

6. Next to the credentials section, Amplitude dynamically creates the query it uses to create Redshift objects. To copy it to the clipboard, click Copy. You can now paste it into the Redshift CLI.

7. Click Next. Amplitude attempts to upload test data using the credentials you entered. If the upload is successful, click Finish.

All future events are automatically sent to Redshift.

From here, Amplitude generates micro-batch files at five-minute intervals and loads them to customer-owned Redshift accounts directly every 10 minutes. You can see the data in your Redshift accounts within 20 minutes after Amplitude receives the events.

Export historical Amplitude data to Redshift¶

To export your historical data from Amplitude into Redshift, navigate to the Backfills tab, select a date range, and click Start Backfill.

This process can take anywhere from a single day to several weeks, depending on your data volume, warehouse size, cluster count, network bandwidth, and number of concurrent historical data exports you currently have, among other factors.

Redshift export format¶

The Event table schema includes the following columns:

Merged User table schema¶

The Merged User table schema contains the following: