notebook_examples/load_data-object_storage-hive-autonomous-database.ipynb

{
 "cells": [
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "@notebook{load_data-object_storage-hive-autonomous-database.ipynb,\n",
    "    title: Loading Data With Pandas & Dask,\n",
    "    summary: Load data from sources including ADW, Object Storage, and Hive in formats like parquet, csv etc,\n",
    "    developed_on: generalml_p38_cpu_v1,\n",
    "    keywords: loading data, autonomous database, adw, hive, pandas, dask, object storage\n",
    "    license: Universal Permissive License v 1.0\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Upgrade Oracle ADS to pick up latest features and maintain compatibility with Oracle Cloud Infrastructure.\n",
    "\n",
    "!pip install -U oracle-ads"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Oracle Data Science service sample notebook.\n",
    "\n",
    "Copyright (c) 2020, 2022 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\">Loading Data from Different Sources Using Pandas and Dask</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"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Compatible conda pack: [General Machine Learning](https://docs.oracle.com/en-us/iaas/data-science/using/conda-generalml-fam.htm) for CPU on Python 3.8 (version 1.0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import ads\n",
    "import logging\n",
    "import numpy as np\n",
    "import os\n",
    "import pandas as pd\n",
    "import shutil\n",
    "import tempfile\n",
    "import warnings\n",
    "from ads.dataset.factory import DatasetFactory\n",
    "from os import path\n",
    "\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "logging.basicConfig(format=\"%(levelname)s:%(message)s\", level=logging.INFO)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='src'></a>\n",
    "## Loading Datasets From Various Sources\n",
    "\n",
    "Loading data into ADS can be done in several different ways. Data can load from a local, network file system, Hadoop Distributed File System (HDFS), Oracle Object Storage, Amazon S3, Google Cloud Service, Azure Blob, Oracle Database, ADW, elastic search instance, NoSQL DB instance, Mongodb and many more sources. This notebook demonstrates how to do this for some of the more common data sources. However, the approach can be generalized to the other data sources.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='Object Storage'></a>\n",
    "### Oracle Cloud Infrastructure Object Storage\n",
    "\n",
    "Use `pandas` to load data from the object storage."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bucket_name = \"hosted-ds-datasets\"\n",
    "namespace = \"bigdatadatasciencelarge\"\n",
    "\n",
    "\n",
    "file_name = \"titanic/titanic.csv\"\n",
    "df = pd.read_csv(\n",
    "    f\"oci://{bucket_name}@{namespace}/{file_name}\",\n",
    "    storage_options=ads.common.auth.default_signer(),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create `ads.dataset.dataset.ADSDataset` object from pandas dataframe to visualize and analyze the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ds = DatasetFactory.from_dataframe(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `ds` variable is an object from the class `ads.dataset.dataset.ADSDataset`. Objects of this class have a method `show_in_notebook` that provides a wealth of exploratory data analysis (EDA) information. It displays summary statistics, correlations, visualizations, and warnings about the condition of the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "ds.show_in_notebook()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Loading files from a folder using glob patterns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Sometimes you have have a folder full of CSVs or parquet files. Read efficiently from the object storage path using [`dask`](https://www.dask.org/)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import dask.dataframe as dd\n",
    "\n",
    "bucket_name = \"hosted-ds-datasets\"\n",
    "namespace = \"bigdatadatasciencelarge\"\n",
    "\n",
    "\n",
    "ddf = dd.read_parquet(\n",
    "    f\"oci://{bucket_name}@{namespace}/nyc_tlc/2017/\",\n",
    "    storage_options=ads.common.auth.default_signer(),\n",
    "    engine=\"pyarrow\",\n",
    ")\n",
    "\n",
    "df = ddf.compute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='adb'></a>\n",
    "### Oracle Autonomous Database\n",
    "\n",
    "`oracle-ads` provides a drop-in replacement for `pandas.read_sql` and `pandas.to_sql` to read data from Oracle database, Big Data Service Hive and MySQL. To learn more read the [user gide](https://accelerated-data-science.readthedocs.io/en/latest/user_guide/loading_data/connect.html#oracle-database)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# If you are using Wallet file, provide the zip file path for `wallet_location`\n",
    "connection_parameters = {\n",
    "    \"user_name\": \"<username>\",\n",
    "    \"password\": \"<password>\",\n",
    "    \"service_name\": \"<service_name_{high|med|low}>\",\n",
    "    \"wallet_location\": \"/full/path/to/my_wallet.zip\",\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import ads\n",
    "\n",
    "df = pd.DataFrame.ads.read_sql(\n",
    "    \"SELECT * FROM SH.SALES\",\n",
    "    connection_parameters=connection_parameters,\n",
    ")\n",
    "\n",
    "df = pd.DataFrame.ads.read_sql(\n",
    "    \"\"\"\n",
    "    SELECT\n",
    "    *\n",
    "    FROM\n",
    "    SH.SALES\n",
    "    \"\"\",\n",
    "    connection_parameters=connection_parameters,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id='adb'></a>\n",
    "### Big Data Service Hive\n",
    "\n",
    "Learn more about different options for reading and writing data from Hive [here](https://accelerated-data-science.readthedocs.io/en/latest/user_guide/loading_data/connect.html#bds-hive)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection_parameters = {\n",
    "    \"host\": \"<hive hostname>\",\n",
    "    \"port\": \"<hive port number>\",\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection_parameters = {\n",
    "    \"host\": \"<database hostname>\",\n",
    "    \"port\": \"<database port number>\",\n",
    "}\n",
    "import pandas as pd\n",
    "import ads\n",
    "\n",
    "# simple read of a SQL query into a dataframe with no bind variables\n",
    "df = pd.DataFrame.ads.read_sql(\n",
    "    \"SELECT * FROM EMPLOYEE\", connection_parameters=connection_parameters, engine=\"hive\"\n",
    ")\n",
    "\n",
    "# read of a SQL query into a dataframe with a bind variable. Use bind variables\n",
    "# rather than string substitution to avoid the SQL injection attack vector.\n",
    "df = pd.DataFrame.ads.read_sql(\n",
    "    \"\"\"\n",
    "    SELECT\n",
    "    *\n",
    "    FROM\n",
    "    EMPLOYEE\n",
    "    WHERE\n",
    "        `emp_no` <= ?\n",
    "    \"\"\",\n",
    "    bind_variables=(1000,),\n",
    "    connection_parameters=connection_parameters,\n",
    "    engine=\"hive\",\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<a id=\"reference\"></a>\n",
    "# References\n",
    "\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/)\n",
    "- [scikit-learn](https://scikit-learn.org/stable/)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.9.9 (main, Nov 21 2021, 03:23:42) \n[Clang 13.0.0 (clang-1300.0.29.3)]"
  },
  "vscode": {
   "interpreter": {
    "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}