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
Usingmodel.case()
- Expressing
if-else
Usingmodel.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
.
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);
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()
withdef my_func(...)
. - Adding a
@model.export()
decorator to the function definition. - Replacing
select(...)
withreturn ...
.
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
andif-else
logic usingmodel.case()
andmodel.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.
- See how RAI compares to SQL in our RAI for SQL Users guide.