{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "3ad2ad0a",
   "metadata": {},
   "source": [
    "# RelationalAI Native App Setup Guide\n",
    "\n",
    "> :warning: Note that the `ACCOUNTADMIN` role is used in this guide. This role is needed for Step 1 and for creating the network rule in Step 4. To manage roles specific the RelationalAI Native App, see Appendix 2 at the bottom of this notebook.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea60581d",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "USE ROLE ACCOUNTADMIN;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3da15010",
   "metadata": {},
   "source": [
    "## Step 1 - Activate the RAI Native App\n",
    "\n",
    "Execute the following three cells to create the RAI service (this usually takes between 5 and 15 minutes):\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f466690d",
   "metadata": {
    "language": "python"
   },
   "outputs": [],
   "source": [
    "import sys\n",
    "import time\n",
    "import json\n",
    "import itertools\n",
    "from snowflake.snowpark.context import get_active_session\n",
    "\n",
    "session = get_active_session()\n",
    "\n",
    "def poll(f):\n",
    "\n",
    "    last_message = \"\"\n",
    "    dots = itertools.cycle([\"⠧\", \"⠏\", \"⠛\", \"⠹\", \"⠼\", \"⠶\"])\n",
    "\n",
    "    def status(message):\n",
    "        spaces = \" \" * (len(\"⠿ \" + last_message) - len(message))\n",
    "        sys.stdout.write(\"\\r\" + message + spaces)\n",
    "        sys.stdout.flush()\n",
    "\n",
    "    for ctr in itertools.count():\n",
    "        if ctr % 10 == 0:\n",
    "            result = f()\n",
    "            if isinstance(result, str):\n",
    "                message = next(dots) + \" \" + result\n",
    "                status(message)\n",
    "                last_message = result\n",
    "            if result is True:\n",
    "                status(\"⠿ Done!\")\n",
    "                return\n",
    "        else:\n",
    "            message = next(dots) + \" \" + last_message\n",
    "            status(message)\n",
    "        time.sleep(0.5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d5114648",
   "metadata": {
    "language": "python"
   },
   "outputs": [],
   "source": [
    "def activate():\n",
    "    try:\n",
    "        session.sql(\"CALL RELATIONALAI.APP.ACTIVATE();\").collect()\n",
    "        return True\n",
    "    except Exception as e:\n",
    "        if \"Unknown user-defined function\" in str(e):\n",
    "            return \"Waiting for app installation to complete...\"\n",
    "        else:\n",
    "            raise e\n",
    "\n",
    "poll(activate)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "91e2bf8e",
   "metadata": {
    "language": "python"
   },
   "outputs": [],
   "source": [
    "def check():\n",
    "    result = session.sql(\"CALL RELATIONALAI.APP.SERVICE_STATUS();\").collect()\n",
    "    status = json.loads(result[0][\"SERVICE_STATUS\"])[0][\"message\"]\n",
    "    if status is None:\n",
    "        status = \"\"\n",
    "    elif status.startswith(\"UNKNOWN\"):\n",
    "        status = \"Working\"\n",
    "    elif status.startswith(\"Readiness probe\"):\n",
    "        status = \"Almost done\"\n",
    "    elif status == \"Running\":\n",
    "        return True\n",
    "    else:\n",
    "        return status + \"...\"\n",
    "\n",
    "poll(check)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36178f07",
   "metadata": {},
   "source": [
    "> :warning: Note: Please review the [documentation on upgrades](https://docs.relational.ai/manage/upgrades) for details about the weekly automatic upgrade schedule.\n",
    "\n",
    "## Step 2 - Setting up Change Data Capture\n",
    "\n",
    "Streams share Snowflake data with the RAI Native App using change data capture (CDC) to capture source table and view changes once every minute.\n",
    "\n",
    "To enable CDC, run the following command:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65ef2a06",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "CALL RELATIONALAI.APP.RESUME_CDC();"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4a6ca989",
   "metadata": {},
   "source": [
    "## Step 3 - Creating user roles\n",
    "\n",
    "The RAI Native App comes with a set of Snowflake application roles for managing access to the app. Application roles can't be granted to users directly, and must be granted to Snowflake database roles instead.\n",
    "\n",
    "The following SQL creates two database roles: `rai_admin` and `rai_developer`. The `rai_admin` role is for full admin access to the RAI Native App, while the `rai_developer` role is for developers who need access to the RAI Python API:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bf5e7db5",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "-- Create a rai_admin role for full admin access to the RAI Native App.\n",
    "CREATE ROLE rai_admin;\n",
    "-- Link the app's `all_admin` role to the created role.\n",
    "GRANT APPLICATION ROLE relationalai.all_admin TO ROLE rai_admin;\n",
    "\n",
    "-- Create a role for developers who need access to the RAI Python API.\n",
    "CREATE ROLE rai_developer;\n",
    "-- Link the app's `rai_user` role to the created role.\n",
    "GRANT APPLICATION ROLE relationalai.rai_user TO ROLE rai_developer;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23ed5de6",
   "metadata": {},
   "source": [
    "Users granted the `rai_developer` role can:\n",
    "\n",
    "- Use the RAI Native App.\n",
    "- Create, use, and delete RAI reasoners.\n",
    "- Create, manage, and delete data streams.\n",
    "- Enable and disable the CDC service.\n",
    "\n",
    "You can grant the `rai_developer` role to users in your Snowflake account using the `GRANT ROLE rai_developer TO USER \"username\";` command. For example, to grant the `rai_developer` role to yourself, run the following cell:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0513530d",
   "metadata": {
    "language": "python"
   },
   "outputs": [],
   "source": [
    "# optional: grant the rai_developer role to yourself\n",
    "session = get_active_session()\n",
    "current_user = session.sql(\"SELECT CURRENT_USER() AS USERNAME;\").collect()[0][\"USERNAME\"]\n",
    "\n",
    "session.sql(f'GRANT ROLE rai_developer TO USER \"{current_user}\"').collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5786e697",
   "metadata": {},
   "source": [
    "Our [Simple Start template](https://relational.ai/build/templates/simple-start) uses a table called `RAI_DEMO.SIMPLE_START.CONNECTIONS`. If you want to be able to run that notebook, either grant permissions to the `rai_developer` role or run the following SQL to create the table now. You can clean up this database when you're done with the demo notebook by running `DROP DATABASE RAI_DEMO CASCADE;`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5965f4a7",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "CREATE DATABASE IF NOT EXISTS RAI_DEMO;\n",
    "CREATE SCHEMA IF NOT EXISTS RAI_DEMO.SIMPLE_START;\n",
    "\n",
    "CREATE OR REPLACE TABLE RAI_DEMO.SIMPLE_START.CONNECTIONS (\n",
    "    STATION_1 INT,\n",
    "    STATION_2 INT\n",
    ");\n",
    "\n",
    "INSERT INTO RAI_DEMO.SIMPLE_START.CONNECTIONS (STATION_1, STATION_2) VALUES\n",
    "(1, 2),\n",
    "(1, 3),\n",
    "(3, 4),\n",
    "(1, 4),\n",
    "(4, 5),\n",
    "(5, 7),\n",
    "(6, 7),\n",
    "(6, 8),\n",
    "(7, 8);\n",
    "\n",
    "GRANT USAGE ON DATABASE RAI_DEMO TO ROLE rai_developer;\n",
    "GRANT USAGE ON SCHEMA RAI_DEMO.SIMPLE_START TO ROLE rai_developer;\n",
    "GRANT SELECT ON TABLE RAI_DEMO.SIMPLE_START.CONNECTIONS TO ROLE rai_developer;\n",
    "ALTER TABLE RAI_DEMO.SIMPLE_START.CONNECTIONS SET CHANGE_TRACKING = TRUE;\n",
    "\n",
    "-- optional: give rai_developer more extensive permissions in the RAI_DEMO database\n",
    "-- this step is necessary for the user to be able to run all the demo notebooks\n",
    "GRANT CREATE SCHEMA ON DATABASE RAI_DEMO TO ROLE rai_developer;\n",
    "GRANT CREATE TABLE ON SCHEMA RAI_DEMO.SIMPLE_START TO ROLE rai_developer;\n",
    "GRANT CREATE TABLE ON FUTURE SCHEMAS IN DATABASE RAI_DEMO TO ROLE rai_developer;\n",
    "GRANT SELECT ON ALL TABLES IN SCHEMA RAI_DEMO.SIMPLE_START TO ROLE rai_developer;\n",
    "GRANT SELECT ON FUTURE TABLES IN DATABASE RAI_DEMO TO ROLE rai_developer;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d13dc328",
   "metadata": {},
   "source": [
    "## Step 4 — Setting up Snowflake Notebooks\n",
    "\n",
    "Snowflake offers two options when you create a new Snowflake Notebook: *Run on Warehouse* and *Run on Container*. We recommend using container notebooks because they support the use of `pip` to install the latest version of the RelationalAI Python library.\n",
    "\n",
    "For more information on using RelationalAI in Snowflake Notebooks, see [Using RAI in a Cloud Notebook](https://docs.relational.ai/build/get-started/cloud-notebook) in the RelationalAI documentation.\n",
    "\n",
    "### Container Notebooks\n",
    "\n",
    "Container notebooks do require a bit of additional setup because (1) they can't be executed with account administrator privileges, and (2) they require you to select a compute pool when creating a notebook.\n",
    "\n",
    "The SQL code below sets up all the necessary resources and permissions to simplify the process of using Container notebooks:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "390f3416",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "-- create a database to contain the notebooks\n",
    "CREATE DATABASE rai_notebooks;\n",
    "\n",
    "-- create a warehouse to select when creating a notebook\n",
    "CREATE WAREHOUSE notebooks_wh;\n",
    "\n",
    "-- create a compute pool to use when creating a notebook\n",
    "CREATE COMPUTE POOL NOTEBOOK_CPU_XS\n",
    "  MIN_NODES = 1\n",
    "  MAX_NODES = 15\n",
    "  INSTANCE_FAMILY = CPU_X64_XS\n",
    "  AUTO_RESUME = true\n",
    "  AUTO_SUSPEND_SECS = 1800\n",
    "  COMMENT = \"Pool for Snowflake Notebooks on Container Runtime\";\n",
    "\n",
    "-- grant the necessary permissions to the rai_developer role\n",
    "GRANT USAGE ON DATABASE rai_notebooks TO ROLE rai_developer;\n",
    "GRANT USAGE ON SCHEMA rai_notebooks.public TO ROLE rai_developer;\n",
    "GRANT CREATE NOTEBOOK ON SCHEMA rai_notebooks.public TO ROLE rai_developer;\n",
    "GRANT USAGE ON WAREHOUSE notebooks_wh TO ROLE rai_developer;\n",
    "GRANT USAGE ON COMPUTE POOL NOTEBOOK_CPU_XS TO ROLE rai_developer;\n",
    "GRANT CREATE SERVICE ON SCHEMA rai_notebooks.public TO ROLE rai_developer;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7854b8a",
   "metadata": {},
   "source": [
    "If you want to be able to install Python packages from PyPI in your notebooks, run the code below to set up an External Access Integration:\n",
    "\n",
    "> If you don't want to enable a PyPI integration, you can skip this step. Notebook users can import the RelationalAI Python library by uploading a ZIP file instead.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d41835b2",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "-- grant the necessary permissions to the rai_developer role\n",
    "CREATE OR REPLACE NETWORK RULE pypi_network_rule\n",
    "MODE = EGRESS\n",
    "TYPE = HOST_PORT\n",
    "VALUE_LIST = ('pypi.org', 'pypi.python.org', 'pythonhosted.org',  'files.pythonhosted.org');\n",
    "\n",
    "CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION pypi_access_integration\n",
    "ALLOWED_NETWORK_RULES = (pypi_network_rule)\n",
    "ENABLED = true;\n",
    "\n",
    "GRANT USAGE ON INTEGRATION pypi_access_integration TO ROLE rai_developer;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b84b9b1a",
   "metadata": {},
   "source": [
    "### Warehouse Notebooks\n",
    "\n",
    "The RelationalAI Python library requires an [External Access Integration](https://docs.snowflake.com/en/sql-reference/sql/create-external-access-integration) to work on notebooks that run on a warehouse. This integration allows the app to pass query results back to the notebook. Run the following code to set up the integration:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "157b9947",
   "metadata": {
    "language": "python"
   },
   "outputs": [],
   "source": [
    "session = get_active_session()\n",
    "\n",
    "system_allowlist = session.sql(\"\"\"\n",
    "SELECT value:host AS URL\n",
    "FROM TABLE(FLATTEN(input=>parse_json(SYSTEM$ALLOWLIST())))\n",
    "WHERE value:type = 'STAGE'\n",
    "\"\"\").collect()\n",
    "\n",
    "if system_allowlist:\n",
    "    urls = \", \".join(row.URL.replace('\"', \"'\") for row in system_allowlist)\n",
    "    egress_rule_commands = [\n",
    "        f\"\"\"\n",
    "        CREATE OR REPLACE NETWORK RULE S3_RAI_INTERNAL_BUCKET_EGRESS\n",
    "        MODE = EGRESS\n",
    "        TYPE = HOST_PORT\n",
    "        VALUE_LIST = ({urls});\n",
    "        \"\"\",\n",
    "        \"\"\"\n",
    "        CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION S3_RAI_INTERNAL_BUCKET_EGRESS_INTEGRATION\n",
    "        ALLOWED_NETWORK_RULES = (S3_RAI_INTERNAL_BUCKET_EGRESS)\n",
    "        ENABLED = true;\n",
    "        \"\"\",\n",
    "        \"\"\"\n",
    "        GRANT USAGE ON INTEGRATION S3_RAI_INTERNAL_BUCKET_EGRESS_INTEGRATION TO ROLE rai_developer;\n",
    "        \"\"\"\n",
    "    ]\n",
    "\n",
    "    for command in egress_rule_commands:\n",
    "        session.sql(command).collect()\n",
    "\n",
    "    print(\"Network rule set up successfully.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11191999",
   "metadata": {},
   "source": [
    "## Step 5 - Set Up Direct Access\n",
    "\n",
    "By default, Python API calls are routed through Snowflake [Service Functions](https://docs.snowflake.com/en/developer-guide/snowpark-container-services/working-with-services#service-functions-using-a-service-from-an-sql-query), which adds some extra overhead to every request.\n",
    "[Direct Access](https://docs.relational.ai/manage/app#direct-access) allows connections straight to the RAI Native App’s secure web address, skipping the middleman and reducing latency—often by about 500ms.\n",
    "\n",
    "> :warning: Note: Connections via Direct Access are not supported by Snowflake Notebooks at this time.\n",
    "> If you only plan to use RelationalAI in Snowflake Notebooks, and not from a local Python environment, you can skip this step.\n",
    "\n",
    "Currently, Direct Access only supports three authentication methods: OAuth, Personal Access Token (PAT), and key-pair.\n",
    "We recommend creating an [OAuth security integration](https://docs.relational.ai/manage/app#da-auth-create-oauth-security-integration) for Direct Access now so that users can authenticate interactively in their local development environments.\n",
    "PAT or key-pair authentication can be set up later for applications and automation.\n",
    "\n",
    "To create an OAuth security integration, execute the following SQL:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca3ef249",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "-- Create a public OAuth client for Direct Access.\n",
    "CREATE SECURITY INTEGRATION RAI_SECURITY_INTEGRATION\n",
    "  TYPE = OAUTH\n",
    "  ENABLED = TRUE\n",
    "  OAUTH_CLIENT = CUSTOM\n",
    "  OAUTH_CLIENT_TYPE = 'PUBLIC'  -- or 'CONFIDENTIAL' if needed\n",
    "  OAUTH_ALLOW_NON_TLS_REDIRECT_URI = TRUE\n",
    "  OAUTH_REDIRECT_URI = 'http://localhost:8001/snowflake/oauth-redirect'\n",
    "  OAUTH_ISSUE_REFRESH_TOKENS = TRUE\n",
    "  OAUTH_REFRESH_TOKEN_VALIDITY = 86400;  -- Time in seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e8cd725",
   "metadata": {},
   "source": [
    "Note that the redirect URI can be any localhost URI, but the port must be open on the user's machine.\n",
    "Now, run the following SQL to retrieve the client ID:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6a70ebe1",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "-- Inspect the created integration. Change the integration name if you used\n",
    "-- a different one in step 1.\n",
    "DESC SECURITY INTEGRATION RAI_SECURITY_INTEGRATION;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f026285",
   "metadata": {},
   "source": [
    "Share the client ID, client secret (if applicable), and redirect URI with users so they can [configure](https://docs.relational.ai/build/guides/configuration#direct-access) their Direct Access connection locally.\n",
    "\n",
    "## Step 6 - Enable Warm Reasoners (Optional)\n",
    "\n",
    "[Warm reasoners](https://docs.relational.ai/manage/compute-resources/#warm-reasoners) are pre-provisioned RAI reasoners that can reduce latency for workloads that require quick startup times.\n",
    "\n",
    "We recommend enabling a warm `logic` reasoner for at least one reasoner size to get the best performance when using the RAI Native App.\n",
    "To enable a warm `logic` reasoner of size `HIGHMEM_X64_S`, run the following SQL command:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19d53682",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "-- Note: Warm reasoners incur additional costs. If you want to enable warm reasoners,\n",
    "-- uncomment the line below and run it.\n",
    "-- CALL RELATIONALAI.APP.ENABLE_WARM_REASONER('logic', 'HIGHMEM_X64_S');"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fae1055e",
   "metadata": {},
   "source": [
    "See the [Warm Reasoners](https://docs.relational.ai/manage/compute-resources/#warm-reasoners) documentation for more information on warm reasoners and how to manage them.\n",
    "\n",
    "## Congratulations! Your RelationalAI app is now ready to use.\n",
    "\n",
    "# Next Steps\n",
    "\n",
    "To get up and running with RelationalAI, download the [Simple Start template](https://docs.relational.ai/build/templates/simple-start), extract the files,and follow the instructions in the `README` file and the `simple-start.ipynb` notebook.\n",
    "\n",
    "For a more detailed example and more information about the RelationalAI Python library, check out the [documentation](https://docs.relational.ai/build/).\n",
    "\n",
    "Links:\n",
    "\n",
    "- Simple Start Notebook: https://relational.ai/build/templates/simple-start\n",
    "- All templates: https://docs.relational.ai/build/templates\n",
    "- Docs: https://docs.relational.ai\n",
    "\n",
    "## APPENDIX 1 - Deactivate the RAI Native App\n",
    "\n",
    "To reduce costs when you are not using the RAI Native App, suspend CDC, and delete all reasoners.\n",
    "\n",
    "> :warning: Note that this task requires the `app_admin` application role.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "239ff996",
   "metadata": {
    "language": "python"
   },
   "outputs": [],
   "source": [
    "session = get_active_session()\n",
    "# note: the use of `skip_appendix` in the Appendices makes the notebook's\n",
    "# \"Run All\" action skip these cells\n",
    "skip_appendix = True\n",
    "\n",
    "# Deactivate the app\n",
    "if not skip_appendix:\n",
    "    session.sql(\"CALL RELATIONALAI.APP.DEACTIVATE();\").collect()\n",
    "\n",
    "# To re-activate the app:\n",
    "# session.sql(\"CALL RELATIONALAI.APP.ACTIVATE();\").collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "214471dc",
   "metadata": {},
   "source": [
    "## APPENDIX 2 - App Upgrades\n",
    "\n",
    "Your RAI Native App is [automatically upgraded](https://docs.relational.ai/manage/upgrades) every Monday at 10:00 UTC. During upgrades, the application cannot process RAI transactions.\n",
    "\n",
    "If you prefer to schedule upgrades for a different day and time, use the [`schedule_upgrade()`](https://docs.relational.ai/api/sql/app/schedule_upgrade) procedure:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c8b53d02",
   "metadata": {
    "language": "python"
   },
   "outputs": [],
   "source": [
    "if not skip_appendix:\n",
    "    # Schedule upgrades for Wednesdays at 15:00 UTC. Times are in 24-hour format.\n",
    "    session.sql(\"CALL relationalai.app.schedule_upgrade('WEDNESDAY', '15:00');\").collect()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Streamlit Notebook",
   "name": "streamlit"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}