Magento to BigQuery: 2 Easy Methods

on Data Integration, Data Warehouse, Google BigQuery, Tutorials • January 3rd, 2022 • Write for Hevo

Magento to BigQuery - Featured Image

Magento is a popular e-commerce platform that lets you set up online stores for your business easily. Often, you may need to analyze the e-commerce data from Magento along with your offline sales data or online marketing data. For such analysis, data from all these sources need to be in a single data warehouse or database.

You may need to transfer your data from Magento to a data warehouse such as BigQuery for easy accessibility and analysis. If you are looking for simple ways to do this, read on to learn two methods to move data from Magento to BigQuery warehouse. This will not only help you analyze your e-commerce strategies but also dynamically optimize them going forward.

Table of Contents

What is Magento?

Magento to BigQuery - Magento logo
Image Source: Magneto

Magento is an open-source e-commerce platform that is the solution of choice for more than 100,000 online stores. Having been downloaded more than 2.5 million times, it is one of the most popular e-commerce solutions currently available, accounting for roughly 30% of the total market share. Being open-source and built on the Zend framework, Magento’s source code is available under OSL (Open Software License) v3.0. The first general availability release of the software was made available on March 31st, 2008, but Magento 2.0 was released on November 17, 2015, with a number of improvements and in two versions – Magento Open Source and Magento Commerce.

What is BigQuery?

Image Source

BigQuery is Google’s cloud data warehouse. It provides up to a Petabyte-scale of data warehousing and allows users to issue SQL-like queries on the stored data. Powered by Google’s Dremel engine and its vast server farms, BigQuery can perform complex queries on vast amounts of data at impressive speeds. BigQuery’s APIs enable users to build custom integrations with other platforms and services, including powerful data visualization tools that aid in business intelligence. 

Why move data from Magento to BigQuery?

There are three main reasons why moving your data from Magento to BigQuery would be a good idea.

  1. Moving data from Magento to BigQuery gives you the ability to run complex queries with improved performance and better response times.
  2. The possibility to combine data from your online store with relevant data from other sources. These extra dimensions of data will help you to derive enterprise-wide cross-functional insights when you move your data from Magento to BigQuery. 
  3. Moving data from Magento to BigQuery gives you the ability to integrate more advanced data visualization tools to aid in data analysis.

Methods of moving data from Magento to BigQuery

Method 1: Manually migrating data from Magneto to BigQuery using Magento API and Google Cloud Console

This method entails developing an automated script that interacts with the Magento API to retrieve records within Magento so that you can manually load them to Redshift. This approach will require you to make significant investments in terms of engineering skills, resources, and time.

Method 2: Migrating data from Magento to BigQuery using Hevo

Hevo can help you export data from Magneto to BigQuery in real-time free of cost, without having to write a single line of code. With the help of Hevo’s pre-built integrations with 100+ sources such as Magneto, you can set up the whole data pipeline without any technical intervention and load data from Magneto to BigQuery or a destination of your choice with ease.

Get Started with Hevo for Free

Methods to Move Data from Magneto to BigQuery

Method 1: Manually migrating data from Magneto to BigQuery using Magento API and Google Cloud Console

Magento’s Web API uses both REST (REpresentational State Transfer) and SOAP (Simple Object Access Protocol) protocols. In this article, we’ll be using the REST framework.

To get more familiar with Magento’s API endpoints, you can run tests on them using GUI tools such as Postman or command-line tools like CURL. This will allow you to become familiar with the JSON objects that the endpoints return when you interact with them. 

Prerequisites

You should have the following in place before starting to load data from Magento to BigQuery:

  1. An active Magento account
  2. A set up BigQuery cloud warehouse
  3. Basic understanding of REST APIs

You can then move data from Magento to BigQuery in the following three steps:

1. Authorizing access to Magento API

Magento’s API uses several authentication methods:

  • OAuth authentication
  • Mobile token authorization
  • User login credentials

You have to make sure that when you are writing your script, you should choose and include one of these authorization processes or the script will not work. If you require guidance on the authorization process, then you can take a look at the section on Authentication in the Magento DevDocs.

2. Extracting data from Magento

The following steps detail how you can extract order data from Magento API using GET command.

The GET command pulls data from particular Magento endpoints. We’ll be looking at calls to the /V1/orders endpoint in this article.

GET <host>/rest/<store_code>/V1/orders/

It is also possible to specify that you would like only top-level objects to be returned. The following call will return the requested objects and their fields:

GET 
<host>/rest/<store_code>/V1/orders/2?fields=billing_address,customer_firstname,customer_lastname

This call will return the following (example) JSON object:

{
"customer_firstname": "Vernon"
"customer_lastname": "DaCosta"
"billing_address": {
  "address_type": "billing"
  "city": "Los Angeles"
  "country_id": "US"
  "customer_address_id": 1
  "email": "vd@example.com"
  "entity_id": 4
  "firstname": "Vernon"
  "lastname": "DaCosta"
  "parent_id": 2
  "postcode": "49628-7978"
  "region": "California"
  "region_code": "CA"
  "region_id": 33
  "street": "6146 Honey Bluff Parkway"
  "telephone": "(555) 229-3326"
  }
}

The script should store the returned data on a local machine. Once this is complete, you can begin loading the data to BigQuery.

3. Loading the data to BigQuery using Google Cloud Console

The following steps will take you through the process of loading the extracted data from Magento to BigQuery using Google Cloud Console.

  1. Go to the Google Cloud Console.
  2. Open the BigQuery web UI.
  3. In the Resources section, open your project and select a dataset.
  4. In the Details panel, click Create table.
  5. On the Create table page, under Source:
    • Under Create table from, click Upload.
    • Under Select file, click Browse.
    • Browse to the file, and click Open.
      Note: Wildcards and comma-separated lists are not supported.
    • Under File format, select JSON (newline delimited).
  6. On the Create table page, under Destination:
    • For Dataset name, choose a dataset.
    • In the Table name field, enter a name for your table.
      Note: Make sure that Table type is set to Native table.
  7. Under Schema, enter the schema definition.
    • Since you’re uploading JSON files, you can check the Auto-detect option.

Your data is now successfully migrated from Magento to BigQuery gives you the ability to and you may now run queries on this data.

Method 2: Migrating data from Magento to BigQuery using Hevo

Magento to BigQuery - Magento to BigQuery with Hevo
Image Source: Self

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 provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. It allows you to focus on key business needs and perform insightful analysis using various BI tools such as Power BI, Tableau, etc. 

Check Out What Makes Hevo Amazing:

  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  • 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.
  • Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • 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.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
  • Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!

Yes! It is that simple. Now, sit back and watch your data move from Magneto to BigQuery.

Conclusion

In this blog, you have learned how to connect the Magneto to BigQuery manually. You also came across the various limitations of connecting Magneto to BigQuery manually. So, if you are looking for a fully-automated data pipeline, then try Hevo.

Visit our Website to Explore Hevo

Hevo is a No-code Data Pipeline and has awesome 100+ pre-built integrations that you can choose from. Hevo can help you integrate your data from numerous sources and load them into a destination to analyze real-time data with a BI tool such as Tableau. It will make your life easier and data migration hassle-free. It is user-friendly, reliable, and secure. Watch this short overview video to get a sense of how Hevo works:

Check out the pricing details here. Want to take Hevo for a spin? Sign Up for a 14-day free trial and see the difference! Experience the power of Hevo first hand. 

What is your preferred approach to transfer data from Magento to BigQuery? Let us know in the comments below.

No-code Data Pipeline for BigQuery