DataZen Functions
DataZen Functions are available anywhere in a SQL CDC Script and allow you to shape field values or parameters during script execution.
When using DataZen Manager, these functions can be used in multiple places throughout DataZen, including:
- Data Pipelines Dynamic Columns
- URL or payload of a job that uses an HTTP connection
- Custom SQL or HTTP Scripts in a target operation
- Target object names for database tables, no-sql tables, and file names
- Initialization and Finalization database scripts when using a DB Target
- Most data pipeline configuration settings
DataZen functions can be early-bound or late-bound.
- #function(): Early-bound functions are applied immediately before the script executes
- @function(): Late-bound functions are applied during script execution and evaluated per row of data when possible
For example, the following uses both a script-level (early-bound) and row-level (late-bound) function call:
APPLY SCHEMA (
string globalGuid = '#rndguid()' -- same guid for all rows
string rowGuid = '@rndguid()' -- different guid for each row
);
The following SQL CDC commands can apply early-bound functions per row:
- APPLY HTTP when using the left join or inner join PROCESSING mode
- EXEC ON DB when using the PER_ROW processing option
- PUSH commands with BATCH 1
- PUSH commands with BATCH N and combined with one of the array operators (@concatjson, @concatjsonarr, @concatxml) [see Payload Formatting below]
Example: DataZen Functions in HTTP Payload
Manager UI Example
SQL CDC Example
PUSH INTO HTTP [http1] (POST /sharepoint/upsertlistitemraw)
ON_UPSERT
CONTENT_TYPE 'application/xxx-form-urlencoded'
PAYLOAD (uid=#rndguid()&name=mylist&data=<root><Title>{{ids}}</Title></root>)
WITH
BATCH 1;
In this example, the HTTP Payload provided to a target system contains a DataZen Function and a field from the source data: - a random guid: #rndguid() - a field name replacement: {{ids}}
Manager UI Hint
To insert a field or function, place the cursor at the desired insertion point,
then double-click on the field or function to insert.
You can nest functions by adding them together in a call.
For example, the following operation gets the current time in UTC, returns
an ISO8601 compliant date format, and URL encodes the output so it can be
passed in a URL function:
#urlencode(#toiso8601(#utcnow()))
or
#urlencode(#utcnow(s))
DataZen Functions
Data Functions
| Function | Description | Comments |
|
#data.calc(a) #data.calc(a, b) |
Performs a data-set calculation using an SQL-like operation and an optional filter. | #data.calc(AVG([age]), age >= 18) |
|
#data.calc_hwm(a, b) #data.calc_hwmdate(a, b) #data.calc_hwmnumber(a, b) |
Calculates the maximum between two values to calculate the next high watermark. Use calc_hwmdate to compare date values and calc_hwmnumber to compare numeric values. | #data.calc_hwmdate(updatedOnFieldName, #getvar(myhwmvar)) |
|
#data.count() #data.count(a) |
Returns the number of records in the data pipeline with an optional filter. | #data.count("age > 18") |
|
#data.sizekb() |
Returns the current data pipeline in-memory footprint in KB. | #data.sizekb() |
|
#data.value(a, b) |
Returns the value of a cell in the data pipeline by providing the row (0-based) and the column name. | #data.value(0, payload) |
|
#decrement(a) #decrement(a, b) |
Decrements a value by one, or by the amount specified. | #decrement({{field1}}, 1) |
| #increment(a) #increment(a, b) | Increments a value by one, or by the amount specified. | #increment({{field1}}, 25) |
| #isnotnull(a,b) | Returns value ‘b’ if value ‘a’ is not a NULL value | You can use this function to replace a non-NULL value with a constant, or the content of another field #isnotnull({{field1}}, 0) |
| #isnotnullorempty(a,b) | Returns value ‘b’ if value ‘a’ is not a NULL value nor an empty string | You can use this function to replace a non-NULL value or non-empty value with a constant, or the content of another field #isnotnullorempty({{field1}}, 0) |
| #isnull(a,b) | Returns value ‘b’ if value ‘a’ is a NULL value | You can use this function to replace a NULL value with a constant, or the content of another field #isnull({{field1}}, 0) |
| #isnullorempty(a,b) | Returns value ‘b’ if value ‘a’ is a NULL value or an empty string | You can use this function to replace a NULL value with a constant, or the content of another field #isnullorempty({{field1}}, 0) |
| #pick(a,b,c…) | Randomly selects a value from the list provided as parameters | Any number of parameters can be provided |
| #rnddouble(a,b) | Generates a random double value between two numbers | Both the lower and upper bound values are required |
| #rndguid() | Returns a new random GUID | |
| #rndint(a,b) | Generates a random integer value (Int32) between two numbers | Both the lower and upper bound values are required |
| #setvar(a,b) | Saves value b in variable a. The variable must exist already for this operation to succeed. If the variable is defined with encryption, the value will be encrypted. | #setvar(totalcount, 1000) |
| #statebag(a) #statebag(a, b) |
Gets or sets a statebag value that can be used as a pipeline variable throughout a pipeline execution. If the key does not exist, this operation returns an empty string, or null if the key name is surrounded by single quotes. |
Sets a statebag value with key TEST of the current datetime #statebag(TEST, #utcnow()) Get a statebag value by its key: TEST #statebag(TEST) Returns an empty string assuming key blahblah was not previously set #statebag(blahblah) Returns null assuming key blahblah was not previously set, assuming this operation is embeded in a SQL command #statebag('blahblah') |
|
#trygetvar(a) #trygetvar(a, b) |
Returns the value stored in an environment variable a if it exists. If the variable is stored encrypted, the value will be decrypted. If the variable doesn't exist, this operation returns b if provided or an empty string. | #trygetvar(totalcount, 0) |
| #trysetvar(a, b) | Saves value b in variable a if the environment variable a exists. If the variable is defined with encryption, the value will be encrypted. | #trysetvar(totalcount, 1000) |
String Functions
| Function | Description | Comments |
|
#compress(a) #compressu(a) #compressa(a) |
Compresses a string as utf-8, unicode (u), or ascii (a) into a string representation of a byte[] using the zlib algorithm |
The output of this operation is a string representing a byte[].
#compress({{fieldToCompress}}) |
|
#contains(a, b) #contains(a, b, c) |
Returns 1 if string b is found in a, optionally starting at index c; returns 0 if b is not found. | #contains('My name is', 'name') |
| #doublequoted(a) | Surrounds the string with double quotes if needed, and it is not null. | #doublequoted(TEST) |
| #format(a, b, …) | Uses the C# string.Format syntax to build a string with any number of parameters | The C# syntax uses {0} for the first parameter, {1} for the second, and so forth. #format('User {0} lives in {1}', {{userId}}, {{state}}) |
|
#frombase64(a) #frombase64a(a) #frombase64u(a) |
Converts a Base64 string into a string representation of a byte[] using UTF8 encoding (frombase64), ASCII (frombase64a) or Unicode (frombase64u). | #frombase64(dGVzdA==) |
|
#fromhex64(a) #fromhex64a(a) #fromhex64u(a) |
Converts a Hexadecimal string into a string representation using UTF8 encoding (fromhex64), ASCII (fromhex64a) or Unicode (fromhex64u). | #fromhex64(74657374) |
|
#hash_md5(a) #hash_md5a(a) #hash_md5u(a) #hash_sha1(a) #hash_sha1a(a) #hash_sha1u(a) #hash_sha256(a) #hash_sha256a(a) #hash_sha256u(a) #hash_sha384(a) #hash_sha384a(a) #hash_sha384u(a) #hash_sha512(a) #hash_sha512a(a) #hash_sha512u(a) |
Performs a hash transformation using the algorithm specified, and using UTF-8 (default), ASCII, or Unicode encoding. |
#hash_sha1('this is the content to hash using sha1 and UTF-8 encoding') #hash_sha256a('this is the content to hash using sha256 and ASCII encoding') |
|
#indexof(a, b) #indexof(a, b, c) |
Returns the index position of a character or string b within a, and optionally starting from index c; returns -1 if b is not found. |
#indexof('My name is', 'name') #indexof('My name is', 'name', 2) |
| #left(a, n) | Returns the left-most n characters of a string | #left('this is a test', 5) |
| #len(a) | Returns the length of a string | #len('this is a test') |
| #ltrim(a) #ltrim(a, b) |
Removes leading spaces or a set of characters specified by b from a |
#ltrim(' this is a test ') #ltrim('___this is a test!!!', '_') |
| #replace(a, b, c) | Replaces all occurrences of b with c, in string a | #replace('this is a cat', 'cat', 'dog') |
| #reverse(a) | Reverses the sequence of characters found in a. | #reverse('radar') |
| #right(a, b) | Takes the last b characters from the content specified in a. | #right('this is a cat', 3) |
| #rtrim(a) #rtrim(a, b) |
Removes trailing spaces or a set of characters specified by b from a |
#rtrim(' this is a test ') #rtrim('___this is a test!!!', '!') |
| #skip(a,n) | Skips the first n characters from the string provided as the first parameter | #skip(my name, 3) |
| #singleline(a) | Removes any line-feed and carriage return characters from the content within. This function is normally used when formatting string values in a JSON documents. | #singleline(This is a sample message that will be turned into a single line text) |
| #singlequoted(a) | Surrounds the string with single quotes if needed, and it is not null. | #singlequoted(TEST) |
|
#substring(a,b) #substring(a,b,c) |
Returns a subset of characters from input string a, starting at index b. If c is not provided, returns input string a starting at index b. If b is greater than the length of a, returns an empty string. |
Returns 123 #substring(123-45-8888, 0, 3) Returns 123 #substring("123-45-8888", 0, 3) Returns '8888' #substring('123-45-8888', 7) |
| #tolower(a) | Converts a string into a lower-case representation | #tolower('TEST') |
| #toupper(a) | Converts a string into an upper-case representation | #tolower('test') |
|
#tobase64(a) #tobase64a(a) #tobase64u(a) |
Transforms a value into an Hex64 representation using UTF8 encoding (#tobase64), ASCII (#tobase64a) or Unicode (#tobase64u). | #tohex64(‘this is a test’) |
|
#tohex64(a) #tohex64a(a) #tohex64u(a) |
Transforms a value into an Hex64 representation using UTF8 encoding (#tohex64), ASCII (#tohex64a) or Unicode (#tohex64u). | #tohex64(‘this is a test’) |
| #trim(x) | Removes leading and trailing spaces from the string provided | #trim(' this is a test ') |
|
#uncompress(a) #uncompressu(a) #uncompressa(a) |
Decompresses a byte array of compressed utf-8 data, unicode (u), or ascii (a) into a string using the zlib algorithm |
The output of this operation is a string.
#uncompress({{fieldToUncompress}}) |
| #unquote(a) | Removes surrounding quotes or double-quotes if any |
This example returns a string that can be interpreted as an integer #unquote('123456789') This will return the HEX representation of TEST without surrounding quotes #tohex64('TEST').unquote() |
|
#unzip(a) #unzipa(a) #unzipu(a) |
Decompresses a byte array of compressed utf-8 data, unicode (u), or ascii (a), into a string using the GZip algorithm |
The output of this operation is a string.
#unzip({{fieldToUnzip}}) |
| #urldecode(c) | Decodes a string from its escaped representation | |
| #urlencode(c) | URL encodes the value provided so it is safe to pass as a URL parameter. | |
| #zip(a) #zipu(a) #zipa(a) |
Compresses a string into a byte array returned as a Base64 encoded utf-8, ascii (a),or unicode (u), string using the GZip algorithm |
The output of this operation is a string.
#zip({{fieldToUnzip}}) |
Date Functions
| Function | Description | Comments |
|
#dateadd(date, timespan) #dateadd(date, timespan, format) #utcdateadd(date, timespan) #utcdateadd(date, timespan, format) |
Adds (or substracts) a timestamp to a date provided as a local datetime (or utc datetime) and returns the new date in the format specified as a local datetime (or utc datetime). The timestamp can contain the following characters: ymdhns, and start with - for a negative timestamp. Default format: s (sortable). | #dateadd(#now(), -5d30n, s) |
| #epoch() | Returns the current UTC date/time as a Unix timestamp in seconds | #epoch() |
| #epoch_ms() | Returns the current UTC date/time as a Unix timestamp in milliseconds | #epoch_ms() |
| #formatdate(date, format) #utcformatdate(date, format) | Formats a string value into a date with a specific format (following the C# DateTime.ToString(format) notation) | #formatDate(11/01/2000T08:00:00.0000, 0:MM/dd/yy H:mm:ss) |
| #fromepoch(a) #fromepoch(a,b) #fromepoch_sec(a) #fromepoch_sec(a,b) |
Converts an epoch number, provided as seconds, into a date/time. Parameter 'b' indicates the date formatting to use. If no date formatting is used, the format provided is unspecified and not guaranteed to remain the same over time. |
#fromepoch(1728440034, G) #fromepoch_sec(1728440034, o) |
| #fromepoch_ms(a) #fromepoch_ms(a,b) |
Converts an epoch number, provided as milliseconds, into a date/time. Parameter 'b' indicates the date formatting to use. If no date formatting is used, the format provided is unspecified and not guaranteed to remain the same over time. |
#fromepoch_ms(1729540001453, G) #fromepoch_ms(1729540001453, o) |
| #now() #now(a) |
Returns the current datetime in the local timezone of the server on which the agent is running with an optional date formatting parameter |
The following returns the current local date of the server running the agent using the ISO 8601 standard #now(o) |
| #parsedate(a, b, c) | Returns a date/time representation of the string provided as 'a', using 'b' as the date format to use for 'a', and 'c' as an optional output date format. | #parsedate(31 12 2018 23:50, dd MM yyyy HH:mm, G) |
| #toepoch(a) #toepoch(a,b) #toepoch_sec(a) #toepoch_sec(a,b) |
Converts a date/time value (parameter 'a') to an epoch number, in seconds. Parameter 'b' indicates whether the date provided is in UTC already (1 or true); this is useful if the date string does not contain timezone information. If the date string already contains timezone information, parameter 'b' is ignored. |
#toepoch('10/9/2024 2:13:54 AM', 1) #toepoch_sec('2024-09-10T02:13:54.000Z') |
| #toepoch_ms(a) #toepoch_ms(a,b) |
Converts a date/time value (parameter 'a') to an epoch number, in millseconds. Parameter 'b' indicates whether the date provided is in UTC already (1 or true); this is useful if the date string does not contain timezone information. If the date string already contains timezone information, parameter 'b' is ignored. |
#toepoch_ms('10/9/2024 2:13:54 AM', 1) #toepoch_ms('2024-09-10T02:13:54.000Z') |
| #toiso8601(x) | Converts a datetime field into a ISO 8601 compliant format | This function converts a datetime field into a string in the ISO 8601 format assuming the date is in the current timezone |
| #torfc1123(x) | Converts a datetime field into a RFC 1123 compliant format | |
| #utcnow() #utcnow(o) |
Returns the current datetime in UTC format with an optional date formatting parameter |
The following returns the current UTC date using the ISO 8601 standard #utcnow(o) |
| #utctoiso8601(x) | Converts a UTC datetime field into a ISO 8601 compliant format | This function converts a datetime field into a string in the ISO 8601 format assuming the date is in the UTC timezone |
| #utctorfc1123(c) | Converts a UTC datetime field into a RFC 1123 compliant format |
Other Functions
| Function | Description | Comments |
| #decrypt(a, b) | Decrypts the content provided in P2 as a Base64 string using the X.509 certificate identified by thumbprint P1. The certificate must be added to the agent by an administrator. | #decrypt(8F79289A8650DE95F321379EE937EF109DAD8D56, '.........') |
| #encrypt(a, b) | Encrypts the content provided in P2 as a Base64 string using the X.509 certificate identified by thumbprint P1. The certificate must be added to the agent by an administrator. | #encrypt(8F79289A8650DE95F321379EE937EF109DAD8D56, 'Content to encrypt') |
| #jsonformat(a) | Parses the JSON content (provided as a string) and applies JSON formatting settings specified by #setjsonsettings | #setjsonsettings(nullvaluehandling:ignore) #jsonformat({\"id\": 1, \"name\": null}) |
| #parse(a, b, c) | Parses the content P1 (provided as a string) as an XML, JSON, or CSV file, using the Path P2 provided (may be null), and returns the attribute P3 given its name or index. | #parse(<root><data><user><name></name>James</user></data></root>, //user, name) |
| #parsejson(a, b, c) | Parses the content P1 (provided as a string) as a JSON document, using the Path P2 provided (may be null), and returns the attribute P3 given its name or index. | #parsejson('{ "id": 1, "name": "james", "age": null }', null, id) |
| #parsexml(a, b, c) | Parses the content P1 (provided as a string) as an XML document, using the Path P2 provided (may be null), and returns the attribute P3 given its name or index. | #parse(<root><data><user><name></name>James</user></data></root>, //user, name) |
| #setjsonsettings(key1:val1,key2:val2,...) |
Sets JSON formatting settings specified before formatting a JSON document. Supported key/values: nullvaluehandling: ignore, include culture: invariantculture, currentculture, fr-FR, ... dateformatstring: a valid date format (ex: yyyy-MM-ddTHH:mm:ss) dateformathandling: isodateformat, microsoftdateformat dateparsehandling: datetime, datetimeoffset, none datetimezonehandling: local, roundtripkind, utc, unspecified floatparsehandling: double, decimal formatting: none, indented stringescapehandling: default, escapehtml, escapenonascii |
#setjsonsettings(nullvaluehandling:ignore, formatting:indented) #jsonformat({\"id\": 1, \"name\": null}) |
| #iif(a,b,c) | Conditionally returns b or c depending on the result of a. A false condition is achieved if the value of a is "null", an empty string, 0, or "false". If b or c can contain commas, you must enclose them with single or double quotes. | #iif(@pagingparam, "150.00", "150,000.00") |
| #last_http_headers() | Returns last HTTP Headers of a SELECT HTTP operation | #last_http_headers() |
| #last_http_content() | Returns last HTTP Content of a SELECT HTTP operation | #last_http_content() |
| #last_http_mediatype() | Returns last HTTP Media Type of a SELECT HTTP operation | #last_http_mediatype() |
| #last_http_status() | Returns last HTTP Status of a SELECT HTTP operation | #last_http_status() |
| #last_http_bytecount() | Returns last HTTP byte count of a SELECT HTTP operation | #last_http_bytecount() |