Use polars rust crate as engine #725

baggiponte · 2023-01-13T21:13:39Z

baggiponte
Jan 13, 2023

Polars is emerging as a powerful contestant in (python's) dataframe showdown. Why would this matter to qsv? Polars engine is written in rust, so you might find inspiration or straight up use that as data manipulation library. This would/could also enable using qsv with parquet files which can't be viewed as they are binary. Also polars supports lazy execution and soon streaming/out of core execution will be released.

Repo: https://github.com/pola-rs/polars

jqnatividad · 2023-01-14T03:14:24Z

jqnatividad
Jan 14, 2023
Maintainer

Thanks @baggiponte for the suggestion.

I've actually been eyeing polars for a while (#480), but haven't gotten around to integrating it.

Some other ideas apart from custom sort order in #480 are:

use polars to implement a pivot command (https://stackoverflow.com/questions/74808057/how-do-i-pivot-a-table-using-rust-polars)
reimplement the join command using polars.
for time-series data, use it to resample and do temporal groupby/partitioning.

WDYT? Do you have other ideas for a specific polars-qsv integration?

2 replies

baggiponte Jan 14, 2023
Author

Thank you for your reply! I don't know a lot about rust and the polars crate, but I guess it might be interesting to get native multithreaded support on most operations? This might be a key element to make group by statements (time series too) and complex aggregations: from something like qsv groupby col1 <file> | enum to qsv groupby col1 <file> | rolling mean 7. Also string/date manipulation functions might be handed off to polars.

I am most excited about working with parquet files (perhaps even partly with remote storages?), but maybe that is managed by duckdb cli.

jqnatividad Jan 15, 2023
Maintainer

A lot of the long-running commands are already multithreaded, and I'm always looking to make the other commands for which it makes sense multithreaded as well - as it introduces its own overhead, and increases complexity.

But you're right that doing aggregations would great benefit from parallelization, and I look forward to experimenting with polars to see how I can leverage its multithreaded, aggregation capabilities.

Date parsing in qsv is already quite robust insofar as its ability in recognize 19 date formats and variants thereof - but in terms of data manipulation, I'm sure polars can be leveraged, especially with its native time-series capabilities.

Parquet export is also a recent addition with the to command, and I look forward to your feedback and the rest of the community, as folks start exercising it.

ondohotola · 2023-01-14T09:32:10Z

ondohotola
Jan 14, 2023

Joel, what I am missing most at the moment is a GROUP BY statement, so I have to pipe to CSVQ. For RW example, I get payment remittance advisories (from health care funders, and each has its own, one weirder than the other) and besides tracking whether they paid everything I claimed and when, I like to see the bottom line, per patient, per (service) day, per whatnot. Or take Zoom attendance lists, where they differentiate between waiting room and attendance proper, and can have you multiple lines (times) per attendee when you drop off and reconnect. I also would like to have some form of UNION statement, so I can display totals as the last line of the table in one pipeline which I currently have to do via temp files or CSVQ either of which is a schlepp :-)-O. Or am I overlooking something?

2 replies

jqnatividad Jan 15, 2023
Maintainer

@ondohotola , one of the key goals of luau's support for prologue and epilogue scripts is to support aggregations. The initial aggregation variables are setup in the prologue script, the sampling is done during the main luau script as the CSV is scanned, and the totals/aggregation calculations are done in the epilogue script.

But you're right that there are common aggregation scenarios where a dedicated qsv command can make things easier.

I'll be sure to add that first to the polars-qsv integration backlog when I start working on the pivot/groupby command...

As for UNIONs, have you tried the joincommand's --full option? It's not exactly a UNION, but perhaps, you can leverage it?

derekmahar Feb 18, 2023

@jqnatividad, I think qsv luau is just as easy to use as Miller's DSL, but more extensible because it can use Lua's packages. However, would it not be possible to combine the prologue, body, and epilogue into a single script, similiar to BEGIN and END in Awk?

ondohotola · 2023-01-15T17:54:36Z

ondohotola
Jan 15, 2023

Joel, writing LUAU scripts for begin/end to do a SELECT client, SUM(paid) as total GROUP BY client is a bit of an effort for a casual user like me. join —full does not do what I want SELECT client, SUM(paid) as total GROUP BY client UNION SELECT ‘Total’, SUM(pad)

1 reply

derekmahar Feb 18, 2023

@ondohotola, have you taken a look at DuckDB?

ondohotola · 2023-02-18T09:25:58Z

ondohotola
Feb 18, 2023

yes, overkill for my current needs, but maybe looming in the future. el

…

On 18. Feb 2023 at 09:19 +0200, Derek Mahar ***@***.***>, wrote: @ondohotola, have you taken a look at DuckDB? — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you were mentioned.Message ID: ***@***.***>

1 reply

derekmahar Feb 18, 2023

As an illustration, here is a comparison of csvq and DuckDB executing the queries that you need. Like csvq, DuckDB can read CSV files directly, supports advanced SQL syntax, is a single binary executable, and is just as easy to use:

$ printf "client,paid\n" > payments.csv
$ printf "Bob,10\nJill,5\nJohn,20\n" >> payments.csv
$ printf "Bob,10\nJill,5\nJohn,20\n" >> payments.csv
$ csvq --format box "SELECT client, SUM(paid) AS total FROM payments GROUP BY client UNION ALL SELECT 'Total', SUM(paid) FROM payments"
┌────────┬───────┐
│ client │ total │
├────────┼───────┤
│ Bob    │    20 │
│ Jill   │    10 │
│ John   │    40 │
│ Total  │    70 │
└────────┴───────┘
$ duckdb -c "SELECT client, SUM(paid) AS total FROM 'payments.csv' GROUP BY client UNION ALL SELECT 'Total', SUM(paid) FROM 'payments.csv'"
┌─────────┬────────┐
│ client  │ total  │
│ varchar │ int128 │
├─────────┼────────┤
│ Bob     │     20 │
│ Jill    │     10 │
│ John    │     40 │
│ Total   │     70 │
└─────────┴────────┘

Notice that unlike in csvq, DuckDB columns have strict types.

jqnatividad · 2023-02-23T15:39:17Z

jqnatividad
Feb 23, 2023
Maintainer

@baggiponte, just wanted to confirm that qsv is now using the polars engine with the joinp command.

There will be more polars-powered commands in future releases (pivot, resample, joinp --asof, etc.)

9 replies

derekmahar Feb 26, 2023

@jqnatividad, you're right! The joinp command is available in target/release/qsv. I must have run $HOME/.cargo/bin/qsv instead by mistake. Oops!

aborruso Mar 1, 2023

@derekmahar forgot that when you build --release, it creates the binary in target/release. Be sure you're running that one, instead of the qsv in your ~/.cargo/bin. 🤦

I have tried to compile, but I have these errors.
I'm using Debian 11.

errors

The following warnings were emitted during compilation:

warning: c++: fatal error: Killed signal terminated program cc1plus
warning: compilation terminated.

error: failed to run custom build command for libduckdb-sys v0.7.1

Caused by:
process didn't exit successfully: /home/aborruso/lavagna/tmp/qsv/target/release/build/libduckdb-sys-fa6c3bffede99d6b/build-script-build (exit status: 1)
--- stdout
cargo:rerun-if-changed=duckdb/duckdb.hpp
cargo:rerun-if-changed=duckdb/duckdb.cpp
TARGET = Some("x86_64-unknown-linux-gnu")
OPT_LEVEL = Some("3")
HOST = Some("x86_64-unknown-linux-gnu")
cargo:rerun-if-env-changed=CXX_x86_64-unknown-linux-gnu
CXX_x86_64-unknown-linux-gnu = None
cargo:rerun-if-env-changed=CXX_x86_64_unknown_linux_gnu
CXX_x86_64_unknown_linux_gnu = None
cargo:rerun-if-env-changed=HOST_CXX
HOST_CXX = None
cargo:rerun-if-env-changed=CXX
CXX = None
cargo:rerun-if-env-changed=CXXFLAGS_x86_64-unknown-linux-gnu
CXXFLAGS_x86_64-unknown-linux-gnu = None
cargo:rerun-if-env-changed=CXXFLAGS_x86_64_unknown_linux_gnu
CXXFLAGS_x86_64_unknown_linux_gnu = None
cargo:rerun-if-env-changed=HOST_CXXFLAGS
HOST_CXXFLAGS = None
cargo:rerun-if-env-changed=CXXFLAGS
CXXFLAGS = None
cargo:rerun-if-env-changed=CRATE_CC_NO_DEFAULTS
CRATE_CC_NO_DEFAULTS = None
DEBUG = Some("false")
CARGO_CFG_TARGET_FEATURE = Some("fxsr,sse,sse2")
cargo:rerun-if-env-changed=CXX_x86_64-unknown-linux-gnu
CXX_x86_64-unknown-linux-gnu = None
cargo:rerun-if-env-changed=CXX_x86_64_unknown_linux_gnu
CXX_x86_64_unknown_linux_gnu = None
cargo:rerun-if-env-changed=HOST_CXX
HOST_CXX = None
cargo:rerun-if-env-changed=CXX
CXX = None
cargo:rerun-if-env-changed=CXXFLAGS_x86_64-unknown-linux-gnu
CXXFLAGS_x86_64-unknown-linux-gnu = None
cargo:rerun-if-env-changed=CXXFLAGS_x86_64_unknown_linux_gnu
CXXFLAGS_x86_64_unknown_linux_gnu = None
cargo:rerun-if-env-changed=HOST_CXXFLAGS
HOST_CXXFLAGS = None
cargo:rerun-if-env-changed=CXXFLAGS
CXXFLAGS = None
cargo:rerun-if-env-changed=CRATE_CC_NO_DEFAULTS
CRATE_CC_NO_DEFAULTS = None
CARGO_CFG_TARGET_FEATURE = Some("fxsr,sse,sse2")
cargo:rerun-if-env-changed=CXX_x86_64-unknown-linux-gnu
CXX_x86_64-unknown-linux-gnu = None
cargo:rerun-if-env-changed=CXX_x86_64_unknown_linux_gnu
CXX_x86_64_unknown_linux_gnu = None
cargo:rerun-if-env-changed=HOST_CXX
HOST_CXX = None
cargo:rerun-if-env-changed=CXX
CXX = None
cargo:rerun-if-env-changed=CXXFLAGS_x86_64-unknown-linux-gnu
CXXFLAGS_x86_64-unknown-linux-gnu = None
cargo:rerun-if-env-changed=CXXFLAGS_x86_64_unknown_linux_gnu
CXXFLAGS_x86_64_unknown_linux_gnu = None
cargo:rerun-if-env-changed=HOST_CXXFLAGS
HOST_CXXFLAGS = None
cargo:rerun-if-env-changed=CXXFLAGS
CXXFLAGS = None
cargo:rerun-if-env-changed=CRATE_CC_NO_DEFAULTS
CRATE_CC_NO_DEFAULTS = None
CARGO_CFG_TARGET_FEATURE = Some("fxsr,sse,sse2")
cargo:rerun-if-env-changed=CXX_x86_64-unknown-linux-gnu
CXX_x86_64-unknown-linux-gnu = None
cargo:rerun-if-env-changed=CXX_x86_64_unknown_linux_gnu
CXX_x86_64_unknown_linux_gnu = None
cargo:rerun-if-env-changed=HOST_CXX
HOST_CXX = None
cargo:rerun-if-env-changed=CXX
CXX = None
cargo:rerun-if-env-changed=CXXFLAGS_x86_64-unknown-linux-gnu
CXXFLAGS_x86_64-unknown-linux-gnu = None
cargo:rerun-if-env-changed=CXXFLAGS_x86_64_unknown_linux_gnu
CXXFLAGS_x86_64_unknown_linux_gnu = None
cargo:rerun-if-env-changed=HOST_CXXFLAGS
HOST_CXXFLAGS = None
cargo:rerun-if-env-changed=CXXFLAGS
CXXFLAGS = None
cargo:rerun-if-env-changed=CRATE_CC_NO_DEFAULTS
CRATE_CC_NO_DEFAULTS = None
CARGO_CFG_TARGET_FEATURE = Some("fxsr,sse,sse2")
cargo:rerun-if-env-changed=CXX_x86_64-unknown-linux-gnu
CXX_x86_64-unknown-linux-gnu = None
cargo:rerun-if-env-changed=CXX_x86_64_unknown_linux_gnu
CXX_x86_64_unknown_linux_gnu = None
cargo:rerun-if-env-changed=HOST_CXX
HOST_CXX = None
cargo:rerun-if-env-changed=CXX
CXX = None
cargo:rerun-if-env-changed=CXXFLAGS_x86_64-unknown-linux-gnu
CXXFLAGS_x86_64-unknown-linux-gnu = None
cargo:rerun-if-env-changed=CXXFLAGS_x86_64_unknown_linux_gnu
CXXFLAGS_x86_64_unknown_linux_gnu = None
cargo:rerun-if-env-changed=HOST_CXXFLAGS
HOST_CXXFLAGS = None
cargo:rerun-if-env-changed=CXXFLAGS
CXXFLAGS = None
cargo:rerun-if-env-changed=CRATE_CC_NO_DEFAULTS
CRATE_CC_NO_DEFAULTS = None
CARGO_CFG_TARGET_FEATURE = Some("fxsr,sse,sse2")
running: "c++" "-O3" "-ffunction-sections" "-fdata-sections" "-fPIC" "-m64" "-std=c++11" "-o" "/home/aborruso/lavagna/tmp/qsv/target/release/build/libduckdb-sys-13393b95a43ce761/out/duckdb/duckdb.o" "-c" "duckdb/duckdb.cpp"
cargo:warning=c++: fatal error: Killed signal terminated program cc1plus
cargo:warning=compilation terminated.
exit status: 1

--- stderr

error occurred: Command "c++" "-O3" "-ffunction-sections" "-fdata-sections" "-fPIC" "-m64" "-std=c++11" "-o" "/home/aborruso/lavagna/tmp/qsv/target/release/build/libduckdb-sys-13393b95a43ce761/out/duckdb/duckdb.o" "-c" "duckdb/duckdb.cpp" with args "c++" did not execute successfully (status code exit status: 1).

derekmahar Mar 1, 2023

Your build process probably ran out of memory. In my experience, the qsv build (due to dependency libduckdb-sys) requires almost 9 GB of RAM. How much memory is installed in your build computer?

aborruso Mar 1, 2023

Your build process probably ran out of memory. In my experience, the qsv build (due to dependency libduckdb-sys) requires almost 9 GB of RAM. How much memory is installed in your build computer?

16 GB

derekmahar Mar 1, 2023

How much RAM is available to your qsv build process? How much RAM does htop show the build process consuming?

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Use polars rust crate as engine #725

{{title}}

{{editor}}'s edit

{{editor}}'s edit

Replies: 5 comments 15 replies

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

Select a reply

Use polars rust crate as engine #725

Replies: 5 comments · 15 replies

jqnatividad Jan 14, 2023 Maintainer

baggiponte Jan 14, 2023 Author

jqnatividad Jan 15, 2023 Maintainer

jqnatividad Jan 15, 2023 Maintainer

jqnatividad Feb 23, 2023 Maintainer

Replies: 5 comments 15 replies

jqnatividad
Jan 14, 2023
Maintainer

baggiponte Jan 14, 2023
Author

jqnatividad Jan 15, 2023
Maintainer

jqnatividad Jan 15, 2023
Maintainer

jqnatividad
Feb 23, 2023
Maintainer