This section explains how to use scripting programming in Exasol.
You can use scripting programming to execute multiple SQL commands sequentially and to handle errors during the executions. You can run control jobs within the database, such as a complex loading process, and simplify the repetitive jobs by parameterized scripts, such as creating a user with its password and privileges.
Additionally, you can process result tables of SQL queries. However, a scripting program is a sequential program and only runs on a single cluster node (except the contained SQL statement). Therefore, it is not reasonable to do iterative operations on big data sets. For this purpose, you can use user-defined functions. For more information, see UDF Scripts.
For scripting programs, only the programming language Lua 5.4 is available, extended by a couple of specific features. The information in the following chapters will enable you to work with scripting language of Exasol. If you want to learn about Lua, read the official Lua documentation.
A scripting program is created, executed, and dropped by the commands CREATE SCRIPT, EXECUTE SCRIPT, and DROP SCRIPT. The return value of the EXECUTE SCRIPT command is either a number (as rowcount) or a result table.
Example
In the following example a scripting program is created that can copy all tables from one schema into a new schema.
--/
CREATE SCRIPT copy_schema (src_schema, dst_schema) AS
-- define function if anything goes wrong
function cleanup()
query([[DROP SCHEMA ::s CASCADE]], {s=dst_schema})
exit()
end
-- first create new schema
query([[CREATE SCHEMA ::s]], {s=dst_schema})
-- then get all tables of source schema
local success, res = pquery([[SELECT table_name FROM EXA_ALL_TABLES
WHERE table_schema=:s]], {s=src_schema})
if not success then
cleanup()
end
-- now copy all tables of source schema into destination
for i=1, #res do
local table_name = res[i][1]
-- create table identifiers
local dst_table = join(".", quote(dst_schema), quote(table_name))
local src_table = join(".", quote(src_schema), quote(table_name))
-- use protected call of SQL execution including parameters
local success = pquery([[CREATE TABLE ::d AS SELECT * FROM ::s]],
{d=dst_table, s=src_table})
if not success then
cleanup()
end
end
/
EXECUTE SCRIPT copy_schema ('MY_SCHEMA', 'NEW_SCHEMA');