8 ms·
I wish more people would publish data as SQLite databases (if the size permits, of course; usually it does). It's so much more reliable than CSVs, which have at
by anonymouzz 8y ago
I wish more people would publish data as SQLite databases (if the size permits, of course; usually it does). It's so much more reliable than CSVs, which have at least a few dimensions of significant differences (quoted/unquoted, comma vs semicolon vs tab vs space, headers/no headers, comments). Not to mention that initial data exploration can be done right in an SQLite explorer/browser tool.
- icelancer 8y agoWhen I open my data I usually publish both CSV dumps (for those who prefer using awk/sed and command line tools; this is usually easier) and MariaDB reconstructive files. I'll consider SQLite databases from now on, I like that idea.
- blitmap 8y agoI wish webpages were 'archived' as sqlite databases. :x I wish a lot of metadata were defined as a database schema, and sqlite lends itself so willingly to becoming the archive/header. Does sqlite do internal gunzip compression? I do understand we have MHTML: https://en.wikipedia.org/wiki/MHTML https://en.wikipedia.org/wiki/MHTML
- voltagex_ 8y agoWARC is the "standard" now for web archiving.
- olalonde 8y agoReminds me of this HN submission: https://news.ycombinator.com/item?id=16809963 https://news.ycombinator.com/item?id=16809963 Apparently CSV is actually quite hard to parse.
- mirimir 8y agoIt's not that CSV is hard to parse. It's that there's no guarantee that you'll get proper CSV. For example, it may literally be "CSV", without quotes. And that's fatal if values contain commas. I've even seen CSV with values that contain ``","``!
- gregmac 8y agoRFC4180 [1] standardizes CSV, but there are many implementations that don't read this 100% and unfortunately even more (including an extremely popular spreadsheet application) that don't write it. If you are including CSV functionality in something you work on, please read and follow this (tiny) spec! [1] https://tools.ietf.org/html/rfc4180 https://tools.ietf.org/html/rfc4180
- blattimwind 8y agoWhether Excel writes standard CSV or not depends on the user's locale settings. E.g. with a German locale you get a semicolon (;) as a separator which you can only change system-wide. However, apart from the changed seperator, it's still standard CSV, which still works with Python or SQLite (.separator ; .import foo.csv foo). A bigger issue is that Excel tends to write large numbers in scientific notation, which is a common issue handling price lists. E.g. it'll turn EAN numbers into 6.2134e+11, losing most of the number. Then you have to go back to the XLS file and change the column type into text and exporting it again as CSV. As this is lossy you can't fix it when receiving such a file. Something like the SQL Server Import/Export Wizard but being able to write SQLite files would be very handy.
- sonofgod 8y agoFun fact - we noticed SQLite wasn't RFC-compliant for it's CSV output (it used native lineendings, not CRLF, which is mandatory). It is fixed now... but I'm now wondering whether SQLite wasn't more correct in the first place...
- kijin 8y ago> I've even seen CSV with values that contain ``","``! Are you sure it wasn't an injection attempt of some kind?
- mirimir 8y agoIt could have been, I suppose. But more likely is twisted creativity. It seems that some businesses are still using ancient systems, based on COBOL, AS/400, etc. There's resistance to changing legacy code. So when business changes require additional data fields, fields sometimes get subdivided. So a field that originally contained stuff like |foo| now contains stuff like |"foo","bar,baz"| or whatever. That works, because there's nothing like CSV in the data system. But when someone tries a CSV export, you get garbage.
- BurningFrog 8y agoIt's hard enough that you should always use the library that handles the 5 weird cases, of which you'll only think of 3. I made the mistake once and learned.
- eecc 8y agoIn several companies, the interview assignment is indeed to implement a CSV parser.
- _wmd 8y agoAs much as I love SQLite, and while it is open source, it is a single implementation that AFAIK has no published open specification. The only way to read an SQLite file is using SQLite, and in that respect, it is for many users just as closed as wrapping something in a word document. CSV isn't perfect, but it provides a ton of flexibility, for example, CSVs can be streamed or support parallel segmented download across a network with useful work possible during the transfer. The format is so simple that it can approach almost free to parse (see e.g. my own https://github.com/dw/csvmonkey https://github.com/dw/csvmonkey ). CSV is also distinguished in that regular home users with spreadsheet programs can usually do most things a developer can do with the same file. For me user empowerment trumps all other goals in software, including warts. Things like JSON, XML or SQLite definitely don't fit in that category, although I guess SQLite is at least better due to the wide availability of decent GUIs for it. Finally as a data transfer format, SQLite has the potential to be massively inefficient. Done incorrectly it can ship useless indexes that can inflate size >100%, and even in the absence of those, depending on how amenable the data is to being stored in a btree and the access patterns used to insert it, can leave tons of wasted space inside the file, or AFAIK even chunks of previously deleted data.
- darkpuma 8y ago>"As much as I love SQLite, and while it is open source, it is a single implementation that AFAIK has no published open specification. The only way to read an SQLite file is using SQLite, and in that respect, it is for many users just as closed as wrapping something in a word document." That's an extreme position to take, particularly since the SQLite code is public domain. Furthermore it's one of the formats recommended by the Library of Congress for archival/data preservation: https://www.loc.gov/preservation/resources/rfs/data.html https://www.loc.gov/preservation/resources/rfs/data.html https://www.sqlite.org/locrsf.html https://www.sqlite.org/locrsf.html
- _wmd 8y ago> The only way to read an SQLite file is using SQLite This part unfortunately isn't a position, it's absolute. It's hard to imagine a situation where as a developer we would not have access to a C runtime or for any reason whatsoever would not be able to use SQLite, but the hard dependency on its code is real, and represents a real hazard in the wrong environment. A super easy example would be parsing data on say, a tiny microcontroller on an IOT device. This can start to hurt quickly: > Compiling with GCC and -Os results in a binary that is slightly less than 500KB in size Open formats at least give you the option of implementing whatever minimal hack is necessary to finish your job without say, introducing some intermediary to do an upfront conversion, and at least for this reason SQLite cannot really be considered a perfectly universal format
- i_feel_great 8y agoI keep harping on about this at work - a sqlite file carries its schema with it so you can inspect it. With foreign keys, notnull, check and other constraints you can easily make out what the data is about. There is a driver in every language it seems, and if not the docs are very good so if you are handy with FFI it is easy to build one. It can be far more compact than XML (gulp! SOAP) with the equivalent amount of data as the amount of data gets larger. Being a single file, it can be sent over the wire like any other. And you use SQL to interface with it. Currently building software for the ATO (Single Touch Payroll) which uses SBR (https://en.wikipedia.org/wiki/Standard_Business_Reporting https://en.wikipedia.org/wiki/Standard_Business_Reporting). The SBR project is listed as having on-going problems, and has cost the ATO ~$AUD1b to date (https://en.wikipedia.org/wiki/List_of_failed_and_overbudget_custom_software_projects https://en.wikipedia.org/wiki/List_of_failed_and_overbudget_...). One of the reasons cited is that it uses XBRL (https://en.wikipedia.org/wiki/XBRL https://en.wikipedia.org/wiki/XBRL). Now imagine if it used sqlite...
- jamougha 8y agoThere's a fairly large ecosystem of tools for creating and processing financial data in XBRL which doesn't exist for SQLite. All the Big 4 handle XBRL already - I know because I write the software they use. It's quite straightforward to take a Word document, for example, and turn it into XBRL; we even use machine learning to automatically tag the tables. I can easily imagine how painful it is for you to process XBRL from scratch, but it's not crazy to exploit the existing infrastructure. Of course if you give a project to IBM I wouldn't be surprised if it costs a billion dollars, especially given they know roughly nothing about XBRL...
- ineedasername 8y agoI'd never heard of XBRL, looks very interesting. It seems it's primarily used in financial reporting environments though. Is it suitable for general purpose reporting as well?
- candiodari 8y ago
- 9712263 8y agoThe problem is data corruption. If a CSV is corrupted, then I could at least parse part of the data. For a corrupted SQL file, I'm done. Also, diff is not working for binary format, and it is more difficult to trace change for SQL format. In this sense, I prefer a SQL dump file.
- quickthrower2 8y agoWhat is the source of the corruption. Unlikely to be disks nowadays but people should take backups of things. Very unlikely to be a SQLite buggy write but again a backup could save you. Worse case I’m sure there are tools to recover a corrupted file.
- darkpuma 8y agoThere are plenty of things that can cause it: https://www.sqlite.org/howtocorrupt.html https://www.sqlite.org/howtocorrupt.html
- ineedasername 8y agoNice of them to publish a guide for those who enjoy corrupted data :) I particularly liked Fake capacity USB sticks. I didn't even realize that was a think. I can't even...
- JoeAltmaier 8y agoOh this goes way back. The first SSD drives from Hong Kong advertised double or quadruple their capacity - you didn't find out until you tried to write the N+1th block and it overwrote the 0th block. Back in the 90's?
- 0x0 8y agoSqlite's sqldiff might be an okay replacement for diff in many cases - https://sqlite.org/sqldiff.html https://sqlite.org/sqldiff.html
- johannes1234321 8y agoAn issue there is the entry level. For CVS a common tool to read and analyse the date exists: Excel. For sqlite there's hardly an approvable tool for analysis of the data. Excel is driving the world.
- beefield 8y agoWell, my personal rule is to never touch csv with excel, because depending on the locales, most of the time excel breaks something in the file. (Of course, in theory I might make a flawless data type mapping when importing the file to excel, but unfortunately that happening is quite rare...)
- johannes1234321 8y agoThis is a concern and there are lots of issues (also think about proper escaping etc.) but Excel is ubiquitous outside hackernews's demography.
- flohofwoe 8y agoI'd rather have a proper text format which I can inspect without a highly specialized tool. In 50 or 500 years it will be hard to hunt down the right tools to open an SQLite file, build SQLite from sources (if they still exist), or reverse-engineer the file format. For a text file you only need to reverse-engineer the ASCII encoding (or maybe UTF-8).
- agumonkey 8y agoReminds me I have to extract data from an 9x day windows application. It's a jetdb that refuses to load in the first viewers I could find. Made me wish it was sqlite (although it didn't exist at the time..)
- goldfeld 8y agoWhat about hdf5? I'm starting to use it as an in-memory, parsed cache representation for data stored in like a plaintext file document database. With python's PyTables and visidata acts as the independent data explorer app.
- pmart123 8y agoHdf5 files are good if the data is write once, and numeric unless things have changed over the last fives years. From my recollection, you can’t append data so you have to rewrite the whole table and text fields are fixed width.
- rabidrat 8y agoI like HDF5 because it's compatible with everything, but it's a bit more difficult to get started with than other modern formats. Partly this is due to design by committee including everything, and the APIs follow suit. With some more modern APIs I think it could come back as an archival format.
- ineedasername 8y agoThat is a truly fantastic idea. Especially if Excel added file format support for it directly to open it within excel with a double click, one table per sheet within the workbook. I know, not the ideal way to use SQLite, but that would ensure easy adoption by the average person that doesn't have SQL or relational database knowledge. (And would allow them to get a bit of that relational knowledge via power query) Some might think Access would be the more obvious choice, but for the average user Access is not an accessible tool (we don't even install it as part of the Office suite where I work) and they're much more comfortable in Excel.
- your-nanny 8y agoRead only, or Excel would have to enforce constraints. But, yes, would be nice
- m_sahaf 8y agoI've probably said this around here before, but I wish people trade SQLite files instead of Excel workbooks. Excel (and the like) can operate on top of SQLite. This way we have data portability, data integrity, and ease of data access.
- mbrumlow 8y agoWhat size restrictions are you worried about ? *Note I work with software that regularly stores terabytes of data in sqlite.
- anonymouzz 8y agoHeh I was thinking that > 1TiB of data in an sqlite db is a bad idea :). How do you process that? The restrictions I had in my mind come from that it's infeasible to concurrently process a large dataset in mapreduce/bigquery style.
- mbrumlow 8y agoThere are many different concurrence modules for sqlite. I can't see why you would have any problems with what you describe. The systems I work with use it for backup data. We have many readers and writes. Some of those export a iscsi daemon that represents a block device from the backup that is then booted form.
- mbrumlow 8y agospelling :(