0

I am using the following command to pull data from foursquare api which is working fine. How can I write the json output as table in databricks? I can't use show/display functions on data output. 

import json, requests
url = 'https://api.foursquare.com/v2/venues/explore'

params = dict(
  client_id='CLIENT_ID',
  client_secret='CLIENT_SECRET',
  v='20180323',
  ll='40.7243,-74.0018',
  query='coffee',
  limit=1
)
resp = requests.get(url=url, params=params)
data = json.loads(resp.text)
  • You can dump the JSON responses into a file and then read it back into a DataFrame using Spark `spark.read.json`. Or you can create directly a DF from the JSON string as explained [here](https://stackoverflow.com/a/49676143/1386551). – blackbishop Feb 10 '20 at 08:28

1 Answers1

0

You could read and write the data received as follows:

df = spark.read.json(resp.text)
location = 'dbfs:/tmp/test.json'
df.write.json(location)

and then create a table using the file created : 

spark.sql(f'''
CREATE TABLE IF NOT EXISTS foursquare
USING JSON 
LOCATION "{location}"
''')
Axel R.
  • 1,141
  • 7
  • 22
  • I am getting this error df = spark.read.json(json.loads(resp.text)) :- TypeError: path can be only string, list or RDD –  Feb 10 '20 at 16:59