Connect SendGrid Webhook to Redshift: 2 Easy Methods

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 will send information about events that occur as SendGrid processes your email to a URL of your choice via HTTP POST. You can use this information to remove unsubscribes, respond to spam reports, identify unengaged recipients, identify bounced email addresses, and perform advanced email analytics. You can insert dynamic data using Unique Arguments and Category Parameters to help build a clear picture of your email program.

Categories and Unique Arguments will be stored as a “Not PII” field that SendGrid can use for counting and other operations. These fields can’t be redacted or removed in most cases. You should avoid putting any PII in this field. SendGrid does not treat this information as Personally Identifiable Information (PII), and its value may be visible to SendGrid employees, stored indefinitely, and even after you leave the SendGrid platform.

If you want to keep track of more event data that is saved for you, you should set up the Event Webhook. You can only store so much information due to the sheer volume of emails you send. Up to 30 days of events can be saved in your Email Activity Feed. The email event data is lost after that time has passed.

What is Amazon Redshift?

AWS Redshift is Amazon Web Services’ solution for data warehousing. The service, like many others provided by AWS, can be set up in a matter of minutes and offers a variety of import options. Redshift data is also encrypted for an extra layer of protection.

You can extract useful information from a large amount of data using Redshift. AWS provides a simple interface for creating clusters automatically, removing the need for infrastructure management.

All data you load into Redshift is compressed by default, and it is decompressed during query execution. Compression saves storage space by reducing the size of data read from storage, reducing the amount of disc I/O, and thus improving query performance.

For storing and analyzing large data sets, Amazon Redshift is a fully managed petabyte-scale cloud data warehouse. One of Amazon Redshift’s key advantages is its ability to handle large amounts of data – it can process unstructured and structured data up to exabytes. The service can also be used to perform large-scale data migrations. Redshift, like other Data Warehouses, is used for Online Analytical Processing (OLAP) Workflow.

To know more about AWS Redshift, follow the official documentation here.

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.

Key Benefits of Amazon Redshift

• Smart Optimization: If your dataset is large, there are several ways to query the data with the same parameters. Different commands have different levels of data usage. Amazon Redshift provides tools and information to improve your queries. These can be used for faster and more resource-efficient operations. 
• Automate Repetitive Tasks: Amazon Redshift is capable of automating tasks that must be completed repeatedly. Creating daily, weekly, or monthly reports is an example of an administrative task. This could be a review of resources and costs. Cleaning up your data can also be a regular maintenance task. All of this can be automated thanks to Amazon Redshift’s actions.
• Speed: With the use of MPP technology, the speed of outputting large amounts of data is unprecedented. AWS’ service costs are unmatched by other cloud service providers.
• Simultaneous Scaling: Amazon Redshift automatically scales up to support the growth of concurrent workloads.
• Query Volume: MPP technology excels in this regard, and you can send thousands of queries to your dataset at any time. Amazon Redshift, on the other hand, is unstoppable. To cope with increasing demand, dynamically allocate processing and memory resources.
• Familiarity: Amazon Redshift is based on PostgreSQL. All SQL queries work with it. In addition, you can choose the SQL, ETL (extract, transform, load), and Business Intelligence (BI) tools you are familiar with. You are not obligated to use the tools provided by Amazon.
• AWS Integration: Amazon Redshift works well with other AWS tools. You can set up integrations between all services, depending on your needs and optimal configuration.
• Redshift API: The Amazon Redshift API is well-documented and has a lot of features. It is possible to use API tools to send queries and receive results. In Python programs, the API can be used to make coding easier.
• Data Encryption:  Amazon provides data encryption for all parts of your Amazon Redshift operation. The user can decide which processes need to be encrypted and which ones do not. Data encryption provides an additional layer of security.
• Safety: Amazon is in charge of cloud security, but users are responsible for application security in the cloud. To provide an extra layer of security, Amazon provides access control, data encryption, and virtual private clouds.
• Partner Ecosystem: AWS was one of the first cloud service providers to introduce Cloud Data Warehouses to the market. Many customers entrust their infrastructure to Amazon. AWS also has a large network of partners who can help build third-party apps and provide implementation services. This partner ecosystem can also be used to see if you can find the best implementation solution for your company.
• AWS Analytics: AWS has a plethora of analytical tools. Amazon Redshift makes all of the Data Analytics possible. Other analytics tools can be integrated with Amazon Redshift with Amazon’s help. Because Amazon Redshift is an AWS community product, it has native integration capabilities with AWS analytics services.
• Open Format: Amazon Redshift can support and provide output in many open formats of data. The most commonly supported formats are Apache Parquet and Optimized Row Columnar (ORC) file formats.
• Easy Deployment: In minutes, Amazon Redshift clusters can be deployed from any location in the world. You’ll have a powerful data warehousing solution in minutes, for a fraction of what your competitors charge.
• Consistent Backup: Amazon backs up your data regularly. In the event of an error, failure, or damage, it can be used to recover. Backups are stored in various locations. This reduces the likelihood of confusion on your website.
• Machine Learning: Amazon Redshift predicts and analyses queries using machine-learning concepts. This makes Amazon Redshift perform faster than any other solution on the market, in addition to MPP. 

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.

SendGrid Webhook to Redshift 

• Method 1: Using Hevo Data to Set Up SendGrid Webhook to Redshift 
• Method 2: Using Custom Code to Move Data from 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: Hevo can import your SendGrid account’s email activity data into your Destination. Hevo uses Webhooks to communicate with SendGrid.

Add Webhook URL in your SendGrid Account

• Step 1.1: The generated Webhook URL should be copied.
• Step 1.2: Open the SendGrid UI and go to Settings > Mail Settings in your SendGrid account.
• Step 1.3: Activate the Event Notification feature.
• Step 1.4: Paste the unique URL you copied in step 1 into the HTTP POST URL field.
• Step 1.5: Choose which Event Notifications you want to test.
• Step 1.6: To save these changes to your settings, check the box in the top right corner.

Sample Event Data:

{
    "email": "example@test.com",
    "timestamp": 1580102529,
    "smtp-id": "<14c5d75ce93.dfd.64b469@ismtpd-555>",
    "event": "deferred",
    "category": "cat facts",
    "sg_event_id": "P0onudGCXGlIhfAoy831Nw==",
    "sg_message_id": "14c5d75ce93.dfd.64b469.filter0001.16648.5515E0B88.0",
    "response": "400 try again later",
    "attempt": "5"
}

• 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.

Try Hevo Today!

SIGN UP HERE FOR A 14-DAY FREE TRIAL

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.

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.

Visit our Website to Explore Hevo

Hevo Data offers a No-code Data Pipeline that can automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Marketing, Customer Management, etc.

This platform allows you to transfer data from 100+ sources (including 40+ Free Sources) such as SendGrid Webhook and Cloud-based Data Warehouses like Snowflake, Google BigQuery, Amazon Redshift, etc. It will provide you with a hassle-free experience and make your work life much easier.

Want to take Hevo for a spin? 

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.