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.

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.

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 <- commandArgs(TRUE)
  a <- read.table(file("stdin"), header=TRUE)
  print(sqldf(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.