02_glow_vcf_explore_germline_databricks.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "deeb269e-77c7-4d0e-b9ab-38ebe6046b57",
     "showTitle": false,
     "title": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# import pyspark\n",
    "# import glow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "a820047f-50b8-40a4-8dcf-71ec4a6002c1",
     "showTitle": false,
     "title": ""
    },
    "tags": []
   },
   "outputs": [],
   "source": [
    "# builder = pyspark.sql.SparkSession.builder.appName(\"GlowVCFExplore\") \\\n",
    "#     .config(\"spark.jars.packages\", \"io.projectglow:glow-spark3_2.12:1.2.1\") \\\n",
    "#     .config(\"spark.hadoop.io.compression.codecs\", \"io.projectglow.sql.util.BGZFCodec\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "a84ec224-e8ab-490e-a13d-28df701e76a0",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# spark = builder.getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "2ee27c0b-689a-4797-9e05-57b438df6c8e",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# spark = glow.register(spark)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "46538e90-6335-47fc-87aa-df398d0378cd",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "1b9bbd91-3b04-4b01-8b95-003aaa99263d",
     "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": "de81d0c7-d1b0-4a99-bc15-2a81f113cecd",
     "showTitle": false,
     "title": ""
    },
    "tags": []
   },
   "source": [
    "# Explore bcBio Germline VCF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "15812539-94c4-4a74-acf2-bc0f9e99748d",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "# bcbio_germline_src = \"./data/bcbio_giab_somatic/na12878-na24385-germline-hg38-truth.vcf.gz\"\n",
    "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": "6a1c7188-e5ba-4460-9919-4c9413d79b0a",
     "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": "83069e13-e52a-4f9c-9c37-555cb576fc14",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "bcbio_germline_df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "dc0f9961-bc53-4260-9878-bf46f9ec9c73",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "bcbio_germline_df.createOrReplaceTempView(\"bcbio_germline_table\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "d501bd22-e0f1-4730-940a-c03c0301e3b4",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "spark.sql(\"describe bcbio_germline_table\").show(n=1000, truncate=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "6be63480-a1db-4580-84d6-f00d59c6cc6e",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "spark.sql(\"select count(1) as number_of_variants from bcbio_germline_table\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "62435cfb-d214-4fdb-9d4d-3f58eafbd993",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "spark.sql(\"select distinct contigName from bcbio_germline_table order by contigName\").show(46)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "167fa75a-5dc3-43d0-961f-46677c747866",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "spark.sql(\"select contigName, start, end from bcbio_germline_table\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "7f68f48a-b558-47c9-bd7d-53ab67478201",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "spark.sql(\"select contigName, count(end) as num_of_pos from bcbio_germline_table group by contigName order by num_of_pos desc\").show(46)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "84188448-1a8c-4d80-a632-1527fcb690e7",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "spark.sql(\"select referenceAllele, alternateAlleles, array_size(alternateAlleles) from bcbio_germline_table\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "3456b6da-8dfb-4480-bdc7-6bd90d2cf3b5",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "spark.sql(\"select referenceAllele, alternateAlleles, count(*) as num_of_snps \\\n",
    "from bcbio_germline_table \\\n",
    "where \\\n",
    "    char_length(referenceAllele) = 1 and \\\n",
    "    array_size(alternateAlleles) = 1 and \\\n",
    "    char_length(alternateAlleles[0]) = 1 \\\n",
    "    group by referenceAllele, alternateAlleles \\\n",
    "    order by num_of_snps desc\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "ee8c63da-8f35-4079-90cf-1c13c15ef044",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "spark.sql(\"select referenceAllele, alternateAlleles, count(*) as num_of_deletions \\\n",
    "from bcbio_germline_table \\\n",
    "where \\\n",
    "    char_length(referenceAllele) > 1 and \\\n",
    "    array_size(alternateAlleles) = 1 and \\\n",
    "    char_length(alternateAlleles[0]) = 1 \\\n",
    "    group by referenceAllele, alternateAlleles \\\n",
    "    order by num_of_deletions desc\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "c3f53a6d-6311-45f1-bb83-e234a1474405",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "spark.sql(\"select referenceAllele, alternateAlleles, count(*) as num_of_insertions \\\n",
    "from bcbio_germline_table \\\n",
    "where \\\n",
    "    char_length(referenceAllele) = 1 and \\\n",
    "    array_size(alternateAlleles) > 1 \\\n",
    "    group by referenceAllele, alternateAlleles \\\n",
    "    order by num_of_insertions desc\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "d2f21977-ce27-4cad-a4f8-417b61e9bb18",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "spark.sql(\"select count(1) as `A>C` from bcbio_germline_table \\\n",
    "where \\\n",
    "    referenceAllele = 'A' and \\\n",
    "    alternateAlleles[0] = 'C' and \\\n",
    "    array_size(alternateAlleles) = 1\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "bbf84431-d3e7-419f-aa15-529d7425ec9f",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "spark.sql(\"select count(1) as `A>AT` from bcbio_germline_table \\\n",
    "where \\\n",
    "    referenceAllele = 'A' and \\\n",
    "    alternateAlleles[0] = 'AT' and \\\n",
    "    array_size(alternateAlleles) = 2\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "ac88c97b-6d8c-414a-b28b-3ee74b9af01c",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "spark.sql(\"select contigName, start, end, referenceAllele, alternateAlleles, genotypes.sampleId, genotypes.alleleDepths \\\n",
    "from bcbio_germline_table\").show(truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "c4231c41-c665-4bd4-9d58-1fea37849c59",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "spark.sql(\"select contigName, start, end, referenceAllele, alternateAlleles, genotypes.sampleId from bcbio_germline_table \\\n",
    "where contigName = 'chr1' and end = 817186\").show(truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "6fb6fdb3-d9a3-4d9f-aecf-63b99337d154",
     "showTitle": false,
     "title": ""
    }
   },
   "outputs": [],
   "source": [
    "spark.sql(\"select contigName, start, end, referenceAllele, alternateAlleles, genotypes.sampleId[0] from bcbio_germline_table \\\n",
    "where genotypes.sampleId[0] = 'NA12878'\").show(truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "60a5e553-732c-489e-8903-e149f2a62c25",
     "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": "a1c1fc4a-5874-423a-a9e1-5dbaacfc439c",
     "showTitle": false,
     "title": ""
    }
   },
   "source": [
    "# Stop Spark Session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "f590f6eb-f35e-4296-a3aa-f503fc45574e",
     "showTitle": false,
     "title": ""
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "# spark.stop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "e8da53f0-9181-49eb-8718-53eefb7cb1ae",
     "showTitle": false,
     "title": ""
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "# Continue to next notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 0,
   "metadata": {
    "application/vnd.databricks.v1+cell": {
     "cellMetadata": {},
     "inputWidgets": {},
     "nuid": "1d42a498-e1b9-4b98-8969-2e1ea56bdd6c",
     "showTitle": false,
     "title": ""
    },
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "application/vnd.databricks.v1+notebook": {
   "dashboards": [],
   "language": "python",
   "notebookMetadata": {
    "pythonIndentUnit": 2
   },
   "notebookName": "02_glow_vcf_explore_germline_databricks",
   "notebookOrigID": 649139525340907,
   "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
}