Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-11-08T23:33:05.107Z
Answers.LastActivityDate: 2021-11-09T00:02:30.307Z
Answers.LastEditDate: 2021-11-09T00:02:30.307Z
Answers.UserDisplayName: Alex Reynolds
Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-11-09T19:52:39.137Z
Answers.LastActivityDate: 2021-11-09T19:52:39.137Z
Answers.UserDisplayName: Alex Reynolds
Body: <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>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2022-11-13T04:07:35.723Z
Comments.Text: 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.
Comments.UserDisplayName: Jessime Kirk
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2022-11-13T05:29:35.607Z
Comments.Text: @JessimeKirk not really. I've heard recently about [Glow](https://glow.readthedocs.io/en/latest/introduction.html). Maybe it will be helpful to you.
Comments.UserDisplayName: 0x90
Tags: <vcf><database><bed><sam><gff>
Title: How to import a large amount of .bed, .gff, .vcf, .paf, .sam files into an SQL database?
ThreadText: # 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**: @username_1 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"
  
  
  ---
  
  