Functions Processor

Use the Functions processor to perform different transformations on a Dataset.

The Functions processor is available in the Components panel under the Processors tab of the ETL applications canvas page.

Functions_Processor

Processor Configuration

Add the Functions operator to a data source or an operator and configure the fields as explained below.

Functions_Processor_01

Config Fields

Config fields are used to create local variables and can be added either using Add Config Fields or Upload Config Fields option.

Transform/Add Fields

Supply dynamic values into the Fields/Columns of the incoming dataset by using inbuilt functions or add a new field with dynamic values into the current schema.

Use the add output field option to add more fields that are required to be transformed, or upload a file to provide the field versus required transformation details.

Message

The message drop-down displays the ID of the incoming component for which the fields are displayed for transformation.

Functions

Functions can be added against each field that is required to be transformed in either Config Fields or Transform/Add Fields section.

Functions_Processor_02

  • Type $ to get the list of all functions.

  • Select a function from the list to get its sample expression in the message box.

    Functions_Processor_03

  • Replace the sample parameter values with the actual values.

    Functions_Processor_04

  • Click on the NEXT button.

    You can view the fields generated from the data source or further configuration options will appear for more advanced fuctions, like Lookup functions.

To understand about configuration parameters for each function, see the topics below:

Lookup Functions

The lookup functions allow users to search for specific values within a database and retrieve the information they need.

The lookup functions in Gathr include lookupMongo, lookupRDBMS, lookupSnowflake, and lookupWS, each with its own unique purpose.

By understanding these functions, the efficiency of retrieving data can be improved.

Type $ followed by lookup to get the list of all the lookup functions supported in Gathr.

Lookup_Functions

Based on the lookup function selected, the configuration fields will appear as described further.

lookupMongo

The lookupMongo function retrieves values from a specified column of a Mongo collection, based on matching string values from another column within the same collection.

lookupMongo_Function

The syntax of the lookupMongo function is as follows:

$lookupMongo(String dbName, String collName, String searchFld, String searchStr, String retColName)

Each parameter should be replaced as per the description given below:

  • dbName (required, string): The Mongo Database where the lookup will be performed.

  • collName (required, string): The collection to be used for lookup within the database.

  • searchFld (required, string): The field/column in the specified collection.

  • searchStr (required, string): The string value of the search field/column against which the reference value is to be fetched.

    To feed dynamic values to the search string parameter, use the below expression:

    @{column.<data-source-ID>.<data-source-column>}

    Where data-source-ID is the ID of the data source connector and data-source-column is the column name of the data source whose values should be matched with return column values for each row.

  • retColName (required, string): The return field/column name of the specified collection from which the values for search string will be fetched.

Example 1:

$lookupMongo('myCompany','employeeData', 'employeeName', 'John', 'employeeSalary')

Here, From the myCompany Mongo Database, employeeData collection, the employeeName field has a string John for which salary detail is to be fetched from the return field employeeSalary.

Example 2:

$lookupMongo('myCompany', 'employeeData', 'employeeName', @{column.AdvancedMongo_592911._Name}, 'employeeSalary')

Here, rows of the employeeName field, will be matched with the rows of _Name field (that is coming from the Advanced Mongo data source) and accordingly the salary detail will be fetched from the return field employeeSalary.

Add more output fields and create queries as per use case.

Click Next to proceed further with the lookupMongo configuration.

Connection Name

Connections are the service identifiers. A connection name can be selected from the list if you have created and saved connection details for MongoDB earlier. Or create one as explained in the topic - MongoDB Connection →

Return Type

Select the return type format for the existing or new field in which the records will be updated as per the lookupMongo details provided earlier.

The supported formats for the return type are: String, Double, Boolean, Date, Integer, Long, Short, Float, Decimal, Byte and Timestamp.

Precision

Total digits that should exist in the decimal number.

Example: The number 123.45 has a precision of 5.

Scale

The number of digits that should exist to the right of the decimal point in the number.

Example: The number 123.45 has a scale of 2.

lookupRDBMS

This function is used to fetch the records from the RDBMS table. Gathr supports database lookup for Postgres, MySQL, Oracle and IBM DB2.

lookupRDBMS_Function

The syntax of the lookupRDBMS function is as follows:

$lookupRDBMS(String selectQuery, int resultRow, String resultColumnName)

It returns the result of the lookupRDBMS configuration details passed by the user as an argument and outputs the given column for the given row.

Each parameter should be replaced as per the description given below:

  • selectQuery(required): Provide the query specific to the database.

  • resultRow(required): Result row number.

  • resultColumnName (required): Result column name.

Example: The number 123.45 has a scale of 2.

Output TypeColumn
ReturnsReturns the value for the given query, row number and column name.
ThrowsApplication Exception

Use Case:

lookupOutput:

$lookupRDBMS(String selectQuery, int resultRow, String resultColumnName)

Example:

$lookupRDBMS("select * from tableName",0,"age")

It will return the age column value from the first row of the query result.

Add more output fields and create queries as per use case.

Click Next to proceed further with the lookupRDBMS configuration.

Connection Name

Connections are the service identifiers. A connection name can be selected from the list if you have created and saved any JDBC connection details earlier. Or create one as explained in the topic - JDBC Connection →.

Return Type

Select the return type format for the existing or new field in which the records will be updated as per the lookupRDBMS details provided earlier.

The supported formats for the return type are: String, Double, Boolean, Date, Integer, Long, Short, Float, Decimal, Byte and Timestamp.

Precision

Total digits that should exist in the decimal number.

Example: The number 123.45 has a precision of 5.

Scale

The number of digits that should exist to the right of the decimal point in the number.

lookupSnowflake

This function returns the result of the lookupSnowflake configuration details passed by the user and outputs the given column for the given row.

lookupSnowflake_Function

The syntax of the lookupSnowflake function is as follows:

$lookupSnowflake(String selectQuery, int resultRow, String resultColumnName)

It returns the result of the lookupSnowflake configuration details passed by the user and outputs the given column for the given row.

Each parameter should be replaced as per the description given below:

  • selectQuery(required): Provide the query specific to the database.

  • resultRow(required): Result row number.

  • resultColumnName (required): Result column name.

Add more output fields and create queries as per use case.

Click Next to proceed further with the lookupRDBMS configuration.

Connection Name

Connections are the service identifiers. A connection name can be selected from the list if you have created and saved connection details for Snowflake earlier. Or create one as explained in the topic - Snowflake Connection →.

Warehouse Name

The warehouse name should be specified.

Schema Name

The schema name should be specified.

Return Type

Select the return type format for the existing or new field in which the records will be updated as per the lookupRDBMS details provided earlier.

The supported formats for the return type are: String, Double, Boolean, Date, Integer, Long, Short, Float, Decimal, Byte and Timestamp.

Precision

Total digits that should exist in the decimal number.

Example: The number 123.45 has a precision of 5.

Scale

The number of digits that should exist to the right of the decimal point in the number.

Example: The number 123.45 has a scale of 2.

lookupWS

This function returns the response of the web service. Currently supports the REST-based web service.

lookupWS_Function

The syntax of the lookupWS function is as follows:

$lookupWS()

Each parameter should be replaced as per the description given below:

Add more output fields and create queries as per use case.

Click Next to proceed further with the lookupWS configuration.

End Point

The endpoint details need to be provided.

Method Type

The request method type is to be selected out of GET or POST.

Content Type

The content type should be provided.

Example: application/json

WS Param

The parameter value should be provided that will return the web service’s response.

If you have any feedback on Gathr documentation, please email us!

← Filter Processor
Hashing Processor →