Overview

In order to use the SQL functions and procedures that come with the RAI Integration Services for Snowflake, you need to cover a few basics. These include setting and using a RAI context, understanding the difference between user-defined functions and stored procedures, and knowing how to regularly update the SQL Library for Snowflake.

RAI Context

Some functions and procedures need to know which RAI resources should be used. To avoid specifying them each time, you can set a RAI context. It is analogous to the USE (opens in a new tab) command in SQL.

Here’s an example showing how to set rai_db as your working RAI database:

CALL RAI.use_rai_database('rai_db');

The following RAI resources can be set as context:

You can check your RAI context by executing the following query:

SELECT RAI.current_rai_database();
SELECT RAI.current_rai_engine();

Using the RAI Context

Once the context is set, the selected resources act like default arguments for functions and procedures. Here is an example showing how to perform a query through exec, specifying all the RAI resources:

SELECT RAI.exec('rai_db', 'rai_engine', 'def output = {1; 2; 3}', null, true)

With the RAI context set, the same query can be executed by providing the query text:

SELECT RAI.exec('def output = {1; 2; 3}')

Functions and Procedures

Snowflake provides user-defined functions (opens in a new tab) (UDFs) and stored procedures (opens in a new tab) to enable users to perform operations that are not available through the built-in Snowflake functionality. The SQL Library for Snowflake includes a collection of UDFs and stored procedures.

UDFs Versus Stored Procedures

User-defined functions are used to calculate and return a value and can be used with SELECT queries.

Stored procedures generally perform administrative operations by executing SQL statements. The body of a stored procedure is allowed, but not required, to explicitly return a value. See Choosing Whether to Write a Stored Procedure or a User-Defined Function (opens in a new tab) for more details.

Referring to SQL Objects

In Snowflake, SQL objects, such as tables or views, are identified by a fully qualified object name (opens in a new tab) with the form <database>.<schema>.<object>.

For instance, to create a RAI data stream for the SQL table sf_db.sf_schema.sf_table you need to use the procedure create_data_stream. Because it is a procedure, you have the choice to specify the simple or the fully qualified name.

CALL RAI.create_data_stream('sf_table');
-- or
CALL RAI.create_data_stream('sf.db.sf_schema.sf_table');

Now, for retrieving information related to this RAI data stream, you use the UDF get_data_stream. Because it is a UDF, you need to specify the fully qualified name as follows:

SELECT RAI.get_data_stream('sf_db.sf_schema.sf_table');

Scalar Versus Tabular UDFs

There are two main ways in which user-defined functions return results. The return value is either a scalar value or a table:

1. Scalar functions (opens in a new tab) return one output row for each input row. The returned row consists of a single value.

2. Tabular functions (opens in a new tab) return a tabular value for each input row. The returned table consists of multiple columns and potentially multiple rows.

To call a scalar UDF, you can use:

SELECT RAI.average_degree('my_graph');

To call a tabular UDF, you need to wrap the call to a SQL table function (opens in a new tab):

SELECT * FROM TABLE(RAI.degree('mygraph');

Calling a tabular UDF as a scalar one returns the following error:

SELECT RAI.degree('mygraph');

For more details, see Calling a UDF (opens in a new tab).

Updating the Library

The SQL Library for Snowflake is installed in a specific Snowflake database and schema. This is called the database link.

To update the library, you must manually update your working database link. See Updating a Database Link for more details.