Intro-creating-tables.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Introduction to Creating Tables\n",
    "\n",
    "To try out how to create tables and insert data into the tables we are using [duckdb](https://duckdb.org/) in this notebook. It is creating a local database which you can run SQL-queries in Jupyter Notebooks against. DBeaver also has a connector so you can also run SQL-queries in there. Duckdb works with [sqlalchemy](https://www.sqlalchemy.org/) a widely used python library to connect to databases and ipython-sql. Ipython-sql allows us to convert a Jupyter code cell into a SQL cell with the jupyter magic command ```%%sql```.\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": [
    "\n",
    "import duckdb\n",
    "import pandas as pd\n",
    "import sqlalchemy\n",
    "import json\n",
    "# No need to import duckdb_engine\n",
    "# SQLAlchemy will auto-detect the driver needed bASed on your connection string!\n",
    "\n",
    "# Import ipython-sql Jupyter extension to create SQL cells\n",
    "%load_ext sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Set configrations on ipython-sql to directly output data to Pandas and to simplify the output that is printed to the notebook."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%config SqlMagic.autopandas = True\n",
    "%config SqlMagic.feedback = False\n",
    "%config SqlMagic.displaycon = False"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Connect ipython-sql to DuckDB using a SQLAlchemy-style connection string. You may either connect to an in memory DuckDB, or a file backed db."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql duckdb:///sampleDB.duckdb"
   ]
  },
  {
   "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",
    "So we will see how to insert csv files and json files into our database but first we will create a tables for the data and for that let's have a look at the data and headers."
   ]
  },
  {
   "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": [
    "Let us start with creating the tables, for that it is standard to define the column types. The column types can differ from sql flavour to sql flavour.\n",
    "\n",
    "We will start with the ```raw_cusomer``` table here we have:\n",
    "- id\n",
    "- first_name\n",
    "- last_name\n",
    "\n",
    "We will also define the ```id``` as ```INTEGER``` and ```PRIMARY KEY``` and additionally we say that it can't have missing values with ```NOT NULL```. The other two columns are strings which is call ```VARCHAR``` in sql."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "CREATE TABLE raw_customers(\n",
    "    id INTEGER PRIMARY KEY NOT NULL,\n",
    "    first_name VARCHAR,\n",
    "    last_name VARCHAR\n",
    "    );\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Next we will create the ```raw_orders``` table:\n",
    "- id\n",
    "- user_id\n",
    "- order_date\n",
    "- status\n",
    "\n",
    "We will define the ```id``` as ```INTEGER``` and ```PRIMARY KEY``` and additionally we say that it can't have missing values with ```NOT NULL```. The ```user_id``` is a ```FOREIGN KEY``` and references the raw_customer id. Order_date is a ```DATE``` and status is a ```VARCHAR```."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "CREATE TABLE raw_orders(\n",
    "    id INTEGER PRIMARY KEY NOT NULL,\n",
    "    user_id INTEGER,\n",
    "    FOREIGN KEY(user_id) REFERENCES raw_customers(id),\n",
    "    order_date DATE,\n",
    "    status VARCHAR\n",
    "    );"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Last we will create the ```raw_payments``` table:\n",
    "- id\n",
    "- order_id\n",
    "- payment_method\n",
    "- amount\n",
    "\n",
    "Again we will define the ```id``` as ```INTEGER``` and ```PRIMARY KEY``` and additionally we say that it can't have missing values with ```NOT NULL```. The ```order_id``` is a ```FOREIGN KEY``` and references the raw_orders id. payment_method is a ```VARCHAR``` and amount is a ```INTEGER```."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "CREATE TABLE raw_payments(\n",
    "    id INTEGER PRIMARY KEY NOT NULL,\n",
    "    order_id INTEGER,\n",
    "    FOREIGN KEY(order_id) REFERENCES raw_orders(id),\n",
    "    payment_method VARCHAR,\n",
    "    amount INTEGER\n",
    "    );"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "So now we created the tables but at the moment they are empty. Let's check that."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "select * from raw_customers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "select * from raw_orders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "select * from raw_payments"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As expected they are empty, so let's change that and use ```INSERT``` for that. Most sql flavours have build in functions to deal with external data sources like csv or json files. In duckdb we can simply use ```read_csv_auto``` to read our csv files and we can than run sql queries against that. For json it is a little bit more complicated we have to create first a table where we store the json itself.\n",
    "\n",
    "For json we we have to install and load the ```json``` extension from duckdb."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "INSTALL 'json';\n",
    "LOAD 'json';"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we have to create the table we store the json in. This table has only one column ```j``` with the data type ```JSON```."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "CREATE TABLE raw_customers_json(\n",
    "    j JSON\n",
    "    );\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can use the ```INSERT INTO``` command followed by a select statement where the json is read with ```read_json_objects()```."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "INSERT INTO raw_customers_json SELECT * FROM read_json_objects('data/raw_customers.json', format='auto')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's have a look if that works:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT * FROM raw_customers_json"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that we have the json in a database we can parse it and put it into our ```raw_customer``` table, we can parse the json with the ```json_extract``` function with the json and the json_path as input e.g. ```json_extract(j, '$.id')```.\n",
    "```json\n",
    "{\"id\":1,\"first_name\":\"Michael\",\"last_name\":\"P.\"}\n",
    "```\n",
    "The example above will get the ```id``` 1.\n",
    "\n",
    "We will also use the ```CAST``` function which converts a value (of any type) into a specified datatype. This will be included in a ```INSERT INTO``` and ```SELECT``` statement. And because there are quotationmarks around the names we ```REPLACE``` them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "INSERT INTO raw_customers\n",
    "SELECT \n",
    "    CAST(json_extract(j, '$.id') AS INTEGER) AS id,\n",
    "    REPLACE(CAST(json_extract(j, '$.first_name')AS VARCHAR),'\"','') AS first_name, \n",
    "    REPLACE(CAST(json_extract(j, '$.last_name')AS VARCHAR),'\"','') AS last_name \n",
    "FROM raw_customers_json;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Inserting the data from the csv files is a little bit easier with the ```read_csv_auto()``` function. \n",
    "\n",
    "First for the orders:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "INSERT INTO raw_orders SELECT id,user_id,order_date,status FROM read_csv_auto('data/raw_orders.csv');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "And for the payments:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "INSERT INTO raw_payments SELECT id,order_id,payment_method,amount FROM read_csv_auto('data/raw_payments.csv');"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's have a look into the data with SQL:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT * FROM raw_customers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT * FROM raw_orders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql\n",
    "\n",
    "SELECT * FROM raw_payments"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Exercises\n",
    "\n",
    "Let's get the RFM features for our data science team."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Recency\n",
    "\n",
    "In order to get recency for each customer you need to join the orders and customer table, get the latest order and calculate the recency in days."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Frequency\n",
    "\n",
    "For frequency we need to get for each customer the total number of orders"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Monetary Value\n",
    "\n",
    "Here you need to also look at payments, and calculate the total money spent by each customer."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%sql"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4. Bonus questions\n",
    "\n",
    "Just because we do data engineering or analytics engineering tasks (calculating features), does not mean we shouldn't be curious about the data. \n",
    "This curiosity can also help us spot mistakes or data inconsistencies, and become recipes for unit tests.\n",
    "\n",
    "Here are some questions you can ask.\n",
    "- when was the last purchase?\n",
    "- what is the average, median recency? what about the range?\n",
    "- how much do people spend on average?\n",
    "- how much did the largest big spender spend?\n",
    "- what is the share of customers that only buy once?\n",
    "\n",
    "How much did you get on the last question? and is the solution query correct? If not, why not?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}