Introduced in Feature Pack 2

Configuring the Data Import Handler mapping

Introduced in Feature Pack 2

In this lesson, after the fields are defined in the search schema, the data extraction must be performed from the relational table. The data extraction is handled by the Data Import Handler, containing configuration files with predefined SQL query lines that extract WebSphere Commerce data. To extend the extraction scope, the lesson steps append these queries with the columns and tables for warranty.

Typically, mapping and extracting data from the database is a two-step process. The first step is flattening the relational tables as temporary tables with the di-preprocess utility, and then running a full index build. However, if the tables are not too complicated in relationships, for example the warranty tables that are used in this tutorial, they can directly be extracted with the Data Import Handler, which is used by the index building tool.

Note: The customization that you perform in this tutorial only modifies one catalog in one language. During this task, you are adding fields for Master Catalog 10001 in United States English. To add fields for additional catalogs or languages, you must repeat this process for the config files of the additional catalogs and languages.

This customization handles only full indexing. If you require the customization to handle delta indexing, you must perform changes to the delta query section in the config file, similar to the changes for full indexing. The queries are defined in multiple locations for products and bundles, depending on which items to index. In addition, there is a separate section of the query for delta updates. To handle delta updates, you must also append that query.

About this task

Procedure

  1. Navigate to the following directory:
    • WebSphere Commerce DeveloperWCDE_installdir/search/solr/home
    • SolarisLinuxAIXWindowsWC_installdir/instances/instance_name/search/solr/home

    This directory contains the Master Catalog folder, in which there are the configurations files for each language.

  2. Open the WCDE_installdir\search\solr\home\MC_10001\en_US\CatalogEntry\conf\wc-data-config.xml file.
  3. Locate <document name="CatalogEntry"> and add the following columns and tables in bold to the select SQL:
    Note: The index names in the following code are examples and might not work depending on your current Feature Pack level and store model.
    
<entity name="Product"
dataSource="WC database"
transformer="ClobTransformer, RegexTransformer"
query="SELECT CATENTRY.CATENTRY_ID,CATENTRY.MEMBER_ID,CATENTRY.CATENTTYPE_ID,CATENTRY.PARTNUMBER,CATENTRY.MFPARTNUMBER,CATENTRY.MFNAME,
STORECENT.STOREENT_ID,
CATENTDESC.NAME,CATENTDESC.SHORTDESCRIPTION,CATENTDESC.THUMBNAIL,CATENTDESC.FULLIMAGE, CATENTDESC.KEYWORD, CATENTDESC.PUBLISHED,
TI_DPGROUP.CATGROUP DPCATGROUP,
TI_APGROUP.CATGROUPS APCATGROUP,
TI_PRODUCTSET.PRODUCTSET,
TI_OFFERPRICE.PRICE,
TI_DPCATENTRY.CATENTRY_PARENT,
TI_CATALOG.CATALOG PARENT_CATALOG_ID,

TI_CASTB1.CAS_F1 CAS_F1ATTR, TI_CASTB1.CAS_F2 CAS_F2ATTR, TI_CASTB1.CAS_F3 CAS_F3ATTR,TI_CASTB1.CAS_F4 CAS_F4ATTR,TI_CASTB1.CAS_F5 CAS_F5ATTR,
TI_CASTB1.CAS_F6 CAS_F6ATTR,TI_CASTB1.CAS_F7 CAS_F7ATTR, TI_CASTB1.CAS_F8 CAS_F8ATTR,TI_CASTB1.CAS_F9 CAS_F9ATTR,TI_CASTB1.CAS_F10 CAS_F10ATTR,
TI_CASTB1.CAS_F11 CAS_F11ATTR,TI_CASTB1.CAS_F12 CAS_F12ATTR,TI_CASTB1.CAS_F13 CAS_F13ATTR,TI_CASTB1.CAS_F14 CAS_F14ATTR,TI_CASTB1.CAS_F15 CAS_F15ATTR,
TI_CASTB1.CAS_F16 CAS_F16ATTR,TI_CASTB1.CAS_F17 CAS_F17ATTR, TI_CASTB1.CAS_F18 CAS_F18ATTR,TI_CASTB1.CAS_F19 CAS_F19ATTR,TI_CASTB1.CAS_F20 CAS_F20ATTR,
TI_CASTB1.CAS_F21 CAS_F21ATTR,TI_CASTB1.CAS_F22 CAS_F22ATTR,TI_CASTB1.CAS_F23 CAS_F23ATTR,TI_CASTB1.CAS_F24 CAS_F24ATTR,TI_CASTB1.CAS_F25 CAS_F21ATTR,
TI_CASTB1.CAS_F26 CAS_F26ATTR,TI_CASTB1.CAS_F27 CAS_F27ATTR,TI_CASTB1.CAS_F28 CAS_F28ATTR,TI_CASTB1.CAS_F29 CAS_F29ATTR,TI_CASTB1.CAS_F30 CAS_F30ATTR,
TI_CAITB1.CAI_F1, TI_CAITB1.CAI_F2, TI_CAITB1.CAI_F3, TI_CAITB1.CAI_F4, TI_CAITB1.CAI_F5, TI_CAITB1.CAI_F6, TI_CAITB1.CAI_F7, TI_CAITB1.CAI_F8, TI_CAITB1.CAI_F9, TI_CAITB1.CAI_F10,
TI_CAFTB1.CAF_F1, TI_CAFTB1.CAF_F2, TI_CAFTB1.CAF_F3, TI_CAFTB1.CAF_F4, TI_CAFTB1.CAF_F5, TI_CAFTB1.CAF_F6, TI_CAFTB1.CAF_F7, TI_CAFTB1.CAF_F8, TI_CAFTB1.CAF_F9, TI_CAFTB1.CAF_F10,

TI_ADSTB1.ADS_F1 ADS_F1ATTR, TI_ADSTB1.ADS_F2 ADS_F2ATTR, TI_ADSTB1.ADS_F3 ADS_F3ATTR,TI_ADSTB1.ADS_F4 ADS_F4ATTR,TI_ADSTB1.ADS_F5 ADS_F5ATTR,
TI_ADSTB1.ADS_F6 ADS_F6ATTR,TI_ADSTB1.ADS_F7 ADS_F7ATTR, TI_ADSTB1.ADS_F8 ADS_F8ATTR,TI_ADSTB1.ADS_F9 ADS_F9ATTR,TI_ADSTB1.ADS_F10 ADS_F10ATTR,
TI_ADSTB1.ADS_F11 ADS_F11ATTR,TI_ADSTB1.ADS_F12 ADS_F12ATTR,TI_ADSTB1.ADS_F13 ADS_F13ATTR,TI_ADSTB1.ADS_F14 ADS_F14ATTR,TI_ADSTB1.ADS_F15 ADS_F15ATTR,
TI_ADSTB1.ADS_F16 ADS_F16ATTR,TI_ADSTB1.ADS_F17 ADS_F17ATTR, TI_ADSTB1.ADS_F18 ADS_F18ATTR,TI_ADSTB1.ADS_F19 ADS_F19ATTR,TI_ADSTB1.ADS_F20 ADS_F20ATTR,
TI_ADSTB1.ADS_F21 ADS_F21ATTR,TI_ADSTB1.ADS_F22 ADS_F22ATTR,TI_ADSTB1.ADS_F23 ADS_F23ATTR,TI_ADSTB1.ADS_F24 ADS_F24ATTR,TI_ADSTB1.ADS_F25 ADS_F21ATTR,
TI_ADSTB1.ADS_F26 ADS_F26ATTR,TI_ADSTB1.ADS_F27 ADS_F27ATTR,TI_ADSTB1.ADS_F28 ADS_F28ATTR,TI_ADSTB1.ADS_F29 ADS_F29ATTR,TI_ADSTB1.ADS_F30 ADS_F30ATTR,
TI_ADITB1.ADI_F1, TI_ADITB1.ADI_F2, TI_ADITB1.ADI_F3, TI_ADITB1.ADI_F4, TI_ADITB1.ADI_F5, TI_ADITB1.ADI_F6, TI_ADITB1.ADI_F7, TI_ADITB1.ADI_F8, TI_ADITB1.ADI_F9, TI_ADITB1.ADI_F10,
TI_ADFTB1.ADF_F1, TI_ADFTB1.ADF_F2, TI_ADFTB1.ADF_F3, TI_ADFTB1.ADF_F4, TI_ADFTB1.ADF_F5, TI_ADFTB1.ADF_F6, TI_ADFTB1.ADF_F7, TI_ADFTB1.ADF_F8, TI_ADFTB1.ADF_F9, TI_ADFTB1.ADF_F10
,XWARRANTY.WARTERM,
XWARRANTY.WARTYPE,
XCAREINSTRUCTION.CAREINSTRUCTION
  4. Append the table join clause in bold: 
    
FROM CATENTRY
INNER JOIN TI_CATENTRY_0 TI_CATENTRY ON (CATENTRY.CATENTRY_ID=TI_CATENTRY.CATENTRY_ID)
LEFT OUTER JOIN STORECENT ON (CATENTRY.CATENTRY_ID=STORECENT.CATENTRY_ID)
LEFT OUTER JOIN CATENTDESC ON (CATENTDESC.CATENTRY_ID=CATENTRY.CATENTRY_ID AND CATENTDESC.LANGUAGE_ID=-1)
LEFT OUTER JOIN TI_DPGROUP_0 TI_DPGROUP ON (CATENTRY.CATENTRY_ID=TI_DPGROUP.CATENTRY_ID)
LEFT OUTER JOIN TI_APGROUP_0 TI_APGROUP ON (CATENTRY.CATENTRY_ID=TI_APGROUP.CATENTRY_ID)
LEFT OUTER JOIN TI_PRODUCTSET_0 TI_PRODUCTSET ON (CATENTRY.CATENTRY_ID=TI_PRODUCTSET.CATENTRY_ID)
LEFT OUTER JOIN TI_OFFERPRICE_0 TI_OFFERPRICE ON (CATENTRY.CATENTRY_ID=TI_OFFERPRICE.CATENTRY_ID)
LEFT OUTER JOIN TI_DPCATENTRY_0 TI_DPCATENTRY ON (CATENTRY.CATENTRY_ID=TI_DPCATENTRY.CATENTRY_ID)
LEFT OUTER JOIN TI_CATALOG_0 TI_CATALOG ON (CATENTRY.CATENTRY_ID=TI_CATALOG.CATENTRY_ID)

LEFT OUTER JOIN TI_CASTB1_0_1 TI_CASTB1 ON (CATENTRY.CATENTRY_ID=TI_CASTB1.CATENTRY_ID)
LEFT OUTER JOIN TI_CAITB1_0_1 TI_CAITB1 ON (CATENTRY.CATENTRY_ID=TI_CAITB1.CATENTRY_ID)
LEFT OUTER JOIN TI_CAFTB1_0_1 TI_CAFTB1 ON (CATENTRY.CATENTRY_ID=TI_CAFTB1.CATENTRY_ID)

LEFT OUTER JOIN TI_ADSTB1_0_1 TI_ADSTB1 ON (CATENTRY.CATENTRY_ID=TI_ADSTB1.CATENTRY_ID)
LEFT OUTER JOIN TI_ADITB1_0_1 TI_ADITB1 ON (CATENTRY.CATENTRY_ID=TI_ADITB1.CATENTRY_ID)
LEFT OUTER JOIN TI_ADFTB1_0_1 TI_ADFTB1 ON (CATENTRY.CATENTRY_ID=TI_ADFTB1.CATENTRY_ID)
LEFT OUTER JOIN XWARRANTY ON (CATENTRY.CATENTRY_ID=XWARRANTY.CATENTRY_ID)
LEFT OUTER JOIN XCAREINSTRUCTION ON (CATENTRY.CATENTRY_ID=XCAREINSTRUCTION.CATENTRY_ID AND XCAREINSTRUCTION.LANGUAGE_ID=-1)
WHERE CATENTRY.CATENTTYPE_ID='ProductBean'"
    Note: These code changes apply to the product section of the configuration file. If the warranty must be added to other sections, for example bundle, the corresponding section queries can also be updated.
  5. Now that the queries are added for data extraction, provide the mapping between the query result set column names and the search index field names. Append the following mapping tags:
    
<!-- CUSTOMIZED FIELDS-->
<field column="WARTERM" name="warterm" />
<field column="WARTYPE" name="wartype" />
<field column="CAREINSTRUCTION" name="careinstruction" />
<!-- END CUSTOMIZATION-->
  6. Save your changes and close the file.
  7. Restart your servers:
    • WebSphere Commerce DeveloperRestart the WebSphere Commerce server.
    • SolarisLinuxAIXWindowsRestart the search server.

Results