https://linen.dev logo
Join Slack
Powered by
This message was deleted.
# general
s
This message was deleted.
r
for example, given this query to what pages given user ID 1107243346 might have visited
Copy code
select * from (
  SELECT 
    path, THETA_SKETCH_ESTIMATE(
      THETA_SKETCH_INTERSECT(DS_THETA(ds_mm), DS_THETA(1107243346))
    ) as path_visited
  FROM events
  where __time >= ...
  group by 1 
) sub where sub.path_visited = 1
can I trust that I can run another (heavy) query to check the information (to test if the user really visited some path) but I can skip any check if no results were found ?
I noticed that THETA_SKETCH is somewhat bigger than just HLL, but I could not figure out how to run this query with HLL Also, side note: if I change the path_visited into HAVING clause the Calcite cannot make a plan for it
v
Great question! My guess will be "theta does not provide a guarantee eater way" but I will see what someone more knowledgeable says
r
https://chat.openai.com/chat think it can return false negatives, but at first it get bloom filter wrong, so do not trust it
did some testing, it does 😕 Druid bloom-filter exists only at query time, it's not possible to use on roll-ups ? Doind on query-time scanning millions of events I think it's better to just run the real query
v
what is ds_mm in this case?
one approach is to use THETA_SKETCH_ESTIMATE_WITH_ERROR_BOUNDS. This should get you upper and lower bound of the estimate
if you expect the estimate to be 1 nad the bounds are 0 and 2 for instance then the sketch could get you false negatives
r
ds_mm was created by 'mm' (user-id)
Copy code
{
  "type": "thetaSketch",
  "name": "ds_mm",
  "fieldName": "mm",
  "size": 16384,
  "shouldFinalize": true,
  "isInputThetaSketch": false,
  "errorBoundsStdDev": null
}
I tried some values, if I choose users from a small combination, it works, but I choose a userid from a big pop, It fails
I was not able to create a working THETA_SKETCH_ESTIMATE_WITH_ERROR_BOUNDS query
if I try just select THETA_SKETCH_ESTIMATE_WITH_ERROR_BOUNDS(ds_mm , 0) it says the ds_mm is not group'ed by If i add to group by, it fails as COMPLEX grouping type is not enabled by default if I try APPROX_COUNT_DISTINCT_DS_THETA(THETA_SKETCH_ESTIMATE_WITH_ERROR_BOUNDS(ds_mm , 0)) calcite fails to create a plan
for my use case, this is kinda fixed right now (see https://apachedruidworkspace.slack.com/archives/C0303FDCZEZ/p1670621113409749?thread_ts=1670621045.271699&cid=C0303FDCZEZ) I still left this datasource enabled becase it's really fast, I think I will add some dashboards with this data (maybe make it more granullar than DAY) I had this use-case where I need to check if a use is still active (eg: use the app in the last N days) before sending an external event I set up a redis where I can keep track of last iteration for a few million users, but when I get a 'cache-miss' I don't know if the user is not active or if the cache was corrupted (I have a checksum bc I use another redis where i keep the most frequent and small values in form of a int, eg: "Samsung" is always encoded to 222, but redis can crash, etc) so I still need to query druid a few times per second, most those requests would never return a row bc the user was inactive for a month
it was taking so much CPU (because it was converting all the strings to INT then making the matching) that I was looking for another ways or even another database for this
2 Views
Open in Slack
PreviousNext
Powered by