Apache Pinot #troubleshooting

Alice

11/02/2022, 12:54 AM

Hi team, is there general rule to improve query performance for queries with lookup function in select and where clauses?

Fizza Abid

11/02/2022, 8:45 AM

Hello, I am getting this error. Can someone help?

Copy code

Caused by: java.net.UnknownHostException: pinot-controller
        at java.net.InetAddress$CachedAddresses.get(InetAddress.java:797) ~[?:?]
        at java.net.InetAddress.getAllByName0(InetAddress.java:1509) ~[?:?]
        at java.net.InetAddress.getAllByName(InetAddress.java:1368) ~[?:?]
        at java.net.InetAddress.getAllByName(InetAddress.java:1302) ~[?:?]
        at shaded.org.apache.http.impl.conn.SystemDefaultDnsResolver.resolve(SystemDefaultDnsResolver.java:45) ~[pinot-all-0.10.0-SNAPSHOT-jar-with-dependencies.jar:0.10.0-SNAPSHOT-078c711d35769be2dc4e4b7e235e06744cf0bba7]
        at shaded.org.apache.http.impl.conn.DefaultHttpClientConnectionOperator.connect(DefaultHttpClientConnectionOperator.java:112) ~[pinot-all-0.10.0-SNAPSHOT-jar-with-dependencies.jar:0.10.0-SNAPSHOT-078c711d35769be2dc4e4b7e235e06744cf0bba7]
        at

Warren McKeown

11/02/2022, 12:47 PM

Hi Team, I am having issues with a basic select query in a REALTIME table (SELECT var1, var2 ... FROM table WHERE var1 = 'XXXXXXXXXX' LIMIT 1000000). When I run this query from the PinotUI, the query

timeUsedMs

is 3022Ms but the wall-time between between me running the query and the results being returned in the Pinot UI is over 40s . Some more facts about the query: totalDocs: 366534278 numDocsScanned: 790596 numSegmentsQueried: 107 The table I am querying's segments are split over 20 server instances per pool. (I am using pool-based instance assignment with 2 pools of 20 servers in each) Can anyone explain what might be causing the wall-time to be so much longer than the

timeUsedMs

Mayank

11/02/2022, 3:34 PM

It might be due to the rendering in UI (due to large json response decoding etc). What happens if you call the rest api directly

Priyank Bagrecha

11/02/2022, 10:21 PM

how can i find out instance assignments for a table using replica groups?

suraj sheshadri

11/02/2022, 11:20 PM

We are seeing that even if the servers are down, the query execution still succeeds and returns the output based on incomplete data on other available servers. This makes the system unstable. How can we ensure that the query fails if any server is down and not query on the incomplete dataset. Thank you.

Mamlesh

11/03/2022, 7:24 AM

Hi all, whats the difference between this JMX, "pinot_server_memory_directBufferUsage_Value" and "pinot_server_realtime_offheapMemoryUsed_Value" as in my local cluster where only single realatime table is enabled. Ive checked values of both JMX is exact same. can anyone explain this.

Rohit Anilkumar

11/03/2022, 7:45 AM

Getting this error when trying to set up pinot with s3 as deep store on an EC2 instance. I am using a zookeeper ensemble and passing the zk addresses as pinot.zk.server=zk1port1,zk2port2…. Executing command: StartController -configFileName conf/pinot-controller.conf Caught exception while starting controller, exiting. java.lang.NullPointerException: null at org.apache.pinot.tools.admin.command.StartControllerCommand.getControllerConf(StartControllerCommand.java:207) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033] at org.apache.pinot.tools.admin.command.StartControllerCommand.execute(StartControllerCommand.java:183) [pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033] at org.apache.pinot.tools.Command.call(Command.java:33) [pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033] at org.apache.pinot.tools.Command.call(Command.java:29) [pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033] at picocli.CommandLine.executeUserObject(CommandLine.java:1953) [pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033] at picocli.CommandLine.access$1300(CommandLine.java:145) [pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033] at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2352) [pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033] at picocli.CommandLine$RunLast.handle(CommandLine.java:2346) [pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033] at picocli.CommandLine$RunLast.handle(CommandLine.java:2311) [pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033] at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2179) [pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033] at picocli.CommandLine.execute(CommandLine.java:2078) [pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033] at org.apache.pinot.tools.admin.PinotAdministrator.execute(PinotAdministrator.java:165) [pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033] at org.apache.pinot.tools.admin.PinotAdministrator.main(PinotAdministrator.java:196) [pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]

Anita Jas

11/03/2022, 11:57 AM

Hello ! The schema I am supposed to use has a column name as Count Pinot is defaulting all values for this column as 0 ( and not picking up the actual value from the table ) need some insights here !

Anita Jas

11/03/2022, 11:58 AM

Is there way to define columns that are same as keywords ?

Fizza Abid

11/03/2022, 2:26 PM

Hello, I am getting error, can someone tell why is it coming?

Copy code

trino error: Failed communicating with server: <https://pinot-cluster-broker-1.pinot-cluster-broker-headless.pinot-cluster.svc.cluster.local:8099/debug/routingTable/>

Andy Cooper

11/03/2022, 9:06 PM

We recently decided to upgrade our ingestion jars from

v0.11.0-SNAPSHOT

v0.11.0

. Since we are using Java8 and have a hard dependency on spark2.4 right now, I had to compile. Now, when running the same spark ingestion job that is working on

v0.11.0-SNAPSHOT

, we are receiving the following error when using the new jars:

Copy code

Can't construct a java object for tag:<http://yaml.org|yaml.org>,2002:org.apache.pinot.spi.ingestion.batch.spec.SegmentGenerationJobSpec; exception=Class not found: org.apache.pinot.spi.ingestion.batch.spec.SegmentGenerationJobSpec

We have been looking at this for a while now and I believe we are at the end of the line and out of ideas on where to look next.

Gerrit van Doorn

11/04/2022, 1:24 AM

Hi folks, is the close() method ever called on a PinotFS implementation? For example in an batch ingestion job?

Prakhar Pande

11/04/2022, 6:19 AM

Hi, any idea what could be the cause of the following error in the controller logs?

Copy code

Failed to delete path /pinot-pre-prod/INSTANCES/Controller_pinot-controller-0.pinot-controller-headless.int-pinot.svc.cluster.local_9000/CURRENTSTATES/200002014bd002e! 
org.apache.helix.zookeeper.zkclient.exception.ZkException: org.apache.zookeeper.KeeperException$NotEmptyException: KeeperErrorCode = Directory not empty for /pinot-pre-prod/INSTANCES/Controller_pinot-controller-0.pinot-controller-headless.int-pinot.svc.cluster.local_9000/CURRENTSTATES/200002014bd002e
	at org.apache.helix.zookeeper.zkclient.exception.ZkException.create(ZkException.java:72) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.helix.zookeeper.zkclient.ZkClient.retryUntilConnected(ZkClient.java:2000) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.helix.zookeeper.zkclient.ZkClient.delete(ZkClient.java:2058) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.helix.manager.zk.ZkBaseDataAccessor.remove(ZkBaseDataAccessor.java:727) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.helix.manager.zk.ZKHelixDataAccessor.removeProperty(ZKHelixDataAccessor.java:389) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.helix.manager.zk.ParticipantManager.carryOverPreviousCurrentState(ParticipantManager.java:461) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.helix.manager.zk.ParticipantManager.handleNewSession(ParticipantManager.java:162) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.helix.manager.zk.ZKHelixManager.handleNewSessionAsParticipant(ZKHelixManager.java:1445) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.helix.manager.zk.ZKHelixManager.handleNewSession(ZKHelixManager.java:1392) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.helix.manager.zk.ZKHelixManager.createClient(ZKHelixManager.java:782) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.helix.manager.zk.ZKHelixManager.connect(ZKHelixManager.java:819) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.pinot.controller.BaseControllerStarter.registerAndConnectAsHelixParticipant(BaseControllerStarter.java:592) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.pinot.controller.BaseControllerStarter.setUpPinotController(BaseControllerStarter.java:392) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.pinot.controller.BaseControllerStarter.start(BaseControllerStarter.java:322) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.pinot.tools.service.PinotServiceManager.startController(PinotServiceManager.java:118) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.pinot.tools.service.PinotServiceManager.startRole(PinotServiceManager.java:87) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]
	at org.apache.pinot.tools.admin.command.StartServiceManagerCommand.lambda$startBootstrapServices$0(StartServiceManagerCommand.java:251) ~[pinot-all-0.11.0-jar-with-dependencies.jar:0.11.0-1b4d6b6b0a27422c1552ea1a936ad145056f7033]

Mathieu Alexandre

11/04/2022, 3:05 PM

Hello i'd like to update

segment.realtime.download.url

field i can get with the API endpoint /segments/{tableName}/{segmentName}/metadata. I can't find a documented method to do it, any ideas plz ?

Thomas Steinholz

11/04/2022, 7:02 PM

I am trying to regenerate a table, I have deleted it but cannot regenerate it. The error states to

try deleting the table to remove all metadata associated with it.

- What metadata do I need to manually remove for this to work?

Prakhar Pande

11/05/2022, 9:16 AM

HI everyone, I am running some perf test on pinot, but getting the following error as soon as I start running the tests and getting the following error,

Liveness probe failed: Get "<http://100.64.24.145:9000/health>": context deadline exceeded (Client.Timeout exceeded while awaiting headers)

However when I am disabling the liveness and readiness probe on controller, things are working completely fine. any idea what could be the cause of the problem? Thanks in advance.

Alice

11/06/2022, 8:15 AM

Hi team, could you help take a look at this error? I created a schema and table, with a BYTES datatype column. There’s no error in server log. But there’s an 450 type error when running a simple ‘select * from table’ query. Any idea how to fix it?

Copy code

{
      "name": "pk",
      "dataType": "STRING",
      "maxLength": 1024
    },
    {
      "name": "pk2",
      "dataType": "BYTES",
      "maxLength": 1024
    }

Alice

11/06/2022, 12:37 PM

Hi team, I got another question about upsert feature table. I found a table takes too much heap and I wanted to add a bytes type column for the new primary key in the following way. pk is previous primary key and it’s uuid string, and new_pk is new primary key, and use toUtf8(pk) to fill the value in all existing segments. Will it help save heap in this way？Is there any way to transform previous primary key, and store it in just 16 bytes? And what’re the right steps to make it take effect for the existing segments?

Copy code

"schemaName": "test_bytes_pk",
    {
      "name": "pk",
      "dataType": "STRING",
      "maxLength": 1024
    },{
      "name": "new_pk",
      "dataType": "BYTES",
      "maxLength": 1024
    }

transform:
     {
        "columnName": "new_pk",
        "transformFunction": "toUtf8(pk)"
      }

Lee Wei Hern Jason

11/07/2022, 7:08 AM

Hi Team, I need help on the following: I have a few segments that are in ERROR state. As i traced the logs, it is trying and failed to download the segment (which is present) from its peers instead of deep store. Error log:

Copy code

Caught exception while fetching segment from: <http://ip-10-110-217-232.ap-southeast-1.compute.internal:8097/segments/transportSurgeMirrorMetric_REALTIME/transportSurgeMirrorMetric__0__151__20221027T0840Z> to: /mnt/data/pinot/index/transportSurgeMirrorMetric_REALTIME/transportSurgeMirrorMetric__0__151__20221027T0840Z.tar.gz

This issue arise when i tried to restart all my Pinot’s servers. The segments are present in both servers which im not too sure why is it trying to download from its peer. Thanks in advance 🙏

Thomas Steinholz

11/08/2022, 1:52 PM

Doing a pinot batch ingestion and getting some weird non-sense error…

Copy code

2022/11/08 13:49:04.066 ERROR [LaunchDataIngestionJobCommand] [main] Got exception to kick off standalone data ingestion job - 

java.lang.RuntimeException: Failed to decode table config from JSON - '{"REALTIME":{"tableName":"uplinkpayloadevent_v1_REALTIME", ... <rest of table config> ...

Caused by: 
org.apache.pinot.shaded.com.fasterxml.jackson.databind.exc.MismatchedInputException: Missing required creator property 'tableName' (index 0)

java.lang.RuntimeException: Failed to decode table config from JSON - <entire table config>

Caused by: org.apache.pinot.shaded.com.fasterxml.jackson.databind.exc.MismatchedInputException: Missing required creator property 'tableName' (index 0)

Thomas Steinholz

11/08/2022, 4:38 PM

When creating a realtime table, adding an offline table, then batch ingesting data into the offline table - it seems that the “hybrid” table is unable to aggregate the queries from both sources. Instead, my offline table has results for data before and overlapping with the data collected in the realtime ingestion since it started. While the hybrid table returns only the Realtime query results. I see this note from the advanced data ingestion docs:

Starting from release-0.11.0, Pinot supports uploading offline segments to real-time tables. This is useful when user wants to bootstrap a real-time table with some initial data, or add some offline data to a real-time table without changing the data stream. Note that this is different from the hybrid table setup, and no time boundary is maintained between the offline segments and the real-time segments.

does this mean that I will have to manually query each table (and join them externally, like in trino) in order to query both realtime and offline tables ingested in this way?

Gaurav Sinha

11/09/2022, 9:40 AM

Hi Team, We are doing a POC for using Pinot for our company. We have it setup on K8S on GCP. Today i am observing an error while trying to query through Pinot UI [4 out of 6 segments are showing as unavailable]-

Copy code

[
  {
    "message": "null:\n4 segments [user_impressions_v1_stg__3__0__20221107T1247Z, user_impressions_v1_stg__0__0__20221107T1247Z, user_impressions_v1_stg__1__0__20221107T1247Z, user_impressions_v1_stg__4__0__20221107T1247Z] unavailable",
    "errorCode": 305
  }
]

Can someone help me out on this ? Tried

Rebalance Server

Rebalance Brokers

without any success

Gaurav Sinha

11/09/2022, 9:53 AM

debug API logs -

Copy code

[
    {
        "tableName": "user_impressions_v1_stg_REALTIME",
        "numSegments": 6,
        "numServers": 3,
        "numBrokers": 3,
        "segmentDebugInfos": [
            {
                "segmentName": "user_impressions_v1_stg__0__0__20221107T1247Z",
                "serverState": {
                    "Server_pinot-poc-server-2.pinot-poc-server-headless.pinot-poc.svc.cluster.local_8098": {
                        "idealState": null,
                        "externalView": null,
                        "segmentSize": null,
                        "consumerInfo": null,
                        "errorInfo": null
                    },
                    "Server_pinot-poc-server-1.pinot-poc-server-headless.pinot-poc.svc.cluster.local_8098": {
                        "idealState": null,
                        "externalView": null,
                        "segmentSize": null,
                        "consumerInfo": null,
                        "errorInfo": null
                    }
                }
            },
            {
                "segmentName": "user_impressions_v1_stg__1__0__20221107T1247Z",
                "serverState": {
                    "Server_pinot-poc-server-0.pinot-poc-server-headless.pinot-poc.svc.cluster.local_8098": {
                        "idealState": null,
                        "externalView": null,
                        "segmentSize": null,
                        "consumerInfo": null,
                        "errorInfo": null
                    },
                    "Server_pinot-poc-server-2.pinot-poc-server-headless.pinot-poc.svc.cluster.local_8098": {
                        "idealState": null,
                        "externalView": null,
                        "segmentSize": null,
                        "consumerInfo": null,
                        "errorInfo": null
                    }
                }
            },
            {
                "segmentName": "user_impressions_v1_stg__2__0__20221107T1247Z",
                "serverState": {
                    "Server_pinot-poc-server-0.pinot-poc-server-headless.pinot-poc.svc.cluster.local_8098": {
                        "idealState": null,
                        "externalView": null,
                        "segmentSize": null,
                        "consumerInfo": null,
                        "errorInfo": null
                    },
                    "Server_pinot-poc-server-1.pinot-poc-server-headless.pinot-poc.svc.cluster.local_8098": {
                        "idealState": null,
                        "externalView": null,
                        "segmentSize": null,
                        "consumerInfo": null,
                        "errorInfo": null
                    }
                }
            },
            {
                "segmentName": "user_impressions_v1_stg__3__0__20221107T1247Z",
                "serverState": {
                    "Server_pinot-poc-server-2.pinot-poc-server-headless.pinot-poc.svc.cluster.local_8098": {
                        "idealState": null,
                        "externalView": null,
                        "segmentSize": null,
                        "consumerInfo": null,
                        "errorInfo": null
                    },
                    "Server_pinot-poc-server-1.pinot-poc-server-headless.pinot-poc.svc.cluster.local_8098": {
                        "idealState": null,
                        "externalView": null,
                        "segmentSize": null,
                        "consumerInfo": null,
                        "errorInfo": null
                    }
                }
            },
            {
                "segmentName": "user_impressions_v1_stg__4__0__20221107T1247Z",
                "serverState": {
                    "Server_pinot-poc-server-0.pinot-poc-server-headless.pinot-poc.svc.cluster.local_8098": {
                        "idealState": null,
                        "externalView": null,
                        "segmentSize": null,
                        "consumerInfo": null,
                        "errorInfo": null
                    },
                    "Server_pinot-poc-server-2.pinot-poc-server-headless.pinot-poc.svc.cluster.local_8098": {
                        "idealState": null,
                        "externalView": null,
                        "segmentSize": null,
                        "consumerInfo": null,
                        "errorInfo": null
                    }
                }
            },
            {
                "segmentName": "user_impressions_v1_stg__5__0__20221107T1247Z",
                "serverState": {
                    "Server_pinot-poc-server-0.pinot-poc-server-headless.pinot-poc.svc.cluster.local_8098": {
                        "idealState": null,
                        "externalView": null,
                        "segmentSize": null,
                        "consumerInfo": null,
                        "errorInfo": null
                    },
                    "Server_pinot-poc-server-1.pinot-poc-server-headless.pinot-poc.svc.cluster.local_8098": {
                        "idealState": null,
                        "externalView": null,
                        "segmentSize": null,
                        "consumerInfo": null,
                        "errorInfo": null
                    }
                }
            }
        ],
        "serverDebugInfos": [],
        "brokerDebugInfos": [],
        "tableSize": {
            "reportedSize": "0 bytes",
            "estimatedSize": "0 bytes"
        },
        "ingestionStatus": {
            "ingestionState": "HEALTHY",
            "errorMessage": ""
        }
    }
]

Rohit Anilkumar

11/09/2022, 11:09 AM

[SOLVED] Hey, I am trying to use S3 as deep store and was trying to follow the example given in the documentation. But once the ingestion job finishes, I can see the status of all the segments as BAD. QUERYING THE table gives

Copy code

[
  {
    "message": "null:\n31 segments unavailable, sampling 10: [airlineStats_OFFLINE_16071_16071_0, airlineStats_OFFLINE_16072_16072_0, airlineStats_OFFLINE_16074_16074_0, airlineStats_OFFLINE_16081_16081_0, airlineStats_OFFLINE_16073_16073_0, airlineStats_OFFLINE_16083_16083_0, airlineStats_OFFLINE_16082_16082_0, airlineStats_OFFLINE_16077_16077_0, airlineStats_OFFLINE_16076_16076_0, airlineStats_OFFLINE_16085_16085_0]",
    "errorCode": 305
  }
]

IngestionSpec

Copy code

executionFrameworkSpec:
  name: 'standalone'
  segmentGenerationJobRunnerClassName: 'org.apache.pinot.plugin.ingestion.batch.standalone.SegmentGenerationJobRunner'
  segmentTarPushJobRunnerClassName: 'org.apache.pinot.plugin.ingestion.batch.standalone.SegmentTarPushJobRunner'
  segmentUriPushJobRunnerClassName: 'org.apache.pinot.plugin.ingestion.batch.standalone.SegmentUriPushJobRunner'

jobType: SegmentCreationAndUriPush
inputDirURI: '<s3://bucket/testdata/>'
includeFileNamePattern: 'glob:**/*.avro'
outputDirURI: '<s3://dev-airflow-artifacts/controllerData/output/airlineStats/segments>'

overwriteOutput: true


pinotFSSpecs:

  - scheme: file
    className: org.apache.pinot.spi.filesystem.LocalPinotFS


  - scheme: s3
    className: org.apache.pinot.plugin.filesystem.S3PinotFS
    configs:
      region: 'us-east-1'

recordReaderSpec:
  dataFormat: 'avro'
  className: 'org.apache.pinot.plugin.inputformat.avro.AvroRecordReader'

tableSpec:

  tableName: 'airlineStats'
  schemaURI: '<http://172.31.11.119:9000/tables/airlineStats/schema>'
  tableConfigURI: '<http://172.31.11.119:9000/tables/airlineStats>'

pinotClusterSpecs:
  - controllerURI: '<http://172.31.14.189:9000>'

pushJobSpec:

  pushAttempts: 2
  pushRetryIntervalMillis: 1000
  segmentUriPrefix: '<s3://bucket>'
  segmentUriSuffix: ''

Sukesh Boggavarapu

11/09/2022, 8:35 PM

I am trying to run a GAPFILL function

Sukesh Boggavarapu

11/09/2022, 8:35 PM

Copy code

SELECT GAPFILL(dt, '1:DAYS:SIMPLE_DATE_FORMAT:yyyy-MM-dd', '2022-09-01', '2022-10-01',
      '1:DAYS', FILL(clicks, 'FILL_DEFAULT_VALUE')) as dt,
      clicks
      FROM (
		select dt,count(*) as clicks from enriched_click where userId=2214923 and dt>='2022-09-01' and dt<='2022-10-01' group by 1 order by 1 desc limit 100)

Sukesh Boggavarapu

11/09/2022, 8:35 PM

but getting a 500 error

Copy code

ProcessingException(errorCode:450, message:InternalError:
java.io.IOException: Failed : HTTP error code : 500
	at org.apache.pinot.controller.api.resources.PinotQueryResource.sendPostRaw(PinotQueryResource.java:317)
	at org.apache.pinot.controller.api.resources.PinotQueryResource.sendRequestRaw(PinotQueryResource.java:355)
	at org.apache.pinot.controller.api.resources.PinotQueryResource.sendRequestToBroker(PinotQueryResource.java:254)
	at org.apache.pinot.controller.api.resources.PinotQueryResource.getQueryResponse(PinotQueryResource.java:225))

Sukesh Boggavarapu

11/09/2022, 8:35 PM

What could be wrong in the query?

Jatin Kumar

11/10/2022, 2:27 AM

Hello, We are querying pinot from superset to build chart using this query but it is failing with below error.

Copy code

SELECT date_trunc('day', CAST(week AS TIMESTAMP)) AS week,
case
when SUM(sessions) = 0 then 0
else (SUM(order_placed)*100.00)/SUM(sessions)
end AS "overall conversion"
FROM
virtual_table
GROUP BY date_trunc('day', CAST(week AS TIMESTAMP))
ORDER BY "overall conversion" DESC
LIMIT 1000;

Seems like SUM is not allowed in case statement , is there any alternative?