we should be aiming to kill SQL as a language
TextQL: Execute SQL Against CSV or TSV
41–50 of 95 posts
Re: TextQL: Execute SQL Against CSV or TSV
#42This is one of those problems that is just hard enough that everyone feels it is useful to solve and just easy enough that everyone can solve it. Hence the dozens or even hundreds of independent solutions. For some reason I never end up using any of them!
Re: TextQL: Execute SQL Against CSV or TSV
#43I’ve worked on a similar tool in the past called Charlatan [1] (no I didn’t choose that name), but it implemented its own SQL-like language and was extensible to support any file type.
Its key difference with TextQL and similar alternatives is it works on streams instead of importing everything in a SQLite then querying it. It seems strange to read a whole file in memory then perform a `SELECT` query on it when you could just run that query while reading the file. That means a much lower memory footprint and faster execution, but on the other hand you can only use the subset of SQL that’s implemented.
Re: TextQL: Execute SQL Against CSV or TSV
#44This is cool. How does it work? I'd guess cleaning the data, and copying to SQL Lite?
It imports everything in an in-memory SQLite database.
Re: TextQL: Execute SQL Against CSV or TSV
#45I use R for this.
Can you show an example of how to use R to read tabular data on stdin (e.g. in a UNIX pipe line) and perform SQL queries?
Using ideas contained in this script you can execute tidyverse commands with R on the command line:
https://github.com/jeroenjanssens/data-science-at-the-comman...
I was not able to commit its options to long term memory, so I've built a bash script that contains a pipe of commands, including Rio.
Re: TextQL: Execute SQL Against CSV or TSV
#46If you work with CSV a lot, then also check out xsv, "a command line program for indexing, slicing, analyzing, splitting and joining CSV files", from Andrew Gallant (burntsushi of Go and Rust fame): https://github.com/BurntSushi/xsv
Another great tool to wrangle csv file is miller http://johnkerl.org/miller/doc/
You can also use log parser lizard to parse CSV files.
Re: TextQL: Execute SQL Against CSV or TSV
#47we should be aiming to kill SQL as a language
Why? Not surprised you got down voted but I am curious?
Re: TextQL: Execute SQL Against CSV or TSV
#48Ha. Shameless plug, but just about a week ago I started to work on a somewhat analogous thing. Executing SQL on XML [1]. I came up with this idea after trying to use stackoverflow data dump and finding out it was stored as XML. I wanted to run queries with LIKE operator on it.
Re: TextQL: Execute SQL Against CSV or TSV
#49Re: TextQL: Execute SQL Against CSV or TSV
#50Apache Spark can do this too, similar to the Apache Drill comment. This thread is starting to feel like Stack Overflow a bit.