7 ms·

Q – Run SQL Directly on CSV or TSV Files

190 points by gjvc 4y ago

kokizzu2 4y ago
sooo.. clickhouse? XD
eatonphil 4y ago
Q is a great project! Here's a comparison of Q against some of the other tools out there [0] (including dsq, which I wrote). And there's a benchmark in there too [1]. Whichever tool you end up using, I'm sure it will help out with your CLI data exploration! [0] https://github.com/multiprocessio/dsq#comparisons https://github.com/multiprocessio/dsq#comparisons [1] https://github.com/multiprocessio/dsq#benchmark https://github.com/multiprocessio/dsq#benchmark
- margarina72 4y ago
  dsq is a great tool, using it regularly to work against csv files, and really nice to use.
  - eatonphil 4y ago
    Awesome to hear!
gizmodo59 4y ago
I have been using Dremio to query large volume of CSV files: https://docs.dremio.com/software/data-sources/files-and-directories/ https://docs.dremio.com/software/data-sources/files-and-dire... Although having them in some columnar format is much better for fast responses. GitHub: https://github.com/dremio/dremio-oss https://github.com/dremio/dremio-oss
- eatonphil 4y ago
  They're sort of different categories. Tools like Q are for command line use. Dremio is a server/web application, right? They accomplish the same thing but you might deploy a tool like Q on production servers for adhoc log analysis or install it in a docker container. (Not saying you should, just explaining the difference.)
  - gizmodo59 4y ago
    I will give it a shot! The web application is just a UI to get started. It acts like a database providing you with jdbc and odbc drivers and arrow flight protocol: https://github.com/dremio-hub/arrow-flight-client-examples https://github.com/dremio-hub/arrow-flight-client-examples
redsaz 4y ago
A satisfied user here. Found it very useful when tools like cut and sort weren't enough, usually when I need to do a join on two different tables (err, files). Left joins work, but I don't think right joins are supported. I've used this in combination with jq as well. I'll use jq to convert json to CSV, and then use SQL to do whatever else.
- WASDx 4y ago
  You also probably have GNU join installed: https://www.gnu.org/software/coreutils/manual/html_node/join-invocation.html https://www.gnu.org/software/coreutils/manual/html_node/join...
- jfoutz 4y ago
  Have you looked at the Unix command ‘join’? This is a cool tool, but I think join is pretty much everywhere.
  - redsaz 4y ago
    Hmm, I'll have to check it out!
- SoftTalker 4y ago
  Same. You can go a long way with cut, sort, etc. and also awk with its pattern matching. But if you're handy with SQL, that can often feel more natural and certainly things like joins among separate CSV files, as well as sums and other aggregates, are easier. If you have "unclean" CSV data, e.g. where the data contains delimiters and/or newlines in quoted fields, you might want to pipe it through csvquote. https://github.com/dbro/csvquote https://github.com/dbro/csvquote
- bobivl 4y ago
  Cut, sort, join and awk can be pretty powerful and fast. If it becomes too tedious to manually write them, you can also use BigBash [1] to convert a SQL query automatically to a one-liner that only use these tools to execute the query. [1] http://bigbash.it http://bigbash.it
josebrwn 4y ago
http://harelba.github.io/q/#requirements http://harelba.github.io/q/#requirements "q is packaged as a compiled standalone-executable that has no dependencies, not even python itself." This is not quite true, on MacOS: "q: A full installation of Xcode.app 12.4 is required to compile this software. Installing just the Command Line Tools is not sufficient. Xcode can be installed from the App Store. Error: q: An unsatisfied requirement failed this build."
- pessimizer 4y ago
  I don't understand this objection. Being able to compile something with no dependencies is different from being able to run something with no dependencies.
- DandyDev 4y ago
  I think you're confusing installing the compiled package and using it (which doesn't require anything else) with _compiling_ the package, which requires XCode on MacOS
daniel-s 4y ago
Is this similar to CSV virtual tables in sqlite? https://www.sqlite.org/csv.html https://www.sqlite.org/csv.html
- eatonphil 4y ago
  It's easier to use than that. With virtual tables you must CREATE TABLE for every schema you have which is very tedious if you are querying CSV files with differing columns.
cma 4y ago
Might mix people up with: https://en.m.wikipedia.org/wiki/Q_(programming_language_from_Kx_Systems) https://en.m.wikipedia.org/wiki/Q_(programming_language_from...
- WASDx 4y ago
  When will people stop giving things one character names, it's impossible to search for.
cm2187 4y ago
Another solution would be to leverage the existing SQL engines. Someone mentioned sqlite virtual table. My home made solution to the very same problem is creating a simple winform that you can drag and drop spreadsheets and csv files onto, analyses them, creates an instance of localdb if there isn't one running, creates the table and uploads the data (so it reads the csv file twice). Then I can use my loved and trusted SQL Server Management studio with the MS SQL engine. The same UI allows to quickly delete tables and databases and create new database in two clicks. (future development: auto-normalise the table to reduce disk space and improve performance). What lacks is good import tools. Most csv import tools are super picky in term of the format of the data (dates in particular) and have too many steps.
- eatonphil 4y ago
  Q is built on top of sqlite. :)
blacksqr 4y ago
"Sqawk is an Awk-like program that uses SQL and can combine data from multiple files. It is powered by SQLite." https://github.com/dbohdan/sqawk https://github.com/dbohdan/sqawk
MilStdJunkie 4y ago
Reminds me of the textQL extension that's available in Asciidoc. Point it to an external CSV file, enable TextQL, and bam, there's your query returned as a table. Handy for parts lists, inventory, that kind of crap. https://github.com/dinedal/textql https://github.com/dinedal/textql https://gist.github.com/mojavelinux/8856117 https://gist.github.com/mojavelinux/8856117
dlkmp 4y ago
When I want to quickly query a csv file (usually log files), I like to use lnav which also supports running SQL queries and supports pretty much any log file format I happened to deal with. https://lnav.org/ https://lnav.org/
SoftTalker 4y ago
Previous discussion: https://news.ycombinator.com/item?id=18453133 https://news.ycombinator.com/item?id=18453133 (284 points|devy|4 years ago|96 comments) https://news.ycombinator.com/item?id=27423276 https://news.ycombinator.com/item?id=27423276 (121 points|thunderbong|1 year ago|63 comments) https://news.ycombinator.com/item?id=24694892 https://news.ycombinator.com/item?id=24694892 (11 points|pcr910303|2 years ago|2 comments)
bachmeier 4y ago
These tools, useful as they are, appear to be only for querying files. AFAICT, insert and update are typically not supported as a way to modify the data.
jmt_ 4y ago
You can do the same thing with csvsql from csvkit: https://towardsdatascience.com/analyze-csvs-with-sql-in-command-line-233202dc1241 https://towardsdatascience.com/analyze-csvs-with-sql-in-comm... Since csvkit comes with so many other tools, I'm not sure I see a reason to use q over csvsql
- gourabmi 4y ago
  Big fan of csvsql here. I use it often to look at log files.
Merad 4y ago
If you like the idea of this but aren't on Linux or want something with a little ui involved, check out the Rainbow CSV extension for VS Code, vim, Sublime, and Atom. It includes RBQL [0] which lets you query CSVs with a sql-like syntax. (Not affiliated with Rainbow CSV or RBQL at all, just a happy user) [0]: https://rbql.org/ https://rbql.org/
redavni 4y ago
For Windows users who click here, this functionality is built in. https://learn.microsoft.com/en-us/cpp/data/odbc/data-source-programmatically-configuring-an-odbc-data-source?source=recommendations&view=msvc-170 https://learn.microsoft.com/en-us/cpp/data/odbc/data-source-...
tanin 4y ago
I've made a similar app, but I aim it to be more convenient, especially for people who don't want to handle installation and command line. Check it out: https://superintendent.app https://superintendent.app -- it is a paid app though.
gorkish 4y ago
Don't know if it's the first such implementation, but perl's DBD::CSV is 25 years old this year.
luzifer42 4y ago
clickhouse-local is a powerful alternative. https://clickhouse.com/docs/en/operations/utilities/clickhouse-local/ https://clickhouse.com/docs/en/operations/utilities/clickhou... https://news.ycombinator.com/item?id=31561780 https://news.ycombinator.com/item?id=31561780
- eatonphil 4y ago
  It's fast. But it's also a 1-2gb binary. And its SQL implementation is work in progress and often makes up its own names for common functions. If you can put up with both for an adhoc cli exploration tool then yeah it's incredible. For analytics queries in general though (not talking about clickhouse-local) I don't think there's any OSS competition.
  - luzifer42 4y ago
    The large binary size argument is a bit old. It seems possible to reduce it below 50MB. https://github.com/ClickHouse/ClickHouse/issues/29378 https://github.com/ClickHouse/ClickHouse/issues/29378
LtWorf 4y ago
Maybe relevant. A while ago I wrote relational (https://ltworf.github.io/relational/ https://ltworf.github.io/relational/) to do relational algebra queries… It can load csv files, has a gui and a cli.
hexo 4y ago
yet another Q among: https://en.wikipedia.org/wiki/Q_(programming_language_from_Kx_Systems) https://en.wikipedia.org/wiki/Q_(programming_language_from_K... https://en.wikipedia.org/wiki/Pure_(programming_language) https://en.wikipedia.org/wiki/Pure_(programming_language)
joshe 4y ago
Temp DB's feel like a slightly underused technique too. (Not saying it's always better.) Ie, just a quick script that adds a serial id as the first column. Then imports to postgres/mysql based on header names (column names) and file name (becomes table name) to a brand new db. Usually DBs are so long lived and carefully designed that there's a bit of mental block to just importing trash data and dropping the whole database later. I'm always 15 mins into awk before i remember. Also in postgres you can do it as a new schema in an existing database, and join with the existing data. Probably safest to not do that in production :-). Like so: https://stackoverflow.com/questions/5712387/can-we-join-two-tables-in-postgresql-if-the-tables-are-in-different-schemas https://stackoverflow.com/questions/5712387/can-we-join-two-... Then just drop the whole schema when you are done screwing around.
bob-bot 4y ago
Love seeing more projects focused on using SQL to query many things. It is a common and familiar language to query and report. I help lead an open source project https//steampipe.io which can query CSV with SQL, among 85+ other endpoints like cloud providers, SaaS APIs, code, logs and more using SQL to query and join data: https://hub.steampipe.io/plugins https://hub.steampipe.io/plugins There is also an interesting dashboards as code concept where you can codify interactive dashboards with HCL + SQL: https://steampipe.io/blog/dashboards-as-code https://steampipe.io/blog/dashboards-as-code
deleted 4y ago
[deleted]
mattewong 4y ago
Nice work! I am a fan of tools like this. However, in my first attempted query (version 3.1.6 on MacOS), I ran into significant performance limitations and more importantly, it did not give correct output. In particular, running on a narrow table with 1mm rows (the same one used in the xsv examples) using the command "select country, count(1) from worldcitiespop_mil.csv group by country" takes 12 seconds just to get an incorrect error 'no such column: country'. using sqlite3, it takes two seconds or so to load, and less than a second to run, and gives me the correct result. Using https://github.com/liquidaty/zsv https://github.com/liquidaty/zsv (disclaimer, I'm one of its authors), I get the correct results in 0.95 seconds with the one-liner `zsv sql 'select country, count(1) from data group by country' worldcitiespop_mil.csv`.
- harelba 4y ago
  Hi, author of q here. Regarding the error you got, q currently does not autodetect headers, so you'd need to add -H as a flag in order to use the "country" column name. You're absolutely correct on failing-fast here - It's a bug which i'll fix. In general regarding speed - q supports automatic caching of the CSV files (through the "-C readwrite" flag). Once it's activated, it will write the data into another file (with a .qsql extension), and will use it automatically in further queries in order to speed things considerably. Effectively, the .qsql files are regular sqlite3 files (with some metadata), and q can be used to query them directly (or any regular sqlite3 file), including the ability to seamlessly join between multiple sqlite3 files. http://harelba.github.io/q/#auto-caching-examples http://harelba.github.io/q/#auto-caching-examples
  - mattewong 4y ago
    Ah, got it, thank you! Just one minor suggestions/feedback point, in case you find helpful, which is that I had to also add the `-d` flag with a comma value. Otherwise with just -H, I get the error "Bad header row" even though my header was simply "Country,City,AccentCity,Region,Population,Latitude,Longitude". This suggests to me that `q` is not assuming the input to be a CSV file, but that seems at odds with the first example in the manual, which is `q "select * from myfile.csv"`, with no `-d` flag. Or perhaps the first example also isn't using a csv delimiter, but it doesn't matter because no specific column is being selected? In addition, given that, from what I gather, a significant convenience of `q` is its auto-detection, then I think it would make sense for it to notice when the input table name ends in ".csv" and based on that, to assume a comma delimiter. Just my 2 cents. Great job!
fny 4y ago
Q runs SQLite under the hood. If you want real firepower, take a look at DuckDB, the codebase is also a work of art. https://github.com/duckdb/duckdb https://github.com/duckdb/duckdb
deleted 4y ago
[deleted]
timhsu518 4y ago
What is the difference between Q and csvsql?
vasvir 4y ago
MariaDB sports several storage engines such as InnoDB, MyISAM, Aria etc One of them is CSV which uses plain text files as backends https://mariadb.com/kb/en/csv-overview/ https://mariadb.com/kb/en/csv-overview/
jimnotgym 4y ago
I always just imported the csv into sqlite (one line command) Then I can have fast reliable queries on it and join it to other data.

190 points by gjvc 4y ago

sooo.. clickhouse? XD

Q is a great project! Here's a comparison of Q against some of the other tools out there [0] (including dsq, which I wrote). And there's a benchmark in there too [1]. Whichever tool you end up using, I'm sure it will help out with your CLI data exploration! [0] https://github.com/multiprocessio/dsq#comparisons https://github.com/multiprocessio/dsq#comparisons [1] https://github.com/multiprocessio/dsq#benchmark https://github.com/multiprocessio/dsq#benchmark

margarina72 4y ago
dsq is a great tool, using it regularly to work against csv files, and really nice to use.
- eatonphil 4y ago
  Awesome to hear!

gizmodo59 4y ago

I have been using Dremio to query large volume of CSV files: https://docs.dremio.com/software/data-sources/files-and-directories/ https://docs.dremio.com/software/data-sources/files-and-dire... Although having them in some columnar format is much better for fast responses. GitHub: https://github.com/dremio/dremio-oss https://github.com/dremio/dremio-oss

eatonphil 4y ago
They're sort of different categories. Tools like Q are for command line use. Dremio is a server/web application, right? They accomplish the same thing but you might deploy a tool like Q on production servers for adhoc log analysis or install it in a docker container. (Not saying you should, just explaining the difference.)
- gizmodo59 4y ago
  I will give it a shot! The web application is just a UI to get started. It acts like a database providing you with jdbc and odbc drivers and arrow flight protocol: https://github.com/dremio-hub/arrow-flight-client-examples https://github.com/dremio-hub/arrow-flight-client-examples

redsaz 4y ago

A satisfied user here. Found it very useful when tools like cut and sort weren't enough, usually when I need to do a join on two different tables (err, files). Left joins work, but I don't think right joins are supported. I've used this in combination with jq as well. I'll use jq to convert json to CSV, and then use SQL to do whatever else.

WASDx 4y ago
You also probably have GNU join installed: https://www.gnu.org/software/coreutils/manual/html_node/join-invocation.html https://www.gnu.org/software/coreutils/manual/html_node/join...
jfoutz 4y ago
Have you looked at the Unix command ‘join’? This is a cool tool, but I think join is pretty much everywhere.
- redsaz 4y ago
  Hmm, I'll have to check it out!
SoftTalker 4y ago
Same. You can go a long way with cut, sort, etc. and also awk with its pattern matching. But if you're handy with SQL, that can often feel more natural and certainly things like joins among separate CSV files, as well as sums and other aggregates, are easier. If you have "unclean" CSV data, e.g. where the data contains delimiters and/or newlines in quoted fields, you might want to pipe it through csvquote. https://github.com/dbro/csvquote https://github.com/dbro/csvquote
bobivl 4y ago
Cut, sort, join and awk can be pretty powerful and fast. If it becomes too tedious to manually write them, you can also use BigBash [1] to convert a SQL query automatically to a one-liner that only use these tools to execute the query. [1] http://bigbash.it http://bigbash.it

josebrwn 4y ago

http://harelba.github.io/q/#requirements http://harelba.github.io/q/#requirements "q is packaged as a compiled standalone-executable that has no dependencies, not even python itself." This is not quite true, on MacOS: "q: A full installation of Xcode.app 12.4 is required to compile this software. Installing just the Command Line Tools is not sufficient. Xcode can be installed from the App Store. Error: q: An unsatisfied requirement failed this build."

pessimizer 4y ago
I don't understand this objection. Being able to compile something with no dependencies is different from being able to run something with no dependencies.
DandyDev 4y ago
I think you're confusing installing the compiled package and using it (which doesn't require anything else) with _compiling_ the package, which requires XCode on MacOS

daniel-s 4y ago

Is this similar to CSV virtual tables in sqlite? https://www.sqlite.org/csv.html https://www.sqlite.org/csv.html

eatonphil 4y ago
It's easier to use than that. With virtual tables you must CREATE TABLE for every schema you have which is very tedious if you are querying CSV files with differing columns.

cma 4y ago

Might mix people up with: https://en.m.wikipedia.org/wiki/Q_(programming_language_from_Kx_Systems) https://en.m.wikipedia.org/wiki/Q_(programming_language_from...

WASDx 4y ago
When will people stop giving things one character names, it's impossible to search for.

cm2187 4y ago

Another solution would be to leverage the existing SQL engines. Someone mentioned sqlite virtual table. My home made solution to the very same problem is creating a simple winform that you can drag and drop spreadsheets and csv files onto, analyses them, creates an instance of localdb if there isn't one running, creates the table and uploads the data (so it reads the csv file twice). Then I can use my loved and trusted SQL Server Management studio with the MS SQL engine. The same UI allows to quickly delete tables and databases and create new database in two clicks. (future development: auto-normalise the table to reduce disk space and improve performance). What lacks is good import tools. Most csv import tools are super picky in term of the format of the data (dates in particular) and have too many steps.

eatonphil 4y ago
Q is built on top of sqlite. :)

blacksqr 4y ago

"Sqawk is an Awk-like program that uses SQL and can combine data from multiple files. It is powered by SQLite." https://github.com/dbohdan/sqawk https://github.com/dbohdan/sqawk

MilStdJunkie 4y ago

Reminds me of the textQL extension that's available in Asciidoc. Point it to an external CSV file, enable TextQL, and bam, there's your query returned as a table. Handy for parts lists, inventory, that kind of crap. https://github.com/dinedal/textql https://github.com/dinedal/textql https://gist.github.com/mojavelinux/8856117 https://gist.github.com/mojavelinux/8856117

dlkmp 4y ago

When I want to quickly query a csv file (usually log files), I like to use lnav which also supports running SQL queries and supports pretty much any log file format I happened to deal with. https://lnav.org/ https://lnav.org/

SoftTalker 4y ago

Previous discussion: https://news.ycombinator.com/item?id=18453133 https://news.ycombinator.com/item?id=18453133 (284 points|devy|4 years ago|96 comments) https://news.ycombinator.com/item?id=27423276 https://news.ycombinator.com/item?id=27423276 (121 points|thunderbong|1 year ago|63 comments) https://news.ycombinator.com/item?id=24694892 https://news.ycombinator.com/item?id=24694892 (11 points|pcr910303|2 years ago|2 comments)

bachmeier 4y ago

These tools, useful as they are, appear to be only for querying files. AFAICT, insert and update are typically not supported as a way to modify the data.

jmt_ 4y ago

You can do the same thing with csvsql from csvkit: https://towardsdatascience.com/analyze-csvs-with-sql-in-command-line-233202dc1241 https://towardsdatascience.com/analyze-csvs-with-sql-in-comm... Since csvkit comes with so many other tools, I'm not sure I see a reason to use q over csvsql

gourabmi 4y ago
Big fan of csvsql here. I use it often to look at log files.

Merad 4y ago

If you like the idea of this but aren't on Linux or want something with a little ui involved, check out the Rainbow CSV extension for VS Code, vim, Sublime, and Atom. It includes RBQL [0] which lets you query CSVs with a sql-like syntax. (Not affiliated with Rainbow CSV or RBQL at all, just a happy user) [0]: https://rbql.org/ https://rbql.org/

redavni 4y ago

For Windows users who click here, this functionality is built in. https://learn.microsoft.com/en-us/cpp/data/odbc/data-source-programmatically-configuring-an-odbc-data-source?source=recommendations&view=msvc-170 https://learn.microsoft.com/en-us/cpp/data/odbc/data-source-...

tanin 4y ago

I've made a similar app, but I aim it to be more convenient, especially for people who don't want to handle installation and command line. Check it out: https://superintendent.app https://superintendent.app -- it is a paid app though.

gorkish 4y ago

Don't know if it's the first such implementation, but perl's DBD::CSV is 25 years old this year.

luzifer42 4y ago

clickhouse-local is a powerful alternative. https://clickhouse.com/docs/en/operations/utilities/clickhouse-local/ https://clickhouse.com/docs/en/operations/utilities/clickhou... https://news.ycombinator.com/item?id=31561780 https://news.ycombinator.com/item?id=31561780

eatonphil 4y ago
It's fast. But it's also a 1-2gb binary. And its SQL implementation is work in progress and often makes up its own names for common functions. If you can put up with both for an adhoc cli exploration tool then yeah it's incredible. For analytics queries in general though (not talking about clickhouse-local) I don't think there's any OSS competition.
- luzifer42 4y ago
  The large binary size argument is a bit old. It seems possible to reduce it below 50MB. https://github.com/ClickHouse/ClickHouse/issues/29378 https://github.com/ClickHouse/ClickHouse/issues/29378

LtWorf 4y ago

Maybe relevant. A while ago I wrote relational (https://ltworf.github.io/relational/ https://ltworf.github.io/relational/) to do relational algebra queries… It can load csv files, has a gui and a cli.

hexo 4y ago

yet another Q among: https://en.wikipedia.org/wiki/Q_(programming_language_from_Kx_Systems) https://en.wikipedia.org/wiki/Q_(programming_language_from_K... https://en.wikipedia.org/wiki/Pure_(programming_language) https://en.wikipedia.org/wiki/Pure_(programming_language)

joshe 4y ago

Temp DB's feel like a slightly underused technique too. (Not saying it's always better.) Ie, just a quick script that adds a serial id as the first column. Then imports to postgres/mysql based on header names (column names) and file name (becomes table name) to a brand new db. Usually DBs are so long lived and carefully designed that there's a bit of mental block to just importing trash data and dropping the whole database later. I'm always 15 mins into awk before i remember. Also in postgres you can do it as a new schema in an existing database, and join with the existing data. Probably safest to not do that in production :-). Like so: https://stackoverflow.com/questions/5712387/can-we-join-two-tables-in-postgresql-if-the-tables-are-in-different-schemas https://stackoverflow.com/questions/5712387/can-we-join-two-... Then just drop the whole schema when you are done screwing around.

bob-bot 4y ago

Love seeing more projects focused on using SQL to query many things. It is a common and familiar language to query and report. I help lead an open source project https//steampipe.io which can query CSV with SQL, among 85+ other endpoints like cloud providers, SaaS APIs, code, logs and more using SQL to query and join data: https://hub.steampipe.io/plugins https://hub.steampipe.io/plugins There is also an interesting dashboards as code concept where you can codify interactive dashboards with HCL + SQL: https://steampipe.io/blog/dashboards-as-code https://steampipe.io/blog/dashboards-as-code

deleted 4y ago

[deleted]

mattewong 4y ago

Nice work! I am a fan of tools like this. However, in my first attempted query (version 3.1.6 on MacOS), I ran into significant performance limitations and more importantly, it did not give correct output. In particular, running on a narrow table with 1mm rows (the same one used in the xsv examples) using the command "select country, count(1) from worldcitiespop_mil.csv group by country" takes 12 seconds just to get an incorrect error 'no such column: country'. using sqlite3, it takes two seconds or so to load, and less than a second to run, and gives me the correct result. Using https://github.com/liquidaty/zsv https://github.com/liquidaty/zsv (disclaimer, I'm one of its authors), I get the correct results in 0.95 seconds with the one-liner `zsv sql 'select country, count(1) from data group by country' worldcitiespop_mil.csv`.

harelba 4y ago
Hi, author of q here. Regarding the error you got, q currently does not autodetect headers, so you'd need to add -H as a flag in order to use the "country" column name. You're absolutely correct on failing-fast here - It's a bug which i'll fix. In general regarding speed - q supports automatic caching of the CSV files (through the "-C readwrite" flag). Once it's activated, it will write the data into another file (with a .qsql extension), and will use it automatically in further queries in order to speed things considerably. Effectively, the .qsql files are regular sqlite3 files (with some metadata), and q can be used to query them directly (or any regular sqlite3 file), including the ability to seamlessly join between multiple sqlite3 files. http://harelba.github.io/q/#auto-caching-examples http://harelba.github.io/q/#auto-caching-examples
- mattewong 4y ago
  Ah, got it, thank you! Just one minor suggestions/feedback point, in case you find helpful, which is that I had to also add the `-d` flag with a comma value. Otherwise with just -H, I get the error "Bad header row" even though my header was simply "Country,City,AccentCity,Region,Population,Latitude,Longitude". This suggests to me that `q` is not assuming the input to be a CSV file, but that seems at odds with the first example in the manual, which is `q "select * from myfile.csv"`, with no `-d` flag. Or perhaps the first example also isn't using a csv delimiter, but it doesn't matter because no specific column is being selected? In addition, given that, from what I gather, a significant convenience of `q` is its auto-detection, then I think it would make sense for it to notice when the input table name ends in ".csv" and based on that, to assume a comma delimiter. Just my 2 cents. Great job!

fny 4y ago

Q runs SQLite under the hood. If you want real firepower, take a look at DuckDB, the codebase is also a work of art. https://github.com/duckdb/duckdb https://github.com/duckdb/duckdb

deleted 4y ago

[deleted]

timhsu518 4y ago

What is the difference between Q and csvsql?

vasvir 4y ago

MariaDB sports several storage engines such as InnoDB, MyISAM, Aria etc One of them is CSV which uses plain text files as backends https://mariadb.com/kb/en/csv-overview/ https://mariadb.com/kb/en/csv-overview/

jimnotgym 4y ago

I always just imported the csv into sqlite (one line command) Then I can have fast reliable queries on it and join it to other data.