SQL Library Reference

Reference manual for the SQL Library for Snowflake.

The SQL Library for Snowflake comprises a collection of SQL functions and procedures. Some of them are overloaded, providing different signatures depending on your needs.

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.

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.

Library Overview

The library is organized into functions and procedures: