https://delta.io logo
f

Fijurrahman Amanulla

09/02/2023, 7:45 AM
Hey guys, I have a 12TB Delta table on AWS S3 (via Databricks) with 20 billion+ records. I've partitioned by date and used
ZORDER
on
s_key
, but querying by a single
s_key
still takes over 40 minutes. Daily merges (update if
s_key
matches, insert if not) are also slow. How can I improve performance? Tried: • Partitioning by date • ZORDER optimization on
s_key
• Regular OPTIMIZE command @Denny Lee @Dominique Brezinski @TD Even after trying the suggested optimizations, the Spark SQL execution plan still appears to read all the files. Why is this happening?
d

Dominique Brezinski

09/02/2023, 2:08 PM
Can you post your the zorder optimize command you are running, the select query you are trying, the explain for the query, and the most recent rows of the table history?
m

Matthew Powers

09/02/2023, 6:37 PM
Z Ordering and Hive style partitioning have tradeoffs. The key is to find the partitioning / Z Ordering strategy that gives the best overall performance for your query patterns. Partitioning by date & Z Ordering by
s_key
will make a query that filters on date &
s_key
faster. If you’re just querying on a single
s_key
then partitioning by date will make the query slower. You could also Z Order by date & s_key and not partition at all. But the best partitioning/Z Ordering depends on your query patterns.
🙏 1
👍 1
s

Samuel LaFell

09/02/2023, 6:55 PM
Been observing this thread, Matthew, and figured it has something to do with Partioning on date and ZORDER on
s_key
. I guess the thought is file-skipping is enabled with s_key ZORDER, but if each s_key is spread across different files (partitioned by date), then it would make sense it still takes a lot of time and can’t actually skip files.
m

Matthew Powers

09/02/2023, 6:58 PM
@Samuel LaFell - yep, your intuition is correct. If Delta can’t skip files based on the s_key column metadata, then it has to read lots of files and the query will be slower.
s

Samuel LaFell

09/02/2023, 7:00 PM
For my learning then, is it often times ideal to Partition & ZORDER by the same column? You ever seen a use case in which those two things would be different?
d

Dominique Brezinski

09/02/2023, 7:15 PM
No, in many cases you should not partition at all.
1
d

Denny Lee

09/02/2023, 7:47 PM
+1 to @Dominique Brezinski call out.
1
👍 1
s

Samuel LaFell

09/03/2023, 12:07 AM
Thank yall very much
f

Fijurrahman Amanulla

09/03/2023, 4:19 AM
Alright folks, If we're thinking of bypassing the usual partitioning and instead adding the partition column and
s_key
to the
ZORDER
, the rationale seems to be enhancing query performance and reducing the need for a full file scan. I'll give this a shot and review the Spark execution plan. But here's a thought: In Databricks, if we opt for an unmanaged table and leverage external storage to establish an external table, would that influence the performance in any way? Specifically, when incorporating ZORDERing in an external table, does it genuinely co-locate the data files on that external storage? My gut feeling is that there shouldn't be a major discrepancy. What's your take on this?
d

Dominique Brezinski

09/03/2023, 1:55 PM
By unmanaged I assume you mean not managed by UnityCatalog? By default UC tables use ingestion time clustering if you don’t specify a partition scheme, which is good. To your point, if you just create a delta table at an explicit storage path, zorder works exactly the same. I don’t know why people are mentioning partitioning AND zordering the s_key column. Don’t do that, unless all updates are focused on a tight range of s_key values, in which case you might want to create a synthetic “bucket” column derived from a modulo operation on the s_key value and partition by that. Otherwise don’t partition the table at all, and just regularly zorder by the s_key column. Without knowing more about your data and query patterns, that is the best advice I can give based on the information provided.
2