Working With the RAI Integration Services

This guide discusses basic aspects about working with the RAI Integration Services for Snowflake.

Introduction

The RAI Integration Services are a collection of features designed to extend your Snowflake environments. They enable RAI’s advanced modeling and querying capabilities to analyze your Snowflake data in new ways, including graph analytics.

To begin working with them, get familiar with the main components, the resources you’ll need, and common workloads and operations.

Components

This section presents the main components of the RAI Integration Services for Snowflake.

RAI Integration

A RAI integration is a secure connection between a Snowflake account and a RAI account, and is required to perform almost any task within the RAI Integration Services.

A RAI integration can have multiple RAI database links. See RAI Integration for more details.

RAI Database Link

A RAI database link is located in a Snowflake database and schema, where it installs the SQL Library for Snowflake. It is identified with the form <database>.<schema>, and by default, the schema is RAI. It belongs to only one RAI integration and you can use it to communicate with and manage your RAI resources.

These are the main elements you can find within a RAI database link:

See RAI Database Link for more details.

SQL Library for Snowflake

The SQL Library for Snowflake provides all the functionality required to work with the RAI Integration Services. You can access the core functionality, perform graph analytics, prepare your data with schema mapping tools and query a RAI database.

It comprises a collection of SQL user-defined functions and procedures provided by the RAI Integration Services:

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

Using the SQL Library for Snowflake

For procedures, you can use the simple or fully qualified object name of an object:

CALL RAI.create_data_stream('sf_table');
-- Or
CALL RAI.create_data_stream('sf_db.sf_schema.sf_table');

For user-defined functions (UDFs), you must always use fully qualified names. Snowflake functions can’t determine the Snowflake context for UDFs.

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

See Referring to SQL Objects for more details.

Updating the SQL Library for Snowflake

To update the library, you must update your working RAI database link. See SQL Library for Snowflake for more details.

Resources

This section introduces all the resources, including both Snowflake and RAI platforms, required to work with the RAI Integration Services.

See Integration Management to learn more about how to configure and manage RAI resources.

Snowflake Resources

These are the Snowflake resources you’ll need to work with the RAI Integration Services:

The project database can be the same as the integration database but it doesn’t have to be. The integration database provides the SQL Library for Snowflake within the schema RAI.

Here’s an example showing how to work within the project context my_project_db.project_1. It uses the create_data_stream procedure from the SQL Library for Snowflake of the RAI database link, which is located in integration_db.RAI.

-- Use the project database.
USE DATABASE my_project_db;
USE SCHEMA project_1;
 
-- Call a procedure from the integration library.
CALL integration_db.RAI.create_data_stream('my_project1_data');

RAI Resources

These are the resources you’ll need from RAI:

You can manage these resources through the SQL Library for Snowflake. For example, you can create and use a RAI database and engine as follows:

SELECT RAI.create_rai_engine('my_rai_engine', 'S');
SELECT RAI.create_rai_database('my_rai_db', 'S');
 
CALL RAI.use_rai_engine('my_rai_engine');
CALL RAI.use_rai_database('my_rai_db');

It is best practice to set up a RAI context. This allows you to set your working RAI database and engine, without the need to specify them each time. See Setting Up Your RAI Context for more details.

Workloads

This section discusses the main workloads and common tasks supported by the RAI Integration Services for Snowflake.

Getting Started

In order for you to use the RAI Integration Services, your account administrator must set up all the necessary resources and share this information with you.

To get started, you need to know the following:

During setup, the RAI database link is linked to a RAI integration, which it will use for all communication with RAI.

If you are an account administrator, see the Quick Start for Snowflake Administrators for more details on how to set up the RAI Integration Services.

Core

Once you have all the necessary resources, you can start working with the RAI Integration Services. A good place to begin is the Quick Start for Snowflake Users.

Managing Your RAI Resources

You can manage your RAI resources using the following functions and procedures:

Setting Up Your 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. This is analogous to the USE (opens in a new tab) command in SQL.

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

CALL RAI.use_rai_database('my_rai_db');
CALL RAI.use_rai_engine('my_rai_engine');

With the RAI context set, creating a graph requires only the SQL table and the desired graph name:

SELECT RAI.create_graph('user_graph', 'my_edge_stream');

If you don’t set a RAI context, you’ll need to specify the RAI engine:

SELECT RAI.create_graph('user_graph', 'my_edge_stream', 'my_rai_engine');

These are the related functions and procedures:

To check which RAI resource is selected, run the current_rai_database/engine command:

SELECT RAI.current_rai_database();
/*+----------------------------+
  | RAI.CURRENT_RAI_DATABASE() |
  +----------------------------+
  | my_rai_db                  |
  +----------------------------+  */

RAI Data Streams

A RAI data stream synchronizes data between a Snowflake database and a RAI database, associating a Snowflake object with a RAI base relation.

It is identified by a fully qualified name with the form <database>.<schema>.<object>, and associated with a RAI database link.

These are the related functions and procedures:

See RAI Data Streams for more details.

Graph Analytics

You can perform graph analytics within Snowflake with RelationalAI’s SQL Library for Snowflake.

Creating Graphs

Here’s an example showing how to create a graph from data stored in the my_sf_edges SQL table:

CALL RAI.create_graph('my_graph', 'my_sf_edges');

These are the functions and procedures you can use to manage your graphs:

See Managing Graphs for more details.

Executing Graph Algorithms

Here’s an example showing how to execute graph algorithms, namely num_nodes, over my_graph:

SELECT RAI.num_nodes('my_graph')

The SQL Graph Library for Snowflake implements a wide range of functions for common graph analytics tasks, including functions for:

• Basic operations.
• Paths.
• Centrality measures.
• Similarity and link prediction.
• Community detection.

Use list_graph_algorithms to list all the available graph algorithms within the SQL Library for Snowflake.

See Working With Algorithms for more details.

Schema Mapping

Before you can create a graph and run graph analytics, you need to prepare your data with schema mapping tools. These tools associate the unique graph node identifiers with the primary keys in your SQL table.

Defining Entities

Defining entities and their keys ensures that each node ID is unique even if you work with multiple SQL tables.

Here’s an example showing how to create and list entities:

-- Create an entity.
CALL RAI.CREATE_ENTITY('entity_name', 'entity_keys');
 
-- List the entities.
SELECT * FROM TABLE(RAI.LIST_ENTITIES());

These are the related functions and procedures:

Mapping Objects and Node Identifiers

You can access the mapping between keys and IDs using lookup tables.

Here’s an example showing how to map objects to node IDs:

-- Define an edge set.
CREATE VIEW Edge(src, dst) AS (
    SELECT RAI.NODE('entity1_name', 'entity1_keys') as src, RAI.NODE('entity2_name', 'entity2_keys') as dst
    FROM my_data
);

These are the related functions and procedures:

See Schema Mapping for more details and follow the step-by-step Schema Mapping Tutorial to get started.

Querying a RAI Database

The SQL Library for Snowflake allows you to query a RAI database from Snowflake. These are the related functions:

A query transaction allows you to:

• Retrieve information from the RAI database.
• Add, delete, or modify persisted data, which are stored in RAI base relations.
• Store RAI data in Snowflake objects.

See Database Transactions to learn more about RAI transactions.

To execute a query against a RAI database from Snowflake, you need to write the query using Rel. Rel is a modern logic-based modeling language.

For instance, say you have successfully created a RAI data stream between your Snowflake table my_sf_table and your RAI base relation my_rai_table. You can query this relation as follows:

SELECT RAI.exec('def output = my_rai_table');

The Rel query def output = my_rai_table is passed as an argument to exec as the query body.

You can also write more complex Rel queries involving the declarations of multiple relations.

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

See exec for more details.

Storing RAI Data in Snowflake

To insert RAI data into Snowflake objects, you can use the exec_into function. It essentially operates in the same way as exec, but stores the query output as a Snowflake object.

You can think of exec_into as behaving in the opposite way to a RAI data stream. It sends data from RAI to Snowflake.

For example, say you want to store your RAI base relation my_rai_relation as a Snowflake table my_sf_table. You can do that as follows:

SELECT RAI.exec_into(
    'my_rai_db',
    'my_rai_engine',
    'my_sf_wh',
    'my_sf_db.my_sf_schema.my_sf_table',
    'def output = my_rai_relation'
);

In this case, the exec_into function signature requires that you specify your RAI database and engine, and your Snowflake warehouse. The target Snowflake object must use a fully qualified name.

See exec_into for more details.

Query Timeout Limit

By default, Snowflake’s timeout limit for queries is set to 10 minutes. This may not be enough for long-running queries like executing graph algorithms on larger graphs.

Loading Models in a RAI Database

The SQL Library for Snowflake allows you to load a model into a RAI database from Snowflake.

Rel models are useful for persisting logic in a RAI database that can be reused later in queries or as building blocks for more complex applications. More specifically, a Rel model is a collection of Rel declarations that is loaded into a RAI database.

The most common is the declaration of derived relations, which behave like views in the SQL world but are much more powerful.

The relations defined in a Rel model are available to other models and to all queries executed in the RAI database.

See Working With Models to learn more.

These are the related functions:

For instance, say you want to load a model into your working RAI database from a specific Azure URI. You can do that as follows:

CALL RAI.use_rai_database('my_rai_db');
CALL RAI.use_rai_engine('my_rai_engine');
 
SELECT RAI.load_model_query(
    'my_rai_model',
    'azure://<account_name>.blob.core.windows.net/container/model.rel'
);

Here, the RAI context is used to set all RAI resources.

If your model is not very complex, you can load it directly as part of the query:

CALL RAI.use_rai_database('my_rai_db');
CALL RAI.use_rai_engine('my_rai_engine');
 
SELECT RAI.load_model_code(
    'my_rai_model',
    'def myrange(x) = range(1, 10, 1, x)'
);

The Rel model def myrange(x) = range(1, 10, 1, x) is passed as an argument to load_model_code.

As when querying a RAI database, you need to write the model using Rel.

Summary

This guide presents the RAI and Snowflake components and resources of the RAI Integration Services for Snowflake. The components include the RAI Integration, RAI database and the SQL library for Snowflake. In addition to the core workload, the RAI Integration Services support graph analytics, schema mapping and querying a RAI database.

See Also

See Graph Analytics, and Schema Mapping for more details on the workloads supported by the RAI Integration Services.