Hi Team I have problem with this timestamp We have this date Apache Pinot #troubleshooting

Hi Team, I have problem with this timestamp, We ha...

Sandeep R

03/18/2022, 1:19 AM

Hi Team, I have problem with this timestamp, We have this date format in table "LOG_TS": "2022-03-09T164742.995+00:00", and I am adding below date format, not sure if this is correct format?

Copy code

"name": "LOG_TS",
      "dataType": "LONG",
      "format": "1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-mm-ddThh:mm:ss.sssZ",
      "granularity": "1:MILLISECONDS"

Sandeep R

03/18/2022, 1:20 AM

here is table

Copy code

{
  "REALTIME": {
    "tableName": "uapinew_REALTIME",
    "tableType": "REALTIME",
    "segmentsConfig": {
      "schemaName": "uapinew",
      "retentionTimeUnit": "DAYS",
      "retentionTimeValue": "100",
      "timeColumnName": "LOG_TS",
      "allowNullTimeValue": false,
      "replication": "2",
      "replicasPerPartition": "2"
    },
    "tenants": {
      "broker": "DefaultTenant",
      "server": "DefaultTenant",
      "tagOverrideConfig": {}
    },
    "tableIndexConfig": {
      "invertedIndexColumns": [],
      "noDictionaryColumns": [],
      "rangeIndexColumns": [],
      "rangeIndexVersion": 1,
      "autoGeneratedInvertedIndex": false,
      "createInvertedIndexDuringSegmentGeneration": false,
      "sortedColumn": [],
      "bloomFilterColumns": [],
      "loadMode": "MMAP",
      "streamConfigs": {
        "streamType": "kafka",
        "stream.kafka.topic.name": "uapitranlog2",
        "stream.kafka.broker.list": "localhost:6667",
        "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.time": "24h",
        "realtime.segment.flush.segment.size": "100M"
      },
      "onHeapDictionaryColumns": [],
      "varLengthDictionaryColumns": [],
      "enableDefaultStarTree": false,
      "enableDynamicStarTreeCreation": false,
      "aggregateMetrics": false,
      "nullHandlingEnabled": false
    },
    "metadata": {},
    "quota": {},
    "routing": {},
    "query": {},
    "fieldConfigList": [
      {
        "name": "SCHEMA_VERSION_NM",
        "encodingType": "RAW",
        "indexType": "TEXT",
        "indexTypes": [
          "TEXT"
        ]
      }
    ],
    "ingestionConfig": {},
    "isDimTable": false
  }
}

Sandeep R

03/18/2022, 1:20 AM

Here is Schema

Copy code

{
  "schemaName": "uapinew",
  "dimensionFieldSpecs": [
    {
      "name": "LOG_ID",
      "dataType": "STRING"
    },
    {
      "name": "REQUEST_TYPE_ID",
      "dataType": "STRING"
    },
    {
      "name": "APP_SERV_HOST_NM",
      "dataType": "STRING"
    },
    {
      "name": "SCHEMA_VERSION_NM",
      "dataType": "STRING"
    },
    {
      "name": "AGENCY_ID",
      "dataType": "STRING"
    },
    {
      "name": "BRANCH_CD",
      "dataType": "STRING"
    },
    {
      "name": "TARGET_BRANCH_CD",
      "dataType": "STRING"
    },
    {
      "name": "PCC_CD",
      "dataType": "STRING"
    },
    {
      "name": "AGENT_CD",
      "dataType": "STRING"
    },
    {
      "name": "ORIGIN_APP_CD",
      "dataType": "STRING"
    },
    {
      "name": "SUCCESS_IND",
      "dataType": "STRING"
    },
    {
      "name": "BOOKED_SEG_QTY",
      "dataType": "INT"
    },
    {
      "name": "AIR_SEG_BOOKED",
      "dataType": "STRING"
    },
    {
      "name": "AIR_SEG_CANCELLED",
      "dataType": "STRING"
    },
    {
      "name": "CAR_SEG_BOOKED",
      "dataType": "STRING"
    },
    {
      "name": "CAR_SEG_CANCELLED",
      "dataType": "STRING"
    },
    {
      "name": "HOTEL_SEG_BOOKED",
      "dataType": "STRING"
    },
    {
      "name": "HOTEL_SEG_CANCELLED",
      "dataType": "STRING"
    },
    {
      "name": "PROVIDER_CD",
      "dataType": "STRING"
    },
    {
      "name": "SUPPLIER_CD",
      "dataType": "STRING"
    },
    {
      "name": "PROVIDER_CD_LIST",
      "dataType": "STRING"
    },
    {
      "name": "SUPPLIER_CD_LIST",
      "dataType": "STRING"
    },
    {
      "name": "TRAVEL_ORIGIN_CD",
      "dataType": "STRING"
    },
    {
      "name": "TRAVEL_DEST_CD",
      "dataType": "STRING"
    },
    {
      "name": "TRAVEL_START_DT",
      "dataType": "STRING"
    },
    {
      "name": "TRAVEL_END_DT",
      "dataType": "STRING"
    },
    {
      "name": "NUM_TRAVELERS_QTY",
      "dataType": "STRING"
    },
    {
      "name": "UR_LOCATOR_CD",
      "dataType": "STRING"
    },
    {
      "name": "ONE_WAY_SHOP_IND",
      "dataType": "STRING"
    },
    {
      "name": "HOST_LOCATOR_CD",
      "dataType": "STRING"
    },
    {
      "name": "SUPPLIER_LOCATOR_CD",
      "dataType": "STRING"
    },
    {
      "name": "OPTIONAL_SVC_QTY",
      "dataType": "STRING"
    },
    {
      "name": "TRAVEL_SEGMENTS",
      "dataType": "STRING"
    },
    {
      "name": "TRAVEL_DATES",
      "dataType": "STRING"
    },
    {
      "name": "FLIGHT_NBR",
      "dataType": "STRING"
    },
    {
      "name": "CLASS_OF_SERVICE",
      "dataType": "STRING"
    },
    {
      "name": "AVAILABILITY_SOURCE",
      "dataType": "STRING"
    },
    {
      "name": "NBR_IN_PARTY",
      "dataType": "STRING"
    },
    {
      "name": "HOTEL_CODE",
      "dataType": "STRING"
    },
    {
      "name": "HOTEL_SEARCH_BY_ADDR_IND",
      "dataType": "STRING"
    },
    {
      "name": "SSL_TERM_REGION",
      "dataType": "STRING"
    },
    {
      "name": "REQUEST_BASE_PRICE",
      "dataType": "STRING"
    },
    {
      "name": "RESPONSE_BASE_PRICE",
      "dataType": "STRING"
    },
    {
      "name": "BASE_DIFFERENCE_TOTAL",
      "dataType": "STRING"
    },
    {
      "name": "REQUEST_TOTAL_PRICE",
      "dataType": "STRING"
    },
    {
      "name": "RESPONSE_TOTAL_PRICE",
      "dataType": "STRING"
    },
    {
      "name": "TOTAL_DIFFERENCE_PRICE",
      "dataType": "STRING"
    },
    {
      "name": "SPLIT_TICKET_SEARCH",
      "dataType": "STRING"
    },
    {
      "name": "SPLIT_TICKET_VALID",
      "dataType": "STRING"
    },
    {
      "name": "SPLIT_TICKET_PERCENT",
      "dataType": "STRING"
    },
    {
      "name": "GAL_RT_MILLISEC_QTY",
      "dataType": "STRING"
    },
    {
      "name": "APO_RT_MILLISEC_QTY",
      "dataType": "STRING"
    },
    {
      "name": "WSP_RT_MILLISEC_QTY",
      "dataType": "STRING"
    },
    {
      "name": "JAL_RT_MILLISEC_QTY",
      "dataType": "STRING"
    },
    {
      "name": "ACH_RT_MILLISEC_QTY",
      "dataType": "STRING"
    },
    {
      "name": "RCH_RT_MILLISEC_QTY",
      "dataType": "STRING"
    },
    {
      "name": "MCH_RT_MILLISEC_QTY",
      "dataType": "STRING"
    },
    {
      "name": "TRM_RT_MILLISEC_QTY",
      "dataType": "STRING"
    },
    {
      "name": "SVCS_RT_MILLISEC_QTY",
      "dataType": "STRING"
    },
    {
      "name": "REQUEST_BYTES_QTY",
      "dataType": "STRING"
    },
    {
      "name": "RESPONSE_BYTES_QTY",
      "dataType": "STRING"
    },
    {
      "name": "ERROR_LEVEL_CD",
      "dataType": "STRING"
    },
    {
      "name": "ERROR_CD",
      "dataType": "STRING"
    },
    {
      "name": "ERROR_TYPE_CD",
      "dataType": "STRING"
    },
    {
      "name": "ERROR_TEXT",
      "dataType": "STRING"
    },
    {
      "name": "SOAP_FAULT_TEXT",
      "dataType": "STRING"
    },
    {
      "name": "TRACE_ID",
      "dataType": "STRING"
    },
    {
      "name": "FARE_RULES_FILTER_CAT_CODES",
      "dataType": "STRING"
    },
    {
      "name": "REQ_RATE_PLAN",
      "dataType": "STRING"
    },
    {
      "name": "NUM_ADULTS",
      "dataType": "STRING"
    },
    {
      "name": "NUM_ROOMS",
      "dataType": "STRING"
    },
    {
      "name": "GUARANTEE_TYPE",
      "dataType": "STRING"
    },
    {
      "name": "OTHER_GUARANTEE_INFO",
      "dataType": "STRING"
    },
    {
      "name": "RSP_RATE_PLAN",
      "dataType": "STRING"
    },
    {
      "name": "RESPONSE_MSG_CODES",
      "dataType": "STRING"
    },
    {
      "name": "HOTEL_CONF_NUM",
      "dataType": "STRING"
    },
    {
      "name": "HOTEL_STATUS",
      "dataType": "STRING"
    },
    {
      "name": "LANGUAGE_CODE",
      "dataType": "STRING"
    },
    {
      "name": "PICKUP_LOC_TYPE",
      "dataType": "STRING"
    },
    {
      "name": "RETURN_LOC_TYPE",
      "dataType": "STRING"
    },
    {
      "name": "PICKUP_LOC_NUM",
      "dataType": "STRING"
    },
    {
      "name": "RETURN_LOC_NUM",
      "dataType": "STRING"
    },
    {
      "name": "VEH_RATE_CODE",
      "dataType": "STRING"
    },
    {
      "name": "VEH_RATE_CAT",
      "dataType": "STRING"
    },
    {
      "name": "VEH_RATE_PERIOD",
      "dataType": "STRING"
    },
    {
      "name": "VEH_DISCOUNT_NUM",
      "dataType": "STRING"
    },
    {
      "name": "ACRISS_VEH_CODE",
      "dataType": "STRING"
    },
    {
      "name": "VEH_RATE_FOR_PERIOD",
      "dataType": "STRING"
    },
    {
      "name": "RSP_BASE_RATE",
      "dataType": "STRING"
    },
    {
      "name": "CDS_TXID",
      "dataType": "STRING"
    },
    {
      "name": "CDS_TIME",
      "dataType": "STRING"
    },
    {
      "name": "CDS_STATUS",
      "dataType": "STRING"
    },
    {
      "name": "CDS_ERROR_MSG",
      "dataType": "STRING"
    },
    {
      "name": "ESTIMATED_TOTAL_AMOUNT",
      "dataType": "STRING"
    },
    {
      "name": "MANDATORY_CHARGE_TOTAL",
      "dataType": "STRING"
    },
    {
      "name": "AIR_CONDITIONING",
      "dataType": "STRING"
    },
    {
      "name": "TRANSMISSION_TYPE",
      "dataType": "STRING"
    },
    {
      "name": "VEHICLE_CLASS",
      "dataType": "STRING"
    },
    {
      "name": "CATEGORY",
      "dataType": "STRING"
    },
    {
      "name": "VEHICLE_TYPE",
      "dataType": "STRING"
    },
    {
      "name": "DOOR_COUNT",
      "dataType": "STRING"
    },
    {
      "name": "AUX1",
      "dataType": "STRING"
    },
    {
      "name": "AUX2",
      "dataType": "STRING"
    },
    {
      "name": "AUX3",
      "dataType": "STRING"
    },
    {
      "name": "CANCELLATION_TEXT",
      "dataType": "STRING"
    },
    {
      "name": "REQUESTED_NEGOTIATED_RATE_CODES_COUNT",
      "dataType": "STRING"
    },
    {
      "name": "RATE_CATEGORY_COUNT",
      "dataType": "STRING"
    },
    {
      "name": "NUMBER_OF_RATE_PLAN_TYPES",
      "dataType": "STRING"
    },
    {
      "name": "NEGOTIATED_RATE_CODES_IN_RESPONSE",
      "dataType": "STRING"
    },
    {
      "name": "RESPONSE_NEGOTIATED_RATE_CODE_COUNT",
      "dataType": "STRING"
    },
    {
      "name": "REQUESTED_VENDOR_LOCATION_COUNT",
      "dataType": "STRING"
    },
    {
      "name": "MAX_WAIT_TIME",
      "dataType": "STRING"
    },
    {
      "name": "RESPONSE_PROPERTIES_COUNT",
      "dataType": "STRING"
    },
    {
      "name": "TAX",
      "dataType": "STRING"
    },
    {
      "name": "SURCHARGE",
      "dataType": "STRING"
    },
    {
      "name": "COMMISION_INDICATOR",
      "dataType": "STRING"
    },
    {
      "name": "PERCENTAGE",
      "dataType": "STRING"
    },
    {
      "name": "GALILEO_GUARANTEE",
      "dataType": "STRING"
    },
    {
      "name": "GALILEO_DEPOSIT",
      "dataType": "STRING"
    },
    {
      "name": "WORLDSPAN_DEPOSIT_GUARANTEE_PREPAYMENT",
      "dataType": "STRING"
    },
    {
      "name": "UAPI_RESPONSE_TIME_MS",
      "dataType": "STRING"
    },
    {
      "name": "SESSION_KEY",
      "dataType": "STRING"
    },
    {
      "name": "AGENCY_COUNTRY",
      "dataType": "STRING"
    },
    {
      "name": "SOURCE_IP_ADDRESS",
      "dataType": "STRING"
    },
    {
      "name": "ACH_TRANSACTION_ID",
      "dataType": "STRING"
    },
    {
      "name": "DB2_LOCK_TIME",
      "dataType": "STRING"
    },
    {
      "name": "DB2_NUMBER_OF_LOCKS",
      "dataType": "STRING"
    },
    {
      "name": "OVERRIDE_PCC",
      "dataType": "STRING"
    },
    {
      "name": "META_SEARCH",
      "dataType": "STRING"
    },
    {
      "name": "SOLUTION_RESULT",
      "dataType": "STRING"
    },
    {
      "name": "PTC_LIST",
      "dataType": "STRING"
    }
  ],
  "metricFieldSpecs": [
    {
      "name": "TOTAL_MILLISECOND_QTY",
      "dataType": "INT"
    }
  ],
  "dateTimeFieldSpecs": [
    {
      "name": "LOG_TS",
      "dataType": "LONG",
      "format": "1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-mm-ddThh:mm:ss.sssZ",
      "granularity": "1:MILLISECONDS"
    }
  ]
}

Sandeep R

03/18/2022, 1:20 AM

Getting below error , "DOOR_COUNT" : "", "ESTIMATED_TOTAL_AMOUNT" : "", "DB2_NUMBER_OF_LOCKS" : "0", "TRAVEL_END_DT" : "", "ACRISS_VEH_CODE" : "", "SOLUTION_RESULT" : "", "BASE_DIFFERENCE_TOTAL" : "", "RESPONSE_BASE_PRICE" : "", "BOOKED_SEG_QTY" : "0", "REQUESTED_VENDOR_LOCATION_COUNT" : "", "CLASS_OF_SERVICE" : "", "NUM_ADULTS" : "", "SESSION_KEY" : "", "SUPPLIER_LOCATOR_CD" : "", "OTHER_GUARANTEE_INFO" : "", "SSL_TERM_REGION" : "\"216.113.156.89:443|/TF-COM_API/EMEA.universal-api.travelport.com_443\"", "REQUEST_BASE_PRICE" : "", "SOAP_FAULT_TEXT" : "", "SURCHARGE" : "" }, "nullValueFields" : [ ] } java.lang.RuntimeException: Caught exception while transforming data type for column: LOG_TS at org.apache.pinot.segment.local.recordtransformer.DataTypeTransformer.transform(DataTypeTransformer.java:95) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at org.apache.pinot.segment.local.recordtransformer.CompositeTransformer.transform(CompositeTransformer.java:83) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at org.apache.pinot.core.data.manager.realtime.LLRealtimeSegmentDataManager.processStreamEvents(LLRealtimeSegmentDataManager.java:518) [pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at org.apache.pinot.core.data.manager.realtime.LLRealtimeSegmentDataManager.consumeLoop(LLRealtimeSegmentDataManager.java:420) [pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at org.apache.pinot.core.data.manager.realtime.LLRealtimeSegmentDataManager$PartitionConsumer.run(LLRealtimeSegmentDataManager.java:568) [pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at java.lang.Thread.run(Thread.java:834) [?:?] Caused by: java.lang.NumberFormatException: For input string: "2022-03-10T053008.627+00:00" at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) ~[?:?] at java.lang.Long.parseLong(Long.java:692) ~[?:?] at java.lang.Long.parseLong(Long.java:817) ~[?:?] at org.apache.pinot.common.utils.PinotDataType$10.toLong(PinotDataType.java:502) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at org.apache.pinot.common.utils.PinotDataType$6.convert(PinotDataType.java:333) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at org.apache.pinot.common.utils.PinotDataType$6.convert(PinotDataType.java:290) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at org.apache.pinot.segment.local.recordtransformer.DataTypeTransformer.transform(DataTypeTransformer.java:90) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] ... 5 more 2022/03/11 054203.583 ERROR [LLRealtimeSegmentDataManager_uapinew__24__0__20220311T0541Z] [uapinew__24__0__20220311T0541Z] Caught exception while transforming the record: { "fieldToValueMap" : { "TRAVEL_DEST_CD" : "", "PCC_CD" : "3S0E", "SUPPLIER_CD_LIST" : "QR|SU|OS|EK|MU|OZ|LX", "RESPONSE_PROPERTIES_COUNT" : "", "ACH_TRANSACTION_ID" : "", "PROVIDER_CD" : "SVCS", "RESPONSE_BYTES_QTY" : "971876", "VEH_RATE_PERIOD" : "", "JAL_RT_MILLISEC_QTY" : "0", "AUX2" : "", "AUX3" : "", "META_SEARCH" : "", "SVCS_RT_MILLISEC_QTY" : "2797", "RSP_RATE_PLAN" : "", "AUX1" : "", "GALILEO_GUARANTEE" : "", "SCHEMA_VERSION_NM" : "SessionContext_v1|air_v45_0|rail_v45_0", "VEH_RATE_FOR_PERIOD" : "",^Z [4]+ Stopped tail -f pinot-all.log [root@vhldvatlb211 logs]# vi pinot-all.log [5]+ Stopped vi pinot-all.log [root@vhldvatlb211 logs]# vi pinot-all.log [6]+ Stopped vi pinot-all.log [root@vhldvatlb211 logs]# vi pinot-all.log at org.apache.pinot.core.data.manager.realtime.LLRealtimeSegmentDataManager.consumeLoop(LLRealtimeSegmentDataManager.java:420) [pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at org.apache.pinot.core.data.manager.realtime.LLRealtimeSegmentDataManager$PartitionConsumer.run(LLRealtimeSegmentDataManager.java:568) [pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at java.lang.Thread.run(Thread.java:834) [?:?] Caused by: java.lang.NumberFormatException: For input string: "2022-03-10T054328.983+00:00" at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) ~[?:?] at java.lang.Long.parseLong(Long.java:692) ~[?:?] at java.lang.Long.parseLong(Long.java:817) ~[?:?] at org.apache.pinot.common.utils.PinotDataType$10.toLong(PinotDataType.java:502) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at org.apache.pinot.common.utils.PinotDataType$6.convert(PinotDataType.java:333) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at org.apache.pinot.common.utils.PinotDataType$6.convert(PinotDataType.java:290) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at org.apache.pinot.segment.local.recordtransformer.DataTypeTransformer.transform(DataTypeTransformer.java:90) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] ... 5 more 2022/03/11 054500.989 ERROR [LLRealtimeSegmentDataManager_uapinew__26__0__20220311T0541Z] [uapinew__26__0__20220311T0541Z] Caught exception while transforming the record: { "fieldToValueMap" : {P_RT_MILLISEC_QTY" : "0", "RSP_BASE_RATE" : "", "CAR_SEG_BOOKED" : "0", "RESPONSE_BASE_PRICE" : "", "BOOKED_SEG_QTY" : "0", "REQUESTED_VENDOR_LOCATION_COUNT" : "", "CLASS_OF_SERVICE" : "", "NUM_ADULTS" : "", "SESSION_KEY" : "", "SUPPLIER_LOCATOR_CD" : "", "OTHER_GUARANTEE_INFO" : "", "SSL_TERM_REGION" : "\"216.113.156.89:443|/TF-COM_API/EMEA.universal-api.travelport.com_443\"", "REQUEST_BASE_PRICE" : "", "SOAP_FAULT_TEXT" : "", "SURCHARGE" : "" }, "nullValueFields" : [ ] } java.lang.RuntimeException: Caught exception while transforming data type for column: LOG_TS at org.apache.pinot.segment.local.recordtransformer.DataTypeTransformer.transform(DataTypeTransformer.java:95) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at org.apache.pinot.segment.local.recordtransformer.CompositeTransformer.transform(CompositeTransformer.java:83) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at org.apache.pinot.core.data.manager.realtime.LLRealtimeSegmentDataManager.processStreamEvents(LLRealtimeSegmentDataManager.java:518) [pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at org.apache.pinot.core.data.manager.realtime.LLRealtimeSegmentDataManager.consumeLoop(LLRealtimeSegmentDataManager.java:420) [pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at org.apache.pinot.core.data.manager.realtime.LLRealtimeSegmentDataManager$PartitionConsumer.run(LLRealtimeSegmentDataManager.java:568) [pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at java.lang.Thread.run(Thread.java:834) [?:?] Caused by: java.lang.NumberFormatException: For input string: "2022-03-10T053008.627+00:00" at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) ~[?:?] at java.lang.Long.parseLong(Long.java:692) ~[?:?] at java.lang.Long.parseLong(Long.java:817) ~[?:?] at org.apache.pinot.common.utils.PinotDataType$10.toLong(PinotDataType.java:502) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at org.apache.pinot.common.utils.PinotDataType$6.convert(PinotDataType.java:333) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at org.apache.pinot.common.utils.PinotDataType$6.convert(PinotDataType.java:290) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at org.apache.pinot.segment.local.recordtransformer.DataTypeTransformer.transform(DataTypeTransformer.java:90) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] ... 5 more 2022/03/11 054203.583 ERROR [LLRealtimeSegmentDataManager_uapinew__24__0__20220311T0541Z] [uapinew__24__0__20220311T0541Z] Caught exception while transforming the record: { "fieldToValueMap" : { "TRAVEL_DEST_CD" : "", "PCC_CD" : "3S0E", "SUPPLIER_CD_LIST" : "QR|SU|OS|EK|MU|OZ|LX", "RESPONSE_PROPERTIES_COUNT" : "", "ACH_TRANSACTION_ID" : "", "PROVIDER_CD" : "SVCS", "RESPONSE_BYTES_QTY" : "971876", "VEH_RATE_PERIOD" : "", "JAL_RT_MILLISEC_QTY" : "0", "AUX2" : "", "AUX3" : "", "META_SEARCH" : "", "SVCS_RT_MILLISEC_QTY" : "2797", "RSP_RATE_PLAN" : "", "AUX1" : "", "GALILEO_GUARANTEE" : "", "SCHEMA_VERSION_NM" : "SessionContext_v1|air_v45_0|rail_v45_0", "VEH_RATE_FOR_PERIOD" : "",^Z [4]+ Stopped tail -f pinot-all.log [root@vhldvatlb211 logs]# vi pinot-all.log [5]+ Stopped vi pinot-all.log [root@vhldvatlb211 logs]# vi pinot-all.log [6]+ Stopped vi pinot-all.log [root@vhldvatlb211 logs]# vi pinot-all.log at org.apache.pinot.core.data.manager.realtime.LLRealtimeSegmentDataManager.consumeLoop(LLRealtimeSegmentDataManager.java:420) [pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at org.apache.pinot.core.data.manager.realtime.LLRealtimeSegmentDataManager$PartitionConsumer.run(LLRealtimeSegmentDataManager.java:568) [pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at java.lang.Thread.run(Thread.java:834) [?:?] Caused by: java.lang.NumberFormatException: For input string: "2022-03-10T054328.983+00:00" at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) ~[?:?] at java.lang.Long.parseLong(Long.java:692) ~[?:?] at java.lang.Long.parseLong(Long.java:817) ~[?:?] at org.apache.pinot.common.utils.PinotDataType$10.toLong(PinotDataType.java:502) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at org.apache.pinot.common.utils.PinotDataType$6.convert(PinotDataType.java:333) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at org.apache.pinot.common.utils.PinotDataType$6.convert(PinotDataType.java:290) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] at org.apache.pinot.segment.local.recordtransformer.DataTypeTransformer.transform(DataTypeTransformer.java:90) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb] ... 5 more 2022/03/11 054500.989 ERROR [LLRealtimeSegmentDataManager_uapinew__26__0__20220311T0541Z] [uapinew__26__0__20220311T0541Z] Caught exception while transforming the record: { "fieldToValueMap" : {

Sandeep R

03/18/2022, 1:21 AM

This is happening in 0.9.3, but the same table and schema is working on 0.8

Sandeep R

03/18/2022, 1:21 AM

@User ^^

Sandeep R

03/18/2022, 1:24 AM

attached log file

pinot-all.log

Mayank

03/18/2022, 2:06 AM

Seems like there is a

in there, not sure if that is SDF

Sandeep R

03/18/2022, 2:14 AM

I have only two options here EPOCH OR SDF

Mayank

03/18/2022, 2:16 AM

I think the format has to change as follows

Mayank

03/18/2022, 2:16 AM

yyyy-mm-dd'T'HH:mm:ss.SSSZ

Mayank

03/18/2022, 2:16 AM

This worked

Mayank

03/18/2022, 2:17 AM

so you need to quote ‘T’, change to HH and SSS

👍 1

Mayank

03/18/2022, 2:17 AM

To be SDF compliant. @User

Sandeep R

03/18/2022, 2:33 AM

so you mean this format will not work "LOG_TS": "2022-03-09T164742.995+00:00",?

Sandeep R

03/18/2022, 2:37 AM

I updated the with this, but no luck

Copy code

{
  "schemaName": "uapinewone",
  "dimensionFieldSpecs": [
    {
      "name": "LOG_ID",
      "dataType": "STRING"
    },
    {
      "name": "REQUEST_TYPE_ID",
      "dataType": "STRING"
    },
    {
      "name": "APP_SERV_HOST_NM",
      "dataType": "STRING"
    },
    {
      "name": "SCHEMA_VERSION_NM",
      "dataType": "STRING"
    },
    {
      "name": "AGENCY_ID",
      "dataType": "STRING"
    },
    {
      "name": "BRANCH_CD",
      "dataType": "STRING"
    },
    {
      "name": "TARGET_BRANCH_CD",
      "dataType": "STRING"
    },
    {
      "name": "PCC_CD",
      "dataType": "STRING"
    },
    {
      "name": "AGENT_CD",
      "dataType": "STRING"
    },
    {
      "name": "ORIGIN_APP_CD",
      "dataType": "STRING"
    },
    {
      "name": "SUCCESS_IND",
      "dataType": "STRING"
    },
    {
      "name": "BOOKED_SEG_QTY",
      "dataType": "INT"
    },
    {
      "name": "AIR_SEG_BOOKED",
      "dataType": "STRING"
    },
    {
      "name": "AIR_SEG_CANCELLED",
      "dataType": "STRING"
    },
    {
      "name": "CAR_SEG_BOOKED",
      "dataType": "STRING"
    },
    {
      "name": "CAR_SEG_CANCELLED",
      "dataType": "STRING"
    },
    {
      "name": "HOTEL_SEG_BOOKED",
      "dataType": "STRING"
    },
    {
      "name": "HOTEL_SEG_CANCELLED",
      "dataType": "STRING"
    },
    {
      "name": "PROVIDER_CD",
      "dataType": "STRING"
    },
    {
      "name": "SUPPLIER_CD",
      "dataType": "STRING"
    },
    {
      "name": "PROVIDER_CD_LIST",
      "dataType": "STRING"
    },
    {
      "name": "SUPPLIER_CD_LIST",
      "dataType": "STRING"
    },
    {
      "name": "TRAVEL_ORIGIN_CD",
      "dataType": "STRING"
    },
    {
      "name": "TRAVEL_DEST_CD",
      "dataType": "STRING"
    },
    {
      "name": "TRAVEL_START_DT",
      "dataType": "STRING"
    },
    {
      "name": "TRAVEL_END_DT",
      "dataType": "STRING"
    },
    {
      "name": "NUM_TRAVELERS_QTY",
      "dataType": "STRING"
    },
    {
      "name": "UR_LOCATOR_CD",
      "dataType": "STRING"
    },
    {
      "name": "ONE_WAY_SHOP_IND",
      "dataType": "STRING"
    },
    {
      "name": "HOST_LOCATOR_CD",
      "dataType": "STRING"
    },
    {
      "name": "SUPPLIER_LOCATOR_CD",
      "dataType": "STRING"
    },
    {
      "name": "OPTIONAL_SVC_QTY",
      "dataType": "STRING"
    },
    {
      "name": "TRAVEL_SEGMENTS",
      "dataType": "STRING"
    },
    {
      "name": "TRAVEL_DATES",
      "dataType": "STRING"
    },
    {
      "name": "FLIGHT_NBR",
      "dataType": "STRING"
    },
    {
      "name": "CLASS_OF_SERVICE",
      "dataType": "STRING"
    },
    {
      "name": "AVAILABILITY_SOURCE",
      "dataType": "STRING"
    },
    {
      "name": "NBR_IN_PARTY",
      "dataType": "STRING"
    },
    {
      "name": "HOTEL_CODE",
      "dataType": "STRING"
    },
    {
      "name": "HOTEL_SEARCH_BY_ADDR_IND",
      "dataType": "STRING"
    },
    {
      "name": "SSL_TERM_REGION",
      "dataType": "STRING"
    },
    {
      "name": "REQUEST_BASE_PRICE",
      "dataType": "STRING"
    },
    {
      "name": "RESPONSE_BASE_PRICE",
      "dataType": "STRING"
    },
    {
      "name": "BASE_DIFFERENCE_TOTAL",
      "dataType": "STRING"
    },
    {
      "name": "REQUEST_TOTAL_PRICE",
      "dataType": "STRING"
    },
    {
      "name": "RESPONSE_TOTAL_PRICE",
      "dataType": "STRING"
    },
    {
      "name": "TOTAL_DIFFERENCE_PRICE",
      "dataType": "STRING"
    },
    {
      "name": "SPLIT_TICKET_SEARCH",
      "dataType": "STRING"
    },
    {
      "name": "SPLIT_TICKET_VALID",
      "dataType": "STRING"
    },
    {
      "name": "SPLIT_TICKET_PERCENT",
      "dataType": "STRING"
    },
    {
      "name": "GAL_RT_MILLISEC_QTY",
      "dataType": "STRING"
    },
    {
      "name": "APO_RT_MILLISEC_QTY",
      "dataType": "STRING"
    },
    {
      "name": "WSP_RT_MILLISEC_QTY",
      "dataType": "STRING"
    },
    {
      "name": "JAL_RT_MILLISEC_QTY",
      "dataType": "STRING"
    },
    {
      "name": "ACH_RT_MILLISEC_QTY",
      "dataType": "STRING"
    },
    {
      "name": "RCH_RT_MILLISEC_QTY",
      "dataType": "STRING"
    },
    {
      "name": "MCH_RT_MILLISEC_QTY",
      "dataType": "STRING"
    },
    {
      "name": "TRM_RT_MILLISEC_QTY",
      "dataType": "STRING"
    },
    {
      "name": "SVCS_RT_MILLISEC_QTY",
      "dataType": "STRING"
    },
    {
      "name": "REQUEST_BYTES_QTY",
      "dataType": "STRING"
    },
    {
      "name": "RESPONSE_BYTES_QTY",
      "dataType": "STRING"
    },
    {
      "name": "ERROR_LEVEL_CD",
      "dataType": "STRING"
    },
    {
      "name": "ERROR_CD",
      "dataType": "STRING"
    },
    {
      "name": "ERROR_TYPE_CD",
      "dataType": "STRING"
    },
    {
      "name": "ERROR_TEXT",
      "dataType": "STRING"
    },
    {
      "name": "SOAP_FAULT_TEXT",
      "dataType": "STRING"
    },
    {
      "name": "TRACE_ID",
      "dataType": "STRING"
    },
    {
      "name": "FARE_RULES_FILTER_CAT_CODES",
      "dataType": "STRING"
    },
    {
      "name": "REQ_RATE_PLAN",
      "dataType": "STRING"
    },
    {
      "name": "NUM_ADULTS",
      "dataType": "STRING"
    },
    {
      "name": "NUM_ROOMS",
      "dataType": "STRING"
    },
    {
      "name": "GUARANTEE_TYPE",
      "dataType": "STRING"
    },
    {
      "name": "OTHER_GUARANTEE_INFO",
      "dataType": "STRING"
    },
    {
      "name": "RSP_RATE_PLAN",
      "dataType": "STRING"
    },
    {
      "name": "RESPONSE_MSG_CODES",
      "dataType": "STRING"
    },
    {
      "name": "HOTEL_CONF_NUM",
      "dataType": "STRING"
    },
    {
      "name": "HOTEL_STATUS",
      "dataType": "STRING"
    },
    {
      "name": "LANGUAGE_CODE",
      "dataType": "STRING"
    },
    {
      "name": "PICKUP_LOC_TYPE",
      "dataType": "STRING"
    },
    {
      "name": "RETURN_LOC_TYPE",
      "dataType": "STRING"
    },
    {
      "name": "PICKUP_LOC_NUM",
      "dataType": "STRING"
    },
    {
      "name": "RETURN_LOC_NUM",
      "dataType": "STRING"
    },
    {
      "name": "VEH_RATE_CODE",
      "dataType": "STRING"
    },
    {
      "name": "VEH_RATE_CAT",
      "dataType": "STRING"
    },
    {
      "name": "VEH_RATE_PERIOD",
      "dataType": "STRING"
    },
    {
      "name": "VEH_DISCOUNT_NUM",
      "dataType": "STRING"
    },
    {
      "name": "ACRISS_VEH_CODE",
      "dataType": "STRING"
    },
    {
      "name": "VEH_RATE_FOR_PERIOD",
      "dataType": "STRING"
    },
    {
      "name": "RSP_BASE_RATE",
      "dataType": "STRING"
    },
    {
      "name": "CDS_TXID",
      "dataType": "STRING"
    },
    {
      "name": "CDS_TIME",
      "dataType": "STRING"
    },
    {
      "name": "CDS_STATUS",
      "dataType": "STRING"
    },
    {
      "name": "CDS_ERROR_MSG",
      "dataType": "STRING"
    },
    {
      "name": "ESTIMATED_TOTAL_AMOUNT",
      "dataType": "STRING"
    },
    {
      "name": "MANDATORY_CHARGE_TOTAL",
      "dataType": "STRING"
    },
    {
      "name": "AIR_CONDITIONING",
      "dataType": "STRING"
    },
    {
      "name": "TRANSMISSION_TYPE",
      "dataType": "STRING"
    },
    {
      "name": "VEHICLE_CLASS",
      "dataType": "STRING"
    },
    {
      "name": "CATEGORY",
      "dataType": "STRING"
    },
    {
      "name": "VEHICLE_TYPE",
      "dataType": "STRING"
    },
    {
      "name": "DOOR_COUNT",
      "dataType": "STRING"
    },
    {
      "name": "AUX1",
      "dataType": "STRING"
    },
    {
      "name": "AUX2",
      "dataType": "STRING"
    },
    {
      "name": "AUX3",
      "dataType": "STRING"
    },
    {
      "name": "CANCELLATION_TEXT",
      "dataType": "STRING"
    },
    {
      "name": "REQUESTED_NEGOTIATED_RATE_CODES_COUNT",
      "dataType": "STRING"
    },
    {
      "name": "RATE_CATEGORY_COUNT",
      "dataType": "STRING"
    },
    {
      "name": "NUMBER_OF_RATE_PLAN_TYPES",
      "dataType": "STRING"
    },
    {
      "name": "NEGOTIATED_RATE_CODES_IN_RESPONSE",
      "dataType": "STRING"
    },
    {
      "name": "RESPONSE_NEGOTIATED_RATE_CODE_COUNT",
      "dataType": "STRING"
    },
    {
      "name": "REQUESTED_VENDOR_LOCATION_COUNT",
      "dataType": "STRING"
    },
    {
      "name": "MAX_WAIT_TIME",
      "dataType": "STRING"
    },
    {
      "name": "RESPONSE_PROPERTIES_COUNT",
      "dataType": "STRING"
    },
    {
      "name": "TAX",
      "dataType": "STRING"
    },
    {
      "name": "SURCHARGE",
      "dataType": "STRING"
    },
    {
      "name": "COMMISION_INDICATOR",
      "dataType": "STRING"
    },
    {
      "name": "PERCENTAGE",
      "dataType": "STRING"
    },
    {
      "name": "GALILEO_GUARANTEE",
      "dataType": "STRING"
    },
    {
      "name": "GALILEO_DEPOSIT",
      "dataType": "STRING"
    },
    {
      "name": "WORLDSPAN_DEPOSIT_GUARANTEE_PREPAYMENT",
      "dataType": "STRING"
    },
    {
      "name": "UAPI_RESPONSE_TIME_MS",
      "dataType": "STRING"
    },
    {
      "name": "SESSION_KEY",
      "dataType": "STRING"
    },
    {
      "name": "AGENCY_COUNTRY",
      "dataType": "STRING"
    },
    {
      "name": "SOURCE_IP_ADDRESS",
      "dataType": "STRING"
    },
    {
      "name": "ACH_TRANSACTION_ID",
      "dataType": "STRING"
    },
    {
      "name": "DB2_LOCK_TIME",
      "dataType": "STRING"
    },
    {
      "name": "DB2_NUMBER_OF_LOCKS",
      "dataType": "STRING"
    },
    {
      "name": "OVERRIDE_PCC",
      "dataType": "STRING"
    },
    {
      "name": "META_SEARCH",
      "dataType": "STRING"
    },
    {
      "name": "SOLUTION_RESULT",
      "dataType": "STRING"
    },
    {
      "name": "PTC_LIST",
      "dataType": "STRING"
    }
  ],
  "metricFieldSpecs": [
    {
      "name": "TOTAL_MILLISECOND_QTY",
      "dataType": "INT"
    }
  ],
  "dateTimeFieldSpecs": [
    {
      "name": "LOG_TS",
      "dataType": "LONG",
      "format": "1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-mm-dd'T'HH:mm:ss.SSSZ",
      "granularity": "1:MILLISECONDS"
    }
  ]
}

Mayank

03/18/2022, 2:41 AM

Can you check if table config actually updated? If not, delete and recreate. And if still not, then paste the error from server

Sandeep R

03/18/2022, 3:44 AM

I re-created table

Copy code

}
java.lang.RuntimeException: Caught exception while transforming data type for column: LOG_TS
        at org.apache.pinot.segment.local.recordtransformer.DataTypeTransformer.transform(DataTypeTransformer.java:95) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb]
        at org.apache.pinot.segment.local.recordtransformer.CompositeTransformer.transform(CompositeTransformer.java:83) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb]
        at org.apache.pinot.core.data.manager.realtime.LLRealtimeSegmentDataManager.processStreamEvents(LLRealtimeSegmentDataManager.java:518) [pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb]
        at org.apache.pinot.core.data.manager.realtime.LLRealtimeSegmentDataManager.consumeLoop(LLRealtimeSegmentDataManager.java:420) [pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb]
        at org.apache.pinot.core.data.manager.realtime.LLRealtimeSegmentDataManager$PartitionConsumer.run(LLRealtimeSegmentDataManager.java:568) [pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb]
        at java.lang.Thread.run(Thread.java:834) [?:?]
Caused by: java.lang.NumberFormatException: For input string: "2022-03-17T03:09:14.211+00:00"
        at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) ~[?:?]
        at java.lang.Long.parseLong(Long.java:692) ~[?:?]
        at java.lang.Long.parseLong(Long.java:817) ~[?:?]
        at org.apache.pinot.common.utils.PinotDataType$10.toLong(PinotDataType.java:502) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb]
        at org.apache.pinot.common.utils.PinotDataType$6.convert(PinotDataType.java:333) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb]
        at org.apache.pinot.common.utils.PinotDataType$6.convert(PinotDataType.java:290) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb]
        at org.apache.pinot.segment.local.recordtransformer.DataTypeTransformer.transform(DataTypeTransformer.java:90) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb]
        ... 5 more
2022/03/18 03:44:14.037 ERROR [LLRealtimeSegmentDataManager_uapi__31__0__20220318T0343Z] [uapi__31__0__20220318T0343Z] Caught exception while transforming the record: {
  "nullValueFields" : [ ],
  "fieldToValueMap" : {
    "TRAVEL_DEST_CD" : "",
    "PCC_CD" : "

Sandeep R

03/18/2022, 4:09 AM

Copy code

java.lang.RuntimeException: Caught exception while transforming data type for column: LOG_TS
        at org.apache.pinot.segment.local.recordtransformer.DataTypeTransformer.transform(DataTypeTransformer.java:95) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb]
        at org.apache.pinot.segment.local.recordtransformer.CompositeTransformer.transform(CompositeTransformer.java:83) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb]
        at org.apache.pinot.core.data.manager.realtime.LLRealtimeSegmentDataManager.processStreamEvents(LLRealtimeSegmentDataManager.java:518) [pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb]
        at org.apache.pinot.core.data.manager.realtime.LLRealtimeSegmentDataManager.consumeLoop(LLRealtimeSegmentDataManager.java:420) [pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb]
        at org.apache.pinot.core.data.manager.realtime.LLRealtimeSegmentDataManager$PartitionConsumer.run(LLRealtimeSegmentDataManager.java:568) [pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb]
        at java.lang.Thread.run(Thread.java:834) [?:?]
Caused by: java.lang.NumberFormatException: For input string: "2022-03-17T03:19:57.370+00:00"
        at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) ~[?:?]
        at java.lang.Long.parseLong(Long.java:692) ~[?:?]
        at java.lang.Long.parseLong(Long.java:817) ~[?:?]
        at org.apache.pinot.common.utils.PinotDataType$10.toLong(PinotDataType.java:502) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb]
        at org.apache.pinot.common.utils.PinotDataType$6.convert(PinotDataType.java:333) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb]
        at org.apache.pinot.common.utils.PinotDataType$6.convert(PinotDataType.java:290) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb]
        at org.apache.pinot.segment.local.recordtransformer.DataTypeTransformer.transform(DataTypeTransformer.java:90) ~[pinot-all-0.9.3-jar-with-dependencies.jar:0.9.3-e23f213cf0d16b1e9e086174d734a4db868542cb]
        ... 5 more
2022/03/18 03:44:14.033 ERROR [LLRealtimeSegmentDataManager_uapi__5__0__20220318T0343Z] [uapi__5__0__20220318T0343Z] Caught exception while transforming the record: {

Mayank

03/18/2022, 4:17 AM

Hmm I can parse the time in the log above using this:

Copy code

DateTimeFunctions.fromDateTime("2022-03-17T03:19:57.370+00:00", "yyyy-mm-dd'T'HH:mm:ss.SSSZ")

Mayank

03/18/2022, 4:19 AM

Can you try changing

"dataType": "LONG",

to STRING, just for testing?

Mayank

03/18/2022, 4:30 AM

If that works, then we can add a transform to convert the string to LONG when storing. @User

Sandeep R

03/18/2022, 5:05 AM

I tried converted from LONG to STRING, but table creation is falling to create.

Mayank

03/18/2022, 5:23 AM

what’s the error?

Rong R

03/18/2022, 3:02 PM

the escape

'T'

simply tells the SDF parser this is not a field but a literal in your format. e.g.

yyyy-mm-dd

means to fill in year/month/date in 4,2,2, digits. if you want to use for example a literal Y in your format

2022-03-17*Y*03:19:57.370+00:00

you can't do it because 'Y' reserves to be placeholder for year values.

Rong R

03/18/2022, 3:03 PM

and

is not a valid SDF format placeholder. so you will get a parsing exception. quoting it with single quote

'T'

means you don't want it to be treated as placeholder but actual literals.

Rong R

03/18/2022, 3:03 PM

see https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html for more info

francoisa

03/25/2022, 9:41 AM

Same issue here with the same format. I’ve managed to get a proper timestamp with : Transform config :

Copy code

{
          "columnName": "creationDate",
          "transformFunction": "FromDateTime(JSONPATHSTRING(data,'$.attributes.creationDate'),'yyyy-MM-dd''T''HH:mm:ssZ')"
        },

Schema :

Copy code

{
      "name": "creationDate",
      "dataType": "TIMESTAMP",
      "format": "1:MILLISECONDS:EPOCH",
      "granularity": "1:MILLISECONDS"
    },

Output on join file 😉

Rong R

03/25/2022, 2:11 PM

create. this will work if your ingestion config transforms it into a JDBC standard string format. however, you cannot simply copy the literal

'yyyy-MM-dd''T''HH:mm:ssZ'

into the schema config:

Copy code

{
  "name": "creationDate",
  "dataType": "TIMESTAMP",
  "format": "1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd''T''HH:mm:ssZ",
  "granularity": "1:MILLISECONDS"
}

this won't work

francoisa

03/25/2022, 2:14 PM

Yes i’ve discovered it the hard way this should be documented as a lot of people will ask 😉

Rong R

03/25/2022, 2:19 PM

yes i agree but I couldn't find a good way to document it. except what I think should happen is: when

dataType = TIMESTAMP

is used,

format

cannot be configured --> because jdbc TIMESTAMP format is fixed. it makes more sense to throw a meaningful exception when one tries to set the

format

field and

TIMESTAMP

type together, and suggest they should go to the ingestionConfig to transform it first

Rong R

03/25/2022, 2:21 PM

I think @User ^ is looking into creating a better datetime documentation as a whole. maybe this would serve as a good example

Mark Needham

03/25/2022, 2:23 PM

we had this example - https://dev.startree.ai/docs/pinot/recipes/datetime-string-to-timestamp

👍 1

Mark Needham

03/25/2022, 2:23 PM

should we add one that uses the 'T' literal?

francoisa

03/25/2022, 2:34 PM

Nope sounds good 😉. This example was the one I’ve used to figured out my issue

Rong R

03/25/2022, 2:39 PM

"T" literal is not standard JDBC standard string so no we cant add that.

Open in Slack

Previous Next