#general
@humengyuk18: Can I use an aggregation result as argument to a UDF during query?
@fx19880617: I think only having clause is supported, udf on aggregation results is not supported afaik
@humengyuk18: The problem is we have sum(a)/sum(b) doing some rate calculation, but sum(b) can be 0, then the result will be NaN, when doing an order by, NaN will be ranked first, I want to fill NaN to 0 in this case.
@jackie.jxt: Can you try adding `having sum(b) != 0` and see if it solves the problem?
@humengyuk18: adding having will filter the result, I actually want to fill NaN with 0.
@jackie.jxt: You can also plug in your own UDF to replace `NaN` with `0`. Udf on aggregation results is supported (post-aggregation), e.g. `sum(a)/sum(b)`
@jackie.jxt: Basically you need `replaceNaN(sum(a)/sum(b))` and plug in `replaceNaN(double value)`
@humengyuk18: So a scalar UDF will work on post-aggregation, but groovy UDF will not work on post-aggregation?
@humengyuk18: I see, I will try implement a scalar UDF.
@humengyuk18: Thanks
@jackie.jxt: Yes, groovy can only be used for record transform, but not post-aggregation as of now
@jackie.jxt: Can you please file an issue about this? It should not be too hard to add a scalar function for groovy. Contributions are very welcome
@humengyuk18: OK, I will file an issue for this.
@patidar.rahul8392: Someone kindly guide where can I find these 2 details.I am trying to use HDFS as deepatorage and need to set these 2 properties as per the pinot docs, but unable to find which path I should use. I am loading streaming data from Kafka topic and pinot is running in cluster-mode. Controller , server and brokers are running on diff diff nodes.
@fx19880617: this is still local disk on the server, which will host segment for serving queries
@patidar.rahul8392: I am able to see these directory on server @fx19880617 kindly suggest if I need to use the same.
@fx19880617: those are local directory, you need to have those directory for pinot-servers to run
@patidar.rahul8392: How should I do that, to set that for servers.?
@fx19880617: you just set a local directory
@fx19880617: make sure you have enough disk space and preferably SSD
@patidar.rahul8392: Oh okay.. Thanks alot @fx19880617
@patidar.rahul8392: Is there any limit in Pinot to execute queries simentenoulsly. I.e. I have created a pipeline through pinot where we are ingesting data from Kafka topic and creating Pinot table.After that connecting from prestro using pinot-connector and in the last step connecting from superset and from there querying pinot table. Here I have generated 4 graphs using diff SQL queries and added all 4 in one dashboard.After sometime when I am trying refresh the dashboard page so it's firing 4 queries to Pinot and all are getting failed it's showing internal error couldn't reach host, and if I am refreshing one slice from the dashboard then it's working fine.i.e. when firing one query at a time. P.S. I am facing this issue when I have started pinot in cluster mode with 3 servers 2 controller and 2 broker. When I am doing the same using quick-start it's working fine and entire dashboard is loading/refreshing at same time.
@patidar.rahul8392: This is msg it's showing if I am trying to refresh dashboard some slice of dashboard is working and some are failing then again I need to refresh it one by one then it's loading on dashboard.
@patidar.rahul8392: @mayanks
@mayanks: Does querying Pinot directly work when you get this error via Presto?
@mayanks: Let's first understand if the issue is on Pinot side or Presto side
@mayanks: Also, let's move to troubleshooting
@patidar.rahul8392: Ok @mayanks let me move question to troubleshooting .
@kennybastani: @kennybastani set the channel topic: Welcome to the community! Please show your support by starring our project repository on GitHub
@joshhighley: are there any systemd scripts for starting / stopping the pinot components?
@mayanks: Not specifically for systemd, but I think you are already aware of Pinot-admin.sh?
@joshhighley: I am, but it doesn't configure logging correctly so I have my own manual scripts. I need pinot to start automatically if the server restarts
@mayanks: What’s the issue with logging? Perhaps we need to fix that?
@joshhighley: I can't remember specifically. there were issues with the log4j config I think
@mayanks: Will fixing logging issue solve your problem ? If so I’d say let’s file an issue and we’ll fix that.
@dlavoie: It would be fairly easy to create a systemd wrapper around `pinot-admin.sh` isn’t it? Logging issues should be treated independently
@mayanks: Yes, agree. I was just trying to ensure that we fix the logging issue as well.
@dlavoie: From my personal experience with the docker image, the flush seems like a problem, last line of logging is always incomplete in `pinot<component>.log`.
@dlavoie: stdout is fine though
@mayanks: I recently fixed a bug in quick start logging, where certain log messages just went missing. I think there might be similar issue in pinot-admin. I’ll also try to check that on my own
@chethanu.tech: Wow, great content. Excellent half hour spent. Great job @xd @joey
@kelvin: Hi! What is the reason for having Pinot queries implicitly default to `limit 10` ? Is there a way to remove the limit in the query without specifying an arbitrary limit value?
@mayanks: Without a default, a simple looking query (to some) like `select *` could end up fetching all of Pinot's data?
@kelvin: Ok, so it's purely for safety. Is there a way to remove the limit without specifying an arbitrary limit value?
@mayanks: No. What's your use case? Is it purely for convenience, or your application does indeed want to pull out all data from Pinot?
@kelvin: Data is streamed into Pinot and over the course of the day, application instances want to get all the messages of interest starting from 0000 hours. Number of messages differ according to the query criteria. We can put in a "large enough" limit value, but that is an assumption it will suffice in the future.
@mayanks: There's the syntactic challenge there. If we make default unlimited then we have the problem above. Once we limit the default, then any non-default value has to be specified due to syntax
@kelvin: I think if anyone does it explicitly, they know what they are getting into.
@mayanks: Yes, the worry is about new users who don't know that they may end up fetching entire data from Pinot just by doing `select *`. Since most new folks won't specify `limit`
@mayanks: We are err'ing on the safer side here.
@kelvin: I think it's fine to retain the implicit default limit. Then offer something to the effect of `limit unlimited` for users wanting to query with knives.
@mayanks: I see. What's the SQL way of doing it?
@kelvin: set a session parameter?
@gqian3: Hi, in Pinot docs, “e.g. 365 DAYS in the example means that segments containing data older than 365 days will be deleted periodically.” Does Pinot delete the segment containing ALL data or ANY data older than the retentionperiod?
@fx19880617: Pinot deletes the entire segment once all the docs in this segment are passed the retention period. Meaning you may see data older than your retention period.
@mayanks: Dear Team As the community grows, we are getting lots of good questions in slack channels that can be helpful not just for the members asking them, but across the community. We spent a lot of time improving our docs to answer these, but there's still a long way to go. We would now like our
@karinwolok1: Welcome new Apache Pinot community members! :wave: We're so happy you're here and joining the movement of speeeeeed for user-facing analytics. :wine_glass: Please take a moment, if you haven't already - to introduce yourself and tell us what brought you here! :smiley: @matt @s.aditya.1811 @santosh.reddy @sahoo.skumar @jcole @fitzystrikesagain @karthik.chopperla @mohit.asingh @mapshen @onur.henden @gqian3 @akash143shah @allison @jcwillia @sgarud @vbondugula @jiasheng55 @rraguram
@allison: Thanks Karin! Excited to join the
@fitzystrikesagain: Hello! We just started working scoping Pinot where I work, so I'm playing around with it
#troubleshooting
@machhindra.nale: @npawar I need help in Kafka streaming with avro schema. Here is my avro schema. Main object is *Metric.* It contains one *MetricSource* nested object. I could stream Metric.asvc fields like product/productversion/metricPath. I dont know how to map MetricSource. I would like to map as follows - MetricSource_time, MetricSource_metric, MetricSource_metricValue, MetricSource_category, MetricSource_subCategory, Metric_product, Metric_productVersion, Metric_metricpath ```Metric.avsc =========== { "namespace": "com.blah", "name": "Metric", "type": "record", "fields": [{ "name": "product", "type": ["string", "null"] },{ "name": "productVersion", "type": ["string", "null"] },{ "name": "MetricSource", "type": ["com.blah.MetricSource", "null"] },{ "name": "metricPath", "type":{ "type": "array", "items": ["string", "null"] } }] } MetricSource.avsc =========== { "namespace": "com.blah", "name": "MetricSource", "type": "record", "fields": [{ "name": "metric", "type": ["string", "null"] },{ "name": "metricValue", "type": ["string", "null"] },{ "name": "time", "type": "long" },{ "name": "timeOffset", "type": "double" },{ "name": "category", "order": "ignore", "type": ["null", "string"], "default": null },{ "name": "subCategory", "order": "ignore", "type": ["null", "string"], "default": null } ] }```
@npawar: Use `transformFunction: jsonPathString(MetricSource, '$.time', <default value>)`
@npawar: This is an example to extract "time"
@npawar:
@machhindra.nale: Do I need to extract at query time? I was hoping to map the fields at the ingestion time.
@patidar.rahul8392: @machhindra.nale you need to do this while ingestion in your table-config-file.json.
@patidar.rahul8392: Like this.
@machhindra.nale: @patidar.rahul8392 thanks for suggestion. I added the transformFunction to table *schema* as follows. It worked. ```{ "name": "metric", "dataType": "STRING", "transformFunction": "jsonPathString(MetricSource, '$.metricClass', 'null')" }```
@npawar: Put this in table confg, not schema
@machhindra.nale: okay. It is strange that it worked even I put it in table schema.. I will move to table config.
@npawar: like this
@npawar: yes, it used to be in schema. We have deprecated it. So better to move to table
@machhindra.nale: Ah..
@jmeyer: Hello :slightly_smiling_face: When ingesting Batch data + data partitioning (Parquet) using a key, that key is "missing" from the parquet file parts (makes sense) However, from what I've seen, Pinot cannot find that key then, and fails to generate the segments My current workaround is to duplicate the partition column. Is that a known issue / possible to adjusts settings ?
@fx19880617: do you have stacktrace for the job? The key should be a column in your data even in batch side
@jmeyer: The schema contains several columns including `dateString` which it is partitionned on This creates parquet partitions without this key
@jmeyer: Actually, now that I look at it again, I'm seeing ``` file:/kpi-data/raw/date=2020-11-30/ab331a05255849bf811a173a380aaf1d.parquet``` Not `dateString=XXX` Curious but I'll check that
@fx19880617: oic, cause the default null string caused the parsing failure
@fx19880617: this date has to be one column in your parquet file
@fx19880617: if you generated this parquet from spark, you can add the partitionkey as a column as well
@jmeyer: If the parquet partitioning key was the one expected by Pinot (`dateString`), it would have worked, right ? (Pulling `dateString` values from the file paths)
@fx19880617: yes
@fx19880617: the error says the job tries to generate the partitionkey but got null value
@fx19880617: so it’s failed
@jmeyer: Makes sense, thanks @fx19880617 :)
@jmeyer: On an unrelated note, I've opened an issue on Python pinot-db driver, let me know what you think when you've got the time ;)
@fx19880617: sounds good!
@patidar.rahul8392: Is there any limit in Pinot to execute queries simentenoulsly. I.e. I have created a pipeline through pinot where we are ingesting data from Kafka topic and creating Pinot table.After that connecting from prestro using pinot-connector and in the last step connecting from superset and from there querying pinot table. Here I have generated 4 graphs using diff SQL queries and added all 4 in one dashboard.After sometime when I am trying refresh the dashboard page so it's firing 4 queries to Pinot and all are getting failed it's showing internal error couldn't reach host, and if I am refreshing one slice from the dashboard then it's working fine.i.e. when firing one query at a time. P.S. I am facing this issue when I have started pinot in cluster mode with 3 servers 2 controller and 2 broker. When I am doing the same using quick-start it's working fine and entire dashboard is loading/refreshing at same time.
@patidar.rahul8392:
@mayanks: When the query fails, can you run the same query directly to Pinot? Let's first find out if isue is Pinot side or Presto side.
@mayanks: cc @fx19880617
@patidar.rahul8392: @mayanks how should I submit 4 queries simentenoulsly with Pinot UI.because this issue I am getting when I was trying to run all the queries same time. If I am running queries seperately then it's working fine.
@mayanks: Same queries running separately works fine. or those are different queries?
@patidar.rahul8392: Same queries working fine separately
@mayanks: What's the resources (CPU/MEM/HEAP) on pinot vms?
@patidar.rahul8392: I am only facing the issue when I am refreshing the entire dashboard where I used all these four queries.
@mayanks: One possibility is that the queries are expensive when run in parallel and your Pinot VMs do not have enough resource to answer the query in parallel.
@mayanks: It is hard to tell from the Presto side stack trace you pasted
@patidar.rahul8392: Hmm any way to check resource allocation in pinot. Queries are also not expensive just taking sum and doing group by on one column. In 2nd query taking count on same group by column. And in 3rd query calculating percentage on same group by column. And I have only 1000 records in table.
@mayanks: I am asking about VM size heap size etc. If you are using latest master there's an appConfig endpoint in Swagger.
@patidar.rahul8392: I m on swagger UI where I need to check that
@mayanks: Is this latest master, or 0.7.1?
@mayanks: Master has it, 0.7.1 does not have that code yet
@mayanks: for latest master code, just search for `appConfigs`
@patidar.rahul8392: appConfigs is not there. I have 0.7 1
@mayanks: yeah that code does not have it
@mayanks: you can log on to vm and find the jvm settings and cpu/ram?
@patidar.rahul8392: Ok let me check
@mayanks: And also for the individual successful queries, let's see the numDocs/numEntries etc. Then we will know how much work each query is doing and how much resource is there on VM.
@patidar.rahul8392: Ok @mayanks
@fx19880617: I feel it’s timeout on pinot side?
@fx19880617: can you check the queries generated from the superset?
@mayanks: Yeah, that's what I am suspecting too.
@mayanks: @fx19880617 He ran the 4 queries generated, and was able to run them individually
@fx19880617: you can view it from the right link button when you enter into each chart
@fx19880617: ic
@mayanks: Only when these queries fired in parallel, that's when this happens.
@fx19880617: then maybe try to increase pinot server timeout
@patidar.rahul8392: Pipeline steps. 1. Connection b/w pinot and Kafka. 2. Connection b/w Pinot and Prestro through Pinot-prestro Connector. 3. Connecting Superset with Pinot with the help of Prestro catalog. So here superset and pinot is communicating with the help of prestro.when I am submitting query is connecting to Pinot schema and table with the help of prestro.
@fx19880617: so are those queries directly against prestosql?
@neilteng233: Hi there, anyone know what should be the schema for such time format? 2021-05-19T18:08:23.583Z Had tried but no luck ```{ "name": "date", "dataType": "STRING", "format": "1:SIMPLE_DATE_FORMAT:yyyy-MM-dd'T'HH:mm:ss.SSS'Z'", "granularity": "1:MILLISECONDS" },```
@mayanks: @npawar ^^
@npawar: looks right. I’ve used this in the past successfully ``` "format": "1:HOURS:SIMPLE_DATE_FORMAT:yyyy-MM-dd'T'HH:mm:ss.SSS'Z'",```
@npawar: what fails?
@jackie.jxt: I think @neilteng233 missed the unit part
@jackie.jxt: We should remove the unit part for `SIMPLE_DATE_FORMAT` because it is ignored anyway
@neilteng233: this is the error msg
@neilteng233: "Cannot add invalid schema: promises. Reason: Unit: SIMPLE_DATE_FORMAT must belong to enum TimeUnit or DateTimeTransformUnit" while sending request:
@neilteng233: OK, let me try add the unit part first
@neilteng233: added the unit solved the problem, thanks. it is a bug of my code.
#minion-improvements
@laxman: ```2021/05/26 16:19:09.069 INFO [SegmentMapper] [TaskStateModelFactory-task_thread-3] Initialized mapper with id: rawServiceView__31__123__20210323T2150Z, input segment: /tmp/PinotMinion/data/RealtimeToOfflineSegmentsTask/tmp-79ad3de0-c4d2-4d02-a452-0c6e8abbd2a1/workingDir/input_segments/rawServiceView__31__123__20210323T2150Z, output dir: /tmp/segment_processor_1622045810469/mapper_output, recordTransformer: class org.apache.pinot.core.segment.processing.transformer.NoOpRecordTransformer, recordFilter: class org.apache.pinot.core.segment.processing.filter.FunctionEvaluatorRecordFilter, partitioners: class org.apache.pinot.core.segment.processing.partitioner.NoOpPartitioner 2021/05/26 16:19:09.079 INFO [ImmutableSegmentLoader] [TaskStateModelFactory-task_thread-3] Successfully loaded segment rawServiceView__31__123__20210323T2150Z with readMode: mmap 2021/05/26 16:19:14.313 INFO [SegmentMapper] [TaskStateModelFactory-task_thread-3] Initialized mapper with id: rawServiceView__2__1226__20210323T2158Z, input segment: /tmp/PinotMinion/data/RealtimeToOfflineSegmentsTask/tmp-79ad3de0-c4d2-4d02-a452-0c6e8abbd2a1/workingDir/input_segments/rawServiceView__2__1226__20210323T2158Z, output dir: /tmp/segment_processor_1622045810469/mapper_output, recordTransformer: class org.apache.pinot.core.segment.processing.transformer.NoOpRecordTransformer, recordFilter: class org.apache.pinot.core.segment.processing.filter.FunctionEvaluatorRecordFilter, partitioners: class org.apache.pinot.core.segment.processing.partitioner.NoOpPartitioner 2021/05/26 16:19:14.323 INFO [ImmutableSegmentLoader] [TaskStateModelFactory-task_thread-3] Successfully loaded segment rawServiceView__2__1226__20210323T2158Z with readMode: mmap 2021/05/26 16:19:20.755 ERROR [TaskFactoryRegistry] [TaskStateModelFactory-task_thread-3] Caught exception while executing task: Task_RealtimeToOfflineSegmentsTask_1622045740512_2 java.lang.IllegalStateException: No files found in mapper output directory: /tmp/segment_processor_1622045810469/mapper_output. Exiting SegmentProcessorFramework. at org.apache.pinot.core.segment.processing.framework.SegmentProcessorFramework.processSegments(SegmentProcessorFramework.java:144) ~[pinot-all-hypertrace-0.7.1-5-shaded.jar:0.7.1-24ebc6d4cd25b1b21225f48c4e7438919246ffe3] at org.apache.pinot.minion.executor.RealtimeToOfflineSegmentsTaskExecutor.convert(RealtimeToOfflineSegmentsTaskExecutor.java:205) ~[pinot-all-hypertrace-0.7.1-5-shaded.jar:0.7.1-24ebc6d4cd25b1b21225f48c4e7438919246ffe3] at org.apache.pinot.minion.executor.BaseMultipleSegmentsConversionExecutor.executeTask(BaseMultipleSegmentsConversionExecutor.java:116) ~[pinot-all-hypertrace-0.7.1-5-shaded.jar:0.7.1-24ebc6d4cd25b1b21225f48c4e7438919246ffe3] at org.apache.pinot.minion.executor.BaseMultipleSegmentsConversionExecutor.executeTask(BaseMultipleSegmentsConversionExecutor.java:51) ~[pinot-all-hypertrace-0.7.1-5-shaded.jar:0.7.1-24ebc6d4cd25b1b21225f48c4e7438919246ffe3] at org.apache.pinot.minion.taskfactory.TaskFactoryRegistry$1.run(TaskFactoryRegistry.java:81) [pinot-all-hypertrace-0.7.1-5-shaded.jar:0.7.1-24ebc6d4cd25b1b21225f48c4e7438919246ffe3] at org.apache.helix.task.TaskRunner.run(TaskRunner.java:71) [pinot-all-hypertrace-0.7.1-5-shaded.jar:0.7.1-24ebc6d4cd25b1b21225f48c4e7438919246ffe3] at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) [?:?] at java.util.concurrent.FutureTask.run(Unknown Source) [?:?] at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source) [?:?] at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [?:?] at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [?:?] at java.lang.Thread.run(Unknown Source) [?:?] 2021/05/26 16:19:21.283 INFO [CallbackHandler] [ZkClient-EventThread-20-zookeeper-0.zookeeper-headless.traceable.svc.cluster.local:2181,zookeeper-1.zookeeper-headless.traceable.svc.cluster.local:2181,zookeeper-2.zookeeper-headless.traceable.svc.cluster.local:2181/pinot] Subscribing changes listener to path: /traceable-views/INSTANCES/Minion_10.76.9.64_9514/MESSAGES, type: CALLBACK, listener: org.apache.helix.messaging.handling.HelixTaskExecutor@304d050b 2021/05/26 16:19:21.284 INFO [CallbackHandler] [ZkClient-EventThread-20-zookeeper-0.zookeeper-headless.traceable.svc.cluster.local:2181,zookeeper-1.zookeeper-headless.traceable.svc.cluster.local:2181,zookeeper-2.zookeeper-headless.traceable.svc.cluster.local:2181/pinot] Subscribing child change listener to path:/traceable-views/INSTANCES/Minion_10.76.9.64_9514/MESSAGES 2021/05/26 16:19:21.285 INFO [CallbackHandler] [ZkClient-EventThread-20-zookeeper-0.zookeeper-headless.traceable.svc.cluster.local:2181,zookeeper-1.zookeeper-headless.traceable.svc.cluster.local:2181,zookeeper-2.zookeeper-headless.traceable.svc.cluster.local:2181/pinot] Subscribing to path:/traceable-views/INSTANCES/Minion_10.76.9.64_9514/MESSAGES took:1```
@laxman: Enabled this in production. Converter task is failing for 4 tables with above error in mapper phase. I haven’t seen this earlier in other clusters I have tested so far @jackie.jxt @npawar: any hints when this can happen and how to recover from this?
@npawar: do you have a filter config?
@npawar: can i see the table config?
@laxman: Table config is same/similar for all 8 tables. I can give you
@laxman: We don’t have any filters/transformers/aggregations
@laxman: ```{ "REALTIME": { "tableName": "rawServiceView_REALTIME", "tableType": "REALTIME", "segmentsConfig": { "timeType": "MILLISECONDS", "schemaName": "rawServiceView", "retentionTimeUnit": "DAYS", "retentionTimeValue": "90", "timeColumnName": "start_time_millis", "replication": "2", "segmentPushType": "APPEND", "replicasPerPartition": "2", "segmentAssignmentStrategy": "BalanceNumSegmentAssignmentStrategy" }, "tenants": { "broker": "DefaultTenant", "server": "DefaultTenant" }, "tableIndexConfig": { "invertedIndexColumns": [ "api_is_external", "customer_id", "service_id", "api_id", "host_header" ], "rangeIndexColumns": [ "start_time_millis" ], "autoGeneratedInvertedIndex": false, "createInvertedIndexDuringSegmentGeneration": false, "bloomFilterColumns": [], "loadMode": "MMAP", "streamConfigs": { "realtime.segment.flush.threshold.rows": "0", "stream.kafka.hlc.zk.connect.string": "zookeeper:2181", "stream.kafka.decoder.class.name": "org.apache.pinot.plugin.inputformat.avro.confluent.KafkaConfluentSchemaRegistryAvroMessageDecoder", "streamType": "kafka", "stream.kafka.decoder.prop.schema.registry.rest.url": "
@laxman: This same table configurations is used across all clusters. Working fine in other clusters except prod where it’s erring out with above stacktrace from mapper.
@npawar: is the exact same version of Pinot deployed across s them all?
@laxman: yes.
@laxman: By the way, this is our forked version pinot 0.7.1 + with very few patches which are blocking us
@laxman:
@laxman: These patches are cherry-picked from apache master branch
@npawar: can you see this log line: ```
@laxman: ```2021/05/26 16:16:45.612 INFO [RealtimeToOfflineSegmentsTaskExecutor] [TaskStateModelFactory-task_thread-3] Starting task: RealtimeToOfflineSegmentsTask with configs: {uploadURL=
@laxman: Beginning mapper phase ```2021/05/26 16:16:50.470 INFO [SegmentProcessorFramework] [TaskStateModelFactory-task_thread-3] Beginning mapper phase. Processing segments: [rawServiceView__7__1224__20210323T2227Z, rawServiceView__28__124__20210323T2303Z, rawServiceView__15__123__20210323T0106Z, rawServiceView__22__124__20210323T0126Z, rawServiceView__27__124__20210323T2237Z, rawServiceView__1__1229__20210323T0230Z, rawServiceView__24__124__20210323T2112Z, rawServiceView__26__122__20210322T2338Z, rawServiceView__17__123__20210323T0028Z, rawServiceView__11__123__20210322T2327Z, rawServiceView__6__1223__20210323T2015Z, rawServiceView__25__123__20210323T0012Z, rawServiceView__4__1230__20210323T0059Z, rawServiceView__9__123__20210323T0144Z, rawServiceView__23__123__20210323T0049Z, rawServiceView__0__4098__20210323T2302Z, rawServiceView__18__123__20210323T2249Z, rawServiceView__29__123__20210323T2116Z, rawServiceView__16__124__20210323T2230Z, rawServiceView__20__125__20210323T2208Z, rawServiceView__21__123__20210323T0038Z, rawServiceView__13__123__20210323T0006Z, rawServiceView__19__124__20210323T2318Z, rawServiceView__10__123__20210323T2201Z, rawServiceView__31__123__20210323T2150Z, rawServiceView__2__1226__20210323T2158Z] 2021/05/26 16:16:50.504 INFO [SegmentMapper] [TaskStateModelFactory-task_thread-3] Initialized mapper with id: rawServiceView__7__1224__20210323T2227Z, input segment: /tmp/PinotMinion/data/RealtimeToOfflineSegmentsTask/tmp-79ad3de0-c4d2-4d02-a452-0c6e8abbd2a1/workingDir/input_segments/rawServiceView__7__1224__20210323T2227Z, output dir: /tmp/segment_processor_1622045810469/mapper_output, recordTransformer: class org.apache.pinot.core.segment.processing.transformer.NoOpRecordTransformer, recordFilter: class org.apache.pinot.core.segment.processing.filter.FunctionEvaluatorRecordFilter, partitioners: class org.apache.pinot.core.segment.processing.partitioner.NoOpPartitioner 2021/05/26 16:16:50.519 INFO [ImmutableSegmentLoader] [TaskStateModelFactory-task_thread-3] Successfully loaded segment rawServiceView__7__1224__20210323T2227Z with readMode: mmap 2021/05/26 16:16:59.238 INFO [SegmentMapper] [TaskStateModelFactory-task_thread-3] Initialized mapper with id: rawServiceView__28__124__20210323T2303Z, input segment: /tmp/PinotMinion/data/RealtimeToOfflineSegmentsTask/tmp-79ad3de0-c4d2-4d02-a452-0c6e8abbd2a1/workingDir/input_segments/rawServiceView__28__124__20210323T2303Z, output dir: /tmp/segment_processor_1622045810469/mapper_output, recordTransformer: class org.apache.pinot.core.segment.processing.transformer.NoOpRecordTransformer, recordFilter: class org.apache.pinot.core.segment.processing.filter.FunctionEvaluatorRecordFilter, partitioners: class org.apache.pinot.core.segment.processing.partitioner.NoOpPartitioner```
@npawar: this is strange
@npawar: you have retention of 90 days
@npawar: but the window calculated is for Oct 28 2020 12pm to Oct 29th 2020
@npawar: these segments should not exist rt
@laxman: > but the window calculated is for Oct 28 2020 12pm to Oct 29th 2020 Where is it?
@laxman: windowStartMs?
@npawar: ```windowStartMs=1603886400000, segmentName=rawServiceView__0__4098__20210323T2302Z,rawServiceView__10__123__20210323T2201Z,rawServiceView__11__123__20210322T2327Z,rawServiceView__13__123__20210323T0006Z,rawServiceView__15__123__20210323T0106Z,rawServiceView__16__124__20210323T2230Z,rawServiceView__17__123__20210323T0028Z,rawServiceView__18__123__20210323T2249Z,rawServiceView__19__124__20210323T2318Z,rawServiceView__1__1229__20210323T0230Z,rawServiceView__20__125__20210323T2208Z,rawServiceView__21__123__20210323T0038Z,rawServiceView__22__124__20210323T0126Z,rawServiceView__23__123__20210323T0049Z,rawServiceView__24__124__20210323T2112Z,rawServiceView__25__123__20210323T0012Z,rawServiceView__26__122__20210322T2338Z,rawServiceView__27__124__20210323T2237Z,rawServiceView__28__124__20210323T2303Z,rawServiceView__29__123__20210323T2116Z,rawServiceView__2__1226__20210323T2158Z,rawServiceView__31__123__20210323T2150Z,rawServiceView__4__1230__20210323T0059Z,rawServiceView__6__1223__20210323T2015Z,rawServiceView__7__1224__20210323T2227Z,rawServiceView__9__123__20210323T0144Z, windowEndMs=1603929600000,```
@npawar: yes
@npawar: and wondowEndMs
@laxman: Shall I try deleting watermark and try?
@npawar: was this the very first run?
@npawar: or was it able to convert any segments successfullly so far?
@laxman: This is the first run. We enabled the converter task today in this cluster
@laxman: So far we have only 8 Realtime tables with 90 days of retention
@npawar: i wonder if the retention manager is deleting the segments before the minion task is able to download and work on them
@npawar: because they are older than 90 days
@npawar: can you listSegemnts?
@laxman:
@npawar: i meant, list segments of realtime table, and see if these segments that the minion task is expecting, actually exist
@laxman: Sure. Will check. Just trying to give more context. We enabled task converter for 7 out of 8 tables we have. For 3 tables there are some segments being generated. For 3 tables I see ZK watermark is Oct 28 as pointed out For 1 other table its generating OFFLINE but getting purged immediately (converter is slower than ingestion)
@laxman:
@laxman: I checked for couple of input segments. They are still available and ONLINE.
@npawar: for the second case, can you check why those old segments aren’t getting deleted? maybe there’s some issue with their time columns. I have a hunch that once you fix that, and restart the minion, it will work
@laxman: I checked for couple of input segments. They are still available and ONLINE.
@npawar: hmm, i think i see the issue
@laxman: Neha, I don’t think there are any stale segments
@npawar: this segment that you shared, has start time in October 2020, and end time in March 2021
@npawar: so there must be a gap in the data ingested.
@npawar: there is likely no data for Oct 28 12pm to Oct 29 00 window
@npawar: we can confirm the theory by running a query like `select count(*) from table where timecolumn >= oct 28 12pm and tiemcolumn < oct 29 00`
@npawar: i believe you had seen this before too in your dev env rt?
@npawar: if you can find the next valid window with data, then you can bump watermark to it
@laxman: dev cluster has only 5 days of retention
@laxman: staging cluster has 90 days retention similar to prod
@laxman: > i believe you had seen this before too in your dev env rt? > if you can find the next valid window with data, then you can bump watermark to it Not us. I think someone posted in the <#C011C9JHN7R|troubleshooting> channel. I responded to that thread to bump the watermark
@laxman: checking the data
@npawar: right..
@laxman: ```select $segmentName, ToDateTime(min(start_time_millis),'yyyy-MM-dd-HH-mm-ss') start_time, ToDateTime(max(start_time_millis) ,'yyyy-MM-dd-HH-mm-ss') end_time, count(*) from rawServiceView group by $segmentName order by min(start_time_millis) limit 100```
@laxman: Ran this query. I see old data from the segments from 28-OCTOBER
@laxman: Why retention didn’t kick in? Why these records/segments were open for so long
@laxman: More of rhetorical questions. Sorry
@npawar: oh cool, i was just about to suggest this ```select count(*), toDateTime(start_time_millis, 'yyyyMMdd') from rawServiceView_REALTIME group by toDateTime(start_time_millis, 'yyyyMMdd') limit 100000```
@npawar: they didn’t get deleted, because the single segment contains data that spans Oct 28 all the way to Mar 24 2021
@npawar: and only 900k docs.
@npawar: so many days must have no data
@npawar: we should handle this in the realtimeToOffline task. Can you please create a github issue?
@npawar: basically be able to detect the data gap, and push the watermark automatically
@laxman: Sure. I will file this
@npawar: thanks. will fix this weekend
@npawar: for now, the workaround should help
@laxman: got it
@laxman: ```select ToDateTime(start_time_millis,'yyyy-MM-dd') start_date, count(*) from rawServiceView group by ToDateTime(start_time_millis,'yyyy-MM-dd') order by min(start_time_millis) limit 100```
@laxman: Ran this.
@laxman: Got only 92 records
@laxman: ```{"columns":["start_date","count(*)"],"records":[["2020-10-28",161],["2021-02-25",17019105],["2021-02-26",27019989],["2021-02-27",27239160],["2021-02-28",28676609],["2021-03-01",26861991],["2021-03-02",29299536],["2021-03-03",30389299],["2021-03-04",29834630],["2021-03-05",29692987],["2021-03-06",29407191],["2021-03-07",30508388],["2021-03-08",29469985],["2021-03-09",27941063],["2021-03-10",28860070],["2021-03-11",27285435],["2021-03-12",26597606],["2021-03-13",25411989],["2021-03-14",26803238],["2021-03-15",18713984],["2021-03-16",26822248],["2021-03-17",31016073],["2021-03-18",32819545],["2021-03-19",30862383],["2021-03-20",30721963],["2021-03-21",31843483],["2021-03-22",32043729],["2021-03-23",31947475],["2021-03-24",32468734],["2021-03-25",30990570],["2021-03-26",31295396],["2021-03-27",28383215],["2021-03-28",30695773],["2021-03-29",32265964],["2021-03-30",32682733],["2021-03-31",30812768],["2021-04-01",27908306],["2021-04-02",30375850],["2021-04-03",29516536],["2021-04-04",30795493],["2021-04-05",35805212],["2021-04-06",35197561],["2021-04-07",32588352],["2021-04-08",29104352],["2021-04-09",29764998],["2021-04-10",28317299],["2021-04-11",32115433],["2021-04-12",33573782],["2021-04-13",31128056],["2021-04-14",28797771],["2021-04-15",27215955],["2021-04-16",30697883],["2021-04-17",30409886],["2021-04-18",31575398],["2021-04-19",32379164],["2021-04-20",24732208],["2021-04-21",22028350],["2021-04-22",29452080],["2021-04-23",28471392],["2021-04-24",31714532],["2021-04-25",34933660],["2021-04-26",35804407],["2021-04-27",21076317],["2021-04-28",24273787],["2021-04-29",27841802],["2021-04-30",29620131],["2021-05-01",33857916],["2021-05-02",34894591],["2021-05-03",33374346],["2021-05-04",36305559],["2021-05-05",32521701],["2021-05-06",31459620],["2021-05-07",25539236],["2021-05-08",28862560],["2021-05-09",32522829],["2021-05-10",32516216],["2021-05-11",34548391],["2021-05-12",30889484],["2021-05-13",23788118],["2021-05-14",26652424],["2021-05-15",32277568],["2021-05-16",39006335],["2021-05-17",33269796],["2021-05-18",32772548],["2021-05-19",35431810],["2021-05-20",31318503],["2021-05-21",30066141],["2021-05-22",32044535],["2021-05-23",32807414],["2021-05-24",32750567],["2021-05-25",25669358],["2021-05-26",19632706]]}```
@npawar: cool, so theory checks out.
@npawar: `"2020-10-28",161` and then directly 2021-02
@laxman: Yes.
@laxman: Not sure how we ended up with this.
@laxman: Will inspect the data one more time and bump the watermark.
@laxman: Thanks a lot @npawar. Thanks to all of you for your time and patience.
@laxman: Bumping the watermark to `2021-02-25` day start. And with that we will losing 161 records. Right? Just validating my understanding
@npawar: right.
@npawar: the first bucketTime should’ve run though..
@npawar: it should’ve started failing after it crossed those initial records
@laxman: Tried bumping watermark for 2 tables. It worked. Now conversion logic moves ahead
@laxman: Have another question related to this flow.
@laxman: Our REALTIME tables segments size is controlled using `"realtime.segment.flush.threshold.segment.size": "100M"` Do we have any similar configuration for OFFLINE tables which can be used during conversion? I checked table config and realtime 2 offline converter config in the docs. Couldn’t find any thing in docs/code
@laxman: @jackie.jxt @fx19880617
@npawar: during conversion, you can control the number of docs
@npawar: from the documentation ```"maxNumRecordsPerSegment": "100_000"```
@npawar:
@npawar: there’s no way to configure size based. But you can estimate the num docs, from the size/docs ratio in the realtime segments
@laxman: okay. Records config I already noticed. Size based control would have been better in this case as many fields got added to schema over last 3 months. Hence size of the record increased significantly.
@npawar: mind creating another github issue?
@laxman: will do Neha
@laxman: Filed two issues
#pinot-docsrus
@mayanks: @mayanks has joined the channel
@nadeemsadim: @nadeemsadim has joined the channel
@mayanks: I have updated the Channel title with a link on how to update the docs.
@kelvin: @kelvin has joined the channel
@shgandhi: @shgandhi has joined the channel
@steotia: @steotia has joined the channel
@g.kishore: @g.kishore has joined the channel
--------------------------------------------------------------------- To unsubscribe, e-mail: dev-unsubscr...@pinot.apache.org For additional commands, e-mail: dev-h...@pinot.apache.org
