SQL Library Reference for Snowflake: Functions
Reference for the SQL functions that come with the RAI Integration Services for Snowflake.
adamic_adar
adamic_adar(graph_name, arguments)Compute the Adamic-Adar index of pairs of nodes in a graph.
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 |
|---|---|---|---|
node1 | Int (opens in a new tab) / Array (opens in a new tab) | Yes | When provided without a node2 argument, adamic_adar returns the similarity of node1 to all other nodes in the graph. |
node2 | Int (opens in a new tab) / Array (opens in a new tab) | No | When provided with node1, the adamic_adar function returns the similarity between node1 and node2. |
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table in which to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description | Schema |
|---|---|---|
| Table (opens in a new tab) | A table containing pairs of nodes and their Adamic-Adar index. | TABLE(node1 INT, node2 INT, score FLOAT) |
Explanation
The Adamic-Adar index measures the similarity of two nodes based on the number of shared edges between them. Higher scores indicate that two nodes are more similar than lower scores.
The adamic_adar function returns a table with three columns
— node1, node2, and score —
whose rows contain pairs of nodes and their Adamic-Adar index value.
If no graph with the provided name exists, an error is returned.
Rows where the score is zero are omitted from the results.
A score of zero indicates that two nodes are neither similar nor dissimilar.
Excluding zeros from the results improves performance and prevents the need
to remove those rows in a post-processing step.
The node1 argument is required.
If a value is provided to node2 as well,
for example, if you pass the object {'node1': 1, 'node2': 2} to the arguments parameter,
then adamic_adar returns the similarity of nodes 1 and 2.
If node2 is not provided,
the function returns the Adamic-Adar index of node1
and every other node in the graph.
If either the node1 or node2 nodes do not exist in the graph,
then the adamic_adar function returns an empty table.
The adamic_adar function 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
Compute the Adamic-Adar index between a given node and every other node in a 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute the Adamic-Adar index of node 1 and every other node in 'my_graph'.
SELECT * FROM TABLE(RAI.adamic_adar('my_graph', {'node1': 1}));
/*+------------------------------------+
| NODE1 | NODE2 | SCORE |
+-------+-------+--------------------+
| 1 | 1 | 2.8853900817779268 |
| 1 | 2 | 1.4426950408889634 |
| 1 | 3 | 1.4426950408889634 |
+-------+-------+--------------------+ */Compute the Adamic-Adar index of two nodes in a 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute the Adamic-Adar index of nodes 1 and 2.
SELECT * FROM TABLE(RAI.adamic_adar('my_graph', {'node1': 1, 'node2': 2}));
/*+------------------------------------+
| NODE1 | NODE2 | SCORE |
+-------+-------+--------------------+
| 1 | 2 | 1.4426950408889634 |
+-------+-------+--------------------+ */Compute the Adamic-Adar index with multiple nodes passed to the node1 parameter:
-- 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute the Adamic-Adar index between nodes 1 and 3 and every other node in 'my_graph'.
SELECT * FROM TABLE(RAI.adamic_adar('my_graph', {'node1': [1, 3]}));
/*+-----------------------------+
| NODE1 | NODE2 | SCORE |
+-------+-------+-------------+
| 1 | 1 | 2.885390082 |
| 1 | 2 | 1.442695041 |
| 1 | 3 | 1.442695041 |
| 3 | 1 | 1.442695041 |
| 3 | 3 | 1.442695041 |
+-------+-------+-------------+ */Compute the Adamic-Adar index of a given node and each node in a graph using a different RAI engine than the engine set in the RAI context, and store the results in a Snowflake table:
-- 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute the Adamic-Adar index of node 1 and every other node in 'my_graph'
-- using the RAI engine 'my_other_rai_engine' and store the results
-- in the Snowflake table `my_result_table`. Note that the name of the
-- Snowflake table must be fully qualified.
SELECT TABLE(RAI.adamic_adar(
'my_graph',
{
'node1': 1,
'rai_engine': 'my_other_rai_engine',
'result_table': '<sf_database>.<sf_schema>.my_result_table'
}
));
-- Query the results.
SELECT * FROM my_result_table;
/*+----------------------------------+
| COL1 | COL2 | COL3 |
+------+------+--------------------+
| 1 | 1 | 2.8853900817779268 |
| 1 | 2 | 1.4426950408889634 |
| 1 | 3 | 1.4426950408889634 |
+------+------+--------------------+ */See Also
jaccard_similarity,
cosine_similarity,
preferential_attachment, and
common_neighbor.
average_degree
average_degree(graph_name)
average_degree(graph_name, arguments)Return the average degree of a graph.
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table in which to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description |
|---|---|
| Float (opens in a new tab) | The average degree of the graph. |
Explanation
The function average_degree('my_graph') returns the average degree
over all degrees of nodes in my_graph.
If no graph with the provided name exists, an error is returned.
Note that in directed graphs, the degree of a node is the sum of its indegree and outdegree.
The average_degree function 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
Get the average degree of 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, 1), (1, 2);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Get the average degree of 'my_graph'.
SELECT RAI.average_degree('my_graph');
/*+-----+
| 1.5 |
+-----+ */Get the average degree of a graph using a different RAI engine than the engine set in the RAI context, and store the result in a Snowflake table:
-- 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');
-- Get the average degree of 'my_graph' using the RAI engine 'my_other_rai_engine'
-- and store the results in the Snowflake table `my_result_table`.
-- Note that the name of the Snowflake table must be fully qualified.
SELECT RAI.max_degree(
'my_graph',
{
'rai_engine': 'my_other_rai_engine',
'result_table': '<sf_database>.<sf_schema>.my_result_table'
}
);
-- Query the results.
SELECT * FROM my_result_table;
/*+------+
| COL1 |
+------+
| 1.5 |
+------+ */See Also
degree,
max_degree,
min_degree, and
degree_histogram.
common_neighbor
common_neighbor(graph_name, arguments)Find common neighbors of nodes in a graph.
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 |
|---|---|---|---|
node1 | Int (opens in a new tab) / Array (opens in a new tab) | Yes | When provided without a node2 or node3 argument, common_neighbor returns the common neighbors of node1 and every other node in the graph. |
node2 | Int (opens in a new tab) / Array (opens in a new tab) | No | When provided with node1, the common_neighbor function returns all of the neighbors common to node1 and node2. |
node3 | Int (opens in a new tab) / Array (opens in a new tab) | No | When provided with node1, the common_neighbor function returns all of the nodes for which node3 and node1 are common neighbors. When all three of node1, node2, and node3 are provided, the function checks whether or not node3 is a common neighbor of node1 and node2. |
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table in which to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description | Schema |
|---|---|---|
| Table (opens in a new tab) | A table containing pairs of nodes and their Adamic-Adar index. | TABLE(node1 INT, node2 INT, node3 INT) |
Explanation
The common_neighbor function returns a table with three columns
— node1, node2, and node3 —
whose rows indicate that node3 is a common neighbor of node1 and node2.
If no graph with the provided name exists, an error is returned.
The node1 argument is required.
If a value is provided to node2 as well,
for example, if you pass the object {'node1': 1, 'node2': 2} to the arguments parameter,
then the common_neighbor function returns the nodes
that are common neighbors of nodes 1 and 2.
You can provide node IDs for all three node1, node2, and node3 arguments
to check whether or not node3 is a common neighbor of node1 and node2.
If so, the common_neighbor function returns the row in the result table
contining node1, node2, and node3.
Otherwise, the function returns an empty table.
If any of the node1, node2, or node3 nodes do not exist in the graph,
then the common_neighbor function returns an empty table.
The common_neighbor function 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
Find all common neighbors of two nodes in a 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 3), (1, 4), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Find common neighbors of nodes 1 and 2.
SELECT * FROM TABLE(RAI.common_neighbor('my_graph', {'node1': 1, 'node2': 2}));
/*+-----------------------+
| NODE1 | NODE2 | NODE3 |
+-------+-------+-------+
| 1 | 2 | 3 |
+------+--------+-------+ */Check whether or not a node is a common neighbor of two other nodes:
-- 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 3), (1, 4), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Check whether or not node 3 is a common neighbor of nodes 1 and 2.
-- The output is non-empty.
SELECT * FROM TABLE(RAI.common_neighbor('my_graph', {'node1': 1, 'node2': 2, 'node2': 3}));
/*+-----------------------+
| NODE1 | NODE2 | NODE3 |
+-------+-------+-------+
| 1 | 2 | 3 |
+------+--------+-------+ */
-- Check whether or not node 4 is a common neighbor of nodes 1 and 2.
-- The output is empty.
SELECT * FROM TABLE(RAI.common_neighbor('my_graph', {'node1': 1, 'node2': 2, 'node2': 4}));
/*+-----------------------+
| NODE1 | NODE2 | NODE3 |
+-------+-------+-------+
| Empty table. |
+------+--------+-------+ */Find the common neighbor of a given node and every other node in a 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 3), (1, 4), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Find the common neighbors of node 1 and every other node in 'my_graph'.
SELECT * FROM TABLE(RAI.common_neighbor('my_graph', {'node1': 1}));
/*+-----------------------+
| NODE1 | NODE2 | NODE3 |
+-------+-------+-------+
| 1 | 1 | 3 |
| 1 | 1 | 4 |
| 1 | 2 | 3 |
+------+--------+-------+ */Find the common neighbor of a given node and every other node in a graph using a different RAI engine than the engine set in the RAI context, and store the results in a Snowflake table:
-- 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Find the common neighbors of node 1 and every other node in 'my_graph'
-- using the RAI engine 'my_other_rai_engine' and store the results
-- in the Snowflake table `my_result_table`. Note that the name of the
-- Snowflake table must be fully qualified.
SELECT TABLE(RAI.common_neighbor(
'my_graph',
{
'node1': 1,
'rai_engine': 'my_other_rai_engine',
'result_table': '<sf_database>.<sf_schema>.my_result_table'
}
));
-- Query the results.
SELECT * FROM my_result_table;
/*+-----------------------+
| NODE1 | NODE2 | NODE3 |
+-------+-------+-------+
| 1 | 1 | 3 |
| 1 | 1 | 4 |
| 1 | 2 | 3 |
+------+--------+-------+ */See Also
jaccard_similarity,
cosine_similarity,
preferential_attachment, and
cosine_similarity.
cosine_similarity
cosine_similarity(graph_name, arguments)Compute the cosine similarity of pairs of nodes in a graph.
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 |
|---|---|---|---|
node1 | Int (opens in a new tab) / Array (opens in a new tab) | Yes | When provided without a node2 argument, cosine_similarity returns the similarity of node1 to all other nodes in the graph. |
node2 | Int (opens in a new tab) / Array (opens in a new tab) | No | When provided with node1, the cosine_similarity function returns the similarity between node1 and node2. |
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table in which to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description | Schema |
|---|---|---|
| Table (opens in a new tab) | A table containing pairs of nodes and their cosine similarity value. | TABLE(node1 INT, node2 INT, score FLOAT) |
Explanation
The cosine similarity measures the similarity of two nodes as the inner product of vector representations of the nodes’ neighborhoods. Values range from -1 to 1, inclusive.
The cosine_similarity function returns a table with three columns
— node1, node2, and score —
whose rows contain pairs of nodes and their cosine similarity value.
If no graph with the provided name exists, an error is returned.
Rows where the score is zero are omitted from the results.
A score of zero indicates that two nodes are neither similar nor dissimilar.
Excluding zeros from the results improves performance and prevents the need
to remove those rows in a post-processing step.
The node1 argument is required.
If a value is provided to node2 as well,
for example, if you pass the object {'node1': 1, 'node2': 2} to the arguments parameter,
then cosine_similarity returns the similarity of nodes 1 and 2.
If node2 is not provided,
the function returns the cosine similarity of node1
and every other node in the graph.
If either the node1 or node2 nodes do not exist in the graph,
then the cosine_similarity function returns an empty table.
The cosine_similarity function 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
Compute the cosine similarity between a given node and every other node in a 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute the cosine similarity of node 1 and every other node in 'my_graph'.
SELECT * FROM TABLE(RAI.cosine_similarity('my_graph', {'node1': 1}));
/*+------------------------------------+
| NODE1 | NODE2 | SCORE |
+-------+-------+--------------------+
| 1 | 1 | 1.0 |
| 1 | 2 | 0.5 |
| 1 | 3 | 0.7071067811865475 |
+-------+-------+--------------------+ */Compute the cosine similarity of two nodes in a 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute the cosine similarity of nodes 1 and 2.
SELECT * FROM TABLE(RAI.cosine_similarity('my_graph', {'node1': 1, 'node2': 2}));
/*+-----------------------+
| NODE1 | NODE2 | SCORE |
+-------+-------+-------+
| 1 | 2 | 0.5 |
+-------+-------+-------+ */Compute the cosine similarity of each node in a graph using a different RAI engine than the engine set in the RAI context, and store the results in a Snowflake table:
-- 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute the cosine similarity of node 1 and every other node in 'my_graph'
-- using the RAI engine 'my_other_rai_engine' and store the results
-- in the Snowflake table `my_result_table`. Note that the name of the
-- Snowflake table must be fully qualified.
SELECT TABLE(RAI.cosine_similarity(
'my_graph',
{
'node1': 1,
'rai_engine': 'my_other_rai_engine',
'result_table': '<sf_database>.<sf_schema>.my_result_table'
}
));
-- Query the results.
SELECT * FROM my_result_table;
/*+----------------------------------+
| COL1 | COL2 | COL3 |
+------+------+--------------------+
| 1 | 1 | 1.0 |
| 1 | 2 | 0.5 |
| 1 | 3 | 0.7071067811865475 |
+------+------+--------------------+ */See Also
jaccard_similarity,
preferential_attachment,
adamic_adar, and
common_neighbor.
create_rai_database
create_rai_database(rai_db)Create a database in RAI.
Parameters
| Parameter | Type | Description |
|---|---|---|
rai_db | Varchar (opens in a new tab) | RAI database name. |
Output
| Type | Description |
|---|---|
| Variant (opens in a new tab) | Status of the creation request. Example:“ok”. |
Explanation
The function create_rai_database creates a database in RAI.
Examples
SELECT RAI.create_rai_database('rai_db');See Also
get_rai_database and
delete_rai_database.
create_rai_engine
create_rai_engine(rai_engine, size)Create an engine in RAI.
Parameters
| Parameter | Type | Description |
|---|---|---|
rai_engine | Varchar (opens in a new tab) | RAI engine name. |
size | Varchar (opens in a new tab) | RAI engine size. Possible values are 'XS', 'S', 'M', 'L', 'XL'. |
Output
| Type | Description |
|---|---|
| Variant (opens in a new tab) | Status of the create engine request. Example:“ok”. |
Explanation
The function create_rai_engine creates an engine in RAI.
Examples
SELECT RAI.create_rai_engine('rai_engine', 'S');See Also
get_rai_engine and
delete_rai_engine.
current_rai_database
current_rai_database()Return the RAI database that is currently selected.
Output
| Type | Description |
|---|---|
| Varchar (opens in a new tab) | The name of the currently selected RAI database. |
Explanation
The function current_rai_database returns the currently selected RAI database.
The database to use is typically set by calling the use_rai_database procedure.
If no database has been previously selected, the function returns NULL.
Examples
SELECT RAI.current_rai_database();
/*+----------------------------+
| RAI.CURRENT_RAI_DATABASE() |
+----------------------------+
| rai_db |
+----------------------------+ */See Also
current_rai_engine
current_rai_engine()Return the RAI engine that is currently selected.
Output
| Type | Description |
|---|---|
| Varchar (opens in a new tab) | The name of the currently selected RAI engine. |
Explanation
The function current_rai_engine returns the currently selected RAI engine.
The engine to use is typically set by calling the use_rai_engine procedure.
If no engine has been previously selected, the function returns NULL.
Examples
SELECT RAI.current_rai_engine();
/*+--------------------------+
| RAI.CURRENT_RAI_ENGINE() |
+--------------------------+
| rai_engine |
+--------------------------+ */See Also
degree
degree(graph_name)
degree(graph_name, arguments)Return the degree of each node in a graph.
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 |
|---|---|---|---|
node | Int (opens in a new tab) / Array (opens in a new tab) | No | Instruct degree to return only the rows in the output table whose node column contains the provided value(s). |
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table in which to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description | Schema |
|---|---|---|
| Table (opens in a new tab) | A table representing pairs of neighbors in the graph. | TABLE(node1 INT, node2 INT) |
Explanation
The function degree('my_graph') returns a table with two columns, node and degree,
whose rows represent the degrees of nodes in my_graph.
If no graph with the provided name exists, an error is returned.
Note that in directed graphs, the degree of a node is the sum of its indegree and outdegree.
The degree function 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
Get the degree of each node in an undirected graph with the RAI engine set in a RAI context:
-- 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Get the degrees of each node in 'my_graph'.
SELECT * FROM TABLE(RAI.degree('my_graph'));
/*+---------------+
| NODE | DEGREE |
+------+--------+
| 1 | 2 |
| 2 | 1 |
+------+--------+ */Get the degree of each node in 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges', {'directed': true});
-- Get the degrees of each node in 'my_graph'.
SELECT * FROM TABLE(RAI.degree('my_graph'));
/*+---------------+
| NODE | DEGREE |
+------+--------+
| 1 | 3 |
| 2 | 1 |
+------+--------+ */Get the degree of a specific node in a 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');
-- Get the degree of node 2 in 'my_graph'.
SELECT * FROM TABLE(RAI.degree('my_graph', {'node': 2}));
/*+---------------+
| NODE | DEGREE |
+------+--------+
| 2 | 2 |
+------+--------+ */Get the degree of multiple nodes simultaneously:
-- 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');
-- Get the degree of nodes 1 and 2 in 'my_graph'.
SELECT * FROM TABLE(RAI.degree('my_graph', {'node': [1, 2]}));
/*+---------------+
| NODE | DEGREE |
+------+--------+
| 1 | 1 |
| 2 | 2 |
+------+--------+ */Get the degree of each node in a graph using a different RAI engine than the engine set in the RAI context, and store the results in a Snowflake table:
-- 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');
-- Get the number of edges in 'my_graph' using the RAI engine 'my_other_rai_engine'
-- and store the results in the Snowflake table `my_result_table`.
-- Note that the name of the Snowflake table must be fully qualified.
SELECT TABLE(RAI.degree(
'my_graph',
{
'rai_engine': 'my_other_rai_engine',
'result_table': '<sf_database>.<sf_schema>.my_result_table'
}
));
-- Query the results.
SELECT * FROM my_result_table;
/*+-------------+
| COL1 | COL2 |
+------+------+
| 1 | 2 |
| 2 | 1 |
+------+------+ */See Also
min_degree,
max_degree,
average_degree,
degree_histogram, and
neighbor.
degree_centrality
degree_centrality(graph_name)
degree_centrality(graph_name, arguments)Compute the degree centrality of nodes in a graph.
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 |
|---|---|---|---|
node | Int (opens in a new tab) / Array (opens in a new tab) | No | Instruct degree_centrality to return only the rows in the output table whose node column contains the provided value. |
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table in which to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description | Schema |
|---|---|---|
| Table (opens in a new tab) | A table containing pairs of nodes and their degree centrality value. | TABLE(node INT, value FLOAT) |
Explanation
Degree centrality is a measure of the centrality, or importance, of a node in a graph based on its degree. Degree centrality is computed as the degree of a node divided by the number of nodes in the graph minus one. For simple graphs without loops this value is at most one. Graphs with loops may have nodes with degree centrality greater than one.
The degree_centrality('my_graph') function returns a table with two columns
— node and value —
whose rows represent pairs of nodes in my_graph and their degree centrality value.
If no graph with the provided name exists, an error is returned.
You may get the degree centrality of a single node
by providing the node ID to the node argument.
If the specified node does not exist in the graph,
an empty table is returned.
The degree_centrality function 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
Compute the degree centrality of each node in a 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute the degree centrality of each node in 'my_graph'.
SELECT * FROM TABLE(RAI.degree_centrality('my_graph'));
/*+--------------+
| NODE | VALUE |
+------+-------+
| 1 | 0.5 |
| 2 | 1 |
| 3 | 0.5 |
+------+-------+ */Compute the degree centrality of a single node:
-- 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute the degree centrality of node 2.
SELECT * FROM TABLE(RAI.degree_centrality('my_graph', {'node': 2}));
/*+--------------+
| NODE | VALUE |
+------+-------+
| 2 | 1 |
+------+-------+ */Compute the degree centrality of each node in a graph using a different RAI engine than the engine set in the RAI context, and store the results in a Snowflake table:
-- 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute the degree centrality of each node in 'my_graph'
-- using the RAI engine 'my_other_rai_engine' and store the results
-- in the Snowflake table `my_result_table`. Note that the name of the
-- Snowflake table must be fully qualified.
SELECT TABLE(RAI.degree_centrality(
'my_graph',
{
'rai_engine': 'my_other_rai_engine',
'result_table': '<sf_database>.<sf_schema>.my_result_table'
}
));
-- Query the results.
SELECT * FROM my_result_table;
/*+-------------+
| COL1 | COL2 |
+------+------+
| 1 | 0.5 |
| 2 | 1 |
| 3 | 0.5 |
+------+------+ */See Also
eigenvector_centrality and
pagerank.
degree_histogram
degree_histogram(graph_name)
degree_histogram(graph_name, arguments)Count the number of nodes with each degree in a graph.
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 |
|---|---|---|---|
degree | Int (opens in a new tab) / Array (opens in a new tab) | No | Instruct degree_histogram to return only the rows in the output table whose degree column contains the provided value. |
count | Int (opens in a new tab) / Array (opens in a new tab) | No | Instruct degree_histogram to return only the rows in the output table whose count column contains the provided value. |
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description | Schema |
|---|---|---|
| Table (opens in a new tab) | A table representing the number of nodes with each degree. | TABLE(node1 INT, node2 INT) |
Explanation
The function degree_histogram('my_graph') returns a table
with two columns, degree and count,
whose rows represent the number of nodes with each degree in the graph.
If no graph with the provided name exists, an error is returned.
Note that in directed graphs, the degree of a node is the sum of its indegree and outdegree.
The degree_histogram function 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
Count the number of nodes with each degree in a 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');
-- Count the number of nodes with each degree in 'my_graph'.
SELECT * FROM TABLE(RAI.degree_histogram('my_graph'));
/*+----------------+
| DEGREE | COUNT |
+--------+-------+
| 1 | 2 |
| 2 | 1 |
+--------+-------+ */Count the number of nodes in a graph with a specific degree:
-- 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');
-- Count the number of nodes with degree 2 in 'my_graph'.
SELECT * FROM TABLE(RAI.degree_histogram('my_graph', {'degree': 2}));
/*+----------------+
| DEGREE | COUNT |
+--------+-------+
| 2 | 1 |
+--------+-------+ */Find all degrees for which there is a specific number of nodes:
-- 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');
-- Count the number of nodes with degree 2 in 'my_graph'.
SELECT * FROM TABLE(RAI.degree_histogram('my_graph', {'count': 2}));
/*+----------------+
| DEGREE | COUNT |
+--------+-------+
| 1 | 2 |
+--------+-------+ */Count the number of nodes in a graph with each degree using a different RAI engine than the one set in the RAI context, and store the results in a Snowflake table:
-- 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');
-- Count the number of nodes with each degree in 'my_graph'
-- using the RAI engine 'my_other_rai_engine' and store the results
-- in the Snowflake table `my_result_table`.
-- Note that the name of the Snowflake table must be fully qualified.
SELECT TABLE(RAI.degree_histogram(
'my_graph',
{
'rai_engine': 'my_other_rai_engine',
'result_table': '<sf_database>.<sf_schema>.my_result_table'
}
));
-- Query the results.
SELECT * FROM my_result_table;
/*+-------------+
| COL1 | COL2 |
+------+------+
| 1 | 2 |
| 2 | 1 |
+------+------+ */See Also
min_degree,
max_degree,
average_degree, and
degree.
delete_rai_database
delete_rai_database(rai_db)Delete a database in RAI.
Parameters
| Parameter | Type | Description |
|---|---|---|
rai_db | Varchar (opens in a new tab) | RAI database name. |
Output
| Type | Description |
|---|---|
| Variant (opens in a new tab) | Status of the deletion request. Example:“ok”. |
Explanation
The function delete_rai_database deletes a database in RAI.
Examples
SELECT RAI.delete_rai_database('rai_db');See Also
delete_rai_engine
delete_rai_engine(rai_engine)Delete an engine in RAI.
Parameters
| Parameter | Type | Description |
|---|---|---|
rai_engine | Varchar (opens in a new tab) | RAI engine name. |
Output
| Type | Description |
|---|---|
| Variant (opens in a new tab) | Status of the deletion request. Example:“ok”. |
Explanation
The function delete_rai_engine deletes an engine in RAI.
Examples
SELECT RAI.delete_rai_engine('rai_engine');See Also
diameter_range
diameter_range(graph_name)
diameter_range(graph_name, arguments)Estimate the diameter of a graph with lower and upper bounds.
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table in which to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description |
|---|---|
| Object (opens in a new tab) | A JSON object containing the lower and upper bounds for the diameter of the graph. |
{"min": 1, "max": 10}Explanation
The diameter_range function is used to determine
the range of possible diameter values for a graph.
If no graph with the provided name exists, an error is returned.
The graph’s diameter is estimated by selecting a number of random nodes in the graph and taking the maximum of all shortest path lengths from each selected node to the other nodes in the graph. This gives a range per node. Then, the intersection of the ranges is taken and the final range is returned.
The diameter_range function 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
Estimate the diameter of a 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), (3, 4);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Estimate the diameter of 'my_graph'.
SELECT RAI.diameter_range('my_graph');
/*+-----------------------+
| { "max": 2, "min": 2} |
+-----------------------+ */Estimate the diameter of a graph using a different RAI engine than the engine set in the RAI context, and store the results in a Snowflake table:
-- 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), (3, 4);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Estimate the diameter of 'my_graph' using the RAI engine 'my_other_rai_engine'
-- and store the results in the Snowflake table `my_result_table`.
-- Note that the name of the Snowflake table must be fully qualified.
SELECT RAI.diameter_range(
'my_graph',
{
'rai_engine': 'my_other_rai_engine',
'result_table': '<sf_database>.<sf_schema>.my_result_table'
}
);
-- Query the results.
SELECT * FROM my_result_table;
/*+-----------------------+
| COL1 |
+-----------------------+
| { "max": 2, "min": 2} |
+-----------------------+ */See Also
eigenvector_centrality
eigenvector_centrality(graph_name)
eigenvector_centrality(graph_name, arguments)Compute the eigenvector centrality of nodes in a graph.
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 |
|---|---|---|---|
node | Int (opens in a new tab) / Array (opens in a new tab) | No | Instruct eigenvector_centrality to return only the rows in the output table whose node column contains the provided value. |
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table in which to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description | Schema |
|---|---|---|
| Table (opens in a new tab) | A table containing pairs of nodes and their eigenvector centrality value. | TABLE(node INT, value FLOAT) |
Explanation
Eigenvector centrality measures a node’s importance in such a way that connections to more important nodes contribute more to a node’s score than connections to less important nodes. It is computed based on the eigenvector associated with the top eigenvalue of the graph’s adjacency matrix.
We use the
power method (opens in a new tab)
to compute the eigenvector in our implementation.
Note that the power method requires the adjacency matrix to be diagonalizable,
and will only converge if the largest positive eignevalue has multiplicity one.
If your graph does not meet either of these requirements,
the eigenvector_centrality function will not converge.
The eigenvector_centrality('my_graph') function returns a table with two columns
— node and value —
whose rows represent pairs of nodes in my_graph
and their eigenvector centrality value.
If no graph with the provided name exists, an error is returned.
You may get the eigenvector centrality of a single node
by providing the node ID to the node argument.
If the specified node does not exist in the graph,
an empty table is returned.
The eigenvector_centrality function 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
Compute the eigenvector centrality of each node in a 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute the eigenvector centrality of each node in 'my_graph'.
SELECT * FROM TABLE(RAI.eigenvector_centrality('my_graph'));
/*+---------------------+
| NODE | VALUE |
+------+--------------+
| 1 | 0.4082482905 |
| 2 | 0.8164965809 |
| 3 | 0.4082482905 |
+------+--------------+ */Compute the eigenvector centrality of a single node:
-- 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute the eigenvector centrality of node 2.
SELECT * FROM TABLE(RAI.eigenvector_centrality('my_graph', {'node': 2}));
/*+---------------------+
| NODE | VALUE |
+------+--------------+
| 2 | 0.8164965809 |
+------+--------------+ */Compute the eigenvector centrality of each node in a graph using a different RAI engine than the engine set in the RAI context, and store the results in a Snowflake table:
-- 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute the eigenvector centrality of each node in 'my_graph'
-- using the RAI engine 'my_other_rai_engine' and store the results
-- in the Snowflake table `my_result_table`. Note that the name of the
-- Snowflake table must be fully qualified.
SELECT TABLE(RAI.eigenvector_centrality(
'my_graph',
{
'rai_engine': 'my_other_rai_engine',
'result_table': '<sf_database>.<sf_schema>.my_result_table'
}
));
-- Query the results.
SELECT * FROM my_result_table;
/*+---------------------+
| COL1 | COL2 |
+------+--------------+
| 1 | 0.4082482905 |
| 2 | 0.8164965809 |
| 3 | 0.4082482905 |
+------+--------------+ */See Also
degree_centrality and
pagerank.
exec
exec(rai_query)
exec(rai_db, rai_engine, rai_query, data, readonly)Execute a query against the RAI database.
Parameters
| Parameter | Type | Description |
|---|---|---|
rai_db | Varchar (opens in a new tab) | RAI database name. |
rai_engine | Varchar (opens in a new tab) | RAI engine name. |
rai_query | Varchar (opens in a new tab) | The query that will be executed in RAI. |
data | Variant (opens in a new tab) | Data input for the rai_query. |
readonly | Boolean (opens in a new tab) | Whether or not the query is read-only. |
Output
| Type | Description |
|---|---|
| Variant (opens in a new tab) | The query output information. |
/*+-----------------------------------------------+
| EXEC('DEF OUTPUT = {1; 2; 3}') |
+-----------------------------------------------+
| [ [ 1 ], [ 2 ], [ 3 ] ] |
+-----------------------------------------------+ */Explanation
The function exec executes in RAI the rai_query passed as a parameter.
It uses the rai_db database and the rai_engine engine.
The data parameter is used to provide input for the query.
The readonly parameter specifies whether or not the rai_query is read-only.
By default, readonly is set to TRUE.
Examples
SELECT RAI.exec('def output = {1; 2; 3}');
SELECT RAI.exec('rai_db', 'rai_engine', 'def output=foo', {'foo' : 'hello'}, TRUE);See Also
exec_into and create_rai_database.
exec_into
exec_into(rai_query, sf_target)
exec_into(rai_db, rai_engine, sf_warehouse, sf_target, rai_query)
exec_into(rai_db, rai_engine, rai_query, data, readonly, sf_warehouse, sf_target)Execute a query against the RAI database and insert the output into a target Snowflake table.
Parameters
| Parameter | Type | Description |
|---|---|---|
rai_db | Varchar (opens in a new tab) | RAI database name. |
rai_engine | Varchar (opens in a new tab) | RAI engine name. |
sf_warehouse | Varchar (opens in a new tab) | The selected Snowflake warehouse. |
sf_target | Varchar (opens in a new tab) | Target Snowflake table that will contain the query output. Note that the table specified by sf_target needs to be fully qualified, for example, database.schema.table. |
rai_query | Varchar (opens in a new tab) | The query that will be executed in RAI. |
data | Variant (opens in a new tab) | Data input for the rai_query. |
readonly | Boolean (opens in a new tab) | Whether or not the query is read-only. |
Output
| Type | Description |
|---|---|
| Variant (opens in a new tab) | Status of the request. Example: “1 row(s) written”. |
Explanation
The function exec_into executes the rai_query passed as a parameter in RAI.
It uses the rai_db database and the rai_engine engine.
The output of the query is stored in the Snowflake table sf_target.
The Snowflake warehouse is specified through sf_warehouse.
Examples
CREATE OR REPLACE TABLE my_data(x INT);
SELECT RAI.exec_into('def output = {1; 2; 3}', 'my_db.rai.my_data');
CREATE OR REPLACE TABLE my_output(s VARCHAR);
SELECT RAI.exec_into(
'rai_db',
'rai_engine',
'def output=foo',
{'foo' : 'hello'},
TRUE,
'my_warehouse',
'my_db.rai.my_output'
);See Also
exec and create_rai_database.
get_rai_database
get_rai_database(rai_db)Get information about a RAI database.
Parameters
| Parameter | Type | Description |
|---|---|---|
rai_db | Varchar (opens in a new tab) | RAI database name. |
Output
| Type | Description |
|---|---|
| Variant (opens in a new tab) | RAI database information as a JSON object. |
{
"account_name": "XXXXXXXXXXXXXXXXXX",
"created_by": "XXXXXXXXXXXXXXXXXX",
"created_on": "2023-05-31T16:05:05.131Z",
"id": "XXXXXXXXXXXXXXXXXX",
"name": "rai_db",
"region": "us-east",
"state": "CREATED"
}Explanation
The function get_rai_database returns information about a RAI database.
Examples
SELECT RAI.get_rai_database('rai_db');See Also
get_data_stream
get_data_stream(datasource)Get a data stream for the RAI Integration.
A data stream is identified by the fully-qualified name (opens in a new tab)
of a SQL object (i.e., table or view) in the form <database>.<schema>.<object>.
Parameters
| Parameter | Type | Description |
|---|---|---|
datasource | Varchar (opens in a new tab) | The fully qualified name of the Snowflake data source that identifies the data stream. |
Output
| Type | Description |
|---|---|
| Variant (opens in a new tab) | Data stream information as a JSON object. |
{
"account": "XXXXXXXXXXXXXXXXXX",
"createdBy": "XXXXXXXXXXXXXXXXXX",
"createdOn": "2023-05-31T11:24:09.710Z",
"dbLink": "sf_db.my_schema",
"id": "XXXXXXXXXXXXXXXXXX",
"integration": "my_integration",
"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 function get_data_stream returns a data stream used to synchronize Snowflake data with RAI.
Examples
SELECT RAI.get_data_stream('sf_table');See Also
get_data_stream_status and
create_data_stream.
get_data_stream_status
get_data_stream_status(datasource)Get status information about a data stream for the RAI Integration.
A data stream is identified by the fully-qualified name (opens in a new tab)
of a SQL object (i.e., table or view) in the form <database>.<schema>.<object>.
Parameters
| Parameter | Type | Description |
|---|---|---|
datasource | Varchar (opens in a new tab) | The fully qualified name of the Snowflake data source that identifies the data stream. |
Output
| Type | Description |
|---|---|
| Variant (opens in a new tab) | Data stream status information as a JSON object. |
{
"account": "XXXXXXXXXXXXXXXXXX",
"id": "XXXXXXXXXXXXXXXXXX",
"name": "my_integration--sf_db.my_schema.datasource",
"raiLoadEnd": XXXXXXXXXXXXXXXXXX,
"snowflakeUnloadStart": XXXXXXXXXXXXXXXXXX
}Explanation
The function get_data_stream returns status information in JSON format about a data stream used to synchronize Snowflake data with RAI.
Examples
SELECT RAI.get_data_stream_status('sf_table');See Also
get_data_stream and
create_data_stream.
get_rai_engine
get_rai_engine(rai_engine)Get information about a RAI engine.
Parameters
| Parameter | Type | Description |
|---|---|---|
rai_engine | Varchar (opens in a new tab) | RAI engine name. |
Output
| Type | Description |
|---|---|
| Variant (opens in a new tab) | RAI engine information as a JSON object. |
{
"account_name": "XXXXXXXXXXXXXXXXXX",
"created_by": "XXXXXXXXXXXXXXXXXX",
"created_on": "2023-05-25T23:04:29.000Z",
"id": "XXXXXXXXXXXXXXXXXX",
"name": "rai_engine",
"region": "us-east",
"size": "S",
"state": "PROVISIONED"
}Explanation
The function get_rai_engine returns information about a RAI engine.
Examples
SELECT RAI.get_rai_engine('rai_engine');See Also
get_graph
get_graph(graph_name)Get information about a graph, if it exists.
Parameters
| Parameter | Type | Description |
|---|---|---|
graph_name | Varchar (opens in a new tab) | The name of the graph. |
Output
| Type | Description |
|---|---|
| Variant (opens in a new tab) | Graph information as a JSON object. |
{
"DIRECTED": false,
"EDGE_STREAM": "my_db.my_schema.my_edge_stream",
"NAME": "my_graph",
"RAI_DATABASE": "my_rai_database"
}Explanation
The function get_graph returns a JSON object with information about the graph,
including the name of the graph, whether or not the graph is directed,
the fully qualified name of the graph’s edge stream,
and the name of the RAI database in which the graph is stored.
Examples
SELECT RAI.get_graph('my_graph');See Also
is_graph_created and
list_graphs.
is_connected
is_connected(graph_name)
is_connected(graph_name, arguments)Check whether or not a graph is connected.
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table in which to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description |
|---|---|
| Boolean (opens in a new tab) | Whether or not the graph is connected. |
Explanation
Returns true if the graph is connected and false otherwise.
If no graph with the provided name exists, an error is returned.
The is_connected function 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
-- 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, 1), (1, 2);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Determine whether or not 'my_graph' is connected.
SELECT RAI.is_connected('my_graph');
/*+------+
| TRUE |
+------+ */See Also
is_graph_created
is_graph_created(graph_name)Check whether a graph called graph_name exists in the RAI Integration.
Parameters
| Parameter | Type | Description |
|---|---|---|
graph_name | Varchar (opens in a new tab) | The name of the graph. |
Output
| Type | Description |
|---|---|
| Boolean (opens in a new tab) | Whether or not the graph exists. |
Explanation
Returns true if a graph called graph_name exists and false if it does not.
Examples
SELECT RAI.is_graph_created('my_graph');See Also
create_graph,
get_graph, and
list_graphs.
jaccard_similarity
jaccard_similarity(graph_name, arguments)Compute the Jaccard similarity of pairs of nodes in a graph.
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 |
|---|---|---|---|
node1 | Int (opens in a new tab) / Array (opens in a new tab) | Yes | When provided without a node2 argument, jaccard_similarity returns the similarity of node1 to all other nodes in the graph. |
node2 | Int (opens in a new tab) / Array (opens in a new tab) | No | When provided with node1, the jaccard_similarity function returns the similarity between node1 and node2. |
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table in which to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description | Schema |
|---|---|---|
| Table (opens in a new tab) | A table containing pairs of nodes and their Jaccard similarity value. | TABLE(node1 INT, node2 INT, score FLOAT) |
Explanation
The Jaccard similarity measures the similarity of two nodes based on the number of neighbors common to both nodes. The values range from 0 to 1, inclusive.
The jaccard_similarity function returns a table with three columns
— node1, node2, and score —
whose rows contain pairs of nodes and their Jaccard similarity value.
If no graph with the provided name exists, an error is returned.
Rows where the score is zero are omitted from the results.
A score of zero indicates that two nodes are neither similar nor dissimilar.
Excluding zeros from the results improves performance and prevents the need
to remove those rows in a post-processing step.
The node1 argument is required.
If a value is provided to node2 as well,
for example, if you pass the object {'node1': 1, 'node2': 2} to the arguments parameter,
then jaccard_similarity returns the Jaccard similarity of nodes 1 and 2.
If node2 is not provided,
the function returns the Jaccard similarity of node1
and every other node in the graph.
If either the node1 or node2 nodes do not exist in the graph,
then the jaccard_similarity function returns an empty table.
The jaccard_similarity function 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
Compute the Jaccard similarity between a given node and every other node in a 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute the Jaccard similarity of node 1 and every other node in 'my_graph'.
SELECT * FROM TABLE(RAI.jaccard_similarity('my_graph', {'node1': 1}));
/*+------------------------------------+
| NODE1 | NODE2 | SCORE |
+-------+-------+--------------------+
| 1 | 1 | 1.0 |
| 1 | 2 | 0.3333333333333333 |
| 1 | 3 | 0.5 |
+-------+-------+--------------------+ */Compute the Jaccard similarity of two nodes in a 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute the Jaccard similarity of nodes 1 and 2.
SELECT * FROM TABLE(RAI.jaccard_similarity('my_graph', {'node1': 1, 'node2': 2}));
/*+------------------------------------+
| NODE1 | NODE2 | SCORE |
+-------+-------+--------------------+
| 1 | 2 | 0.3333333333333333 |
+-------+-------+--------------------+ */Compute the Jaccard similarity of each node in a graph using a different RAI engine than the engine set in the RAI context, and store the results in a Snowflake table:
-- 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute the Jaccard similarity of node 1 and every other node in 'my_graph'
-- using the RAI engine 'my_other_rai_engine' and store the results
-- in the Snowflake table `my_result_table`. Note that the name of the
-- Snowflake table must be fully qualified.
SELECT TABLE(RAI.jaccard_similarity(
'my_graph',
{
'node1': 1,
'rai_engine': 'my_other_rai_engine',
'result_table': '<sf_database>.<sf_schema>.my_result_table'
}
));
-- Query the results.
SELECT * FROM my_result_table;
/*+----------------------------------+
| COL1 | COL2 | COL3 |
+------+------+--------------------+
| 1 | 1 | 1.0 |
| 1 | 2 | 0.3333333333333333 |
| 1 | 3 | 0.5 |
+------+------+--------------------+ */See Also
cosine_similarity,
preferential_attachment,
adamic_adar, and
common_neighbor.
list_data_streams
list_data_streams()List all created data streams of the database link.
A data stream is identified by the fully-qualified name (opens in a new tab)
of a SQL object (i.e., table or view) in the form <database>.<schema>.<object>.
Output
| Type | Description |
|---|---|
| Variant (opens in a new tab) | List of all data streams as a JSON object. |
[
{
"account": "XXXXXXXXXXXXXXXXXX",
"createdBy": "XXXXXXXXXXXXXXXXXX",
"createdOn": "2023-05-31T11:24:09.710Z",
"dbLink": "sf_db.my_schema",
"id": "XXXXXXXXXXXXXXXXXX",
"integration": "my_integration",
"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 function list_data_streams belongs to a specific database link located in a Snowflake schema (for example, snowflake_database.RAI) and returns a list of all active data streams within this database link.
The list of data streams is returned in JSON format.
Examples
SELECT RAI.list_data_streams();See Also
get_data_stream and
create_data_stream.
list_entities
list_entities()Return a list of all available entities.
Output Table
| Type | Description | Schema |
|---|---|---|
| Table (opens in a new tab) | A table of entities represented by their name and key. | TABLE(entity_name VARCHAR, entity_key ARRAY) |
Examples
SELECT * FROM TABLE(SF_INT.LIST_ENTITIES());
/* +-------------+-----------------+
| ENTITY_NAME | ENTITY_KEY |
|-------------+-----------------|
| Client | [ |
| | "first_name", |
| | "last_name" |
| | ] |
| Product | [ |
| | "name" |
| | ] |
+-------------+-----------------+ */See Also
list_graphs
list_graphs()List all available graphs in the RAI Integration.
Output
| Type | Description |
|---|---|
| Array (opens in a new tab) | List of all available graphs as a JSON object. |
[
{
"DIRECTED": false,
"EDGE_STREAM": "my_db.my_schema.my_edge_stream",
"NAME": "my_graph",
"RAI_DATABASE": "my_rai_db"
}
]Explanation
The function list_graphs returns a list in JSON format including all available graphs.
Examples
SELECT RAI.list_graphs();See Also
is_graph_created and
get_graph.
list_graph_algorithms
list_graph_algorithms()List all available graph algorithms within the SQL Library for Snowflake.
Output
| Type | Description |
|---|---|
| Object (opens in a new tab) | List of all available graph algorithms as a JSON object. |
{
"_documentation": "https://docs.relational.ai/preview/snowflake/graph-analytics",
"basics": {
"average_degree": {
"description": "Returns the average degree of the graph."
},
"degree": {
"description": "Finds the degrees of each node in the graph."
},
"degree_histogram": {
"description": "Counts the number of nodes with each degree in the graph."
},
"max_degree": {
"description": "Returns the maximum degree of the graph."
},
"min_degree": {
"description": "Returns the minimum degree of the graph. In directed graphs, the degree of a node is the sum of its indegree and outdegree."
},
"neighbor": {
"description": "Finds the neighbors of each node in the graph."
},
"num_edges": {
"description": "Returns the number of edges in the graph."
},
...
}
}Explanation
The function list_graph_algorithms returns a list in JSON format including all available graph algorithms within the SQL Library for Snowflake.
Examples
SELECT RAI.list_graph_algorithms();See Also
list_lookups
list_entities()Return a list of all tables and entities currently covered by the lookup table.
Output
| Type | Description | Schema |
|---|---|---|
| Table (opens in a new tab) | A table of entities represented by their name and key attributes. | TABLE(entity_name VARCHAR, database_name VARCHAR, schema_name VARCHAR, table_name VARCHAR, entity_key_attrs ARRAY) |
Examples
SELECT * FROM TABLE(RAI.list_lookups());
/* +-------------+----------------+-------------+------------+------------------------+
| ENTITY_NAME | DATABASE_NAME | SCHEMA_NAME | TABLE_NAME | ENTITY_KEY_ATTRS |
|-------------+----------------+-------------+------------+------------------------|
| Client | COMMERCE_DB | ONLINE_SHOP | PURCHASE | [ |
| | | | | "CLIENT_FIRST_NAME", |
| | | | | "CLIENT_LAST_NAME" |
| | | | | ] |
| Product | COMMERCE_DB | ONLINE_SHOP | PURCHASE | [ |
| | | | | "PRODUCT_NAME" |
| | | | | ] |
+-------------+----------------+-------------+------------+------------------------+ */See Also
load_model
load_model(rai_db, rai_engine, model_name, model_path)Create a model (aka Rel code) in the RAI database.
Parameters
| Parameter | Type | Description |
|---|---|---|
rai_db | Varchar (opens in a new tab) | RAI database name. |
rai_engine | Varchar (opens in a new tab) | RAI engine name. |
model_name | Varchar (opens in a new tab) | RAI model name. |
model_path | Varchar (opens in a new tab) | The path to a file with Rel code. The path is typically a URI. For example: azure://account_name.blob.core.windows.net/container/model.rel. The model (i.e., Rel code) will be downloaded and imported into RAI. |
Output
| Type | Description |
|---|---|
| Variant (opens in a new tab) | Status of the loading model request. Example:“ok”. |
Explanation
The function load_model imports the model from the specified Azure URI provided through the model_path parameter and stores it in the RAI database.
The function load_model will download the Rel code (that is also called a model) from the specified path and import it into RAI.
Examples
SELECT RAI.load_model(
'rai_db',
'rai_engine',
'my_model',
'azure://<account_name>.blob.core.windows.net/container/model.rel'
);See Also
load_model_code and load_model_query.
load_model_code
load_model(rai_db, rai_engine, model_name, model_code)Create a model given as Rel code in the RAI database.
Parameters
| Parameter | Type | Description |
|---|---|---|
rai_db | Varchar (opens in a new tab) | RAI database name. |
rai_engine | Varchar (opens in a new tab) | RAI engine name. |
model_name | Varchar (opens in a new tab) | RAI model name. |
model_code | Varchar (opens in a new tab) | The Rel code (also known as model) that will be imported into RAI. |
Output
| Type | Description |
|---|---|
| Variant (opens in a new tab) | Status of the load model request. Example:“ok”. |
Explanation
The function load_model_code creates a model inside RAI with the given name and code.
Examples
SELECT RAI.load_model_code('rai_db', 'rai_engine', 'my_model', 'def myrange(x) = range(1, 10, 1, x)');See Also
load_model and load_model_query.
load_model_query
load_model_query(model_name, model_path)Create a model (aka Rel code) in the RAI database.
Parameters
| Parameter | Type | Description |
|---|---|---|
model_name | Varchar (opens in a new tab) | RAI model name. |
model_path | Varchar (opens in a new tab) | The path to a file with Rel code. The path is typically a URI. For example: azure://account_name.blob.core.windows.net/container/model.rel. The model (i.e., Rel code) will be downloaded and imported into RAI. |
Output
| Type | Description |
|---|---|
| Variant (opens in a new tab) | Status of the load model request. Example:“ok”. |
Explanation
The function load_model_query imports the model from the specified Azure URI provided through the model_path parameter and stores it in RAI.
The function load_model_query will download the Rel code (that is also called a model) from the specified path and import it into RAI.
Examples
SELECT RAI.load_model_query(
'my_model',
'azure://<account_name>.blob.core.windows.net/container/model.rel'
);See Also
load_model and load_model_code.
lookup
lookup(node_id)Retrieve the object description corresponding to the given node ID.
Parameters
| Parameter | Type | Description |
|---|---|---|
node_id | Integer (opens in a new tab) | A node’s integer ID. |
Output
| Type | Description |
|---|---|
| Object (opens in a new tab) | A dictionary containing values of key attributes that identify the object of the entity whose name is stored under the key type. |
Explanation
The entity name of the object is stored in the dictionary under the key type.
Before using this function, you must populate the lookup table with
create_lookup.
If this function raises the Failure during expansion or Materialized View ... is invalid errors, it is likely due to schema changes to one of the source tables used to populate and
maintain the lookup table. Use rebuild_lookup_table to repair the
lookup table and list_lookups to verify that the lookup table has
been populated by scanning the necessary tables.
Examples
SELECT LOOKUP(-5527614564291079873);
/* +------------------------------+
| LOOKUP(-5527614564291079873) |
|------------------------------|
| { |
| "first_name": "John", |
| "last_name": "Smith", |
| "type": "Client" |
| } |
+------------------------------+ */ SELECT LOOKUP(-2071496898560134469);
/* +------------------------------+
| LOOKUP(-2071496898560134469) |
|------------------------------|
| { |
| "name": "Scissors", |
| "type": "Product" |
| } |
+------------------------------+ */See Also
node, list_entities, create_lookup, and
list_lookups.
max_degree
max_degree(graph_name)
max_degree(graph_name, arguments)Return the maximum degree of a graph.
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table in which to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description |
|---|---|
| Int (opens in a new tab) | The maximum degree of the graph. |
Explanation
The function max_degree('my_graph') returns the maximum degree
over all degrees of nodes in my_graph.
If no graph with the provided name exists, an error is returned.
Note that in directed graphs, the degree of a node is the sum of its indegree and outdegree.
The max_degree function 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
Get the maximum degree a directed 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, 1), (1, 2);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Get the maximum degree of 'my_graph'.
SELECT RAI.max_degree('my_graph');
/*+---+
| 2 |
+---+ */Get the maximum degree in 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, 1), (1, 2);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges', {'directed': true});
-- Get the maximum degree of 'my_graph'.
SELECT RAI.max_degree('my_graph');
/*+---+
| 3 |
+---+ */Get the maximum degree of a graph using a different RAI engine than the engine set in the RAI context, and store the result in a Snowflake table:
-- 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');
-- Get the maximum degree of 'my_graph' using the RAI engine 'my_other_rai_engine'
-- and store the results in the Snowflake table `my_result_table`.
-- Note that the name of the Snowflake table must be fully qualified.
SELECT RAI.max_degree(
'my_graph',
{
'rai_engine': 'my_other_rai_engine',
'result_table': '<sf_database>.<sf_schema>.my_result_table'
}
);
-- Query the results.
SELECT * FROM my_result_table;
/*+------+
| COL1 |
+------+
| 2 |
+------+ */See Also
degree,
max_degree,
average_degree, and
degree_histogram.
min_degree
min_degree(graph_name)
min_degree(graph_name, arguments)Return the minimum degree of a graph.
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table in which to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description |
|---|---|
| Int (opens in a new tab) | The minimum degree of the graph. |
Explanation
The function min_degree('my_graph') returns the minimum degree
over all degrees of nodes in my_graph.
If no graph with the provided name exists, an error is returned.
Note that in directed graphs, the degree of a node is the sum of its indegree and outdegree.
The min_degree function 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
Get the minimum degree of a directed 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, 1), (1, 2);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Get the minimum degree of 'my_graph'.
SELECT RAI.min_degree('my_graph');
/*+---+
| 1 |
+---+ */Get the minimum degree of a graph using a different RAI engine than the engine set in the RAI context, and store the result in a Snowflake table:
-- 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');
-- Get the minimum degree of 'my_graph' using the RAI engine 'my_other_rai_engine'
-- and store the results in the Snowflake table `my_result_table`.
-- Note that the name of the Snowflake table must be fully qualified.
SELECT RAI.min_degree(
'my_graph',
{
'rai_engine': 'my_other_rai_engine',
'result_table': '<sf_database>.<sf_schema>.my_result_table'
}
);
-- Query the results.
SELECT * FROM my_result_table;
/*+------+
| COL1 |
+------+
| 1 |
+------+ */See Also
degree,
min_degree,
average_degree, and
degree_histogram.
neighbor
neighbor(graph_name)
neighbor(graph_name, arguments)Return the number of edges in a graph.
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 |
|---|---|---|---|
node1 | Int (opens in a new tab) / Array (opens in a new tab) | No | Instruct neighbor to return only the rows in the output table corresponding to neighbors of node1. |
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table in which to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description | Schema |
|---|---|---|
| Table (opens in a new tab) | A table representing pairs of neighbors. | TABLE(node1 INT, node2 INT) |
Explanation
The function neighbor('my_graph') returns a table
with two columns, node1 and node2,
whose rows represent pairs of neighbors in my_graph.
If no graph with the provided name exists, an error is returned.
Note that in directed graphs, a node u is a neighbor of node v
if u is either an inneighbor or outneighbor of v, or both.
The neighbor function 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
Get the neighbors of each node in a 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');
-- Get the neighbors of each node in 'my_graph'.
SELECT * FROM TABLE(RAI.neighbor('my_graph'));
/*+---------------+
| NODE1 | NODE2 |
+-------+-------+
| 1 | 2 |
| 2 | 1 |
| 2 | 3 |
| 3 | 2 |
+-------+-------+ */Get the neighbor of a specific node in a 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');
-- Get the neighbors of node 2 in 'my_graph'.
SELECT * FROM TABLE(RAI.neighbor('my_graph', {'node1': 2}));
/*+---------------+
| NODE1 | NODE2 |
+-------+-------+
| 2 | 1 |
| 2 | 3 |
+-------+-------+ */Get the neighbors of each node in a graph using a different RAI engine than the engine set in the RAI context, and store the results in a Snowflake table:
-- 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');
-- Get pairs of neighbors in 'my_graph' using the RAI engine 'my_other_rai_engine'
-- and store the results in the Snowflake table `my_result_table`.
-- Note that the name of the Snowflake table must be fully qualified.
SELECT TABLE(RAI.neighbor(
'my_graph',
{
'rai_engine': 'my_other_rai_engine',
'result_table': '<sf_database>.<sf_schema>.my_result_table'
}
));
-- Query the results.
SELECT * FROM my_result_table;
/*+--------------+
| COL1 | COL2 |
+-------+------+
| 1 | 2 |
| 2 | 1 |
| 2 | 3 |
| 3 | 2 |
+-------+------+ */See Also
node
node(entity_name, entity_key_values)Compute the node ID corresponding to an object of a given entity and identified by specific key values.
Parameters
| Parameter | Type | Description |
|---|---|---|
entity_name | Varchar (opens in a new tab) | The name of the entity. |
entity_key_values | Variant (opens in a new tab) or Array (opens in a new tab) | The key value(s) identifying an object in the scope of the entity. |
Output
| Type | Description |
|---|---|
| Number (opens in a new tab) | The integer ID of the corresponding node. |
Explanation
The function is polymorphic: It accepts either a singular value if the entity key is a
singleton, or an array of values if the entity key is composite (has multiple attributes).
Snowflake coerces almost all basic values to the Variant data type, except for character
data, which needs to be cast explicitly. For example, node('Product', 'Crayons'::VARIANT).
You should use entities that have been previously declared with
create_entity, and the number of entity key values should be the same as
the number of key attributes used in the entity declaration.
Examples
SELECT RAI.node('Client', ['John', 'Smith']);
/* +------------------------------------------+
| SF_INT.NODE('CLIENT', ['JOHN', 'SMITH']) |
|------------------------------------------|
| -5527614564291079873 |
+------------------------------------------+ */The following example shows an alternative use case where the entity has a single key attribute:
SELECT RAI.node('Order', 2095029);
/* +-------------------------------+
| SF_INT.NODE('ORDER', 2095029) |
|-------------------------------|
| -5670855959970978372 |
+-------------------------------+ */Because the Varchar (opens in a new tab) is not coerced (opens in a new tab) by Snowflake into Variant (opens in a new tab), creating the nodes of an entity with a single Varchar requires either explicitly casting Variant into Variant, or using a single element list.
SELECT RAI.node('Product', 'Scissors'::VARIANT);
/* +---------------------------------------------+
| SF_INT.NODE('PRODUCT', 'SCISSORS'::VARIANT) |
|---------------------------------------------|
| -2071496898560134469 |
+---------------------------------------------+ */
SELECT RAI.node('Product', ['Scissors']);
/* +--------------------------------------+
| SF_INT.NODE('PRODUCT', ['SCISSORS']) |
|--------------------------------------|
| -2071496898560134469 |
+--------------------------------------+ */Typically, the node function is used to define sets of edges. For instance, if you work
with a table Purchase(Client_First_Name, Client_Last_Name, Product_Name) and want to
create a graph connecting clients to their purchased products, you can define a view with the
edges as follows:
CREATE VIEW Purchase_Edge(src, dst) AS (
SELECT NODE('Client', [Client_First_Name, Client_Last_Name])
NODE('Product', [Product_Name])
FROM Purchases
);See Also
num_edges
num_edges(graph_name)
num_edges(graph_name, arguments)Return the number of edges in a graph.
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table in which to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description |
|---|---|
| Int (opens in a new tab) | The number of edges in the graph. |
Explanation
The function num_edges('my_graph') returns the number of edges in my_graph.
If no graph with the provided name exists, an error is returned.
The num_edges function 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
Get the number of edges in a 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');
-- Get the number of edges in 'my_graph'.
SELECT RAI.num_edges('my_graph');
/*+---+
| 2 |
+---+ */Get the number of edges in a graph using a different RAI engine than the engine set in the RAI context, and store the results in a Snowflake table:
-- 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');
-- Get the number of edges in 'my_graph' using the RAI engine 'my_other_rai_engine'
-- and store the results in the Snowflake table `my_result_table`.
-- Note that the name of the Snowflake table must be fully qualified.
SELECT RAI.num_edges(
'my_graph',
{
'rai_engine': 'my_other_rai_engine',
'result_table': '<sf_database>.<sf_schema>.my_result_table'
}
);
-- Query the results.
SELECT * FROM my_result_table;
/*+------+
| COL1 |
+------+
| 2 |
+------+ */See Also
num_nodes
num_nodes(graph_name)
num_nodes(graph_name, arguments)Return the number of nodes in a graph.
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table in which to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description |
|---|---|
| Int (opens in a new tab) | The number of nodes in the graph. |
Explanation
The function num_nodes('my_graph') returns the number of nodes in my_graph.
If no graph with the provided name exists, an error is returned.
The num_nodes function 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
Get the number of nodes in a 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');
-- Get the number of nodes in 'my_graph'.
SELECT RAI.num_nodes('my_graph');
/*+---+
| 3 |
+---+ */Get the number of nodes in a graph using a different RAI engine than the engine set in the RAI context, and store the results in a Snowflake table:
-- 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');
-- Get the number of nodes in 'my_graph' using the RAI engine 'my_other_rai_engine'
-- and store the results in the Snowflake table `my_result_table`.
-- Note that the name of the Snowflake table must be fully qualified.
SELECT RAI.num_nodes(
'my_graph',
{
'rai_engine': 'my_other_rai_engine',
'result_table': '<sf_database>.<sf_schema>.my_result_table'
}
);
-- Query the results.
SELECT * FROM my_result_table;
/*+------+
| COL1 |
+------+
| 3 |
+------+ */See Also
num_triangles
num_triangles(graph_name)
num_triangles(graph_name, arguments)Compute the number of unique triangles in a graph.
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table in which to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description |
|---|---|
| Int (opens in a new tab) | The number of unique triangles in the graph. |
Explanation
The function num_triangles('my_graph') returns
the number of unique triangles in my_graph.
If no graph with the provided name exists, an error is returned.
The num_triangles function 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
Compute the number of unique triangles in a 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, 3), (1, 4), (2, 1), (3, 2), (3, 4)
);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges', {'directed': true});
-- Get the number of unique triangles in 'my_graph'.
SELECT RAI.num_triangles('my_graph');
/*+---+
| 1 |
+---+ */If a graph has no triangles, the result of num_triangles is zero:
-- 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');
-- Get the number of unique triangles in 'my_graph'.
SELECT RAI.num_triangles('my_graph');
/*+---+
| 0 |
+---+ */Get the number of unique triangles in a graph using a different RAI engine than the engine set in the RAI context, and store the results in a Snowflake table:
-- 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, 3), (1, 4), (2, 1), (3, 2), (3, 4)
);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges', {'directed': true});
-- Get the number of nodes in 'my_graph' using the RAI engine 'my_other_rai_engine'
-- and store the results in the Snowflake table `my_result_table`.
-- Note that the name of the Snowflake table must be fully qualified.
SELECT RAI.num_triangles(
'my_graph',
{
'rai_engine': 'my_other_rai_engine',
'result_table': '<sf_database>.<sf_schema>.my_result_table'
}
);
-- Query the results.
SELECT * FROM my_result_table;
/*+------+
| COL1 |
+------+
| 1 |
+------+ */See Also
triangle_count, and
unique_triangle.
pagerank
pagerank(graph_name)
pagerank(graph_name, arguments)Compute the PageRank of nodes in a graph.
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 |
|---|---|---|---|
node | Int (opens in a new tab) / Array (opens in a new tab) | No | Instruct pagerank to return only the rows in the output table whose node column contains the provided value. |
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table in which to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description | Schema |
|---|---|---|
| Table (opens in a new tab) | A table containing pairs of nodes and their PageRank value. | TABLE(node INT, value FLOAT) |
Explanation
PageRank is a measure
of the centrality, or importance, of a node in a graph.
It is similar to
eigenvector_centrality,
but with an additional scaling factor.
The pagerank('my_graph') function returns a table with two columns
— node and value —
whose rows represent pairs of nodes and their PageRank value.
If no graph with the provided name exists, an error is returned.
You may get the PageRank of a single node
by providing the node ID to the node argument.
If the specified node does not exist in the graph, an empty table is returned.
The pagerank function 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
Compute the PageRank of each node in a 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute the PageRank of each node in 'my_graph'.
SELECT * FROM TABLE(RAI.pagerank('my_graph'));
/*+---------------------+
| NODE | VALUE |
+------+--------------+
| 1 | 0.2567563672 |
| 2 | 0.4864872655 |
| 3 | 0.2567563672 |
+------+--------------+ */Compute the PageRank of a single node:
-- 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute the PageRank of node 2.
SELECT * FROM TABLE(RAI.pagerank('my_graph', {'node': 2}));
/*+---------------------+
| NODE | VALUE |
+------+--------------+
| 2 | 0.4864872655 |
+------+--------------+ */Compute the PageRank of each node in a graph using a different RAI engine than the engine set in the RAI context, and store the results in a Snowflake table:
-- 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute the PageRank of each node in 'my_graph'
-- using the RAI engine 'my_other_rai_engine' and store the results
-- in the Snowflake table `my_result_table`. Note that the name of the
-- Snowflake table must be fully qualified.
SELECT TABLE(RAI.pagerank(
'my_graph',
{
'rai_engine': 'my_other_rai_engine',
'result_table': '<sf_database>.<sf_schema>.my_result_table'
}
));
-- Query the results.
SELECT * FROM my_result_table;
/*+---------------------+
| COL1 | COL2 |
+------+--------------+
| 1 | 0.2567563672 |
| 2 | 0.4864872655 |
| 3 | 0.2567563672 |
+------+--------------+ */See Also
degree_centrality and
eigenvector_centrality.
ping
ping()Ping RAI and return the ping timestamp.
Output
| Type | Description |
|---|---|
| Variant (opens in a new tab) | A timestamp of the ping time from Snowflake to RAI. |
Explanation
The function ping returns a timestamp of the ping time from Snowflake to RAI. This is used to check whether RAI is accessible and running as expected.
Examples
SELECT RAI.ping();See Also
preferential_attachment
preferential_attachment(graph_name, arguments)Compute the preferential attachment score of pairs of nodes in a graph.
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 |
|---|---|---|---|
node1 | Int (opens in a new tab) / Array (opens in a new tab) | Yes | The source node to compare other nodes to when computing preferential attachment. When provided without a node2 argument, preferential_attachment returns the similarity of node1 to all other nodes in the graph. |
node2 | Int (opens in a new tab) / Array (opens in a new tab) | No | When provided with node1, the preferential_attachment function returns the similarity between node1 and node2. |
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table in which to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description | Schema |
|---|---|---|
| Table (opens in a new tab) | A table containing pairs of nodes and their preferential attachment scores. | TABLE(node1 INT, node2 INT, score INT) |
Explanation
The preferential attachment score measures the similarity of two nodes as the product of their degrees. Higher scores indicate that two nodes are more similar than lower scores.
The preferential_attachment function returns a table with three columns
— node1, node2, and score —
whose rows contain pairs of nodes and their preferential attachment scores.
If no graph with the provided name exists, an error is returned.
Rows where the score is zero are omitted from the results.
A score of zero indicates that two nodes are neither similar nor dissimilar.
Excluding zeros from the results improves performance and prevents the need
to remove those rows in a post-processing step.
The node1 argument is required.
If a value is provided to node2 as well,
for example, if you pass the object {'node1': 1, 'node2': 2} to the arguments parameter,
then preferential_attachment returns the similarity of nodes 1 and 2.
If node2 is not provided,
the function returns the preferential attachment score of node1
and every other node in the graph.
If either the node1 or node2 nodes do not exist in the graph,
then the preferential_attachment function returns an empty table.
The preferential_attachment function 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
Compute the preferential attachment score between a given node and every other node in a 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute the preferential attachment score of node 1 and every other node in 'my_graph'.
SELECT * FROM TABLE(RAI.preferential_attachment('my_graph', {'node1': 1}));
/*+-----------------------+
| NODE1 | NODE2 | SCORE |
+-------+-------+-------+
| 1 | 1 | 4 |
| 1 | 2 | 4 |
| 1 | 3 | 2 |
+-------+-------+-------+ */Compute the preferential attachment score of two nodes in a 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute the preferential attachment scores of nodes 1 and 2.
SELECT * FROM TABLE(RAI.preferential_attachment('my_graph', {'node1': 1, 'node2': 2}));
/*+-----------------------+
| NODE1 | NODE2 | SCORE |
+-------+-------+-------+
| 1 | 2 | 4 |
+-------+-------+-------+ */Compute the preferential attachment score of each node in a graph using a different RAI engine than the engine set in the RAI context, and store the results in a Snowflake table:
-- 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2), (2, 3);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute the preferential attachment score of node 1 and every other node in 'my_graph'
-- using the RAI engine 'my_other_rai_engine' and store the results
-- in the Snowflake table `my_result_table`. Note that the name of the
-- Snowflake table must be fully qualified.
SELECT TABLE(RAI.preferential_attachment(
'my_graph',
{
'node1': 1,
'rai_engine': 'my_other_rai_engine',
'result_table': '<sf_database>.<sf_schema>.my_result_table'
}
));
-- Query the results.
SELECT * FROM my_result_table;
/*+--------------------+
| COL1 | COL2 | COL3 |
+------+------+------+
| 1 | 1 | 4 |
| 1 | 2 | 4 |
| 1 | 3 | 2 |
+------+------+------+ */See Also
jaccard_similarity,
cosine_similarity,
adamic_adar, and
common_neighbor.
shortest_path_length
shortest_path_length(graph_name, arguments)Return the length of a shortest path from a source node to one or more target nodes in a graph.
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 |
|---|---|---|---|
source | Int (opens in a new tab) / Array (opens in a new tab) | Yes | Indicates the starting node from which shortest paths are calculated. When specified without the target argument, shortest_path_length returns the lengths of shortest paths from source node to every node in the graph. |
target | Int (opens in a new tab) / Array (opens in a new tab) | No | When specified with a source node, shortest_path_length returns the length of a shortest path from the source node to the target node. |
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table in which to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description | Schema |
|---|---|---|
| Table (opens in a new tab) | A table containing the shortest path length between pairs of nodes. | TABLE(source INT, target INT, length INT) |
Explanation
The function shortest_path_length returns a table with three columns
— source, target, and length —
whose rows represent the length of a shortest path
from a source node to a target node.
If no graph with the provided name exists, an error is returned.
A source node is required.
You may also specify a target node.
For example, if you pass the object {'source': 1, 'target': 5} to the arguments parameter,
then shortest_path_length returns the length of a shortest path
from node 1 to node 5.
If no target node is provided,
the function returns the lengths of shortest paths
from the source node to every node in the graph
that is reachable from the source node.
If either the source node or target node do not exist in the graph,
then the shortest_path_length function returns an empty table.
If the target node is unreachable from the source node,
the function also returns an empty table.
The shortest_path_length function 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
Get the length of a shortest path between two nodes in a 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 two 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');
-- Get the length of a shortest path between node 1 and node 3
-- and display the full table result.
SELECT * FROM TABLE(RAI.shortest_path_length('my_graph', {'source': 1, 'target': 3}));
/*+--------------------------+
| SOURCE | TARGET | LENGTH |
+--------+--------+--------+
| 1 | 3 | 2 |
+--------+--------+--------+ */
-- Select only the length from the result.
SELECT length FROM TABLE(RAI.shortest_path_length('my_graph', {'source': 1, 'target': 3}));
/*+--------+
| LENGTH |
+--------+
| 2 |
+--------+ */Get the length of a shortest path from a source node to all reachable nodes:
-- 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 1), (1, 2);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges', {'directed': true});
-- Get the lengths of a shortest path from node 1 in 'my_graph'
-- to all nodes reachable from node 1.
SELECT * FROM TABLE(RAI.shortest_path_length('my_graph', {'source': 1}));
/*+--------------------------+
| SOURCE | TARGET | LENGTH |
+--------+--------+--------+
| 1 | 1 | 0 |
| 1 | 2 | 1 |
| 1 | 3 | 2 |
+--------+--------+--------+ */Get the length of a shortest path from a source node to all reachable nodes using a different RAI engine than the engine set in the RAI context, and store the results in a Snowflake table:
-- 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');
-- Get the lengths of a shortest path from node 1 in 'my_graph'
-- to all nodes reachable from node 1 using the RAI engine 'my_other_rai_engine'
-- and store the results in the Snowflake table `my_result_table`.
-- Note that the name of the Snowflake table must be fully qualified.
SELECT TABLE(RAI.shortest_path_length(
'my_graph',
{
'source': 1,
'rai_engine': 'my_other_rai_engine',
'result_table': '<sf_database>.<sf_schema>.my_result_table'
}
));
-- Query the results.
SELECT * FROM my_result_table;
/*+--------------------+
| COL1 | COL2 | COL3 |
+------+------+------+
| 1 | 1 | 0 |
| 1 | 2 | 1 |
| 1 | 3 | 2 |
+------+------+------+ */See Also
transitive_closure
transitive_closure(graph_name, arguments)Computes the transitive closure of the edges in a graph and may be used to determine which nodes are reachable from each node in the graph.
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 |
|---|---|---|---|
source | Int (opens in a new tab) / Array (opens in a new tab) | Yes | Indicates the starting node from which transitive closure is calculated. When specified without the target argument, transitive_closure returns all nodes reachable from the source node. |
target | Int (opens in a new tab) / Array (opens in a new tab) | No | When specified with a source node, transitive_closure can be used to determine if the target node is reachable from the source node. If target is reachable from source, then transitive_closure returns a non-empty result. Otherwise, it returns an empty table. |
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table in which to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description | Schema |
|---|---|---|
| Table (opens in a new tab) | A table containing pairs of reachable nodes. | TABLE(source INT, target INT) |
Explanation
The transitive_closure function
computes the transitive closure of the edges in a graph.
It is used to determine if a node is reachable from another node
and returns a table with two columns
— source and target —
whose rows represent pairs of nodes
where the target node is reachable from the source node
in a specified graph.
If no graph with the provided name exists, an error is returned.
A source node is required.
You may also specify a target node.
When both a source and target node a specified,
the transitive_closure function can be used to determine
whether or not the target node is reachable from the source node.
For example,
if you pass the object {'source': 1, 'target': 5} to the arguments parameter,
then transitive_closure returns a table with a single row
if node 5 is reachable from node 1,
and returns an empty table if it is unreachable.
If no target node is provided,
the function returns every node in the graph that is reachable from the source node.
If either the source node or target node do not exist in the graph,
then the transitive_closure function returns an empty table.
If the target node is unreachable from the source node,
the function also returns an empty table.
The transitive_closure function 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
Determine if a node is reachable from another node in a 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 2), (3, 4);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Check whether or not node 2 is reachable from node 1.
-- The output is non-empty.
SELECT * FROM TABLE(RAI.transitive_closure('my_graph', {'source': 1, 'target': 2}));
/*+-----------------+
| SOURCE | TARGET |
+--------+--------+
| 1 | 2 |
+--------+--------+ */
-- Check whether or not node 3 is reachable from node 1.
SELECT length FROM TABLE(RAI.transitive_closure('my_graph', {'source': 1, 'target': 3}));
/*+-----------------+
| SOURCE | TARGET |
+--------+--------+
| Empty result |
+--------+--------+ */Get every node reachable from a given node:
-- 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 2), (3, 4);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Get all nodes in 'my_graph' reachable from node 1.
SELECT * FROM TABLE(RAI.transitive_closure('my_graph', {'source': 1}));
/*+-----------------+
| SOURCE | TARGET |
+--------+--------+
| 1 | 2 |
| 1 | 3 |
+--------+--------+ */Get every node reachable from a given node using a different RAI engine than the engine set in the RAI context, and store the results in a Snowflake table:
-- 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 2), (3, 4);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Get all nodes reachable from node 1 using the RAI engine 'my_other_rai_engine'
-- and store the results in the Snowflake table `my_result_table`.
-- Note that the name of the Snowflake table must be fully qualified.
SELECT TABLE(RAI.transitive_closure(
'my_graph',
{
'source': 1,
'rai_engine': 'my_other_rai_engine',
'result_table': '<sf_database>.<sf_schema>.my_result_table'
}
));
-- Query the results.
SELECT * FROM my_result_table;
/*+-------------+
| COL1 | COL2 |
+------+------+
| 1 | 2 |
| 1 | 3 |
+------+------+ */See Also
triangle_count
triangle_count(graph_name)
triangle_count(graph_name, arguments)Count the number of unique triangles to which each node in a graph belongs.
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 |
|---|---|---|---|
node | Int (opens in a new tab) / Array (opens in a new tab) | No | Instruct triangle_count to return only the rows in the output table whose node column contains the provided value. |
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description | Schema |
|---|---|---|
| Table (opens in a new tab) | A table whose rows contain the number of unique triangles to which a node belongs. | TABLE(node INT, count INT) |
Explanation
The function triangle_count('my_graph') returns a table
with two columns, node and count,
whose rows represent the number of unique triangles in the graph
to which each node belongs.
If no graph with the provided name exists, an error is returned.
The triangle_count function 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
Count the unique triangles to which each node in a graph belongs:
-- 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), (2, 4), (3, 1), (3, 4), (5, 1)
);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges', {'directed': true});
-- Count the number of unique triangles in 'my_graph' to which each node belongs.
SELECT * FROM TABLE(RAI.triangle_count('my_graph'));
/*+--------------+
| NODE | COUNT |
+------+-------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 0 |
| 5 | 0 |
+------+-------+ */Compute the number of unique triangles to which a given node belongs:
-- 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), (2, 4), (3, 1), (3, 4), (5, 1)
);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges', {'directed': true});
-- Count the number of unique triangles in 'my_graph' to which each node belongs.
SELECT * FROM TABLE(RAI.triangle_count('my_graph', {'node': 2}));
/*+--------------+
| NODE | COUNT |
+------+-------+
| 2 | 1 |
+------+-------+ */Count the unique triangles to which each node in a graph belongs using a different RAI engine than the one set in the RAI context, and store the results in a Snowflake table:
-- 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), (2, 4), (3, 1), (3, 4), (5, 1)
);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges', {'directed': true});
-- Count the number of unique triangles in 'my_graph' to which
-- each node belongs using the RAI engine 'my_other_rai_engine'
-- and store the results in the Snowflake table `my_result_table`.
-- Note that the name of the Snowflake table must be fully qualified.
SELECT TABLE(RAI.triangle_count(
'my_graph',
{
'rai_engine': 'my_other_rai_engine',
'result_table': '<sf_database>.<sf_schema>.my_result_table'
}
));
-- Query the results.
SELECT * FROM my_result_table;
/*+-------------+
| COL1 | COL2 |
+------+------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 0 |
| 5 | 0 |
+------+------+ */See Also
num_triangles, and
unique_triangle.
unique_triangle
unique_triangle(graph_name)
unique_triangle(graph_name, arguments)Compute triples of nodes, unique up to order, that form a triangle in a graph.
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 |
|---|---|---|---|
node1 | Int (opens in a new tab) / Array (opens in a new tab) | No | Instruct unique_triangle to return only the rows in the output table whose node1 column contains the given node. |
node2 | Int (opens in a new tab) / Array (opens in a new tab) | No | Instruct unique_triangle to return only the rows in the output table whose node2 column contains the given node.. |
node3 | Int (opens in a new tab) / Array (opens in a new tab) | No | Instruct unique_triangle to return only the rows in the output table whose node3 column contains the given node. |
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table in which to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description | Schema |
|---|---|---|
| Table (opens in a new tab) | A table whose rows contain triples of nodes forming unique triangles in a graph. | TABLE(node1 INT, node2 INT, node3 INT) |
Explanation
The unique_triangle function returns a table with three columns
— node1, node2, and node3 —
whose rows indicate that node1, node2, and node3
form a unique triangle in a graph.
If no graph with the provided name exists, an error is returned.
In undirected graphs, the uniqueness of each triangle is guaranteed
because the nodes of each triange are returned so that node1 < node2 < node3.
In directed graphs, the output table only contains rows for which
node1 < node2, node1 < node3, and node2 != node3.
For example, the table rows 1, 2, 3 and 1, 3, 2 represent
two unique directed triangles.
If any of node1, node2, or node3 do not exist in the graph,
then the unique_triangle function returns an empty table.
The unique_triangle function 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
Compute all of the unique triangles in 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS (
SELECT * FROM VALUES (1, 3), (2, 1), (2, 4), (3, 2), (3, 4)
);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges', {'directed': true});
-- Compute all of the unique triangles in 'my_graph'.
SELECT * FROM TABLE(RAI.unique_triangle('my_graph'));
/*+-----------------------+
| NODE1 | NODE2 | NODE3 |
+-------+-------+-------+
| 1 | 3 | 2 |
+-------+-------+-------+ */Compute all of the unique triangles in 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS (
SELECT * FROM VALUES (1, 3), (2, 1), (2, 4), (3, 2), (3, 4)
);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute all of the unique triangles in 'my_graph'.
SELECT * FROM TABLE(RAI.unique_triangle('my_graph'));
/*+-----------------------+
| NODE1 | NODE2 | NODE3 |
+-------+-------+-------+
| 1 | 2 | 3 |
| 2 | 3 | 4 |
+-------+-------+-------+ */Given two nodes, compute the nodes forming unique triangles with the given nodes:
-- 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS (
SELECT * FROM VALUES (1, 3), (2, 1), (2, 4), (3, 2), (3, 4)
);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Find all unique triangles in 'my_graph' starting with node 1 and node 3.
SELECT * FROM TABLE(RAI.unique_triangle('my_graph', {'node1': 1, 'node2': 3}));
/*+-----------------------+
| NODE1 | NODE2 | NODE3 |
+-------+-------+-------+
| 1 | 3 | 2 |
+-------+-------+-------+ */Determine whether or not three nodes form a unique triangle:
-- 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS (
SELECT * FROM VALUES (1, 3), (2, 1), (2, 4), (3, 2), (3, 4)
);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Determine whether or not nodes 1, 3, and 2 form a unique triangle in 'my_graph'.
-- Note that the output table is non-empty, since there is a unique triange
-- containing nodes 1, 3, and 2.
SELECT * FROM TABLE(
RAI.unique_triangle('my_graph', {'node1': 1, 'node2': 3, 'node3': 2})
);
/*+-----------------------+
| NODE1 | NODE2 | NODE3 |
+-------+-------+-------+
| 1 | 3 | 2 |
+-------+-------+-------+ */
-- Determine whether or not nodes 1, 2, and 3 form a unique triangle in 'my_graph'.
-- Note that the output table is empty, since there is no unique triangle
-- containing nodes 1, 2, and 3 in that order.
SELECT * FROM TABLE(
RAI.unique_triangle('my_graph', {'node1': 1, 'node2': 2, 'node3': 3})
);
/*+-----------------------+
| NODE1 | NODE2 | NODE3 |
+-------+-------+-------+
| Empty table |
+-------+-------+-------+ */Compute all of the unique triangles in an undirected graph using a different RAI engine than the engine set in the RAI context, and store the results in a Snowflake table:
-- 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS (
SELECT * FROM VALUES (1, 3), (2, 1), (2, 4), (3, 2), (3, 4)
);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Find the common neighbors of node 1 and every other node in 'my_graph'
-- using the RAI engine 'my_other_rai_engine' and store the results
-- in the Snowflake table `my_result_table`. Note that the name of the
-- Snowflake table must be fully qualified.
SELECT TABLE(RAI.unique_triangle(
'my_graph',
{
'rai_engine': 'my_other_rai_engine',
'result_table': '<sf_database>.<sf_schema>.my_result_table'
}
));
-- Query the results.
SELECT * FROM my_result_table;
/*+-----------------------+
| NODE1 | NODE2 | NODE3 |
+-------+-------+-------+
| 1 | 2 | 3 |
| 2 | 3 | 4 |
+-------+-------+-------+ */See Also
num_triangles, and
triangle_count.
weakly_connected_component
weakly_connected_component(graph_name)
weakly_connected_component(graph_name, arguments)Computes the weakly connected components of a graph.
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 |
|---|---|---|---|
node | Int (opens in a new tab) / Array (opens in a new tab) | No | When provided, the function returns the index of the component to which node belongs. |
component | Int (opens in a new tab) / Array (opens in a new tab) | No | When provided without node, the function returns all nodes in the given component. Use node and component together to check whether or not a given node belongs to a given component. |
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. |
result_table | Varchar (opens in a new tab) | No | The fully qualified name of the Snowflake table in which to store results. If this argument is provided, the function returns an empty table. |
Output
| Type | Description | Schema |
|---|---|---|
| Table (opens in a new tab) | A table containing nodes and the index of the weakly connected component to which they belong. | TABLE(node INT, component INT) |
Explanation
The weakly_connected_component('my_graph') function
computes the
weak component (opens in a new tab)
to which each node in the graph belongs.
The component ID is the minimum ID of any node belonging to the component.
If no graph with the provided name exists, an error is returned.
Both directed and undirected graphs are supported
by the weakly_connected_component function.
In undirected graphs, weakly connected components are the same
as connected components.
The weakly_connected_component function 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
Compute the weakly connected components of a 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 2), (3, 4);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Compute the weakly connected components of 'my_graph'.
SELECT * FROM TABLE(RAI.weakly_connected_component('my_graph'));
/*+------------------+
| NODE | COMPONENT |
+------+-----------+
| 1 | 1 |
| 2 | 1 |
| 3 | 3 |
| 4 | 3 |
+------+-----------+ */Find the weakly connected component to which a given node belongs:
-- 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 2), (3, 4);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges', {'directed': true});
-- Find the weakly connected component of 'my_graph' containing node 2.
SELECT * FROM TABLE(RAI.weakly_connected_component('my_graph', {'node': 2}));
/*+------------------+
| NODE | COMPONENT |
+------+-----------+
| 2 | 1 |
+------+-----------+ */Find all nodes in a given component of a 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 2), (3, 4);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Find all nodes in 'my_graph' belonging to component 3.
SELECT * FROM TABLE(RAI.weakly_connected_component('my_graph', {'component': 3}));
/*+------------------+
| NODE | COMPONENT |
+------+-----------+
| 3 | 3 |
| 4 | 3 |
+------+-----------+ */Determine whether or not a given node belongs to a given component:
-- 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 two nodes and two edges.
CREATE TABLE my_edges(x INT, y INT) AS SELECT * FROM VALUES (1, 2), (3, 4);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Check whether or not node 2 belongs to component 1.
-- Note that the output table is non-empty, since node 2 belongs to component 1.
SELECT * FROM TABLE(
RAI.weakly_connected_component('my_graph', {'node': 2, 'component': 1})
);
/*+------------------+
| NODE | COMPONENT |
+------+-----------+
| 2 | 1 |
| 4 | 3 |
+------+-----------+ */
-- Check whether or not node 2 belongs to component 3.
-- Note that the output table is empty, since node 2 does not belong to component 3.
SELECT * FROM TABLE(
RAI.weakly_connected_component('my_graph', {'node': 2, 'component': 1})
);
/*+------------------+
| NODE | COMPONENT |
+------+-----------+
| Empty table |
+------+-----------+ */Compute the weakly connected components of a graph using a different RAI engine than the engine set in the RAI context, and store the results in a Snowflake table:
-- 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), (3, 4);
CALL RAI.create_data_stream('my_edges');
CALL RAI.create_graph('my_graph', 'my_edges');
-- Get the number of edges in 'my_graph' using the RAI engine 'my_other_rai_engine'
-- and store the results in the Snowflake table `my_result_table`.
-- Note that the name of the Snowflake table must be fully qualified.
SELECT TABLE(RAI.weakly_connected_component(
'my_graph',
{
'rai_engine': 'my_other_rai_engine',
'result_table': '<sf_database>.<sf_schema>.my_result_table'
}
));
-- Query the results.
SELECT * FROM my_result_table;
/*+-------------+
| COL1 | COL2 |
+------+------+
| 1 | 1 |
| 2 | 1 |
| 3 | 3 |
| 4 | 3 |
+------+------+ */See Also