Trouble Calculating values

Hello,

I am working on a Druid POC, I am able to stand up druid and process the data. However, I am trying to setup a metricSpec with javascript type, to divide two numbers. Here is the snippet of code:

{

“type”: “javascript”,

“name”: “win_rate”,

“fieldNames”: [“wins”, “bids”],

“fnAggregate” : “function(current, a, b) { return current + (a / b); }”,

“fnCombine” : “function(partialA, partialB) { return partialA + partialB; }”,

“fnReset” : “function() { return 0; }”
}

``

When I connect the druid cluster to our instance of pivot, I am getting no value. I also tried to connect it to panoramix and was still not getting an issue. Can anyone please help me try to troubleshoot this? Here is my spec file that I am using:

[

{

“dataSchema” : {

“dataSource” : “reporting”,

“parser” : {

“type” : “string”,

“parseSpec” : {

“format” : “json”,

“timestampSpec” : {

“column” : “timestamp”,

“format” : “auto”

},

“dimensionsSpec” : {

“dimensions”: [“adid”,“name”,“strategyname”,“mediaid”],

“dimensionExclusions” : ,

“spatialDimensions” :

}

}

},

“metricsSpec” : [{

“type” : “count”,

“name” : “count”

}, {

“type” : “longSum”,

“name” : “count_requests”,

“fieldName” : “requests”

}, {

“type” : “longSum”,

“name” : “count_bids”,

“fieldName” : “bids”

}, {

“type” : “longSum”,

“name” : “count_wins”,

“fieldName” : “wins”

}, {

“type” : “longSum”,

“name” : “count_clicks”,

“fieldName” : “clicks”

}, {

“type” : “doubleSum”,

“name” : “count_spend”,

“fieldName” : “spend”

}, {

“type”: “javascript”,

“name”: “win_rate”,

“fieldNames”: [“wins”, “bids”],

“fnAggregate” : “function(current, a, b) { return current + (a / b); }”,

“fnCombine” : “function(partialA, partialB) { return partialA + partialB; }”,

“fnReset” : “function() { return 0; }”

}, {

“type”: “javascript”,

“name”: “ctr”,

“fieldNames”: [“clicks”, “wins”],

“fnAggregate” : “function(current, a, b) { return current + (a/b).toFixed(2); }”,

“fnCombine” : “function(partialA, partialB) { return partialA + partialB; }”,

“fnReset” : “function() { return 0; }”

},{

“type” : “doubleSum”,

“name” : “count_imps”,

“fieldName” : “imps”

}],

“granularitySpec” : {

“type” : “uniform”,

“segmentGranularity” : “FIVE_MINUTE”,

“queryGranularity” : “NONE”

}

},

“ioConfig” : {

“type” : “realtime”,

“firehose”: {

“type”: “kafka-0.8”,

“consumerProps”: {

“zookeeper.connect”: “localhost:2181”,

“zookeeper.connection.timeout.ms” : “15000”,

“zookeeper.session.timeout.ms” : “15000”,

“zookeeper.sync.time.ms” : “5000”,

“group.id”: “reporting-example_test”,

“fetch.message.max.bytes” : “1048586”,

“auto.offset.reset”: “largest”,

“auto.commit.enable”: “false”

},

“feed”: “rreporting”

},

“plumber”: {

“type”: “realtime”

}

},

“tuningConfig”: {

“type” : “realtime”,

“maxRowsInMemory”: 500000,

“intermediatePersistPeriod”: “PT3m”,

“windowPeriod”: “PT1m”,

“basePersistDirectory”: “/tmp/realtime/basePersist”,

“rejectionPolicy”: {

“type”: “serverTime”

}

}

}

]

``

Thanks,

Gabe

Please look at this example of how a divide operation should be done in Druid:
http://druid.io/docs/latest/querying/post-aggregations.html

I strongly recommend you do not use javascript aggregators.

If you are more comfortable with SQL, I suggest you use https://github.com/implydata/plyql

That tool will convert your SQL query into an optimal Druid query

There is a verbose flag so you can view the actual Druid query being sent.

Hi Fangjin,

Thanks for your help, quick question can you please provide an example of how to setup the post aggregation? Idk if I reading the document correct but it looks like it has to be part of a query. Is this correct or can I add the post aggregator to the .spec file?

We are using the pivot UI so we want all the aggregation to be done at the spec file.

Thanks,
Gabe

Gabriel, the post aggregation is sent as part of the query. Similar to the “aggregations” object, you specify “postAggregations” as another field in the query. In Pivot, you can define derived measures in the Pivot config file, which translate to queries with post aggregations when Pivot actually talks to Druid.

The pivot example config has examples of post aggregations: https://github.com/implydata/pivot/blob/master/config.yaml.sample

Thanks Fangjin,

That worked! It is so much easy to use the post aggregations objects rather than trying to troubleshoot the javascript functions.

Thanks again!

Gabe