5 ms·

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?

by gaius 9y ago

scrollaway 9y ago
https://github.com/dinedal/textql#key-differences-between-textql-and-sqlite-importing https://github.com/dinedal/textql#key-differences-between-te...
TAForObvReasons 9y ago
It's discussed in the README: https://github.com/dinedal/textql#key-differences-between-textql-and-sqlite-importing 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 9y ago
  #!/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 9y ago
    Erm, people don't have rc installed though. I had completely forgotten it existed, to be honest.
    - codemac 9y ago
      OK, thanks for the heads up.
lowboy 9y ago
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 9y ago
  But it is SQLite under the covers? A better title then would be an SQLite wrapper for use in shell pipelines.
  - bayonetz 9y ago
    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 9y ago
      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 9y ago
        Sure, but it doesn’t need to be in the title
    - Too 9y ago
      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.
  - 9y ago
jssmith 9y ago
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 https://sqlite.org/vtab.html), which would avoid the loading step and perhaps make streaming processing possible.
- MR4D 9y ago
  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 9y ago
    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 9y ago
      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.
    - luckydata 9y ago
      I think this might be helpful context: https://calcite.apache.org/docs/stream.html https://calcite.apache.org/docs/stream.html
naveen99 9y ago
You skipped the line to create the bare table first in SQLite: https://www.sqlite.org/cvstrac/wiki?p=ImportingFiles https://www.sqlite.org/cvstrac/wiki?p=ImportingFiles
- banku_brougham 9y ago
  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 9y ago
    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.

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?

by gaius 9y ago

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?

scrollaway 9y ago

https://github.com/dinedal/textql#key-differences-between-textql-and-sqlite-importing https://github.com/dinedal/textql#key-differences-between-te...

TAForObvReasons 9y ago

It's discussed in the README: https://github.com/dinedal/textql#key-differences-between-textql-and-sqlite-importing 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 9y ago
#!/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 9y ago
  Erm, people don't have rc installed though. I had completely forgotten it existed, to be honest.
  - codemac 9y ago
    OK, thanks for the heads up.

lowboy 9y ago

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 9y ago
But it is SQLite under the covers? A better title then would be an SQLite wrapper for use in shell pipelines.
- bayonetz 9y ago
  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 9y ago
    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 9y ago
      Sure, but it doesn’t need to be in the title
  - Too 9y ago
    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.
- 9y ago

jssmith 9y ago

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 https://sqlite.org/vtab.html), which would avoid the loading step and perhaps make streaming processing possible.

MR4D 9y ago
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 9y ago
  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 9y ago
    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.
  - luckydata 9y ago
    I think this might be helpful context: https://calcite.apache.org/docs/stream.html https://calcite.apache.org/docs/stream.html

naveen99 9y ago

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

banku_brougham 9y ago
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 9y ago
  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.