Hi team, I have a delta table (~200 GB, 6 Billion rows) stored on S3. I want to query using databricks SQL the count of distinct values over one column(string) on a given timeframe. I partitioned by day, with auto optimize=True, so the file size ranged between 100-300 Mb. Tried zorder on this column, bloom filter, and a combination of both. I’ve tried different cluster sizes up until X-large, and no matter what, query time is a minimum of one minute. Is this the optimum databricks & delta can achieve, or am I doing something wrong? Thanks!

Let me first say I'm no expert. Seems like the biggest optimization would be taking advantage of pratition pruning, so in other words verifying that the partitions outside of your timeframe is not scanned/read in any way at all from s3. Beyond that I think this might be more of a spark tuning challenge. Depending on what your specific goal of doing the distinct count is, there are some functions that can give you approximate counts which should perform better. It might also be worth trying a single big computer with good I/O and lots of ram over a big cluster of average machines. It could be that the bottleneck is comparing the various sets when they are returning from the nodes and even network I/O assosiated with that. Something else to look out for could be lack of pratitions/spark distribution (separate from delta) maybe? So even if you try applying larger clusters work isnt being spread out properly so nothing is gained. Since you are using databricks it might also be worth investigating if your code can run mostly on photon also. Just some speculation 😛

Thanks for the detailed answer, but as far as I understand, most of the things you suggested are impossible on data bricks SQL, I can’t switch to less but bigger machines and can’t control spark internals such as partitions. The only strategy I can think of right now is to create aggregation tables on each hour or something, which is costly and a bit ugly ….

One important thing is that z order only works on the first 32 columns. It’s better for high cardinality data, so hard to say w/out knowing the count. Bloom filter will be good if it’s heavier and more unique text. All that said, 1 minute for 200GB table doesn’t seem too bad to me considering it’s close to 1000 files

Thanks for the answer! There are 6 columns total, and the relevant column is indeed high cardinality data (a random 48-length string). so using both zorder and bloom do you think this is the best I can get?

I know that zorder will help, I’m not sure how much a bloom filter will matter on top of that. I think it’s better for longer text type of stuff like searching for a particular word, but I’m absolutely not sure about it

OK, thanks

@Roy Green since you're using Databricks, have you tried Delta Caching? (https://docs.databricks.com/optimizations/disk-cache.html#delta-cache-renamed-to-disk-cache) In addition to Z-Order indexes which you discussed above, I suppose you might be able to leverage this feature to improve your query speed (as it caches the associated parquet data files for faster retrieval), especially if the count distinct query is going to be running frequently (or on regular interval). Check it out may be!

Thanks Omkar, I think this is enabled by default on databricks SQL, is it? I can confirm that at the second time I run the query is much faster, however - as it serverless and I turned on the automatic shutdown, the cache invalidation happens a lot any idea how to overcome that?

Yes @Roy Green if you're using SSD-based workers, I suppose the workers come preconfigured to optimally make use of disk cache (mentioned here in the first para). Indeed you're right, in autoscaling mode whenever a worker is shutdown, the cache is lost and Spark would've to reload the partitions from the source - in such cases the query might take longer to execute. This too is mentioned in this doc in the Notes section. If you're facing too frequent cache invalidation due to autoscaling, you can probably try to tweak the

Min Idle

and

Max Capacity

in your serverless pool config as required. Although please be careful to not set

Min Idle

too high as it may cost you more and the workers may stay idle during off-peak hours. More info here on best practices: https://docs.databricks.com/clusters/cluster-config-best-practices.html#autoscaling This talk might also be useful: https://www.databricks.com/dataaisummit/session/scaling-your-workloads-databricks-serverless Hoping this will be helpful to you! 😁

Thanks, it’s indeed helpful. but I’m still struggling trying to optimize the query time somehow (regardless of the cache).

@Roy Green Can you check the output of

EXPLAIN

for your query? It may tell you which parts of the query are taking time. Explain syntax here: https://docs.databricks.com/sql/language-manual/sql-ref-syntax-qry-explain.html

I did, it’s the scan- the fetching of the files from S3

Not really. S3 takes some time. Delta does it’s own listing to avoid expensive/longer S3 listing. You have a lot of files to read.

yeah that’s true

yes. plain optimize will do 1GB

but this contradicts the z-order

Depends on how many files you’re skipping from zorder. You can see that in the sql tab of the query. How many files scanned vs total

a lot are skipped

it’s a balance

I understand

I don’t think so

OK

@Roy Green Yeah, if your goal specifically is to keep a distinct count by a string column, you can probably try out storing it in Redis (key - your string column, value - the column's count). Periodically update the counters by checking for new data in your source table. This should be super fast as this is a typical Redis (hashmap) use case which will return you the distinct counts for the specified key in O(1) time.

but then how can I use these values on my dashboards?

Depends, what kind of dashboards are these?

Databricks sql dashboard

Okay, in that case you won't be able to use Redis. But yes you'll be able to do the same in a Delta table e.g. if your column's name is

book_name

then you schema will be something like:

table BookCounter
  book_name string (your string column)
  book_count integer (it's overall count)

Periodically update this Counter table from your source table.

OK I’ll try that