sdk/python/using-mltable/quickstart/mltable-quickstart.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "# 🚀 MLTable Quickstart\n",
    "\n",
    "In this notebook, you create a Table (`mltable`) of the [NYC Green Taxi Data](https://learn.microsoft.com/azure/open-datasets/dataset-taxi-green?tabs=azureml-opendatasets) from Azure Open Datasets. The data is in parquet format and covers year 2008-2021. The data files are in the following folder structure on a publicly accessible blob storage account:\n",
    "\n",
    "```text\n",
    "/\n",
    "└── green\n",
    "    ├── puYear=2008\n",
    "    │   ├── puMonth=1\n",
    "    │   │   ├── _committed_2983805876188002631\n",
    "    │   │   └── part-XXX.snappy.parquet\n",
    "    │   ├── ... \n",
    "    │   └── puMonth=12\n",
    "    │       ├── _committed_2983805876188002631\n",
    "    │       └── part-XXX.snappy.parquet\n",
    "    ├── ...\n",
    "    └── puYear=2021\n",
    "        ├── puMonth=1\n",
    "        │   ├── _committed_2983805876188002631\n",
    "        │   └── part-XXX.snappy.parquet\n",
    "        ├── ...\n",
    "        └── puMonth=12\n",
    "            ├── _committed_2983805876188002631\n",
    "            └── part-XXX.snappy.parquet\n",
    "```\n",
    "\n",
    "With this data, you want to load into a Pandas data frame:\n",
    "\n",
    "- Only the parquet files for years 2015-19.\n",
    "- A random sample of the data.\n",
    "- Correct data (for example, where trip distance is greater than 0).\n",
    "- Relevant columns.\n",
    "- New columns - year and month - using the path information (`puYear=X/puMonth=Y`).\n",
    "\n",
    "You could achieve these data loading steps with Pandas code. However, achieving *reproducibility* is difficult because you'd either need to:\n",
    "\n",
    "1. share code, which means if the schema changes (for example, a column name change) then all users need to update their code, or\n",
    "1. write an ETL pipeline, which is heavy weight.\n",
    "\n",
    "Azure ML Tables provide a light-weight mechanism to serialize (save) the data loading steps in an `MLTable` file so that you and team members can *reproduce* the Pandas data frame. If the schema changes, you only update the `MLTable` file rather than multiple places containing Python data loading code.\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 📦 Install dependencies\n",
    "\n",
    "Ensure you have the latest MLTable library and dependencies."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ensure you have the dependencies for this notebook installed.\n",
    "%pip install -r ../mltable-requirements.txt"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 🐍 Create an MLTable using the Python SDK\n",
    "\n",
    "Here you build your data loading steps using the `mltable` Python SDK. The `show()` method allows you to see the effect of the data loading transformation."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mltable\n",
    "\n",
    "# glob the parquet file paths for years 2015-19, all months.\n",
    "paths = [\n",
    "    {\n",
    "        \"pattern\": \"wasbs://nyctlc@azureopendatastorage.blob.core.windows.net/green/puYear=2015/puMonth=*/*.parquet\"\n",
    "    },\n",
    "    {\n",
    "        \"pattern\": \"wasbs://nyctlc@azureopendatastorage.blob.core.windows.net/green/puYear=2016/puMonth=*/*.parquet\"\n",
    "    },\n",
    "    {\n",
    "        \"pattern\": \"wasbs://nyctlc@azureopendatastorage.blob.core.windows.net/green/puYear=2017/puMonth=*/*.parquet\"\n",
    "    },\n",
    "    {\n",
    "        \"pattern\": \"wasbs://nyctlc@azureopendatastorage.blob.core.windows.net/green/puYear=2018/puMonth=*/*.parquet\"\n",
    "    },\n",
    "    {\n",
    "        \"pattern\": \"wasbs://nyctlc@azureopendatastorage.blob.core.windows.net/green/puYear=2019/puMonth=*/*.parquet\"\n",
    "    },\n",
    "]\n",
    "\n",
    "# create a table from the parquet paths\n",
    "tbl = mltable.from_parquet_files(paths)\n",
    "\n",
    "# table a random sample\n",
    "tbl = tbl.take_random_sample(probability=0.001, seed=735)\n",
    "\n",
    "# filter trips with a distance > 0\n",
    "tbl = tbl.filter(\"col('tripDistance') > 0\")\n",
    "\n",
    "# Drop columns\n",
    "tbl = tbl.drop_columns([\"puLocationId\", \"doLocationId\", \"storeAndFwdFlag\"])\n",
    "\n",
    "# Create two new columns - year and month - where the values are taken from the path\n",
    "tbl = tbl.extract_columns_from_partition_format(\"/puYear={year}/puMonth={month}\")\n",
    "\n",
    "# print the first 5 records of the table as a check\n",
    "tbl.show(5)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 🐼 Load into a Pandas data frame\n",
    "\n",
    "You can load your Azure ML Table into Pandas using:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# You can load the table into a pandas dataframe\n",
    "# NOTE: The data is in East US region and the data is large, so this will take several minutes (~7mins)\n",
    "# to load if you are in a different region.\n",
    "\n",
    "# df = tbl.to_pandas_dataframe()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 💾 Save data loading steps \n",
    "\n",
    "Next, you'll save all your data loading steps into an `MLTable` file. This allows you to *reproduce* your Pandas data frame at a later point in time without having to redefine the data loading steps in your code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# serialize the above data loading steps into an MLTable file\n",
    "tbl.save(\"./nyc_taxi\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 🔍 View the saved file\n",
    "\n",
    "In the next code cell, we show you the `MLTable` file so you can understand how the data loading steps are serialized into a file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"./nyc_taxi/MLTable\", \"r\") as f:\n",
    "    print(f.read())"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## ♻️ Reproduce data loading steps\n",
    "\n",
    "Now that the data loading steps have been serialized into a file, you can reproduce them at any point in time using the `load()` method. This means you do not need to redefine your data loading steps in code and makes it easier to share with others."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mltable\n",
    "\n",
    "# load the previously saved MLTable file\n",
    "tbl = mltable.load(\"./nyc_taxi/\")\n",
    "\n",
    "# You can load the table into a pandas dataframe\n",
    "# NOTE: The data is in East US region and the data is large, so this will take several minutes (~7mins)\n",
    "# to load if you are in a different region.\n",
    "\n",
    "# load the table into pandas\n",
    "# df = tbl.to_pandas_dataframe()\n",
    "\n",
    "# print the head of the data frame\n",
    "# df.head()\n",
    "# print the shape and column types of the data frame\n",
    "# print(f\"Shape: {df.shape}\")\n",
    "# print(f\"Columns:\\n{df.dtypes}\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 🤝 Create a data asset to aid sharing and reproducibility\n",
    "\n",
    "Your `MLTable` file is currently saved on disk, making it hard to share with Team members. By creating a *data asset* in AzureML, your MLTable will be uploaded to cloud storage and \"bookmarked\", meaning your Team members can access the MLTable using a friendly name. Also, the data asset is *versioned*."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "subscription_id = \"<SUBSCRIPTION_ID>\"\n",
    "resource_group = \"<RESOURCE_GROUP>\"\n",
    "workspace = \"<AML_WORKSPACE_NAME>\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import time\n",
    "from azure.ai.ml import MLClient\n",
    "from azure.ai.ml.entities import Data\n",
    "from azure.ai.ml.constants import AssetTypes\n",
    "from azure.identity import DefaultAzureCredential\n",
    "\n",
    "# set the version number of the data asset to the current UTC time\n",
    "VERSION = time.strftime(\"%Y.%m.%d.%H%M%S\", time.gmtime())\n",
    "\n",
    "# connect to the AzureML workspace\n",
    "ml_client = MLClient(\n",
    "    DefaultAzureCredential(), subscription_id, resource_group, workspace\n",
    ")\n",
    "\n",
    "my_data = Data(\n",
    "    path=\"./nyc_taxi\",\n",
    "    type=AssetTypes.MLTABLE,\n",
    "    description=\"A random sample of NYC Green Taxi Data between 2015-19.\",\n",
    "    name=\"green-quickstart\",\n",
    "    version=VERSION,\n",
    ")\n",
    "\n",
    "ml_client.data.create_or_update(my_data)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 📖 Read the data asset in an interactive session\n",
    "\n",
    "Now you have your MLTable stored in the cloud, you and Team members can access it using a friendly name in an interactive session (for example, a notebook)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mltable\n",
    "from azure.ai.ml import MLClient\n",
    "from azure.identity import DefaultAzureCredential\n",
    "\n",
    "# connect to the AzureML workspace\n",
    "ml_client = MLClient(\n",
    "    DefaultAzureCredential(), subscription_id, resource_group, workspace\n",
    ")\n",
    "\n",
    "# get the latest version of the data asset\n",
    "# Note: The version was set in the previous code cell.\n",
    "data_asset = ml_client.data.get(name=\"green-quickstart\", version=VERSION)\n",
    "\n",
    "# create a table\n",
    "tbl = mltable.load(f\"azureml:/{data_asset.id}\")\n",
    "\n",
    "tbl.show(5)\n",
    "\n",
    "# load into pandas\n",
    "# NOTE: The data is in East US region and the data is large, so this will take several minutes (~7mins) to load if you are in a different region.\n",
    "# df = tbl.to_pandas_dataframe()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 📖 Read the data asset in a job\n",
    "\n",
    "You can also access your Table in a job, using:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from azure.ai.ml import MLClient, command, Input\n",
    "from azure.ai.ml.entities import Environment\n",
    "from azure.identity import DefaultAzureCredential\n",
    "\n",
    "# connect to the AzureML workspace\n",
    "ml_client = MLClient(\n",
    "    DefaultAzureCredential(), subscription_id, resource_group, workspace\n",
    ")\n",
    "\n",
    "# get the latest version of the data asset\n",
    "# Note: the VERSION was set in a previous cell.\n",
    "data_asset = ml_client.data.get(name=\"green-quickstart\", version=VERSION)\n",
    "\n",
    "job = command(\n",
    "    command=\"python train.py --input ${{inputs.green}}\",\n",
    "    inputs={\"green\": Input(type=\"mltable\", path=data_asset.id)},\n",
    "    compute=\"cpu-cluster\",\n",
    "    environment=Environment(\n",
    "        image=\"mcr.microsoft.com/azureml/openmpi4.1.0-ubuntu20.04\",\n",
    "        conda_file=\"./job-env/conda_dependencies.yml\",\n",
    "    ),\n",
    "    code=\"./src\",\n",
    ")\n",
    "\n",
    "ml_client.jobs.create_or_update(job)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10 - SDK V2",
   "language": "python",
   "name": "python310-sdkv2"
  },
  "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.10.9"
  },
  "nteract": {
   "version": "nteract-front-end@1.0.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}