notebook_examples/feature_store_querying.ipynb

{
 "cells": [
  {
   "cell_type": "raw",
   "id": "e0578ecc",
   "metadata": {},
   "source": [
    "qweews@notebook{feature_store-querying.ipynb,\n",
    "    title: Feature store handling querying operations\n",
    "    summary:  Using feature store to transform, store and query your data using pandas like interface to query and join\n",
    "    developed_on: fspyspark32_p38_cpu_v3,\n",
    "    keywords: feature store, querying,\n",
    "    license: Universal Permissive License v 1.0\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "948fa9e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "!odsc conda install -s fspyspark32_p38_cpu_v3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b9f398c8",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "Oracle Data Science service sample notebook.\n",
    "\n",
    "Copyright (c) 2022, 2023 Oracle, Inc. All rights reserved. Licensed under the [Universal Permissive License v 1.0](https://oss.oracle.com/licenses/upl).\n",
    "\n",
    "***\n",
    "\n",
    "# <font color=\"red\">Feature store handling querying operations</font>\n",
    "<p style=\"margin-left:10%; margin-right:10%;\">by the <font color=\"teal\">Oracle Cloud Infrastructure Data Science Service.</font></p>\n",
    "\n",
    "---\n",
    "# Overview:\n",
    "---\n",
    "Managing many datasets, data sources, and transformations for machine learning is complex and costly. Poorly cleaned data, data issues, bugs in transformations, data drift, and training serving skew all lead to increased model development time and poor model performance. Feature store can be used to solve many of the problems becuase it provides a centralised way to transform and access data for training and serving time. Feature store helps define a standardised pipeline for ingestion of data and querying of data.This notebook demonstrates how to use feature store using a notebook spark session.\n",
    "\n",
    "Compatible conda pack: [PySpark 3.2 and Feature Store](https://docs.oracle.com/iaas/data-science/using/conda-pyspark-fam.htm) for CPU on Python 3.8 (version 1.0)\n",
    "\n",
    "## Contents:\n",
    "\n",
    "- <a href=\"#concepts\">1. Introduction</a>\n",
    "- <a href='#pre-requisites'>2. Pre-requisites to Running this Notebook</a>\n",
    "    - <a href='#setup'>2.1. Setup</a>\n",
    "    - <a href='#policies'>2.2 Policies</a>\n",
    "    - <a href='#prerequisites_authentication'>2.3 Authentication</a>\n",
    "    - <a href='#prerequisites_variables'>2.4 Variables</a>\n",
    "- <a href='#featurestore_querying'>3. Feature store querying</a>\n",
    "    - <a href='#data_exploration'>3.1. Exploration of data in feature store</a>\n",
    "    - <a href='#load_featuregroup'>3.2. Create feature store logical entities</a>\n",
    "    - <a href='#explore_featuregroup'>3.3. Explore feature groups</a>\n",
    "    - <a href='#select_subset_featuregroup'>3.4. Select subset of features</a>\n",
    "    - <a href='#filter_featuregroup'>3.5. Filter feature groups</a>\n",
    "    - <a href='#join_featuregroup'>3.6. Apply joins on feature groups</a>\n",
    "    - <a href='#create_dataset'>3.7. Create dataset from multiple or one feature group</a>\n",
    "    - <a href='#sql_query'>3.8. Free form sql query</a>\n",
    "    - <a href='#featurestore_yaml'>3.9. Feature store Entities using YAML</a>\n",
    "- <a href='#ref'>4. References</a>\n",
    "\n",
    "---\n",
    "\n",
    "**Important:**\n",
    "\n",
    "Placeholder text for required values are surrounded by angle brackets that must be removed when adding the indicated content. For example, when adding a database name to `database_name = \"<database_name>\"` would become `database_name = \"production\"`.\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "42049fc1",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<a id=\"concepts\"></a>\n",
    "# 1. Introduction\n",
    "\n",
    "OCI Data Science feature store is a stack-based API solution that's deployed using OCI Resource Manager in your tenancy.\n",
    "\n",
    "Review the following key terms to understand the Data Science feature store:\n",
    "\n",
    "\n",
    "* **Feature Vector**: Set of feature values for any one primary or identifier key. For example, all or a subset of features of customer id ‘2536’ can be called as one feature vector.\n",
    "\n",
    "* **Feature**: A feature is an individual measurable property or characteristic of a phenomenon being observed.\n",
    "\n",
    "* **Entity**: An entity is a group of semantically related features. The first step a consumer of features would typically do when accessing the feature store service is to list the entities and the entities associated features. Or an entity is an object or concept that is described by its features. Examples of entities are customer, product, transaction, review, image, document, and so on.\n",
    "\n",
    "* **Feature Group**: A feature group in a feature store is a collection of related features that are often used together in machine learning (ML) models. It serves as an organizational unit within the feature store for you to manage, version and share features across different ML projects. By organizing features into groups, data scientists and ML engineers can efficiently discover, reuse and collaborate on features reducing the redundant work and ensuring consistency in feature engineering.\n",
    "\n",
    "* **Feature Group Job**: A feature group job is the processing instance of a feature group. Each feature group job  includes validation results and statistics results.\n",
    "\n",
    "* **Dataset**: A dataset is a collection of features that are used together to either train a model or perform model inference.\n",
    "\n",
    "* **Dataset Job**: dataset job is the processing instance of a dataset. Each dataset job includes validation results and statistics results."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d0a334f4",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<a id='pre-requisites'></a>\n",
    "# 2. Pre-requisites to Running this Notebook\n",
    "\n",
    "Notebook Sessions are accessible through the following conda environment: \n",
    "\n",
    "* **PySpark 3.2 and Feature Store Python 3.8 (fspyspark32_p38_cpu_v3)**\n",
    "\n",
    "You can customize `fspyspark32_p38_cpu_v3`, publish it, and use it as a runtime environment for a Notebook session.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4af0af00",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<a id='setup'></a>\n",
    "### 2.1. Setup\n",
    "\n",
    "<a id='setup_spark-defaults'></a>\n",
    "### `spark-defaults.conf`\n",
    "\n",
    "The `spark-defaults.conf` file is used to define the properties that are used by Spark. A templated version is installed when you install a Data Science conda environment that supports PySpark. However, you must update the template so that the Data Catalog metastore can be accessed. You can do this manually. However, the `odsc data-catalog config` commandline tool is ideal for setting up the file because it gathers information about your environment, and uses that to build the file.\n",
    "\n",
    "The `odsc data-catalog config` command line tool needs the `--metastore` option to define the Data Catalog metastore OCID. No other command line option is needed because settings have default values, or they take values from your notebook session environment. Following are common parameters that you may need to override.\n",
    "\n",
    "The `--authentication` option sets the authentication mode. It supports resource principal and API keys. The preferred method for authentication is resource principal, which is sent with `--authentication resource_principal`. If you want to use API keys, then use the `--authentication api_key` option. If the `--authentication` isn't specified, API keys are used. When API keys are used, information from the OCI configuration file is used to create the `spark-defaults.conf` file.\n",
    "\n",
    "Object Storage and Data Catalog are regional services. By default, the region is set to the region your notebook session is running in. This information is taken from the environment variable, `NB_REGION`. Use the `--region` option to override this behavior.\n",
    "\n",
    "The default location of the `spark-defaults.conf` file is `/home/datascience/spark_conf_dir` as defined in the `SPARK_CONF_DIR` environment variable. Use the `--output` option to define the directory where to write the file.\n",
    "\n",
    "You need to determine what settings are appropriate for your configuration. However, the following works for most configurations and is run in a terminal window.\n",
    "\n",
    "```bash\n",
    "odsc data-catalog config --authentication resource_principal --metastore <metastore_id>\n",
    "```\n",
    "For more assistance, use the following command in a terminal window:\n",
    "\n",
    "```bash\n",
    "odsc data-catalog config --help\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2a7e4d36",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<a id='policies'></a>\n",
    "### 2.2. Policies\n",
    "This section covers the creation of dynamic groups and policies needed to use the service.\n",
    "\n",
    "* [Data Flow Policies](https://docs.oracle.com/iaas/data-flow/using/policies.htm)\n",
    "* [Data Catalog Metastore Required Policies](https://docs.oracle.com/en-us/iaas/data-catalog/using/metastore.htm)\n",
    "* [Getting Started with Data Flow](https://docs.oracle.com/iaas/data-flow/using/dfs_getting_started.htm)\n",
    "* [About Data Science Policies](https://docs.oracle.com/iaas/data-science/using/policies.htm)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "01b4a125",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<a id=\"prerequisites_authentication\"></a>\n",
    "### 2.3. Authentication\n",
    "The [Oracle Accelerated Data Science SDK (ADS)](https://docs.oracle.com/iaas/tools/ads-sdk/latest/index.html) controls the authentication mechanism with the notebook session.<br>\n",
    "To setup authentication use the ```ads.set_auth(\"resource_principal\")``` or ```ads.set_auth(\"api_key\")```."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "83fcace3",
   "metadata": {
    "ExecuteTime": {
     "start_time": "2023-05-24T08:26:08.577504Z"
    },
    "is_executing": true,
    "pycharm": {
     "is_executing": true,
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import ads\n",
    "ads.set_auth(auth=\"resource_principal\", client_kwargs={\"fs_service_endpoint\": \"https://{api_gateway}/20230101\"})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19776f21",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<a id=\"prerequisites_variables\"></a>\n",
    "### 2.4. Variables\n",
    "To run this notebook, you must provide some information about your tenancy configuration. To create and run a feature store, you must specify a `<compartment_id>` and `<metastore_id>` which is the OCID of the Data Catalog metastore."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5f64eeed",
   "metadata": {
    "pycharm": {
     "is_executing": true,
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "compartment_id = os.environ.get(\"NB_SESSION_COMPARTMENT_OCID\")\n",
    "metastore_id = \"<metastore_id>\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f0a83335",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<a id=\"featurestore_querying\"></a>\n",
    "# 3. Feature store querying\n",
    "By default the **PySpark 3.2 and Feature Store Python 3.8** conda environment includes pre-installed [great-expectations](https://legacy.docs.greatexpectations.io/en/latest/reference/core_concepts/validation.html) library. In an ADS feature store module, you can either use the Python programmatic or YAML interface to define feature store entities. The joining functionality is heavily inspired by the APIs used by Pandas to merge, join or filter DataFrames. The APIs allow you to specify which features to select from which feature group, how to join them and which features to use in join conditions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eb3412d3",
   "metadata": {
    "pycharm": {
     "is_executing": true,
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\", message=\"iteritems is deprecated\")\n",
    "warnings.filterwarnings(\"ignore\", category=DeprecationWarning)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "045ccbd5",
   "metadata": {
    "pycharm": {
     "is_executing": true,
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from ads.feature_store.feature_store import FeatureStore\n",
    "from ads.feature_store.feature_group import FeatureGroup\n",
    "from ads.feature_store.model_details import ModelDetails\n",
    "from ads.feature_store.dataset import Dataset\n",
    "from ads.feature_store.common.enums import DatasetIngestionMode\n",
    "\n",
    "from ads.feature_store.feature_group_expectation import ExpectationType\n",
    "from great_expectations.core import ExpectationSuite, ExpectationConfiguration\n",
    "from ads.feature_store.feature_store_registrar import FeatureStoreRegistrar"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d563816",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<a id=\"data_exploration\"></a>\n",
    "### 3.1. Exploration of data in feature store"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1555f0c6",
   "metadata": {
    "pycharm": {
     "is_executing": true,
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "flights_df = pd.read_csv(\"https://objectstorage.us-ashburn-1.oraclecloud.com/p/hh2NOgFJbVSg4amcLM3G3hkTuHyBD-8aE_iCsuZKEvIav1Wlld-3zfCawG4ycQGN/n/ociodscdev/b/oci-feature-store/o/beta/data/flights/flights.csv\")[['YEAR', 'MONTH', 'DAY', 'DAY_OF_WEEK', 'AIRLINE', 'FLIGHT_NUMBER', 'ORIGIN_AIRPORT', 'DESTINATION_AIRPORT']]\n",
    "flights_df = flights_df.head(100)\n",
    "flights_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5e5f465a",
   "metadata": {
    "pycharm": {
     "is_executing": true,
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "airports_df = pd.read_csv(\"https://objectstorage.us-ashburn-1.oraclecloud.com/p/hh2NOgFJbVSg4amcLM3G3hkTuHyBD-8aE_iCsuZKEvIav1Wlld-3zfCawG4ycQGN/n/ociodscdev/b/oci-feature-store/o/beta/data/flights/airports.csv\")\n",
    "airports_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3ec97d4f",
   "metadata": {
    "pycharm": {
     "is_executing": true,
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "airlines_df = pd.read_csv(\"https://objectstorage.us-ashburn-1.oraclecloud.com/p/hh2NOgFJbVSg4amcLM3G3hkTuHyBD-8aE_iCsuZKEvIav1Wlld-3zfCawG4ycQGN/n/ociodscdev/b/oci-feature-store/o/beta/data/flights/airlines.csv\")\n",
    "airlines_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3591d3f5",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<a id=\"load_featuregroup\"></a>\n",
    "### 3.2. Create feature store logical entities"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "afe201db",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<a id=\"create_feature_store\"></a>\n",
    "#### 3.2.1 Feature Store\n",
    "Feature store is the top level entity for feature store service\n",
    "\n",
    "Call the ```.create()``` method of the Feature store instance to create a feature store."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d9d6ad03",
   "metadata": {
    "pycharm": {
     "is_executing": true,
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_store_resource = (\n",
    "    FeatureStore().\n",
    "    with_description(\"Data consisting of flights\").\n",
    "    with_compartment_id(compartment_id).\n",
    "    with_name(\"flights details\").\n",
    "    with_offline_config(metastore_id=metastore_id)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "227026c4",
   "metadata": {
    "pycharm": {
     "is_executing": true,
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_store = feature_store_resource.create()\n",
    "feature_store"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef5a4667",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "#### 3.2.2 Entity\n",
    "An entity is a group of semantically related features."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45085ab9",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "entity = feature_store.create_entity(\n",
    "    name=\"Flight details2\",\n",
    "    description=\"description for flight details\"\n",
    ")\n",
    "entity"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "632bf93e",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "#### 3.2.3 Feature group\n",
    "A feature group is an object that represents a logical group of time-series feature data as it is found in a datasource."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b89495e",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<a id=\"create_feature_group_flights\"></a>\n",
    "##### Flights Feature Group\n",
    "\n",
    "Create feature group for flights\n",
    "\n",
    "<div>\n",
    "    <img src=\"https://objectstorage.us-ashburn-1.oraclecloud.com/p/hh2NOgFJbVSg4amcLM3G3hkTuHyBD-8aE_iCsuZKEvIav1Wlld-3zfCawG4ycQGN/n/ociodscdev/b/oci-feature-store/o/beta/_images/feature_group_flights.gif\" width=\"700\" height=\"350\" />\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1d43d22e",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_group_flights = (\n",
    "    FeatureGroup()\n",
    "    .with_feature_store_id(feature_store.id)\n",
    "    .with_primary_keys([\"FLIGHT_NUMBER\"])\n",
    "    .with_name(\"flights_feature_group\")\n",
    "    .with_entity_id(entity.id)\n",
    "    .with_compartment_id(compartment_id)\n",
    "    .with_schema_details_from_dataframe(flights_df)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "57b4f4ba",
   "metadata": {
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_group_flights.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6f44fb84",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_group_flights.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dbe7bf1d",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_group_flights.materialise(flights_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d1375d51",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<a id=\"create_feature_group_airport\"></a>\n",
    "##### Airport Feature Group\n",
    "\n",
    "Create feature group for airport"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7478156",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "expectation_suite_airports = ExpectationSuite(\n",
    "    expectation_suite_name=\"test_airports_df\"\n",
    ")\n",
    "expectation_suite_airports.add_expectation(\n",
    "    ExpectationConfiguration(\n",
    "        expectation_type=\"expect_column_values_to_not_be_null\",\n",
    "        kwargs={\"column\": \"IATA_CODE\"},\n",
    "    )\n",
    ")\n",
    "expectation_suite_airports.add_expectation(\n",
    "    ExpectationConfiguration(\n",
    "        expectation_type=\"expect_column_values_to_be_between\",\n",
    "        kwargs={\"column\": \"LATITUDE\", \"min_value\": -1.0, \"max_value\": 1.0},\n",
    "    )\n",
    ")\n",
    "\n",
    "expectation_suite_airports.add_expectation(\n",
    "    ExpectationConfiguration(\n",
    "        expectation_type=\"expect_column_values_to_be_between\",\n",
    "        kwargs={\"column\": \"LONGITUDE\", \"min_value\": -1.0, \"max_value\": 1.0},\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "98b0d9d9",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_group_airports = (\n",
    "    FeatureGroup()\n",
    "    .with_feature_store_id(feature_store.id)\n",
    "    .with_primary_keys([\"IATA_CODE\"])\n",
    "    .with_name(\"airport_feature_group\")\n",
    "    .with_entity_id(entity.id)\n",
    "    .with_compartment_id(compartment_id)\n",
    "    .with_schema_details_from_dataframe(airports_df)\n",
    "    .with_expectation_suite(\n",
    "        expectation_suite=expectation_suite_airports,\n",
    "        expectation_type=ExpectationType.LENIENT,\n",
    "     )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3463648b",
   "metadata": {
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_group_airports.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "76f08b90",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_group_airports.materialise(airports_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "113b7667",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_group_airports.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e930bf58",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<a id=\"create_feature_group_airlines\"></a>\n",
    "##### Airlines Feature Group\n",
    "\n",
    "Create feature group for airlines\n",
    "\n",
    "<div>\n",
    "    <img src=\"https://objectstorage.us-ashburn-1.oraclecloud.com/p/hh2NOgFJbVSg4amcLM3G3hkTuHyBD-8aE_iCsuZKEvIav1Wlld-3zfCawG4ycQGN/n/ociodscdev/b/oci-feature-store/o/beta/_images/feature_groups.gif\" width=\"700\" height=\"350\" />\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78c0a897",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "142fe0d7",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "expectation_suite_airlines = ExpectationSuite(\n",
    "    expectation_suite_name=\"test_airlines_df\"\n",
    ")\n",
    "expectation_suite_airlines.add_expectation(\n",
    "    ExpectationConfiguration(\n",
    "        expectation_type=\"expect_column_values_to_not_be_null\",\n",
    "        kwargs={\"column\": \"IATA_CODE\"},\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f97a23ae",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_group_airlines = (\n",
    "    FeatureGroup()\n",
    "    .with_feature_store_id(feature_store.id)\n",
    "    .with_primary_keys([\"IATA_CODE\"])\n",
    "    .with_name(\"airlines_feature_group\")\n",
    "    .with_entity_id(entity.id)\n",
    "    .with_compartment_id(compartment_id)\n",
    "    .with_schema_details_from_dataframe(airlines_df)\n",
    "    .with_expectation_suite(\n",
    "        expectation_suite=expectation_suite_airlines,\n",
    "        expectation_type=ExpectationType.STRICT,\n",
    "     )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "be296c13",
   "metadata": {
    "jupyter": {
     "outputs_hidden": false
    },
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_group_airlines.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9fb43c77",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_group_airlines.materialise(airlines_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "84d8d0e5",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_group_airlines.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "819eb96a",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<a id=\"explore_featuregroup\"></a>\n",
    "### 3.3. Explore feature groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "073619d5",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_group_flights.get_features_df()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "739b6a03",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_group_airports.get_features_df()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9b23b75e",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_group_airlines.get_features_df()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b1c03f89",
   "metadata": {},
   "source": [
    "You can retrieve feature data in a DataFrame, that can either be used to train models."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "22e37984",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_group_flights.select().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "edff19c5",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_group_airports.select().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2ad430c9",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_group_airlines.select().show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5492e552",
   "metadata": {},
   "source": [
    "You can call the `get_statistics()` method of the feature group to fetch statistics for a specific ingestion job.You can use `to_pandas()` or `to_json()` to view the statistics.\n",
    "You can visualize feature statistics with `to_viz()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fbc8b079",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_group_airlines.get_statistics().to_pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e01774b5",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_group_flights.get_statistics().to_pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d855e164",
   "metadata": {},
   "outputs": [],
   "source": [
    "feature_group_airlines.get_statistics().to_viz()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb91ecc0",
   "metadata": {},
   "source": [
    "You can call the `get_validation_output()` method of the FeatureGroup instance to fetch validation results for a specific ingestion job."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "72c4f95e",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_group_airlines.get_validation_output().to_pandas()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5d8910e9",
   "metadata": {},
   "outputs": [],
   "source": [
    "feature_group_airlines.get_validation_output().to_summary()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e4b0482",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<a id=\"select_subset_featuregroup\"></a>\n",
    "### 3.4. Select subset of features"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b01d641",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_group_airlines.select(['IATA_CODE']).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5185396",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<a id=\"filter_featuregroup\"></a>\n",
    "### 3.5. Filter feature groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "711f8520",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_group_airlines.filter(feature_group_airlines.IATA_CODE == \"EV\").show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93cec82e",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<a id=\"join_featuregroup\"></a>\n",
    "### 3.6. Apply joins on feature groups\n",
    "As in Pandas, if the feature has the same name on both feature groups, then you can use the `on=[]` paramter. If they have different names, then you can use the `left_on=[]` and `right_on=[]` paramters:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "35354d3b",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "from ads.feature_store.common.enums import JoinType\n",
    "\n",
    "query = (\n",
    "    feature_group_flights.select()\n",
    "    .join(feature_group_airlines.select(), left_on=['ORIGIN_AIRPORT'], right_on=['IATA_CODE'], join_type=JoinType.LEFT)\n",
    "    .join(feature_group_airports.select(), left_on=['AIRLINE'], right_on=['IATA_CODE'], join_type=JoinType.LEFT)\n",
    ")\n",
    "query.show(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b0816473",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "query.to_string()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0582b55d",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<a id=\"create_dataset\"></a>\n",
    "### 3.7. Create dataset from multiple or one feature group\n",
    "A dataset is a collection of feature snapshots that are joined together to either train a model or perform model inference.\n",
    "\n",
    "<div>\n",
    "    <img src=\"https://objectstorage.us-ashburn-1.oraclecloud.com/p/hh2NOgFJbVSg4amcLM3G3hkTuHyBD-8aE_iCsuZKEvIav1Wlld-3zfCawG4ycQGN/n/ociodscdev/b/oci-feature-store/o/beta/_images/dataset.gif\" width=\"700\" height=\"350\" />\n",
    "</div>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c4badd9d",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "dataset = (\n",
    "    Dataset()\n",
    "    .with_description(\"Combined dataset for flights\")\n",
    "    .with_compartment_id(compartment_id)\n",
    "    .with_name(\"flights_dataset\")\n",
    "    .with_entity_id(entity.id)\n",
    "    .with_feature_store_id(feature_store.id)\n",
    "    .with_query(query.to_string())\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1f6a637b",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<a id=\"create_dataset\"></a>\n",
    "##### Create Dataset\n",
    "\n",
    "Call the ```.create()``` method of the Dataset instance to create a dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d1ead88d",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "dataset.create()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "23b1fc86",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "dataset.materialise()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36c45a94",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "#### Interoperability with model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b21d6fea",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "model_details = ModelDetails().with_items([\"ocid1.modelcatalog...\"])\n",
    "dataset.add_models(model_details)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98bc412a",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<a id=\"visualise_lineage\"></a>\n",
    "#### Visualise lineage\n",
    "\n",
    "Use the ```.show()``` method on the Dataset instance to visualize the lineage of the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "618700fe",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "dataset.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1e3af28f",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "dataset.profile().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bab39bb4",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "dataset.as_of(version_number=0).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "121bec25",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<a id=\"sql_query\"></a>\n",
    "### 3.8.  Freeform SQL query\n",
    "Feature store provides a way to query feature store using free flow query. User need to mention `entity id` as the database name and `feature group name` as the table name to query feature store. This functionality can be useful if you need to express more complex queries for your use case"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9c77f632",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "entity_id = entity.id\n",
    "\n",
    "sql = (f\"SELECT flights_feature_group.*, airport_feature_group.IATA_CODE \"\n",
    "       f\"FROM `{entity_id}`.flights_feature_group flights_feature_group \"\n",
    "       f\"LEFT JOIN `{entity_id}`.airport_feature_group airport_feature_group \"\n",
    "       f\"ON flights_feature_group.ORIGIN_AIRPORT=airport_feature_group.IATA_CODE\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "06b49d30",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_store.sql(sql).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bba2f30e",
   "metadata": {},
   "source": [
    "<a id=\"featurestore_yaml\"></a>\n",
    "### 3.9. Feature store Entities using YAML\n",
    "In an ADS feature store module, you can either use the Python programmatic interface or YAML to define feature store entities. Below section describes how to create feature store entities using YAML as an interface."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "61df41e1",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "feature_store_yaml = f\"\"\"\n",
    "apiVersion: v1\n",
    "kind: featureStore\n",
    "spec:\n",
    "  displayName: Flights feature store\n",
    "  compartmentId: \"{compartment_id}\"\n",
    "  offlineConfig:\n",
    "    metastoreId: \"{metastore_id}\"\n",
    "\n",
    "  entity: &flights_entity\n",
    "    - kind: entity\n",
    "      spec:\n",
    "        name: Flights\n",
    "\n",
    "  featureGroup:\n",
    "    - kind: featureGroup\n",
    "      spec:\n",
    "        entity: *flights_entity\n",
    "        name: flights_feature_group\n",
    "        primaryKeys:\n",
    "          - IATA_CODE\n",
    "        inputFeatureDetails:\n",
    "          - featureType: STRING\n",
    "            name: IATA_CODE\n",
    "            orderNumber: 1\n",
    "          - featureType: STRING\n",
    "            name: AIRPORT\n",
    "            orderNumber: 2\n",
    "          - featureType: STRING\n",
    "            name: CITY\n",
    "            orderNumber: 3\n",
    "          - featureType: STRING\n",
    "            name: STATE\n",
    "            orderNumber: 4\n",
    "          - featureType: STRING\n",
    "            name: COUNTRY\n",
    "            orderNumber: 5\n",
    "          - featureType: FLOAT\n",
    "            name: LATITUDE\n",
    "            orderNumber: 6\n",
    "          - featureType: FLOAT\n",
    "            name: LONGITUDE\n",
    "            orderNumber: 7\n",
    "    - kind: featureGroup\n",
    "      spec:\n",
    "        entity: *flights_entity\n",
    "        name: airlines_feature_group\n",
    "        primaryKeys:\n",
    "          - IATA_CODE\n",
    "        inputFeatureDetails:\n",
    "          - featureType: STRING\n",
    "            name: IATA_CODE\n",
    "            orderNumber: 1\n",
    "          - featureType: STRING\n",
    "            name: AIRPORT\n",
    "            orderNumber: 2\n",
    "          - featureType: STRING\n",
    "            name: CITY\n",
    "            orderNumber: 3\n",
    "          - featureType: STRING\n",
    "            name: STATE\n",
    "            orderNumber: 4\n",
    "          - featureType: STRING\n",
    "            name: COUNTRY\n",
    "            orderNumber: 5\n",
    "          - featureType: FLOAT\n",
    "            name: LATITUDE\n",
    "            orderNumber: 6\n",
    "          - featureType: FLOAT\n",
    "            name: LONGITUDE\n",
    "            orderNumber: 7\n",
    "\n",
    "    - kind: featureGroup\n",
    "      spec:\n",
    "        entity: *flights_entity\n",
    "        name: airport_feature_group\n",
    "        primaryKeys:\n",
    "          - IATA_CODE\n",
    "        inputFeatureDetails:\n",
    "          - featureType: STRING\n",
    "            name: IATA_CODE\n",
    "            orderNumber: 1\n",
    "          - featureType: STRING\n",
    "            name: AIRLINE\n",
    "            orderNumber: 2\n",
    "  dataset:\n",
    "    - kind: dataset\n",
    "      spec:\n",
    "        name: flights_dataset\n",
    "        entity: *flights_entity\n",
    "        description: \"Dataset for flights\"\n",
    "        query: 'SELECT flight.IATA_CODE, flight.AIRPORT FROM flights_feature_group flight'\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a959f983",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "registrar = FeatureStoreRegistrar.from_yaml(yaml_string=feature_store_yaml)\n",
    "registrar.create()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a2a61514",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "<a id='ref'></a>\n",
    "# 4. References\n",
    "\n",
    "- [Feature Store Documentation](https://feature-store-accelerated-data-science.readthedocs.io/en/latest/overview.html)\n",
    "- [ADS Library Documentation](https://accelerated-data-science.readthedocs.io/en/latest/index.html)\n",
    "- [Data Science YouTube Videos](https://www.youtube.com/playlist?list=PLKCk3OyNwIzv6CWMhvqSB_8MLJIZdO80L)\n",
    "- [OCI Data Science Documentation](https://docs.cloud.oracle.com/en-us/iaas/data-science/using/data-science.htm)\n",
    "- [Oracle Data & AI Blog](https://blogs.oracle.com/datascience/)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c4c00ed",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}