HN Explorer

94 comments

[ 2.8 ms ] story [ 160 ms ] thread
aembleton
This is cool. How does it work? I'd guess cleaning the data, and copying to SQL Lite?
hk__2
It imports everything in an in-memory SQLite database.
ConceitedCode
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
[deleted]
(comment deleted)
jillesvangurp
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.

michaelmior
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.)
striking
For anyone looking to do this with plain SQLite, one can import a CSV by running (in the REPL)

    .mode csv
    .headers on
    .import my.csv tablename
This does look extremely convenient, though; being able to use UNIX pipes will be a huge improvement to my workflow.
[deleted]
(comment deleted)
gaius
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?
TAForObvReasons
It's discussed in the README: https://github.com/dinedal/textql#key-differences-between-te...

> 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.

codemac 

    #!/usr/bin/env rc
    
    row_headers=''
    output_mode=column
    
    fn usage {
      echo $0 usage
      echo   -o <sqlite output mode> '#' which output mode to use
      echo   -r                      '#' if present, output row headers
      echo   -h                      '#' this message
    }
    
    while(~ $1 -*) {
      switch($1) {
        case -o
          output_mode=$2
          shift
        case -r
          row_headers='.headers on'
        case -h
          usage
          exit 0
        case *
          echo Bad args: $*
          usage
          exit 1
       }
       shift
    }
    
    test $#* -ne 1 && echo Wrong number of arguments && usage && exit 1
    
    sql_statement=$1
    sqlite_bin=''
    { which sqlite > /dev/null >[2=1] && sqlite_bin=sqlite } || { which sqlite3 >/dev/null >[2=1] && sqlite_bin=sqlite3 }
    
    table_name=atable
    stdin_file=/proc/$pid/fd/0
    {
      echo .mode csv
      echo .import $stdin_file $table_name
      echo $row_headers
      echo .mode $output_mode
      echo $sql_statement
    } | $sqlite_bin
yoklov
Erm, people don't have rc installed though. I had completely forgotten it existed, to be honest.
codemac
OK, thanks for the heads up.
lowboy
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.

gaius
But it is SQLite under the covers? A better title then would be an SQLite wrapper for use in shell pipelines.
TAForObvReasons
That's a silly complaint. It's a tool powered by SQLite. Under your extended definition, tools like web browsers (which use SQLite to power various things) would be SQLite wrappers.
decebalus1
I don't see it as a complaint but as a piece of feedback and if you're the author then you may want to revisit your attitude.
always_good
Nah, people say such ridiculous garbage that they need to be called out. Especially here where people seem to take delight in disparaging your work in the tiniest way they can think of.

I don't think OP needs to pussy foot around some stranger on an internet message board. It's free shit.

gaius
If I wrote 10 lines of JS and claimed credit for the whole web browser, more like
mulmen
My favorite C wrapper for scripting is Perl.

The Readme is describing the differences in importing. I'm not sure how your distinction makes this any more clear.

tejasmanohar
"SQLite in your shell" would be a catchy title :-)
bayonetz
In this case, It’s what it does that matters, not how it does it. I don’t care if SQLLite is powering it. Maybe the author would want to swap out the SQLLite engine with something else in the future too. Then it’s even better that they didn’t advertise the link to SQLLite as a selling point.
h1d
It does actually matter what's running under, so you know what to expect from its behavior and if one doesn't exactly need the few features textQL offers exclusively, you would think to just use SQLite without random layers on top.
sockgrant
Sure, but it doesn’t need to be in the title
Too
SQL dialects have significant differences. You can't simply change the underlying engine without breaking backward compatibility. It's also good to redirect documentation to official sqllite docs instead of writing a 1000 page manual yourself.
jssmith
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 the loading step and perhaps make streaming processing possible.

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

ams6110
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.
barrkel
Many relational operations don't require a whole stream to compute, and many of those that do don't need it all at once.

Projection (mapping), a join against a fully loaded other side as well as filtering work.

Aggregation can consume an indefinite stream with limited working set if the cardinality of the grouping key isn't large.

And of course you can combine these in nested and unioned operations, computing across multiple indefinite streams concurrently and with limited working set.

It would be tricky to make work effecively without hinting for things like joins, for sure; join order is one of the hardest bits a query engine optimizes.

orionblastar
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.

naveen99
You skipped the line to create the bare table first in SQLite: https://www.sqlite.org/cvstrac/wiki?p=ImportingFiles
banku_brougham
Link is to a page that says obsolete since 2009. I wasn't able to find similar page in the docs, can you help?
naveen99
Help with what ? If you want to import a csv file into SQLite, just follow the "obsolete" docs which probably still work. If you want help finding a similar page in the docs, just read all the docs or download them and grep. It may or may not exist.
kibwen
If 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
fnl
And check out "Motivation" in xsv's readme (at the end) to understand why it might be a better choice than most other tools suggested here, particularly those that go through Python and sqlight.

Also, while I like/applaud Miller (also referenced), xsv has a simpler UI that only handles CSV.

banku_brougham
`xsv` looks great, I will try it out.

My main hesitation: Its another DSL to learn, there is a big benefit to keeping this kind of work within the SQL world for me (or R, python, etc depending on the user).

On the other hand, getting table stats and frequencies right from the shell is a huge time saver over SQL.

th0ma5
Apache MetaModel is great for this in Java.
daamien
PostgreSQL does this out of the box with :

  CREATE EXTENSION file_fdw;
  CREATE SERVER import FOREIGN DATA WRAPPER file_fdw;
  CREATE FOREIGN TABLE foo (
    col1 text,
    col2 text,
    ...
  ) SERVER import 
  OPTIONS ( filename '/path/to/foo.csv', format 'csv' );
  SELECT col1 FROM foo WHERE col2='x';
techtalsky
However this is something you can put in /use/local/bin and use alongside other command line tools with pipes
Myrmornis
Which is exactly why a convenient alternative was desired.
banku_brougham
Having to define the schema is the major hangup for me in my workflow. I would prefer a wrapper that could run within unix pipelines around arbitrary text data files, but they all use SQLite.

I personally have been using harelba’s q (the un-googleable utility), which is just fine.

It would be great not to shift gears into SQLite syntax and date formatting all the time. Does anyone know of a similar tool that runs over postgres?

chrisweekly
> "prefer a wrapper that could run within unix pipelines around arbitrary text data files"

That sounds like [lnav](https://www.lnav.org).

For queries, it does use SQLite under the hood, but before querying your "arbitrary text data" files, you can use regex to define a custom format (eg with named groups and back references), providing structure against which standard SQL is an ideal tool to query.

It's simpler than I'm probably making it sound. Highly recommended.

cplat
Apache Drill (https://drill.apache.org/) is also a great alternative. Although advertised as a query engine for Hadoop, it works perfectly fine on local CSV and JSON files.
__david__
I usually appreciate the animated gif screenshot (especially for curses based programs), but this particular animated gif would have been better off as just plain text—there's too much interesting data that can't be copied, it's real hard to go back to look at something that you missed and it's frustrating to have to wait while fake typing happens. I really wish there was just a transcript of the gif that I could read at my own pace.
partycoder
I use R for this.
Myrmornis
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?
partycoder
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
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
goatlover
Why would you be running SQL queries in R, a language with built-in support for handling tabular data?
Myrmornis
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.

ravinizme
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/

ams6110
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.

Nitrado
ClickHouse ships with a command line tool which does this (without the actual database server):

    ps aux | tail -n +2 | awk '{ printf("%s\t%s\n", $1, $4) }' | \
        clickhouse-local -S "user String, mem Float64" \
            -q "SELECT user, round(sum(mem), 2) as memTotal FROM table GROUP BY user ORDER BY memTotal DESC FORMAT Pretty"

    
    ┏━━━━━━━━━━┳━━━━━━━━━━┓
    ┃ user     ┃ memTotal ┃
    ┡━━━━━━━━━━╇━━━━━━━━━━┩
    │ clickho+ │      0.7 │
    ├──────────┼──────────┤
    │ root     │      0.2 │
    ├──────────┼──────────┤
    │ netdata  │      0.1 │
    ├──────────┼──────────┤
    │ ntp      │        0 │
    ├──────────┼──────────┤
    │ dbus     │        0 │
    ├──────────┼──────────┤
    │ nginx    │        0 │
    ├──────────┼──────────┤
    │ polkitd  │        0 │
    ├──────────┼──────────┤
    │ nscd     │        0 │
    ├──────────┼──────────┤
    │ postfix  │        0 │
    └──────────┴──────────┘
Has the advantage of being really fast.
spullara
the additional requirement to set up the schema is kind of onerous.
networked
Shameless plug: Sqawk can do nearly the same without you defining a schema.

  $ ps aux | sqawk -output table \
                   'select user, round(sum("%mem"), 2) as memtotal
                    from a
                    group by user
                    order by memtotal desc' \
                   header=1
  ┌────────┬────┐
  │dbohdan │67.1│
  ├────────┼────┤
  │  root  │3.5 │
  ├────────┼────┤
  │ avahi  │0.0 │
  ├────────┼────┤
  │ daemon │0.0 │
  ├────────┼────┤
  │message+│0.0 │
  ├────────┼────┤
  │ nobody │0.0 │
  ├────────┼────┤
  │  ntp   │0.0 │
  ├────────┼────┤
  │ rtkit  │0.0 │
  ├────────┼────┤
  │ syslog │0.0 │
  ├────────┼────┤
  │ uuidd  │0.0 │
  ├────────┼────┤
  │whoopsie│0.0 │
  └────────┴────┘
Link: https://github.com/dbohdan/sqawk
[deleted]
(comment deleted)
clatan
we should be aiming to kill SQL as a language
walshemj
Why? Not surprised you got down voted but I am curious?
orionblastar
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.
_mhr_
What would you suggest we replace it with?
zmmmmm
This 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!
hk__2
I’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.

[1]: https://github.com/BatchLabs/charlatan#charlatan

kamac
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

_mhr_
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.
kamac
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.
rhacker
Apache Spark can do this too, similar to the Apache Drill comment. This thread is starting to feel like Stack Overflow a bit.
mekazu
If only when googling “sql on csv” this comments page would show up first.