PQL Support for generalized expressions in selection and aggregation

[agrawaldevesh]

The context is that I am trying to enable more general purpose analytics atop my Pinot tables. The idea being that users shouldn't have to precisely log the exact expression they are interested in. I am trying to hook up Apache Superset with Pinot.

So I would like support for such features:

1. Single column simple expressions: "select max(2 * bytesRead) from foo" or "select 2*max(bytesRead) from foo"
2. Multi column simple expressions: "select max(bytesRead/bytesWritten) from foo" (Would this be hard to implement ?)
3. More and more support for SQL constructs like IF, CASE etc.

Do you think these features would be straight forward to implement or would they require some architectural changes/refactoring before we can have them ? Are there efforts in the community that relate to this ? Also, what's the overall plan/roadmap about such SQL-ness ?

Thanks

[Jackie-Jiang]

The first 2 cases are already supported in Pinot.

1. SELECT MAX(MULT(bytesRead, 2)) FROM foo
2. SELECT MAX(DIV(bytesRead, bytesWritten)) FROM foo

We have support for function: ADD, SUB, MULT, DIV in aggregation. Currently no support for selection yet (they are not that useful though because you can perform the operation on client side). Feel free to try them out.

[agrawaldevesh]

Doing expressions on the client side (in the select statement) can be done on the client but it is essentially an involved operation:

Consider a contrived query like this: Select mul(foo, 2), div(foo, bar) from Table where mul(baz, 4) = 10.

If this query is generated in an adhoc manner, the client would have to have support for parsing this query and figuring out that the foo, bar need to multiplied like so and that baz has to be ignored. That's a lot to ask from a client and it is something that would be easier to implement in the database itself.

Can you help me understand how much work would it be to implement such features ? I am happy implementing if they won't require a lot of architectural changes :-)

[Jackie-Jiang]

I tried to implement it, but found the change is not trivial (don't remember the details). Selection should not be very hard to support, and does not requires architectural changes. But filter support would be hard, and the performance could be very bad because no index can be applied.

[sunny19930321]

Doing expressions on the client side (in the select statement) can be done on the client but it is essentially an involved operation:

Consider a contrived query like this: Select mul(foo, 2), div(foo, bar) from Table where mul(baz, 4) = 10.

If this query is generated in an adhoc manner, the client would have to have support for parsing this query and figuring out that the foo, bar need to multiplied like so and that baz has to be ignored. That's a lot to ask from a client and it is something that would be easier to implement in the database itself.

Can you help me understand how much work would it be to implement such features ? I am happy implementing if they won't require a lot of architectural changes :-)

hello，@agrawaldevesh Superset already supports pinot, but how do I graph it? My time field is in milliseconds
sqllab

charts
The data has been queried, but superset does not display it

[Jackie-Jiang]

All the asked features are supported now with SQL query