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.
Processor Configuration
Add the Functions operator to a data source or an operator and configure the fields as explained below.
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.
Type $ to get the list of all functions.
Select a function from the list to get its sample expression in the message box.
Replace the sample parameter values with the actual values.
Functions processor supports MVEL expressions.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.
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.
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.The search string can be a static value, or it can be given as a dynamic value coming from an incoming connector.To feed dynamic values to the search string parameter, use the below expression:
@{
column.<data-source-ID>.<data-source-column>}Where
data-source-IDis the ID of the data source connector anddata-source-columnis 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.
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): Query output from nth row.resultColumnName(required): Result column name.
| Output Type | Column |
|---|---|
| Returns | Returns the value for the given query, row number and column name. |
| Throws | Application Exception |
Use Case:
lookupOutput:
$lookupRDBMS(String selectQuery, int resultRow, String resultColumnName)
Example:
select * from employee where age > 25
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.
Example: The number 123.45 has a scale of 2.
lookupSnowflake
This function returns the result of the lookupSnowflake configuration details passed by the user and outputs the given column for the given row.
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): Query output from nth row.resultColumnName(required): Result column name.
Example:
select * from sales where amount > 1500
It will return the amount 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 lookupSnowflake 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.
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!