2_Array_Examples.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e2af07ff",
   "metadata": {},
   "source": [
    "# Getting Started with Arrays in BigQuery\n",
    "Arrays are an important topic to understand on at least a basic level to work with data in BigQuery. It allows additional information to be stored in lists or key-value pairs within the table. Both can be searched on and extracted to find exactly what you are looking for.\n",
    "\n",
    "First you need to install and prepare the environment again."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "02561304",
   "metadata": {},
   "source": [
    "```\n",
    "\n",
    "# These commands were run when the notebook was created by the instructors and are shown in case you choose to work with Notebooks later.\n",
    "\n",
    "\n",
    "#!pip install google.cloud.bigquery\n",
    "#!pip install google.cloud.storage\n",
    "#!pip3 install --upgrade google-cloud-bigquery\n",
    "#!pip install google --user\n",
    "#!pip install --upgrade 'google-cloud-bigquery[bqstorage,pandas]' --user\n",
    "\n",
    "\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4b5fe4be",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Run this command block to install the BigQuery extension.\n",
    "%load_ext google.cloud.bigquery"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2c5f6779",
   "metadata": {},
   "source": [
    "# Problem : I want to find RNA-Seq data from estrogen receptor positive breast cancer cell lines.\n",
    "Doing a search like this will require looking into the attributes stored in the database for each record. If you are an experienced user of SRA you might already know how to do a search similar to this with Entrez and Run Selector. Here we will find the results in an SQL search primarily using the attributes column. The methods used to do this can be applied to build very targeted searches of submitter supplied information.\n",
    "\n",
    "## What Does an Array look like in BigQuery?\n",
    "Here you will look at a couple of columns from the database to show examples of arrays. You will select the accession column and the columns that are either simple arrays or are an array of data structures. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18743fb9",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "\n",
    "SELECT acc, datastore_filetype, datastore_provider, datastore_region, attributes\n",
    "FROM `nih-sra-datastore.sra.metadata`\n",
    "WHERE organism = 'Homo sapiens'\n",
    "LIMIT 5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b6c50bee",
   "metadata": {},
   "source": [
    "## Viewing what is in the Attributes Column of an SRA Record\n",
    "To understand what is in the attributes column in the database it may help to view the contents as a table. All of the columns other than acc are arrays \\[ \\] with a comma separated list of items inside. The attributes column is an array of structs { } that have a key (k) and a value (v) for each item in the array. You will use the UNNEST function in BigQuery to extract the contents of the attributes column into one item per row. \n",
    "\n",
    "## Listing the Contents of an Array\n",
    "If you unnest the attributes column, you get the list of the key-value pairs. The contents of this column are some standard items like \"bytes\" and \"bases\" as well as items that were provided by the submitter as additional information. This means that runs in the database will have some attributes in common but also some attributes not on other runs. There can also be keys that are repeated so one key might have multiple values in a single run. \n",
    "\n",
    "### Naming Unnested Data\n",
    "When you unnest the data you can give the result of the unnest function a name (unnested_attributes in the below query) and then use that name in the select statement. Like you did before when naming tables, it can be used to make queries a little easier to type and read."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "713e06d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "\n",
    "SELECT unnested_attributes\n",
    "FROM `nih-sra-datastore.sra.metadata`,\n",
    "    UNNEST(attributes) as unnested_attributes \n",
    "WHERE acc = 'SRR2973262'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9962febe",
   "metadata": {},
   "source": [
    "## Listing the Keys and Values in the Attributes as a Table\n",
    "You can further break a structured list like this {'k': 'sex_calc', 'v': 'female'} apart into (k) and (v) columns by using unnested_attributes.k and unnested_attributes.v in the query. At this point you will be seeing all the keys and values in a table format for a single accession in the metadata table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d2ec3b3a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "\n",
    "SELECT unnested_attributes.k, unnested_attributes.v\n",
    "FROM `nih-sra-datastore.sra.metadata`,\n",
    "    UNNEST(attributes) as unnested_attributes \n",
    "WHERE acc = 'SRR2973262'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6bb2de6d",
   "metadata": {},
   "source": [
    "## Finding Runs with a Certain Attribute\n",
    "Listing out all the attribute keys and values can be quite useful to help build searches for very specific things. Looking at the results for the above query, one of the attributes is tissue_sam : colon. If you want to find all runs where that exists you can use the following query."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b31b4101",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "\n",
    "SELECT acc\n",
    "FROM `nih-sra-datastore.sra.metadata`   \n",
    "WHERE ('tissue_sam', 'colon') in UNNEST(attributes)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca8d90c1",
   "metadata": {},
   "source": [
    "## Search for Breast Cancer Cell Line Records\n",
    "Now you can use the UNNEST function to search for the data in the initial problem. The LIKE function with the wildcard % in will let you search for records that contain certain words or phrases with anything around them. The query below will search for all records that mention \"breast\" and \"cancer\" and \"cell\" and \"line\" in at least one attribute."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "86a15a96",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "\n",
    "SELECT acc, assay_type, consent, librarysource, libraryselection, bioproject,\n",
    "FROM `nih-sra-datastore.sra.metadata` meta,\n",
    "    UNNEST(attributes) as extracted\n",
    "WHERE extracted.v LIKE '%breast%cancer%cell%line%'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ec96c153",
   "metadata": {},
   "source": [
    "## Subselect\n",
    "A subselect is a select statement embedded within another select statement. In this case you will use a subselect to get the value of attributes using the attribute key name. This example uses 'source_name_sam' to see values that are on *some* records and 'bases' which will be on *all* records.\n",
    "\n",
    "You can also find the key for the attribute that contained the breast cancer cell line entry by using a subselect. Because there might be more than one attribute key that contains that phrase in a single record, you can use the limit function to just find the first hit. You could do something like build an array from all the key hits but that is more involved and not necessary in this example."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5b9167dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "\n",
    "SELECT acc, assay_type, consent, librarysource, libraryselection, bioproject,\n",
    "    (select v from unnest(attributes) where k = 'source_name_sam')as source_name,\n",
    "    (select v from unnest(attributes) where k = 'bases')as bases,\n",
    "    (select k from unnest(attributes) where v like '%breast%cancer%cell%line%' limit 1) as breast_cancer_attribute,\n",
    "FROM `nih-sra-datastore.sra.metadata` meta,\n",
    "    UNNEST(attributes) as extracted\n",
    "WHERE extracted.v LIKE '%breast%cancer%cell%line%'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "40bca2c1",
   "metadata": {},
   "source": [
    "## Limiting the Search Further\n",
    "Now you will add the search about the Estrogen Receptor status of the cells as well as limit it to paired RNA-Seq datasets using cDNA selection that are in the public domain. And again you will query the keys to see what attribute contained the information about the cells being ER-positive."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b04eac0f",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery\n",
    "\n",
    "SELECT acc, assay_type, consent, librarysource, libraryselection, bioproject,\n",
    "    (select v from unnest(attributes) where k = 'source_name_sam')as source_name,\n",
    "    (select v from unnest(attributes) where k = 'bases')as bases,\n",
    "    (select k from unnest(attributes) where v like '%ER-positive%' limit 1) as ER_column\n",
    "FROM `nih-sra-datastore.sra.metadata` meta,\n",
    "    UNNEST(attributes) as extracted\n",
    "WHERE assay_type = 'RNA-Seq'\n",
    "    AND consent = 'public'\n",
    "    AND libraryselection = 'cDNA'\n",
    "    AND librarylayout = 'PAIRED'\n",
    "    AND extracted.v LIKE '%breast%cancer%cell%line%'\n",
    "    AND extracted.v LIKE '%ER-positive%'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e52a1e3",
   "metadata": {},
   "source": [
    "## Storing Accessions or Metadata Tables in a Variable\n",
    "In the last couple of examples we will look at storing query output in a file within Jupyter. You can store the results of the query in a variable using a function in the bigquery module. You do this by providing the name of the variable you will use (\"metadata\" in this example) to store the output in. This is somewhat specific to the bigquery python module you're using here but the there may be some overlap with other python methods you may be using for work.\n",
    "\n",
    "First we will store the metadata table from the previouls in one variable then we will store just the accessions in a different variable."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "42399576",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery metadata\n",
    "\n",
    "SELECT acc, assay_type, consent, librarysource, libraryselection, bioproject,\n",
    "    (select v from unnest(attributes) where k = 'source_name_sam')as source_name,\n",
    "    (select v from unnest(attributes) where k = 'bases')as bases,\n",
    "    (select k from unnest(attributes) where v like '%ER-positive%' limit 1) as ER_column\n",
    "FROM `nih-sra-datastore.sra.metadata` meta,\n",
    "    UNNEST(attributes) as extracted\n",
    "WHERE assay_type = 'RNA-Seq'\n",
    "    AND consent = 'public'\n",
    "    AND libraryselection = 'cDNA'\n",
    "    AND librarylayout = 'PAIRED'\n",
    "    AND extracted.v LIKE '%breast%cancer%cell%line%'\n",
    "    AND extracted.v LIKE '%ER-positive%'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "063c0d33",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%bigquery accessions\n",
    "\n",
    "SELECT acc\n",
    "FROM `nih-sra-datastore.sra.metadata` meta,\n",
    "    UNNEST(attributes) as extracted\n",
    "WHERE assay_type = 'RNA-Seq'\n",
    "    AND consent = 'public'\n",
    "    AND libraryselection = 'cDNA'\n",
    "    AND librarylayout = 'PAIRED'\n",
    "    AND extracted.v LIKE '%breast%cancer%cell%line%'\n",
    "    AND extracted.v LIKE '%ER-positive%'\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d6e699b0",
   "metadata": {},
   "source": [
    "## Viewing the Contents of a Variable\n",
    "You can output the contents of a variable by running a cell with just the variable name in it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1a7d3e29",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "metadata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2bf521be",
   "metadata": {},
   "outputs": [],
   "source": [
    "accessions"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f9fcbbf",
   "metadata": {},
   "source": [
    "## Storing the Variable Contents as a File\n",
    "The results variable contains a dataframe with an index (0-5) and a header (acc) that you don't want in the accession list. You will remove those using the to_string function using the options of index=False and header=False. This will save a file with just a list of accessions. Alternatively if you wanted to save the results of a metadata query that included the header information, you can omit the header=False option."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6a25ab01",
   "metadata": {},
   "outputs": [],
   "source": [
    "file = open(\"metadata.txt\", \"w\")\n",
    "file.write(metadata.to_string())\n",
    "file.close()\n",
    "\n",
    "file = open(\"accessions.txt\", \"w\")\n",
    "file.write(accessions.to_string(index=False, header=False))\n",
    "file.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "10919f54",
   "metadata": {},
   "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}