pylineage

This package provides simple tools for parsing and visualizing your .sql scripts.

Installation

The package is distributed through PyPI, and can be installed with

pip install pylineage

In order to create a lineage graph you should also install and add Graphviz to your Path.

SQL Parser

Individual basic .sql scripts can be parsed by running

from pylineage import SqlParser

parser = SqlParser()
query = """

SELECT column1
     , column2 AS c2
FROM my_table t
WHERE column1 = 1

"""

parsed = parser.parse(query)

The output looks as follows

>>> parsed

{
  "select": [{ "content": "column1" }, { "content": "column2", "alias": "c2" }],
  "from": { "content": "my_table", "alias": "t" },
  "where": ["column1 = 1"]
}

Currently the parser supports the clauses

Lineage Graph

Based on the SQL parser, a lineage graph can be constructed. We start off with the main constructor:

from pylineage import LineageGraph

lineage_graph = LineageGraph()

Subsequently, there are two options of adding SQL scripts: (1) as individual input strings or (2) as directory.

lineage_graph.extend_graph_from_input_string("CREATE TABLE my_view AS SELECT column1 FROM my_table")

lineage_graph.extend_graph_from_directory("/data")

The graph can be cleared at any time by running

lineage_graph.clear_graph()

One purpose of parsing and visualizing is to obtain execution order. This can be obtained by running

lineage_graph.get_execution_order()

NOTE
Any inner query that is not a part of a source clause (FROM / JOIN) is not included as a node in the graph. As such, statements like the one below are not taken into account.

...
WHERE column not in (
  SELECT column
  FROM table2
)

Finally the Lineage Graph can be accessed by directly checking

lineage_graph.graph

or by running it in interactive mode:

lineage_graph.serve_graph()

The interactive mode offers convenient highlighting and dragging capabilities.