#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
@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:
#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: [email protected] For additional commands, e-mail: [email protected]
