How Pushed Filters work with Parquet files in databricks?

user_15909676 0
Viewed 31

How does pushedFilters work while using parquet files ?

Below are the two queries that I submitted in databricks.

HighVolume = spark.read.parquet("/FileStore/shared_uploads/highVolume/*.parquet") \
  .where("originating_base_num in ('B02764','B02617')").count()

HighVolume_wofilter = spark.read.parquet("/FileStore/shared_uploads/highVolume/*.parquet") \
  .count()

Physical Plan: Clearly mentions PushedFilter is not null for HighVolume dataframe.

HighVolume :
  PushedFilters: [In(originating_base_num, [B02617,B02764])]

HighVolume_wofilter:
  PushedFilters: []

But while checking Spark UI, I observed that spark is reading all the rows in both the cases ( ignoring the filters).

snippet:

HighVolume :

enter image description here

HighVolume_wofilter:

enter image description here

Can someone please help me understand that why instead of having the filters in Physical plan, all the rows are being read. ?

Thanks!

databricks apache-spark-sql apache-spark
1 Answers

When you working with parquet there are few types of optimizations:

  • Skipping reading the not necessary files when table is partitioned, and there is a condition on that partition. In the explain it will be visible as PartitionFilters: [p#503 IN (1,2)] (p is the partition column). In this case Spark will read only files related to the given partitions - it's most efficient way for Parquet.

  • Skipping some data inside the files - Parquet format has internal statistics, such as, min/max per column, etc. that allows to skip reading blocks inside Parquet that doesn't contain your data. These filters will be shown as PushedFilters: [In(p, [1,2])]. But this may not be efficient if your data is inside min/max range, so Spark needs to read all blocks and filter on the Spark level.

P.S. Please take into account that Delta Lake format allows to access data more efficiently because of the data skipping, bloom filters, Z-ordering, etc.

Alex_Ott_186270
Related