Hi All, In documentation, it saying Pinot can not support joins in queries. is there any alternative to achieve that. I have to implement derived stats. which will query on multiple pinot tables(schema) and get the data.

Presto can help with that. If your lookup data is reasonable, it can also be achieved by your querying application by joining the results from independent queries

I’ve been thinking about this a bunch recently. Few different options: • Load data into a data sink that supports JOINS. Like Rockset (https://rockset.com/) • Use a query engine to do JOINs, like Trino (https://trino.io/) or PrestoDB (https://prestodb.io/) or Apache Drill (https://drill.apache.org/). No opinions here, I’ll side step the politics here 🙏

We use the latest trino version in production here and it works great for us, just specify the controller url and the rest just works

I assume existing pinot connector will not do udf push down. So Presto has to load entire data from Pino to do any operations which in turn negate the purpose of using Pinot.

You can push the entire query down with the "passthrough" query described in the docs, including udf's, example:

select * from "select foo, max(baz) from mytable where pinot_udf(bar) = 'value' group by foo"

Ahh ok, I tried this couple of months ago and it was not working, not sure I missed something. Are you saying it is working for you ? Are you using trinio or presto db ?

trino - if you have a specific query I can help you with it, you can obfuscate the table/column names if you need to 🙂

That explains it , I am using presto db ..!

I would ask in the trino slack. For the pinot connector you just need to specify the controller url's in a catalog file and use the defaults. lmk how it goes, I can help

Thanks @Elon