Canalizaciones de índice de URL de Ingest

​

La siguiente secuencia de pasos ilustra la canalización de indexación de URL implementada en Apache NiFi. Hay disponibles amplios ejemplos para cada etapa. Se proporcionan en las secciones inferiores de este tema. Los ejemplos incluyen código SQL, información de la tabla de esquema y entradas y salidas de ejemplo. El flujo consta principalmente de siete etapas:

1. Crear documento de URL de categoría
2. Crear el documento de URL de producto
3. Actualizar tipo de página para documento de URL de categoría
4. Actualizar URL de categoría para el lenguaje de reserva
5. Actualizar URL de producto para el lenguaje de reserva
6. Actualizar URL de SEO al documento de categoría
7. Actualizar URL de SEO al documento de producto

Esta etapa describe cómo los datos de URL de SEO de categoría se pueden transformar y cargar en el índice de la URL. Empieza con la ejecución del siguiente SQL para recuperar datos de categoría de la base de datos de Commerce:

 (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

A continuación, el conjunto de resultados se pasa al​ procesador CreateCategorytURLDocumentFromDatabase para su transformación, utilizando la tabla siguiente para ​correlacionar el campo de base de datos devuelto desde el SQL anterior con un campo de índice en el índice de URL:​ Por ejemplo, consulte los ejemplos de la etapa 1.

Empieza con la ejecución del SQL siguiente para recuperar datos de producto de la base de datos de Commerce:

 (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

Empieza con la ejecución del SQL siguiente para recuperar datos de producto de la base de datos de Commerce:

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

A continuación, el conjunto de resultados se pasa al​​ procesador UpdatePageTypeFromDatabase para su transformación, utilizando la tabla siguiente para ​correlacionar el campo de base de datos devuelto desde el SQL anterior con un campo de índice en el índice de URL:​​Por ejemplo, consulte los ejemplos de la etapa 3.

En esta etapa se describe cómo se pueden transformar y cargar los datos de URL DEO de categoría para el lenguaje de reserva en el índice de URL. Empieza con la ejecución de la consulta Elasticsearch siguiente en el índice de URL:

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​

En esta etapa se describe cómo se pueden transformar y cargar los datos de URL DEO de producto para el lenguaje de reserva en el índice de URL.​Empieza con la ejecución de la consulta Elasticsearch siguiente en el índice de URL:

 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

Esta etapa describe cómo los datos de URL de SEO de Producto se pueden transformar y cargar en el índice de la Categoría. Empieza con la ejecución de la siguiente consulta Elasticsearch en el índice de URL:

        {
	    "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}
	                    }
	                }
	            ]
	        }
	    }
	}

A continuación, el conjunto de resultados se pasa al procesador UpdateCategoryURL​ para su transformación, utilizando la tabla siguiente para ​correlacionar el campo de base de datos devuelto desde el SQL anterior con un campo de índice en el índice de productos:​​ Por ejemplo, consulte los ejemplos de la etapa 6.

Esta etapa describe cómo los datos de URL de SEO de Producto se pueden transformar y cargar en el índice del Producto. Empieza con la ejecución de la siguiente consulta Elasticsearch en el índice de URL:

       ​{
	    "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}
	                    }
	                }
	            ]
	        }
	    }
	}

A continuación, el conjunto de resultados se pasa al procesador UpdateProductURL​ para su transformación utilizando la tabla siguiente para ​correlacionar el campo de base de datos devuelto desde el SQL anterior con un campo de índice en el índice de productos:​​​ Por ejemplo, consulte los ejemplos de la etapa 7.