8
$\begingroup$

Are there best practices to load different bioinformatics file formats such as VCF, BED, GFF, and SAM to SQL databases? I am wondering how people out there do that efficiently.

All of these three formats are tab-separated files, so basically the following should work. I feel weird about it since most people I know don't use MySQL to work with these files.

LOAD DATA LOCAL INFILE 'bed.bed'
INTO TABLE bed-file
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS (list of the columns)
SET creation_date  = STR_TO_DATE(@creation_date, '%m/%d/%y');
$\endgroup$
2
  • $\begingroup$ You could look at GBrowse or EnsEMBL to do this, but they are quite... heavy? Perhaps look at generic MySQL optimization? $\endgroup$
    – Dan Bolser
    Nov 8 '21 at 12:36
  • 1
    $\begingroup$ > I feel weird about it since most people I know don't use MySQL to work with these files. see biostars.org/p/456664 "Forum:Why does bioinformatics use a special format like a BAM file instead of a database like SQLite?" $\endgroup$
    – Pierre
    Nov 8 '21 at 13:24
2
$\begingroup$

The clodius tool puts tab-delimited data from a BED file into an rtree in a sqlite3 database:

https://github.com/higlass/clodius/blob/c98bb16ade93402fcea3b749d705c52ea165b609/clodius/cli/aggregate.py#L457-L777

Data are stored in this database at multiple "zoom levels", which allows for fast queries at wide genomic extents:

https://github.com/higlass/clodius/blob/develop/clodius/tiles/beddb.py

Aggregation (input) and querying (output) seem efficient in practice, but efficiency may depend on aspects of your specific dataset, i.e., whether you mean efficiency in execution time of INSERT or SELECT statements, or in space efficiency in the overall database size.

Other data can be converted to BED via vcf2bed, sam2bed, gff2bed, etc. and then aggregated. This is not the only way to do things, of course, but hopefully this can suggest directions to explore.

$\endgroup$
1
$\begingroup$

You might also take a look at this genomics-focused sqlite extension: https://github.com/mlin/GenomicSQLite

As described in the repository README:

This SQLite3 loadable extension adds features to the ubiquitous embedded RDBMS supporting applications in genome bioinformatics:

  • genomic range indexing for overlap queries & joins
  • in-SQL utility functions, e.g. reverse-complement DNA, parse "chr1:2,345-6,789"
  • automatic streaming storage compression (also available standalone)
  • reading directly from HTTP(S) URLs (also available standalone)
  • pre-tuned settings for "big data"
$\endgroup$
-2
$\begingroup$

Answer from @liam-mcintyre converted from comment:

I don't use dask as it doesn't support enough pandas functionality (unfortunately). With pandas I do it with read_csv... if its big then read in chunks and send chunks to separate threads. If you want to ask a specific question with example data etc then I can show code.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.