Quick Start for Snowflake Administrators

Get Ready

To set up the RAI Integration Services for Snowflake, you need to:

• Have a Snowflake account and be member of the ACCOUNTADMIN role.
• Have a RAI account and be an administrator in this account.

In this quick start you will predominantly use SQL and the RAI CLI to set up the RAI Integration Services.

As you go through it, be aware of the following:

• Fields written with angle brackets <> indicate that you need to replace them with the actual value in your environment.
• RAI resources, unlike Snowflake resources, are case-sensitive.

Create Snowflake Resources

One of the first steps in setting up the RAI Integration Services is to decide which database to contain them in. If you don’t want to use an existing database, just create one:

USE ROLE sysadmin;
CREATE DATABASE my_sf_db;
CREATE WAREHOUSE my_sf_wh;

Here, you are also creating a warehouse that is needed to be able to execute some SQL commands as part of this setup.

Create User Role and Proxy User

Next, create a user role to own the RAI Integration Services within Snowflake:

USE ROLE accountadmin;
CREATE ROLE integration_user_role;
GRANT ROLE integration_user_role TO ROLE sysadmin;

The SYSADMIN role inherits all the privileges that are granted to integration_user_role. This ensures that system and account admins are able to manage the resources created by this new user role. More information on role hierarchy (opens in a new tab) can be found in the Snowflake documentation.

In the later step Grant Permissions, you will use this user role to grant all the necessary permissions to the RAI Integration Services.

It’s best practice to create a proxy user, my_sf_proxy_user, at this stage. This proxy will execute commands in the background, which is needed to provide key functionality of the RAI Integration Services. This user should be added to the role integration_user_role you just created.

USE ROLE accountadmin;
CREATE USER my_sf_proxy_user PASSWORD = '<sf_proxy_password>';
GRANT ROLE integration_user_role TO USER my_sf_proxy_user;

Create a RAI OAuth Client

Before you can create a RAI integration with the RAI CLI, you need to create a RAI OAuth client for your admin role.

To create this client, log into the RAI Console (opens in a new tab), go to Settings, select OAuth clients, and click Create. For details on creating OAuth clients, see Managing Users and OAuth Clients in the RAI Console.

Give the client a name and select all transaction, engine, database, and OAuth permissions.

Once created, the OAuth client ID and secret are displayed on the right-hand side. You will need them in the next step, where you will set up a RAI profile for your admin role.

RAI Config File

To use an OAuth client with the RAI CLI, you need to store its credentials in a RAI profile in your RAI config file located at <HOME_DIR>/.rai/config:

[rai-admin-profile]
region = us-east
host = azure.relationalai.com
port = 443
client_id = <your_client_id>
client_secret = <your_client_secret>

Replace <your_client_id> and <your_client_secret> with the actual values for the OAuth client you just created.

Install the RAI CLI

You need the RAI CLI (opens in a new tab) to be able to create a RAI integration with Snowflake. To install the CLI, download the latest release (opens in a new tab) for your OS. For more information, see the RelationalAI CLI.

You can run the following command to see all the commands available:

rai --help

You will see several Snowflake-related commands, such as create-snowflake-integration.

Now it is time to create the RAI integration.

Create a RAI Integration

To create a RAI integration named myintegration, execute the following command:

rai create-snowflake-integration myintegration  \
    --account <sf_account_name>                 \
    --admin-username <sf_username>              \
    --admin-password <sf_password>              \
    --proxy-username my_sf_proxy_user           \
    --proxy-password "<sf_proxy_password>"      \
    --profile rai-admin-profile

If successful, you will get a response that includes all the integration information in JSON format:

Create Snowflake integration 'myintegration' account='<sf_account_name>' ...
Ok (5.0s)
{
    "id": "******",
    "kind": "SNOWFLAKE",
    "name": "myintegration",
    "account": "<rai_account_name>",
    "createdBy": "**********@clients",
    "createdOn": "2023-**-**T**:**:**.***Z",
    "state": "CREATED",
    "consentUrl": "https://******",
    "snowflake": {
        "account": "<sf_account_name>"
    }
}

The admin-related options refer to your Snowflake admin account. The proxy-related options refer to the proxy user my_sf_proxy_user you created at the beginning.

The options for your rai create-snowflake-integration command are explained in the table below.

The RAI integration myintegration consists of two Snowflake integrations — a Snowflake API integration (opens in a new tab) and Snowflake storage integration (opens in a new tab).

The API integration myintegration has the same name as the RAI integration. The storage integration myintegration_storint has the suffix _storint added to its name.

Grant Permissions

Once the RAI integration is created, it’s time to grant (opens in a new tab) the user role integration_user_role all necessary permissions.

First, the following permissions need to be granted so that the integration can perform all the necessary operational functions:

USE ROLE accountadmin;
 
-- Assign permissions on integration objects to user role.
GRANT USAGE ON INTEGRATION myintegration TO integration_user_role;
GRANT USAGE ON INTEGRATION myintegration_storint TO integration_user_role;
 
-- Grant permissions to manage and execute tasks
GRANT EXECUTE managed task ON account TO integration_user_role;
GRANT EXECUTE task ON account TO integration_user_role;

In particular, the role requires access to the integrations associated with the RAI integration myintegration. It also needs to be able to manage and execute tasks in order to create RAI database links.

The user role integration_user_role also needs permissions to work with a warehouse and the database that will hold RAI database link, which will give access users the functionality of the RAI Integration Services.

USE ROLE accountadmin;
 
-- Grant usage and schema creation permissions
-- to the DB that will hold RAI database link.
GRANT USAGE ON DATABASE my_sf_db TO ROLE integration_user_role;
GRANT CREATE SCHEMA ON DATABASE my_sf_db TO ROLE integration_user_role;
 
-- Grant usage permission to a warehouse
GRANT USAGE ON WAREHOUSE my_sf_wh TO ROLE integration_user_role;

Create a RAI Database Link

Once the RAI integration is set up and access has been granted, you can create a RAI database link. A database link installs the SQL Library for Snowflake in a specific Snowflake database and schema. The SQL Library allows the Snowflake user to use the RAI integration within the Snowflake environment.

To create a RAI database link and install the SQL Library for Snowflake in the schema my_sf_db.rai, execute the following command:

rai create-snowflake-database-link myintegration  \
    --database my_sf_db                           \
    --role integration_user_role                  \
    --profile rai-admin-profile                   \
    --username <sf_username>                      \
    --password <sf_password>

By default, the RAI database link installs the SQL Library in the schema rai. With the option --schema, you can select any other schema.

By specifying the Snowflake user role integration_user_role, you ensure that the SQL Library for Snowflake is accessible to members of this user role.

The SQL Library contains UDF functions and procedures. You can verify successful installation by running the following commands:

USE DATABASE my_sf_db;
 
SHOW PROCEDURES IN SCHEMA RAI;
/*+------------+--------------------+-------------+ ... +-------------------------------------------------------------------+ ...
  | created_on |        name        | schema_name | ... | description                                                       | ...
  |------------+--------------------+-------------+ ... +-------------------------------------------------------------------+ ...
  |    ****    | create_data_stream |     RAI     | ... | Automatically created object for RAI integration "myintegration". | ...
  |    ...     |         ...        |     ...     | ... | ....                                                              | ...
 */
 
SHOW USER FUNCTIONS IN SCHEMA RAI;
/*+------------+-------------+-------------+ ... +-------------------------------------------------------------------+ ...
  | created_on |    name     | schema_name | ... | description                                                       | ...
  |------------+-------------+-------------+ ... +-------------------------------------------------------------------+ ...
  |    ****    | ADAMIC_ADAR |     RAI     | ... | Automatically created object for RAI integration "myintegration". | ...
  |    ...     |    ...      |     ...     | ... | ....                                                              | ...
*/

To ensure that the procedures and functions you see are part of the SQL Library of the RAI integration, look at the description column. It reads Automatically created object for RAI integration "myintegration" for all functions and procedures associated with a RAI integration.

Create a RAI Database and Engine

The RAI resources database and engine need to be set up. Without them, users won’t be able to create graphs from their SQL data or execute graph algorithms.

To create a RAI database and an engine, run the commands:

USE ROLE sysadmin;
USE warehouse my_sf_wh;
 
SELECT RAI.create_rai_database('my_rai_db');
/*+------+
  | "ok" |
  +------+ */
 
SELECT RAI.create_rai_engine('my_rai_engine', 'S');
/*+------+
  | "ok" |
  +------+ */

Here you create a RAI database my_rai_db and a RAI engine my_rai_engine of size S. Creating an engine may take up to one minute.

Add Users to the User Integration Role

To add individual users to integration_user_role, execute the GRANT ROLE (opens in a new tab) command:

USE ROLE accountadmin;
GRANT ROLE integration_user_role TO USER <user1>;
GRANT ROLE integration_user_role TO USER <user2>;

You can also grant integration_user_role to other user roles in your organization.

USE ROLE accountadmin;
GRANT ROLE integration_user_role TO ROLE <other_user_role>;

Provide the Integration Information

Provide users with the following information so that they can access the RAI Integration Services.

Users do not need the integration name to get started. The database link location is sufficient.

Finish

Users are now able to access and use the RAI Integration Services without any further assistance from an admin.