Aggregate

Operation Name

Aggregate

Function Overview

Aggregates the input data.

Data Model

Table model type.

Refer to "Table model type schema" for input/output schema.

Properties

Basic settings

Item name	Required/Optional	Use of variables	Description	Remarks
Name	Required	Not available	Enter the name to show on the script canvas.
Input data	Required	Not available	Select the component on the script canvas.

Required settings

Item name	Required/Optional	Use of variables	Description	Remarks
Group key configuration	Optional	-	Specify columns which would be keys for aggregate operation. Each column can be operated with the following buttons. [Add]: Adds a key. [Delete]: Deletes a key.	When multiple columns are specified as keys, input data is grouped based on the combination of the values of those columns and aggregated by each group. When the key is not specified, all the input data would be aggregated as the same group. You cannot specify a byte[] type column and aggregate data.
Group key configuration/Column name	Required	Not available	Select a column name that would be a key.	Displays the index that shows the order after the column name. When the value of [Input data] is selected or changed, column name will be updated.

Aggregate settings

Item name	Required/Optional	Use of variables	Description	Remarks
Aggregate target configuration	Optional	-	Specify columns and aggregate function that would be aggregate targets. Each column can be operated with the following buttons. [Add]: Adds a aggregate target. [Delete]: Deletes a aggregate target.
Aggregate target configuration/Column name	Required	Not available	Select a column name that would be an aggregate target.	Displays the index that shows the order after the column name. When the value of [Input data] is selected or changed, column name will be updated. Column name set in [Group key configuration/Column name] will not be displayed as choice.
Aggregate target configuration/Function	Required	Not available	Select an aggregate function to process the aggregate target.	Enabled when [Aggregate target configuration/Column name] is selected. Displays only the aggregate functions applicable to the data type of the column specified in [Aggregate target configuration/Column name]. For more details, refer to "Aggregate Function".

Output settings

Item name	Required/Optional	Use of variables	Description	Remarks
Configure output manually	Optional	Not available	Select whether or not to set columns or aggregated results to be output manually. [Checked]: Output target is set manually. [Not checked]:(Default) Output target will be automatically set according to the content of the specified property items.	When it is not checked, the output target would be in the following order. Setting order of [Group key configuration] Setting order of [Aggregate target configuration] [Count]
Output target configuration	Optional	-	Specify aggregated results or columns to be output. Each column can be operated with the following buttons. [Up]: Moves the output order of the output target upwards by one . [Down]: Moves the output order of the output target downwards by one. [Add]: Adds an output target. [Delete]: Deletes the output target.	Enabled when [Configure output manually] is checked.
Output target configuration/Data source	Required	Not available	Select a data source of the output target. [<Group key configuration/Column name>]: Outputs the column that would be the group key. [<Aggregate target configuration/Function>] of [<Aggregate target configuration/Column name>]: Outputs the aggregated result of the columns to be aggregated. [Count]: Outputs the data count that comes under the same aggregate group.	Displays the index that shows the order after the column name. When the value of [Input data] is selected or changed, column name will be updated. The internal data type of output column depends on the selected data source. [<Group key configuration/Column name>]: Same as the input data type [<Aggregate target configuration/Function>] of [<Aggregate target configuration/Column name>]: Data type of the aggregated result [Count]: int type For more details on aggregated result, refer to "Aggregate Function".

Comment

Item name	Required/Optional	Use of variables	Description	Remarks
Comment	Optional	Not available	You can write a short description of this adapter. The description will be reflected in the specifications.

Schema

Input Schema

The same as the schema of the input source component.

Output Schema

Depending on the [Output Settings], the number of columns will vary.

For schema structure, refer to "Table model type schema".

Loading Schema in Mapper

Output schema will be loaded automatically while input schema should be done manually.
Specify the schema of the data to be read.

For more details, refer to "Edit Schema".

Mass Data Processing

Mass data processing is not supported.

PSP Usage

PSP is not supported.

Available Component Variables

Component variable name	Description	Remarks
message_category	Stores the category to which corresponding message code belongs to, when an error occurs.	Default value is null.
message_code	Stores its corresponding message code of occurred error.	Default value is null.
message_level	Stores the severity of corresponding message code of the occurred error.	Default value is null.
error_type	Returns the error type when error occurred.	Default value is null. The error type will be in the below format. Example: "java.io.FileNotFoundException" The stored details may vary depending on the DataSpider Servista version.
error_message	Returns the error message when error occurred.	Default value is null. The stored details may vary depending on the DataSpider Servista version.
error_trace	Returns the trace information when error occurred.	Default value is null. The stored details may vary depending on the DataSpider Servista version or the client application to be executed.

Schema Modification of Input Source Component

When the schema of the component specified in [Input data] is modified, to reflect those changes to aggregate operation, open the property setting dialog of aggregate operation processing and click [Finish] button.

When the schema structure of the component specified in [Input data] is modified (swapping or deletion of schema elements), [Group key configuration/Column name] or the settings of [Aggregate target configuration/Column name], [Output target configuration/Data source] must be modified accordingly.
The reason behind is that the aggregate operation keeps the information of columns by their orders (indices) rather than by their names.

Null and Empty String

When there is a null or an empty string existing in the value of the column which is specified in [Group Key Configuration], the value will be processed as group key value.

An error occurs when there is a null or an empty string existing in the value of the column which is specified in [Aggregate target configuration] with any of the following aggregate functions.
- Summation
- Minimum (number)
- Maximum (number)
- Minimum (date/time)
- Maximum (date/time)

Aggregate Function

Aggregate functions have some limitations by nature on the data types that can be used as the input for processing. Further, the data type of aggregated result might be different to the input data type.

For more details on data type, refer to "Data Types".

Aggregate function	Description	Availability for input data type											Data type of aggregated result
Aggregate function	Description	byte	short	int	long	float	double	BigDecimal	boolean	String	Date	byte[]	Data type of aggregated result
Unique count	Aggregates the number of unique values.	○	○	○	○	○	○	○	○	○	○	×	int
First value	Extracts the first value.	○	○	○	○	○	○	○	○	○	○	○	Same as input
Last value	Extracts the last value.	○	○	○	○	○	○	○	○	○	○	○	Same as input
First non-null value	Extracts the first non-null value.	○	○	○	○	○	○	○	○	○	○	○	Same as input
Last non-null value	Extracts the last non-null value.	○	○	○	○	○	○	○	○	○	○	○	Same as input
Summation	Calculates the summation as number.	○	○	○	○	○	○	○	×	○	×	×	BigDecimal
Minimum (number)	Extracts the value corresponding to the numerical minimum.	○	○	○	○	○	○	○	×	○	×	×	Same as input
Maximum (number)	Extracts the value corresponding to the numerical maximum.	○	○	○	○	○	○	○	×	○	×	×	Same as input
Minimum (date/time)	Extracts the value corresponding to the earliest date or time.	×	×	×	×	×	×	×	×	○	○	×	Same as input
Maximum (date/time)	Extracts the value corresponding to the latest date or time.	×	×	×	×	×	×	×	×	○	○	×	Same as input

How to read the table

Symbol Description

○ Can be applied as input.

× Cannot be applied as input.

Symbol	Description
○	Can be applied as input.
×	Cannot be applied as input.

The internal data type char is treated as String.

Specification Limits

Multi-thread processing is supported.
For the specification limits on multi-thread processing, refer to "Specification Limits".

When specifying [Group key configuration], [Aggregate target configuration] and [Output target configuration], the output schema of input source component must be configured.
For components requiring manual schema settings, load schemas with Mapper.

Order of the result data of the aggregate operation is not assured.

Even when mass data processing is performed by the input source component, data is temporarily held in the memory during the execution of aggregate operation.
Therefore, when the data amount is massive, an OutOfMemoryError might occur even with mass data processing enabled.
In such case, expand the memory allocation of DataSpiderServer.

Main Exceptions

Exception name	Cause	Solution
InputDataNotFoundException	[Input data] is not specified.	Specify the [Input data], or draw an input data flow.
InvalidInputTypeException	[Input data] is not table model type.	Check whether the component specified in [Input data] is table model type.
InvalidPropertyConfigurationException Value is not specified in <property name>	Value is not specified in [<property name>].	Specify the [<property name>].
InvalidPropertyConfigurationException Value is not specified in [<property name2>] of the row [<row number>] in <property name1>.	Value is not specified in [<property name2>].	Specify the [<property name2>].
InvalidPropertyConfigurationException The value specified in the row [<row number>] of <property name> is duplicated.	The value specified in [<property name>] is duplicated.	Check whether the value specified in [<property name>] is appropriate.
ConversionFailedException Column value cannot be processed as specified aggregate target.	The contents of input data contains some values that cannot be processed as per the specification in [Aggregate target configuration].	Check whether the input data is compatible with the contents specified in [Aggregate target configuration], or whether any null or empty string is contained.