Functions

Use record functions to determine information about a record, such as the stage that created it or whether a field exists in the record.

You can replace any argument with a literal or an expression that evaluates to the argument. String literals must be enclosed in single or double quotation marks.

The expression language provides the following general record functions:

record:attribute(<attribute name>)

Returns the value of the specified record attribute. Use to return data, for example, for the "tag" attribute that you set using the File Tail origin.

Uses the following argument:

• attribute name - Name of the record header attribute.

Return type: String.

For example, the following expression returns the value of a tag attribute added by a File Tail origin:

${record:attribute('tag')}

record:attributeOrDefault(<attribute name> , <default value>)

Returns the value of the specified record header attribute. When the attribute does not exist or has no value, returns the specified default value.

Uses the following arguments:

• attribute name - Name of the record header attribute.
• default value - Value to use when the record header attribute does not exist or has no value.

Return type: Returns a string value when returning the field attribute value. Otherwise, returns the type of the specified default value.

For example, the following expression returns the value of the tag attribute, and returns NA when no value exists:

${record:attributeOrDefault('tag', 'NA')}

record:creator()

Returns the instance name of the stage that created the record, typically an origin stage. Data is stored in the stageCreator field of a record.

Return type: String.

For example, the following expression returns the instance name of the stage that creates each record:

${record:creator()}

record:eventCreation()

Returns the epoch timestamp when an event occurred. Use to return the creation timestamp from an event record. Returns null values when applied to other records.

Return type: String.

For example, the following expression returns the time that the event-generating stage generated an event:

${record:eventCreation()}

record:eventType()

Returns the event type from an event record. Returns null values when applied to other records.

Event generating stages create different event types. For information about the possible event types, see "Event Records" in the stage documentation.

Return type: String.

For example, the following expression returns the event type from an event record:

${record:eventType()}

record:eventVersion()

Returns the event version from an event record. Returns null values when applied to other records.

Return type: String.

For example, the following expression returns the event version from an event record:

${record:eventVersion()}

record:exists(<field path>)

Determines if the specified field is in a record. When the field is not in the record, returns false. When the field is in the record, returns true.

Return type: Boolean.

For example, the following expression returns true when the Name field exists in a record:

${record:exists('/Name')}

It returns false when the Name field is not part of the record.

record:fieldAttribute(<field path>, <field attribute name>)

Returns the value for the specified field attribute.

Uses the following arguments:

• field path - Path to the field with the attribute.
• field attribute name - Name of the field attribute.

Return type: String

For example, the following expression returns the value for the pk attribute on the ID field:

record:fieldAttribute('/ID', 'pk')

record:fieldAttributeOrDefault(<field path>, <field attribute name>, <default value>)

Returns the value for the field attribute when it exists. If the attribute does not exist, returns the specified default value.

Uses the following arguments:

• field path - Path to the field with the attribute.
• field attribute name - Name of the field attribute.
• default value - Value to use when the field attribute does not exist or has no value.

Return type: Returns a string value when returning the field attribute value. Otherwise, returns the type of the specified default value.

For example, the following expression returns the value for the pk attribute on the ID field when available, otherwise, it returns "n".

record:fieldAttributeOrDefault('/ID', 'pk', 'n')

record:id()

Returns the record ID. The record ID is also stored in the sourceId record header attribute.

Return type: String.

record:path()

Returns the stages in the pipeline that processed a record. Data is stored in the stagesPath field of a record. The field path uses the following format:

<origin instance name>:<next stage instance name>:<next stage instance name>:... 

Return type: String.

record:type(<field path>)

Checks the data type of a field. Possible return values are:

• Boolean
• Char
• Byte
• Short
• Integer
• Long
• Float
• Double
• Date
• Datetime
• Decimal
• String
• Time
• Byte-Array
• Map
• List

Return type: String.

record:value(<field path>)

Returns the value of the field in the appropriate data type.

Return type: When the field includes a value, returns the type based on the field value. When the field is null, the function returns no value.

Nulls are coerced based on the expected return type of the function. For example, if an EL is expected to return a string, null is coerced to an empty string. However, a null integer is coerced to 0.

record:valueOrDefault(<field path> , <default value>)

Returns the value of the field in the appropriate data type. If the field does not exist or if the field is null, returns the default value.

Use to provide a default value when a field does not exist or when a field is null.

Return type: Variable, based on whether the function returns the field value or default value.

Use delimited data record functions to process delimited data with the list root field type. If you configured an origin to process the delimited data with the list-map root field type, you can use standard record functions.

For more information about the delimited data root field types, see Delimited Data Root Field Type.

You can replace any argument with a literal or an expression that evaluates to the argument. String literals must be enclosed in single or double quotation marks.

Error record functions provide information about error records. Use error functions to process error records.

For example, you might use error functions in a Stream Selector to pass data to different error handling pipelines based on the type of error that occurred.

The expression language provides the following error functions:

Use Base64 functions to encode or decode data using Base64.

You can replace any argument with a literal or an expression that evaluates to the argument. String literals must be enclosed in single or double quotation marks.

The expression language provides the following Base64 functions:

base64:decodeBytes(<string>)

Returns a decoded byte array from a Base64 encoded string.

Return type: Byte Array.

Uses the following argument:

• string - The Base64 encoded string to decode.

For example, ${base64:decodeBytes(record:value("/ID"))} decodes the Base64 encoded string in the ID field as a byte array value.

base64:decodeString(<string>, <charset>)

Returns a decoded string from a Base64 encoded string using the specified character set.

Return type: String.

Uses the following arguments:

• string - The Base64 encoded string to decode.
• charset - The character set to use to decode the data.

For example, ${base64:decodeString(record:value("/ID"), "UTF-8")} decodes the Base64 encoded string in the ID field using the UTF-8 character set as a string value.

base64:encodeBytes(<byte array>, <urlSafe: true | false>)

Returns a Base64 encoded string value of the specified byte array.

Return type: String.

Uses the following arguments:

• byte array - The byte array to encode using Base64.
• urlSafe - When set to true, encodes the data so that it can be safely sent in a URL.

For example, ${base64:encodeBytes(record:value("/ID"), true)} uses Base64 to encode the byte array in the ID field such that the encoded data is URL safe.

base64:encodeString(<string>, <urlSafe: true | false>, <charset>)

Returns a Base64 encoded string value of the specified string.

Return type: String.

Uses the following arguments:

• string - The string to encode using Base64.
• urlSafe - When set to true, encodes the data so that it can be safely sent in a URL.
• charset - The character set to use to encode the data.

For example, ${base64:encodeString(record:value("/ID"), false, "UTF-8")} uses Base64 to encode the string in the ID field using the UTF-8 character set such that the encoded data is not URL safe.

Use credential functions to retrieve sensitive information stored as secrets from supported credential stores.

Before you use a credential function, you must configure a credential store. For more information about credential stores, see Credential Stores in the Data Collector documentation..

You can use credential functions in stages that require sensitive information, such as user names or passwords, to securely access data in external systems. Credential functions allow pipelines to access external systems without exposing sensitive details.

You cannot use credential functions in all stages. For example, you cannot use credential functions in the Expression Evaluator processor. If credential functions were allowed in stages such as the Expression Evaluator, any user with access to the pipeline could access or print sensitive values, compromising the security of the external system.

String literals must be enclosed in single or double quotation marks.

The expression language provides the following credential functions:

credential:get(<cstoreId>, <userGroup>, <name>)

Returns the secret from the credential store. Uses the following arguments:

• cstoreId - Unique ID of the credential store to use. Use the ID specified in the $SDC_CONF/credential-stores.properties file. For more information, see Enabling Credential Stores in the Data Collector.
• userGroup - Group that a user must belong to in order to access the secret. Only users that have execute permission on the pipeline and that belong to this group can validate, preview, or run the pipeline that retrieves the secret.

  If working with Control Hub, specify the group using the required naming convention: <group ID>@<organization ID>.

  To grant access to all users, specify the default all group when working only with Data Collector. When working with Control Hub and Data Collector version 3.16.0 or later, you can specify the default group using all or all@<organization ID>. StreamSets recommends using all so that you do not need to modify credential functions when migrating pipelines from Data Collector to Control Hub.

  Note: When working with Control Hub and a Data Collector version earlier than 3.16.0, you must use the default all@<organization ID> group.
• name - Name of the secret to retrieve from the credential store. Use the required format for the credential store:
  • AWS Secrets Manager - Enter the secret name using the following format:

    "<name><separator><key>"

    • <name> is the name of the secret to read.
    • <separator> is the separator defined in the $SDC_CONF/credential-stores.properties file.
    • <key> is the key for the value that you want returned.
  • CyberArk - Enter the secret name using the following format:

    "<safe><separator><folder><separator><object name>[<separator><element name>]"

    • <safe> is the CyberArk safe to read. <separator> is the separator defined in the $SDC_CONF/credential-stores.properties file.
    • <folder> is the CyberArk folder to read.
    • <object name> is the CyberArk object or secret to read.
    • <element name> is an optional name for the value that you want returned.

      If you do not specify <element name>, Data Collector uses Content.

  • Google Secret Manager - Enter the secret name using the following format:

    "<name><delimiter><version ID>"

    • <name> is the secret name.
    • <delimiter> is the delimiter defined in the $SDC_CONF/credential-stores.properties file.
    • <version ID> is the version of the secret to return.
  • Hashicorp Vault - Enter the secret name using the following format:

    "<path><separator><key>"

    • <path> is the path in Vault to read,
    • <separator> is the separator defined in the $SDC_CONF/credential-stores.properties file.
    • <key> is the key for the value that you want returned.
  • Java keystore - Enter the name of the secret added to the Java keystore file using the jks-credentialstore add command.
  • Azure Key Vault - Enter the name of the key or secret to retrieve from Azure Key Vault.

Return type: String.

For example, the following expression returns the value of the OracleDBPassword credential defined in the devjks Java keystore credential store and allows any user belonging to the devops group access to the credential when validating, previewing, or running the pipeline:

${credential:get("devjks", "devops@MyCompany", "OracleDBPassword")}

credential:getWithOptions(<cstoreId>, <userGroup>, <name>, <storeOptions>)

Returns the secret from the credential store using additional options to communicate with the credential store. Not applicable for the Java keystore or Google Secret Manager credential stores.

For example, use this function with the CyberArk credential store to define the separator for the secret name within the function. Or use this function with the Hashicorp Vault credential store when you might need a delay of 10 seconds or more before the credentials can be used successfully.

Uses the following arguments:

• cstoreId - Unique ID of the credential store to use. Use the ID specified in the $SDC_CONF/credential-stores.properties file. For more information, see Enabling Credential Stores in the Data Collector.
• userGroup - Group that a user must belong to in order to access the secret. Only users that have execute permission on the pipeline and that belong to this group can validate, preview, or run the pipeline that retrieves the secret.

  If working with Control Hub, specify the group using the required naming convention: <group ID>@<organization ID>.

  To grant access to all users, specify the default all group when working only with Data Collector. When working with Control Hub and Data Collector version 3.16.0 or later, you can specify the default group using all or all@<organization ID>. StreamSets recommends using all so that you do not need to modify credential functions when migrating pipelines from Data Collector to Control Hub.

  Note: When working with Control Hub and a Data Collector version earlier than 3.16.0, you must use the default all@<organization ID> group.
• name - Name of the secret to retrieve from the credential store. Use the required format for the credential store:
  • AWS Secrets Manager - Enter the secret name using the following format:

    "<name><separator><key>"

    • <name> is the name of the secret to read.
    • <separator> is the separator defined in the $SDC_CONF/credential-stores.properties file or in the store options passed with the function.

  • <key> is the key for the value that you want returned.

  • CyberArk - Enter the secret name using the following format:

    "<safe><separator><folder><separator><object name><separator><element name>"

    • <safe> is the CyberArk safe to read.
    • <separator> is the separator defined in the $SDC_CONF/credential-stores.properties file.
    • <folder> is the CyberArk folder to read.
    • <object name> is the CyberArk object to read.
    • <element name> is an optional name for the value that you want returned.

      If you do not specify <element name>, Data Collector uses Content.

  • Hashicorp Vault - Enter the secret name using the following format:

    "<path><separator><key>"

    • <path> is the path in Vault to read.
    • <separator> is the separator defined in the $SDC_CONF/credential-stores.properties file.
    • <key> is the key for the value that you want returned.
  • Azure Key Vault - Enter the name of the key or secret to retrieve from Azure Key Vault.
• storeOptions - Additional options to communicate with the credential store.
  For AWS Secrets Manager, you can use the following options:

  • separator - Specifies the separator for name and key values in the credential functions, overriding the credentialStore.aws.config.nameKey.separator property.
  • alwaysRefresh - When set to true, forces the key to refresh its cached value before Data Collector retrieves the value, overriding the credentialStore.aws.config.cache.ttl.millis property. Be aware that always refreshing the cached value significantly increases the pipeline run time.

  For CyberArk, you can use the following options:

  • separator - Separator to use for the secret name.
  • ConnectionTimeout - Connection timeout value.
  • FailRequestOnPasswordChange - Whether to fail the request on a password change, set to true or false.

  For Hashicorp Vault, you can use the delay option to enter a delay in milliseconds to allow time for external processing. Use the delay option when using the Vault AWS secret backend to generate AWS access credentials based on IAM policies. According to Vault documentation, you might need a delay of 10 seconds or more before the credentials can be used successfully.

  For Azure Key Vault, you can use the following options:

  • url - Overrides the credentialStore.azure.config.vault.url property in the $SDC_CONF/credential-stores.properties file.
  • retry - Overrides the credentialStore.azure.config.credential.retry.millis property in the $SDC_CONF/credential-stores.properties file.
  • refresh - Overrides the credentialStore.azure.config.credential.refresh.millis property in the $SDC_CONF/credential-stores.properties file.
  • credentialType=certificate - Instructs Azure Key Vault to retrieve the stored PEM certificate as a certificate rather than as a secret. Use to retrieve a PEM certificate stored in Azure Key Vault when you configure a stage to use a remote keystore or truststore for SSL/TLS encryption.
  Use the following format to specify options:

  "<option1>=<value>,<option2>=<value>"

  For example, to use the pipe symbol (|) as the CyberArk separator, enter the following for the options argument:

  "separator=|"

  Or to set the Vault delay to 1,000 milliseconds, enter the following for the options argument:

  "delay=1000"

Return type: String.

For example, the following expression returns the value of the key password stored in the Vault path /secret/databases/oracle from the vault credential store after waiting for a delay of 1,000 milliseconds. The name argument uses the default ampersand (&) as the separator. The expression allows any user belonging to the devops group access to the secret when validating, previewing, or running the pipeline:

${credential:getWithOptions("vault", "devops@MyCompany", "/secret/databases/oracle&password", "delay=1000")}

Use data drift functions to create alerts when data drift occurs. You can use these functions in data drift rules.

You can replace any argument with a literal or an expression that evaluates to the argument. String literals must be enclosed in single or double quotation marks.

Data generation functions are included in the Protector stage library. You can use data generation functions only in the Protector: Expression processor.

You can use field functions in field path expressions that determine the set of fields that a processor uses. Each function is evaluated against a set of matching fields individually.

For more information about field path expressions, see Field Path Expressions. For a list of stages where you can use field functions, see Supported Stages.

Use file functions to return information about a file name or path. For example, you might use a file function to remove a file extension from a file path or to return part of the path.

You can replace any argument with a literal or an expression that evaluates to the argument. String literals must be enclosed in single or double quotation marks.

Use job functions to return information about a Control Hub job. For example, you might use a job function to return the name of the job running a pipeline.

You can replace any argument with a literal or an expression that evaluates to the argument. String literals must be enclosed in single or double quotation marks.

Use math functions to perform math on numeric values.

You can replace any argument with a literal or an expression that evaluates to the argument. String literals must be enclosed in single or double quotation marks.

Use pipeline functions to determine information about a pipeline, such as the pipeline title or ID. The expression language provides the following pipeline functions:

pipeline:email()

Returns the email address of the user who started the pipeline.

Return type: String.

pipeline:id()

Returns the ID of the pipeline. The ID is a UUID automatically generated when the pipeline is created and is used by Data Collector to identify the pipeline. The pipeline ID cannot be changed.

Return type: String.

For example, you can include the function in the directory template for the Hadoop FS destination to include the pipeline ID in the output directory name. The following expression creates output directories based on the pipeline ID and timestamp of a record:

/outputfiles/${pipeline:id()}/${YYYY()}-${MM()}-${DD()}-${hh()}

pipeline:name()

Like pipeline:id, this function returns the ID of the pipeline. The ID is a UUID automatically generated when the pipeline is created and is used by Data Collector to identify the pipeline. The pipeline ID cannot be changed.

In Data Collector versions earlier than 2.3.0.0, this function returned the pipeline name. This function is retained for backward compatibility. Use pipeline:id for new development.

Return type: String.

For example, you can include the function in the directory template for the Hadoop FS destination to include the pipeline ID in the output directory name. The following expression creates output directories based on the pipeline ID and timestamp of a record:

/outputfiles/${pipeline:name()}/${YYYY()}-${MM()}-${DD()}-${hh()}

pipeline:startTime()

Returns the start time of the pipeline.

Return type: Datetime.

pipeline:title()

Returns the title or name of the pipeline.

Return type: String.

pipeline:user()

Returns the user who started the pipeline.

Return type: String.

pipeline:version()

Returns the pipeline version when the pipeline has been published to StreamSets Control Hub. Returns "UNDEFINED" if the pipeline has not been published to Control Hub. Use this function when you have registered Data Collector to work with Control Hub.

Return type: String.

Use string functions to transform string data.

You can replace any argument with a literal or an expression that evaluates to the argument. String literals must be enclosed in single or double quotation marks.

The expression language provides the following string functions:

Use time functions to return the current time or to transform datetime data.

You can replace any datetime argument with an expression that evaluates to a datetime value. You cannot replace a datetime argument with a datetime literal.

You can replace any long or string argument with a literal or an expression that evaluates to the argument. String literals must be enclosed in single or double quotation marks.

The expression language provides the following time functions:

time:createDateFromStringTZ(<string>, <time zone>, <date format>)

Creates a Date object based on a datetime in a String field and using the specified time zone. The datetime string should not include the time zone.

Uses the following arguments:

• string - String with datetime values, not including the time zone.
• time zone - Time zone associated with the datetime values. The time zone is used when creating the Date object.
  You can use the following time zone formats:

  • <area>/<location> - For example, America/Chicago or Europe/Madrid.
  • Numeric time zones with the GMT prefix, such as GMT-0500 or GMT-8:00. Note that numeric-only time zones such as -500 are not supported.
  • Short time zone IDs such as EST and CST - These time zones should generally be avoided because they can stand for multiple time zones, e.g. CST stands for both Central Standard Time and China Standard Time.
• date format - The date format used by the string data. For information about date formats, see https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html.

For example, the following expression converts string values in the Datetime field to a Date object using the time zone specified in the TZ field. It assumes the date is in the format, "03/30/2016 12:15:33 AM":

${time:createDateFromStringTZ(record:value('/Datetime'), 'record:value('/TZ')', 'MM/dd/yyyy hh:mm:ss a')}

time:dateAddition(<intervalType>, <number>, <date>)

Adds an interval to a datetime value.

Uses the following arguments:

• intervalType- String that defines the type of interval to add. Specify one of the following interval types:
  • YEAR

  • QUARTER
  • MONTH
  • DAY_OF_YEAR
  • DAY

  • WEEK

  • WEEKDAY

  • HOUR

  • MINUTE

  • SECOND

  • MILLISECOND
• number - Number to add to the datetime value. Specify a negative number to subtract from the datetime value.
• date - Datetime value to add the interval to. Use one of the following functions to specify the datetime value:
  • Enter the time:createDateFromStringTZ(<string>, <time zone>, <date format>) function to specify a fixed datetime.
  • Enter the time:now() function to use the current time of the Data Collector machine.
  • Enter the record:value(<field path>) function to use a date that is in a record field.

Return type: Date.

For example, the following expression adds ten months to the datetime value and returns the value 2012-03-05 00:00:00:

${time:dateAddition("MONTH", 10, time:createDateFromStringTZ("2011-05-05 00:00:00", "Pacific/Honolulu","yyyy-MM-dd HH:mm:ss"))}

The following expression adds ten months to the datetime value and returns a value that is ten months from the time of the Data Collector machine when the expression is run:

${time:dateAddition("MONTH", 10, time:now())}

time:dateDifference(<intervalType>, <datetime1>, <datetime2>)

Determines the interval between two datetime values.

Uses the following arguments:

• intervalType - String that defines the type of interval to return. Specify one of the following interval types:
  • YEAR

  • QUARTER
  • MONTH
  • DAY_OF_YEAR
  • DAY

  • WEEK

  • WEEKDAY

  • HOUR

  • MINUTE

  • SECOND

  • MILLISECOND
• datetime1 - Starting datetime value.
• datetime2 - Ending datetime value.

Use one of the following functions to specify both datetime values:

• Enter the time:createDateFromStringTZ(<string>, <time zone>, <date format>) function to specify a fixed datetime.
• Enter the time:now() function to use the current time of the Data Collector machine.
• Enter the record:value(<field path>) function to use a date that is in a record field.

Return type: Long.

For example, the following expression calculates the number of years between the two datetime values and returns the value 12:

${time:dateDifference("YEAR", time:createDateFromStringTZ("2023-05-05 00:00:00", "Pacific/Honolulu","yyyy-MM-dd HH:mm:ss"), time:createDateFromStringTZ("2011-05-05 00:00:00", "Pacific/Honolulu","yyyy-MM-dd HH:mm:ss"))}

time:dateTimeToMilliseconds(<Date object>)

Converts a Date object to an epoch or UNIX time in milliseconds.

For example, the following expression converts the current time to epoch or UNIX time in seconds, and then multiplies the value by 1000 to convert the value to milliseconds:

${time:dateTimeToMilliseconds(time:now())}

Return type: Long.

time:dateTimeZoneOffset(<Date object>, <time zone>)

Returns the time zone offset in milliseconds for the specified date and time zone. The time zone offset is the difference in hours and minutes from Coordinated Universal Time (UTC).

Uses the following arguments:

• Date object - Date object to use.
• time zone - Time zone associated with the Date object.
  You can use the following time zone formats:

  • <area>/<location> - For example, America/Chicago or Europe/Madrid.
  • Numeric time zones with the GMT prefix, such as GMT-0500 or GMT-8:00. Note that numeric-only time zones such as -500 are not supported.
  • Short time zone IDs such as EST and CST - These time zones should generally be avoided because they can stand for multiple time zones, e.g. CST stands for both Central Standard Time and China Standard Time.

For example, the following expression returns the time zone offset of the Date object stored in the Date field using the time zone specified in the TZ field:

${time:dateTimeZoneOffset(record:value('/Date'), record:value('/TZ'))}

Return type: Long.

time:extractDateFromString(<string>, <format string>)

Extracts a Date object from a String, based on the specified date format.

Uses the following arguments:

• string - String to extract the Date object from.
• format string - String that specifies the date format of the data in the <string> argument. For information about creating a date format, see https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html.

For example, the following expression converts the string '2017-05-01 20:15:30.915' to a Date object:

${time:extractDateFromString('2017-05-01 20:15:30.915','yyyy-MM-dd HH:mm:ss.SSS')}

Return type: Date object.

time:extractLongFromDate(<Date object>, <format string>)

Extracts a long value from a Date object, based on the specified date format.

Uses the following arguments:

• Date object - Date object to extract the long value from.
• format string - String that specifies the format of the Date data to be extracted. For information about creating a date format, see https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html.

For example, the following expression returns the month as a long value from the Date object stored in the Timestamp field:

${time:extractLongFromDate(record:value('/Timestamp'), "MM")}

Return type: Long.

Note: Because the function returns a long value, you cannot specify non-numeric data in the data format string. For example, the date format "MMM" returns the three character abbreviation for the month (such as "Jul"), which causes the function to return incorrect results.

time:extractStringFromDate(<Date object>, <format string>)

Extracts a string value from a Date object based on the specified date format.

Uses the following arguments:

• Date object - Date object to extract the string value from.
• format string - String that specifies the format of the Date data to be extracted. For information about creating a date format, see https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html.

For example, the following expression returns the month as a string value from the Date object stored in the Timestamp field:

${time:extractStringFromDate(record:value('/Timestamp'), "MMM")}

Return type: String.

time:extractStringFromDateTZ(<Date object>, <time zone>, <format string>)

Extracts a string value from a Date object, converting the GMT time in the Date object to the specified date format and time zone. The function adjusts for daylight savings when given the time zone in the appropriate format.

Uses the following arguments:

• Date object - Date object to use.
• time zone - Time zone to use in the conversion.

  To convert the time zone and adjust for daylight savings, use the <Area>/<Location> format. For example, America/Chicago or Europe/Madrid.

  Short time zone IDs such as EST and CST return data, but note that these time zones do not adjust for daylight savings, and should be avoided because they can stand for multiple time zones, e.g. CST stands for both Central Standard Time and China Standard Time.

  You can use numeric time zones with the GMT prefix, such as GMT-0500 or GMT-8:00, but these time zones also do not account for daylight savings. Note that numeric-only time zones such as -500 are not supported.

• format string - String that specifies the format to use to express the date. For information about creating a date format, see https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html.

Return type: String.

Returns an empty string when the time zone is not specified or invalid.

For example, the following expression converts a Date object to a string date while adjusting to the America/New_York time zone and daylight savings. It expresses the date in the format, "03/30/2016 12:15:33 AM":

${time:extractStringFromDateTZ(record:value('/datetime'), 'America/New_York', 'MM-dd-yyyy hh:mm:ss a')}

time:millisecondsToDateTime(<long>)

Converts an epoch or UNIX time in milliseconds to a Date object.

If the epoch or UNIX time is in seconds, multiply the value by 1000 to produce a value in the milliseconds range. For example, the following expression multiplies the epoch time in seconds stored in the epochTime field and then converts the value to a Date object:

${time:millisecondsToDateTime(record:value('/epochTime')* 1000)}

Return type: Date object.

time:now()

Returns the current time of the Data Collector machine as a java.util.Date object.

For example, you might use the function in the Expression Evaluator to create a field with the time of processing.

Return type: Datetime.

time:nowNanoInstant()

Returns a LocalDateTime object set to the current date and time of the Data Collector machine with nanoseconds precision.

Return type: LocalDateTime object.

time:nowNanoTimestampNumber()

Returns a Number object set to the current date and time of the Data Collector machine, specified as epoch time, with nanoseconds precision.

Return type: Number object.

time:nowNanoTimestampString()

Returns a String object set to the current date and time of the Data Collector machine, specified as epoch time, with nanoseconds precision.

Return type: String object.

time:nowNanoZonedInstant()

Returns a ZonedDateTime object set to the current date and time of the Data Collector machine with nanoseconds precision.

Return type: ZonedDateTime object.

time:nowNumber()

Returns the current time of the Data Collector machine as a Long with milliseconds precision.

Return type: Long.

time:timeZoneOffset(<time zone>)

Returns the time zone offset in milliseconds for the specified time zone. The time zone offset is the difference in hours and minutes from Coordinated Universal Time (UTC).

Uses the following argument:

• time zone - Time zone to use.
  You can use the following time zone formats:

  • <area>/<location> - For example, America/Chicago or Europe/Madrid.
  • Numeric time zones with the GMT prefix, such as GMT-0500 or GMT-8:00. Note that numeric-only time zones such as -500 are not supported.
  • Short time zone IDs such as EST and CST - These time zones should generally be avoided because they can stand for multiple time zones, e.g. CST stands for both Central Standard Time and China Standard Time.

For example, the following expression returns the time zone offset using the time zone specified in the TZ field:

${time:timeZoneOffset(record:value('/TZ'))}

Return type: Long.

time:trimDate(<datetime>)

Trims the date portion of a datetime value by setting the date portion to January 1, 1970.

For example, if the current time of the Data Collector machine is Jul 25, 2016 5:18:05 PM, then ${time.trimDate(time:now())} returns: Jan 1, 1970 5:18:05 PM.

Return type: Datetime.

time:trimTime(<datetime>)

Trims the time portion of a datetime value by setting the time portion to 00:00:00.

For example, if the value of the pickup_datetime field is Jul 22, 2016 11:16:10 AM, then ${time:trimTime(record:value("/pickup_datetime"))} returns: Jul 22, 2016 12:00:00 AM.

Return type: Datetime.

Use timestamp functions to return the current time or to transform datetime data.

Some of these functions have corresponding time functions. StreamSets recommends using timestamp functions for all new development for better performance.

You can replace any argument with a literal or an expression that evaluates to the argument. String literals must be enclosed in single or double quotation marks.

The expression language provides the following timestamp functions:

In miscellaneous functions, you can replace any argument with a literal or an expression that evaluates to the argument. String literals must be enclosed in single or double quotation marks.

The expression language provides the following miscellaneous functions:

alert:info()

Returns information about the trigger for a data drift alert. Use only in alert text for data drift alerts.

avro:decode(<schema>, <byte array>)

Returns an Avro record by using the specified schema to decode the specified byte array. You can use this function in the Kafka Message Key property when the Kafka stage processes Avro data.

Uses the following arguments:

• schema - Avro schema to use to decode the specified byte array.
• byte array - Byte array to decode.

emptyList()

Creates an empty list.

emptyMap()

Creates an empty map.

every(<interval>, < hh() | mm() | ss() >)

Represents the interval of hours, minutes, or seconds for generating output directories for the Hadoop FS, Local FS, or MapR FS destination.

When used, a destination generates output directories for the specified interval beginning on the hour. For example, when generating directories every 30 minutes, it generates a directory on the hour and on the half-hour.

You can use the function once in the Directory Template property to replace the hour, minute, or second datetime variables.

Use the function to replace the smallest time interval in the directory template.

Note: Destinations generate a directory for the smallest unit of measure by default, so do not use the every function to generate a directory every hour, minute, or second. For more information, see Directory Templates.

Uses the following arguments:

• interval - An integer factor or submultiple of 60 that represents the interval of minutes or seconds to wait between directory generation. Use one of the following values: 1, 2, 3, 4, 5, 6, 10, 12, 15, 20, or 30.
• < hh() | mm() | ss() > - Use hh() for hours, mm() for minutes, and ss() for seconds.

For example, the following Directory Template generates a new directory every twelve hours, beginning on the hour:

/outputfiles/${YY()}-${MM()}-${DD()}-${every(12,hh())}

The following Directory Template generates a new directory every fifteen minutes, beginning on the hour:

/outputfiles/${YY()}-${MM()}-${DD()}-${hh()}-${every(15,mm())}

The following Directory Template generates a new directory every 30 seconds, beginning on the hour:

/outputfiles/${YY()}-${MM()}-${DD()}-${hh()}-${mm()}-${every(30,ss())}

field:field()

Returns the field name. Available only in the Decimal field expression properties of the Hive Metadata processor.

Return type: String.

isEmptyList()

Returns true or false based on whether a list is empty.

Return type: Boolean.

isEmptyMap()

Returns true or false based on whether a map is empty.

Return type: Boolean.

jvm:maxMemoryMB()

Returns the Java heap size allocated to the Data Collector in MB.

length()

Returns the length of a list.

Return type: Integer.

list:join(<list field>, <separator>)

Merges elements in a List field into a String field, using the specified separator between elements.

Uses the following arguments:

• list field - The List field that you want to merge.
• separator - The string to use to separate the elements in the merged field.

For example, to merge the list in a colors field using a semicolon as a separator character, you can use the following expression:

${list:join(record:value('/colors'), ";")}

And if the list field includes "red", "blue", and "yellow" as elements, the expression produces the following string data:

red;blue;yellow

Return type: String.

list:joinSkipNulls(<list field>, <separator>)

Merges elements in a List field into a String field, using the specified separator between elements and skipping null values.

Uses the following arguments:

• list field - The List field that you want to merge.
• separator - The string to use to separate the elements in the merged field.

For example, say you use the following expression to merge the list in the colors field:

${list:join(record:value('/colors'), ";")}

And if the list field includes "red", "blue, null, "yellow", the expression ignores the null value and produces the following string data:

red;blue;yellow

Return type: String.

offset:column(<position>)

Returns the value of the positioned offset column for the current table. Available only in the additional offset column conditions of the JDBC Multitable Consumer origin.

Uses the following argument:

• position - Position of the offset column. For example, enter 0 for the first offset column defined in the table configuration. Enter 1 for the second defined offset column.

Return type: String.

runtime:availableProcessors()

Returns the number of processors available to the Java virtual machine. You can use this function when you want to configure multithreaded processing based on the number of processors available to Data Collector.

Return type: Integer.

runtime:conf(<runtime property name>)

Returns the value for the specified runtime property. Use to call a runtime property.

Uses the following argument:

• runtime property name - Name of the runtime property to call. The property must be defined in the Data Collector configuration file or in a separate runtime properties file specified in the Data Collector configuration file.

For more information, see Runtime Properties.

runtime:loadResource(<file name>, <restricted: true | false>)

Returns the value in the specified file, trimming any leading or trailing whitespace characters from the file. Use to call a runtime resource.

Uses the following arguments:

• file name - Name of the file that contains the information to be loaded. The file must reside in the $SDC_RESOURCES directory.
• restricted - Whether the file has restricted permissions. If set to true, the file must be owned by the system user who runs the Data Collector and read and writable only by the owner.

For example, the following expression returns the contents of the restricted JDBCpassword.txt file, trimming any leading or trailing whitespace characters:

${runtime:loadResource("JDBCpassword.txt", true)}

For more information about runtime resources, see Runtime Resources.

runtime:loadResourceRaw(<file name>, <restricted: true | false>)

Returns the entire contents in the specified file, including any leading or trailing whitespace characters in the file. Use to call a runtime resource.

Uses the following arguments:

• file name - Name of the file that contains the information to be loaded. The file must reside in the $SDC_RESOURCES directory.
• restricted - Whether the file has restricted permissions. If set to true, the file must be owned by the system user who runs the Data Collector and read and writable only by the owner.

For example, the following expression returns the entire contents of the restricted JDBCpassword.txt file, including any leading or trailing whitespace characters:

${runtime:loadResourceRaw("JDBCpassword.txt", true)}

For more information about runtime resources, see Runtime Resources.

runtime:resourcesDirPath()

Returns the full path to the directory for runtime resource files.

For example, when configuring a stage to use SSL/TLS encryption, use the following expression to define the name and location of the keystore file stored in the $SDC_RESOURCES directory:

${runtime:resourcesDirPath()}/keystore.jks

Return type: String.

sdc:hostname()

Returns the host name of the Data Collector machine.

For example, you might use the function in the directory template for the Hadoop FS destination to write to a directory that includes the Data Collector host name.

Return type: String.

sdc:id()

Returns the Data Collector ID.

For a pipeline that runs in standalone execution mode, the ID is a unique identifier associated with the Data Collector, such as 58efbb7c-faf4-4d8e-a056-f38667e325d0. The ID is stored in the following file: $SDC_DATA/sdc.id.

For a pipeline that runs in cluster mode, the ID is the Data Collector worker partition ID generated by a cluster application, such as Spark or MapReduce.

size()

Returns the size of a map.

Return type: Integer.

uuid:uuid()

Returns a randomly generated UUID. For example, you might use the function in an Expression Evaluator processor to generate a UUID for an ID field added to each record.

The uuid:uuid() function uses a lot of entropy on Linux systems and can cause your entropy pools to run dry. When this happens, your pipelines slow to a halt but continue to run. Throughput effectively goes to zero while the system waits for entropy to again become available. As a best practice, we recommend running the haveged daemon on any Data Collector node where the uuid:uuid() function is used. The haveged daemon regenerates your entropy pools.

Return type: String.

vault:read(<path>, <key>)

Returns the value for the key on the specified path. You can use the function in username, password, and similar properties such as AWS access key IDs and secret access keys. You can also use the function in HTTP headers and bodies when using HTTPS.

Important: This function is now deprecated and will be removed in a future release. We recommend using the credential functions available with the Vault credential store integration in pipelines that include JDBC stages. For more information, see Credential Stores in the Data Collector documentation.

Return type: String.

Uses the following arguments:

• path - The path in Vault to read.
• key - The key for the value that you want returned.

vault:readWithDelay(<path>, <key>, <delay>)

Returns the value for the key on the specified path after waiting the specified amount of time. Use when you want a delayed response to allow time for external processing.

Important: This function is now deprecated and will be removed in a future release. We recommend using the credential functions available with the Vault credential store integration in pipelines that include JDBC stages. For more information, see Credential Stores in the Data Collector documentation.

You can use the function in username, password, and similar properties such as AWS access key IDs and secret access keys. You can also use the function in HTTP headers and bodies when using HTTPS.

Return type: String.

Uses the following arguments:

• path - The path in Vault to read.
• key - The key for the value that you want returned.
• delay - Milliseconds to wait before returning the value.