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 |
|---|---|---|---|
|
String |
Yes |
The raw SQL query string for which the lineage will be generated. |
|
String |
No |
Specific target column for which the lineage will be generated (optional). Defaults to tracing all columns if omitted. |
|
String |
No |
Optional flag/container to map objects to architectural layers (default: |
|
String |
No |
Optional hierarchy identifier used to resolve layer containers (default: |
|
Boolean/String |
No |
If set to |
3.4.3. Generated SQL Workflow¶
When invoked, the script generates an extensive SQL statement that performs the following logical steps:
Snapshot Resolution (CTE: max_versionid): Identifies the most recent metadata snapshot by fetching the maximum
version_idfrom thelin_basetable.Live Lineage Generation (CTE: live_lineage): Invokes the internal
lin_live_lineage_udfto parse the providedTARGET_SQLagainst the system’s catalog (exa_all_columns). It joins these real-time results with the latest snapshot oflin_items_allto resolve exact item identifiers.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 vialin_md_system_mapping.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
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);