Following is my DIH config
<dataConfig> <script><![CDATA[ function addfield(row){ var field = row.get(
'COLcolor_col').trim().toLowerCase().replace('/',' or ').replace('&','and'
).replace(':','').replace(' ','_'); var field_value = row.get(
'COLcolor_col_val'); row.put(field,field_value); return row; } function
stringtoarray(row) { var value=row.get('category_ids'); if(value !="" &&
value !=null) { cat_arr=value.split("/"); row.put('cat_ids',cat_arr); return
row; } } ]]> </script> <dataSource type="JdbcDataSource" driver=
"com.mysql.jdbc.Driver" url="jdbc:mysql://######/######" batchSize="-1" user
="######" password="######" autoReconnect="true" tinyInt1isBit="false"/> <
document>
<entity name="products" pk="id" transformer="RegexTransformer" query = "
SELECT p.product_id as id, REPLACE(REPLACE(REPLACE(pd.product, '\n', ''),
'', ''), '\r','') as product, pd.meta_keywords, pd.search_words,
pd.page_title, p.product_id, p.company_id, p.list_price as list_price, min(
pp.price) as price, p.third_price, pp.retail_price, IF(p.third_price != '
0.00', ROUND(((p.list_price - p.third_price) / p.list_price) * 100), ROUND
(((p.list_price - pp.price) / p.list_price) * 100 )) as discount_percentage,
IF( p.third_price != '0.00', p.third_price, IF( pp.price != '0.00', pp.price,
p.list_price ) ) AS sort_price, REPLACE(REPLACE(ps.name, '\n', ''), '\r',
'') as seo_name, pe.name as brand_url, CONCAT('images/thumbnails/',floor(if(
i.image_id!=0, i.image_id,il.image_id)/1000),'/320/320/',REPLACE(REPLACE(
image_path, '\n', ''), '\r', '')) as image_url, max(IF(pc.link_type = 'M',
cat.id_path, null)) as id_path, cpfv.variant_id as brand_id, REPLACE(REPLACE
(REPLACE(cpfvd.variant, '\n', ''), '', ''), '\r','') as brand, REPLACE(
REPLACE(REPLACE(CONCAT(cpfv.variant_id,'_',cpfvd.variant), '\n', ''), '',
''), '\r','') as show_brand, CONCAT(cpfv.variant_id,'_',c1.category_id) as
suggest_brand, GROUP_CONCAT(distinct(pc.category_id) SEPARATOR '/') as
category_ids, GROUP_CONCAT(distinct(pc.category_id) SEPARATOR '/') as
cat_ids, GROUP_CONCAT(distinct(c1.status) SEPARATOR '/') as category_status,
GROUP_CONCAT(distinct(c1.category_id) SEPARATOR '/') as metacategory_ids,
max(IF(pc.link_type = 'M', cd2.category_id, null)) as category_id, max(IF(
pc.link_type = 'M', REPLACE(REPLACE(REPLACE(cd2.category, '\n', ''), '',
''), '\r',''), null)) as category, max(if(pc.link_type = 'M', c1.category_id,
null)) as metacategory_id, max(IF(pc.link_type = 'M', REPLACE(REPLACE(
REPLACE(cd1.category, '\n', ''), '', ''), '\r',''), null)) as metacategory,
max(IF(pc.link_type = 'M', REPLACE(REPLACE(REPLACE(CONCAT(cat.category_id,'_
',cd2.category), '\n', ''), '', ''), '\r','') , null)) as show_category, max
(IF(pc.link_type = 'M', REPLACE(REPLACE(REPLACE(CONCAT(cat.category_id,'_',
cd2.category,'_',cd1.category), '\n', ''), '', ''), '\r',''), null)) as
show_metacategory, max(IF(pc.link_type = 'M', cat.status, null)) as
maincategory_status, max(IF(pc.link_type = 'M', c1.status, null)) as
metacategory_status, max(IF(pc.link_type = 'M', REPLACE(cd2.category_path
,'///','>'), null)) as category_path, c.company as merchant, c.zone_type as
zone, CONCAT(c.lat,',',c.lng) as geolocation,
CAST(SUBSTRING_INDEX(c.zipcode, '-', -1) AS UNSIGNED) as zipcode,
REPLACE(REPLACE(REPLACE(CONCAT(c.company_id,'_',c.company), '\n', ''), '',
''), '\r','') as show_merchant, max(IF(pc.link_type = 'M',
REPLACE(REPLACE(REPLACE(CONCAT(cd1.category_id,'_',cd1.category), '\n',
''), '', ''), '\r',''), null)) as show_meta_id, max(IF(pc.link_type = 'M',
c1.include_in_search, null)) as include_in_search, c.is_ngo,
if(p.tracking='O',IF(sum(inv.amount)=0 OR sum(inv.amount) IS
NULL,0,1),IF(p.amount=0,0,1)) as product_amount_available, c.sdeep_rating
as merchant_rating, if(p.product_rating IS NULL,'0.00',p.product_rating) as
product_rating, popularity.total as popularity, popularity.bought as
bestsellers, promo.promotion_id, promo.compaign_tag_id as campaign_tag_id,
LOWER(REPLACE(promodesc.name,' ','_')) as promotion_name, c.fulfillment_id,
promo.supported_platform, promo.idms_campaign_id,
DATE_FORMAT(FROM_UNIXTIME(to_date),'%Y-%m-%dT %H:%i:%sZ') as to_date,
DATE_FORMAT(FROM_UNIXTIME(from_date),'%Y-%m-%dT %H:%i:%sZ') as from_date,
promo.promotion_type_id, promo.coupon_code, promo.cod_platform,
promo.number_of_usages, promo.allow_no_of_usages as allow_usages,
promo.stop as stop_other_rules, promo.once_per_customer, if(promo.to_date
>= UNIX_TIMESTAMP(),if(promo.status =
'A',CONCAT(promo.promotion_id,'_',REPLACE(REPLACE(promodesc.internal_name,'_','
'),'#',''),'#',promo.promotion_type_id,'-',promo.status,'-',promo.to_date),''),'')
as show_promotion, cm.market_id,
REPLACE(REPLACE(REPLACE(CONCAT(cm.market_id,'_',cm.seo_name), '\n', ''),
'', ''), '\r','') as show_market, p.storefront_featured_product,
p.tracking, p.free_shipping, p.shipping_freight, if(p.tracking='O',
sum(if(inv.amount IS NOT NULL ,inv.amount,0)), p.amount) as amount,
p.deals_index as deal_index, p.deals_index as deals_index, p.feature_index,
p.boost_index, p.is_cod, p.price_see_inside, p.deal_inside_badge,
c.neighbourhood_marketplace as is_neighbourhood_merchant,
p.neighbourhood_marketplace as is_neighbourhood_product, p.object_collate
as neighbourhood_tp_check, p.special_offer_badge, p.freebee_inside,
p.last_update, p.timestamp, p.status, p.manufacturer_reference_number,
p.is_wholesale_product, p.wholesale_type, p.min_qty, p.why_buy_reason_1,
p.why_buy_reason_2, p.master_id, p.is_master, p.anniversary_tag,
p.marketplace_product_visibility, c.marketplace_merchant_visibility,
max(if(pc.link_type = 'M', cat.marketplace_category_visibility, null)) as
marketplace_category_visibility, p.mobile_boost_index,
p.is_factory_outlet_product, p.outlet_brand_id as outlet_brand_id,
cob.brand_name as outlet_brand_name,
REPLACE(REPLACE(REPLACE(CONCAT(p.outlet_brand_id,'_',cob.brand_name), '\n',
''), '', ''), '\r','') as show_outlet_brand, outlet.outlet_status as
is_outlet_status, cob.status as is_cob_status, UNIX_TIMESTAMP() as
solr_update, c.status as company_status, c.company, c.is_trm, cspr.sort_1,
cspr.sort_2, if(p.special_offer_text IS NULL, '', p.special_offer_text) as
special_offer_text, REPLACE(REPLACE(REPLACE(pd.clean_full_description,
'\n', ''), '', ''), '\r','') as full_description,
REPLACE(REPLACE(REPLACE(pd.clean_short_description, '\n', ''), '', ''),
'\r','') as short_description FROM cscart_products p INNER JOIN
cscart_product_descriptions as pd ON pd.product_id = p.product_id and
pd.lang_code = 'EN' left join clues_search_promotion as cspr on
cspr.product_id=p.product_id LEFT JOIN cscart_product_prices as pp ON
pp.product_id = p.product_id LEFT JOIN cscart_companies AS c ON
c.company_id = p.company_id INNER JOIN cscart_products_categories as pc ON
pc.product_id = p.product_id INNER JOIN cscart_categories as cat ON
cat.category_id = pc.category_id AND cat.status IN ('A', 'H') LEFT JOIN
cscart_seo_names as ps ON ps.object_id = p.product_id AND ps.type = 'p'
LEFT JOIN cscart_product_popularity as popularity ON popularity.product_id
= p.product_id LEFT JOIN cscart_product_options_inventory as inv on
inv.product_id=p.product_id left JOIN cscart_category_descriptions cd1 on
cd1.category_id=SUBSTRING_INDEX(SUBSTRING_INDEX(id_path, '/',2), '/',1)
left JOIN cscart_category_descriptions cd2 ON cd2.category_id =
pc.category_id left JOIN cscart_categories c1 on c1.category_id =
cd1.category_id left JOIN cscart_images_links il ON il.object_id =
p.product_id and il.object_type = 'product' and il.type = 'M' left JOIN
cscart_images i ON il.detailed_id = i.image_id left join
cscart_product_features_values cpfv on cpfv.product_id=p.product_id and
cpfv.feature_id=53 left join cscart_product_feature_variant_descriptions
cpfvd on cpfvd.variant_id=cpfv.variant_id left JOIN cscart_seo_names pe ON
cpfv.variant_id = pe.object_id and pe.type = 'e' left join
clues_factory_outlet_apply outlet on outlet.company_id=c.company_id LEFT
join clues_outlet_brands cob on cob.brand_id=p.outlet_brand_id LEFT join
cscart_promotions promo on promo.promotion_id = p.promotion_id LEFT join
cscart_promotion_descriptions promodesc on promodesc.promotion_id =
promo.promotion_id LEFT join clues_markets cm ON POSITION(c.zipcode IN
cm.pincode) != 0 WHERE 1 AND p.status IN ('A','H') AND (c.status = 'A')
group by p.product_id" deltaImportQuery="SELECT p.product_id as id,
REPLACE(REPLACE(REPLACE(pd.product, '\n', ''), '', ''), '\r','') as
product, pd.meta_keywords, pd.search_words, pd.page_title, p.product_id,
p.company_id, p.list_price as list_price, min(pp.price) as price,
p.third_price, pp.retail_price, IF(p.third_price != '0.00',
ROUND(((p.list_price - p.third_price) / p.list_price) * 100),
ROUND(((p.list_price - pp.price) / p.list_price) * 100 )) as
discount_percentage, IF( p.third_price != '0.00', p.third_price, IF(
pp.price != '0.00', pp.price, p.list_price ) ) AS sort_price,
REPLACE(REPLACE(ps.name, '\n', ''), '\r', '') as seo_name, pe.name as
brand_url, CONCAT('images/thumbnails/',floor(if(i.image_id!=0,
i.image_id,il.image_id)/1000),'/320/320/',REPLACE(REPLACE(image_path, '\n',
''), '\r', '')) as image_url, max(IF(pc.link_type = 'M', cat.id_path,
null)) as id_path, cpfv.variant_id as brand_id,
REPLACE(REPLACE(REPLACE(cpfvd.variant, '\n', ''), '', ''), '\r','') as
brand, REPLACE(REPLACE(REPLACE(CONCAT(cpfv.variant_id,'_',cpfvd.variant),
'\n', ''), '', ''), '\r','') as show_brand,
CONCAT(cpfv.variant_id,'_',c1.category_id) as suggest_brand,
GROUP_CONCAT(distinct(pc.category_id) SEPARATOR '/') as category_ids,
GROUP_CONCAT(distinct(pc.category_id) SEPARATOR '/') as cat_ids,
GROUP_CONCAT(distinct(c1.status) SEPARATOR '/') as category_status,
GROUP_CONCAT(distinct(c1.category_id) SEPARATOR '/') as metacategory_ids,
max(IF(pc.link_type = 'M', cd2.category_id, null)) as category_id,
max(IF(pc.link_type = 'M', REPLACE(REPLACE(REPLACE(cd2.category, '\n', ''),
'', ''), '\r',''), null)) as category, max(if(pc.link_type = 'M',
c1.category_id, null)) as metacategory_id, max(IF(pc.link_type = 'M',
REPLACE(REPLACE(REPLACE(cd1.category, '\n', ''), '', ''), '\r',''), null))
as metacategory, max(IF(pc.link_type = 'M',
REPLACE(REPLACE(REPLACE(CONCAT(cat.category_id,'_',cd2.category), '\n',
''), '', ''), '\r','') , null)) as show_category, max(IF(pc.link_type =
'M',
REPLACE(REPLACE(REPLACE(CONCAT(cat.category_id,'_',cd2.category,'_',cd1.category),
'\n', ''), '', ''), '\r',''), null)) as show_metacategory,
max(IF(pc.link_type = 'M', cat.status, null)) as maincategory_status,
max(IF(pc.link_type = 'M', c1.status, null)) as metacategory_status,
max(IF(pc.link_type = 'M', REPLACE(cd2.category_path,'///','>'), null)) as
category_path, c.company as merchant, c.zone_type as zone,
CONCAT(c.lat,',',c.lng) as geolocation, CAST(SUBSTRING_INDEX(c.zipcode,
'-', -1) AS UNSIGNED) as zipcode,
REPLACE(REPLACE(REPLACE(CONCAT(c.company_id,'_',c.company), '\n', ''), '',
''), '\r','') as show_merchant, max(IF(pc.link_type = 'M',
REPLACE(REPLACE(REPLACE(CONCAT(cd1.category_id,'_',cd1.category), '\n',
''), '', ''), '\r',''), null)) as show_meta_id, max(IF(pc.link_type = 'M',
c1.include_in_search, null)) as include_in_search, c.is_ngo,
if(p.tracking='O',IF(sum(inv.amount)=0 OR sum(inv.amount) IS
NULL,0,1),IF(p.amount=0,0,1)) as product_amount_available, c.sdeep_rating
as merchant_rating, if(p.product_rating IS NULL,'0.00',p.product_rating) as
product_rating, popularity.total as popularity, popularity.bought as
bestsellers, promo.promotion_id, promo.compaign_tag_id as campaign_tag_id,
LOWER(REPLACE(promodesc.name,' ','_')) as promotion_name, c.fulfillment_id,
promo.supported_platform, promo.idms_campaign_id,
DATE_FORMAT(FROM_UNIXTIME(to_date),'%Y-%m-%dT %H:%i:%sZ') as to_date,
DATE_FORMAT(FROM_UNIXTIME(from_date),'%Y-%m-%dT %H:%i:%sZ') as from_date,
promo.promotion_type_id, promo.coupon_code, promo.cod_platform,
promo.number_of_usages, promo.allow_no_of_usages as allow_usages,
promo.stop as stop_other_rules, promo.once_per_customer, if(promo.to_date
>= UNIX_TIMESTAMP(),if(promo.status =
'A',CONCAT(promo.promotion_id,'_',REPLACE(REPLACE(promodesc.internal_name,'_','
'),'#',''),'#',promo.promotion_type_id,'-',promo.status,'-',promo.to_date),''),'')
as show_promotion, cm.market_id,
REPLACE(REPLACE(REPLACE(CONCAT(cm.market_id,'_',cm.seo_name), '\n', ''),
'', ''), '\r','') as show_market, p.storefront_featured_product,
p.tracking, p.free_shipping, p.shipping_freight, if(p.tracking='O',
sum(if(inv.amount IS NOT NULL ,inv.amount,0)), p.amount) as amount,
p.deals_index as deal_index, p.deals_index as deals_index, p.feature_index,
p.boost_index, p.is_cod, p.price_see_inside, p.deal_inside_badge,
c.neighbourhood_marketplace as is_neighbourhood_merchant,
p.neighbourhood_marketplace as is_neighbourhood_product, p.object_collate
as neighbourhood_tp_check, p.special_offer_badge, p.freebee_inside,
p.last_update, p.timestamp, p.status, p.manufacturer_reference_number,
p.is_wholesale_product, p.wholesale_type, p.min_qty, p.why_buy_reason_1,
p.why_buy_reason_2, p.master_id, p.is_master, p.anniversary_tag,
p.marketplace_product_visibility, c.marketplace_merchant_visibility,
max(if(pc.link_type = 'M', cat.marketplace_category_visibility, null)) as
marketplace_category_visibility, p.mobile_boost_index,
p.is_factory_outlet_product, p.outlet_brand_id as outlet_brand_id,
cob.brand_name as outlet_brand_name,
REPLACE(REPLACE(REPLACE(CONCAT(p.outlet_brand_id,'_',cob.brand_name), '\n',
''), '', ''), '\r','') as show_outlet_brand, outlet.outlet_status as
is_outlet_status, cob.status as is_cob_status, UNIX_TIMESTAMP() as
solr_update, c.status as company_status, c.company, c.is_trm, cspr.sort_1,
cspr.sort_2, if(p.special_offer_text IS NULL, '', p.special_offer_text) as
special_offer_text, REPLACE(REPLACE(REPLACE(pd.clean_full_description,
'\n', ''), '', ''), '\r','') as full_description,
REPLACE(REPLACE(REPLACE(pd.clean_short_description, '\n', ''), '', ''),
'\r','') as short_description FROM cscart_products p INNER JOIN
cscart_product_descriptions as pd ON pd.product_id = p.product_id and
pd.lang_code = 'EN' left join clues_search_promotion as cspr on
cspr.product_id=p.product_id LEFT JOIN cscart_product_prices as pp ON
pp.product_id = p.product_id LEFT JOIN cscart_companies AS c ON
c.company_id = p.company_id INNER JOIN cscart_products_categories as pc ON
pc.product_id = p.product_id INNER JOIN cscart_categories as cat ON
cat.category_id = pc.category_id AND cat.status IN ('A', 'H') LEFT JOIN
cscart_seo_names as ps ON ps.object_id = p.product_id AND ps.type = 'p'
LEFT JOIN cscart_product_popularity as popularity ON popularity.product_id
= p.product_id LEFT JOIN cscart_product_options_inventory as inv on
inv.product_id=p.product_id left JOIN cscart_category_descriptions cd1 on
cd1.category_id=SUBSTRING_INDEX(SUBSTRING_INDEX(id_path, '/',2), '/',1)
left JOIN cscart_category_descriptions cd2 ON cd2.category_id =
pc.category_id left JOIN cscart_categories c1 on c1.category_id =
cd1.category_id left JOIN cscart_images_links il ON il.object_id =
p.product_id and il.object_type = 'product' and il.type = 'M' left JOIN
cscart_images i ON il.detailed_id = i.image_id left join
cscart_product_features_values cpfv on cpfv.product_id=p.product_id and
cpfv.feature_id=53 left join cscart_product_feature_variant_descriptions
cpfvd on cpfvd.variant_id=cpfv.variant_id left JOIN cscart_seo_names pe ON
cpfv.variant_id = pe.object_id and pe.type = 'e' left join
clues_factory_outlet_apply outlet on outlet.company_id=c.company_id LEFT
join clues_outlet_brands cob on cob.brand_id=p.outlet_brand_id LEFT join
cscart_promotions promo on promo.promotion_id = p.promotion_id LEFT join
cscart_promotion_descriptions promodesc on promodesc.promotion_id =
promo.promotion_id LEFT join clues_markets cm ON POSITION(c.zipcode IN
cm.pincode) != 0 WHERE 1 AND p.status IN ('A','H') AND (c.status = 'A') and
p.product_id = '${dataimporter.delta.id}' group by p.product_id"
deltaQuery="SELECT p.product_id as id, REPLACE(REPLACE(REPLACE(pd.product,
'\n', ''), '', ''), '\r','') as product, pd.meta_keywords, pd.search_words,
pd.page_title, p.product_id, p.company_id, p.list_price as list_price,
min(pp.price) as price, p.third_price, pp.retail_price, IF(p.third_price !=
'0.00', ROUND(((p.list_price - p.third_price) / p.list_price) * 100),
ROUND(((p.list_price - pp.price) / p.list_price) * 100 )) as
discount_percentage, IF( p.third_price != '0.00', p.third_price, IF(
pp.price != '0.00', pp.price, p.list_price ) ) AS sort_price,
REPLACE(REPLACE(ps.name, '\n', ''), '\r', '') as seo_name, pe.name as
brand_url, CONCAT('images/thumbnails/',floor(if(i.image_id!=0,
i.image_id,il.image_id)/1000),'/320/320/',REPLACE(REPLACE(image_path, '\n',
''), '\r', '')) as image_url, cat.id_path as id_path, cpfv.variant_id as
brand_id, REPLACE(REPLACE(REPLACE(cpfvd.variant, '\n', ''), '', ''),
'\r','') as brand,
REPLACE(REPLACE(REPLACE(CONCAT(cpfv.variant_id,'_',cpfvd.variant), '\n',
''), '', ''), '\r','') as show_brand,
CONCAT(cpfv.variant_id,'_',c1.category_id) as suggest_brand,
GROUP_CONCAT(distinct(pc.category_id) SEPARATOR '/') as category_ids,
GROUP_CONCAT(distinct(pc.category_id) SEPARATOR '/') as cat_ids,
GROUP_CONCAT(distinct(c1.status) SEPARATOR '/') as category_status,
GROUP_CONCAT(distinct(c1.category_id) SEPARATOR '/') as metacategory_ids,
max(IF(pc.link_type = 'M', cd2.category_id, null)) as category_id,
max(IF(pc.link_type = 'M', REPLACE(REPLACE(REPLACE(cd2.category, '\n', ''),
'', ''), '\r',''), null)) as category, max(if(pc.link_type = 'M',
c1.category_id, null)) as metacategory_id, max(IF(pc.link_type = 'M',
REPLACE(REPLACE(REPLACE(cd1.category, '\n', ''), '', ''), '\r',''), null))
as metacategory, max(IF(pc.link_type = 'M',
REPLACE(REPLACE(REPLACE(CONCAT(cat.category_id,'_',cd2.category), '\n',
''), '', ''), '\r','') , null)) as show_category, max(IF(pc.link_type =
'M',
REPLACE(REPLACE(REPLACE(CONCAT(cat.category_id,'_',cd2.category,'_',cd1.category),
'\n', ''), '', ''), '\r',''), null)) as show_metacategory,
max(IF(pc.link_type = 'M', cat.status, null)) as maincategory_status,
max(IF(pc.link_type = 'M', c1.status, null)) as metacategory_status,
max(IF(pc.link_type = 'M', REPLACE(cd2.category_path,'///','>'), null)) as
category_path, c.company as merchant, c.zone_type as zone,
CONCAT(c.lat,',',c.lng) as geolocation, CAST(SUBSTRING_INDEX(c.zipcode,
'-', -1) AS UNSIGNED) as zipcode,
REPLACE(REPLACE(REPLACE(CONCAT(c.company_id,'_',c.company), '\n', ''), '',
''), '\r','') as show_merchant, max(IF(pc.link_type = 'M',
REPLACE(REPLACE(REPLACE(CONCAT(cd1.category_id,'_',cd1.category), '\n',
''), '', ''), '\r',''), null)) as show_meta_id, max(IF(pc.link_type = 'M',
c1.include_in_search, null)) as include_in_search, c.is_ngo,
if(p.tracking='O',IF(sum(inv.amount)=0 OR sum(inv.amount) IS
NULL,0,1),IF(p.amount=0,0,1)) as product_amount_available, c.sdeep_rating
as merchant_rating, if(p.product_rating IS NULL,'0.00',p.product_rating) as
product_rating, popularity.total as popularity, popularity.bought as
bestsellers, promo.promotion_id, promo.compaign_tag_id as campaign_tag_id,
LOWER(REPLACE(promodesc.name,' ','_')) as promotion_name, c.fulfillment_id,
promo.supported_platform, promo.idms_campaign_id,
DATE_FORMAT(FROM_UNIXTIME(to_date),'%Y-%m-%dT %H:%i:%sZ') as to_date,
DATE_FORMAT(FROM_UNIXTIME(from_date),'%Y-%m-%dT %H:%i:%sZ') as from_date,
promo.promotion_type_id, promo.coupon_code, promo.cod_platform,
promo.number_of_usages, promo.allow_no_of_usages as allow_usages,
promo.stop as stop_other_rules, promo.once_per_customer, if(promo.to_date
>= UNIX_TIMESTAMP(),if(promo.status =
'A',CONCAT(promo.promotion_id,'_',REPLACE(REPLACE(promodesc.internal_name,'_','
'),'#',''),'#',promo.promotion_type_id,'-',promo.status,'-',promo.to_date),''),'')
as show_promotion, cm.market_id,
REPLACE(REPLACE(REPLACE(CONCAT(cm.market_id,'_',cm.seo_name), '\n', ''),
'', ''), '\r','') as show_market, p.storefront_featured_product,
p.tracking, p.free_shipping, p.shipping_freight, if(p.tracking='O',
sum(if(inv.amount IS NOT NULL ,inv.amount,0)), p.amount) as amount,
p.deals_index as deal_index, p.deals_index as deals_index, p.feature_index,
p.boost_index, p.is_cod, p.price_see_inside, p.deal_inside_badge,
c.neighbourhood_marketplace as is_neighbourhood_merchant,
p.neighbourhood_marketplace as is_neighbourhood_product, p.object_collate
as neighbourhood_tp_check, p.special_offer_badge, p.freebee_inside,
p.last_update, p.timestamp, p.status, p.manufacturer_reference_number,
p.is_wholesale_product, p.wholesale_type, p.min_qty, p.why_buy_reason_1,
p.why_buy_reason_2, p.master_id, p.is_master, p.anniversary_tag,
p.marketplace_product_visibility, c.marketplace_merchant_visibility,
max(if(pc.link_type = 'M', cat.marketplace_category_visibility, null)) as
marketplace_category_visibility, p.mobile_boost_index,
p.is_factory_outlet_product, p.outlet_brand_id as outlet_brand_id,
cob.brand_name as outlet_brand_name,
REPLACE(REPLACE(REPLACE(CONCAT(p.outlet_brand_id,'_',cob.brand_name), '\n',
''), '', ''), '\r','') as show_outlet_brand, outlet.outlet_status as
is_outlet_status, cob.status as is_cob_status, UNIX_TIMESTAMP() as
solr_update, c.status as company_status, c.company, c.is_trm, cspr.sort_1,
cspr.sort_2, if(p.special_offer_text IS NULL, '', p.special_offer_text) as
special_offer_text, REPLACE(REPLACE(REPLACE(pd.clean_full_description,
'\n', ''), '', ''), '\r','') as full_description,
REPLACE(REPLACE(REPLACE(pd.clean_short_description, '\n', ''), '', ''),
'\r','') as short_description FROM cscart_products p INNER JOIN
cscart_product_descriptions as pd ON pd.product_id = p.product_id and
pd.lang_code = 'EN' left join clues_search_promotion as cspr on
cspr.product_id=p.product_id LEFT JOIN cscart_product_prices as pp ON
pp.product_id = p.product_id LEFT JOIN cscart_companies AS c ON
c.company_id = p.company_id INNER JOIN cscart_products_categories as pc ON
pc.product_id = p.product_id INNER JOIN cscart_categories as cat ON
cat.category_id = pc.category_id AND cat.status IN ('A', 'H') LEFT JOIN
cscart_seo_names as ps ON ps.object_id = p.product_id AND ps.type = 'p'
LEFT JOIN cscart_product_popularity as popularity ON popularity.product_id
= p.product_id LEFT JOIN cscart_product_options_inventory as inv on
inv.product_id=p.product_id left JOIN cscart_category_descriptions cd1 on
cd1.category_id=SUBSTRING_INDEX(SUBSTRING_INDEX(id_path, '/',2), '/',1)
left JOIN cscart_category_descriptions cd2 ON cd2.category_id =
pc.category_id left JOIN cscart_categories c1 on c1.category_id =
cd1.category_id left JOIN cscart_images_links il ON il.object_id =
p.product_id and il.object_type = 'product' and il.type = 'M' left JOIN
cscart_images i ON il.detailed_id = i.image_id left join
cscart_product_features_values cpfv on cpfv.product_id=p.product_id and
cpfv.feature_id=53 left join cscart_product_feature_variant_descriptions
cpfvd on cpfvd.variant_id=cpfv.variant_id left JOIN cscart_seo_names pe ON
cpfv.variant_id = pe.object_id and pe.type = 'e' left join
clues_factory_outlet_apply outlet on outlet.company_id=c.company_id LEFT
join clues_outlet_brands cob on cob.brand_id=p.outlet_brand_id LEFT join
cscart_promotions promo on promo.promotion_id = p.promotion_id LEFT join
cscart_promotion_descriptions promodesc on promodesc.promotion_id =
promo.promotion_id LEFT join clues_markets cm ON POSITION(c.zipcode IN
cm.pincode) != 0 WHERE 1 AND p.status IN ('A','H') AND (c.status = 'A') AND
( p.last_update >= '${dih.last_index_time}' OR c.trigger_update >=
'${dih.last_index_time}' OR cat.trigger_update >= '${dih.last_index_time}'
OR inv.last_update >= '${dih.last_index_time}' OR cd1.trigger_update >=
'${dih.last_index_time}' OR ps.last_update >= '${dih.last_index_time}')
group by p.product_id" deletedPkQuery="SELECT p.product_id as id FROM
cscart_products p LEFT JOIN cscart_products_categories pc ON p.product_id =
pc.product_id and link_type = 'M' LEFT JOIN cscart_companies c ON
c.company_id = p.company_id LEFT JOIN cscart_categories cat ON
cat.category_id = pc.category_id WHERE ( p.status NOT IN ('A','H') OR
c.status != 'A' ) AND ( p.last_update >= '${dataimporter.last_index_time}'
OR c.updated_at >= '${dataimporter.last_index_time}') " > <entity name=
"feature" transformer="script:addfield" query="SELECT pf.feature_id,
REPLACE(REPLACE(REPLACE(pfd.filter, '\n', ''), '', ''), '\r','') as
COLcolor_col, pfv.variant_id,
CONCAT(pfv.variant_id,'_',(REPLACE(REPLACE(REPLACE(fvd.variant, '\n', ''),
'', ''), '\r',''))) as COLcolor_col_val FROM cscart_product_filters pf
inner join cscart_product_filter_descriptions pfd on pfd.filter_id =
pf.filter_id inner join cscart_categories c on find_in_set (c.category_id,
pf.categories_path) inner join cscart_products_categories pc on
pc.category_id = c.category_id left join cscart_product_features_values pfv
on pfv.feature_id = pf.feature_id and pfv.product_id = pc.product_id left
join cscart_product_feature_variant_descriptions fvd on fvd.variant_id =
pfv.variant_id where pf.feature_id != 53 and pc.product_id = '${products.id}'
and pf.status = 'A' order by pf.position asc"> </entity> <entity name=
"option" transformer="script:addfield" query="select distinct
ov.variant_id,
REPLACE(REPLACE(REPLACE(REPLACE(CONCAT('o_',LOWER(trim(od.option_name))), '
','_'), '\'',''), '.',''), '/', '_or_') AS COLcolor_col, CONCAT(
od.option_id,'-',ov.variant_id,'_',(REPLACE(REPLACE(REPLACE(vd.variant_name,
'\n', ''), '', ''), '\r','')) ) AS COLcolor_col_val FROM
cscart_product_global_option_links g INNER JOIN
cscart_product_option_variants ov ON ov.option_id = g.option_id INNER JOIN
cscart_product_option_variants_descriptions vd ON vd.variant_id =
ov.variant_id INNER JOIN cscart_product_options_descriptions od ON
od.option_id = ov.option_id INNER JOIN cscart_product_options_inventory inv
ON g.product_id = inv.product_id where inv.product_id=${products.id} AND
inv.amount > 0 AND ov.variant_id IN
(SUBSTRING_INDEX(SUBSTRING_INDEX(inv.combination,'_',2),'_',-1),
SUBSTRING_INDEX(SUBSTRING_INDEX(inv.combination,'_',4),'_',-1),
SUBSTRING_INDEX(SUBSTRING_INDEX(inv.combination,'_',6),'_',-1),
SUBSTRING_INDEX(SUBSTRING_INDEX(inv.combination,'_',8),'_',-1))"> </entity>
<entity name="combination" query="select
product_id,CONCAT(REPLACE(combination,'_','-'),'_',amount) as
combination_inv from cscart_product_options_inventory where product_id = ${
products.id} AND amount > 0"> </entity> <field column="cat_ids" splitBy="/"
sourceColName="cat_ids"/> <field column="metacategory_ids" splitBy="/"
sourceColName="metacategory_ids"/> <field column=
"marketplace_product_visibility" splitBy="/" sourceColName=
"marketplace_product_visibility"/> <field column="id_path_ids" splitBy="/"
sourceColName="id_path"/> <field column="marketplace_category_visibility"
splitBy="/" sourceColName="marketplace_category_visibility"/> <field column=
"marketplace_merchant_visibility" splitBy="/" sourceColName=
"marketplace_merchant_visibility"/> </entity> </document> </dataConfig>
On Fri, Nov 27, 2015 at 12:04 PM, Gora Mohanty <g...@mimirtech.com> wrote:
> On 27 November 2015 at 11:48, Midas A <test.mi...@gmail.com> wrote:
> > Big Integer part is coming from mysql .
>
> So, show us that part of the DIH config. As Alex mentioned, most
> likely there is some error in the conversion: DIH seems to be getting
> a string rather than a BigInteger.
>
> > use of
> transformer add latency
> > while indexing ?
>
> No, this is unlikely to have anything to do with latency.
>
> Regards,
> Gora
>
