{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b583fcbc-47e7-40ff-8c0e-4049230c1788",
   "metadata": {},
   "source": [
    "## Oracle AI Data Platform v1.0\n",
    "\n",
    "Copyright © 2025, Oracle and/or its affiliates.\n",
    "\n",
    "Licensed under the Universal Permissive License v 1.0 as shown at https://oss.oracle.com/licenses/upl/\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "acb1f028-cba1-4bb1-905e-e597c129a9b7",
   "metadata": {
    "execution": {
     "iopub.status.busy": "2025-03-25T18:25:40.094Z"
    },
    "type": "python"
   },
   "source": [
    "# Connect Using Custom JDBC Driver\n",
    "\n",
    "**Overview**\n",
    "\n",
    "This notebook demonstrates using custom JDBC JAR files added to the compute cluster, this notebooks includes examples for;\n",
    "- SQLLite\n",
    "- Snowflake\n",
    "\n",
    "You can install your own JDBC driver and follow similar steps as below."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7e209c1e-76df-471c-8240-93187d4f7586",
   "metadata": {},
   "source": [
    "# Connect Using Custom JDBC Driver - SQLLite\n",
    "\n",
    " **Prerequisites**\n",
    "\n",
    "1. For this example, we will download a lightweight JDBC JAR file that we can demonstrate extensibility of adding custom JDBC JAR files with minimal dependency. Download the SQLLite JDBC Jar file here - https://github.com/xerial/sqlite-jdbc/releases/download/3.46.1.3/sqlite-jdbc-3.46.1.3.jar\n",
    "2. Install the JAR file in a compute cluster. You will have to then restart the cluster to use the JDBC JAR.\n",
    "\n",
    "**Overview**\n",
    "\n",
    "This notebook demonstrates using a new JDBC JAR file added to the compute cluster. It covers:\n",
    " \n",
    " 1. Create dataframe from a table represented by a SQL query\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8e8ddb66-468c-4eb5-9ee9-b32082f004b1",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "82356f31-7cba-432a-85ed-9bea208105e7",
   "metadata": {
    "execution": {
     "iopub.status.busy": "2025-03-25T19:02:05.267Z"
    },
    "type": "python"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<pre>Reading data from db....\n",
       "+--------------------+--------------------+\n",
       "|                  c1|                  c2|\n",
       "+--------------------+--------------------+\n",
       "|1.000000000000000000|2.000000000000000000|\n",
       "+--------------------+--------------------+\n",
       "\n",
       "</pre>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "JDBC_URL = \"jdbc:sqlite:memory:myDb\"\n",
    "DRIVER = \"org.sqlite.JDBC\"\n",
    "SRC_TABLE = \"(SELECT 1 c1, 2 c2)\"\n",
    "fetch_size = 1000\n",
    " \n",
    "print(\"Reading data from db....\")\n",
    "jdbc_url = \"{}\".format(JDBC_URL)\n",
    " \n",
    "properties = {\n",
    "        \"driver\": \"{}\".format(DRIVER),\n",
    "        \"password\": \"\",\n",
    "        \"user\": \"sa\",\n",
    "        \"fetchsize\": fetch_size\n",
    "  }\n",
    "\n",
    "src_df = spark.read.format(\"jdbc\").options(**properties).option(\"dbtable\",SRC_TABLE).option(\"url\",jdbc_url).load()\n",
    "src_df.show() "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4141a691-17df-4cf4-a791-78e74fe58e53",
   "metadata": {},
   "source": [
    "# Connect Using Custom JDBC Driver - Snowflake\n",
    "\n",
    " **Prerequisites**\n",
    "\n",
    "1. For this example, we will download a Snowflake Spark and JDBC JAR file that we can demonstrate extensibility of adding custom JDBC JAR files with minimal dependency. Download the Spark Jar file here - https://docs.snowflake.com/en/user-guide/spark-connector-install and the JDBC driver from here; https://docs.snowflake.com/en/release-notes/clients-drivers/jdbc-2025\n",
    "2. Install the Snowflake Spark and JDBC JAR files in a compute cluster. You will have to then restart the cluster to use the JDBC JAR. This was tested with;\n",
    "- spark-snowflake_2.12-3.1.1.jar\n",
    "- snowflake-jdbc-3.19.0.jar to cluster.\n",
    "\n",
    "\n",
    "**Overview**\n",
    "\n",
    "This notebook demonstrates using a new JDBC JAR file added to the compute cluster. It covers:\n",
    " \n",
    " 1. Create dataframe from a table represented by a SQL query\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "642d8046-9eb0-4452-9ba7-3fbacce747b0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<pre>\n",
       "+--------------------+--------------------+\n",
       "|                  c1|                  c2|\n",
       "+--------------------+--------------------+\n",
       "|1.000000000000000000|2.000000000000000000|\n",
       "+--------------------+--------------------+\n",
       "\n",
       "</pre>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "#Snowflake properties\n",
    "snowflake_options = {\n",
    " \"sfUrl: \"\",\n",
    " \"sfUser\": \"\",\n",
    " \"sfPassword\": \"\",\n",
    " \"sfDatabase\": \"DATAFLOW\",\n",
    " \"sfSchema\": \"DF_SCHEMA\",\n",
    " \"swarehouse\": \"COMPUTE_WH\"\n",
    "}\n",
    "\n",
    "df = spark.read \\\n",
    "    .format(\"snowflake\") \\\n",
    "    .options(**snowflake_options) \\\n",
    "    .option(\"dbtable\", \"test_1\") \\\n",
    "    .load()\n",
    "\n",
    "df.show(5)"
   ]
  }
 ],
 "metadata": {
  "Last_Active_Cell_Index": 4,
  "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}