If you have attended the latest Liferay Symposiums or other such events, you may have been at a talk where an ETL system was demonstrated. With these tools, you can easily import and export data between external systems and Liferay Commerce.

Before going on, if you haven’t worked with Liferay Commerce or its integrations, I recommend you to watch this video of DEVCON 2019 which provides an overview of the installation process, the advantages and the default integrations of Commerce.

Although there is still no comprehensive documentation available that explains the process in depth or a step by step guide on how to do it, I have decided to share my experience here, putting together a tutorial with ideas and tricks, so that those who have never worked with Talend and / or Liferay Commerce before, will be able to get started, use the "tLiferayOutput" component developed for Talend and learn a little about the headless APIs that Liferay includes out-of-the-box.

This is the first blog in a series of 4, but don't panic, the objective of dividing it into several, is to make your reading lighter.

Previous knowledge of Talend or indeed of Liferay Commerce is not necessary to be able to follow this as we will go through it step by step each point, so that you can learn and enjoy in the process, starting with the installation and ending, with the ETL working and loading products to Liferay Commerce.

In this first post we will start with a brief introduction of the tools that we will use throughout the current and upcoming posts; then we will dive into Talend, ending with a connection to the database and showing the data.

In the second post we will show how to configure Liferay Commerce in a few steps and a short introduction about the things to consider with the Integration between Liferay Commerce and an external system.

In the third post we will start the configuration of the product loading using the components of Liferay.

In the fourth and last we will create the "routines" (jobs) that process and transform the data in Talend according to the OpenAPI specification as well as the load process and validation in Liferay Commerce.

With Liferay Commerce installed on Liferay Portal CE (or Liferay DXP), we will start building our ETL in Talend 7.1, in order to automate the loading of products from data in an external database. Here we will use MySQL, but the external system could be SAP, Salesforce, a csv file. In those cases, only the input component would change; the logic of how to do it is the same regardless of the data source.

What will we need throughout this series?

• Liferay Portal CE 7.2 with Tomcat and Liferay Commerce 2.0.7 installed: 
  • You can download it from here, choosing the option, “Portal CE + Commerce Bundle”.

• MySQL Server 5.7
  • If you want to use the sample database that is used in this post, it will be necessary to import the dump with products for “DumpProducts.sql” available here and download the product pictures available here. The DB data is in Portuguese, it was what I had available when I started writing this post.

• Talend Open Studio for ESB (vesión 7.1.1) installed and with the Liferay plugins/connectors
  • You can download it from this link 
  • The Liferay components that we will use with Talend are not included out-of-the-box, so you will need to compile and install them. They are available in the master branch of Liferay’s GitHub repo : https://github.com/liferay/liferay-portal
  • Inside the repo, the connector is available at the following path: liferay-portal/modules/etl/talend
  • In the same path, you have the installation instructions available, for Talend and the components.

• SwaggerHub, where you will find the Headless APIs for “liferay portal” and “commerce”.
  • To load products, we will use the following API and method: commerce-admin-catalog/v1.0#/Product/post_products_
  • From your local Liferay installation the API call should look like this: “http://localhost:8080/o/headless-commerce-admin-catalog/v1.0/products/”
  • API calls can be made from PostMan or with the "curl" command. Remember that it is necessary to provide credentials using one of the out-of-the-box authentication protocols: OAUTH2 and / or Basic.
  • For more information on Headless APIs, I recommend you consult the following documentation

…given that we have already clarified everything that we will use and we have everything installed and configured, let's get to work...

1. Start Liferay Portal with Commerce.

2. Start MySQL with the sample dump imported (you will need to have the “commerce-products” table).

3. Start Talend Open Studio with a new workspace.

In Talend Open Studio: 

We will create our first Job.

1. In the Repository, right click on “Job Design” > “Create Job”. I used the name “insertProduct”, but you can choose the name you prefer. 

2. To the right of the screen you will find the “Palette”, you can search “Liferay” and if your installation went well, you will have 5 components available.

3. Now in the Palette, look for “tDBInput” and drag it to the “designer” area. We will use this component for the connection to the database with the products. To configure it, double click on the component and it will present the settings. In the image below, I highlighted the relevant fields. Fill in these fields according to your database:

4. Click on “Guess Schema” to check the connection is OK; then click on “Table Name” and select the table “products”.

5. In the “Query” field, write the query to retrieve the data from the table (“select * from products”) and click on “Edit Schema”. You will see the table data displayed:

6. Now that the external DB configuration is configured and validated, we can test the output of this data that we will then send to Liferay Commerce. For this, we will use only the names of the products limited to 10. 

To achieve that, we’ll do:

• In the “Query” field write “select name from products limit 10”, click on “Guess Schema”, validate that you have the column selected and click OK.

• A modal will ask whenever the query changes, if you want to propagate the change, then it is always important to accept, clicking on the "yes" button. Otherwise when we go to do the mapping, it won’t bring the values as variables automatically. 

7. Returning to the components, go to the “Palette” where we look for the component “tLogRow” and then we drag it to the designer area.

8. Click on the component “tDBInput” and a red arrow will be displayed, click on it and drag it to the component “tLogRow”:

9. Double click on “tLogRow” and from “Basic Settings” click on the “Edit Schema” option. Here we can see the input and output of the data, which as we have accepted the propagation previously, the “tLogRow” automatically catches the mapping, as seen in the image below (here it is not necessary to do anything, it is just an explanation for those who have never worked with Talend and wish to understand whats happening).

10. Now select the Job (the blue square that includes the two components), click on “Run” to execute it. In Job execution logs, you will see the 10 product names according to the query we made earlier.

Well done! If you were able to complete it without ever having worked with Talend in an ETL before, it will be more familiar to you in the next steps and you'll be able to create the integration for the Liferay Commerce product catalog.

We have finished this first introductory step for beginners. In the next post, we will configure Liferay Commerce in a few steps and understand the items necessary for the integration.

PS: Thanks to Bernard Mc Closkey (Commerce Solution Architect for EMEA) for helping me on the translation of this Blog post.