Shopify to Redshift: 2 Easy Methods

Software As A Service offerings like Shopify has revolutionized the way businesses step up their Sales channels. Shopify provides a complete set of tools to aid in setting up an e-commerce platform in a matter of a few clicks. Shopify comes bundles with all the configurations to support a variety of payment gateways and customizable online shop views. Bundles with this package are also the ability to run analysis and aggregation over the customer data collected through Shopify images.

Even with all these built-in Shopify capabilities, organizations sometimes need to import the data from Shopify to their Data Warehouse since that allows them to derive meaningful insights by combining the Shopify data with their organization data. Doing this also means they get to use the full power of a Data Warehouse rather than being limited to the built-in functionalities of Shopify Analytics. This post is about the methods in which data can be loaded from Shopify to Redshift, one of the most popular cloud-based data warehouse.

Methods to connect Shopify to Redshift

There are multiple methods that can be used to connect Shopify to Redshift and load data easily:

• Method 1: Using Shopify APIs to connect Shopify to Redshift
• Method 2: Using Hevo Data, a No-code Data Pipeline to Connect Shopify to Redshift

Method 1: Using Shopify APIs to connect Shopify to Redshift

Since Redshift supports loading data to tables using CSV, the most straightforward way to accomplish this move is to use the CSV export feature of Shopify Admin. But this is not always practical since this is a manual process and is not suitable for the kind of frequent sync that typical organizations need. We will focus on the basics of accomplishing this in a programmatic way which is much better suited for typical requirements.

Shopify provides a number of APIs to access the Product, Customer, and Sales data. For this exercise, we will use the Shopify Private App feature. A Private App is an app built to access only the data of a specific Shopify Store. To create a Private App script, we first need to create a username and password in the Shopify Admin. Once you have generated the credentials, you can proceed to access the APIs. We will use the product API for reference in this post. 

Use the below snippet of code to retrieve the details of all the products in the specified Shopify store.

curl --user shopify_app_user:shopify_app_password GET /admin/api/2019-10/products.json?limit=100

The important parameter here is the Limit parameter. This field is there because the API is paginated and it defaults to 50 results in case the Limit parameter is not provided. The maximum pagination limit is 250 results per second.

To access the full data, Developers need to buffer the id of the last item in the previous request and use that to form the next curl request. The next curl request would look like as below.

curl --user shopify_app_user:shopify_app_password GET /admin/api/2019-10/products.json? limit=100&since_id=632910392 -o products.json

You will need a loop to execute this. From the above steps, you will have a set of JSON files that should be imported to Redshift to complete our objective. Fortunately, Redshift provides a COPY command which works well with JSON data. Let’s create a Redshift table before we export the data.

create table products( product_id varchar(25) NOT NULL, type varchar(25) NOT NULL, vendor varchar(25) NOT NULL, handle varchar(25) NOT NULL, published_scope varchar(25) NOT NULL )

Once the table is created, we can use the COPY command to load the data. Before copying ensure that the JSON files are loaded into an S3 bucket since we will be using S3 as the source for COPY command. Assuming data is already in S3, let’s proceed to the actual COPY command. The challenge here is that the Shopify API result JSON is a very complex nested JSON that has a large number of details. To map the appropriate keys to Redshift values, we will need a json_path file that Redshift uses to map fields in JSON to the Redshift table. The command will look as below.

copy products           from ‘s3://products_bucket/products.json’            iam_role ‘arn:aws:iam:0123456789012:role/MyRedshiftRole'           json ‘s3://products_bucket/products_json_path.json’ The json_path file for the above command will be as below. {     "jsonpaths": [         "$['id']",         "$['product_type']",         "$[‘vendor’]",         "$[‘handle’]",         "$[‘published_scope’]"     ] }

This is how you can connect Shopify to Redshift. Please note that this was a simple example and oversimplifies many of the actual pitfalls in the COPY process from Shopify to Redshift.

Limitations of migrating data using Shopify APIs

• The Developer needs to implement a logic to accommodate the pagination that is part of the API results. 
• Shopify APIs are rate limited. The requests are throttled based on a Leaky Bucket algorithm with a bucket size of 40 and 2 requests per second leak in case of admin APIs. So your custom script will need a logic to handle this limit in case your data volume is high.
• In case you need to Clean, Transform, Filter data before loading it to the Warehouse, you will need to build additional code to achieve this.
• The above approach works for a one-off load but if frequent sync which also handles duplicates is needed, additional logic needs to be developed using a Redshift Staging Table.
• In case you want to copy details that are inside the nested JSON structure or arrays in Shopify format, the json_path file development will take some development time.

Method 2: Using Hevo Data, a No-code Data Pipeline to Connect Shopify to Redshift

Hevo Data, a No-code Data Pipeline can help you move data from 100+ Data Sources including Shopify (among 40+ Free sources) swiftly to Redshift. Hevo is fully managed and completely automates the process of not only loading data from your desired source 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. It helps transfer data from Shopify to a destination of your choice for free.

Steps to use Hevo Data:

Hevo Data focuses on two simple steps to get you started:

Configure Source: Connect Hevo Data with Shopify by simply providing the API key and Pipeline name.

Integrate Data: Load data from Shopify to Redshift by simply providing your Redshift database credentials. Enter a name for your database, the host and port number for your Redshift database and connect in a matter of minutes.

Advantages of using Hevo Data Platform:

• Real-Time Data Export: Hevo with its strong integration with 100+ sources, allows you to transfer data quickly & efficiently. This ensures efficient utilization of bandwidth on both ends.
• Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
• Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.
• Minimal Learning: Hevo with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
• Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
• Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.

About Shopify

Shopify is a powerful e-commerce platform designed to allow people or businesses to sell their offerings/products online. Shopify helps you set up an online store and also offers a Point Of Sale (POS) to sell the products in person. Shopify provides you with Payment Gateways, Customer Engagement techniques, Marketing, and even Shipping facilities to help you get started.

Various product or services that you can sell on the Shopify:

• Physical Products: Shopify allows you to perform the door-step delivery of the products you’ve manufactured that can be door-shipped to the customer. These include anything like Printed Mugs/T-Shirt, Jewellery, Gifts, etc.
• Digital Products: Digital Products can include E-Books, Audios, Course Material, etc.
• Services and Consultation: If you’re providing services like Life Consultation, Home-Cooked delicacies, Event Planning, or anything else, Shopify has got you covered.
• Memberships: Various memberships such as Gym memberships, Yoga-classes membership, Event Membership, etc. can be sold to the customers.
• Experiences: Event-based experiences like Adventurous Sports and Travel, Mountain Trekking, Wine Tasting, events, and hands-on workshops. You can use Shopify to sell tickets for these experiences as well.
• Rentals: If you’re running rental services like Apartment rentals, rental Taxis, or Gadgets, you can use Shopify to create Ads and engage with the customer.
• Classes: Online studies, Fitness classes can be advertised here.

Shopify allows you to analyze Trends and Customer Interaction on their platform. However, for advanced Analytics, you may need to store the data into some Database or Data Warehouse to perform in-depth Analytics and then move towards a Visualization tool to create appealing reports that can demonstrate these Trends and Market positioning.

For further information on Shopify, you can check the official site here.

About Redshift

Redshift is a columnar Data Warehouse managed by Amazon Web Services (AWS). It is designed to run complex Analytical problems in a cost-efficient manner. It can store petabyte-scale data and enable fast analysis. Redshift’s completely managed warehouse setup, combined with its powerful MPP (Massively Parallel Processing) have made it one of the most famous Cloud Data Warehouse options among modern businesses. You can read more about the features of Redshift here.

Conclusion

In this blog, you were introduced to the key features of Shopify and Amazon Redshift. You learned about two methods to connect Shopify to Redshift. The first method is connecting using Shopify API. However, you explored some of the limitations of this manual method. Hence, an easier alternative, Hevo Data was introduced to you to overcome the challenges faced by previous methods. You can seamlessly connect Shopify to Redshift with Hevo for free.

visit our website to explore hevo

Want to try Hevo?

sign up for a 14-day free trial and experience the feature-rich Hevo suite first hand. Have a look at our unbeatable pricing, which will help you choose the right plan for you.

What are your thoughts on moving data from Shopify to Redshift? Let us know in the comments.