Connect SendGrid Webhook to Redshift: 2 Easy Methods

Effortlessly connect and sync data from SendGrid’s Webhook to Redshift, enabling real-time insights without the hassle of manual processes. Watch our 1-minute demo below to see how Hevo simplifies this setup!

SendGrid is a cloud-based email delivery service that helps companies, Shipping Notifications, Friend Requests, Sign-up Confirmations, and Email Newsletters are just a few of the email types managed by it.

AWS Redshift is an Amazon Web Services Data Warehouse service. It’s commonly used for large-scale data storage and analysis, as well as large database migrations.

This article talks about the ways to load data instantly from SendGrid Webhook to Redshift. It also gives a brief introduction to Redshift and SendGrid Webhook.

What is SendGrid Webhook?

The SendGrid Event Webhook sends information about events related to your emails to a specified URL via HTTP POST. This data helps manage unsubscribes, respond to spam reports, track unengaged recipients, handle bounced emails, and conduct advanced email analytics. You can use Unique Arguments and Category Parameters to enhance insights into your email program. These fields are considered “Not PII,” meaning they are not treated as Personally Identifiable Information (PII) and may be stored indefinitely by SendGrid. To track more event data, you can save up to 30 days of events in the Email Activity Feed, after which it will be lost.

What is Amazon Redshift?

AWS Redshift is a fully managed, petabyte-scale cloud data warehouse that enables you to quickly set up and analyze large data sets, handling both structured and unstructured data. It offers automatic compression, improving query performance by saving storage and reducing I/O. With its ease of setup, encryption, and support for OLAP workflows, Redshift helps extract valuable insights from massive datasets. To learn more, check out the official AWS Redshift documentation.

Key Features of Amazon Redshift

• The Advanced Query Accelerator (AQUA) in Amazon Redshift speeds up queries 10 times faster than other cloud data warehouses.
• For ETL, batch job processing, and dashboarding, Amazon Redshift’s Materialistic view allows you to achieve faster query performance.
• Amazon Redshift’s architecture scales up to petabytes and scales down quickly as needed.
• Amazon Redshift allows for data securely sharing between Amazon Redshift clusters.
• Amazon Redshift consistently delivers fast results, even when thousands of queries are running at the same time.
• With the help of ANSI SQL, Amazon Redshift can directly query files such as CSV, Avro, Parquet, JSON, and ORC.
• Amazon Redshift has excellent Machine Learning support, and developers can use SQL to create, train, and deploy Amazon Sagemaker models.
• Amazon Redshift allows users to write queries and export the data back to Data Lake.

Why Connect SendGrid Webhook to Redshift?

To deliver real-time updates about the Emails that you send, like Bounced Emails, Clicked Links, Replies, Unsubscribes or Spam Reports, Queries, Orders or Affirmative Actions, etc., Sendgrid provides Webhooks that will notify a URL of your choice and intimate to you about the event that took place. So, if someone places an order by clicking a link in your email, Sendgrid’s Webhook will send a message to your prescribed URL. 

You can select which events you would like to be informed about, and Webhook will send you a properly formatted JSON array of your selected events on one request. If your receiving URL does not give a 2xx response, the Sendgrid Webhook will try a few more times to send the POST request.

Connecting Sendgrid Webhook to Redshift or any data warehousing solution it helps better the Email Delivery service and helps track the Email Activity Feed.

Methods to Connect SendGrid Webhook to Redshift 

Method 1: Using Hevo Data to Set Up  SendGrid Webhook to Redshift

Hevo provides an Automated No-code Data Pipeline that helps you move your SendGrid Webhook to Redshift. Hevo is fully-managed and completely automates the process of not only loading data from your 100+ data sources(including 40+ free sources)but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Using Hevo Data, you can connect SendGrid Webhook to Redshift in the following 2 steps:

• Step 1: Select Sendgrid Webhook as your source.

• Step 2: To set up Amazon Redshift as a destination in Hevo, follow these steps:
  • Step 2.1: In the Asset Palette, select DESTINATIONS.
  • Step 2.2: In the Destinations List View, click + CREATE.
  • Step 2.3: Select Amazon Redshift from the Add Destination page.
  • Step 2.4: Set the following parameters on the Configure your Amazon Redshift Destination page:
    • Destination Name: A unique name for your Destination.
    • Database Cluster Identifier: Amazon Redshift host’s IP address or DNS.
    • Database Port: The port on which your Amazon Redshift server listens for connections. Default value: 5439
    • Database User: A user with a non-administrative role in the Redshift database.
    • Database Password: The password of the user.
    • Database Name: The name of the Destination database where data will be loaded.
    • Database Schema: The name of the Destination database schema. Default value: public.
  • Step 2.5: Click Test Connection to test connectivity with the Amazon Redshift warehouse.
  • Step 2.6: Once the test is successful, click SAVE DESTINATION.

Here are more reasons to try Hevo:

• Smooth Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to your schema in the desired Data Warehouse.
• Exceptional Data Transformations: Best-in-class & Native Support for Complex Data Transformation at fingertips. Code & No-code Flexibility is designed for everyone.
• Quick Setup: Hevo with its automated features, can be set up in minimal time. Moreover, with its simple and interactive UI, it is extremely easy for new customers to work on and perform operations.
• Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
• Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

Method 2: Using Custom Code to Move Data from SendGrid Webhook to Redshift

In this method, to Connect SendGrid Webhook to Redshift, first, you will send data from SendGrid Webhook to CSV and then Migrate data from CSV to Redshift.

SendGrid Webhook to CSV 

Request a CSV

• You can use the Email Activity API to query all of your stored messages, and individual messages, and download a CSV file containing data about the stored messages.
• You can examine the data associated with your messages once they’ve been retrieved to get a better understanding of your mail sent. You could, for example, retrieve all bounced messages or all messages with the same subject line and look for patterns.

POST /v3/messages/download
Base url: https://api.sendgrid.com

• A backend process will generate a CSV file in response to this request. After the file has been generated, the worker will send an email to the user requesting that they download it. In three days, the link will expire.
• The last 1 million messages are contained in the CSV file. There will be a rate limit of one request every 12 hours for this endpoint (rate limit may change).
• The only difference between this endpoint and the GET Single Message endpoint is that download is added to indicate that this is a CSV download request, but the same query is used to determine what the CSV should contain.

<?php // Uncomment next line if you're not using a dependency loader (such as Composer) // require_once '<PATH TO>/sendgrid-php.php'; $apiKey = getenv('SENDGRID_API_KEY'); $sg = new SendGrid($apiKey); try { $response = $sg->client->messages()->download()->post(); print $response->statusCode() . "n"; print_r($response->headers()); print $response->body() . "n"; } catch (Exception $ex) { echo 'Caught exception: '. $ex->getMessage(); }

Download a CSV

GET /v3/messages/download/{download_uuid}
Base url: https://api.sendgrid.com

• The “Request a CSV” endpoint will return a Presigned URL that can be used to download the CSV that was requested.

<?php // Uncomment next line if you're not using a dependency loader (such as Composer) // require_once '<PATH TO>/sendgrid-php.php'; $apiKey = getenv('SENDGRID_API_KEY'); $sg = new SendGrid($apiKey); $download_uuid = "6f240bf5-d42d-4e4c-b159-82c1a82c1e87"; try { $response = $sg->client->messages()->download()->_($download_uuid)->get(); print $response->statusCode() . "n"; print_r($response->headers()); print $response->body() . "n"; } catch (Exception $ex) { echo 'Caught exception: '. $ex->getMessage(); }

CSV to Redshift

Using Amazon S3 Bucket

• One of the simplest ways of loading CSV files into Amazon Redshift is using an S3 Bucket. It involves two stages – loading the CSV files into S3 and consequently loading the data from S3 to Amazon Redshift.
  • Step 1: Create a manifest file that contains the CSV data to be loaded. Upload this to S3 and preferably gzip the files.
  • Step 2:  Once loaded onto S3, run the COPY command to pull the file from S3 and load it to the desired table. If you have used gzip, your code will be of the following structure:

COPY <schema-name>.<table-name> (<ordered-list-of-columns>) FROM '<manifest-file-s3-url>' 

CREDENTIALS'aws_access_key_id=<key>;aws_secret_access_key=<secret-key>' GZIP MANIFEST;

• Here, using the CSV keyword is of significance to help Amazon Redshift identify the file format. You also need to specify any column arrangements or row headers to be dismissed, as shown below:

COPY table_name (col1, col2, col3, col4)
FROM 's3://<your-bucket-name>/load/file_name.csv'
credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>'
CSV;

-- Ignore the first line
COPY table_name (col1, col2, col3, col4)
FROM 's3://<your-bucket-name>/load/file_name.csv'
credentials 'aws_access_key_id=<Your-Access-Key-ID>;aws_secret_access_key=<Your-Secret-Access-Key>'
CSV
INGOREHEADER 1;

• This process will successfully load your desired CSV datasets to Amazon Redshift in a pretty straightforward way.

Using an AWS Data Pipeline

• You can also use the AWS Data Pipeline to extract and load your CSV files. The benefit of using the AWS Data Pipeline for loading is the elimination of the need to implement a complicated ETL framework. Here, you can implement template activities to efficiently carry out data manipulation tasks.
• Use the RedshiftCopyActivity to copy your CSV data from your host source into Redshift. This template copies data from Amazon RDS, Amazon EMR, and Amazon S3. 

• The limitation can be seen in a lack of compatibility with some data warehouses that could be potential host sources. This method is essentially manual as the copy activity implements after every iteration of Data Loading. For a more reliable approach, especially when dealing with dynamic Data Sets, you might want to rely on something that is self-managed.

Learn More About:

Working with Sendgrid Webhook

Conclusion

In a few steps, this blog explains the different ways to load data from SendGrid Webhook to Redshift. It also gives an overview of SendGrid Webhook and Redshift.

FAQ on Sendgrid Webhook to Redshift