<commit_before>{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.7.3"
    },
    "colab": {
      "name": "sesion6.ipynb",
      "provenance": []
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ft50fw517yvk"
      },
      "source": [
        "# NoSQL (HBase) (sesión 6)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wiopea6aOBbi"
      },
      "source": [
        "![hbase-logo.e139b77f7031062f738f0fc28210e0ffa6ca26c8.png](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAkoAAAGqCAIAAAB77fzRAAAAGXRFWHRTb2Z0d2FyZQBBZG9iZSBJbWFnZVJlYWR5ccllPAAANWVJREFUeNrtnQd4FVX+v2NZ96/+sO+q+LOs/tayxbKgW1zLFtetimXdVUGxo4JiwYrComJdsWKXFcuqq6KugqsoCpZUQgokQCAFSCCUhIQUQhL+n2TW+1zmtrl35t4p932feXxiSCZ3zpw575xzvud7cjYDAAAEjhyKAAAA0BsAAAB6AwAAQG8AAAD<KEY>AYAAIDeAAAAvQEAAKA3AAAA9AYAAIDeAAAA0BsAAAB6AwAA9AYAAIDeAAAA0BsAAAB6AwAAQG8AAIDeAAAA0BsAAAB6AwAAQG8AAADoDQAAAL0BAAB6AwAAQG8AAADoDQAAAL0BAACgNwAAQG8AAADoDQAAAL0BAACgNwAAAPQGAACA3gAAAL0BAACgNwAAAPQGAACA3gAAANAbAACgNwAAAPQGAACA3gAAANAbAAAAegMAAEBvAACA3gAAANAbAAAAegMAAEBvAAAA6A0AANAbAAAAegMAAEBvAAAA6A0AAAC9AQAAoDcAAEBvAAAA6A0AAAC9AQAAoDcAAAD0BgAA6A0AAAC9AQAAoDcAAAD0BgAAgN4AAADQGwAAoDcAAAD0BgAAgN4AAADQGwAAAHoDAAD0BgAAgN4AAADQGwAAAHoDAABImv8Pa99NbmfcsbgAAAAASUVORK5CYII=)\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4HLSQ6r6Ok9v"
      },
      "source": [
        "Esta hoja muestra cómo acceder a bases de datos HBase y también a conectar la salida con Jupyter.\n",
        "\n",
        "Se puede utilizar el *shell* propio de HBase en la máquina virtual.\n",
        "\n",
        "Con HBase vamos a simular un modo _semi-clúster_ con una sola máquina.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "julNKL3JQZRW"
      },
      "source": [
        "Instalamos la versión 8 de Java y la seleccionamos como opción por defecto:\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "K60d3mLKZg6x"
      },
      "source": [
        "%%capture\n",
        "import os       #importing os to set environment variable\n",
        "def install_java():\n",
        "  !apt-get update -qq > /dev/null\n",
        "  !apt-get install -y openjdk-8-jdk-headless -qq > /dev/null      #install openjdk\n",
        "  os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-8-openjdk-amd64\"     #set environment variable\n",
        "  !java -version       #check java version\n",
        "install_java()\n",
        "!update-java-alternatives -s java-1.8.0-openjdk-amd64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nqEVgAChMFcR"
      },
      "source": [
        "%env HBASE_VERSION=2.3.3"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9hv1DUSoFOys"
      },
      "source": [
        "%%bash \n",
        "wget -q https://downloads.apache.org/hbase/$HBASE_VERSION/hbase-$HBASE_VERSION-bin.tar.gz\n",
        "tar -xzf hbase-$HBASE_VERSION-bin.tar.gz"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "uMmB5l_oaNJD"
      },
      "source": [
        "%%bash\n",
        "hbase-$HBASE_VERSION/bin/start-hbase.sh\n",
        "hbase-$HBASE_VERSION/bin/hbase-daemon.sh restart thrift"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_PjCnzExOBbo"
      },
      "source": [
        "from pprint import pprint as pp\n",
        "import pandas as pd\n",
        "import matplotlib.pyplot as plt\n",
        "import matplotlib\n",
        "\n",
        "%matplotlib inline\n",
        "matplotlib.style.use('ggplot')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "h8JfspqJOBb_"
      },
      "source": [
        "Usaremos la librería `happybase` para python. La cargamos a continuación y hacemos la conexión."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IVq4QcNzOBcQ"
      },
      "source": [
        "!pip install happybase"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PP5jW7W0IWrF"
      },
      "source": [
        "import gzip\n",
        "from urllib.request import Request,urlopen\n",
        "import io\n",
        "import os\n",
        "import os.path as path\n",
        "\n",
        "def download_csv(baseurl, filename):\n",
        "    file = path.abspath(path.join(os.getcwd(),filename))\n",
        "    request = Request(baseurl + '/' + filename+'.gz?raw=true')\n",
        "    response = urlopen(request)\n",
        "    buf = io.BytesIO(response.read())\n",
        "    f = gzip.GzipFile(fileobj=buf)\n",
        "    data = f.read()\n",
        "    with open (filename, 'wb') as ff:\n",
        "      ff.write(data)\n",
        "        \n",
        "baseurl = 'https://github.com/dsevilla/bdge-data/blob/master/es.stackoverflow/'\n",
        "download_csv(baseurl, 'Posts.csv')\n",
        "download_csv(baseurl, 'Users.csv')\n",
        "download_csv(baseurl, 'Tags.csv')\n",
        "download_csv(baseurl, 'Comments.csv')\n",
        "download_csv(baseurl, 'Votes.csv')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VnqPwDMR7ywp"
      },
      "source": [
        "import happybase\n",
        "from contextlib import contextmanager\n",
        "\n",
        "HBASEHOST = 'localhost'\n",
        "\n",
        "class Connection():\n",
        "    \n",
        "    def __init__(self, host):\n",
        "        self.host = host\n",
        "        self._genpool()\n",
        "        \n",
        "    def _genpool(self):\n",
        "        self.pool = happybase.ConnectionPool(size=5, host=self.host)\n",
        "    \n",
        "    @contextmanager\n",
        "    def connection(self):\n",
        "        for _ in range(5): # Probar 5 veces a regenerar el pool\n",
        "            for _ in range(5): # Probar 5 veces a conectar\n",
        "                with self.pool.connection() as connection:\n",
        "                    try:\n",
        "                        connection.tables()\n",
        "                        yield connection\n",
        "                        return\n",
        "                    except Exception as e:\n",
        "                        pass\n",
        "            self._genpool()\n",
        "        raise Exception(\"HBase Connection Error\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "T8CWSFZa7yw7"
      },
      "source": [
        "hbasecon = Connection(HBASEHOST)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "30jYcJud7yxF"
      },
      "source": [
        "with hbasecon.connection() as connection:\n",
        "    print(connection.tables())"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GT37NUcY7yxQ"
      },
      "source": [
        "Para la carga inicial, vamos a crear todas las tablas con una única familia de columnas, `rawdata`, donde meteremos toda la información _raw_ comprimida. Después podremos hacer reorganizaciones de los datos para hacer el acceso más eficiente. Es una de las muchas ventajas de no tener un esquema."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DLLkGnnQ7yxR"
      },
      "source": [
        "# Create tables\n",
        "tables = ['posts', 'votes', 'users', 'tags', 'comments']\n",
        "for t in tables:\n",
        "    try:\n",
        "        with hbasecon.connection() as connection:\n",
        "            connection.create_table(\n",
        "                t,\n",
        "                {\n",
        "                    'rawdata': dict(max_versions=1,compression='GZ')\n",
        "                })\n",
        "    except Exception as e:\n",
        "        print(\"Database already exists: {0}. {1}\".format(t, e))\n",
        "        pass\n",
        "with hbasecon.connection() as connection:\n",
        "    print(connection.tables())"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Y07GxAB77yxX"
      },
      "source": [
        "El código de importación es siempre el mismo, ya que se coge la primera fila del CSV que contiene el nombre de las columnas y se utiliza para generar nombres de columnas dentro de la familia de columnas dada como parámetro. La función `csv_to_hbase()` acepta un fichero CSV a abrir, un nombre de tabla y una familia de columnas donde agregar las columnas del fichero CSV. En nuestro caso siempre va a ser `rawdata`."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1dAKqxpH7yxZ"
      },
      "source": [
        "import csv\n",
        "\n",
        "def csv_to_hbase(file, tablename, cf):\n",
        "    with hbasecon.connection() as connection, open(file) as f:\n",
        "        table = connection.table(tablename)\n",
        "\n",
        "        # La llamada csv.reader() crea un iterador sobre un fichero CSV\n",
        "        reader = csv.reader(f, dialect='excel')\n",
        "        \n",
        "        # Se leen las columnas. Sus nombres se usarán para crear las diferentes columnas en la familia\n",
        "        columns = next(reader)\n",
        "        columns = [cf + ':' + c for c in columns]\n",
        "        \n",
        "        with table.batch(batch_size=500) as b:\n",
        "            for row in reader:\n",
        "                # La primera columna se usará como Row Key\n",
        "                b.put(row[0], dict(zip(columns[1:], row[1:])))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3mkoAxWY7yxg"
      },
      "source": [
        "for t in tables:\n",
        "    print(\"Importando tabla {0}...\".format(t))\n",
        "    %time csv_to_hbase(t.capitalize() + '.csv', t, 'rawdata')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JRlhb2x17yxo"
      },
      "source": [
        "### Consultas sencillas desde Python\n",
        "\n",
        "A continuación veremos algunas consultas sencillas desde python usando el API de `happybase`."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "AbUFk3zV7yxq"
      },
      "source": [
        "def posts():\n",
        "  with hbasecon.connection() as connection:\n",
        "    return connection.table('posts')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "72e7fcjE7yxz"
      },
      "source": [
        "Obtener el Post con `Id` 5. La orden más sencilla e inmediata de HBase es obtener una fila, opcionalmente limitando las columnas a mostrar:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1Wii2xrl7yx2"
      },
      "source": [
        "posts().row(b'5',columns=[b'rawdata:Body'])"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "I_AL9JlL7yyB"
      },
      "source": [
        "El siguiente código permite mostrar de forma amigable las tablas extraídas de la base de datos en forma de diccionario:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4ZfMeUZv7yyD"
      },
      "source": [
        "# http://stackoverflow.com/a/30525061/62365\n",
        "class DictTable(dict):\n",
        "    # Overridden dict class which takes a dict in the form {'a': 2, 'b': 3},\n",
        "    # and renders an HTML Table in IPython Notebook.\n",
        "    def _repr_html_(self):\n",
        "        htmltext = [\"<table width=100%>\"]\n",
        "        for key, value in self.items():\n",
        "            htmltext.append(\"<tr>\")\n",
        "            htmltext.append(\"<td>{0}</td>\".format(key.decode('utf-8')))\n",
        "            htmltext.append(\"<td>{0}</td>\".format(value.decode('utf-8')))\n",
        "            htmltext.append(\"</tr>\")\n",
        "        htmltext.append(\"</table>\")\n",
        "        return ''.join(htmltext)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": true,
        "id": "OTKDiVrr7yyN"
      },
      "source": [
        "# Muestra cómo queda la fila del Id del Post 9997\n",
        "DictTable(posts().row(b'5'))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ydnhHy9r7yyT"
      },
      "source": [
        "DictTable(posts().row(b'5',columns=[b'rawdata:AnswerCount',b'rawdata:AcceptedAnswerId']))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GciY3L4X7yyb"
      },
      "source": [
        "Y también se puede recorrer como un diccionario normal (el `decode` se utiliza para convertir los valores binarios de la base de datos a una codificación UTF-8):"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "h1Iit47u7yyd"
      },
      "source": [
        "row = posts().row(b'5')\n",
        "for key, value in row.items():\n",
        "    print(\"Key = '%s', Value = '%s'\" %(key,value.decode('utf-8')[:40]))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "n9_FOwMZ7yyi"
      },
      "source": [
        "Finalmente, también se puede recorrer toda la tabla estableciendo filtros, que se estudiarán después. Se utiliza la función `scan`. Se puede iterar con los parámetros `key` y `data`. Por ejemplo, calcular el tamaño máximo de la longitud del texto de los posts:\n",
        "\n",
        "**(OJO, es un ejemplo, no se debería hacer así)**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "akRUvI4g7yyj"
      },
      "source": [
        "max_len = 0\n",
        "for key, data in posts().scan():\n",
        "    cur_len = len(data[b'rawdata:Body'].decode('utf-8'))\n",
        "    if cur_len > max_len:\n",
        "        max_len = cur_len\n",
        "\n",
        "print(\"Máxima longitud: %s caracteres.\" % (max_len))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "nGVT2PjD7yyp"
      },
      "source": [
        "### Construcción de estructuras anidadas\n",
        "\n",
        "Al igual que pasaba con MongoDB, las bases de datos NoSQL como en este caso HBase permiten almacenar estructuras de datos complejas. En nuestro caso vamos a agregar los comentarios de cada pregunta o respuesta (post) en columnas del mismo. Para ello, creamos una nueva familia de columnas `comments`.\n",
        "\n",
        "HBase es bueno para añadir columnas sencillas, por ejemplo que contengan un valor. Sin embargo, si queremos añadir objetos complejos, tenemos que jugar con la codificación de la familia de columnas y columna.\n",
        "\n",
        "Usaremos el shell porque `happybase` no permite alterar tablas ya creadas.\n",
        "\n",
        "En el `shell` de HBase pondremos lo siguiente:\n",
        "\n",
        "```\n",
        "disable 'posts'\n",
        "alter 'posts', {NAME => 'comments', VERSIONS => 1}\n",
        "enable 'posts'\n",
        "```\n",
        "\n",
        "Cada comentario que añadimos contiene, al menos:\n",
        "\n",
        "- un id único\n",
        "- un texto\n",
        "- un autor\n",
        "- etc.\n",
        "\n",
        "¿Cómo se consigue meterlo en una única familia de columnas?\n",
        "\n",
        "Hay varias formas. La que usaremos aquí, añadiremos el **id** de cada comentario como parte del nombre de la columna. Por ejemplo, el comentario con Id 2000, generará las columnas:\n",
        "\n",
        "- `Id_2000` (valor 2000)\n",
        "- `UserId_2000`\n",
        "- `PostId_2000`\n",
        "- `Text_2000`\n",
        "\n",
        "con sus correspondientes valores. Así, todos los datos relativos al comentario con Id original 2000, estarán almacenados en todas las columnas que terminen en \"`_2000`\". La base de datos permite implementar filtros que nos permiten buscar esto de forma muy sencilla. Los veremos después."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "d1H07CoYEtst"
      },
      "source": [
        "!hbase-$HBASE_VERSION/bin/hbase shell"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VEc-wiyP7yyr"
      },
      "source": [
        "with hbasecon.connection() as connection:\n",
        "    comments = connection.table('comments')\n",
        "    posts = connection.table('posts')\n",
        "\n",
        "    with posts.batch(batch_size=500) as bp:\n",
        "        # Hacer un scan de la tabla\n",
        "        for key, data in comments.scan():\n",
        "            comment = {'comments:' +\n",
        "                       d.decode('utf-8').split(':')[1] + \"_\" +\n",
        "                       key.decode('utf-8') : \n",
        "                       data[d] for d in data.keys()}\n",
        "            bp.put(data[b'rawdata:PostId'], comment)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8yNVgVH6FRBI"
      },
      "source": [
        "download_csv('https://github.com/dsevilla/bdge-data/blob/master/wikipedia/','eswiki.xml')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yoDUWrB77yy1"
      },
      "source": [
        "Se crea la tabla para albergar la `wikipedia`. Igual que la vista en teoría, pero aquí se usa `wikipedia` en vez de `wiki` para que no colisionen la versión completa con la reducida."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UgKuiEMQ7yy2"
      },
      "source": [
        "De nuevo en el `shell` de HBase:\n",
        "\n",
        "```\n",
        "create 'wikipedia' , 'text', 'revision'\n",
        "disable 'wikipedia' # Para evitar su uso temporal\n",
        "alter 'wikipedia' , { NAME => 'text', VERSIONS => org.apache.hadoop.hbase.HConstants::ALL_VERSIONS }\n",
        "alter 'wikipedia' , { NAME => 'revision', VERSIONS => org.apache.hadoop.hbase.HConstants::ALL_VERSIONS }\n",
        "alter 'wikipedia' , { NAME => 'text', COMPRESSION => 'GZ', BLOOMFILTER => 'ROW'}\n",
        "enable 'wikipedia' \n",
        "```"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rAckssUxE-vu"
      },
      "source": [
        "!hbase-$HBASE_VERSION/bin/hbase shell"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pMAdxKEs7yy3"
      },
      "source": [
        "Este código, visto en teoría, recorre el árbol XML construyendo documentos y llamando a la función `callback` con cada uno. Los documentos son diccionarios con las claves encontradas dentro de los tags `<page>...</page>`."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "yhzpdKOs7yy4"
      },
      "source": [
        "import xml.sax\n",
        "import re\n",
        "\n",
        "class WikiHandler(xml.sax.handler.ContentHandler):\n",
        "\n",
        "    def __init__(self):\n",
        "        self._charBuffer = ''\n",
        "        self.document = {}\n",
        "\n",
        "    def _getCharacterData(self):\n",
        "        data = self._charBuffer\n",
        "        self._charBuffer = ''\n",
        "        return data\n",
        "\n",
        "    def parse(self, f, callback):\n",
        "        self.callback = callback\n",
        "        xml.sax.parse(f, self)\n",
        "\n",
        "    def characters(self, data):\n",
        "        self._charBuffer = self._charBuffer + data\n",
        "\n",
        "    def startElement(self, name, attrs):\n",
        "        if name == 'page':\n",
        "        # print 'Start of page'\n",
        "            self.document = {}\n",
        "        if re.match(r'title|timestamp|username|comment|text', name):\n",
        "            self._charBuffer = ''\n",
        "\n",
        "    def endElement(self, name):\n",
        "        if re.match(r'title|timestamp|username|comment|text', name):\n",
        "            self.document[name] = self._getCharacterData()\n",
        "            # print(name, ': ', self.document[name][:20])\n",
        "        if 'revision' == name:\n",
        "            self.callback(self.document)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WeJngcIN7yzA"
      },
      "source": [
        "El codigo a continuación, cada vez que el código anterior llama a la función `processdoc()` se añade un documento a la base de datos."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rJ199Q-57yzA"
      },
      "source": [
        "import time\n",
        "\n",
        "class FillWikiTable():\n",
        "    \"\"\"Llena la tabla Wiki\"\"\"\n",
        "    def __init__(self,connection):\n",
        "        # Conectar a la base de datos a través de Thrift\n",
        "        self.table = connection.table('wikipedia')\n",
        "\n",
        "    def run(_s):\n",
        "        def processdoc(d):\n",
        "            print(\"Callback called with {0}\".format(d['title']))\n",
        "            tuple_time = time.strptime(d['timestamp'], \"%Y-%m-%dT%H:%M:%SZ\")\n",
        "            timestamp = int(time.mktime(tuple_time))\n",
        "            _s.table.put(d['title'],\n",
        "                         {'text:': d.get('text',''),\n",
        "                          'revision:author': d.get('username',''),\n",
        "                          'revision:comment': d.get('comment','')},\n",
        "                         timestamp=timestamp)\n",
        "\n",
        "        with open('eswiki.xml','r') as f:\n",
        "            start = time.time()\n",
        "            WikiHandler().parse(f, processdoc)\n",
        "            end = time.time()\n",
        "            print (\"End adding documents. Time: %.5f\" % (end - start))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "w4YtbHJv7yzI"
      },
      "source": [
        "with hbasecon.connection() as connection:\n",
        "    FillWikiTable(connection).run()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "myMIS8dp7yzQ"
      },
      "source": [
        "El código a continuación permite ver las diferentes versiones de una revisión. Como la versión reducida es muy pequeña no da lugar a que haya ninguna revisión, pero con este código se vería. Hace uso del _shell_ de HBase:\n",
        "\n",
        "```    \n",
        "get 'wikipedia', 'Commodore Amiga', {COLUMN => 'revision',VERSIONS=>10}\n",
        "```"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PYz2G1mt7yzS"
      },
      "source": [
        "with hbasecon.connection() as connection:\n",
        "    wikipedia = connection.table('wikipedia')\n",
        "    for key,data in wikipedia.scan(columns=['revision'], row_start='A', row_stop='B', limit=10):\n",
        "        print(key,'->',data)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Ztu2ObZC7yza"
      },
      "source": [
        "La siguiente consulta no poduce resultados. ¿Por qué?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4CV5p3Q17yzb"
      },
      "source": [
        "with hbasecon.connection() as connection:\n",
        "    wikipedia = connection.table('wikipedia')\n",
        "    for key,data in wikipedia.scan(columns=['revision'], row_start='A', row_stop='B',\n",
        "                                   filter=\"PrefixFilter('B')\",\n",
        "                                   limit=10):\n",
        "        print (key,'->',data)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "t12MSPh37yzj"
      },
      "source": [
        "## En los siguientes ejercicios, usando `happybase`, utiliza o bien `scan` o bien `row` donde sea conveniente:"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kfnKjWjA7yzl"
      },
      "source": [
        "## EJERCICIO: Mostrar la familia de columnas `revision` para la entrada `ASCII` de la tabla `wikipedia`."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PJC4mECT7yzn"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "edzW7ZPs7yzu"
      },
      "source": [
        "## EJERCICIO: Mostrar las 20 primeras filas de la tabla `wikipedia` cuyas columnas empiecen por 'com'."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FQpZfHEs7yzz"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "As3LYDng7yz6"
      },
      "source": [
        "## EJERCICIO: Mostrar las 20 primeras filas de la tabla `wikipedia` cuyas columnas empiecen por 'com' y la clave de fila empieza por 'B'."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gYESBtOi7yz8"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "S4a77o967y0F"
      },
      "source": [
        "## EJERCICIO: Mostrar sólo la columna `revision:author` de las filas de la tabla `wikipedia` cuya clave empiece por `a` y termine por `a` (obviando mayúsculas y minúsculas)."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ZxSo3H2t7y0I"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EBkZ4a7Q7y0Q"
      },
      "source": [
        "## EJERCICIO: Mostrar las filas de la tabla `users` (sólo la columna `rawdata:Location`) de usuarios de España (se supondrá que su localización (columna `rawdata:Location`) contiene `España` o `ES`, obviando mayúsculas y minúsculas)."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "c1ke67qn7y0Q"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PJz1lQYS7y0X"
      },
      "source": [
        "## EJERCICIO: Crear una nueva tabla `poststags` que, de forma eficiente, para cada _tag_, liste los `Id` de los posts que utilizan ese _tag_.\n",
        "\n",
        "*Nota: El código de ejemplo para separar todas las etiquetas del campo Tags de un post está en la sesión 1.*"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Gu0tAb007y0Y"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vyWa2IdE7y0d"
      },
      "source": [
        "## EJERCICIO: Construya una tabla (de la forma más eficiente) que sirva para, dados un ID de usuario que pregunta y un ID de otro usuario que responde, se pueda decir (en orden constante) si forman una pareja al estilo de la RQ4. Con esa tabla, responder a la RQ4."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vpDD6N1b7y0e"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}<commit_msg>Update sesion6.ipynb<commit_after>{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.7.3"
    },
    "colab": {
      "name": "sesion6.ipynb",
      "provenance": []
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ft50fw517yvk"
      },
      "source": [
        "# NoSQL (HBase) (sesión 6)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "wiopea6aOBbi"
      },
      "source": [
        "![hbase-logo.e139b77f7031062f738f0fc28210e0ffa6ca26c8.png](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAkoAAAGqCAIAAAB77fzRAAAAGXRFWHRTb2Z0d2FyZQBBZG9iZSBJbWFnZVJlYWR5ccllPAAANWVJREFUeNrtnQd4FVX+v2NZ96/+sO+q+LOs/tayxbKgW1zLFtetimXdVUGxo4JiwYrComJdsWKXFcuqq6KugqsoCpZUQgokQCAFSCCUhIQUQhL+n2TW+1zmtrl35t4p932feXxiSCZ3zpw575xzvud7cjYDAAAEjhyKAAAA0BsAAAB6AwAAQG8AAADoDQAAAL0BAAB6AwAAQG8AAADoDQAAAL0BAACgNwAAQG8AAADoDQAAAL0BAACgNwAAAPQGAACA3gAAAL0BAACgNwAAAPQGAACA3gAAANAbAACgNwAAAPQGAACA3gAAANAbAAAA<KEY>AAQG8AAADoDQAAAL0BAACgNwAAAPQGAACA3gAAAL0BAACgNwAAAPQGAACA3gAAANAbAACgNwAAAPQGAACA3gAAANAbAAAAegMAAEBvAACA3gAAANAbAAAAegMAAEBvAAAA6A0AANAbAAAAegMAAEBvAAAA6A0AAAC9AQAAoDcAAEBvAAAA6A0AAAC9AQAAoDcAAAD0BgAA6A0AAAC9AQAAoDcAAAD0BgAAgN4AAADQGwAAoDcAAAD0BgAAgN4AAADQGwAAAHoDAAD0BgAAgN4AAADQGwAAAHoDAABImv8Pa99NbmfcsbgAAAAASUVORK5CYII=)\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4HLSQ6r6Ok9v"
      },
      "source": [
        "Esta hoja muestra cómo acceder a bases de datos HBase y también a conectar la salida con Jupyter.\n",
        "\n",
        "Se puede utilizar el *shell* propio de HBase en la máquina virtual.\n",
        "\n",
        "Con HBase vamos a simular un modo _semi-clúster_ con una sola máquina.\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "julNKL3JQZRW"
      },
      "source": [
        "Instalamos la versión 8 de Java y la seleccionamos como opción por defecto:\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "K60d3mLKZg6x"
      },
      "source": [
        "%%capture\n",
        "import os       #importing os to set environment variable\n",
        "def install_java():\n",
        "  !apt-get update -qq > /dev/null\n",
        "  !apt-get install -y openjdk-8-jdk-headless -qq > /dev/null      #install openjdk\n",
        "  os.environ[\"JAVA_HOME\"] = \"/usr/lib/jvm/java-8-openjdk-amd64\"     #set environment variable\n",
        "  !java -version       #check java version\n",
        "install_java()\n",
        "!update-java-alternatives -s java-1.8.0-openjdk-amd64"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "nqEVgAChMFcR"
      },
      "source": [
        "%env HBASE_VERSION=2.4.7"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9hv1DUSoFOys"
      },
      "source": [
        "%%bash \n",
        "wget -q https://downloads.apache.org/hbase/$HBASE_VERSION/hbase-$HBASE_VERSION-bin.tar.gz\n",
        "tar -xzf hbase-$HBASE_VERSION-bin.tar.gz"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "uMmB5l_oaNJD"
      },
      "source": [
        "%%bash\n",
        "hbase-$HBASE_VERSION/bin/start-hbase.sh\n",
        "hbase-$HBASE_VERSION/bin/hbase-daemon.sh restart thrift"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_PjCnzExOBbo"
      },
      "source": [
        "from pprint import pprint as pp\n",
        "import pandas as pd\n",
        "import matplotlib.pyplot as plt\n",
        "import matplotlib\n",
        "\n",
        "%matplotlib inline\n",
        "matplotlib.style.use('ggplot')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "h8JfspqJOBb_"
      },
      "source": [
        "Usaremos la librería `happybase` para python. La cargamos a continuación y hacemos la conexión."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IVq4QcNzOBcQ"
      },
      "source": [
        "!pip install happybase"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PP5jW7W0IWrF"
      },
      "source": [
        "import gzip\n",
        "from urllib.request import Request,urlopen\n",
        "import io\n",
        "import os\n",
        "import os.path as path\n",
        "\n",
        "def download_csv(baseurl, filename):\n",
        "    file = path.abspath(path.join(os.getcwd(),filename))\n",
        "    request = Request(baseurl + '/' + filename+'.gz?raw=true')\n",
        "    response = urlopen(request)\n",
        "    buf = io.BytesIO(response.read())\n",
        "    f = gzip.GzipFile(fileobj=buf)\n",
        "    data = f.read()\n",
        "    with open (filename, 'wb') as ff:\n",
        "      ff.write(data)\n",
        "        \n",
        "baseurl = 'https://github.com/dsevilla/bdge-data/blob/master/es.stackoverflow/'\n",
        "download_csv(baseurl, 'Posts.csv')\n",
        "download_csv(baseurl, 'Users.csv')\n",
        "download_csv(baseurl, 'Tags.csv')\n",
        "download_csv(baseurl, 'Comments.csv')\n",
        "download_csv(baseurl, 'Votes.csv')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VnqPwDMR7ywp"
      },
      "source": [
        "import happybase\n",
        "from contextlib import contextmanager\n",
        "\n",
        "HBASEHOST = 'localhost'\n",
        "\n",
        "class Connection():\n",
        "    \n",
        "    def __init__(self, host):\n",
        "        self.host = host\n",
        "        self._genpool()\n",
        "        \n",
        "    def _genpool(self):\n",
        "        self.pool = happybase.ConnectionPool(size=5, host=self.host)\n",
        "    \n",
        "    @contextmanager\n",
        "    def connection(self):\n",
        "        for _ in range(5): # Probar 5 veces a regenerar el pool\n",
        "            for _ in range(5): # Probar 5 veces a conectar\n",
        "                with self.pool.connection() as connection:\n",
        "                    try:\n",
        "                        connection.tables()\n",
        "                        yield connection\n",
        "                        return\n",
        "                    except Exception as e:\n",
        "                        pass\n",
        "            self._genpool()\n",
        "        raise Exception(\"HBase Connection Error\")"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "T8CWSFZa7yw7"
      },
      "source": [
        "hbasecon = Connection(HBASEHOST)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "30jYcJud7yxF"
      },
      "source": [
        "with hbasecon.connection() as connection:\n",
        "    print(connection.tables())"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GT37NUcY7yxQ"
      },
      "source": [
        "Para la carga inicial, vamos a crear todas las tablas con una única familia de columnas, `rawdata`, donde meteremos toda la información _raw_ comprimida. Después podremos hacer reorganizaciones de los datos para hacer el acceso más eficiente. Es una de las muchas ventajas de no tener un esquema."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DLLkGnnQ7yxR"
      },
      "source": [
        "# Create tables\n",
        "tables = ['posts', 'votes', 'users', 'tags', 'comments']\n",
        "for t in tables:\n",
        "    try:\n",
        "        with hbasecon.connection() as connection:\n",
        "            connection.create_table(\n",
        "                t,\n",
        "                {\n",
        "                    'rawdata': dict(max_versions=1,compression='GZ')\n",
        "                })\n",
        "    except Exception as e:\n",
        "        print(\"Database already exists: {0}. {1}\".format(t, e))\n",
        "        pass\n",
        "with hbasecon.connection() as connection:\n",
        "    print(connection.tables())"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Y07GxAB77yxX"
      },
      "source": [
        "El código de importación es siempre el mismo, ya que se coge la primera fila del CSV que contiene el nombre de las columnas y se utiliza para generar nombres de columnas dentro de la familia de columnas dada como parámetro. La función `csv_to_hbase()` acepta un fichero CSV a abrir, un nombre de tabla y una familia de columnas donde agregar las columnas del fichero CSV. En nuestro caso siempre va a ser `rawdata`."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1dAKqxpH7yxZ"
      },
      "source": [
        "import csv\n",
        "\n",
        "def csv_to_hbase(file, tablename, cf):\n",
        "    with hbasecon.connection() as connection, open(file) as f:\n",
        "        table = connection.table(tablename)\n",
        "\n",
        "        # La llamada csv.reader() crea un iterador sobre un fichero CSV\n",
        "        reader = csv.reader(f, dialect='excel')\n",
        "        \n",
        "        # Se leen las columnas. Sus nombres se usarán para crear las diferentes columnas en la familia\n",
        "        columns = next(reader)\n",
        "        columns = [cf + ':' + c for c in columns]\n",
        "        \n",
        "        with table.batch(batch_size=500) as b:\n",
        "            for row in reader:\n",
        "                # La primera columna se usará como Row Key\n",
        "                b.put(row[0], dict(zip(columns[1:], row[1:])))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "3mkoAxWY7yxg"
      },
      "source": [
        "for t in tables:\n",
        "    print(\"Importando tabla {0}...\".format(t))\n",
        "    %time csv_to_hbase(t.capitalize() + '.csv', t, 'rawdata')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "JRlhb2x17yxo"
      },
      "source": [
        "### Consultas sencillas desde Python\n",
        "\n",
        "A continuación veremos algunas consultas sencillas desde python usando el API de `happybase`."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "AbUFk3zV7yxq"
      },
      "source": [
        "def posts():\n",
        "  with hbasecon.connection() as connection:\n",
        "    return connection.table('posts')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "72e7fcjE7yxz"
      },
      "source": [
        "Obtener el Post con `Id` 5. La orden más sencilla e inmediata de HBase es obtener una fila, opcionalmente limitando las columnas a mostrar:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1Wii2xrl7yx2"
      },
      "source": [
        "posts().row(b'5',columns=[b'rawdata:Body'])"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "I_AL9JlL7yyB"
      },
      "source": [
        "El siguiente código permite mostrar de forma amigable las tablas extraídas de la base de datos en forma de diccionario:"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4ZfMeUZv7yyD"
      },
      "source": [
        "# http://stackoverflow.com/a/30525061/62365\n",
        "class DictTable(dict):\n",
        "    # Overridden dict class which takes a dict in the form {'a': 2, 'b': 3},\n",
        "    # and renders an HTML Table in IPython Notebook.\n",
        "    def _repr_html_(self):\n",
        "        htmltext = [\"<table width=100%>\"]\n",
        "        for key, value in self.items():\n",
        "            htmltext.append(\"<tr>\")\n",
        "            htmltext.append(\"<td>{0}</td>\".format(key.decode('utf-8')))\n",
        "            htmltext.append(\"<td>{0}</td>\".format(value.decode('utf-8')))\n",
        "            htmltext.append(\"</tr>\")\n",
        "        htmltext.append(\"</table>\")\n",
        "        return ''.join(htmltext)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "scrolled": true,
        "id": "OTKDiVrr7yyN"
      },
      "source": [
        "# Muestra cómo queda la fila del Id del Post 9997\n",
        "DictTable(posts().row(b'5'))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ydnhHy9r7yyT"
      },
      "source": [
        "DictTable(posts().row(b'5',columns=[b'rawdata:AnswerCount',b'rawdata:AcceptedAnswerId']))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GciY3L4X7yyb"
      },
      "source": [
        "Y también se puede recorrer como un diccionario normal (el `decode` se utiliza para convertir los valores binarios de la base de datos a una codificación UTF-8):"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "h1Iit47u7yyd"
      },
      "source": [
        "row = posts().row(b'5')\n",
        "for key, value in row.items():\n",
        "    print(\"Key = '%s', Value = '%s'\" %(key,value.decode('utf-8')[:40]))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "n9_FOwMZ7yyi"
      },
      "source": [
        "Finalmente, también se puede recorrer toda la tabla estableciendo filtros, que se estudiarán después. Se utiliza la función `scan`. Se puede iterar con los parámetros `key` y `data`. Por ejemplo, calcular el tamaño máximo de la longitud del texto de los posts:\n",
        "\n",
        "**(OJO, es un ejemplo, no se debería hacer así)**"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "akRUvI4g7yyj"
      },
      "source": [
        "max_len = 0\n",
        "for key, data in posts().scan():\n",
        "    cur_len = len(data[b'rawdata:Body'].decode('utf-8'))\n",
        "    if cur_len > max_len:\n",
        "        max_len = cur_len\n",
        "\n",
        "print(\"Máxima longitud: %s caracteres.\" % (max_len))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "nGVT2PjD7yyp"
      },
      "source": [
        "### Construcción de estructuras anidadas\n",
        "\n",
        "Al igual que pasaba con MongoDB, las bases de datos NoSQL como en este caso HBase permiten almacenar estructuras de datos complejas. En nuestro caso vamos a agregar los comentarios de cada pregunta o respuesta (post) en columnas del mismo. Para ello, creamos una nueva familia de columnas `comments`.\n",
        "\n",
        "HBase es bueno para añadir columnas sencillas, por ejemplo que contengan un valor. Sin embargo, si queremos añadir objetos complejos, tenemos que jugar con la codificación de la familia de columnas y columna.\n",
        "\n",
        "Usaremos el shell porque `happybase` no permite alterar tablas ya creadas.\n",
        "\n",
        "En el `shell` de HBase pondremos lo siguiente:\n",
        "\n",
        "```\n",
        "disable 'posts'\n",
        "alter 'posts', {NAME => 'comments', VERSIONS => 1}\n",
        "enable 'posts'\n",
        "```\n",
        "\n",
        "Cada comentario que añadimos contiene, al menos:\n",
        "\n",
        "- un id único\n",
        "- un texto\n",
        "- un autor\n",
        "- etc.\n",
        "\n",
        "¿Cómo se consigue meterlo en una única familia de columnas?\n",
        "\n",
        "Hay varias formas. La que usaremos aquí, añadiremos el **id** de cada comentario como parte del nombre de la columna. Por ejemplo, el comentario con Id 2000, generará las columnas:\n",
        "\n",
        "- `Id_2000` (valor 2000)\n",
        "- `UserId_2000`\n",
        "- `PostId_2000`\n",
        "- `Text_2000`\n",
        "\n",
        "con sus correspondientes valores. Así, todos los datos relativos al comentario con Id original 2000, estarán almacenados en todas las columnas que terminen en \"`_2000`\". La base de datos permite implementar filtros que nos permiten buscar esto de forma muy sencilla. Los veremos después."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "d1H07CoYEtst"
      },
      "source": [
        "!hbase-$HBASE_VERSION/bin/hbase shell"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VEc-wiyP7yyr"
      },
      "source": [
        "with hbasecon.connection() as connection:\n",
        "    comments = connection.table('comments')\n",
        "    posts = connection.table('posts')\n",
        "\n",
        "    with posts.batch(batch_size=500) as bp:\n",
        "        # Hacer un scan de la tabla\n",
        "        for key, data in comments.scan():\n",
        "            comment = {'comments:' +\n",
        "                       d.decode('utf-8').split(':')[1] + \"_\" +\n",
        "                       key.decode('utf-8') : \n",
        "                       data[d] for d in data.keys()}\n",
        "            bp.put(data[b'rawdata:PostId'], comment)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "8yNVgVH6FRBI"
      },
      "source": [
        "download_csv('https://github.com/dsevilla/bdge-data/blob/master/wikipedia/','eswiki.xml')"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yoDUWrB77yy1"
      },
      "source": [
        "Se crea la tabla para albergar la `wikipedia`. Igual que la vista en teoría, pero aquí se usa `wikipedia` en vez de `wiki` para que no colisionen la versión completa con la reducida."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UgKuiEMQ7yy2"
      },
      "source": [
        "De nuevo en el `shell` de HBase:\n",
        "\n",
        "```\n",
        "create 'wikipedia' , 'text', 'revision'\n",
        "disable 'wikipedia' # Para evitar su uso temporal\n",
        "alter 'wikipedia' , { NAME => 'text', VERSIONS => org.apache.hadoop.hbase.HConstants::ALL_VERSIONS }\n",
        "alter 'wikipedia' , { NAME => 'revision', VERSIONS => org.apache.hadoop.hbase.HConstants::ALL_VERSIONS }\n",
        "alter 'wikipedia' , { NAME => 'text', COMPRESSION => 'GZ', BLOOMFILTER => 'ROW'}\n",
        "enable 'wikipedia' \n",
        "```"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rAckssUxE-vu"
      },
      "source": [
        "!hbase-$HBASE_VERSION/bin/hbase shell"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pMAdxKEs7yy3"
      },
      "source": [
        "Este código, visto en teoría, recorre el árbol XML construyendo documentos y llamando a la función `callback` con cada uno. Los documentos son diccionarios con las claves encontradas dentro de los tags `<page>...</page>`."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "yhzpdKOs7yy4"
      },
      "source": [
        "import xml.sax\n",
        "import re\n",
        "\n",
        "class WikiHandler(xml.sax.handler.ContentHandler):\n",
        "\n",
        "    def __init__(self):\n",
        "        self._charBuffer = ''\n",
        "        self.document = {}\n",
        "\n",
        "    def _getCharacterData(self):\n",
        "        data = self._charBuffer\n",
        "        self._charBuffer = ''\n",
        "        return data\n",
        "\n",
        "    def parse(self, f, callback):\n",
        "        self.callback = callback\n",
        "        xml.sax.parse(f, self)\n",
        "\n",
        "    def characters(self, data):\n",
        "        self._charBuffer = self._charBuffer + data\n",
        "\n",
        "    def startElement(self, name, attrs):\n",
        "        if name == 'page':\n",
        "        # print 'Start of page'\n",
        "            self.document = {}\n",
        "        if re.match(r'title|timestamp|username|comment|text', name):\n",
        "            self._charBuffer = ''\n",
        "\n",
        "    def endElement(self, name):\n",
        "        if re.match(r'title|timestamp|username|comment|text', name):\n",
        "            self.document[name] = self._getCharacterData()\n",
        "            # print(name, ': ', self.document[name][:20])\n",
        "        if 'revision' == name:\n",
        "            self.callback(self.document)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WeJngcIN7yzA"
      },
      "source": [
        "El codigo a continuación, cada vez que el código anterior llama a la función `processdoc()` se añade un documento a la base de datos."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rJ199Q-57yzA"
      },
      "source": [
        "import time\n",
        "\n",
        "class FillWikiTable():\n",
        "    \"\"\"Llena la tabla Wiki\"\"\"\n",
        "    def __init__(self,connection):\n",
        "        # Conectar a la base de datos a través de Thrift\n",
        "        self.table = connection.table('wikipedia')\n",
        "\n",
        "    def run(_s):\n",
        "        def processdoc(d):\n",
        "            print(\"Callback called with {0}\".format(d['title']))\n",
        "            tuple_time = time.strptime(d['timestamp'], \"%Y-%m-%dT%H:%M:%SZ\")\n",
        "            timestamp = int(time.mktime(tuple_time))\n",
        "            _s.table.put(d['title'],\n",
        "                         {'text:': d.get('text',''),\n",
        "                          'revision:author': d.get('username',''),\n",
        "                          'revision:comment': d.get('comment','')},\n",
        "                         timestamp=timestamp)\n",
        "\n",
        "        with open('eswiki.xml','r') as f:\n",
        "            start = time.time()\n",
        "            WikiHandler().parse(f, processdoc)\n",
        "            end = time.time()\n",
        "            print (\"End adding documents. Time: %.5f\" % (end - start))"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "w4YtbHJv7yzI"
      },
      "source": [
        "with hbasecon.connection() as connection:\n",
        "    FillWikiTable(connection).run()"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "myMIS8dp7yzQ"
      },
      "source": [
        "El código a continuación permite ver las diferentes versiones de una revisión. Como la versión reducida es muy pequeña no da lugar a que haya ninguna revisión, pero con este código se vería. Hace uso del _shell_ de HBase:\n",
        "\n",
        "```    \n",
        "get 'wikipedia', 'Commodore Amiga', {COLUMN => 'revision',VERSIONS=>10}\n",
        "```"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PYz2G1mt7yzS"
      },
      "source": [
        "with hbasecon.connection() as connection:\n",
        "    wikipedia = connection.table('wikipedia')\n",
        "    for key,data in wikipedia.scan(columns=['revision'], row_start='A', row_stop='B', limit=10):\n",
        "        print(key,'->',data)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Ztu2ObZC7yza"
      },
      "source": [
        "La siguiente consulta no poduce resultados. ¿Por qué?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4CV5p3Q17yzb"
      },
      "source": [
        "with hbasecon.connection() as connection:\n",
        "    wikipedia = connection.table('wikipedia')\n",
        "    for key,data in wikipedia.scan(columns=['revision'], row_start='A', row_stop='B',\n",
        "                                   filter=\"PrefixFilter('B')\",\n",
        "                                   limit=10):\n",
        "        print (key,'->',data)"
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "t12MSPh37yzj"
      },
      "source": [
        "## En los siguientes ejercicios, usando `happybase`, utiliza o bien `scan` o bien `row` donde sea conveniente:"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "kfnKjWjA7yzl"
      },
      "source": [
        "## EJERCICIO: Mostrar la familia de columnas `revision` para la entrada `ASCII` de la tabla `wikipedia`."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "PJC4mECT7yzn"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "edzW7ZPs7yzu"
      },
      "source": [
        "## EJERCICIO: Mostrar las 20 primeras filas de la tabla `wikipedia` cuyas columnas empiecen por 'com'."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "FQpZfHEs7yzz"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "As3LYDng7yz6"
      },
      "source": [
        "## EJERCICIO: Mostrar las 20 primeras filas de la tabla `wikipedia` cuyas columnas empiecen por 'com' y la clave de fila empieza por 'B'."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "gYESBtOi7yz8"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "S4a77o967y0F"
      },
      "source": [
        "## EJERCICIO: Mostrar sólo la columna `revision:author` de las filas de la tabla `wikipedia` cuya clave empiece por `a` y termine por `a` (obviando mayúsculas y minúsculas)."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ZxSo3H2t7y0I"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EBkZ4a7Q7y0Q"
      },
      "source": [
        "## EJERCICIO: Mostrar las filas de la tabla `users` (sólo la columna `rawdata:Location`) de usuarios de España (se supondrá que su localización (columna `rawdata:Location`) contiene `España` o `ES`, obviando mayúsculas y minúsculas)."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "c1ke67qn7y0Q"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "PJz1lQYS7y0X"
      },
      "source": [
        "## EJERCICIO: Crear una nueva tabla `poststags` que, de forma eficiente, para cada _tag_, liste los `Id` de los posts que utilizan ese _tag_.\n",
        "\n",
        "*Nota: El código de ejemplo para separar todas las etiquetas del campo Tags de un post está en la sesión 1.*"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Gu0tAb007y0Y"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vyWa2IdE7y0d"
      },
      "source": [
        "## EJERCICIO: Construya una tabla (de la forma más eficiente) que sirva para, dados un ID de usuario que pregunta y un ID de otro usuario que responde, se pueda decir (en orden constante) si forman una pareja al estilo de la RQ4. Con esa tabla, responder a la RQ4."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vpDD6N1b7y0e"
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}

{'fraction_non_alphanumeric': 0.16266869095816464, 'fraction_numerical': 0.01325910931174089, 'mean_word_length': 1.7805347091932457, 'pattern_counts': {'":': 692, '<': 25, '<?xml version=': 0, '>': 57, 'https://': 6, 'lorem ipsum': 0, 'www.': 0, 'xml': 10}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 32, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '467381', 'n_tokens_mistral': 21314, 'n_tokens_neox': 20635, 'n_words': 4289}