How to import data from external Hive database into Magento 2

Today, we are going to shed light on one significant improvement to the default admin routine. The following article describes how to import data from an external Hive database into Magento 2. First, we concentrate on a traditional approach to the data migration from a remote database into Magento 2. Next, when all the pros and cons are revealed, provide a more user-friendly way of achieving the same goal. To make the long story short, let’s proceed to the universal approach to importing data from Hive into Magento 2.

The Common Way of Moving Hive Bases to Magento 2

You cannot connect Magento 2 to any external database directly with the help of default import/export tools, and Hive is not an exception. It means that you have to perform a series of actions to transfer data from a remote database to your e-commerce store. Firstly, it is necessary to create a CSV file with all the required information by running data export from Hive. Secondly, you need to edit the file to make it compatible with the Magento 2 requirements. Finally, the document can be imported into your database. Let’s explore each step in more detail.

CSV Export from Hive Database

As you might have already guessed, it is necessary to run a code to create a CSV file. If you are not a tech-savvy person, this approach may embarrass you, but don’t panic – we’ve prepared something that dramatically improved the procedure and eliminates coding from it. But let’s explore the traditional approach to the Magento 2 data import from Hive first. Use the following example to create your script to export a CSV file with the Hive table:

But you may argue that this snippet doesn’t produce an output with headers so that you should add them manually. To solve this problem, use the following hive conf property set to true before executing the query:

Thus, you will export a CSV file that contains headers and can be used for the further update, but don’t be so fast – it is still necessary to edit it before running the Magento 2 Hive import.

Map Third-Party Attributes to Magento 2 Attributes

Chances are, your remote database contains some third-party attributes that are not suitable for the Magento 2 import. It means that you should edit them manually. The more attributes the data file include, the more time you will spend matching them to their corresponding Magento 2 designations.

If you are not familiar with the problem, here is a brief explanation. Different systems store and organize information in multiple ways. Consequently, differences in its representation occur. The aftermath of the diversity is the inability to move data between two systems directly. You always should edit attributes before the import.

Thus, open your CSV file with the Hive output and find all attributes unsuitable for the Magento 2 import. Edit them before transferring the update into your system. Read this article for further information: The Complete Guide to Magento 2 Product Import / Export. Here, you will discover what attributes to use and how to adopt an external output to the needs of Magento 2.

Import Hive Table into Magento 2

The aforementioned article describes the import process as well. What are its drawbacks? You cannot automate this routine. It is necessary to repeat the whole algorithm to update your Magento 2 database. But there is a solution that dramatically improves the situation. Before describing it, we’d like to summarize all the pros and cons.

Pros & Cons

As you can see, the traditional approach to importing data from an external Hive database to Magento 2 has lots of difficulties. But what are the positive aspects?

Pros

1. You can import data from Hive into Magento 2.

Cons

1. You need coding experience to export a CSV file from Hive.
2. You should edit a file if field names are not added.
3. You should map attributes manually.
4. You should repeat the whole procedure every time an update is required.

This approach is far from being a user-friendly and intuitive one. You need to work within different interfaces and consider various nuances. What do we offer instead?

The Easy Way of Moving Hive Bases into Magento 2

As you might have already guessed, it is necessary to install a third party tool to move Hive bases into Magento 2 in a user-friendly way. Among dozens of import/export modules only Improved Import & Export for Magento 2 offers the necessary tools. Let’s take a look at how the default data transfer from Hive to Magento 2 looks when you use the extension.

Hive Database Connection

The first vital improvement to the default import process is the ability to eliminate CSV files from the transfer. It means that you don’t need to implement the first step of the algorithm described above. Furthermore, the direct connection to Hive lets you avoid data file editing. As a result, you no longer need to work with commands or look for unsuitable attributes in a data file.

Magento 2 Data Import from Hive

Yes, the Improved Import & Export extension allows importing data from a remote Hive base into Magento 2 directly. But what about different attribute standards? You can either apply a mapping presets or match them in your Magento admin! We will describe both opportunities lately.

Another vital improvement provided by our extension is the ability to automate the data import process. You can either create a schedule of updates or choose events that will trigger the procedure. These features are described in the corresponding section of the article below.

How to Import Data From Hive into Magento 2

Let’s see how to import data from Hive into Magento 2 with the help of the Improved Import & Export Magento 2 extension.

1. Go to Magento 2 backend -> extension admin -> import section and create a new import job there.
2. Configure the job: add a schedule (or choose a trigger), select an imported entity, match attributes, etc.
3. Save and launch the job.

Now, the Improved Import & Export extension will automatically transfer data from Hive into Magento 2. Let’s take a look at the pros and cons of this approach.

Pros & Cons

As you can see, the difference between the traditional algorithm and improved approach is vast. The first one isn’t improved – it is entirely replaced by the new one. Even if you still rely on the standard way, you must admit that we offer a more optimized approach to importing data from Hive into Magento 2.

Pros

1. You don’t need to export data files.
2. There is no need to edit data files.
3. The import process is entirely automated.
4. You create a direct connection between Magento 2 and MariaDB.

Cons

1. Installing a third-party extension is necessary.

If you have any difficulties with the installation process and its further configuration, our specialists will help you.

Now, we’d like to draw your attention to some key facts about Hive and then proceed to the more in-depth exploration of the Improved Import & Export Magento 2 extension.

Get Improved Import & Export Magento 2 Extension

Hive Facts

Hive is often compared with HBase, Impala, and Spark SQL. Being a relational DBMS, it is considered a data warehouse software for querying and managing large distributed datasets. Let’s take a look at other features and facts:

• Hive is built on Hadoop.
• In addition to the Relational DBMS model, it is also a key-value store;
• Its developer is Apache Software Foundation;
• The initial release is dated back to 2012; the current one is 2.3.0 for July 2017;
• The license model is Open Source (Apache Version 2);
• There is no cloud-based offer;
• The implementation language is Java;
• Server operating systems are all OS with a Java VM;
• Data scheme, typing, and secondary indexes are available;
• SQL is represented as SQL-like DML and DDL statements;
• APIs and other access methods include JDBC, ODBC, and Thrift;
• Supported programming languages are C++, Java, PHP, and Python;
• Server-side scripts are available;
• Note that there are no triggers;
• Partitioning methods include sharding;
• Replication methods are based on a selectable replication factor;
• MapReduce is available;
• Consistency concepts include Eventual Consistency;
• No foreign keys and transaction concepts are represented;
• Concurrency and durability are among available features;
• User concepts are described by access rights for users, groups, and roles.

Improved Import & Export Magento 2 Extension

Now, it’s time to tell a few more words about the Improved Import & Export Magento 2 extension. Let’s start with the ability to automate import and export processes.

Automated Import & Export Processes

As mentioned above, you can achieve the automation in two ways: by creating a schedule of by using triggers.

Cron Schedule

The Improved Import & Export Magento 2 extension supports cron allowing you to create any custom schedule of updates. It is only necessary to know the default cron syntax. Alternatively,  you can select one of the predefined intervals which are fully customizable as well.

If you don’t need to automate import/export processes, the Improved Import & Export lets you create jobs that have no schedule. It is useful for one-time migrations or asynchronous updates that cannot be scheduled.

And you can always launch an automated profile manually. This will save you from the necessity to create a new import job if an update is provided before or after schedule. Below, you can see how simple is the configuration:

Events

As for the second way to automate import and export processes, it adds even more flexibility. You can select a system event or a custom one and use it as a trigger that launches the specific operation. Note that it is also possible to create conditions to make the procedure more precise. For further information about event-based automation, read this article:  How to Run Magento 2 Import or Export After Specific System Event or Process.

Now, when you know how to automate the Magento 2 Hive import, we’d like to draw your attention to the mapping opportunities provided by the Improved Import & Export Magento 2 extension.

Advanced Mapping Features

Our module offers a plethora of mapping features, but the most important two are mapping presets and the extension’s matching interface. Below, we describe each one in more detail.

Mapping Presets

With the advanced mapping presets provided by the Improved Import & Export plugin, you will forever forget about inconvenienced caused by manual data editing. Our team has created the most user-friendly way to solve the problem of different attribute standards. Now, you only need to select an external platform and apply the corresponding preset. The extension will analyze the input file matching its attributes to ones used internally. The process looks as follows:

You must admit that is impossible to simplify it even more. Still, you have an opportunity to do the same procedure manually.

Matching Interface

Alternatively, we provide a user-friendly matching interface where you can achieve the same goal manually. There is no need to open data files. Instead, you select a third-party attribute in one column and specify the corresponding one used in Magento in front of it. If necessary, set a hardcoded attribute value in the third column. It will be added to all items in the table automatically. The attribute matching is illustrated below:

Attribute Values Mapping

Additionally to attributes, you can map their values. The process is based on the same principle as the procedure mentioned above. Note that you can match multiple external values to one used internally. For example, you have an external attribute “color” with two values: “lime-green” and “avocado-green”. In your database, there is only one corresponding value – “green”. Match each external designation to the one used internally. You can find more information regarding attribute values mapping here: Attribute Values Mapping.

Attribute Values Editing

The Improved Import & Export Magento 2 module extends the opportunities described above even more with the ability to edit attribute values. With our extension, you can:

• Add prefix;
• Add suffix;
• Split data;
• Merge data.

Note that the commands can be combined to edit multiple attribute values in bulk. And you can create conditions to apply changes more precisely. For further information, follow this link:  How to Modify Attribute Values During Import and Export in Magento 2.

Category Mapping

And you can always match external categories to ones used internally. Alternatively, the Improved Import & Export Magento 2 extension provides the ability to greate missing sections of your catalog right during the import process. Below, you can see how the category mapping is applied:

Both features are described here:  Category Mapping.

Attributes On The Fly

The last feature we’d like to draw your attention to in the mapping section is the ability to create attributes on the fly. To create missing attributes, add parameters necessary to generate the missing data, and the Improved Import & Export Magento 2 extension will do everything for you. Use the following general format to achieve this goal:

Attribute|attribute_property_name:attribute_property_value|…

You can discover the full algorithm of attributes creation on the fly here: Product attributes import.

Extended Connectivity Options

While you can connect Magento 2 to Hive directly without data files, the Improved Import & Export Magento 2 extension still supports them. It also works with various online sheets as well as provides the ability to leverage several API connections. Let’s explore each feature individually.

Multiple File Standards

You no longer need to convert data files to CSV. Although it is the only supported file format, the Improved Import & Export extension adds several more options. The module works with XML, JSON, ODS, and Excel additionally to CSV. It means that you can freely import-export data via all five standards.

Multiple File Sources

Another benefit provided by Improved Import & Export is the availability of numerous file sources. First of all, the extension supports FTP/SFTP. Thus, you can use your local server as a point of file exchange. At the same time, any remote server can be utilized for the same purpose.

An alternative option is represented by Dropbox support. You can use an existing account or create a new one to transfer files between Magento 2 and external systems.

When it comes to import processes only, the Improved Import & Export Magento 2 extension allows transferring data files into your website via a direct URL. Just specify the address, and the module will move a file into Magento 2 updating the system.

Note that all compressed data is unpacked automatically!

Alternative Ways of Import & Export

As we’ve already mentioned, another crucial aspect of the Improved Import & Export Magento 2 extension is the ability to avoid files from data transfers. Currently, the module offers two ways of achieving this goal. First of all, you can use some intermediaries, such as Google Sheets, Office 365 Excel, or Zoho Sheet. The Google Sheets Magento 2 integration has the following appearance:

The second way to avoid data files is represented by the support for various API connections: REST, SOAP, and GraphQL. Note that you can freely apply all the features described above to any connection type or entity.

Watch the video below to see the extension in action:

Final Words

Although the traditional approach to importing data from Hive to Magento 2 seems to be complicated, it is no longer a problem if you use the Improved Import & Export Magento 2 extension. Now, you can connect your e-commerce store to a remote database directly transferring all the necessary information in a matter of a few clicks.

It is no longer necessary to export a CSV from Hive, edit data file, or map attributes. Furthermore, you don’t need to relaunch the import process every time an update is required. Automate data transfers in one of the two ways (or combine both approaches). You can even match external attribute values and categories to ones used within your Magento 2 database.

While everything is straightforward and intuitive, you won’t face any difficulties trying to connect your Magento 2 store to a remote Hive database. For further information, follow the link below and contact our support:

Get Improved Import & Export Magento 2 Extension