provider/tutorial/notebooks/part1-load-data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load feature values into the Feature store\n",
    "\n",
    "The objective of this tutorial is to build a model that predicts if a driver will complete a trip based on a number of features ingested into Feast.\n",
    "\n",
    "This notebook creates you will create and load data into the following 3 feature tables.\n",
    "\n",
    "**Customer Profile**: This contains features related to a customer entity such as current balance, lifetime trip count, average number of passengers per trip. A snippet of data:\n",
    "\n",
    "| datetime         | customer_id | current_balance | avg_passenger_count | lifetime_trip_count | created          |\n",
    "|------------------|-------------|-----------------|---------------------|---------------------|------------------|\n",
    "| 2021-03-17 19:31 | 1010        | 0.889188        |     0.049057        |          412        | 2021-03-24 19:38 |\n",
    "| 2021-03-18 19:31 | 1010        | 0.979273        |     0.212630        |          639        | 2021-03-24 19:38 |\n",
    "| 2021-03-19 19:31 | 1010        | 0.976549        |     0.176881        |           70        | 2021-03-24 19:38 |\n",
    "| 2021-03-20 19:31 | 1010        | 0.273697        |     0.325012        |           68        | 2021-03-24 19:38 |\n",
    "\n",
    "**Driver table**: This contains features related to a driver entity such as conversion rate, average number of daily trips. A snippet of data:\n",
    "\n",
    "| datetime         | driver_id | conv_rate | acc_rate | avg_daily_trips | created          |\n",
    "|------------------|-----------|-----------|----------|-----------------|------------------|\n",
    "| 2021-03-17 19:31 |     5010  | 0.229297  | 0.685843 | 861             | 2021-03-24 19:34 |\n",
    "| 2021-03-17 20:31 |     5010  | 0.781655  | 0.861280 | 769             | 2021-03-24 19:34 |\n",
    "| 2021-03-17 21:31 |     5010  | 0.150333  | 0.525581 | 778             | 2021-03-24 19:34 |\n",
    "| 2021-03-17 22:31 |     5010  | 0.951701  | 0.228883 | 570             | 2021-03-24 19:34 |\n",
    "\n",
    "\n",
    "**Orders table**: This is a typical *fact table* that contains the order information such driver/customer id and whether the trip was completed. A snippet of data:\n",
    "\n",
    "| order_id | driver_id | customer_id | order_is_success |    event_timestamp  |\n",
    "|----------|-----------|-------------|------------------|---------------------|\n",
    "|      100 |      5004 |        1007 |                0 | 2021-03-10 19:31:15 |\n",
    "|      101 |      5003 |        1006 |                0 | 2021-03-11 22:02:50 |\n",
    "|      102 |      5010 |        1005 |                0 | 2021-03-13 00:34:24 |\n",
    "|      103 |      5010 |        1001 |                1 | 2021-03-14 03:05:59 |\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.sql import text\n",
    "from azureml.core import Workspace\n",
    "\n",
    "ws = Workspace.from_config()\n",
    "kv = ws.get_default_keyvault()\n",
    "\n",
    "engine = create_engine(kv.get_secret(\"FEAST-OFFLINE-STORE-CONN\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "## Create Customer profile table and load data\n",
    "The cell below will create the customer profile table and load the data into the Synapse table. Loading is achieved using the ``COPY INTO` bulk load available in Synapse (the CSV data is available on public blob):\n",
    "\n",
    "```sql\n",
    "COPY INTO dbo.customer_profile\n",
    "FROM 'https://feastonazuredatasamples.blob.core.windows.net/feastdatasamples/customer_profile.csv'\n",
    "WITH\n",
    "(\n",
    "\tFILE_TYPE = 'CSV'\n",
    "\t,FIRSTROW = 2\n",
    "\t,MAXERRORS = 0\n",
    ")\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with engine.connect() as con:\n",
    "    # create and load customer profile table\n",
    "    file = open(\"../sql/create_cx_profile_table.sql\")\n",
    "    query = text(file.read())\n",
    "    print(\"creating customer profile table...\", end=\"\")\n",
    "    con.execute(query)\n",
    "    print(\"done\")\n",
    "    file = open(\"../sql/load_cx_profile_data.sql\")\n",
    "    query = text(file.read())\n",
    "    print(\"loading customer profile data...\", end=\"\")\n",
    "    con.execute(query)\n",
    "    print(\"done\")\n",
    "\n",
    "pd.read_sql_query(\"select top 10 * from dbo.customer_profile\", engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create drivers table and load data\n",
    "The cell below will create the drivers table and load the data into the Synapse table. Loading is achieved using the ``COPY INTO` bulk load available in Synapse (the CSV data is available on public blob):\n",
    "\n",
    "```sql\n",
    "COPY INTO dbo.driver_hourly\n",
    "FROM 'https://feastonazuredatasamples.blob.core.windows.net/feastdatasamples/driver_hourly.csv'\n",
    "WITH\n",
    "(\n",
    "\tFILE_TYPE = 'CSV'\n",
    "\t,FIRSTROW = 2\n",
    "\t,MAXERRORS = 0\n",
    ")\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with engine.connect() as con:\n",
    "    file = open(\"../sql/create_drivers_table.sql\")\n",
    "    query = text(file.read())\n",
    "    print(\"creating drivers table...\", end=\"\")\n",
    "    con.execute(query)\n",
    "    print(\"done\")\n",
    "    file = open(\"../sql/load_drivers_data.sql\")\n",
    "    query = text(file.read())\n",
    "    print(\"loading drivers data...\", end=\"\")\n",
    "    con.execute(query)\n",
    "    print(\"done\")\n",
    "\n",
    "pd.read_sql_query(\"select top 10 * from dbo.driver_hourly\", engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Create orders table and load data\n",
    "The cell below will create the orders table and load the data into the Synapse table. Loading is achieved using the ``COPY INTO` bulk load available in Synapse (the CSV data is available on public blob):\n",
    "\n",
    "```sql\n",
    "COPY INTO dbo.orders\n",
    "FROM 'https://feastonazuredatasamples.blob.core.windows.net/feastdatasamples/orders.csv'\n",
    "WITH\n",
    "(\n",
    "\tFILE_TYPE = 'CSV'\n",
    "\t,FIRSTROW = 2\n",
    "\t,MAXERRORS = 0\n",
    ")\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with engine.connect() as con:\n",
    "    file = open(\"../sql/create_orders_table.sql\")\n",
    "    query = text(file.read())\n",
    "    print(\"creating orders table...\", end=\"\")\n",
    "    con.execute(query)\n",
    "    print(\"done\")\n",
    "    file = open(\"../sql/load_orders_data.sql\")\n",
    "    query = text(file.read())\n",
    "    print(\"loading orders data...\", end=\"\")\n",
    "    con.execute(query)\n",
    "    print(\"done\")\n",
    "\n",
    "pd.read_sql_query(\"select top 10 * from dbo.orders\", engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Next Steps\n",
    "With the feature values loaded into the feature store, you will need to register the features in the feast central registry. [Follow the Register Features part of the tutorial](./01-register-features.ipynb)."
   ]
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "345f65c856c916347c1c128e68c8bf02d14700c226b24dbdbabf5b612d5c3621"
  },
  "kernelspec": {
   "display_name": "Python 3.8.12 64-bit ('feast-dev': conda)",
   "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.8.12"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}