Hi guys, these are the two messages in my kafka to...
# troubleshooting
r
Hi guys, these are the two messages in my kafka topic: @Xiang Fu @Mayank @Jackie
Copy code
Consumed message: key = 844881521, value = {"bch_name": "SHOPIFY", "bch_code": "SH", "ar_awb_id": null, "ar_zone": null, "ch_id": 2993000, "ch_name": "Shopify", "ch_company_id": 18682, "ch_base_channel_code": "SH", "am_awb_code": null, "am_ofd1": null, "am_picked_up_date": null, "o_id": 848523000, "o_company_id": 18682, "o_channel_id": 2993000, "o_shipping_method": "SR", "o_sla": 48, "o_customer_city": "Fatehpur", "o_customer_state": "Uttar Pradesh", "o_customer_pincode": "212655", "o_payment_method": "cod", "o_net_total": "\u0002¾¼", "o_total": "\u0002Ñà", "o_created_at": "2025-05-29 10:22:22", "co_id": null, "co_mode": null, "a_id": null, "a_awb_code": null, "a_cod": null, "a_shipment_id": null, "a_applied_weight_amount": null, "a_charge_weight_amount": null, "s_id": 844881521, "s_order_id": 848523000, "s_company_id": 18682, "s_courier": null, "s_sr_courier_id": null, "s_awb": null, "s_awb_assign_date": null, "s_total": "\u0002Ñà", "s_status": 11, "s_shipped_date": null, "s_delivered_date": null, "s_rto_initiated_date": null, "s_rto_delivered_date": null, "s_created_at": "2025-05-29 10:22:33", "s_updated_at": "2025-05-29 10:22:35", "s_pickup_scheduled_date": null, "s_etd": null, "op": "u", "awbs_source": "Unknown", "couriers_source": "Unknown", "ts_ms_source_kafka": 1748494354367, "ts_ms_merged_kafka": 1748494355210}, partition = 3, offset = 15113790, timestamp = 2025-05-29T04:52:35.213Z
[4:03 PM] Kavya Ramaiah

Consumed message: key = 844881521, value = {"bch_name": null, "bch_code": null, "ar_awb_id": null, "ar_zone": null, "ch_id": null, "ch_name": null, "ch_company_id": null, "ch_base_channel_code": null, "am_awb_code": null, "am_ofd1": null, "am_picked_up_date": null, "o_id": null, "o_company_id": null, "o_channel_id": null, "o_shipping_method": null, "o_sla": null, "o_customer_city": null, "o_customer_state": null, "o_customer_pincode": null, "o_payment_method": null, "o_net_total": null, "o_total": null, "o_created_at": null, "co_id": null, "co_mode": null, "a_id": null, "a_awb_code": null, "a_cod": null, "a_shipment_id": null, "a_applied_weight_amount": null, "a_charge_weight_amount": null, "s_id": 844881521, "s_order_id": null, "s_company_id": null, "s_courier": null, "s_sr_courier_id": null, "s_awb": null, "s_awb_assign_date": null, "s_total": null, "s_status": null, "s_shipped_date": null, "s_delivered_date": null, "s_rto_initiated_date": null, "s_rto_delivered_date": null, "s_created_at": "2025-05-29 10:22:33", "s_updated_at": null, "s_pickup_scheduled_date": null, "s_etd": null, "op": "d", "awbs_source": null, "couriers_source": null, "ts_ms_source_kafka": 1748494359974, "ts_ms_merged_kafka": null}, partition = 3, offset = 15114045, timestamp = 2025-05-29T04:52:41.778Z
Ideally in pinot it should Delete them as I am using this tableConfig:
Copy code
{
  "tableName": "shipmentMerged_final",
  "tableType": "REALTIME",
  "segmentsConfig": {
    "timeColumnName": "s_created_at",
    "timeType": "DAYS",
    "replication": "2",
    "retentionTimeUnit": "DAYS",
    "retentionTimeValue": "3",
    "minimizeDataMovement": false
  },
  "tableIndexConfig": {
    "loadMode": "MMAP",
    "nullHandlingEnabled": true,
    "createInvertedIndexDuringSegmentGeneration": true,
    "invertedIndexColumns": [
      "o_customer_city",
      "o_customer_pincode",
      "o_customer_state",
      "s_company_id",
      "s_courier",
      "o_shipping_method",
      "o_payment_method",
      "s_status",
      "s_sr_courier_id"
    ],
    "noDictionaryColumns": [
      "s_etd",
      "s_shipped_date",
      "a_awb_code",
      "s_order_id",
      "s_id",
      "a_id",
      "o_id",
      "a_shipment_id",
      "s_awb_assign_date",
      "s_delivered_date",
      "s_awb",
      "s_rto_initiated_date",
      "s_pickup_scheduled_date",
      "a_applied_weight_amount_double",
      "o_total_double",
      "o_created_at",
      "s_updated_at",
      "o_net_total_double",
      "a_charge_weight_amount_double",
      "s_rto_delivered_date",
      "ar_awb_id",
      "am_picked_up_date",
      "am_ofd1",
      "s_total_double",
      "ts_ms_merged_kafka"
    ],
    "bloomFilterColumns": [
      "s_company_id"
    ],
    "sortedColumn": [
      "s_company_id"
    ],
    "varLengthDictionaryColumns": [
      "o_customer_state",
      "ar_zone",
      "s_courier",
      "o_customer_pincode",
      "o_payment_method",
      "o_shipping_method",
      "o_customer_city"
    ]
  },
  "ingestionConfig": {
    "streamIngestionConfig": {
      "streamConfigMaps": [
        {
          "streamType": "kafka",
          "stream.kafka.consumer.type": "lowlevel",
          "stream.kafka.decoder.prop.format": "AVRO",
          "stream.kafka.consumer.group.id": "shipmentMerged-consumer-group",
          "stream.kafka.decoder.prop.schema.registry.rest.url": "http://internal-adfbe53cf874c419b80ef29810ee56b7-1168949678.ap-south-1.elb.amazonaws.com:8081",
          "stream.kafka.topic.name": "pinot_d0_d2_realtime",
          "stream.kafka.consumer.factory.class.name": "org.apache.pinot.plugin.stream.kafka20.KafkaConsumerFactory",
          "stream.kafka.decoder.class.name": "org.apache.pinot.plugin.inputformat.avro.confluent.KafkaConfluentSchemaRegistryAvroMessageDecoder",
          "stream.kafka.broker.list": "internal-a01a7420dce764739aecf132fdd316d8-1810051101.ap-south-1.elb.amazonaws.com:9094",
          "stream.kafka.schema.registry.url": "http://internal-adfbe53cf874c419b80ef29810ee56b7-1168949678.ap-south-1.elb.amazonaws.com:8081",
          "realtime.segment.flush.threshold.time": "24h",
          "realtime.segment.flush.threshold.segment.size": "150M",
          "stream.kafka.consumer.prop.auto.offset.reset": "smallest"
        }
      ]
    },
    "transformConfigs": [
      {
        "columnName": "ingestion_ts",
        "transformFunction": "now()"
      },
      {
        "columnName": "is_deleted",
        "transformFunction": "compareFields(op, 'd')"
      },
      {
        "columnName": "s_total_double",
        "transformFunction": "bytesToDouble(o_net_total, 10, 2)"
      },
      {
        "columnName": "o_net_total_double",
        "transformFunction": "bytesToDouble(o_net_total, 10, 2)"
      },
      {
        "columnName": "o_total_double",
        "transformFunction": "bytesToDouble(o_total, 10, 2)"
      },
      {
        "columnName": "a_applied_weight_amount_double",
        "transformFunction": "bytesToDouble(a_applied_weight_amount, 10, 2)"
      },
      {
        "columnName": "a_charge_weight_amount_double",
        "transformFunction": "bytesToDouble(a_charge_weight_amount, 10, 2)"
      }
    ]
  },
  "routing": {
    "instanceSelectorType": "strictReplicaGroup"
  },
  "upsertConfig": {
    "mode": "FULL",
    "consistencyMode": "SYNC",
    "comparisonColumns": [
      "ts_ms_source_kafka"
    ],
    "deleteRecordColumn": "is_deleted",
    "dropOutOfOrderRecord": true
  },
  "tenants": {},
  "metadata": {
    "customConfigs": {}
  }
}
In this I am using Upsert's delete record config to delete the records once is_deleted is true and that is true is op=d Why this happened can anyone give any suggestion of loophole???
what’s the value of
is_deleted
you could query from the table
can you try
Copy code
SET skipUpsert=true;
SELECT * FROM shipmentMerged_final WHERE s_id = '844881521'
have your tried
"transformFunction": "equals(op, 'd')"
in the
transformConfigs
r
yeah we found the issue, it was coming because for op=d there was updated_at as null so it wasn't taking that for the latest update. thanks for the help