02_glow_vcf_explore_germline.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 Germline VCF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "bcbio_germline_src = \"./data/bcbio_giab_somatic/na12878-na24385-germline-hg38-truth.vcf.gz\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bcbio_germline_df = spark.read.format(\"vcf\").load(bcbio_germline_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_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_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_germline_df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "bcbio_germline_df.createOrReplaceTempView(\"bcbio_germline_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_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_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_germline_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:19:54\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:19:54\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:19:54\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:19:54\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "[Stage 2:==============>                                            (1 + 3) / 4]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------+\n",
      "|number_of_variants|\n",
      "+------------------+\n",
      "|           2123774|\n",
      "+------------------+\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "spark.sql(\"select count(1) as number_of_variants from bcbio_germline_table\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:20:01\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:20:01\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:20:01\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:20:01\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_germline_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|817185|817186|\n",
      "|      chr1|817340|817341|\n",
      "|      chr1|817888|817889|\n",
      "|      chr1|818801|818802|\n",
      "|      chr1|818811|818812|\n",
      "|      chr1|818953|818954|\n",
      "|      chr1|819122|819123|\n",
      "|      chr1|819583|819584|\n",
      "|      chr1|824319|824320|\n",
      "|      chr1|824456|824457|\n",
      "|      chr1|825531|825532|\n",
      "|      chr1|825766|825767|\n",
      "|      chr1|826576|826577|\n",
      "|      chr1|826892|826893|\n",
      "|      chr1|827208|827209|\n",
      "|      chr1|827211|827212|\n",
      "|      chr1|827220|827221|\n",
      "|      chr1|827251|827252|\n",
      "|      chr1|828013|828014|\n",
      "|      chr1|830724|830725|\n",
      "+----------+------+------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:20:09\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_germline_table\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:20:12\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:20:12\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:20:12\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:20:12\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "[Stage 9:==============>                                            (1 + 3) / 4]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+----------+\n",
      "|contigName|num_of_pos|\n",
      "+----------+----------+\n",
      "|      chr2|    178117|\n",
      "|      chr1|    176212|\n",
      "|      chr3|    159225|\n",
      "|      chr4|    146225|\n",
      "|      chr6|    142792|\n",
      "|      chr5|    132555|\n",
      "|      chr7|    122488|\n",
      "|     chr11|    118991|\n",
      "|     chr10|    114638|\n",
      "|      chr8|    109925|\n",
      "|     chr12|    102157|\n",
      "|      chr9|     97462|\n",
      "|     chr13|     94342|\n",
      "|     chr14|     74504|\n",
      "|     chr15|     65969|\n",
      "|     chr17|     54231|\n",
      "|     chr18|     53338|\n",
      "|     chr20|     45056|\n",
      "|     chr19|     40865|\n",
      "|     chr16|     40249|\n",
      "|     chr21|     31506|\n",
      "|     chr22|     22927|\n",
      "+----------+----------+\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "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": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------+----------------+----------------------------+\n",
      "|referenceAllele|alternateAlleles|array_size(alternateAlleles)|\n",
      "+---------------+----------------+----------------------------+\n",
      "|              G|             [A]|                           1|\n",
      "|              A|             [G]|                           1|\n",
      "|              C|             [G]|                           1|\n",
      "|              A|             [G]|                           1|\n",
      "|              A|             [G]|                           1|\n",
      "|              T|             [C]|                           1|\n",
      "|              G|             [A]|                           1|\n",
      "|              C|             [T]|                           1|\n",
      "|              T|             [C]|                           1|\n",
      "|              T|             [A]|                           1|\n",
      "|              C|             [T]|                           1|\n",
      "|              T|             [C]|                           1|\n",
      "|              A|            [AT]|                           1|\n",
      "|              G|             [A]|                           1|\n",
      "|              G|             [C]|                           1|\n",
      "|              C|             [G]|                           1|\n",
      "|              T|             [C]|                           1|\n",
      "|              T|             [A]|                           1|\n",
      "|              G|             [A]|                           1|\n",
      "|              T|             [A]|                           1|\n",
      "+---------------+----------------+----------------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:20:20\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_germline_table\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:20:25\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:20:25\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:20:25\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:20:25\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",
      "|              T|             [C]|     318495|\n",
      "|              A|             [G]|     317816|\n",
      "|              C|             [T]|     305162|\n",
      "|              G|             [A]|     304371|\n",
      "|              G|             [C]|      78709|\n",
      "|              C|             [G]|      78244|\n",
      "|              A|             [C]|      77564|\n",
      "|              T|             [G]|      77273|\n",
      "|              G|             [T]|      75411|\n",
      "|              C|             [A]|      75199|\n",
      "|              T|             [A]|      63624|\n",
      "|              A|             [T]|      63543|\n",
      "+---------------+----------------+-----------+\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "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": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:20:31\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:20:31\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:20:31\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:20:31\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "[Stage 16:=============================>                            (2 + 2) / 4]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------+----------------+----------------+\n",
      "|referenceAllele|alternateAlleles|num_of_deletions|\n",
      "+---------------+----------------+----------------+\n",
      "|             CT|             [C]|           12202|\n",
      "|             AT|             [A]|           11623|\n",
      "|             CA|             [C]|           11392|\n",
      "|             TA|             [T]|           10079|\n",
      "|             GA|             [G]|            7649|\n",
      "|             GT|             [G]|            6514|\n",
      "|             TG|             [T]|            2879|\n",
      "|             AG|             [A]|            2368|\n",
      "|             AC|             [A]|            2254|\n",
      "|             TC|             [T]|            2042|\n",
      "|            CTT|             [C]|            1698|\n",
      "|             GC|             [G]|            1584|\n",
      "|            CAA|             [C]|            1426|\n",
      "|            CAT|             [C]|            1210|\n",
      "|            ATT|             [A]|             906|\n",
      "|            TAA|             [T]|             897|\n",
      "|            TAC|             [T]|             809|\n",
      "|            CTG|             [C]|             671|\n",
      "|            CAG|             [C]|             660|\n",
      "|             CG|             [C]|             614|\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_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": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:20:39\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:20:39\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:20:39\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:20:39\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]|              905|\n",
      "|              C|       [CT, CTT]|              733|\n",
      "|              A|       [AT, ATT]|              596|\n",
      "|              T|       [TA, TAA]|              504|\n",
      "|              G|       [GA, GAA]|              366|\n",
      "|              C|       [CAA, CA]|              325|\n",
      "|              G|       [GT, GTT]|              324|\n",
      "|              C|       [CTT, CT]|              313|\n",
      "|              A|       [ATT, AT]|              213|\n",
      "|              T|       [TAA, TA]|              164|\n",
      "|              T|    [TAC, TACAC]|              154|\n",
      "|              C|     [CAA, CAAA]|              131|\n",
      "|              G|          [C, T]|              130|\n",
      "|              G|       [GAA, GA]|              123|\n",
      "|              C|          [A, T]|              120|\n",
      "|              C|      [CA, CAAA]|              117|\n",
      "|              C|          [A, G]|              116|\n",
      "|              A|          [G, T]|              114|\n",
      "|              G|       [GTT, GT]|              113|\n",
      "|              G|          [A, T]|              112|\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_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": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:20: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:20: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:20: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:20:47\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",
      "|77564|\n",
      "+-----+\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "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": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:20:58\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:20:58\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:20:58\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:20:58\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",
      "| 745|\n",
      "+----+\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "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": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+------+------+---------------+----------------+---------+------------+\n",
      "|contigName|start |end   |referenceAllele|alternateAlleles|sampleId |alleleDepths|\n",
      "+----------+------+------+---------------+----------------+---------+------------+\n",
      "|chr1      |817185|817186|G              |[A]             |[NA12878]|[[78, 454]] |\n",
      "|chr1      |817340|817341|A              |[G]             |[NA12878]|[[107, 342]]|\n",
      "|chr1      |817888|817889|C              |[G]             |[NA12878]|[[74, 220]] |\n",
      "|chr1      |818801|818802|A              |[G]             |[NA12878]|[[0, 202]]  |\n",
      "|chr1      |818811|818812|A              |[G]             |[NA12878]|[[0, 190]]  |\n",
      "|chr1      |818953|818954|T              |[C]             |[NA12878]|[[0, 246]]  |\n",
      "|chr1      |819122|819123|G              |[A]             |[NA12878]|[[110, 363]]|\n",
      "|chr1      |819583|819584|C              |[T]             |[NA12878]|[[91, 378]] |\n",
      "|chr1      |824319|824320|T              |[C]             |[NA12878]|[[92, 391]] |\n",
      "|chr1      |824456|824457|T              |[A]             |[NA12878]|[[0, 355]]  |\n",
      "|chr1      |825531|825532|C              |[T]             |[NA12878]|[[0, 243]]  |\n",
      "|chr1      |825766|825767|T              |[C]             |[NA12878]|[[0, 244]]  |\n",
      "|chr1      |826576|826577|A              |[AT]            |[NA12878]|[[0, 0]]    |\n",
      "|chr1      |826892|826893|G              |[A]             |[NA12878]|[[60, 224]] |\n",
      "|chr1      |827208|827209|G              |[C]             |[NA12878]|[[0, 259]]  |\n",
      "|chr1      |827211|827212|C              |[G]             |[NA12878]|[[0, 263]]  |\n",
      "|chr1      |827220|827221|T              |[C]             |[NA12878]|[[0, 259]]  |\n",
      "|chr1      |827251|827252|T              |[A]             |[NA12878]|[[0, 257]]  |\n",
      "|chr1      |828013|828014|G              |[A]             |[NA12878]|[[0, 0]]    |\n",
      "|chr1      |830724|830725|T              |[A]             |[NA12878]|[[0, 171]]  |\n",
      "+----------+------+------+---------------+----------------+---------+------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:21:04\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_germline_table\").show(truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:21:08\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:21:10\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:21:10\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:21:10\tAsciiLineReader\tCreating an indexable source for an AsciiFeatureCodec using a stream that is neither a PositionalBufferedStream nor a BlockCompressedInputStream\n",
      "[Stage 30:>                                                         (0 + 3) / 3]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+------+------+---------------+----------------+---------+\n",
      "|contigName|start |end   |referenceAllele|alternateAlleles|sampleId |\n",
      "+----------+------+------+---------------+----------------+---------+\n",
      "|chr1      |817185|817186|G              |[A]             |[NA12878]|\n",
      "+----------+------+------+---------------+----------------+---------+\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "                                                                                \r"
     ]
    }
   ],
   "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": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+------+------+---------------+----------------+---------------------+\n",
      "|contigName|start |end   |referenceAllele|alternateAlleles|genotypes.sampleId[0]|\n",
      "+----------+------+------+---------------+----------------+---------------------+\n",
      "|chr1      |817185|817186|G              |[A]             |NA12878              |\n",
      "|chr1      |817340|817341|A              |[G]             |NA12878              |\n",
      "|chr1      |817888|817889|C              |[G]             |NA12878              |\n",
      "|chr1      |818801|818802|A              |[G]             |NA12878              |\n",
      "|chr1      |818811|818812|A              |[G]             |NA12878              |\n",
      "|chr1      |818953|818954|T              |[C]             |NA12878              |\n",
      "|chr1      |819122|819123|G              |[A]             |NA12878              |\n",
      "|chr1      |819583|819584|C              |[T]             |NA12878              |\n",
      "|chr1      |824319|824320|T              |[C]             |NA12878              |\n",
      "|chr1      |824456|824457|T              |[A]             |NA12878              |\n",
      "|chr1      |825531|825532|C              |[T]             |NA12878              |\n",
      "|chr1      |825766|825767|T              |[C]             |NA12878              |\n",
      "|chr1      |826576|826577|A              |[AT]            |NA12878              |\n",
      "|chr1      |826892|826893|G              |[A]             |NA12878              |\n",
      "|chr1      |827208|827209|G              |[C]             |NA12878              |\n",
      "|chr1      |827211|827212|C              |[G]             |NA12878              |\n",
      "|chr1      |827220|827221|T              |[C]             |NA12878              |\n",
      "|chr1      |827251|827252|T              |[A]             |NA12878              |\n",
      "|chr1      |828013|828014|G              |[A]             |NA12878              |\n",
      "|chr1      |830724|830725|T              |[A]             |NA12878              |\n",
      "+----------+------+------+---------------+----------------+---------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "WARNING\t2022-10-08 02:21:15\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_germline_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
}