Apache Pinot #troubleshooting

Join Slack

Neha Pawar

04/30/2020, 12:21 AM

i believe it's the use of single quotes vs double quotes to escape

Neha Pawar

04/30/2020, 12:22 AM

i get an exception if I use single quotes:

Copy code

QueryExecutionError:\norg.apache.pinot.core.query.exception.BadQueryRequestException: Caught exception while initializing transform function: dateTimeConvert\n\tat org.apache.pinot.core.operator.transform.function.TransformFunctionFactory.get(TransformFunctionFactory.java:131)\n\tat org.apache.pinot.core.operator.transform.TransformOperator.<init>(TransformOperator.java:56)\n\tat org.apache.pinot.core.plan.TransformPlanNode.run(TransformPlanNode.java:120)\n\tat org.apache.pinot.core.plan.AggregationGroupByOrderByPlanNode.run(AggregationGroupByOrderByPlanNode.java:103)\n\tat org.apache.pinot.core.plan.AggregationGroupByOrderByPlanNode.run(AggregationGroupByOrderByPlanNode.java:46)\n\tat org.apache.pinot.core.plan.CombinePlanNode.run(CombinePlanNode.java:88)\n\tat org.apache.pinot.core.plan.InstanceResponsePlanNode.run(InstanceResponsePlanNode.java:38)\n\tat org.apache.pinot.core.plan.GlobalPlanImplV0.execute(GlobalPlanImplV0.java:45)\n\tat org.apache.pinot.core.query.executor.ServerQueryExecutorV1Impl.processQuery(ServerQueryExecutorV1Impl.java:220)\n\tat org.apache.pinot.core.query.scheduler.QueryScheduler.processQueryAndSerialize(QueryScheduler.java:152)\n\tat org.apache.pinot.core.query.scheduler.QueryScheduler.lambda$createQueryFutureTask$0(QueryScheduler.java:136)\n\tat java.util.concurrent.FutureTask.run(FutureTask.java:266)\n\tat java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)\n\tat com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:111)"

Dan Hill

04/30/2020, 4:09 AM

Switching to double quotes, I get the following error

Copy code

java.lang.NullPointerException: null
	at org.apache.pinot.core.common.datatable.DataTableImplV2.positionCursorInVariableBuffer(DataTableImplV2.java:453) ~[pinot-all-0.3.0-SNAPSHOT-jar-with-dependencies.jar:0.3.0-SNAPSHOT-3cb4a25fbad543ed552443e8d884a489079dc003]
	at org.apache.pinot.core.common.datatable.DataTableImplV2.getObject(DataTableImplV2.java:393) ~[pinot-all-0.3.0-SNAPSHOT-jar-with-dependencies.jar:0.3.0-SNAPSHOT-3cb4a25fbad543ed552443e8d884a489079dc003]
	at org.apache.pinot.core.query.reduce.GroupByDataTableReducer.getIndexedTable(GroupByDataTableReducer.java:355) ~[pinot-all-0.3.0-SNAPSHOT-jar-with-dependencies.jar:0.3.0-SNAPSHOT-3cb4a25fbad543ed552443e8d884a489079dc003]
	at org.apache.pinot.core.query.reduce.GroupByDataTableReducer.setSQLGroupByInResultTable(GroupByDataTableReducer.java:195) ~[pinot-all-0.3.0-SNAPSHOT-jar-with-dependencies.jar:0.3.0-SNAPSHOT-3cb4a25fbad543ed552443e8d884a489079dc003]
	at org.apache.pinot.core.query.reduce.GroupByDataTableReducer.reduceAndSetResults(GroupByDataTableReducer.java:142) ~[pinot-all-0.3.0-SNAPSHOT-jar-with-dependencies.jar:0.3.0-SNAPSHOT-3cb4a25fbad543ed552443e8d884a489079dc003]
	at org.apache.pinot.core.query.reduce.BrokerReduceService.reduceOnDataTable(BrokerReduceService.java:186) ~[pinot-all-0.3.0-SNAPSHOT-jar-with-dependencies.jar:0.3.0-SNAPSHOT-3cb4a25fbad543ed552443e8d884a489079dc003]
	at org.apache.pinot.broker.requesthandler.SingleConnectionBrokerRequestHandler.processBrokerRequest(SingleConnectionBrokerRequestHandler.java:107) ~[pinot-all-0.3.0-SNAPSHOT-jar-with-dependencies.jar:0.3.0-SNAPSHOT-3cb4a25fbad543ed552443e8d884a489079dc003]
	at org.apache.pinot.broker.requesthandler.BaseBrokerRequestHandler.handleRequest(BaseBrokerRequestHandler.java:319) ~[pinot-all-0.3.0-SNAPSHOT-jar-with-dependencies.jar:0.3.0-SNAPSHOT-3cb4a25fbad543ed552443e8d884a489079dc003]
	at org.apache.pinot.broker.api.resources.PinotClientRequest.processSqlQueryPost(PinotClientRequest.java:146) ~[pinot-all-0.3.0-SNAPSHOT-jar-with-dependencies.jar:0.3.0-SNAPSHOT-3cb4a25fbad543ed552443e8d884a489079dc003]

Dan Hill

04/30/2020, 4:10 AM

Here's the query:

Copy code

select dateTimeConvert("timestamp", '1:MILLISECONDS:EPOCH', '1:HOURS:EPOCH', '1:HOURS') as mydate, sum(impressions) from events group by mydate

Dan Hill

04/30/2020, 4:10 AM

In schema...

Copy code

{
      "name": "impressions",
      "dataType": "LONG"
    },

Dan Hill

04/30/2020, 4:11 AM

Copy code

"timeFieldSpec": {
    "incomingGranularitySpec": {
      "name": "timestamp",
      "dataType": "LONG",
      "timeFormat" : "EPOCH",
      "timeType": "MILLISECONDS"
    }
  }

Neha Pawar

04/30/2020, 4:11 AM

are you using the pinot release, or do you have the latest code?

and was this working fine before?

Dan Hill

04/30/2020, 4:12 AM

I downloaded the code about a month ago

Dan Hill

04/30/2020, 4:12 AM

I'll pull and build.

Dan Hill

04/30/2020, 4:26 AM

Sweet! Yea, the latest build fixed this.

Dan Hill

04/30/2020, 4:29 AM

I can get the query to work in the Pinot Data Explorer. Presto still shows the

Function datetrunc not registered

error (same for

dateTimeConvert

Neha Pawar

04/30/2020, 4:48 AM

@Xiang Fu any ideas why the case ^

Kishore G

04/30/2020, 4:53 AM

I think there is a translation from datetrunc

Kishore G

04/30/2020, 4:58 AM

whats the prestoudf

Kishore G

04/30/2020, 4:59 AM

sorry query

Dan Hill

04/30/2020, 5:26 AM

Copy code

SELECT DATETRUNC('day', "timestamp") as mydate, SUM(cost_usd_micros) FROM pinot.default.events_testing GROUP BY mydate;

Kishore G

04/30/2020, 5:28 AM

output of explain?

Kishore G

04/30/2020, 5:31 AM

Copy code

select dateTimeConvert('timestamp', '1:MILLISECONDS:EPOCH', '1:DAYS:SIMPLE_DATE_FORMAT:yyyyMMdd tz(America/Los_Angeles)', '1:DAYS') as mydate, sum(impressions) from events group by mydate

Dan Hill

04/30/2020, 5:59 AM

I just get

Function datetimeconvert not registered

Dan Hill

04/30/2020, 5:59 AM

When I run that in Presto

Dan Hill

04/30/2020, 5:59 AM

No explain results.

Neelakanthanvinod Thanappan

04/30/2020, 6:42 AM

Hi All, We are using superset pinot Docker image. The new database addition in Superset with Pinot was successful. But while executing queries on pinot its getting timed out.

Kishore G

04/30/2020, 6:42 AM

whats the query

Xiang Fu

04/30/2020, 6:05 PM

there is a config

pinot.use-date-trunc=true

you need to set in

pinot.properties

to enable that function. @Dan Hill

👍 1

Dan Hill

04/30/2020, 7:16 PM

Cool. Thanks!

Dan Hill

05/01/2020, 12:23 AM

@Xiang Fu - I added that to my pinot.properties and I still get the same error.

Function datetrunc not registered

Dan Hill

05/01/2020, 12:24 AM

I think my config is setup correctly.

pinot.allow-multiple-aggregations=true

works.

Xiang Fu

05/01/2020, 12:35 AM

try

DATE_TRUNC

Xiang Fu

05/01/2020, 12:35 AM

in your function name?

Dan Hill

05/01/2020, 12:42 AM

Cool, that helps. Now I get...

Copy code

Unexpected parameters (varchar(4), bigint) for function date_trunc. Expected: date_trunc(varchar(x), date) , date_trunc(varchar(x), time) , date_trunc(varchar(x), time with time zone) , date_trunc(varchar(x), timestamp) , date_trunc(varchar(x), timestamp with time zone)

if I try to cast my milliseconds timestamp column to timestamp, I get...

Copy code

Cannot cast bigint to timestamp

I see other pinot presto options related to detecting timestamp columns. Should I use those options?