Have you ever stared at a text file on the screen, hoping it would have been a database so you could ask anything you want about it? I had that feeling many times, and I've finally understood that it's not the database that I want. It's the language - SQL.
SQL is a declarative language for data, and as such it allows me to define what I want without caring about how exactly it's done. This is the reason SQL is so powerful, because it treats data as data and not as bits and bytes (and chars).
The goal of this tool is to provide a bridge between the world of text files and of SQL.
"q allows performing SQL-like statements on tabular text data, including joins and subqueries"You can use this gitter chat room for contacting me directly. I'm trying to be available at the chat room as much as possible.
- Seamless multi-table SQL support, including joins. filenames are just used instead of table names (use - for stdin)
- Automatic column name and column type detection (Allows working more naturally with the data)
- Multiple parsing modes - relaxed and strict. Relaxed mode allows to easily parse semi-structured data, such as log files.
- Standard installation - RPM, Homebrew (Mac). Debian package coming soon.
- Support for quoted fields
- Full UTF-8 support (and other encodings)
- Handling of gzipped files
- Output delimiter matching and selection
- Output beautifier
- man page when installed through the RPM package
Quick usage example: sudo find /tmp -ls | q "select c5,c6,sum(c7)/1024.0/1024 as total from - group by c5,c6 order by total desc"
Full examples and a beginner's tutorial can be found here
Current stable version is 1.3.0.
No special requirements other than python >= 2.5 are needed.
Just run brew install q.
Thanks @stuartcarnie for the initial homebrew formula
- Download the main q executable from here into a folder in the PATH.
- Make the file executable.
For Windows machines, also download q.bat here into the same folder and use it to run q.
Download the RPM here here.
Install using rpm -ivh <rpm-name>.
RPM Releases also contain a man page. Just enter man q.
Debian packaing is in progress. In the mean time install manually. See the section below.
q's basic usage is very simple:q <flags> <query>, but it has lots of features under the hood and in the flags that can be passed to the command.
Simplest execution is q "SELECT * FROM myfile" which prints the entire file.
Complete information can be found here
Some implementation details can be found here
- No checks and bounds on data size
- Spaces in file names are not supported yet. I'm working on it.
- Faster reuse of previous data loading
- Allow working with external DB
- Real parsing of the SQL, allowing smarter execution of queries.
- Smarter batch insertion to the database
- Full Subquery support (will be possible once real SQL parsing is performed)
- Provide mechanisms beyond SELECT - INSERT and CREATE TABLE SELECT and such.
Some information regarding the rationale for this tool and related philosophy can be found here
History of changes can be found here
Any feedback/suggestions/complaints regarding this tool would be much appreciated. Contributions are most welcome as well, of course.
Harel Ben-Attia, [email protected], @harelba on Twitter