This message was deleted.
# generals
Slackbot
03/30/2023, 12:47 PMThis message was deleted.
v
Vijay Narayanan
03/30/2023, 1:08 PMcount distinct by default will get you approximate count. So there will some variation in results. For the sql query I would use approx_count_distinct_ds_theta ....this will give exact count for values less than the sketch size (default of 32k).
d
David McHealy
03/30/2023, 1:10 PMDo you recommend ds_theta over ds_hll for this?
g
Giwrgos Gkοlfopoulos
03/30/2023, 1:13 PMHello, let me to explain you.
Sql query give correct results because it select all data (regardless of segmentation) and find the distinct customer id / version.
Pydruid query not give correct results because it finds distinct customer_id / version / segment(month)
so if a version exists in 2 months, even if it is the same customer, it will bring me these records as well.
for example:
sql returns:
Version Number_of_Unique_Borrowers
60 108028
61 107814
62 44475
but Pydruid returns:
Version Number of Unique Borrowers
60 122353
61 119460
62 44475
v
Vijay Narayanan
03/30/2023, 1:41 PMthis is due to the approximate count distinct
Vijay Narayanan
03/30/2023, 1:41 PMpydruid will not group by month
🙌 1
g
Giwrgos Gkοlfopoulos
03/30/2023, 1:42 PMhmm ok, so how pydruid query should be to give the same results with sql?
v
Vijay Narayanan
03/30/2023, 1:43 PM@David McHealy the main difference between hll and theta is that theta will give you exact counts upto the sketch size. this is useful in many scenarios. HLL does have a lower memory fottprint however
Vijay Narayanan
03/30/2023, 1:44 PM@Giwrgos Gkοlfopoulos you will have to turn off approx count distinct but that will affect performance a lot.
Vijay Narayanan
03/30/2023, 1:46 PMI don't know if pydruid can handle theta sketch (I think it generates native query directly). Even if you use theta sketch it will still be approximate beyond the size of the sketch
Vijay Narayanan
03/30/2023, 1:59 PMI will take a look at this and get back
🙌 1
Vijay Narayanan
03/30/2023, 2:10 PMtheta sketches are supported in pydruid https://github.com/druid-io/pydruid
Vijay Narayanan
03/30/2023, 2:21 PMhere is a pydruid with theta sketch
Copy code
q = query.groupby(
datasource='wikipedia_rollup',
granularity='all',
intervals='1970-01-01/2023-01-01',
aggregations={'distinct_count':thetasketch('channel', size=262144)
}
)Vijay Narayanan
03/30/2023, 2:22 PMin the sql use approx_count_distinct_ds_theta(channel,262144). this will give you exact count upto 262144 and approx after that
g
Giwrgos Gkοlfopoulos
03/31/2023, 10:40 AMhmm theta gives approximate result. if I want to get the exact number?
d
David McHealy
03/31/2023, 10:51 AMThe highest you can get an exact count depends on how much data you can store in memory. All you can do is set an upper boundary.
g
Giwrgos Gkοlfopoulos
04/04/2023, 4:46 PMOk David. Thank you very much!