text: user734: 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');
  
  
  user776: 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"
  
  
  
  user776: 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.
  
original_text: 734: <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>
   776: <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>
   776: <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>
  