3.4. LIN_LIVE_LINEAGE Reference

3.4.1. Overview

LIN_LIVE_LINEAGE is a Lua scalar script deployed within the Exasol environment. It acts as a dynamic SQL generator designed to be used in conjunction with Exasol’s IMPORT FROM SCRIPT functionality.

The script dynamically constructs a complex hierarchical query (using CONNECT BY) that calculates the live, column-level data lineage for a given SQL statement. It bridges on-the-fly SQL parsing (via lin_live_lineage_udf) with the persisted historical lineage data stored in the lin_base and lin_items_all tables.

3.4.2. Parameters

The function generate_sql_for_import_spec(import_spec) extracts its arguments from the import_spec.parameters object.

Parameter

Type

Required

Description

TARGET_SQL

String

Yes

The raw SQL query string for which the lineage will be generated.

TARGET_COL

String

No

Specific target column for which the lineage will be generated (optional). Defaults to tracing all columns if omitted.

LAYER_MAPPING

String

No

Optional flag/container to map objects to architectural layers (default: RED_STD_ARCHITECTURE).

HIERARCHY

String

No

Optional hierarchy identifier used to resolve layer containers (default: RED_STANDARD).

SQL_DEBUG

Boolean/String

No

If set to "TRUE", returns the generated SQL statement as a string for debugging rather than executing it.

3.4.3. Generated SQL Workflow

When invoked, the script generates an extensive SQL statement that performs the following logical steps:

  1. Snapshot Resolution (CTE: max_versionid): Identifies the most recent metadata snapshot by fetching the maximum version_id from the lin_base table.

  2. Live Lineage Generation (CTE: live_lineage): Invokes the internal lin_live_lineage_udf to parse the provided TARGET_SQL against the system’s catalog (exa_all_columns). It joins these real-time results with the latest snapshot of lin_items_all to resolve exact item identifiers.

  3. Node & Edge Resolution: Combines the persisted dependency edges (from lin_base) with the newly generated live edges. It formats the node names, resolving physical schema/table combinations or mapping them to friendly system/project names via lin_md_system_mapping.

  4. Hierarchical Traversal (CONNECT BY): Executes an Oracle-style recursive graph traversal:

    1START WITH a.is_start_node = 1
    2CONNECT BY NOCYCLE prior a.referenced_item_id = a.item_id
    
  5. Output Formatting: Calculates the exact path depth (path_level) for each traversed node in the hierarchy.

3.4.4. Returns

The script emits the structured lineage graph with the following core attributes:

  • version_id: The timestamp or ID of the lineage snapshot.

  • path_level: The depth of the node in the directed acyclic graph (DAG).

  • source_system_name: The name of the source system.

  • source_schema: The name of the source schema.

  • source_table: The name of the source object.

  • source_column: The name of the source column.

  • source_column_datatype: The data type of the source column.

  • source_object_type: The object type of the source object (e.g., TABLE, VIEW).

  • source_object_layer: The name of the source object layer.

  • source_bi_project: The name of the source BI project.

  • source_bi_type: The name of the source BI type.

  • source_bi_id: The name of the source BI id.

  • target_system_name: The name of the target system.

  • target_schema: The name of the target schema.

  • target_table: The name of the target object.

  • target_column: The name of the target column.

  • target_column_datatype: The data type of the target column.

  • target_object_type: The object type of the target object (e.g., TABLE, VIEW).

  • target_object_layer: The name of the target object layer.

  • target_bi_project: The name of the target BI project.

  • target_bi_type: The name of the target BI type.

  • target_bi_id: The name of the target BI id.

  • expression: The SQL transformation logic applied at this step (if available).

  • is_leaf: Boolean flag indicating if the node is a terminal leaf.

  • is_cycle: Boolean flag indicating if the node is part of a circular dependency.

3.4.5. Usage Example

To execute this script and retrieve the live lineage within Exasol, use the IMPORT FROM SCRIPT syntax:

Example 1: CTE Expression

1SELECT * FROM (
2    IMPORT FROM SCRIPT "REDLIN"."LIN_LIVE_LINEAGE" WITH
3    TARGET_SQL = 'with x as (SELECT 3 + 2 AS col_c), y as (select 5 as col_a) select col_c*col_a as col_b from x join y on col_c=col_a'
4);

Example 2: Simple Select Statement

1SELECT * FROM (
2    IMPORT FROM SCRIPT "REDLIN"."LIN_LIVE_LINEAGE" WITH
3    TARGET_SQL = 'SELECT col_a + col_b AS col_c FROM my_schema.my_table'
4);

Example 3: Debugging the generated SQL

1SELECT * FROM (
2    IMPORT FROM SCRIPT "REDLIN"."LIN_LIVE_LINEAGE" WITH
3    TARGET_SQL = 'SELECT col_a + col_b AS col_c FROM my_schema.my_table',
4    SQL_DEBUG = 'TRUE'
5);