4 ms·
What do you mean? I don't think this process ends up with less info than the CSV file format itself. Short of doing that, cleaning very dirty data has no satis
by rscho 4y ago
What do you mean? I don't think this process ends up with less info than the CSV file format itself.
Short of doing that, cleaning very dirty data has no satisfactory solution, I think. Optional typing is a nice middle ground between untyped and slow (R, Python) or strictly typed and tedious (all other DB engines).
- dekhn 4y agoMaybe I misunderstand, but if you take a data type that is a union of two datatypes (string and int) and push it into somethign that converts all the data to strings, you lose the information of whether a string that looks like an int was originally a string or int. Maybe instead you mean to use the database functionality to identify problems like that in the original source data. If somebody hands me a string "11101100101001" I would not attempt to interpret it by parsing it into a binary number, but I think that's because I really like strong, simple typing.
- at_a_remove 4y agoLike I said earlier, I don't know what I am getting. Strong, simple typing is premature at this juncture.
- dekhn 4y agoGot it, it's data exploration not a formal ETL pipeline code.
- at_a_remove 4y agoI've done it for formal ETL pipeline code, too. I make it tighter, but not too tight. Sadly, the spec given me was very ... optimistic? Bright-eyed? Many things that were not supposed to happen did happen, data that shouldn't have gotten near me did, and so on. SQLite swallowed it and kept on truckin', because when it comes to ETL, one dumb field entry shouldn't stop the show. Catch it, yes, deal with it when you note it in the logs, absolutely, but one must continue, and for that SQLite is most excellent.
- ratrocket 4y agoI can't answer for the person you're commenting to, but I use sqlite in a similar way and the loss of information isn't an issue. When you're exploring data, just trying to make sense of what someone has sent you (say, migrating data from an existing system to a new system) you probably have a pretty good idea if the column is supposed to have integers or strings in it. In my own use of it, it's very unlikely that the loss of information will cause a problem. Eventually the data will be type-checked -- not in sqlite (I haven't experimented with the new-ish "strict tables" feature yet), but in my case the data will eventually flow through a typed programming language. Anyway, different ways to use the tool, for sure! And in some cases one would definitely need to be attuned to the issue you're raising. In the kind of situation I'm thinking of the data is usually so dirty anyway, a bit of string->integer conversion won't hurt (probably).
- dekhn 4y agoI get it, you don't have typing control over your data exchange. somebody might send you an unquoted-string CSV with multiple data types in the same column: id,location,name 1,90210,Tori Spelling 2,Schitt's Creek,Eugene Levy and you use sqlite to quickly explore the loaded data to check the set of types used in a column, and maybe even glean what the meaning is.
- ratrocket 4y agoExactly. The thing I'm talking about is necessarily a semi-manual process (data exploration, usually prior to migrating data from one system to another). In the example you give, when you've done all the exploration you need, there's a program interpreting that CSV that ensures the location column values are strings. At least, that's how I do it!
- rscho 4y ago> Maybe instead you mean to use the database functionality to identify problems like that in the original source data. Yes, that's exactly my use case.