Aggregate

Overview

You can use this Snap to apply aggregate functions on input data using the Group By support. This Snap enables you to calculate an aggregate function on a set of values to return a single scalar value.

Note:

  • This Snap does not support list and map objects referenced in the JSON paths.

  • If the input documents areunsorted and GROUP-BY fields are used, you must use the Sort Snap upstream of the Aggregate Snap to presort the input document stream and set the Sorted stream field Ascending or Descending to prevent the out-of-memory error. However, if the total size of input documents is expected to be relatively small compared to the available memory, then Sort Snap is not required upstream.

    Learn more about presorting unsorted input documents to be processed by the Aggregate Snap.



The following are the commonly used SQL Aggregate functions:

  • AVG – calculates the average of a set of values.

  • COUNT – counts rows in a specified table or view.

  • MIN – gets the minimum value in a set of values.

  • MAX – gets the maximum value in a set of values.

  • SUM – calculates the sum of values.

  • CONCAT – calculates the sum of values.

  • UNIQUE_CONCAT – calculates the sum of values.

Snap views

View Description Examples of upstream and downstream Snaps
Input Each document should contain values referenced in the Aggregate fields and the GROUP-BY fields field set. If not, the input data is sent to the error view.
Output Each document contains the mapped data that includes key-value entries of the GROUP-BY field name and its value, and a key-value entry of the Result field and its value, if processed successfully.
Error

Error handling is a generic way to handle errors without losing data or failing the Snap execution. You can handle the errors that the Snap might encounter when running the pipeline by choosing one of the following options from the When errors occur list under the Views tab. The available options are:

  • Stop Pipeline Execution Stops the current pipeline execution when an error occurs.
  • Discard Error Data and Continue Ignores the error, discards that record, and continues with the remaining records.
  • Route Error Data to Error View Routes the error data to an error view without stopping the Snap execution.

Learn more about Error handling in Pipelines.

Snap settings

Legend:
  • Expression icon (): Allows using pipeline parameters to set field values dynamically (if enabled). SnapLogic Expressions are not supported. If disabled, you can provide a static value.
  • SnapGPT (): Generates SnapLogic Expressions based on natural language using SnapGPT. Learn more.
  • Suggestion icon (): Populates a list of values dynamically based on your Snap configuration. You can select only one attribute at a time using the icon. Type into the field if it supports a comma-separated list of values.
  • Upload : Uploads files. Learn more.
Learn more about the icons in the Snap settings dialog.
Field / Field set Type Description
Label String

Required. Specify a unique name for the Snap. Modify this to be more appropriate, especially if more than one of the same Snaps is in the pipeline.

Default value: Aggregate

Example: Aggregate_Avg
Aggregate fields
Required. Use this field set to define the type of Aggregate function to perform on the field and the key name to be used in the output. This field set contains the following fields:
  • Function
  • Field
  • Result field
Function String
Required. Select the functions that applies to the aggregate field value in the input data. The available functions and the supported datatypes are:

  • COUNT – all types.

  • SUM – Number (Numeric string is converted to a number).

  • MIN – Number, String, DateTime, LocalDateTime, LocalDate, LocalTime.

  • MAX – Number, String, DateTime, LocalDateTime, LocalDate, LocalTime.

    • When using a date for MIN/MAX functions, change “$DATE.date" to "$DATE" in the Aggregate field.

  • AVG – Number, DateTime, LocalDateTime, LocalDate, LocalTime (Numeric string is converted to a number).

  • CONCAT – Concatenates all records separated by a pipe (|).

  • UNIQUE_CONCAT – Concatenates all unique records separated by a pipe (|).

Note: When you select the AVG function, the Snap rounds up all numeric values that have more than 16 digits. The AVG function handles the numeric values as below:
  • If the raw value is less than 16 with or without decimals, then the output value displays without any change.
    • Raw Value: 1234567890/1234567890.1234
    • Output Value: 1234567890/1234567890.1234
  • If the raw value is greater than or equal to 16 with or without decimals, then the output value is rounded off to 16 digits, including decimals.
    • Raw Value: 123456789012345.67
    • Output Value: 123456789012345.7
  • If the raw value is greater than 16 with or without decimals, then the output value is rounded up to 16 digits and expressed in exponential notation.

    • Raw Value: 1234567890123456789/1234567890123456789.123

    • Output Value: 1.234567890123457E+18

  • If the raw value is non-terminating decimal, then the output value is truncated to 16 digits.

    • Raw Value: 1.3333333333333333333333

    • Output Value: 1.333333333333333

Default value: SUM

Example: AVG
Field String/Expression

Required. Specify a JSON path to the field on which the Aggregate function should be applied such as $items.item. Learn more: JSONPath - XPath for JSON.

Default value: None.

Example: $Total or DateTime.parse($service)
Result field String

Required. Specify the field name to be used for mapped data in the output. This value is the aggregate computed result corresponding to the GROUP-BY field values.

Default value: None.

Example: Revenue
GROUP-BY fields

Use this field set to define field paths and names. If you leave this field blank, the Snap produces only one output document.
Field String/Expression

Specify a JSON path for the GROUP-BY field.

Default value: None.

Example: $.Product.Name
Output field String Required. Specify the GROUP-BY field name to be used in the output map data. If left blank, the Field path is used instead.

Default value: None.

Example: ProductName
Integer mode Checkbox

Select this checkbox if you want the Snap to produce integer results rounded half up.

Note: The input data can be mixed in integers and floating-point numbers, and the Snap maintains intermediate results in floating-point numbers. The value of this field is ignored in the COUNT Aggregate function.

Default status: Deselected
Sorted streams Dropdown list

Required. Select an option to specify if the input documents are sorted or not.

This option enables the Snap to verify if the input is sorted as it processes each document and performs the aggregation efficiently and displays an error if the records are not sorted.

The available options are:
  • Unsorted: The input documents are not sorted.
  • Ascending: The input documents are sorted in an ascending order.
  • Descending: The input documents are sorted in descending order.
Note: If the input data stream contains a large number of documents, then presort the input using the Sort Snap as this uses less memory and results in an effective performance. If you select Unsorted and are using GROUP-BY fields, the Aggregate Snap uses MapDB internally in order to avoid an out-of-memory error issue. Therefore, starting with 434patches23034, when GROUP-BY fields are used in the Aggregate Snap and the Sorted streams field is Unsorted, the order of the output documents may be different from one execution to another.

Default value: Unsorted

Example: Ascending
Minimum memory (MB) String/Expression If the available memory is less than this property value while processing input documents, the Snap stops to fetch the next input document until more memory is available. This feature is disabled if this property value is 0.

Default value: 500

Example: 750
Out-of-memory timeout (minutes) String/Expression If the Snap pauses longer than this property value while waiting for more memory available, it throws an exception to prevent the system from running out of memory.

Default value: 30

Example: 40
Snap execution Dropdown list
Choose one of the three modes in which the Snap executes. Available options are:
  • Validate & Execute: Performs limited execution of the Snap and generates a data preview during pipeline validation. Subsequently, performs full execution of the Snap (unlimited records) during pipeline runtime.
  • Execute only: Performs full execution of the Snap during pipeline execution without generating preview data.
  • Disabled: Disables the Snap and all Snaps that are downstream from it.

Default value: Validate and Execute

Example: Disabled

Temporary files

During execution, data processing on Snaplex nodes occurs principally in-memory as streaming and is unencrypted. When processing larger datasets that exceed the available compute memory, the Snap writes unencrypted pipeline data to local storage to optimize the performance. These temporary files are deleted when the pipeline execution completes. You can configure the temporary data's location in the Global properties table of the Snaplex node properties, which can also help avoid pipeline errors because of the unavailability of space. Learn more about Temporary Folder in Configuration Options.

Troubleshooting

Error Message

Error Reason Resolution
EXPRESSIONS_DETECTED Expressions have been detected in Aggregate. Remove all expressions.
ERR_UNSUPPORTED_AGGR_FUNCTION The selected aggregate function is not supported. Select a valid aggregate function.
INPUT_VARIABLES_NOT_SUBSTITUTED One or more variables in the JSON file are not mapped. Either remove non substituted variables starting with $ or map them using an upstream snap.
ERR_EDITOR_KEYWORDS_NOT_ESCAPED Reserved characters are detected in the JSON key value pair. Replace the reserved characters using # and [[ ]]. For example, replace ‘##’ as #[[##]]# to escape it.
ERR_MAPDB_VALUE_NULL MapDB intermittently retrieves null for non-null value. Use Sort Snap to sort the input data stream.

Examples