exasol-sql-preprocessor

name

exasol-sql-preprocessor

description

Use when implementing, debugging, or extending Exasol SQL preprocessors to customize SQL syntax, add compatibility layers, enforce query governance, build wrapper-view plus preprocessor architectures, or bridge user-facing SQL into other Exasol extension points such as virtual schemas. Covers official Exasol preprocessor APIs, upstream GitHub examples, Lua-first implementation patterns, architecture tradeoffs, and live-validation workflow.

Exasol SQL Preprocessor

When To Use This Skill

Use this skill when the task is about:

• SQL_PREPROCESSOR_SCRIPT
• CREATE ... PREPROCESSOR SCRIPT
• adding new SQL sugar or shorthand in Exasol
• translating unsupported SQL into supported Exasol SQL
• adapting SQL generated by external tools
• blocking, redirecting, or governing classes of queries before compilation
• bridging user-facing syntax into virtual schemas or other Exasol extension layers

Do not use this skill for plain UDF work or plain virtual-schema adapter work unless the problem is specifically about SQL that must be rewritten before Exasol compiles it.

First Moves

1. Check the current official Exasol docs if the task depends on current platform behavior or version support.
2. Study upstream examples before inventing a new rewrite pattern.
3. Validate the important behavior against a live Exasol instance before finalizing the design.

Official starting points:

• SQL preprocessor: https://docs.exasol.com/db/latest/database_concepts/sql_preprocessor.htm
• Lua runtime and bundled modules: https://docs.exasol.com/db/latest/database_concepts/udf_scripts/lua.htm

Upstream examples worth checking first:

• Official docs examples:
  • Python sqlglot translation for TOP n
  • Lua rewrites for IF(...)
  • Lua rewrites for ANY / ALL
  • macro-style ls command example
• Exasol GitHub examples:
  • exasol/public-knowledgebase/Database-Features/preprocessor-scripts-explained.md
  • exasol/public-knowledgebase/Database-Features/using-the-sql-preprocessor-to-support-postgresql-mysql-functions.md
  • exasol/public-knowledgebase/Database-Features/unica-connection-with-mysql-template.md
  • exasol/public-knowledgebase/Database-Features/how-to-block-users-from-seeing-roles.md
  • exasol/public-knowledgebase/Database-Features/remove-ability-disable-preprocessor.md

If you are working in this repository, also inspect:

• tools/generate_preprocessor_sql.py
• tools/generate_wrapper_preprocessor_sql.py
• tools/generate_wrapper_views_sql.py
• tools/wrapper_package_tool.py
• tools/test_nano_preprocessor_parser_lane.py
• regenerate wrapper artifacts under dist/exasol-json-tables/ when you need concrete generated SQL or package examples
• tests/test_preprocessor_library_builder.py
• tests/test_preprocessor_refactor_phase0.py
• tests/test_preprocessor_early_out.py
• tests/test_wrapper_surface.py
• tests/test_wrapper_errors.py
• tests/test_wrapper_evaluation.py

Mental Model

The preprocessor is a compiler-front text rewrite hook. It runs before Exasol compiles the SQL statement.

Treat it as:

• the right tool for compatibility layers and syntax extension
• a possible control point for query governance
• a sharp tool that can affect every SQL statement in a session or system

The normal question is not "can this be parsed in Lua?" but:

• what is the narrowest safe rewrite that solves the user's problem
• should this happen in the session only or system-wide
• is a preprocessor actually better than a view, UDF, adapter, or client-side rewrite

Typical good use cases from the official docs and Exasol GitHub examples:

• compatibility rewrites for frontend-generated SQL
• adding syntax sugar or macros
• governance checks against specific metadata tables or commands
• helper syntax that must survive into a virtual schema or another downstream layer

Architecture Choice Before Coding

Decide which layer should own the problem before writing the rewrite.

What the preprocessor can and cannot do

The preprocessor rewrites SQL text before compilation. It does not change the underlying database object or its catalog metadata.

Practical consequence:

• a preprocessor can make SELECT ... feel nicer
• it cannot make raw helper columns disappear from SYS.EXA_ALL_COLUMNS
• it cannot make a raw table surface look clean to generic tooling by itself

If the user needs metadata hiding, choose an object layer as well:

• wrapper views
• virtual schemas

Fast decision rule

Use raw tables + preprocessor only when:

• the raw table surface is already acceptable
• the task is mostly syntax sugar or governance

Use wrapper views + preprocessor when:

• the data already lives in Exasol
• helper columns must be hidden from metadata and SELECT *
• ordinary UDF usage on the wrapped data should keep working
• the main job is semantic sugar over local Exasol tables

Use virtual schema + preprocessor when:

• the source is external, or should behave like an external federated source
• the adapter must own metadata folding or pushdown semantics
• helper syntax must bridge into adapter rewrite logic

Important lesson from this repository:

• raw tables + preprocessor was not enough to hide JSON helper columns
• wrapper views + preprocessor was enough to hide them and keep UDFs working
• virtual schemas remained useful for semantic folding such as variant-column behavior

Execution And Security Facts

Internalize these before writing code:

• The preprocessor is a schema object.
• It runs in the caller's context and privileges.
• Users who need EXECUTE on the script can also read it.
• A user can normally disable a system-wide preprocessor in their own session by setting SQL_PREPROCESSOR_SCRIPT = NULL.
• Statements containing passwords are excluded from preprocessing.
• CREATE SCRIPT statements are themselves preprocessed.

Design implications:

• never rely on secrecy or obscurity inside a preprocessor
• do not put secrets into the script body
• if the preprocessor enforces system-wide governance, understand whether users must be prevented from disabling it
• disable the active preprocessor before redeploying or replacing it

The Exasol knowledge-base example remove-ability-disable-preprocessor.md points to the database parameter alterSystemProhibitsAlterSessionForPreprocessing=1 when system-level enforcement must not be bypassable by ordinary users.

Choose The Language Deliberately

Default to Lua unless there is a strong reason not to.

• Use Lua when you need maximum compatibility, token-level control, or support for older Exasol versions.
• Use Python 3 or Java only if the target Exasol version is new enough and the rewrite clearly benefits from richer libraries.

Current official docs say:

• Exasol 2025.1.5 and later support Python 3 and Java preprocessor scripts.
• Earlier versions only support Lua preprocessing.

Practical rule:

• If the target version is unknown, prefer Lua.
• If the target is explicitly 2025.1.5+ and you need dialect translation or AST tooling, consider Python 3.
• Do not introduce a heavier language just because it exists.

Design Rules

Keep semantics honest

Do not silently redefine familiar SQL semantics unless that is explicitly the intended feature.

Good:

• DATE(...) to TO_DATE(...)
• ADDDATE to ADD_DAYS
• TOP n to valid Exasol syntax
• domain-specific helper syntax such as JSON_IS_EXPLICIT_NULL(col)

Bad:

• hijacking plain IS NULL or another common operator for an unrelated meaning

Prefer narrow token rewrites

The successful upstream patterns are narrow, local, and deterministic.

Prefer:

• matching a token sequence
• rewriting that one construct
• returning valid SQL

Avoid:

• trying to parse all of SQL yourself
• broad regex rewrites over raw text
• doing metadata I/O for every statement unless governance truly requires it

If you use query() or pquery() inside a preprocessor:

• treat it as an exceptional governance mechanism
• keep the query narrow and fast
• remember that official Exasol guidance warns against this in global preprocessors because it slows all SQL and increases transaction-conflict risk

Keep the main preprocessor thin

Exasol’s docs recommend keeping the main preprocessor wrapper thin and moving real logic into helper code.

Follow that advice:

• inside Exasol, keep the main script as a wrapper and import auxiliary scripts where appropriate
• in a source repository, keep the transformation logic in normal source files or a generator and emit a small installable script

Roll out in this order

1. Disable any active session-level preprocessor before deployment.
2. Create or replace the script.
3. Enable it for your own session.
4. Test with representative statements.
5. Only then consider system-wide activation.
6. Ensure users have EXECUTE privilege.

Important practical detail from Exasol’s GitHub knowledge base:

• replacing the script can drop object-level grants
• schema-level execute grants are often easier to manage than regranting object-level execute after every replace

Keep governance code simple

The knowledge-base governance example blocks queries to selected metadata tables by:

• tokenizing the SQL
• checking for disallowed identifiers
• querying metadata to check privileges
• raising an error when the user lacks the required privilege

That pattern is valid, but it should stay narrow. Governance preprocessors are easy to overbuild.

Prefer object shaping over heavy hot-path metadata reads

If the problem is really:

• hiding columns
• renaming a user-facing surface
• exposing a curated table contract

prefer:

• views
• virtual schemas

over repeatedly calling query() inside preprocessing to rediscover metadata on every statement.

The preprocessor can query metadata, but that should be a narrow exception, not the architecture.

Lua Tokenization Facts You Must Internalize

If you use Lua preprocessing, sqlparsing behavior matters more than intuition.

The key fact, confirmed by official docs and live debugging in this repo, is:

• multipart identifiers such as test.tab and "foo"."bar" are single identifier tokens

Do not assume they tokenize as:

• identifier, ., identifier

Safe rules:

• use sqlparsing.tokenize() and think in compiler tokens, not raw characters
• use sqlparsing.normalize() for case-insensitive comparisons
• use sqlparsing.iswhitespaceorcomment() when matching meaningful token sequences
• use sqlparsing.find() when the pattern fits
• if you must split a multipart identifier yourself, respect quotes and escaped quotes

Patterns That Work Well

1. Compatibility rewrites for external tools

This is the most common upstream pattern.

Examples from Exasol GitHub and docs:

• PostgreSQL / MySQL DATE(...) to Exasol TO_DATE(...)
• Unica MySQL-template SQL adjustments such as ADDDATE to ADD_DAYS
• SQL Server style TOP n translation through Python sqlglot

Use this pattern when a tool emits stable, repeated SQL shapes that Exasol does not support directly.

2. Syntax sugar and macro-style extensions

Examples from official docs:

• IF(condition, a, b) rewritten to CASE WHEN ... THEN ... ELSE ... END
• ANY / ALL rewritten into supported SQL forms
• Unix-like ls command expanded into metadata queries

Use this pattern when the user benefit comes from cleaner SQL authoring rather than from changing semantics.

3. Governance and policy checks

Examples from Exasol GitHub:

• block users from querying EXA_ALL_ROLES or EXA_ALL_USERS unless they have SELECT ANY DICTIONARY

Use this pattern when:

• the policy must apply before compilation
• the logic spans multiple schemas or metadata surfaces
• views or privileges alone are not enough

Be strict about scope. Governance preprocessors can become fragile if they try to police all SQL.

4. Companion syntax for another extension layer

This repo adds a useful advanced pattern:

• use the preprocessor to translate user-facing helper syntax into SQL shapes that survive into a virtual schema

Example:

• Exasol can strip user-defined scalar function calls before virtual-schema pushdown
• the preprocessor can rewrite the helper call into a marker expression
• the adapter can then recognize that marker and implement the real semantics

Use this pattern when the preprocessor is not the final semantic engine, but the bridge to one.

5. Wrapper-view plus preprocessor as a full local extension architecture

This repository adds another advanced pattern that is worth using elsewhere:

• generate ordinary views that hide physical helper columns
• scope the preprocessor to those views
• rewrite helper, path, and array syntax into ordinary Exasol SQL over the view layer

This pattern is especially strong when:

• the source data is already in Exasol
• users want normal table/view behavior and UDF compatibility
• a virtual schema would add complexity without real federation value

Be explicit that this is views + preprocessor, not literally preprocessor-only.

Validation Workflow

Baseline

Before trusting the rewrite:

1. run the original query without the preprocessor
2. enable the preprocessor for the session
3. run the rewritten query
4. compare results and failure modes

Inspect the transformed SQL

Use the tools Exasol gives you:

• auditing tables, especially EXA_DBA_AUDIT_SQL, when available
• profiling or logging if the environment has it
• EXPLAIN VIRTUAL if the rewritten SQL feeds a virtual schema

Do not settle for “the query returned something plausible”.

Profile cold and warm runs separately

When performance matters, do not measure only one run.

Use this workflow:

1. ALTER SESSION SET QUERY_CACHE='OFF'
2. ALTER SESSION SET PROFILE='ON'
3. run the query
4. ALTER SESSION SET PROFILE='OFF'
5. FLUSH STATISTICS
6. inspect EXA_USER_PROFILE_LAST_DAY
7. COMMIT so automatically created join indexes persist
8. run the same query again as the warm case

Why this matters:

• Exasol creates join indexes automatically
• first execution can pay a significant INDEX CREATE cost
• subsequent executions can be much cheaper

This repository’s Nano benchmarks showed:

• wrapper views were effectively free in steady state for path and rowset queries
• hidden self-joins used for semantic recovery were the main cold-start risk
• virtual-schema paths had a small recurring PUSHDOWN overhead even when the eventual SQL shape was similar

Preprocessor performance heuristics

Treat these as defaults until measurement proves otherwise:

• token-only rewrites are usually cheap
• wrapper views are often inlined well by the optimizer
• helper rewrites that add new joins can be expensive on the first run
• self-joins back to the same root table are the highest-risk pattern
• query() / pquery() in the hot path are usually a bad trade unless governance requires them

Test cases that matter

At minimum, cover:

• quoted identifiers
• schema-qualified and alias-qualified names
• whitespace and comments between tokens
• nested parentheses
• malformed input
• repeated occurrences of the construct
• interactions with CREATE SCRIPT and deployment
• interactions with EXPLAIN if you expect users to debug through explained statements
• permission behavior if the preprocessor is used for governance

Prefer live Exasol over local reasoning

Do not trust a rewrite just because the generated SQL looks plausible.

Validate it against a live Exasol instance because the most important boundaries are compiler behavior, session behavior, and privilege behavior.

If you are working in this repository, the main live checks are:

• python3 tools/test_nano_preprocessor_parser_lane.py
• python3 tests/test_preprocessor_refactor_phase0.py
• python3 tests/test_wrapper_surface.py
• python3 tests/test_wrapper_errors.py
• python3 tests/test_wrapper_evaluation.py
• python3 tests/test_wrapper_package_tool.py

If your local workspace keeps the optional ignored benchmark harnesses, also use:

• python3 benchmarks/study_wrapper_performance.py

Repo-Specific Guidance For This Repository

If the task is in this repository, reuse the existing generator unless the task explicitly requires a fresh implementation.

• tools/generate_preprocessor_sql.py already produces installable Lua preprocessors
• it supports configurable helper names
• it uses join-based path rewrites for dotted paths and bracket access
• this repository is organized around the wrapper-view + preprocessor package architecture

If you change the generated Lua:

1. patch the generator, not only the generated SQL
2. regenerate the example SQL files
3. rerun the Nano tests that cover the changed behavior

Known Pitfalls

• Forgetting that CREATE SCRIPT statements are themselves preprocessed
• Forgetting that EXPLAIN ... statements are also preprocessed and may need their own supported shape
• Assuming dotted identifiers tokenize as separate . tokens
• Using raw regex where token-aware matching is needed
• Overloading common SQL semantics for domain-specific meaning
• Enabling a new preprocessor globally before session-level validation
• Forgetting to grant EXECUTE after deployment
• Relying on secrecy even though users with EXECUTE can read the script
• Building governance logic that depends on heavy metadata queries in the hot path
• Trying to use preprocessing alone to solve a metadata-shaping problem that really needs views or a virtual schema
• Assuming extra semantic joins are free without checking cold-vs-warm profile data
• Claiming a rewrite works system-wide when it has only been tested on a narrow statement shape

Expected Output

When you implement or update a preprocessor, aim to deliver:

• the script or generator change
• installation SQL or generation command
• the activation steps
• at least one live Exasol validation
• explicit documentation of the supported surface and current boundaries

Be clear about what is:

• guaranteed by official docs
• learned from upstream examples
• inferred from experimentation
• limited to the current implementation