{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "4d9be40f",
   "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": "765c8f5b",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "USE ROLE ACCOUNTADMIN;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c78f9f72",
   "metadata": {},
   "source": [
    "## Step 1 - Share Continuous Telemetry Data\n",
    "\n",
    "To receive support from RelationalAI, you must opt-in to sharing continuous telemetry data. 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.\n",
    "\n",
    "To enable sharing, click the *Enable* button under *Data Products > Apps > RelationalAI* under the *Events and logs* tab in Snowsight. Run the cell below to check that this step has been completed.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3e560534",
   "metadata": {
    "language": "python"
   },
   "outputs": [],
   "source": [
    "session = get_active_session()\n",
    "app_data = session.sql(\"describe application relationalai;\").collect()\n",
    "sharing_enabled = dict(app_data)[\"share_events_with_provider\"]\n",
    "if sharing_enabled:\n",
    "    print(\"Continuous telemetry data sharing is enabled.\")\n",
    "else:\n",
    "    print(\"Please enable sharing of continuous telemetry data in Snowsight.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ed4d448",
   "metadata": {},
   "source": [
    "## Step 2 - Activate the RAI Native App\n",
    "\n",
    "The *Grant* button under *Data Products > Apps > RelationalAI* in Snowsight runs the following SQL command to grant the necessary permissions to the app. If you haven't clicked that button yet, you can run the code here instead. It doesn't hurt to run it again if you're not sure.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7199e3bd",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "GRANT\n",
    "      EXECUTE TASK,\n",
    "      EXECUTE MANAGED TASK,\n",
    "      CREATE COMPUTE POOL,\n",
    "      CREATE WAREHOUSE\n",
    "ON ACCOUNT TO APPLICATION RELATIONALAI;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "69a12b66",
   "metadata": {},
   "source": [
    "Now 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": "44313940",
   "metadata": {
    "language": "python"
   },
   "outputs": [],
   "source": [
    "import sys\n",
    "import time\n",
    "import json\n",
    "import itertools\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": "f4c78ff1",
   "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": "fe2ab45b",
   "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.startswith(\"UNKNOWN\"):\n",
    "        status = \"Working\"\n",
    "    if status.startswith(\"Readiness probe\"):\n",
    "        status = \"Almost done\"\n",
    "    if status == \"Running\":\n",
    "        return True\n",
    "    else:\n",
    "        return status + \"...\"\n",
    "\n",
    "poll(check)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9819704e",
   "metadata": {},
   "source": [
    "> :warning: **IMPORTANT**\n",
    "> While RelationalAI is in preview mode, you must upgrade the application weekly after RAI releases an updated native app. Please review the [Upgrades](https://relational.ai/docs/manage/upgrades) section of the RelationalAI Native App documentation and subscribe to the release notes to receive notifications about new versions.\n",
    "\n",
    "\n",
    "\n",
    "## Step 3 - 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": "308cf9c7",
   "metadata": {
    "language": "sql"
   },
   "outputs": [],
   "source": [
    "CALL RELATIONALAI.APP.RESUME_CDC();"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9472c3ac",
   "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*. 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.\n",
    "\n",
    "To enable users who aren't account administrators to run RelationalAI in Snowflake Notebooks, see Appendix 2 for how to create a `rai_user` role and grant access to this integration.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "909b5780",
   "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",
    "\n",
    "    for command in egress_rule_commands:\n",
    "        session.sql(command).collect()\n",
    "\n",
    "    print(\"Network rule set up successfully.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04cb49f0",
   "metadata": {},
   "source": [
    "## Congratulations! Your RelationalAI app is now ready to use.\n",
    "\n",
    "# Next Steps\n",
    "\n",
    "To get up and running with RelationalAI in Snowflake Notebooks, download the [Simple Start Snowflake Notebook](https://relational.ai/notebooks/simple-start-snowflake.ipynb), upload it to your Snowflake account (https://app.snowflake.com > + icon in left sidebar > Notebooks > Import .ipynb file), and follow the instructions in the notebook.\n",
    "\n",
    "To use a local Python installation instead, download the [Simple Start Jupyter notebook](https://relational.ai/notebooks/simple-start.ipynb) and follow the [instructions for running the notebook](https://relational.ai/docs/develop/example-notebooks#instructions).\n",
    "\n",
    "For a more detailed example and more information about the RelationalAI Python library, check out the [Getting Started guide](https://relational.ai/docs/get-started).\n",
    "\n",
    "Links:\n",
    "\n",
    "- Simple Start Notebook: https://relational.ai/notebooks/simple-start.ipynb\n",
    "- Example Notebooks: https://relational.ai/docs/develop/example-notebooks\n",
    "- Docs: https://relational.ai/docs\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 engines.\n",
    "\n",
    "> :warning: Note that this task requires the `app_admin` application role.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ff35a097",
   "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": "8b4aa55a",
   "metadata": {},
   "source": [
    "## APPENDIX 2 - Defining a RelationalAI User Role\n",
    "\n",
    "- We start by creating a new role that can be granted to any users permitted to use this application.\n",
    "- 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.\n",
    "- 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.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7a6eb86d",
   "metadata": {
    "language": "python"
   },
   "outputs": [],
   "source": [
    "if not skip_appendix:\n",
    "    # Create a role for accessing the app\n",
    "    session.sql(\"CREATE ROLE rai_user;\").collect()\n",
    "\n",
    "    # Link the app's user role to the created role.\n",
    "    session.sql(\"GRANT APPLICATION ROLE relationalai.all_admin TO ROLE rai_user;\").collect()\n",
    "\n",
    "    # Grant USAGE on the egress integration to the role\n",
    "    # This is necessary for users with the `rai_user` role to be able to\n",
    "    # run the app in Snowflake Notebooks\n",
    "    session.sql(\"GRANT USAGE ON INTEGRATION S3_RAI_INTERNAL_BUCKET_EGRESS_INTEGRATION TO ROLE rai_user;\").collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0a9fad93",
   "metadata": {},
   "source": [
    "## APPENDIX 3 - App Upgrades\n",
    "\n",
    "Your RAI Native App is [automatically upgraded](https://relational.ai/docs/native_app/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://relational.ai/docs/reference/sql/schedule_upgrade) procedure:\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc144016",
   "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
}