Send data to Amazon Redshift

Amazon RedShift is a data warehouse located within Amazon Web Services that can handle massive sets of column-oriented data.

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

  1. Get details

  2. Configure Amazon Redshift

  3. Add webhook destination

  4. Add data template

How this destination works

Use Amazon Redshift to send data from Amperity to a Lambda function that automates uploading that file to an Amazon S3 bucket, after which that data is copied directly to a table in Amazon Redshift.

Send data tables from Amperity to Amazon Redshift.

An Amazon Redshift destination works like this:

  1. Use a query to build a table with rows that match the schema of a table in Amazon Redshift.

  2. Configure a webhook destination to send data to a Lambda function that is running in AWS Lambda.

  3. Deploy a Lambda function to AWS Lambda. The Lambda function may be deployed as a serverless application (recommended) or it may be a custom Lambda function that you have built using the open source toolkit provided by Amperity, and then uploaded to AWS Lambda.

  4. Configure the Lambda function to run against the data that is sent from Amperity. The Lambda function will ensure that the data sent from Amperity matches the schema of the table in Amazon Redshift.

  5. The Lambda function uploads validated data to an Amazon S3 bucket.

  6. Data is copied to a table in Amazon Redshift from that Amazon S3 bucket.

Get details

Amazon Redshift requires the following configuration details:

Detail one.

A Lambda function must be uploaded to AWS Lambda that is running in your Amazon AWS account, and then configured to be available to Amperity.

Detail one.

An Amazon S3 bucket must be created. The Lambda function will send data to the Amazon S3 bucket, after which that data will be loaded to Amazon Redshift.

Detail one.

Amazon Redshift must be enabled in your Amazon AWS account.

Detail one.

A webhook destination that is configured in your Amperity tenant that sends query results to a Lambda function running in AWS Lambda.

Configure Amazon Redshift

To configure Amazon Redshift, you must complete one of the following series of steps:

  1. Use the pre-built serverless application

  2. Manual configuration

Serverless application

The AWS Serverless Application Repository is a managed repository for pre-built serverless applications. Find an application, configure it, and then deploy it to your Amazon AWS account.

Amperity manages a pre-built serverless application for Amazon Redshift. Use this application to send valid phone numbers and messages from Amperity to Amazon Redshift.

To deploy a serverless application

  1. Log in to your Amazon AWS account.

  2. Open the AWS Serverless Application Repository .

  3. Search for the amperity-amazon-redshift-runner , and then open it. On the “amperity-amazon-redshift-runner” page, click Deploy to deploy the application to your Amazon AWS account.

  4. Open the AWS Lambda console, and then open the “amperity-amazon-redshift-runner” Lambda function, and then click Configure.

  5. On the Configuration page, click Environment variables, and then click Edit.

  6. Add the following values for each key-pair:

    Key

    Description

    REDSHIFT_CLUSTER_ID

    The Amazon Redshift cluster ID. For example: “redshift-cluster-1”.

    REDSHIFT_DB_NAME

    The name of the database in Amazon Redshift. For example: “acme”.

    REDSHIFT_DB_USER

    The name of the database user for Amazon Redshift. For example: “awsuser”.

    REDSHIFT_IAM_ROLE

    The name of the Identity and Access Management (IAM) role with permission to access Amazon Redshift. For example: “arn:aws:iam::123456:role/service-role/AmazonRedshift-CommandsAccessRole-20220101123123”.

    REDSHIFT_TABLE_NAME

    The name of the table in Amazon Redshift. For example: “Acme_Customers”.

    S3_BUCKET

    The name of the Amazon S3 bucket to which the Lambda function will write data. For example: “acme-s3-bucket”.

  7. Click Save.

Manual configuration

A manual configuration requires more steps than deploying a serverless application, but enables the use of a custom Lambda function and the ability to define inline policies.

To manually configure Amazon Redshift, do each of the following steps:

  1. Add basic function to your AWS Lambda console

  2. Get Lambda function template

  3. Upload function to AWS Lambda

  4. Configure function in AWS Lambda

  5. Add the API gateway

  6. Set AWS Lambda environment variables

  7. Attach policies

Add function to AWS Lambda

Add a basic function to your AWS Lambda console using only the default function without any customization.

To add a function to AWS Lambda

  1. Log in to your Amazon AWS account.

  2. Open the AWS Lambda console, and then click Create function.

  3. Select Author from scratch.

  4. Under Basic information, specify the function name, runtime, and architecture. For example:

    Function name “Amperity function for Amazon Redshift”

    Runtime “Python 3.9”

    Architecture “x86_64”

  5. Click Save.

Get Lambda function template

Amperity provides a pre-built collection of Lambda functions that is available from an open-source repository on GitHub.

Note

Lambda functions are built using Python. Amazon AWS provides runtimes for Python that include an SDK, along with credentials from an AWS Identity and Access Management (IAM) role that you manage.

To get the Lambda function template

  1. Clone the open source amperity-lambda-runner repository or download it as a ZIP file.

  2. In the repo, navigate to “/src/lambdas/lambda_handlers/”, and then open “amazon_redshift.py”.

  3. You may use this file directly (requiring only configuration updates) or you may customize it to support your desired workflow.

Upload function to AWS Lambda

You must upload the application code (and any dependencies) to AWS Lambda as a ZIP file archive.

To upload a Lambda function to AWS Lambda

  1. Add the application code and dependencies to a ZIP file archive.

    Tip

    To build the ZIP file for Amazon Redshift, run the following command from your amperity-lambda-runner directory:

    sh util/lambda-build.sh filename=amazon_redshift.py
    

    This will create a ZIP file that contains the following files: amazon_redshift.py, amperity_runner.py, and helpers.py.

  2. Open the AWS Lambda console, and then open the Code tab.

  3. Click Upload from, and then click ZIP file.

  4. Find the ZIP file archive that contains the application code (and any dependencies), and then click Upload.

  5. Click Save.

Configure function in AWS Lambda

AWS Lambda must be updated for the name of the Amazon Redshift application and to specify a timeout value.

To configure the Lambda function in AWS Lambda

  1. Open the AWS Lambda console, and then open the Code tab.

  2. Under Runtime settings, select Edit.

  3. Under Handler, add “app.amazon_redshift”, and then click Save.

  4. In the AWS Lambda console, open the Configuration tab.

  5. Under General configuration, select Edit.

  6. Set the Timeout value to “15 min 0 sec”, and then click Save.

Add the API gateway

Amazon API Gateway provides tools for creating and documenting web APIs that route HTTP requests to AWS Lambda functions. An API gateway is required to use webhook destinations.

To add an API gateway

  1. Open the AWS Lambda console.

  2. Open the Lambda function for Amazon Redshift.

  3. Click Add trigger.

  4. From the drop-down menu, select “API Gateway”, and then select the following:

    Setting

    Value

    Intent

    Create a new API

    API type

    REST API

    Security

    API Key

  5. Click Add.

    You will need these values when configuring the webhook destination in Amperity.

Set AWS Lambda environment variables

Use AWS Lambda environment variables provide to the Lambda function details about Amazon Redshift, how the tables can be accessed, and from which Amazon S3 bucket table updates will be loaded.

To set environment variables

  1. Open the AWS Lambda console.

  2. Open the Lambda function for Amazon Redshift.

  3. Click Configuration.

  4. On the Configuration page, click Environment variables, and then click Edit.

  5. Click Add environment variable, and then set the following key-value pairs:

    Key

    Description

    Key

    Description

    REDSHIFT_CLUSTER_ID

    The Amazon Redshift cluster ID. For example: “redshift-cluster-1”.

    REDSHIFT_DB_NAME

    The name of the database in Amazon Redshift. For example: “acme”.

    REDSHIFT_DB_USER

    The name of the database user for Amazon Redshift. For example: “awsuser”.

    REDSHIFT_IAM_ROLE

    The name of the Identity and Access Management (IAM) role with permission to access Amazon Redshift. For example: “arn:aws:iam::123456:role/service-role/AmazonRedshift-CommandsAccessRole-20220101123123”.

    REDSHIFT_TABLE_NAME

    The name of the table in Amazon Redshift. For example: “Acme_Customers”.

    S3_BUCKET

    The name of the Amazon S3 bucket to which the Lambda function will write data. For example: “acme-s3-bucket”.

  6. Click Save.

Attach policies

You can use inline policies to manage access to the Lambda function in AWS Lambda.

To attach policies

  1. Open the AWS Lambda console.

  2. Open the Lambda function for Amazon Redshift.

  3. Click Configuration.

  4. On the Configuration page, under Execution role, and then click the name of the role to be used with this Lambda function.

  5. Click Add permissions, and then click Attach policies. Set the following values:

    AmazonRedshiftFullAccess

    AWSLambdaExecute

    AWSLambdaRole

    AmazonS3ReadOnlyAccess

  6. Click Attach policies.

Add webhook destination

Use a webhook destination to send query results from Amperity to a Lambda function running in AWS Lambda in your instance of Amazon AWS.

To add a destination

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

  2. Enter the name of the destination and a description. For example, “Amazon Redshift” and “Send table rows that match a table schema in Amazon Redshift”.

  3. From the Plugin drop-down, select “Webhook”.

  4. The “webhook” credential type is selected automatically.

  5. From the Credential drop-down, select a credential that has already been configured for this destination or click Create a new credential, which opens the Create New Credential dialog box. For new credentials, enter a name for the credential, the API key, and the webhook URL. (The webhook URL is the endpoint for the API gateway.) Click Save.

    Tip

    The API key and webhook URL settings are available from your AWS Lambda console. Log in to your AWS Lambda console, and then click Configuration. The API key is in the Details section and the webhook URL is in the Triggers section.

    For example:

    API key AB1c2D3eFGH4j5KL6

    Webhook URL https://acme.execute-api.us-east-123.amazonaws.com/Prod/run/

  6. Select Allow business users to use this destination.

    This allows users who have been assigned to the Amp360 User and/or AmpIQ User policies to use this destination with orchestrations and/or campaigns.

  7. Select Allow business users with limited PII access to send data.

    This allows users who have been assigned to the Restrict PII access policy option to use this destination with orchestrations and/or campaigns.

  8. Click Save.

Add data template

A data template defines how columns in Amperity data structures are sent to downstream workflows. A data template is part of the configuration for sending query and segment results from Amperity to an external location.

To add a data template

Step 1.

From the Destinations tab, open the menu for a destination that is configured for Amazon Redshift, and then select Add data template.

This opens the Add Data Template dialog box.

Step 1

Enter the name of the data template and a description. For example: “Amazon Redshift” and “Send table rows that match a table schema to Amazon Redshift.”.

Step 2.

Verify business user access to queries and orchestrations and access to segments and campaigns.

A business user may also have restricted access to PII, which prevents them from viewing and sending customer profile data.

Step 2.

If business user access was not configured as part of the destination, you may configure access from the data template.

Step 3.

Verify all configuration settings.

Important

Under Webhook settings, if Webhook Settings is empty, set it to empty curly braces: {}.

Verify settings for the data template.

Note

When the settings required by Amazon Redshift were are not configured as part of the destination, you must configure them as part of the data template before making this destination available to campaigns.

Step 4.

Review all settings, and then click Save.

Save the data template.

After you have saved the data template, and depending on how you configured it, business users can send query results to Amazon Redshift.