Script Language support for SQL Preprocessor

Details

Detail name	Value
Changelog Number	25484
Type	Improvement
Status	Resolved
Fix Versions	Exasol 2025.1.5, Exasol 2025.2.0
Resolution Date	2025-12-05

Background

Exasol has an SQL Preprocessor that allows the user to write arbitrary scripts in Lua to rewrite each submitted SQL statement before execution.

It would be more flexible to be able to write preprocessor scripts in other languages. For example, the user might want to use the existing Python library SQLGlot to convert SQL queries from another database’s dialect to Exasol’s dialect.

Improvement

We added support for SQL Preprocessor scripts written in languages other than LUA, including PYTHON3 and JAVA. This feature is limited to languages which also support virtual schema adapter scripts.

Define the preprocessor script using the new syntax "CREATE <language> PREPROCESSOR SCRIPT <name> AS ...". For LUA, the existing syntax CREATE LUA SCRIPT is also supported for backwards compatibility. For languages other than LUA, the preprocessor script must implement a callback function, see table below; they will receive the original SQL statement as a string parameter and must return the preprocessed SQL statement.

Language	Preprocessor script API	Status
JAVA	`String adapterCall(final ExaMetadata metadata, final String sqlStatement)`	new
PYTHON3	`adapter_call(sql_statement)`	new
LUA	`sqlparsing.getsqltext()`, `sqlparsing.setsqltext(string)`, and utility functions in `sqlparsing`	unchanged
Custom languages	The preprocessor scripts use the same script language container protocol as virtual schema adapter scripts. Any script language supporting virtual schema adapter calls also supports SQL preprocessing through the same API. Only the meaning of the passed and returned strings differs between virtual schema and preprocessor adapter calls.	new

Use the new preprocessor scripts in the same way as the existing Lua scripting programs:

Set the session or system variable SQL_PREPROCESSOR_SCRIPT to the name of the preprocessor script to enable the preprocessing.
To stop the preprocessing, set the session or system variable SQL_PREPROCESSOR_SCRIPT to NULL.
To drop a preprocessor script, use the existing DROP SCRIPT <name> statement.
The required privileges are the same as for Lua scripting programs.

Differences to Lua scripting programs:

In the system tables EXA_ALL_SCRIPTS, EXA_DBA_SCRIPTS, and EXA_USER_SCRIPTS, scripts created through the new syntax are listed with the new script type PREPROCESSOR.
EXECUTE SCRIPT cannot be used to call preprocessor scripts. For easier development, you can move the implementation to a different script type and import it into the preprocessor script.

Python example using SQLGlot

Exasol does not support the non-standard syntax "SELECT TOP N ... ORDER BY ..." used by some other databases including SQL Server. But this is not a problem because SQLGlot can rewrite this syntax to a form that Exasol does support. The SQLGlot library is included in the PYTHON3 script language container.

-- Setup: Schema
ALTER SESSION SET SQL_PREPROCESSOR_SCRIPT = NULL;
CREATE SCHEMA sql_preprocessor;
OPEN SCHEMA sql_preprocessor;
CREATE TABLE example_users(name VARCHAR(100));
INSERT INTO example_users VALUES 'A', 'B', 'C', 'D', 'E', 'F';

-- Setup: Create a preprocessor script to rewrite TSQL queries into Exasol syntax.
--/
CREATE PYTHON3 PREPROCESSOR SCRIPT sql_preprocessor.python_script AS    
import sqlglot
def adapter_call(sql_statement):
    translated = sqlglot.transpile(sql_statement, read="tsql", write="exasol")
    return translated[0]
/

-- Observed: Syntax error
SELECT TOP 5 * FROM example_users ORDER BY name;

-- Enable the preprocessor script.
ALTER SESSION SET SQL_PREPROCESSOR_SCRIPT = sql_preprocessor.python_script;

-- Observed: Success
SELECT TOP 5 * FROM example_users ORDER BY name;

Simple Java example

--/
CREATE JAVA PREPROCESSOR SCRIPT sql_preprocessor.java_preprocessor AS
class JAVA_PREPROCESSOR {
    public static String adapterCall(final ExaMetadata metadata, final String sqlStatement) throws Exception {
        // Implement the preprocessing here.
        return sqlStatement;
    }
}
/

Simple Lua example using the new syntax

--/
CREATE LUA PREPROCESSOR SCRIPT sql_preprocessor.lua_preprocessor AS
    local statement = sqlparsing.getsqltext()
    -- Implement the preprocessing here.
    sqlparsing.setsqltext(statement)
/

Changed behavior

* The session/system variable SQL_PREPROCESSOR_SCRIPT can now also be used to select preprocessor scripts written in script languages other than Lua. * We added a new non-reserved keyword, "PREPROCESSOR".