Basic Functionality#

This guide covers the basics of expressing rules and queries with the RelationalAI (RAI) Python package. Read the Core Concepts guide before proceeding to learn the basics of RAI’s declarative query-builder syntax.

To follow along with this guide, you’ll need:

A Snowflake account with the RAI Native App installed.
A Snowflake user that has been granted the app_user application role.
The relationalai Python package, either installed locally or in a cloud notebook.

Table of Contents#

Inspecting Types and Properties
Filtering Objects by Type
Aliasing Column Names
Filtering Objects by Property Value
A Note About Logical Operators
Filtering By Existence of Conditions
Expressing if Using model.case()
Expressing if-else Using model.match()
Calculating Values
Using Aggregate Functions
Dealing With Null Values
Writing Results to Snowflake
Exporting SQL Stored Procedures
Summary and Next Steps

Inspecting Types and Properties#

Use inspect() to see a sample of objects of a given type:

#import relationalai as rai
from relationalai.std.inspect import inspect, pprint


# =====
# SETUP
# =====

model = rai.Model("MyModel")
Person = model.Type("Person")
Adult = model.Type("Adult")

with model.rule():
    Person.add(id=1).set(name="Alice", age=16)
    Person.add(id=2).set(name="Bob", age=18)

with model.rule():
    person = Person()
    person.age >= 18
    person.set(Adult)


# =======
# EXAMPLE
# =======

df = inspect(Person)
print(df)
#                   person   name  age
# 0 aiTt+pn1Y6IexW+u9SgXhg  Alice   16
# 1 ppSccXmTLAgcxu4ZWgcKug    Bob   18

# Alternatively, use pprint() to pretty print a table.
pprint(Person)
#   person                   id   name    age
#  ───────────────────────────────────────────
#   d1SmRsWF5TLVmYhCCPhD9g   1    Alice   16
#   g4rDjPY1HHWkEikWQXw+3Q   2    Bob     18

The first column contains the object’s unique identifier and is labeled with the lowercased name of the type. Additional columns show the object’s properties. Only properties of objects that are used by the type being inspected are shown.

For example, inspecting Adult shows the hash for one object, but no properties:

#inspect(Adult)
#   adult
#  ────────────────────────
#   g4rDjPY1HHWkEikWQXw+3Q

The model only knows properties that can be statically inferred from the rules. In this case, even though Adult is a subtype of Person, the model does not know that Adult objects have the same properties as Person objects.

You can remedy this by inspecting the Person type and intersecting it with the Adult type using the & operator:

#inspect(Person & Adult)
#   person                   id   name   age
#  ──────────────────────────────────────────
#   g4rDjPY1HHWkEikWQXw+3Q   2    Bob    18

To get a list of all statically known properties of a type, use Type.known_properties():

#print(Person.known_properties())
# ['id', 'name', 'age']

Filtering Objects by Type#

Variables that represent objects in a query or rule are created by calling a type like a function:

#with model.query() as select:
    # person is a variable that represents a Person object.
    person = Person()
    response = select(person.id, person.name)

There are several ways to filter objects by additional types:

#import relationalai as rai


# =====
# SETUP
# =====

model = rai.Model("MyModel")
Person = model.Type("Person")
Adult = model.Type("Adult")
Student = model.Type("Student")

with model.rule():
    Person.add(id=1).set(Adult, Student, name="Alice", age=20)
    Person.add(id=2).set(Adult, name="Bob", age=18)


# ========
# EXAMPLES
# ========

# EXAMPLE 1: Using Type(object).
with model.query() as select:
    person = Adult()  # person is an Adult object
    Student(person)  # AND person is a Student object
    response = select(person.id, person.name)


# EXAMPLE 2: Passing additional types to Type().
with model.query() as select:
    person = Adult(Student)  # Could also be written as: Person(Adult, Student).
    response = select(person.id, person.name)


# EXAMPLE 3a: Using the & operator.
AdultStudent = Adult & Student
with model.query() as select:
    person = AdultStudent()
    response = select(person.id, person.name)


# EXAMPLE 3b: Using the & operator inline.
with model.query() as select:
    person = (Adult & Student)()
    response = select(person.id, person.name)


# All four queries return the same results.
print(response.results)
#    id   name
# 0   1  Alice

To filter objects that are in one type or another, combine types using the pipe (|) operator:

## Using the | operator.
AdultOrStudent = Adult | Student
with model.query() as select:
    person = AdultOrStudent()
    response = select(person.id, person.name)


# Using the | operator inline.
with model.query() as select:
    person = (Adult | Student)()
    response = select(person.id, person.name)


# Both queries return the same results.
print(response.results)
#    id   name
# 0   1  Alice
# 1   2    Bob

Aliasing Column Names#

Use alias() to rename result columns:

#from relationalai.std import alias


with model.query() as select:
    person = Person()
    response = select(
        alias(person.name, "person_name"),
        alias(person.age, "person_age"),
    )

print(response.results)
#   person_name  person_age
# 0       Alice          20

Filtering Objects by Property Value#

Use comparison operators to filter objects and their properties. The following operators are supported:

Operator	Description
`==`	Equal to
`!=`	Not equal to
`>`	Greater than
`>=`	Greater than or equal to
`<`	Less than
`<=`	Less than or equal to
`.in_()`	In a list of values

For example, the following query selects people who are at least 20 years old and like the color blue:

#import relationalai as rai


# =====
# SETUP
# =====

model = rai.Model("MyModel")
Person = model.Type("Person")

with model.rule():
    alice = Person.add(id=1).set(name="Alice", age=18, favorite_color="red")
    bob = Person.add(id=2).set(name="Bob", age=20, favorite_color="blue")
    carol = Person.add(id=3).set(name="Carol", age=22, favorite_color="green")

    alice.friends.add(bob)
    bob.friends.extend([alice, carol])
    carol.friends.add(bob)


# =======
# EXAMPLE
# =======

with model.query() as select:
    person = Person()
    person.age >= 20
    person.favorite_color == "blue"
    response = select(person.name, person.age, person.favorite_color)

print(response.results)
#   name  age favorite_color
# 0  Bob   20           blue

You may replace == by passing keyword arguments when you call the type. The following query is equivalent to the previous one:

#with model.query() as select:
    person = Person(favorite_color="blue")
    person.age >= 20
    response = select(person.name)

print(response.results)
#   name
# 0  Bob

Chained comparisons, such as 20 <= person.age < 30, are supported:

#with model.query() as select:
    person = Person()
    20 <= person.age < 30
    response = select(person.name)

print(response.results)
#     name
# 0    Bob
# 1  Carol

Conditions on multi-valued properties filter both the object and the property

#with model.query() as select:
    person = Person()
    # Filter person.friends to only include friends named "Alice". People without
    # friends named "Alice" are filtered out.
    person.friends.name == "Alice"
    response = select(person.name, person.friends.name)

print(response.results)
#   name  name2
# 0  Bob  Alice


# Compare that to:

with model.query() as select:
    person = Person()
    # Filter person.friends to only include friends not named "Alice". People who
    # are only friends with someone named "Alice" are filtered out.
    person.friends.name != "Alice"
    response = select(person.name, person.friends.name)

# Bob is included in the results because he is friends with Carol.
print(response.results)
#     name  name2
# 0  Alice    Bob
# 1    Bob  Carol
# 2  Carol    Bob

Use InstanceProperty.in_() to filter a property by a list of values:

#with model.query() as select:
    person = Person()
    person.favorite_color.in_(["blue", "red"])
    response = select(person.name)

print(response.results)
#     name
# 0  Alice
# 1    Bob

A Note About Logical Operators#

Logical operators like and, or, and not are not supported in RAI query-builder syntax.

Python’s logical operators expect Boolean operands. For a comparison operator like > to return a Boolean, it must compare the left operand to the right operand when Python interprets the expression. But the truth value of a comparison in RAI query-builder syntax can’t be known until the entire query is constructed and evaluated.

Conditions in RAI queries are implicitly conjunctive, which removes the need for an and operator. Each line in a rule or query is automatically joined to the previous line by and:

#with model.query() as select:
    person = Person()  # person is a Person object
    person.age >= 18   # AND person is at least 18 years old
    person.favorite_color == "blue"  # AND person likes the color blue
    response = select(person.name)

The trade-off is that expressing or is less natural. Approaches for writing disjunctions are discussed in the Expressing if Using model.case() and Expressing if-else Using model.match() sections.

Filtering By Existence of Conditions#

Use model.found() to filter objects based on the existence of a condition:

#import relationalai as rai


# =====
# SETUP
# =====

model = rai.Model("MyModel")
Person = model.Type("Person")

with model.rule():
    alice = Person.add(name="Alice")
    bob = Person.add(name="Bob")
    carol = Person.add(name="Carol")

    alice.friends.add(bob)
    bob.friends.extend([alice, carol])
    carol.friends.add(bob)


# =======
# EXAMPLE
# =======

with model.query() as select:
    person = Person()
    # Filter people who have friends named "Alice".
    with model.found():
        person.friends.name == "Alice"
    response = select(person.name, person.friends.name)

# Note person.friends has not been filtered, only person.
print(response.results)
#   name  name2
# 0  Bob  Alice
# 1  Bob  Carol


# Compare to:

with model.query() as select:
    person = Person()
    # Filter person.friends to only include friends named "Alice".
    person.friends.name == "Alice"
    response = select(person.name, person.friends.name)

print(response.results)
#   name  name2
# 0  Bob  Alice

model.found() works like a SQL EXISTS clause. Objects pass the filter if all of the conditions inside the model.found() block are true.

model.not_found() filters if none of the conditions inside the block are true:

#with model.query() as select:
    person = Person()
    # Filter people who do not have friends named "Alice".
    with model.not_found():
        person.friends.name == "Alice"
    response = select(person.name, person.friends.name)

print(response.results)
#     name name2
# 0  Alice   Bob
# 1  Carol   Bob


# Compare to:

with model.query() as select:
    person = Person()
    # Filter person.friends to only include friends not named "Alice".
    person.friends.name != "Alice"
    response = select(person.name, person.friends.name)

print(response.results)
#     name  name2
# 0  Alice    Bob
# 1    Bob  Carol
# 2  Carol    Bob

Expressing `if` Using `model.case()`#

Python’s if keyword isn’t supported in RAI query-builder syntax, since conditions like person.age >= 18 don’t return a Boolean. Instead, if is expressed using the model.case() context manager:

#import relationalai as rai


# =====
# SETUP
# =====

model = rai.Model("MyModel")
Person = model.Type("Person")
Senior = model.Type("Senior")
Adult = model.Type("Adult")
Minor = model.Type("Minor")

with model.rule():
    alice = Person.add(name="Alice", age=10)
    bob = Person.add(name="Bob", age=30)
    carol = Person.add(name="Carol", age=70)


# =======
# EXAMPLE
# =======

with model.rule():
    person = Person()

    # If a person is under 18, they are a minor.
    with model.case():
        person.age < 18
        person.set(Minor)

    # If a person is 18 or older, they are an adult.
    with model.case():
        person.age >= 18
        person.set(Adult)

    # If a person is 65 or older, they are a senior.
    with model.case():
        person.age >= 65
        person.set(Senior)


# Both Bob and Carol are adults.
with model.query() as select:
    adult = Adult()
    response = select(adult.name, adult.age)

print(response.results)
#     name  age
# 0    Bob   30
# 1  Carol   70

Actions inside a model.case() block, like setting types and properties, are applied only if the conditions inside the block are true. Multiple blocks work like a chain of if statements without elif or else clauses. You can express if-elif-else using model.match().

Note that model.case() is only necessary if you need a rule or query to branch based on multiple conditions. For example, the following two rules are equivalent:

#with model.rule():
    person = Person()
    with model.case():
        person.age < 18
        person.set(Minor)


# The following rule is equivalent to the preceding one. No case block is needed
# because there is only one logical branch.
with model.rule():
    person = Person()
    person.age < 18
    person.set(Minor)

You may replace model.case() with a condition. For instance, the rule in the preceding example could be written more compactly as:

#with model.rule():
    person = Person()
    with person.age < 18:
        person.set(Minor)
    with person.age >= 18:
        person.set(Adult)
    with person.age >= 65:
        person.set(Senior)

Note that chained comparisons are not supported:

#Teenager = model.Type("Teenager")

with model.rule():
    person = Person()

    # INCORRECT
    with 13 <= person.age <= 19:  # This raises an error.
        person.set(Teenager)

    # CORRECT
    with model.case():
        13 <= person.age <= 19
        person.set(Teenager)

    # Or, alternatively:
    with 13 <= person.age:
        person.age <= 19
        person.set(Teenager)

model.case() blocks may be nested:

#with model.rule:
    person = Person()
    with person.age < 18:
        person.set(Minor)
    with person.age >= 18:
        person.set(Adult)
        with person.age >= 65:  # Nested case block.
            person.set(Senior)

Expressing `if-else` Using `model.match()`#

model.case() allows you to write if-like logic, but it doesn’t support else clauses. To express if-else logic, you can use model.match(), which behaves like a Python match statement or a SQL CASE expression:

#import relationalai as rai


# =====
# SETUP
# =====

model = rai.Model("MyModel")
Person = model.Type("Person")
Adult = model.Type("Adult")
Minor = model.Type("Minor")

with model.rule():
    Person.add(name="Alice", age=8)
    Person.add(name="Bob", age=15)
    Person.add(name="Carol", age=19)


# =======
# EXAMPLE
# =======

with model.rule():
    person = Person()

    with model.match():
        # If the person is 18 or older, they are an adult.
        with model.case():
            person.age >= 18
            person.set(Adult)
        # Could also be written as:
        # with person.age >= 18:
        #     person.set(Adult)

        # Otherwise, they are a minor.
        # Note: model.case() without a condition is the default case.
        with model.case():
            person.set(Minor)


# Both Bob and Carol are adults.
with model.query() as select:
    minor = Minor()
    response = select(minor.name, minor.age)

# Both Alice and Bob are minors, but Carol is not.
print(response.results)
    name  age
0  Alice    8
1    Bob   15

Only model.case() blocks are allowed inside a model.match() block. You may replace model.case() with a guard condition, such as with person.age >= 18, to express the same logic more compactly.

For each object in the query, only the actions inside the first .case() with matching conditions are applied. A default case can be defined as a .case() block with no conditions.

You can also use model.match() to conditionally assign values to a variable:

#import relationalai as rai
from relationalai.std import alias


# =====
# SETUP
# =====

model = rai.Model("MyModel")
Student = model.Type("Student")
Course = model.Type("Course")
Grade = model.Type("Grade")

with model.rule():
    math = Course.add(name="Math")
    science = Course.add(name="Science")
    history = Course.add(name="History")

    alice = Student.add(name="Alice")
    alice.grades.extend([
        Grade.add(student=alice, course=math).set(value=90),
        Grade.add(student=alice, course=science).set(value=85),
        Grade.add(student=alice, course=history).set(value=75),
    ])

    bob = Student.add(name="Bob")
    bob.grades.extend([
        Grade.add(student=bob, course=math).set(value=65),
        Grade.add(student=bob, course=science).set(value=80),
        Grade.add(student=bob, course=history).set(value=90),
    ])



# =======
# EXAMPLE
# =======

with model.query() as select:
    student = Student()
    grade = student.grades  # grade is one of the student's grades

    # Conditionally set letter_grade to "A", "B", "C", or "F" based on the grade's value
    with model.match() as letter_grade:
        with grade.value >= 90:
            letter_grade.add("A")  # .add() sets the value for letter_grade
        with grade.value >= 80:
            letter_grade.add("B")
        with grade.value >= 70:
            letter_grade.add("C")
        with model.case():
            letter_grade.add("F")

    response = select(
        student.name,
        grade.course.name,
        alias(grade.value, "grade"),
        alias(letter_grade, "letter_grade"),
    )

print(response.results)
#     name    name2  grade letter_grade
# 0  Alice  History     75            C
# 1  Alice     Math     90            A
# 2  Alice  Science     85            B
# 3    Bob  History     90            A
# 4    Bob     Math     65            F
# 5    Bob  Science     80            B

with model.match() as letter_grade introduces a new variable letter_grade. You call letter_grade.add() inside of a model.case() block to assign a value to it.

Multiple values may be assigned to a variable using model.match(multiple=True):

#from relationalai.std.strings import concat


with model.query() as select:
    student = Student()
    grade = student.grades
    course = grade.course

    with model.match(multiple=True) as message:
        # If the grade is less than 80, add a message about getting tutoring
        with grade.value < 80:
            message.add(concat("Consider tutoring for ", course.name))

        # If the grade is less than 70, add a message about failing the course
        with grade.value < 70:
            message.add(concat("Failing ", course.name))

    response = select(student.name, alias(message, "message"))

# The message variable can point to multiple values. Since Bob's grade in math
# is less than 80 and also less than 70, he gets both messages.
print(response.results)
#     name                        message
# 0  Alice  Consider tutoring for History
# 1    Bob     Consider tutoring for Math
# 2    Bob                   Failing Math

When multiple=True is passed to model.match(), every matching model.case() block is applied. As a consequence, you can use model.match() as a substitute for Python’s or operator, which, as explained in A Note About Logical Operators, is not supported in RAI query-builder syntax.

For example, the following query gets students whose name is Alice or whose grade in history is at least 80:

#with model.query() as select:
    with model.match(multiple=True) as student:

        # Get students whose name is Alice.
        with model.case():
            student.add(Student(name="Alice"))

        # Get students whose grade in history is at least 80.
        with model.case():
            s = Student()
            # model.found() is used to check for the existence of conditions
            # without filtering properties.
            with model.found():
                s.grades.course.name == "History"
                s.grades.value > 80
            student.add(s)

    # Select the name of matching students.
    response = select(student.name)

print(response.results)
#     name
# 0  Alice
# 1    Bob

Calculating Values#

Python’s arithmetic operators, as well as functions from relationalai.std.math, can be used to do arithmetic and perform calculations with object properties and other variables:

#import relationalai as rai
from relationalai.std.math import sqrt


# =====
# SETUP
# =====

model = rai.Model("MyModel")
Person = model.Type("Person")

with model.rule():
    alice = Person.add(id=1).set(name="Alice", age=10)
    bob = Person.add(id=2).set(name="Bob", age=30)
    carol = Person.add(id=3).set(name="Carol")  # Carol has no age property.


# =======
# EXAMPLE
# =======

with model.query() as select:
    person = Person()
    value = sqrt(person.age + 10)
    response = select(person.name, value)

print(response.results)
#     name         v
# 0  Alice  4.472136
# 1    Bob  6.324555

When a property like person.age is used in an arithmetic expression, objects for which the property is not set are filtered out. This is why Carol is not included in the results of the preceding query.

Use InstanceProperty.or_() to set a default value in the expression for objects with missing properties:

#with model.query() as select:
    person = Person()
    value = sqrt(person.age.or_(0) + 10)  # If person.age is not set, use 0.
    response = select(person.name, value)

# Now Carol has a value.
print(response.results)
#     name         v
# 0  Alice  4.472136
# 1    Bob  6.324555
# 2  Carol  3.162278

All of Python’s arithmetic operators are supported:

Operator	Description
`+`	Addition
`-`	Subtraction
`*`	Multiplication
`/`	Division
`//`	Floor division
`%`	Modulus
`**`	Exponentiation

See relationalai.std.math for a list of available mathematical functions.

Using Aggregate Functions#

Aggregate functions like sum() and count() may be imported from relationalai.std.aggregates and used to compute aggregate values:

#import relationalai as rai
from relationalai.std import aggregates
from relationalai.std import alias


# =====
# SETUP
# =====

model = rai.Model("MyModel")
Salesperson = model.Type("Salesperson")
Sale = model.Type("Sale")

with model.rule():
    alice = Salesperson.add(name="Alice")
    alice.sales.extend([
        Sale.add(salesperson=alice, amount=100.00, region="North"),
        Sale.add(salesperson=alice, amount=200.00, region="North"),
    ])

    bob = Salesperson.add(name="Bob")
    bob.sales.extend([
        Sale.add(salesperson=bob, amount=100.00, region="North"),
        Sale.add(salesperson=bob, amount=200.00, region="South"),
    ])

    Salesperson.add(name="Carol")


# ========
# EXAMPLES
# ========

# Get the total amount of all sales.
with model.query() as select:
    sale = Sale()
    total_sales = aggregates.sum(sale, sale.amount)
    response = select(alias(total_sales, "total_sales"))

print(response.results)
#    total_sales
# 0        600.0

sum(sale, sale.amount) calculates the sum of the amount property over all sale objects. You can think of the aggregation as follows:

The arguments describe a table with columns for each argument.
The aggregation is performed over the column corresponding to the final argument.

So, sum(sale, sale.amount) sums over rows for each sale object, ensuring that each sale contributes to the total. Without the sale argument, the sum is calculated over the set of all values of sale.amount:

#with model.query() as select:
    sale = Sale()
    total_sales = aggregates.sum(sale.amount)
    response = select(alias(total_sales, "total_sales"))

# Although there are two sales with amount 100.0 and two sales with amount 200.0,
# the sum is calculated over the set {100.0, 200.0}.
print(response.results)
#    total_sales
# 0        300.0

Every aggregation function has a per parameter that allows you to group the aggregation by a set of values:

## Get the total sales for each salesperson.
with model.query() as select:
    salesperson = Salesperson()
    sale = salesperson.sales
    total_sales = aggregates.sum(sale, sale.amount, per=[salesperson])
    response = select(salesperson.name, alias(total_sales, "total_sales"))

print(response.results)
#     name  total_sales
# 0  Alice        300.0
# 1    Bob        300.0


# Get the total sales for each salesperson in each region.
with model.query() as select:
    salesperson = Salesperson()
    sale = salesperson.sales
    region = sale.region
    total_sales = aggregates.sum(sale, sale.amount, per=[salesperson, region])
    response = select.distinct(salesperson.name, region, alias(total_sales, "total_sales"))
    # NOTE: select.distinct() is used since region is keyed by sale. select()
    # returns a row for each person and each one of their sales, producing two
    # rows for Alice, who has two sales in the North region.

print(response.results)
#     name region  total_sales
# 0  Alice  North        300.0
# 1    Bob  North        100.0
# 2    Bob  South        200.0

You may use multiple aggregations in the same rule or query. For example, you can calculate each salesperson’s percentage of total sales by summing their sales and dividing by the total:

#with model.query() as select:
    salesperson = Salesperson()
    sale = salesperson.sales
    sales_per_person = aggregates.sum(sale, sale.amount, per=[salesperson])
    total_sales = aggregates.sum(sale, sale.amount)
    pct_total = sales_per_person / total_sales
    response = select(salesperson.name, alias(pct_total, "pct_total_sales"))

print(response.results)
#     name  pct_total_sales
# 0  Alice              0.5
# 1    Bob              0.5

When properties are passed to an aggregate function, objects for which the property is not set are filtered out. This is why Carol is not included in the results of the preceding queries.

To set a default aggregate value for objects that don’t have a property set, use a Model.match() block:

#with model.query() as select:
    salesperson = Salesperson()
    with model.match() as pct_total:
        with model.case():
            sale = salesperson.sales
            total_sales = aggregates.sum(sale, sale.amount)
            sales_per_person = aggregates.sum(sale, sale.amount, per=[salesperson])
            pct_total.add(sales_per_person / total_sales)
        # Set pct_total to 0.0 for salespeople with no sales.
        with model.case():
            pct_total.add(0.0)
    response = select(salesperson.name, alias(pct_total, "pct_total_sales"))

print(response.results)
#     name  pct_total_sales
# 0  Alice              0.5
# 1    Bob              0.5
# 2  Carol              0.0

Filters and aggregations are applied in the order that they are defined in the query. This means that filters defined after an aggregation function is called do not affect the aggregation.

The following aggregate functions are available:

Function	Description
`avg()`	Compute the average (mean) of a property or other variable.
`count()`	Count objects, properties, and other values.
`max()`	Find the maximum value of a property or other variable.
`min()`	Find the minimum value of a property or other variable.
`rank_asc()`	Rank objects, properties, and other values in ascending order.
`rank_desc()`	Rank objects, properties, and other values in descending order.
`sum()`	Compute the sum of a property or other variable.

See the reference documentation for more information.

Dealing With Null Values#

Properties with missing values are displayed as null values in query results:

#import relationalai as rai

model = rai.Model("MyModel")
Person = model.Type("Person")

with model.rule():
    Person.add(id=1).set(name="Alice", age=20)
    Person.add(id=2).set(name="Bob")  # Bob's age is not set.

# pandas query format
with model.query() as select:
    person = Person()
    response = select(person.name, person.age)

print(response.results)
#     name   age
# 0  Alice  20.0
# 1    Bob   NaN


# Snowpark query format
with model.query(format="snowpark") as select:
    person = Person()
    response = select(person.name, person.age)

response.results.show()
# ------------------
# |"NAME"  |"AGE"  |
# ------------------
# |Bob     |NULL   |
# |Alice   |20     |
# ------------------

For queries that return pandas DataFrames, null values are displayed as NaN (Not a Number), NaT (Not a Time), or <NA>, depending on the data type of the result column. See the pandas documentation for details. In queries that return SnowPark DataFrames, the value is NULL.

IMPORTANT

Although NaN or NULL may be displayed in query results, properties do not actually point to NaN, NULL, or even None values. Properties not set for an object have no value.

Use InstanceProperty.or_() to provide a default value for objects with missing properties:

#with model.query() as select:
    person = Person()
    response = select(person.name, person.age.or_(-1))

print(response.results)
#     name  age
# 0  Alice   20
# 1    Bob   -1

To filter out null values, use InstanceProperty.has_value() to assert that a property is set:

#with model.query() as select:
    person = Person()
    person.age.has_value()  # Filter out objects where age is not set.
    response = select(person.name, person.age)

# Only people with an age property are returned in the results.
print(response.results)
#     name  age
# 0  Alice   20

Conversely, you can filter out objects where a property is set by calling .has_value() inside a model.not_found() block:

#with model.query() as select:
    person = Person()
    # Filter out people with an age property.
    with model.not_found():
        person.age.has_value()
    response = select(person.name, person.age)

# Only people with no age property are returned in the results.
print(response.results)
#   name  age
# 0  Bob  NaN

When properties are used in an aggregation, arithmetic, or any other expression, objects without the property are filtered out:

#with model.query() as select:
    person = Person()
    double_age = 2 * person.age
    response = select(person.name, double_age)

# People without an age property are filtered out.
print(response.results)
#     name  result
# 0  Alice      40

Again, .or_() is useful in these situations to provide default values:

#with model.query() as select:
    person = Person()
    double_age = 2 * person.age.or_(0)  # If age is not set, use 0.
    response = select(person.name, double_age)

print(response.results)
#     name  result
# 0  Alice      40
# 1    Bob       0

Writing Results to Snowflake#

Results of queries can be written to a Snowflake table by setting the query’s format parameter to "snowpark" and using Snowpark DataFrame methods to write the results to a table:

#import relationalai as rai


# =====
# SETUP
# =====

model = rai.Model("MyModel")
Person = model.Type("Person")

with model.rule():
    Person.add(name="Alice", age=20)
    Person.add(name="Bob", age=25)
    Person.add(name="Carol", age=30)


# =======
# EXAMPLE
# =======

# Use format="snowpark" to get results as a Snowpark DataFrame.
with model.query(format="snowpark") as select:
    person = Person()
    response = select(person.name, person.age)

# Write the results to a Snowflake table using Snowpark DataFrame methods.
# Replace "<db>.<schema>.<table>" with the fully-qualified name of your table.
response.results.write.save_as_table("<db>.<schema>.<table>")

When the query’s format parameter is set to "snowpark", results are written to a temporary table in the RAI Native App’s Snowflake database. The query returns a Snowpark DataFrame that references the temporary table.

You may set format="snowpark" in the Model constructor to change the default format for all queries:

## Set the default format for all queries against the model to "snowpark".
model = rai.Model("MyModel", format="snowpark")

Refer to the Snowpark documentation for more information on working with Snowpark DataFrames.

Exporting SQL Stored Procedures#

Queries can be parameterized and exported as SQL stored procedures. Snowflake SQL users can use the stored procedures to invoke the query in their Snowflake environment.

To export a query, you write the query as a Python function, instead of using the model.query() context manager, and wrap the function with the Model.export() decorator:

#from typing import Tuple

import relationalai as rai


# =====
# SETUP
# =====

model = rai.Model("people")
Person = model.Type("Person")

with model.rule():
    alice = Person.add(id=1).set(name="Alice", age=15)
    bob = Person.add(id=2).set(name="Bob", age=20)
    carol = Person.add(id=3).set(name="Carol", age=25)

    alice.friends.add(carol)
    bob.friends.add(carol)
    carol.friends.extend([alice, bob])


# =======
# EXAMPLE
# =======

# Define a function to export as a stored procedure. Replace <db> and <schema>
# with the names of your Snowflake database and schema. NOTE: Type hints for both
# arguments and return values are required.
@model.export("<db>.<schema>")
def get_friends_of_person(id: int) -> Tuple[str, int]:
    person = Person(id=id)
    friend = person.friends
    return friend.name, friend.age

# The stored procedure can now be called in Snowflake SQL:
# CALL <db>.<schema>.get_friends_of_person(1);

IMPORTANT

Functions decorated with @model.export() cannot be called directly in Python. They may only be invoked as a stored procedure in Snowflake SQL.

You can convert a query into a SQL stored procedure by:

Replacing with model.query() with def my_func(...).
Adding a @model.export() decorator to the function definition.
Replacing select(...) with return ....

The function is required to have type hints for both function arguments and return values. See Model.export() for supported types.

Exported functions may take advantage of the full range of RAI query-builder syntax, including advanced features like graph analytics.

Summary and Next Steps#

In this guide, you learned the basics of writing rules and queries in RAI’s query-builder syntax, including:

Filtering objects by type and property value
Expressing if and if-else logic using model.case() and model.match().
Calculating values with arithmetic operators and aggregate functions.
Writing query results to Snowflake.
Exporting queries as SQL stored procedures.

To learn more:

Check out the reference documentation to learn more about the query-builder syntax and the standard library functions available in RAI.
Browse our library of example notebooks to see real-world use cases solved with RAI.
Read the RAI Python to SQL comparison guide.

Basic Functionality#

Table of Contents#

Inspecting Types and Properties#

Filtering Objects by Type#

Aliasing Column Names#

Filtering Objects by Property Value#

A Note About Logical Operators#

Filtering By Existence of Conditions#

Expressing if Using model.case()#

Expressing if-else Using model.match()#

Calculating Values#

Using Aggregate Functions#

Dealing With Null Values#

Writing Results to Snowflake#

Exporting SQL Stored Procedures#

Summary and Next Steps#

Expressing `if` Using `model.case()`#

Expressing `if-else` Using `model.match()`#