05_glow_vcf_deltatable_germline_databricks.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "1cd70f49-7618-438c-bdd9-8e1995a5fb06",
     "showTitle": false,
     "title": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# import pyspark\n",
    "# import glow\n",
    "from delta import *\n",
    "from pyspark.sql.functions import explode, col, size"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "c48c4c0d-f212-47a0-bb0a-d29eb1429c86",
     "showTitle": false,
     "title": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "#  builder = pyspark.sql.SparkSession.builder.appName(\"GlowDeltalakeETL\") \\\n",
    "#     .config(\"spark.hadoop.io.compression.codecs\", \"io.projectglow.sql.util.BGZFCodec\") \\\n",
    "#     .config(\"spark.sql.extensions\", \"io.delta.sql.DeltaSparkSessionExtension\") \\\n",
    "#     .config(\"spark.sql.catalog.spark_catalog\", \"org.apache.spark.sql.delta.catalog.DeltaCatalog\")\n",
    "\n",
    "# extra_packages = [\n",
    "#     \"io.projectglow:glow-spark3_2.12:1.2.1\",\n",
    "# ]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "0b53e6af-f8d3-46f4-bc90-64f9c5c7439e",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# spark = configure_spark_with_delta_pip(builder, extra_packages=extra_packages).getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "ebac74d2-b891-4813-8e1a-0a9c8e0bf939",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# spark = glow.register(spark)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "c57cf29f-b288-4f30-9331-16aaad42305b",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "389de7cc-eda9-48e9-808e-3870162a2bae",
     "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": "fa6df714-764b-4a72-89b6-f5c89bf8e6ba",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "# ETL Germline VCF and Save as Deltatable\n",
    "\n",
    "* NOTE: This is repetition of previous Somatic step."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "97e3d410-501f-4a35-8ed3-7fa4e8c2207c",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "bcbio_germline_src = \"dbfs:/tmp/dannywong/na12878-na24385-germline-hg38-truth.vcf.gz\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "175b5b17-1749-47bc-ac38-551da2c0b218",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "bcbio_germline_df = spark.read.format(\"vcf\").load(bcbio_germline_src)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "1d3d7c42-c8af-4ea0-b78e-884a43cc7436",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "bcbio_germline_df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "68cb23f7-bc36-4ad7-9287-add437ba49e9",
     "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": "c45f7ef7-473e-4d3e-88fd-d7da96468b78",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## ETL ON NESTED GENOTYPES COLUMN"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "96c97a4d-6683-4e77-b77b-c850034c9e0b",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "bcbio_germline_df_exploded = bcbio_germline_df.withColumn(\"genotypes\", explode(\"genotypes\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "039aade3-d2c6-49f1-b633-dca39a31dcbb",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "bcbio_germline_df_exploded.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "b1ed6de4-4718-4448-976f-87272eec81d2",
     "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": "53101d76-2e53-46da-bb81-b27581279e7f",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## FLATTEN NESTED GENOTYPE FIELDS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "9563fdf0-528d-4b0a-bb56-707016f8bcb8",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "def flatten_struct_fields(df_):\n",
    "    \"\"\"\n",
    "    REF https://github.com/microsoft/genomicsnotebook\n",
    "    :param df_:\n",
    "    :return:\n",
    "    \"\"\"\n",
    "    flat_cols = [c[0] for c in df_.dtypes if c[1][:6] != 'struct']\n",
    "    nested_cols = [c[0] for c in df_.dtypes if c[1][:6] =='struct']\n",
    "    flat_df = df_.select(flat_cols + [col(nc+'.'+c).alias(nc+'_'+c) for nc in nested_cols for c in df_.select(nc+'.*').columns])\n",
    "    return flat_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "7364811d-de6a-4f83-920d-fce6b1500181",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "bcbio_germline_df_exploded_flatten = flatten_struct_fields(bcbio_germline_df_exploded)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "01c17848-3a70-4bc2-806b-66cc6fae3443",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "bcbio_germline_df_exploded_flatten.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "ef193346-2375-4012-99bf-f90384487696",
     "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": "1b62b8b9-1579-4113-a7df-af50e0b95372",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "## QA TRANSFORMED DATAFRAME"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "d4dd00a3-83fa-40b5-82c9-2ef0ac36354b",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "bcbio_germline_df_exploded_flatten.select(\"genotypes_sampleId\").distinct().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "f038633d-4007-47fc-aa17-d34456cd13c5",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "bcbio_germline_df_exploded_flatten \\\n",
    "    .select(\"contigName\", \"start\", \"end\", \"INFO_AC\", \"genotypes_sampleId\", \"genotypes_calls\", \"genotypes_alleleDepths\") \\\n",
    "    .where(\"genotypes_sampleId = 'NA12878'\") \\\n",
    "    .show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "303cdbea-306e-468c-8d25-c2e4282ec82b",
     "showTitle": false,
     "title": ""
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "bcbio_germline_df_exploded_flatten \\\n",
    "    .cube(\"INFO_AC\") \\\n",
    "    .count() \\\n",
    "    .show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "d785505c-a6bf-4184-8050-e3a03be80f6f",
     "showTitle": false,
     "title": ""
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "bcbio_germline_df_exploded_flatten \\\n",
    "    .cube(\"alternateAlleles\") \\\n",
    "    .count() \\\n",
    "    .show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "60777879-f888-462f-b3b4-c6d0e50a9914",
     "showTitle": false,
     "title": ""
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "n = bcbio_germline_df_exploded_flatten.select(\"genotypes_sampleId\").distinct().count()\n",
    "print(f\"number of samples: {n}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "47ab6b9c-f7cd-4245-9db8-26b89b069d08",
     "showTitle": false,
     "title": ""
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "n = bcbio_germline_df_exploded_flatten.count()\n",
    "print(f\"number of records: {n}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "f8eecdd2-9635-44d1-9a9b-0d9ced40ac64",
     "showTitle": false,
     "title": ""
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "n = bcbio_germline_df_exploded_flatten \\\n",
    "    .where(size(\"alternateAlleles\") > 1) \\\n",
    "    .count()\n",
    "\n",
    "print(f\"number of multiallelic sites: {n}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "8db538f6-8e21-4714-b6fc-355e0081cc54",
     "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": "4aa6dfbf-2e9c-4653-8c9f-5c9f68843d98",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "# Write to Deltatable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "4a83567f-930b-49b5-a107-c0ff49412582",
     "showTitle": false,
     "title": ""
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "germline_table = \"dbfs:/tmp/dannywong/lakehouse/bcbio/germline_table\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "d0848e21-cbaa-427b-8e62-e42703bcd6c1",
     "showTitle": false,
     "title": ""
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "bcbio_germline_df_exploded_flatten.write.format(\"delta\").mode(\"overwrite\").save(germline_table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "31ca6099-3c26-42a8-a5a4-09f0e9b136bb",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "%fs\n",
    "ls dbfs:/tmp/dannywong/lakehouse/bcbio/germline_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "7a1808cd-3091-4359-b49d-4fb72f7d8eea",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "!tree ./lakehouse/bcbio/germline_table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "03186ce6-d293-4f4d-84c6-27253b6d0123",
     "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": "c2d3ecd2-9c87-4a7c-a277-f2470a774564",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "# Stop Spark Session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "80de513d-9370-4f89-b616-e1b23af22b0e",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# spark.stop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "4f91628e-6283-47df-966a-a64da0efc105",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# Continue to next notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "1a32bb61-2035-42de-acd1-9e103966e21a",
     "showTitle": false,
     "title": ""
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "language": "python",
   "notebookMetadata": {
    "mostRecentlyExecutedCommandWithImplicitDF": {
     "commandId": 649139525341161,
     "dataframes": [
      "_sqldf"
     ]
    },
    "pythonIndentUnit": 2
   },
   "notebookName": "05_glow_vcf_deltatable_germline_databricks",
   "notebookOrigID": 649139525341013,
   "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
}