What is the best practice to load a delta table specific partition in databricks?

Question

I would like to know what is the best way to load a delta table specific partition ? Is option 2 loading the all table before filtering ?

option 1 :

df = spark.read.format("delta").option('basePath','/mnt/raw/mytable/')\
   .load('/mnt/raw/mytable/ingestdate=20210703')

(Is the basePath option needed here ?)

option 2 :

df = spark.read.format("delta").load('/mnt/raw/mytable/')
df = df.filter(col('ingestdate')=='20210703')

Many thanks in advance !

Communication

Answer 1

In the second option, spark loads only the relevant partitions that has been mentioned on the filter condition, internally spark does partition pruning and load only the relevant data from source table.

Whereas in the first option, you are directly instructing spark to load only the respective partitions as defined.

So in both the cases, you will end up loading only the respective partitions data.

Lanai

Answer 2

If your table is partitioned and you want to read just one partition, you can do it using where

val partition = "year = '2019'"


val df = spark.read
 .format("delta")
 .load(path)
 .where(partition)

Nutgall

where is just alias to filter, so this is the option 2 – Exceptive