Word_Embeddings_MySQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Word Embeddings in MySQL\n",
    "\n",
    "This example uses the official MySQL Connector within Python3 to store and retrieve various amounts of Word Embeddings.\n",
    "\n",
    "We will use a local MySQL database running as a Docker Container for testing purposes. To start the database run:\n",
    "\n",
    "```\n",
    "docker run -ti --rm --name ohmysql -e MYSQL_ROOT_PASSWORD=mikolov -e MYSQL_DATABASE=embeddings -p 3306:3306 mysql:5.7\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector\n",
    "import io\n",
    "import time\n",
    "import numpy\n",
    "import plotly\n",
    "from tqdm import tqdm_notebook as tqdm"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Dummy Embeddings\n",
    "\n",
    "For testing purposes we will use randomly generated numpy arrays as dummy embbeddings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def embeddings(n=1000, dim=300):\n",
    "    \"\"\"\n",
    "    Yield n tuples of random numpy arrays of *dim* length indexed by *n*\n",
    "    \"\"\"\n",
    "    idx = 0\n",
    "    while idx < n:\n",
    "        yield (str(idx), numpy.random.rand(dim))\n",
    "        idx += 1"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Conversion Functions\n",
    "\n",
    "Since we can't just save a NumPy array into the database, we will convert it into a BLOB."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def adapt_array(array):\n",
    "    \"\"\"\n",
    "    Using the numpy.save function to save a binary version of the array,\n",
    "    and BytesIO to catch the stream of data and convert it into a BLOB.\n",
    "    \"\"\"\n",
    "    out = io.BytesIO()\n",
    "    numpy.save(out, array)\n",
    "    out.seek(0)\n",
    "\n",
    "    return out.read()\n",
    "\n",
    "def convert_array(blob):\n",
    "    \"\"\"\n",
    "    Using BytesIO to convert the binary version of the array back into a numpy array.\n",
    "    \"\"\"\n",
    "    out = io.BytesIO(blob)\n",
    "    out.seek(0)\n",
    "\n",
    "    return numpy.load(out)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = mysql.connector.connect(user='root', password='mikolov',\n",
    "                              host='127.0.0.1',\n",
    "                              database='embeddings')\n",
    "\n",
    "cursor = connection.cursor()\n",
    "cursor.execute('CREATE TABLE IF NOT EXISTS `embeddings` (`key` TEXT, `embedding` BLOB);')\n",
    "connection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1.58 s, sys: 170 ms, total: 1.75 s\n",
      "Wall time: 1min 55s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "for key, emb in embeddings():\n",
    "    arr = adapt_array(emb)\n",
    "    cursor.execute('INSERT INTO `embeddings` (`key`, `embedding`) VALUES (%s, %s);', (key, arr))\n",
    "    connection.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 393 ms, sys: 42.7 ms, total: 435 ms\n",
      "Wall time: 1.26 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "for key, _ in embeddings():\n",
    "    cursor.execute('SELECT embedding FROM `embeddings` WHERE `key`=%s;', (key,))\n",
    "    data = cursor.fetchone()\n",
    "    arr = convert_array(data[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Sample some data\n",
    "\n",
    "To test the I/O we will write and read some data from the database. This may take a while."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "500\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "4e20bb4a9c1f4d1894ee07047f791f13",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=500), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "1000\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a2021b7ea3574e5b83b4b979c5311511",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=1000), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "2000\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "1097d7445cff497c981e4c722b0d2297",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=2000), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "3000\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "c5b0c58b705544808be945e6d5d39f27",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=3000), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "4000\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "19503ee28fad43139df4c77cf288c1bc",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=4000), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "5000\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "f90096ec8a1b4863a12a831bbf0dc955",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=5000), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "DONE\n"
     ]
    }
   ],
   "source": [
    "write_times = []\n",
    "read_times = []\n",
    "counts = [500, 1000, 2000, 3000, 4000, 5000]\n",
    "\n",
    "for c in counts:\n",
    "    print(c)\n",
    "    cursor.execute('DROP TABLE IF EXISTS `embeddings`;')\n",
    "    cursor.execute('CREATE TABLE IF NOT EXISTS `embeddings` (`key` TEXT, `embedding` BLOB);')\n",
    "    connection.commit()\n",
    "\n",
    "    start_time_write = time.time()\n",
    "    for key, emb in tqdm(embeddings(c), total=c):\n",
    "        arr = adapt_array(emb)\n",
    "        cursor.execute('INSERT INTO `embeddings` (`key`, `embedding`) VALUES (%s, %s);', (key, arr))\n",
    "        connection.commit()\n",
    "    write_times.append(time.time() - start_time_write)\n",
    "    \n",
    "    start_time_read = time.time()\n",
    "    for key, emb in embeddings(c):\n",
    "        cursor.execute('SELECT embedding FROM `embeddings` WHERE `key`=%s;', (key,))\n",
    "        data = cursor.fetchone()\n",
    "        arr = convert_array(data[0])\n",
    "    read_times.append(time.time() - start_time_read)\n",
    "    \n",
    "print('DONE')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<script>requirejs.config({paths: { 'plotly': ['https://cdn.plot.ly/plotly-latest.min']},});if(!window.Plotly) {{require(['plotly'],function(plotly) {window.Plotly=plotly;});}}</script>"
      ],
      "text/vnd.plotly.v1+html": [
       "<script>requirejs.config({paths: { 'plotly': ['https://cdn.plot.ly/plotly-latest.min']},});if(!window.Plotly) {{require(['plotly'],function(plotly) {window.Plotly=plotly;});}}</script>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.plotly.v1+json": {
       "data": [
        {
         "mode": "lines+markers",
         "type": "scatter",
         "uid": "ae608ed4-af8f-11e8-8bf3-94b86d86a98e",
         "x": [
          500,
          1000,
          2000,
          3000,
          4000,
          5000
         ],
         "y": [
          59.611292362213135,
          148.12936902046204,
          231.10561180114746,
          370.53470754623413,
          511.198233127594,
          641.969072341919
         ]
        }
       ],
       "layout": {
        "title": "MySQL Write Times",
        "xaxis": {
         "title": "Embedding Count"
        },
        "yaxis": {
         "title": "Time in Seconds"
        }
       }
      },
      "text/html": [
       "<div id=\"e91c585b-0871-461b-868a-ccb25bf8ba6a\" style=\"height: 525px; width: 100%;\" class=\"plotly-graph-div\"></div><script type=\"text/javascript\">require([\"plotly\"], function(Plotly) { window.PLOTLYENV=window.PLOTLYENV || {};window.PLOTLYENV.BASE_URL=\"https://plot.ly\";\n",
       "        Plotly.plot(\n",
       "            'e91c585b-0871-461b-868a-ccb25bf8ba6a',\n",
       "            [{\"mode\": \"lines+markers\", \"x\": [500, 1000, 2000, 3000, 4000, 5000], \"y\": [59.611292362213135, 148.12936902046204, 231.10561180114746, 370.53470754623413, 511.198233127594, 641.969072341919], \"type\": \"scatter\", \"uid\": \"ae608ed4-af8f-11e8-8bf3-94b86d86a98e\"}],\n",
       "            {\"title\": \"MySQL Write Times\", \"xaxis\": {\"title\": \"Embedding Count\"}, \"yaxis\": {\"title\": \"Time in Seconds\"}},\n",
       "            {\"showLink\": true, \"linkText\": \"Export to plot.ly\"}\n",
       "        ).then(function () {return Plotly.addFrames('e91c585b-0871-461b-868a-ccb25bf8ba6a',{});}).then(function(){Plotly.animate('e91c585b-0871-461b-868a-ccb25bf8ba6a');})\n",
       "        });</script>"
      ],
      "text/vnd.plotly.v1+html": [
       "<div id=\"e91c585b-0871-461b-868a-ccb25bf8ba6a\" style=\"height: 525px; width: 100%;\" class=\"plotly-graph-div\"></div><script type=\"text/javascript\">require([\"plotly\"], function(Plotly) { window.PLOTLYENV=window.PLOTLYENV || {};window.PLOTLYENV.BASE_URL=\"https://plot.ly\";\n",
       "        Plotly.plot(\n",
       "            'e91c585b-0871-461b-868a-ccb25bf8ba6a',\n",
       "            [{\"mode\": \"lines+markers\", \"x\": [500, 1000, 2000, 3000, 4000, 5000], \"y\": [59.611292362213135, 148.12936902046204, 231.10561180114746, 370.53470754623413, 511.198233127594, 641.969072341919], \"type\": \"scatter\", \"uid\": \"ae608ed4-af8f-11e8-8bf3-94b86d86a98e\"}],\n",
       "            {\"title\": \"MySQL Write Times\", \"xaxis\": {\"title\": \"Embedding Count\"}, \"yaxis\": {\"title\": \"Time in Seconds\"}},\n",
       "            {\"showLink\": true, \"linkText\": \"Export to plot.ly\"}\n",
       "        ).then(function () {return Plotly.addFrames('e91c585b-0871-461b-868a-ccb25bf8ba6a',{});}).then(function(){Plotly.animate('e91c585b-0871-461b-868a-ccb25bf8ba6a');})\n",
       "        });</script>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "plotly.offline.init_notebook_mode(connected=True)\n",
    "trace = plotly.graph_objs.Scatter(\n",
    "    y = write_times,\n",
    "    x = counts,\n",
    "    mode = 'lines+markers'\n",
    ")\n",
    "layout = plotly.graph_objs.Layout(title=\"MySQL Write Times\",\n",
    "                yaxis=dict(title='Time in Seconds'),\n",
    "                xaxis=dict(title='Embedding Count'))\n",
    "data = [trace]\n",
    "fig = plotly.graph_objs.Figure(data=data, layout=layout)\n",
    "plotly.offline.iplot(fig, filename='jupyter-scatter-write')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<script>requirejs.config({paths: { 'plotly': ['https://cdn.plot.ly/plotly-latest.min']},});if(!window.Plotly) {{require(['plotly'],function(plotly) {window.Plotly=plotly;});}}</script>"
      ],
      "text/vnd.plotly.v1+html": [
       "<script>requirejs.config({paths: { 'plotly': ['https://cdn.plot.ly/plotly-latest.min']},});if(!window.Plotly) {{require(['plotly'],function(plotly) {window.Plotly=plotly;});}}</script>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/vnd.plotly.v1+json": {
       "data": [
        {
         "mode": "lines+markers",
         "type": "scatter",
         "uid": "afe2dd8e-af8f-11e8-8bf3-94b86d86a98e",
         "x": [
          500,
          1000,
          2000,
          3000,
          4000,
          5000
         ],
         "y": [
          0.5181713104248047,
          1.3684329986572266,
          5.713267803192139,
          12.329740524291992,
          19.260291576385498,
          27.02959108352661
         ]
        }
       ],
       "layout": {
        "title": "MySQL Read Times",
        "xaxis": {
         "title": "Embedding Count"
        },
        "yaxis": {
         "title": "Time in Seconds"
        }
       }
      },
      "text/html": [
       "<div id=\"e470506f-6b81-4fde-a406-2ada908b1994\" style=\"height: 525px; width: 100%;\" class=\"plotly-graph-div\"></div><script type=\"text/javascript\">require([\"plotly\"], function(Plotly) { window.PLOTLYENV=window.PLOTLYENV || {};window.PLOTLYENV.BASE_URL=\"https://plot.ly\";\n",
       "        Plotly.plot(\n",
       "            'e470506f-6b81-4fde-a406-2ada908b1994',\n",
       "            [{\"mode\": \"lines+markers\", \"x\": [500, 1000, 2000, 3000, 4000, 5000], \"y\": [0.5181713104248047, 1.3684329986572266, 5.713267803192139, 12.329740524291992, 19.260291576385498, 27.02959108352661], \"type\": \"scatter\", \"uid\": \"afe2dd8e-af8f-11e8-8bf3-94b86d86a98e\"}],\n",
       "            {\"title\": \"MySQL Read Times\", \"xaxis\": {\"title\": \"Embedding Count\"}, \"yaxis\": {\"title\": \"Time in Seconds\"}},\n",
       "            {\"showLink\": true, \"linkText\": \"Export to plot.ly\"}\n",
       "        ).then(function () {return Plotly.addFrames('e470506f-6b81-4fde-a406-2ada908b1994',{});}).then(function(){Plotly.animate('e470506f-6b81-4fde-a406-2ada908b1994');})\n",
       "        });</script>"
      ],
      "text/vnd.plotly.v1+html": [
       "<div id=\"e470506f-6b81-4fde-a406-2ada908b1994\" style=\"height: 525px; width: 100%;\" class=\"plotly-graph-div\"></div><script type=\"text/javascript\">require([\"plotly\"], function(Plotly) { window.PLOTLYENV=window.PLOTLYENV || {};window.PLOTLYENV.BASE_URL=\"https://plot.ly\";\n",
       "        Plotly.plot(\n",
       "            'e470506f-6b81-4fde-a406-2ada908b1994',\n",
       "            [{\"mode\": \"lines+markers\", \"x\": [500, 1000, 2000, 3000, 4000, 5000], \"y\": [0.5181713104248047, 1.3684329986572266, 5.713267803192139, 12.329740524291992, 19.260291576385498, 27.02959108352661], \"type\": \"scatter\", \"uid\": \"afe2dd8e-af8f-11e8-8bf3-94b86d86a98e\"}],\n",
       "            {\"title\": \"MySQL Read Times\", \"xaxis\": {\"title\": \"Embedding Count\"}, \"yaxis\": {\"title\": \"Time in Seconds\"}},\n",
       "            {\"showLink\": true, \"linkText\": \"Export to plot.ly\"}\n",
       "        ).then(function () {return Plotly.addFrames('e470506f-6b81-4fde-a406-2ada908b1994',{});}).then(function(){Plotly.animate('e470506f-6b81-4fde-a406-2ada908b1994');})\n",
       "        });</script>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "plotly.offline.init_notebook_mode(connected=True)\n",
    "trace = plotly.graph_objs.Scatter(\n",
    "    y = read_times,\n",
    "    x = counts,\n",
    "    mode = 'lines+markers'\n",
    ")\n",
    "layout = plotly.graph_objs.Layout(title=\"MySQL Read Times\",\n",
    "                yaxis=dict(title='Time in Seconds'),\n",
    "                xaxis=dict(title='Embedding Count'))\n",
    "data = [trace]\n",
    "fig = plotly.graph_objs.Figure(data=data, layout=layout)\n",
    "plotly.offline.iplot(fig, filename='jupyter-scatter-read')"
   ]
  }
 ],
 "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}