Skip to content

SQL queries

Not every question is a graph question. lance-csr also runs plain SQL, through DataFusion, over the same PyArrow tables and Lance datasets. No GraphConfig is needed: you write the joins yourself.

Python
import pyarrow as pa
from lance_csr import SqlEngine, SqlQuery

datasets = {
    "person": pa.table({"id": [1, 2, 3], "name": ["Alice", "Bob", "Carol"], "age": [28, 34, 29]}),
    "knows": pa.table({"src_id": [1, 1, 2], "dst_id": [2, 3, 3]}),
}

SqlQuery: one query

Python
result = SqlQuery("SELECT name, age FROM person WHERE age > 30").execute(datasets)
print(result.to_pylist())  # [{'name': 'Bob', 'age': 34}]
Method What it does
SqlQuery(sql) Holds the query text.
execute(datasets) Registers the datasets, runs the query and returns a pyarrow.Table.
explain(datasets) The DataFusion logical and physical plans, as text.
sql() The query text.

SqlEngine: many queries

SqlEngine registers the datasets once and reuses the DataFusion session:

Python
engine = SqlEngine(datasets)

print(engine.execute("SELECT COUNT(*) AS cnt FROM person").to_pylist())  # [{'cnt': 3}]

friends = engine.execute("""
    SELECT p1.name AS person, p2.name AS friend
    FROM person p1
    JOIN knows k ON p1.id = k.src_id
    JOIN person p2 ON p2.id = k.dst_id
    ORDER BY person, friend
""")
print(friends.to_pylist())
# [{'person': 'Alice', 'friend': 'Bob'}, {'person': 'Alice', 'friend': 'Carol'},
#  {'person': 'Bob', 'friend': 'Carol'}]

Table names are lower-cased

SqlEngine lower-cases dataset names when it registers them, so a "Person" key is queried as person. Constructing an engine with an empty dict raises ValueError.

The SQL dialect is DataFusion's; see the DataFusion SQL reference for what it supports. SqlEngine has no refresh() or CSR: those are CypherEngine features.

From Cypher to SQL

CypherQuery.to_sql(datasets, dialect=None) shows the SQL that a Cypher query is equivalent to. It's handy for understanding a query, or for running a graph pattern on another SQL engine:

Python
from lance_csr import CypherQuery, GraphConfig, SqlDialect

config = (
    GraphConfig.builder()
    .node_table("Person", "id")
    .rel_table("KNOWS", "Person", "Person", "src_id", "dst_id")
    .build()
)
query = CypherQuery(
    "MATCH (a:Person)-[:KNOWS]->(b:Person) RETURN a.name, b.name"
).with_config(config)
graph = {"Person": datasets["person"], "KNOWS": datasets["knows"]}

print(query.to_sql(graph))
print(query.to_sql(graph, SqlDialect.PostgreSql))

SqlDialect has Default (DataFusion's), Spark, PostgreSql, MySql and Sqlite.

This is the join path

The generated SQL expresses each hop as a join. It describes what the query computes, not how CypherEngine runs it: with use_csr=True, hops run on the CSR instead.