<p>The <a href="https://github.com/higlass/clodius" rel="nofollow noreferrer">clodius</a> tool puts tab-delimited data from a BED file into an <a href="https://www.sqlite.org/rtree.html" rel="nofollow noreferrer">rtree</a> in a sqlite3 database:</p>
<p><a href="https://github.com/higlass/clodius/blob/c98bb16ade93402fcea3b749d705c52ea165b609/clodius/cli/aggregate.py#L457-L777" rel="nofollow noreferrer">https://github.com/higlass/clodius/blob/c98bb16ade93402fcea3b749d705c52ea165b609/clodius/cli/aggregate.py#L457-L777</a></p>
<p>Data are stored in this database at multiple &quot;zoom levels&quot;, which allows for fast queries at wide genomic extents:</p>
<p><a href="https://github.com/higlass/clodius/blob/develop/clodius/tiles/beddb.py" rel="nofollow noreferrer">https://github.com/higlass/clodius/blob/develop/clodius/tiles/beddb.py</a></p>
<p>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 <code>INSERT</code> or <code>SELECT</code> statements, or in space efficiency in the overall database size.</p>
<p>Other data can be converted to BED via <code>vcf2bed</code>, <code>sam2bed</code>, <code>gff2bed</code>, etc. and then aggregated. This is not the only way to do things, of course, but hopefully this can suggest directions to explore.</p>

CC BY-SA 4.0
2021-11-08T23:33:05.107Z
2021-11-09T00:02:30.307Z
2021-11-09T00:02:30.307Z
Alex Reynolds
<p>You might also take a look at this genomics-focused sqlite extension: <a href="https://github.com/mlin/GenomicSQLite" rel="nofollow noreferrer">https://github.com/mlin/GenomicSQLite</a></p>
<p>As described in the repository README:</p>
<blockquote>
<p>This SQLite3 loadable extension adds features to the ubiquitous
embedded RDBMS supporting applications in genome bioinformatics:</p>
<ul>
<li>genomic range indexing for overlap queries &amp; joins</li>
<li>in-SQL utility functions, e.g. reverse-complement DNA, parse &quot;chr1:2,345-6,789&quot;</li>
<li>automatic streaming storage compression (also available standalone)</li>
<li>reading directly from HTTP(S) URLs (also available standalone)</li>
<li>pre-tuned settings for &quot;big data&quot;</li>
</ul>
</blockquote>

CC BY-SA 4.0
2021-11-09T19:52:39.137Z
2021-11-09T19:52:39.137Z
Alex Reynolds
<p>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.</p>
<p>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.</p>
<pre><code>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');
</code></pre>

CC BY-SA 4.0
2022-11-13T04:07:35.723Z
Did you ever find a satisfactory answer to this question? I'd love to hear more about what sort of solution, if any, you ended up with.
Jessime Kirk
CC BY-SA 4.0
2022-11-13T05:29:35.607Z
@JessimeKirk not really. I've heard recently about [Glow](https://glow.readthedocs.io/en/latest/introduction.html). Maybe it will be helpful to you.
0x90
<vcf><database><bed><sam><gff>
How to import a large amount of .bed, .gff, .vcf, .paf, .sam files into an SQL database?
# How to import a large amount of .bed, .gff, .vcf, .paf, .sam files into an SQL database?

(Asked by: username_0 on 2021-01-21)

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');

**Tags:** vcf, database, bed, sam, gff
> **Comments:**
> **username_1**: Did you ever find a satisfactory answer to this question? I'd love to hear more about what sort of solution, if any, you ended up with.  
> **username_0**: @JessimeKirk not really. I've heard recently about [Glow](https://glow.readthedocs.io/en/latest/introduction.html). Maybe it will be helpful to you.  

---

## 2 answers

### Answer by username_2 on 2021-11-08. Score: 2

The [clodius](https://github.com/higlass/clodius) tool puts tab-delimited data from a BED file into an [rtree](https://www.sqlite.org/rtree.html) 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.


---

### Answer by username_2 on 2021-11-09. Score: 1

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"


---

