Add DataFusion to h2oai/db-benchmark

Note: migrated from original JIRA: https://issues.apache.org/jira/browse/ARROW-11252

I would like to see DataFusion added to h2oai/db-benchmark so that we can see how we compare to other solutions (including Pandas, Spark, cuDF, and Polars).

Since Polars (another Rust DataFrame library that uses Arrow) has already been added, I am hoping that we can learn from their scripts.

There is an issue filed against db-benchmark for adding DataFusion:

h2oai/db-benchmark#107

@alamb

@alamb @houqp FYI i am picking up on the work @Dandandan started on this.

below are the current results i have after adding join queries:

group by
q1 took 56 ms
q2 took 289 ms
q3 took 1305 ms
q4 took 69 ms
q5 took 1158 ms
q7 took 1198 ms
q10 took 24691 ms

join
q1 took 261 ms
q2 took 367 ms
q3 took 334 ms
q4 took 507 ms
q5 took 1936 ms

@alamb

@alamb @houqp FYI i am picking up on the work @Dandandan started on this.

below are the current results i have after adding join queries:
group by
q1 took 56 ms
q2 took 289 ms
q3 took 1305 ms
q4 took 69 ms
q5 took 1158 ms
q7 took 1198 ms
q10 took 24691 ms

join
q1 took 261 ms
q2 took 367 ms
q3 took 334 ms
q4 took 507 ms
q5 took 1936 ms

Thank you very much @matthewmturner. From looking at your results and extrapolating a bit from my earlier benchmarking and published results it seems like DF does very well on the join queries. Hopefully we can do some real comparisons later.

@matthewmturner

@matthewmturner I'm assuming that's on the 0.5 GB bench?

It does seem to be a little lacking for group by in comparison to polars and others.

@matthewmturner

@matthewmturner I'm assuming that's on the 0.5 GB bench?

It does seem to be a little lacking for group by in comparison to polars and others.

Yes. I still need to double check table setup and queries to make sure it's apples to apples though - so take it with a grain of salt for the moment.

I also see the code which used to load data into partitions using Mentale::load now loads it into 1 partition (with try_new). Not sure if the difference is still big as we now do Round Robin repartitioning of the data, but would maybe still save a bit.

@Dandandan

@Dandandan my understanding of what i did was set the expected partitions at the ExecutionContext / ExecutionConfig level and then collect that into record batches from a DataFrame with df.collect_partitioned which would take into account the target partitions. That being said i had to play around with that a lot to get a MemTable so im not confident its doing what i expected. I'll look into it a little more though.

If what i did was incorrect is there a more idiomatic way to create a MemTable with the desired number of partitions?

Separately, i havent been able to find docs on the supported math functions. Any info you could provide on that?

Went through the code and found the math functions that are supported. I think it would be nice to add documentation to the datafusion site on what's supported. But of course that is a separate topic - i've created an issue for it.

I think for the more advanced group by queries we'll need to add median, standard dev, and correlation functions. ive created an issue for adding those as well - but hopefully we can submit benchmark without those and add them when the functionality is added.

i think we'll be able to add query 8, going to work on that.

I think for the more advanced group by queries we'll need to add median, standard dev, and correlation functions. ive created an issue for adding those as well - but hopefully we can submit benchmark without those and add them when the functionality is added.

FWIW standard deviation and correlation can be calculated using the existing aggregation functions (aka AVG(X) and AVG(X^2)), numerical precision issues not withstanding

Median is harder -- I think it will need special casing as it can't be calculated using partial aggregates

@alamb

@alamb yes agree. The median is needed in same query with std deviation but im going to try adding the one with correlation

I feel a bit silly for asking this, but is the ability to raise a value / column to a power implemented in datafusion? I've tried the below ways with no luck. Am I missing something obvious?

DataFusion CLI v5.1.0

❯ select 2**2;  🤔 Invalid statement: sql parser error: Expected end of statement, found: 2

DataFusion CLI v5.1.0

❯ select 2^2;
NotImplemented("Unsupported SQL binary operator BitwiseXor")
❯ select power(2,2);
Plan("Invalid function 'power'")

I get the same errors when running on table columns

@houqp

im also curious what people think about using the python bindings instead of rust? there are existing utilities we could leverage if so. however, can we generate an optimized build for python like we are in rust?

@houqp @alamb

I feel a bit silly for asking this, but is the ability to raise a value / column to a power implemented in datafusion? I've tried the below ways with no luck. Am I missing something obvious?

I thought it was pow or power but when I tried to dig around I didn't find it implemented. I filed #1493 to track

im also curious what people think about using the python bindings instead of rust? there are existing utilities we could leverage if so. however, can we generate an optimized build for python like we are in rust?

In general, I think using the python bindings is a great idea for integration of datafusion with other systems. I don't know how very much about how to build / use them, but I would love to see more documentation on the process :)

@Dandandan

Another cross post from slack

This is the latest with the new groupby queries (6 and 9) included:

q1: 0.038001750000000056
q2: 0.46213770899999995
q3: 2.206588334
q4: 0.03716179199999958
q5: 2.2481447910000005
q6: 2.099691 NEW
q7: 1.9977297499999995
q8: 3.0949106670000006
q9: 2.20049575 NEW
q10: 49.882744625

Only about half(5) of the engines benchmarked even complete these so that already puts us in a pretty good spot. However, of those that do complete it we are on the slower side - about tied with the current slowest.
however as @Dandandan noted this is without some optimizations in place. im going to work on adding those next.

also im going to work on building an automation script so anyone can run this benchmark themselves and play with optimizations they have in mind.

@realno

@realno FYI see above for latest - as i know you expressed a specific interest in this.

@realno

@realno FYI see above for latest - as i know you expressed a specific interest in this.

I am actually reading this and the slack channel, lol :D Thanks @matthewmturner for the update and progress! I will definitely participate in optimization as much as I can. This is a great start!

@realno

@realno oh and I had an issue with the approx_median function. I ended up having to use the quantile function instead. I didn't really get the chance to look into it though.

@realno

@realno oh and I had an issue with the approx_median function. I ended up having to use the quantile function instead. I didn't really get the chance to look into it though.

I will look into it. If you can gather some info and create an issue please tag me on it. Thanks!

I think this issue is now complete

Sliding Hash Join Algorithm (SWHJ) (#147)

Global join selection (#183)

Projection Pushdown (#211)

Sliding Hash Join Algorithm (SWHJ) (#147)

Global join selection (#183)

Projection Pushdown (#211)

fix: Avoid null exception in removeSubquery (apache#147)

replace master with main (apache#147)

Navigation Menu

Repository navigation

Add DataFusion to h2oai/db-benchmark #147

15 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Add DataFusion to h2oai/db-benchmark #147

Description

Activity

matthewmturner commented on Dec 25, 2021

Dandandan commented on Dec 25, 2021

jon-chuang commented on Dec 25, 2021

matthewmturner commented on Dec 25, 2021

Dandandan commented on Dec 25, 2021

matthewmturner commented on Dec 26, 2021

matthewmturner commented on Dec 26, 2021

alamb commented on Dec 26, 2021

matthewmturner commented on Dec 26, 2021

matthewmturner commented on Dec 26, 2021

matthewmturner commented on Dec 26, 2021

alamb commented on Dec 27, 2021

alamb commented on Dec 27, 2021

15 remaining items

matthewmturner commented on Feb 23, 2022

matthewmturner commented on Feb 23, 2022

realno commented on Feb 23, 2022

matthewmturner commented on Feb 23, 2022

realno commented on Feb 23, 2022

alamb commented on Mar 7, 2023

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions