How to import data from Netezza to Magento 2

Below, you can find two ways of connecting Magento 2 to Netezza – a relational DBMS. The integration allows importing data from a remote database to your e-commerce store. Firstly, we explore a universal approach that can be applied to all databases. Secondly, we provide a significantly improved way of achieving the same goal.

The Common Way of Moving Netezza Tables into Magento 2

The common way of moving Netezza tables into Magento 2 is based on the three following pillars. First of all, you should export a CSV file from a remote database. Why CSV, you will ask? Because it is the only format supported by Magento out of the box.

Next, edit the exported data to make it suitable for the Magento 2 requirements. Usually, external systems include attributes that are supported only inside them. Thus, you should change them according to the Magento 2 format.

The same is about attribute values. Although you can freely import them into your database, it is recommended to apply changes to a CSV file before doing that. Thus, you will dramatically decrease the amount of work in the future since editing values in the Magento 2 admin is a more time-consuming routine.

Finally, you can import data into your Magento 2 database. Use the default Magento 2 tools to complete this goal. The native import solution has lots of limitations, but it is enough for importing a CSV file to update your database. Let’s take a look at each step in more detail.

CSV Export from Netezza Database

You can export the Netezza table to CSV in several ways. Below, we explore two ways of achieving this goal: via Netezza external table or Netezza nzsql commands.
Note that the Netezza external table is the fastest way especially in comparison to a Netezza nzsql command. As for the later one, it is an easy method compared to creating Netezza external tables.

Netezza Export Table using Netezza External Tables

Use the Netezza transient external table to export the desired data to a CSV file. Note that it is possible to transfer not just the table output but also any Netezza SQL query or statement. If you are using a transient external table, there is no need to write the schema definition.
It is possible to use the following command to get the output:

You can use it to export data to a mounted file system on a local Netezza host. If you are running extract on a remote machine that has JDBC, ODBC, or OLEDB installed then use the ‘remoteshource odbc’ option in USING clause.

Unfortunately, no external table option lets you wrap the Netezza table column data in a quotation mark, but you can concatenate the single or double quote explicitly in SQL statements as follows:

Netezza Export Table using the nzsql command

Alternatively, it is possible to use the Netezza nzsql CLI with the option -o to export the data to a CSV file. It is slower compared to external tables, but here is the command example:

Map Third-Party Attributes to Magento 2 Attributes

Now, when the CSV output from Netezza is generated, you can proceed to the second stage – editing. Take the file and review it for external attributes. Next, replace them with the corresponding internal ones. The process is the more time-consuming, the more attributes the file include. As mentioned above, you can also edit the available values replacing them with the ones used in your database. After doing this work, proceed to the next step.

Import Netezza Table into Magento 2

Now, use the standard Magento 2 import tools to transfer data from a CSV file to Magento 2. Unfortunately, you cannot automate this process, so it is necessary to repeat all the three steps every time a new update is required.

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 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 Netezza to Magento 2 offers.

Pros

1. Magento 2 Netezza data import.

The only positive aspect of the traditional approach is the completion of our final goal: you establish the connection between Netezza and Magento 2. 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. Limited CSV export capabilities;
3. New commands to master;
4. Time-consuming attributes editing;
5. Slow and limited import;
6. Inability to automate further data transfers.

These reasons were enough for us to look for a better way of importing data from Netezza to Magento 2. To make the long story short, let’s describe it right now.

The Easy Way of Moving Netezza Bases into Magento 2

To improve the default import process, you need a third-party Magento 2 module, so meet the Improved Import & Export extension – our tool that simplifies every integration of your e-commerce store with external systems. The plugin is designed to reduce the complexity of the aforementioned approach and automate the described processes. Below, we explain how the solution works saving your time and effort.

Netezza Database Connection

The first key improvement related to the Magento 2 Netezza Integration is the ability to connect your ecommerce store to the database directly. Yes, 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 replacing them with more efficient solutions.

Instead of transferring files, our extension moves data. Instead of datafile editing, it allows matching external attributes to the corresponding internal ones within just a few clicks via mapping presets. At the same time, you can do everything manually as well, but the process takes place right in your Magento admin.

Furthermore, the ability to match attribute values is also represented within the Improved Import & Export Magento 2 extension. 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.

How to import data from Netezza to Magento 2

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

1. Go to 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 Netezza 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 time and effort necessary to connect your Magento 2 store to a remote Netezza database. Do you want more good news? You also prevent further time expenditures since the process is entirely automated.

Pros & Cons

Now, we’d like to draw your attention to the pros and cons related to the Improved Import & Export module and the Magento 2 Netezza integration:

Pros

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

Cons

If you look at the traditional approach and compare the improved one with it, it is tough to figure out any drawbacks, but there is one – the necessity to install a third-party module. But don’t make hasty conclusions! With our friendly support, it is more positive experience than a negative aspect. Contact us, and the Firebear team will help you solve any difficulties related to the installation. And don’t forget that we can implement your individual improvements to the module’s functionality and discuss their further implementation!

The features of the Improved Import & Export Magento 2 extension are described below, but let’s focus on Netezza’s characteristics.

Get Improved Import & Export Magento 2 Extension

Netezza Facts

Netezza is often compared with Teradata, Oracle and Microsoft SQL Server. Being a relational DBMS and key-value store, the platform is used as a  data warehouse and analytics appliance part of IBM PureSystems. Its other features are:

• The developer behind the database is IBM;
• The project exists since 2000;
• It operates under the commercial license;
• No cloud-based options are available;
• The only server operating system is Linux which is included in the appliance;
• Data scheme, typing, secondary indexes, and SQL are supported;
• APIs and other access methods are represented by JDBC, ODBC, and OLE DB;
• Supported programming languages include C, C++, Fortran, Java, Lua, Perl, Python, and R;
• Server-side scripts are supported;
• Triggers are not available;
• Partitioning methods are represented by sharding;
• Replication methods – by the master-slave replication;
• MapReduce is supported;
• Foreign keys are not available;
• Transaction concepts are represented by ACID;
• Concurrency and durability features are available;
• User concepts are represented by the fine-grained authorization concept.

Improved Import & Export Magento 2 Extension

Now, let’s dive deep under the hood of the Improved Import & Export extension and the way it simplifies the Magento 2 Netezza integration and other external connections. First of all, we’d like to draw your attention to several opportunities related to the data transfers automation.

Automated Import & Export Processes

Our module allows automating data transfers in two ways. You can either create an update schedule or specify events that will be used as triggers.

Schedules

The Improved Import & Export Magento 2 extension incorporates cron allowing you to create any custom schedule of updates. You only have to apply the default cron syntax. If you don’t want to do that, choose one of the predefined intervals, which are fully customizable as well.

Alternatively, the Improved Import & Export plugin allows creating profiles that have no schedules. They can be used for one-time data migrations and asynchronous updates. And don’t forget that every scheduled profile can be launched manually whenever you want.

The following gif image illustrates how to configure the appropriate section of the extension:

Events

As for the second approach to the automation, the Improved Import & Export module supports both system events and custom processes. You can not only select a trigger but also create conditions to make its use more efficient. For further information about this feature, read this article: How to Run Magento 2 Import or Export After Specific System Event or Process.

Advanced Mapping Features

Now, we’d like to draw your attention to the core mapping features of our extension. Let’s start with the easiest way of attributes matching.

Mapping Presets

The problem of different attribute standards is no longer so dramatic if you use Improved Import & Export. It can be solved in a few clicks: select and apply a preset suitable for your integration. Next, our plugin will analyze the input data and then match all third-party attributes to the corresponding ones used in your database.

The feature is illustrated by the following gif image:

The migration from Magento 1 to 2.x becomes easier, isn’t it?

Matching Interface

In case a preset for your particular integration is not available (contact us and ask to solve this issue), you can map attributes manually with the help of a convenient interface. First of all, you need to select a third-party designation. Then, specify the corresponding internal one in front of it. That’s all! The following image illustrates the process:

The additional improvement here is the ability to determine hardcoded attribute values. The third column from the example above lets you set it so that all items related to the attribute will get the specified designation.

Attribute Values Mapping

Attribute values mapping is another prominent feature of the extension. With the Improved Import & Export Magento 2 module, it is fast, intuitive, and user-friendly. The process is based on the same logic as the attributes matching described above: you select a third-party designation and specify the internal one next to it. For further information about the feature, check this article: Attribute Values Mapping.

Attribute Values Editing

Another feature that increases the efficiency of your routine related to attribute values is represented by the ability to edit them in bulk. Firstly, the Improved Import & Export Magento 2 extension lets you add a prefix to multiple attribute values. At the same time, you can edit them by specifying a suffix. These two rules can be combined and applied simultaneously.

If different attribute values are merged, you can split them within a few clicks. The opposite process is possible as well. Thus, if the data file contains information related to one value which is split between different sections, it is no longer a problem. The following article sheds light on attribute values editing with the help of the Improved Import & Export extension: How to Modify Attribute Values During Import and Export in Magento 2.

Category Mapping

When moving product data from external systems, you usually have to deal with categories unrelated to your catalog. As a result, you have to spend hours of time editing the imported product data for each item individually. Luckily, our module offers a better solution to this problem: you can match external categories to the ones used internally just like product attributes during the import process. The following gif image illustrates the feature:

And you can create new categories on the fly. The process is reduced to selecting a parent category and specifying a name of a new one. For further information, follow this link: Category Mapping.

Attributes On The Fly

If your data file lacks properties, you can create them on the fly. The Improved Import & Export Magento 2 module enables you to specify parameters necessary to do that via the following general form:

Attribute|attribute_property_name:attribute_property_value|…

The full process of attributes on the fly creation is described in the following article: Product attributes import.

Extended Connectivity Options

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

Multiple File Standards

First of all, our module substantially increases the number of supported file formats compared to the out of the box platform. Above, we’ve already mentioned that you should generate a CSV file from the Netezza table since the Magento platform supports this format only. 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

In the topic of file transfers, we should also say a few words about support for numerous sources that can be utilized as a point of data exchanges. Firstly, you can rely on 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 import only, our plugin enables the capability to transfer files via a direct URL. It is also worth mentioning that Improved Import & Export unpacks compressed data automatically. Thus, you no longer have to do that manually to make a further update possible.

Alternative Ways of Import & Export

As for alternative ways of data transfers, the Improved Import & Export extension provides several options in this area. Firstly, our module enables you to use various online sheets to create a bridge between your Magento 2 website and external systems. The extension supports Google Sheets, Office 365 Excel, and Zoho Sheet. The Google Sheets integration looks as follows:

Secondly, Improved Import & Export lets you establish a direct API connection between your Magento 2 website and any external systems. Use REST, SOAP, and GraphQL integrations to achieve this goal.

Watch the following video to see the extension in action:

Final Words

As you can see, the Improved Import & Export extension dramatically simplifies the default procedure of importing remote bases from Netezza to Magento 2. It allows eliminating file transfers from the process, offers intuitive attribute matching tools, and provides the ability to automate all transfers. You no longer have to spend hours of time creating CSV output, editing it, importing files into Magento 2, and repeating everything from the very beginning. With the Improved Import & Export Magento 2 extension, it is possible to configure the module once and then let it do all the work. For further information about the plugin and the Magento 2 Netezza integration, follow the link below and contact our support:

Get Improved Import & Export Magento 2 Extension