streamnative.io
Help Center
Login
  1. StreamNative Managed Cloud
  2. Process Data Streams
  3. pfSQL (Alpha)

Built-in UDFs

Note

This feature is currently in alpha. If you want to try it out or have any questions, submit a ticket to the support team.

Built-in Common UDFs

Data Type Conversion Function

The pfSQL cast function is used to convert a value from one data type to another. Currently, it supports 6 data types, including INT32, INT64, FLOAT, DOUBLE, STRING, and BOOLEAN.

The syntax of the cast function is:

SELECT cast(field1, to=int32) from topic

The cast function takes two parameters, the first one is the field name, and the second one is the target data type. The target data type is specified by the to keyword, and it can be one of the following values:

  • "int32"
  • "int64"
  • "float"
  • "double"
  • "string"
  • "boolean"

The conversion rules to be followed are shown in the following table.

int32int64floatdoublestringboolean
int32No need to castCast directlyCast directlyCast directlyString.valueOf!=0 : true, ==0: false
int64Out of the range of INT32: throw Exception, otherwise cast directlyNo need to castCast directlyCast directlyString.valueOf!=0L : true, ==0L: false
floatOut of the range of INT32: throw Exception, otherwise Math.round()Out of the range of INT64: throw Exception, otherwise Math.round()No need to castCast directlyString.valueOf!=0.0f : true, ==0.0f: false
doubleOut of the range of INT32: throw Exception, otherwise Math.round()Out of the range of INT64: throw Exception, otherwise Math.round()Out of the range of FLOAT: throw Exception, otherwise cast directlyNo need to castString.valueOf!=0.0 : true, ==0.0: false
stringInteger.parseInt()Long.parseLong()Float.parseFloat()Double.parseDouble()No need to casttext.toLowerCase ==”true” : true, text.toLowerCase == “false” : false, otherwise: throw Exception
booleanbool ? 1 : 0bool ? 1L : 0Lbool ? 1.0f : 0.0fbool ? 1.0 : 0.0bool ? "true" : "false"No need to cast

Const Value Function

The pfSQL const function is used to return a constant value. The syntax of the const function is:

SELECT const(value=val, type=typ) from topic

The const function takes two parameters, the first one is the constant value, and the second one is the data type of the constant value. The data type is specified by the type keyword, and it can be one of the following values:

  • "int32"
  • "int64"
  • "float"
  • "double"
  • "string"
  • "boolean"

The following example shows how to use the const function:

SELECT const(value=1, type=int32) from topic

The above SQL statement returns a constant value of type int32 with value 1.

Built-in Math UDFs

Currently, pfSQL supports the following mathmatical functions. The behavior of these mathmatical functions is identical to the behavior of the corresponding functions in the Java Math standard library.

Function NameAllowed input schema typesOutput schema typeattributes parameterCorresponding Java Math function
sinINT32 / INT64 / FLOAT / DOUBLEDOUBLE-Math#sin(double)
cosINT32 / INT64 / FLOAT / DOUBLEDOUBLE-Math#cos(double)
tanINT32 / INT64 / FLOAT / DOUBLEDOUBLE-Math#tan(double)
asinINT32 / INT64 / FLOAT / DOUBLEDOUBLE-Math#asin(double)
acosINT32 / INT64 / FLOAT / DOUBLEDOUBLE-Math#acos(double)
atanINT32 / INT64 / FLOAT / DOUBLEDOUBLE-Math#atan(double)
sinhINT32 / INT64 / FLOAT / DOUBLEDOUBLE-Math#sinh(double)
coshINT32 / INT64 / FLOAT / DOUBLEDOUBLE-Math#cosh(double)
tanhINT32 / INT64 / FLOAT / DOUBLEDOUBLE-Math#tanh(double)
degreesINT32 / INT64 / FLOAT / DOUBLEDOUBLE-Math#toDegrees(double)
radiansINT32 / INT64 / FLOAT / DOUBLEDOUBLE-Math#toRadians(double)
absINT32 / INT64 / FLOAT / DOUBLESame type as the input-Math#abs(int) / Math#abs(long) / Math#abs(float) / Math#abs(double)
ceilINT32 / INT64 / FLOAT / DOUBLEDOUBLE-Math#ceil(double)
floorINT32 / INT64 / FLOAT / DOUBLEDOUBLE-Math#floor(double)
expINT32 / INT64 / FLOAT / DOUBLEDOUBLE-Math#exp(double)
lnINT32 / INT64 / FLOAT / DOUBLEDOUBLE-Math#log(double)
log10INT32 / INT64 / FLOAT / DOUBLEDOUBLE-Math#log10(double)
sqrtINT32 / INT64 / FLOAT / DOUBLEDOUBLE-Math#sqrt(double)
signINT32 / INT64 / FLOAT / DOUBLEDOUBLE-Math#signum(double)

The syntax of the mathmatical functions is:

SELECT field1, sin(field2), cos(field3), tan(field4) as tan from topic

Limitations

  1. The UDF only support in SELECT statement, we are working on it to extend the UDF to WHERE and other statements.
  2. The UDF only support passing full path of single field in SELECT statement yet.
Previous
Understand pfSQL
Next
Log and Monitor