https://pinot.apache.org/ logo
Join Slack
Powered by
I have a question around `DISTINCTCOUNT`, I have a...
# troubleshooting
k
I have a question around 
DISTINCTCOUNT
, I have a use case where I want a distinct count based on a column, which has very high cardinality. 900million rows out of which 890million might be distinct. 
DISTINCTCOUNT
function fails because of OOM. I see that 
DISTINCTCOUNT
is implemented using a HashSet which will load all the distinct values in memory. I was thinking if a bloom filter with low false positivity rate may help here. Thoughts?
p
r
please try the 
distinctCountThetaSketch
or 
distinctCountHLL
functions. They each have parameters to trade resource consumption for accuracy
šŸ™Œ 1
šŸ‘ 1
k
Great - thanks @Peter Pringle @Richard Startin for your help.
I looked at these and they work way better for single column distinct. In my use case it would be best to use an 
id
and 
date
column to count the distinct, which is not supported by HLL or sketches. Is there any other way I could do that? I am considering following options: ā€¢ combine id and date into a single column just used for distinct queries. ā€¢ using a partial upsert config with an additional column 
count
which can be incremented whenever there is a duplicate event and then just count the rows will give me distinct rows and for total rows I could SUM() on 
count
column. Any other ideas?
r
option 1 is better
thankyou 1
we can add multi-arg capabilities to hll/thetasketch - could you create an issue to register interest?
āœ… 1
k
Created a quick one https://github.com/apache/pinot/issues/8225, let me know if this needs more details.
For option2, is it not going to work at all?
r
option 1 will perform better
k
yea, the only limitation is that with HLL or sketches we will have approximate counts, with option 2 we can just count and it will be eventually exact?
r
by all means, try it, but upsert has a large footprint on heap, proportional to the cardinality of the primary keys
it also means that every insert is really an update - the pre-existing key needs to be located, which in general will be acceptable, but if you have any very common keys it will create contention
k
thanks for the input. I will try and see how it performs. At this point I am not able to get basic upsert working. It is inserting another row.
2 Views
Open in Slack
PreviousNext
Powered by