10 ms·
TextQL: Execute SQL Against CSV or TSV
- clatan 9y agowe should be aiming to kill SQL as a language
- walshemj 9y agoWhy? Not surprised you got down voted but I am curious?
- orionblastar 9y agoIt 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_ 9y agoWhat would you suggest we replace it with?
- aembleton 9y agoThis is cool. How does it work? I'd guess cleaning the data, and copying to SQL Lite?
- hk__2 9y agoIt imports everything in an in-memory SQLite database.
- ConceitedCode 9y agoThis 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/ 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 9y ago[deleted]
- donarb 9y agoPython's pandas library can also do sql-like queries against csv data. https://pandas.pydata.org/pandas-docs/stable/comparison_with_sql.html https://pandas.pydata.org/pandas-docs/stable/comparison_with...
- jillesvangurp 9y agoWas 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 9y agoAlthough 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 9y agoFor 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 9y ago[deleted]
- mingodad 9y agoTo have an idea of what can be done with sqlite look at this solution to the Hashcode 2018 https://github.com/mingodad/sqlite3-hashcode-2018 https://github.com/mingodad/sqlite3-hashcode-2018 using only sqlite3 command line.
- gaius 9y agoIn 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 agohttps://github.com/dinedal/textql#key-differences-between-textql-and-sqlite-importing https://github.com/dinedal/textql#key-differences-between-te...
- TAForObvReasons 9y agoIt'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
- 9y ago
- kibwen 9y agoIf 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 https://github.com/BurntSushi/xsv
- jhoechtl 9y agoAnother great tool to wrangle csv file is miller http://johnkerl.org/miller/doc/ http://johnkerl.org/miller/doc/
- walshemj 9y agoYou can also use log parser lizard to parse CSV files.
- fnl 9y agoAnd 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 9y ago`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 9y agoApache MetaModel is great for this in Java.
- daamien 9y agoPostgreSQL 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';
- daamien 9y ago... and with dozens of other data sources: https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers
- techtalsky 9y agoHowever this is something you can put in /use/local/bin and use alongside other command line tools with pipes
- Myrmornis 9y agoWhich is exactly why a convenient alternative was desired.
- banku_brougham 9y agoHaving 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 9y ago> "prefer a wrapper that could run within unix pipelines around arbitrary text data files" That sounds like [lnav](https://www.lnav.org 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 9y agoApache Drill (https://drill.apache.org/ 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__ 9y agoI 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.
- berta 9y agoHow is this different from https://github.com/harelba/q https://github.com/harelba/q
- lowleveldesign 9y agoIf you are on Windows you may consider using Log Parser Studio [1]. It is a GUI over logparser.exe which understands a lot of various file formats and uses SQL to query them [2]. [1] https://gallery.technet.microsoft.com/office/Log-Parser-Studio-cd458765 https://gallery.technet.microsoft.com/office/Log-Parser-Stud... [2] https://en.m.wikipedia.org/wiki/Logparser https://en.m.wikipedia.org/wiki/Logparser
- noveltyaccount 9y agoCame here to say this. Log parser is amazing. Check out Log Parser Lizard for a good IDE.
- pX0r 9y agoWhile dealing with CSV files using SQL statements is cool and all, SQL queries are still SQL queries.
- IMTDb 9y agohttps://en.wikipedia.org/wiki/Truism https://en.wikipedia.org/wiki/Truism
- partycoder 9y agoI use R for this.
- Myrmornis 9y agoCan 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 agoUsing 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 agoRather 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 agoFor 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 9y agoCool! Thanks.
- goatlover 9y ago
- kbenson 9y agoIn the past, I've used fsql[1] when I needed this. This looks interesting though. 1: https://metacpan.org/pod/distribution/App-fsql/bin/fsql https://metacpan.org/pod/distribution/App-fsql/bin/fsql
- ravinizme 9y agoSeem 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/ http://harelba.github.io/q/
- ams6110 9y agoYes, 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 9y agoClickHouse 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 9y agothe additional requirement to set up the schema is kind of onerous.
- networked 9y agoShameless 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 https://github.com/dbohdan/sqawk
- deleted 9y ago[deleted]
- zmmmmm 9y agoThis 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 9y agoI’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 https://github.com/BatchLabs/charlatan#charlatan
- kamac 9y agoHa. 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 https://github.com/kamac/AskXML
- _mhr_ 9y agoThis 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 9y agoThanks! 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 9y agoApache Spark can do this too, similar to the Apache Drill comment. This thread is starting to feel like Stack Overflow a bit.
- mekazu 9y agoIf only when googling “sql on csv” this comments page would show up first.
- blacksqr 9y agoSqawk [1] is an Awk-like program that uses SQL and can combine data from multiple files. It is powered by SQLite. [1] https://github.com/dbohdan/sqawk https://github.com/dbohdan/sqawk
- otoburb 9y agoI like VisiData[1][2] for CSV/TSV data exploration. textql is a good command-line follow-up to run quick direct queries against the data without needing to see visual/spatial structure. [1] https://jsvine.github.io/intro-to-visidata/ https://jsvine.github.io/intro-to-visidata/ [2] Previous HN VisiData thread https://news.ycombinator.com/item?id=16515299 https://news.ycombinator.com/item?id=16515299
- emmelaich 9y agoLooks pretty good! Just for historical interest, similar things have been done before. e.g.http://quisp.sourceforge.net/shsqlhome.html http://quisp.sourceforge.net/shsqlhome.html See also Facebook's osquery of course. Turns even complex structures into sql tables -- helped by Augeas.net.
- chrisweekly 9y agoDoes this have any benefits over lnav[1]? [1] https://www.lnav.org 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
- bobivl 9y agoThere is also BigBash [0] that converts an Sql statement to a bash one-liner (using sed, grep, awk,...) which can then run to execute the query. The advantage is that you can let it run on very large file(s) because of the streaming nature of linus bintools. [0] http://bigbash.it http://bigbash.it or the corresponding Github repo.
- ilitirit 9y agoI'm pretty sure many people have written or attempted to write similar tools at least once before. It's as simple as programmatically importing to an in-memory sqlite instance and then executing SQL queries.
- srhngpr 9y agoI'm a big fan of SQL Notebook [1]. It is open source and has a great UI to quickly work with a number of importable formats (CSV, Excel, JSON) as well as server-based DBs. [1] https://sqlnotebook.com/ https://sqlnotebook.com/
- Corrado 9y agoOne 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?
- owlninja 9y agoThese responses are so HackerNews. Everyone asking 'why not use x project?', and each x is different...
- daotoad 9y agoYawn. We've had this in Perl for decades using the DBI Shell (dibsh). DBI is a common database interface. To connect to a data source, one uses an appropriate driver (by convention, in the DBD name space, such as DBD::Pg or DBD::MySQL) to get a db handle that can run SQL commands. There are a lot of drivers for things like CSV files, in memory database, etc. Plus you can access all this programmatically and not just through the dbish command. Since this is a central part of Perl infrastructure, there are a ton of tools that extend the DBI, from a variety of ORMs like DBIx::Class, to data export and even DDL delta management. Generalized Database Interface: https://metacpan.org/pod/DBI https://metacpan.org/pod/DBI DBI Shell program: https://metacpan.org/pod/DBI::Shell https://metacpan.org/pod/DBI::Shell CSV driver: https://metacpan.org/pod/DBD::CSV https://metacpan.org/pod/DBD::CSV