Example Tools to collect Histogram and Samples.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "bf3fc825",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json\n",
    "import re\n",
    "import math\n",
    "import numpy as np\n",
    "from collections import defaultdict\n",
    "import psycopg2\n",
    "import time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "7b2c6c54",
   "metadata": {},
   "outputs": [],
   "source": [
    "imdb_schema = {'title': ['t.id', 't.kind_id', 't.production_year'],\n",
    " 'movie_companies': ['mc.id',\n",
    "  'mc.company_id',\n",
    "  'mc.movie_id',\n",
    "  'mc.company_type_id'],\n",
    " 'cast_info': ['ci.id', 'ci.movie_id', 'ci.person_id', 'ci.role_id'],\n",
    " 'movie_info_idx': ['mi_idx.id', 'mi_idx.movie_id', 'mi_idx.info_type_id'],\n",
    " 'movie_info': ['mi.id', 'mi.movie_id', 'mi.info_type_id'],\n",
    " 'movie_keyword': ['mk.id', 'mk.movie_id', 'mk.keyword_id']}\n",
    "t2alias = {'title':'t','movie_companies':'mc','cast_info':'ci',\n",
    "          'movie_info_idx':'mi_idx','movie_info':'mi','movie_keyword':'mk'}\n",
    "alias2t = {}\n",
    "for k,v in t2alias.items(): alias2t[v] = k"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "6003bc53",
   "metadata": {},
   "outputs": [],
   "source": [
    "conm = psycopg2.connect(database=\"imdb\", user=\"postgres\", host=\"127.0.0.1\",password=\"admin\", port=\"5432\")\n",
    "conm.set_session(autocommit=True)\n",
    "cur = conm.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c817505f",
   "metadata": {},
   "outputs": [],
   "source": [
    "def to_vals(data_list):\n",
    "    for dat in data_list:\n",
    "        val = dat[0]\n",
    "        if val is not None: break\n",
    "    try:\n",
    "        float(val)\n",
    "        return np.array(data_list, dtype=float).squeeze()\n",
    "    except:\n",
    "#         print(val)\n",
    "        res = []\n",
    "        for dat in data_list:\n",
    "            try:\n",
    "                mi = dat[0].timestamp()\n",
    "            except:\n",
    "                mi = 0\n",
    "            res.append(mi)\n",
    "        return np.array(res)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0203cab",
   "metadata": {},
   "source": [
    "## Histogram"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "3f623386",
   "metadata": {},
   "outputs": [],
   "source": [
    "hist_file = pd.DataFrame(columns=['table','column','bins','table_column'])\n",
    "hist_file\n",
    "for table,columns in imdb_schema.items():\n",
    "    for column in columns:\n",
    "        cmd = 'select {} from {} as {}'.format(column, table,t2alias[table])\n",
    "        cur.execute(cmd)\n",
    "        col = cur.fetchall()\n",
    "        col_array = to_vals(col)\n",
    "        hists = np.nanpercentile(col_array, range(0,101,2), axis=0)\n",
    "        res_dict = {\n",
    "            'table':table,\n",
    "            'column':column,\n",
    "            'table_column': '.'.join((table, column)),\n",
    "            'bins':hists\n",
    "        }\n",
    "        hist_file = hist_file.append(res_dict,ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "338e676c",
   "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>table</th>\n",
       "      <th>column</th>\n",
       "      <th>bins</th>\n",
       "      <th>table_column</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>title</td>\n",
       "      <td>t.id</td>\n",
       "      <td>[1.0, 50567.22, 101133.44, 151699.66, 202265.8...</td>\n",
       "      <td>title.t.id</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>title</td>\n",
       "      <td>t.kind_id</td>\n",
       "      <td>[1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, ...</td>\n",
       "      <td>title.t.kind_id</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>title</td>\n",
       "      <td>t.production_year</td>\n",
       "      <td>[1880.0, 1913.0, 1923.0, 1942.0, 1955.0, 1960....</td>\n",
       "      <td>title.t.production_year</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>movie_companies</td>\n",
       "      <td>mc.id</td>\n",
       "      <td>[1.0, 52183.56, 104366.12, 156548.68, 208731.2...</td>\n",
       "      <td>movie_companies.mc.id</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>movie_companies</td>\n",
       "      <td>mc.company_id</td>\n",
       "      <td>[1.0, 6.0, 19.0, 27.0, 68.0, 133.0, 160.0, 189...</td>\n",
       "      <td>movie_companies.mc.company_id</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>movie_companies</td>\n",
       "      <td>mc.movie_id</td>\n",
       "      <td>[2.0, 104911.79999999999, 194182.0, 295563.0, ...</td>\n",
       "      <td>movie_companies.mc.movie_id</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>movie_companies</td>\n",
       "      <td>mc.company_type_id</td>\n",
       "      <td>[1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, ...</td>\n",
       "      <td>movie_companies.mc.company_type_id</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>cast_info</td>\n",
       "      <td>ci.id</td>\n",
       "      <td>[1.0, 724887.86, 1449774.72, 2174661.58, 28995...</td>\n",
       "      <td>cast_info.ci.id</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>cast_info</td>\n",
       "      <td>ci.movie_id</td>\n",
       "      <td>[1.0, 54874.0, 105584.0, 161492.0, 216566.0, 2...</td>\n",
       "      <td>cast_info.ci.movie_id</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>cast_info</td>\n",
       "      <td>ci.person_id</td>\n",
       "      <td>[1.0, 77446.0, 145798.0, 212749.58000000007, 2...</td>\n",
       "      <td>cast_info.ci.person_id</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>cast_info</td>\n",
       "      <td>ci.role_id</td>\n",
       "      <td>[1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, ...</td>\n",
       "      <td>cast_info.ci.role_id</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>movie_info_idx</td>\n",
       "      <td>mi_idx.id</td>\n",
       "      <td>[1.0, 27601.68, 55202.36, 82803.04, 110403.72,...</td>\n",
       "      <td>movie_info_idx.mi_idx.id</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>movie_info_idx</td>\n",
       "      <td>mi_idx.movie_id</td>\n",
       "      <td>[2.0, 81071.0, 164222.0, 254987.08, 346733.72,...</td>\n",
       "      <td>movie_info_idx.mi_idx.movie_id</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>movie_info_idx</td>\n",
       "      <td>mi_idx.info_type_id</td>\n",
       "      <td>[99.0, 99.0, 99.0, 99.0, 99.0, 99.0, 99.0, 99....</td>\n",
       "      <td>movie_info_idx.mi_idx.info_type_id</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>movie_info</td>\n",
       "      <td>mi.id</td>\n",
       "      <td>[1.0, 296715.38, 593429.76, 890144.14, 1186858...</td>\n",
       "      <td>movie_info.mi.id</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>movie_info</td>\n",
       "      <td>mi.movie_id</td>\n",
       "      <td>[1.0, 112225.0, 216062.76, 315018.0, 430227.52...</td>\n",
       "      <td>movie_info.mi.movie_id</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>movie_info</td>\n",
       "      <td>mi.info_type_id</td>\n",
       "      <td>[1.0, 1.0, 1.0, 2.0, 2.0, 2.0, 2.0, 2.0, 3.0, ...</td>\n",
       "      <td>movie_info.mi.info_type_id</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>movie_keyword</td>\n",
       "      <td>mk.id</td>\n",
       "      <td>[1.0, 90479.58, 180958.16, 271436.74, 361915.3...</td>\n",
       "      <td>movie_keyword.mk.id</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>movie_keyword</td>\n",
       "      <td>mk.movie_id</td>\n",
       "      <td>[2.0, 174196.0, 318022.0, 504832.0, 704239.0, ...</td>\n",
       "      <td>movie_keyword.mk.movie_id</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>movie_keyword</td>\n",
       "      <td>mk.keyword_id</td>\n",
       "      <td>[1.0, 77.0, 132.0, 230.0, 331.0, 347.0, 384.0,...</td>\n",
       "      <td>movie_keyword.mk.keyword_id</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              table               column  \\\n",
       "0             title                 t.id   \n",
       "1             title            t.kind_id   \n",
       "2             title    t.production_year   \n",
       "3   movie_companies                mc.id   \n",
       "4   movie_companies        mc.company_id   \n",
       "5   movie_companies          mc.movie_id   \n",
       "6   movie_companies   mc.company_type_id   \n",
       "7         cast_info                ci.id   \n",
       "8         cast_info          ci.movie_id   \n",
       "9         cast_info         ci.person_id   \n",
       "10        cast_info           ci.role_id   \n",
       "11   movie_info_idx            mi_idx.id   \n",
       "12   movie_info_idx      mi_idx.movie_id   \n",
       "13   movie_info_idx  mi_idx.info_type_id   \n",
       "14       movie_info                mi.id   \n",
       "15       movie_info          mi.movie_id   \n",
       "16       movie_info      mi.info_type_id   \n",
       "17    movie_keyword                mk.id   \n",
       "18    movie_keyword          mk.movie_id   \n",
       "19    movie_keyword        mk.keyword_id   \n",
       "\n",
       "                                                 bins  \\\n",
       "0   [1.0, 50567.22, 101133.44, 151699.66, 202265.8...   \n",
       "1   [1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, ...   \n",
       "2   [1880.0, 1913.0, 1923.0, 1942.0, 1955.0, 1960....   \n",
       "3   [1.0, 52183.56, 104366.12, 156548.68, 208731.2...   \n",
       "4   [1.0, 6.0, 19.0, 27.0, 68.0, 133.0, 160.0, 189...   \n",
       "5   [2.0, 104911.79999999999, 194182.0, 295563.0, ...   \n",
       "6   [1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, ...   \n",
       "7   [1.0, 724887.86, 1449774.72, 2174661.58, 28995...   \n",
       "8   [1.0, 54874.0, 105584.0, 161492.0, 216566.0, 2...   \n",
       "9   [1.0, 77446.0, 145798.0, 212749.58000000007, 2...   \n",
       "10  [1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, ...   \n",
       "11  [1.0, 27601.68, 55202.36, 82803.04, 110403.72,...   \n",
       "12  [2.0, 81071.0, 164222.0, 254987.08, 346733.72,...   \n",
       "13  [99.0, 99.0, 99.0, 99.0, 99.0, 99.0, 99.0, 99....   \n",
       "14  [1.0, 296715.38, 593429.76, 890144.14, 1186858...   \n",
       "15  [1.0, 112225.0, 216062.76, 315018.0, 430227.52...   \n",
       "16  [1.0, 1.0, 1.0, 2.0, 2.0, 2.0, 2.0, 2.0, 3.0, ...   \n",
       "17  [1.0, 90479.58, 180958.16, 271436.74, 361915.3...   \n",
       "18  [2.0, 174196.0, 318022.0, 504832.0, 704239.0, ...   \n",
       "19  [1.0, 77.0, 132.0, 230.0, 331.0, 347.0, 384.0,...   \n",
       "\n",
       "                          table_column  \n",
       "0                           title.t.id  \n",
       "1                      title.t.kind_id  \n",
       "2              title.t.production_year  \n",
       "3                movie_companies.mc.id  \n",
       "4        movie_companies.mc.company_id  \n",
       "5          movie_companies.mc.movie_id  \n",
       "6   movie_companies.mc.company_type_id  \n",
       "7                      cast_info.ci.id  \n",
       "8                cast_info.ci.movie_id  \n",
       "9               cast_info.ci.person_id  \n",
       "10                cast_info.ci.role_id  \n",
       "11            movie_info_idx.mi_idx.id  \n",
       "12      movie_info_idx.mi_idx.movie_id  \n",
       "13  movie_info_idx.mi_idx.info_type_id  \n",
       "14                    movie_info.mi.id  \n",
       "15              movie_info.mi.movie_id  \n",
       "16          movie_info.mi.info_type_id  \n",
       "17                 movie_keyword.mk.id  \n",
       "18           movie_keyword.mk.movie_id  \n",
       "19         movie_keyword.mk.keyword_id  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hist_file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b5e2e58e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ef529f2e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ae8d831",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2105c2b1",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "d4edfae9",
   "metadata": {},
   "source": [
    "## Sample\n",
    "### Steps (There may be other easier methods)\n",
    "1. generate 1000 sample points for each table\n",
    "2. duplicate database schema from full db\n",
    "    > pg_dump imdb -s -O > imdb_schema.sql\n",
    "3. create small base by in psql\n",
    "    > create database imdb_sample\n",
    "4. create schema using imdb_schema.sql\n",
    "5. load the sample data using pandas and sqlalchemy\n",
    "6. query the small base to get sample bitmaps for each predicate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2e1898af",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "a4e3e74c",
   "metadata": {},
   "source": [
    "Step 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "597d182e",
   "metadata": {},
   "outputs": [],
   "source": [
    "## sampling extension\n",
    "cmd = 'CREATE EXTENSION tsm_system_rows'\n",
    "cur.execute(cmd)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "1cf5907c",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = list(imdb_schema.keys())\n",
    "sample_data = {}\n",
    "for table in tables:\n",
    "    cur.execute(\"Select * FROM {} LIMIT 0\".format(table))\n",
    "    colnames = [desc[0] for desc in cur.description]\n",
    "\n",
    "    ts = pd.DataFrame(columns = colnames)\n",
    "\n",
    "    for num in range(1000):\n",
    "        cmd = 'SELECT * FROM {} TABLESAMPLE SYSTEM_ROWS(1)'.format(table)\n",
    "        cur.execute(cmd)\n",
    "        samples = cur.fetchall()\n",
    "        for i,row in enumerate(samples):\n",
    "            ts.loc[num]=row\n",
    "    \n",
    "    sample_data[table] = ts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "527faa17",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "ef688869",
   "metadata": {},
   "source": [
    "Step 5 (Do step 2-4 outside first)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "1a6e412b",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "engine = create_engine('postgresql://postgres:admin@localhost:5432/imdb_sample')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "668a0092",
   "metadata": {},
   "outputs": [],
   "source": [
    "for k,v in sample_data.items():\n",
    "    v['sid'] = list(range(1000))\n",
    "    cmd = 'alter table {} add column sid integer'.format(k)\n",
    "    cur.execute(cmd)\n",
    "    v.to_sql(k,engine,if_exists='append',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "518deeb6",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "2d6ebf84",
   "metadata": {},
   "source": [
    "Step 6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b6b930eb",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "53ae99d2",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "query_file = pd.read_csv('data/imdb/workloads/synthetic.csv',sep='#',header=None)\n",
    "query_file.columns = ['table','join','predicate','card']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "51e850dc",
   "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>table</th>\n",
       "      <th>join</th>\n",
       "      <th>predicate</th>\n",
       "      <th>card</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>cast_info ci</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ci.person_id,=,172968</td>\n",
       "      <td>838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>title t,movie_info mi</td>\n",
       "      <td>t.id=mi.movie_id</td>\n",
       "      <td>t.kind_id,&lt;,3,t.production_year,=,2008,mi.info...</td>\n",
       "      <td>297013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>title t,cast_info ci</td>\n",
       "      <td>t.id=ci.movie_id</td>\n",
       "      <td>ci.person_id,&lt;,3194645</td>\n",
       "      <td>31427248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>title t,cast_info ci,movie_info mi</td>\n",
       "      <td>t.id=ci.movie_id,t.id=mi.movie_id</td>\n",
       "      <td>ci.person_id,=,1742124,ci.role_id,&gt;,2,mi.info_...</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>title t,cast_info ci,movie_info_idx mi_idx</td>\n",
       "      <td>t.id=ci.movie_id,t.id=mi_idx.movie_id</td>\n",
       "      <td>t.kind_id,=,7,t.production_year,&gt;,0,ci.role_id...</td>\n",
       "      <td>733244</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        table  \\\n",
       "0                                cast_info ci   \n",
       "1                       title t,movie_info mi   \n",
       "2                        title t,cast_info ci   \n",
       "3          title t,cast_info ci,movie_info mi   \n",
       "4  title t,cast_info ci,movie_info_idx mi_idx   \n",
       "\n",
       "                                    join  \\\n",
       "0                                    NaN   \n",
       "1                       t.id=mi.movie_id   \n",
       "2                       t.id=ci.movie_id   \n",
       "3      t.id=ci.movie_id,t.id=mi.movie_id   \n",
       "4  t.id=ci.movie_id,t.id=mi_idx.movie_id   \n",
       "\n",
       "                                           predicate      card  \n",
       "0                              ci.person_id,=,172968       838  \n",
       "1  t.kind_id,<,3,t.production_year,=,2008,mi.info...    297013  \n",
       "2                             ci.person_id,<,3194645  31427248  \n",
       "3  ci.person_id,=,1742124,ci.role_id,>,2,mi.info_...        12  \n",
       "4  t.kind_id,=,7,t.production_year,>,0,ci.role_id...    733244  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_file.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "557efe49",
   "metadata": {},
   "outputs": [],
   "source": [
    "conm = psycopg2.connect(database=\"imdb_sample\", user=\"postgres\", host=\"127.0.0.1\",password=\"admin\", port=\"5432\")\n",
    "conm.set_session(autocommit=True)\n",
    "cur = conm.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9d66ae91",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "95ae2a68",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "table_samples = []\n",
    "for i,row in query_file.iterrows():\n",
    "    table_sample = {}\n",
    "    preds = row['predicate'].split(',')\n",
    "    for i in range(0,len(preds),3):\n",
    "        left, op, right = preds[i:i+3]\n",
    "        alias,col = left.split('.')\n",
    "        table = alias2t[alias]\n",
    "        pred_string = ''.join((col,op,right))\n",
    "        q = 'select sid from {} where {}'.format(table, pred_string)\n",
    "        cur.execute(q)\n",
    "        sps = np.zeros(1000).astype('uint8')\n",
    "        sids = cur.fetchall()\n",
    "        sids = np.array(sids).squeeze()\n",
    "        if sids.size>1:\n",
    "            sps[sids] = 1\n",
    "        if table in table_sample:\n",
    "            table_sample[table] = table_sample[table] & sps\n",
    "        else:\n",
    "            table_sample[table] = sps\n",
    "    table_samples.append(table_sample)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "bb76cfbe",
   "metadata": {},
   "outputs": [],
   "source": [
    "# table_samples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "391e6808",
   "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}