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

TextQL: Execute SQL Against CSV or TSV

github.com

Post reply on HN
Print view View original

51–60 of 95 posts

Re: TextQL: Execute SQL Against CSV or TSV

#51

Post by jillesvangurp »

ConceitedCode wrote: post #3

This looks very simple and easy to use. Are there any differences with using this instead of CSVKit? https://csvkit.readthedocs.io/en/1.0.3/ It includes a tool called csvsql. Example usage - csvsql --query "select name from data where age > 30" data.csv > new.csv

Was wondering the same. I've been using csvkit a lot lately. This looks like a subset of functionality using more or less the same approach. Csvsql uses sqlite under neath and you can do some nice things with this like joins, using sql functions, etc. There is also stuff like amazon athena that allows you to do similar things in s3 at scale.

Csvkit is great with pipes and you can also easily convert between csv tsv and even stuff like json.

Re: TextQL: Execute SQL Against CSV or TSV

#52

Post by tejasmanohar »

gaius wrote: post #11
lowboy wrote: post #8

Earlier quoted context omitted.

From the README: Key differences between textql and sqlite importing sqlite import will not accept stdin, breaking unix pipes. textql will happily do so. textql supports quote escaped delimiters, sqlite does not. textql leverages the sqlite in memory database feature as much as possible and only touches disk if asked.

But it is SQLite under the covers? A better title then would be an SQLite wrapper for use in shell pipelines.

"SQLite in your shell" would be a catchy title :-)

Re: TextQL: Execute SQL Against CSV or TSV

#54

Post by naveen99 »

gaius wrote: post #5

In SQLite you can just do sqlite> .import myfile.csv mytable sqlite> select ... What does this tool give you that SQLite doesn't do out of the box?

You skipped the line to create the bare table first in SQLite: https://www.sqlite.org/cvstrac/wiki?p=ImportingFiles

Re: TextQL: Execute SQL Against CSV or TSV

#56

Post by otoburb »

I like VisiData[1][2] for CSV/TSV data exploration. textql is a good command-line follow-up to run quick direct queries against the data without needing to see visual/spatial structure.

[1] https://jsvine.github.io/intro-to-visidata/

[2] Previous HN VisiData thread https://news.ycombinator.com/item?id=16515299

Re: TextQL: Execute SQL Against CSV or TSV

#57

Post by ams6110 »

ravinizme wrote: post #36

Seem very similar to q q is out there for years, with a very large community. q is a command line tool that allows direct execution of SQL-like queries on CSVs/TSVs (and any other tabular text files). http://harelba.github.io/q/

Yes, I use q all the time for slicing and dicing delimited files. The only problem I have with it is that the name can make it a littler harder to find if you don't remember the repo.

Since q will read stdin and write CVS to stdout you can chain several queries on the command line, or use it in series with other with other commands such as cat, grep, sed, etc.

Highly recommended if you like SQL and deal with delimited files.

Re: TextQL: Execute SQL Against CSV or TSV

#58

Post by ams6110 »

MR4D wrote: post #38
jssmith wrote: post #21

Earlier quoted context omitted.

As a command-line tool it could serve as a more powerful replacement for unix utilities that I often use for simple analysis jobs: sort, uniq, wc, awk, sometimes cut and sed. I'm going to give it a try. It looks like textql loads everything into an in-memory SQLite instance, whereas I'd really like to see an approach that uses the SQLite's virtual table mechanism ( https://sqlite.org/vtab.html ), which would avoid th…

What you describe would be a super nice utility. Great for prototyping and development, as the code could be copy & pasted from such a tool into application code. The streaming would make it memory efficient, and possibly able to handle some big data - maybe not true "Big Data", but certainly 10s of gigabytes. Anyone want to take this idea into a GoFundMe site?

SQL is set-oriented. How would that work on a potentially indefinite stream, other than as a simple filter which you could just do with a tool such as awk.

Re: TextQL: Execute SQL Against CSV or TSV

#59

Post by orionblastar »

MR4D wrote: post #38
jssmith wrote: post #21

Earlier quoted context omitted.

As a command-line tool it could serve as a more powerful replacement for unix utilities that I often use for simple analysis jobs: sort, uniq, wc, awk, sometimes cut and sed. I'm going to give it a try. It looks like textql loads everything into an in-memory SQLite instance, whereas I'd really like to see an approach that uses the SQLite's virtual table mechanism ( https://sqlite.org/vtab.html ), which would avoid th…

What you describe would be a super nice utility. Great for prototyping and development, as the code could be copy & pasted from such a tool into application code. The streaming would make it memory efficient, and possibly able to handle some big data - maybe not true "Big Data", but certainly 10s of gigabytes. Anyone want to take this idea into a GoFundMe site?

Sort of a good idea to play with a small database to see how things work if it was on a SQL database.

I usually have to develop a database in Windows and Access. One more tool to work in Linux is a good idea.

I used to use awk and sed before.

Re: TextQL: Execute SQL Against CSV or TSV

#60

Post by orionblastar »

walshemj wrote: post #47
clatan wrote: post #41

we should be aiming to kill SQL as a language

Why? Not surprised you got down voted but I am curious?

It is a negative comment. We should make alternatives not kill off technologies. Even the Commodore 64 has use with the right modern add ons, now that is hacking.
Post reply on HN