Hello I am trying to query my pinot table by a BYTE type field. I am setting the query parameter in a PreparedStatement . I know internally Pinot store bytes as hex string but when I try to query it using the absolute value via code I get the following error

Caused by: org.apache.pinot.client.PinotClientException: Query had processing exceptions: 
[{"message":"QueryExecutionError:\norg.apache.pinot.spi.exception.BadQueryRequestException: java.lang.IllegalArgumentException: Cannot convert value: '0xac384c53f03855fa1b3616052f8ba32c6c2a2fec' to type: BYTES\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner.convertValue(ColumnValueSegmentPruner.java:469)\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner$ValueCache$CachedValue.ensureDataType(ColumnValueSegmentPruner.java:526)\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner$ValueCache.get(ColumnValueSegmentPruner.java:493)\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner.pruneEqPredicate(ColumnValueSegmentPruner.java:287)\n...\nCaused by: java.lang.IllegalArgumentException: Cannot convert value: 'HIQQEXWKW53RKN4W6XXC4Q232SYNZ3SZANVZZSUME5B5PRGXL663UAQA' to type: BYTES\n\tat org.apache.pinot.spi.data.FieldSpec$DataType.convertInternal(FieldSpec.java:522)\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner.convertValue(ColumnValueSegmentPruner.java:467)\n\t... 20 more","errorCode":200},{"message":"QueryExecutionError:\norg.apache.pinot.spi.exception.BadQueryRequestException: java.lang.IllegalArgumentException: Cannot convert value: 'HIQQEXWKW53RKN4W6XXC4Q232SYNZ3SZANVZZSUME5B5PRGXL663UAQA' to type: BYTES\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner.convertValue(ColumnValueSegmentPruner.java:469)\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner$ValueCache$CachedValue.ensureDataType(ColumnValueSegmentPruner.java:526)\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner$ValueCache.get(ColumnValueSegmentPruner.java:493)\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner.pruneEqPredicate(ColumnValueSegmentPruner.java:287)\n...\nCaused by: java.lang.IllegalArgumentException: Cannot convert value: 'HIQQEXWKW53RKN4W6XXC4Q232SYNZ3SZANVZZSUME5B5PRGXL663UAQA' to type: BYTES\n\tat org.apache.pinot.spi.data.FieldSpec$DataType.convertInternal(FieldSpec.java:522)\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner.convertValue(ColumnValueSegmentPruner.java:467)\n\t... 20 more","errorCode":200},{"message":"QueryExecutionError:\norg.apache.pinot.spi.exception.BadQueryRequestException: java.lang.IllegalArgumentException: Cannot convert value: 'HIQQEXWKW53RKN4W6XXC4Q232SYNZ3SZANVZZSUME5B5PRGXL663UAQA' to type: BYTES\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner.convertValue(ColumnValueSegmentPruner.java:469)\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner$ValueCache$CachedValue.ensureDataType(ColumnValueSegmentPruner.java:526)\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner$ValueCache.get(ColumnValueSegmentPruner.java:493)\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner.pruneEqPredicate(ColumnValueSegmentPruner.java:287)\n...\nCaused by: java.lang.IllegalArgumentException: Cannot convert value: 'HIQQEXWKW53RKN4W6XXC4Q232SYNZ3SZANVZZSUME5B5PRGXL663UAQA' to type: BYTES\n\tat org.apache.pinot.spi.data.FieldSpec$DataType.convertInternal(FieldSpec.java:522)\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner.convertValue(ColumnValueSegmentPruner.java:467)\n\t... 20 more","errorCode":200},{"message":"QueryExecutionError:\norg.apache.pinot.spi.exception.BadQueryRequestException: java.lang.IllegalArgumentException: Cannot convert value: 'HIQQEXWKW53RKN4W6XXC4Q232SYNZ3SZANVZZSUME5B5PRGXL663UAQA' to type: BYTES\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner.convertValue(ColumnValueSegmentPruner.java:469)\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner$ValueCache$CachedValue.ensureDataType(ColumnValueSegmentPruner.java:526)\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner$ValueCache.get(ColumnValueSegmentPruner.java:493)\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner.pruneEqPredicate(ColumnValueSegmentPruner.java:287)\n...\nCaused by: java.lang.IllegalArgumentException: Cannot convert value: 'HIQQEXWKW53RKN4W6XXC4Q232SYNZ3SZANVZZSUME5B5PRGXL663UAQA' to type: BYTES\n\tat org.apache.pinot.spi.data.FieldSpec$DataType.convertInternal(FieldSpec.java:522)\n\tat org.apache.pinot.core.query.pruner.ColumnValueSegmentPruner.convertValue(ColumnValueSegmentPruner.java:467)\n\t... 20 more","errorCode":200}]

I don't see a way to set the value as a byte in the prepared statement.Any thoughts?

Pinot does not store BYTES internally as hex strings, it just returns it as hex string from query

Ahhh sorry I misunderstood. So actually the field is BYTES in the pinot table and when I am creating my query something like

select * from entity where xyzcolumn ='0xac384c53f03855fa1b3616052f8ba32c6c2a2fec'

where xyzcolumn is a byte column and I provide the hex string as the query parameter I get the above exception. DO I need to convert that to bytes or something before querying?

Can you try to remove the

0x

in the front?

0x doesn't really matter a simple string is also not working. We decided to go with bytes , because we thought that data will be stored more efficiently that way compared to string or base64. Are we not really able to query a byte column? I was able to do it through the controller ui but not via code.

You should be able to query bytes column. But you are using this data type for the wrong reasons. You can still use string data type, Pinot will store data efficiently.

Ok got it. We can change that and try it. So is there a good practice as to when we should be using bytes?

One example is like a serialized data structure such as HLL, TDigest. Or other binary blobs.

ok is there a recommendation for public keys?