Hi, how can I speed up segment reload from offline to online after server restart? I have 1500 tables for realtime partial upsert, and they have ran several days. After restarted, the server logs show

Sleeping 1 second waiting for all segments loaded for table: ...

I also found lots of logs in controller said

Event b45f4423_DEFAULT : Message already exists for {server} to transit {table}.{segment} from OFFLINE to ONLINE, isRelay: false

The cpu and memory resource did not exhaust by controller and zookeeper. How can I speed up segment reload?

Hi everyone, I recently found an issue while ingesting data in the offline table. My pipeline for offline table ingestion is: 1. Extracting data from mysql and converting it to parquet files. 2. Ingesting these parquet files to the offline table using spark ingestion job. Now I found that while ingesting a column which was originally of date data type in mysql, all my rows in pinot had the same column as a default value i.e. 1970-01-01. On debugging further, I got to know that the date datatype is stored as INT32 in parquet and the NativeRecordExtractor on the pinot side has handling only for INT96 columns (datetime columns). Basically for datetime columns in pinot side, we only have handling for INT96 columns and in my case, INT92 goes unhandled which certainly is causing this issue. Is there a workaround for this? Could someone please help

Hi team, we are load testing pinot with jmeter. We are facing this issue while running test plan :

java.sql.SQLException: Cannot create PoolableConnectionFactory (null)
	at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:653) ~[commons-dbcp2-2.9.0.jar:2.9.0]
	at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:531) ~[commons-dbcp2-2.9.0.jar:2.9.0]
	at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:731) ~[commons-dbcp2-2.9.0.jar:2.9.0]
	at org.apache.jmeter.protocol.jdbc.config.DataSourceElement.initPool(DataSourceElement.java:308) ~[ApacheJMeter_jdbc.jar:5.5]
	at org.apache.jmeter.protocol.jdbc.config.DataSourceElement.testStarted(DataSourceElement.java:128) ~[ApacheJMeter_jdbc.jar:5.5]
	at org.apache.jmeter.engine.StandardJMeterEngine.notifyTestListenersOfStart(StandardJMeterEngine.java:218) ~[ApacheJMeter_core.jar:5.5]
	at org.apache.jmeter.engine.StandardJMeterEngine.run(StandardJMeterEngine.java:396) ~[ApacheJMeter_core.jar:5.5]
	at java.lang.Thread.run(Thread.java:1623) ~[?:?]
Caused by: java.sql.SQLFeatureNotSupportedException
	at org.apache.pinot.client.base.AbstractBaseConnection.getAutoCommit(AbstractBaseConnection.java:114) ~[pinot-jdbc-client-0.13.0-SNAPSHOT-shaded.jar:0.13.0-SNAPSHOT-0797e05da61c37629da3e99bdeb4d96dc60d7911]
	at org.apache.commons.dbcp2.DelegatingConnection.getAutoCommit(DelegatingConnection.java:308) ~[commons-dbcp2-2.9.0.jar:2.9.0]
	at org.apache.commons.dbcp2.PoolableConnectionFactory.activateObject(PoolableConnectionFactory.java:119) ~[commons-dbcp2-2.9.0.jar:2.9.0]
	at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:108) ~[commons-dbcp2-2.9.0.jar:2.9.0]
	at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:649) ~[commons-dbcp2-2.9.0.jar:2.9.0]
	... 7 more

can someone please help on how to establish connection properly in jmeter

Hello Team, We are using minion task POST api to schedule ingestion job by running following API for our tables. /tasks/schedule { "taskType": "SegmentGenerationAndPushTask", "tableName": "mytable" } But above API gives following response always in case of success(segment loaded successfully for table from input directory). Response: { "code": 500, "error": "HTTP 500 Internal Server Error" } Is this a bug? We are using Pinot version 0.12.0 Please let me know if there is any workaround for this issue

Hello all - I'm running Pinot on Kubernetes, installed via helm. How do I configure S3 for Deep Storage? It's not totally clear where the configs go from the documentation. I have detailed in a Github issue on exceptions I'm facing and the commands I've tried.I originally passed the configs to

helm install --set config.key=value

Hi All, We are facing an issue with pinot. We use trino to query pinot. When the query uses "join" with a

...IS NOT NULL

condition, the query fails (trino: internal error) , but when the query uses left / right join with the same conditions, it executes albeit with incorrect results. Any ideas on why this may be happening and how we should go about debugging it? Thanks !!!

Hello @Seunghyun @Mayank @Mathieu Alexandre We have some issues with realtime tables since the migration we did to Pinot 0.10.0. The issue seeams related to pinot and kafka. After the migration, Stream ingestion is not working.

Is there a way to do a date diff in pinot, i didnt see the function may be i am missing it. We have a date column that is string and would like to do a date diff like date_diff(‘day’, cast(date_key as date), TIMESTAMP ‘2023-04-22’). is this possible in pinot

Hi Team, i configured pinot cluster, and load testing using localhost:8099(port-forwarding), queries are not going to all brokers, what is simplest way to configure a Http load balancer

Hi Team, I am trying to configure Zookeeper username and password by setting env variable ZOOKEEPER_USERNAME and ZOOKEEPER_PASSWORD in controller . But keep getting exception in thread

Would appreciate any help here. How did you configure S3 Deep Store on k8s? Where exactly does the config go? I tried values.yaml

What does the exception

Caused by: java.lang.IllegalStateException: PinotFS for scheme: s3 has not been initialized

exactly mean? Log is from the controller. Assuming I passed in in the correct configs to helm values, do I need to do a custom build of Pinot?

Hello I am new Pinot. I have a question on ingest transformations. I have a string field with time value in yyyy-mm-ddThhmmssZ in the input csv file. I want to use this for time dimension string value and use superset for chart generation. My questions 1. can use the string value of time dimension for chart generation (superset shows as varchar) ? 2. I created two other fields timee_epoch field with timestamp datatype and timestamp_long with long data type for epoch seconds. In the table schema I have used transformation functions to convert into fromEpochSecondsfromEpochSeconds(fromDateTime(dateTimeStr, ’yyyy-MM-dd’’T’‘HHmmssZ’)) . After data ingestion the derived fields are showing negative value and 1970 Jan 01 000000 for long ad timestamp fields, which indicating the null value. The string field has expected correct times. could some one tell what could have gone wrong and how to debug?

any ideas on how i can get

PROPERTYSTORE/SEGMENTS/<table_name>

path of a cluster's zookeeper? ( I am looking for to use swagger for this ) .

Is there any way to get access to the

ChannelHandlerContext

inside the controller and broker access control implementations? Looking at the code probably not but just wanted to make sure. Ideally we would want to use mTLS in combination with the information stored in the cert to make decisions whether a user has access to an operation

We have a batch data load usecase where we need to partition segments based on country so when the user filters on country only selected segments for the country are queried. We have 4 countries. When i used the murmur partition logic it created 4 segments files which are huge in size. How do i create segments of lower size say 100 segments instead of just 4 while i apply the murmur partition logic that pinot will understand. Is there any config?

Hi team, we are facing the following error when we use

SparkSegmentTarPushJobRunner

to create and push segment files to pinot

[HelixStateTransitionHandler] [HelixTaskExecutor-message_handle_thread_22] Exception while executing a state transition task dimension_xxx
java.lang.reflect.InvocationTargetException: null
	at jdk.internal.reflect.GeneratedMethodAccessor356.invoke(Unknown Source) ~[?:?]
	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
	at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
	at org.apache.helix.messaging.handling.HelixStateTransitionHandler.invoke(HelixStateTransitionHandler.java:350) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.helix.messaging.handling.HelixStateTransitionHandler.handleMessage(HelixStateTransitionHandler.java:278) [pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.helix.messaging.handling.HelixTask.call(HelixTask.java:97) [pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.helix.messaging.handling.HelixTask.call(HelixTask.java:49) [pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?]
	at java.lang.Thread.run(Thread.java:829) [?:?]
Caused by: java.lang.RuntimeException: Caught exception while loading lookup table: xxxDim_OFFLINE after adding segment: dimension_xxx
	at org.apache.pinot.core.data.manager.offline.DimensionTableDataManager.addSegment(DimensionTableDataManager.java:101) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.pinot.core.data.manager.BaseTableDataManager.addSegment(BaseTableDataManager.java:216) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.pinot.core.data.manager.BaseTableDataManager.addOrReplaceSegment(BaseTableDataManager.java:409) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.pinot.server.starter.helix.HelixInstanceDataManager.addOrReplaceSegment(HelixInstanceDataManager.java:385) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.pinot.server.starter.helix.SegmentOnlineOfflineStateModelFactory$SegmentOnlineOfflineStateModel.onBecomeOnlineFromOffline(SegmentOnlineOfflineStateModelFactory.java:163) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	... 11 more
Caused by: java.lang.RuntimeException: Failed to use PinotSegmentRecordReader to read immutable segment
	at org.apache.pinot.segment.local.indexsegment.immutable.ImmutableSegmentImpl.getRecord(ImmutableSegmentImpl.java:232) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.pinot.core.data.manager.offline.DimensionTableDataManager.createDimensionTable(DimensionTableDataManager.java:146) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.pinot.core.data.manager.offline.DimensionTableDataManager.loadLookupTable(DimensionTableDataManager.java:127) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.pinot.core.data.manager.offline.DimensionTableDataManager.addSegment(DimensionTableDataManager.java:97) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.pinot.core.data.manager.BaseTableDataManager.addSegment(BaseTableDataManager.java:216) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.pinot.core.data.manager.BaseTableDataManager.addOrReplaceSegment(BaseTableDataManager.java:409) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.pinot.server.starter.helix.HelixInstanceDataManager.addOrReplaceSegment(HelixInstanceDataManager.java:385) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.pinot.server.starter.helix.SegmentOnlineOfflineStateModelFactory$SegmentOnlineOfflineStateModel.onBecomeOnlineFromOffline(SegmentOnlineOfflineStateModelFactory.java:163) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]

Could you please help take a look?

Hi Team, we are building a saas platform, where we need to maintain data for multiple customers. We have more than 100 different tables (all realtime). If we partition on say customer_id column, is it guranteed that created segments will only have data for a single customer_id? or segment can share a few? If it will have data only for single customer_id then is it possible to add different retention period based on value of customer_id? would appreciate any direction here with pointers to sample config etc. Another followup question does retention job will delete segments from deep store as well or just from servers?

Hey guys, I noticed that when using the retention policy for segments we completely lose access to old segments and are no longer queryable anymore, is there any mecanism for us to save on disk resources while being able to access very old segments no long in disk? Like a mechanism to identify that I am querying data way past the retention policy and being able to download again the segments from the deep store and make them available for some more time?

Hi All, I am testing out pinot with one table and schema. ive set

"realtime.segment.flush.threshold.rows":"10000",
"realtime.segment.flush.threshold.time":"2m"

and im pushing messages to kafka. i want the consuming segment to commit and ive tried pushing data with timestamps 5 minutes apart. even pushed messages with timestamp 5 minutes greater than the "segment.creation.time". But the consuming segment still stays open. Please help me understand this behaviour.

I am getting the following error during ingestion.

No enum constant java.util.concurrent.TimeUnit.milliseconds

For this transformation:

{
        "columnName": "device_timestamp_hour_bucket",
        "transformFunction": "dateTrunc('hour', fromDateTime(deviceTimestamp, 'yyyy-MM-dd''T''HH:mm:ss.SSS''Z'), 'milliseconds')"
      },

It works fine when I run it in the query explorer

x-posting 🙇‍♀️

Hi When I use this call - http://localhost:9000/tables/click1Table_OFFLINE/schema, I get {"code":404,"error":"Schema not found for table: click1Table_OFFLINE"}, but when I use http://localhost:9000/tables/click1Table_OFFLINE , I get the response with one of the elements as {"schemaName":"clickSchema","replication":"1","minimizeDataMovement":false} ..... .what could be the reason and how to solve it?

Hello - using Spark to ingest parquet files. What would be a reason for Null / Falsy values like that on all records? Incorrect schema definition?

Hello everyone I am trying to use Presto on EMR as proxy server to connect to pinot i am able to connect to pinot (which is running on EKS) but when i try to run the queries it is going to internal ip of brokers.

select * from test_table;
Query 20230428_130204_00001_43kjv failed: java.net.UnknownHostException: pinot-broker-1.pinot-broker-headless.default-namespace.svc.cluster.local: Name or service not known

how can pass the broker service endpoint to presto pinot connector config. current config of pinot

connector.name=pinot
pinot.controller-rest-service=<pinot-controller-url>
pinot.controller-urls=<pinot-controller-url>
pinot.secure-connection=true
pinot.rest-proxy-service-for-query=true
pinot.allow-multiple-aggregations=true
pinot.use-date-trunc=true
pinot.infer-date-type-in-schema=true
pinot.infer-timestamp-type-in-schema=true
pinot.use-streaming-for-segment-queries=false
pinot.forbid-broker-queries=true

I have question. when I tried to create the table from admin console, schema name comes as empty in the bottom right part of Add Table screen, which is readOnly. How can I add or connect the schema in the AddTable from admin Console? Am i missing any steps for selecting schema ?

After playing around with controller API's, • GET /schemas/{{ schemaName }} returns 404 when schema not found with code and error as resp. • GET /tenants/{{ tenantName }} returns 404 when tenant not found with code and error as resp • GET /tables/{{ tableName}} returns 200 when table not found with an empty response. Why does GET /tables return 200 and empty response when table does not exist ?

Does anyone know under what circumstances an ingest would lead to default / Falsy / Null values? My data is definitely not like this, and I'm sure my schema looks like my data (I can DM if interested).

Hi, I noticed that there is a

Deleted_Segments/

folder for the controller, and it appears that the data in this folder is not actually being deleted and is building up over time. Just wanted to check if there are any known bugs around this? And if it is safe to manually the delete the data in the folder? (note that this is for the controller deepstore using S3). I see a large number of left over segments that were supposed to be deleted due to retention set on the tables.

Hey, I tried to insert data via Pinot DB, and I get this error:

ProcessingException(errorCode:150, message:PQLParsingError:
java.lang.ClassCastException: class org.apache.calcite.sql.SqlInsert cannot be cast to class org.apache.calcite.sql.SqlSelect (org.apache.calcite.sql.SqlInsert and org.apache.calcite.sql.SqlSelect are in unnamed module of loader 'app')
	at org.apache.pinot.sql.parsers.CalciteSqlParser.compileSqlNodeToPinotQuery(CalciteSqlParser.java:357)
	at org.apache.pinot.sql.parsers.CalciteSqlParser.compileToPinotQuery(CalciteSqlParser.java:143)
	at org.apache.pinot.sql.parsers.CalciteSqlCompiler.compileToBrokerRequest(CalciteSqlCompiler.java:35)
	at org.apache.pinot.controller.api.resources.PinotQueryResource.getQueryResponse(PinotQueryResource.java:166))

Is there other way that I can insert data not via kafka or s3?