TQL reference

DADD(DateTime, Period)

add a Period to a DateTime value

for example, DADD(ts, DHOURS(2)) adds two hours to the value of ts

DAFTER(DateTime, DateTime)

true if the second date is after the first

DBEFORE(DateTime, DateTime)

true if the second date is before the first

DBETWEEN(DateTime, DateTime, DateTime)

true if the first date is after the second and before the third

DBETWEEN( origTs, DSUBTRACT(ts, DSECS(1)), DADD(ts, DSECS(1)) ) == true

DDAYS(DateTime)

return the day of the month of the DateTime

DDAYS(Integer)

return Integer days as a Period for DADD or DSUBSTRACT

DDIFF(DateTime, DateTime)

return a Period in which the difference in milliseconds between the two dates is stored

DDIFF(LocalDate, LocalDate)

return the number of whole days between the two partial datetimes as an Integer

DHOURS(DateTime)

return the hour of the day of the DateTime

DHOURS(Integer)

return Integer hours as a Period for DADD or DSUBSTRACT

DMILLIS(DateTime)

return the milliseconds of the DateTime

DMILLIS(Integer)

return Integer milliseconds as a Period for DADD or DSUBSTRACT

DMINS(DateTime)

return the minutes of the hour of the DateTime

DMINS(Integer)

return Integer minutes as a Period for DADD or DSUBSTRACT

DMONTHS(DateTime)

return the month of the year of the DateTime

DMONTHS(Integer)

return Integer months as a Period for DADD or DSUBSTRACT

DNOW()

return the current system time as DateTime

DSECS(DateTime)

return the seconds of the DateTime

DSECS(Integer)

return Integer seconds as a Period for DADD or DSUBSTRACT

DSUBTRACT(DateTime, Period)

subtract a Period from a date

for example, DSUBTRACT(ts, DHOURS(2)) subtracts two hours from the value of ts

DYEARS(DateTime)

return the year of the DateTime

DYEARS(Integer)

return Integer years as a Period for DADD or DSUBSTRACT

TO_DATE(Long)

convert an epoch time value to a DateTime

See MultiLogApp for an example.

TO_DATE(Object)

convert a Date, sql.Date. sql.Timestamp, Long, or String to a DateTime

For String input, recommended only for patterns not supported by TO_DATEF.

Depending on the format of the input value, output format may be an ISO-formatted date, yyyy/MM/dd, yyyy/MM/dd with time, yyyy-MMM-dd, yyyy-MMM-dd with time, or yyyy/MM/dd HH:mm:ss.SSS. Use TO_Date(Object, String) for other patterns.

When using an aggregate function on a DateTime field, use TO_DATE to convert the returned object to a DateTime, for example, select TO_DATE(last(dateTime)) as dateTime.

TO_DATE(Object, String)

convert a String to a DateTime using any org.joda.time.format.DateTimeFormat pattern

Recommended only for patterns not supported by TO_DATEF. See MultiLogApp for an example.

TO_DATEF(Object, String)

convert a String to a DateTime using an org.joda.time.format.DateTimeFormat pattern containing only y, M, d, h, m, s, and S

TO_DATEF is over ten times faster than the TO_DATE functions, so is preferred for supported formats. 

See the joda-time API reference for information on writing pattern strings; see PosApp for an example.

TO_STRING(DateTime, String)

convert a DateTime to a String with specific format

TO_ZONEDDATETIME (Long)

convert an epoch time value to a java.time.ZonedDateTime

TO_ZONEDDATETIME (Object)

convert a String to a java.time.ZonedDateTime using the yyyy-MM-dd HH:mm:ss.SSSSSSSSS z pattern

If the String does not match the yyyy-MM-dd HH:mm:ss.SSSSSSSSS z pattern, use TO_ZONEDDATETIME(Object,String).

TO_ZONEDDATETIME (Object, String)

convert a String to a java.time.ZonedDateTime using any org.joda.time.format.DateTimeFormat pattern

See the joda-time API reference for information on writing pattern strings.

AVROTOJSON(Object datum, Boolean IgnoreNulls)

convert an Avro node to a JSON node

Object datum must be an Avro GenericRecord present in an AvroEvent output by a source using an AvroParser.

If Boolean IgnoreNulls is true, any Avro fields with null values will be omitted from the JSON, so, for example, {a: 100, b: null, c: 'test'} will return{a:100, c:'test'}.

clearUserData()

See Adding user-defined data to JSONNodeEvent streams.

JSONArrayAdd(JsonNode node, Object value)

add object value at the end of array node

Use .get() to select the array. For example, JSONArrayAdd(data.get("PhoneNumbers"),"987") will add 987 to the end of the PhoneNumbers array node.

JSONArrayInsert(JsonNode node, int index, Object value)

add object value as an element at position index in array node

Use .get to select the array. For example, JSONArrayInsert(data.get("PhoneNumbers"),0,"987") will insert 987 at the beginning of the PhoneNumbers array. Object value must be deserialized as per Jackson ObjectMapper.readTree.

JSONFrom(Object value)

create a JSONNode from object value

For example, JSONFrom('{ "name":"John", "age":30, "city":"New York"}'). Object value must be deserialized as per Jackson ObjectMapper.readTree.

JSONGetBoolean(JsonNode node, String field)

get a Boolean value from specified field of JSONNode node

If the field is a Boolean, returns true or false. For other types, returns false.

JSONGetDouble(JsonNode node, String field)

get a double value from specified field of JSONNode node

If the field is numeric (that is, isNumber() returns true), returns a 64-bit floating point (double) value. For other types, returns 0.0. For integer values, conversion is done using default Java type coercion. With BigInteger values, this may result in overflows.

JSONGetInteger(JsonNode node, String field)

get an integer value from specified field of JSONNode node

If the field is numeric (that is, isNumber() returns true), returns an integer value. For other types, returns 0. For floating-point numbers, the value is truncated using default Java type coercion.

JSONGetString(JsonNode node, String field)

get a string value from specified field of JSONNode node

Non-string values (that is, ones for which isTextual() returns false) are returned as null. Empty string values are returned as empty strings.

JSONNew()

create an empty JSONNode object

JSONRemove(JsonNode node, Collection< String >fieldNames)

remove specified fields from of JSONNode node

For example, SELECT JSONRemove(data, "ID").

JSONSet(JsonNode node, String field, Object value)

set the value specified field in specified JSONNode to object value

Overwrites any existing value. Object value must be deserialized as per Jackson ObjectMapper.readTree.

makeJSON(String jsonText)

create a JSONNode

putUserdata()

See Adding user-defined data to JSONNodeEvent streams.

removeUserData()

See Adding user-defined data to JSONNodeEvent streams.

TO_JSON_NODE(Object obj)

convert object to a JSON node

Object must be in ObjectMapper.readTree format.

USERDATA()

See Adding user-defined data to JSONNodeEvent streams.

putUserData()

Works exactly like the putUserData() function for JSONNodeEvent.

See Adding user-defined data to JSONNodeEvent streams for details.

USERDATA(ParquetEvent event, String key)

Works exactly like the USERDATA() function for JSONNodeEvent.

See Adding user-defined data to JSONNodeEvent streams for details.

ARLEN(String)

returns the number of fields in the specified array

see Handling variable-length events with CQs for an example

IP_CITY(String)

get the city for an IP address

uses MaxMind GeoIP

IP_COUNTRY(String)

get the country for an IP address

uses MaxMind GeoIP

IP_LAT(String)

get the latitude for an IP address

uses MaxMind GeoIP

IP_LON(String)

get the longitude for an IP address

uses MaxMind GeoIP

match(String s, String regex)

match(String s, String regex, Integer groupNumber)

match the string using the specified regex expression. You can optionally specify the capture group number (the default is 0).

supports only single return value )see Using regular expressions (regex))

maxOccurs(String)

value that had the maximum occurrences in the String

see MultiLogApp for examples

replaceString(Event s, String findString, String newString)

for input stream s, replaces all occurrences of findString (in all fields) with newString

For example, SELECT replaceString(s,'MyCompany','PartnerCompany') replace all occurrences of MyCompany with PartnerCompany.

Use only with events of user-defined types.

replaceStringRegex(Event s, String regex, String newString)

for input stream s, replaces all strings (in all fields) that match the specified regex expression with newString

For example, SELECT replaceStringRegex(s,’\\s’,’’) would remove all whitespace, and SELECT replaceStringRegex(s,’\\d’,’x’) would replace all numerals with x.

Use only with events of user-defined types.

SLEFT(Object, Integer)

returns only the characters to the left of position Integer from the object

SRIGHT(Object, Integer)

returns only the characters to the right of position Integer from the object

for example, SRIGHT(orderAmount,1) would remove a dollar, Euro, or other currency sign from the beginning of a string

TO_BOOLEAN(Object)

convert a string to a Boolean

TO_STRING(Object)

convert any object to a string

BEFORE(String) / BEFOREORDERED(String)

returns the values in the WAEvent before array of the specified stream as a java.util.HashMap, with column names as the keys

see Using the DATA(), DATAORDERED(), BEFORE(), and BEFOREORDERED() functions

changeOperationToInsert

See "To Staging" in Using database event transformers.

clearUserdata

See Adding user-defined data to WAEvent streams.

DATA[Integer]

returns the value from field number Integer in a WAEvent data array

see Parsing the fields of WAEvent for CDC readers

DATA(String) / DATAORDERED(String)

returns the values in the WAEvent data array of the specified stream as a java.util.HashMap, with column names as the keys

see Parsing the fields of WAEvent for CDC readers , Using the DATA() function, and Using the DATA(), DATAORDERED(), BEFORE(), and BEFOREORDERED() functions

IS_PRESENT()

see Parsing the fields of WAEvent for CDC readers

maxOccurs(String)

value that had the maximum occurrences in the String

see MultiLogApp for examples

META(<stream name>, key)

extracts a value from a WAEvent METADATA map

see Using the META() function

MODIFY()

See Changing and masking field values using MODIFY and Modifying and masking values in the WAEvent data array using MODIFY.

putUserdata

See Adding user-defined data to WAEvent streams.

replaceData()

See Modifying the WAEvent data array using replace functions.

replaceString()

See Modifying the WAEvent data array using replace functions.

replaceStringRegex()

See Modifying the WAEvent data array using replace functions.

send()

See Using send() functions

USERDATA(stream name,key)

extracts a value from a WAEvent USERDATA map

See Adding user-defined data to WAEvent streams.

VALUE(stream name,key)

returns the value in the specified WAEvent stream that matches the specified key

See NetFlow Parser, NVP (name-value pair) Parser, or SNMP Parser for examples of use.