Cannot read JSON from /Volumes

sathyafmt
New Contributor III

‎09-28-2024 01:31 PM

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...

  1. What does the error None.get probably mean ? [I looked in Query History & it has the same error]
  2. Are Volumes not supported in path ? I tried with path="dbfs:/Volumes/my_catalog/my_schema/jsondir/test.json" also, but with no success.
  3. 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 !

0 Kudos

filipniziol
Esteemed Contributor

‎09-29-2024 11:16 AM

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")

 

szymon_dybczak
Esteemed Contributor III

‎09-29-2024 11:52 PM - edited ‎09-30-2024 12:03 AM

Hi @sathyafmt ,

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

0 Kudos

sathyafmt
New Contributor III

‎10-02-2024 08:50 AM

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!

0 Kudos

filipniziol
Esteemed Contributor

‎10-03-2024 12:20 AM

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:

filipniziol_1-1727938886360.png

When running in notebook, it works just fine

filipniziol_0-1727937970832.png

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:

filipniziol_2-1727939904989.png

Then when trying to enable volumes I got an error:

filipniziol_3-1727939950286.png

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

View solution in original post

sathyafmt
New Contributor III

‎10-03-2024 10:52 AM - edited ‎10-03-2024 10:54 AM

@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..