Redash with JSON data sent by an Erddap server

Support
1

hello I am new to redash and I dont succeed to manage a Json flow sent by an erddap data server

for instance this json URL

url: https://erddap.osupytheas.fr/erddap/tabledap/meteo_toulon_2ce5.json?time,Temperature&time>=2022-06-25T00%3A00%3A00Z&time<2022-06-27T00%3A00%3A00Z&.draw=linesAndMarkers&.marker=3|5&.color=0x000000&.colorBar=|||||&.bgColor=0xffccccff

returns this json flow

{
  "table": {
    "columnNames": ["time", "Temperature"],
    "columnTypes": ["String", "float"],
    "columnUnits": ["UTC", null],
    "rows": [
      ["2022-06-25T00:00:00Z", 21.8],
      ["2022-06-25T00:06:00Z", 21.5],
      ["2022-06-25T00:12:00Z", 21.2],
      ["2022-06-25T00:18:00Z", 21],
      ["2022-06-25T00:24:00Z", 20.8],
      ["2022-06-25T00:30:00Z", 20.6],

and I dont succeed to graph Temperature over DateTime
I get “rows” as a list of list

is this Json flow compliant with redash requirement,?
and how can I do to get Datetime as X columns and Temperature as Y

thanks for your help

Maurice

2

Welcome to the forum! That API endpoint doesn’t return data in the format Redash understands.

One option you can use is the Python query runner, which will let you reformat this data as needed. Or, if you find yourself working with data from this source very often, you could write a custom query runner to do it.

Here’s the python query I used to make it work:

import requests

url = "https://erddap.osupytheas.fr/erddap/tabledap/meteo_toulon_2ce5.json?time,Temperature&time>=2022-06-25T00%3A00%3A00Z&time<2022-06-27T00%3A00%3A00Z&.draw=linesAndMarkers&.marker=3|5&.color=0x000000&.colorBar=|||||&.bgColor=0xffccccff"

data = requests.get(url).json()

result["columns"] = [{"name": name, "type":"string"} for name in data["table"]["columnNames"]]

for row in data["table"]["rows"]:
    this_row = {}
    for colname, value in zip(data["table"]["columnNames"], row):
        this_row = {**this_row, **{colname: value}}
    result["rows"].append(this_row)

CleanShot 2022-07-02 at 16.06.21@2x
CleanShot 2022-07-02 at 16.06.21@2x3236×2618 542 KB

4

thanks for your answer and solution

I have enabled :

REDASH_ADDITIONAL_QUERY_RUNNERS: "redash.query_runner.python"

in the docker-compose.yml file, && restarted the docker container (docker-compose stop && docker-compose up -d)

  • created a new python-json query with the python code you gave me

but when I run the code I get this error

Error running query: ‘NoneType’ object has no attribute ‘annotate_query’

what have I omitted?

thanks for your help

Maurice

5

I have found what seems to be the solution of the error above… It is that we have to include also
REDASH_ADDITIONAL_QUERY_RUNNERS=redash.query_runner.python
in the /opt/redash/env file

So now, the python script can run, but with a new syntax error

Error running query: <type ‘exceptions.SyntaxError’> invalid syntax (, line 12)

line12 is: this_row = {**this_row, **{colname: value}}

do you know where the error comes from? since I have textually copied the code you gave me

In a first step I would want to make your code work, before understanding how it works
thanks for your help

Maurice

6

What version of Redash do you use? My example assumes you are on Redash V10, which uses Python 3. The syntax error could be because you are on an older version of Redash that uses Python 2.

If that’s the case you can change the code to:

# python2 version
import requests

url = "https://erddap.osupytheas.fr/erddap/tabledap/meteo_toulon_2ce5.json?time,Temperature&time>=2022-06-25T00%3A00%3A00Z&time<2022-06-27T00%3A00%3A00Z&.draw=linesAndMarkers&.marker=3|5&.color=0x000000&.colorBar=|||||&.bgColor=0xffccccff"

data = requests.get(url).json()

result["columns"] = [{"name": name, "type":"string"} for name in data["table"]["columnNames"]]

for row in data["table"]["rows"]:
    this_row = {}
    for colname, value in zip(data["table"]["columnNames"], row):
        this_row = this_row.update({colname: value})
    result["rows"].append(this_row)
7

hello
I got the setup.sh script from here GitHub - getredash/setup: Setup scripts for Redash Cloud Images

so indeed , it seems that from the system status, the version is a v8 ?

  • Version 8.0.0+b32245

I dont know which version of python is ran in the container ?

anyway I change the python code from the last you gave me, and now I have this error

Error running query: <type ‘exceptions.AttributeError’> ‘NoneType’ object has no attribute ‘update’
from the line

 this_row = this_row.update({colname: value})

so maybe I can try to reinstall a last version of redash , if you tell me where to get the last version to get the version 10 ?

thanks

Maurice

8

I dont know why the this_row.update({colname: value}) doesn’t work in redash?
since I tested the same successfully in a python2 and python3 console

Error running query: <type ‘exceptions.AttributeError’> ‘NoneType’ object has no attribute ‘update’

So I replaced the .update function, by something equivalent this_row[colname]=value
and it seems to work…

I get now my 2 columns “time” and “Temperature” and I can graph temperature over time from an erddap json flow

for row in data[“table”][“rows”]:
this_row = {}
for colname, value in zip(data[“table”][“columnNames”], row):
#this_row = this_row.update({colname: value})
this_row[colname]=value
result[“rows”].append(this_row)

In conclusion, if you can tell me how and where to get the redash v10 version? because I think I use the v8 from GitHub - getredash/setup: Setup scripts for Redash Cloud Images
other question, how can I know the python version which run into the docker redash contianer?

thanks for all
I begin to better understand

Maurice

9

For experimentation, I’d recommend following our devloop instructions here: https://redash.io/help/open-source/dev-guide/docker

You could also just follow the instructions to upgrade your V8 instance to V10: Upgrade from V8 to V10 Walkthrough - YouTube. Upgrading your V8 instance only takes a few minutes, especially if you used the setup script.

Note that the setup script has not been updated for V10 (yet). So the Docker instructions, or the upgrade path are the only way to install V10 without manually modifying the setup script.

10

many thanks I have updated in redash 10… it works
thanks for you help on this thread

2 Likes