Scripting

This section explains how to work with scripting programming in Exasol.

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.

You can also use scripting programs to process result tables of SQL queries. However, since a scripting program is a sequential program and only runs on a single cluster node (except the contained SQL statement), iterative operations on big data sets will be very time-consuming. For this purpose, you can instead use user defined functions (UDFs). For more information, see UDF Scripts.

The scripting language

The articles in this section explain how to use the scripting language in Exasol, which is Lua 5.4 extended with some specific features for Exasol. If you want to learn more about Lua, refer to the official Lua documentation. Other scripting languages are not supported.

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:

The following scripting program will copy all tables from a 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');