# HDF5 and BioSQL solutions

I'm looking at better database/storage solutions for NCBI virus data, with all attributes particularly year and country of isolation, together with structural data, possible antibody data, T-cell data and bioinformatics data such as %GC etc... The amount of data is comparatively small, with a maximum size of 3 x ([6000 < no. viruses < 20000] for 10 genes) and each entry will contain nucleotide, amino acid and one or more pieces of alignment data (i.e. with indels).

The data will be used for numerous different calculations from structural biology, phylogenetics, and deep learning. Some of the calculations will require parallel processing. A typical calculation would be to identify a clade/similarity/difference then screen the database to look at the isolates information.

Previously I've used lots of flat files which are loaded at initiation and are held within a data pipeline either as an object (Python) or especially a multi-dimensional hash (Perl, preferred).

The two solutions that I think look suitable are:

1. BioSQL - The official Genbank solution, however my SQL commands are very basic and the documentation appears minimal (for BioSQL)
2. HDF5 (hierarchical data format) - this looks good because it uses complex hashes (dictionaries), and allows layering of multiple information onto a single Genbank sequence code (key). It has strong documentation. However, I understand this isn't a "database" (long term storage), but thats how I intend to use it.

JSON/Pickling I had a look at JSON, which to me looks okay, because I'm fond of complex hashes / dictionaries, but it appears to be more of a Web solution. Finally, "pickling" (Python) is cool, very cool, but I understand this not a database solution, because it risk compatibility issues, e.g. with future Python releases.

I am interested in knowing what archival systems board members are using, and a critique whether my assessment is ok for moving forward.

• If you are querying repeatedly and have multiple features on which you are applying search criteria, use a proper database with indexed features/columns/fields. – Alex Reynolds May 30 at 23:04

Just to conclude, @user172818 and @AlexReynolds are collectively saying either learn SQL or stick with CSV. Shoe-horning isn't an option.

A family member did industrial SQL databasing (Windows-based) and IMO its a parallel universe. Perl/hashes and Python Pandas/hierarchical-indexing and NCBI's "e-fetch/e-search" framework, are all my databasing data-munging I need so I'll stick with "flat-files".

If someone wants to post something to this extent, I'll sign the question complete.

HDF5, I suspect, has some sort of application to "higher order programming" (HoP),

... edit ... actually it doesn't I checked it out. Its application power is focused on calculations outside my remit and imposes restrictions on numpy array 'magic', so in my application pickling is a better short solution and longterm CSV.

• Hashes are memory intensive. Perhaps keep an eye on your memory usage, if you go this route. This is why indexing and sorting approaches are used. – Alex Reynolds Jun 4 at 2:37
• Thanks, I appreciate critical feedback. In defence of Perl v5.20 now has postfix dereferencing ->@* syntax, which a step forward for hashes. The data loads involved are less than a single higher eukaryotic genome. Obviously not doing dataframe style calculations in a hash ... its doable done (higher order programming), but possibly surpassed. – Michael G. Jun 4 at 13:10