Cannot read JSON from /Volumes

[sathyafmt]

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 !

[filipniziol]

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]

Hi @sathyafmt ,

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

[sathyafmt]

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!

[filipniziol · Accepted solution]

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.

[sathyafmt]

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