How to import data from Vertica to Magento 2

If you are exhausted by transferring data from Vertica to Magento 2, you’ve come to the right place. Below, we describe a tiny tool designed to automate this process. If you are looking for how to establish the Magento 2 Vertica integration – also welcome! The following article describes a universal approach suitable for all remote databases and compares it to the more efficient one. We know how to import data from Vertica to Magento 2 and would like to share this knowledge with you.

The Common Way of Moving Vertica Tables into Magento 2

The common approach of moving Vertica tables into Magento 2 is based on three pillars. First of all, you need to create a CSV output since it is the only data format suitable for the Magento import. Next, it is necessary to edit the generated file making it ready for the import process. Finally, you can transfer the desired data into Magento 2. Let’s describe each step in more detail.

CSV Export from Vertica Database

Exporting a CSV file from Vertica is not an easy task. If you want to export data with the help of the SELECT query into CSV, you may try to use the following command:

But it will probably lead to an error. Instead, you should use VSQL. For instance, the following command will help you export a CSV file from the Vertica database:

For further information, follow this link: Exporting Data Using vsql.

Map Third-Party Attributes to Magento 2 Attributes

Now, you should adopt the content of the CSV file to the Magento 2 requirements. Since different systems always have their demands regarding the way data is represented within, it is impossible to import information from one platform to another directly. You should still work with attributes and sometimes their values. And while different values don’t block the import process, unrelated attributes cause errors. That’s why you should take the output CSV file from the previous step and review its content.

The more attributes you have – the more work should be done. As for values, we recommend editing them as well in case your database doesn’t contain the same designations. Thus, you will keep your catalog in order. Otherwise, it would be necessary to fix problems from the admin section which is even more time-consuming.

Import Vertica Table into Magento 2

Now, when all third-party attributes and their values are matched to the ones used in Magento 2, it is possible to run the import process. The default Magento 2 tools are enough to complete this goal, but they will never automate the procedure. Consequently, you have to repeat the steps above every time an update is required. Check the following guide to discover proper Magento 2 attributes and other nuances of the successful import process: The Complete Guide to Magento 2 Product Import / Export.

Pros & Cons

So, what are the pros and cons of the traditional approach to data import from Vertica to Magento 2? Let’s see.

Pros

1. Magento 2 Vertica data import.

The only positive aspect here is the ability to complete the import process. The default procedure is far from being user-friendly, but you can apply the same logic to every integration. But there are too many drawbacks.

Cons

1. Complicated data export;
2. Time-consuming data editing;
3. Limited import capabilities;
4. Lack of automation.

You must admit that these reasons are enough to look for a more user-friendly way of importing data from Vertica to Magento 2, and below we’ve prepared one.

The Easy Way of Moving Vertica Bases into Magento 2

It is not a surprise that the improved way of moving data from Vertica to Magento 2 is based on a third-party extension, so meet the Improved Import & Export module. This tool is designed to simplify your integration with the database as well as enhance all import and export processes. The extension has lots of useful features, but let’s take a look at the improved method of importing Vertica bases into Magento 2 first.

Vertica Database Connection

Instead of creating, editing, and transferring CSV files, you can connect to Vertica directly with the Improved Import & Export Magento 2 extension. Our module provides the ability to establish a data bridge between your e-commerce website and the database where all the necessary updates are effortlessly transferred from one point to another. The new way of connection has several unique features.

Magento 2 Vertica Data Import

Since the Magento 2 Vertica data import is no longer based on data files, you eliminate the two first steps of the default procedure. The Improved Import & Export Magento 2 extension provides the ability to eradicate both complicated CSV export process and time-consuming attributes matching. Furthermore, you can automate data transfers entirely. There are two ways of doing so. Firstly, the module lets you create a schedule of updates. Secondly, you can specify event-based triggers. Both features are described further in this post.

But direct data transfers don’t solve the problem of different attribute standards. Therefore, you still have to match them. Luckily, the Improved Import & Export Magento 2 extension provides several robust tools that revamp entirely the procedure described above. For attributes, you can use mapping presets or an intuitive matching interface. As for their values, they can also be mapped or edited in bulk. We will describe all these features soon. Now, let’s take a look at the improved data import from Vertica into Magento 2.

How to Import Data From Vertica to Magento 2

The data import process from Vertica to Magento 2 with the Improved Import & Export extension looks as follows:

1. Go to the Magento admin, find the Improved Import & Export extension, open its import section, and create a new job (profile) there.
2. Specify parameters required for the connection to the Vertica database, map attributes, create a schedule (or set triggers), and configure other options if necessary;
3. Save and launch the profile.

That’s it! These small steps are necessary to replace the complicated standard way of moving data from a remote database into Magento 2. The Improved Import & Export extension helps to save tons of time and effort connecting your e-commerce store to Vertica. And note that the process is fully automated, so you prevent further time expenditures!

Pros & Cons

Let’s see what pros and cons the Improved Import & Export offers then it comes to the Magento 2 Vertica integration.

Pros

1. Rapid Magento 2 Vertica integration;
2. The direct connection between systems;
3. No file export/import;
4. No file editing;
5. Fast attributes matching;
6. Automated data transfers.

Cons

What are the cons, you will ask? The only negative aspect is the necessity to install a third-party module. How do we fix it? With the help of our friendly support. Contact us in case of any difficulties, and the Firebear team will help you solve the problem. Don’t forget that you can propose your particular changes to the module’s functionality. We will discuss their implementation with pleasure!

Thus, the installation of the Improved Import & Export extension is a necessity if you want to import data from Vertica to Magento 2 without any headaches in a more efficient way. Now, let’s take a look at the features of the database and then proceed to a more in-depth exploration of our module.

Get Improved Import & Export Magento 2 Extension

Vertica Facts

Being simultaneously a relational DBMS, document store, and key-value store, Vertica is often compared with Cassandra, Amazon Redshift, and Oracle. Its specific characteristics include Columnar storage, MPP, Hybrid storage model, Aggressive data compression, High availability, ANSI SQL, etc. It is available both as a cloud and on-premise solution.

Vertica’s main competitive advantages include top performance, the ability to deploy the database anywhere, wide open source innovations, and analysis in place. Users report that SQL queries run up to 500 times faster than in the case of traditional databases. And Vertica is the first databases to offer in-database machine learning. Other features include:

• Typical application scenarios of Vertica include:
  • Embedded analytics;
  • Communication and network analytics;
  • Data Warehouse modernization;
  • Fraud monitoring and Risk management;
  • IoT analytics;
  • Customer behavior analytics.
• The database developer is Vertica / Micro Focus;
• Its current release is Vertica Analytics Platform 9.0, September 2017;
• The server operating system of Vertica is Linux;
• Data scheme, typing, and XML are supported;
• Secondary indexes are not available, but the concept of ‘projections’ can be used instead;
• SQL is supported;
• APIs and other access methods include:
  • Kafka
  • Proprietary protocol
  • RESTful HTTP API
  • ADO.NET
  • JDBC
  • ODBC
• Supported programming languages are:
  • C++
  • Java
  • Perl
  • Python
  • R
• Server-side scripts are supported;
• Triggers are not represented within the platform;
• Partitioning methods are represented by Sharding;
• Replication methods – by master-master replication;
• MapReduce is represented by Vertica Connector for Hadoop;
• Consistency concepts include Immediate Consistency;
• Foreign keys are supported;
• Transaction concepts are represented by ACID;
• Concurrency and durability features are supported;
• User concepts are represented by fine-grained access rights according to SQL-standard;
• Kerberos, LDAP, Ident, and hash are supported as well;
• Cost-based and subscription-based models are available.

Improved Import & Export Magento 2 Extension

Below, we describe the Improved Import & Export Magento 2 extension and shed light on its automation, mapping, and connectivity features.

Automated Import & Export Processes

As mentioned above, our module provides two ways of making every connection entirely automated. You can either create a schedule or specify events that will be considered triggers.

Schedules

The Improved Import & Export Magento 2 extension supports cron allowing you to create a custom schedule of updates. Use the standard cron syntax to specify any period that suits your business needs. At the same time, the module enables you to select one of the predefined intervals which are fully customizable as well.

If necessary, you can create a profile that has no schedule. Use it for one-time migrations or asynchronous updates. And don’t forget that the ability to launch import or export processes is available for scheduled profiles as well.

Below, you can see how to configure a section with schedules:

Events

In addition to schedules, you can specify what system processes or custom events can launch updates. Furthermore, the Improved Import & Export Magento 2 extension lets you specify various conditions to run data transfers more precisely. For further information, follow this link: How to Run Magento 2 Import or Export After Specific System Event or Process.

Advanced Mapping Features

The following section of this article explores features designed to improve your interaction with attributes. Several ways of attributes mapping and their values editing are discussed below.

Mapping Presets

The ability to match external attributes to ones used internally is dramatically simplified with the help of mapping presets. The Improved Import & Export extension provides a list of predefined mapping schemes that can be applied in a few clicks. Thus, you don’t need to match external designations to ones used internally in a manual mode. Just click a few buttons, and our module will analyze the input data making it suitable for the import process.

Below, you can see how to apply a preset for migration from Magento 1 to Magento 2:

Matching Interface

If a preset for your specific business case is not available, you can still match attributes more efficiently than it was proposed at the beginning. The Improved Import & Export extension offers an intuitive interface where you select an external designation and specify a corresponding internal one in front of it. At the same time, the module allows applying changes to the attribute values. A single default value can be specified in the corresponding column. The following gif illustrates the process:

Attribute Values Mapping

Besides, it is possible to match attribute values in a more efficient way. You no longer need to edit files to achieve this goal. The Improved Import & Export Magento 2 extension provides the ability to specify an external attribute value and match it to the corresponding internal one just like we’ve performed above with attributes. Since attribute values vary a lot depending on each business, there are no presets, but manual matching within a user-friendly environment won’t take much time. Follow this link for further information: Attribute Values Mapping.

Attribute Values Editing

In addition to the feature above, we also provide the ability to edit attribute values in bulk. You can use one or multiple commands simultaneously to implement changes. Furthermore, our extension provides the ability to create specific conditions to change attribute values more precisely. You can either add a prefix or suffix. At the same time, Improved Import & Export provides the ability to split and merge data depending on the condition of values. Sometimes, multiple values can be combined. If they are separated, it is not a problem as well. The ability to edit attribute values is described in this article: How to Modify Attribute Values During Import and Export in Magento 2.

Category Mapping

Another opportunity related to mapping dramatically simplified product transfers. When you import data from an external catalog, its categories may be different from yours. As a result, the new ones will be created after the import. With the Improved Import & Export Magento 2 extension, you can match them to ones used internally. The following gif image illustrates the corresponding functionality of our module:

And you can create categories on the fly. It is also a straightforward procedure. Specify a parent category and type a name of a new one. For further information, follow this link: Category Mapping.

Attributes On The Fly

And if there are some missing attributes, you can create them on the fly. The Improved Import & Export Magento 2 module enables you to specify parameters necessary for the automatic attribute generation. Use the following general form:

Attribute|attribute_property_name:attribute_property_value|…

The full algorithm is described here: Product attributes import.

Extended Connectivity Options

While the Magento 2 Vertica integration is based on a direct connection, there are several more ways to synchronize the data flow between Magento 2 and external platforms. You can use file transfers or rely on intermediaries.

Multiple File Standards

While Magento works with CSV files only, the Improved Import & Export Magento 2 extension dramatically extends the default functionality of the platform. It provides the ability to import/export data via XML, JSON, ODS, and Excel. Consequently, you no longer need to convert files of this formats into CSV and back.

Multiple File Sources

To transfer data files seamlessly, the Improved Import & Export module support numerous sources. The extension supports the FTP/SFTP connection. It means that you can move files using a local or remote server. A Dropbox account can be utilized for the same purpose. And if you deal with import processes only, it is possible to specify a direct URL. Note that all compressed files are unzipped/untarred automatically.

Alternative Ways of Import & Export

If you don’t want to deal with file transfers (like in case of the Magento 2 Vertica integration), the Improved Import & Export extension offers two alternative ways of integration. Firstly, you can use various online sheets. Use such intermediaries as Google Sheets, Office 365 Excel, or Zoho Sheet. The Google Sheets integration lets you import data in the following way:

Secondly, the module allows establishing the direct API connection. You can freely use REST, SOAP, and GraphQL integrations to synchronize the Magento 2 store with an external system.

Watch the following video to see the extension in action:

Final Words

As you can see, the default way of transferring data from Vertica to Magento 2 can be significantly improved with the help of our extension. The module not only eliminates most steps from the transfer it also allows automating the remaining processes. Thus, your work is reduced to configuring the import profile. For further information about the extension and the Magento 2 Vertica integration with its help, follow the link below and contact our support:

Get Improved Import & Export Magento 2 Extension