search

apache / pinot

Apache Pinot - A realtime distributed OLAP datastore
https://pinot.apache.org/
Apache License 2.0
5.31k stars 1.24k forks source link

How to set DATETIMECONVERT granularity of MONTHS, YEARS #6168

Open rec7y33 opened 3 years ago

rec7y33 commented 3 years ago

Hello, I am using PQL on the incubator pinot, and was trying to query data with datetime transformation. I want to set granularity of years:

DATETIMECONVERT(EventStartTime, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm:ss', '1:YEARS')

but I got this error:

[ { "errorCode": 200, "message": "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:201)\n\tat org.apache.pinot.core.operator.transform.TransformOperator.<init>(TransformOperator.java:56)\n\tat org.apache.pinot.core.plan.TransformPlanNode.run(TransformPlanNode.java:52)\n\tat org.apache.pinot.core.plan.AggregationGroupByOrderByPlanNode.run(AggregationGroupByOrderByPlanNode.java:98)\n\tat org.apache.pinot.core.plan.AggregationGroupByOrderByPlanNode.run(AggregationGroupByOrderByPlanNode.java:41)\n\tat org.apache.pinot.core.plan.CombinePlanNode.run(CombinePlanNode.java:94)\n\tat org.apache.pinot.core.plan.InstanceResponsePlanNode.run(InstanceResponsePlanNode.java:33)\n\tat org.apache.pinot.core.plan.GlobalPlanImplV0.execute(GlobalPlanImplV0.java:45)\n\tat org.apache.pinot.core.query.executor.ServerQueryExecutorV1Impl.processQuery(ServerQueryExecutorV1Impl.java:234)\n\tat org.apache.pinot.core.query.executor.QueryExecutor.processQuery(QueryExecutor.java:60)\n\tat org.apache.pinot.core.query.scheduler.QueryScheduler.processQueryAndSerialize(QueryScheduler.java:155)\n\tat org.apache.pinot.core.query.scheduler.QueryScheduler.lambda$createQueryFutureTask$0(QueryScheduler.java:139)\n\tat java.util.concurrent.FutureTask.run(FutureTask.java:266)\n\tat java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)" } ]

What should I do? Thanks

Jackie-Jiang commented 3 years ago

@rec7y33 I think the problem is that YEARS is not a valid TimeUnit. The highest granularity for TimeUnit is DAYS

rec7y33 commented 3 years ago

I need to aggregate the data by months and years, what can I do to achieve that?