4 ms·

I use R for this.

Myrmornis 9y ago
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?
- knbknb 9y ago
  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-command-line/blob/master/tools/Rio 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.
- partycoder 9y ago
  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.
  - marcle 9y ago
    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
    - Myrmornis 8y ago
      Cool! Thanks.
- goatlover 9y ago
  Why would you be running SQL queries in R, a language with built-in support for handling tabular data?
  - Myrmornis 8y ago
    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.

I 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?

knbknb 9y ago
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-command-line/blob/master/tools/Rio 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.
partycoder 9y ago
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.
- marcle 9y ago
  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
  - Myrmornis 8y ago
    Cool! Thanks.
goatlover 9y ago
Why would you be running SQL queries in R, a language with built-in support for handling tabular data?
- Myrmornis 8y ago
  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.