SQL Library Reference for Snowflake: Procedures

Reference for the SQL procedures that come with the RAI Integration Services for Snowflake.

create_data_stream

create_data_stream(datasource)
create_data_stream(datasource, rai_db)
create_data_stream(datasource, rai_db, rai_baserelation)

Create a RAI data stream. A RAI data stream is identified by the fully qualified schema name (opens in a new tab) of a SQL object, such as a table or view, in the form <database>.<schema>.<object>.

Parameters

Parameter	Type	Description
`datasource`	Varchar (opens in a new tab)	Snowflake data source.
`rai_db`	Varchar (opens in a new tab)	Target RAI database. If not specified, the default database is the one selected with `use_rai_database`.
`rai_baserelation`	Varchar (opens in a new tab)	Target RAI base relation. If not specified, the default base relation name is the Snowflake `datasource` name.

Output

Type	Description
Variant (opens in a new tab)	A JSON object representing the RAI data stream information.

{
    "account": "******",
    "createdBy": "******",
    "createdOn": "2023-05-31T11:24:09.710Z",
    "dbLink": "sf_db.my_schema",
    "id": "******",
    "integration": "myintegration",
    "name": "datasource",
    "rai": {
        "database": "rai_db",
        "relation": "rai_baserelation"
    },
    "snowflake": {
        "database": "sf_db",
        "object": "sf_db.my_schema.datasource",
        "schema": "my_schema"
    },
    "state": "CREATED"
}

Explanation

The procedure create_data_stream generates a RAI data stream to synchronize Snowflake objects with RAI. Once you have created the stream, the Snowflake data source is synced with the corresponding RAI base relation.

Note that only Snowflake tables and views can be linked with a data stream.

⚠

A Snowflake data source can only be linked to one RAI base relation.

Examples

CALL RAI.create_data_stream('sf_table', 'rai_db', 'rai_table');

See Also

delete_data_stream.

create_entity

create_entity(entity_name, entity_keys)

Declare an entity.

Parameters

Parameter	Type	Description
`entity_name`	Varchar (opens in a new tab)	The name of the entity.
`entity_key`	Varchar (opens in a new tab) or Array (opens in a new tab)	The names of entity key attributes.

Output

Type	Description
Object (opens in a new tab)	The status of the creation request. A dictionary with at least two fields: boolean `success` and an array of strings `messages`.

Explanation

An entity declaration consists of an entity name and key attributes of the entity. An entity is used to describe a type of “thing”, and its key attributes allow you to distinguish between any two things of the same type.

⚠

An entity may be defined only once: The same entity cannot have different key definitions.

An entity will be declared implicitly by create_lookup if the entity name used in the call of create_lookup has not been declared explicitly with create_entity. Attempting to use create_entity to declare an entity after it has been used in create_lookup will likely result in an error. It is advisable to declare all entities with create_entity before using them to populate the lookup table with create_lookup.

The name of the entity must be a valid Snowflake object identifier: It must begin with a letter or _ and can consist only of letters, digits, and the characters _ and $. The list of the key attribute names must not have repetitions.

⚠

The attribute name type is reserved and cannot be used in entity_key.

⚠

Concurrent use of create_entity is not recommended and may result in a corrupted system state.

Examples

   CALL RAI.create_entity('Client', ['first_name', 'last_name']);
/* +------------------------------------------------------------------+
   | CREATE_ENTITY                                                    |
   |------------------------------------------------------------------|
   | {                                                                |
   |   "messages": [                                                  |
   |     "Added entity Client with key [first_name, last_name]."      |
   |   ],                                                             |
   |   "success": true                                                |
   | }                                                                |
   +------------------------------------------------------------------+ */

   CALL RAI.create_entity('Product', 'name');
/* +--------------------------------------------------+
   | CREATE_ENTITY                                    |
   |--------------------------------------------------|
   | {                                                |
   |   "messages": [                                  |
   |     "Added entity Product with key [name]."      |
   |   ],                                             |
   |   "success": true                                |
   | }                                                |
   +--------------------------------------------------+ */

create_graph

create_graph(graph_name, edge_stream_name)
create_graph(graph_name, edge_stream_name, arguments)

Create a graph from the specified edge data stream.

Parameters

Parameter	Type	Description
`graph_name`	Varchar (opens in a new tab)	The name of the graph. Names are case-sensitive and must be unique.
`edge_stream_name`	Varchar (opens in a new tab)	The name of the data stream to use for the graph’s edges.
`arguments`	Object (opens in a new tab)	A JSON object containing additional arguments.

Supported Arguments

The following arguments may be specified in the arguments object:

Argument Name	Type	Required	Description
`rai_engine`	Varchar (opens in a new tab)	No	The name of the RAI engine to use to execute the algorithm. Required if no engine has been set in a RAI context.
`directed`	Boolean (opens in a new tab)	No	Whether or not the graph is directed. By default, `directed` is `false`.
`overwrite`	Boolean (opens in a new tab)	No	Whether or not the procedure should overwrite an existing graph with the same name. By default, `overwrite` is `false` and the procedure will fail if a graph with the provided name already exists.

Output

Type	Description
Variant (opens in a new tab)	A JSON object indicating whether or not the graph was created.

{
    "message": "Graph 'my_graph' created in RAI database 'my_db'.",
    "success": true
}

Explanation

The procedure create_graph creates a graph in RAI using the data as specified by the Snowflake edge data stream. The graph is stored in the same RAI database to which the data stream points.

For create_graph to work, the data stream must have been previously created. You can check the status of a data stream using the get_data_stream_status function.

Multiple graphs may be created from the same data stream. Graph names are case-sensitive and must be unique. To see all of the existing graphs, use the list_graphs function.

The create_graph procedure requires a RAI engine. You can either specify the engine in a RAI context or provide an engine name to the rai_engine argument. The next section contains concrete examples.

Examples

Create an undirected graph:

-- Set the RAI context.
CALL RAI.use_rai_database('my_rai_db');
CALL RAI.use_rai_engine('my_rai_engine');
 
-- Create an undirected graph with three nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');

Create a directed graph:

-- Set the RAI context.
CALL RAI.use_rai_database('my_rai_db');
CALL RAI.use_rai_engine('my_rai_engine');
 
-- Create a directed graph with three nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges', {'directed': true});

See Also

delete_graph and update_graph.

create_lookup

create_lookup(entity_name, table_name, key_column_names)

Add an entity to the lookup table.

Parameters

Parameter	Type	Description
`entity_name`	Varchar (opens in a new tab)	The name of the entity.
`table_name`	Varchar (opens in a new tab)	The name of the table (or view). Can be fully qualified.
`key_column_names`	Varchar (opens in a new tab) or Array (opens in a new tab)	The column names that contain identifying information for each object.

Output

Type	Description
Object (opens in a new tab)	The status of the creation request. A dictionary with at least two fields: boolean `success` and an array of strings `messages`.

Explanation

This procedure populates the lookup table with all the objects of the entity that can be constructed from the rows of the table using the identifying (key) information of each object found in the specified columns. The function is polymorphic: key_column_names can be a single string or a list of strings. The table table_name must exist and be accessible; it can however be a fully qualified name, such as db.schema.table. The function key_column_names must consist only of the column names of the given table. In general, table_name can refer to a view or a materialized view. Local (temporary) tables, however, are not currently supported.

If an entity of the given name has not been previously declared, then it will be declared implicitly using key_column_names as the name of the key attributes of the entity.

⚠

If an entity of the given name has already been declared, explicitly or implicitly, then the number of column names in key_column_names must be the same as the number of key attributes of the entity.

Technically, create_lookup creates a part of the lookup table, which is a (possibly materialized) view based on the source table: Any changes (inserts, deletes, and updates) to the source table are automatically reflected in the lookup table. However, modifying the schema of the source table may result in a corrupted state of the scan. This is typically indicated by the error messages Failure during expansion or Materialized View ... is invalid when using the lookup table or the lookup function.

Should this occur, try rebuilding the lookup table with rebuild_lookup_table. Then, ensure that all required scans are in place by verifying the result of list_lookups.

Currently, the procedure create_lookup performs a number of operations (materialized view creation) that make it particularly expensive to run. Consequently, it might be tempting to run multiple calls of create_lookup concurrently in separate transactions or sessions. This is not advisable and is not safe practice: Running create_lookup concurrently might corrupt the state of the system and the Lookup Table in particular.

Examples

   CALL RAI.create_lookup('Client', 'Purchase', ['Client_First_Name', 'Client_Last_Name']);
/* +--------------------------------------------------------------------------------------------------------------------------------------------------+
   | CREATE_LOOKUP                                                                                                                                    |
   |--------------------------------------------------------------------------------------------------------------------------------------------------|
   | {                                                                                                                                                |
   |   "messages": [                                                                                                                                  |
   |     "Loaded lookup table part for entity Client on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [CLIENT_FIRST_NAME, CLIENT_LAST_NAME].",  |
   |     "Added lookup table scan for entity Client on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [CLIENT_FIRST_NAME, CLIENT_LAST_NAME].",   |
   |     "lookup table definition updated."                                                                                                           |
   |   ],                                                                                                                                             |
   |   "success": true                                                                                                                                |
   | }                                                                                                                                                |
   +--------------------------------------------------------------------------------------------------------------------------------------------------+ */

   CALL RAI.create_lookup('Product', 'Purchase', 'Product_Name');
/* +---------------------------------------------------------------------------------------------------------------------------+
   | CREATE_LOOKUP                                                                                                             |
   |---------------------------------------------------------------------------------------------------------------------------|
   | {                                                                                                                         |
   |   "messages": [                                                                                                           |
   |     "Loaded lookup table part for entity Product on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [PRODUCT_NAME].", |
   |     "Added lookup table scan for entity Product on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [PRODUCT_NAME].",  |
   |     "lookup table definition updated."                                                                                    |
   |   ],                                                                                                                      |
   |   "success": true                                                                                                         |
   | }                                                                                                                         |
   +---------------------------------------------------------------------------------------------------------------------------+ */

See Also

create_entity and drop_lookup.

delete_data_stream

delete_data_stream(datasource)

Delete a RAI data stream. A RAI data stream is identified by the fully qualified schema name (opens in a new tab) of a SQL object, such as a table or view, in the form <database>.<schema>.<object>.

Parameters

Parameter	Type	Description
`datasource`	Varchar (opens in a new tab)	Snowflake data source that identifies the RAI data stream.

Output

Type	Description
Varchar (opens in a new tab)	Query output information, for example, “ok”.

Explanation

The procedure delete_data_stream removes a RAI data stream that synchronizes Snowflake data with RAI. When you delete a data stream both the Snowflake SQL object and the RAI relation persist, i.e., they are not deleted.

Examples

CALL RAI.delete_data_stream('sf_table');

See Also

create_data_stream.

delete_graph

delete_graph(graph_name)
delete_graph(graph_name, arguments)

Delete the graph called graph_name.

Parameters

Parameter	Type	Description
`graph_name`	Varchar (opens in a new tab)	The name of the graph.
`arguments`	Object (opens in a new tab)	A JSON object containing additional arguments

Supported Arguments

The following arguments may be specified in the arguments object:

Argument Name	Type	Required	Description
`rai_engine`	Varchar (opens in a new tab)	No	The name of the RAI engine to use to execute the algorithm. Required if no engine has been set in a RAI context.

Output

Type	Description
Variant (opens in a new tab)	A JSON object indicating whether or not the graph was deleted.

{
    "message": "Graph 'my_graph' removed from RAI database 'my_db'.",
    "success": true
}

Explanation

The procedure delete_graph removes the graph called graph_name from RAI. If no graph called graph_name exists, an error is returned.

The delete_graph procedure requires a RAI engine. You can either specify the engine in a RAI context or provide an engine name to the rai_engine argument. The next section contains concrete examples.

Examples

Delete a graph named my_graph with the engine my_engine selected via use_rai_engine:

-- Use the RAI engine `'my_engine'` and save it to the session.
CALL RAI.use_rai_engine('my_engine');
 
-- Delete the graph `'my_graph'`.
CALL RAI.delete_graph('my_graph');

Delete a graph named my_graph with the engine my_engine provided in an arguments object:

CALL RAI.delete_graph('my_graph', {'rai_engine': 'my_engine'})

See Also

create_graph and update_graph.

drop_all_entities

drop_all_entities()

Remove information on all entities from the system and reset the schema map.

Output

Type	Description
Object (opens in a new tab)	The status of the creation request. A dictionary with at least two fields: boolean `success` and an array of strings `messages`.

Explanation

The procedure drop_all_entity removes information on all entities, including all uses of entities in the lookup table, effectively emptying it. Therefore, executing this procedure resets the state of the Schema Mapping Library to its initial empty setting.

Examples

CALL RAI.drop_all_entities();
/* +--------------------------------------------------------------------------------------------------------------------------------------------------+
   | DROP_ALL_ENTITIES                                                                                                                                |
   |--------------------------------------------------------------------------------------------------------------------------------------------------|
   | {                                                                                                                                                |
   |   "messages": [                                                                                                                                  |
   |     "Removed lookup table part for entity Product on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [PRODUCT_NAME].",                       |
   |     "Removed lookup table scan for entity Product on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [PRODUCT_NAME].",                       |
   |     "Removed entity Product.",                                                                                                                   |
   |     "Removed lookup table part for entity Client on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [CLIENT_FIRST_NAME, CLIENT_LAST_NAME].", |
   |     "Removed lookup table scan for entity Client on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [CLIENT_FIRST_NAME, CLIENT_LAST_NAME].", |
   |     "Removed entity Client.",                                                                                                                    |
   |     "lookup table definition updated."                                                                                                           |
   |   ],                                                                                                                                             |
   |   "success": true                                                                                                                                |
   | }                                                                                                                                                |
   +--------------------------------------------------------------------------------------------------------------------------------------------------+  */

drop_all_lookups

drop_all_lookups()

Remove all entries from the lookup table.

Output

Type	Description
Object (opens in a new tab)	The status of the creation request. A dictionary with at least two fields: boolean `success` and an array of strings `messages`.

Explanation

The procedure drop_all_lookups removes all entities from the lookup table, effectively emptying it. It does not, however, remove any entity declarations.

Examples

CALL RAI.drop_all_lookups();
/* +--------------------------------------------------------------------------------------------------------------------------------------------------+
   | DROP_ALL_LOOKUPS                                                                                                                                |
   |--------------------------------------------------------------------------------------------------------------------------------------------------|
   | {                                                                                                                                                |
   |   "messages": [                                                                                                                                  |
   |     "Removed lookup table part for entity Product on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [PRODUCT_NAME].",                       |
   |     "Removed lookup table scan for entity Product on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [PRODUCT_NAME].",                       |
   |     "Removed lookup table part for entity Client on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [CLIENT_FIRST_NAME, CLIENT_LAST_NAME].", |
   |     "Removed lookup table scan for entity Client on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [CLIENT_FIRST_NAME, CLIENT_LAST_NAME].", |
   |     "lookup table definition updated."                                                                                                           |
   |   ],                                                                                                                                             |
   |   "success": true                                                                                                                                |
   | }                                                                                                                                                |
   +--------------------------------------------------------------------------------------------------------------------------------------------------+  */

drop_entity

drop_entity(entity_name)

Remove the declaration of a given entity from the system, including its entry in the lookup table.

Parameters

Parameter	Type	Description
`entity_name`	Varchar (opens in a new tab)	The name of the entity.

Output

Type	Description
Object (opens in a new tab)	The status of the creation request. A dictionary with at least two fields: boolean `success` and an array of strings `messages`.

Explanation

The function drop_entity removes the declaration of a given entity from the system. This function also removes all entries that relate to this entity from the lookup table. Using drop_entity is equivalent to executing drop_lookup for all individual lookups related to the same entity.

Examples

   CALL RAI.drop_entity('Client');
/* +----------------------------------------------------------------------------------------------------------------------------+
   | DROP_ENTITY                                                                                                                |
   |----------------------------------------------------------------------------------------------------------------------------|
   | {                                                                                                                          |
   |   "messages": [                                                                                                            |
   |     "Removed lookup table part for entity Product on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [PRODUCT_NAME].", |
   |     "Removed lookup table scan for entity Product on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [PRODUCT_NAME].", |
   |     "Removed entity Product.",                                                                                             |
   |     "lookup table definition updated."                                                                                     |
   |   ],                                                                                                                       |
   |   "success": true                                                                                                          |
   | }                                                                                                                          |
   +----------------------------------------------------------------------------------------------------------------------------+ */

drop_lookup

drop_lookup(entity_name, table_name, key_column_names)

Remove entries from the lookup table that correspond to the entity entity_name created from the column key_column_names in table table_name.

Parameters

Parameter	Type	Description
`entity_name`	Varchar (opens in a new tab)	The name of the entity.
`table_name`	Varchar (opens in a new tab)	The name of the table (or view). Can be fully qualified.
`key_column_names`	Varchar (opens in a new tab) or Array (opens in a new tab)	The column names containing identifying information for each object.

Output

Type	Description
Object (opens in a new tab)	The status of the creation request. A dictionary with at least two fields: boolean `success` and an array of strings `messages`.

Explanation

The procedure drop_lookup removes the entries from the lookup table that was created by the corresponding lookup — that is, through the execution of create_lookup with the same arguments. If the specified entity does not exist in the lookup table, the procedure essentially ignores the request and signals an error (setting success to FALSE).

Note that executing drop_entity removes all entries relating to an entity name from the lookup table, regardless of which lookup created them. In this sense, it is less selective than drop_lookup.

Examples

CALL RAI.drop_lookup('Client', 'Purchase', ['Client_First_Name', 'Client_Last_Name']);
/* +--------------------------------------------------------------------------------------------------------------------------------------------------+
   | DROP_LOOKUP                                                                                                                                      |
   |--------------------------------------------------------------------------------------------------------------------------------------------------|
   | {                                                                                                                                                |
   |   "messages": [                                                                                                                                  |
   |     "Removed lookup table part for entity Client on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [CLIENT_FIRST_NAME, CLIENT_LAST_NAME].", |
   |     "Removed lookup table scan for entity Client on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [CLIENT_FIRST_NAME, CLIENT_LAST_NAME].", |
   |     "lookup table definition updated."                                                                                                           |
   |   ],                                                                                                                                             |
   |   "success": true                                                                                                                                |
   | }                                                                                                                                                |
   +--------------------------------------------------------------------------------------------------------------------------------------------------+ */

   CALL RAI.drop_lookup('Product', 'Purchase', 'Product_Name');
/* +----------------------------------------------------------------------------------------------------------------------------+
   | DROP_LOOKUP                                                                                                                |
   |----------------------------------------------------------------------------------------------------------------------------|
   | {                                                                                                                          |
   |   "messages": [                                                                                                            |
   |     "Removed lookup table part for entity Product on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [PRODUCT_NAME].", |
   |     "Removed lookup table scan for entity Product on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [PRODUCT_NAME].", |
   |     "lookup table definition updated."                                                                                     |
   |   ],                                                                                                                       |
   |   "success": true                                                                                                          |
   | }                                                                                                                          |
+-------------------------------------------------------------------------------------------------------------------------------+ */

get_data_stream_status

get_data_stream_status(datasource)

Get status information about a RAI data stream. A RAI data stream within a given RAI database link is identified by the fully qualified name (opens in a new tab) of a SQL object, such as a table or view, in the form <database>.<schema>.<object>.

Parameters

Parameter	Type	Description
`datasource`	Varchar (opens in a new tab)	The name of the Snowflake data source that identifies the RAI data stream. If the name is not fully qualified, `get_data_stream_status` will attempt to determine it.

Output

Type	Description
TABLE (opens in a new tab)	RAI data stream status information returned as a TABLE(PROPERTY VARCHAR, VALUE VARIANT). The status information describes whether a RAI data stream is healthy as well as whether the data have been synced.

The returned table includes the following properties (rows):

Column	Description
Source	The source table or view, for example, `sf_db.my_schema.my_table`.
DB Link	The database link, for example, `sf_db.my_schema`.
Integration	The integration name, for example, `myintegration`.
Data sync Status	The sync status. Possible values are `"Syncing: pending unloading from SF"` or `"Fully synced"`. In the first case syncing is in progress, while in the second case the syncing has completed.
Data stream health	The health of the data stream. Possible values are `"Healthy"` and `"Unhealthy"`. For `"Unhealthy"`, there is typically an additional description indicating the reason why the stream is unhealthy, for example, `"Unhealthy - task MY_DB9_MY_TABLE_TASK is SUSPENDED"`.
Latest changes received from SF - SF unload time	The timestamp of the latest changes received from Snowflake.
Latest changes received from SF - Total rows	The number of rows received from Snowflake in the latest syncing.
Latest changes written to RAI - SF unload time	The timestamp indicating the start of writing the data from Snowflake to RAI.
Latest changes written to RAI - Load end time	The timestamp of the end time of the syncing.
Latest changes written to RAI - Total rows	The number of rows written to RAI.

Explanation

The procedure get_data_stream_status returns operational and synchronization information about a RAI data stream.

Note that get_data_stream_status is different from get_data_stream. Specifically, get_data_stream returns only static and metadata information about the RAI data stream and does not return details on the status of the operational and synchronization state of the RAI data stream.

Possible Sates of a RAI Data Stream A RAI data stream can have the following states:

Data Stream Health	Data Sync Status	Description
“Healthy”	“Fully synced”	Healthy and fully synced RAI data stream.
“Healthy”	“Syncing: …”	Healthy and not fully synced RAI data stream. Data synchronization is in progress.
“Unhealthy - …”	“Syncing: …”	Non-operational RAI data stream. Data synchronization has been suspended.

Resuming a Suspended RAI Data Stream In case the data stream has been suspended, you can resume its syncing task after the problem has been rectified by using:

ALTER TASK task_name RESUME

Examples

Here’s an example of invoking get_data_stream_status right after the creation of a RAI data stream:

-- Create data.
CREATE TABLE my_table(x INT, y INT) AS
    SELECT * FROM VALUES (1, 2), (2, 3), (3, 1);
 
-- Set up the RAI data stream.
CALL RAI.create_data_stream('my_table', 'rai_db');
 
-- Check the status of the RAI data stream.
CALL RAI.get_data_stream_status('my_table');
 
-- Example output: healthy data stream waiting for the loading of the data to RAI
/* +---------------------------------------------------------------------------------------------+
   | PROPERTY	                                        | VALUE                                  |
   |---------------------------------------------------------------------------------------------|
   | Source                                             | "sf_db.my_schema.my_table"             |
   | DB Link	                                        | "sf_db.my_schema"                      |
   | Integration	                                    | "myintegration"                        |
   | Data sync Status                                   | "Syncing: pending loading into RAI"    |
   | Data stream health                                 | "Healthy"                              |
   | Latest changes received from SF - SF unload time   | "2023-06-26 19:12:02.483"              |
   | Latest changes received from SF - Total rows       | 1000000                                |
   | Latest changes written to RAI - SF unload time     | (null)                                 |
   | Latest changes written to RAI - Load end time      | (null)                                 |
   | Latest changes written to RAI - Total rows         | (null)                                 |
   +---------------------------------------------------------------------------------------------+ */

The status shows that the RAI data stream is operational and in a healthy state. The data, however, have not been synced yet, which is indicated by "Syncing: pending loading into RAI" in the Data sync Status field.

rebuild_lookup_table

rebuild_lookup_table()

Rebuild the lookup table by dropping and recreating all lookup table parts.

Output

Type	Description
Object (opens in a new tab)	The status of the creation request. A dictionary with at least two fields: boolean `success` and an array of strings `messages`.

Explanation

If schema changes are made to the source tables, errors such as Failure during expansion or Materialized View ... is invalid may occur when using the lookup table or the lookup function. In this case, run rebuild_lookup_table and verify that all parts are there using list_lookups.

Examples

   CALL RAI.rebuild_lookup_table();
/* +--------------------------------------------------------------------------------------------------------------------------------------------------+
   | REBUILD_LOOKUP_TABLE                                                                                                                             |
   |--------------------------------------------------------------------------------------------------------------------------------------------------|
   | {                                                                                                                                                |
   |   "messages": [                                                                                                                                  |
   |     "Removed lookup table part for entity Product on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [PRODUCT_NAME].",                       |
   |     "Removed lookup table scan for entity Product on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [PRODUCT_NAME].",                       |
   |     "Removed lookup table part for entity Client on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [CLIENT_FIRST_NAME, CLIENT_LAST_NAME].", |
   |     "Removed lookup table scan for entity Client on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [CLIENT_FIRST_NAME, CLIENT_LAST_NAME].", |
   |     "Loaded lookup table part for entity Client on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [CLIENT_FIRST_NAME, CLIENT_LAST_NAME].",  |
   |     "Added lookup table scan for entity Client on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [CLIENT_FIRST_NAME, CLIENT_LAST_NAME].",   |
   |     "Loaded lookup table part for entity Product on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [PRODUCT_NAME].",                        |
   |     "Added lookup table scan for entity Product on table COMMERCE_DB.ONLINE_SHOP.PURCHASE with columns [PRODUCT_NAME].",                         |
   |     "lookup table definition updated."                                                                                                           |
   |   ],                                                                                                                                             |
   |   "success": true                                                                                                                                |
   | }                                                                                                                                                |
   +--------------------------------------------------------------------------------------------------------------------------------------------------+ */

set_rai_exit_on_error

set_rai_exit_on_error()

Set the session variable 'RAI_EXIT_ON_ERROR'.

Output

Type	Description
Varchar (opens in a new tab)	`'RAI_EXIT_ON_ERROR'` value.

Explanation

The procedure set_rai_exit_on_error sets the session variable 'RAI_EXIT_ON_ERROR' to 'true'. When set to 'true', procedures throw exceptions on any errors, instead of returning an error object.

This is useful for aborting the execution of long computations as soon as an error occurs.

The possible 'RAI_EXIT_ON_ERROR' values are 'true', 'false', and null. By default, the 'RAI_EXIT_ON_ERROR' value is null when the session variable has not been previously set.

Examples

CALL RAI.set_rai_exit_on_error();

See Also

unset_rai_exit_on_error.

unset_rai_exit_on_error

unset_rai_exit_on_error()

Unset the session variable 'RAI_EXIT_ON_ERROR'.

Output

Type	Description
Varchar (opens in a new tab)	`'RAI_EXIT_ON_ERROR'` value.

Explanation

The procedure unset_rai_exit_on_error sets the session variable 'RAI_EXIT_ON_ERROR' to 'false'. When set to 'false', procedures do not throw exceptions on any errors. Instead, they return an error object.

The possible 'RAI_EXIT_ON_ERROR' values are 'true', 'false', and null. By default, the 'RAI_EXIT_ON_ERROR' value is null when the session variable has not been previously set.

Examples

CALL RAI.unset_rai_exit_on_error();

See Also

set_rai_exit_on_error.

update_graph

update_graph(graph_name, arguments)

Update the graph called graph_name.

Parameters

Parameter	Type	Description
`graph_name`	Varchar (opens in a new tab)	The name of the graph.
`arguments`	Object (opens in a new tab)	A JSON object containing additional arguments.

Supported Arguments

The following arguments may be specified in the arguments object:

Argument Name	Type	Required	Description
`edge_stream_name`	Varchar (opens in a new tab)	No	The name of the new data stream to use for the graph’s edges. If not provided, the graph’s data stream is unchanged.
`directed`	Boolean (opens in a new tab)	No	Whether or not the graph is directed. If not provided, the graph’s `directed` property is unchanged.
`rai_engine`	Varchar (opens in a new tab)	No	The name of the RAI engine to use to execute the algorithm. Required if no engine has been set in a RAI context.

Output

Type	Description
Variant (opens in a new tab)	A JSON object indicating whether or not the graph was updated.

{
    "message": "Graph 'my_graph' created in RAI database 'my_db'.",
    "success": true
}

The update_graph procedure is an alias for create_graph with overwrite set to true, so the response message says created instead of updated.

Explanation

The update_graph procedure updates the graph called graph_name in RAI. If no graph called graph_name exists, an error is returned. You can alter properties of the graph by providing new values in the arguments object.

When you update a graph, the existing graph is deleted and recreated with the updated properties. That is, the update_graph procedure is an alias for create_graph with the overwrite argument set to true. The same arguments for create_graph are supported, as well as an edge_stream_name argument used to change the table or view that represents the edges of the graph. You can not change the name of a graph.

The update_graph procedure requires a RAI engine. You can either specify the engine in a RAI context or provide an engine name to the rai_engine argument. The next section contains concrete examples.

Examples

Update a graph named my_graph to use a new edge stream my_edge_stream with the engine my_engine selected by use_rai_engine:

-- Use the RAI engine `'my_engine'` and save it to the session.
CALL RAI.use_rai_engine('my_engine');
 
-- Update the graph to use a new edge stream `'my_edge_stream'`.
CALL RAI.update_graph('my_graph', {'edge_stream_name': 'my_edge_stream'});

Update a graph named my_graph to use a new edge stream called my_edge_stream with the engine my_engine provided in the arguments object:

CALL RAI.update_graph('my_graph', {'rai_engine': 'my_engine', 'edge_stream_name': 'my_edge_stream'});

See Also

create_graph and delete_graph.

use_rai_database

use_rai_database(rai_db)

Set a RAI database to work with.

Parameters

Parameter	Type	Description
`rai_db`	Varchar (opens in a new tab)	RAI database name.

Output

Type	Description
Varchar (opens in a new tab)	RAI database name, for example, “rai_db”.

Explanation

The procedure use_rai_database sets a RAI database to work with. Once selected, all queries are sent to that RAI database.

Examples

CALL RAI.use_rai_database('rai_db');

See Also

use_rai_engine.

use_rai_engine

use_rai_engine(rai_engine)

Set a RAI engine to work with.

Parameters

Parameter	Type	Description
`rai_engine`	Varchar (opens in a new tab)	RAI engine name.

Output

Type	Description
Varchar (opens in a new tab)	RAI engine name, for example, “rai_engine”.

Explanation

The procedure use_rai_engine sets a RAI engine to work with. Once selected, all queries are executed using that RAI engine.

Examples

CALL RAI.use_rai_engine('rai_engine');

See Also

use_rai_database.

Was this doc helpful?