Roll-up by date column

Hi , I am having hard time to roll-up by a column . I have a csv data and loading it into druid. Now, I need to again roll-up based on a column date. But I don’t have similar record on other column for each similar dates.

So, is it possible to roll-up again ( or show by some query ) by date column only. Example I have a column of date 13/01/2020, then I need to roll up that irrespective of other column with distinct information without removing it from spec?

Providing the result after query in attachment.

query-ac1dddb3-52b1-40f8-8bd2-fdcfd066b39d.csv (1.76 KB)

Does a GROUP BY Date query work?

Yes , it works. But is there something we can do in ingestion spec to achieve the same ?

Hi,

If I understand correctly, you can change the granularity in ingestion spec.

Rohit Ghosh rohitcyclone01@gmail.com 于2020年3月30日周一 下午5:03写道:

Hi , what granularity should I mention ?

this is my current spec :-

{

“type”: “index_parallel”,

“spec”: {

“ioConfig”: {

“appendToExisting”: true,

“type”: “index_parallel”,

“firehose”: {

“type”: “local”,

“baseDir”: “/Users/rg/Downloads”,

“filter”: “thirdrecord.csv”

}

},

“tuningConfig”: {

“type”: “index_parallel”

},

“dataSchema”: {

“dataSource”: “testcsv2”,

“granularitySpec”: {

“type”: “uniform”,

“segmentGranularity”: “DAY”,

“queryGranularity”: “DAY”,

“rollup”: true

},

“parser”: {

“type”: “string”,

“parseSpec”: {

“format”: “csv”,

“timestampSpec”: {

“column”: “created_at”,

“format”: “auto”

},

“hasHeaderRow”: true,

“dimensionsSpec”: {

“dimensions”: [

“date”,

“event_type”,

“user_id”

]

}

}

},

“metricsSpec”: [

{

“name”: “count”,

“type”: “count”

},

{

“name”: “no_of_participants”,

“type”: “longSum”,

“fieldName”: “no_of_participants”

}

]

}

}

}

``

Hi,

In your case, data is already aggregated by date, and current aggregate granularity is DAY, i.e, data on the same day will be aggreagated, see 1. When you query by day, druid will return aggregated data.

If you need different aggregate granularity, you can change queryGranularity in granularitySpec.

Rohit Ghosh rohitcyclone01@gmail.com 于2020年3月31日周二 下午2:28写道:

Hi ,

I understood this one, but my problem is instead of doing GROUP BY using query , is anything we can do from spec ? My current aggregate granularity is DAY which completely fine. And the result by query gives me what I want to achieve.

my query is
SELECT “date” , “event_type”, SUM(no_of_participants) AS “no_of_participants” FROM “testcsv2” GROUP BY “date” , “event_type”

``