Apache Pinot Daily Email Digest (2020-11-10)

[Pinot Slack Email Digest <sn...@apache.org>]

### _#general_

  
 **@mohammedgalalen056:** Is there is any way to convert sql table schema to
pinot table schema?  
**@fx19880617:** @elon.azoulay may have some idea :slightly_smiling_face:  
**@elon.azoulay:** Yes, we are open sourcing this. In prestosql you can create
a pinot table directly.  
**@mohammedgalalen056:** Awesome  
 **@chundong.wang:** Just reported , `NullPointerException` is thrown when
query with aggregation on top of groovy functions. Should we expect
aggregation to work on top of groovy as it’s treated as transform function?  
**@chundong.wang:** From the code, the NPE is thrown at,
```operatorLatch.countDown();``` Which doesn’t make a lot sense to me as it
should never be NULL…  
**@chinmay.cerebro:** might be better to continue in troubleshooting channel ?
@npawar @jackie.jxt FYI  
**@npawar:** this works for me: ```select DestState,
avg(groovy('{"returnType":"DOUBLE","isSingleValue":true}', 'arg0 > arg1 ?
arg0.toDouble() : arg1.toDouble()', ActualElapsedTime, AirTime)) as theAvg
from airlineStats where ActualElapsedTime>0 and AirTime>0 group by DestState
limit 10```  
**@npawar:** im on master though  
**@npawar:** nothing wrong with the query should’ve worked. let me investigate
more  
**@npawar:** for 0.5.0 looks like the line throwing the NPE is
`_indexedTable.finish(false);`  
**@npawar:** any exceptions before this one?  
**@chundong.wang:** I don’t see other exceptions from the query response.  
**@npawar:** how about in the server logs?  
**@chundong.wang:** let me rerun the query and see if I can find relevant log
on brokers.  
**@npawar:** please check server also. this exception is from the server code  
**@chundong.wang:** server you mean the nodes, right?  
**@npawar:** i mean pinot-server logs. Check that along with pinot-broker  
**@chundong.wang:** I found below error on server,
```java.lang.ClassCastException: java.lang.Long cannot be cast to
java.lang.Double at
org.apache.pinot.core.operator.transform.function.GroovyTransformFunction.transformToDoubleValuesSV(GroovyTransformFunction.java:280)
at
org.apache.pinot.core.operator.docvalsets.TransformBlockValSet.getDoubleValuesSV(TransformBlockValSet.java:85)
at
org.apache.pinot.core.query.aggregation.function.AvgAggregationFunction.aggregateGroupBySV(AvgAggregationFunction.java:103)
at
org.apache.pinot.core.query.aggregation.groupby.DefaultGroupByExecutor.aggregate(DefaultGroupByExecutor.java:143)
at
org.apache.pinot.core.query.aggregation.groupby.DefaultGroupByExecutor.process(DefaultGroupByExecutor.java:130)
at
org.apache.pinot.core.operator.query.AggregationGroupByOrderByOperator.getNextBlock(AggregationGroupByOrderByOperator.java:106)
at
org.apache.pinot.core.operator.query.AggregationGroupByOrderByOperator.getNextBlock(AggregationGroupByOrderByOperator.java:38)
at org.apache.pinot.core.operator.BaseOperator.nextBlock(BaseOperator.java:49)
at
org.apache.pinot.core.operator.combine.GroupByOrderByCombineOperator$1.runJob(GroupByOrderByCombineOperator.java:137)
at org.apache.pinot.core.util.trace.TraceRunnable.run(TraceRunnable.java:40)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
~[?:1.8.0_265] at java.util.concurrent.FutureTask.run(FutureTask.java:266)
~[?:1.8.0_265] at
java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
~[?:1.8.0_265] at
shaded.com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:111)
at
shaded.com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:58)
at
shaded.com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:75)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
~[?:1.8.0_265] at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
~[?:1.8.0_265] at java.lang.Thread.run(Thread.java:748) [?:1.8.0_265]```  
**@chundong.wang:** Why is it casting things to double?  
**@npawar:** 2 reasons: 1\. In your groovy query, i see return type double. So
the final result returned by your script should be double. For instance, in
the sample query I shared, my arg0 and arg1 were INT, so I used `toDouble()`
on them 2\. AVG, SUM functions expect double (i think)  
**@npawar:** it could be failing for you in one of these 2 palces  
**@chundong.wang:** AH  
**@chundong.wang:** let me try converting everything to double first.
:slightly_smiling_face:  
**@chundong.wang:** hmm… now i’m getting below error with `toDouble()` i
think. Let me play around with it a bit. Thanks for the help!!
```java.lang.ClassCastException: java.math.BigDecimal cannot be cast to
java.lang.Double at
org.apache.pinot.core.operator.transform.function.GroovyTransformFunction.transformToDoubleValuesSV(GroovyTransformFunction.java:280)
at
org.apache.pinot.core.operator.docvalsets.TransformBlockValSet.getDoubleValuesSV(TransformBlockValSet.java:85)
at
org.apache.pinot.core.query.aggregation.function.AvgAggregationFunction.aggregateGroupBySV(AvgAggregationFunction.java:103)```  
**@chundong.wang:** I eventually get it to work. So to sum up, 1\.
Avg/Sum/PercentileTDigest## would all require `double` so need to convert the
all possible return values to `double` ; 2\. I have a number literal, `0.0`,
which I thought would make it double but actually is bigDecimal. So instead I
used `0d` and second exception is gone.  
**@npawar:** :ok_hand:  
**@chundong.wang:** Much appreciated, @npawar!  
**@chundong.wang:** :pray:  
 **@807661679:** @807661679 has joined the channel  
 **@tingchen:** Can Pinot make the time boundary offset configurable for
hybrid tables? Right now it is hardcoded to be 1 day: . It does not work in
all environments: It is much better to leave this field to be a configurable
parameter for Pinot broker. File a issue for this:  

### _#random_

  
 **@807661679:** @807661679 has joined the channel  

###  _#troubleshooting_

  
 **@elon.azoulay:** Hi, I tried to create a table with a boolean type when I
look at the schema it's saved as a string: ```{ "schemaName" : "myTable3",
"dimensionFieldSpecs" : [ { "name" : "col1", "dataType" : "LONG",
"defaultNullValue" : 0 }, { "name": "col2", "dataType": "BOOLEAN", }],
"metricFieldSpecs" : [ { "name" : "m1", "dataType" : "LONG" } ],
"dateTimeFieldSpecs" : [ { "name" : "ts", "dataType" : "LONG", "format" :
"1:MILLISECONDS:EPOCH", "granularity" : "1:SECONDS" } ] }``` Becomes this in
pinot: ```{ "schemaName": "myTable3", "dimensionFieldSpecs": [ { "name":
"col1", "dataType": "LONG", "defaultNullValue": 0 }, { "name": "col2",
"dataType": "STRING" } ], "metricFieldSpecs": [ { "name": "m1", "dataType":
"LONG" } ], "dateTimeFieldSpecs": [ { "name": "ts", "dataType": "LONG",
"format": "1:MILLISECONDS:EPOCH", "granularity": "1:SECONDS" } ] }```  
 **@jackie.jxt:** @elon.azoulay Inside Pinot, `BOOLEAN` is stored and handled
as `STRING`  
**@elon.azoulay:** Thanks! I remember seeing that in the docs, is there a way
to annotate a field so that it's returned as a boolean?  
**@jackie.jxt:** If the input data is boolean, the response should have
`"true"` / `"false"`  
**@elon.azoulay:** So response will be string even for a boolean type in the
select list? i.e. pinot boolean type is only used internally?  
 **@jackie.jxt:** For `BOOLEAN`, you might want to put a `defaultNullValue`,
or it will use the default `null`  

###  _#time-based-segment-pruner_

  
 **@jackie.jxt:** @jackie.jxt has joined the channel  
 **@snlee:** @jackie.jxt  
\--------------------------------------------------------------------- To
unsubscribe, e-mail: dev-unsubscribe@pinot.apache.org For additional commands,
e-mail: dev-help@pinot.apache.org