How to Connect Shopify to Redshift: Easy Steps Explained

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.

Introduction to Shopify

Shopify is an e-commerce platform that helps businesses to create online stores and sell their products. They also have an online retail point of sale system (Shopify POS) where merchants can sell their products in person. It is designed for all businesses regardless of their sizes. Shopify provides a set of tools and services, such as payment gateways, shipping, marketing, and customer engagement, so that users can grow their businesses easily. Read about integrating Shopify to MySQL and centralize your data for a complete picture of your operations.

Various products or services that you can sell on Shopify

• Physical Products: Shopify supports door-step delivery of your manufactured products such as jewelry, custom t-shirts, mugs, etc. You can schedule the delivery of various products using Shopify.
• Digital Products: With Shopify, you can provide digital services such as e-books, audio files, course materials, etc. to your customers.   
• Services and Consultation: If you provide 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 memberships, event memberships, etc., can be sold to customers using Shopify.
• 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 events as well.
• Rentals: You can also promote and advertise various types of rental services, such as rental taxis, furniture, gadgets, etc., using Shopify and engage with customers in a better and more efficient way.
• Classes: You can advertise online tutorials on Shopify, allowing customers to subscribe to various online courses such as studies, fitness, yoga, etc. 

Introduction to Amazon Redshift

Amazon Redshift became the first generation of Cloud-based Data Warehouse to mark a move away from on-premise Data Warehouse Systems. It is based on MPP technology from ParAccel, which itself is a fork of the popular Postgres open-source database. Redshift was an instant hit among companies, primarily because it brought immediate access to a fully-managed, petabyte-scale, cost-effective data solution for analyzing large volumes of data without having to maintain hardware resources. 

Today, Redshift is the fastest-growing service on Amazon Web Services (AWS) and is used by tens of thousands of customers worldwide to process exabytes (1018 bytes) of data daily. It provides an innovative approach to query execution that seamlessly combines database operators in each query fragment. Amazon Redshift also automates common administrative tasks like provisioning, configuration, monitoring, backup, and security, so that you can focus on high-value tasks.

Business Benefits of Using Amazon Redshift

• Native Integration with AWS: Redshift databases take full advantage of Amazon’s cloud server infrastructure and the AWS ecosystem to work seamlessly with the rest of the tools from AWS. This can include data storage and backups to AWS S3, streaming ingestion from Kinesis and MSK, Redshift ML, federated queries to Aurora and RDS operational databases, and many more.
• Easy Deployment: Every business can set up and deploy its AWS Redshift Data Warehouse in a matter of just minutes. AWS delivers high performance with gigabyte to petabyte-scale scalability at a fraction of the cost of what other Cloud-based Data Warehouses offer.
• Massively Parallel Processing: Amazon Redshift Data Warehouse features a powerful combination of massively parallel processing and columnar data storage to provide efficient storage and optimum query performance across large datasets. It distributes table rows to multiple compute nodes so that data can be processed in parallel, and uses columnar data storage to reduce your overall I/O requirements. 
• Concurrency Scalability: Amazon Redshift cluster nodes can be added with more processing power when you need it. To handle burst workloads and concurrent queries, Amazon Redshift can automatically and transparently add more concurrency scaling resources without burning a hole in your pocket.
• Real-time Data Analytics: Redshift supports Real-time Data Streaming with external streaming providers such as Kafka or Kinesis. It can ingest real-time data and process it super quickly using services like AWS Glue, AWS QuickSight, and AWS Sagemaker. For additional details, please see one of our other blog posts, Real-Time Redshift Analytics: 5 Simple Steps to Get Started.

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.

Also, take a look at how you can connect Shopify to PostgreSQL to explore other ways you can integrate your Shopify data.

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

Step 2.1: Configure Shopify as your Source

Step 2.2: Configure Redshift as your Destination

Advantages of using Hevo Data Platform 

• Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer. 
• Schema Management: Hevo can automatically detect the schema of the incoming data and maps it to the destination schema.
• Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.

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.

Sign up for a 14-day free trial and simplify your data integration process. Check out the pricing details to understand which plan fulfills all your business needs.

Frequently Asked Questions (FAQs)