01_glow_vcf_explore_somatic.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pyspark\n",
    "import glow"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "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": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "spark = builder.getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "spark = glow.register(spark)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "spark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "# Page Break"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Explore bcBio Somatic VCF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "bcbio_somatic_src = \"./data/bcbio_giab_somatic/na12878-na24385-somatic-hg38-truth.vcf.gz\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bcbio_somatic_df = spark.read.format(\"vcf\").load(bcbio_somatic_src)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- contigName: string (nullable = true)\n",
      " |-- start: long (nullable = true)\n",
      " |-- end: long (nullable = true)\n",
      " |-- names: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- referenceAllele: string (nullable = true)\n",
      " |-- alternateAlleles: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- qual: double (nullable = true)\n",
      " |-- filters: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- splitFromMultiAllelic: boolean (nullable = true)\n",
      " |-- INFO_platformnames: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- INFO_callsetwithotheruniqgenopassing: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- INFO_callsetnames: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- INFO_AC: array (nullable = true)\n",
      " |    |-- element: integer (containsNull = true)\n",
      " |-- INFO_FREQ: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- INFO_varType: string (nullable = true)\n",
      " |-- INFO_DPSum: integer (nullable = true)\n",
      " |-- INFO_datasetsmissingcall: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- INFO_AN: integer (nullable = true)\n",
      " |-- INFO_callsets: integer (nullable = true)\n",
      " |-- INFO_callsetwiththisuniqgenopassing: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- INFO_callable: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- INFO_difficultregion: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- INFO_datasets: integer (nullable = true)\n",
      " |-- INFO_SOMTYPE: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- INFO_platformbias: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- INFO_platforms: integer (nullable = true)\n",
      " |-- INFO_arbitrated: string (nullable = true)\n",
      " |-- INFO_filt: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- INFO_datasetnames: array (nullable = true)\n",
      " |    |-- element: string (containsNull = true)\n",
      " |-- genotypes: array (nullable = true)\n",
      " |    |-- element: struct (containsNull = true)\n",
      " |    |    |-- sampleId: string (nullable = true)\n",
      " |    |    |-- conditionalQuality: integer (nullable = true)\n",
      " |    |    |-- alleleDepths: array (nullable = true)\n",
      " |    |    |    |-- element: integer (containsNull = true)\n",
      " |    |    |-- phased: boolean (nullable = true)\n",
      " |    |    |-- calls: array (nullable = true)\n",
      " |    |    |    |-- element: integer (containsNull = true)\n",
      " |    |    |-- depth: integer (nullable = true)\n",
      " |    |    |-- IGT: string (nullable = true)\n",
      " |    |    |-- IPS: string (nullable = true)\n",
      " |    |    |-- ADALL: array (nullable = true)\n",
      " |    |    |    |-- element: integer (containsNull = true)\n",
      " |    |    |-- PS: integer (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "bcbio_somatic_df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bcbio_somatic_df.createOrReplaceTempView(\"bcbio_somatic_table\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------------+-------+\n",
      "|            col_name|           data_type|comment|\n",
      "+--------------------+--------------------+-------+\n",
      "|          contigName|              string|   null|\n",
      "|               start|              bigint|   null|\n",
      "|                 end|              bigint|   null|\n",
      "|               names|       array<string>|   null|\n",
      "|     referenceAllele|              string|   null|\n",
      "|    alternateAlleles|       array<string>|   null|\n",
      "|                qual|              double|   null|\n",
      "|             filters|       array<string>|   null|\n",
      "|splitFromMultiAll...|             boolean|   null|\n",
      "|  INFO_platformnames|       array<string>|   null|\n",
      "|INFO_callsetwitho...|       array<string>|   null|\n",
      "|   INFO_callsetnames|       array<string>|   null|\n",
      "|             INFO_AC|          array<int>|   null|\n",
      "|           INFO_FREQ|       array<string>|   null|\n",
      "|        INFO_varType|              string|   null|\n",
      "|          INFO_DPSum|                 int|   null|\n",
      "|INFO_datasetsmiss...|       array<string>|   null|\n",
      "|             INFO_AN|                 int|   null|\n",
      "|       INFO_callsets|                 int|   null|\n",
      "|INFO_callsetwitht...|       array<string>|   null|\n",
      "|       INFO_callable|       array<string>|   null|\n",
      "|INFO_difficultregion|       array<string>|   null|\n",
      "|       INFO_datasets|                 int|   null|\n",
      "|        INFO_SOMTYPE|       array<string>|   null|\n",
      "|   INFO_platformbias|       array<string>|   null|\n",
      "|      INFO_platforms|                 int|   null|\n",
      "|     INFO_arbitrated|              string|   null|\n",
      "|           INFO_filt|       array<string>|   null|\n",
      "|   INFO_datasetnames|       array<string>|   null|\n",
      "|           genotypes|array<struct<samp...|   null|\n",
      "+--------------------+--------------------+-------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.sql(\"describe bcbio_somatic_table\").show(n=1000, truncate=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:14:35\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:14:35\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:14:35\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:14:35\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------+\n",
      "|number_of_variants|\n",
      "+------------------+\n",
      "|           1082945|\n",
      "+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.sql(\"select count(1) as number_of_variants from bcbio_somatic_table\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:14:45\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:14:45\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:14:45\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:14:45\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "[Stage 5:==============>                                            (1 + 3) / 4]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+\n",
      "|contigName|\n",
      "+----------+\n",
      "|      chr1|\n",
      "|     chr10|\n",
      "|     chr11|\n",
      "|     chr12|\n",
      "|     chr13|\n",
      "|     chr14|\n",
      "|     chr15|\n",
      "|     chr16|\n",
      "|     chr17|\n",
      "|     chr18|\n",
      "|     chr19|\n",
      "|      chr2|\n",
      "|     chr20|\n",
      "|     chr21|\n",
      "|     chr22|\n",
      "|      chr3|\n",
      "|      chr4|\n",
      "|      chr5|\n",
      "|      chr6|\n",
      "|      chr7|\n",
      "|      chr8|\n",
      "|      chr9|\n",
      "+----------+\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "spark.sql(\"select distinct contigName from bcbio_somatic_table order by contigName\").show(46)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+-------+-------+\n",
      "|contigName|  start|    end|\n",
      "+----------+-------+-------+\n",
      "|      chr1| 852046| 852047|\n",
      "|      chr1| 971789| 971791|\n",
      "|      chr1| 974038| 974039|\n",
      "|      chr1| 975013| 975014|\n",
      "|      chr1|1004110|1004112|\n",
      "|      chr1|1004624|1004625|\n",
      "|      chr1|1005428|1005429|\n",
      "|      chr1|1005903|1005904|\n",
      "|      chr1|1005953|1005954|\n",
      "|      chr1|1006158|1006159|\n",
      "|      chr1|1008306|1008307|\n",
      "|      chr1|1009183|1009184|\n",
      "|      chr1|1013548|1013556|\n",
      "|      chr1|1014227|1014228|\n",
      "|      chr1|1014862|1014863|\n",
      "|      chr1|1015924|1015925|\n",
      "|      chr1|1016183|1016184|\n",
      "|      chr1|1016622|1016623|\n",
      "|      chr1|1017113|1017114|\n",
      "|      chr1|1034779|1034780|\n",
      "+----------+-------+-------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:14:51\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n"
     ]
    }
   ],
   "source": [
    "spark.sql(\"select contigName, start, end from bcbio_somatic_table\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:14:55\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:14:55\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:14:55\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:14:55\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "                                                                                \r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+----------+\n",
      "|contigName|num_of_pos|\n",
      "+----------+----------+\n",
      "|      chr2|     93239|\n",
      "|      chr1|     91664|\n",
      "|      chr3|     83863|\n",
      "|      chr6|     77849|\n",
      "|      chr5|     71727|\n",
      "|      chr4|     67549|\n",
      "|      chr7|     63553|\n",
      "|      chr8|     55448|\n",
      "|     chr10|     54948|\n",
      "|     chr11|     53769|\n",
      "|     chr12|     52404|\n",
      "|      chr9|     50528|\n",
      "|     chr13|     42746|\n",
      "|     chr14|     37538|\n",
      "|     chr15|     30810|\n",
      "|     chr17|     29174|\n",
      "|     chr18|     27555|\n",
      "|     chr20|     26771|\n",
      "|     chr19|     20911|\n",
      "|     chr16|     20594|\n",
      "|     chr21|     16145|\n",
      "|     chr22|     14160|\n",
      "+----------+----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.sql(\"select contigName, count(end) as num_of_pos from bcbio_somatic_table group by contigName order by num_of_pos desc\").show(46)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------+----------------+----------------------------+\n",
      "|referenceAllele|alternateAlleles|array_size(alternateAlleles)|\n",
      "+---------------+----------------+----------------------------+\n",
      "|              C|             [T]|                           1|\n",
      "|             AG|             [A]|                           1|\n",
      "|              C|             [T]|                           1|\n",
      "|              C|             [T]|                           1|\n",
      "|             TG|             [T]|                           1|\n",
      "|              A|             [G]|                           1|\n",
      "|              C|            [CA]|                           1|\n",
      "|              C|             [T]|                           1|\n",
      "|              G|             [A]|                           1|\n",
      "|              C|             [T]|                           1|\n",
      "|              G|             [C]|                           1|\n",
      "|              T|             [C]|                           1|\n",
      "|       GGCCCACA|             [G]|                           1|\n",
      "|              G|             [A]|                           1|\n",
      "|              A|             [C]|                           1|\n",
      "|              A|           [ATT]|                           1|\n",
      "|              A|             [G]|                           1|\n",
      "|              G|             [A]|                           1|\n",
      "|              A|            [AT]|                           1|\n",
      "|              C|             [T]|                           1|\n",
      "+---------------+----------------+----------------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:15:01\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n"
     ]
    }
   ],
   "source": [
    "spark.sql(\"select referenceAllele, alternateAlleles, array_size(alternateAlleles) from bcbio_somatic_table\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:15:05\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:15:05\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:15:05\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:15:05\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "[Stage 13:==============>                                           (1 + 3) / 4]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------+----------------+-----------+\n",
      "|referenceAllele|alternateAlleles|num_of_snps|\n",
      "+---------------+----------------+-----------+\n",
      "|              G|             [A]|     179649|\n",
      "|              C|             [T]|     179274|\n",
      "|              T|             [C]|     145030|\n",
      "|              A|             [G]|     144496|\n",
      "|              G|             [T]|      42193|\n",
      "|              C|             [A]|      41828|\n",
      "|              C|             [G]|      40728|\n",
      "|              G|             [C]|      40494|\n",
      "|              A|             [C]|      36837|\n",
      "|              T|             [G]|      36177|\n",
      "|              T|             [A]|      33016|\n",
      "|              A|             [T]|      32659|\n",
      "+---------------+----------------+-----------+\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "spark.sql(\"select referenceAllele, alternateAlleles, count(*) as num_of_snps \\\n",
    "from bcbio_somatic_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": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:15:11\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:15:11\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:15:11\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:15:11\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "[Stage 16:==============>                                           (1 + 3) / 4]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------+----------------+----------------+\n",
      "|referenceAllele|alternateAlleles|num_of_deletions|\n",
      "+---------------+----------------+----------------+\n",
      "|             AT|             [A]|            5802|\n",
      "|             CT|             [C]|            5631|\n",
      "|             CA|             [C]|            5300|\n",
      "|             TA|             [T]|            5080|\n",
      "|             GA|             [G]|            3949|\n",
      "|             GT|             [G]|            2947|\n",
      "|             TG|             [T]|            1759|\n",
      "|             AG|             [A]|            1563|\n",
      "|             AC|             [A]|            1474|\n",
      "|             TC|             [T]|            1324|\n",
      "|            CAT|             [C]|             939|\n",
      "|            CTT|             [C]|             862|\n",
      "|             GC|             [G]|             861|\n",
      "|            CAA|             [C]|             691|\n",
      "|            CAG|             [C]|             567|\n",
      "|            TAA|             [T]|             521|\n",
      "|            CTG|             [C]|             476|\n",
      "|            TAC|             [T]|             462|\n",
      "|            ACT|             [A]|             372|\n",
      "|             CG|             [C]|             362|\n",
      "+---------------+----------------+----------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "spark.sql(\"select referenceAllele, alternateAlleles, count(*) as num_of_deletions \\\n",
    "from bcbio_somatic_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": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:15:18\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:15:18\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:15:18\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:15:18\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "[Stage 19:=============================>                            (2 + 2) / 4]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------+----------------+-----------------+\n",
      "|referenceAllele|alternateAlleles|num_of_insertions|\n",
      "+---------------+----------------+-----------------+\n",
      "|              C|       [CA, CAA]|               49|\n",
      "|              A|       [AT, ATT]|               47|\n",
      "|              C|       [CT, CTT]|               38|\n",
      "|              T|       [TA, TAA]|               33|\n",
      "|              T|    [TAC, TACAC]|               24|\n",
      "|              A|         [AT, T]|               23|\n",
      "|              T|         [A, TA]|               22|\n",
      "|              G|       [GT, GTT]|               21|\n",
      "|              G|       [GA, GAA]|               20|\n",
      "|              C|          [G, T]|               14|\n",
      "|              C|     [CAA, CAAA]|               13|\n",
      "|              C|      [CA, CAAA]|               13|\n",
      "|              C|          [A, T]|               11|\n",
      "|              G|          [A, C]|               10|\n",
      "|              C|          [A, G]|               10|\n",
      "|              C|     [CTT, CTTT]|               10|\n",
      "|              C|         [CT, T]|               10|\n",
      "|              C|      [CT, CTTT]|                9|\n",
      "|              G|         [GT, T]|                9|\n",
      "|              G|    [GTA, GTATA]|                9|\n",
      "+---------------+----------------+-----------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "spark.sql(\"select referenceAllele, alternateAlleles, count(*) as num_of_insertions \\\n",
    "from bcbio_somatic_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": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:15:30\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:15:30\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:15:30\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:15:30\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "[Stage 22:==============>                                           (1 + 3) / 4]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----+\n",
      "|  A>C|\n",
      "+-----+\n",
      "|36837|\n",
      "+-----+\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "spark.sql(\"select count(1) as `A>C` from bcbio_somatic_table \\\n",
    "where \\\n",
    "    referenceAllele = 'A' and \\\n",
    "    alternateAlleles[0] = 'C' and \\\n",
    "    array_size(alternateAlleles) = 1\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:15:36\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:15:36\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:15:36\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:15:36\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "[Stage 25:==============>                                           (1 + 3) / 4]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+\n",
      "|A>AT|\n",
      "+----+\n",
      "|  84|\n",
      "+----+\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "spark.sql(\"select count(1) as `A>AT` from bcbio_somatic_table \\\n",
    "where \\\n",
    "    referenceAllele = 'A' and \\\n",
    "    alternateAlleles[0] = 'AT' and \\\n",
    "    array_size(alternateAlleles) = 2\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+-------+-------+---------------+----------------+---------+------------+\n",
      "|contigName|start  |end    |referenceAllele|alternateAlleles|sampleId |alleleDepths|\n",
      "+----------+-------+-------+---------------+----------------+---------+------------+\n",
      "|chr1      |852046 |852047 |C              |[T]             |[NA12878]|[[133, 139]]|\n",
      "|chr1      |971789 |971791 |AG             |[A]             |[NA12878]|[[192, 148]]|\n",
      "|chr1      |974038 |974039 |C              |[T]             |[NA12878]|[[143, 129]]|\n",
      "|chr1      |975013 |975014 |C              |[T]             |[NA12878]|[[103, 100]]|\n",
      "|chr1      |1004110|1004112|TG             |[T]             |[NA12878]|[[152, 141]]|\n",
      "|chr1      |1004624|1004625|A              |[G]             |[NA12878]|[[129, 119]]|\n",
      "|chr1      |1005428|1005429|C              |[CA]            |[NA12878]|[[158, 135]]|\n",
      "|chr1      |1005903|1005904|C              |[T]             |[NA12878]|[[131, 121]]|\n",
      "|chr1      |1005953|1005954|G              |[A]             |[NA12878]|[[137, 129]]|\n",
      "|chr1      |1006158|1006159|C              |[T]             |[NA12878]|[[150, 136]]|\n",
      "|chr1      |1008306|1008307|G              |[C]             |[NA12878]|[[39, 32]]  |\n",
      "|chr1      |1009183|1009184|T              |[C]             |[NA12878]|[[171, 158]]|\n",
      "|chr1      |1013548|1013556|GGCCCACA       |[G]             |[NA12878]|[[120, 113]]|\n",
      "|chr1      |1014227|1014228|G              |[A]             |[NA12878]|[[192, 170]]|\n",
      "|chr1      |1014862|1014863|A              |[C]             |[NA12878]|[[151, 145]]|\n",
      "|chr1      |1015924|1015925|A              |[ATT]           |[NA12878]|[[165, 116]]|\n",
      "|chr1      |1016183|1016184|A              |[G]             |[NA12878]|[[150, 141]]|\n",
      "|chr1      |1016622|1016623|G              |[A]             |[NA12878]|[[23, 18]]  |\n",
      "|chr1      |1017113|1017114|A              |[AT]            |[NA12878]|[[134, 129]]|\n",
      "|chr1      |1034779|1034780|C              |[T]             |[NA12878]|[[17, 24]]  |\n",
      "+----------+-------+-------+---------------+----------------+---------+------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:15:42\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n"
     ]
    }
   ],
   "source": [
    "spark.sql(\"select contigName, start, end, referenceAllele, alternateAlleles, genotypes.sampleId, genotypes.alleleDepths \\\n",
    "from bcbio_somatic_table\").show(truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:15:45\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:15:47\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:15:47\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "WARNING\t2022-10-08 02:15:47\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "[Stage 30:===================>                                      (1 + 2) / 3]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+------+------+---------------+----------------+---------+\n",
      "|contigName|start |end   |referenceAllele|alternateAlleles|sampleId |\n",
      "+----------+------+------+---------------+----------------+---------+\n",
      "|chr1      |852046|852047|C              |[T]             |[NA12878]|\n",
      "+----------+------+------+---------------+----------------+---------+\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "spark.sql(\"select contigName, start, end, referenceAllele, alternateAlleles, genotypes.sampleId from bcbio_somatic_table \\\n",
    "where contigName = 'chr1' and end = 852047\").show(truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+-------+-------+---------------+----------------+---------------------+\n",
      "|contigName|start  |end    |referenceAllele|alternateAlleles|genotypes.sampleId[0]|\n",
      "+----------+-------+-------+---------------+----------------+---------------------+\n",
      "|chr1      |852046 |852047 |C              |[T]             |NA12878              |\n",
      "|chr1      |971789 |971791 |AG             |[A]             |NA12878              |\n",
      "|chr1      |974038 |974039 |C              |[T]             |NA12878              |\n",
      "|chr1      |975013 |975014 |C              |[T]             |NA12878              |\n",
      "|chr1      |1004110|1004112|TG             |[T]             |NA12878              |\n",
      "|chr1      |1004624|1004625|A              |[G]             |NA12878              |\n",
      "|chr1      |1005428|1005429|C              |[CA]            |NA12878              |\n",
      "|chr1      |1005903|1005904|C              |[T]             |NA12878              |\n",
      "|chr1      |1005953|1005954|G              |[A]             |NA12878              |\n",
      "|chr1      |1006158|1006159|C              |[T]             |NA12878              |\n",
      "|chr1      |1008306|1008307|G              |[C]             |NA12878              |\n",
      "|chr1      |1009183|1009184|T              |[C]             |NA12878              |\n",
      "|chr1      |1013548|1013556|GGCCCACA       |[G]             |NA12878              |\n",
      "|chr1      |1014227|1014228|G              |[A]             |NA12878              |\n",
      "|chr1      |1014862|1014863|A              |[C]             |NA12878              |\n",
      "|chr1      |1015924|1015925|A              |[ATT]           |NA12878              |\n",
      "|chr1      |1016183|1016184|A              |[G]             |NA12878              |\n",
      "|chr1      |1016622|1016623|G              |[A]             |NA12878              |\n",
      "|chr1      |1017113|1017114|A              |[AT]            |NA12878              |\n",
      "|chr1      |1034779|1034780|C              |[T]             |NA12878              |\n",
      "+----------+-------+-------+---------------+----------------+---------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:15:51\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n"
     ]
    }
   ],
   "source": [
    "spark.sql(\"select contigName, start, end, referenceAllele, alternateAlleles, genotypes.sampleId[0] from bcbio_somatic_table \\\n",
    "where genotypes.sampleId[0] = 'NA12878'\").show(truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "# Page Break"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Stop Spark Session"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "spark.stop()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": [
    "# Continue to next notebook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false,
    "jupyter": {
     "outputs_hidden": false
    }
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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": 4
}