DOCSEARCHbyAlgolia
Submit Docs

SQLLineage Documentation

repository·master·Indexed 23 days ago

https://github.com/reata/sqllineage

A Python-powered static SQL lineage analysis tool that identifies source and target tables and columns from SQL commands. It supports multiple dialects (including SparkSQL, Hive, Snowflake, and BigQuery), column-level lineage analysis, and metadata-aware resolution via SQLAlchemy. The tool provides a CLI for parsing strings or files and can generate visual Directed Acyclic Graph (DAG) representations of data flow.

Tokens
9.7K
Snippets
24
Records
77
Agent score
81%

What's inside sqllineage

  1. Get started with SQLLineage

    master

    SQLLineage is a tool used to analyze SQL commands to identify their source and target tables. It leverages sqlfluff and sqlparse for parsing, and networkx for graph-based lineage storage.

    To get started, you can:

    1. Use the CLI: Install the package and use the built-in command-line tool for quick analysis.
    2. Use the Python API: Integrate SQLLineage directly into your Python scripts for programmatic lineage analysis.
    3. Advanced Usage: Perform lineage analysis on multi-statement SQL or visualize the resulting lineage graphs.

  2. Improve column lineage accuracy with MetaDataProvider

    master

    To accurately handle cases like SELECT * or unqualified columns in joins, SQLLineage requires metadata. Without metadata, column lineage output may only be partially accurate.

    SQLLineage provides the MetaDataProvider mechanism to allow users to supply metadata information. You can use the built-in implementations or extend the base class sqllineage.core.metadata_provider.MetaDataProvider to build your own.

  3. Understand the LineageHolder abstraction hierarchy

    master

    SQLLineage uses LineageHolder abstractions to store lineage results at different granularities during analysis. The hierarchy is structured as follows:

    1. SubQueryLineageHolder: Holds lineage at the subquery level. This is an internal abstraction used by the LineageAnalyzer during the parsing and analysis process.
    2. StatementLineageHolder: Represents the lineage result for a single SQL statement. This is the primary object generated by LineageAnalyzer when analyzing a statement.
    3. SQLLineageHolder: Acts as a high-level container that assembles multiple StatementLineageHolder objects into a Directed Acyclic Graph (DAG) structure. This provides the final, unified lineage output for complex scripts containing multiple statements.

  4. Core abstractions in SQLLineage

    master

    SQLLineage is built around several key components that handle the lifecycle of lineage analysis:

    • LineageRunner: The primary entry point for executing SQLLineage tasks.
    • LineageAnalyzer: The core engine responsible for analyzing a single SQL statement.
    • LineageHolder: A container used to hold lineage results at various levels of granularity.
    • MetaDataProvider: An interface used to provide metadata (such as schema information) to assist the analyzer in producing more accurate lineage results.
    • Data Classes: Internal models used to represent the lineage data structures.

  5. How SQLLineage represents multiple statements using a DAG

    master

    When analyzing SQL containing multiple statements, SQLLineage uses a Directed Acyclic Graph (DAG) data structure to represent the lineage.

    • Vertices: Represent Tables, Views, or Columns (in the case of column-level lineage).
    • Edges: Represent the data flow, indicating that data from a source vertex contributes to a target vertex.

    This DAG-based approach allows SQLLineage to combine the lineage results of individual statements into a single, cohesive map of data movement, which also facilitates lineage visualization.

  6. Understand Column-Level Lineage Design Principles

    master

    SQLLineage implements column-level lineage using the following core principles:

    1. Static Analysis First: The tool remains primarily a static code analysis tool. It is designed to tolerate missing information and does not depend solely on external metadata to function.
    2. Unified DAG: Column-level lineage is not a separate graph from table-level lineage. Instead, they are part of a unified Directed Acyclic Graph (DAG). This is achieved by treating Table and Column as different types of vertices in a property graph, where edges represent both column-to-table relationships and lineage flows.

  7. How SQLLineage handles ambiguous column lineage

    master

    When performing column-level lineage, SQLLineage uses specific strategies to handle common SQL ambiguities:

    Handling SELECT *

    When a SELECT * is used (e.g., INSERT OVERWRITE tab1 SELECT * FROM tab2;), the tool cannot know the specific columns in the source table. It handles this by adding a virtual column * for each table. The resulting lineage is represented as tab2.* -> tab1.*.

    Handling Unprefixed Columns in Joins

    When columns lack a table or alias prefix in a join (e.g., SELECT col2 FROM tab2 JOIN tab3 ON tab2.col1 = tab3.col1), the tool cannot definitively determine the source. To account for this, it adds edges from all potential sources to the target: tab2.col2 -> tab1.col2 and tab3.col2 -> tab1.col2. These edges are marked so they can be visually distinguished (e.g., as dotted lines) in lineage visualizations.

    -- Example of SELECT *
INSERT OVERWRITE tab1
SELECT * FROM tab2;

-- Example of ambiguous join columns
INSERT OVERWRITE tab1
SELECT col2
FROM tab2
JOIN tab3
ON tab2.col1 = tab3.col1

  8. Improve column-level lineage accuracy with MetaDataProvider

    master

    By default, SQLLineage performs column-to-table resolution in a "best-effort" way when metadata is unavailable. For ambiguous queries, it will provide possible table candidates rather than a definitive mapping.

    To achieve higher accuracy for column-level lineage, you can leverage the MetaDataProvider functionality. This allows you to programmatically register metadata information, enabling SQLLineage to perform semantic analysis (catalog resolution) to resolve columns to their specific tables.

  9. How dialect-aware lineage works

    master

    SQLLineage uses a parser interface to handle the fragmentation of SQL dialects.

    • Parser Implementations: Located in the sqllineage.core.parser module. These implementations extend LineageAnalyzer and common Models.
    • Analysis Flow: The LineageRunner splits the input SQL into single statements, which are then passed to the LineageAnalyzer. The analyzer returns a StatementLineageHolder containing the lineage information.
    • Error Handling: When using dialect-aware parsing, the system can explicitly notify you of issues via:
      • InvalidSyntaxException: When the SQL syntax is not recognized.
      • UnsupportedStatementException: When the syntax is valid but the lineage cannot be analyzed.

  10. Understand SQLLineage's static analysis approach

    master

    SQLLineage is a static SQL code lineage analysis tool. It does not execute SQL against a server and requires no client/server interaction. Instead, it treats SQL as text, parses it, and analyzes the resulting structure.

    Because it operates on the unresolved AST (Abstract Syntax Tree) layer immediately after the parsing phase, it has the following characteristics:

    • Pros: It is dialect-agnostic and can function without a live database connection or catalog.
    • Cons: Column-level lineage may not be 100% accurate because the tool does not inherently know the schema behind statements like SELECT * or ambiguous joins (e.g., SELECT col FROM tab1 JOIN tab2).

  11. Understand the SQLLineage data model components

    master

    SQLLineage uses a set of core data classes to represent the structural components of SQL queries. When working with lineage results, you will interact with these four primary abstractions:

    • Schema: Represents a logical grouping of tables (e.g., database_name.table_name).
    • Table: Represents a specific data table or view within a schema.
    • SubQuery: Represents a nested query structure used within a larger SQL statement.
    • Column: Represents individual fields within a table or subquery, enabling column-level lineage tracking.

  12. Understand the LineageAnalyzer abstraction

    master

    In sqllineage, LineageAnalyzer is an abstract base class that defines the core processing logic for analyzing a single SQL statement.

    When working with the library's internals or extending it, you should know that:

    • It serves as the foundation for statement-level analysis.
    • Specific parser implementations inherit from LineageAnalyzer to perform analysis tailored to the Abstract Syntax Tree (AST) generated by that specific parser.
    • The results of the analysis are stored in a StatementLineageHolder object.