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

81–90 of 95 posts

Re: TextQL: Execute SQL Against CSV or TSV

#81

Post by marcle »

badminton1 wrote: post #49
Myrmornis wrote: post #31

Earlier quoted context omitted.

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?

Rather than SQL queries, you would be using the R language. Unless you use a package like sqldf. stdin can be read just like any other file.

For what it's worth, if we define Rsql as the following script:

  #!/usr/bin/env Rscript
  library(sqldf, quietly=TRUE)
  statement
then we can call

  ps -Ao user | Rsql 'select USER, count(*) as nprocesses from a group by USER limit 5'
to get the output

          USER nprocesses
  1      avahi          2
  2     colord          1
  3     daemon          1
  4     marcle        136
  5 messagebus          1

Re: TextQL: Execute SQL Against CSV or TSV

#82

Post by kamac »

_mhr_ wrote: post #77
kamac wrote: post #48

Ha. 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. [1]: https://github.com/kamac/AskXML

This is a great idea. There are so many APIs and text dumps from websites and databases that are XML, and it would be nice to be able to query them with regular relational operations. I'll be following your project! I will say that I believe a streaming backend would be more suitable, since most of the XML dumps I'm thinking of are humongous and won't always fit in memory.

Thanks! About fitting into memory; the way the library currently works should technically work even on very big files, as the XML is iteratively converted into a SQL database. You can choose whether you want that database to be in a file or in memory. The only thing that could break right now is synchronizing back to XML after having made changes with UPDATE or INSERT statements, because I'm not paging results.

Re: TextQL: Execute SQL Against CSV or TSV

#84

Post by yehosef »

chrisweekly wrote: post #67

Does this have any benefits over lnav[1]? [1] https://www.lnav.org -- a feature-rich, powerful, flexible "mini-ETL" with an embedded sqlite engine -- is one of my all-time favorite CLI tools that somehow remains under the radar

Thanks for the link - looks cool!

Re: TextQL: Execute SQL Against CSV or TSV

#86

Post by Myrmornis »

marcle wrote: post #81
badminton1 wrote: post #49

Earlier quoted context omitted.

Rather than SQL queries, you would be using the R language. Unless you use a package like sqldf. stdin can be read just like any other file.

For what it's worth, if we define Rsql as the following script: #!/usr/bin/env Rscript library(sqldf, quietly=TRUE) statement then we can call ps -Ao user | Rsql 'select USER, count(*) as nprocesses from a group by USER limit 5' to get the output USER nprocesses 1 avahi 2 2 colord 1 3 daemon 1 4 marcle 136 5 messagebus 1

Cool! Thanks.

Re: TextQL: Execute SQL Against CSV or TSV

#87

Post by michaelmior »

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

Although csvsql is great, the real reason I love csvkit is that most of the tools feel like standard *nix tools that just operate on CSV files (csvcut, csvgrep, etc.)

Re: TextQL: Execute SQL Against CSV or TSV

#88

Post by Myrmornis »

goatlover wrote: post #61
Myrmornis wrote: post #31

Earlier quoted context omitted.

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?

Why would you be running SQL queries in R, a language with built-in support for handling tabular data?

I was asking because the topic of this discussion is specifically running SQL queries (that's what TextQL does).

But, there certainly are good reasons for using SQL in R. Packages like sqldf exist, so their authors would probably be able to give the best answer to your question.

Some reasons:

- SQL is a very widely-known DSL for working with tabular data, so it may make sense to make that interface available within R.

- For certain operations (e.g. specific types of joins) it may be more natural / easier to express the operation in SQL.

- For large data sets, some operations in R have large memory footprints, and doing the operations in a database may have lower memory requirements.

Re: TextQL: Execute SQL Against CSV or TSV

#89

Post by Corrado »

One thing that I would like to add to my projects is the animated GIF shell. I've looked at several ways to create them but they all seem really clunky and force me to type correctly the first time. What do people use to create these helpful animations?
Post reply on HN