Model.export()#

#Model.export(schema: str) -> Callable

Exports a Python function as a Snowflake stored procedure. Must be used as a decorator for the function to be exported. The exported stored procedure has the same name as the function and can be called from Snowflake SQL.

Parameters#

Returns#

A Callable object representing the decorated function. Note, however, that the returned function cannot be used directly in Python. Only the stored procedure created in Snowflake may be called.

Example#

Use export() to export a Python function as a Snowflake stored procedure.

The contents of the decorated function must be written in the RelationalAI query-builder syntax. You can think of the function as containing the contents of a Model.query() block with the select() function replaced by a return statement.

There are two types of stored procedures that can be created using export():

1. Create a stored procedure that returns a table.

   Provide type hints for both function parameters and return values to export a stored procedure that returns a table of results:

   #import relationalai as rai
   
   # Create a model named "MyModel".
   model = rai.Model("MyModel")
   
   # Create a Person type.
   Person = model.Type("Person")
   
   # Add some people to the model.
   with model.rule():
       alice = Person.add(id=1).set(name="Alice", age=21)
       bob = Person.add(id=2).set(name="Bob", age=22)
       carol = Person.add(id=3).set(name="Carol", age=23)
       # Create a friends property and assign some friends to each person.
       alice.friends.extend([bob, carol])
       bob.friends.extend([alice])
       carol.friends.extend([alice])
   
   # Define a function to export as a stored procedure. Type hints for function
   # parameters are required. When type hints are provided, as in this example,
   # the resulting stored procedure returns a table of results.
   @model.export("sandbox.public")
   def get_friends_of_person(id: int) -> int:
       person = Person()
       person.id == id
       return person.friends.id
   
   # The stored procedure can now be called in Snowflake SQL:
   rai.Provider().sql("CALL sandbox.public.get_friends_of_person(1);")
   

2. Create a stored procedure that saves results to a table.

   If the decorated function only has type hints for its parameters and no return type hint, the resulting stored procedure will write results to a specified table. The table name must be provided when the stored procedure is called in Snowflake SQL:

   #import relationalai as rai
   
   # Create a model named "MyModel".
   model = rai.Model("MyModel")
   
   # Create a Person type.
   Person = model.Type("Person")
   
   # Add some people to the model.
   with model.rule():
       alice = Person.add(id=1).set(name="Alice", age=21)
       bob = Person.add(id=2).set(name="Bob", age=22)
       carol = Person.add(id=3).set(name="Carol", age=23)
       # Create a friends property and assign some friends to each person.
       alice.friends.extend([bob, carol])
       bob.friends.extend([alice])
       carol.friends.extend([alice])
   
   # Define a function to export as a stored procedure. Type hints for function
   # parameters are required. When no return type hint is provided, the resulting
   # stored procedure writes results to a specified table.
   @model.export("sandbox.public")
   def get_friends_of_person(id: int):
       person = Person()
       person.id == id
       return person.friends.id, person.friends.name
   
    # The stored procedure can now be called in Snowflake SQL. Note that the
    # fully-qualified name of the table where the results will be saved must
    # be provided as an argument.
    rai.Provider().sql("CALL sandbox.public.get_friends_of_person(1, 'sandbox.public.friends_of_alice');")
   
    # The result table can then be queried in Snowflake SQL.
    rai.Provider().sql("SELECT * FROM sandbox.public.friends_of_alice;")
   

Supported Type Hints#

For both types of stored procedures, the decorated functions type hints must be on of the following supported types:

Exported Stored Procedure Signature#

The signature of the stored procedure created by export() is determined by the type hints of the decorated function and whether or not return type hints are provided:

1. With return type hints: If the decorated function has return type hints, the stored procedure signature is the same as the return type hints of the function, with an additional optional ENGINE parameter for specifying a specific RAI engine to use when executing the stored procedure.

   For example, the get_friends_of_person function in the first example above has a return type hint of int, so the stored procedure signature is:

   #CREATE OR REPLACE PROCEDURE sandbox.public.get_friends_of_person(id INT, ENGINE VARCHAR)
   RETURNS TABLE (id INT)
   

2. Without return type hints: If the decorated function does not have return type hints, the stored procedure signature is the same as the function’s parameter type hints, with two additional parameters:

   • A required SAVE_AS_TABLE parameter for specifying the fully-qualified name of the table where the results will be saved.
   • An optional ENGINE parameter for specifying a specific RAI engine to use when executing the stored procedure.

   For example, the get_friends_of_person function in the second example above does not have a return type hint, so the stored procedure signature is:

   #CREATE OR REPLACE PROCEDURE sandbox.public.get_friends_of_person(id INT, SAVE_AS_TABLE VARCHAR, ENGINE VARCHAR)
   RETURNS VARCHAR
   

For more details on Snowflake stored procedures, refer to the Snowflake documentation.

Engine Selection#

Stored procedures created using export() contain a copy of the query generated by the decorated function. When the stored procedure is called in Snowflake SQL, the query is executed using a RelationalAI engine. Which engine is used to execute the query is determined in the following order:

1. The engine passed to the procedures ENGINE parameter, if present.
2. The first available engine created by the user calling the stored procedure, if there are any.
3. The engine specified in the user’s config at export time.

See Also#

• Model.query()
• Model.rule()