SQL vs Python

SQL and Python are two interfaces for building the same lazy Vane Data pipeline. You can use either interface independently or mix them in one pipeline: both produce Relation objects that either interface can consume.

SQL Interface

Use con.sql(...) when the transformation is clearest as a SQL statement. Python Relation variables such as source can be referenced directly by name:

example.py

import vane


con = vane.connect()
source = con.values(
    (
        vane.lit(1).alias("order_id"),
        vane.lit("books").alias("category"),
        vane.lit(2).alias("quantity"),
        vane.lit(12).alias("unit_price"),
    ),
    (vane.lit(2), vane.lit("games"), vane.lit(1), vane.lit(60)),
    (vane.lit(3), vane.lit("books"), vane.lit(3), vane.lit(8)),
    (vane.lit(4), vane.lit("stationery"), vane.lit(1), vane.lit(5)),
)


result = con.sql("""
    SELECT
        order_id,
        category,
        quantity * unit_price AS total
    FROM source
    WHERE quantity * unit_price >= 20
    ORDER BY order_id
""")
result.show()

Python Interface

Use Relation methods for table operations and Expression objects for columns, literals, arithmetic, and predicates:

example.py

import vane


con = vane.connect()
source = con.values(
    (
        vane.lit(1).alias("order_id"),
        vane.lit("books").alias("category"),
        vane.lit(2).alias("quantity"),
        vane.lit(12).alias("unit_price"),
    ),
    (vane.lit(2), vane.lit("games"), vane.lit(1), vane.lit(60)),
    (vane.lit(3), vane.lit("books"), vane.lit(3), vane.lit(8)),
    (vane.lit(4), vane.lit("stationery"), vane.lit(1), vane.lit(5)),
)


result = source.filter(
    vane.col("quantity") * vane.col("unit_price") >= vane.lit(20)
).select(
    vane.col("order_id"),
    vane.col("category"),
    (
        vane.col("quantity") * vane.col("unit_price")
    ).alias("total"),
).order("order_id")
result.show()

Mixed Pipeline

Relations can cross the interface boundary in either direction. This example uses SQL to compute totals, Python to select rows, and SQL to aggregate the result:

example.py

import vane


con = vane.connect()
source = con.values(
    (
        vane.lit(1).alias("order_id"),
        vane.lit("books").alias("category"),
        vane.lit(2).alias("quantity"),
        vane.lit(12).alias("unit_price"),
    ),
    (vane.lit(2), vane.lit("games"), vane.lit(1), vane.lit(60)),
    (vane.lit(3), vane.lit("books"), vane.lit(3), vane.lit(8)),
    (vane.lit(4), vane.lit("stationery"), vane.lit(1), vane.lit(5)),
)


# SQL stage.
priced = con.sql("""
    SELECT
        order_id,
        category,
        quantity * unit_price AS total
    FROM source
""")


# Python stage.
selected = priced.filter(
    vane.col("total") >= vane.lit(20)
).select(
    vane.col("category"),
    vane.col("total"),
)


# SQL stage.
summary = con.sql("""
    SELECT category, sum(total) AS revenue
    FROM selected
    GROUP BY category
    ORDER BY category
""")
summary.show()