How to Import Data from Cassandra to Magento 2

The following article discusses two utterly different approaches to Magento 2 data import from Cassandra. Unfortunately, it is not possible to connect the database to you store directly if you don’t use any third-party tools. Although there is an alternative solution, many people still run the integration manually spending hours of time on the synchronization. Indeed, the alternative approach offers a much more efficient way to achieve the same goal. Below, we describe both ways to import data from Cassandra to Magento 2.

Traditional Approach to Data Import from Cassandra to Magento 2

Any traditional approach to the data import from a remote database to Magento consists of three steps. First of all, you should create an output file with the desired information. Usually, it is a CSV file. Some databases offer an interface where you can export all data without any need to work with the code. The others require coding experience so that you cannot get the output file by clicking on a few buttons.

Next, you need to make the output file suitable for Magento 2. Note that it is not possible to transfer data files between such native platforms as Magento 1.x and 2.x without modifying their attributes; therefore, the necessity to edit the output from Cassandra is not a surprise at all.

The third step requires importing the output to Magento 2. If attributes are matched correctly, this process won’t take much time unless you have an incredibly huge file. It is also necessary to mention that you have to repeat each of these steps while running every update.

Now, when you know the universal scheme of the traditional data import from Cassandra to Magento 2, let’s concentrate on each step individually.

How to Export Data from Cassandra to CSV File

To export data from Cassandra to a CSV file, you need to use the simple COPY command.  Note that it works in both directions so that you can import or export data. The default setup of a CSV file separates fields with a comma (,) and records with a line separator, quotes field values in double-quotes (“”) and escapes double-quotes with a backslash inside a double-quoted string (“\””). Also, note that it doesn’t have a “header” record on the first line. The available CSV syntax options are:

• DELIMITER – a character used to separate fields;  (default: ,).
• QUOTE – a quoting character used to quote field values; (default: “).
• ESCAPE – a character used to escape literal uses of the QUOTE character; (default: \).
• HEADER – the first row of a CSV file; (default: false). When “true”, it causes COPY TO to output a valid header line.
• ENCODING – used by COPY TO to output Unicode strings; (default: utf8).
• NULL – used by COPY TO to represent a value that is not present; (default: empty string).

To make the use case clearer, we need to provide an example. To output data with a header row with the “|” field separator, single-quote characters for quoting values, and doubled-single-quotes for representing literal single-quotes, use the following command:

You will get the following output:

Now, you have to match attributes available from the output to ones used in Magento 2.

How to Match Cassandra Attributes to Magento 2 Attributes

The output mentioned above has nothing in common with data files used to update your Magento 2 database. It’s just an illustration of how to get the desired information. Usually, data files contain many more rows and columns. The more their volume is, the more work you should do matching third party attributes to internal ones. And it is a very painful routine since:

• You have to repeat it on every update;
• You have to fix every attribute manually;
• You have to check the whole document looking for mistakes.

Use sample files to edit the Cassandra output. You can find them here: Magento 2 Import & Export Sample Files. And don’t forget to check our article that explains manual data file editing: The Complete Guide to Magento 2 Product Import / Export.

How to Import Data from Cassandra to Magento 2 via CSV

Finally, we can discuss the last stage of importing tables from Cassandra to Magento 2. The process is quite simple if everything is ok with your CSV files. In case something is wrong (incorrect attributes are used), you will find that out right during the import process. Return to the previous stage to correct all mistakes that prevent the Cassandra output from being imported to Magento 2.

Note that you have to repeat data import manually every time the update is required. The procedure can quickly become annoying within a short period if data files need to be imported on a regular basis.

Perhaps, the only positive aspect of the traditional approach is the ability to integrate Cassandra with Magento 2. But there are still lots of drawbacks:

• You have to do everything manually;
• The work is divided into three stages and takes place in different environments;
• The probability of errors is extremely high (due to the direct data file editing);
• It is necessary to repeat all the steps to run every update.

Luckily, there is a more efficient way to import data from Cassandra to Magento 2.

User-Friendly Approach to Data Import from Cassandra to Magento 2

Meet the Improved Import & Export Magento 2 extension. The module is designed to avoid the steps above by providing the ability to create a direct connection between Cassandra and Magento 2. Instead of multiple environments, you work in a Magento admin. Furthermore, there is no need to repeat any actions due to the ability to automate the synchronization.

Direct Cassandra Connection

The ability to connect to the remote database directly dramatically decreases the time necessary to run data import from Cassandra to Magento 2. You don’t need to create any output, but it is still possible to do that with the Improved Import & Export Magento 2 extension. Despite the chosen approach (with or without files), data updates can be fully automated. You can seamlessly create individual schedules for all updates. Below, we shed light on the most efficient way of synchronization, so no file transfers are described in this chapter of the article. Yes, you don’t need to create CSV files dealing with the code solution mentioned above. The Improved Import & Export extension transfers all data to your store without any intermediaries allowing you to save lots of time and effort. The problem of different attribute standards is solved in a much more efficient manner as well.

Data Mapping for Magento 2 Cassandra Integration

Different attributes cause problems even if you avoid data file transfers. There is still a necessity to match one values to their alternatives to make the integration possible. But you don’t have to worry about that problem since the Improved Import & Export Magento 2 extension offers a user-friendly mapping interface with tons of various features. Below, we describe it detailly. As for the Magento 2 Cassandra integration, the module provides the ability to map all attributes right in the extension admin. Next, you can apply the created scheme for further updates saving tons of time in future since attributes matching is a one-time procedure. Now, we’d like to say a few words about several benefits of the data import process that is also an inevitable part the Magento 2 Cassandra synchronization, but a much more user-friendly one when you use Improved Import & Export.

Direct Data Import from Cassandra to Magento 2

Now, let’s take a look at how the complicated traditional approach is transferred with the help of the Improved Import & Export Magento 2 extension. Instead of working in the three different environments, you should only perform the following actions in your admin:

• In the admin of Improved Import & Export, open the import interface.
• Create a new import profile:
  • You can automate updates with a schedule;
  • Configure other parameters, including an import behavior and a connection type.
• Choose a database and select a table to import data from.
• Map attributes.
• Launch the update.

If a schedule is created, the extension will launch all further updates automatically. It is evident that such conditions improve the integration between Magento 2 and Cassandra dramatically. The new algorithm is much more comfortable than the traditional one. With the help of the Improved Import & Export Magento 2 extension, you not only simplify data transfers but also make it fully autonomous right after the corresponding import profile is configured and saved.

Below, we’d like to draw your attention to the core features of Cassandra and then describe the functionality of the Improved Import & Export Magento 2 extension in more detail.

Get Improved Import & Export Magento 2 Extension

Cassandra Features

Cassandra is often compared with MongoDB, HBase, and Redis. Being a wide-column store, it incorporates the ideas of BigTable and DynamoDB. These are some other facts about Cassandra:

• The developer of this database is Apache Software Foundation.
• Its initial release took place in 2008. The current version – 3.11.2 – is dated to February 2018.
• The licensing model is Open Source
• No cloud-based options are available at the moment.
• The implementation language of Cassandra is Java.
• The database supports the following server operating systems:
  • BSD;
  • Linux;
  • OS X;
  • Windows.
• Note that Cassandra is a schema-free system when it comes to the data scheme.
• It provides typing but lacks XML support.
• Secondary indexes are restricted.
• The SQL support is represented with SQL-like SELECT, DML and DDL statements (CQL).
• APIs and other access methods are represented with CQL and CQL-like languages.
• As for supported programming languages, Cassandra offers a broad list that includes C# and C++, Clojure, Erlang, Java, JavaScript, PHP, Python, Ruby, etc.
• No server-side scripts are available.
• Cassandra offers triggers, sharding partitioning methods, and a selectable replication factor. MapReduce is available as well.
• Consistency concepts include Eventual Consistency and Immediate Consistency.
• No foreign keys and transaction concepts are available.
• Note that Cassandra supports concurrency and durability, but there are no in-memory capabilities represented within the system.
• As for user concepts, access rights can be defined per object.

Now, when you know the critical facts about Cassandra, we can draw your attention to the core features of the Improved Import & Export Magento 2 Extension.

Improved Import & Export Magento 2 Extension

The following part of the article sheds light on the ability to create schedules within Improved Import & Export, describes its extended connectivity options, as well as demonstrates mapping opportunities represented with the extension.

Broad Scheduling Options

Our extension allows you to create any custom update schedule. Due to the cron support, the Improved Import & Export module provides extra flexibility. You only need to use the default cron syntax. If you don’t want to create custom schedules, the plugin offers an opportunity to select one of the predefined values. If necessary, the can be customized as well using the same principle.

At the same time, you can create import/export jobs without schedules. Such profiles are launched in a manual mode only. Note that the same option is available for scheduled updates. If you get an essential data file long before the planned update, you can launch the appropriate import profile in an asynchronous mode. Here is how you can create various schedule types:

Extended Connectivity Options

Another vital improvement provided by our extension is its extended connectivity opportunities. The Improved Import & Export Magento 2 extension not only substantially increases the list of supported file formats, but it also adds numerous file destinations and alternative ways of data transfers to the default Magento functionality.

Multiple File Formats

The Magento platform is bound to a single file format only. It works with CSV files, but there are many more standards used by external systems. As a result, you have to convert the external output to make it suitable for the e-commerce platform.

Fortunately, the Improved Import & Export Magento 2 extension dramatically extends the list of supported file formats. In addition to CSV, it allows you to leverage  XML, JSON, ODS, and Excel. It means that you can import data to your store without converting them to CSV or create the corresponding output that is suitable for external systems.

Thus, support for multiple file formats is another feature that acts as a robust time-saver, but it is just the beginning!

Numerous File Sources

In addition to multiple file formats, the Improved Import & Export extension works with numerous file sources. As a result, you can fully automate data transfers for integrations based on files. With our module, you can leverage the following file formats:

• FTP/SFTP. Firstly, it is possible to leverage a local server for file transfers. Secondly, a remote server can be utilized for importing/exporting data files to/from Magento 2.
• Dropbox. A Dropbox account is the second option that you can use to connect your store to a third-party platform.
• URL. Direct URL uploads are another way to synchronize Magento 2 with an external system, but it is an import-only option.

Note that compressed data can be imported into your store. The Improved Import & Export extension unzips/untars it automatically.

Alternative Ways of Import & Export

As for the alternative ways of data migration, they are developed to eliminate file transfers from the process. The first available option is based on Google Sheets. You can freely transfer data from the service to your e-commerce store as follows:

Other ways include support for SOAP API, REST API, and GraphQL API integrations, which are used for establishing more complex connections. It is also worth mentioning that all extension features work seamlessly with all connection types, file sources, and entities.

Advanced Mapping Functionality

As mentioned above, the mapping functionality discretion of the Improved Import & Export Magento 2 extension is another crucial time-saver. Tons of improvements in this direction come with the module so that you can not only make the external output suitable for Magento but also modify it according to your specific business needs. Let’s describe each feature individually.

Attributes Mapping

It’s no longer necessary to mention the problem of different attribute standards but we need to pay separate attention to the solution that helps you forget about multiple headaches caused by the lack of the unified approach to the way data is stored. With the Improved Import & Export module, you can fully leverage a convenient mapping interface to match external attributes to ones used in Magento 2 making any external output suitable for the Magento 2 import. With the ability to apply a corresponding preset for the selected import table, the procedure is reduced to just a few clicks.

Being pre-made mapping schemes, mapping presets radically improve every integration. They completely eliminate the necessity to match attributes on your own. The standard procedure (described below) can be replaced with just two actions. Firstly, you should select a platform which provides the import file. Secondly, it is necessary to apply the corresponding mapping preset.

As you can see, the necessity to edit data files directly is eliminated/ Moreover, you don’t need to configure mapping presets manually, but you still can do that. Below, you can see how to apply a mapping preset for the Magento 1/Magento 2 migration.

Go to the ‘Select a Platform’ field, choose the preset, and clicking the ‘Load Map Attributes’ button. If necessary, you can view sample files below the drop-down.

To map attributes automatically, the Improved Import & Export Magento 2 extension compares system attributes with ones from the import file and matches unsuitable table fields to the corresponding system values. The process ends up with with the premade mapping scheme that looks like the following one:

Please, contact our support regarding mapping presets for your specific business case. Leave your requests here: Firebear Help Center.

As we’ve just mentioned, the Improved Import & Export Magento 2 extension lets you do the same things manually. If there is no preset for your particular business case, you can quickly create a new mapping scheme manually.

In the mapping section of the extension, add a new mapping row and specify the first values: select a default Magento 2 attribute in the ‘System Attribute’ column and specify a corresponding import attribute in the ‘Import Attribute’ column.

The third column is designed for hardcoded (default) values. You can specify them for all items related to the corresponding mapped attribute. Also, note that the created mapping scheme is saved and used for all further updates. As mentioned above, the mapping procedure is turned into a one-time process. Thus, you don’t need to repeat any of the steps above. To make the long story short, let’s proceed to other features that improve every integration even more.

Attribute Values Mapping

Our extension extends the functionality mentioned above even more. With the Improved Import & Export module, you can map not only attributes but their values. The necessity to edit them manually is wholly eliminated saving you from spending hours of time on the routine tasks. Match external attribute values to the corresponding values used in your Magento 2 database within just a few clicks. The process of value synchronization is as simple as the following three steps:

• Choose an external attribute;
• map it to the one used internally;
• Repeat the same actions with their values.

For the better understanding of the feature, take a look at the following example. For instance, you have an import file that includes the “color” attribute with two values: “ultramarine-blue” and “deep-blue”. There is also the same attribute in your database. Let’s assume that it has only one value – “blue.”

The Improved Import & Export Magento 2 module allows mapping both external values to the one used internally. As a result, products will be imported with the “blue” value instead of “ultramarine-blue” and “deep-blue”.

Attribute Values Editing

To extend the functionality mentioned above even more, we’ve introduced the ability to modify attribute values during the import process. With the Improved Import & Export Magento 2 extension, you can edit multiple attribute values in bulk with rules. Use the following ones to make attribute values entirely suitable for your business needs:

• Add prefix. Add any prefix to any attribute value. For instance, you need to change the “blue” value of the “color” attribute to “ultramarine-blue”. Add the “ultramarine-” prefix to all existing values in bulk. Specify additional conditions to make the procedure more precise. Note that the extension will do everything for you.
• Add suffix. The same logic may be applied to suffixes. Besides, you can always combine two rules.
• Split data. The Improved Import & Export Magento 2 module provides the ability to split data into several sections if it is stored in a single block.
• Merge data. The opposite action is possible as well.

Improved Import & Export lets you edit values stored in the source file using RegEx. For further information, read this article: How to Modify Attribute Values During Import and Export in Magento 2.

Attributes On The Fly

In case some attributes are not available in the import file, you can create them on the fly. The Improved Import & Export Magento 2 extension lets you replace the attribute name with the data necessary to create a missing attribute. Use the following general scheme to generate missing attributes on the fly:

Start each new empty column with ‘attribute’ and use a pipe symbol to separate values. The full scheme for creating attributes looks as follows:

The feature is described in more details here: Product attributes import.

Category Mapping

The last mapping feature we want to draw your attention to is the category mapping. The Improved Import & Export Magento 2 extension provides the ability to save tons of time by matching external categories to the internal ones. And you can do everything right in a new import profile. Besides, you can freely create new categories during the update process.

Go to the Map Categories section and load values from the ‘categories’ column of the import table:

• Click the ‘Load Categories From Import File’ button and then ‘Add New’.
• In the ‘Import category path’ column, choose an external category.
• Match it to an internal one in the ‘New category path’ column.

Do everything like shown below:

As for the ability to create new categories, it is also quite simple. You only need to click the ‘New Category’ button and then specify its name and parent category. Everything works as follows:

To get a better understanding of the extension features, watch the following video:

Conclusion

You must admit that the Improved Import & Export extension dramatically simplifies the way data is imported from Cassandra to Magento 2.  There is no need to create a CSV file, modify its content in an editor, and run the import process. Our module extends the default platform opportunities by providing the direct connection between two systems. As a Magento 2 owner, you should only configure a few options in the admin section of the extension. For further information, follow the link below and contact the support:

Get Improved Import & Export Magento 2 Extension