Intro-sql-in-python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Python SQL Tutorial\n",
    "\n",
    "In this notebook, we will show you how to use Fugue SQL to work with SQL in a python environment. Fugue SQL is a SQL engine that can run on Spark, Dask, Ray, and Pandas. It is a SQL engine that is designed for data scientists and engineers and to be easy to use.\n",
    "It also allows us to convert a Jupyter code cell into a SQL cell with the jupyter magic command ```%%fsql```.\n",
    "\n",
    "DuckDB is an in-process SQL OLAP database management system. The speed is very good on even gigabytes of data on local machines. Fugue has a deep integration with DuckDB. Fugue not only uses DuckDB as the SQL engine, but also implemented all execution engine methods using DuckDB SQL and relations. So in most part of the workflow, the data tables are kept in DuckDB and in rare cases the tables will be materialized and converted to arrow dataframes. \n",
    "\n",
    "In this notebook you will learn how to create tables and insert data into the created tables."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import duckdb\n",
    "import pandas as pd\n",
    "import sqlalchemy\n",
    "import json\n",
    "from fugue_notebook import setup\n",
    "import fugue_duckdb\n",
    "\n",
    "setup()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have two csv files in our data directory:\n",
    "- raw_orders.csv\n",
    "- raw_payments.csv\n",
    "\n",
    "and a json file:\n",
    "- raw_customers.json\n",
    "\n",
    "\n",
    "We will use pandas to load the data first:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_customers = pd.read_json('data/raw_customers.json', orient='records', lines=True)\n",
    "df_customers.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_orders = pd.read_csv('data/raw_orders.csv')\n",
    "df_orders.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_payment = pd.read_csv('data/raw_payments.csv')\n",
    "df_payment.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But we could do the same with fugue. With ```%%fsql``` we can convert the cell into a SQL cell and use fugue SQL to query the data. ```duck```  is the name of the execution engine which is DuckDB in our case.\n",
    "\n",
    "With ```LOAD``` we can import our data and save it in a variable. We can use the variable name to query the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%fsql duck\n",
    "\n",
    "raw_customers = LOAD \"data/raw_customers.json\"\n",
    "\n",
    "SELECT * FROM raw_customers\n",
    "PRINT"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As you can see you can load the data from various sources like csv, json, parquet, etc. and you can also load data from a database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%fsql duck\n",
    "\n",
    "raw_orders = LOAD \"data/raw_orders.csv\" (header = \"true\")\n",
    "\n",
    "SELECT * FROM raw_orders\n",
    "PRINT"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%fsql duck\n",
    "\n",
    "raw_payments = LOAD \"data/raw_payments.csv\" (header = \"true\")\n",
    "\n",
    "SELECT * FROM raw_payments\n",
    "\n",
    "PRINT"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can have a look at some questions. For example the RFM (Recency, Frequency, and Monetary) questions that are relevant for analysing customer behaviour. Here we can either load the data again with fugue or combine pandas and fugue and use fugue on top of pandas dataframes.\n",
    "\n",
    "- When did the customers last purchase?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "First we will join the orders and the customers table to get the customers name and the order date in one table and save it with ```YIELD``` in a dataframe ```df1``` that we can also use outside of this cell.\n",
    "\n",
    "There are also ways to save the data in a database or as a files with ```SAVE```."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%fsql\n",
    "\n",
    "SELECT o.user_id, o.order_date, c.first_name, c.last_name FROM df_orders AS o\n",
    "JOIN df_customers AS c\n",
    "ON o.user_id = c.id\n",
    "YIELD DATAFRAME AS df1\n",
    "\n",
    "PRINT"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can write a python function that we can use with fugue in SQL. For that we have to define the schema first. Here we will use everything so we will use the ```*``` to select all columns. The schema is defined with a comment before the function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "#schema: *\n",
    "def get_latest_order_date_per_customer(df: pd.DataFrame) -> pd.DataFrame:\n",
    "    return df.sort_values('order_date', ascending=False).groupby('user_id').first().reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This python function will return the maximum date of the order date column. And can be used now in a fugue SQL query. For that we will use the ```TRANSFORM``` command. This command will apply the function to the dataframe. The function we want to use is defined by the ```USING``` command. The result will be saved in a new dataframe ```df2```."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%fsql duck\n",
    "\n",
    "df2 = TRANSFORM df1 USING get_latest_order_date_per_customer\n",
    "YIELD DATAFRAME AS df2\n",
    "PRINT"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This Dataframe ```df2``` can than also be used again as a pandas DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 1\n",
    "\n",
    "-  What is the customer recency in days?\n",
    "\n",
    "The answer to this question you can get from the previous table, by getting the date difference. Do this as an exercise."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%fsql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-  How much did they spend?\n",
    "\n",
    "To answer this question let us join all 3 tables together and save it in a dataframe ```df3```.\n",
    "\n",
    "Additionally let's save the joined table as a parquet file for possible future use. Also we will see here that we can use global variables inside of fugue sql cells if we use double curly brackets ```{{}}``` around them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "PATH = os.path.join(os.getcwd())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%fsql\n",
    "\n",
    "SELECT o.user_id, c.first_name, c.last_name, p.amount FROM df_orders AS o\n",
    "JOIN df_customers AS c\n",
    "ON o.user_id = c.id\n",
    "JOIN df_payment AS p\n",
    "ON o.id = p.order_id\n",
    "YIELD DATAFRAME AS df3\n",
    "\n",
    "SAVE OVERWRITE \"{{PATH}}/data/joined_data.parquet\" \n",
    "\n",
    "PRINT\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And now we can simply sum about the amount per customer:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%fsql duck\n",
    "\n",
    "SELECT first_name, last_name, SUM(amount) FROM df3\n",
    "GROUP BY first_name, last_name\n",
    "PRINT"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 2\n",
    "\n",
    "Now you can try and answer the next questions as an exercise either you can write a python function that you apply with ```TRANSFORM``` or you can use the SQL to answer the questions. How does the query look without TRANSFORM?\n",
    "\n",
    "- What is the most common payment method?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%fsql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercise 3\n",
    "\n",
    "- Create a table that includes the aggregated data from the questions above!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%fsql"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.4"
  },
  "vscode": {
   "interpreter": {
    "hash": "acb89e642cd2cd6c1dc061b354ab19b4cee33abd1eb266de0681fafafaae361f"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}