Striim

Welcome to the Striim Help Center and Community Site

3.10.3 TQL reference

Follow

This section covers TQL data types, operators, functions, and reserved keywords. For data definition statements, see DDL and component reference.

Supported data types

SQL data types are not supported in TQL. The following Java data types are supported:

  • java.lang.Byte

  • java.lang.Double

  • java.lang.Float

  • java.lang.Integer

  • java.lang.Long

  • java.lang.Short

  • java.lang.String

For convenience, you may specify these in TQL as byte, double, float, integer, long, short, and string, and they will be converted to the above types on import.

org.joda.time.DateTime is imported automatically and may be specified as DateTime.

All these types support nulls.

Operators

TQL supports the following operators.

Arithmetic operators

See https://www.w3resource.com/sql/arithmetic-operators/sql-arithmetic-operators.php for an introduction.

  • +: add

  • -: subtract

  • *: multiply

  • /: divide

  • %: modulo

Comparison operators

See http://www.sqltutorial.org/sql-comparison-operators for an introduction.

  • =: equal to

  • !=: not equal to

  • <>: not equal to

  • >: greater than

  • <: less than

  • >=: greater than or equal to

  • <=: less than or equal to

  • !<: not less than

  • !>: not greater than

Logical operators

See http://www.sqltutorial.org/sql-logical-operators for an introduction.

Functions

TQL supports the following functions.

Functions for supported data types

Striim supports all native functions for these supported data types:

Aggregate functions

To avoid unexpected results from a SELECT statement containing an aggregate function :

  • Always include a GROUP BY clause.

  • If selecting from a window, all fields other than the one in the GROUP BY clause should use an aggregate function. For example, instead of SELECT a, b, SUM(c) FROM WINDOW10s GROUP BY a, you should use SELECT a, LAST(b), SUM(c) FROM WINDOW10s GROUP BY a.

function

notes

AVG

Works only with Double and Float. To calculate an average for an Integer or Long field, cast it as Double or Float. For example:

SELECT
  AVG(TO_FLOAT(MyWindow.PosData)) AS AvgPosData
FROM MyWindow;

COUNT [DISTINCT]

FIRST

returns Java.lang.Object: see Using FIRST and LAST

LAST

returns Java.lang.Object: see Using FIRST and LAST

LIST(Object,...)

returns a collection of events: see Using pattern matching for an example

MAX

MIN

SUM

Date functions

See http://joda-time.sourceforge.net/apidocs/org/joda/time/Period.html for an explanation of the Period object. A P in printed results represents a Period object.

function

description

notes

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.

Striim supports all date functions natively associated with Joda-Time. See http://joda-time.sourceforge.net/apidocs for more information.

JSONNode functions

Use the following functions in CQs with an input and/or output stream of type JSONNodeEvent, or to create or manipulate any other JSONNode objects.

When the JSONNode objects are supplied by the CQ's input stream, JsonNode node is the DATA element of JSONNodeEvent. If there is more than one JSONNodeEvent input stream, choose one by using an alias for the stream, for example, s.data.

function

description

notes

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.

Masking functions

The primary use for these functions is to anonymize personally identifiable information, for example, as required by  the European Union's General Data Protection Regulation.

The String value argument is the name of the field containing the values to be masked.

The String functionType argument is ANONYMIZE_COMPLETELY, ANONYMIZE_PARTIALLY, or a custom mask:

function

notes

maskCreditCardNumber(String value, String functionType)

Input must be of the format ####-####-####-#### or ################. For the value 1234-5678-9012-3456, partially anonymized output would be xxxx-xxxx-xxxx-3456 and fully anonymized would be xxxx-xxxx-xxxx-xxxx. For the value 1234567890123456, partially anonymized output would be xxxxxxxxxxxx3456 and fully anonymized would be xxxxxxxxxxxxxxxx. 

maskEmailAddress(String value, String functionType)

Input must be a valid email address. For the value msmith@example.com, partially anonymized output would be mxxxxx@example.com and fully anonymized would be xxxxxxxxxxxxxxxxxx.

maskGeneric(String value, String functionType)

Input may be of any length. Partially anonymized output masks all but the last four characters, fully anonymized masks all characters.

maskPhoneNumber(String value, String functionType)

The input field format must be a ten-digit telephone number in the format ###-###-####, (###)-###-####, ##########, +1-###-###-####, +1(###)###-####, +1##########, or +1(###)#######.

For the value 123-456-7890 or +1-123-456-7890, partially anonymized output would be xxx-xxx-7890 and fully anonymized would be xxx-xxx-xxxx.

If you use a custom mask and the input field values are of varying lengths, use ELSE functions to handle each length. See Changing and masking field values using MODIFY for an example.

maskPhoneNumber(String value, String regex, Integer group)

The String regex parameter is a regular expression that matches the phone number pattern and splits it into regex groups. The Integer group parameter specifies a group within that expression to be exposed. The other groups will be masked. See the example below and this tutorial for more information.

maskSSN(String value, String functionType)

The input field format must be ###-##-#### (US Social Security number format).

For the value 123-45-6789, partially anonymized output would be xxx-xx-6789 and fully anonymized would be xxx-xx-xxxx.

The following example shows how to mask telephone numbers from various countries that have different lengths:

CREATE SOURCE PhoneNumbers USING FileReader  ( 
  positionbyeof: false,
  directory: 'Samples',
  wildcard: 'EUPhoneNumbers.csv'
 ) 
 PARSE USING DSVParser  ( 
  header: true,
  trimquote: false
 ) 
OUTPUT TO phoneNumberStream ;

CREATE CQ FilterNameAndPhone 
INSERT INTO TypedStream
SELECT TO_STRING(data[0]) as country,
 TO_STRING(data[1]) as phoneNumber
FROM phoneNumberStream p;

CREATE CQ MaskPhoneNumberBasedOnPattern 
INSERT INTO MaskedPhoneNumber
SELECT country,
  maskPhoneNumber(phoneNumber, "(\\\\d{0,4}\\\\s)(\\\\d{0,4}\\\\s)([0-9 ]+)", 1, 2) 
FROM TypedStream;

CREATE TARGET MaskedPhoneNumberOut USING FileWriter  ( 
  filename: 'MaskedData'
) 
FORMAT USING DSVFormatter() 
INPUT FROM MaskedPhoneNumber;

Within the regular expression, groups 1 and 2 (exposed) are \\\\d{0,4}\\\\s, which represents zero to four digits followed by a space, and group 3 (masked) is ([0-9 ]+), which represents zero to 9 digits.

If Striim/Samples/EUPhoneNumbers.csv contains the following:

country,phoneNumber
AT,43 5 1766 1001
UK,44 844 493 0787
UK,44 20 7730 1234
DE,49 69 86 799 799
DE,49 211 42168340
IE,353 818 365000

the output file will contain:

AT,435xxxxxxxx
UK,44844xxxxxxx
UK,4420xxxxxxxx
DE,4969xxxxxxxx
DE,49211xxxxxxxx
IE,353818xxxxxx
Creating a masking CQ in the web UI

The Flow Designer includes a special Masking component to create masking CQs:

masking_palette.png

Click Masking, drag it into the workspace, and drop:

  1. Name the CQ.

  2. Select the input stream.

  3. Click ADD COLUMN and select a column to include in the output. 

    Masking_UI.png
  4. To pass the field unmasked, do not select a masking function. To mask it, select the appropriate masking function.

  5. Optionally, change the alias.

  6. Repeat steps 3-5 for each field to be included in the output.

  7. Select or specify the output, then click Save.

With the masking CQ above, using FileWriter with JSONFormatter, if the input was:

"Mary Stuart",1234-5678-9012-3456,mary.stuart@example.com,800-555-1212,1234-56-789

the masked output would be:

 {
  "name":"Stuart, Mary",
  "cc":"xxxxxxxxxxxxxxx3456",
  "email":"mxxxxxxxxxx@example.com",
  "phone":"xxx-xxx-1212",
  "SSN":"xxx-xx-6789"
 }

If you wish to edit the SELECT statement, click Convert to CQ. When you click Save, the component will be converted to a regular CQ, and if you edit it again the masking UI will no longer be available.

Numeric functions

function

description

NVL(Object, Object)

return the first object if it is not null, otherwise return the second object, for example:

NVL(COUNT(*),0)
NVL(ROUND_DOUBLE(SUM(Duration/60),1),0)

ROUND_DOUBLE(Object, Object)

round a double to the specified number of places

ROUND_FLOAT(Object, Object)

round a float to the specified number of places

TO_DOUBLE(Object)

convert a byte, float, integer, long, short, or string to a double

TO_FLOAT(Object)

convert a byte, double, integer, long, short, or string to a float

TO_INT(Object):

convert a byte, double, float, long, short, or string to an integer. To convert a JSON object to an integer, use this syntax instead: obj.TO_INT()

TO_LONG(Object)

convert a byte, double, float, integer, short, or string to a long

TO_SHORT(Object)

convert a byte, double, float, integer, long, or string to a short

String functions

function

description

notes

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

WAEvent functions

Use the following functions in CQs with an input stream of type WAEvent.

function

description

notes

BEFORE(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() and BEFORE() functions

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)

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() and BEFORE() 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.

USERDATA(stream name,key)

extracts a value from a WAEvent USERDATA map

See Adding user-defined data to WAEvent streams.

Miscellaneous functions

function

notes

CONSTRAINED_MULTIPLE_LINEAR_REGRESSION()

See Using analytics and regression functions.

CONSTRAINED_POLYNOMIAL_REGRESSION()

See Using analytics and regression functions.

CONSTRAINED_SIMPLE_LINEAR_REGRESSION()

See Using analytics and regression functions.

eventList()

See Using EVENTLIST.

ITERATOR()

See Using ITERATOR.

MULTIPLE_LINEAR_REGRESSION()

See Using analytics and regression functions.

NSK_CNVT_TXNID_TO_UNSIGNED()

See Functions for HP NonStop transaction IDs.

NSK_TXN_STRING()

See Functions for HP NonStop transaction IDs.

NSK_TXNS_ARE_SAME()

See Functions for HP NonStop transaction IDs.

POLYNOMIAL_REGRESSION()

See Using analytics and regression functions.

PREV()

See Referring to Past Events.

SIMPLE_LINEAR_REGRESSION()

See Using analytics and regression functions.

Imported Java functions are supported

Optionally, standard and/or custom Java classes may be imported into TQL applications and their functions used in TQL statements. For an example, see Using imported Java functions.

List of reserved keywords

The following reserved keywords may not be used as identifiers in TQL applications or queries. The entries in lowercase are Java keywords that may be used as identifiers provided they are in uppercase or have initial capitals.

abstract
ADD
ALERTSUBSCRIBER
ALL
ALTER
AND
ANY
APPLICATION
AS
ASC
assert
AT
BETWEEN
boolean
break
BY
byte
CACHE
CASCADE
CASE
CAST
catch
CDUMP
char
CLASS
CLEAR
CLUSTER
CONNECT
const
CONTEXT
continue
CQ
CREATE
CROSS
DASHBOARD
DATE
DAY
DAYS
DEFAULT
DEPLOY
DEPLOYMENT
DEPLOYMENTGROUP
DESC
DESCRIBE
DG
DGDISTINCT
DISABLE
DISTINCT
do
DROP

DUMP
ELSE
ENABLE
ENCRYPTION
END
enum
ERRORS
EVENT
EVENTTABLE
EVERY
EXAMPLE
EXCEPTION
EXCEPTIONHANDLER
EXCEPTIONSTORE
EXCEPTIONSTORES
EXEC
EXIT
EXPORT
extends
EXTERNAL
FALSE
final
finally
float
FLOW
for
FORCE
FORMAT
FROM
FULL
GO
goto
GRACE
GRANT
GROUP
GROUPS
GROUPSHAVING
HEARTBEAT
HELP
HISTORY
HOUR
HOURS
IDENTIFIED
if
IMMEDIATE
implements
IMPORT
IN
INNER
INPUT

INPUTOUTPUT
INSERT
INSTANCEOF
int
integer
interface
INTERVAL
INTO
IRSTREAM
IS
ISTREAM
ITERATOR
JAR
JOIN
JUMPING
KEEP
KEY
LAST
LDAP
LEFT
LIBRARIES
LIKE
LIMIT
LINK
LIST
LOAD
long
MAP
MATCH_PATTERN
MAXLIMIT
MDUMP
MEMORY
MERGE
MGET
MIN_SERVERS
MINUTE
MINUTES
MON
MONITOR
MONTH
NAMEDQUERY
NAMESPACE
native
NEW
NONE
NOT
NULL
OF
OFF
OFFSET

ON
ONE
OPENTRANSACTIONS
OR
ORDER
OUTER
OUTPUT
OVER
package
PARSE
PARTITION
PASSPHRASE
PATTERN
PERIOD
PERSIST
PERSISTENT
PLAN
PREV
private
PROPERTIES
PROPERTYSET
PROPERTYVARIABLE
protected
public
PUSH
QUERY
QUIT
RANGE
READ
REALTIME
RECOMPILE
RECOVERY
REMOVE
REPLACE
RESET
RESUME
return
REVOKE
RIGHT
ROLE
ROW
ROWS
RSTREAM
RUN
SAMPLE
SCHEMA
SECOND
SECONDS
SELECT
SELECTIVITY
session
SET
short
SHOW
SLEEP
SLIDE
SORTER
SOURCE
START
STATIC
STATS
STATUS
STOP
STREAM
strictfp
SUBSCRIPTION
super
switch
synchronized
TARGET
THEN
this
throw
throws
TIMESTAMP
TO
TTL
TRANSACTIONID
TRANSIENT
TRUE
try
TYPE
TYPES
UNDEPLOY
UNLOAD
UPDATE
USE
USER
USING
VISUALIZATION
void
volatile
WACTIONSTORE
WAIT
WHEN
WHERE
while
WI
WINDOW
WITH
WITHIN
YEAR

If you mistakenly use a reserved keyword as an identifier, you will receive a syntax error. For example:

Syntax error at:
Create Type Order
            ^^^^^
3.10.3
Was this article helpful?
0 out of 0 found this helpful

Comments