topic Re: Read file from dbfs with pd.read_csv() using databricks-connect in Data Engineering

Read file from dbfs with pd.read_csv() using databricks-connect

hamzatazib96 — Wed, 18 Aug 2021 16:11:46 GMT

Hello all,

As described in the title, here's my problem:

1. I'm using databricks-connect in order to send jobs to a databricks cluster

2. The "local" environment is an AWS EC2

3. I want to read a CSV file that is in DBFS (databricks) with

pd.read_csv()

. Reason for that is that it's too big to do

spark.read.csv()

and then

.toPandas()

(crashes everytime).

4. When I run

pd.read_csv("/dbfs/FileStore/some_file")

I get a

FileNotFoundError

because it points to the local S3 buckets rather than to dbfs. Is there a way to do what I want to do (e.g. change where pandas looks for files with some options)?

Thanks a lot in advance!

Re: Read file from dbfs with pd.read_csv() using databricks-connect

User16763506586 — Wed, 29 Sep 2021 11:09:16 GMT

Hi,

what happens if you change it to below ?

pd.read_csv("file:/dbfs/FileStore/some_file")

Re: Read file from dbfs with pd.read_csv() using databricks-connect

venter2021 — Thu, 28 Oct 2021 09:38:01 GMT

I am having a similar issue:

I am running databricks-connect from within a docker container
I have a .xls file stored in Azure File storage, which is mounted to dbfs
I would like to read this excel file with

pd.read_excel("dbfs:/mnt/path/to/file.xls")

Has a solution been found for this?

Re: Read file from dbfs with pd.read_csv() using databricks-connect

venter2021 — Thu, 28 Oct 2021 09:38:29 GMT

Trying it with pd.read_excel does not help.

Re: Read file from dbfs with pd.read_csv() using databricks-connect

Student185 — Wed, 24 Nov 2021 01:42:02 GMT

I've tried, which doesn't work.

Re: Read file from dbfs with pd.read_csv() using databricks-connect

Anonymous — Wed, 24 Nov 2021 14:54:18 GMT

Hi @Kaniz Fatma ,

I am having similar issues when using databricks-connect with Azure. I am not able to read data that is already mounted to dbfs (from a datalake gen2). The data is readable within the Azure Databricks Notebook environment but not from databricks-connect.

Re: Read file from dbfs with pd.read_csv() using databricks-connect

Anonymous — Wed, 24 Nov 2021 14:58:49 GMT

Hi,

My DBR:

9.1 LTS (includes Apache Spark 3.1.2, Scala 2.12)

Re: Read file from dbfs with pd.read_csv() using databricks-connect

Anonymous — Wed, 24 Nov 2021 15:03:40 GMT

@Kaniz Fatma ,

All tests in databricks-connect pass. I am also able to run the examples provided in the documentation (which do not read data from dbfs)

Re: Read file from dbfs with pd.read_csv() using databricks-connect

Anonymous — Wed, 24 Nov 2021 15:49:15 GMT

Hi @Kaniz Fatma ,

No, I still haven't found the solution and I can't read from dbfs (not with pandas.read_csv).

I meant to say that the setup tests pass, so the issue is not in the setup)

Re: Read file from dbfs with pd.read_csv() using databricks-connect

Anonymous — Wed, 24 Nov 2021 15:58:38 GMT

Hi @Kaniz Fatma ,

I will try that and report!

Re: Read file from dbfs with pd.read_csv() using databricks-connect

Anonymous — Wed, 24 Nov 2021 16:25:59 GMT

Hi @Kaniz Fatma ,

I can confirm that after downgrading to the DBR 6.4, and passing all the tests in:

databricks-connect test

I am still getting the FileNotFound error when trying to use

pd.read_csv('/dbfs/mnt/datalake_gen2_data/some.csv'')

Re: Read file from dbfs with pd.read_csv() using databricks-connect

Student185 — Wed, 24 Nov 2021 19:39:52 GMT

Hi Fatma,

Thanks for asking.

I've tried 10.1 ML (includes Apache Spark 3.2.0, Scala 2.12) and 9.1 LTS (Scala 2.12, Spark 3.1.2) . Both of them don't work.

However, it works while I read it via spark. And I used display(dbutils.fs.ls("dbfs:/FileStore/tables/")) to test it, my file path(dbfs:/FileStore/tables/POS_CASH_balance.csv) exists. So I don't think it is the problem of the path or my code of pandas. I personally guess that the free version didn't support reading csv/files from dbfs via pandas directly, isn't it?

Here is the change of my code, and the change works

pd.read_csv('dbfs:/FileStore/tables/POS_CASH_balance.csv')-->spark.read.csv('dbfs:/FileStore/tables/POS_CASH_balance.csv)

Hope my experience could help others.

Cheers

Re: Read file from dbfs with pd.read_csv() using databricks-connect

Anonymous — Thu, 25 Nov 2021 08:18:03 GMT

Hi,

After some research, I have found out that the pandas API reads only local files. This means that even if a read_csv command works in the Databricks Notebook environment, it will not work when using databricks-connect (pandas reads locally from within the notebook environment).

A work around is to use the pyspark spark.read.format('csv') API to read the remote files and append a ".toPandas()" at the end so that we get a pandas dataframe.

df_pandas = spark.read.format('csv').options(header='true').load('path/in/the/remote/dbfs/filesystem/').toPandas()

Re: Read file from dbfs with pd.read_csv() using databricks-connect

Student185 — Thu, 25 Nov 2021 18:09:06 GMT

Hi Arturooa,

It seems we are holding a similar conclusion. Just a quick question, what do you mean for 'local files'? I've uploaded my files into dbfs, are they not local files after that?

Thanks

Re: Read file from dbfs with pd.read_csv() using databricks-connect

Anonymous — Fri, 26 Nov 2021 11:40:44 GMT

Hi @Yuanyue Liu ,

The spark engine is connected to the (remote) workers on Databricks, this is the reason why you can read the data from the dbfs by use of:

spark.read.format('csv').options(header='true').load('path/in/the/remote/dbfs/filesystem/')

The same happens with dbutils, for example. You can read files in the dbfs with for example:

dbutils.fs.ls(files_path)

Pandas does not connect directly to the remote filesystem (dbfs). That is the reason why you have to first read the remote data with spark and then transform to an in-memory dataframe (pandas).

I am using pandas profiling and after I make an HTML report, which is written to the local driver (since pandas_profiling does not connect to the remote filesystem either), I use dbutils to upload data to my mnt drive in dbfs (that comes from a datalake gen2).

I hope this helps!

Re: Read file from dbfs with pd.read_csv() using databricks-connect

Anonymous — Mon, 06 Dec 2021 17:10:50 GMT

@Arturo Amador - Would you be happy to mark your answer as best if the issue has been resolved by what you found? That will help others find your answer more quickly in the future.

Re: Read file from dbfs with pd.read_csv() using databricks-connect

Anonymous — Wed, 15 Dec 2021 10:14:09 GMT

Hi, @Piper Wilson ,

it is actually @hamzatazib96 that needs to mark the answer as best 🙂

Re: Read file from dbfs with pd.read_csv() using databricks-connect

Anonymous — Wed, 15 Dec 2021 18:47:24 GMT

WHOOPS! Thank you, @Arturo Amador!

@hamzatazib96 - If any of the answers solved the issue, would you be happy to mark it as best?

Re: Read file from dbfs with pd.read_csv() using databricks-connect

hamzatazib96 — Wed, 15 Dec 2021 19:42:58 GMT

Done! Thanks all for the answers and help!

Best way I found around this was to simply do an SCP transfer using the databricks exe from DBFS to an S3 bucket. The flow was:

DBFS -> EC2 Local -> S3 bucket

Re: Read file from dbfs with pd.read_csv() using databricks-connect

martud — Wed, 04 Jan 2023 21:04:13 GMT

DataBricks community edition 10.4 LTS ML (Apache Spark 3.2.1, Scala 2.12) has the same problem with pd.read_csv.

The spark.read statement replaces the original column names with (_c0, _c1,…), unless .option("header", true") is used.

The following forms should work:

path = 'dbfs:/FileStore/tables/POS_CASH_balance.csv'

spark.read
.option("header", "true")
.csv(path)

spark.read
.format("csv")
.option("header", "true")
.load(file_name)