-- # RelationalAI Native App Setup Guide

-- > 🚨 We recommend **running each SQL cell separately** instead of using the
-- "Run All" feature.

-- ## Step 1 - Share Continuous Telemetry Data

-- To receive support from RelationalAI, you must opt-in to share continuous
-- telemetry data. Telemetry is written to your account’s active event table.
-- This telemetry contains operational information such as internal system
-- logs or engine sizes and usage data such as model attributes or obfuscated
-- query plan information. Customer data and personally identifiable
-- information are not included in continuous telemetry data.

-- > 🚨 Note that the `ACCOUNTADMIN` role is used in this guide. This role is
-- needed only for the event-table related operations. To manage roles
-- specific the RelationalAI Native App, see Appendix 3 at the bottom of this
-- notebook.

USE ROLE ACCOUNTADMIN;

-- ### Step 1A - Check whether active event table exists

-- Use the SHOW PARAMETERS command to determine if you have an active event
-- table.

-- If the `event_table` parameter is set, the command returns the name of the
-- active event table. In that case, skip step 1B and move to step 1C.

SHOW PARAMETERS LIKE 'event_table' in ACCOUNT;

-- ### Step 1B - Create event table

-- An empty result indicates that no event table is set, you therefore need
-- to create one.

-- > 💡 You can customize the database, schema and table name below as needed.
-- Default values have been provided.

SET event_db_name = 'TELEMETRY';
SET event_schema_name = 'PUBLIC';
SET event_table_name = 'EVENTS';

-- Define additional helper variables
SET event_db_schema = $event_db_name || '.' || $event_schema_name;
SET event_db_schema_table = $event_db_name || '.' || $event_schema_name || '.' || $event_table_name;

-- Create event database, schema and table
CREATE DATABASE IF NOT EXISTS IDENTIFIER($event_db_name);
CREATE SCHEMA IF NOT EXISTS  IDENTIFIER($event_db_schema);
CREATE EVENT TABLE IF NOT EXISTS IDENTIFIER($event_db_schema_table);

ALTER ACCOUNT SET EVENT_TABLE = $event_db_schema_table;

-- ### Step 1C - Enable telemetry sharing with RelationalAI

-- By running the next code block, you consent to and enable sharing
-- continuous telemetry data with RelationalAI.

ALTER APPLICATION relationalai SET SHARE_EVENTS_WITH_PROVIDER = TRUE;

-- ## Step 2 - Configure Compute Resources

-- Dedicated compute pools are required for users to consume the RAI service
-- via the relationalai Python package. These compute pools host RAI engines,
-- which are the compute resources that execute RAI queries. There are two
-- engine sizes available, each corresponding to a different compute pool
-- instance family: `HIGHMEM_X64_S` and `HIGHMEM_X64_M`.

-- Engines, not compute pools, incur costs, and the required engine size
-- depends on the workload. Users must choose a compatible compute pool when
-- creating engines. To ensure availability, you may wish to create a compute
-- pool for each engine size. However, you may start with only a
-- `HIGHMEM_X64_S` compute pool and create a `HIGHMEM_X64_M` compute pool
-- later if needed.

-- > ⭐ Notice how the `MAX_NODES` attribute is set to 1 by default. This is
-- sufficient for the `rai_service_pool`, but depending on your use case, you
-- might want to adjust it for the other compute pools. You can do this at
-- any point in time by running the `ALTER COMPUTE POOL` command.

-- > ⭐ Adjusting the `AUTO_SUSPEND_SECS` parameter helps balance cost control
-- with availability for engine provisioning. See documentation on Cost
-- Management for details on how costs are accrued.

-- Create compute pools for the RAI engines, one for each supported instance family
CREATE COMPUTE POOL IF NOT EXISTS rai_engine_pool_s
      FOR APPLICATION relationalai
      MIN_NODES = 1
      MAX_NODES = 1
      AUTO_RESUME = TRUE
      AUTO_SUSPEND_SECS = 300
      INSTANCE_FAMILY = HIGHMEM_X64_S;

GRANT USAGE, MONITOR ON COMPUTE POOL rai_engine_pool_s TO APPLICATION relationalai;

CREATE COMPUTE POOL IF NOT EXISTS rai_engine_pool_m
      FOR APPLICATION relationalai
      MIN_NODES = 1
      MAX_NODES = 1
      AUTO_RESUME = TRUE
      AUTO_SUSPEND_SECS = 300
      INSTANCE_FAMILY = HIGHMEM_X64_M;

GRANT USAGE, MONITOR ON COMPUTE POOL rai_engine_pool_m TO APPLICATION relationalai;

-- ## Step 3 - Create the RAI service

-- Execute the following command to create the RAI service:

CALL RELATIONALAI.APP.CREATE_SERVICE();

-- After approximately 30 seconds, you can check that the service is running:

CALL RELATIONALAI.APP.SERVICE_STATUS();

-- > 🚨 **IMPORTANT**
-- > While RelationalAI is in preview mode, you must upgrade the application
-- weekly after RAI releases an updated native app. Please review the
-- Upgrades section of the RelationalAI Native App documentation and
-- subscribe to the release notes to receive notifications about new
-- versions.

-- ## Step 4 - Setting up Change Data Capture

-- Streams share Snowflake data with the RAI Native App using change data
-- capture (CDC) to capture source table and view changes once every minute.

-- To enable CDC, an engine needs to be configured to be the CDC engine.

-- We start by creating an engine of size `HIGHMEM_X64_S` that we call
-- `demo_engine`.

CALL RELATIONALAI.API.CREATE_ENGINE('demo_engine', 'rai_engine_pool_s', 'HIGHMEM_X64_S');

-- Once the engine creation has finished (this can take anywhere between 1
-- and 5 minutes), all we need to do is set this engine to be the CDC engine.

CALL RELATIONALAI.APP.SETUP_CDC('demo_engine');

-- ## Congratulations! Your RelationalAI app is ready to use.

-- ### Next Steps

-- To get up and running with the RelationalAI native app, download the
-- Simple Start Jupyter notebook and follow the instructions for running the
-- notebook.

-- For a more detailed example and more information about the RelationalAI
-- Python library, check out the Getting Started guide.

-- Links:

-- - Simple Start Notebook:
-- https://relational.ai/notebooks/simple-start.ipynb
-- - Example Notebooks: https://relational.ai/docs/example_notebooks
-- - Docs: https://relational.ai/docs

-- ## APPENDIX 1 - Suspend or drop the RAI Service and deleting all engines

-- Suspending the RAI service temporarily halts operations to reduce costs
-- without completely stopping it. Certain background tasks, continue to run
-- while the service is suspended and may incur charges. It is also possible
-- to drop the service completely.
-- The service can be resumed at any time using the `RESUME_SERVICE()`
-- procedure.

-- > 🚨 Note that this task requires the `app_admin` application role.

-- Suspend CDC
CALL RELATIONALAI.APP.SUSPEND_CDC();

-- Delete the engine we created:
CALL RELATIONALAI.API.DELETE_ENGINE('demo_engine', TRUE);

-- List the engines:
SELECT * FROM RELATIONALAI.API.ENGINES;

-- For each engine name in the output of the above `SELECT` statement (if any),
-- fill in the engine name in the following command and run it:
CALL RELATIONALAI.API.DELETE_ENGINE('<engine_name>', TRUE);

-- Suspend the service
CALL RELATIONALAI.APP.SUSPEND_SERVICE();

-- Drop the service
CALL RELATIONALAI.APP.DROP_SERVICE();

-- ## APPENDIX 2 - Resume Service and Re-create Engine

-- Resume the service after suspending it:
CALL RELATIONALAI.APP.RESUME_SERVICE();

-- Recreate service after dropping it
CALL RELATIONALAI.APP.CREATE_SERVICE();

-- Recreate the engine if necessary:
CALL RELATIONALAI.API.CREATE_ENGINE('demo_engine', 'rai_engine_pool_s', 'HIGHMEM_X64_S');

-- Resume CDC:
CALL RELATIONALAI.APP.RESUME_CDC();

-- ## APPENDIX 3 - Defining a RelationalAI User Role

-- - We start by creating a new role that can be granted to any users
-- permitted to use this application.
-- - We then link the application's user role to this new role. Note that it
-- is possible to create more fine-grained roles at a later stage.
-- - Finally, we grant `MONITOR` permissions on the role to allow users to
-- see engine compute pools. This is needed for the relationalai Python
-- library to manage engines.

-- Create a role specific for accessing the app
CREATE ROLE rai_user;

-- Link the app's user role to the created role.
GRANT APPLICATION ROLE relationalai.all_admin TO ROLE rai_user;

-- Allow the role to see engine compute pools.
GRANT MONITOR ON COMPUTE POOL rai_engine_pool_s TO ROLE rai_user;
GRANT MONITOR ON COMPUTE POOL rai_engine_pool_m TO ROLE rai_user;