Scripting

This section describes scripting programming using Exasol’s scripting language.

Introduction

In Exasol, you can create and use scripts to execute multiple SQL commands sequentially and to handle errors during the executions. You can use scripts to run control jobs within the database, for example, a complex loading process. You can also simplify repetitive jobs such as creating a user with its password and privileges by using parameterized scripts.

A script is created, executed, and dropped using the SQL 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.

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.

What is the difference between scripts and UDFs?

You can also use scripts to process result tables of SQL queries. However, although the SQL statement contained in the script will run on all cluster nodes, the script that processes the result is a sequential program and only runs on a single cluster node. This means that iterative operations on big data sets will be very time-consuming.

For this purpose, you can instead use user defined functions (UDFs), which run on all cluster nodes and therefore utilize the advantages of parallelization in Exasol. UDFs also support several programming languages such as Java, Python, Lua, and R.

To learn more about how to create and use UDFs, see User Defined Functions (UDFs).

Examples

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