cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Why is My MIN MAX Query Still Slow on a 29TB Delta Table After Liquid Clustering and Optimization?

laudhon
New Contributor II

Hello,

I have a large Delta table with a size of 29TB. I implemented Liquid Clustering on this table, but running a simple MIN MAX query on the set cluster column is still extremely slow. I have already optimized the table. Am I missing something in my implementation?

1 ACCEPTED SOLUTION

Accepted Solutions

LuisRSanchez
New Contributor III

Hi

this operation should take seconds because it use the precomputed statistics for the table. Then few elements to verify:

  • if the data type is datetime or integer should work, if it is string data type then it needs to read all data.
  • verify the column position, normally delta lake only create statistics for the some columns (i think first 15), if the column is not at the list of column to precompute stats then it needs to read all data.

if you need to read all data, then i saw in the image that only 4 task are running means 4 cores, then i would recommend to use a bigger cluster  in memory and cores (scale up) with fewer nodes to reduce the shuffle.

View solution in original post

6 REPLIES 6

jacovangelder
Honored Contributor

What is the data type of the field you're querying?
All I can see is the name "_PartitionColumnUTC_". Judging by the name it is a date/timestamp but this is me making assumptions. 

Hello, this is a type integer in the format YYYYMMDD

That is strange, min/max of integers should be able to be retrieved very quickly, especially if they are partitioned columns. You are 100% sure it is an integer column and not a string? You didn't specify any filter clauses in your queries that would potentially trigger a full table scan?

Hi Jaco,

Using the ANALYZE TABLE command fixed the issue; however, I am still experiencing very slow queries on the STRING type of a different cluster key. Does liquid clustering not support the STRING type very well?

Ah then your table had to have its statistics refilled, glad it works now. 
As for string types, it should work just as well.
"slow" is a bit subjective maybe. You have not yet mentioned the warehouse tier/cluster config, are you using sufficient processing power?

LuisRSanchez
New Contributor III

Hi

this operation should take seconds because it use the precomputed statistics for the table. Then few elements to verify:

  • if the data type is datetime or integer should work, if it is string data type then it needs to read all data.
  • verify the column position, normally delta lake only create statistics for the some columns (i think first 15), if the column is not at the list of column to precompute stats then it needs to read all data.

if you need to read all data, then i saw in the image that only 4 task are running means 4 cores, then i would recommend to use a bigger cluster  in memory and cores (scale up) with fewer nodes to reduce the shuffle.

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group