Read Delta table from multiple folders

user_6273496 0
Viewed 4407

I'm working on a Databricks. I'm reading my delta table like this:

path = "/root/data/foo/year=2021/"
df = spark.read.format("delta").load(path)

However within the year=2021 folder there are sub-folders for each day day=01, day=02, day=03, etc...

How can I read folders of day 4,5,6 for example?

edit#1

I'm reading answer from different questions and it seems that the proper way to achieve this is to use a filter applied the partitioned column

azure-data-lake pyspark azure-databricks databricks
4 Answers

Seems the better way to read partitioned delta tables is to apply a filter on the partitions:

df = spark.read.format("delta").load('/whatever/path')
df2 = df.filter("year = '2021' and month = '01' and day in ('04','05','06')")
user_141657300

List them as comma separated values enclosed in curly brackets

path = "/root/data/foo/year=2021/{04,05,06}/"

or

path = "/root/data/foo/year=2021/[04,05,06]/"
path = "/root/data/foo/year=2021/0[4|5|6]/"
user_46813410

  1. Remove .format("delta"). from your Source code path

  2. Use below UDF

    def fileexists(filepath, FromDay, ToDay): mylist = dbutils.fs.ls(filepath) maxcount = len(mylist) - ToDay + 1 maxcount1 = maxcount - FromDay return [item[0] for item in mylist][maxcount1:maxcount]

    filepath = Parent File Path = "/root/data/foo/year=2021/" FromDay = Start day folder ToDay = End day folder

Note: Change the function as per your requirement.

user_88935330

.load() accepts a list as well as a str. In your particular example, tryp this:

path = [f'/root/data/foo/year=2021/day={ea}' for ea in ['01', '02, '03]]

N.b. glob pattern is acceptable, but not RegEx. I'm on Spark 3.2.1.

user_46975010
Related