beginner: working with Exasol resultsets in Lua scripts

SQL-Fighter

Hi,

I'm a beginner with regards to Exasol scripting with Lua. I've been through the course material for fundamentals and database development, but am struggling with one aspect. That is the use of query results with Lua. Once I have this aspect unlocked then I think I will have some understanding of working with scripting.

Here is an example of what i'm trying to achieve.

 

 

 

 

 

 

    --/
CREATE OR REPLACE SCRIPT myscript() AS
 function get_users()
  return {'John', 'Jane'}
 end
 output(table.concat(get_users(), ", "))
/

execute script myscript() with output;

 

 

 

 

 

The above will give me the following result

John, Jane

 

Which is fine for the hard coded lua array, but how can I get it to perform the same output for a list retrieved from the system tables. I have tried the following but am getting errors.

 

 

 

 

 

    --/
CREATE OR REPLACE SCRIPT myscript() AS
 function get_users()
  return query([[SELECT USER_NAME FROM EXA_ALL_USERS;]])
 end
 output(table.concat(get_users(), ", "))
/

execute script myscript() with output;

 

 

 

 

 

43000:"bad argument #1 for function 'concat' (table expected, got userdata)" caught in script "EXACADEMY"."MYSCRIPT" at line 4

 

I was under the assumption that the return type for query() is a lua table?

Any help appreciated in me getting over the little hurdle in understanding. Thank you.

 

1 ACCEPTED SOLUTION

SQL-Fighter

It looks like such a syntactical approach as mentioned above is not possible. From what I can gather you have to interrogate the resultset from the query in order to build another lua table variable in order for the table.concatenate to operate on it as shown below.

CREATE OR REPLACE SCRIPT myscript() AS
 function get_users()
   qres = query([[SELECT USER_NAME FROM EXA_ALL_USERS]])
   res = {}
   
   for i=1, #qres do
        res[i] = qres[i][1]
   end
   return res
 end
 output(table.concat(get_users(), ", "))


execute script myscript() with output;

This will give the desired output

John, Jane

 

*if John and Jane were users of the Exasol database.

View solution in original post

5 REPLIES 5

SQL-Fighter

It looks like such a syntactical approach as mentioned above is not possible. From what I can gather you have to interrogate the resultset from the query in order to build another lua table variable in order for the table.concatenate to operate on it as shown below.

CREATE OR REPLACE SCRIPT myscript() AS
 function get_users()
   qres = query([[SELECT USER_NAME FROM EXA_ALL_USERS]])
   res = {}
   
   for i=1, #qres do
        res[i] = qres[i][1]
   end
   return res
 end
 output(table.concat(get_users(), ", "))


execute script myscript() with output;

This will give the desired output

John, Jane

 

*if John and Jane were users of the Exasol database.

View solution in original post

Team Exasol
Team Exasol

Hi Eskewel,

You already found the right way to go, just a bit of background information for you:

A query returns you an two dimensional array.

Example:

exa-Valerie_0-1591621858204.png

qres = query([[SELECT NAME, TYPE FROM ANIMALS]])

would give you an array with 3 rows, each row containing 2 entries.

 

 

qres = query([[SELECT NAME FROM ANIMALS]])

would give you an array with 3 rows, each containing only one entry but still in a row-wise format.

[[Molly],[Tiger],[Bello]]

Therefore, for returning only the names, you have to iterate through your resultset in order to get a one dimensional array out of the two dimensional array.

 

SQL-Fighter

Hi @exa-Valerie , 

Thank you for the follow up explanation. It has really helped in understanding the interaction between the Lua elements of the script and the native Exasol SQL elements.

I'm trying to visualise this interaction, especially with the resultset(s).

Would I be correct in the following portrayal of Exasol tables and Lua tables. I have some understanding that Lua tables are in-fact arrays, and in keeping with this you represent a table using a multi-dimensional array.

So taking your example above, would I be right in saying that the result returned by the following query

 

qres = query([[SELECT NAME, TYPE FROM ANIMALS]])

 

would be a table resultset on the right side represented as so

 

[[Molly, cat],[Tiger cat],[Bello, dog]]

 

Does this mean that 'qres' is of that type and that is why we must transfer to contents of it to another variable which is initialised as a Lua array?

So if I wanted to work with the full contents of the resultset above I would need to do the following

 

   res = {}
   
   for r=1, #qres do
   	res[r] = {}
   	for c=1, #qres[r] do
        res[r][c] = qres[r][c]
    end
   end

 

This would then populate my second Lua "table" (multi-array) and then I would be able to operate on it. This in effect would build the lua table as below as a multi-dimensional array.

res = {{'Molly', 'cat'}, {'Tiger', 'cat'}, {'Bello', 'dog'}}

 

Here I have modified my original code to extract from a multi-array.

CREATE OR REPLACE SCRIPT my_schema.my_script() returns table AS
 function get_animals()
   qres = query([[SELECT name, animalType FROM dds_axiom.animal]])
   res = {}
   
   for r=1, #qres do
   	res[r] = {}
   	for c=1, #qres[r] do
        res[r][c] = qres[r][c]
    end
   end
   return res
 end
 output(table.concat(get_animals()[1], ", "))
 output(table.concat(get_animals()[2], ", "))
 output(table.concat(get_animals()[3], ", "))


execute script my_schema.myscript() with output;

When calling this with output you would get the following.

Molly, cat
Tiger, cat
Bello, dog

 

Another basic representation of Lua multi-array manipulation with hard-coded multi-array to elaborate the point is below.

CREATE OR REPLACE SCRIPT my_schema.my_script2() returns table AS
 function get_animals()
   res = {{'Molly', 'cat'}, {'Tiger', 'cat'}, {'Bello', 'dog'}}
   return res
 end
 output(table.concat(get_animals()[1], ", "))
 output(table.concat(get_animals()[2], ", "))
 output(table.concat(get_animals()[3], ", "))


execute script my_schema.myscript2() with output;

 

But, if I now exit with this multi-array table then it will give me an error.

CREATE OR REPLACE SCRIPT my_schema.my_script() returns table AS
 function get_animals()
   qres = query([[SELECT name, animalType FROM dds_axiom.animal]])
   res = {}
   
   for r=1, #qres do
   	res[r] = {}
   	for c=1, #qres[r] do
        res[r][c] = qres[r][c]
    end
   end
   return res
 end
 output(table.concat(get_animals()[1], ", "))
 output(table.concat(get_animals()[2], ", "))
 output(table.concat(get_animals()[3], ", "))
 
 exit(get_animals())

Is there a way to convert it back to a exasol table?

Team Exasol
Team Exasol

Hi Eskewel,

For return values have a look at the documentation:

https://docs.exasol.com/database_concepts/scripting/db_interaction.htm#ReturnValueofaScript

The documentation contains multiple examples:

1) Returning a result set directly

-- script which returns result table of query
--/
CREATE SCRIPT script_2 RETURNS TABLE AS
	exit(query([[SELECT * FROM DUAL]]))
/

 2) Returning a modified result set

-- return explicitly created table
--/
CREATE SCRIPT script_3 RETURNS TABLE AS
	local result_table = {{decimal(1),"abc",true},
			       {decimal(2),"xyz",false},
			       {decimal(3),nil,nil}}
	exit(result_table, "i int, c char(3), b bool")
/

As you can see, you have to define the name and type of the elements you want to return. So for the animal-example it would be (sorry for the formatting, it didn't survive being copied over here):

--/
CREATE OR REPLACE SCRIPT animals_script() returns table AS
function get_animals()
qres = query([[SELECT name, animalType FROM animals]])
res = {}

for r=1, #qres do
res[r] = {}
for c=1, #qres[r] do
res[r][c] = qres[r][c]
end
end
conc = {}
for r=1, #res do
conc[r] = {table.concat(res[r], ", ")}
end
return conc
end
exit(get_animals(), "Conc_Value VARCHAR(200)")
/

execute script animals_script();

BUT: What you should keep in mind when using Lua is always, whether it's the right tool for what you're trying to achieve or whether plain SQL/ a UDF might be the better solution.

UDFs and SQL statements can be executed in parallel whereas the concat part in your LUA script can't be parallelized.

So if you want to concat columns, this can be done even without using a UDF:

select name || ', '|| animalType as concat_value from animals;

This is way shorter and this way you can benefit from parallelizing your request.

If you chose Scripting in order to handle some special cases, think about writing a UDF. So e.g. if you want to treat NULL objects different, this can be done by writing a UDF.

 

We have an article in the knowledge base, that explains when to use Scripting/ UDFs.

 

Hope that helps,

Valerie

SQL-Fighter

Thank you so much for this explanation of outputting the Lua table as a resultset. This was the missing piece knowledge for me. I can now vaguely remember in one of the training videos it being mentioned about "defining the columns" in the exit statement. I tried to find the doc for the exit() function but have been unable to locate it.