Hi Team, I am trying Ingestion aggregation over a...
# troubleshooting
a
Hi Team, I am trying Ingestion aggregation over a TIMESTAMP column at day level buckets along with some other columns as group by's the kafka message that is produced to the topic is
Copy code
{
  "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
Copy code
{
  "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
Copy code
{
  "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
Copy code
"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
Copy code
"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.
x
how does the value in
aggregated_created_at
look like? you can sue dateTrunc function to make your time into daily bucket