Ingest URL index pipeline

​

The following sequence of steps illustrates the URL indexing pipeline implemented in Apache NiFi. Extensive example samples are available for each stage. These are provided in the bottom sections of this topic. The examples include SQL code, schema table information, and sample inputs and outputs. The flow consists of mainly seven stages:

1. Create Category URL ​​document​​​​​​​​​​​​​​​​​
2. Create Product URL ​​document​​​​​​​​​​​​​​​​​
3. Update Page Type for Category URL document
4. Update Category URL for fallback language
5. Update Product URL for fallback language
6. Update SEO URL to Category document
7. Update SEO URL to Product document

This stage describes how Category SEO URL data can be transformed and loaded into the URL index. It starts with running the following SQL to retrieve category data from the Commerce database:

 (SELECT A.SEOURL_ID, B.SEOURLKEYWORD_ID, B.URLKEYWORD, B.MOBILEURLKEYWORD, B.STOREENT_ID, B.STATUS,
		       B.LANGUAGE_ID, C.SEOURLKWD_ID_NEW, D.URLKEYWORD URLKEYWORD_NEW, A.TOKENNAME, A.TOKENVALUE, L.LOCALENAME,
		       P.DISPLAYNAME, P.DESCRIPTION, P.CATGROUP_ID, P.NAME, P.SHORTDESCRIPTION, P.KEYWORD,
		       P.SITE_SEOPAGEDEF_ID, P.SITE_PAGENAME, P.SITE_TITLE, P.SITE_META_DESC, P.SITE_META_KEYWORD,
		       P.SITE_IMAGE_ALT_DESC, P.SITE_OBJECT_ID, P.SITE_STOREENT_ID, P.IDENTIFIER,
	           P.OVERRIDE_SEOPAGEDEF_ID, P.OVERRIDE_PAGENAME, P.OVERRIDE_TITLE, P.OVERRIDE_META_DESC,
	           P.OVERRIDE_META_KEYWORD, P.OVERRIDE_IMAGE_ALT_DESC, P.OVERRIDE_OBJECT_ID, TO_CHAR(P.OVERRIDE_STOREENT_ID) OVERRIDE_STOREENT_ID
		  FROM SEOURL A, SEOURLKEYWORD B
			   LEFT OUTER JOIN SEOREDIRECT C ON B.SEOURLKEYWORD_ID=C.SEOURLKWD_ID_ORIG
			   LEFT OUTER JOIN SEOURLKEYWORD D ON C.SEOURLKWD_ID_NEW=D.SEOURLKEYWORD_ID
			   LEFT OUTER JOIN LANGUAGE L ON B.LANGUAGE_ID = L.LANGUAGE_ID
			   LEFT OUTER JOIN (
					SELECT COALESCE(S.DISPLAYNAME, SD.DISPLAYNAME) DISPLAYNAME,
					       COALESCE(S.DESCRIPTION, SD.DESCRIPTION) DESCRIPTION, 
					       CG.IDENTIFIER, CD.CATGROUP_ID, CD.NAME, CD.KEYWORD, CD.SHORTDESCRIPTION,
					       E.SEOPAGEDEF_ID SITE_SEOPAGEDEF_ID,
					       E.PAGENAME SITE_PAGENAME,
					       E.TITLE SITE_TITLE,
					       E.META_DESC SITE_META_DESC,
					       E.META_KEYWORD SITE_META_KEYWORD,
					       E.IMAGE_ALT_DESC SITE_IMAGE_ALT_DESC,
					       E.OBJECT_ID SITE_OBJECT_ID,
					       E.STOREENT_ID SITE_STOREENT_ID,
					       F.SEOPAGEDEF_ID OVERRIDE_SEOPAGEDEF_ID,
					       F.PAGENAME OVERRIDE_PAGENAME,
					       F.TITLE OVERRIDE_TITLE,
					       F.META_DESC OVERRIDE_META_DESC,
					       F.META_KEYWORD OVERRIDE_META_KEYWORD,
					       F.IMAGE_ALT_DESC OVERRIDE_IMAGE_ALT_DESC,
					       F.OBJECT_ID OVERRIDE_OBJECT_ID,
					       F.STOREENT_ID OVERRIDE_STOREENT_ID
					  FROM STOREENTDS S
					       INNER JOIN STORE ST ON S.STOREENT_ID=ST.STORE_ID
					       INNER JOIN CATGRPDESC CD ON S.LANGUAGE_ID=CD.LANGUAGE_ID ${extCatgroupAndSQL1b}
						   INNER JOIN CATGROUP CG ON CG.CATGROUP_ID=CD.CATGROUP_ID
						   ${TI_DELTA_CG_URL_JOIN_QUERY}
					       INNER JOIN STOREENTDS SD ON ST.STORE_ID=SD.STOREENT_ID
					       LEFT OUTER JOIN (
					       		SELECT SC.OBJECT_ID OBJECT_ID,
					       			   SA.SEOPAGEDEF_ID SEOPAGEDEF_ID,
					       			   SA.PAGENAME PAGENAME,
					       			   SB.TITLE TITLE,
					       			   SB.META_DESC META_DESC,
					       			   SB.META_KEYWORD META_KEYWORD,
					       			   SB.IMAGE_ALT_DESC IMAGE_ALT_DESC,
					       			   SA.STOREENT_ID STOREENT_ID
					              FROM SEOPAGEDEF SA, SEOPAGEDEFDESC SB, SEOPAGEDEFOVR SC
					 			 WHERE SA.SEOPAGEDEF_ID=SB.SEOPAGEDEF_ID AND SA.SEOPAGEDEF_ID=SC.SEOPAGEDEF_ID
					   			   AND SA.STOREENT_ID IN (SELECT RELATEDSTORE_ID FROM STOREREL
					   			   					      WHERE STORE_ID=${param.storeId} AND STRELTYP_ID=-4)
					   			   AND SB.LANGUAGE_ID=${param.langId}
					   			   AND SC.OBJECTTYPE = 'CatalogGroup') E ON E.OBJECT_ID = 0
					       LEFT OUTER JOIN (
					       		SELECT SC.OBJECT_ID OBJECT_ID,
					       			   SA.SEOPAGEDEF_ID SEOPAGEDEF_ID,
					       			   SA.PAGENAME PAGENAME,
					       			   SB.TITLE TITLE,
					       			   SB.META_DESC META_DESC,
					       			   SB.META_KEYWORD META_KEYWORD,
					       			   SB.IMAGE_ALT_DESC IMAGE_ALT_DESC,
					       			   SA.STOREENT_ID STOREENT_ID
					              FROM SEOPAGEDEF SA, SEOPAGEDEFDESC SB, SEOPAGEDEFOVR SC
					 			 WHERE SA.SEOPAGEDEF_ID=SB.SEOPAGEDEF_ID AND SA.SEOPAGEDEF_ID=SC.SEOPAGEDEF_ID
					   			   AND SA.STOREENT_ID IN (SELECT RELATEDSTORE_ID FROM STOREREL
					   			   					      WHERE STORE_ID=${param.storeId} AND STRELTYP_ID=-4)
					   			   AND SB.LANGUAGE_ID=${param.langId}
					   			   AND SC.OBJECTTYPE = 'CatalogGroup') F ON F.OBJECT_ID = CD.CATGROUP_ID
					 WHERE ST.STORE_ID=${param.storeId}
					   AND (( S.STOREENT_ID=ST.STORE_ID AND S.LANGUAGE_ID=${param.langId} )
					    OR  ( SD.LANGUAGE_ID=ST.LANGUAGE_ID ))
			   ) P ON P.CATGROUP_ID IN (
				 SELECT C.CATGROUP_ID
				   FROM CATGRPREL R, CATGROUP C
				  WHERE R.CATALOG_ID = ${param.catalogId}
				    AND R.CATALOG_ID IN (SELECT CATALOG_ID FROM STORECAT WHERE STOREENT_ID IN
				        (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))
				    AND R.CATGROUP_ID_CHILD = C.CATGROUP_ID AND C.MARKFORDELETE = 0
				  UNION
				 SELECT C.CATGROUP_ID
				   FROM CATTOGRP R, CATGROUP C
				  WHERE R.CATALOG_ID = ${param.catalogId}
				    AND R.CATALOG_ID IN (SELECT CATALOG_ID FROM STORECAT WHERE STOREENT_ID IN
				        (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))
				    AND R.CATGROUP_ID = C.CATGROUP_ID AND C.MARKFORDELETE = 0)
		 WHERE B.STOREENT_ID IN
		       (SELECT RELATEDSTORE_ID FROM STOREREL E WHERE E.STORE_ID=${param.storeId} AND E.STRELTYP_ID=-4)
		   AND A.SEOURL_ID=B.SEOURL_ID AND B.LANGUAGE_ID=${param.langId} AND A.TOKENNAME IN ('CategoryToken')
		   AND A.TOKENVALUE = P.CATGROUP_ID) ORDER BY SEOURL_ID 
                 OFFSET ${param.offset} ROWS FETCH NEXT ${param.pageSize} ROWS ONLY

Next, the result set is passed to the CreateCategorytURLDocumentFromDatabase​ processor for transformation, using the following table to ​map the database field returned from the SQL above to an index field in the URL index:​ For example code, see stage 1 samples.

It starts with running the following SQL to retrieve product data from the Commerce database:

 (SELECT A.SEOURL_ID, B.SEOURLKEYWORD_ID, B.URLKEYWORD, B.MOBILEURLKEYWORD, B.STOREENT_ID, B.STATUS,
			B.LANGUAGE_ID, C.SEOURLKWD_ID_NEW, D.URLKEYWORD URLKEYWORD_NEW, A.TOKENNAME, A.TOKENVALUE, L.LOCALENAME,
			P.DISPLAYNAME, P.DESCRIPTION, P.CATENTRY_ID, P.NAME, P.SHORTDESCRIPTION,
			P.KEYWORD, P.PARTNUMBER, P.MFNAME, P.MFPARTNUMBER, P.CATENTTYPE_ID,
			P.SITE_SEOPAGEDEF_ID, P.SITE_PAGENAME, P.SITE_TITLE, P.SITE_META_DESC, P.SITE_META_KEYWORD,
			P.SITE_IMAGE_ALT_DESC, P.SITE_OBJECT_ID, P.SITE_STOREENT_ID,
			P.OVERRIDE_SEOPAGEDEF_ID, P.OVERRIDE_PAGENAME, P.OVERRIDE_TITLE, P.OVERRIDE_META_DESC,
			P.OVERRIDE_META_KEYWORD, P.OVERRIDE_IMAGE_ALT_DESC, P.OVERRIDE_OBJECT_ID, P.OVERRIDE_STOREENT_ID
	      FROM SEOURL A, SEOURLKEYWORD B
			LEFT OUTER JOIN SEOREDIRECT C ON B.SEOURLKEYWORD_ID=C.SEOURLKWD_ID_ORIG
			LEFT OUTER JOIN SEOURLKEYWORD D ON C.SEOURLKWD_ID_NEW=D.SEOURLKEYWORD_ID
			LEFT OUTER JOIN LANGUAGE L ON B.LANGUAGE_ID = L.LANGUAGE_ID
			LEFT OUTER JOIN (
				 SELECT COALESCE(S.DISPLAYNAME, SD.DISPLAYNAME) DISPLAYNAME,
						COALESCE(S.DESCRIPTION, SD.DESCRIPTION) DESCRIPTION,
						CE.CATENTRY_ID, CD.NAME, CD.SHORTDESCRIPTION, CE.CATENTTYPE_ID,
						CD.KEYWORD, CE.PARTNUMBER, CE.MFNAME, CE.MFPARTNUMBER,
						E.SEOPAGEDEF_ID SITE_SEOPAGEDEF_ID,
						E.PAGENAME SITE_PAGENAME,
						E.TITLE SITE_TITLE,
						E.META_DESC SITE_META_DESC,
						E.META_KEYWORD SITE_META_KEYWORD,
						E.IMAGE_ALT_DESC SITE_IMAGE_ALT_DESC,
						E.OBJECT_ID SITE_OBJECT_ID,
						E.STOREENT_ID SITE_STOREENT_ID,
						F.SEOPAGEDEF_ID OVERRIDE_SEOPAGEDEF_ID,
						F.PAGENAME OVERRIDE_PAGENAME,
						F.TITLE OVERRIDE_TITLE,
						F.META_DESC OVERRIDE_META_DESC,
						F.META_KEYWORD OVERRIDE_META_KEYWORD,
						F.IMAGE_ALT_DESC OVERRIDE_IMAGE_ALT_DESC,
						F.OBJECT_ID OVERRIDE_OBJECT_ID,
						F.STOREENT_ID OVERRIDE_STOREENT_ID
				   FROM STOREENTDS S
						INNER JOIN STORE ST ON S.STOREENT_ID=ST.STORE_ID
						INNER JOIN CATENTDESC CD ON S.LANGUAGE_ID=CD.LANGUAGE_ID ${extCatentryAndSQL1b}
						INNER JOIN CATENTRY CE ON CD.CATENTRY_ID=CE.CATENTRY_ID
						${TI_DELTA_JOIN_QUERY_ATTCH}
						INNER JOIN STOREENTDS SD ON ST.STORE_ID=SD.STOREENT_ID
						LEFT OUTER JOIN (
								SELECT SC.OBJECT_ID OBJECT_ID,
									   SA.SEOPAGEDEF_ID SEOPAGEDEF_ID,
									   SA.PAGENAME PAGENAME,
									   SB.TITLE TITLE,
									   SB.META_DESC META_DESC,
									   SB.META_KEYWORD META_KEYWORD,
									   SB.IMAGE_ALT_DESC IMAGE_ALT_DESC,
									   SA.STOREENT_ID STOREENT_ID
							   FROM SEOPAGEDEF SA, SEOPAGEDEFDESC SB, SEOPAGEDEFOVR SC
							   WHERE SA.SEOPAGEDEF_ID=SB.SEOPAGEDEF_ID AND SA.SEOPAGEDEF_ID=SC.SEOPAGEDEF_ID
								   AND SA.STOREENT_ID IN (SELECT RELATEDSTORE_ID FROM STOREREL
															 WHERE STORE_ID=${param.storeId} AND STRELTYP_ID=-4)
								   AND SB.LANGUAGE_ID=${param.langId}
								   AND SC.OBJECTTYPE = 'CatalogEntry') E ON E.OBJECT_ID = 0
						LEFT OUTER JOIN (
								SELECT SC.OBJECT_ID OBJECT_ID,
									   SA.SEOPAGEDEF_ID SEOPAGEDEF_ID,
									   SA.PAGENAME PAGENAME,
									   SB.TITLE TITLE,
									   SB.META_DESC META_DESC,
									   SB.META_KEYWORD META_KEYWORD,
									   SB.IMAGE_ALT_DESC IMAGE_ALT_DESC,
									   SA.STOREENT_ID STOREENT_ID
							   FROM SEOPAGEDEF SA, SEOPAGEDEFDESC SB, SEOPAGEDEFOVR SC
							   WHERE SA.SEOPAGEDEF_ID=SB.SEOPAGEDEF_ID AND SA.SEOPAGEDEF_ID=SC.SEOPAGEDEF_ID
								   AND SA.STOREENT_ID IN (SELECT RELATEDSTORE_ID FROM STOREREL
															 WHERE STORE_ID=${param.storeId} AND STRELTYP_ID=-4)
								   AND SB.LANGUAGE_ID=${param.langId}
								   AND SC.OBJECTTYPE = 'CatalogEntry') F ON F.OBJECT_ID = CE.CATENTRY_ID
				  WHERE ST.STORE_ID=${param.storeId}
					AND (( S.STOREENT_ID=ST.STORE_ID AND S.LANGUAGE_ID=${param.langId} )
					 OR  ( SD.LANGUAGE_ID=ST.LANGUAGE_ID ))
			) P ON P.CATENTRY_ID IN (
			  SELECT C.CATENTRY_ID
				FROM CATGPENREL R, CATENTRY C
			   WHERE R.CATALOG_ID = ${param.catalogId} AND R.CATALOG_ID IN (SELECT CATALOG_ID FROM STORECAT WHERE STOREENT_ID IN
					 (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))
				 AND R.CATENTRY_ID = C.CATENTRY_ID AND C.MARKFORDELETE = 0
				 AND C.CATENTTYPE_ID IN ('ProductBean', 'ItemBean', 'VariantBean'))
	     WHERE B.STOREENT_ID IN
			(SELECT RELATEDSTORE_ID FROM STOREREL E WHERE E.STORE_ID=${param.storeId} AND E.STRELTYP_ID=-4)
		AND A.SEOURL_ID=B.SEOURL_ID AND B.LANGUAGE_ID=${param.langId} AND A.TOKENNAME IN ('ProductToken')
		AND A.TOKENVALUE = P.CATENTRY_ID) ORDER BY SEOURL_ID
             OFFSET ${param.offset} ROWS FETCH NEXT ${param.pageSize} ROWS ONLY

It starts with running the following SQL to retrieve product data from the Commerce database:

SELECT B.SEOURLKEYWORD_ID		       
		  FROM SEOURL A, SEOURLKEYWORD B
		 WHERE B.STOREENT_ID IN
		       (SELECT RELATEDSTORE_ID FROM STOREREL E WHERE E.STORE_ID=${param.storeId} AND E.STRELTYP_ID=-4)
		   AND A.SEOURL_ID=B.SEOURL_ID AND B.LANGUAGE_ID=${param.langId} AND A.TOKENNAME IN ('CategoryToken')
		   AND A.TOKENVALUE IN (
				 SELECT P.CATGROUP_ID 
				   FROM CATGPENREL P,
						(SELECT G.CATGROUP_ID, R.CATALOG_ID
						   FROM CATGRPREL R, CATGROUP G
						  WHERE R.CATALOG_ID = ${param.catalogId}
						    AND R.CATALOG_ID IN (SELECT CATALOG_ID FROM STORECAT WHERE STOREENT_ID IN
									(SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))
						    AND R.CATGROUP_ID_CHILD = G.CATGROUP_ID AND G.MARKFORDELETE = 0
						  UNION
						 SELECT G.CATGROUP_ID, R.CATALOG_ID
						   FROM CATTOGRP R, CATGROUP G
						  WHERE R.CATALOG_ID = ${param.catalogId}
						    AND R.CATALOG_ID IN (SELECT CATALOG_ID FROM STORECAT WHERE STOREENT_ID IN
									(SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))
						    AND R.CATGROUP_ID = G.CATGROUP_ID AND G.MARKFORDELETE = 0) H
				 ${TI_DELTA_CG_URL_1C_JOIN_QUERY}
				 WHERE P.CATALOG_ID = H.CATALOG_ID AND P.CATGROUP_ID = H.CATGROUP_ID ${extCatgroupAndSQL1a}
			     UNION
                SELECT C.CATGROUP_ID
				  FROM CATGRPREL R, CATGROUP C
				 ${TI_DELTA_CG_JOIN_QUERY}
				 WHERE R.CATALOG_ID = ${param.catalogId} ${extCatgroupAndSQL}
				   AND R.CATALOG_ID IN (SELECT CATALOG_ID FROM STORECAT WHERE STOREENT_ID IN
						   (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))
				   AND R.CATGROUP_ID_CHILD = C.CATGROUP_ID AND C.MARKFORDELETE = 0 AND C.DYNAMIC = 1)
               ORDER BY B.SEOURLKEYWORD_ID
               OFFSET ${param.offset} ROWS FETCH NEXT ${param.pageSize} ROWS ONLY

Next, the result set is passed to the UpdatePageTypeFromDatabase​​ processor for transformation, using the following table to ​map the database field returned from the SQL above to an index field in the URL index:​​For example code, see stage 3 samples.

This stage describes how Category SEO URL data for fallback langague can be transformed and loaded into the URL index.​ It starts with running the following Elasticsearch query against the URL index:

SELECT S.TOKENVALUE, SD.URLKEYWORD FROM SEOURL S, SEOURLKEYWORD SD 
			where S.SEOURL_ID IN(SELECT A.SEOURL_ID FROM SEOURL A
	           LEFT OUTER JOIN SEOURLKEYWORD B ON (A.SEOURL_ID=B.SEOURL_ID AND B.LANGUAGE_ID=${param.langId}
	                                                AND B.STOREENT_ID IN
	                                                    (SELECT RELATEDSTORE_ID FROM STOREREL E WHERE E.STORE_ID=${param.storeId} AND E.STRELTYP_ID=-4))
			   LEFT OUTER JOIN SEOREDIRECT C ON B.SEOURLKEYWORD_ID=C.SEOURLKWD_ID_ORIG
			   LEFT OUTER JOIN SEOURLKEYWORD D ON C.SEOURLKWD_ID_NEW=D.SEOURLKEYWORD_ID
			   LEFT OUTER JOIN LANGUAGE L ON B.LANGUAGE_ID = L.LANGUAGE_ID
			   LEFT OUTER JOIN (
					SELECT COALESCE(S.DISPLAYNAME, SD.DISPLAYNAME) DISPLAYNAME,
					       COALESCE(S.DESCRIPTION, SD.DESCRIPTION) DESCRIPTION, 
					       CG.IDENTIFIER, CD.CATGROUP_ID, CD.NAME, CD.KEYWORD, CD.SHORTDESCRIPTION,
					       E.SEOPAGEDEF_ID SITE_SEOPAGEDEF_ID,
					       E.PAGENAME SITE_PAGENAME,
					       E.TITLE SITE_TITLE,
					       E.META_DESC SITE_META_DESC,
					       E.META_KEYWORD SITE_META_KEYWORD,
					       E.IMAGE_ALT_DESC SITE_IMAGE_ALT_DESC,
					       E.OBJECT_ID SITE_OBJECT_ID,
					       E.STOREENT_ID SITE_STOREENT_ID,
					       F.SEOPAGEDEF_ID OVERRIDE_SEOPAGEDEF_ID,
					       F.PAGENAME OVERRIDE_PAGENAME,
					       F.TITLE OVERRIDE_TITLE,
					       F.META_DESC OVERRIDE_META_DESC,
					       F.META_KEYWORD OVERRIDE_META_KEYWORD,
					       F.IMAGE_ALT_DESC OVERRIDE_IMAGE_ALT_DESC,
					       F.OBJECT_ID OVERRIDE_OBJECT_ID,
					       F.STOREENT_ID OVERRIDE_STOREENT_ID
					  FROM STOREENTDS S
					       INNER JOIN STORE ST ON S.STOREENT_ID=ST.STORE_ID
					       INNER JOIN CATGRPDESC CD ON S.LANGUAGE_ID=CD.LANGUAGE_ID ${extCatgroupAndSQL1b}
						   INNER JOIN CATGROUP CG ON CG.CATGROUP_ID=CD.CATGROUP_ID
						   ${TI_DELTA_CG_URL_JOIN_QUERY}
					       INNER JOIN STOREENTDS SD ON ST.STORE_ID=SD.STOREENT_ID
					       LEFT OUTER JOIN (
					       		SELECT SC.OBJECT_ID OBJECT_ID,
					       			   SA.SEOPAGEDEF_ID SEOPAGEDEF_ID,
					       			   SA.PAGENAME PAGENAME,
					       			   SB.TITLE TITLE,
					       			   SB.META_DESC META_DESC,
					       			   SB.META_KEYWORD META_KEYWORD,
					       			   SB.IMAGE_ALT_DESC IMAGE_ALT_DESC,
					       			   SA.STOREENT_ID STOREENT_ID
					              FROM SEOPAGEDEF SA, SEOPAGEDEFDESC SB, SEOPAGEDEFOVR SC
					 			 WHERE SA.SEOPAGEDEF_ID=SB.SEOPAGEDEF_ID AND SA.SEOPAGEDEF_ID=SC.SEOPAGEDEF_ID
					   			   AND SA.STOREENT_ID IN (SELECT RELATEDSTORE_ID FROM STOREREL
					   			   					      WHERE STORE_ID=${param.storeId} AND STRELTYP_ID=-4)
					   			   AND SB.LANGUAGE_ID=${param.langId}
					   			   AND SC.OBJECTTYPE = 'CatalogGroup') E ON E.OBJECT_ID = 0
					       LEFT OUTER JOIN (
					       		SELECT SC.OBJECT_ID OBJECT_ID,
					       			   SA.SEOPAGEDEF_ID SEOPAGEDEF_ID,
					       			   SA.PAGENAME PAGENAME,
					       			   SB.TITLE TITLE,
					       			   SB.META_DESC META_DESC,
					       			   SB.META_KEYWORD META_KEYWORD,
					       			   SB.IMAGE_ALT_DESC IMAGE_ALT_DESC,
					       			   SA.STOREENT_ID STOREENT_ID
					              FROM SEOPAGEDEF SA, SEOPAGEDEFDESC SB, SEOPAGEDEFOVR SC
					 			 WHERE SA.SEOPAGEDEF_ID=SB.SEOPAGEDEF_ID AND SA.SEOPAGEDEF_ID=SC.SEOPAGEDEF_ID
					   			   AND SA.STOREENT_ID IN (SELECT RELATEDSTORE_ID FROM STOREREL
					   			   					      WHERE STORE_ID=${param.storeId} AND STRELTYP_ID=-4)
					   			   AND SB.LANGUAGE_ID=${param.langId}
					   			   AND SC.OBJECTTYPE = 'CatalogGroup') F ON F.OBJECT_ID = CD.CATGROUP_ID
					 WHERE ST.STORE_ID=${param.storeId}
					   AND (( S.STOREENT_ID=ST.STORE_ID AND S.LANGUAGE_ID=${param.langId} )
					    OR  ( SD.LANGUAGE_ID=ST.LANGUAGE_ID ))
			   ) P ON P.CATGROUP_ID IN (
				 SELECT C.CATGROUP_ID
				   FROM CATGRPREL R, CATGROUP C
				  WHERE R.CATALOG_ID = ${param.catalogId}
				    AND R.CATALOG_ID IN (SELECT CATALOG_ID FROM STORECAT WHERE STOREENT_ID IN
				        (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))
				    AND R.CATGROUP_ID_CHILD = C.CATGROUP_ID AND C.MARKFORDELETE = 0
				  UNION
				 SELECT C.CATGROUP_ID
				   FROM CATTOGRP R, CATGROUP C
				  WHERE R.CATALOG_ID = ${param.catalogId}
				    AND R.CATALOG_ID IN (SELECT CATALOG_ID FROM STORECAT WHERE STOREENT_ID IN
				        (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))
				    AND R.CATGROUP_ID = C.CATGROUP_ID AND C.MARKFORDELETE = 0)
		 WHERE A.TOKENNAME IN ('CategoryToken')
		   AND A.TOKENVALUE = P.CATGROUP_ID AND B.SEOURLKEYWORD_ID IS NULL) 
		   AND S.SEOURL_ID = SD.SEOURL_ID AND SD.LANGUAGE_ID = ${default.language} ORDER BY S.SEOURL_ID
	OFFSET ${param.offset} ROWS FETCH NEXT ${param.pageSize} ROWS ONLY​

This stage describes how Product SEO URL data for fallback langague can be transformed and loaded into the URL index.​ It starts with running the following Elasticsearch query against the URL index:

 SELECT S.TOKENVALUE, SD.URLKEYWORD FROM SEOURL S, SEOURLKEYWORD SD 
	where S.SEOURL_ID IN(SELECT A.SEOURL_ID FROM SEOURL A
            LEFT OUTER JOIN SEOURLKEYWORD B ON (A.SEOURL_ID=B.SEOURL_ID AND B.LANGUAGE_ID=${param.langId}
                                                    AND B.STOREENT_ID IN
                                                        (SELECT RELATEDSTORE_ID FROM STOREREL E WHERE E.STORE_ID=${param.storeId} AND E.STRELTYP_ID=-4))
			LEFT OUTER JOIN SEOREDIRECT C ON B.SEOURLKEYWORD_ID=C.SEOURLKWD_ID_ORIG
			LEFT OUTER JOIN SEOURLKEYWORD D ON C.SEOURLKWD_ID_NEW=D.SEOURLKEYWORD_ID
			LEFT OUTER JOIN LANGUAGE L ON B.LANGUAGE_ID = L.LANGUAGE_ID
			LEFT OUTER JOIN (
				 SELECT COALESCE(S.DISPLAYNAME, SD.DISPLAYNAME) DISPLAYNAME,
						COALESCE(S.DESCRIPTION, SD.DESCRIPTION) DESCRIPTION,
						CE.CATENTRY_ID, CD.NAME, CD.SHORTDESCRIPTION, CE.CATENTTYPE_ID,
						CD.KEYWORD, CE.PARTNUMBER, CE.MFNAME, CE.MFPARTNUMBER,
						E.SEOPAGEDEF_ID SITE_SEOPAGEDEF_ID,
						E.PAGENAME SITE_PAGENAME,
						E.TITLE SITE_TITLE,
						E.META_DESC SITE_META_DESC,
						E.META_KEYWORD SITE_META_KEYWORD,
						E.IMAGE_ALT_DESC SITE_IMAGE_ALT_DESC,
						E.OBJECT_ID SITE_OBJECT_ID,
						E.STOREENT_ID SITE_STOREENT_ID,
						F.SEOPAGEDEF_ID OVERRIDE_SEOPAGEDEF_ID,
						F.PAGENAME OVERRIDE_PAGENAME,
						F.TITLE OVERRIDE_TITLE,
						F.META_DESC OVERRIDE_META_DESC,
						F.META_KEYWORD OVERRIDE_META_KEYWORD,
						F.IMAGE_ALT_DESC OVERRIDE_IMAGE_ALT_DESC,
						F.OBJECT_ID OVERRIDE_OBJECT_ID,
						F.STOREENT_ID OVERRIDE_STOREENT_ID
				   FROM STOREENTDS S
						INNER JOIN STORE ST ON S.STOREENT_ID=ST.STORE_ID
						INNER JOIN CATENTDESC CD ON S.LANGUAGE_ID=CD.LANGUAGE_ID ${extCatentryAndSQL1b}
						INNER JOIN CATENTRY CE ON CD.CATENTRY_ID=CE.CATENTRY_ID
						${TI_DELTA_JOIN_QUERY_ATTCH}
						INNER JOIN STOREENTDS SD ON ST.STORE_ID=SD.STOREENT_ID
						LEFT OUTER JOIN (
								SELECT SC.OBJECT_ID OBJECT_ID,
									   SA.SEOPAGEDEF_ID SEOPAGEDEF_ID,
									   SA.PAGENAME PAGENAME,
									   SB.TITLE TITLE,
									   SB.META_DESC META_DESC,
									   SB.META_KEYWORD META_KEYWORD,
									   SB.IMAGE_ALT_DESC IMAGE_ALT_DESC,
									   SA.STOREENT_ID STOREENT_ID
							   FROM SEOPAGEDEF SA, SEOPAGEDEFDESC SB, SEOPAGEDEFOVR SC
							   WHERE SA.SEOPAGEDEF_ID=SB.SEOPAGEDEF_ID AND SA.SEOPAGEDEF_ID=SC.SEOPAGEDEF_ID
								   AND SA.STOREENT_ID IN (SELECT RELATEDSTORE_ID FROM STOREREL
															 WHERE STORE_ID=${param.storeId} AND STRELTYP_ID=-4)
								   AND SB.LANGUAGE_ID=${param.langId}
								   AND SC.OBJECTTYPE = 'CatalogEntry') E ON E.OBJECT_ID = 0
						LEFT OUTER JOIN (
								SELECT SC.OBJECT_ID OBJECT_ID,
									   SA.SEOPAGEDEF_ID SEOPAGEDEF_ID,
									   SA.PAGENAME PAGENAME,
									   SB.TITLE TITLE,
									   SB.META_DESC META_DESC,
									   SB.META_KEYWORD META_KEYWORD,
									   SB.IMAGE_ALT_DESC IMAGE_ALT_DESC,
									   SA.STOREENT_ID STOREENT_ID
							   FROM SEOPAGEDEF SA, SEOPAGEDEFDESC SB, SEOPAGEDEFOVR SC
							   WHERE SA.SEOPAGEDEF_ID=SB.SEOPAGEDEF_ID AND SA.SEOPAGEDEF_ID=SC.SEOPAGEDEF_ID
								   AND SA.STOREENT_ID IN (SELECT RELATEDSTORE_ID FROM STOREREL
															 WHERE STORE_ID=${param.storeId} AND STRELTYP_ID=-4)
								   AND SB.LANGUAGE_ID=${param.langId}
								   AND SC.OBJECTTYPE = 'CatalogEntry') F ON F.OBJECT_ID = CE.CATENTRY_ID
				  WHERE ST.STORE_ID=${param.storeId}
					AND (( S.STOREENT_ID=ST.STORE_ID AND S.LANGUAGE_ID=${param.langId} )
					 OR  ( SD.LANGUAGE_ID=ST.LANGUAGE_ID ))
			) P ON P.CATENTRY_ID IN (
			  SELECT C.CATENTRY_ID
				FROM CATGPENREL R, CATENTRY C
			   WHERE R.CATALOG_ID = ${param.catalogId} AND R.CATALOG_ID IN (SELECT CATALOG_ID FROM STORECAT WHERE STOREENT_ID IN
					 (SELECT RELATEDSTORE_ID FROM STOREREL WHERE STATE = 1 AND STRELTYP_ID = -4 AND STORE_ID = ${param.storeId}))
				 AND R.CATENTRY_ID = C.CATENTRY_ID AND C.MARKFORDELETE = 0
				 AND C.CATENTTYPE_ID IN ('ProductBean', 'ItemBean', 'VariantBean'))
	  WHERE A.TOKENNAME IN ('ProductToken')
		AND A.TOKENVALUE = P.CATENTRY_ID AND B.SEOURLKEYWORD_ID IS NULL) 
		AND S.SEOURL_ID = SD.SEOURL_ID AND SD.LANGUAGE_ID = ${default.language} ORDER BY S.SEOURL_ID 
		OFFSET ${param.offset} ROWS FETCH NEXT ${param.pageSize} ROWS ONLY

This stage describes how the SEO URL data can be retrieved from the URL index and then load back into the Category index. It starts with running the following Elasticsearch query against the URL index:

        {
	    "stored_fields": [
	        "identifier.url",
	        "token.id"
	    ],
	    "size": 10000,
	    "_source": false,
	    "query": {
	        "bool": {
	            "must_not": [
	                {
	                    "exists": {
	                        "field": "redirect"
	                    }
	                }
	            ],
	            "must": [
			{
			"terms": {
			    "token.name": [ "CategoryToken" ]
			}
	​		},
	                {
	                    "term": {
	                        "id.language": ${param.langId}
	                    }
	                },
	                {
	                    "term": {
	                        "id.store": ${param.storeId}
	                    }
	                }
	            ]
	        }
	    }
	}

Next, the result set is passed to the UpdateCategoryURL​ processor for transformation, using the following table to ​map the database field returned from the SQL above to an index field in the Product index:​​ For example code, see stage 6 samples.

This stage describes how the SEO URL data can be retrieved from the URL index and then load back into the Product index. It starts with running the following Elasticsearch query against the URL index:​

       ​{
	    "stored_fields": [
	        "identifier.url",
	        "token.id"
	    ],
	    "size": 10000,
	    "_source": false,
	    "query": {
	        "bool": {
	            "must_not": [
	                {
	                    "exists": {
	                        "field": "redirect"
	                    }
	                }
	            ],
	            "must": [
			{
			    "terms": {
				"token.name": [ "ProductToken"​ ]
			    }
			},
	                {
	                    "term": {
	                        "id.language": ${param.langId}
	                    }
	                },
	                {
	                    "term": {
	                        "id.store": ${param.storeId}
	                    }
	                }
	            ]
	        }
	    }
	}

Next, the result set is passed to the UpdateProductURL​ processor for transformation, using the following table to ​map the database field returned from the SQL above to an index field in the Product index:​​ For example code, see stage 7 samples.