A query script is composed of:
- one or more declarations of a query statement or expression,
- an execution statement,
- one or more INCLUDE directives (optional) and
- an INIT block (optional)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
INIT( input_arg:'bar', _config_param:'conf_value' ); /* next line is commented out */ //INCLUDE './path/to/script.sx'; $local_const = 30; $local_expression = 'foo ' + UPPER($input_arg); @generate_fields=dual(5) | select(i); @generate_fields | select(i, $input_arg, $local_const, $local_expression, config('query.config_param')) | select(*, pi:i+0.14) | limit(3) ;
- line 1 defines INIT block
- line 2 defines input argument named ‘input_arg’ with initial string value ‘bar’
- line 3 assigns to Query Configuration parameter ‘_query.config_param’ initial string value ‘conf_value’
- line 6 has a comment (can be applied to multiple lines too)
- line 7 has a commented out INCLUDE directive
- line 9 declares a constant variable
- line 10 declaration of an user defined function
- line 11 declaration of a query using DUAL and select
- lines 13-16 execution statement followed by query process commands
- query stream
- A stream of structured data (collection of tuples) passed between query commands.
- Any single or pipelined query command or function returning query stream
Statements in SXQL script must be terminated with a semicolon (“;”).
A statement can be written on several lines to increase readability. The line feeds and insignificant whitespaces are ignored by interpreter.
Declarations can be written in any order, i.e it has no effect on the sequence of execution, which is determined solely by their actual dependencies.
Two comment styles are supported for inserting comments:
/* comment comment */
From query processing standpoint it is important to know beforehand (i.e at compile time) if the return value is a structured query stream or a single value. Therefore the return type must be specified at declaration:
- expression variable names (returning single value) must be prepended by dollar “$” character:
$variable_name = expression;
- stream variable names must be prepended by “@” character:
@variable_name = statement;
Variables declared outside of the INIT block are visible inside the script, i.e they are local variables.
A variable name must begin with a letter. Subsequent characters can be letters, numbers or underscore (
A variable name must be unique in the scope of a script (and included scripts).
1 2 3
$local_const = 30; $local_expression = 'foo ' + UPPER($input_arg); @generate_fields=dual(5) | select(i);
- where line:
- declaration of a local variable pointing to a constant expression
- declaration of a local variable pointing to a string concatenation expression
- declaration of a query statement
A query script gets executed by calling a statement declaration - called the execution statement. This can be either record stream creation command or reference to declared (or included) stream type variable.
1 2 3 4 5
@generate_fields | select(i, $public_arg, $private_arg, $private_const, config('query.config_param')) | select(*, pi:i+0.14) | limit(3) ;
There can be only one execution statement in a script. Other declared streams and expressions may depend on it, in which case they are executed in their respective dependency order.
Execution of a script takes place in the following sequence:
- Initialization (if present).
- The execution dependency tree is constructed
- Statements are executed in the order dictated by the dependency tree
The INIT directive must be the first statement of a script. Parameters are separated by a comma and enclosed in parenthesis. Parameter/argument name and value are separated by a colon “:”.
INIT'(' name':'(value | ':'type) [, ...] ')'
- name - name of the parameter or input argument. Must begin with a letter or underscore, subsequent characters
can be letters, numbers or underscore (
- value - the initial value of the parameter or argument.
- type - the data type of the argument prepended with a colon “:”. Makes argument mandatory at calling.
- name - name of the parameter or input argument. Must begin with a letter or underscore, subsequent characters can be letters, numbers or underscore (
When declaring Query configuration: items:
- Values can not be declared using type.
- When specifying namespace then enclose the name in single or double-quotes.
- Declared parameter names will be renamed during the execution of the query by removing the leading underscore symbol.
- Parameter names without namespace are mapped to ‘query’.
Declaring an input argument with value makes it optional when calling script. Declaring an input argument with a type makes it mandatory when calling script.
1 2 3 4 5 6 7
INIT( username:'john', min_age::INTEGER, _timezone:'GMT', '_query.locale':'et' '_query.parse.ignoreErrors':true, );
- line 2: declares optional input argument ‘username’ with default value ‘john’
- line 3: declares mandatory input argument ‘min_age’ type ref:ft_integer
- line 4: specifying configuration parameter name ‘_timezone’ without namespace results in initializing ‘_query.timezone’ to string value ‘GMT’
- line 5: sets query configuration parameter ‘ignoreErrors’ in namespace ‘query.parse’ to boolean value ‘true’
Complex and long scripts can be hard to read and understand. Capturing reusable parts of code is beneficial both for readability as well as productivity.
The names of declared variables must remain unique in the scope of current and included scripts!
A script named
_include.sx will be included automatically by any script created in the same -or subfolder to it.
For example if you execute script
/team/folder/subfolder/myscript.sx then automatically are included
(if existing and readable) in this order:
You can use this feature for conveniently executing frequently used queries.
Example: declare a stream variable which executes a script providing COVID-19 statistics in the
@covid_infection_rates = @[/user/varia/covid19/infection_rates.sx];
Now, in any of the script created directly in
/user directory or in its subdirectories (use CTRL+N shortcut) I can
refer to this variable.
pressing CTRL+SPACE after typing first letters of included variable will reveal drop-down list with its full name.
The INCLUDE directive brings target script into the visibility scope of current - i.e all declarations become accessible as if they have been declared within the current script. The execution statement of the included script is not executed.
The current script must be saved in order to use the relative path (i.e it must have a place in the resource tree).
Example. We have defined a commonly used convenience function for calculating and displaying nicely formatted ratio
between two digits in the
/shared/utils/tools.sx folder. To use it:
1 2 3
include "/shared/utils/tools.sx"; dual | select($calcRatio(1.1, 10)); // function $calcRatio(value, total) is defined in included script file