write_queries_to_join_Snowflake_with_Postgres_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "07aeaf82",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: glaredb in /opt/homebrew/anaconda3/envs/glaredb_test/lib/python3.11/site-packages (0.9.2)\r\n"
     ]
    }
   ],
   "source": [
    "! pip install glaredb --upgrade"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b35450d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import glaredb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "41406bcb",
   "metadata": {},
   "outputs": [],
   "source": [
    "con = glaredb.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "08379f57",
   "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>borough_name</th>\n",
       "      <th>borough_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>BRONX</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>BROOKLYN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>QUEENS</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>STATEN ISLAND</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    borough_name  borough_id\n",
       "0      MANHATTAN           1\n",
       "1          BRONX           2\n",
       "2       BROOKLYN           3\n",
       "3         QUEENS           4\n",
       "4  STATEN ISLAND           5"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\n",
    "    f\"\"\"\n",
    "    SELECT * FROM\n",
    "        read_postgres(\n",
    "        'host=pg.demo.glaredb.com port=5432 user={postgres_user} password={postgres_password} dbname=postgres',  \n",
    "        'public',\n",
    "        'borough_lookup'\n",
    "    ) \n",
    "    \"\"\"\n",
    ").to_pandas()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d1050884",
   "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>borough_name</th>\n",
       "      <th>apartment_number</th>\n",
       "      <th>land_square_feet</th>\n",
       "      <th>borough</th>\n",
       "      <th>neighborhood</th>\n",
       "      <th>sale_price</th>\n",
       "      <th>nta_code</th>\n",
       "      <th>zip_code</th>\n",
       "      <th>bbl</th>\n",
       "      <th>sale_date</th>\n",
       "      <th>...</th>\n",
       "      <th>year_built</th>\n",
       "      <th>council_district</th>\n",
       "      <th>lot</th>\n",
       "      <th>sale_day</th>\n",
       "      <th>longitude</th>\n",
       "      <th>nta</th>\n",
       "      <th>community_board</th>\n",
       "      <th>building_class_as_of_final_roll</th>\n",
       "      <th>total_units</th>\n",
       "      <th>tax_class_as_of_final_roll</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>None</td>\n",
       "      <td>4,651</td>\n",
       "      <td>1</td>\n",
       "      <td>ALPHABET CITY</td>\n",
       "      <td>14000000</td>\n",
       "      <td>None</td>\n",
       "      <td>10009.0</td>\n",
       "      <td>1.003730e+09</td>\n",
       "      <td>1546992000000000000</td>\n",
       "      <td>...</td>\n",
       "      <td>1920.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>19</td>\n",
       "      <td>9</td>\n",
       "      <td>-73.978971</td>\n",
       "      <td>Lower East Side</td>\n",
       "      <td>103.0</td>\n",
       "      <td>C7</td>\n",
       "      <td>30.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>None</td>\n",
       "      <td>2,169</td>\n",
       "      <td>1</td>\n",
       "      <td>ALPHABET CITY</td>\n",
       "      <td>872500</td>\n",
       "      <td>None</td>\n",
       "      <td>10009.0</td>\n",
       "      <td>1.003770e+09</td>\n",
       "      <td>1548374400000000000</td>\n",
       "      <td>...</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>50</td>\n",
       "      <td>25</td>\n",
       "      <td>-73.977419</td>\n",
       "      <td>Lower East Side</td>\n",
       "      <td>103.0</td>\n",
       "      <td>C2</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2A</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>None</td>\n",
       "      <td>2,165</td>\n",
       "      <td>1</td>\n",
       "      <td>ALPHABET CITY</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>10009.0</td>\n",
       "      <td>1.003840e+09</td>\n",
       "      <td>1547596800000000000</td>\n",
       "      <td>...</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>16</td>\n",
       "      <td>16</td>\n",
       "      <td>-73.982525</td>\n",
       "      <td>Lower East Side</td>\n",
       "      <td>103.0</td>\n",
       "      <td>C4</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>None</td>\n",
       "      <td>1,916</td>\n",
       "      <td>1</td>\n",
       "      <td>ALPHABET CITY</td>\n",
       "      <td>19475</td>\n",
       "      <td>None</td>\n",
       "      <td>10009.0</td>\n",
       "      <td>1.003930e+09</td>\n",
       "      <td>1548892800000000000</td>\n",
       "      <td>...</td>\n",
       "      <td>1920.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>38</td>\n",
       "      <td>31</td>\n",
       "      <td>-73.977433</td>\n",
       "      <td>Lower East Side</td>\n",
       "      <td>103.0</td>\n",
       "      <td>C7</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2B</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>None</td>\n",
       "      <td>2,468</td>\n",
       "      <td>1</td>\n",
       "      <td>ALPHABET CITY</td>\n",
       "      <td>102083</td>\n",
       "      <td>None</td>\n",
       "      <td>10009.0</td>\n",
       "      <td>1.004060e+09</td>\n",
       "      <td>1548892800000000000</td>\n",
       "      <td>...</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>27</td>\n",
       "      <td>31</td>\n",
       "      <td>-73.979553</td>\n",
       "      <td>East Village</td>\n",
       "      <td>103.0</td>\n",
       "      <td>C4</td>\n",
       "      <td>14.0</td>\n",
       "      <td>2</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",
       "      <td>...</td>\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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>4N</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>EAST VILLAGE</td>\n",
       "      <td>0</td>\n",
       "      <td>None</td>\n",
       "      <td>10003.0</td>\n",
       "      <td>1.004438e+09</td>\n",
       "      <td>1547164800000000000</td>\n",
       "      <td>...</td>\n",
       "      <td>2007.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1107</td>\n",
       "      <td>11</td>\n",
       "      <td>-73.989061</td>\n",
       "      <td>East Village</td>\n",
       "      <td>103.0</td>\n",
       "      <td>R4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>10B</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>EAST VILLAGE</td>\n",
       "      <td>1800000</td>\n",
       "      <td>None</td>\n",
       "      <td>10003.0</td>\n",
       "      <td>1.004518e+09</td>\n",
       "      <td>1548201600000000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1125</td>\n",
       "      <td>23</td>\n",
       "      <td>-73.984922</td>\n",
       "      <td>East Village</td>\n",
       "      <td>103.0</td>\n",
       "      <td>R4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>EAST VILLAGE</td>\n",
       "      <td>1300000</td>\n",
       "      <td>None</td>\n",
       "      <td>10003.0</td>\n",
       "      <td>1.004468e+09</td>\n",
       "      <td>1546905600000000000</td>\n",
       "      <td>...</td>\n",
       "      <td>1928.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1102</td>\n",
       "      <td>8</td>\n",
       "      <td>-73.988480</td>\n",
       "      <td>East Village</td>\n",
       "      <td>103.0</td>\n",
       "      <td>R9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>None</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>EAST VILLAGE</td>\n",
       "      <td>668000</td>\n",
       "      <td>None</td>\n",
       "      <td>10003.0</td>\n",
       "      <td>1.004698e+09</td>\n",
       "      <td>1548374400000000000</td>\n",
       "      <td>...</td>\n",
       "      <td>1900.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1102</td>\n",
       "      <td>25</td>\n",
       "      <td>-73.987508</td>\n",
       "      <td>East Village</td>\n",
       "      <td>103.0</td>\n",
       "      <td>R9</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>MANHATTAN</td>\n",
       "      <td>PKG4</td>\n",
       "      <td>29,705</td>\n",
       "      <td>1</td>\n",
       "      <td>EAST VILLAGE</td>\n",
       "      <td>253500</td>\n",
       "      <td>None</td>\n",
       "      <td>10009.0</td>\n",
       "      <td>1.004398e+09</td>\n",
       "      <td>1548892800000000000</td>\n",
       "      <td>...</td>\n",
       "      <td>2015.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1005</td>\n",
       "      <td>31</td>\n",
       "      <td>-73.982328</td>\n",
       "      <td>East Village</td>\n",
       "      <td>103.0</td>\n",
       "      <td>RG</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   borough_name apartment_number land_square_feet borough   neighborhood  \\\n",
       "0     MANHATTAN             None            4,651       1  ALPHABET CITY   \n",
       "1     MANHATTAN             None            2,169       1  ALPHABET CITY   \n",
       "2     MANHATTAN             None            2,165       1  ALPHABET CITY   \n",
       "3     MANHATTAN             None            1,916       1  ALPHABET CITY   \n",
       "4     MANHATTAN             None            2,468       1  ALPHABET CITY   \n",
       "..          ...              ...              ...     ...            ...   \n",
       "95    MANHATTAN               4N                0       1   EAST VILLAGE   \n",
       "96    MANHATTAN              10B                0       1   EAST VILLAGE   \n",
       "97    MANHATTAN             None                0       1   EAST VILLAGE   \n",
       "98    MANHATTAN             None                0       1   EAST VILLAGE   \n",
       "99    MANHATTAN             PKG4           29,705       1   EAST VILLAGE   \n",
       "\n",
       "   sale_price nta_code  zip_code           bbl            sale_date  ...  \\\n",
       "0    14000000     None   10009.0  1.003730e+09  1546992000000000000  ...   \n",
       "1      872500     None   10009.0  1.003770e+09  1548374400000000000  ...   \n",
       "2           0     None   10009.0  1.003840e+09  1547596800000000000  ...   \n",
       "3       19475     None   10009.0  1.003930e+09  1548892800000000000  ...   \n",
       "4      102083     None   10009.0  1.004060e+09  1548892800000000000  ...   \n",
       "..        ...      ...       ...           ...                  ...  ...   \n",
       "95          0     None   10003.0  1.004438e+09  1547164800000000000  ...   \n",
       "96    1800000     None   10003.0  1.004518e+09  1548201600000000000  ...   \n",
       "97    1300000     None   10003.0  1.004468e+09  1546905600000000000  ...   \n",
       "98     668000     None   10003.0  1.004698e+09  1548374400000000000  ...   \n",
       "99     253500     None   10009.0  1.004398e+09  1548892800000000000  ...   \n",
       "\n",
       "    year_built  council_district   lot  sale_day  longitude              nta  \\\n",
       "0       1920.0               2.0    19         9 -73.978971  Lower East Side   \n",
       "1       1900.0               2.0    50        25 -73.977419  Lower East Side   \n",
       "2       1900.0               2.0    16        16 -73.982525  Lower East Side   \n",
       "3       1920.0               2.0    38        31 -73.977433  Lower East Side   \n",
       "4       1900.0               2.0    27        31 -73.979553     East Village   \n",
       "..         ...               ...   ...       ...        ...              ...   \n",
       "95      2007.0               2.0  1107        11 -73.989061     East Village   \n",
       "96         0.0               2.0  1125        23 -73.984922     East Village   \n",
       "97      1928.0               2.0  1102         8 -73.988480     East Village   \n",
       "98      1900.0               2.0  1102        25 -73.987508     East Village   \n",
       "99      2015.0               2.0  1005        31 -73.982328     East Village   \n",
       "\n",
       "   community_board  building_class_as_of_final_roll  total_units  \\\n",
       "0            103.0                               C7         30.0   \n",
       "1            103.0                               C2          5.0   \n",
       "2            103.0                               C4          7.0   \n",
       "3            103.0                               C7         10.0   \n",
       "4            103.0                               C4         14.0   \n",
       "..             ...                              ...          ...   \n",
       "95           103.0                               R4          1.0   \n",
       "96           103.0                               R4          1.0   \n",
       "97           103.0                               R9          0.0   \n",
       "98           103.0                               R9          0.0   \n",
       "99           103.0                               RG          1.0   \n",
       "\n",
       "   tax_class_as_of_final_roll  \n",
       "0                           2  \n",
       "1                          2A  \n",
       "2                          2B  \n",
       "3                          2B  \n",
       "4                           2  \n",
       "..                        ...  \n",
       "95                          2  \n",
       "96                          2  \n",
       "97                          2  \n",
       "98                          2  \n",
       "99                          4  \n",
       "\n",
       "[100 rows x 32 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "con.sql(\n",
    "    f\"\"\"\n",
    "SELECT postgres.borough_name, snowflake.* FROM \n",
    "    read_snowflake(\n",
    "    '{snowflake_account}.us-central1.gcp', \n",
    "    '{snowflake_user}',\n",
    "    '{snowflake_password}',\n",
    "    'sandbox',\n",
    "    'compute_wh',\n",
    "    'accountadmin',\n",
    "    'public',\n",
    "    'nyc_sales'\n",
    "    ) snowflake \n",
    "LEFT JOIN \n",
    "    read_postgres(\n",
    "        'host=pg.demo.glaredb.com port=5432 user={postgres_user} password={postgres_password} dbname=postgres',  \n",
    "        'public',\n",
    "        'borough_lookup'\n",
    "    ) postgres ON\n",
    "    snowflake.borough = postgres.borough_id\n",
    "    limit 100  \n",
    "    \"\"\").to_pandas()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "glaredb_test",
   "language": "python",
   "name": "glaredb_test"
  },
  "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.11.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}