testing_impala_vs_mysql.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from impala.dbapi import connect\n",
    "from impala.util import as_pandas\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = connect(host='133.186.168.6', port=21050)\n",
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"SELECT * FROM MWS_COLT_ITEM_IVT LIMIT 1\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp = as_pandas(cursor)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>item_id</th>\n",
       "      <th>stock_id</th>\n",
       "      <th>stock_amount</th>\n",
       "      <th>add_price</th>\n",
       "      <th>collect_day</th>\n",
       "      <th>reg_id</th>\n",
       "      <th>reg_dt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2335883412</td>\n",
       "      <td>5052433</td>\n",
       "      <td>27963814005</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>20171231</td>\n",
       "      <td>SERVER</td>\n",
       "      <td>2018-01-01 00:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           id  item_id     stock_id stock_amount add_price collect_day  \\\n",
       "0  2335883412  5052433  27963814005           20         0    20171231   \n",
       "\n",
       "   reg_id               reg_dt  \n",
       "0  SERVER  2018-01-01 00:00:00  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tmp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['5951500', '5257497', '681669', '5951501', '293124', '2889643',\n",
       "       '5257500', '291817', '5951502', '289313'], dtype=object)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tmp.item_id.unique()[:10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.execute(\"SELECT * FROM MWS_COLT_ITEM_IVT WHERE item_id IN ('627208', '5257497', '293124' )\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "tmpp = as_pandas(cursor)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "from util import *\n",
    "from time import gmtime, strftime\n",
    "from pytz import timezone\n",
    "from datetime import datetime\n",
    "from sqlalchemy import ForeignKey, Table, Column, String, Integer, Float, Boolean, MetaData, select\n",
    "from joblib import Parallel, delayed\n",
    "\n",
    "epopcon_db = Epopcon_db()\n",
    "\n",
    "wspider_engine = epopcon_db.get_engine(production=True)\n",
    "wspider_temp_engine = epopcon_db.get_engine(production=False)\n",
    "\n",
    "ids_df = pd.read_sql_query(\"SELECT ID FROM MWS_COLT_ITEM WHERE RELEASE_DT > '2018-01-01'\", wspider_engine)\n",
    "\n",
    "DENOM = 500\n",
    "item_ids = ids_df.ID.values\n",
    "n_batches = math.ceil( len(item_ids) / float(DENOM))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "item_ids = ids_df.ID.values.astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "batch_ls = [str(tuple(batch)) for batch in np.array_split(item_ids, n_batches)]\n",
    "batch_lst = [(idx, str(row)) for idx, row in enumerate(batch_ls)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = \"SELECT * FROM MWS_COLT_ITEM_IVT WHERE item_id IN ('6741169')\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\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>item_id</th>\n",
       "      <th>stock_id</th>\n",
       "      <th>stock_amount</th>\n",
       "      <th>add_price</th>\n",
       "      <th>collect_day</th>\n",
       "      <th>reg_id</th>\n",
       "      <th>reg_dt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2374232564</td>\n",
       "      <td>6741168</td>\n",
       "      <td>28993135001</td>\n",
       "      <td>99</td>\n",
       "      <td>0</td>\n",
       "      <td>20180105</td>\n",
       "      <td>SERVER</td>\n",
       "      <td>2018-01-05 20:59:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2374232565</td>\n",
       "      <td>6741169</td>\n",
       "      <td>28993122001</td>\n",
       "      <td>999</td>\n",
       "      <td>0</td>\n",
       "      <td>20180105</td>\n",
       "      <td>SERVER</td>\n",
       "      <td>2018-01-05 20:59:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2374232566</td>\n",
       "      <td>6741170</td>\n",
       "      <td>28993131001</td>\n",
       "      <td>999</td>\n",
       "      <td>0</td>\n",
       "      <td>20180105</td>\n",
       "      <td>SERVER</td>\n",
       "      <td>2018-01-05 20:59:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2374232567</td>\n",
       "      <td>6741171</td>\n",
       "      <td>28993141001</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>20180105</td>\n",
       "      <td>SERVER</td>\n",
       "      <td>2018-01-05 20:59:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2374232568</td>\n",
       "      <td>6741174</td>\n",
       "      <td>28993177001</td>\n",
       "      <td>10</td>\n",
       "      <td>0</td>\n",
       "      <td>20180105</td>\n",
       "      <td>SERVER</td>\n",
       "      <td>2018-01-05 20:59:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83325</th>\n",
       "      <td>3163064190</td>\n",
       "      <td>6741631</td>\n",
       "      <td>28993810001</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>20180304</td>\n",
       "      <td>SERVER</td>\n",
       "      <td>2018-03-04 20:54:11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83326</th>\n",
       "      <td>3163082843</td>\n",
       "      <td>6741629</td>\n",
       "      <td>28992818001</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>20180304</td>\n",
       "      <td>SERVER</td>\n",
       "      <td>2018-03-04 20:57:55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83327</th>\n",
       "      <td>3163098327</td>\n",
       "      <td>6741628</td>\n",
       "      <td>28992813001</td>\n",
       "      <td>20</td>\n",
       "      <td>0</td>\n",
       "      <td>20180304</td>\n",
       "      <td>SERVER</td>\n",
       "      <td>2018-03-04 20:59:46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83328</th>\n",
       "      <td>3163108773</td>\n",
       "      <td>6741652</td>\n",
       "      <td>28971853001</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>20180304</td>\n",
       "      <td>SERVER</td>\n",
       "      <td>2018-03-04 21:01:53</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83329</th>\n",
       "      <td>3163123514</td>\n",
       "      <td>6741623</td>\n",
       "      <td>28991956001</td>\n",
       "      <td>30</td>\n",
       "      <td>0</td>\n",
       "      <td>20180304</td>\n",
       "      <td>SERVER</td>\n",
       "      <td>2018-03-04 21:04:59</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>83330 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               id  item_id     stock_id stock_amount add_price collect_day  reg_id               reg_dt\n",
       "0      2374232564  6741168  28993135001           99         0    20180105  SERVER  2018-01-05 20:59:53\n",
       "1      2374232565  6741169  28993122001          999         0    20180105  SERVER  2018-01-05 20:59:53\n",
       "2      2374232566  6741170  28993131001          999         0    20180105  SERVER  2018-01-05 20:59:53\n",
       "3      2374232567  6741171  28993141001           10         0    20180105  SERVER  2018-01-05 20:59:53\n",
       "4      2374232568  6741174  28993177001           10         0    20180105  SERVER  2018-01-05 20:59:53\n",
       "...           ...      ...          ...          ...       ...         ...     ...                  ...\n",
       "83325  3163064190  6741631  28993810001            3         0    20180304  SERVER  2018-03-04 20:54:11\n",
       "83326  3163082843  6741629  28992818001           20         0    20180304  SERVER  2018-03-04 20:57:55\n",
       "83327  3163098327  6741628  28992813001           20         0    20180304  SERVER  2018-03-04 20:59:46\n",
       "83328  3163108773  6741652  28971853001            1         0    20180304  SERVER  2018-03-04 21:01:53\n",
       "83329  3163123514  6741623  28991956001           30         0    20180304  SERVER  2018-03-04 21:04:59\n",
       "\n",
       "[83330 rows x 8 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%time\n",
    "query = \"SELECT * FROM MWS_COLT_ITEM_IVT WHERE item_id IN %s\" % batch_lst[3][1]\n",
    "\n",
    "cursor.execute(query)\n",
    "\n",
    "tmppp = as_pandas(cursor)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "metadata": {},
   "outputs": [],
   "source": [
    "start_time = time.time()\n",
    "query = \"SELECT * FROM MWS_COLT_ITEM_IVT WHERE item_id IN %s\" % batch_lst[8][1]\n",
    "\n",
    "cursor.execute(query)\n",
    "\n",
    "tmppp = as_pandas(cursor)\n",
    "elapsed_time = time.time() - start_time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 134,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "6.746815204620361"
      ]
     },
     "execution_count": 134,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "elapsed_time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.14"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}