How to load data from the Magento to Google BigQuery

Extract your data from Magento

Magento exposes its platform through both a REST and a SOAP interface. Both can be used to pull data from it, which is also the scope of this article and interact with the platform.

By using these interfaces, developers create rich applications and plugins for Magento. In this post, we will use the REST version of the Magento platform.

As a Web API following the RESTful architecture principles, it can be accessed through HTTP. As a RESTful API, interacting with it can be achieved using tools like CURL or Postman or by using HTTP clients for your favorite language or framework. A few suggestions:

Magento does not publish official SDKs but by using the SOAP interface it is possible to automatically generate clients that can act as SDKs for your favorite language or platform. For example, in Java, you can create a client in Eclipse by providing the WSDL file that Magento exposes after you set up the platform.

Magento API Authentication

Magento is a self-hosted platform unless you are using the Enterprise cloud edition, so you have much more control over its access than other solutions, but if you want to access its data through the REST API that it has, then you will have to use OAuth for authentication which Magento supports.

Magento rate limiting

As you would like to avoid stressing your e-commerce platform facing your customers, you should make sure that your pipeline process does not overstress your Magento installation. As a platform hosted on your premises, it doesn’t impose any rate-limiting. But this is completely at your discretion.

Endpoints and available resources

Magento exposes the following resources:

  • Products. Retrieve the list of products, create, update, delete a product.
  • Product categories. Retrieve the list of categories assigned to a product, assign and unassign the category from a product.
  • Product images. Retrieve the list of websites assigned to a product, assign, unassign a website to/from a product.
  • Customers. Retrieve the list of customers, create, delete a customer, and update the customer information.
  • Customer Addresses. Retrieve the list of customer addresses, create, update, and delete the customer address.
  • Inventory. Retrieve the list of stock items, update required stock items.
  • Sales Orders. Retrieve the list of sales orders with detailed information on order addresses, items, and comments.
  • Order Addresses. Retrieve information on the specified order comments.
  • Order Items. Retrieve information on specified order items.

The API is possible to return either JSON or XML responses. This is something that you can control by providing the appropriate Accept headercontent type.

We can request all the above resources from the Magento platform to pull out a list of results with all the associated data, so ideally, we would like to pull all the data and make sure that we keep them up to date on our analytics platform of choice for further analysis. For this post, we will just see how we can pull data for one resource, the Sales Orders. The process is the same for all other resources.

HTML
https://magentohost/api/rest/orders

As a platform hosted by you, you need to replace the “magentohost” part of the URL with the actual URL of the host that has Magento running. The rest of the URL is the same as the above. The default response is in XML and looks like the following:

JAVASCRIPT
<?xml version="1.0"?>
<magento_api>
 <data_item_1>
   <customer_id>3</customer_id>
   <base_discount_amount>0.0000</base_discount_amount>
   <base_shipping_amount>455.0000</base_shipping_amount>
   <base_shipping_tax_amount>0.0000</base_shipping_tax_amount>
   <base_subtotal>13650.0000</base_subtotal>
   <base_tax_amount>0.0000</base_tax_amount>
   <base_total_paid></base_total_paid>
   <base_total_refunded></base_total_refunded>
   <tax_amount>0.0000</tax_amount>
   <total_paid></total_paid>
   <total_refunded></total_refunded>
   <base_shipping_discount_amount>0.0000</base_shipping_discount_amount>
   <base_subtotal_incl_tax>13650.0000</base_subtotal_incl_tax>
   <base_total_due>14105.0000</base_total_due>
   <total_due>14105.0000</total_due>
   <base_currency_code>USD</base_currency_code>
   <tax_name></tax_name>
   <tax_rate></tax_rate>
   <addresses>
     <data_item>
       <region>Palau</region>
       <postcode>19103</postcode>
       <lastname>Doe</lastname>
       <street>2356 Jody Road Philadelphia, PA 19103</street>
       <city>PA</city>
       <telephone>610-634-1181</telephone>
       <country_id>US</country_id>
       <firstname>John</firstname>
       <address_type>billing</address_type>
       <prefix></prefix>
       <middlename></middlename>
       <suffix></suffix>
       <company></company>
     </data_item>
     <data_item>
       <region>Palau</region>
       <postcode>19103</postcode>
       <lastname>Doe</lastname>
       <street>2356 Jody Road Philadelphia, PA 19103</street>
       <city>PA</city>
       <telephone>610-634-1181</telephone>
       <country_id>US</country_id>
       <firstname>John</firstname>
       <address_type>shipping</address_type>
       <prefix></prefix>
       <middlename></middlename>
       <suffix></suffix>
       <company></company>
     </data_item>
   </addresses>
   <order_items>
     <data_item>
       <sku>Sunglasses_1</sku>
       <price>150.0000</price>
       <base_price>150.0000</base_price>
       <base_original_price>150.0000</base_original_price>
       <tax_percent>0.0000</tax_percent>
       <tax_amount>0.0000</tax_amount>
       <base_tax_amount>0.0000</base_tax_amount>
       <base_discount_amount>0.0000</base_discount_amount>
       <base_row_total>13650.0000</base_row_total>
       <base_price_incl_tax>150.0000</base_price_incl_tax>
       <base_row_total_incl_tax>13650.0000</base_row_total_incl_tax>
     </data_item>
   </order_items>
 </data_item_1>
 <data_item_2>
   <customer_id>3</customer_id>
   <base_discount_amount>0.0000</base_discount_amount>
   <base_shipping_amount>95.0000</base_shipping_amount>
   <base_shipping_tax_amount>0.0000</base_shipping_tax_amount>
   <base_subtotal>3350.0000</base_subtotal>
   <base_tax_amount>0.0000</base_tax_amount>
   <base_total_paid>2445.0000</base_total_paid>
   <base_total_refunded>1845.0000</base_total_refunded>
   <tax_amount>0.0000</tax_amount>
   <total_paid>2445.0000</total_paid>
   <total_refunded>1845.0000</total_refunded>
   <base_shipping_discount_amount>0.0000</base_shipping_discount_amount>
   <base_subtotal_incl_tax>3350.0000</base_subtotal_incl_tax>
   <base_total_due>1000.0000</base_total_due>
   <total_due>1000.0000</total_due>
   <base_currency_code>USD</base_currency_code>
   <tax_name></tax_name>
   <tax_rate></tax_rate>
   <addresses>
     <data_item>
       <region>Palau</region>
       <postcode>19103</postcode>
       <lastname>Doe</lastname>
       <street>2356 Jody Road Philadelphia, PA 19103</street>
       <city>PA</city>
       <telephone>610-634-1181</telephone>
       <country_id>US</country_id>
       <firstname>John</firstname>
       <address_type>billing</address_type>
       <prefix></prefix>
       <middlename></middlename>
       <suffix></suffix>
       <company></company>
     </data_item>
     <data_item>
       <region>Palau</region>
       <postcode>19103</postcode>
       <lastname>Doe</lastname>
       <street>2356 Jody Road Philadelphia, PA 19103</street>
       <city>PA</city>
       <telephone>610-634-1181</telephone>
       <country_id>US</country_id>
       <firstname>John</firstname>
       <address_type>shipping</address_type>
       <prefix></prefix>
       <middlename></middlename>
       <suffix></suffix>
       <company></company>
     </data_item>
   </addresses>
   <order_items>
     <data_item>
       <sku>Sunglasses_1</sku>
       <price>150.0000</price>
       <base_price>150.0000</base_price>
       <base_original_price>150.0000</base_original_price>
       <tax_percent>0.0000</tax_percent>
       <tax_amount>0.0000</tax_amount>
       <base_tax_amount>0.0000</base_tax_amount>
       <base_discount_amount>0.0000</base_discount_amount>
       <base_row_total>1350.0000</base_row_total>
       <base_price_incl_tax>150.0000</base_price_incl_tax>
       <base_row_total_incl_tax>1350.0000</base_row_total_incl_tax>
     </data_item>
     <data_item>
       <sku>Sun_glasses</sku>
       <price>200.0000</price>
       <base_price>200.0000</base_price>
       <base_original_price>200.0000</base_original_price>
       <tax_percent>0.0000</tax_percent>
       <tax_amount>0.0000</tax_amount>
       <base_tax_amount>0.0000</base_tax_amount>
       <base_discount_amount>0.0000</base_discount_amount>
       <base_row_total>2000.0000</base_row_total>
       <base_price_incl_tax>200.0000</base_price_incl_tax>
       <base_row_total_incl_tax>2000.0000</base_row_total_incl_tax>
     </data_item>
   </order_items>
 </data_item_2>
</magento_api>

As we can see, we get back a list of items, with each one representing an order that contains all the information that we would like to use for further analysis. Information like the discount that we might have applied, the taxes paid the base price of the order, etc. As we might have many order objects to retrieve, we should paginate through the results. To do that, we need to provide the “page” and “limit” parameters to our GET request.

Now that we have the results from our Magento shop, we can further process them before we are able to load them into the BI platform of our choice.

Prepare your data to be sent from Magento to Google BigQuery

Before you load your data into BigQuery, you should make sure that it is presented in a format supported by it. For example, if the API you pull data from returns XML, you must first transform it into a serialization BigQuery understands. Currently, two data formats are supported:

  • CSV, and
  • JSON

You also need to make sure that the data types you are using are the ones supported by BigQuery, which are the following:

  • STRING
  • INTEGER
  • FLOAT
  • BOOLEAN
  • RECORD
  • TIMESTAMP

For more information, please check the Preparing Data for BigQuery page on the documentation.

Load Data from Magento to Google BigQuery

If you want to load data from Magento to Google BigQuery, you have to use one of the following supported data sources.

  1. Google Cloud Storage
  2. Sent data directly to BigQuery with a POST request
  3. Google Cloud Datastore Backup
  4. Streaming insert
  5. App Engine log files
  6. Cloud Storage logs

From the above list of sources, 5 and 6 are not applicable in our case.

For Google Cloud Storage, you first have to load your data into it. There are a few options on how to do this. For example, you can use the console directly as described here, and do not forget to follow the best practices.

Another option is to post your data through the JSON API. As we see again, APIs play an important role in both the extraction and the loading of data into our data warehouse. In its simplest case, it’s just a matter of one HTTP POST request using a tool like CURL or Postman. It should look like the following example.

JAVASCRIPT
POST /upload/storage/v1/b/myBucket/o?uploadType=media&name=myObject
HTTP/1.1 Host: www.googleapis.com
Content-Type: application/text
Content-Length: number_of_bytes_in_file
Authorization: Bearer your_auth_token your Magento data

If everything went ok, you should get something like the following as a response from the server:

JAVASCRIPT
HTTP/1.1 200 Content-Type: application/json { "name": "myObject" }

Working with Curl or Postman is good only for testing. If you would like to automate loading your data into Google Bigquery, you should write some code to send your data to Cloud Storage.

In case you are developing on the Google App Engine, you can use the library that is available for the languages that are supported by it:

If you are using one of the above languages and not coding for the Google App Engine, you can access Cloud Storage from your environment. Interacting such a feature-rich product like Cloud Storage can become quite complicated depending on your use case. For more details on the different options that exist, you can check Cloud Storage documentation.

If you are looking for a less engaged and more neutral way of using Cloud Storage, you can consider a solution like RudderStack.

After loading your data into Google Cloud Storage, you have to create a Load Job for BigQuery to load the data into it. This Job should point to the source data in Cloud Storage that has to be imported. This happens by providing source URIs that point to the appropriate objects.

The previous method used a POST request to the Cloud Storage API to store the data and then load it into BigQuery. Another way to go is to direct HTTP POST requests to BigQuery with the data you would like to query.

This approach is similar to how we loaded the data to Cloud Storage through the JSON API, but it uses the appropriate end-points of BigQuery to load the data directly. The way to interact with it is quite similar, for more information can be found on the Google BigQuery API Reference and on the page that describes how to load data into BigQuery using POST. You can interact with it using the HTTP client library of the language or framework of your choice. A few options are:

The best way to load data from Magento to BigQuery

So far, we just scraped the surface of what you can do with BigQuery and how to load data into it. Things can get even more complicated if you want to integrate data coming from different sources.

Are you striving to achieve results right now?

Instead of writing, hosting, and maintaining a flexible data infrastructure, RudderStack can automatically handle everything for you.

RudderStack, with one click, integrates with sources or services, creates analytics-ready data, and syncs your Magento to BigQuery right away.

Sign Up For Free And Start Sending Data
Test out our event stream, ELT, and reverse-ETL pipelines. Use our HTTP source to send data in less than 5 minutes, or install one of our 12 SDKs in your website or app.
Don't want to go through the pain of direct integration? RudderStack's Magento integration makes it easy to send data from Magento to Google BigQuery.