topic Re: Cannot read JSON from /Volumes in Data Engineering

Cannot read JSON from /Volumes

sathyafmt — Sat, 28 Sep 2024 20:31:33 GMT

I am trying to read in a JSON file with this in SQL Editor & it fails with None.get

CREATE TEMPORARY VIEW multilineJson USING json OPTIONS (path="/Volumes/my_catalog/my_schema/jsondir/test.json", multiline=true);

None.get is all the error it has.The permissions are fine, I have granted all privileges to all users & I am able to: LIST '/Volumes/my_catalog/my_schema/jsondir/test.json' without errors. The Diagnose error co-pilot wants me to use s3 path...

What does the error None.get probably mean ? [I looked in Query History & it has the same error]
Are Volumes not supported in path ? I tried with path="dbfs:/Volumes/my_catalog/my_schema/jsondir/test.json" also, but with no success.
I also tried with
CREATE TEMPORARY VIEW multilineJson(author STRING, efficacy DOUBLE, rating INTEGER, custom_labels VARIANT) .. and same error.

Sample JSON used -

[ { "author": "user1", "efficacy": 0.0, "rating": 5, "custom_labels": [ "label1", "label2" ] }, { "author": "user2", "efficacy": 0.9, "rating": 4, "custom_labels": [ "label3", "label4" ] } ]

Thanks !

Re: Cannot read JSON from /Volumes

filipniziol — Sun, 29 Sep 2024 18:16:58 GMT

Hi @sathyafmt ,

None.get is a scala erros that suggests that a value does not exist.
Maybe it is about the path?
I wonder whether you can run the below code to make sure that the file is read to dataframe:

df = spark.read.option("multiline", "true").json("/Volumes/my_catalog/my_schema/jsondir/test.json") df.show()

If yes, try to create your view using python:

df.createOrReplaceTempView("my_view")

Re: Cannot read JSON from /Volumes

szymon_dybczak — Mon, 30 Sep 2024 07:03:57 GMT

Hi @sathyafmt ,

What runtime are you on? There could be some limitations applied to Scala depending on runtime you choose.

Re: Cannot read JSON from /Volumes

sathyafmt — Wed, 02 Oct 2024 15:50:25 GMT

thanks @szymon_dybczak @filipniziol for your response.

I am just doing this in the SQL Editor (not in python notebook or scala). I don't know how to get the runtime version .. lmk how to get it & I'll add that info.

Thanks again!

Re: Cannot read JSON from /Volumes

filipniziol — Thu, 03 Oct 2024 07:20:51 GMT

Hi @sathyafmt ,

Are you using Serverless SQL Warehouse?
It seems accessing Volumes in Serverless SQL Warehouse is not implemented yet.

When running the code in SQL Warehouse I am getting the same error as you:

When running in notebook, it works just fine

Could you check whether you can create a table and it reads from the directory?

CREATE TABLE my_json_table USING json OPTIONS ( path '/Volumes/my_catalog/my_schema/jsondir/test.json', multiline 'true' );

When running this using SQL Serverless Warehouse I got an error:

Then when trying to enable volumes I got an error:

To sum up I believe as of now in Serverless SQL Warehouse volumes are not supported.

Re: Cannot read JSON from /Volumes

sathyafmt — Thu, 03 Oct 2024 17:54:07 GMT

@filipniziol - Yes, I was on Serverless SQL Warehouse. It works with "CERATE TABLE .. " thx! I am surprised that the warehouse type is impacting this feature.

But I got the SQL from databricks documentation -https://docs.databricks.com/en/query/formats/json.html#multi-line-mode & I was hoping it would work..