CosmosDB query runs extremely fast, but same query in Pyspark SQL takes ages

Daniel Kavanagh 0
Viewed 51

I'm running a query like:

SELECT c.Name, count(c.Enabled) as Redeemed
FROM c
WHERE NOT IS_NULL(c.Enabled)
AND c.Name NOT IN ('EXAMPLE1', 'EXAMPLE2')
GROUP BY c.Name

on a cosmos DB table (using spark.cosmos.read with a customQuery) with 50mil records and it returns one row in 0.05 seconds. The same query run on a massive Pyspark cluster takes over an hour on the action if not df.rdd.isEmpty() after executing the query.

Is this just the nature of pyspark, or am I doing the query in an inefficient way? Should I instead use no custom query, and instead filter the dataframe?

Edit: I'm not totally sure why, but adding the partitioning strategy of Restrictive to the query options made this go down to a few seconds.

azure-cosmosdb-sqlapi azure-cosmosdb pyspark apache-spark-sql
1 Answers

The solution was to add "spark.cosmos.partitioning.strategy": "Restrictive" to the query options, which for some reason apparently works well when returning small datasets, even when querying large datasets. Doing this made it go from over an hour to a few seconds.

Daniel Kavanagh0
Related