query_federation_with_glaredb.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "eabd3c14",
   "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.5)\r\n"
     ]
    }
   ],
   "source": [
    "! pip install glaredb --upgrade"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a960a283",
   "metadata": {},
   "outputs": [],
   "source": [
    "import glaredb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "bf2d44b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "con = glaredb.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "2df2e5d5",
   "metadata": {
    "scrolled": true
   },
   "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>product_id</th>\n",
       "      <th>product_name</th>\n",
       "      <th>category</th>\n",
       "      <th>total_sales</th>\n",
       "      <th>avg_satisfaction</th>\n",
       "      <th>num_surveys</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>105</td>\n",
       "      <td>Portable Laptop</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>18521735.40</td>\n",
       "      <td>3.20</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>112</td>\n",
       "      <td>High-Performance Tablet</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>15218819.52</td>\n",
       "      <td>2.74</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>109</td>\n",
       "      <td>Deluxe Monitor</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>12704637.00</td>\n",
       "      <td>3.25</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>106</td>\n",
       "      <td>Smart Desk</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>10943635.20</td>\n",
       "      <td>3.08</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>110</td>\n",
       "      <td>Noise-Cancelling Headphones</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>5309734.50</td>\n",
       "      <td>3.04</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>111</td>\n",
       "      <td>Compact Printer</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>3724213.50</td>\n",
       "      <td>2.84</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>118</td>\n",
       "      <td>Portable External SSD</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>2988387.92</td>\n",
       "      <td>3.16</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>114</td>\n",
       "      <td>4K Webcam</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>2607993.96</td>\n",
       "      <td>3.12</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>102</td>\n",
       "      <td>Wireless Mouse</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>2491688.50</td>\n",
       "      <td>3.18</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>104</td>\n",
       "      <td>Smart Speaker</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>2456754.30</td>\n",
       "      <td>2.90</td>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>108</td>\n",
       "      <td>Wireless Keyboard</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>2388514.58</td>\n",
       "      <td>2.91</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>107</td>\n",
       "      <td>Eco-Friendly Lamp</td>\n",
       "      <td>Accessories</td>\n",
       "      <td>1993707.66</td>\n",
       "      <td>2.93</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>103</td>\n",
       "      <td>Coffee Maker</td>\n",
       "      <td>Appliances</td>\n",
       "      <td>1979604.00</td>\n",
       "      <td>3.21</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>120</td>\n",
       "      <td>Smart Light Bulb Set</td>\n",
       "      <td>Appliances</td>\n",
       "      <td>1657523.70</td>\n",
       "      <td>2.87</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>113</td>\n",
       "      <td>Fast-Charging Power Bank</td>\n",
       "      <td>Accessories</td>\n",
       "      <td>1475704.80</td>\n",
       "      <td>3.23</td>\n",
       "      <td>48</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>101</td>\n",
       "      <td>Ergonomic Chair</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>1331513.54</td>\n",
       "      <td>3.03</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>116</td>\n",
       "      <td>Adjustable Monitor Stand</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>1209697.50</td>\n",
       "      <td>3.04</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>119</td>\n",
       "      <td>Cable Management Kit</td>\n",
       "      <td>Accessories</td>\n",
       "      <td>744302.16</td>\n",
       "      <td>3.35</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>117</td>\n",
       "      <td>Wireless Charging Pad</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>656421.12</td>\n",
       "      <td>3.24</td>\n",
       "      <td>38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>115</td>\n",
       "      <td>Ergonomic Mouse Pad</td>\n",
       "      <td>Accessories</td>\n",
       "      <td>615132.28</td>\n",
       "      <td>3.18</td>\n",
       "      <td>49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   product_id                 product_name     category  total_sales  \\\n",
       "0         105              Portable Laptop  Electronics  18521735.40   \n",
       "1         112      High-Performance Tablet  Electronics  15218819.52   \n",
       "2         109               Deluxe Monitor  Electronics  12704637.00   \n",
       "3         106                   Smart Desk    Furniture  10943635.20   \n",
       "4         110  Noise-Cancelling Headphones  Electronics   5309734.50   \n",
       "5         111              Compact Printer  Electronics   3724213.50   \n",
       "6         118        Portable External SSD  Electronics   2988387.92   \n",
       "7         114                    4K Webcam  Electronics   2607993.96   \n",
       "8         102               Wireless Mouse  Electronics   2491688.50   \n",
       "9         104                Smart Speaker  Electronics   2456754.30   \n",
       "10        108            Wireless Keyboard  Electronics   2388514.58   \n",
       "11        107            Eco-Friendly Lamp  Accessories   1993707.66   \n",
       "12        103                 Coffee Maker   Appliances   1979604.00   \n",
       "13        120         Smart Light Bulb Set   Appliances   1657523.70   \n",
       "14        113     Fast-Charging Power Bank  Accessories   1475704.80   \n",
       "15        101              Ergonomic Chair    Furniture   1331513.54   \n",
       "16        116     Adjustable Monitor Stand    Furniture   1209697.50   \n",
       "17        119         Cable Management Kit  Accessories    744302.16   \n",
       "18        117        Wireless Charging Pad  Electronics    656421.12   \n",
       "19        115          Ergonomic Mouse Pad  Accessories    615132.28   \n",
       "\n",
       "    avg_satisfaction  num_surveys  \n",
       "0               3.20           45  \n",
       "1               2.74           58  \n",
       "2               3.25           60  \n",
       "3               3.08           60  \n",
       "4               3.04           45  \n",
       "5               2.84           50  \n",
       "6               3.16           56  \n",
       "7               3.12           57  \n",
       "8               3.18           50  \n",
       "9               2.90           39  \n",
       "10              2.91           46  \n",
       "11              2.93           58  \n",
       "12              3.21           66  \n",
       "13              2.87           45  \n",
       "14              3.23           48  \n",
       "15              3.03           29  \n",
       "16              3.04           50  \n",
       "17              3.35           51  \n",
       "18              3.24           38  \n",
       "19              3.18           49  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = con.sql(f\"\"\"\n",
    "\n",
    "SELECT \n",
    "    p.product_id,\n",
    "    p.product_name,\n",
    "    p.category,\n",
    "    SUM(t.total_amount) as total_sales,\n",
    "    AVG(s.satisfaction_score) as avg_satisfaction,\n",
    "    COUNT(DISTINCT s.survey_id) as num_surveys\n",
    "FROM \n",
    "    read_postgres(\n",
    "        'postgresql://postgres:postgres@localhost:5433/postgres', \n",
    "        'public',\n",
    "        'transactions'\n",
    "        ) t\n",
    "INNER JOIN \n",
    "    read_snowflake(\n",
    "        '{snowflake_account}.us-central1.gcp', \n",
    "        '{snowflake_username}',\n",
    "        '{snowflake_password}',\n",
    "        'sandbox',\n",
    "        'compute_wh',\n",
    "        'accountadmin',\n",
    "        'public',\n",
    "        'products'\n",
    "        ) p \n",
    "ON t.product_id = p.product_id\n",
    "LEFT JOIN \n",
    "    read_parquet(\n",
    "            's3://glaredb-sandbox/survey_data.parquet',\n",
    "            access_key_id => '{aws_access_key_id}',\n",
    "            secret_access_key => '{aws_secret_access_key}',\n",
    "            region=>'us-east-2'\n",
    "            ) s ON t.product_id = s.product_id\n",
    "GROUP BY \n",
    "    p.product_id, p.product_name, p.category\n",
    "HAVING num_surveys > 3\n",
    "ORDER BY \n",
    "    total_sales DESC\n",
    "LIMIT 20;\n",
    "\n",
    "\n",
    "\"\"\").to_pandas()\n",
    "\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ab17d2b2",
   "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>product_id</th>\n",
       "      <th>product_name</th>\n",
       "      <th>category</th>\n",
       "      <th>total_sales</th>\n",
       "      <th>avg_satisfaction</th>\n",
       "      <th>num_surveys</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>105</td>\n",
       "      <td>Portable Laptop</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>21608691.30</td>\n",
       "      <td>3.20</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>112</td>\n",
       "      <td>High-Performance Tablet</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>16541186.46</td>\n",
       "      <td>2.74</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>109</td>\n",
       "      <td>Deluxe Monitor</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>13607611.20</td>\n",
       "      <td>3.25</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>106</td>\n",
       "      <td>Smart Desk</td>\n",
       "      <td>Furniture</td>\n",
       "      <td>12311589.60</td>\n",
       "      <td>3.08</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>110</td>\n",
       "      <td>Noise-Cancelling Headphones</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>5966701.65</td>\n",
       "      <td>3.04</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>111</td>\n",
       "      <td>Compact Printer</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>4250673.00</td>\n",
       "      <td>2.84</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>118</td>\n",
       "      <td>Portable External SSD</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>3346188.16</td>\n",
       "      <td>3.16</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>102</td>\n",
       "      <td>Wireless Mouse</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>2935633.00</td>\n",
       "      <td>3.18</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>114</td>\n",
       "      <td>4K Webcam</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>2763014.58</td>\n",
       "      <td>3.12</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>108</td>\n",
       "      <td>Wireless Keyboard</td>\n",
       "      <td>Electronics</td>\n",
       "      <td>2715538.24</td>\n",
       "      <td>2.91</td>\n",
       "      <td>46</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  product_id                 product_name     category  total_sales  \\\n",
       "0        105              Portable Laptop  Electronics  21608691.30   \n",
       "1        112      High-Performance Tablet  Electronics  16541186.46   \n",
       "2        109               Deluxe Monitor  Electronics  13607611.20   \n",
       "3        106                   Smart Desk    Furniture  12311589.60   \n",
       "4        110  Noise-Cancelling Headphones  Electronics   5966701.65   \n",
       "5        111              Compact Printer  Electronics   4250673.00   \n",
       "6        118        Portable External SSD  Electronics   3346188.16   \n",
       "7        102               Wireless Mouse  Electronics   2935633.00   \n",
       "8        114                    4K Webcam  Electronics   2763014.58   \n",
       "9        108            Wireless Keyboard  Electronics   2715538.24   \n",
       "\n",
       "   avg_satisfaction  num_surveys  \n",
       "0              3.20           45  \n",
       "1              2.74           58  \n",
       "2              3.25           60  \n",
       "3              3.08           60  \n",
       "4              3.04           45  \n",
       "5              2.84           50  \n",
       "6              3.16           56  \n",
       "7              3.18           50  \n",
       "8              3.12           57  \n",
       "9              2.91           46  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = con.sql(f\"\"\"\n",
    "WITH combined_transactions AS (\n",
    "    SELECT\n",
    "        product_id,\n",
    "        total_amount\n",
    "    FROM\n",
    "        read_postgres(\n",
    "        'postgresql://postgres:postgres@localhost:5433/postgres', \n",
    "        'public',\n",
    "        'transactions'\n",
    "        ) t\n",
    "    \n",
    "    UNION ALL\n",
    "    \n",
    "    SELECT\n",
    "        product_id,\n",
    "        total_amount\n",
    "    FROM\n",
    "        './store_transactions.csv'\n",
    ")\n",
    "\n",
    "SELECT \n",
    "    p.product_id,\n",
    "    p.product_name,\n",
    "    p.category,\n",
    "    SUM(ct.total_amount) as total_sales,\n",
    "    AVG(s.satisfaction_score) as avg_satisfaction,\n",
    "    COUNT(DISTINCT s.survey_id) as num_surveys\n",
    "FROM combined_transactions ct\n",
    "    \n",
    "INNER JOIN \n",
    "    read_snowflake(\n",
    "        '{snowflake_account}.us-central1.gcp', \n",
    "        '{snowflake_username}',\n",
    "        '{snowflake_password}',\n",
    "        'sandbox',\n",
    "        'compute_wh',\n",
    "        'accountadmin',\n",
    "        'public',\n",
    "        'products'\n",
    "        ) p \n",
    "ON ct.product_id::int = p.product_id\n",
    "LEFT JOIN \n",
    "    read_parquet(\n",
    "            's3://glaredb-sandbox/survey_data.parquet',\n",
    "            access_key_id => '{aws_access_key_id}',\n",
    "            secret_access_key => '{aws_secret_access_key}',\n",
    "            region=>'us-east-2'\n",
    "            ) s ON ct.product_id = s.product_id\n",
    "GROUP BY \n",
    "    p.product_id, p.product_name, p.category\n",
    "HAVING num_surveys > 3\n",
    "ORDER BY \n",
    "    total_sales DESC\n",
    "LIMIT 10;\n",
    "\n",
    "\n",
    "\"\"\").to_pandas()\n",
    "\n",
    "df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "glaredb_dev",
   "language": "python",
   "name": "glaredb_dev"
  },
  "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}