Aggregation for currency conversion

Hi,

We are migrating our internal datastore to druid and in order to reach feature parity we need to implement some sort of currency conversion. I would like to explain the feature we are trying to implement and I would like to get recommendation if our way to implement is the correct one. In our data that we inject to druid we have revenues in a reference currency, for instance eur. However we offer to our customer the capability to see data in any currency by applying exchange rates as given by the European Central Bank. This is an example of data we have already right now in druid

Day,Site,Revenues(EUR)

20170101,Site1,5

20170101,Site2,7

20170101,Site3,2

20170102,Site1,4

20170103,Site2,5

I want to be able to do a query where I group by Site and I see revenues for example in USD. Therefore assuming that on 20170101 we had a factor of 1.08 and on 20170102 we had 1.05 and on 20170103 we had 1.07 as final result I will get

Site1, 5x1.08+4x1.05

Site2, 7x1.08+5x1.07

Site3, 2x1.08

I think that we could implement this by implementing an aggregation. I would do the first proof of concept by using a javascript aggregation. My idea would be to get exchange rates from a json file (or another database like done for lookups) and then make sure that during the aggregation the right factors are applied. Do you think that this would work? What do you think about performance? If we would implement this with an extension would performance be much better?

An additional question would also be, how do I pass the target currency to the query and the javascript function of the aggregator? I read the documentation but I was not able to find an answer.

Thank you very much for your support.

Kind regards,

Silvio

Silvio,
Would you know all the target currencies upfront or is that list dynamic?

If it is known beforehand can’t all the target conversions done in the query?This can be achieved without java script aggregator and using the built in post aggregators.

Dear Rohit,

Thank you very much for your answer. I don’t believe that here post-aggregation would help because as you can see in my example in the previous message for each day I need to consider a different exchange rate and in the final result day is not going to appear anymore (I am doing a group by site).

The list of currencies is known when we create the query and of course it must be part of a set of predefined currencies that is fixed.

Please let me know if I am overseeing something.

Kind regards,

Silvio

Silvio,
Would an inner query help here?

Where the result of inner query could be at site,date,currency level and outer query can aggregate it further at site,currency level.

I am not sure of the comparison of performance of this approach with the java script aggregator that you proposed because even this approach would be very resource intensive.

Hi Rohit,

Thank you very much for your reply. The approach that you propose is very interesting. Is there a way to do inner queries with REST interface? If not probably it might be worth to try plyQL. Do you know how plyQL is handling this kind of queries?

Thank you very much.

Kind regards,

Silvio

Yes you can do inner queries via REST interface by using the "Query Data Source” .
In short,you define a query which can be used as datasource for outer query.
http://druid.io/docs/latest/querying/datasource.html

Wow! this is really great! Ok we are going to test