Quick Start for Snowflake Administrators

Get Ready

Before you begin, make sure that you are:

• A member of the ACCOUNTADMIN role in Snowflake.
• An administrator on the RAI platform.

To go through this quick start, you will predominantly use SQL and the RAI CLI.

Fields written with angle brackets <> indicate that you need to replace them with the actual value in your environment.

Create Snowflake Resources

One of the first steps to set up the RAI Integration Services is to identify a dedicated database and user role. This Quick Start will show you how to do this. Users will also need a warehouse if they want to complete the Quick Start for Snowflake Users.

To create a new database and an X-Small warehouse, run:

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

Create a Dedicated User Role

To create a new user role, execute the following commands:

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.

Ensure that this role has all the necessary permissions to create database link and manage tasks:

USE ROLE accountadmin;
 
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 ON ALL SCHEMAS IN DATABASE my_sf_db TO ROLE integration_user_role;
 
GRANT OPERATE ON WAREHOUSE my_sf_wh TO ROLE integration_user_role;
GRANT USAGE ON WAREHOUSE my_sf_wh TO ROLE integration_user_role;
 
GRANT EXECUTE managed task ON account TO integration_user_role;
GRANT EXECUTE task ON account TO integration_user_role;

Other permissions specific to the RAI integration are granted at a later stage.

Create a Proxy User

Next, create a Snowflake user rai_proxy_user with a password. This Snowflake user will act as a proxy executing commands from the RAI Integration Services. You should also add this user to the role integration_user_role you just created.

USE ROLE accountadmin;
CREATE USER rai_proxy_user PASSWORD = '<rai_proxy_password>';
GRANT ROLE integration_user_role TO USER rai_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 (macOS (opens in a new tab), Linux (opens in a new tab), and Windows (opens in a new tab)). 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 rai_proxy_user       \
  --proxy-password "<rai_proxy_password>" \
  --profile rai-admin-profile

The admin-related options refer to your Snowflake admin account. The proxy-related options refer to the proxy user rai_proxy_user you created in step 4.

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

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>"
  }
}

Now, you need to contact RAI support and request authorization in your RAI account. You need to provide your RAI account and the RAI integration name.

This needs to happen after you have set up the RAI integration but before you create the database link.

Grant Permissions

To grant users access to the RAI integration you just created, provide all the necessary permissions to the user role integration_user_role, and add the users as members of this role.

Grant Usage Permissions to the User Role

To grant usage permissions, execute the following GRANT USAGE (opens in a new tab) commands:

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;

The RAI integration myintegration consists of two 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.

The user role also needs broad permissions for the target schema my_sf_db.RAI of the RAI database link that you will create in the next step.

USE ROLE accountadmin;
-- Grant broad permissions to operate on the target schema of the database link.
GRANT ALL ON SCHEMA my_sf_db.RAI TO ROLE integration_user_role;

Add Users to the User 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>;

Create a 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:

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.

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.