Rajat
05/30/2025, 10:39 AMConsumed 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:
{
"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???Xiang Fu
Xiang Fu
is_deleted you could query from the tableXiang Fu
SET skipUpsert=true;
SELECT * FROM shipmentMerged_final WHERE s_id = '844881521'Xiang Fu
"transformFunction": "equals(op, 'd')" in the transformConfigsRajat
05/30/2025, 4:06 PM