Scripting

This section explains the scripting programming that you can use to execute multiple SQL commands sequentially and to handle errors during the executions. You can run control jobs within the database (for example, complex loading processes) 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, see UDF Functions for more details.

For control jobs including several SQL commands, you must use Lua scripts. UDFs are not able to run SQL commands natively.

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 script 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 script 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');