Grouping Extract Results with Nuggetizer

Owned by Marius Navickas
Last updated: Jun 03, 2024 by Pamela Totas (Deactivated)
1 min read

 

Extract results for WarehousePosition and WarehouseOpenLot feedtypes may be grouped by the entity ID, effective date or both entity ID and effective date (delimiter between fields for grouping is '^')

The extract results may be grouped through the use of the task parameter, GroupbyExtractKey, within an RTR for a single extract on extractservice or as a global parameter, W_GROUPBY_EXTRACT_KEY, specified in the w_config_custom.inc for all running extracts on the region for the WarehousePosition and WarehouseOpenLot feedtypes. When executing the eagle_wrf_generic_extract workflow, the ALLEXTRACTS_GroupbyExtractKey parameter should be applied.
The name of the fields for grouping should be the same as in the database
Example RTR parameter for a single extract for extractservice
<taskParameter>
<name>GroupbyExtractKey</name>
<dataType>S</dataType>
<value>ENTITY_ID^EFFECTIVE_DATE</value>
</taskParameter>
Example RTR param for extract with using eagle_wrf_generic_extract workflow
<taskParameter>
<name>ALLEXTRACTS_GroupbyExtractKey</name>
<dataType>S</dataType>
<value>ENTITY_ID^EFFECTIVE_DATE</value>
</taskParameter>
Example global flag for w_config_custom.inc
<CODE> :W_GROUPBY_EXTRACT_KEY: := 'ENTITY_ID^EFFECTIVE_DATE'</CODE>
Before the grouping is applied, the extracted data should be ordered by grouping fields. To order data from the query, use the parameter MicroBatchSplitKey.
Example RTR param for single extract for extractservice:
<taskParameter>
<name>MicroBatchSplitKey</name>
<dataType>S</dataType>
<value>ENTITY_ID,EFFECTIVE_DATE</value>
</taskParameter>
Example RTR param for extract with using eagle_wrf_generic_extract workflow:
<taskParameter>
<name>ALLEXTRACTS_MicroBatchSplitKey</name>
<dataType>S</dataType>
<value>ENTITY_ID,EFFECTIVE_DATE</value>
</taskParameter>
The filename for the extracted files group may include the entity_id of group(@EntityId); effective_date of group (@effectivedate); and the sequence number related to each group (@SequencerNumber) with using ExtractNamePatternEx parameter in RTR
Example RTR parameter for single extract for extractservice:
<taskParameter>
<name>ExtractNamePatternEx</name>
<dataType>S</dataType>
<value><at:var at:name="CORRELATIONID_" />FeedType_<at:var at:name="effectivedate_" />EntityId_<at:var at:name="SequencerNumber_" />RecordCount.xml</value>
</taskParameter>
Example RTR parameter for extract with using eagle_wrf_generic_extract workflow:
<taskParameter>
<name>ALLEXTRACTS_ExtractNamePatternEx</name>
<dataType>S</dataType>
<value><at:var at:name="CORRELATIONID_" />FeedType_<at:var at:name="effectivedate_" />EntityId_<at:var at:name="SequencerNumber_" />RecordCount.xml</value>
</taskParameter>
The grouped result should be split for nuggets. The record counts in the file of group is according to the RecordCountPerNugget or MicroBatchKeyRecordCount (both behave the same way) parameter value in RTR. If the count of extracted records for a group is more than the RecordCountPerNugget or MicroBatchKeyRecordCount value, then the group sequence number will be increased and 'll creates a new file for the same group. Each group of extracted data have the own group sequence number of created files that is the new group initial sequence number is 1.
Example:
Example RTR param for single extract for extractservice:
<taskParameter>
<name>RecordCountPerNugget</name>
<dataType>S</dataType>
<value>50</value>
</taskParameter>
Example RTR param for extract with using eagle_wrf_generic_extract workflow:
<taskParameter>
<name>ALLEXTRACTS_RecordCountPerNugget</name>
<dataType>S</dataType>
<value>50</value>
</taskParameter>
RTR example for eagle_wrf_generic_extract workflow to group extract results by entity_id and effective_date for WarehousePosition and WarehouseOpenLot objects with using nuggetizer and JMS distribution and reflect the group data in the filename
<EagleML xsi:type="RunTaskRequestSync" eaglemlVersion="2-0" xsi:schemaLocation="http://www.eagleinvsys.com/2011/EagleML-2-0 eagleml-main-2-0.xsd" eaglemlType="RunTaskRequest" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.eagleinvsys.com/2011/EagleML-2-0">
<header>
<messageId>53EEF58C87968591</messageId>
<sentBy>[http://www.client.com</sentBy|]>
<sendTo>http://www.eagleinvsys.com/eagle_ml-2-0_default_cm_wrkfl_listener</sendTo>
<replyTo>http://www.client.com/Eagle Control Messages</replyTo>
<creationTimestamp>2017-02-27T15:16:14-05:00</creationTimestamp>
</header>
<taskIdentifier>
<correlationId correlationIdScheme="correlationIdScheme">{GUID}</correlationId>
<businessTaskId correlationIdScheme="businessTaskIdScheme">TST_EXTRACT_GROUPBY_ENTITYLIST_MC2</businessTaskId>
<ac:structured-macro ac:name="anchor" ac:schema-version="1" ac:macro-id="35dc55c4-64d4-4827-a79b-bf96add7b7ea"><ac:parameter ac:name="">_GoBack</ac:parameter></ac:structured-macro> </taskIdentifier>
<taskParameters>
<taskParameter>
<name>ActionType</name>
<dataType>S</dataType>
<value>EXECUTE</value>
</taskParameter>
<taskParameter>
<name>StreamName</name>
<dataType>S</dataType>
<value>eagle_ml-2-0_default_cm_execwkfl</value>
</taskParameter>
<taskParameter>
<name>WorkflowName</name>
<dataType>S</dataType>
<value>eagle_wrf_generic_extract</value>
</taskParameter>
<taskParameter>
<name>FeedType</name>
<dataType>S</dataType>
<value>WRHSPOSITIONEXTRACT,WRHSOPENLOTEXTRACT</value>
</taskParameter>
<taskParameter>
<name>ALLEXTRACTS_fromeffectivedate</name>
<dataType>S</dataType>
<value>2020-09-01</value>
</taskParameter>
<taskParameter>
<name>ALLEXTRACTS_toeffectivedate</name>
<dataType>S</dataType>
<value>2020-09-30</value>
</taskParameter>
<taskParameter>
<name>ALLEXTRACTS_datasources</name>
<dataType>S</dataType>
<value>STARDIRECT,STARGAAP,STARIFRS,STARSTAT</value>
</taskParameter>
<taskParameter>
<name>ALLEXTRACTS_UseGenericExtractorAllTaskParams</name>
<dataType>S</dataType>
<value>Y</value>
</taskParameter>
<taskParameter>
<name>OutputFormat</name>
<dataType>S</dataType>
<value>EagleML</value>
</taskParameter>
<taskParameter>
<name>ALLEXTRACTS_RecordCountPerNugget</name>
<dataType>S</dataType>
<value>50</value>
</taskParameter>
<taskParameter>
<name>ALLEXTRACTS_entityselectiontype</name>
<dataType>S</dataType>
<value>DynamicPortfolioList</value>
</taskParameter>
<taskParameter>
<name>ALLEXTRACTS_entityselectionvalue</name>
<dataType>S</dataType>
<value>TST_ENTITY_LIST</value>
</taskParameter>
<taskParameter>
<name>ALLEXTRACTS_ExtractNamePatternEx</name>
<dataType>S</dataType>
<value><at:var at:name="CORRELATIONID_" />FeedType_<at:var at:name="effectivedate_" />EntityId_<at:var at:name="SequencerNumber_" />RecordCount.xml</value>
</taskParameter>
<taskParameter>
<name>ALLEXTRACTS_MicroBatchSplitKey</name>
<dataType>S</dataType>
<value>ENTITY_ID,EFFECTIVE_DATE</value>
</taskParameter>
<taskParameter>
<name>ALLEXTRACTS_GroupbyExtractKey</name>
<dataType>S</dataType>
<value>ENTITY_ID^EFFECTIVE_DATE</value>
</taskParameter>
<taskParameter>
<name>DeliveryMethod</name>
<dataType>S</dataType>
<value>JMS</value>
</taskParameter>
<taskParameter>
<name>ExtractDeliveryMethod</name>
<dataType>S</dataType>
<value>JMS</value>
</taskParameter>
<taskParameter>
<name>ReplyDeliveryMethod</name>
<dataType>S</dataType>
<value>JMS</value>
</taskParameter>
<taskParameter>
<name>ACKDeliveryMethod</name>
<dataType>S</dataType>
<value>JMS</value>
</taskParameter>
<taskParameter>
<name>JMSReplyDeliveryStream</name>
<dataType>S</dataType>
<value>mc2_jms_tsr</value>
</taskParameter>
<taskParameter>
<name>JMSAckDeliveryStream</name>
<dataType>S</dataType>
<value>mc2_jms_ack</value>
</taskParameter>
<taskParameter>
<name>JMSExtractDeliveryStream</name>
<value>mc2_jms_extract</value>
</taskParameter>
<taskParameter>
<name>JMSDeliveryURI</name>
<dataType>S</dataType>
<value>eaglejms-stream:mc2_jms_extract</value>
</taskParameter>
<taskParameter>
<name>EnableAckDelivery</name>
<dataType>S</dataType>
<value>Y</value>
</taskParameter>
<taskParameter>
<name>EnableReplyDelivery</name>
<dataType>S</dataType>
<value>Y</value>
</taskParameter>
</taskParameters>
</EagleML>
TSR result example:
<extractDetails>
<URI>extract://extract_warehouse_position_eagleml</URI>
<extractSummary>
<transactionCount>17</transactionCount>
<objectCount>17</objectCount>
<rowCount>17</rowCount>
<fileCount>8</fileCount>
</extractSummary>
<extractTaskDetails>
<extractStats>
<extractFilename>WRHSPOSITIONEXTRACT_2020-09-10_42053_1_1_1.xml</extractFilename>
<transactionCount>1</transactionCount>
<objectCount>1</objectCount>
<rowCount>1</rowCount>
</extractStats>
<extractStats>
<extractFilename>WRHSPOSITIONEXTRACT_2020-09-11_42053_1_1_1.xml</extractFilename>
<transactionCount>1</transactionCount>
<objectCount>1</objectCount>
<rowCount>1</rowCount>
</extractStats>
<extractStats>
<extractFilename>WRHSPOSITIONEXTRACT_2020-09-09_42053_2_1_3.xml</extractFilename>
<transactionCount>3</transactionCount>
<objectCount>3</objectCount>
<rowCount>3</rowCount>
</extractStats>
<extractStats>
<extractFilename>WRHSPOSITIONEXTRACT_2020-09-09_42053_2_2_3.xml</extractFilename>
<transactionCount>3</transactionCount>
<objectCount>3</objectCount>
<rowCount>3</rowCount>
</extractStats>
<extractStats>
<extractFilename>WRHSPOSITIONEXTRACT_2020-09-09_42053_2_3_2.xml</extractFilename>
<transactionCount>2</transactionCount>
<objectCount>2</objectCount>
<rowCount>2</rowCount>
</extractStats>
<extractStats>
<extractFilename>WRHSPOSITIONEXTRACT_2020-09-10_42053_2_1_3.xml</extractFilename>
<transactionCount>3</transactionCount>
<objectCount>3</objectCount>
<rowCount>3</rowCount>
</extractStats>
<extractStats>
<extractFilename>WRHSPOSITIONEXTRACT_2020-09-10_42053_2_2_3.xml</extractFilename>
<transactionCount>3</transactionCount>
<objectCount>3</objectCount>
<rowCount>3</rowCount>
</extractStats>
<extractStats>
<extractFilename>WRHSPOSITIONEXTRACT_2020-09-10_42053_2_3_1.xml</extractFilename>
<transactionCount>1</transactionCount>
<objectCount>1</objectCount>
<rowCount>1</rowCount>
</extractStats>
</extractTaskDetails>
</extractDetails>
</taskUnit>
<taskUnit>
<extractDetails>
<URI>extract://extract_warehouse_openlot_eagleml</URI>
<extractSummary>
<transactionCount>17</transactionCount>
<objectCount>17</objectCount>
<rowCount>17</rowCount>
<fileCount>8</fileCount>
</extractSummary>
<extractTaskDetails>
<extractStats>
<extractFilename>WRHSOPENLOTEXTRACT_2020-09-10_42053_1_1_1.xml</extractFilename>
<transactionCount>1</transactionCount>
<objectCount>1</objectCount>
<rowCount>1</rowCount>
</extractStats>
<extractStats>
<extractFilename>WRHSOPENLOTEXTRACT_2020-09-11_42053_1_1_1.xml</extractFilename>
<transactionCount>1</transactionCount>
<objectCount>1</objectCount>
<rowCount>1</rowCount>
</extractStats>
<extractStats>
<extractFilename>WRHSOPENLOTEXTRACT_2020-09-09_42053_2_1_3.xml</extractFilename>
<transactionCount>3</transactionCount>
<objectCount>3</objectCount>
<rowCount>3</rowCount>
</extractStats>
<extractStats>
<extractFilename>WRHSOPENLOTEXTRACT_2020-09-09_42053_2_2_3.xml</extractFilename>
<transactionCount>3</transactionCount>
<objectCount>3</objectCount>
<rowCount>3</rowCount>
</extractStats>
<extractStats>
<extractFilename>WRHSOPENLOTEXTRACT_2020-09-09_42053_2_3_2.xml</extractFilename>
<transactionCount>2</transactionCount>
<objectCount>2</objectCount>
<rowCount>2</rowCount>
</extractStats>
<extractStats>
<extractFilename>WRHSOPENLOTEXTRACT_2020-09-10_42053_2_1_3.xml</extractFilename>
<transactionCount>3</transactionCount>
<objectCount>3</objectCount>
<rowCount>3</rowCount>
</extractStats>
<extractStats>
<extractFilename>WRHSOPENLOTEXTRACT_2020-09-10_42053_2_2_3.xml</extractFilename>
<transactionCount>3</transactionCount>
<objectCount>3</objectCount>
<rowCount>3</rowCount>
</extractStats>
<extractStats>
<extractFilename>WRHSOPENLOTEXTRACT_2020-09-10_42053_2_3_1.xml</extractFilename>
<transactionCount>1</transactionCount>
<objectCount>1</objectCount>
<rowCount>1</rowCount>
</extractStats>
</extractTaskDetails>
</extractDetails>
RTR example for single extract via extract service with grouping result by entity_id and effective_date for WarehousePosition with using nuggitizer and file distribution
<EagleML xsi:type="RunTaskRequestSync" eaglemlVersion="2-0" xsi:schemaLocation="http://www.eagleinvsys.com/2011/EagleML-2-0 eagleml-main-2-0.xsd" eaglemlType="RunTaskRequest" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.eagleinvsys.com/2011/EagleML-2-0">
<header>
<messageId>53EEF58C87968591</messageId>
<sentBy>http://www.client.com</sentBy>
<sendTo>http://www.eagleinvsys.com/eagle_ml-2-0_default_out_extract_service</sendTo>
<replyTo>http://www.client.com/Eagle Control Messages</replyTo>
<creationTimestamp>2017-02-27T15:16:14-05:00</creationTimestamp>
</header>
<taskIdentifier>
<correlationId correlationIdScheme="correlationIdScheme">{GUID}</correlationId>
<businessTaskId correlationIdScheme="businessTaskIdScheme">ES_TST_EXTRACT_GROUPBY_MC2</businessTaskId>
</taskIdentifier>
<taskTypeEnum>LOAD</taskTypeEnum>
<taskParameters>
<taskParameter>
<name>ActionType</name>
<dataType>S</dataType>
<value>EXECUTE</value>
</taskParameter>
<taskParameter>
<name>StreamName</name>
<dataType>S</dataType>
<value>eagle_ml-2-0_default_out_extract_service</value>
</taskParameter>
<taskParameter>
<name>IncludeExtendedDetails</name>
<dataType>S</dataType>
<value>ALL</value>
</taskParameter>
<taskParameter>
<name>FeedType</name>
<dataType>S</dataType>
<value>WRHSPOSITIONEXTRACT</value>
</taskParameter>
<taskParameter>
<name>fromeffectivedate</name>
<dataType>S</dataType>
<value>2020-10-09</value>
</taskParameter>
<taskParameter>
<name>toeffectivedate</name>
<dataType>S</dataType>
<value>2020-10-11</value>
</taskParameter>
<taskParameter>
<name>datasources</name>
<dataType>S</dataType>
<value>EAGLE PACE</value>
</taskParameter>
<taskParameter>
<name>UseGenericExtractorAllTaskParams</name>
<dataType>S</dataType>
<value>Y</value>
</taskParameter>
<taskParameter>
<name>OutputFormat</name>
<dataType>S</dataType>
<value>EagleML</value>
</taskParameter>
<taskParameter>
<name>MicroBatchSplitKey</name>
<dataType>S</dataType>
<value>ENTITY_ID,EFFECTIVE_DATE</value>
</taskParameter>
<taskParameter>
<name>RecordCountPerNugget</name>
<dataType>S</dataType>
<value>50</value>
</taskParameter>
<taskParameter>
<name>entityselectiontype</name>
<dataType>S</dataType>
<value>DynamicPortfolioList</value>
</taskParameter>
<taskParameter>
<name>entityselectionvalue</name>
<dataType>S</dataType>
<value>ENT42053</value>
</taskParameter>
<taskParameter>
<name>GroupbyExtractKey</name>
<dataType>S</dataType>
<value>ENTITY_ID^EFFECTIVE_DATE</value>
</taskParameter>
<taskParameter>
<name>ExtractNamePatternEx</name>
<dataType>S</dataType>
<value><at:var at:name="CORRELATIONID_" />FeedType_<at:var at:name="effectivedate_" />EntityId_<at:var at:name="SequencerNumber_" />RecordCount.xml</value>
</taskParameter>
</taskParameters>
</EagleML>
RTR example for single extract via extract service with grouping result by entity_id for WarehouseOpenLot with using nuggitizer and file distribution
<EagleML xsi:type="RunTaskRequestSync" eaglemlVersion="2-0" xsi:schemaLocation="http://www.eagleinvsys.com/2011/EagleML-2-0 eagleml-main-2-0.xsd" eaglemlType="RunTaskRequest" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.eagleinvsys.com/2011/EagleML-2-0">
<header>
<messageId>53EEF58C87968591</messageId>
<sentBy>http://www.client.com</sentBy>
<sendTo>http://www.eagleinvsys.com/eagle_ml-2-0_default_out_extract_service</sendTo>
<replyTo>http://www.client.com/Eagle Control Messages</replyTo>
<creationTimestamp>2017-02-27T15:16:14-05:00</creationTimestamp>
</header>
<taskIdentifier>
<correlationId correlationIdScheme="correlationIdScheme">{GUID}</correlationId>
<businessTaskId correlationIdScheme="businessTaskIdScheme">ES_TST_EXTRACT_GROUPBY_MC2</businessTaskId>
</taskIdentifier>
<taskTypeEnum>LOAD</taskTypeEnum>
<taskParameters>
<taskParameter>
<name>ActionType</name>
<dataType>S</dataType>
<value>EXECUTE</value>
</taskParameter>
<taskParameter>
<name>StreamName</name>
<dataType>S</dataType>
<value>eagle_ml-2-0_default_out_extract_service</value>
</taskParameter>
<taskParameter>
<name>IncludeExtendedDetails</name>
<dataType>S</dataType>
<value>ALL</value>
</taskParameter>
<taskParameter>
<name>FeedType</name>
<dataType>S</dataType>
<value>WRHSOPENLOTEXTRACT</value>
</taskParameter>
<taskParameter>
<name>fromeffectivedate</name>
<dataType>S</dataType>
<value>2020-10-09</value>
</taskParameter>
<taskParameter>
<name>toeffectivedate</name>
<dataType>S</dataType>
<value>2020-10-11</value>
</taskParameter>
<taskParameter>
<name>datasources</name>
<dataType>S</dataType>
<value>EAGLE PACE</value>
</taskParameter>
<taskParameter>
<name>UseGenericExtractorAllTaskParams</name>
<dataType>S</dataType>
<value>Y</value>
</taskParameter>
<taskParameter>
<name>OutputFormat</name>
<dataType>S</dataType>
<value>EagleML</value>
</taskParameter>
<taskParameter>
<name>MicroBatchSplitKey</name>
<dataType>S</dataType>
<value>ENTITY_ID</value>
</taskParameter>
<taskParameter>
<name>RecordCountPerNugget</name>
<dataType>S</dataType>
<value>50</value>
</taskParameter>
<taskParameter>
<name>entityselectiontype</name>
<dataType>S</dataType>
<value>DynamicPortfolioList</value>
</taskParameter>
<taskParameter>
<name>entityselectionvalue</name>
<dataType>S</dataType>
<value>ENT42053</value>
</taskParameter>
<taskParameter>
<name>GroupbyExtractKey</name>
<dataType>S</dataType>
<value>ENTITY_ID</value>
</taskParameter>
<taskParameter>
<name>ExtractNamePatternEx</name>
<dataType>S</dataType>
<value><at:var at:name="CORRELATIONID_" />FeedType_<at:var at:name="effectivedate_" />EntityId_<at:var at:name="SequencerNumber_" />RecordCount.xml</value>
</taskParameter>
</taskParameters>
</EagleML>