{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import re\n",
    "from hashlib import sha256\n",
    "from itertools import combinations\n",
    "%run ./utils.ipynb\n",
    "get_rds_engine()\n",
    "engine = get_rds_engine()\n",
    "\n",
    "# setting pandas visual settings \n",
    "pd.set_option('display.max_columns', 20)\n",
    "pd.set_option('max_colwidth', 1000)\n",
    "pd.set_option(\"max_rows\", 100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = 'SELECT schema_name FROM information_schema.schemata;'\n",
    "base_df = pd.read_sql(sql, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "base_df['len'] = base_df['schema_name'].str.len()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "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>schema_name</th>\n",
       "      <th>len</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>pg_catalog</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>a441ffb172866cb4928c84a73de403ca15da4a54cc535e704413ab621</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>information_schema</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>public</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>alex</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 schema_name  len\n",
       "0                                                 pg_catalog   10\n",
       "1  a441ffb172866cb4928c84a73de403ca15da4a54cc535e704413ab621   57\n",
       "2                                         information_schema   18\n",
       "3                                                     public    6\n",
       "4                                                       alex    4"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "base_df.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "schemas_list = base_df[base_df['len'] > 20].schema_name.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = []\n",
    "for schema in schemas_list:\n",
    "    sql = f\"SELECT substring(max(source), '.*\\/.*\\/') source from {schema}.collection;\"\n",
    "    try:\n",
    "        df = pd.read_sql(sql, engine)\n",
    "        df['schema'] = schema\n",
    "        data.append(df)\n",
    "    except:\n",
    "        pass\n",
    "#     df = pd.read_sql(sql, engine)\n",
    "    \n",
    "\n",
    "result =  pd.concat(data, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "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>source</th>\n",
       "      <th>schema</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>incoming-fan-data/Unified Music Group - Ocean Alley/prepared_data/</td>\n",
       "      <td>a441ffb172866cb4928c84a73de403ca15da4a54cc535e704413ab621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>incoming-fan-data/Unified Music Group - In Hearts Wake/prepared_data/</td>\n",
       "      <td>bd345f915775993a4d3de1dae65b93b067ad69dde4286a1e1639e5cd2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>devel---useruploadoriginals/be6cd7606fa5713d61489fd4e3eb9d48bf802c084d5a7461196f1ec4dce76d38/</td>\n",
       "      <td>c7b4555c468ffee2efc9483182179802806c02543c14b6381ea9e39a0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>devel---useruploadoriginals/567629985fcc76c2c1171074b059024fe3257d74a30e6467bacc3df6e436fe4c/</td>\n",
       "      <td>cbf3efb2b31de8152853ca27e58f0048f7c63c9cf97a2511e0f9c6b0e</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>frontend-api-devel-filestore/ce68cc4221118e031936848539c2d93e8fd5c8a068a319bf1741c0c7b/uploads/</td>\n",
       "      <td>ce68cc4221118e031936848539c2d93e8fd5c8a068a319bf1741c0c7b</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>None</td>\n",
       "      <td>c947f75b43c862f14cebf9cbcfd060aa2253fd0383f55e7e793574a65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>None</td>\n",
       "      <td>ce6c741c1c5e1ab77f6cc20f8a37bbdbf47b3b3594c98c93787b6dfe4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>incoming-fan-data/PIXIES/prepared_data_latest/</td>\n",
       "      <td>ace3c2bb4abd9fcaf9807975e6ab940131386dc9ecddb2b7e31288ff8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>None</td>\n",
       "      <td>c7b538defdde08de612a48a0b9a7a1725ace9bfa49d74a7909d81e856</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>None</td>\n",
       "      <td>c0ffc723792260e9107bf0f45f49d599ac98c94ae86da29ae6ec67b77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>frontend-api-devel-filestore/b7d89fb50c4a2f34b76fae90f10aeaee56088099ba030d52671b8bc7/fcf00c02-76f0-4ae2-a65a-b52a980b4c76/uploads/293868371/</td>\n",
       "      <td>cb7d89fb50c4a2f34b76fae90f10aeaee56088099ba030d52671b8bc7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>incoming-fan-data/Janis Joplin/prepared_data/</td>\n",
       "      <td>af65a0ef98e7420800afdea2beb81059bde2bc59d797cc0851412ac41</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>frontend-api-devel-filestore/ca7a460d855e229dd0384001e1c9f5d6fb59381f967ba1b7ec36a7591/uploads/</td>\n",
       "      <td>ca7a460d855e229dd0384001e1c9f5d6fb59381f967ba1b7ec36a7591</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>frontend-api-demo-filestore/75129c309d757357eb4ea164b23628b943c5e418e4f67e125cbc79e5/13055e97-4107-4c3a-981b-77e7e88c6d73/uploads/587154278/</td>\n",
       "      <td>c75129c309d757357eb4ea164b23628b943c5e418e4f67e125cbc79e5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>incoming-fan-data/Unified Music Group - In Hearts Wake/prepared_data/</td>\n",
       "      <td>bd345f915775993a4d3de1dae65b93b067ad69dde4286a1e1639e5cdb</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>frontend-api-devel-filestore/c2c39528d40007daacf7b0633f0eaf8307c64b6f0c2021bad30a7a21a/uploads/</td>\n",
       "      <td>c2c39528d40007daacf7b0633f0eaf8307c64b6f0c2021bad30a7a21a</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                                                                           source  \\\n",
       "0                                                                              incoming-fan-data/Unified Music Group - Ocean Alley/prepared_data/   \n",
       "1                                                                           incoming-fan-data/Unified Music Group - In Hearts Wake/prepared_data/   \n",
       "2                                                   devel---useruploadoriginals/be6cd7606fa5713d61489fd4e3eb9d48bf802c084d5a7461196f1ec4dce76d38/   \n",
       "3                                                   devel---useruploadoriginals/567629985fcc76c2c1171074b059024fe3257d74a30e6467bacc3df6e436fe4c/   \n",
       "4                                                 frontend-api-devel-filestore/ce68cc4221118e031936848539c2d93e8fd5c8a068a319bf1741c0c7b/uploads/   \n",
       "5                                                                                                                                            None   \n",
       "6                                                                                                                                            None   \n",
       "7                                                                                                  incoming-fan-data/PIXIES/prepared_data_latest/   \n",
       "8                                                                                                                                            None   \n",
       "9                                                                                                                                            None   \n",
       "10  frontend-api-devel-filestore/b7d89fb50c4a2f34b76fae90f10aeaee56088099ba030d52671b8bc7/fcf00c02-76f0-4ae2-a65a-b52a980b4c76/uploads/293868371/   \n",
       "11                                                                                                  incoming-fan-data/Janis Joplin/prepared_data/   \n",
       "12                                                frontend-api-devel-filestore/ca7a460d855e229dd0384001e1c9f5d6fb59381f967ba1b7ec36a7591/uploads/   \n",
       "13   frontend-api-demo-filestore/75129c309d757357eb4ea164b23628b943c5e418e4f67e125cbc79e5/13055e97-4107-4c3a-981b-77e7e88c6d73/uploads/587154278/   \n",
       "14                                                                          incoming-fan-data/Unified Music Group - In Hearts Wake/prepared_data/   \n",
       "15                                                frontend-api-devel-filestore/c2c39528d40007daacf7b0633f0eaf8307c64b6f0c2021bad30a7a21a/uploads/   \n",
       "\n",
       "                                                       schema  \n",
       "0   a441ffb172866cb4928c84a73de403ca15da4a54cc535e704413ab621  \n",
       "1   bd345f915775993a4d3de1dae65b93b067ad69dde4286a1e1639e5cd2  \n",
       "2   c7b4555c468ffee2efc9483182179802806c02543c14b6381ea9e39a0  \n",
       "3   cbf3efb2b31de8152853ca27e58f0048f7c63c9cf97a2511e0f9c6b0e  \n",
       "4   ce68cc4221118e031936848539c2d93e8fd5c8a068a319bf1741c0c7b  \n",
       "5   c947f75b43c862f14cebf9cbcfd060aa2253fd0383f55e7e793574a65  \n",
       "6   ce6c741c1c5e1ab77f6cc20f8a37bbdbf47b3b3594c98c93787b6dfe4  \n",
       "7   ace3c2bb4abd9fcaf9807975e6ab940131386dc9ecddb2b7e31288ff8  \n",
       "8   c7b538defdde08de612a48a0b9a7a1725ace9bfa49d74a7909d81e856  \n",
       "9   c0ffc723792260e9107bf0f45f49d599ac98c94ae86da29ae6ec67b77  \n",
       "10  cb7d89fb50c4a2f34b76fae90f10aeaee56088099ba030d52671b8bc7  \n",
       "11  af65a0ef98e7420800afdea2beb81059bde2bc59d797cc0851412ac41  \n",
       "12  ca7a460d855e229dd0384001e1c9f5d6fb59381f967ba1b7ec36a7591  \n",
       "13  c75129c309d757357eb4ea164b23628b943c5e418e4f67e125cbc79e5  \n",
       "14  bd345f915775993a4d3de1dae65b93b067ad69dde4286a1e1639e5cdb  \n",
       "15  c2c39528d40007daacf7b0633f0eaf8307c64b6f0c2021bad30a7a21a  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "conda_python3",
   "language": "python",
   "name": "conda_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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}