Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The CSV Virtual Table

sqlite.org

Post reply on HN
Print view View original

51–52 of 52 posts

Re: The CSV Virtual Table

#51

Post by tanin »

habibur wrote: post #32

All my data are in TSV files, separated by tabs instead of comma and strings are not quoted. It makes parsing or manually editing data so much easier. You need to escape only like 5 characters \0 \n \r \\ \t and even binary data will fit in there. I was checking if sqlite's CSV supports custom separator and unquoted strings for this to work. Found it doesn't.

I built a desktop app that is based on Sqlite, and it is extended to handle other separators.

Not sure if this is what you are looking for.

You can try it out: https://superintendent.app

PS. it also loads GBs of file in 10-20s because that part is written in C.

Re: The CSV Virtual Table

#52

Post by wenc »

geokon wrote: post #48
wenc wrote: post #42

Earlier quoted context omitted.

I’m not aware of other ways to get at the material though I’m sure Widom’s website at Stanford might have some. It’s not just a size thing but also a concurrency thing. Data frames are like tiny local databases. SQL databases are like central databases that many parties read and write from. There’s no switchover point as such. A common use case is to use SQL to query a subset of data from a central database, and that…

Right! the dplyr functionality seems to match 1-to-1 to what a cursory look at SQL told me - hence my confusion as to why they reinvented the wheel instead of using an industry standard The async table access is an interesting angle - assuming you're doing mutations that probably places some constraints. But in the case of in-app databasese my SQLite that's generally not a concern. From what you've said, at the end o…

I think it's more of a case of preference and ergonomics.

For local dataframes, it's still more ergonomic to use a language's own constructs than to adopt a totally separate DSL for data manipulation. SQL is a different "language" which has its own cognitive overheads. So things like dplyr, pandas, and LINQ (in C#) were invented as language-native ways to manipulate tabular data structures without the overhead of context-switching [1]. After all, learning SQL and being good at it on top of mastering one's own programming language takes something extra.

That said, it's possible and indeed sometimes preferable to use pure SQL for local data tables. I use duckdb to do complex manipulations on dataframes in Python because it's a lot faster than Pandas (due to some columnar optimizations) and because I can express certain things more succinctly in SQL than in Pandas (it's true -- I appreciate Pandas for the achievements that it is, but its syntax can be verbose with all its .apply() and df[df[col=="abc"]] incantations).

Using SQL via sqlite to manipulate local tables confer similar benefits.

[1] To be fair, pandas, dplyr and LINQ -- by virtue of being built into their respective languages -- can do things that SQL can't (or can't do easily) because it's not constrained by some of SQL's design.

Post reply on HN