Extending HCL Commerce V9.

1 Elasticsearch-based Search
Ingest Service | Query Service
Search Customization Scenario
A product/variant is visible if the following custom condition satisfies. The fields in the below custom
conditions are from a custom extension table – XVISIBILITY which are used to determine if a product is
visible on storefront or not. Based on this logic we will add a visibility field index at the product level.

LiveStatus=LIVE AND lastVisibleDate > lastInvisibleDate AND

sysDate > firstVisibleDate

Introduction
The customization needs us to create a custom field, say X_Displayable in the product index. The
indexing process in the V9.1 HCL Commerce Elasticsearch-based search solution starts with the Ingest
Service and involves ETL (Extract, Transform, Load) data flows in NiFi that are defined as Connectors.
Connectors are connected data flow pipelines that are set up with NiFi process groups. The Ingest
Service and NiFi processes complete the extraction and transformation of the data for output for
updating the Elasticsearch index.

This document explains the steps that are required to create the customization in NiFi to index visibility
data followed by the customization to the user data field name mapping to map the custom visibility
field (x_displayable) to user data using the configuration API (Query Service API) and finally extend the
Search Profile which would be used on Storefront to return the custom visibility index field as response
field via Search REST API.

Approach
The following steps were followed to create the customization.
1. Create the custom table - XVISIBILITY
2. Create a NIFI connector using Ingest.
3. Update the Elasticsearch schema to hold the custom field for visibility.
4. Define the SQL which fetches the visibility of product from XVISIBILITY.
5. Join the custom connector to the auth.reindex
6. Build Index and verify the custom indexed fields – X_DISPLAYABLE.
7. Customize the User Data Mapping to map indexed fields to response fields to be returned by
extended search profile via REST API.
8. Test the Index and Rest API to verify the custom visibility field.

These steps are explained in the following section in detail.

Copyright © 2021 HCL Technologies Limited | www.hcltechsw.com | HCL CONFIDENTIAL

Extending HCL Commerce V9.1 Elasticsearch-based Search
Ingest Service | Query Service
Create the custom table – XVISIBILITY

Run the following SQL to create the table.

CREATE TABLE XVISIBILITY(
CATENTRY_ID BIGINT NOT NULL,
STORE_ID INTEGER NOT NULL,
LIVE_STATUS VARCHAR (254),
START_DATE VARCHAR (254),
DISPLAY_OUT_OF_STOCK INTEGER,
ALWAYS_VISIBLE INTEGER,
FIRST_VISIBLE_DATE TIMESTAMP,
UPDATED_DATE TIMESTAMP,
CREATED_DATE TIMESTAMP,
UPDATED_BY INTEGER,
CREATED_BY INTEGER,
LAST_VISIBLE_DATE TIMESTAMP,
LAST_INVISIBLE_DATE TIMESTAMP,
CALC_UPDATED_DATE TIMESTAMP,
END_DATE TIMESTAMP,
HIDE_FROM_SITE INTEGER,
OPTCOUNTER SMALLINT NOT NULL DEFAULT 0 ,
FOREIGN KEY (CATENTRY_ID) REFERENCES CATENTRY (CATENTRY_ID),
FOREIGN KEY (STORE_ID) REFERENCES STORE (STORE_ID))

Ensure to populate this table with business data.

Create a NIFI connector using Ingest

Create a custom connector in Ingest Service. This can be done using ‘CreateConnector’ Ingest Service
API. This connector will do – update the index schema, connect to the database and run custom ingest
sql to fetch visibility data. To create a connector in Ingest service, use the following API:

http://<hostname/IP>:30800/swagger-ui/index.html?url=/v3/api-
docs&validatorUrl=#/Create%20Connector%20Configuration/createConnector

For the API body, use the attached JSON provided

custom.json

Copyright © 2021 HCL Technologies Limited | www.hcltechsw.com | HCL CONFIDENTIAL

Extending HCL Commerce V9.1 Elasticsearch-based Search
Ingest Service | Query Service

Update the Elasticsearch schema to hold the custom field for visibility.
First, go to the nifi home page at : http://<hostname/IP>:30600/nifi/
Here you can view all the processors. To update the ElasticSearch Schema you need to go to the
processor “Specify Schema to Update” ( Leverage Nifi Search option to go to this processor)
Update the properties for the processor Specify Schema to Update.

a. Go to the new "custom" pipeline and double-click Schema Update Custom Connector.
b. Double-click Schema Update process group.
c. Find the Setup Elasticsearch Index Schema process group.
d. Find and right-click Specify Schema to Update and stop the processor.
e. Double-click the processor to modify the settings.
f. Go to the PROPERTIES tab and update the property param.schema with the value product.
g. Click Apply.
h. Start the processor by right-clicking and selecting start

{
"properties": {
"custom": {
"properties": {
"X_DISPLAYABLE": {
"type": "keyword",
"doc_values": "true",
"index": "true",
"store": "true"
},
"X_FIELD1":{
"type":"keyword",
"doc_values":"true",
"index":"true",
"store":"true"
}
}
}
}
}

Define the SQL which fetches the visibility of product from XVISIBILITY.
In this step we need to define the SQL which fetches the visibility of product from XVISIBILITY. For this in
the nifi home we need to
1. Go to the new custom pipeline and double-click Custom Connector Pipe Template (Database).
2. Go to Custom Connector Pipe > Execute SQL.
3. Inside the Execute SQL process group find the process Define custom SQL and index name.
4. Right-click and stop the processor.
5. Double-click the processor to modify the settings.
6. Go to the Properties tab and update the property index.name with the value product.
7. Update the property ingest.database.sql with following value:

Copyright © 2021 HCL Technologies Limited | www.hcltechsw.com | HCL CONFIDENTIAL

Extending HCL Commerce V9.1 Elasticsearch-based Search
Ingest Service | Query Service

SELECT xv.CATENTRY_ID, xv.store_id , c.FIELD1 AS X_FIELD1,

CASE
WHEN xv.live_status ='LIVE' AND xv.last_Visible_Date >
xv.last_Invisible_Date AND sysDate > xv.first_Visible_Date
THEN 'dispayable'
ELSE 'not displayable'
END AS X_DISPLAYABLE
FROM xvisibility xv , catentry c , store s
WHERE xv.catentry_id = c.catentry_id
AND xv.store_id = s.store_id

Update the processor which reads the result set of above SQL and writes the index Json following below
steps:

Update processor CustomConnectorPipeProcessor.

a. Go to the new "custom" pipeline and double-click Custom Connector Pipe Template (Database).
b. Go to the Custom Connector Pipe > Custom Connector Pipe Processor process group.
c. Find and right-click CustomConnectorPipeProcessor and stop the processor.
d. Double-click the processor to modify the settings.
e. Go to the Properties tab.
f. For the property Script Body, add the following lines and then click OK and Apply.

final Map p = new HashMap();

final String X_DISPLAYABLE = json.get("X_DISPLAYABLE");
if (X_DISPLAYABLE != null){
p.put("X_DISPLAYABLE", X_DISPLAYABLE);
}
final String X_FIELD1 = json.get("X_FIELD1");
if (X_FIELD1 != null){
p.put("X_FIELD1", X_FIELD1);
}
document.put("custom", p);

Attaching the entire final Script for reference.

script.txt

Copyright © 2021 HCL Technologies Limited | www.hcltechsw.com | HCL CONFIDENTIAL

Extending HCL Commerce V9.1 Elasticsearch-based Search
Ingest Service | Query Service
Join the custom connector to the auth.reindex
Now we have our custom process groups ready and we need to join them to auth.reindex. Please follow
the step 5-7 at
https://help.hcltechsw.com/commerce/9.1.0/tutorials/tutorial/tsd_connectorconfigure_elastic.html for
detailed instruction on this step.

Below is the illustration of how the custom process groups would look like after we join them to
auth.reindex.

Build Index and verify the custom indexed fields – x_displayable

Build the Search index by running the following command from a REST client.

POST: https://CommerceServerName:ts-app-
port/wcs/resources/admin/index/dataImport/build?connectorId=auth.reindex&storeId=1

The above API returns a Job code, we will check the build index status using the job code retunred via
the REST client by running following API.

GET: https://CommerceServerName:ts-app-
port/wcs/resources/admin/index/dataImport/status?jobStatusId=jobStatusId

STATUS = 0 = Success
STATUS = -1 = In progress
STATUS = 1 = Error

Verify that the displayable field is indexed.

Using a REST client, issue the following POST commands to verify that the search index data are
available. Product data:
http://<ElasticSearchServerName>:30200/auth.<storeId>.product/_search

Copyright © 2021 HCL Technologies Limited | www.hcltechsw.com | HCL CONFIDENTIAL

Extending HCL Commerce V9.1 Elasticsearch-based Search
Ingest Service | Query Service

Copyright © 2021 HCL Technologies Limited | www.hcltechsw.com | HCL CONFIDENTIAL

Extending HCL Commerce V9.1 Elasticsearch-based Search
Ingest Service | Query Service
Customize the User Data Mapping

To map the user data field and create a profile, use the following Query Service API:
http://<hostname/IP>:30920/search/resources/swagger-
ui/index.html?configUrl=/search/resources/v3/api-docs/swagger-config.

• From the Swagger UI, go to the Search-Config-Resource > POST: /api/v2/configuration API.
• Enter the nodeName as component.
• For the API body, use the following code:

{
"valuemappingservice": {
"valuemapping": [
{
"internalName": "CatalogEntryUserDataFieldNameMapping",
"externalName": "CatalogEntryUserDataFieldNameMapping",
"valuemap": [
{
"externalValue": "X_DISPLAYABLE",
"internalValue": "custom. X_DISPLAYABLE"
},
{
"externalValue": "X_FIELD1",
"internalValue": "custom. X_ FIELD1"
}
]
}
]
}
}

• Restart Query service, for this run docker restart <container_id>

Create extension search profile using the configuration API :

• From the Swagger UI, go to Search-Profile-Resource > POST: /api/v2/documents/profiles/{profileName}.
• Enter the profileName as X_findProductByIds_x.

Copyright © 2021 HCL Technologies Limited | www.hcltechsw.com | HCL CONFIDENTIAL

Extending HCL Commerce V9.1 Elasticsearch-based Search
Ingest Service | Query Service
• For the API body, use the following code:

{
"parentProfileName": "HCL_findProductByIds_Details ",
"profileName": "X_findProductByIds_x ",
"query": {
"responseFields": [
"custom.X_DISPLAYABLE"
]
}
}
Test
the
Index
and Rest API to verify the custom visibility field.

Hit the REST API – product view by Ids using the custom profile created and verify the custom visibility
field – X_DISPLAABLE under UserData Section.

Copyright © 2021 HCL Technologies Limited | www.hcltechsw.com | HCL CONFIDENTIAL