https://pinot.apache.org/ logo
Join Slack
Powered by
I am facing a issue with a table. I observe that w...
# troubleshooting
h
I am facing a issue with a table. I observe that when I create a table, the data is injected but after 1-2 days the new data is not visible. I don’t see the latest data. If i delete and re-create the table it will show the new data . This table is a upsert table.
Copy code
"routing": {
        "instanceSelectorType": "strictReplicaGroup"
    },
"query": {},
"upsertConfig": {
    "mode": "PARTIAL",
    "partialUpsertStrategies": {
        "status": "OVERWRITE",
        "tenant_name": "OVERWRITE",
        "sub_tenant_name": "OVERWRITE"
    },
    "defaultPartialUpsertStrategy": "OVERWRITE",
    "hashFunction": "NONE"
},
🟢 1
k
Hi Can you add the complete table config and schema here also, can you try querying with 
option(skipUpsert=true)
h
Ok will share the schema.. and table def..
I checked with option(skipUpsert=true), still I dont see data
One more thing - I created a new table with same settings. I am able to see the data in the new table (table_v2) but not in old table (table_v1)
Here is the def for table
Copy code
{
  "schemaName": "schema_v1",
  "dimensionFieldSpecs": [   
    {
      "name": "channel",
      "dataType": "STRING"
    },
    {
      "name": "pipeline",
      "dataType": "STRING"
    },    
    {
      "name": "id",
      "dataType": "STRING"
    },
    {
      "name": "id_type",
      "dataType": "STRING"
    },    
    {
      "name": "status",
      "dataType": "STRING"
    },    
    {
      "name": "lob_name",
      "dataType": "STRING"
    }
  ],
  "dateTimeFieldSpecs": [
    {
      "name": "timestamp",
      "dataType": "STRING",
      "format": "1:HOURS:SIMPLE_DATE_FORMAT:yyyy-MM-dd'T'HH:mm:ss.SSS",
      "granularity": "1:MINUTES"
    }
  ],
  "primaryKeyColumns": [
    "id",
    "id_type"
  ]
}



{
    "tableName": "table_v1",
    "tableType": "REALTIME",
    "segmentsConfig": {
        "schemaName": "schema_v1",
        "retentionTimeUnit": "DAYS",
        "retentionTimeValue": "2",
        "replication": "2",
        "timeColumnName": "timestamp",
        "allowNullTimeValue": true,
        "replicasPerPartition": "2"
    },
    "tenants": {
        "broker": "DefaultTenant",
        "server": "DefaultTenant",
        "tagOverrideConfig": {}
    },
    "tableIndexConfig": {
        "invertedIndexColumns": [
            "pipeline",
            "channel"           
        ],
        "noDictionaryColumns": [],
        "streamConfigs": {
            "streamType": "kafka",
            "stream.kafka.topic.name": "----MY TOPOOC-----",
            "stream.kafka.broker.list": "{{kafka}}",
            "stream.kafka.consumer.type": "lowlevel",
            "stream.kafka.consumer.prop.auto.offset.reset": "largest",
            "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": "1h",
            "realtime.segment.flush.desired.size": "100M",
            "realtime.segment.flush.autotune.initialRows": "10000"
        },
        "sortedColumn": [],
        "bloomFilterColumns": [
            "channel"
        ],
        "loadMode": "MMAP",
        "onHeapDictionaryColumns": [],
        "varLengthDictionaryColumns": [],
        "enableDefaultStarTree": false,
        "enableDynamicStarTreeCreation": false,
        "aggregateMetrics": false,
        "nullHandlingEnabled": true,
        "rangeIndexColumns": [],
        "rangeIndexVersion": 1,
        "autoGeneratedInvertedIndex": false,
        "createInvertedIndexDuringSegmentGeneration": false
    },
    "metadata": {},
    "quota": {},
    "routing": {
        "instanceSelectorType": "strictReplicaGroup"
    },
    "query": {},
    "upsertConfig": {
        "mode": "PARTIAL",
        "partialUpsertStrategies": {
            "status": "OVERWRITE",
            "lob_name": "OVERWRITE"          
        },
        "defaultPartialUpsertStrategy": "OVERWRITE",
        "hashFunction": "NONE"
    },
    "ingestionConfig": {},
    "isDimTable": false
}
k
Hi, is there a need for partial upsert in this case. With the current config, what you are doing is only full upsert since every column is getting OVERWRITTEN.
h
the status and lob_name update can happen in diff events… What i need: 1. I have a row e.g. id=10, lob_name=abcd, status=init, pipeline=p1, channel=c1, udf_1=0 2. Event 1 -> update status=processing, udf_1=10 3. Event 2 -> update lob_name=client_1 4. Event 2 -> update status=done Result expected: id=10, lob_name=client_1, status=done, pipeline=p1, channel=c1, udf_1=10
What settings you suggest? The major problem I see is data not coming to table. When this happened I checked the logs of servers, I did not see the kafka consuming logs (normally I see topic name and offset in server logs).. As i also mentioned above, I created a v2 table. Now v1 table had data only till 28th and v2 has data for 29. So the data is there in the kafka. Not sure why v1 table is not showing that data
k
ok. then, what you need to do is set 
"defaultPartialUpsertStrategy": "IGNORE"
and mention only the columns that need to be updated in 
partialUpsertStrategies
such as status and lob_name with mode 
OVERWRITE
The data not getting consumed is strange though. Are there any segments created for 29th in v1 table? If not, can you once check the status of the latest segment
h
Done.. Created a new table v3 with what you suggested.
No in v1 table i have only 10 segments (hist_v1__0__0__20220628T0808Z, hist_v1__1__0__20220628T0808Z, …) Note these tables do not have lot of data e.g. < 100K-500K records per day for now. It will go to 1-5M per day once I have all traffic in
Something is wrong - none of the tables are ingesting new data.. I checked he logs and I dont see consumeing logs kubectl -n pinot logs --follow pod/pinot-server-0 | grep my_topic -> no output
k
what does it show in Zookeeper Browser -> IDEAL STATES -> table name
h
Copy code
hist_v3__0__0__20220629T0659Z": {
      "Server_pinot-server-0.pinot-server-headless.pinot.svc.cluster.local_8098": "CONSUMING",
      "Server_pinot-server-3.pinot-server-headless.pinot.svc.cluster.local_8098": "CONSUMING"
    },
k
and in EXTERNAL VIEW?
h
same thing
Also I am sure that the kafka has data - i can see it coming in “kafka-console-consumer”
k
@saurabh dubey can you help here
s
Could you check PROPERTYSTORE in ZK to confirm there are segments in "IN_PROGRESS" state?
h
“segment.realtime.status”: “IN_PROGRESS”
Just to give more info: Given below is the info about one of the partition from kafka topic (10th part) - this is from PROPERTYSTORE.
Copy code
Same table:

Table v1: crated on 28:

hist_v1__9__0__20220628T0808Z
29330927


Table v1: crated on 28 (11:30 PM) - because V1 was stuck
hist_v2__9__0__20220628T1927Z
29596616


Table v1: crated on 29 ( - because V2 was stuck
hist_v3__9__0__20220629T0659Z
29840247

All of them has:
"segment.realtime.status": "IN_PROGRESS"
@saurabh dubey anything on this? FYI - I also created same table in a different Pinot cluster. Same issue now. It stopped ingesting data..
k
Hi What is offset retention period set in Kafka topic?
h
2 days : PartitionCount: 10 ReplicationFactor: 2 Configs: min.insync.replicas=2,retention.ms=172800000,message.format.version=2.3-IV1,unclean.leader.election.enable=false
If you are looking for setting of “__consumer_offsets” -> it is 2 days.
k
got it. And the consumption stops after 24 hours or does it happen before that as well. can be verified by the timestamp difference between first and last row
h
On 29th it stopped 2 times in a day. Both v2 and v3 table stopped in a single day
k
What I wanted to know was approximate duration in minutes. That might give us some hints as to if it is a period job causing this issue inside pinot or a random event
h
Ok will share the data..
FYI I also ran the exact same table in new pinot cluster - same issue .. it is stuck there as well..
Copy code
Table V2 
1656462921
GMT: Wednesday, June 29, 2022 0:35:21
Your time zone: Wednesday, June 29, 2022 6:05:21 GMT+05:30
Relative: 2 days ago


1656470101579
GMT: Wednesday, June 29, 2022 2:35:01.579
Your time zone: Wednesday, June 29, 2022 8:05:01.579 GMT+05:30

--------------------------------------------------------------------


Table V3 (which i created once V2 table stopped):
1656486065
GMT: Wednesday, June 29, 2022 7:01:05
Your time zone: Wednesday, June 29, 2022 12:31:05 GMT+05:30


1656486423000
GMT: Wednesday, June 29, 2022 7:07:03
Your time zone: Wednesday, June 29, 2022 12:37:03 GMT+05:30
Copy code
I do see a issue in my timestamp - some timestamps are "1656462921" and others are "1656470101579" (extra 3 digit)
Not sure if this is the issue - anyways I have fixed the issues in my job now all timestamps are millseconds
Open in Slack
PreviousNext
Powered by