04_building_summary_tables.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Querying Data from Google BigQuery to SQLite Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "from google.cloud import bigquery\n",
    "from google.api_core.exceptions import GoogleAPIError\n",
    "import os\n",
    "import polars as pl\n",
    "import pandas as pd\n",
    "import random\n",
    "\n",
    "client = bigquery.Client(project=\"wedge-project-jbangtson\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Function to run a query\n",
    "def run_query(query):\n",
    "    try:\n",
    "        query_job = client.query(query)\n",
    "        df = query_job.to_dataframe()\n",
    "        #results = query_job.result()\n",
    "\n",
    "        bytes_processed = query_job.total_bytes_processed\n",
    "        mb_processed = bytes_processed / (1024 ** 2)\n",
    "        cost_per_tb = 5.0\n",
    "\n",
    "\n",
    "        tb_processed = bytes_processed / (1024 ** 4)  # Convert bytes to terabytes\n",
    "        estimated_cost = tb_processed * cost_per_tb\n",
    "\n",
    "        # Display the processed data and estimated cost\n",
    "        print(f\"Data processed: {mb_processed:.2f} MB\")\n",
    "        \n",
    "\n",
    "        print(f\"Estimated bytes processed: {bytes_processed}\")\n",
    "        print(f\"Estimated cost: ${estimated_cost:.10f}\\n\\n\")\n",
    "\n",
    "        print(f\"Estimated bytes processed against a full year of data: {bytes_processed*50}\")\n",
    "        print(f\"Estimated cost against a full year of data: ${estimated_cost*50:.20f}\")\n",
    "        print(f\"Estimated cost against a full year of data every 6 hours: ${(estimated_cost*50)*(4*365):.20f}\\n\\n---------------\")\n",
    "\n",
    "        \n",
    "\n",
    "        return df.astype(str)\n",
    "    except GoogleAPIError as e:\n",
    "        print(f\"Error running query: {e}\")\n",
    "        return None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "## Summary Table One: Sales by Date by Hour\n",
    " \n",
    "Sales by date by hour: By calendar date (YYYY-MM-DD) and hour of the day, \n",
    "determine the total spend in the store, the number of transactions, and a count of the number of items"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<>:17: SyntaxWarning: invalid escape sequence '\\C'\n",
      "<>:17: SyntaxWarning: invalid escape sequence '\\C'\n",
      "C:\\Users\\justi\\AppData\\Local\\Temp\\ipykernel_16164\\3505907786.py:17: SyntaxWarning: invalid escape sequence '\\C'\n",
      "  sales_date_df.to_csv(f'E:\\College\\Fall 2024\\ADA\\Wedge\\Wedge_Project\\summary_tables\\comma-sep\\sales_date_summary.txt', sep='\\t', index=False)\n",
      "c:\\Users\\justi\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\google\\cloud\\bigquery\\table.py:1727: UserWarning: BigQuery Storage module not found, fetch data with the REST endpoint instead.\n",
      "  warnings.warn(\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data processed: 1896.51 MB\n",
      "Estimated bytes processed: 1988637624\n",
      "Estimated cost: $0.0090432769\n",
      "\n",
      "\n",
      "Estimated bytes processed against a full year of data: 99431881200\n",
      "Estimated cost against a full year of data: $0.45216384569357614964\n",
      "Estimated cost against a full year of data every 6 hours: $660.15921471262117847800\n",
      "\n",
      "---------------\n"
     ]
    }
   ],
   "source": [
    "\n",
    "sales_date_query = f\"\"\"\n",
    "        SELECT\n",
    "        FORMAT_DATETIME('%Y-%m-%d', datetime) AS date,\n",
    "        EXTRACT(HOUR FROM datetime) AS hour,\n",
    "        COUNT(DISTINCT datetime) AS transaction_count,\n",
    "        SUM(quantity) AS total_items_sold,\n",
    "        SUM(total) AS total_spend\n",
    "        FROM\n",
    "        `the_wedge_dataset.transArchive_*`\n",
    "        GROUP BY\n",
    "        date, hour\n",
    "        ORDER BY\n",
    "        date, hour;\n",
    "        \"\"\"\n",
    "\n",
    "sales_date_df = run_query(sales_date_query)\n",
    "sales_date_df.to_csv(f'E:\\College\\Fall 2024\\ADA\\Wedge\\Wedge_Project\\summary_tables\\comma-sep\\sales_date_summary.txt', sep='\\t', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "## Summary Table Two: Sales by Owner by Year by Month\n",
    "A file that has the following columns: card_no, year, month, sales, transactions, and items.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<>:20: SyntaxWarning: invalid escape sequence '\\C'\n",
      "<>:20: SyntaxWarning: invalid escape sequence '\\C'\n",
      "C:\\Users\\justi\\AppData\\Local\\Temp\\ipykernel_16164\\739534758.py:20: SyntaxWarning: invalid escape sequence '\\C'\n",
      "  sales_owner_df.to_csv(f'E:\\College\\Fall 2024\\ADA\\Wedge\\Wedge_Project\\summary_tables\\comma-sep\\sales_owner_summary.txt', sep='\\t', index=False)\n",
      "c:\\Users\\justi\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\google\\cloud\\bigquery\\table.py:1727: UserWarning: BigQuery Storage module not found, fetch data with the REST endpoint instead.\n",
      "  warnings.warn(\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data processed: 2530.61 MB\n",
      "Estimated bytes processed: 2653539520\n",
      "Estimated cost: $0.0120669007\n",
      "\n",
      "\n",
      "Estimated bytes processed against a full year of data: 132676976000\n",
      "Estimated cost against a full year of data: $0.60334503359626978636\n",
      "Estimated cost against a full year of data every 6 hours: $880.88374905055388808250\n",
      "\n",
      "---------------\n"
     ]
    }
   ],
   "source": [
    "\n",
    "sales_owner_year = f\"\"\"\n",
    "        SELECT\n",
    "        card_no,\n",
    "        EXTRACT(YEAR FROM datetime) AS year,\n",
    "        EXTRACT(MONTH FROM datetime) AS month,\n",
    "        COUNT(DISTINCT datetime) AS transactions,\n",
    "        SUM(quantity) AS items,\n",
    "        SUM(total) AS sales\n",
    "        FROM\n",
    "        `the_wedge_dataset.transArchive_*`\n",
    "        WHERE\n",
    "        card_no IS NOT NULL \n",
    "        GROUP BY\n",
    "        card_no, year, month\n",
    "        ORDER BY\n",
    "        card_no, year, month;\n",
    "        \"\"\"\n",
    "\n",
    "sales_owner_df = run_query(sales_owner_year)\n",
    "sales_owner_df.to_csv(f'E:\\College\\Fall 2024\\ADA\\Wedge\\Wedge_Project\\summary_tables\\comma-sep\\sales_owner_summary.txt', sep='\\t', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "## Summary Table Three: Sales by Product Description by Year by Month\n",
    "A file that has the following columns: upc, description, department number, department name, year, month, sales, transactions, and items.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<>:51: SyntaxWarning: invalid escape sequence '\\C'\n",
      "<>:51: SyntaxWarning: invalid escape sequence '\\C'\n",
      "C:\\Users\\justi\\AppData\\Local\\Temp\\ipykernel_16164\\1787018039.py:51: SyntaxWarning: invalid escape sequence '\\C'\n",
      "  sales_product_desc_year_df.to_csv(f'E:\\College\\Fall 2024\\ADA\\Wedge\\Wedge_Project\\summary_tables\\comma-sep\\sales_product_desc_year.txt', sep='\\t', index=False)\n",
      "c:\\Users\\justi\\AppData\\Local\\Programs\\Python\\Python312\\Lib\\site-packages\\google\\cloud\\bigquery\\table.py:1727: UserWarning: BigQuery Storage module not found, fetch data with the REST endpoint instead.\n",
      "  warnings.warn(\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data processed: 5627.87 MB\n",
      "Estimated bytes processed: 5901248863\n",
      "Estimated cost: $0.0268357729\n",
      "\n",
      "\n",
      "Estimated bytes processed against a full year of data: 295062443150\n",
      "Estimated cost against a full year of data: $1.34178864368550421204\n",
      "Estimated cost against a full year of data every 6 hours: $1959.01141978083614958450\n",
      "\n",
      "---------------\n"
     ]
    }
   ],
   "source": [
    "\n",
    "sales_product_desc_year = f\"\"\"\n",
    "        SELECT\n",
    "    upc,\n",
    "    description,\n",
    "    department,\n",
    "    CASE\n",
    "        WHEN department = 1 THEN 'PACKAGED GROCERY'\n",
    "        WHEN department = 2 THEN 'PRODUCE'\n",
    "        WHEN department = 3 THEN 'BULK'\n",
    "        WHEN department = 4 THEN 'REF GROCERY'\n",
    "        WHEN department = 5 THEN 'CHEESE'\n",
    "        WHEN department = 6 THEN 'FROZEN'\n",
    "        WHEN department = 7 THEN 'BREAD'\n",
    "        WHEN department = 8 THEN 'DELI'\n",
    "        WHEN department = 9 THEN 'GEN MERCH'\n",
    "        WHEN department = 10 THEN 'SUPPLEMENTS'\n",
    "        WHEN department = 11 THEN 'PERSONAL CARE'\n",
    "        WHEN department = 12 THEN 'HERBS&SPICES'\n",
    "        WHEN department = 13 THEN 'MEAT'\n",
    "        WHEN department = 14 THEN 'JUICE BAR'\n",
    "        WHEN department = 15 THEN 'MISC P/I'\n",
    "        WHEN department = 16 THEN 'FISH&SEAFOOD'\n",
    "        WHEN department = 17 THEN 'BAKEHOUSE'\n",
    "        WHEN department = 18 THEN 'FLOWERS'\n",
    "        WHEN department = 19 THEN 'WEDGEWORLDWIDE'\n",
    "        WHEN department = 20 THEN 'MISC P/I - WWW'\n",
    "        WHEN department = 21 THEN 'CATERING'\n",
    "        WHEN department = 22 THEN 'BEER & WINE'\n",
    "        ELSE 'UNKNOWN'\n",
    "    END AS department_name,\n",
    "    EXTRACT(YEAR FROM datetime) AS year,\n",
    "    EXTRACT(MONTH FROM datetime) AS month,\n",
    "    SUM(total) AS sales,\n",
    "    COUNT(DISTINCT datetime) AS transactions,\n",
    "    SUM(quantity) AS items\n",
    "FROM\n",
    "  `the_wedge_dataset.transArchive_*`\n",
    "GROUP BY\n",
    "    description,\n",
    "    year,\n",
    "    month,\n",
    "    department,\n",
    "    upc\n",
    "ORDER BY\n",
    "    year,\n",
    "    month,\n",
    "    upc;\n",
    "        \"\"\"\n",
    "\n",
    "sales_product_desc_year_df = run_query(sales_product_desc_year)\n",
    "sales_product_desc_year_df.to_csv(f'E:\\College\\Fall 2024\\ADA\\Wedge\\Wedge_Project\\summary_tables\\comma-sep\\sales_product_desc_year.txt', sep='\\t', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating Database and Tables\n",
    "\n",
    "\n",
    "### Sales Date Summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<>:2: SyntaxWarning: invalid escape sequence '\\C'\n",
      "<>:2: SyntaxWarning: invalid escape sequence '\\C'\n",
      "C:\\Users\\justi\\AppData\\Local\\Temp\\ipykernel_16164\\3588922480.py:2: SyntaxWarning: invalid escape sequence '\\C'\n",
      "  conn = sqlite3.connect('E:\\College\\Fall 2024\\ADA\\Wedge\\Wedge_Project\\summary_tables\\dbLite\\wedge.db')\n"
     ]
    }
   ],
   "source": [
    "# Create a connection to the database file\n",
    "conn = sqlite3.connect('E:\\College\\Fall 2024\\ADA\\Wedge\\Wedge_Project\\summary_tables\\dbLite\\wedge.db')\n",
    "\n",
    "# Create a cursor object to interact with the database\n",
    "cursor = conn.cursor()\n",
    "\n",
    "# Create a table\n",
    "cursor.execute('''\n",
    "CREATE TABLE IF NOT EXISTS sales_date_summary (\n",
    "date DATE,\n",
    "hour INTEGER,\n",
    "transaction_count INTEGER,\n",
    "total_items_sold INTEGER,\n",
    "total_spend REAL\n",
    ")\n",
    "''')\n",
    "\n",
    "# Commit the changes and close the connection\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sales Owner Summary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<>:2: SyntaxWarning: invalid escape sequence '\\C'\n",
      "<>:2: SyntaxWarning: invalid escape sequence '\\C'\n",
      "C:\\Users\\justi\\AppData\\Local\\Temp\\ipykernel_16164\\2865754892.py:2: SyntaxWarning: invalid escape sequence '\\C'\n",
      "  conn = sqlite3.connect('E:\\College\\Fall 2024\\ADA\\Wedge\\Wedge_Project\\summary_tables\\dbLite\\wedge.db')\n"
     ]
    }
   ],
   "source": [
    "# Create a connection to the database file\n",
    "conn = sqlite3.connect('E:\\College\\Fall 2024\\ADA\\Wedge\\Wedge_Project\\summary_tables\\dbLite\\wedge.db')\n",
    "\n",
    "# Create a cursor object to interact with the database\n",
    "cursor = conn.cursor()\n",
    "\n",
    "# Create a table\n",
    "cursor.execute('''\n",
    "CREATE TABLE IF NOT EXISTS sales_owner_summary (\n",
    "card_no REAL,\n",
    "year INTEGER,\n",
    "month INTEGER,\n",
    "transactions REAL,\n",
    "items REAL,\n",
    "sales REAL           \n",
    ")\n",
    "''')\n",
    "\n",
    "# Commit the changes and close the connection\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Sales Product Description\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<>:2: SyntaxWarning: invalid escape sequence '\\C'\n",
      "<>:2: SyntaxWarning: invalid escape sequence '\\C'\n",
      "C:\\Users\\justi\\AppData\\Local\\Temp\\ipykernel_16164\\1719469209.py:2: SyntaxWarning: invalid escape sequence '\\C'\n",
      "  conn = sqlite3.connect('E:\\College\\Fall 2024\\ADA\\Wedge\\Wedge_Project\\summary_tables\\dbLite\\wedge.db')\n"
     ]
    }
   ],
   "source": [
    "# Create a connection to the database file\n",
    "conn = sqlite3.connect('E:\\College\\Fall 2024\\ADA\\Wedge\\Wedge_Project\\summary_tables\\dbLite\\wedge.db')\n",
    "\n",
    "# Create a cursor object to interact with the database\n",
    "cursor = conn.cursor()\n",
    "\n",
    "# Create a table\n",
    "cursor.execute('''\n",
    "CREATE TABLE IF NOT EXISTS sales_product_desc_year_summary (\n",
    "    upc REAL,\n",
    "    description TEXT,\n",
    "    department INTEGER,\n",
    "    department_name TEXT,\n",
    "    year INTEGER,\n",
    "    month INTEGER,\n",
    "    sales REAL,\n",
    "    transactions INTEGER,\n",
    "    items REAL\n",
    ")\n",
    "''')\n",
    "\n",
    "# Commit the changes and close the connection\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Uploading Data to SQLite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data uploaded successfully.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<>:2: SyntaxWarning: invalid escape sequence '\\C'\n",
      "<>:2: SyntaxWarning: invalid escape sequence '\\C'\n",
      "C:\\Users\\justi\\AppData\\Local\\Temp\\ipykernel_16164\\2729933576.py:2: SyntaxWarning: invalid escape sequence '\\C'\n",
      "  conn = sqlite3.connect('E:\\College\\Fall 2024\\ADA\\Wedge\\Wedge_Project\\summary_tables\\dbLite\\wedge.db')\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "# Connect to the SQLite database\n",
    "conn = sqlite3.connect('E:\\College\\Fall 2024\\ADA\\Wedge\\Wedge_Project\\summary_tables\\dbLite\\wedge.db')\n",
    "\n",
    "# Upload the DataFrame to the SQLite table\n",
    "sales_date_df.to_sql('sales_date_summary', conn, if_exists='append', index=False)\n",
    "\n",
    "# Close the connection\n",
    "conn.close()\n",
    "\n",
    "print(\"Data uploaded successfully.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<>:2: SyntaxWarning: invalid escape sequence '\\C'\n",
      "<>:2: SyntaxWarning: invalid escape sequence '\\C'\n",
      "C:\\Users\\justi\\AppData\\Local\\Temp\\ipykernel_16164\\1883380411.py:2: SyntaxWarning: invalid escape sequence '\\C'\n",
      "  conn = sqlite3.connect('E:\\College\\Fall 2024\\ADA\\Wedge\\Wedge_Project\\summary_tables\\dbLite\\wedge.db')\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data uploaded successfully.\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "# Connect to the SQLite database\n",
    "conn = sqlite3.connect('E:\\College\\Fall 2024\\ADA\\Wedge\\Wedge_Project\\summary_tables\\dbLite\\wedge.db')\n",
    "\n",
    "# Upload the DataFrame to the SQLite table\n",
    "sales_owner_df.to_sql('sales_owner_summary', conn, if_exists='append', index=False)\n",
    "\n",
    "# Close the connection\n",
    "conn.close()\n",
    "\n",
    "print(\"Data uploaded successfully.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<>:2: SyntaxWarning: invalid escape sequence '\\C'\n",
      "<>:2: SyntaxWarning: invalid escape sequence '\\C'\n",
      "C:\\Users\\justi\\AppData\\Local\\Temp\\ipykernel_16164\\2355263730.py:2: SyntaxWarning: invalid escape sequence '\\C'\n",
      "  conn = sqlite3.connect('E:\\College\\Fall 2024\\ADA\\Wedge\\Wedge_Project\\summary_tables\\dbLite\\wedge.db')\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data uploaded successfully.\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n",
    "# Connect to the SQLite database\n",
    "conn = sqlite3.connect('E:\\College\\Fall 2024\\ADA\\Wedge\\Wedge_Project\\summary_tables\\dbLite\\wedge.db')\n",
    "\n",
    "# Upload the DataFrame to the SQLite table\n",
    "sales_product_desc_year_df.to_sql('sales_product_desc_year_summary', conn, if_exists='append', index=False)\n",
    "\n",
    "# Close the connection\n",
    "conn.close()\n",
    "\n",
    "print(\"Data uploaded successfully.\")"
   ]
  }
 ],
 "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.12.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}