General guidelines for choosing databricks worker type
1. Memory optimized
a lot of shuffle and spills, or when spark cahcing is needed
this is typical when joining many big tables, the data needs to shuffle to different nodes for joining
2. Storage optimized
analytical workload that raeds the same data repeatedly
3. Compute optimized
ETL jobs that don't require wide transformation (e.g. join and aggregation). Join needs multiple tables held in memory, and aggregation need whole table in memory.
If just copy/flow data from one place to another, with computation at row level or small batch level, e.g. derive column, concat name, some small window aggregation, then not too nmuch memory is required.
IF the ETL needs joins or lookups or anything that needs to hold big data in memory for long, memory optimized if more suitable.
4. GPU Optimized
obviously for machine learning
5. General purpose / all purpose
all purpose cluster should only be used for ad hoc query and interactive notebook during development and testing.
Auto scaling.
Autoscaling should not be used for Spark Structured Streaming because once the cluster scales up, it is difficult to determine when to scale it down.
Photon
provides faster query / scan of big data, and with many small files.
faster Delta Cache access and Delta writing using merge, update, insert, etc.
join improvements as well
Recommended to enable Photon if
ETL has MERGE operations
write large Delta/parquet data to cloud storage
scan / join / agg of large datasets
ad hoc / interactive sql queries
Shuffle partition number
It should be a multiplication of the total number of worker cores, so each core is busy
-- in SQLset spark.sql.shuffle.partitions = M*<number of total cores in cluster>
-- in PySparkspark.conf.set("spark.sql.shuffle.partitions", M*sc.defaultParallelism)
-- M is a multiplier here,
-- In the absence of manual shuffle partition tuning set M to 2 or 3 as a rule of thumb
Partition
dont partition table that is too small (eg. < 1TB). The ingestion time clustering will order data by ingestion time.
each partition should be big enough, e.g. > 1GB, parition column should be low cardinality, e.g. Year
Optimize
OPTIMIZE compacts files, and can be applied with ZORDER, which sorts and co-locates data by selected columns
use high cardinality columns for z-ordering, e.g. customer id