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: 

Best practices for Liquid clustering and z-ordering for existing streaming delta tables

prakash360
New Contributor II

Hello, I have been tasked to optimize some of our existing tables in delta lake in Databricks and I was able to perform following clause on some of our delta tables but I wasn't able to execute the same clause against some of our streaming tables.

 

ALTER TABLE table_name
CLUSTER BY (column1, column2);

 

Error that I receive when I execute above statement for our streaming tables:


[STREAMING_TABLE_OPERATION_NOT_ALLOWED.UNSUPPORTED_OPERATION] The operation ALTER TABLE is not allowed: The operation is not supported on Streaming Tables. SQLSTATE: 42601


My goal is to apply liquid clustering against all tables but seems like, we may have to build tables from scratch to apply liquid clustering on streaming table, is that right?As an alternative, I thought I should apply z-ordering against streaming table since following SQL, I was able to apply. Now thinking, what's the right way to apply z-ordering? Can I just continue executing following SQL every day or shall I apply the spark config params in my code?

 

 

OPTIMIZE table_name ZORDER BY column1, column2;

 


Or

 

 

pipelines.autoOptimize.managed = true
pipelines.autoOptimize.zOrderCols = "column1,column2"

 


source: https://docs.databricks.com/en/delta-live-tables/properties.html

Appreciate the feedback and guide for best path forward to apply best possible optimization against my delta table to limit the file scans.

2 REPLIES 2

filipniziol
New Contributor III

Hi @prakash360 ,

There are a couple of things here:
1. You do not want to run ALTER TABLE commands on DLT. The idea of DLT is that they are fully managed by the DLT pipeline. Try to modify the DLT pipeline itself. 

2. At least try liquid clustering, as this a new, recommended feature. However, the liquid clustering in DLT was added just 2 weeks ago.

3. In order to make it work use preview 15.2 runtime and set channel of your DLT pipeline to preview:

filipniziol_0-1726007101075.png

prakash360
New Contributor II

Hi @filipniziol , thank you for your quick response. Not executing the alter statement against the DLT table directly and instead adjusting the pipeline configuration approach makes sense. 

Regarding the liquid clustering on the DLT tables, I assume if we set the "Channel" setting to "Preview", it would enable us to apply "ALTER STATEMENT" against DLT tables perhaps or any thoughts on how we can apply liquid clustering on the existing DLT Table alternatively?

My goal is to avoid performing refresh on DLT tables since it will require huge amounts of data writes.  

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