1000Genomes.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "6d00f0cb",
   "metadata": {},
   "source": [
    "# Sample Queries on the 1000 Genomes, gnomAD and ClinVar data lake\n",
    "\n",
    "In this notebook, we will demonstrate some sample genomics queries that are typically made by clinical geneticists and researchers on genomics variant data. We will use the parquet/ORC transformed variant data from the 3502 DRAGEN-reanalyzed 1000 Genomes dataset available in 3 different schemas at s3://aws-roda-hcls-datalake/thousandgenomes_dragen/:\n",
    "\n",
    "1. var_partby_samples - Dataset partitioned by sample ID\n",
    "2. var_partby_chrom - Dataset partitioned by chromosome and bucketed by samples\n",
    "3. var_nested - Nested schema consisting of variant sites with sample IDs and genotypes that contain the variant\n",
    "\n",
    "\n",
    "We use the transformed annotations from ClinVar that are available at https://registry.opendata.aws/clinvar/ and the transformed population allele frequency data from gnomAD at to demonstrate how to make queries that use the raw variant data with annotations. \n",
    "\n",
    "Please use the CloudFormation templates for 1000 Genomes DRAGEN, ClinVar and gnomAD available at https://github.com/aws-samples/data-lake-as-code/tree/roda to add these tables to your Glue Data Catalog.\n",
    "\n",
    "We use the annotations from ClinVar that are available at https://registry.opendata.aws/clinvar/ and the population allele frequency data from gnomAD to demonstrate how to make queries that use the raw variant data with annotations. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da1f415b",
   "metadata": {},
   "source": [
    "## Import Dependencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "id": "40a247b1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: PyAthena in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (2.3.0)\n",
      "Requirement already satisfied: tenacity>=4.1.0 in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (from PyAthena) (8.0.0)\n",
      "Requirement already satisfied: boto3>=1.4.4 in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (from PyAthena) (1.18.21)\n",
      "Requirement already satisfied: botocore>=1.5.52 in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (from PyAthena) (1.21.21)\n",
      "Requirement already satisfied: s3transfer<0.6.0,>=0.5.0 in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (from boto3>=1.4.4->PyAthena) (0.5.0)\n",
      "Requirement already satisfied: jmespath<1.0.0,>=0.7.1 in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (from boto3>=1.4.4->PyAthena) (0.10.0)\n",
      "Requirement already satisfied: python-dateutil<3.0.0,>=2.1 in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (from botocore>=1.5.52->PyAthena) (2.8.1)\n",
      "Requirement already satisfied: urllib3<1.27,>=1.25.4 in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (from botocore>=1.5.52->PyAthena) (1.26.6)\n",
      "Requirement already satisfied: six>=1.5 in /home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages (from python-dateutil<3.0.0,>=2.1->botocore>=1.5.52->PyAthena) (1.15.0)\n",
      "\u001b[33mWARNING: You are using pip version 21.1.3; however, version 21.2.4 is available.\n",
      "You should consider upgrading via the '/home/ec2-user/anaconda3/envs/python3/bin/python -m pip install --upgrade pip' command.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "import boto3, os\n",
    "import pandas as pd\n",
    "\n",
    "s3 = boto3.resource('s3')\n",
    "glue = boto3.client('glue')\n",
    "cfn = boto3.client('cloudformation')\n",
    "import sys\n",
    "!{sys.executable} -m pip install PyAthena\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "id": "ef7c9007",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "us-east-1\n"
     ]
    }
   ],
   "source": [
    "session = boto3.session.Session()\n",
    "region = session.region_name\n",
    "print(region)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "id": "ae19a466",
   "metadata": {},
   "outputs": [],
   "source": [
    "databasename=\"\\\"thousandgenomes_dragen_dl_awsroda\\\"\"\n",
    "partitioned_chr = \"\\\"var_partby_chrom\\\"\"\n",
    "partitioned_samples = \"\\\"var_partby_samples\\\"\"\n",
    "nested = \"\\\"var_nested\\\"\"\n",
    "clinvarname=\"\\\"clinvar_summary_variants_dl-awsroda\\\"\"\n",
    "clinvar_summary=\"\\\"variant_summary\\\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da312974",
   "metadata": {},
   "source": [
    "### Connect to Athena"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 196,
   "id": "81fefecb",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyathena import connect\n",
    "from pyathena.pandas.util import as_pandas\n",
    "from pyathena.async_cursor import AsyncCursor, AsyncDictCursor\n",
    "from pyathena.error import NotSupportedError, ProgrammingError\n",
    "from pyathena.model import AthenaQueryExecution\n",
    "from pyathena.result_set import AthenaResultSet\n",
    "\n",
    "conn = connect(s3_staging_dir='s3://athena-query-results-ss',region_name=region) #replace with your own bucket name\n",
    "cursor = conn.cursor()\n",
    "\n",
    "def execute_query_async(query):\n",
    "    query_summary = '''Query execution summary:\n",
    "        DataScanned(MB): {}\n",
    "        ExecutionTime(s): {}\n",
    "        QueuingTime(s): {}'''\n",
    "\n",
    "    df = None\n",
    "    acursor = conn.cursor(AsyncCursor)\n",
    "    query_id, future = acursor.execute(query)\n",
    "    result_set = future.result()\n",
    "    if result_set.state == AthenaQueryExecution.STATE_SUCCEEDED:\n",
    "        print(query_summary.format(result_set.data_scanned_in_bytes/100000, \n",
    "                                   result_set.engine_execution_time_in_millis/1000, \n",
    "                                   result_set.query_queue_time_in_millis/1000))    \n",
    "        rows = result_set.fetchall()\n",
    "        cols = [x[0] for x in result_set.description]\n",
    "        df = pd.DataFrame(rows, columns=cols)\n",
    "        \n",
    "    acursor.close()\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2bb3d31d",
   "metadata": {},
   "source": [
    "#### Let us explore the schema of the tables under the 1000 genomes transformed dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "79e584cc",
   "metadata": {},
   "source": [
    "Here is the flat table schema for the data sorted by sample ID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "id": "3ee113ae",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10 entries, 0 to 9\n",
      "Data columns (total 41 columns):\n",
      " #   Column                         Non-Null Count  Dtype  \n",
      "---  ------                         --------------  -----  \n",
      " 0   variant_id                     10 non-null     object \n",
      " 1   chrom                          10 non-null     object \n",
      " 2   pos                            10 non-null     int64  \n",
      " 3   alleles                        10 non-null     object \n",
      " 4   rsid                           0 non-null      object \n",
      " 5   qual                           10 non-null     float64\n",
      " 6   filters                        10 non-null     object \n",
      " 7   info.ac                        10 non-null     object \n",
      " 8   info.af                        10 non-null     object \n",
      " 9   info.an                        10 non-null     int64  \n",
      " 10  info.db                        10 non-null     bool   \n",
      " 11  info.dp                        10 non-null     int64  \n",
      " 12  info.end                       0 non-null      object \n",
      " 13  info.fs                        10 non-null     float64\n",
      " 14  info.fractioninformativereads  10 non-null     float64\n",
      " 15  info.lod                       0 non-null      object \n",
      " 16  info.mq                        10 non-null     float64\n",
      " 17  info.mqranksum                 0 non-null      object \n",
      " 18  info.qd                        10 non-null     float64\n",
      " 19  info.r2_5p_bias                0 non-null      object \n",
      " 20  info.readposranksum            0 non-null      object \n",
      " 21  info.sor                       10 non-null     float64\n",
      " 22  ad                             10 non-null     object \n",
      " 23  af                             10 non-null     object \n",
      " 24  dp                             10 non-null     int64  \n",
      " 25  fir2                           10 non-null     object \n",
      " 26  f2r1                           10 non-null     object \n",
      " 27  gp                             10 non-null     object \n",
      " 28  gq                             10 non-null     int64  \n",
      " 29  gt.alleles                     10 non-null     object \n",
      " 30  gt.phased                      10 non-null     bool   \n",
      " 31  mb                             10 non-null     object \n",
      " 32  pl                             10 non-null     object \n",
      " 33  pri                            10 non-null     object \n",
      " 34  ps                             2 non-null      float64\n",
      " 35  sb                             10 non-null     object \n",
      " 36  sq                             0 non-null      object \n",
      " 37  sample_id                      10 non-null     object \n",
      " 38  ref                            10 non-null     object \n",
      " 39  alt                            10 non-null     object \n",
      " 40  partition_0                    10 non-null     object \n",
      "dtypes: bool(2), float64(7), int64(5), object(27)\n",
      "memory usage: 3.2+ KB\n"
     ]
    }
   ],
   "source": [
    "cursor.execute(\"SELECT * from \" + databasename + \".\" + partitioned_samples + \"limit 10\")\n",
    "df = as_pandas(cursor)\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d2fd19a5",
   "metadata": {},
   "source": [
    "Here is the schema for the flat table partitioned by chromosome and bucketed by samples. As you can see, the schema is very similar to the one partitioned by samples, except that \"chrom\" is the field that the data is partitioned by"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "id": "5788f0b3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10 entries, 0 to 9\n",
      "Data columns (total 41 columns):\n",
      " #   Column                         Non-Null Count  Dtype  \n",
      "---  ------                         --------------  -----  \n",
      " 0   variant_id                     10 non-null     object \n",
      " 1   pos                            10 non-null     int64  \n",
      " 2   ref                            10 non-null     object \n",
      " 3   alt                            10 non-null     object \n",
      " 4   sample_id                      10 non-null     object \n",
      " 5   alleles                        10 non-null     object \n",
      " 6   rsid                           0 non-null      object \n",
      " 7   qual                           10 non-null     float64\n",
      " 8   filters                        10 non-null     object \n",
      " 9   info.ac                        0 non-null      object \n",
      " 10  info.af                        0 non-null      object \n",
      " 11  info.an                        0 non-null      object \n",
      " 12  info.db                        10 non-null     bool   \n",
      " 13  info.dp                        10 non-null     int64  \n",
      " 14  info.end                       0 non-null      object \n",
      " 15  info.fs                        0 non-null      object \n",
      " 16  info.fractioninformativereads  10 non-null     float64\n",
      " 17  info.lod                       10 non-null     float64\n",
      " 18  info.mq                        10 non-null     float64\n",
      " 19  info.mqranksum                 1 non-null      float64\n",
      " 20  info.qd                        0 non-null      object \n",
      " 21  info.r2_5p_bias                0 non-null      object \n",
      " 22  info.readposranksum            1 non-null      float64\n",
      " 23  info.sor                       0 non-null      object \n",
      " 24  ad                             10 non-null     object \n",
      " 25  af                             10 non-null     object \n",
      " 26  dp                             10 non-null     int64  \n",
      " 27  fir2                           10 non-null     object \n",
      " 28  f2r1                           10 non-null     object \n",
      " 29  gp                             0 non-null      object \n",
      " 30  gq                             0 non-null      object \n",
      " 31  gt.alleles                     10 non-null     object \n",
      " 32  gt.phased                      10 non-null     bool   \n",
      " 33  mb                             10 non-null     object \n",
      " 34  pl                             0 non-null      object \n",
      " 35  pri                            0 non-null      object \n",
      " 36  ps                             8 non-null      float64\n",
      " 37  sb                             10 non-null     object \n",
      " 38  sq                             10 non-null     float64\n",
      " 39  partition_0                    10 non-null     object \n",
      " 40  chrom                          10 non-null     object \n",
      "dtypes: bool(2), float64(8), int64(3), object(28)\n",
      "memory usage: 3.2+ KB\n"
     ]
    }
   ],
   "source": [
    "cursor.execute(\"SELECT * from \" + databasename + \".\" + partitioned_chr + \"limit 10\")\n",
    "df = as_pandas(cursor)\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7abd5930",
   "metadata": {},
   "source": [
    "Here is the nested schema. In this schema, most of the FORMAT and INFO fields are not retained. Each row is a variant site with an array consisting of sample IDs and genotypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "id": "f4faae6f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 10 entries, 0 to 9\n",
      "Data columns (total 6 columns):\n",
      " #   Column      Non-Null Count  Dtype \n",
      "---  ------      --------------  ----- \n",
      " 0   variant_id  10 non-null     object\n",
      " 1   pos         10 non-null     int64 \n",
      " 2   ref         10 non-null     object\n",
      " 3   alt         10 non-null     object\n",
      " 4   samples     10 non-null     object\n",
      " 5   chrom       10 non-null     object\n",
      "dtypes: int64(1), object(5)\n",
      "memory usage: 608.0+ bytes\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>variant_id</th>\n",
       "      <th>pos</th>\n",
       "      <th>ref</th>\n",
       "      <th>alt</th>\n",
       "      <th>samples</th>\n",
       "      <th>chrom</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>chrY:CT:C:3526296</td>\n",
       "      <td>3526296</td>\n",
       "      <td>CT</td>\n",
       "      <td>C</td>\n",
       "      <td>[{id=NA18504, gts=[1]}, {id=HG03812, gts=[1]},...</td>\n",
       "      <td>chrY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>chrY:T:C:3749071</td>\n",
       "      <td>3749071</td>\n",
       "      <td>T</td>\n",
       "      <td>C</td>\n",
       "      <td>[{id=NA18504, gts=[1]}, {id=HG01986, gts=[1]},...</td>\n",
       "      <td>chrY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>chrY:C:G:3992262</td>\n",
       "      <td>3992262</td>\n",
       "      <td>C</td>\n",
       "      <td>G</td>\n",
       "      <td>[{id=NA18504, gts=[1]}, {id=HG01986, gts=[1]},...</td>\n",
       "      <td>chrY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>chrM:T:C:195</td>\n",
       "      <td>195</td>\n",
       "      <td>T</td>\n",
       "      <td>C</td>\n",
       "      <td>[{id=HG01573, gts=[0, 1]}, {id=HG03850, gts=[0...</td>\n",
       "      <td>chrM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>chrM:C:A:16159</td>\n",
       "      <td>16159</td>\n",
       "      <td>C</td>\n",
       "      <td>A</td>\n",
       "      <td>[{id=HG01573, gts=[0, 1]}, {id=HG01928, gts=[0...</td>\n",
       "      <td>chrM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>chrM:C:CA:16257</td>\n",
       "      <td>16257</td>\n",
       "      <td>C</td>\n",
       "      <td>CA</td>\n",
       "      <td>[{id=HG01573, gts=[1, 0]}, {id=HG00844, gts=[0...</td>\n",
       "      <td>chrM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>chrM:G:A:8363</td>\n",
       "      <td>8363</td>\n",
       "      <td>G</td>\n",
       "      <td>A</td>\n",
       "      <td>[{id=HG00149, gts=[0, 1]}, {id=NA18987, gts=[0...</td>\n",
       "      <td>chrM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>chrM:A:G:11818</td>\n",
       "      <td>11818</td>\n",
       "      <td>A</td>\n",
       "      <td>G</td>\n",
       "      <td>[{id=HG03650, gts=[0, 1]}, {id=NA19771, gts=[0...</td>\n",
       "      <td>chrM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>chrM:C:T:16222</td>\n",
       "      <td>16222</td>\n",
       "      <td>C</td>\n",
       "      <td>T</td>\n",
       "      <td>[{id=HG03650, gts=[0, 1]}, {id=HG04094, gts=[0...</td>\n",
       "      <td>chrM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>chrM:A:G:5951</td>\n",
       "      <td>5951</td>\n",
       "      <td>A</td>\n",
       "      <td>G</td>\n",
       "      <td>[{id=HG03850, gts=[0, 1]}, {id=HG01986, gts=[0...</td>\n",
       "      <td>chrM</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          variant_id      pos ref alt  \\\n",
       "0  chrY:CT:C:3526296  3526296  CT   C   \n",
       "1   chrY:T:C:3749071  3749071   T   C   \n",
       "2   chrY:C:G:3992262  3992262   C   G   \n",
       "3       chrM:T:C:195      195   T   C   \n",
       "4     chrM:C:A:16159    16159   C   A   \n",
       "5    chrM:C:CA:16257    16257   C  CA   \n",
       "6      chrM:G:A:8363     8363   G   A   \n",
       "7     chrM:A:G:11818    11818   A   G   \n",
       "8     chrM:C:T:16222    16222   C   T   \n",
       "9      chrM:A:G:5951     5951   A   G   \n",
       "\n",
       "                                             samples chrom  \n",
       "0  [{id=NA18504, gts=[1]}, {id=HG03812, gts=[1]},...  chrY  \n",
       "1  [{id=NA18504, gts=[1]}, {id=HG01986, gts=[1]},...  chrY  \n",
       "2  [{id=NA18504, gts=[1]}, {id=HG01986, gts=[1]},...  chrY  \n",
       "3  [{id=HG01573, gts=[0, 1]}, {id=HG03850, gts=[0...  chrM  \n",
       "4  [{id=HG01573, gts=[0, 1]}, {id=HG01928, gts=[0...  chrM  \n",
       "5  [{id=HG01573, gts=[1, 0]}, {id=HG00844, gts=[0...  chrM  \n",
       "6  [{id=HG00149, gts=[0, 1]}, {id=NA18987, gts=[0...  chrM  \n",
       "7  [{id=HG03650, gts=[0, 1]}, {id=NA19771, gts=[0...  chrM  \n",
       "8  [{id=HG03650, gts=[0, 1]}, {id=HG04094, gts=[0...  chrM  \n",
       "9  [{id=HG03850, gts=[0, 1]}, {id=HG01986, gts=[0...  chrM  "
      ]
     },
     "execution_count": 187,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\"SELECT * from \" + databasename +\".\" + nested + \" limit 10\")\n",
    "df = as_pandas(cursor)\n",
    "df.info()\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "16051205",
   "metadata": {},
   "source": [
    "### Queries on variant data alone\n",
    "Here are some examples of queries on just the variant data with no annotations"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad849f8a",
   "metadata": {},
   "source": [
    "**Example 1:** Query for variants that are on a specific gene BRCA1 (chr17:43044295-43125364) in a specific sample HG02625.\n",
    "This query will run faster and will need to scan only data within the specific sample partition, so we will use the dataset that is partitioned by sample"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "id": "1b5f2e62",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query execution summary:\n",
      "        DataScanned: 47353926\n",
      "        ExecutionTime(s): 8.382\n",
      "        QueuingTime(s): 0.156\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>variant_id</th>\n",
       "      <th>chrom</th>\n",
       "      <th>pos</th>\n",
       "      <th>alleles</th>\n",
       "      <th>rsid</th>\n",
       "      <th>qual</th>\n",
       "      <th>filters</th>\n",
       "      <th>info.ac</th>\n",
       "      <th>info.af</th>\n",
       "      <th>info.an</th>\n",
       "      <th>...</th>\n",
       "      <th>mb</th>\n",
       "      <th>pl</th>\n",
       "      <th>pri</th>\n",
       "      <th>ps</th>\n",
       "      <th>sb</th>\n",
       "      <th>sq</th>\n",
       "      <th>sample_id</th>\n",
       "      <th>ref</th>\n",
       "      <th>alt</th>\n",
       "      <th>partition_0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>chr17:G:A:43044391</td>\n",
       "      <td>chr17</td>\n",
       "      <td>43044391</td>\n",
       "      <td>[G, A]</td>\n",
       "      <td>None</td>\n",
       "      <td>45.73</td>\n",
       "      <td>[]</td>\n",
       "      <td>[1]</td>\n",
       "      <td>[0.5]</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>[12, 17, 9, 10]</td>\n",
       "      <td>[81, 0, 50]</td>\n",
       "      <td>[0.0, 34.77, 37.77]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[13, 16, 13, 6]</td>\n",
       "      <td>None</td>\n",
       "      <td>HG02625</td>\n",
       "      <td>G</td>\n",
       "      <td>A</td>\n",
       "      <td>HG02625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>chr17:CTT:C:43044804</td>\n",
       "      <td>chr17</td>\n",
       "      <td>43044804</td>\n",
       "      <td>[CTT, C, CTTTT]</td>\n",
       "      <td>None</td>\n",
       "      <td>308.73</td>\n",
       "      <td>[]</td>\n",
       "      <td>[1, 1]</td>\n",
       "      <td>[0.5, 0.5]</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>[0, 0, 17, 10]</td>\n",
       "      <td>[317, 263, 52, 391, 0, 54]</td>\n",
       "      <td>[0.0, 4.0, 7.0, 4.0, 8.0, 7.0]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[0, 0, 17, 10]</td>\n",
       "      <td>None</td>\n",
       "      <td>HG02625</td>\n",
       "      <td>CTT</td>\n",
       "      <td>C</td>\n",
       "      <td>HG02625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>chr17:C:A:43045257</td>\n",
       "      <td>chr17</td>\n",
       "      <td>43045257</td>\n",
       "      <td>[C, A]</td>\n",
       "      <td>None</td>\n",
       "      <td>49.94</td>\n",
       "      <td>[]</td>\n",
       "      <td>[1]</td>\n",
       "      <td>[0.5]</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>[9, 10, 11, 7]</td>\n",
       "      <td>[85, 0, 50]</td>\n",
       "      <td>[0.0, 34.77, 37.77]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[12, 7, 8, 10]</td>\n",
       "      <td>None</td>\n",
       "      <td>HG02625</td>\n",
       "      <td>C</td>\n",
       "      <td>A</td>\n",
       "      <td>HG02625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>chr17:A:G:43046604</td>\n",
       "      <td>chr17</td>\n",
       "      <td>43046604</td>\n",
       "      <td>[A, G]</td>\n",
       "      <td>None</td>\n",
       "      <td>48.94</td>\n",
       "      <td>[]</td>\n",
       "      <td>[1]</td>\n",
       "      <td>[0.5]</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>[9, 8, 5, 8]</td>\n",
       "      <td>[84, 0, 50]</td>\n",
       "      <td>[0.0, 34.77, 37.77]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[11, 6, 9, 4]</td>\n",
       "      <td>None</td>\n",
       "      <td>HG02625</td>\n",
       "      <td>A</td>\n",
       "      <td>G</td>\n",
       "      <td>HG02625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>chr17:C:CA:43046757</td>\n",
       "      <td>chr17</td>\n",
       "      <td>43046757</td>\n",
       "      <td>[C, CA]</td>\n",
       "      <td>None</td>\n",
       "      <td>50.00</td>\n",
       "      <td>[]</td>\n",
       "      <td>[1]</td>\n",
       "      <td>[0.5]</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>[8, 5, 9, 6]</td>\n",
       "      <td>[56, 0, 50]</td>\n",
       "      <td>[0.0, 6.0, 9.0]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[7, 6, 10, 5]</td>\n",
       "      <td>None</td>\n",
       "      <td>HG02625</td>\n",
       "      <td>C</td>\n",
       "      <td>CA</td>\n",
       "      <td>HG02625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>chr17:GTT:G:43123349</td>\n",
       "      <td>chr17</td>\n",
       "      <td>43123349</td>\n",
       "      <td>[GTT, G]</td>\n",
       "      <td>None</td>\n",
       "      <td>50.00</td>\n",
       "      <td>[]</td>\n",
       "      <td>[1]</td>\n",
       "      <td>[0.5]</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>[3, 5, 7, 3]</td>\n",
       "      <td>[52, 0, 50]</td>\n",
       "      <td>[0.0, 2.0, 5.0]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[5, 3, 4, 6]</td>\n",
       "      <td>None</td>\n",
       "      <td>HG02625</td>\n",
       "      <td>GTT</td>\n",
       "      <td>G</td>\n",
       "      <td>HG02625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>207</th>\n",
       "      <td>chr17:A:G:43123628</td>\n",
       "      <td>chr17</td>\n",
       "      <td>43123628</td>\n",
       "      <td>[A, G]</td>\n",
       "      <td>None</td>\n",
       "      <td>47.93</td>\n",
       "      <td>[]</td>\n",
       "      <td>[1]</td>\n",
       "      <td>[0.5]</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>[10, 12, 6, 10]</td>\n",
       "      <td>[83, 0, 50]</td>\n",
       "      <td>[0.0, 34.77, 37.77]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[12, 10, 8, 8]</td>\n",
       "      <td>None</td>\n",
       "      <td>HG02625</td>\n",
       "      <td>A</td>\n",
       "      <td>G</td>\n",
       "      <td>HG02625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>208</th>\n",
       "      <td>chr17:A:G:43124230</td>\n",
       "      <td>chr17</td>\n",
       "      <td>43124230</td>\n",
       "      <td>[A, G]</td>\n",
       "      <td>None</td>\n",
       "      <td>50.00</td>\n",
       "      <td>[]</td>\n",
       "      <td>[1]</td>\n",
       "      <td>[0.5]</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>[9, 7, 6, 12]</td>\n",
       "      <td>[85, 0, 50]</td>\n",
       "      <td>[0.0, 34.77, 37.77]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[8, 8, 13, 5]</td>\n",
       "      <td>None</td>\n",
       "      <td>HG02625</td>\n",
       "      <td>A</td>\n",
       "      <td>G</td>\n",
       "      <td>HG02625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>209</th>\n",
       "      <td>chr17:T:C:43124331</td>\n",
       "      <td>chr17</td>\n",
       "      <td>43124331</td>\n",
       "      <td>[T, C]</td>\n",
       "      <td>None</td>\n",
       "      <td>50.00</td>\n",
       "      <td>[]</td>\n",
       "      <td>[1]</td>\n",
       "      <td>[0.5]</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>[6, 5, 6, 6]</td>\n",
       "      <td>[85, 0, 50]</td>\n",
       "      <td>[0.0, 34.77, 37.77]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[10, 1, 8, 4]</td>\n",
       "      <td>None</td>\n",
       "      <td>HG02625</td>\n",
       "      <td>T</td>\n",
       "      <td>C</td>\n",
       "      <td>HG02625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>210</th>\n",
       "      <td>chr17:G:C:43125170</td>\n",
       "      <td>chr17</td>\n",
       "      <td>43125170</td>\n",
       "      <td>[G, C]</td>\n",
       "      <td>None</td>\n",
       "      <td>110.19</td>\n",
       "      <td>[]</td>\n",
       "      <td>[2]</td>\n",
       "      <td>[1.0]</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>[0, 0, 13, 8]</td>\n",
       "      <td>[148, 63, 0]</td>\n",
       "      <td>[0.0, 34.77, 37.77]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[0, 0, 6, 15]</td>\n",
       "      <td>None</td>\n",
       "      <td>HG02625</td>\n",
       "      <td>G</td>\n",
       "      <td>C</td>\n",
       "      <td>HG02625</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>211 rows × 41 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               variant_id  chrom       pos          alleles  rsid    qual  \\\n",
       "0      chr17:G:A:43044391  chr17  43044391           [G, A]  None   45.73   \n",
       "1    chr17:CTT:C:43044804  chr17  43044804  [CTT, C, CTTTT]  None  308.73   \n",
       "2      chr17:C:A:43045257  chr17  43045257           [C, A]  None   49.94   \n",
       "3      chr17:A:G:43046604  chr17  43046604           [A, G]  None   48.94   \n",
       "4     chr17:C:CA:43046757  chr17  43046757          [C, CA]  None   50.00   \n",
       "..                    ...    ...       ...              ...   ...     ...   \n",
       "206  chr17:GTT:G:43123349  chr17  43123349         [GTT, G]  None   50.00   \n",
       "207    chr17:A:G:43123628  chr17  43123628           [A, G]  None   47.93   \n",
       "208    chr17:A:G:43124230  chr17  43124230           [A, G]  None   50.00   \n",
       "209    chr17:T:C:43124331  chr17  43124331           [T, C]  None   50.00   \n",
       "210    chr17:G:C:43125170  chr17  43125170           [G, C]  None  110.19   \n",
       "\n",
       "    filters info.ac     info.af  info.an  ...               mb  \\\n",
       "0        []     [1]       [0.5]        2  ...  [12, 17, 9, 10]   \n",
       "1        []  [1, 1]  [0.5, 0.5]        2  ...   [0, 0, 17, 10]   \n",
       "2        []     [1]       [0.5]        2  ...   [9, 10, 11, 7]   \n",
       "3        []     [1]       [0.5]        2  ...     [9, 8, 5, 8]   \n",
       "4        []     [1]       [0.5]        2  ...     [8, 5, 9, 6]   \n",
       "..      ...     ...         ...      ...  ...              ...   \n",
       "206      []     [1]       [0.5]        2  ...     [3, 5, 7, 3]   \n",
       "207      []     [1]       [0.5]        2  ...  [10, 12, 6, 10]   \n",
       "208      []     [1]       [0.5]        2  ...    [9, 7, 6, 12]   \n",
       "209      []     [1]       [0.5]        2  ...     [6, 5, 6, 6]   \n",
       "210      []     [2]       [1.0]        2  ...    [0, 0, 13, 8]   \n",
       "\n",
       "                             pl                             pri  ps  \\\n",
       "0                   [81, 0, 50]             [0.0, 34.77, 37.77] NaN   \n",
       "1    [317, 263, 52, 391, 0, 54]  [0.0, 4.0, 7.0, 4.0, 8.0, 7.0] NaN   \n",
       "2                   [85, 0, 50]             [0.0, 34.77, 37.77] NaN   \n",
       "3                   [84, 0, 50]             [0.0, 34.77, 37.77] NaN   \n",
       "4                   [56, 0, 50]                 [0.0, 6.0, 9.0] NaN   \n",
       "..                          ...                             ...  ..   \n",
       "206                 [52, 0, 50]                 [0.0, 2.0, 5.0] NaN   \n",
       "207                 [83, 0, 50]             [0.0, 34.77, 37.77] NaN   \n",
       "208                 [85, 0, 50]             [0.0, 34.77, 37.77] NaN   \n",
       "209                 [85, 0, 50]             [0.0, 34.77, 37.77] NaN   \n",
       "210                [148, 63, 0]             [0.0, 34.77, 37.77] NaN   \n",
       "\n",
       "                  sb    sq  sample_id  ref  alt  partition_0  \n",
       "0    [13, 16, 13, 6]  None    HG02625    G    A      HG02625  \n",
       "1     [0, 0, 17, 10]  None    HG02625  CTT    C      HG02625  \n",
       "2     [12, 7, 8, 10]  None    HG02625    C    A      HG02625  \n",
       "3      [11, 6, 9, 4]  None    HG02625    A    G      HG02625  \n",
       "4      [7, 6, 10, 5]  None    HG02625    C   CA      HG02625  \n",
       "..               ...   ...        ...  ...  ...          ...  \n",
       "206     [5, 3, 4, 6]  None    HG02625  GTT    G      HG02625  \n",
       "207   [12, 10, 8, 8]  None    HG02625    A    G      HG02625  \n",
       "208    [8, 8, 13, 5]  None    HG02625    A    G      HG02625  \n",
       "209    [10, 1, 8, 4]  None    HG02625    T    C      HG02625  \n",
       "210    [0, 0, 6, 15]  None    HG02625    G    C      HG02625  \n",
       "\n",
       "[211 rows x 41 columns]"
      ]
     },
     "execution_count": 188,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"SELECT * from {}.{}\n",
    "  WHERE chrom='chr17'\n",
    "  AND pos BETWEEN 43044295 AND 43125364 \n",
    "  AND sample_id = 'HG02625' \"\"\".format(databasename,partitioned_samples)\n",
    "\n",
    "df = execute_query_async(query)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "239af8cd",
   "metadata": {},
   "source": [
    "**Example 2:** Query to find samples that have variants in a specific region, in this case, the BRCA1 gene (chr17:43044295-43125364). \n",
    "This query can be run on the data that is partitioned by chromosome "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 189,
   "id": "d7ebccde",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query execution summary:\n",
      "        DataScanned: 915862188\n",
      "        ExecutionTime(s): 2.376\n",
      "        QueuingTime(s): 0.158\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sample_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NA19983</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NA18968</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>HG03401</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>HG03190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HG02075</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3197</th>\n",
       "      <td>HG01798</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3198</th>\n",
       "      <td>HG00663</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3199</th>\n",
       "      <td>HG03897</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3200</th>\n",
       "      <td>NA20870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3201</th>\n",
       "      <td>HG03125</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3202 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     sample_id\n",
       "0      NA19983\n",
       "1      NA18968\n",
       "2      HG03401\n",
       "3      HG03190\n",
       "4      HG02075\n",
       "...        ...\n",
       "3197   HG01798\n",
       "3198   HG00663\n",
       "3199   HG03897\n",
       "3200   NA20870\n",
       "3201   HG03125\n",
       "\n",
       "[3202 rows x 1 columns]"
      ]
     },
     "execution_count": 189,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"SELECT DISTINCT(sample_id) from {}.{}\n",
    "  where chrom='chr17' \n",
    "  and pos between 43044295 and 43125364 \"\"\".format(databasename,partitioned_chr)\n",
    "\n",
    "df=execute_query_async(query)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d5190a76",
   "metadata": {},
   "source": [
    "**Example 2a:** Query 2 on the nested data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 197,
   "id": "503952b3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query execution summary:\n",
      "        DataScanned(MB): 14821.13335\n",
      "        ExecutionTime(s): 7.328\n",
      "        QueuingTime(s): 0.164\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>HG00096</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>HG00097</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>HG00099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>HG00100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HG00101</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3197</th>\n",
       "      <td>NA21137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3198</th>\n",
       "      <td>NA21141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3199</th>\n",
       "      <td>NA21142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3200</th>\n",
       "      <td>NA21143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3201</th>\n",
       "      <td>NA21144</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3202 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           id\n",
       "0     HG00096\n",
       "1     HG00097\n",
       "2     HG00099\n",
       "3     HG00100\n",
       "4     HG00101\n",
       "...       ...\n",
       "3197  NA21137\n",
       "3198  NA21141\n",
       "3199  NA21142\n",
       "3200  NA21143\n",
       "3201  NA21144\n",
       "\n",
       "[3202 rows x 1 columns]"
      ]
     },
     "execution_count": 197,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"SELECT DISTINCT(sample.id) from (\n",
    "  SELECT samples from {}.{}\n",
    "  where chrom='chr1'\n",
    "  and pos between 9033567 and 9142000\n",
    "  ) as f,\n",
    "  unnest(f.samples) as s(sample)\n",
    "  order by 1\"\"\".format(databasename,nested)\n",
    "df=execute_query_async(query)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d6ea4f2",
   "metadata": {},
   "source": [
    "**Example 3:** Query to find only samples that have homozygous variants in a specific region, in this case, the BRCA1 gene (chr17:43044295-43125364). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "id": "50b6152a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query execution summary:\n",
      "        DataScanned(MB): 10959.8672\n",
      "        ExecutionTime(s): 2.643\n",
      "        QueuingTime(s): 0.305\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sample_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>HG03633</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NA07034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>HG00280</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NA18566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>HG03846</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3179</th>\n",
       "      <td>HG04204</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3180</th>\n",
       "      <td>NA20756</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3181</th>\n",
       "      <td>HG00561</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3182</th>\n",
       "      <td>HG01668</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3183</th>\n",
       "      <td>NA19921</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3184 rows × 1 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     sample_id\n",
       "0      HG03633\n",
       "1      NA07034\n",
       "2      HG00280\n",
       "3      NA18566\n",
       "4      HG03846\n",
       "...        ...\n",
       "3179   HG04204\n",
       "3180   NA20756\n",
       "3181   HG00561\n",
       "3182   HG01668\n",
       "3183   NA19921\n",
       "\n",
       "[3184 rows x 1 columns]"
      ]
     },
     "execution_count": 198,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"SELECT DISTINCT(sample_id) from {}.{}\n",
    "  where chrom='chr17'\n",
    "  and pos between 43044295 and 43125364\n",
    "  and array_join(\"gt.alleles\", '|') in ('0|1')\"\"\".format(databasename,partitioned_chr)\n",
    "\n",
    "df=execute_query_async(query)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a2e2a22d",
   "metadata": {},
   "source": [
    "### Queries joining variant data with ClinVar annotation data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df466fd4",
   "metadata": {},
   "source": [
    "**ClinVar has a number of tables, but the one that we will use here is the summary_variants table. This table has many fields, but we will only be using a subset of them. We also need to create a Variant ID that can be used to join with the raw variant tables, so to make this easier, we will create a \"view\" of the summary_variants table that we can use with subsequent queries**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "id": "593561bd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pyathena.cursor.Cursor at 0x7ffabdc10160>"
      ]
     },
     "execution_count": 199,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\"create or replace view clinvar as \"\n",
    "       \"select concat('chr', chromosome, ':', referenceallelevcf, ':', alternateallelevcf, ':', cast(positionvcf as varchar)) as variant_id, \"\n",
    "       \"chromosome as chrom, \"\n",
    "       \"positionvcf as pos, \"\n",
    "       \"referenceallelevcf as ref, \"\n",
    "       \"alternateallelevcf as alt, \"\n",
    "       \"genesymbol as genename, \" \n",
    "       \"clinicalsignificance as clinicalsignificance, \"\n",
    "       \"numbersubmitters as num_submitters, \"\n",
    "       \"reviewstatus as reviewstatus, \"\n",
    "       \"split(phenotypeids, ',') as phenotypeids, \"\n",
    "       \"split(phenotypelist, ',') as phenotypelist \"\n",
    "       \"from \\\"clinvar_summary_variants_dl-awsroda\\\".\\\"variant_summary\\\" where assembly = 'GRCh38'\"\n",
    "        \"and referenceallelevcf <> 'na' and alternateallelevcf <> 'na'\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9c738f43",
   "metadata": {},
   "source": [
    "**Example 4:** Modify query 3 to find the number of samples that have \"variants of Unknown Significance\" in the BRCA1 gene"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "id": "cefbefa0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query execution summary:\n",
      "        DataScanned(MB): 6687.40946\n",
      "        ExecutionTime(s): 5.725\n",
      "        QueuingTime(s): 0.203\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_col0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1550</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   _col0\n",
       "0   1550"
      ]
     },
     "execution_count": 200,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"SELECT count(DISTINCT(sample.id)) FROM (\n",
    "       SELECT samples FROM {}.{} AS v \n",
    "       JOIN clinvar a \n",
    "       ON v.variant_id = a.variant_id \n",
    "       WHERE a.clinicalsignificance = 'Uncertain significance' \n",
    "       AND v.chrom='chr17' \n",
    "       AND v.pos BETWEEN 43044295 AND 43125364 \n",
    "       ) AS f, \n",
    "       UNNEST(f.samples) AS s(sample) \"\"\".format(databasename, nested)\n",
    "df=execute_query_async(query)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73695372",
   "metadata": {},
   "source": [
    "**Example 4a:** To have a greater degree of confidence in the results, we want to only consider those entries in ClinVar that have more than 1 submission. This query will filter the results from Example 4 to only consider those that have > 1 submitter."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "id": "bf64c5c6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query execution summary:\n",
      "        DataScanned(MB): 6694.43858\n",
      "        ExecutionTime(s): 5.143\n",
      "        QueuingTime(s): 0.151\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_col0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>41</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   _col0\n",
       "0     41"
      ]
     },
     "execution_count": 201,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"SELECT count(DISTINCT(sample.id)) from (\n",
    "       SELECT samples FROM {}.{} as v \n",
    "       JOIN clinvar a\n",
    "       ON v.variant_id = a.variant_id\n",
    "       WHERE a.clinicalsignificance = 'Uncertain significance' \n",
    "       AND v.chrom='chr17' \n",
    "       AND v.pos BETWEEN 43044295 AND 43125364 \n",
    "       AND a.num_submitters > 1 \n",
    "       ) AS f, \n",
    "       UNNEST(f.samples) AS s(sample)  \"\"\".format(databasename, nested)\n",
    "df=execute_query_async(query)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce43a857",
   "metadata": {},
   "source": [
    "**Example 5a:**  Find all variants in this dataset that have a pathogenic variant in the BRCA1 gene (using the gene annotation from ClinVar). Running the same query using the chromosome and position range for the BRCA1 gene is much faster due to the partitioning of the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "id": "4c7ef54e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query execution summary:\n",
      "        DataScanned(MB): 924640.11515\n",
      "        ExecutionTime(s): 79.597\n",
      "        QueuingTime(s): 0.154\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_col0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   _col0\n",
       "0      4"
      ]
     },
     "execution_count": 208,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"SELECT COUNT(DISTINCT sample_id) FROM {}.{} as v \n",
    "           JOIN clinvar AS a  \n",
    "           ON v.variant_id = a.variant_id \n",
    "           WHERE a.genename='BRCA1' AND clinicalsignificance='Pathogenic' \"\"\".format(databasename,partitioned_chr)\n",
    "\n",
    "df=execute_query_async(query)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "def94d01",
   "metadata": {},
   "source": [
    "**Example 5b:** Run Query 5a using the nested dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 209,
   "id": "fda90377",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query execution summary:\n",
      "        DataScanned(MB): 230663.85143\n",
      "        ExecutionTime(s): 35.196\n",
      "        QueuingTime(s): 0.227\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_col0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   _col0\n",
       "0      4"
      ]
     },
     "execution_count": 209,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"SELECT COUNT(DISTINCT sample.id) FROM \n",
    "           (SELECT samples from {}.{} AS v  \n",
    "           JOIN clinvar AS a \n",
    "           ON v.variant_id = a.variant_id \n",
    "           WHERE a.genename='BRCA1' AND clinicalsignificance='Pathogenic') AS f, \n",
    "           UNNEST(f.samples) AS s(sample)\"\"\".format(databasename, nested)\n",
    "\n",
    "df=execute_query_async(query)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "117b2deb",
   "metadata": {},
   "source": [
    "### Queries using 1000 genomes with gnomAD and ClinVar\n",
    "\n",
    "The gnomAD sites data is available at s3://juayu-sampledata/geno_dataset/gnomad/sites/. We will first create a View of the gnomAD table with the fields we are going to be using in this set of queries to simplify things. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "id": "df7c1655",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<pyathena.cursor.Cursor at 0x7ffabdc10160>"
      ]
     },
     "execution_count": 204,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\"create or replace view gnomad as \"\n",
    "       \" select concat(\\\"locus.contig\\\", ':', alleles[1], ':', alleles[2], ':', cast(\\\"locus.position\\\" as varchar)) as variant_id \"\n",
    "       \", \\\"locus.contig\\\" as chrom\"\n",
    "       \", \\\"locus.position\\\" as pos\"\n",
    "       \", alleles[1] as ref\"\n",
    "       \", alleles[2] as alt\"\n",
    "       \", rsid\" \n",
    "       \", filters\"\n",
    "       \", \\\"info.ac\\\" as info_ac\"\n",
    "       \", \\\"info.an\\\" as info_an\"\n",
    "       \", \\\"info.af\\\" as info_af\"\n",
    "       \", \\\"info.popmax\\\" as info_popmax\"\n",
    "       \", partition_0 \"\n",
    "\"from gnomad.sites \" \n",
    "\"where cardinality(filters) = 0\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "682d3435",
   "metadata": {},
   "source": [
    "**gnomAD view**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 205,
   "id": "9e6cb4f5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query execution summary:\n",
      "        DataScanned(MB): 1110.29305\n",
      "        ExecutionTime(s): 2.524\n",
      "        QueuingTime(s): 0.237\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>variant_id</th>\n",
       "      <th>chrom</th>\n",
       "      <th>pos</th>\n",
       "      <th>ref</th>\n",
       "      <th>alt</th>\n",
       "      <th>rsid</th>\n",
       "      <th>filters</th>\n",
       "      <th>info_ac</th>\n",
       "      <th>info_an</th>\n",
       "      <th>info_af</th>\n",
       "      <th>info_popmax</th>\n",
       "      <th>partition_0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>chr18:A:G:856887</td>\n",
       "      <td>chr18</td>\n",
       "      <td>856887</td>\n",
       "      <td>A</td>\n",
       "      <td>G</td>\n",
       "      <td>rs1403391816</td>\n",
       "      <td>[]</td>\n",
       "      <td>[2]</td>\n",
       "      <td>152092</td>\n",
       "      <td>[1.31499E-5]</td>\n",
       "      <td>[nfe]</td>\n",
       "      <td>chr18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>chr18:G:C:856899</td>\n",
       "      <td>chr18</td>\n",
       "      <td>856899</td>\n",
       "      <td>G</td>\n",
       "      <td>C</td>\n",
       "      <td>None</td>\n",
       "      <td>[]</td>\n",
       "      <td>[1]</td>\n",
       "      <td>152130</td>\n",
       "      <td>[6.57333E-6]</td>\n",
       "      <td>[nfe]</td>\n",
       "      <td>chr18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>chr18:G:C:856903</td>\n",
       "      <td>chr18</td>\n",
       "      <td>856903</td>\n",
       "      <td>G</td>\n",
       "      <td>C</td>\n",
       "      <td>rs1398081239</td>\n",
       "      <td>[]</td>\n",
       "      <td>[1]</td>\n",
       "      <td>152098</td>\n",
       "      <td>[6.57471E-6]</td>\n",
       "      <td>[eas]</td>\n",
       "      <td>chr18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>chr18:G:A:856905</td>\n",
       "      <td>chr18</td>\n",
       "      <td>856905</td>\n",
       "      <td>G</td>\n",
       "      <td>A</td>\n",
       "      <td>rs1337701794</td>\n",
       "      <td>[]</td>\n",
       "      <td>[1]</td>\n",
       "      <td>152082</td>\n",
       "      <td>[6.5754E-6]</td>\n",
       "      <td>[eas]</td>\n",
       "      <td>chr18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>chr18:C:T:856909</td>\n",
       "      <td>chr18</td>\n",
       "      <td>856909</td>\n",
       "      <td>C</td>\n",
       "      <td>T</td>\n",
       "      <td>rs1400157635</td>\n",
       "      <td>[]</td>\n",
       "      <td>[3]</td>\n",
       "      <td>152116</td>\n",
       "      <td>[1.97218E-5]</td>\n",
       "      <td>[eas]</td>\n",
       "      <td>chr18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>chr18:C:T:856910</td>\n",
       "      <td>chr18</td>\n",
       "      <td>856910</td>\n",
       "      <td>C</td>\n",
       "      <td>T</td>\n",
       "      <td>None</td>\n",
       "      <td>[]</td>\n",
       "      <td>[1]</td>\n",
       "      <td>152156</td>\n",
       "      <td>[6.5722E-6]</td>\n",
       "      <td>[eas]</td>\n",
       "      <td>chr18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>chr18:A:G:856919</td>\n",
       "      <td>chr18</td>\n",
       "      <td>856919</td>\n",
       "      <td>A</td>\n",
       "      <td>G</td>\n",
       "      <td>None</td>\n",
       "      <td>[]</td>\n",
       "      <td>[1]</td>\n",
       "      <td>152110</td>\n",
       "      <td>[6.57419E-6]</td>\n",
       "      <td>None</td>\n",
       "      <td>chr18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>chr18:A:G:856923</td>\n",
       "      <td>chr18</td>\n",
       "      <td>856923</td>\n",
       "      <td>A</td>\n",
       "      <td>G</td>\n",
       "      <td>None</td>\n",
       "      <td>[]</td>\n",
       "      <td>[1]</td>\n",
       "      <td>152104</td>\n",
       "      <td>[6.57445E-6]</td>\n",
       "      <td>[afr]</td>\n",
       "      <td>chr18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>chr18:A:G:856929</td>\n",
       "      <td>chr18</td>\n",
       "      <td>856929</td>\n",
       "      <td>A</td>\n",
       "      <td>G</td>\n",
       "      <td>rs1342158649</td>\n",
       "      <td>[]</td>\n",
       "      <td>[2]</td>\n",
       "      <td>152094</td>\n",
       "      <td>[1.31498E-5]</td>\n",
       "      <td>[nfe]</td>\n",
       "      <td>chr18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>chr18:G:A:856932</td>\n",
       "      <td>chr18</td>\n",
       "      <td>856932</td>\n",
       "      <td>G</td>\n",
       "      <td>A</td>\n",
       "      <td>rs1310906294</td>\n",
       "      <td>[]</td>\n",
       "      <td>[1]</td>\n",
       "      <td>152100</td>\n",
       "      <td>[6.57462E-6]</td>\n",
       "      <td>[amr]</td>\n",
       "      <td>chr18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         variant_id  chrom     pos ref alt          rsid filters info_ac  \\\n",
       "0  chr18:A:G:856887  chr18  856887   A   G  rs1403391816      []     [2]   \n",
       "1  chr18:G:C:856899  chr18  856899   G   C          None      []     [1]   \n",
       "2  chr18:G:C:856903  chr18  856903   G   C  rs1398081239      []     [1]   \n",
       "3  chr18:G:A:856905  chr18  856905   G   A  rs1337701794      []     [1]   \n",
       "4  chr18:C:T:856909  chr18  856909   C   T  rs1400157635      []     [3]   \n",
       "5  chr18:C:T:856910  chr18  856910   C   T          None      []     [1]   \n",
       "6  chr18:A:G:856919  chr18  856919   A   G          None      []     [1]   \n",
       "7  chr18:A:G:856923  chr18  856923   A   G          None      []     [1]   \n",
       "8  chr18:A:G:856929  chr18  856929   A   G  rs1342158649      []     [2]   \n",
       "9  chr18:G:A:856932  chr18  856932   G   A  rs1310906294      []     [1]   \n",
       "\n",
       "   info_an       info_af info_popmax partition_0  \n",
       "0   152092  [1.31499E-5]       [nfe]       chr18  \n",
       "1   152130  [6.57333E-6]       [nfe]       chr18  \n",
       "2   152098  [6.57471E-6]       [eas]       chr18  \n",
       "3   152082   [6.5754E-6]       [eas]       chr18  \n",
       "4   152116  [1.97218E-5]       [eas]       chr18  \n",
       "5   152156   [6.5722E-6]       [eas]       chr18  \n",
       "6   152110  [6.57419E-6]        None       chr18  \n",
       "7   152104  [6.57445E-6]       [afr]       chr18  \n",
       "8   152094  [1.31498E-5]       [nfe]       chr18  \n",
       "9   152100  [6.57462E-6]       [amr]       chr18  "
      ]
     },
     "execution_count": 205,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"select * from gnomad limit 10\"\n",
    "df = execute_query_async(query)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "77279986",
   "metadata": {},
   "source": [
    "**Example 6:** Find all rare variants (Minor Allele frequency < 0.01) in the BRCA1 gene from gnomAD. We will use the view we created above."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "id": "453f5b3e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query execution summary:\n",
      "        DataScanned(MB): 3.43072\n",
      "        ExecutionTime(s): 2.562\n",
      "        QueuingTime(s): 0.183\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_col0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>16938</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   _col0\n",
       "0  16938"
      ]
     },
     "execution_count": 206,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"SELECT COUNT(*) from gnomad \\\n",
    "         WHERE partition_0 = 'chr17' \\\n",
    "         AND (cardinality(info_af) > 0 and info_af[1] < 0.01) \\\n",
    "         AND pos BETWEEN 43044295 and 43125364\"\n",
    "df=execute_query_async(query)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ba6a269",
   "metadata": {},
   "source": [
    "**Example 7:** Let us now find how many samples in the 1000 genomes dataset have rare variants (at least 2 subjects with Minor allele frequency < 0.01) in the BRCA1 region."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 207,
   "id": "f388fd19",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query execution summary:\n",
      "        DataScanned(MB): 145584.90366\n",
      "        ExecutionTime(s): 27.747\n",
      "        QueuingTime(s): 0.163\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>_col0</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3068</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   _col0\n",
       "0   3068"
      ]
     },
     "execution_count": 207,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"SELECT COUNT(DISTINCT sample_id) FROM {}.{} as v \n",
    "           JOIN gnomad AS a  \n",
    "           ON v.variant_id = a.variant_id \n",
    "           WHERE v.chrom='chr17' AND\n",
    "           v.pos BETWEEN 43044295 AND 43125364 AND\n",
    "           cardinality(a.info_af) > 0 AND a.info_af[1] < 0.01 \n",
    "           AND a.info_an > 1 \"\"\".format(databasename,partitioned_chr)\n",
    "df=execute_query_async(query)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9cc4bd81",
   "metadata": {},
   "source": [
    "**Example 7a:** Find all samples in the 1000 genomes dataset have rare variants (at least 2 subjects with Minor allele frequency < 0.01) in the BRCA1 region and are labeled Pathogenic or Likely pathogenic in ClinVar."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 210,
   "id": "2bd3ba21",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Query execution summary:\n",
      "        DataScanned(MB): 406273.40489\n",
      "        ExecutionTime(s): 38.644\n",
      "        QueuingTime(s): 0.261\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>variant_id</th>\n",
       "      <th>pos</th>\n",
       "      <th>ref</th>\n",
       "      <th>alt</th>\n",
       "      <th>sample_id</th>\n",
       "      <th>alleles</th>\n",
       "      <th>rsid</th>\n",
       "      <th>qual</th>\n",
       "      <th>filters</th>\n",
       "      <th>info.ac</th>\n",
       "      <th>...</th>\n",
       "      <th>chrom</th>\n",
       "      <th>pos</th>\n",
       "      <th>ref</th>\n",
       "      <th>alt</th>\n",
       "      <th>genename</th>\n",
       "      <th>clinicalsignificance</th>\n",
       "      <th>num_submitters</th>\n",
       "      <th>reviewstatus</th>\n",
       "      <th>phenotypeids</th>\n",
       "      <th>phenotypelist</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>chr17:C:T:43082403</td>\n",
       "      <td>43082403</td>\n",
       "      <td>C</td>\n",
       "      <td>T</td>\n",
       "      <td>HG00365</td>\n",
       "      <td>[C, T]</td>\n",
       "      <td>None</td>\n",
       "      <td>50.00</td>\n",
       "      <td>[]</td>\n",
       "      <td>[1]</td>\n",
       "      <td>...</td>\n",
       "      <td>17</td>\n",
       "      <td>43082403</td>\n",
       "      <td>C</td>\n",
       "      <td>T</td>\n",
       "      <td>BRCA1</td>\n",
       "      <td>Pathogenic</td>\n",
       "      <td>16</td>\n",
       "      <td>reviewed by expert panel</td>\n",
       "      <td>[MONDO:MONDO:0011450, MedGen:C2676676, OMIM:60...</td>\n",
       "      <td>[Breast-ovarian cancer,  familial 1|not provid...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>chr17:G:A:43092128</td>\n",
       "      <td>43092128</td>\n",
       "      <td>G</td>\n",
       "      <td>A</td>\n",
       "      <td>HG03929</td>\n",
       "      <td>[G, A]</td>\n",
       "      <td>None</td>\n",
       "      <td>49.67</td>\n",
       "      <td>[]</td>\n",
       "      <td>[1]</td>\n",
       "      <td>...</td>\n",
       "      <td>17</td>\n",
       "      <td>43092128</td>\n",
       "      <td>G</td>\n",
       "      <td>A</td>\n",
       "      <td>BRCA1</td>\n",
       "      <td>Pathogenic</td>\n",
       "      <td>11</td>\n",
       "      <td>reviewed by expert panel</td>\n",
       "      <td>[MONDO:MONDO:0011450, MedGen:C2676676, OMIM:60...</td>\n",
       "      <td>[Breast-ovarian cancer,  familial 1|not provid...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>chr17:G:A:43124063</td>\n",
       "      <td>43124063</td>\n",
       "      <td>G</td>\n",
       "      <td>A</td>\n",
       "      <td>HG02164</td>\n",
       "      <td>[G, A]</td>\n",
       "      <td>None</td>\n",
       "      <td>50.00</td>\n",
       "      <td>[]</td>\n",
       "      <td>[1]</td>\n",
       "      <td>...</td>\n",
       "      <td>17</td>\n",
       "      <td>43124063</td>\n",
       "      <td>G</td>\n",
       "      <td>A</td>\n",
       "      <td>BRCA1</td>\n",
       "      <td>Pathogenic</td>\n",
       "      <td>14</td>\n",
       "      <td>reviewed by expert panel</td>\n",
       "      <td>[MONDO:MONDO:0011450, MedGen:C2676676, OMIM:60...</td>\n",
       "      <td>[Breast-ovarian cancer,  familial 1|not provid...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 64 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           variant_id       pos  ref  alt sample_id alleles  rsid   qual  \\\n",
       "0  chr17:C:T:43082403  43082403    C    T   HG00365  [C, T]  None  50.00   \n",
       "1  chr17:G:A:43092128  43092128    G    A   HG03929  [G, A]  None  49.67   \n",
       "2  chr17:G:A:43124063  43124063    G    A   HG02164  [G, A]  None  50.00   \n",
       "\n",
       "  filters info.ac  ... chrom       pos  ref  alt genename  \\\n",
       "0      []     [1]  ...    17  43082403    C    T    BRCA1   \n",
       "1      []     [1]  ...    17  43092128    G    A    BRCA1   \n",
       "2      []     [1]  ...    17  43124063    G    A    BRCA1   \n",
       "\n",
       "   clinicalsignificance  num_submitters              reviewstatus  \\\n",
       "0            Pathogenic              16  reviewed by expert panel   \n",
       "1            Pathogenic              11  reviewed by expert panel   \n",
       "2            Pathogenic              14  reviewed by expert panel   \n",
       "\n",
       "                                        phenotypeids  \\\n",
       "0  [MONDO:MONDO:0011450, MedGen:C2676676, OMIM:60...   \n",
       "1  [MONDO:MONDO:0011450, MedGen:C2676676, OMIM:60...   \n",
       "2  [MONDO:MONDO:0011450, MedGen:C2676676, OMIM:60...   \n",
       "\n",
       "                                       phenotypelist  \n",
       "0  [Breast-ovarian cancer,  familial 1|not provid...  \n",
       "1  [Breast-ovarian cancer,  familial 1|not provid...  \n",
       "2  [Breast-ovarian cancer,  familial 1|not provid...  \n",
       "\n",
       "[3 rows x 64 columns]"
      ]
     },
     "execution_count": 210,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"SELECT * FROM {}.{} as v \n",
    "           JOIN gnomad AS a  \n",
    "           ON v.variant_id = a.variant_id \n",
    "           JOIN clinvar as clin\n",
    "           on a.variant_id = clin.variant_id\n",
    "           WHERE v.chrom='chr17' AND\n",
    "           v.pos BETWEEN 43044295 AND 43125364 AND\n",
    "           cardinality(filter(info_af, x -> x < 0.01)) > 0 \n",
    "           AND a.info_an > 1 \n",
    "           AND (clin.clinicalsignificance ='Pathogenic' OR\n",
    "                clin.clinicalsignificance = 'Likely pathogenic')\"\"\".format(databasename,partitioned_chr)\n",
    "df=execute_query_async(query)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 211,
   "id": "b64f1461",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aa2d9e69",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "instance_type": "ml.t3.medium",
  "kernelspec": {
   "display_name": "conda_python3",
   "language": "python",
   "name": "conda_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.6.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}