Other

COLUMN

COLUMN(string_expr)

Selects column by name string_expr. This function can be used when arguments are evaluated dynamically at runtime (for instance when writing user defined functions).

output type:the type of the column selected.

Example:

1
dual(5) | select(ip_addr:COLUMN('ip'));

CONFIG

CONFIG()

Returns names and values of all currently set query configuration parameters.

output type:ARRAY

Example:

1
dual | select(CONFIG());
config
[query.locale=en_US, query.timezone=Etc/GMT, query.batch_mode=false, query.now=1569229486584]

CONFIG(param_name)

Returns value of query configuration parameter param_name. If not found returns NULL.

output type:ARRAY

Example:

1
dual | select(CONFIG('query.timezone'));
config
Etc/GMT

ES_QUERY

ES_QUERY(default_field, query_string)

Executes query_string in Elasticsearch. default_field is used for query terms if no prefix fields are specified in the query_string. Use _all to refer to all index fields or a particular field name.

Example: Retrieve from Elasticsearch index apache records where any of its fields contain the words “login” or “logout”.

Note

Elasticsearch database must be running at localhost port 9200 for the example to work.

1
2
ES(uri:"http://127.0.0.1:9200", index:"apache")
| filter(ES_QUERY(_all, "login OR logout"));

OBJECT_DESC

OBJECT_DESC(variant_object)

Returns string with names and types of keys in VARIANT_OBJECT argument.

output type:STRING

Example

1
2
3
dual
| select(object:VARIANT_OBJECT({int:1, string:"hello world"}))
| select(OBJECT_DESC(object));
object_desc
{“int”:”INTEGER”,”string”:”STRING”}

FLAG

FLAG(country_code)

Returns Unicode flag symbol of ISO 3166 country_code.

output type:STRING

Example

1
dual | select(country:'Estonia', flag:FLAG('EE'));
country flag
Estonia 🇪🇪

GZIP

GZIP(string)
Returns gzip-compressed string
output type:BYTES

Example

1
dual | select(plain:"Hello World!") | select(plain, compressed:GZIP(plain));
plain compressed
Hello World! [31,-117,8,0,0,0,0,0,0,0,-13,72,-51,-55,-55,87,8,-49,47,-54,73,81,4,0,-93,28,41,28,12,0,0,0]

GUNZIP

GUNZIP(bytes)
Returns gzip uncompressed bytes.
output type:STRING

Example:

1
2
3
dual
| select(compressed:BYTES([31,-117,8,0,0,0,0,0,0,0,-13,72,-51,-55,-55,87,8,-49,47,-54,73,81,4,0,-93,28,41,28,12,0,0,0]))
| select(compressed, plain:GUNZIP(compressed));
compressed plain
[31,-117,8,0,0,0,0,0,0,0,-13,72,-51,-55,-55,87,8,-49,47,-54,73,81,4,0,-93,28,41,28,12,0,0,0] Hello World!

HASH64

HASH64(arg)

Computes 64-bit digest value of argument.

Note

The algorithm is not cryptographically secure.

output type:LONG

Example

1
dual | select(s, hash64:HASH64(s));
s hash64
0ho0 -1180816457071597385

FETCH

FETCH(uri)

Fetch content as string from Data Access URI. If the uri refers to any defined data store then access to its target is subject to the data store’s read ACL.

Example

1
2
3
4
/* select the content of example script into string field 'script'.
   NB! This works only when examples have been extracted to /user
*/
SELECT FETCH("sx:/user/examples/sorting.sx") as script;
output type:STRING

SLEEP

SLEEP(integer)
Causes the currently executing thread to sleep (temporarily cease execution) for integer milliseconds.
output type:BOOLEAN

Example: place 1-second delay after fetching a row from DUAL:

1
dual(5) | select(time:SYS_TIME(), SLEEP(1000));
time sleep
2019-09-23 10:10:01.449 +0000 true
2019-09-23 10:10:02.455 +0000 true
2019-09-23 10:10:03.458 +0000 true
2019-09-23 10:10:04.460 +0000 true
2019-09-23 10:10:05.464 +0000 true

SIZE

SIZE(arg)

Returns the number of elements in ARRAY, TUPLE, VARIANT_ARRAY or VARIANT_OBJECT argument.

output type:INTEGER

Example:

1
dual(5) | select(array, array_size:SIZE(array));
array array_size
[] 0
[0.0.0.1] 1
[0.0.0.2, 0.0.0.3] 2
[0.0.0.3, 0.0.0.4, 0.0.0.5] 3
[0.0.0.4, 0.0.0.5, 0.0.0.6, 0.0.0.7] 4

SIZEOF

SIZEOF(arg)

Returns the argument size in bytes.

output type:LONG

Example:

1
dual | select(int:i, sizeof_int:SIZEOF(i), tuple, sizeof_tuple:SIZEOF(tuple));
int sizeof_int tuple sizeof_tuple
0 4 {i=0 ip=0.0.0.0 ip6=0.0.0.0 s=”0ho0” t=”0ho0” ips=[] ip6s=[]} 63

TYPE

TYPE(arg)

Returns the string identifying data type of the argument.

output type:STRING

Example:

1
dual | select(int_t:TYPE(i),i, time_t:TYPE(t),t, type_array:TYPE(array),array);
int_t i time_t t type_array array
INTEGER 0 TIMESTAMP 2019-09-23 10:20:43.278 +0000 ARRAY []