Synapse Analytics sql on-demand sync with spark pool is very slow to query
I have files loaded into an azure storage account gen2, and am using Azure Synapse Analytics to query them. Following the documentation here: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/develop-storage-files-spark-tables, I should be able to create a spark sql table to query the partitioned data, and thus subsequently use the metadata from spark sql in my sql on demand query to given the line in the doc: When a table is partitioned in Spark, files in storage are organized by folders. Serverless SQL pool will use partition metadata and only target relevant folders and files for your query
My data is partitioned in ADLS gen2 as:
Running the query in a spark notebook in Synapse Analytics returns in just over 4 seconds, as it should given the partitioning:
However, now running the same query in the sql on demand sql side script never completes:
This result and extreme reduction in performance compared to spark pool is completely counter to what the documentation notes. Is there something I am missing in the query to make sql-on demand use the partitions?
Filepath() and filename() functions can be used in the WHERE clause to filter the files to be read. Which that you can achieve the pruning you have been looking for.
- graph.triplets seems not work as expected
- PySpark MongoDB :: java.lang.NoClassDefFoundError: com/mongodb/client/model/Collation
- How do I access the fields within a VARIANT column while reading from Kafka using Spark?
- pyspark: how to specify rebalance partitioning hint with columns
- Is Python UDF still inefficient in Spark?
- How to import AnalysisException in PySpark
- Updated scalapb class fails to render old dataframe
- Create a Column with Values Based on an Array of Column Names Provided in Another Column
- How to join on multiple columns in Pyspark?
- Databricks: Issue while creating spark data frame from pandas
- UnsatisfiedLinkError while writing to S3 using Staging S3A Committer on Windows
- How to install postgresql in my docker image?
- Why Spark won't store Broadcasted data in off heap memory? Why does it store one copy per executor?
- Are Parquet files highly structured or semi structured?
- Is it possible to register the dataFrame as a SQL temporary view on spark structured streaming dataframe?
- How to cast an array of struct in a spark dataframe using selectExpr?
- convert columns of pyspark data frame to lowercase
- What are the various join types in Spark?
- Reading multiple Parquet files in PySpark notebook
- spark dataframe groupping does not count nulls
- Read a list of s3 data by in scala spark in batches
- EMRserverless is allocating half of the memory to the executors than what we actually define in spark jobs
- Using external library in PySpark UDF pickle error
- Spring Boot Spark on K8S (Minikube): cannot assign instance of java.lang.invoke.SerializedLambda
- Understanding the shuffle in spark
- Difference between describe() and summary() in Apache Spark
- Apache Spark: Get number of records per partition
- How to show full column content in a Spark Dataframe?
- How to Read Multiple CSV Files with Skipping Rows and Footer in PySpark Efficiently?
- pyspark 403 error trying to access openly available AWS S3 bucket