0

This is for one of my personal projects. I want to store a table of data in a BLOB/TEXT column in the database. I don't want to store the data in its own database table because the schema of this data will often change, and I don't really need to query it.

So I am searching for a data format that would be:

  • Suited for storing a table of variously typed data (dates, timestamps, integers, floats, strings, etc)
  • Open source and implemented in several programming languages
  • Preferably binary

Thank you very much

art-solopov
  • 247
  • 3
  • 7
  • What formats have you looked at, and why are they not suitable? – Mael Mar 09 '18 at 08:05
  • I've considered CSV but it's not binary and has typing problems. I could use JSON or MessagePack, but it's more suited to hierarchical data (basically, I'll have to repeat column names for every row). Also, the typing isn't ideal. I could use XML but it's similar to JSON in that it's more suited for hierarchical data. – art-solopov Mar 09 '18 at 08:44
  • 3
    Also I could be REALLY passive-aggressive and write an SQLite database into a BLOB... – art-solopov Mar 09 '18 at 08:44
  • Why binary? Just for reasons of saving space? If you really need that (which I bet you don't), use some text format combined with a compression algorithm. XML has the widest range of support and allows storing any type you like. I have seen several applications where zipped XML was used. – Doc Brown Mar 10 '18 at 07:33

5 Answers5

1

The defacto standard is surely CSV. Unless you have good reasons not to, then store it this way.

As the format of the data can vary, then the header row can be used to both describe the column's purpose (ie its name) and its format (ie its type), allowing the remaining rows to just be simple text.

CVS's are easy to parse and are well supported by all major languages and frameworks. So development and maintenance times are kept to a minimum.

If space is an issue, then you could go down the route of a custom binary format. But then you have to "roll your own", they are harder to parse, more error prone and are not human-readable. So the other option is to zip the contents. Again zip libraries are readily available for all major languages and frameworks, saving you time and simplifying maintenance once again.

David Arno
  • 38,972
  • 9
  • 88
  • 121
  • Yeah, that's the first thing that comes to mind... I can even tweak it a bit to store data type indicators in the headers. Thank you! – art-solopov Mar 09 '18 at 07:58
  • Well, that will not be binary ofcourse, storing floats and ints in a csv will consume more space than a binary dump. You should make up how important that extra space is in your case. – Teimpz Mar 09 '18 at 08:03
  • Oh, man... Why CSV? – Mael Mar 09 '18 at 08:06
  • CSV covers ever aspect of OP's request *except* typing. How would you know "2018-03-09T13:00:00" is a string or a date? Maybe assuming it is a date would work 99.9% of the time.. but the point is 99.9% isn't 100%. – Neil Mar 09 '18 at 08:10
  • @Neil Sure, but it is notoriously hard to parse, ambiguous, and there are several notions of what "CSV" means (in my experience - every program has its own). Or maybe I'm just scarred because of the quality of CSV files I used to receive... – Mael Mar 09 '18 at 08:14
  • @Mael Oh I agree. CSV files are more of a loose convention than any sort of reliable standard. I'm convinced programmers dealing with CSV files is what inspired more standard formats such as JSON and XML. – Neil Mar 09 '18 at 08:21
  • @Mael, I have updated my answer too explain "why CSV". Short answer: because it's by far the best choice; by a mile. – David Arno Mar 09 '18 at 08:26
  • @Neil, JSON and XML are great for more complex data. They are a case of over-engineering for simple tabular data though. Use the solution appropriate to the problem. – David Arno Mar 09 '18 at 08:28
  • 1
    @DavidArno I still think that CSV is not a good choice if you don't have to use it, but I don't want to argue, so we'll just have to agree to disagree. I wouldn't touch CSV with a ten-foot pole if I didn't have to, but unfortunately I must agree with you that it *is* a defacto standard in many cases... :-( – Mael Mar 09 '18 at 08:42
1

The baseline

JSON (http://json.org) is a format that is dynamic and schema-less. But it is text-based format, so may not be appropriate for you if you require binary.

Binary formats

BSON (http://bsonspec.org) may be interesting to you. It is a "simple" JSON translated to binary (not quite, but close enough). It is used in MongoDB to store data, so it is field-proven.

CBOR (http://cbor.io) is another binary JSON-like format. It's advantage (to me, at least) is that it is supported by an RFC (RFC 7049) and so has a defined standard which may prove useful.

BSON and CBOR are probably your best shots if you need binary JSON-like format.

Community
  • 1
Mael
  • 2,305
  • 1
  • 13
  • 26
0

Document databases are designed for flexibility. If you have varying attributes with large amount of data document database is good option. Instead of storing data in different tables they are stored together in the same document.

Open source document database is for example MongoDB. MongoDB uses JSON called binary JSON that's is able to perform binary serialization.

Marcin C
  • 101
  • 4
  • I just feel like using a whole database to store about twenty documents of data that will always be read together is pretty much overkill. – art-solopov Mar 09 '18 at 08:47
0

Use JSON / BSON with Mariadb / Toukdb engine, it'll compress the data for you very nicely. Unlike innodb compression, so if you tried compression before but dropped it because it sucked in inno - try this one...

Slawek
  • 2,884
  • 16
  • 16
-1

Assuming you don't wish to query the data, it would seem logical that the next best priority would be being able to restore the table as efficiently as possible. There are usually supported ways of exporting/importing tables from a database to a file on the disk.

My advice would be to stream the supported format of exported table data directly into a blob. You can then take that data and call upon that same database to "import" as if it were a normal import file from the disk.

The biggest advantage of this approach is that there is literally 0 hassle with typing. The database knows how best to save its own type information. No need to reinvent the wheel.

You should be made aware that this approach may cause problems if you update your database (not likely, but there's still that possibility). So before updating your database, please check that old imports are still accepted in the new version.

Usually these import files are flat files (columns are padded with spaces to guarantee that each column value is found in the same position for each line), and therefore they might occupy a lot more space then the table itself. You might consider zipping the contents first before inserting them, but I'll leave you to decide whether the efficiency/space tradeoff is worth it in your case.

Neil
  • 22,670
  • 45
  • 76