API Datasource Error Due to Null Values

Technical queries
, , ,
#1

Hello Team,

While configuring an API as a datasource in open source BI Helical Insight 6.1 GA, we encountered the following error during table creation:

“An error occurred while creating Table. Reason: java.sql.SQLException: Parser Error: syntax error at or near ‘,’”

After debugging, we noticed that the API JSON response contains null values for several keys.

How can we handle this issue and successfully create metadata and reports from the API?

Thanks,
Helical Insight.

#2

Hello,

This issue occurs because null values in the API response can break schema parsing and lead to SQL/parser errors during metadata creation.

Root Cause
• API response contains fields like:

{
  "name": null,
  "status": null
}

• Helical Insight may fail to interpret such fields properly during schema generation
• This results in parsing errors like:
syntax error at or near ‘,’

Solution: Use transformResponse Property
Helical Insight provides a property called:

transformResponse

• Default value: false
• When set to true, it allows you to apply custom transformation logic using Groovy

Steps to Fix

  1. Enable transformResponse
    “transformResponse”: true

  2. Add Groovy Script to Handle Null Values

Use the following script to replace all null values with “NA”:

import com.fasterxml.jackson.databind.JsonNode
import com.fasterxml.jackson.databind.node.ObjectNode
import com.fasterxml.jackson.databind.node.JsonNodeFactory

JsonNode transformResponse(JsonNode data) {

    def factory = JsonNodeFactory.instance

    if (data.isArray()) {
        data.each { element ->
            if (element.isObject()) {
                element.fieldNames().each { field ->
                    if (element.get(field).isNull()) {
                        element.put(field, "NA")
                    }
                }
            }
        }
    }

    return data
}

image

What this does

• Iterates through API response
• Detects fields with null values
• Replaces them with “NA”
• Ensures consistent and parseable data structure

Result

After applying this:

• Connection is created successfully
• Metadata generation works
• Reports can be built without errors

Alternative Approach

• Fix API to avoid nulls (if possible)
• Or replace nulls with default values at source

Further reading (how to connect and use API as a datasource): https://www.helicalinsight.com/connect-and-use-an-api-as-a-data-source-in-helical-insight-5-0/

Thank You,
Helical Insight.