Skip to content
Working With the Integration

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:

ElementWorkloadDescription
RAI data streamsCoreSynchronizes your data between Snowflake and RAI, associating a Snowflake object with a RAI base relation.
GraphsGraph analyticsCreated from a RAI data stream that represents the graph’s edge set and stored in a RAI database.
Lookup tablesSchema mappingHolds the association between an entity’s ID and its identifying information.
EntitiesSchema mappingRepresents real-world elements that can be uniquely identified independently of their properties or other relationships.

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:

TypeDescription
Scalar functionsCalculate and return a single value. Can be used with a SELECT statement.
Tabular functionsCalculate and return a tabular value. Can be used with a SELECT statement.
Stored proceduresMostly used for administrative tasks. May return data. They are allowed, but not required, to explicitly return a value.

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.

💡

Use fully qualified names when using functions of the SQL Library for Snowflake.

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.

🔎

RAI resources are case-sensitive. Snowflake resources are not.

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:

ResourceDescription
Integration database (opens in a new tab)Provides the RAI Integration Services for Snowflake.
Project database (opens in a new tab)Data storage holding your project data.
Warehouse (opens in a new tab)Snowflake’s computation resource.

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:

ResourceDescription
RAI engineComputational heart of the RKGS. Analogous to Snowflake warehouses.
RAI databaseCore element of the storage layer. Contains RAI relations and graphs.

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:

ResourceExample value
Snowflake warehouse<sf_wh>
Snowflake database<sf_db>
RAI engine<rai_engine>
RAI database<rai_db>
RAI database link<sf_db>.<sf_schema>

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

🔎

As a user, you don’t need to know the specific RAI integration. Knowing the location of the RAI database link is sufficient.

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:

ActionDatabaseEngine
Createcreate_rai_databasecreate_rai_engine
Get informationget_rai_databaseget_rai_engine
Useuse_rai_databaseuse_rai_engine
Deletedelete_rai_databasedelete_rai_engine

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');

Make sure that the RAI resources exist before you set them.

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:

ActionDatabaseEngine
Set upuse_rai_databaseuse_rai_engine
Monitorcurrent_rai_databasecurrent_rai_engine

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:

ActionRAI data stream
Createcreate_data_stream
Get informationget_data_stream
Get statusget_data_stream_status
Listlist_data_streams
Deletedelete_data_stream

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:

ActionGraphs
Createcreate_graph
Verifyis_graph_created
Get informationget_graph
Listlist_graphs
Updateupdate_graph
Deletedelete_graph
🔎

You can create multiple graphs from the same RAI data stream. All graphs created with the same schema must have unique names. Graph names are case sensitive.

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:

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

🔎

Nodes are identified by unique integer IDs. Use the Schema Mapping tools to establish the node identifier as a unique integer value.

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());

The RAI Integration Services require that each entity has a unique key, which may consist of multiple attributes.

These are the related functions and procedures:

ActionEntity
Createcreate_entity
Listlist_entities
Removedrop_entity
Remove alldrop_all_entities

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:

ActionLookup
Add an entitycreate_lookup
Listlist_lookups
Map an object to a node IDnode
Map a node ID to an objectlookup
Rebuildrebuild_lookup_table
Remove some entriesdrop_lookup
Remove all entriesdrop_all_lookups

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:

FunctionDescription
execExecutes a query against a RAI database.
exec_intoExecutes a query against a RAI database and stores the query result in a Snowflake table.

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.

💡

To execute a query against a RAI database, you need to write it using Rel.

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.

If you need to increase your Snowflake account timeout limit, please get in touch with your account administrator.

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.

🔎

Rel models are used to define derived relations — think views — to enhance your data model with logic and reasoning.

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:

FunctionDescription
load_modelLoads a model into a RAI database, given a name and file path to the Rel code.
load_model_codeLoads a model into a RAI database, given a name and Rel code within the query.
load_model_queryOperates as load_model and requires a RAI context.

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.

Was this doc helpful?