aggregation groupby runs failed, the result is always 0

Hi,

I ingest the some data using this json file and it shows successfully. I can get every row from DOC_ID using selector method. And counting number is OK too.

But when I use aggregation groupby, the value always is 0. Do I make mistake ? :frowning:

curl -X POST ‘http://localhost:8082/druid/v2/?pretty’ -H ‘content-type: application/json’ -d ’

{

“queryType”: “groupBy”,

“dataSource”: “cm_sample”,

“dimensions”: [“TITLE”],

“aggregations”:[

{“type”:“longSum”,“name”:“BUSS_TABLE_IDA”,“fieldName”:“AMOUNT”}

],

“granularity”: “all”,

“intervals”: [

“2016-01-01T00:00:00.000/2016-01-20T00:00:00.000”

]

}’

Hi Gary,

To perform an aggregation on AMOUNT, it needs to be declared as a metric in your ingestion spec and not as a dimension. Remove it from the list of dimensions and then add an appropriate aggregator to the metricsSpec. In the case of your query you would probably want something like:

       "metricsSpec": [
           {
               "type": "count",
               "name": "count"
           },
           {
               "type": "longSum",
               "name": "AMOUNT",
               "fieldName": "AMOUNT"
           }]

See http://druid.io/docs/latest/querying/aggregations.html for more details on aggregation types.

To be clear, after modifying your ingestion spec you’ll need to rerun the Hadoop indexing job to generate a new version of the segment before your query will succeed.

Hi David

Thanks for your quick reply.

In that website, it writes “Aggregations can be provided at ingestion time as part of the ingestion spec as a way of summarizing data before it enters Druid. Aggregations can also be specified as part of many queries at query time.”

In my understanding, if the user defines the special metric before ingesting the data, druid can do pre-computation. If the user doesn’t define the metric, druid will start instant calculating, though it is slow.

Am I wrong???

Gary

Hey Gary,

Unfortunately, right now the metric must be defined in the schema at ingestion time to support Druid’s roll-up mechanism (http://druid.io/docs/latest/design/). There have been requests for no-roll-up / schemaless ingestion but this has not yet been implemented.