Can't visualise db data with spaces in JSON Object fields on grafana.

[mallikarjun-12]

What happened:
When using the ClickHouse plugin in Grafana to retrieve data from a ClickHouse database, I have encountered issues when dealing with JSON data that contains fields with spaces. The sample JSON data provided is as follows:

{
  "timestamp": "2023-12-06 09:20:32",
  "level": "debug",
  "log": {
    "caller": "runtime/asm_arm64.s:1197",
    "cost": 0,
    "count": 0,
    "error": "ws closed: 1001 ",
    "error string": "ws closed: 1001 ",
    "errors": "",
    "id": "IHK9LYy8",
    "index": 0,
    "ip": "",
    "level": "debug",
    "method": "",
    "msg": "error reading client data",
    "path": "",
    "query": "",
    "requestID": "",
    "segment": "",
    "service": "ws",
    "status": 0,
    "statusCode": 0,
    "symbol": "",
    "time": 0,
    "time_taken": 0,
    "ts": "2023-12-06T09:20:32.903Z",
    "user": "",
    "user-agent": "",
    "userID": "",
    "userId": ""
  },
  "service": "ws",
  "msg": "error reading client data",
  "status": 0,
  "requestID": "",
  "ip": "0.0.0.0",
  "method": "",
  "path": "",
  "errors": "",
  "cost": 0
}

Query: SELECT "log", "level", "timestamp" FROM "default"."logs" LIMIT 10;

Error Field: error string

Error: error querying the database: clickhouse: unsupported column type "string String"

What you expected to happen:
Visualise data even if the JSON fields have spaces.

How to reproduce it (as minimally and precisely as possible):

• Install Clickhouse plugin
• add click house details and connect
• Insert data into a sample table with one column as JSON which has a field with spaces.
• Try to visualise table data on grafana

Screenshots

Environment:

• Grafana version: 10.1.2 (commit: 8e428858dd, branch: HEAD)
• Plugin version: 3.3.0
• OS Grafana is installed on: 6.1.52-71.125.amzn2023.aarch64
• User OS & Browser: Google Chrome
• Others:

[adamyeats]

@mallikarjun-12 Hey there! I'm looking into reproducing this on my side currently. Would you be able to confirm the exact schema of the table you're trying to read from (DESCRIBE TABLE output for the table would be great), and the table engine (e.g. MergeTree, TinyLog etc.) that the table uses please?

[alyssabull]

Hey @mallikarjun-12! Is this still an issue? If it is, can you provide us with more information (listed in the comment above)? Thanks!

[aangelisc]

Hi @mallikarjun-12, are you able to confirm if this is still an issue? Reading the ClickHouse docs I can see that this type is obsolete. Does the Map type work instead?

[siva-vunet]

Hey @adamyeats I have encountered this exact same issue today. Follow the steps below for replication -

1. Create a table with one JSON column using a MergeTree engine. Ex:

CREATE TABLE default.sample_table ( json_data Object('json') ) ENGINE = MergeTree ORDER BY tuple()

2. Insert a json with a space in any one of the keys. Ex:

INSERT INTO sample_table VALUES ('{"key 1":10}')

3. Try querying the table from the grafana plugin which should lead you to the following error -
   error querying the database: clickhouse: unsupported column type "1 Int8"`

[aangelisc]

Thank you for the update @siva-vunet, have you also attempted a similar test with the Map type?

@SpencerTorres are you able to chime in here? It seems this may be related to how ClickHouse handles JSON rather than something specific to Grafana.

[siva-vunet]

Yes I have tried this with the Map data type and it doesn't throw any error there. Have tried with Array and Nested too just in case and was able to successfully query the rows with spaces in them.

[SpencerTorres]

I believe the map type is preferred for now if that works for you. ClickHouse docs say the JSON field is experimental and obsolete.