{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# OmniSci SQL editor\n",
    "\n",
    "Ibis is a great tool for abtracting SQL queries, and for more programmatic query generation. However, there are times when it is still useful to quickly test raw SQL. Fortunately, we can use an ibis connection and some display magics to accomplish this.\n",
    "\n",
    "First, we make the relevant imports:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import ibis\n",
    "import omnisci_renderer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we make a connection to the database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "omnisci_cli = ibis.mapd.connect(\n",
    "    host='metis.mapd.com', user='mapd', password='HyperInteractive',\n",
    "    port=443, database='mapd', protocol= 'https'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And verify that the connection worked"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['flights_donotmodify',\n",
       " 'contributions_donotmodify',\n",
       " 'tweets_nov_feb',\n",
       " 'zipcodes_orig',\n",
       " 'zipcodes',\n",
       " 'demo_vote_clean']"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "omnisci_cli.list_tables()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's create a SQL editor from this client:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.omnisci.sqleditor+json": {
       "connection": {
        "dbname": "mapd",
        "host": "metis.mapd.com",
        "password": "HyperInteractive",
        "port": 443,
        "protocol": "https",
        "user": "mapd"
       },
       "query": ""
      },
      "text/plain": [
       "<omnisci_renderer.OmniSciSQLEditorRenderer at 0x7fe0cdfd1940>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "omnisci_renderer.OmniSciSQLEditorRenderer(omnisci_cli)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "That gave us a blank canvas for getting data from the database. However, it can be nice to start from an ibis expreesion and then vary it from there. To do that, we can give an expression as the second argument for the `OmniSciSQLEditorRenderer`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.omnisci.sqleditor+json": {
       "connection": {
        "dbname": "mapd",
        "host": "metis.mapd.com",
        "password": "HyperInteractive",
        "port": 443,
        "protocol": "https",
        "user": "mapd"
       },
       "query": "SELECT \"goog_x\" AS x, \"goog_y\" AS y, \"tweet_id\" AS rowid\nFROM tweets_nov_feb"
      },
      "text/plain": [
       "<omnisci_renderer.OmniSciSQLEditorRenderer at 0x7fe0f838a9b0>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "table = omnisci_cli.table('tweets_nov_feb')\n",
    "expr = table[table.goog_x.name('x'), table.goog_y.name('y'), table.tweet_id.name('rowid')]\n",
    "\n",
    "omnisci_renderer.OmniSciSQLEditorRenderer(omnisci_cli, expr)"
   ]
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}