Shopify to MySQL: 2 Easy Methods

Shopify is an eCommerce platform that enables businesses to sell their products in an online store without spending time and effort on developing the store software.

Even though Shopify provides its 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 must load Shopify data into their relational databases or data warehouses. In this post, we will discuss how to load from Shopify to MySQL, one of the most popular relational databases in use today.

Method 1: Using Hevo to connect Shopify to MySQL

The best way to avoid the above limitations is to use a fully managed Data Pipeline platform as Hevo works out of the box. It will automate your data flow in minutes without writing any line of code. Its fault-tolerant architecture makes sure that your data is secure and consistent.

Hevo provides a truly efficient and fully automated solution to manage data in real-time and always has analysis-ready data at MySQL.

With Hevo’s point-and-click interface, loading data from Shopify to MySQL comes down to 2 simple steps:

• Step 1: Connect and configure your Shopify data source by providing the Pipeline Name, Shop Name, and Admin API Password.

• Step 2: Input credentials to the MySQL destination where the data needs to be loaded. These include the Destination Name, Database Host, Database Port, Database User, Database Password, and Database Name.

Method 2: Using Custom ETL Code to connect Shopify to MySQL

Shopify provides two options to access its product and sales data:

1. Use the export option in the Shopify reporting dashboard: This method provides a simple click-to-export function that allows you to export products, 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. 
2. Use Shopify rest APIs to access data: Shopify APIs provide programmatic access to products, orders, sales, and customer data. APIs are subject to throttling for higher request rates and use a leaky bucket algorithm to contain the number of simultaneous requests from a single user. The leaky bucket algorithm works based on the analogy of a bucket that leaks at 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:

• Step 1: Using Shopify Export Option 
• Step 2: Using Shopify REST APIs to Access Data 

Step 1: Using Shopify Export Option

The first method provides 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. The below steps detail how Shopify customers’ 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 with a downloadable CSV file.
• Login to MySQL and use the below statement to create a table according to the 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 following command:

LOAD DATA INFILE'customers.csv' INTO TABLE customers FIELDS TERMINATED BY ','  ENCLOSED BY '"' LINES TERMINATED BY 'rn'  IGNORE 1 LINES

Now, that was very simple. But, the problem here is that this is a manual process, and programmatically doing this is impossible. If you want to set up a continuous syncing process, this method will not be helpful. For that, we will need to use the Shopify APIs.

Step 2: Using Shopify REST APIs to Access Data 

Shopify provides a large set of APIs that are meant for building applications that interact with Shopify database. Our focus today will be on the product APIs allowing 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 users interact with only a specific Shopify store.

In this case, authentication is done by generating a username and password from the Shopify Admin. If you need to build an application that any Shopify store can use, 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 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

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 to the next page:

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, we are using the Linux command-line utility called jq. You can read more about this utility here. For simplicity, we are only extracting the 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 'rn' ;

Now you have your Shopify product data in your MySQL. 

Limitations of Using Custom ETL Code to Connect 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 want to bring a limited set of data points from Shopify MySQL. You will need to write a complicated JSON parser if you need to extract more data points
• This approach fits well if you need 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 to accomplish this would be using 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.

Get Started with Hevo for Free by choosing Hevo’s 14-day free trial.

Use Cases of Shopify to MySQL Integration

Connecting data from Shopify to MySQL has various advantages. Here are a few usage scenarios:

1. Advanced Analytics: MySQL’s extensive data processing capabilities allow you to run complicated queries and data analysis on your Shopify data, resulting in insights that would not be achievable with Shopify alone.
2. Data Consolidation: If you’re using various sources in addition to Shopify, syncing to MySQL allows you to centralize your data for a more complete picture of your operations, as well as set up a change data capture process to ensure that there are no data conflicts in the future.
3. Historical Data Analysis: Shopify has limitations with historical data. Syncing data to MySQL enables long-term data retention and trend monitoring over time.
4. Data Security and Compliance: MySQL offers sophisticated data security measures. Syncing Shopify data to MySQL secures your data and enables advanced data governance and compliance management.
5. Scalability: MySQL can manage massive amounts of data without compromising performance, making it a perfect alternative for growing enterprises with expanding Shopify data.

Conclusion

• This blog talks about the different methods you can use to connect Shopify to MySQL in a seamless fashion: using custom ETL Scripts and a third-party tool, Hevo.
• That’s it! No Code, No ETL. Hevo takes care of loading all your data in a reliable, secure, and consistent fashion from Shopify to 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. It helps transfer data from Shopify to a destination of your choice for free.

FAQ on Shopify to MySQL

Sign up for 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 moving data from Shopify to MySQL? Let us know in the comments.