Send query results to Amazon Redshift

You can send data from Amperity to Amazon Redshift. First build a query that outputs a table with rows that match the schema of a table in Amazon Redshift. Then use an orchestration to send that data from Amperity to Amazon Redshift.

Note

A Lambda function running in AWS Lambda validates that rows in your table output match the table schema in Amazon Redshift. Only validated rows are copied to your table in Amazon Redshift.

Send data tables from Amperity to Amazon Redshift.

This topic describes the steps that are required to send table rows that match a table schema to Amazon Redshift from Amperity:

  1. Build a query

  2. Add orchestration

  3. Run orchestration

Note

Amazon Redshift must be enabled as a webhook destination before you can configure an orchestration to send query results.

Build query

Build a query that returns rows that match the schema of a table in Amazon Redshift. For example, to send customer addresses for use with a downstream direct mail campaign use a query similar to:

SELECT
  amperity_id AS amperity_id
  ,given_name AS given_name
  ,surname AS surname
  ,generational_suffix AS suffix
  ,address AS address
  ,address2 AS address_line_2
  ,city AS city
  ,CASE
    WHEN LENGTH(state) = 2
    THEN state
    ELSE NULL
  END AS state
  ,postal AS postal
FROM Merged_Customers

Note

A Lambda function running in AWS Lambda will validates that rows match the table schema before sending data to Amazon Redshift.

Add orchestration

An orchestration defines the relationship between query results and a destination, including the location to which those query results will be sent and the frequency at which the orchestration will be run.

To add an orchestration

  1. From the Destinations tab, click Add Orchestration. This opens the Add Orchestration dialog box.

  2. From the Object Type drop-down, select Query.

  3. From the Object drop-down, select the query for which results will be sent to Amazon Redshift.

  4. From the Destination drop-down, select a destination that is configured for sending data to Amazon Redshift.

  5. From the Data Template drop-down, select a data template.

  6. Verify all settings.

  7. Set the workflow to Manual. (You can change this to automatic later, after verifying the end-to-end workflow.)

  8. Click Save.

Run orchestration

Run the orchestration manually to validate that it works.

To run the orchestration

  1. From the Destinations tab, under Orchestrations, open the    menu for the Amazon Redshift orchestration, and then select Run.

  2. The Status column for the orchestration will update to say “Waiting to start…”, after which the notifications pane will update to include a notification that shows the current status.

  3. When the orchestration has run successfully, the status is updated to “Completed”.