Arkoprovo Sircar
04/10/2025, 5:24 PM{
"created_at": "2024-11-06T18:19:10Z",
"producer_message_id": "msg-6",
"request_metadata": {
"request": {
"account": "account123",
"path": "/api/v1/resource",
"query_string": "param1=value1",
"verb": "GET",
"application_id": "app-xyz",
"is_internal_app": true,
"invoker_ip": "192.168.1.1",
"authorization": "Bearer token"
},
"response": {
"elapsed_time": 120,
"response_status": 200
}
},
"request_metadata.response.elapsed_time": 120,
"request_metadata.response.response_status": 200
}
created_at is at ISO format
New schema
{
"schemaName": "ApiGatewayUsage2_DailyAggregated",
"dimensionFieldSpecs": [
{
"name": "request_metadata.request.account",
"dataType": "STRING"
},
{
"name": "request_metadata.request.application_id",
"dataType": "STRING"
},
{
"name": "request_metadata.request.is_internal_app",
"dataType": "BOOLEAN"
}
],
"metricFieldSpecs": [
{
"name": "TotalCallVolume",
"dataType": "LONG"
}
],
"dateTimeFieldSpecs": [
{
"name": "aggregated_created_at",
"dataType": "TIMESTAMP",
"notNull": false,
"format": "TIMESTAMP",
"granularity": "1:DAYS"
}
]
}
and the table config
{
"tableName": "ApiGatewayUsage2_DailyAggregated",
"tableType": "REALTIME",
"tenants": {
"broker": "DefaultTenant",
"server": "DefaultTenant",
"tagOverrideConfig": {}
},
"segmentsConfig": {
"schemaName": "ApiGatewayUsage2_DailyAggregated",
"timeColumnName": "aggregated_created_at",
"replication": "1",
"replicasPerPartition": "1",
"retentionTimeUnit": "DAYS",
"retentionTimeValue": "30"
},
"tableIndexConfig": {
"loadMode": "MMAP",
"invertedIndexColumns": [],
"createInvertedIndexDuringSegmentGeneration": false,
"rangeIndexColumns": [],
"sortedColumn": [
"aggregated_created_at"
],
"bloomFilterColumns": [],
"noDictionaryColumns": [
"TotalCallVolume"
],
"onHeapDictionaryColumns": [],
"varLengthDictionaryColumns": [],
"enableDefaultStarTree": false,
"nullHandlingEnabled": false,
"streamConfigs": {
"streamType": "kafka",
"stream.kafka.topic.name": "testkafka1",
"stream.kafka.broker.list": "host.docker.internal:19092",
"stream.kafka.consumer.type": "lowlevel",
"stream.kafka.consumer.prop.auto.offset.reset": "smallest",
"stream.kafka.consumer.factory.class.name": "org.apache.pinot.plugin.stream.kafka20.KafkaConsumerFactory",
"stream.kafka.decoder.class.name": "org.apache.pinot.plugin.stream.kafka.KafkaJSONMessageDecoder",
"realtime.segment.flush.threshold.rows": "0",
"realtime.segment.flush.threshold.segment.rows": "0",
"realtime.segment.flush.threshold.time": "24h",
"realtime.segment.flush.threshold.segment.size": "100M"
}
},
"ingestionConfig": {
"continueOnError": true,
"rowTimeValueCheck": false,
"segmentTimeValueCheck": true,
"transformConfigs": [
{
"columnName": "aggregated_created_at",
"transformFunction": "DATETIMECONVERT(created_at, '1:MILLISECONDS:EPOCH', '1:DAYS:SIMPLE_DATE_FORMAT:yyyy-MM-dd', '1:DAYS')"
}
],
"aggregationConfigs": [
{
"columnName": "TotalCallVolume",
"aggregationFunction": "COUNT(*)"
}
]
},
"routing": {
"instanceSelectorType": "replicaGroup"
}
}
I tried transforming the created_at into aggregated_created_at of day bucket only by trying to ignore hours, minute, second etc
"transformConfigs": [
{
"columnName": "aggregated_created_at",
"transformFunction": "DATETIMECONVERT(created_at, '1:MILLISECONDS:EPOCH', '1:DAYS:SIMPLE_DATE_FORMAT:yyyy-MM-dd', '1:DAYS')"
}
],
and aggregation config as
"aggregationConfigs": [
{
"columnName": "TotalCallVolume",
"aggregationFunction": "COUNT(*)"
}
]
although count(*) working, but date is not transforming properly into it's day level buckets
Also, the other columns for request_metadata.request.account request_metadata.request.application_id and request_metadata.request.is_internal_app are coming as null (in case of the boolean one, coming false).
Please help me here.Xiang Fu
aggregated_created_at look like? you can sue dateTrunc function to make your time into daily bucket