Lab_ex_3_1.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Ex. 3.1 Hive\n",
    "We use the *pyHive* lib: https://github.com/dropbox/PyHive \n",
    "\n",
    "## Resources\n",
    "* [HQL cheat sheet](http://hortonworks.com/wp-content/uploads/2016/05/Hortonworks.CheatSheet.SQLtoHive.pdf)\n",
    "* [Hive reference](https://cwiki.apache.org/confluence/display/Hive/LanguageManual)\n",
    "\n",
    "## Install\n",
    "Install *pyHive* via *anaconda*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3.7.6\n"
     ]
    }
   ],
   "source": [
    "from platform import python_version\n",
    "\n",
    "print(python_version())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting package metadata (repodata.json): done\n",
      "Solving environment: done\n",
      "\n",
      "## Package Plan ##\n",
      "\n",
      "  environment location: /home/keuper/anaconda3\n",
      "\n",
      "  added / updated specs:\n",
      "    - pyhive\n",
      "\n",
      "\n",
      "The following packages will be SUPERSEDED by a higher-priority channel:\n",
      "\n",
      "  ca-certificates    conda-forge::ca-certificates-2020.4.5~ --> pkgs/main::ca-certificates-2020.1.1-0\n",
      "  certifi            conda-forge::certifi-2020.4.5.2-py37h~ --> pkgs/main::certifi-2020.4.5.2-py37_0\n",
      "  conda              conda-forge::conda-4.8.3-py37hc8dfbb8~ --> pkgs/main::conda-4.8.3-py37_0\n",
      "  openssl            conda-forge::openssl-1.1.1g-h516909a_0 --> pkgs/main::openssl-1.1.1g-h7b6447c_0\n",
      "\n",
      "\n",
      "Preparing transaction: done\n",
      "Verifying transaction: done\n",
      "Executing transaction: done\n"
     ]
    }
   ],
   "source": [
    "! conda install -y pyhive"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "#import libs\n",
    "from pyhive import hive\n",
    "from TCLIService.ttypes import TOperationState"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# try to connect\n",
    "server=\"ec2-63-34-10-95.eu-west-1.compute.amazonaws.com\"\n",
    "cursor = hive.connect(server).cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "#show our databases\n",
    "cursor.execute('show databases')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('default',)]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#get data from execution\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "#use default db\n",
    "cursor.execute('use default')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "#show tables in db\n",
    "cursor.execute('show tables')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('employee',), ('salary',)]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('employee_id', 'int', ''),\n",
       " ('birthday', 'date', ''),\n",
       " ('first_name', 'string', ''),\n",
       " ('family_name', 'string', ''),\n",
       " ('gender', 'char(1)', ''),\n",
       " ('work_day', 'date', '')]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get table layout\n",
    "cursor.execute('describe employee')\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#select data\n",
    "cursor.execute('select * from employee')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#get daata from selction\n",
    "employee = cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(10001, None, \"'Georgi'\", \"'Facello'\", \"'\", None),\n",
       " (10002, None, \"'Bezalel'\", \"'Simmel'\", \"'\", None),\n",
       " (10003, None, \"'Parto'\", \"'Bamford'\", \"'\", None),\n",
       " (10004, None, \"'Chirstian'\", \"'Koblick'\", \"'\", None),\n",
       " (10005, None, \"'Kyoichi'\", \"'Maliniak'\", \"'\", None),\n",
       " (10006, None, \"'Anneke'\", \"'Preusig'\", \"'\", None),\n",
       " (10007, None, \"'Tzvetan'\", \"'Zielinski'\", \"'\", None),\n",
       " (10008, None, \"'Saniya'\", \"'Kalloufi'\", \"'\", None),\n",
       " (10009, None, \"'Sumant'\", \"'Peac'\", \"'\", None),\n",
       " (10010, None, \"'Duangkaew'\", \"'Piveteau'\", \"'\", None)]"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#have a look\n",
    "employee[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('employee_id', 'int', ''),\n",
       " ('salary', 'int', ''),\n",
       " ('start_date', 'date', ''),\n",
       " ('end_date', 'date', '')]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get salary table layout\n",
    "cursor.execute('describe salary')\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#select data\n",
    "cursor.execute('select * from salary')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#get daata from selction\n",
    "salary = cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "salary[:10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ex 1\n",
    "Get employees sorted by ``family_name``. Return first 10 entries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\" \")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ex 2\n",
    "Get ``family_name`` and ``salary`` of employees sorted by salary. Return first 100 entries. Hint: you need to join both tables..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\" \")\n",
    "data=cursor.fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Ex 3\n",
    "Get the average salary by gender. Hint: use Group by"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\" \")\n",
    "cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}