Hi All, Does anyone know if pinot support Case-When Statement with LIKE expression. I need the case when statement matching with a part of a text .

Hi team I have hybrid table and met a problem like this. For example, Let's say that there is a row with pk '1'.

select * from A_OFFLINE where pk='1' --data displays
select * from A where pk='1' --data doesn't display
select * from A --data with pk 1 displays

Is this behavior expected thing?

Does pinot JDBC client support pinot authentication. We are trying to connect to our pinot instance but keep getting 403.

When I am trying to create the pinot table using presto pinot connector, it seems impossible to define the maxLength for String Column. Did I miss anything?

Hi Is there a way to get min, max, sum as Long type? I am querying min and max values for Timestamp (Long) column in a table and the results are in Double

select min("Timestamp"), max("Timestamp") from test

Looking at the source code min, max and sum only return Double

It seems the s3 deep store controller dir cannot end with / I had following config

controller.data.dir=<s3://pinot-data/pinot/controller_data/>

When controller tried uploading completed realtime segment. The path had // , between controller data dir and the table dir which lead to failure to copy and then deleting the temp segments

2022/01/18 12:08:31.989 INFO [S3PinotFS] [grizzly-http-server-5] mkdir <s3://pinot-data/pinot/controller_data/test>
2022/01/18 12:08:32.008 INFO [S3PinotFS] [grizzly-http-server-5] Copying uri <s3://pinot-data/pinot/controller_data//test/test__3__0__20220118T1147Z.tmp.c88bn119-kdei-kf87-ki87-z937482d8x5> to uri <s3://pinot-data/pinot/controller_data//test/test__3__0__20220118T1147Z>
2022/01/18 12:08:32.047 INFO [S3PinotFS] [grizzly-http-server-5] Deleting uri <s3://pinot-data/pinot/controller_data//test/test__3__0__20220118T1147Z> force true
2022/01/18 12:08:32.088 ERROR [SegmentCompletionFSM_test__3__0__20220118T1147Z] [grizzly-http-server-5] Caught exception while committing segment file for segment: test__3__0__20220118T1147Z
java.io.IOException: software.amazon.awssdk.services.s3.model.NoSuchKeyException: The specified key does not exist.

Removing / at the end of controller dir resulted in correct path

controller.data.dir=<s3://pinot-data/pinot/controller_data>

Is it some know issue in 0.9.3 ?

Does pinot support partitioning realtime table over a column while ingesting from Kinesis? From docs it seems realtime partitioning is supported only with Kafka

Trying to launch a offline DIM table load using. The process finishes. The segments seem to be created. But I don't see any data. Does

pinot-admin.sh

push logs relevant to the job other than what I see in console ?

apache-pinot-0.9.3-bin/bin/pinot-admin.sh LaunchDataIngestionJob -jobSpecFile standaloneJobSpec.yaml

Hi, here is question on partial upsert table with out of order events When there is a timeColumnName = “update_time” which is the same as each __time column, we wanted to test whether overwrite is working under the condition that time columns is not in order. Even though we sent kafka messages not in order , we expected the overwrite result would have been worked in the order by update_time. However, after first event (actual order in the events might be the second)’s update_time is given, if the second event's (let’s assume actual order of the second event might be the first)update_time is smaller then the first event, nothing is partially overwritten since first event’s update_time is larger. We wanted to see pinot automatically set the second event as the actual first event by comparing the first event -> and automatically partial-overwrite first event “on” second event. In the documentation, it said handling out-of-order events is possible. Is it only adapted on append mode table config? if it is not, how can we use these out-of-order events in partially overwriting table? Table config { "tableName":"upsertTest1", "tableType":"REALTIME", "segmentsConfig":{ "timeColumnName":"update_time", "timeType":"MILLISECONDS", "schemaName":"upsertTest1", "replicasPerPartition":"2" }, "tenants":{ }, "tableIndexConfig":{ "loadMode":"MMAP", "streamConfigs":{ "streamType":"kafka", "realtime.segment.flush.threshold.time":"6h", "stream.kafka.consumer.type":"lowLevel", "stream.kafka.consumer.prop.auto.offset.reset":"smallest", "stream.kafka.topic.name":"test.upsert.test", "stream.kafka.decoder.class.name":"org.apache.pinot.plugin.stream.kafka.KafkaJSONMessageDecoder", "stream.kafka.consumer.factory.class.name":"org.apache.pinot.plugin.stream.kafka20.KafkaConsumerFactory", "stream.kafka.broker.list”:~~~~ }, "nullHandlingEnabled":true }, "fieldConfigList":[ ], "metadata":{ "customConfigs":{ } }, "routing":{ "instanceSelectorType":"strictReplicaGroup" }, "upsertConfig":{ "mode":"PARTIAL", "partialUpsertStrategies":{ "a":"OVERWRITE", "b":"OVERWRITE", "c":"OVERWRITE", "d":"OVERWRITE", "pending_time":"OVERWRITE", "issued_time":"OVERWRITE", "matched_time":"OVERWRITE", "unmatched_time":"OVERWRITE" } } } Schema config { "schemaName": "upsertTest1", "dimensionFieldSpecs": [ { "name": "demand_id", "dataType": "STRING" }, { "name": "a", "dataType": "STRING" }, { "name": "b", "dataType": "STRING" }, { "name": "c", "dataType": "STRING" }, { "name": "d", "dataType": "STRING" } ], "dateTimeFieldSpecs": [ { "name": "update_time", "dataType": "TIMESTAMP", "format": "1MILLISECONDSEPOCH", "granularity": "1:MILLISECONDS" }, { "name": "create_time", "dataType": "TIMESTAMP", "format": "1MILLISECONDSEPOCH", "granularity": "1:MILLISECONDS" }, { "name": "pending_time", "dataType": "TIMESTAMP", "format": "1MILLISECONDSEPOCH", "granularity": "1:MILLISECONDS" }, { "name": "issued_time", "dataType": "TIMESTAMP", "format": "1MILLISECONDSEPOCH", "granularity": "1:MILLISECONDS" }, { "name": "matched_time", "dataType": "TIMESTAMP", "format": "1MILLISECONDSEPOCH", "granularity": "1:MILLISECONDS" }, { "name": "unmatched_time", "dataType": "TIMESTAMP", "format": "1MILLISECONDSEPOCH", "granularity": "1:MILLISECONDS" } ], "primaryKeyColumns": [ "demand_id" ] }

Hi, I am experimenting with instance assignment for partitions Trying to get all the segments for a partition in one server, with no replication (1 replica)

"instanceAssignmentConfigMap": {
      "OFFLINE": {
        "tagPoolConfig": {
          "tag": "DefaultTenant_OFFLINE",
          "poolBased": false,
          "numPools": 0
        },
        "replicaGroupPartitionConfig": {
          "replicaGroupBased": true,
          "numInstances": 2,
          "numReplicaGroups": 1,
          "numInstancesPerReplicaGroup": 2,
          "numPartitions": 4,
          "numInstancesPerPartition": 1
        }
      }
    },

Getting this error when trying rebalance server dry run

"description": "Caught exception while calculating target assignment: java.lang.IllegalStateException: Instance partitions: transactions_partitioned_OFFLINE should contain 1 replica and 1 partition for non-replica-group based assignment"

What could be wrong with this config?

Hey everyone. Quick question.. Is it possible to use the RealtimeToOfflineSegmentsTask with refresh offline tables?

Hey everyone again 👋 What is the easiest way to figure out why certain components are in “BAD” state? (Tables, segments etc). Is there any resource/API I can call to get a description as to why?

Hello 👋 I am seeing an error while trying to reload one of my segments.. Anyone seen this before?

We noticed that some brokers seem to have missing routing entries, ex. missing segments and/or servers. When the broker starts up, we see messages like:

WARN [BaseInstanceSelector] [HelixTaskExecutor-message_handle_thread] Failed to find servers hosting segment: MYTABLE-1641542240329_2022-01-06_2022-01-06_6 for table: MYTABLE

When we issue the rebuild routing table api to the broker it just rebuilds an identical routing table with the same missing servers/segments. Is there any way to resolve that? We are also looking... could it be related to

ZkCacheBaseDataAccessor

not refreshing?

Hi everyone does anyone know why this query fails: select * from mydata where epoc_time_stamp > now() - 86400000 if I just use now() it works fine. but when i deduct 1 day from now() I get the following error: "message": "QueryExecutionError\norg.apache.pinot.spi.exception.BadQueryRequestException java.lang.IllegalArgumentException: Cannot convert value: '1.64264431985E12' to type: LONG\n\tat interestingly this query works: select epoc_time_stamp, now() as mynow from mydata where epoc_time_stamp > mynow - 86400000

Hi Team, Is there any way to send notification to UI or BI tools while or after pinot consuming the data from kafka topic. please let me know if there is way or how can pinot notify to UI?

Hi team. We have 16 partitions in a particular topic. We’re observing that one particular partition (partition 6) is not getting assigned to any realtime server. Due to this, there is no ingestion happening from that partition and the lag is increasing linearly. We rotated the realtime servers but that partition just isn’t getting assigned to any server (verified this form IDEASTATE. That partition doesn’t have any CONSUMING entry). How can we debug this further?

java.util.concurrent.ExecutionException: org.apache.pinot.client.PinotClientException: Pinot returned HTTP status 500, expected 200 at org.apache.pinot.client.JsonAsyncHttpPinotClientTransport$BrokerResponseFuture.get(JsonAsyncHttpPinotClientTransport.java:191) at org.apache.pinot.client.JsonAsyncHttpPinotClientTransport$BrokerResponseFuture.get(JsonAsyncHttpPinotClientTransport.java:141) at org.apache.pinot.client.Connection$ResultSetGroupFuture.get(Connection.java:221) at org.apache.pinot.client.Connection$ResultSetGroupFuture.get(Connection.java:212) at org.apache.pinot.client.Connection$ResultSetGroupFuture.get(Connection.java:186) at org.apache.pinot.client.WorkerThread.processCommand(WorkerThread.java:48) at org.apache.pinot.client.WorkerThread.run(WorkerThread.java:29) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:834) Caused by: org.apache.pinot.client.PinotClientException: Pinot returned HTTP status 500, expected 200 at org.apache.pinot.client.JsonAsyncHttpPinotClientTransport$BrokerResponseFuture.get(JsonAsyncHttpPinotClientTransport.java:185) ... 9 more

Hi Team I am using Kinesis consumer of real time table The server keeps logging

WARN [KinesisConsumer] Task aborted due to exception

and below stack trace

2022/01/20 10:55:36.685 WARN [KinesisConsumer] [pool-207-thread-1] Task aborted due to exception.
shaded.software.amazon.awssdk.core.exception.AbortedException: Thread was interrupted
        at shaded.software.amazon.awssdk.core.exception.AbortedException$BuilderImpl.build(AbortedException.java:84) ~[pinot-all-0.10.0-SNAPSHOT-jar-with-dependencies.jar:0.10.0-SNAPSHOT-44c4b65b990e4e05dbca973f3d7073c89dc3dc65]
        at shaded.software.amazon.awssdk.core.exception.AbortedException.create(AbortedException.java:38) ~[pinot-all-0.10.0-SNAPSHOT-jar-with-dependencies.jar:0.10.0-SNAPSHOT-44c4b65b990e4e05dbca973f3d7073c89dc3dc65]
        at shaded.software.amazon.awssdk.core.internal.http.pipeline.stages.ApiCallTimeoutTrackingStage.handleInterruptedException(ApiCallTimeoutTrackingStage.java:143) ~[pinot-all-0.10.0-SNAPSHOT-jar-with-dependencies.jar:0.10.0-SNAPSHOT-44c4b65b990e4e05dbca973f3d7073c89dc3dc65]
        at shaded.software.amazon.awssdk.core.internal.http.pipeline.stages.ApiCallTimeoutTrackingStage.translatePipelineException(ApiCallTimeoutTrackingStage.java:107) ~[pinot-all-0.10.0-SNAPSHOT-jar-with-dependencies.jar:0.10.0-SNAPSHOT-44c4b65b990e4e05dbca973f3d7073c89dc3dc65]
        at shaded.software.amazon.awssdk.core.internal.http.pipeline.stages.ApiCallTimeoutTrackingStage.execute(ApiCallTimeoutTrackingStage.java:62) ~[pinot-all-0.10.0-SNAPSHOT-jar-with-dependencies.jar:0.10.0-SNAPSHOT-44c4b65b990e4e05dbca973f3d7073c89dc3dc65]
        at shaded.software.a

Is this normal mode of operation of kinesis consumer? It seems the threads gets aborted if there is nothing new to consume from stream. On querying, the table real time is returning the expected data and number of records, but server continuously logs this exception

Hi team, is there any mechanism to ingest data synchronously into Apache Pinot and make it immediately available? Asking purely for unit testing purposes - ideally the ingestion should be synchronous.

Hi Team, We were trying out to move the completed segments to different hosts, but the completed segment is still on REALTIME Server and not moved to OFFLINE Server. Reference: https://docs.pinot.apache.org/operators/operating-pinot/tuning/realtime#moving-completed-segments-to-different-hosts Servers are tagged like below, server1: "listFields": { "TAG_LIST": [ "DefaultTenant_OFFLINE" ] } Serve2: "listFields": { "TAG_LIST": [ "DefaultTenant_REALTIME" ] } Table Config: "tenants": { "broker": "DefaultTenant", "server": "DefaultTenant", "tagOverrideConfig": { "realtimeConsuming": "DefaultTenant_REALTIME", "realtimeCompleted": "DefaultTenant_OFFLINE" } }, can someone help?

Hi team, Regarding Pinot Cluster Design, 1) keeping controller along with Server is good approach? (w.r.t disk, memory and network). 2) If not, how to go about hosting controller? can we keep it along with any of the component? or it should be hosted alone? 3) If we keep controller separately, Won't a increase in hop will impact read and write performance?

Can some please on this? https://github.com/apache/pinot/issues/8041

Hi Team, encountered an issue with Realtime Table upsert mode. I have a primary key, which is present in Realtime Completed Segment. When i am pushing the same primary with some updated values now, query is retruning 2 rows. Consuming Segment is on Server1 and completed segment is present to Server2.( i,e another server) Can someone help?

Hi team, I have a question regarding how to correctly configure

maxUsableHostMemory

in

RealtimeProvisioningHelper

. We have 6 tables being consumed by around 10 realtime servers. Each server has an upper limit of 40G of memory. Segments are also relocated to a different set of servers (not realtime) once they’re committed. The docs say “This is the total memory available in each host for hosting

retentionHours

worth of data (i.e. “hot” data) of this table.“. How would the value be calculated in this case? Should be simply do 40/6 as a rough estimate?

Hi Team I am using this Pinot Chart https://github.com/apache/pinot/tree/master/kubernetes/helm/pinot But while Upgrade with helm cmd its throughing an ERROR as:

Error: UPGRADE FAILED: create: failed to create: Request entity too large: limit is 3145728

It Seems due to zookeeper chart folder. Can anyone pls help me with resolution for this?? Thanks In advance.

Hi Folks, Does the Kinesis consumer handles shard splitting? I have a realtime table which has stopped consuming events. Looking at the logs all the realtime segments are completed. There are no consuming segments. It correlates to shard split in Kinesis. It seems Pinot consumers stopped consuming after the shard is split. There are no error logs. Is there some manual step that is required if shard split happens?

Hi Team, Observed one more case in RealTime Table. I was ingesting backdated data I.e from Jan 1,2022 yesterday. I had set retention to 15days. After sometime, segments were getting deleted, upon checking the logs. I got the following: 2022/01/24 070250.350 INFO [PeriodicTaskScheduler] [pool-10-thread-5] Starting RetentionManager with running frequency of 21600 seconds. 2022/01/24 070250.350 INFO [BasePeriodicTask] [pool-10-thread-5] [TaskRequestId: auto] Start running task: RetentionManager 2022/01/24 070250.352 INFO [ControllerPeriodicTask] [pool-10-thread-5] Processing 2 tables in task: RetentionManager 2022/01/24 070250.352 INFO [RetentionManager] [pool-10-thread-5] Start managing retention for table: reporting_aggregations_OFFLINE 2022/01/24 070250.358 INFO [RetentionManager] [pool-10-thread-5] Segment push type is not APPEND for table: reporting_aggregations_OFFLINE, skip managing retention 2022/01/24 070250.360 INFO [RetentionManager] [pool-10-thread-5] Segment lineage metadata clean-up is successfully processed for table: reporting_aggregations_OFFLINE 2022/01/24 070250.360 INFO [RetentionManager] [pool-10-thread-5] Start managing retention for table: reporting_aggregations_REALTIME 2022/01/24 070250.396 INFO [RetentionManager] [pool-10-thread-5] Deleting 4 segments from table: reporting_aggregations_REALTIME 2022/01/24 070250.396 INFO [PinotHelixResourceManager] [pool-10-thread-5] Trying to delete segments: [reporting_aggregations__0__0__20220124T1232Z, reporting_aggregations__0__1__20220124T1346Z, reporting_aggregations__0__2__20220124T1418Z, reporting_aggregations__0__3__20220124T1438Z] from table: reporting_aggregations_REALTIME 2022/01/24 070250.414 INFO [RetentionManager] [pool-10-thread-5] Segment lineage metadata clean-up is successfully processed for table: reporting_aggregations_REALTIME 2022/01/24 070250.414 INFO [RetentionManager] [pool-10-thread-5] Removing aged (more than 7 days) deleted segments for all tables 2022/01/24 070250.911 INFO [ControllerPeriodicTask] [pool-10-thread-5] Finish processing 2/2 tables in task: RetentionManager 2022/01/24 070250.911 INFO [BasePeriodicTask] [pool-10-thread-5] [TaskRequestId: auto] Finish running task: RetentionManager in 561ms 2022/01/24 070252.420 INFO [SegmentDeletionManager] [PinotHelixResourceManagerExecutorService] Deleted 0 segments from table reporting_aggregations_REALTIME:[] 2022/01/24 070252.420 INFO [SegmentDeletionManager] [PinotHelixResourceManagerExecutorService] Postponing deletion of 4 segments from table reporting_aggregations_REALTIME 2022/01/24 070256.457 INFO [SegmentDeletionManager] [PinotHelixResourceManagerExecutorService] Moved segment reporting_aggregations__0__3__20220124T1438Z from hdfs://nameservice1/data/max/poc/hdfs/controller/reporting_aggregations/reporting_aggregations__0__3__20220124T1438Z to hdfs://nameservice1/data/max/poc/hdfs/controller/Deleted_Segments/reporting_aggregations/reporting_aggregations__0__3__20220124T1438Z 2022/01/24 070256.468 INFO [SegmentDeletionManager] [PinotHelixResourceManagerExecutorService] Moved segment reporting_aggregations__0__0__20220124T1232Z from hdfs://nameservice1/data/max/poc/hdfs/controller/reporting_aggregations/reporting_aggregations__0__0__20220124T1232Z to hdfs://nameservice1/data/max/poc/hdfs/controller/Deleted_Segments/reporting_aggregations/reporting_aggregations__0__0__20220124T1232Z 2022/01/24 070256.468 INFO [SegmentDeletionManager] [PinotHelixResourceManagerExecutorService] Deleted 2 segments from table reporting_aggregations_REALTIME:[reporting_aggregations__0__3__20220124T1438Z, reporting_aggregations__0__0__20220124T1232Z] 2022/01/24 070256.469 INFO [SegmentDeletionManager] [PinotHelixResourceManagerExecutorService] Postponing deletion of 2 segments from table reporting_aggregations_REALTIME 2022/01/24 070258.353 INFO [PeriodicTaskScheduler] [pool-10-thread-6] Starting SegmentStatusChecker with running frequency of 300 seconds. 2022/01/24 070258.353 INFO [BasePeriodicTask] [pool-10-thread-6] [TaskRequestId: auto] Start running task: SegmentStatusChecker 2022/01/24 070258.355 INFO [ControllerPeriodicTask] [pool-10-thread-6] Processing 2 tables in task: SegmentStatusChecker 2022/01/24 070258.372 INFO [ControllerPeriodicTask] [pool-10-thread-6] Finish processing 2/2 tables in task: SegmentStatusChecker 2022/01/24 070258.372 INFO [BasePeriodicTask] [pool-10-thread-6] [TaskRequestId: auto] Finish running task: SegmentStatusChecker in 19ms 2022/01/24 070300.716 INFO [LeadControllerUtils] [ControllerLeadershipFetchingThread] Getting Helix leader: d9-max-insert-1.srv.net_9000, Helix version: 0.9.8, mtime: 1643035318065 2022/01/24 070304.488 INFO [SegmentDeletionManager] [PinotHelixResourceManagerExecutorService] Moved segment reporting_aggregations__0__2__20220124T1418Z from hdfs://nameservice1/data/max/poc/hdfs/controller/reporting_aggregations/reporting_aggregations__0__2__20220124T1418Z to hdfs://nameservice1/data/max/poc/hdfs/controller/Deleted_Segments/reporting_aggregations/reporting_aggregations__0__2__20220124T1418Z 2022/01/24 070304.497 INFO [SegmentDeletionManager] [PinotHelixResourceManagerExecutorService] Moved segment reporting_aggregations__0__1__20220124T1346Z from hdfs://nameservice1/data/max/poc/hdfs/controller/reporting_aggregations/reporting_aggregations__0__1__20220124T1346Z to hdfs://nameservice1/data/max/poc/hdfs/controller/Deleted_Segments/reporting_aggregations/reporting_aggregations__0__1__20220124T1346Z 2022/01/24 070304.497 INFO [SegmentDeletionManager] [PinotHelixResourceManagerExecutorService] Deleted 2 segments from table reporting_aggregations_REALTIME:[reporting_aggregations__0__2__20220124T1418Z, reporting_aggregations__0__1__20220124T1346Z] 2022/01/24 070313.351 INFO [PeriodicTaskScheduler] [pool-10-thread-7] Starting RealtimeSegmentValidationManager with running frequency of 3600 seconds. 2022/01/24 070313.351 INFO [BasePeriodicTask] [pool-10-thread-7] [TaskRequestId: auto] Start running task: RealtimeSegmentValidationManager 2022/01/24 070313.352 INFO [ControllerPeriodicTask] [pool-10-thread-7] Processing 2 tables in task: RealtimeSegmentValidationManager 2022/01/24 070313.352 INFO [RealtimeSegmentValidationManager] [pool-10-thread-7] Run segment-level validation 2022/01/24 070313.425 INFO [ConsumerConfig] [pool-1

can partial upsert be adapted in schema evolution throughout realtime data?

Hi All, I am new to Pinot, we are trying to load csv file into table using config.yaml file. but we are getting error. could you please help us to load the file into table.