ELT Math Function
In this article
An account for the Snap
You must define an account for this Snap to communicate with your target CDW. Click the account specific to your target CDW below for more information:
Overview
You can use this Snap to perform mathematical operations/functions on your source dataset. This Snap provides multiple lists of supported mathematical functions based on the target database configured in its Account. Each list of functions is further segregated based on the number of operands/arguments it needs—unary (one) and binary (two).
Snap Type
ELT Math Function Snap is a TRANSFORM-type Snap that performs unary and binary mathematical operations on the dataset.
Prerequisites
Valid accounts and access permissions to connect to the following:
Source: AWS S3, Redshift, Azure Cloud Storage, or Google Cloud Storage
Target: Snowflake, Redshift, Azure Synapse, Databricks Lakehouse Platform, or BigQuery
Limitations and Known Issues
None.
Snap Input and Output
Input/Output | Type of View | Number of Views | Examples of Upstream and Downstream Snaps | Description |
---|---|---|---|---|
Input | Document |
|
| A document containing the SQL query that yields the data required to perform the mathematical (unary or binary) operations. |
Output | Document |
|
| A document containing the incremental SQL query that includes the mathematical functions defined in the Snap. |
Snap Settings
Click the = (Expression) button in the Snap's configuration, if available, to define the corresponding field value using expression language and Pipeline parameters.
You can use the SQL Expressions and Functions supported for ELT for this purpose. This list is common to all target CDWs supported. You can also use other expressions/functions that your target CDW supports.
Field names marked with an asterisk ( * ) in the table below are mandatory.
Field Name | Type | Description |
---|---|---|
Label* | String | The name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your Pipeline. Default Value: ELT Math Function |
Get preview data | Checkbox | Select this checkbox to include a preview of the query's output. The Snap performs limited execution and generates a data preview during Pipeline validation. In the case of ELT Pipelines, only the SQL query flows through the Snaps but not the actual source data. Hence, the preview data for a Snap is the result of executing the SQL query that the Snap has generated in the Pipeline. The number of records displayed in the preview (upon validation) is the smaller of the following:
Rendering Complex Data Types in Databricks Lakehouse Platform Based on the data types of the fields in the input schema, the Snap renders the complex data types like map and struct as object data type and array as an array data type. It renders all other incoming data types as-is except for the values in binary fields are displayed as a base64 encoded string and as string data type. Default Value: Not selected |
Pass through | Checkbox | Select this checkbox to specify that the Snap must include the incoming document (SQL query) in its output document. Default Value: Not selected |
Arithmetic unary functions | Specify your arithmetic unary function/operation to be performed on a single table column/its value, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row. This fieldset consists of the following fields:
| |
Arithmetic unary expression name* | String/Suggestion | Select an arithmetic unary expression/function to use. Click the Suggest icon to retrieve the list of the supported functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured.
Default Value: None. |
Argument* | String/Expression | Each of the arithmetic unary functions needs only one value/argument. Hence, specify an argument for the selected expression/function. This is typically a column name in your source table. |
Alias* | String | Specify the column in which to display the result of the unary function. You can also reference this name in downstream Snaps to process the data further. Default Value: None. |
Arithmetic binary functions | Specify your arithmetic binary function/operation to be performed on any two table columns/their values, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row. This fieldset consists of the following fields:
| |
Arithmetic binary expression name* | String/Suggestion | Select an arithmetic binary expression/function to use. Click the Suggest icon to retrieve the list of the supported arithmetic binary functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured.
In addition to the above, you can use certain mathematical functions with specific target CDWs as mentioned below:
Default Value: None. |
Argument* | String/Expression | Each of the arithmetic binary functions needs two values/arguments. Specify the first argument in this field for the selected expression/function. This is typically a column name in your source table. |
Argument* | String/Expression | Each of the arithmetic binary functions needs two values/arguments. Specify the second argument in this field for the selected expression/function. This is typically a column name in your source table. |
Alias* | String | Specify the column in which to display the result of the binary function. You can also reference this name in downstream Snaps to process the data further. Default Value: None. |
Rounding and truncation unary functions | Specify your rounding and truncation unary function/operation to be performed on a single table column/its value, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row. This fieldset consists of the following fields:
| |
Rounding and truncation unary expression name* | String/Suggestion | Select a rounding and truncation unary expression/function to use. Click the Suggest icon to retrieve the list of the supported functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured.
In addition to the above, you can use certain mathematical functions with specific target CDWs as mentioned below:
Default Value: None. |
Argument* | String/Expression | Each of the rounding and truncation unary functions needs only one value/argument. Hence, specify an argument for the selected expression/function. This is typically a column name in your source table. |
Alias* | String | Specify the column in which to display the result of the unary function. You can also reference this name in downstream Snaps to process the data further. Default Value: None. |
Rounding and truncation binary functions | Specify your rounding and truncation binary function/operation to be performed on any two table columns/their values, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row. This fieldset consists of the following fields:
| |
Rounding and truncation binary expression name* | String/Suggestion | Select a rounding and truncation binary expression/function to use. Click the Suggest icon to retrieve the list of the supported rounding and truncation binary functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured.
In addition to the above, you can use certain mathematical functions with specific target CDWs as mentioned below:
Default Value: None. |
Argument* | String/Expression | Each of the rounding and truncation binary functions needs two values/arguments. Specify the first argument in this field for the selected expression/function. This is typically a column name in your source table. |
Argument* | String/Expression | Each of the rounding and truncation binary functions needs two values/arguments. Specify the second argument in this field for the selected expression/function. This is typically a column name in your source table. |
Alias* | String | Specify the column in which to display the result of the binary function. You can also reference this name in downstream Snaps to process the data further. Default Value: None. |
Exponent and root unary functions | Specify your exponent and root unary function/operation to be performed on a single table column/its value, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row. This fieldset consists of the following fields:
| |
Exponent and root unary expression name* | String/Suggestion | Select an exponent and root unary expression/function to use. Click the Suggest icon to retrieve the list of the supported functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured.
In addition to the above, you can use certain mathematical functions with specific target CDWs as mentioned below:
Default Value: None. |
Argument* | String/Expression | Each of the exponent and root unary functions needs only one value/argument. Hence, specify an argument for the selected expression/function. This is typically a column name in your source table. |
Alias* | String | Specify the column in which to display the result of the unary function. You can also reference this name in downstream Snaps to process the data further. Default Value: None. |
Exponent and root binary functions | Specify your exponent and root binary function/operation to be performed on any two table columns/their values, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row. This fieldset consists of the following fields:
| |
Exponent and root binary expression name* | String/Suggestion | Select an exponent and root binary expression/function to use. Click the Suggest icon to retrieve the list of the supported exponent and root binary functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured.
Default Value: None. |
Argument* | String/Expression | Each of the exponent and root binary functions needs two values/arguments. Specify the first argument in this field for the selected expression/function. This is typically a column name in your source table. |
Argument* | String/Expression | Each of the exponent and root binary functions needs two values/arguments. Specify the second argument in this field for the selected expression/function. This is typically a column name in your source table. |
Alias* | String | Specify the column in which to display the result of the binary function. You can also reference this name in downstream Snaps to process the data further. Default Value: None. |
Logarithmic unary functions | Specify your logarithmic unary function/operation to be performed on a single table column/its value, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row. This fieldset consists of the following fields:
| |
Logarithmic unary expression name* | String/Suggestion | Select a logarithmic unary expression/function to use. Click the Suggest icon to retrieve the list of the supported functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured.
In addition to the above, you can use certain mathematical functions with specific target CDWs as mentioned below:
Default Value: None. |
Argument* | String/Expression | Each of the logarithmic unary functions needs only one value/argument. Hence, specify an argument for the selected expression/function. This is typically a column name in your source table. |
Alias* | String | Specify the column in which to display the result of the unary function. You can also reference this name in downstream Snaps to process the data further. Default Value: None. |
Logarithmic binary functions | Specify your logarithmic binary function/operation to be performed on any two table columns/their values, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row. This fieldset consists of the following fields:
| |
Logarithmic binary expression name* | String/Suggestion | Select a logarithmic binary expression/function to use. Click the Suggest icon to retrieve the list of the supported logarithmic binary functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured.
Default Value: None. |
Argument* | String/Expression | Each of the logarithmic binary functions needs two values/arguments. Specify the first argument in this field for the selected expression/function. This is typically a column name in your source table. |
Argument* | String/Expression | Each of the logarithmic binary functions needs two values/arguments. Specify the second argument in this field for the selected expression/function. This is typically a column name in your source table. |
Alias* | String | Specify the column in which to display the result of the binary function. You can also reference this name in downstream Snaps to process the data further. Default Value: None. |
Trigonometric general functions | Specify your trigonometric general function/operation to be performed on a single table column/its value, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row. This fieldset consists of the following fields:
| |
Trigonometric general expression name* | String/Suggestion | Select a trigonometric general expression/function to use. Click the Suggest icon to retrieve the list of the supported functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured.
Default Value: None. |
Alias* | String | Specify the column in which to display the result of the trigonometric general function. You can also reference this name in downstream Snaps to process the data further. Default Value: None. |
Trigonometric unary functions | Specify your trigonometric unary function/operation to be performed on a single table column/its value, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row. This fieldset consists of the following fields:
| |
Trigonometric unary expression name* | String/Suggestion | Select a trigonometric unary expression/function to use. Click the Suggest icon to retrieve the list of the supported functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured.
Default Value: None. |
Argument* | String/Expression | Each of the trigonometric unary functions needs only one value/argument. Hence, specify an argument for the selected expression/function. This is typically a column name in your source table. |
Alias* | String | Specify the column in which to display the result of the unary function. You can also reference this name in downstream Snaps to process the data further. Default Value: None. |
Trigonometric binary functions | Specify your trigonometric binary function/operation to be performed on any two table columns/their values, using this fieldset. Click to add a new row. Ensure that you specify each function in a separate row. This fieldset consists of the following fields:
| |
Trigonometric binary expression name* | String/Suggestion | Select a trigonometric binary expression/function to use. Click the Suggest icon to retrieve the list of the supported trigonometric binary functions. The list displays the functions supported by the database that you select in the Account settings. An error is displayed if an account is not configured.
Default Value: None. |
Argument* | String/Expression | Each of the trigonometric binary functions needs two values/arguments. Specify the first argument in this field for the selected expression/function. This is typically a column name in your source table. |
Argument* | String/Expression | Each of the trigonometric binary functions needs two values/arguments. Specify the second argument in this field for the selected expression/function. This is typically a column name in your source table. |
Alias* | String | Specify the column in which to display the result of the binary function. You can also reference this name in downstream Snaps to process the data further. Default Value: None. |
Troubleshooting
Error | Reason | Resolution |
---|---|---|
Missing property value | You have not specified a value for the mandatory field where this message is displayed. | Ensure that you specify appropriate values for all mandatory fields in the Snap configuration. |
Downloads