regresion_arbol_de_decisiones_postgresql.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/rubuntu/uaa-417-sistemas-de-gestion-de-bases-de-datos-avanzados/blob/main/regresion_arbol_de_decisiones_postgresql.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "81e8ce10",
      "metadata": {
        "id": "81e8ce10"
      },
      "source": [
        "# Regresión con Arbol de Decisiones\n",
        "\n",
        "A continuación se presenta un ejemplo detallado del flujo de trabajo para:\n",
        "\n",
        "1. Configurar PostgreSQL con la extensión PL/Python3u en Google Colab.\n",
        "2. Entrenar un modelo de regresión (árbol de decisión) usando el dataset California Housing.\n",
        "3. Guardar el modelo entrenado.\n",
        "4. Crear una función definida por el usuario (UDF) en PostgreSQL con PL/Python3u que cargue el modelo y realice predicciones al ser invocada desde SQL.\n",
        "\n",
        "### Paso a paso\n",
        "\n",
        "#### 1. Configurar PostgreSQL en Google Colab\n",
        "\n",
        "**Nota:**  \n",
        "Google Colab no provee PostgreSQL ni su configuración por defecto. Tendremos que instalarlo y configurarlo manualmente. También, Colab se ejecuta en un entorno efímero, lo que implica que cada vez que se reinicie el entorno, se deberá repetir la configuración."
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%capture\n",
        "# Instalar PostgreSQL\n",
        "!apt-get install postgresql postgresql-contrib postgresql-plpython3-14\n",
        "# Instalar bibliotecas\n",
        "!pip install ipython-sql psycopg2-binary pymysql sqlalchemy prettytable==0.7.2"
      ],
      "metadata": {
        "id": "aatI_-bl-uQF"
      },
      "id": "aatI_-bl-uQF",
      "execution_count": 1,
      "outputs": []
    },
    {
      "cell_type": "code",
      "execution_count": 2,
      "id": "6c7f87bc",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "6c7f87bc",
        "outputId": "7cb5bb88-766e-4315-e4f6-e1a2308e3049"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * Starting PostgreSQL 14 database server\n",
            "   ...done.\n",
            "CREATE DATABASE\n",
            "ALTER ROLE\n"
          ]
        }
      ],
      "source": [
        "# Iniciar el servicio de PostgreSQL\n",
        "!service postgresql start\n",
        "\n",
        "# Crear base de datos\n",
        "!sudo -u postgres psql -c \"CREATE DATABASE colab;\"\n",
        "!sudo -u postgres psql -c \"ALTER USER postgres PASSWORD 'postgres';\"\n"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "dd0888b6",
      "metadata": {
        "id": "dd0888b6"
      },
      "source": [
        "Ahora podemos conectarnos a la base de datos. En Colab, usaremos la interfaz por línea de comando `psql`. Antes, se instaló la extensión `ipython-sql` para correr consultas SQL desde la notebook."
      ]
    },
    {
      "cell_type": "markdown",
      "id": "f6d5aacf",
      "metadata": {
        "id": "f6d5aacf"
      },
      "source": [
        "Para las siguientes celdas, asumiremos que nos conectaremos con `%sql` mágicas. Si no se desea, se puede obviar esta parte y usar el comando `!psql ...`."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 3,
      "id": "1795ed16",
      "metadata": {
        "id": "1795ed16"
      },
      "outputs": [],
      "source": [
        "%load_ext sql\n",
        "%sql postgresql://postgres:postgres@localhost:5432/colab"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "7b28fddd",
      "metadata": {
        "id": "7b28fddd"
      },
      "source": [
        "#### 2. Entrenar el modelo de regresión con el dataset California Housing\n",
        "\n",
        "Usaremos `scikit-learn` para cargar el dataset de California Housing, entrenar un modelo de árbol de decisión, y guardarlo usando `pickle`."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 4,
      "id": "dcb465c5",
      "metadata": {
        "id": "dcb465c5"
      },
      "outputs": [],
      "source": [
        "from sklearn.datasets import fetch_california_housing\n",
        "from sklearn.tree import DecisionTreeRegressor\n",
        "from sklearn.model_selection import train_test_split\n",
        "import pickle\n",
        "\n",
        "# Cargar dataset\n",
        "data = fetch_california_housing()\n",
        "X = data.data\n",
        "y = data.target\n",
        "\n",
        "# Dividir datos\n",
        "X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)\n",
        "\n",
        "# Entrenar modelo de árbol de decisión\n",
        "model = DecisionTreeRegressor(random_state=42)\n",
        "model.fit(X_train, y_train)\n",
        "\n",
        "# Guardar el modelo en un archivo local\n",
        "with open(\"california_model.pkl\", \"wb\") as f:\n",
        "    pickle.dump(model, f)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "70e3a0b2",
      "metadata": {
        "id": "70e3a0b2"
      },
      "source": [
        "Podemos evaluar brevemente la calidad del modelo:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 5,
      "id": "2843ef9e",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "2843ef9e",
        "outputId": "ef435bd4-b606-4daa-aa80-f361203eb99b"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "0.495235205629094"
            ]
          },
          "metadata": {},
          "execution_count": 5
        }
      ],
      "source": [
        "from sklearn.metrics import mean_squared_error\n",
        "\n",
        "y_pred = model.predict(X_test)\n",
        "mse = mean_squared_error(y_test, y_pred)\n",
        "mse"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "2a1bbb70",
      "metadata": {
        "id": "2a1bbb70"
      },
      "source": [
        "#### 3. Habilitar PL/Python3u en PostgreSQL y crear la función UDF en PostgreSQL con PL/Python3u\n",
        "\n",
        "Ahora, crearemos una función en la base de datos que:\n",
        "\n",
        "- Cargará el modelo desde el archivo `california_model.pkl`.\n",
        "- Recibirá un vector de floats (las características a predecir).\n",
        "- Devolverá una predicción.\n",
        "\n",
        "**Puntos importantes:**\n",
        "\n",
        "- PL/Python3u puede acceder al sistema de archivos del servidor (en este caso, el mismo entorno de Colab, ya que el servidor se ejecuta localmente).\n",
        "- Hay que asegurarse de que el archivo `california_model.pkl` sea accesible por el usuario que corre el servidor PostgreSQL.\n",
        "- Si se requiere, se pueden ajustar permisos (`chmod`), pero en Colab normalmente no es problema si el archivo está en el directorio actual.\n",
        "\n",
        "Primero, instalemos `cloudpickle` dentro del entorno global de Python que usa PL/Python3u, aunque generalmente `pickle` estándar es suficiente. Verificaremos que PL/Python3u permita importar `pickle`.\n",
        "\n",
        "**Nota:**  \n",
        "PL/Python3u permite el uso de módulos estándar de Python, pero no siempre el entorno es idéntico al de la notebook. De ser necesario, podríamos colocar el contenido del modelo serializado dentro del UDF, o asegurarnos de que la ruta sea correcta.\n",
        "\n",
        "Crearemos la UDF:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 6,
      "id": "5bd49a5e",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "5bd49a5e",
        "outputId": "8ba65af7-3840-42e4-a756-01abbf6736ae"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * postgresql://postgres:***@localhost:5432/colab\n",
            "Done.\n",
            "Done.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[]"
            ]
          },
          "metadata": {},
          "execution_count": 6
        }
      ],
      "source": [
        "%%sql\n",
        "\n",
        "CREATE EXTENSION IF NOT EXISTS plpython3u;\n",
        "\n",
        "CREATE OR REPLACE FUNCTION predict_california_price(features float8[])\n",
        "RETURNS float8 AS $$\n",
        "    import pickle\n",
        "    import os\n",
        "\n",
        "    model_path = '/content/california_model.pkl'  # Ajustar ruta si es necesario\n",
        "    if not hasattr(plpy, 'model'):\n",
        "        # Cargar el modelo sólo una vez y guardarlo en un atributo de plpy\n",
        "        with open(model_path, 'rb') as f:\n",
        "            plpy.model = pickle.load(f)\n",
        "\n",
        "    # features llega como un array de float8. Debemos convertirlo en la forma (1, n_features)\n",
        "    import numpy as np\n",
        "    X = np.array(features).reshape(1, -1)\n",
        "    pred = plpy.model.predict(X)[0]\n",
        "    return float(pred)\n",
        "$$ LANGUAGE plpython3u;"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "ff247ddd",
      "metadata": {
        "id": "ff247ddd"
      },
      "source": [
        "Lo que hace esta función:\n",
        "\n",
        "- Comprueba si `plpy.model` está definido. Si no, carga el modelo desde el archivo pickle. Esto evita recargar el modelo en cada llamada.\n",
        "- Toma el array de entrada `features`, lo convierte a un numpy array 2D y llama al método `predict`.\n",
        "- Devuelve la predicción como un float.\n",
        "\n",
        "#### 5. Invocar la función desde SQL\n",
        "\n",
        "Asumamos que queremos predecir el valor medio de una vivienda para un conjunto de características. El dataset California Housing tiene 8 características por vivienda:  \n",
        "`MedInc, HouseAge, AveRooms, AveBedrms, Population, AveOccup, Latitude, Longitude`.\n",
        "\n",
        "Por ejemplo, tomemos una muestra de `X_test`:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 7,
      "id": "d9f386f7",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "d9f386f7",
        "outputId": "9eee0119-2e2c-4466-ce36-ece5a3a2be59"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "array([ 1.68120000e+00,  2.50000000e+01,  4.19220056e+00,  1.02228412e+00,\n",
              "        1.39200000e+03,  3.87743733e+00,  3.60600000e+01, -1.19010000e+02])"
            ]
          },
          "metadata": {},
          "execution_count": 7
        }
      ],
      "source": [
        "sample_features = X_test[0]  # Un vector de 8 valores\n",
        "sample_features"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "cf52585f",
      "metadata": {
        "id": "cf52585f"
      },
      "source": [
        "Suponiendo que esto arroja algo como `[   4.4742,  34. ,    6.525 ,   1.0238,  150.   ,   2.1841,  37.88 , -122.23 ]` (estos son ejemplos, pueden variar).\n",
        "\n",
        "Podemos llamar la función desde SQL:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 8,
      "id": "a4197fc7",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 36
        },
        "id": "a4197fc7",
        "outputId": "7684d811-e325-4a66-b97b-441e7b554e39"
      },
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "'SELECT predict_california_price(ARRAY[1.6812,25.0,4.192200557103064,1.0222841225626742,1392.0,3.8774373259052926,36.06,-119.01]);'"
            ],
            "application/vnd.google.colaboratory.intrinsic+json": {
              "type": "string"
            }
          },
          "metadata": {},
          "execution_count": 8
        }
      ],
      "source": [
        "sample_features_list = list(sample_features)\n",
        "query = f\"SELECT predict_california_price(ARRAY[{','.join(str(x) for x in sample_features_list)}]);\"\n",
        "query"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "e701b302",
      "metadata": {
        "id": "e701b302"
      },
      "source": [
        "Ejecutamos:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 9,
      "id": "ea4c92b4",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 97
        },
        "id": "ea4c92b4",
        "outputId": "8816cc32-4f50-4034-e313-89a492dd9dbb"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " * postgresql://postgres:***@localhost:5432/colab\n",
            "1 rows affected.\n"
          ]
        },
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "[(0.414,)]"
            ],
            "text/html": [
              "<table>\n",
              "    <tr>\n",
              "        <th>predict_california_price</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "        <td>0.414</td>\n",
              "    </tr>\n",
              "</table>"
            ]
          },
          "metadata": {},
          "execution_count": 9
        }
      ],
      "source": [
        "%%sql\n",
        "\n",
        "$query"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "79b5ab27",
      "metadata": {
        "id": "79b5ab27"
      },
      "source": [
        "Esto debería devolver la predicción como un valor float.\n",
        "\n",
        "### Resumen\n",
        "\n",
        "En resumen, hemos:\n",
        "\n",
        "- Configurado PostgreSQL en Colab y habilitado PL/Python3u.\n",
        "- Entrenado un modelo de árbol de decisión sobre el dataset California Housing.\n",
        "- Guardado el modelo en un archivo pickle.\n",
        "- Creado un UDF en PostgreSQL con PL/Python3u que carga el modelo y realiza predicciones.\n",
        "- Invocado la función desde SQL para obtener predicciones."
      ]
    }
  ],
  "metadata": {
    "jupytext": {
      "cell_metadata_filter": "-all",
      "main_language": "python",
      "notebook_metadata_filter": "-all"
    },
    "colab": {
      "provenance": [],
      "include_colab_link": true
    },
    "language_info": {
      "name": "python"
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}