2.4. Fixing SQLGLOT Parsing Issues

In some cases, the sqlglot parser might have difficulties parsing complex or specific Exasol SQL syntax. To handle these cases, REDLIN provides a mechanism to override the original view definition with a corrected one for the sqlglot parser using the lin_md_corrected_views_base table.

2.4.1. The Corrected Views Table

When sqlglot fails to parse a view, you can provide an alternative, simplified, or corrected SQL definition that sqlglot can parse. This ensures that the lineage is generated correctly even if the original SQL is too complex.

Instead of writing to the table directly, this configuration is managed via an API interface.

2.4.1.1. API Parameters - “API_REDLIN”.”ADD_MD_CORRECTED_VIEWS_BASE

The API interface expects the following parameters to register a corrected view:

============== ============ ============== =================================
Parameter Name Properties   Default Value  Parameter Description
============== ============ ============== =================================
p_schema_name  (PK)(NN)(UC)                Schema name
p_view_name    (PK)(NN)(UC)                View name
p_view_text                                View Definition
p_comment                                  Comment related to the correction
p_active                                   Active Configuration
============== ============ ============== =================================

2.4.1.2. Usage

1. Identify the view that sqlglot fails to parse.

2. Create a simplified version of the view’s SQL (p_view_text) that maintains the same source dependencies (to ensure correct lineage) but uses syntax that sqlglot supports.

3. Use the API to insert or update the record for the specific p_schema_name and p_view_name.

4. Ensure p_active is set appropriately to enable the configuration.

5. The next time the lineage harvester runs, it will use your provided p_view_text instead of the original view definition from the database.

2.4.1.3. Known SQLGlot Exasol Parsing Issues

• Exasol Skyline

SELECT * FROM funds PREFERRING HIGH performance PLUS LOW volatility;

• Self written Emit Functions

• UTF8 in casts

cast (null as                                           varchar (20) utf8) as dwh_test

• Values Table Clause

SELECT * FROM VALUES BETWEEN 1 AND 15 WITH STEP 4;