q - Run SQL directly on delimited files and multi-file sqlite databases
See the code
q's purpose is to bring SQL expressive power to the Linux command line and to provide easy access to text as actual data.
q allows the following:
The following table shows the impact of using caching:
| Rows | Columns | File Size | Query time without caching | Query time with caching | Speed Improvement |
|---|---|---|---|---|---|
| 5,000,000 | 100 | 4.8GB | 4 minutes, 47 seconds | 1.92 seconds | x149 |
| 1,000,000 | 100 | 983MB | 50.9 seconds | 0.461 seconds | x110 |
| 1,000,000 | 50 | 477MB | 27.1 seconds | 0.272 seconds | x99 |
| 100,000 | 100 | 99MB | 5.2 seconds | 0.141 seconds | x36 |
| 100,000 | 50 | 48MB | 2.7 seconds | 0.105 seconds | x25 |
Notice that for the current version, caching is not enabled by default, since the caches take disk space. Use -C readwrite or -C read to enable it for a query, or add caching_mode to .qrc to set a new default.
q's web site is https://harelba.github.io/q/ or https://q.textasdata.wiki It contains everything you need to download and use q immediately.
q treats ordinary files as database tables, and supports all SQL constructs, such as WHERE, GROUP BY, JOINs, etc. It supports automatic column name and type detection, and provides full support for multiple character encodings.
Here are some example commands to get the idea:
$ q "SELECT COUNT(*) FROM ./clicks_file.csv WHERE c3 > 32.3"
$ ps -ef | q -H "SELECT UID, COUNT(*) cnt FROM - GROUP BY UID ORDER BY cnt DESC LIMIT 3"
$ q "select count(*) from some_db.sqlite3:::albums a left join another_db.sqlite3:::tracks t on (a.album_id = t.album_id)"
Detailed examples are in here
New Major Version 3.1.6 is out with a lot of significant additions.
Instructions for all OSs are here.
The previous version 2.0.19 Can still be downloaded from here
Any feedback/suggestions/complaints regarding this tool would be much appreciated. Contributions are most welcome as well, of course.
Linkedin: Harel Ben Attia
Twitter @harelba
Email harelba@gmail.com
q on twitter: #qtextasdata
Patreon: harelba - All the money received is donated to the Center for the Prevention and Treatment of Domestic Violence in my hometown - Ramla, Israel.
(top 24 of 109)
54,603 followers · starred Nov 2018
502 followers · starred Mar 2014
18 followers · starred Mar 2019
10,358 followers · starred Feb 2016
q - Run SQL directly on delimited files and multi-file sqlite databases
See the code
q's purpose is to bring SQL expressive power to the Linux command line and to provide easy access to text as actual data.
q allows the following:
The following table shows the impact of using caching:
| Rows | Columns | File Size | Query time without caching | Query time with caching | Speed Improvement |
|---|---|---|---|---|---|
| 5,000,000 | 100 | 4.8GB | 4 minutes, 47 seconds | 1.92 seconds | x149 |
| 1,000,000 | 100 | 983MB | 50.9 seconds | 0.461 seconds | x110 |
| 1,000,000 | 50 | 477MB | 27.1 seconds | 0.272 seconds | x99 |
| 100,000 | 100 | 99MB | 5.2 seconds | 0.141 seconds | x36 |
| 100,000 | 50 | 48MB | 2.7 seconds | 0.105 seconds | x25 |
Notice that for the current version, caching is not enabled by default, since the caches take disk space. Use -C readwrite or -C read to enable it for a query, or add caching_mode to .qrc to set a new default.
q's web site is https://harelba.github.io/q/ or https://q.textasdata.wiki It contains everything you need to download and use q immediately.
q treats ordinary files as database tables, and supports all SQL constructs, such as WHERE, GROUP BY, JOINs, etc. It supports automatic column name and type detection, and provides full support for multiple character encodings.
Here are some example commands to get the idea:
$ q "SELECT COUNT(*) FROM ./clicks_file.csv WHERE c3 > 32.3"
$ ps -ef | q -H "SELECT UID, COUNT(*) cnt FROM - GROUP BY UID ORDER BY cnt DESC LIMIT 3"
$ q "select count(*) from some_db.sqlite3:::albums a left join another_db.sqlite3:::tracks t on (a.album_id = t.album_id)"
Detailed examples are in here
New Major Version 3.1.6 is out with a lot of significant additions.
Instructions for all OSs are here.
The previous version 2.0.19 Can still be downloaded from here
Any feedback/suggestions/complaints regarding this tool would be much appreciated. Contributions are most welcome as well, of course.
Linkedin: Harel Ben Attia
Twitter @harelba
Email harelba@gmail.com
q on twitter: #qtextasdata
Patreon: harelba - All the money received is donated to the Center for the Prevention and Treatment of Domestic Violence in my hometown - Ramla, Israel.
(top 24 of 109)
54,603 followers · starred Nov 2018
502 followers · starred Mar 2014
18 followers · starred Mar 2019
10,358 followers · starred Feb 2016