How to Load Data From Magento to Google BigQuery

Teams often struggle when Magento data sits in one place while marketing, ads, or offline sales data sits elsewhere. You cannot get a complete view of performance until everything comes together in a single data warehouse or database.

Migrating data from Magento to BigQuery solves this with a centralized environment to run deeper analysis and improve decision-making.

This guide walks you through two practical ways to move your Magento data into BigQuery so you can use it to refine your e-commerce strategy with more confidence.

Prerequisites to Integrate Magento to BigQuery

Before you move data from Magento to BigQuery, you need a few setup steps in place. The requirements are slightly different depending on whether you use a no-code tool like Hevo or the manual API method.

When using Hevo Data

You’ll need access to both your Magento database and your BigQuery project.

Magento prerequisites:

1. Database credentials for the MySQL database connected to your Magento store.
2. Hevo’s IP addresses are whitelisted.
3. SELECT privileges granted to the database user.
4. A Hevo role assigned as a Team Administrator, Team Collaborator, or Pipeline Administrator.

BigQuery prerequisites:

1. An active Google Cloud project.
2. Required roles for the Google account connecting to BigQuery.
3. An active billing account linked to the project.
4. Permission in Hevo to add a Destination. Any admin or collaborator role is acceptable except Billing Admin.

When using the API + UI import method

1. An active Magento account with API access.
2. A configured BigQuery cloud warehouse.
3. Basic understanding of REST APIs.

Method	Best Use Case
No-code load with Hevo Data	Automated scheduled data syncing for non-technical users, with automatic schema mapping.
Manual API + UI import	One-time migrations for users with technical expertise who need full control over the extraction process without relying on a third-party tool.

Understanding Magento to BigQuery Integration

What is Magento?

Magento is one of the most popular open-source e-commerce solutions available today. Originally built on the Zend framework, it now uses components of the Laminas project. Magento’s source code is available under OSL (Open Software License) v3.0. 

What makes Magento valuable is the amount of operational and customer data it generates. From transactions and inventory updates to user behavior and marketing interactions, it offers powerful features for product catalogs, order management, and storefront customization. This data becomes crucial for analytics and business decisions.

For more information on Magento’s costs and editions, check out our Magento pricing guide.

What is BigQuery?

Google BigQuery is a fully managed and serverless data warehouse built for high-speed analytics. It can handle Petabyte-scale datasets 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 large amounts of data. BigQuery’s APIs enable you to build custom integrations with other platforms and services, including powerful data visualization tools that aid in business intelligence.

Integrating Magento with BigQuery allows you to bring all your store, customer, and sales data into a central analytics platform.

What Are the Ways to Migrate Data from Magento to BigQuery?

You can migrate your data using two methods:

Method 1: Migrating data from Magento to BigQuery using Hevo

Hevo Data automates data ingestion from Magento to BigQuery without writing a single line of code. With 150+ fully managed integrations, it handles the heavy lifting so you can focus on better insights.

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

In this method, you write API scripts to move data from Magento to BigQuery. Even though effective, this method requires some technical expertise. API rate limits can also be a problem.

Let’s now explore both methods in detail.

Method 1: Migrating data from Magento to BigQuery using Hevo

You can connect Magento to Hevo through MySQL. Before configuring the source, ensure the following:

1. Whitelist Hevo’s IPs based on your MySQL hosting environment, such as RDS, Aurora, Azure, GCP, MariaDB, or Generic MySQL.
2. Grant SELECT privileges to your database user.

GRANT SELECT ON *.* TO 'username';

Step 1: Configure your Source

1. Go to the Navigation Bar and click PIPELINES.
2. Click + Create Pipeline.
3. Select Magento via MySQL as the Source.
4. Select your Destination to proceed to the Configure your Magento via MySQL Source page.

5. Enter connection details:

• Pipeline name under 255 characters
• Database host’s IP or DNS without http or https
• Port with default value of 3306
• Database user and password
• Database name

6. Connection options:

• SSH Tunnel (Optional): Secure access without exposing your database.
• SSL (Optional): Upload CA file, client certificate, and key if required.

7. Click Test & Continue.

Hevo auto-detects the schema and data model based on your Magento MySQL tables.

Learn more about configuring Magento here.

Step 2: Select your Destination

Once your Magento source is configured, set up BigQuery as your Destination. Make sure the essential roles are already assigned to the Google account you’ll be using.

1. Go to Destinations in the navigation Bar.
2. Click + Create Standard Destination.
3. Select Google BigQuery as the Destination type, and the Google BigQuery Destination page will pop up.

4. Add the following details:

• Destination name under 255 characters.
• Choose OAuth or Service Account depending on your setup.
• Project ID of your BigQuery instance.

5. Automatically create a dataset, select an existing dataset, or click on + New Dataset.

6. Automatically create a bucket, select an existing bucket, or click + New GCS Bucket. Ensure the bucket has no retention policy.

7. Advanced settings (Optional):

• Populate Loaded Timestamp
• Sanitize Table/Column Names. 

Note: This setting cannot be changed later.

8. Click Test Connection.

9. Click Save & Continue.

Read more on configuring BigQuery here.

That’s it! It is that simple. No code, no hassle. You can now sit back and watch your data move from Magento to BigQuery.

Here’s what makes Hevo stand out:

• Security: Hevo supports a fault-tolerant architecture that ensures data is handled in a secure and consistent manner with zero data loss.
• Schema management: Hevo removes the tedious task of schema management and automatically detects the schema of incoming data and maps it to the destination schema.
• Incremental data load: Hevo allows you to transfer only the data that has been modified to ensure efficiency at both ends.
• Auto-scaling: Hevo grows with your data requirements, and it can process small to enterprise-grade datasets with the same efficiency without slowing your pipelines.

Want to set up a pipeline now? Perform effortless Magento to BigQuery migration with Hevo.

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

This method involves writing a script that calls the Magento REST API to extract data, store it locally, and then manually upload the extracted JSON files into BigQuery.

Magento’s Web API uses both REST (Representational State Transfer) and SOAP (Simple Object Access Protocol) protocols. The REST API is recommended for ETL workflows and is what we’ll use in this guide.

To preview or test Magento API responses, you can run tests on them using GUI tools, such as Postman, or command-line tools like cURL. This makes you familiar with the JSON objects that the endpoints return when you interact with them. 

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

1. Authorizing access to the Magento API
2. Extracting data from Magento
3. Loading the data to BigQuery using Google Cloud Console

Step 1: Authorizing access to the Magento API

Magento’s API uses several authentication methods:

1. OAuth authentication
2. Token authorization for mobile applications
3. User login credentials

You have to make sure that when you are writing your script, you choose and include one of these authorization processes, or the script will not work.

For example, you can authenticate using an Admin Token, which requires Two-Factor Authentication (2FA). You’ll need to authenticate with your 2FA provider first.

For example, use the following call with Google Authenticator:

POST <host>/rest/<store_code>/V1/tfa/provider/google/authenticate

You’ll need to include both your admin credentials and the one-time password (OTP) from your authenticator app in the request. Once authenticated, you’ll receive a token to use for subsequent API calls. Note that admin tokens are valid for four hours by default.

If you require further guidance on the authorization process, explore the Authentication section in this documentation.

Step 2: Extracting data from Magento

Magento’s REST API can send and receive data in both JSON and XML formats. That said, JSON is the default response format.

You can extract order data records from the Magento API using the REST GET command. The GET command pulls data from particular Magento endpoints.

In this example, we’ll be looking at calls to the /V1/orders endpoint.

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

You can specify the top-level objects you would like to be returned with the following fields parameter:

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 load the data into Google BigQuery.

Step 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.

Note: Make sure that the Table type is set to Native table.

8. Under Schema, enter the schema definition.

• Since you’re uploading JSON files, you can check the Auto-detect option. This setting attempts to infer the correct Google BigQuery data types, like STRING, INTEGER, or TIMESTAMP.

9. Click Create table

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

Don’t want to go through the hassle of extracting, scripting, and uploading separately? Get started in minutes with Hevo’s 14-day trial!

Why move data from Magento to BigQuery?

Moving your Magento data into BigQuery gives you up-to-date and scalable analytics compared to Magento’s built-in reporting tools. BigQuery can handle complex queries across millions of orders, customers, and products with higher performance, something operational databases aren’t designed for.

Centralizing Magento data in BigQuery also lets you combine it with other business systems, such as CRM tools, logistics platforms, and even your marketing data when you integrate Google and Facebook Ads.

Here are some of the key uses of Magento and BigQuery integration:

• Sales and revenue analysis: Track revenue trends across products, categories, regions, or marketing campaigns.
• Customer behavior and personalization: Use browsing and purchase history to build segmentation and recommendations.
• Inventory and supply chain optimization: Forecast demand and monitor stock levels to reduce backorders.
• Customer Lifetime Value (CLV) and retention: Predict CLV, identify churn risk, and optimize retention journeys.

Once in BigQuery, you can plug in BI dashboards, AI models, or automated reporting for efficient strategies and decision-making.

Read more about Magento integration with these essentials:

• Magento Export: Easy Steps to Export your Data
• Magento SAP Integration: 3 Easy Steps
• Connect Magento to PostgreSQL: Simplified 101
• Load Data from Magento to MySQL

The Easiest Way to Move Magento Data to BigQuery

We’ve explored two distinct paths to load data from Magento to BigQuery. The manual API scripts offer you flexibility and control. However, this method demands significant engineering time, monitoring, and constant maintenance.

A fully managed platform like Hevo simplifies the entire journey by removing the technical burden. Its no-code pipeline, automatic schema handling, and built-in transformations with SQL support make data management easier for you. It provides more than 150 connectors, including SaaS apps, files, and databases, to connect all your tools.

Plus, it is compliant with SOC 2 Type II, GDPR, HIPAA, DORA, and CPRA, which keeps your data secure as it moves through pipelines.

This convenience is what makes Hevo the more efficient choice.

Want to try it today? Book a free demo to see Hevo in action.

FAQs on Magento to BigQuery