User Guide¶

from gsql2rsql import GraphContext

# Table names are just strings - no database connection required
graph = GraphContext(
    nodes_table="my_nodes",
    edges_table="my_edges",
)
graph.set_types(
    node_types=["Person", "Company"],
    edge_types=["WORKS_AT"],
)

# Generate SQL from OpenCypher
sql = graph.transpile("""
    MATCH (p:Person)-[:WORKS_AT]->(c:Company)
    RETURN p.node_id, c.node_id
""")

print(sql)  # Copy this SQL to run on Databricks

from gsql2rsql import GraphContext

# Define schema with additional attributes
graph = GraphContext(
    nodes_table="catalog.schema.nodes",
    edges_table="catalog.schema.edges",
    extra_node_attrs={"name": str, "age": int, "score": float},
    extra_edge_attrs={"weight": float, "timestamp": str},
)

graph.set_types(
    node_types=["Person", "Company", "Account"],
    edge_types=["KNOWS", "WORKS_AT", "OWNS"],
)

# Now you can use those attributes in queries
sql = graph.transpile("""
    MATCH (p:Person)-[:WORKS_AT]->(c:Company)
    WHERE c.industry = 'Technology'
    RETURN p.name, c.name AS company
    LIMIT 100
""")

print(sql)

# Filter by node_type without declaring it in extra_node_attrs
sql = graph.transpile("""
    MATCH (a)-[r]->(b)
    WHERE a.node_type IN ['Person', 'Company']
    RETURN a.node_id, r.relationship_type, b.node_id
""")

# Node 'a' has no label - matches ANY node type
sql = graph.transpile("""
    MATCH (a)-[:WORKS_AT]->(c:Company)
    RETURN a, c.name
""")

sql = graph.transpile("""
    MATCH path = (origin:Person {id: 12345})-[:KNOWS*1..3]->(friend:Person)
    RETURN friend.name, length(path) AS hops
    ORDER BY hops
""")

sql = graph.transpile(
    """
    MATCH path = (a:Person)-[:KNOWS*1..4]->(b:Person)
    WHERE a.node_id = 'alice' AND b.node_id = 'dave'
    RETURN nodes(path) AS path_nodes
    """,
    bidirectional_mode="recursive"  # Enable optimization
)

# Inline filter (optimized - pushed to source scan)
sql = graph.transpile("""
    MATCH (p:Person {status: 'active'})-[:KNOWS]->(friend)
    RETURN p.name, friend.name
""")

# Equivalent WHERE clause (less optimized)
sql = graph.transpile("""
    MATCH (p:Person)-[:KNOWS]->(friend)
    WHERE p.status = 'active'
    RETURN p.name, friend.name
""")

sql = graph.transpile("""
    MATCH (a:Person)-[:KNOWS]-(b:Person)
    WHERE a.id = 123
    RETURN b.name
""")

sql = graph.transpile("""
    MATCH (p:Person)-[:WORKS_AT]->(c:Company)
    RETURN c.name, COUNT(p) AS employees, AVG(p.salary) AS avg_salary
    ORDER BY employees DESC
""")

sql = graph.transpile("""
    MATCH (p:Person)-[:WORKS_AT]->(c:Company)
    WITH c, COUNT(p) AS employee_count
    WHERE employee_count > 100
    MATCH (c)-[:LOCATED_IN]->(city:City)
    RETURN c.name, employee_count, city.name
""")

sql = graph.transpile("""
    MATCH (p:Person)-[:KNOWS]->(friend:Person)
    RETURN p.name AS source, friend.name AS target, 'KNOWS' AS type
    UNION
    MATCH (p:Person)-[:WORKS_AT]->(c:Company)
    RETURN p.name AS source, c.name AS target, 'WORKS_AT' AS type
""")

from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()

graph = GraphContext(
    spark=spark,  # Enable execution
    nodes_table="catalog.schema.nodes",
    edges_table="catalog.schema.edges",
)
graph.set_types(node_types=["Person"], edge_types=["KNOWS"])

# Transpile and execute
sql = graph.transpile("MATCH (p:Person) RETURN p.name LIMIT 10")
df = spark.sql(sql)
df.show()

from gsql2rsql import OpenCypherParser, LogicalPlan, SQLRenderer
from gsql2rsql.common.schema import NodeSchema, EdgeSchema, EntityProperty
from gsql2rsql.renderer.schema_provider import SimpleSQLSchemaProvider, SQLTableDescriptor

# 1. Define schema (SimpleSQLSchemaProvider)
schema = SimpleSQLSchemaProvider()

person = NodeSchema(
    name="Person",
    properties=[
        EntityProperty(property_name="id", data_type=int),
        EntityProperty(property_name="name", data_type=str),
    ],
    node_id_property=EntityProperty(property_name="id", data_type=int)
)
schema.add_node(
    person,
    SQLTableDescriptor(
        table_name="catalog.schema.people",  # Separate table for Person nodes
        node_id_columns=["id"],
    )
)

knows = EdgeSchema(
    name="KNOWS",
    source_node_id="Person",
    sink_node_id="Person",
    source_id_property=EntityProperty(property_name="person_id", data_type=int),
    sink_id_property=EntityProperty(property_name="friend_id", data_type=int),
)
schema.add_edge(
    knows,
    SQLTableDescriptor(
        entity_id="Person@KNOWS@Person",
        table_name="catalog.schema.friendships",  # Separate table for KNOWS edges
    )
)

# 2. Transpile
parser = OpenCypherParser()
ast = parser.parse("MATCH (p:Person)-[:KNOWS]->(f:Person) RETURN p.name, f.name")
plan = LogicalPlan.process_query_tree(ast, schema)
plan.resolve(original_query="...")

renderer = SQLRenderer(db_schema_provider=schema)
sql = renderer.render_plan(plan)

# ❌ INVALID - WHERE cannot follow UNWIND directly
sql = graph.transpile("""
    MATCH (s)-[e:KNOWS*1..3]->(o)
    UNWIND e AS r
    WHERE r.weight > 5
    RETURN r.src, r.dst
""")
# Error: no viable alternative at input 'WHERE'

# ✅ CORRECT - Use WITH to enable WHERE filtering
sql = graph.transpile("""
    MATCH (s)-[e:KNOWS*1..3]->(o)
    UNWIND e AS r
    WITH r WHERE r.weight > 5
    RETURN r.src, r.dst
""")

# ❌ INVALID - 'end' is a reserved word
sql = graph.transpile("""
    MATCH (start)-[:KNOWS]->(end)
    RETURN end.name
""")
# Error: no viable alternative at input '...)->(end'

# ✅ CORRECT - Use a different variable name
sql = graph.transpile("""
    MATCH (start)-[:KNOWS]->(target)
    RETURN target.name
""")

# ❌ May fail in Spark - '+' not supported for strings
sql = graph.transpile("""
    MATCH (p:Person)
    RETURN p.first_name + ' ' + p.last_name AS full_name
""")

# ✅ CORRECT - Use CONCAT function
sql = graph.transpile("""
    MATCH (p:Person)
    RETURN CONCAT(p.first_name, ' ', p.last_name) AS full_name
""")