Databricks PySpark: java.lang.ArrayStoreException: java.util.HashMap

I am having an issue using with creating a data frame using PySpark from data gathered using an API into Databricks.

I am able to connect using an API, and using the requests library I gather:

def gather_data():
  url = www.data.com
  response = requests.get(URL)
  if response.status_code == 200:
     return response.json()
  else:
     print("error")


gather_data()

Doing this I am able to see my data as a json file, although when trying to read using a function like:

data = gather_data()
df = spark.read.option("multiline","true").json(data)

I get error code: java.lang.ArrayStoreException: java.util.HashMap

The data looks like:

Out[72]:[{'name':'Johnny',
'id_number':12,
'birthday': '2023-04-03 08:00:00.0'},
{'name: 'Dave',
'id_number':56,
'birthday': 2023-04-03 08:00:01.0'}]

The same happens when I use:

- type(gather_data())
- df = spark.read.json(data)
- df = spark.createDataFrame([Row(**i) for I in data])

At first I assumed I was missing the multiline, but that was not the case.

Typically when working with csv files, I can just read it straight into a data frame but this isn't the case here.

Through forums I have seen that the response.json() method returns what seems to be a dict type, although I can't confirm this as that type method won't complete on it.

I have gone through multiple answers and can't seem to find one that will work yet.

I have seen other methods define the schema and its types, but I am using many date time stamps, and would like to preserve the data, and am unsure how to accomplish this with a very large dataset.

The problem arises because you're not returning the JSON as text, but you're using response.json() function that returns parsed representation of that JSON.

You have following choice:

• Just create a dataframe from your parsed object using the createDataFrame:

data = gather_data()
df = spark.createDataFrame([data])

• not parse the data, and use spark.read.json, but you'll need to replace response.json() with response.text in your gather_data function:

data = gather_data()
df = spark.read.json(sc.parallelize([data]))