Shopify is an eCommerce platform offered as a service which enables the business to sell their products in an online store without having to spend time and effort on developing the store software. Shopify offers a complete suite of products to handle everything from setting up the business landing page to the integration of payment services like Stripe. Even though Shopify provides its own suite of analytics reports, it is not always easy to combine Shopify data with the organization’s on-premise data and run analysis tasks. Therefore most organizations will need to load Shopify data into their relational databases or data warehouse. In this post, we will discuss how to load from Shopify to MySQL, one of the most popular relational databases in use today.
Methods for Shopify to MySQL Data Replication
There are two popular methods to move data from Shopify to MySQL
Method 1: Build custom ETL code to move data from Shopify to MySQL
Method 2: A ready to use Hevo Data Integration Platform (14 Days Free Trial)
This blog covers the first approach in great detail. The blog also covers the limitations of this approach and also highlights the workarounds to mitigate them.
Shopify to MySQL: Using Custom Code Method
Getting Shopify Data Access
Shopify provides two options to access its product and sales data.
- Use the export option in the Shopify reporting dashboard – This method provides a simple click to export function which allows you to export product, orders or customer data into CSV files. The caveat here is that this will be a completely manual process and there is no way to do this programmatically.
- Use Shopify rest APIs to access data – Shopify APIs provide programmatic access to products, orders, sales, and customer data. APIs are subject throttling for higher request rates and uses a leaky bucket algorithm to contain the number of simultaneous requests from a single user. Leaky bucket algorithm works based on the analogy of a bucket that leaks in the bottom. The leak rate is the number of requests that will be processed simultaneously and the size of the bucket is the number of maximum requests that can be buffered. Anything over the buffered request count will lead to an API error informing the user of the request rate limit in place.
Let us now move into how data can be loaded to MySQL using each of the above methods.
Shopify to MySQL: Using Shopify Export option
The first method provides a simple click and export solutions to get the product, orders and customer data into CSV. This CSV can then be used to load to a MySQL instance. Below steps detail how Shopify customer’s data can be loaded to MySQL this way.
- Go to Shopify admin and go to the customer’s tab
- Click Export
- Select whether you want to export all customers or a specified list of customers. Shopify allows you to select or search customers if you only want to export a specific list
- After selecting customers, select ‘plain CSV’ as the file format
- Click Export customers and Shopify will provide you a downloadable CSV file
- Login to MySQL and use the below statement to create a table according to Shopify format
CREATE TABLE customers ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, email VARCHAR(50), company VARCHAR(50), address1 VARCHAR(50), address2 VARCHAR(50), city VARCHAR(50), province VARCHAR(50), province_code VARCHAR(50), country VARCHAR(50), country_code VARCHAR(50), zip VARCHAR(50), phone VARCHAR(50), accepts_markting VARCHAR(50), total_spent DOUBLE, email VARCHAR(50), total_orders INT, tags VARCHAR(50), notes VARCHAR(50), tax_exempt VARCHAR(50)
- Load data using the below command
LOAD DATA INFILE'customers.csv' INTO TABLE customers FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES
Now that was very simple. But, the problem here is that this is a manual process and programmatically doing this is not possible. If you are looking to set up a continuous syncing process, this method will not be helpful. For that, we will need to use the Shopify APIs
Shopify to MySQL: Using the Shopify APIs
Shopify provides a large set of APIs that are meant for building applications that interact with Shopify data. Our focus today will be on the product APIs which allows the users to access all the information related to products belonging to the specific user account.
We will be using the Shopify private apps mechanism to interact with APIs. Private Apps are Shopify’s way of letting the user interact with only a specific Shopify store. Authentication in this case if done by generating a username and password from the Shopify Admin. If you need to build an application that can be used by any Shopify store, you will need a public app configuration with OAuth authentication.
Before beginning the steps, ensure you have gone to Shopify Admin and have access to the generated username and password.
Once you have access to the credential, accessing the APIs is very easy and is done using the basic HTTP authentication. Let’s look into how the most basic API can be called using the generated username and password.
curl --user:password GET https://shop.myshopify.com/admin/api/2019-10/shop.json
As evident from the above snippet, a username and password, separated by a colon is prepended to the URL
To get a list of all the products in Shopify use the following command.
curl --user user:password GET /admin/api/2019-10/products.json?limit=100
Please note this endpoint is paginated and will return only a maximum of 250 results per page. The default pagination limit is 50 if the limit parameter is not given. From the initial response, users need to store the id of the last product they received and then use it with the next request to get the next page. This is done as below.
curl --user user:password GET /admin/api/2019-10/products.json?limit=100&since_id=632910392 -o products.json
Where since_id is the last product id that was received on the previous page.
The response from the API is a nested JSON that contains all the information related to the products such as title, description, images, etc. and more importantly the variants sub JSON which provides all the variant-specific information like barcode, price,inventory_quantity, and much more information. Users need to parse this JSON output and convert the JSON file into a CSV file of the required format before loading it to MySQL.
For this, I am using the Linux command-line utility called jq. You can read more about this utility here. For simplicity, I am only extracting id, product_type and product title from the result. Assuming your API response is stored in products.json
Cat products.json | jq '.data.headers | [.id .product_type product_title] | join(", ")' >> products.csv
Please note you will need to write complicated JSON parsers if you need to retrieve more fields.
Once the CSV files are obtained, create the required MYSQL command beforehand and load data using the ‘LOAD DATA INFILE’ command shown in the previous section.
LOAD DATA INFILE'products.csv' INTO TABLE customers FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' ;
Now you have your Shopify product data in your MySQL.
Limitations of the building custom code to move data from Shopify to MySQL:
Shopify provides two easy methods to retrieve the data into files. But, both these methods are easy only when the requests are one-off and the users do not need to execute them continuously in a programmatic way. Some of the limitations and challenges that you may encounter are as follows:
- The above process works fine if you are looking to bringing a limited set of data points from Shopify to MySQL. You will need to write complicated JSON parser if you need to extract more data points
- This approach fits well if your need is a one-time or batch data load from Shopify to MySQL. In case you are looking at real-time data sync from Shopify to MySQL, the above method will not work.
An easier way of accomplishing this would be to use a fully-managed data pipeline solution like Hevo, which can mask all these complexities and deliver a seamless data integration experience from Shopify to MySQL.
Shopify to MySQL: Exploring an easier alternative – Hevo Data
The best way to avoid the above limitations is to use a fully-managed Data Pipeline platform like Hevo works out of the box. With Hevo’s point and click interface, loading data from Shopify to MySQL comes down to 2 simple steps:
- Connect and configure your Shopify data source
- Input credentials to the MySQL destination where the data needs to be loaded
That’s it! No Code, No ETL. Hevo takes care of loading all your data in a reliable, secure and consistent fashion into MySQL.
Hevo can additionally connect to a variety of data sources (Databases, Cloud Applications, Sales and Marketing tools, etc.) making it easy to scale your data infrastructure at will.
Hevo comes with a 14-day free trial. Sign up today to explore how Hevo makes Shopify to MySQL a cakewalk for you!
What are your thoughts about the different approaches to move data from Shopify to MySQL? Let us know in the comments.