Database Comparison - Blobs versus Edge Store.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This notebook investigates how fast using the edge store to do simple seeding queries is compared to loading in-memory, then operating with NetworkX"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pybel import BELGraph, Manager\n",
    "import sys\n",
    "import pandas as pd\n",
    "from pybel.struct.summary import count_functions\n",
    "from pybel.manager.models import Edge, network_edge\n",
    "import time\n",
    "from sqlalchemy import and_\n",
    "from pybel.constants import VERSION"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3.6.3 (default, Oct  9 2017, 09:47:56) \n",
      "[GCC 4.2.1 Compatible Apple LLVM 9.0.0 (clang-900.0.37)]\n"
     ]
    }
   ],
   "source": [
    "print(sys.version)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Tue Dec 19 14:16:22 2017\n"
     ]
    }
   ],
   "source": [
    "print(time.asctime())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.9.8-dev\n"
     ]
    }
   ],
   "source": [
    "print(VERSION)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Manager connection=mysql+mysqldb://root@localhost/pybel?charset=utf8>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "m = Manager()\n",
    "m"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>version</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>Alzheimer's Disease Knowledge Assembly</td>\n",
       "      <td>4.3.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>11</td>\n",
       "      <td>BEL Framework Large Corpus Document</td>\n",
       "      <td>20150611</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>CDR-SB Associations</td>\n",
       "      <td>1.0.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2</td>\n",
       "      <td>NeuroMMSigDB for Alzheimer Disease</td>\n",
       "      <td>20171205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>Paths from CDR-SB to Hippocampal Volumne</td>\n",
       "      <td>1.0.1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id                                      name   version\n",
       "0  10    Alzheimer's Disease Knowledge Assembly     4.3.7\n",
       "1  11       BEL Framework Large Corpus Document  20150611\n",
       "2   9                       CDR-SB Associations     1.0.5\n",
       "3   2        NeuroMMSigDB for Alzheimer Disease  20171205\n",
       "4   6  Paths from CDR-SB to Hippocampal Volumne     1.0.1"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(\n",
    "    {\n",
    "        'id': n.id, \n",
    "        'name': n.name, \n",
    "        'version': n.version\n",
    "    } \n",
    "    for n in m.list_recent_networks()\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def print_summary(graph):\n",
    "    print(*sorted(count_functions(graph_by_network_id).items()), sep='\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Getting a Single Graph\n",
    "\n",
    "### Using NetworkX"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Protein', 6295)\n",
      "('BiologicalProcess', 195)\n",
      "('Complex', 1798)\n",
      "('Abundance', 593)\n",
      "('Pathology', 146)\n",
      "('RNA', 5821)\n",
      "('Reaction', 11)\n",
      "('Gene', 1002)\n",
      "('Composite', 7)\n",
      "('miRNA', 33)\n",
      "CPU times: user 910 ms, sys: 45.9 ms, total: 956 ms\n",
      "Wall time: 1.11 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "graph_by_network_id = m.get_graph_by_id(11)\n",
    "print_summary(graph_by_network_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_graph_by_network_edges(manager, network_id, **kwargs):\n",
    "    network = manager.get_network_by_id(network_id)\n",
    "    \n",
    "    edges = network.edges\n",
    "    \n",
    "    graph = BELGraph(**kwargs)\n",
    "    \n",
    "    for edge in edges:\n",
    "        edge.insert_into_graph(graph)\n",
    "        \n",
    "    return graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Counter({'Protein': 6295, 'RNA': 5821, 'Complex': 1798, 'Gene': 1002, 'Abundance': 593, 'BiologicalProcess': 195, 'Pathology': 146, 'miRNA': 33, 'Reaction': 11, 'Composite': 7})\n",
      "CPU times: user 2min 10s, sys: 2.61 s, total: 2min 13s\n",
      "Wall time: 2min 38s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "graph_by_edges = get_graph_by_network_edges(m, 11)\n",
    "print_summary(graph_by_edges)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This query works, but needs serious optimization to be generally useful, especially since this kind of query automatically eliminates the need to do in-memory graph join operations."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Getting Multiple Graphs\n",
    "\n",
    "### Using NetworkX"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "network_ids = [10, 2, 9]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Abundance', 427)\n",
      "('Complex', 746)\n",
      "('Protein', 1757)\n",
      "('Reaction', 20)\n",
      "('Pathology', 128)\n",
      "('BiologicalProcess', 418)\n",
      "('Gene', 1023)\n",
      "('RNA', 492)\n",
      "('Composite', 68)\n",
      "('miRNA', 47)\n",
      "CPU times: user 3.03 s, sys: 56.7 ms, total: 3.09 s\n",
      "Wall time: 3.11 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "graph_by_network_ids = m.get_graph_by_ids(network_ids)\n",
    "print_summary(graph_by_network_ids)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_graph_by_networks_edges(manager, network_ids, **kwargs):\n",
    "    edges = manager.session.query(Edge).join(network_edge).filter(network_edge.c.network_id.in_(network_ids))\n",
    "    \n",
    "    graph = BELGraph(**kwargs)\n",
    "    \n",
    "    for edge in edges:\n",
    "        edge.insert_into_graph(graph)\n",
    "        \n",
    "    return graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Gene', 1023)\n",
      "('Abundance', 417)\n",
      "('RNA', 483)\n",
      "('Protein', 1729)\n",
      "('Composite', 68)\n",
      "('Complex', 743)\n",
      "('Reaction', 18)\n",
      "('BiologicalProcess', 412)\n",
      "('Pathology', 128)\n",
      "('miRNA', 47)\n",
      "CPU times: user 59 s, sys: 1.17 s, total: 1min\n",
      "Wall time: 1min 12s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "graph_by_networks_edges = get_graph_by_networks_edges(m, network_ids)\n",
    "print_summary(graph_by_networks_edges)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Getting Edges Matching an Annotation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "graph = "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_graph_by_annotation(manager, keyword, value, **kwargs):\n",
    "    edges = manager.session.query(Edge).\\\n",
    "        join(network_edge).join(edge_annotation).join(AnnotationEntry)\\\n",
    "        filter(_and(network_edge.c.network_id.in_(network_ids),\n",
    "                    edge_annotation.c.annotation_id == annotation_id))\n",
    "    \n",
    "    graph = BELGraph(**kwargs)\n",
    "    \n",
    "    for edge in edges:\n",
    "        edge.insert_into_graph(graph)\n",
    "        \n",
    "    return graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "annotation = m.get_annotation_entry"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}