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/
Q – Run SQL Directly on CSV or TSV Files
51–60 of 62 posts
Re: Q – Run SQL Directly on CSV or TSV Files
#52clickhouse-local is a powerful alternative. https://clickhouse.com/docs/en/operations/utilities/clickhou... https://news.ycombinator.com/item?id=31561780
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.
The large binary size argument is a bit old.
It seems possible to reduce it below 50MB.
Re: Q – Run SQL Directly on CSV or TSV Files
#53I 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.
Re: Q – Run SQL Directly on CSV or TSV Files
#54Earlier quoted context omitted.
Any experienced programmer learns to not use string processing on structured data, because that will bite them in the ass. Meanwhile HN luddites: let me use awk, cut and whatnot despite the existence of an util that explicitly sidesteps this issue.
"structured data" usually means there are delimiting characters, states, etc. AWK can certainly handle this. It's a simple and powerful language. I don't think I've ever used it to parse JSON, but I've definitely used it to output simple JSON.
Are you telling me that awk can correctly identify delimiters inside quoted strings? Escaped quotes inside quoted strings? Newlines inside quoted strings? I.e. that awk actually has a csv parser? Very cool if so.
Re: Q – Run SQL Directly on CSV or TSV Files
#55Earlier quoted context omitted.
Any experienced programmer learns to not use string processing on structured data, because that will bite them in the ass. Meanwhile HN luddites: let me use awk, cut and whatnot despite the existence of an util that explicitly sidesteps this issue.
/me runs the example on bigbash.it, cleaned up a bit: ( trap "kill 0" SIGINT; export LC_ALL=C; find movies.dat.gz -print0 | xargs -0 -i sh -c "gzip -dc {} | tail -n +2" | sed "s/::/;/g" | cut -d $';' -f2 | sort -t$';' -k 1,1 | head -n10 | awk -F ';' '{print $1}' ) Yeah, how about no. That's a very neat site and a clever hack, but there are clear escaping flaws in there for valid movie names. bash and standard unix to…
Yep, pretty sure that this script doesn't handle quoted strings in any way, and would promptly mangle a title that contains a semicolon.
Re: Q – Run SQL Directly on CSV or TSV Files
#56I have been using Dremio to query large volume of CSV files: 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
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.)
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
Re: Q – Run SQL Directly on CSV or TSV Files
#57Nice 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…
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.
Re: Q – Run SQL Directly on CSV or TSV Files
#58Nice 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…
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 (wi…
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!
Re: Q – Run SQL Directly on CSV or TSV Files
#59Earlier quoted context omitted.
"structured data" usually means there are delimiting characters, states, etc. AWK can certainly handle this. It's a simple and powerful language. I don't think I've ever used it to parse JSON, but I've definitely used it to output simple JSON.
Are you telling me that awk can correctly identify delimiters inside quoted strings? Escaped quotes inside quoted strings? Newlines inside quoted strings? I.e. that awk actually has a csv parser? Very cool if so.
[deleted]
Re: Q – Run SQL Directly on CSV or TSV Files
#60Earlier quoted context omitted.
"structured data" usually means there are delimiting characters, states, etc. AWK can certainly handle this. It's a simple and powerful language. I don't think I've ever used it to parse JSON, but I've definitely used it to output simple JSON.
Are you telling me that awk can correctly identify delimiters inside quoted strings? Escaped quotes inside quoted strings? Newlines inside quoted strings? I.e. that awk actually has a csv parser? Very cool if so.
Yeah, you can implement a basic FSM and use `next` to handle fake `RS` (e.g. newlines).
I'm not necessarily recommending it, but it's certainly possible and could be portable and really fast to run with a low memory footprint.