Connecting Shopify Webhook to Redshift: 2 Easy Methods

Online stores and e-commerce businesses are booming and more people are shifting towards e-commerce businesses. An e-commerce site handles many operations, tracks users, receives and sends data, etc. But not all of us can build our own e-commerce site or online store from scratch. Shopify is an e-commerce platform allowing users to set up their online stores within minutes.

Shopify contains huge volumes of valuable data related to orders, users, inventory, sales, products, etc. All this data can be used to analyze and optimize the store and workflow. Amazon Redshift is a fully managed Data Warehouse that enables users to load theri business data to a storage space for real-time analysis. Connecting Shopify Webhook to Redshift allows users to get instantly notified and transfer relevant data from Shopify to Redshift when any specified event triggers such as new order placed, users added, etc. 

Connecting Shopify Webhook to Redshift helps in fetching data when any specific event occurs. In this article, you will learn about Shopify and Amazon Redshift. You will read about the two methods to connect Shopify Webhook to Redshift. 

Prerequisites 

• An active Shopify account.
• An active AWS account.

What is Shopify?

Shopify is an e-commerce platform allowing users to build an online store and retail point-of-sale systems within a few minutes. It is a leading online store builder widely used by beginners and professionals. This platform provides design themes, site hosting, marketing tools, and even a blog to help entrepreneurs manage their businesses easily. First-time online business owners can use Shopify to start a small-scale store before expanding to a personal website in the future. Users don’t need any technical knowledge to get started with building a Shopify site because it allows users to build online stores without writing a single line of code using its user-friendly interface.

Shopify offers its services with 5 subscription packages. Shopify charges $29 for the Basic Shopify edition suggested for beginners, $ 79 for the Shopify edition, and $229 for the Advanced Shopify edition, Shopify Plus edition, and Shopify Lite edition. You can integrate your Shopify store with third-party tools if your need exceeds the purview of in-built Shopify tools. 

Key Features of Shopify

Some of the main features of Shopify are listed below:

• Analytics and Reporting: Shopify allows users access to a plethora of Marketing Tools for Email Marketing and in-built Analytics and Reporting tools. 
• Pre-built Themes: Shopify comes with hundreds of pre-built and ready-to-set up themes of all categories allowing users to set up an online store in just a few minutes. It comes with Live Search and Drop-Down Menus.
• App Integrations: Shopify offers integration with various third-party apps and services for a better user experience and easy accessibility.

What is Amazon Redshift?

Amazon Redshift is a fully managed Data Warehousing service provider by AWS (Amazon Web Services). AWS Redshift is designed to store petabytes of data and perform real-time analysis to generate insights. It comes with Massively Parallel Processing Technology (MPT) for extraordinary performance and high querying speed. 

It’s a column-oriented Database that stores the data in a columnar format compared to traditional Databases stored in a row format. Amazon Redshift has its own compute engine to perform computing and generate critical insights. 

Key Features of Amazon Redshift

Some of the main features of Amazon Redshift are listed below:

• Massively Parallel Processing: Amazon Redshift supports MPP distributed design in which several processors apply the divide and conquer strategy to handle large data sets.
• Fault-Tolerant: Amazon Redshift continuously monitors the health of a cluster and automatically replicated data from the failed drives and replaces nodes as required to deliver a fault-tolerant architecture.
• Flexible Querying: Amazon Redshift comes with a query editor that allows users to flexibly query data from the console or connect any other SQL client tools or BI tools.

Connecting Shopify Webhook to Redshift

Now that you have understood about Shopify and Amazon Redshift. In this section, you will go through the two methods to connect Shopify Webhook to Redshift. The following methods to connect Shopify Webhook to Redshift are listed below:

• Method 1: Simplify Shopify Webhook to Redshift Connection Using Hevo
• Method 2: Manually Connecting Shopify Webhook to Redshift

Method 1: Simplify Shopify Webhook to Redshift Connection Using Hevo

Hevo Data helps you connect Shopify Webhook to Redshift in a completely hassle-free & automated manner. Hevo supports Webhooks as a source and loads data from Webhooks to any Data Warehouse in minutes. Follow Hevo’s Webhooks Integration guide to connect Shopify Webhook to Redshift. Hevo lets you create your Shopify to Redshift connection in 2 steps: 

1. From the list of sources provided, select Shopify as the source. Enter the Pipeline name and click continue.

2. On the Add Destination page, select Amazon Redshift. On the Configure your Amazon Redshift Destination page, fill in all the mandatory fields to continue.

3. Click TEST CONNECTION. This button is enabled once all the mandatory fields are specified.

4. Click SAVE & CONTINUE. This button is enabled once all the mandatory fields are specified. That’s it; you have successfully connected Shopify Webhook to Redshift.

Method 2: Manually Connecting Shopify Webhook to Redshift

The manual process to connect Shopify Webhook to Redshift requires you to create a webhook for every event and load that data to Amazon Redshift. The following steps to manually connect Shopify Webhook to Redshift are listed below:

Step 1: Creating Shopify Webhook

• First, log in to your Shopify account here.
• Now click on the Settings option located at the bottom of the left side navigation pane.

• Now, navigate to the Notifications option.

• Here, scroll down till the end and here, you will see the Webhooks section.
• Click on the Create Webhook button.

• It will open a dialog box to add a webhook.

• Select any of the events for which you want to send data when that event occurs. 
• Then choose the format of data you want, here we are going with JSON.
• Here, we are using a webhook tester site for retrieving the data when any event triggers. In the URL section paster the URL from https://webhook.site/.
• Select the Webhook API Version.
• Then, click on the Save button.

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

• Now, go to your Amazon Redshift CLI and run the following command given below:

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

• The output of the command 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",
            ...
            ...
}

• Here, you can copy the IP Addresses of your Nodes in the PublicIPAddress parameter of the ClusterNodes parameter and the Cluster Public Key from the ClusterPublicKey parameter.

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

The Cluster Public Key of the Amazon Redshift will establish a secure SSH connection between the remote host and the Amazon Redshift cluster. Now you can use the key by running the following commanklds locally:

• 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

Amazon Redshift uses Manifest File to connect to the local machine. It contains entries of the SSH host endpoints and the commands to be executed on the 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 5: 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 6: 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;

Conclusion

In this article, you learned about Shopify, Amazon Redshift, and why to connect Shopify Webhook to Redshift. Also, you went through two methods to connect Shopify Webhook to Redshift. The manual method is tedious and time-consuming as you have to manually create a webhook for every event and copy the data to AWS. Instead, you can go for an automated method to connect your Shopify Webhook to Redshift using Hevo. Sign up for Hevo’s 14-day free trial and experience seamless data migration.

FAQs