How to Import Data from BigQuery to Magento 2

In the following article, we explore the Magento 2 BigQuery integration. If your partner provides updates via this remote database, you will find out two ways of importing the information back to your e-commerce store. Firstly, we shed light on a universal approach which is more complicated and slow. Secondly, you will discover how to replace its steps with a user-friendly process which can be fully automized. To make the long story short, let’s see how to move BigQuery tables into Magento 2 using the common approach.

The Common Way of Moving BigQuery Tables into Magento 2

If you want to move a remote table from BigQuery to Magento 2 by yourself, you need to perform the following steps. First of all, create a CSV output that contains the update. Next, you should take the file, review its content, and edit attributes and even their values. Finally, the data can be added to your database. Let’s take a look at each step individually.

CSV Export from BigQuery Database

You will face lots of limitations while exporting data from BigQuery. And the first one is related to Magento 2 itself: you need to create a CSV output since it is the only supported format. And take a look at the following aspects of the database that should always be considered:

• Exporting data to a local file or Google Drive is impossible. Instead, you should save query results to a local file. Google BigQuery supports the only supported export location – Google Cloud Storage.
• The size of your export file is limited to 1 GB only. If you are exporting more than 1 GB of data, it is necessary to use a wildcard to export the data into multiple files.
• When it comes to CSV export, it is impossible to export nested and repeated data. These data types are supported in Avro and JSON exports only.
• It is impossible to export data from multiple tables in a single export job.
• You cannot export individual partitions while exporting data from partitioned tables.
• The classic BigQuery web UI provides only the GZIP compression.

And it is the first step of the data import from BigQuery to Magento 2. If you want to deal with the limitations above, follow this link to find out the whole algorithm of data export: Exporting Data Table from BigQuery. Although there is a better way to chive the same goal, we still want to lead you through the common form of importing information from a remote database to your e-commerce website. Let’s take a look at what to do with the attributes and their values.

Map Third-Party Attributes to Magento 2 Attributes

Right after you’ve generated a CSV file, it is necessary to review all its details. Chances are, your partner offers data from a system that uses absolutely different attributes than Magento 2. Thus, editing them is a mandatory step. And don’t forget about attribute values. While working with a CSV file, you have a good chance to change them in bulk instead of modifying each one individually for every product.

Import BigQuery Table into Magento 2

Finally, you can start the import process. Use the standard Magento 2 import tools to transfer data from a CSV file to Magento 2. Unfortunately, no automation is possible, so it is necessary to repeat all the three steps every time a new update is provided.

If you still want to do everything manually, check the following Magento 2 import guide: The Complete Guide to Magento 2 Product Import / Export. It sheds light on various data import details including proper attributes and other nuances of the successful data migration.

Pros & Cons

Let’s see what pros and cons the traditional approach to data import from BigQuery to Magento 2 offers.

Pros

1. Magento 2 BigQuery data import.

As you might have already guessed, the only positive aspect of the traditional approach is the completion of our final goal. You can finally move data stored in BigQuery to your Magento 2 website. The traditional approach lets you establish the connection between two systems, but what is the price? Unfortunately, the default procedure is far from being efficient, intuitive, and user-friendly. There is still a room for improvement especially in the areas related to the following drawbacks:

Cons

1. Three different environments to work with;
2. The very limited export functionality of BigQuery;
3. Time-consuming attributes and their values editing;
4. Slow and limited import;
5. Inability to automate further data transfers.

These reasons were enough for us to look for a better way of importing data from BigQuery to Magento 2. And there is a solution that dramatically enhances the process.

The Easy Way of Moving BigQuery Bases into Magento 2

And this solution is called the Improved Import & Export extension – you number one time-saver when it comes to data transfers. Our tool simplifies every integration of your e-commerce store with external systems. The plugin is designed to reduce the complexity of the approach described above automating its steps. Below, we explain how the solution works saving your time and effort.

BigQuery Database Connection

The first key improvement related to the Magento 2 BigQuery Integration is the ability to connect your ecommerce store to the database directly. As a result, there is no need to export CSV files and edit them. The Improved Import and Export module provides the ability to eliminate the first two steps of the traditional approach to data transfers by replacing them with more efficient solutions.

Instead of transferring files, our extension creates a data flow between the two systems. Instead of datafile editing, it provides the ability to match external attributes to the corresponding internal ones within just a few clicks. To simplify this process as much as possible, the Improved Import & Export Magento 2 extension offers mapping presets. At the same time, you can do everything manually as well, but the process takes place right in your Magento admin and is dramatically simplified in comparison to the default manual procedure that takes place in case of data files.

Besides, the Improved Import & Export Magento 2 extension offers the ability to match attribute values. You can save tons of time and effort by leveraging this feature which is described below along with multiple other opportunities represented by the module, such as values editing of category mapping.

How to import data from BigQuery to Magento 2

The data import process from BigQuery to Magento 2 with the help of Improved Import & Export is very straightforward and intuitive. It consists of the following steps:

1. In your Magento admin, open the Improved Import & Export extension and create a new import job in its import section.
2. Specify the parameters required for the connection to the BigQuery database, map attributes, create a schedule, and configure other settings if needed;
3. Save and launch the profile.

As you can see, our module dramatically decreases the complexity of the standard way of connecting your Magento 2 store to a remote BigQuery database. As for more good news, we should also say that you prevent further time expenditures since further updates can be entirely automated.

Pros & Cons

The pros and cons related to the Improved Import & Export module and the Magento 2 BigQuery integration are the following:

Pros

1. The most user-friendly connection of your Magento 2 website to the BigQuery database on the market;
2. The direct data transfer between the two systems without any headaches;
3. Eliminated file export/import processes;
4. No file editing;
5. Fast attributes matching with the help of presets or user-friendly tools;
6. Attribute values matching and editing;
7. Fully automated data transfers between the two systems.

Cons

Take a look at the traditional approach and compare the improved one with it. Have you noticed any drawbacks? There is one – the necessity to install a third-party module. But it is the payment for automating your daily routine: if you don’t want to do that than someone should get the responsibility, and it is our module!

But with our friendly support, both the installation and configuration are more a positive experience than a negative aspect. Contact us, and the Firebear team will help you solve any difficulties related to them. And don’t forget that we can implement your custom ideas related to the module’s functionality!

The features of the Improved Import & Export Magento 2 extension are described below, but let’s focus on BigQuery’s characteristics before going any further.

Get Improved Import & Export Magento 2 Extension

BigQuery Facts

Google BigQuery is often compared with Amazon Redshift, Google Cloud Datastore, and Google Cloud Bigtable. Being a relational DBMS, it is also used as a key-value store. Other features of the database include:

• The project is held by Google and exists since 2010;
• It operates under a commercial license;
• BigQuery offers cloud-based features;
• Its server operating system is hosted;
• Data scheme and typing are provided;
• There is no XML support and secondary indexes;
• SQL is supported;
• APIs and other access methods are represented by RESTful HTTP/JSON API;
• Supported programming languages include .Net, Java, JavaScript, Objective-C, PHP, Python, and Ruby;
• Server-side scripts are represented by user-defined functions;
• Triggers, partitioning methods, and MapReduce are not available;
• Consistency concepts are represented by Immediate Consistency;
• No foreign keys transaction concepts are available;
• Concurrency and durability features are represented;
• No in-memory capabilities are offered;
• Access privileges (owner, writer, reader) are available for whole datasets, not for individual tables.

Improved Import & Export Magento 2 Extension

Now, let’s take a look at the major features of the Improved Import & Export extension and the way it simplifies the Magento 2 BigQuery integration. First of all, we’d like to tell you about the opportunities related to the data transfers automation.

Automated Import & Export Processes

Our module allows automating data transfers in two different ways. Firstly, you can create an update schedule. Secondly, it is possible to specify events that will be used as triggers.

Schedules

The Improved Import & Export Magento 2 module works with cron allowing you to create any custom schedule of updates. It is only necessary to apply the default cron syntax. In case you don’t want to do that, choose one of the predefined intervals. Note that they are also fully customizable.

Alternatively, the Improved Import & Export extension enables profiles that have no schedules. They are especially useful for one-time data migrations and asynchronous updates.

It is also worth mentioning that every scheduled profile can be launched manually whenever you want. Thus, if an update is provided with a delay, you can import it into Magento 2 without waiting for the next planned event.

The following gif image illustrates how to configure the appropriate section of the Improved Import & Export module:

Events

As for the second way of the automation, our module offers a system of event-based imports/exports. Improved Import & Export supports both system events and custom processes. You can not only select a trigger but also create conditions to increase its efficiency. For further information, read this article: How to Run Magento 2 Import or Export After Specific System Event or Process.

Advanced Mapping Features

Now, let’s see how the Improved Import & Export Magento 2 extension enhances the work with attributes. Below, we shed light on the core mapping features of our module. Let’s start with the easiest way of attributes matching.

Mapping Presets

The problem of different attribute standards is no longer a headache if you work with Improved Import & Export. Due to our module, it can be solved in a few clicks only since it is only necessary to select and apply a preset suitable for your integration. Our plugin will do all the work for you: it analyzes the input data and then matches all third-party attributes to the corresponding ones used in your database.

The feature is illustrated by the following gif image which displays the data migration from Magento 1 to 2.x:

Matching Interface

There is also a corresponding interface where you can do mapping manually. In case a preset for your particular integration is not available, or you want to control all the steps, select a third-party designation and specify the corresponding internal one in front of it. That’s all! The following image illustrates the process:

Remember that you can also determine hardcoded attribute values. The third column from the example above provides the ability to set it so that all items related to the attribute will get the specified designation.

Attribute Values Mapping

Attribute values mapping is another feature that substantially simplifies your daily routine. With the Improved Import & Export Magento 2 extension, the process becomes fast, intuitive, and user-friendly. Being based on the same logic as the attributes matching described above, it provides the ability to select a third-party designation and specify the internal one next to it. For further information, check this article: Attribute Values Mapping.

Attribute Values Editing

Editing attribute values in bulk is another important time-saver provided by our extension. And there are several rules that improve your daily work. Firstly, the Improved Import & Export Magento 2 module lets you add a prefix to multiple attribute values. Of course, you can edit them by specifying a suffix. Feel free to combine these two rules and apply them simultaneously.

If different attribute values are combined, you can split them within a few clicks. The opposite action is also available. Thus if the data file contains information related to one value which is divided into multiple sections, it is no longer a problem. The following article sheds light on various details of attribute values editing: How to Modify Attribute Values During Import and Export in Magento 2.

Category Mapping

Another mapping feature of the Improved Import & Export Magento 2 extension is related to categories. When you move product data from external systems, you usually have to deal with a catalog structure which is not similar to yours. As a result, it is necessary to spend hours of time editing the imported product data for each item individually.

Luckily, there is an alternative solution. With our module, you can match external categories to the ones used internally just like product attributes. Everything happens during the import process. The following gif image illustrates the feature:

If necessary, you can create new categories on the fly. The process is simplified to a set of very straightforward actions: select a parent category and specify a name of a new one. For further information, follow this link: Category Mapping.

Attributes On The Fly

If your data file lacks attributes, you can create them on the fly. The Improved Import & Export Magento 2 extension provides the ability to specify parameters necessary to do that automatically. Use the following general form:

Attribute|attribute_property_name:attribute_property_value|…

The full process of attributes on the fly creation is described here: Product attributes import.

Extended Connectivity Options

Although the Magento 2 BigQuery integration is based on the direct connection, we would also like to draw your attention to the extended connectivity options of the Improved Import & Export Magento 2 extension.

Multiple File Standards

Our plugin substantially increases the number of supported file formats in comparison to the default Magento platform. Above, we’ve already mentioned that you should generate a CSV file from the BigQuery table due to the native limitations of your e-commerce store. When it comes to the Improved Import & Export Magento 2 extension, our tool adds support for XML, JSON, ODS, and Excel. You can freely import and export data in these formats applying all the extension’s features.

Multiple File Sources

Since we are already talking about file transfers, it is also necessary to say a few words about support for numerous sources. Firstly, you can exchange data between Magento 2 and BigQuery using the FTP/SFTP connection which allows utilizing a local or remote server as a point of transfers. The same functionality is possible with a Dropbox account.

When it comes to moving data to your store, our plugin enables you to transfer files via a direct URL. It is also worth mentioning that Improved Import & Export unpacks file archives automatically. So if you’ve created a compressed output, you no longer have to unzip it manually.

Alternative Ways of Import & Export

As for alternative ways of data transfers, our module offers several more options in this area. Firstly, it enables you to rely on various intermediaries to create a bridge between your Magento 2 website and external platforms. The extension supports Google Sheets, Office 365 Excel, and Zoho Sheet. The Google Sheets integration is illustrated below:

Secondly, Improved Import & Export lets you establish a direct connection between your Magento 2 website and any external systems on the basis of API. Use REST, SOAP, and GraphQL integrations to synchronize the data flow.

Watch the following video to see the extension in action:

Final Words

You must admit that the Improved Import & Export Magento 2 extension dramatically improves the way you move data from BigQuery to Magento 2. The three steps are reduced to one; instead of multiple environments, you work in your Magento admin; there is no need to edit data files manually; All further updates are automated, etc. If you still have any questions about the Improved Import & Export extension and the Magento 2 BigQuery integration, follow the link below and contact our support:

‘Get Improved Import & Export Magento 2 Extension