In a previous post, I wrote about downloading SRA files from NCBI-SRA or EBI-ENA using the R package SRAdb. In this post, I will write about using SQL to query the SRA SQLite file, with the aim of giving the downloaded sequencing files meaningful titles.

Please follow the instructions below to establish a connection to the SRA SQLite file. For more details, please visit the aforementioned post.

# load required libraries
# load extra libraries
# download the SRA SQL database, only if does not exist locally
# Warning: the download size is ~2.3GB and the extracted size is ~37 GB!
if( ! file.exists('~/SRAmetadb.sqlite') ) {
   sqlfile <- getSRAdbFile()
} else {
  sqlfile <- '~/SRAmetadb.sqlite'
# connect to the SRA SQLite database
sra_con = dbConnect(RSQLite::SQLite(), sqlfile)

The structure of the SRA SQLite database

The SRA SQLite file consists of multiple tables. Each table has a number of fields (or columns) and a number of records (or rows). Each field has a type which corresponds to the type of data it stores.

Let’s start by listing all the tables in the database.

# list the tables in a database
dbListTables(sra_con) -> sra_tables

As you see, we have 13 tables in the database. To explore one of the tables further (e.g. experiment), we can list the fields in this particular table.

# show the fields in a table

In the experiment table, we have 42 fields (or columns). Next, we will explore what type of data each field contains.

# check the data type of each column in a table
dbGetQuery(sra_con,'PRAGMA TABLE_INFO(experiment)')

Furthermore, we can list the descriptions of what is stored in each field of each table.

colDescriptions(sra_con=sra_con) -> colDesc

Using SQL to query the SRA SQLite database

We can use SQL to submit queries to the database. For example, to access the first ten records (or rows) of the experiment table:

# select the first 10 records from the experiment table
   FROM experiment LIMIT 10"

To search for keywords, e.g. accessibility, within the field of study_description in the study table:

# search for 'accessibility' and to retrieve the study accession and title

dbGetQuery(sra_con, paste(
  "SELECT study_accession,
   FROM study
   WHERE study_description LIKE '%accessib%'",
  sep=" "

Notice that in SQL, we can use %, which represents zero, one, or multiple characters and _ which represents a single character. In the example above %accessibl% means finds any value that has accessibl in any position.

To get general statistics about the database, we can list all types of study:

# list all study types and number of studies contained in each type
dbGetQuery(sra_con, paste(
  "SELECT study_type AS 'Study Type',
          COUNT(*) AS Number
   FROM study
   GROUP BY study_type
   ORDER BY Number DESC",
  sep = ""

Finally, we can list all types of library strategies:

# list all types of library strategies and number of runs in each type
dbGetQuery(sra_con, paste(
  "SELECT library_strategy AS 'Library Strategy',
          COUNT (*) AS Runs
  FROM experiment
  GROUP BY library_strategy
  sep = ""

Renaming downloaded sequence files

As in the previous post, I picked a random SRA submission (e.g. SRP042080). One nice feature in SRAdb is the ability to list all SRA submission objects associated with a specific object.

sraConvert( c('SRP042080'), sra_con = sra_con ) -> sra_project
# list available runs in the study
sra_project$run -> sra_runs
# list the download links
'~/Downloads' -> destDir
getFASTQfile(sra_runs, sra_con, destDir = destDir, srcType = "fasp") -> EBI_cmd

We will build the query to get the run titles as follow:

  • Join the experiment and run tables using the experiment_accession field
  • Join them with the fastq table using run_accession (optional)
  • Filter the experiment_accession of interest.
  • Order by library_name
dbGetQuery(sra_con, paste(
  "SELECT experiment.library_name AS 'Library Name',
          experiment.title AS 'exp_title',
          run.run_accession AS 'run',
          fastq.FASTQ_FILES AS 'fastq'
   FROM experiment
   JOIN run ON experiment.experiment_accession = run.experiment_accession
   JOIN fastq ON run.run_accession = fastq.run_accession
   WHERE study_accession = 'SRP042080'
   ORDER BY library_name",
   sep = ""
)) -> runs_titles


It is time to use our regex skills to generate the rename table.

  • The expression ^.*:.()|;.*$ will:

    • look into the exp_title column
    • match any character from the start of the line including : and the space after it
    • match any character from the end of the line till last ; and including all ;
    • capture the characters in between using ()
    • store the results in a new column run_title
  • The expression (^.*/*/)(.*$) will:

    • look into the fasp column
    • use first capturing group (^.*/*/) to
    • match any characters from the start of the line till the last / in the path.
    • use second capturing group (.*$) to
    • capture the rest of the full path, which is the file name and the extension
    • return only the second capturing group using \\2
    • store the results in a new column fasp_file

Then we will use str_replace which will, for each row, search in the new fasp_file column to match the string in run and replace it with the string from run_title. The results will be a new column called file_name.

merge(runs_titles, EBI_cmd) %>%
  mutate(run_title = str_replace_all(exp_title, "^.*:.()|;.*$", "")) %>%
  mutate(fasp_file = str_replace(fasp, "(^.*/*/)(.*$)", "\\2")) %>%
  mutate(file_name = str_replace(fasp_file, run, run_title)) %>%
  select(file_name, fasp_file) -> rename_table


Now that we have the rename_table, we can simply loop through the rows to create a symbolic link named file_name to the fasp_file.

knitr::opts_knit$set(root.dir = "~/Downloads")
for(seq in seq_along(rename.table$fasp_file)){
    file.symlink(paste0(rename.table$fasp_file [seq]), paste0(rename.table$file_name[seq]))