presto-pyhive.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Connecting to Presto and Hive with PyHive\n",
    "\n",
    "- Github - [dropbox/PyHive](https://github.com/dropbox/PyHive)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "from pyhive import hive, presto\n",
    "from sqlalchemy.engine import create_engine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "with hive.Connection(host='localhost', port=10000, database='default') as connection:\n",
    "    cursor = connection.cursor()\n",
    "    cursor.execute(\"SHOW TABLES\")\n",
    "    \n",
    "    items = cursor.fetchall()\n",
    "    columns = [v[0] for v in cursor.description]\n",
    "    df = pd.DataFrame(items, columns=columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>tab_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>gdelt_csv_2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>gdelt_csv_2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>gdelt_parquet_2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>gdelt_parquet_inserted_2019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>gdelt_parquet_inserted_2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>ne_10_states_provinces_parquet</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>ne_110_countries_parquet</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         tab_name\n",
       "0                  gdelt_csv_2019\n",
       "1                  gdelt_csv_2020\n",
       "2              gdelt_parquet_2020\n",
       "3     gdelt_parquet_inserted_2019\n",
       "4     gdelt_parquet_inserted_2020\n",
       "5  ne_10_states_provinces_parquet\n",
       "6        ne_110_countries_parquet"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10 entries, 0 to 9\n",
      "Data columns (total 9 columns):\n",
      " #   Column                          Non-Null Count  Dtype \n",
      "---  ------                          --------------  ----- \n",
      " 0   gdelt_csv_2020.event_id         10 non-null     int64 \n",
      " 1   gdelt_csv_2020.date             10 non-null     object\n",
      " 2   gdelt_csv_2020.event_date       10 non-null     object\n",
      " 3   gdelt_csv_2020.event_code       10 non-null     int64 \n",
      " 4   gdelt_csv_2020.event_base_code  10 non-null     int64 \n",
      " 5   gdelt_csv_2020.event_root_code  10 non-null     int64 \n",
      " 6   gdelt_csv_2020.lat              10 non-null     object\n",
      " 7   gdelt_csv_2020.lon              10 non-null     object\n",
      " 8   gdelt_csv_2020.source_url       10 non-null     object\n",
      "dtypes: int64(4), object(5)\n",
      "memory usage: 5.3 KB\n"
     ]
    }
   ],
   "source": [
    "with hive.connect(host='localhost', port=10000, database='default') as connection:\n",
    "    cursor = connection.cursor()\n",
    "    cursor.execute(\"SELECT * FROM gdelt_csv_2020 LIMIT 10\")\n",
    "    \n",
    "    items = cursor.fetchall()\n",
    "    columns = [v[0] for v in cursor.description]\n",
    "    df = pd.DataFrame(items, columns=columns)\n",
    "\n",
    "df.info(memory_usage='deep')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Using SQLAlchemy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10 entries, 0 to 9\n",
      "Data columns (total 9 columns):\n",
      " #   Column           Non-Null Count  Dtype \n",
      "---  ------           --------------  ----- \n",
      " 0   event_id         10 non-null     int64 \n",
      " 1   date             10 non-null     object\n",
      " 2   event_date       10 non-null     object\n",
      " 3   event_code       10 non-null     int64 \n",
      " 4   event_base_code  10 non-null     int64 \n",
      " 5   event_root_code  10 non-null     int64 \n",
      " 6   lat              10 non-null     object\n",
      " 7   lon              10 non-null     object\n",
      " 8   source_url       10 non-null     object\n",
      "dtypes: int64(4), object(5)\n",
      "memory usage: 4.7 KB\n"
     ]
    }
   ],
   "source": [
    "engine = create_engine('presto://localhost:8080/hive/default')\n",
    "\n",
    "df = pd.read_sql(\"SELECT * FROM gdelt_csv_2020 LIMIT 10\", engine)\n",
    "df.info(memory_usage='deep')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10 entries, 0 to 9\n",
      "Data columns (total 9 columns):\n",
      " #   Column           Non-Null Count  Dtype  \n",
      "---  ------           --------------  -----  \n",
      " 0   event_id         10 non-null     int64  \n",
      " 1   date             10 non-null     object \n",
      " 2   event_date       10 non-null     object \n",
      " 3   event_code       10 non-null     int64  \n",
      " 4   event_base_code  10 non-null     int64  \n",
      " 5   event_root_code  10 non-null     int64  \n",
      " 6   lat              10 non-null     float64\n",
      " 7   lon              10 non-null     float64\n",
      " 8   source_url       10 non-null     object \n",
      "dtypes: float64(2), int64(4), object(3)\n",
      "memory usage: 3.3 KB\n"
     ]
    }
   ],
   "source": [
    "engine = create_engine('hive://localhost:10000/default')\n",
    "\n",
    "df = pd.read_sql(\"SELECT * FROM gdelt_csv_2020 LIMIT 10\", engine)\n",
    "df.info(memory_usage='deep')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "sgds",
   "language": "python",
   "name": "sgds"
  },
  "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.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}