AI agent tools to external services with HTTP

external_tool_creation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "f634fc29-6585-4cd4-aec5-5ac54c75daf4",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "source": [
    "# Slack Message Posting Tool\n",
    "\n",
    "This notebook implements an external tool to send messages to Slack. It consists of four main components:\n",
    "\n",
    "1. **Creating a UC Connection Object**: Stores the authorization details required for the Slack integration.\n",
    "2. **Defining Pre- and Post-Processing Functions**: Handles the necessary steps before and after the Slack API call.\n",
    "3. **Defining the External Function**: Queries Slack to post the message.\n",
    "4. **End-to-End Testing**: Verifies the entire setup, ensuring seamless operation from start to finish."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "756623e5-9a03-4221-8057-d50ea4a1c864",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "source": [
    "### Create a UC Connection\n",
    "This code creates a UC Connection object. The connection object stores the bearer_token in order to authenticate to the external function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "6a6ce835-1690-4d85-91f3-6b846fa6378d",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "DROP CONNECTION IF EXISTS test_sql_slack_connection;\n",
    "CREATE CONNECTION test_sql_slack_connection\n",
    "TYPE HTTP\n",
    "OPTIONS (\n",
    "  host 'https://slack.com',\n",
    "  port '443',\n",
    "  base_path '/',\n",
    "  bearer_token '<FILL_IN_TOKEN>'\n",
    ");\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "d9d6f66b-89af-4aa3-9f98-8a3f88ff72a1",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "source": [
    "### Pre Processor Function (Optional)\n",
    "\n",
    "This code defines a pre-processor function that is invoked before making the external API call. The pre-processor function receives the arguments intended for the external call, modifies them as needed, and returns the updated version. In this case, the function takes a struct containing channel and text, and appends a disclaimer message to the text before returning the modified struct."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "1d2e1253-f883-4dad-b1a3-3bb0ca6ab299",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "CREATE OR REPLACE FUNCTION main.default.pre_process_slack_message(data Struct < channel: STRING, text: STRING >) \n",
    "RETURNS Struct < channel: STRING, text: STRING > \n",
    "LANGUAGE PYTHON \n",
    "AS $$\n",
    "\n",
    "# Add disclaimer that this message is from an AI\n",
    "slack_text = \"This message is from an AI Agent:\\n\" + data.text\n",
    "\n",
    "# Combine the cleaned message with the channel name in the desired format\n",
    "return {\"channel\": data.channel, \"text\": data.text}\n",
    "$$"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "45ed5db1-ace9-4ed1-b5a7-591bc2f31483",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "source": [
    "### Post Processor Function (Optional)\n",
    "\n",
    "This code defines a post processor function that is invoked after making the external API call. The post-processor function receives response data as status code and text and expects a string to be returned. In this case, the function looks at the status code and responds with a success or failure message"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "6195f694-9a5c-4a4e-bb51-e6e29645a8ea",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "CREATE OR REPLACE FUNCTION main.default.post_process_slack_message(\n",
    "  response_data Struct < statusCode: INT, text: STRING >\n",
    ") \n",
    "RETURNS STRING \n",
    "LANGUAGE PYTHON AS $$\n",
    "\n",
    "# Add disclaimer that this message is from an AI\n",
    "if response_data.statusCode == 200:\n",
    "    return \"Successfully sent message to Slack!\"\n",
    "else:\n",
    "    return \"Encountered failure when executing request. Message from Call: \" + text\n",
    "$$"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "041ead4f-f19b-4f2a-9c2e-556c71a07e6a",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "source": [
    "### Define the External Function\n",
    "\n",
    "This code defines an external function that takes a text parameter and sends the message to a Slack channel. The function utilizes `http_request`, which accepts the connection object, HTTP method, request body, and the JSON payload for the external call. Additionally, the input JSON is passed through the pre-processing function, and the response is processed by the post-processing function, as defined earlier"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {
      "byteLimit": 2048000,
      "implicitDf": true,
      "rowLimit": 10000
     },
     "inputWidgets": {},
     "nuid": "eb379c1c-ca2d-47fd-a809-dde49773a2f4",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%sql\n",
    "CREATE OR REPLACE FUNCTION main.default.slack_send_message(text STRING COMMENT 'message content')\n",
    "RETURNS STRING\n",
    "COMMENT \"Use this to send a slack message\"\n",
    "RETURN (\n",
    "  main.default.post_process_slack_message(\n",
    "    http_request(\n",
    "      conn => 'test_sql_slack_connection',\n",
    "      method => 'POST',\n",
    "      path => '/api/chat.postMessage',\n",
    "      json =>\n",
    "        to_json(\n",
    "          main.default.pre_process_slack_message(\n",
    "            named_struct('channel', \"C077DN6SHJS\", 'text', concat(\"On behalf of \", current_user(), \": \", text))\n",
    "          )\n",
    "        )\n",
    "    )\n",
    "  )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "763c1780-63a8-4610-accf-6b67121a794e",
     "showTitle": false,
     "tableResultSettingsMap": {},
     "title": ""
    }
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "environmentMetadata": {
    "base_environment": "",
    "client": "1"
   },
   "language": "python",
   "notebookMetadata": {
    "mostRecentlyExecutedCommandWithImplicitDF": {
     "commandId": 1188265850909521,
     "dataframes": [
      "_sqldf"
     ]
    },
    "pythonIndentUnit": 2
   },
   "notebookName": "external tool creation",
   "widgets": {}
  },
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}