Unable to set shuffle partitions on DLT pipeline

[PushkarDeole]

Hello,

We are using a 5 worker node DLT job compute for a continuous mode streaming pipeline. The worker configuration is Standard_D4ads_v5 i.e. 4 cores so total cores across 5 workers is 20 cores.

We have wide transformation at some places in the pipeline using group By since we need to apply state to pandas. So at each group By, it is creating 200 partitions and tasks underneath. Since there are several flows in the pipeline, it is at times executing multiple stages concurrently each with 200 tasks, so the 20 core compute cluster is getting overloaded.

Hence we need to reduce the shuffle.partitions down to 20 or so however we are not finding a way to do this in DLT pipeline.

Configured following settings: spark.sql.shuffle.partitions however it is not taking effect.

Also tried spark.databricks.adaptive.autoOptimizeShuffle.enabled setting this to true however it has no effect as well and still the group By is using 200 partitions.

Can someone throw some suggestion as to how this shuffle partition can be changed in DLT pipeline as this is causing lot of resource issues and CPU issues with the cluster?

[gchandra]

Try setting  spark.sql.shuffle.partitions to auto

[PushkarDeole]

Hello @gchandra thanks for the suggestion.. i already tried this by adding a conf property in advanced configuration in the DLT pipeline however it is not taking effect and also i don't see this property in the spark configuration in associated job compute for this pipeline. 

Below is snapshot from json for DLT pipeline configuration:

"name": "axp-data-pipeline",
"edition": "ADVANCED",
"storage": "dbfs:/mnt/dlh/",
"configuration": {
"kafka.cert.secret.key": "kafka-ca-cert",
"kafka.host.secret.key": "kafka-host",
"kafka.maxOffsetsPerTrigger": "1000000",
"kafka.password.secret.key": "kafka-admin-password",
"kafka.port.secret.key": "kafka-port-sasl",
"kafka.schemaregistry.host.secret.key": "kafka-schemaregistry-host",
"kafka.schemaregistry.port.secret.key": "kafka-schemaregistry-port",
"kafka.user.secret.key": "kafka-admin-username",
"secrets.scope": "analytics-perf-dbw-green-scope",
"spark.databricks.io.cache.enabled": "true",
"topic.dimensions": "testuser.avro,testadmin,testreason-code",
"topic.normalizer": "test-incoming-feed",
"topic.raw-facts": "test1,test2,test3,test4",
"spark.sql.shuffle.partitions": "auto"
},

Pls see the attached image from job compute spark environment where the property configured in pipeline is not visible.

Am I setting this correctly or is there any other way to set it?