Standard ANSI SQL, whilst a great language for database querying, doesn’t allow for commonly used features like looping or extensive variable usage. There are variants which support some of this e.g. Transact-SQL but not on Kognitio until the introduction of Kognitio scripts.

But 2 languages in one script? How does this work?

Kognitio scripts (also referred to as ‘Kog’ scripts) are based on the Lua language where SQL statements can be written together with Lua code for extra functionality. They allow typical procedural language operations without the need of extensive workarounds in a non-procedural language like SQL.

It can be a bit overwhelming for SQL only users so we’ll go over some useful tips for getting started, for more in depth reading refer to the console scripting guide on the forum.

To work with Kog scripts in Kognitio Console make sure that your scripting pane options tab has settings similar to those below. This will allow you to work effectively in debug mode.

 

Variable and function integration (for management)

Set variables via:

use_schema = ''
use_table = 'example'

To call these arguments in SQL just add a $ before the variable name:

create table $use_schema.$use_table(mycolumn varchar);

This is very useful if users share a generic script to be used on different data sets. Rather than changing the schema/table for every query in the script, managing all the variables at the top of the script makes changing the source data/script parameters set much more organised.

Note that if you were to use variables in a Lua only code then you don’t need to reference them with “$”. Here the variable “a” is used in a loop:

a = 0
for i=1,5 do
    a = a +1;
end

Functions work in a similar way:

function square_num(a)
return a*a;
end
select $square_num(value) from values between 1 and 5;

Remember that Lua is case sensitive where it’s typically written in lower case (“and” is a reserved word but “And” or “AND” could be used as variable names).

The variable name isn’t restricted to alphanumeric characters, however if it doesn’t begin with one then you’ll need to enclose with brackets e.g. “$(_myvar)”. You can also reference a Lua table this way “$(myluatable[1])” which refers to the first column.

When stepping through a Kog script in Kognitio Console, you can always view the currently defined variables via the script variables pane (view > panes > script variables).

Try: Managing variables can be extended further via local variables, try creating two functions which take a global variable as an argument and alter it to suit the function need. Further this by using the global table _G..

Saving results to Lua tables e.g. column names/table names

You may have used the feature “$Col1” in Kognitio’s SQL scripting to retrieve a result from a previously run query. However this limits you to only the first row of results. A solution to this is to save them to a Lua table.

Declaring a table and insert via:

mytable= {};
table.insert(mytable,'element_1');
print(mytable[1]);

To set predefined elements:

mytable {'element_1','element_2'}

To save results from an SQL query:

average_values = select avg(value), avg(value*value) from values between 1 and 10;

Example: Save column names for an SQL table/view into a Lua table

mycolumns = select seq,name 
from SYS.IPE_COLUMN
where table_id in (select table_id 
                   from SYS.IPE_VIEW
                   where schema_id in (select id from SYS.IPE_ALLSCHEMA
                                       where name = '')
                   and name = ''
                   )
order by 1;
print(mycolumns.rows[1].NAME);

This saves the column names from a specified SQL table into a Lua table and prints the name of the first column. Some return values for Lua tables taken from our console scripting guide:

Table Variable Value
numRows Integer, Number of rows in the resultset.
numCols Integer, Number of columns in the resultset.
Status Integer, 0 = OK
colNames Table
Rows Table

Try: You’ll notice that you can also access a Lua tables column names via the colNames table variable so you can save an SQL table into a Lua table and call the colNames variable to get them rather than fetching it from the SYS tables.

Note: Returning results of a SQL query to lua tables should be restricted to small result sets as these are stored in the memeory of your local system where Kognitio console is running.

Looping with Lua and SQL

A basic version is possible in ANSI SQL but becomes a bit limited as you’re only able to work with an incrementing defined counter. In Kog scripting you can perform bread and butter loops like the classic “for” and “while” loops. These let you set index limitations as well as have an index paired alongside a value and iterate over Lua defined constructs like arrays and tables.

for i=1,5 do
    ...    
end
while i <5 do
   ...    
end

The “pairs” notation to include an index:

for a,b in pairs() do
   ...    
end

To integrate this with SQL queries just add them in to the loop, remember to end each query with a semicolon.

for i=1,5 do
    select avg(value) from values between 1 and $i ;
end

Example: Writing SQL automatically via concatenation (when there’s lots to write)

In the previous tip we saw the we can saved the results of an SQL query to a Lua table, we can now iterate through them by calling the table name and adding a suffix for the rows. Let’s take the column names of a previous table for example.

mycolumns = select seq,name 
from SYS.IPE_COLUMN
where table_id in (
                   select table_id from SYS.IPE_VIEW
                   where schema_id in (select id from SYS.IPE_ALLSCHEMA
                                       where name = '')
                   and name = ''
                   )
order by 1
;

And apply it to our concatenation. The aim is to write the SQL syntax for every column to calculate the average and maximums whilst excluding the unique ID column from this.

myschema ='CHAK'
mytable = 'CONCAT_STRINGS_EG '

--Table and view columns are stored in different system tables, 
--toggle with flag below depending on data source
--1= table, 2= view
type_flag = 1
if type_flag==1 then
    table_type = "IPE_TABLE"
    selection = 'id'
elseif type_flag==2 then
    table_type = "IPE_VIEW"
    selection = 'table_id'
end

drop table $myschema.$mytable;
create table $myschema.$mytable 
(id int, sale_price decimal(5,2), amount_paid decimal(5,2), no_items int);

insert into $myschema.$mytable
select 
row_number() over(),value*(value mod 5), ceiling(value*(value mod 5)),generate_key() 
from values between 1 and 5;

mycolumns = select seq,name from "SYS"."IPE_COLUMN"
where table_id in (
    select $selection from "SYS".$table_type
    where schema_id in (select id from "SYS"."IPE_ALLSCHEMA"
                       where name = '$myschema')
    and name = '$mytable'
    )
order by 1
;

col_average = ''
col_maximum = ''
for x,y in pairs(mycolumns.rows) do
   --Skip x=1 because that is the ID and we don't want to calculate metrics for it
    if x==2 then
        col_average = 'avg('..y.NAME..') avg_'..y.NAME
        col_maximum = 'max('..y.NAME..') max_'..y.NAME
    elseif x>2 then
        col_average = col_average..',avg('..y.NAME..') avg_'..y.NAME
        col_maximum = col_maximum..',max('..y.NAME..') max_'..y.NAME
    end
end

select id, $col_average from $myschema.$mytable group by 1;
select id, $col_maximum from $myschema.$mytable group by 1;

This will concatenate each of the 3 column names so that you can insert into another SQL statement later via “$col_average” and “$col_maximum” saving the need to type out each column name especially if this code is required in different queries. Extremely useful when producing a generic version for different datasets as the column names don’t need to be redefined. You could even wrap the whole code in a “for” loop and repeat this for different tables or views.

Try: Note that x is the index and y is the value in the above. Since SQL columns are separated by a comma in the coding, we use the index x to determine the first column and not add a comma in front of it. Conversely you could change it so that it only adds a comma when the index is not the first one.

Kognitio modules (Section off code)

As you build up your code you may find that you have functions which can be useful elsewhere i.e. other scripts where you can just copy them across but this can make scripts unnecessarily verbose. Instead putting them in Kog modules allows you to put them into another file to be called up later when needed.
Firstly you’ll need to set the path to your Kog modules. The example below is for a local machine folder but this will also work for network drives allowing multiple users to contribute and access them.

_G.package.path= _G.package.path.. ";?.kog";

Then you can load any files in there them via “require” similar to the module/library/package loading in other languages:

 scr1 = require "Kog_Module_Script1"
 scr1.run()

The first line designates that module as scr1 and the second runs it. In our example we’re going to run two modules depending on the time of day: the average during the day and the sum at the end of the day.

Getting the times:

select time '09:00:00';
stime = Col1;
print(stime)
select time '17:00:00';
etime =  Col1;
print(etime)
ctime=os.date("%X");
print(ctime)

if ctime > stime and ctime < etime then
    mod1 = require ""
    mod1.run()
else if ctime > etime then
    mod2 = require ""
    mod2.run()
else print("Not ready")
end

Which opens the module:

local x = {}
x.name = "Average Script"
select avg(value) from values between 1 and 5;

or:

local x = {}
x.name = "Sum Script"
select sum(value) from values between 1 and 5;

depending on the time of day. The idea being that you can also define functions in there and build up libraries to call in future development rather than inserting copious amounts of code from past scripts and making it very verbose.

Try: Replace the data source in the SQL scripts with your own data set and then insert a function into each script, starting off by printing the time of day and building up the complexity.