06_metadata_deltatable_databricks.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "6e37fdfa-8651-4e9a-83ae-4f6c6e9f9918",
     "showTitle": false,
     "title": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# import pyspark\n",
    "from delta import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "44c50984-6404-467e-8897-c9b33ee3a75e",
     "showTitle": false,
     "title": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# builder = pyspark.sql.SparkSession.builder.appName(\"MetadataDeltalakeETL\") \\\n",
    "#     .config(\"spark.sql.extensions\", \"io.delta.sql.DeltaSparkSessionExtension\") \\\n",
    "#     .config(\"spark.sql.catalog.spark_catalog\", \"org.apache.spark.sql.delta.catalog.DeltaCatalog\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "fee24653-d932-413c-b8e1-a3049cb0cae6",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# spark = configure_spark_with_delta_pip(builder).getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "d0d8e1af-36e8-4d25-bf25-3a50b75b95eb",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "6a68bc19-f9f2-4c8f-ab80-2a5d52016e19",
     "showTitle": false,
     "title": ""
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "# Page Break"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "13ddd6c7-bd66-4950-ac84-cf89b03b298a",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "# Load Metadata and Save as Deltatable\n",
    "\n",
    "* NOTE:\n",
    "    * This `meatadata.tsv` is total make up mock for demonstration.\n",
    "    * To simplify thing, it is prepared in denormalized flat table form."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "cd650c03-cf0e-41df-944b-05757d1cd813",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# meta_src = \"./data/metadata.csv\"\n",
    "\n",
    "meta_src = \"dbfs:/Workspace/Repos/danny.wong@databricks.com/genomic-bigdata-spark/data/metadata.csv\" #not working"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "af8a79d0-eac3-481b-9667-50d9896002fc",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "meta_df = spark.read.csv(meta_src, header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "70148479-12d7-4092-91e7-e567a02aed07",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "meta_df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "9970511f-f378-4194-aa5f-27f2a18eceb7",
     "showTitle": false,
     "title": ""
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "# Page Break"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "6b8ca8ce-65b6-45ff-940e-466e6118be61",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "# Data Warehouse Models\n",
    "\n",
    "> Consider: some ETL or complex transformation have done here!! :)\n",
    "\n",
    "* At this point, we can take one step back and think about leveraging Data Warehouse data models.\n",
    " * Some examples but not limited to:\n",
    "    * Multi-Dimensional data model such as Star schema, Snowflake schema\n",
    "        * For example, wrap around our `somatic_table` as Fact table and, build metadata as surrounding dimension table(s)\n",
    "    * Data Vault data modelling such as arranging in Hub, Satellite, Links concepts (depends on data stages i.e. Bronze, Silver, Gold, Platinum)\n",
    "    * Or, simple flat table, Data Mart or, even some simplified Relation model if that suit for the use case"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "07a1ab0c-a51a-4b26-8326-275502d869e1",
     "showTitle": false,
     "title": ""
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "# Page Break"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "eb625e38-69c8-4930-809d-ac374b857459",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## QA TRANSFORMED DATAFRAME"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "67ea7144-e32d-431d-9b2b-38077b193d9e",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "meta_df.select(\"SampleID\").distinct().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "f2c93fad-0dca-4b98-b5ca-a5291852b218",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "meta_df \\\n",
    "    .select(\"SequenceRunName\", \"Timestamp\", \"SubjectID\", \"LibraryID\", \"SampleID\", \"StudyID\", \"StudyType\", \"Pipeline\", \"DiseaseCode\", \"SNOMED\") \\\n",
    "    .where(\"SampleID = 'NA12878'\") \\\n",
    "    .show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "306c7773-364c-494f-83a2-f2342232dfb8",
     "showTitle": false,
     "title": ""
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "meta_df \\\n",
    "    .dropna() \\\n",
    "    .cube(\"StudyID\") \\\n",
    "    .count() \\\n",
    "    .show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "8e85f222-7b58-42af-bce3-f1dd69b4f35b",
     "showTitle": false,
     "title": ""
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "# Page Break"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "46f38572-6ae0-41c1-8145-f5a0ebe658a8",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "# Write to Deltatable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "139c6b18-32a0-4fd3-b79a-f48a033e35e8",
     "showTitle": false,
     "title": ""
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "metadata_table = \"./lakehouse/bcbio/metadata_table\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "961e5484-04ed-49ed-bf29-5e7cc3b79fbf",
     "showTitle": false,
     "title": ""
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "meta_df.write.format(\"delta\").mode(\"overwrite\").save(metadata_table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "42220f81-d55b-4953-8b35-2252aed3dd08",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "!ls ./lakehouse/bcbio/metadata_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "6dee99e4-0111-459e-b501-d0bea5263653",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# Page Break"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "2babece2-521a-4559-b954-8627129971c0",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "# Summary\n",
    "\n",
    "* In real world scenario, this could involve much more complex structure and/or integration with upstream systems.\n",
    "    * For example, interfacing with systems such as RedCAP, FHIR, or Pathling, ontology and some kind of clinical Phenotype look up server\n",
    "    * And there may be possibility with dynamically looking them up from these systems\n",
    "* Consider: we need to ingest some minimal metadata info and, need to ETL out these minimal Phenotype info from these systems\n",
    "* We may or may not need to ingest these meta information into data warehouse -- which depends on case by case basis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "dd89d25f-9b77-4e18-8848-13dc5ae03dea",
     "showTitle": false,
     "title": ""
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "# Page Break"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "c7763319-a39e-45d7-8eea-42fdd729a98d",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "# Stop Spark Session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "b72b4c0e-d219-4a09-8f27-40e29a25504e",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "spark.stop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "31fa4267-17a4-4658-8db4-fb1b0228082b",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# Continue to next notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "cb313f05-4cc8-4b3a-adb8-19cc83af1e72",
     "showTitle": false,
     "title": ""
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "language": "python",
   "notebookMetadata": {
    "pythonIndentUnit": 2
   },
   "notebookName": "06_metadata_deltatable_databricks",
   "notebookOrigID": 649139525341012,
   "widgets": {}
  },
  "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.10.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}