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.

	int32	int64	float	double	string	boolean
int32	No need to cast	Cast directly	Cast directly	Cast directly	`String.valueOf`	`!=0 : true, ==0: false`
int64	Out of the range of INT32: throw Exception, otherwise cast directly	No need to cast	Cast directly	Cast directly	`String.valueOf`	`!=0L : true, ==0L: false`
float	Out of the range of INT32: throw Exception, otherwise `Math.round()`	Out of the range of INT64: throw Exception, otherwise `Math.round()`	No need to cast	Cast directly	`String.valueOf`	`!=0.0f : true, ==0.0f: false`
double	Out 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 directly	No need to cast	`String.valueOf`	`!=0.0 : true, ==0.0: false`
string	`Integer.parseInt()`	`Long.parseLong()`	`Float.parseFloat()`	`Double.parseDouble()`	No need to cast	`text.toLowerCase ==”true”` : true, `text.toLowerCase == “false”` : false, otherwise: throw Exception
boolean	`bool ? 1 : 0`	`bool ? 1L : 0L`	`bool ? 1.0f : 0.0f`	`bool ? 1.0 : 0.0`	`bool ? "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 Name	Allowed input schema types	Output schema type	attributes parameter	Corresponding Java Math function
sin	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	-	`Math#sin(double)`
cos	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	-	`Math#cos(double)`
tan	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	-	`Math#tan(double)`
asin	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	-	`Math#asin(double)`
acos	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	-	`Math#acos(double)`
atan	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	-	`Math#atan(double)`
sinh	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	-	`Math#sinh(double)`
cosh	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	-	`Math#cosh(double)`
tanh	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	-	`Math#tanh(double)`
degrees	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	-	`Math#toDegrees(double)`
radians	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	-	`Math#toRadians(double)`
abs	INT32 / INT64 / FLOAT / DOUBLE	Same type as the input	-	`Math#abs(int) / Math#abs(long) / Math#abs(float) / Math#abs(double)`
ceil	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	-	`Math#ceil(double)`
floor	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	-	`Math#floor(double)`
exp	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	-	`Math#exp(double)`
ln	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	-	`Math#log(double)`
log10	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	-	`Math#log10(double)`
sqrt	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	-	`Math#sqrt(double)`
sign	INT32 / INT64 / FLOAT / DOUBLE	DOUBLE	-	`Math#signum(double)`

The syntax of the mathmatical functions is:

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

Limitations

The UDF only support in SELECT statement, we are working on it to extend the UDF to WHERE and other statements.
The UDF only support passing full path of single field in SELECT statement yet.