SendGrid to Redshift Migration: 2 Easy Methods

Most businesses consider Digital Marketing as one of the most reliable techniques to reach potential customers. These platforms can help businesses improve upon and automate a lot of processes making the job of their Marketing teams easier and faster. There are various tools used by Marketers today for running their Marketing Campaigns. One of the most popular tools by businesses for Email Marketing is SendGrid.

This article will provide you with an in-depth understanding of how you can set up SendGrid to Redshift Migration to perform a comprehensive analysis of your SendGrid data.

Introduction to SendGrid

SendGrid is a Cloud-based Simple Mail Transfer Protocol (SMTP) provider that allows users to send Emails without setting up any Email Servers. SendGrid provides its users with reliable Email delivery, scalability, and real-time analytics along with a flexible API that allows users to set up custom integrations easily. It is a popular Email Automation platform with a strong focus on Email deliverability. This means that the platform ensures that Emails do not end up in the Spam folders of the recipients but are sent directly to the Inbox.

Understanding the Key Features of SendGrid

• Email Deliverability: One of the most popular features of SendGrid is that ensures that emails reach the Inbox folder of the recipients and not the Spam folder. SendGrid helps businesses understand the various factors that might result in their Emails being sent to Spam. This could include parameters such as Bounce Rate, Spam Reports, IP Health, number of people unsubscribing, etc. Based on the understanding of these factors, it suggests changes to improve the sender’s reputation. It also has various functionalities that can implement Email validations to filter out misspelled, inactive, disposable, or non-existent Email Addresses so that your Email delivery rates and actions on Emails improve further 
• Email Editor: SendGrid houses its own Email Editor that allows users to implement pre-built Email templates or build templates from scratch either using its drag and drop functionality or coding the Email template from scratch. SendGrid also allows users to add custom tags to Emails which can be used to add custom fields. These custom fields can be used to personalize Email based on the information that the businesses have about the recipients. 

• Analytics: SendGrid houses robust in-built functionality that allows businesses to get comprehensive insights into customer behavior. Along with basic metrics such as the number of Opens, Clicks, Bounces, Spam Reports, etc., SendGrid also allows businesses to track various complex metrics such as Opens by location, email provider, device, etc.

Introduction to Amazon Redshift

Amazon Redshift is a fully-managed petabyte-scale Cloud-based Data Warehouse, that was developed by Amazon. It was designed for the storage and analysis of petabyte-scale data. Amazon Redshift is built on a Column-oriented Architecture and designed to connect with numerous SQL-based clients, Business Intelligence, and Data Visualization tools and make data available to users in real-time. Based on PostgreSQL 8, Amazon Redshift delivers significantly enhanced performance and more efficient querying as compared to all other Data Warehouses. This helps teams make sound business analyses and decisions. More than 15,000 businesses now use Amazon Redshift globally, including large Enterprises such as Pfizer, McDonald’s, Facebook, etc.

Understanding the Key Features of Amazon Redshift

The key features of Amazon Redshift are as follows:

• Massively Parallel Processing (MPP): Massively Parallel Processing is a distributed design approach in which the divide and conquer strategy is applied by several processors on large data jobs. A large processing job is broken down into smaller jobs which are then distributed among a cluster of Compute Nodes. These Nodes perform their computations parallelly rather than sequentially. As a result, there is a considerable reduction in the amount of time Redshift requires to complete a single, massive job.
• Fault Tolerance: Data Accessibility and Reliability are of paramount importance for any user of a database or a Data Warehouse. Amazon Redshift monitors its Clusters and Nodes around the clock. When any Node or Cluster fails, Amazon Redshift automatically replicates all data to healthy Nodes or Clusters.
• Redshift ML: Amazon Redshift houses a functionality called Redshift ML that gives data analysts and database developers the ability to create, train, and deploy Amazon SageMaker models using SQL seamlessly.
• Column-Oriented Design: Amazon Redshift is a Column-oriented Data Warehouse. This makes it a simple and cost-effective solution for businesses to analyze all their data using their existing Business Intelligence tools. Amazon Redshift achieves optimum query performance and efficient storage by leveraging Massively Parallel Processing (MPP), Columnar Data Storage, along with efficient and targeted Data Compression Encoding schemes.

Methods to Set up SendGrid to Redshift Migration

Businesses can set up SendGrid to Redshift Migration by implementing one of the two following methods:

• Method 1: Manual SendGrid to Redshift Migration
• Method 2: SendGrid to Redshift Migration Using Hevo Data

Method 1: Manual SendGrid to Redshift Migration

You can leverage the following SendGrid APIs to retrieve the account’s information such as statistics, bounces, spam reports, unsubscribe, etc. based on your business use case and data requirements:

• Bounces API
• Blocks API
• Campaigns API
• Contacts API
• Designs API
• Sender Identities API
• Spam Reports API
• Invalid Emails API
• Users API
• Subusers API

You can use the following commands to extract data from any of the APIs:

curl -X "GET" "https://api.sendgrid.com/v3/templates" -H "Authorization: Bearer Your.API.Key-HERE" -H "Content-Type: application/json"

For this example, you will load Contacts data from SendGrid using the Contacts API. The following API call helps you retrieve all contact lists:

GET /contactdb/lists

The following endpoint allows you to retrieve all of your recipient lists:

https://api.sendgrid.com/v3/contactdb/lists

Schema of the object returned would be as follows:

lists	array [object]	required
id	integer	required
name	string	required
recipient_count	integer	required

You can set up SendGrid to Redshift Migration by implementing the following steps:

• Step 1: Creating SendGrid API key
• Step 2: Retrieving Redshift Cluster Public Key and Cluster Node IP Addresses
• Step 3: Placing Redshift Cluster’s Public Key on your Server
• Step 4: Uploading Manifest File to Amazon S3 Bucket
• Step 5: Loading Data into Amazon Redshift

Step 1: Creating SendGrid API key

• Open SendGrid settings from the left Navigation Bar, and select API Keys.
• From the upper right corner, select Create API Key. You will see a page similar to the image below allowing you to define the specifics of your API Key.

• Enter a unique name for your API Key, select Full Access for API Key Permissions, and select Create and View.

• Copy the API Key created and save it in a secure location.

Step 2: Retrieving Redshift Cluster Public Key and Cluster Node IP Addresses

• Use the Amazon Redshift CLI to run the following command:

aws redshift describe-clusters --cluster-identifier <cluster-identifier>

• The response will look similar to this:

{
    "Clusters": [
        {
            "VpcSecurityGroups": [],
            "ClusterStatus": "available",
            "ClusterNodes": [
                {
                    "PrivateIPAddress": "10.xxx.xxx.xxx",
                    "NodeRole": "LEADER",
                    "PublicIPAddress": "10.xxx.xxx.xxx"
                },
                {
                    "PrivateIPAddress": "10.xxx.xxx.xxx",
                    "NodeRole": "COMPUTE-0",
                    "PublicIPAddress": "10.xxx.xxx.xxx"
                },
                {
                    "PrivateIPAddress": "10.xxx.xxx.xxx",
                    "NodeRole": "COMPUTE-1",
                    "PublicIPAddress": "10.xxx.xxx.xxx"
                }
            ],
            "AutomatedSnapshotRetentionPeriod": 5,
            "PreferredMaintenanceWindow": "mon:02:00-mon:02:30",
            "AvailabilityZone": "ap-south-1",
            "NodeType": "dc2.large",
            "ClusterPublicKey": "ssh-rsa yourpublickey...B4YHg Amazon-Redshift",
            ...
            ...
}

• You will now be able to see the IP Addresses of your Nodes in the PublicIPAddress parameter of the ClusterNodes parameter and the Cluster Public Key can be seen in ClusterPublicKey parameter.

Step 3: Placing Redshift Cluster’s Public Key on your Server

The Redshift Cluster’s Public Key will enable secure SSH connections between the remote host and the Amazon Redshift cluster. You can use the key by implementing the following steps:

• Create the ~/.ssh directory in the user’s root directory.

$ mkdir -p ~/.ssh

• Create the authorized_keys file.

$ touch ~/.ssh/authorized_keys

• Copy and paste the contents of the Redshift Cluster’s Public Key into the /home/<username>/.ssh/authorized_keys file on your local machine so that it can recognize the Amazon Redshift Cluster and accept the SSH connection.  

Step 4: Creating a Manifest File on Local Machine

A Manifest file is needed so that Amazon Redshift can use it to connect to your local machine. It specifies the entries of the SSH host endpoints and the commands that are executed on your machine to send data to Amazon Redshift. The format for the Manifest file is as follows:

{
  "entries": [
    {"endpoint":"<ssh_endpoint_or_IP>",
      "command": "<remote_command>",
      "mandatory":true,
      "publickey": "<public_key>",
      "username": "<host_user_name>"},
    {"endpoint":"<ssh_endpoint_or_IP>",
      "command": "<remote_command>",
      "mandatory":true,
      "publickey": "<public_key>",
      "username": "host_user_name"}
    ]
}

An example of the Manifest file is as follows:

{
  "entries": [
    {"endpoint":"ec2-555-22-777-999.compute-1.amazonaws.com",
      "command": "cat sendgrid_contact_lists.json",
      "mandatory":true,
      "publickey": "AAAAB3NzaC1yc2EAAAADAQABAAABAQCyztSdr8r+",
      "username": "your_user_name"},
    {"endpoint":"ec2-555-22-777-999.compute-1.amazonaws.com",
      "command": "cat sendgrid_spam_reports.json",
      "mandatory":true,
      "publickey": "AAAAB3NzaC1yc2EAAAADAQABAAABAQCyztSdr8r+",
      "username": "your_user_name"}
    ]
}

Step 4: Uploading Manifest File to Amazon S3 Bucket

The following command copies an object into an Amazon S3 bucket and grants read permissions on the object to all users:

aws s3 cp file.txt s3://my-bucket/ --grants 
read=uri=http://acs.amazonaws.com/groups/global/AllUsers

Step 5: Loading Data into Amazon Redshift

You can now use the following COPY command to connect to your local machine and load data extracted from SendGrid to Redshift:

copy contacts
from 's3://your_s3_bucket/ssh_manifest' credentials
iam_role 'arn:aws:iam::0123456789:role/Your_Redshift_Role'
delimiter '|'
ssh;

Limitations of Manual SendGrid to Redshift Migration

The limitations associated with manual SendGrid to Redshift Migration are as follows:

• Manual SendGrid to Redshift Migration is a resource-intensive process that will demand constant intervention from your developers for maintenance and testing purposes.
• This script does not scale well, especially in cases where SendGrids generate a large volume of data.
• There are too many data points from all the Sendgrid APIs. You would need to map every data point to the relevant Redshift table manually. This will eventually become hard to manage when data is being extracted from multiple APIs.
• If you need to clean, transform, and enrich the data before loading it to the Data Warehouse, you would need to take additional steps to perform the required transformations.
• The manual process would be helpful in doing a batch or one-time data load. If your use case is to move data from SendGrid to Redshift in real-time, additional steps would need to be taken to ensure that the pipeline can handle that.

Method 2: SendGrid to Redshift Migration Using Hevo Data

The following steps can be implemented to set up SendGrid to Redshift Migration using Hevo:

• Configure Source: Connect Hevo Data with SendGrid by providing a unique name for your Pipeline, along with your authorized SendGrid API Key.

• Integrate Data: Complete SendGrid to Redshift migration by providing information about your Redshift database and its credentials such as database name, username, and password, along with information about port number associated with your Redshift database. You’ll also need to need to provide the schema name for your database, and its cluster, along with a unique name for your destination.

Conclusion

This article provided you with a step-by-step guide on how you can set up SendGrid to Redshift Migration manually or using Hevo. However, there are certain limitations associated with the manual method. If those limitations are not a concern to your operations, then using it is the best option but if it is, then you should consider using automated Data Integration platforms like Hevo.

Hevo helps you directly transfer data from a source of your choice to a Data Warehouse, Business Intelligence, or desired destination in a fully automated and secure manner without having to write the code. It will make your life easier and make data migration hassle-free. It is User-Friendly, Reliable, and Secure.

FAQ on SendGrid to Redshift Migration