Connect and use an API as a data source in Helical Insight 5.0

Open source BI product Helical Insight from it’s Enterprise Edition 5.2.2 onwards is providing support for connecting to and using REST APIs as a data source. We support GET and POST requests. In this tutorial we would be covering how to use API as a data source.

We are providing detailed information on how to connect to and use API data source:

1. Log in to your Helical Insight application and go to the “Data Sources” module.

2. Once you are on “Data Sources” module, you will see many options to connect to different databases. Out of that you need to choose “API”. Reference image is provided below:

3. Once you click on ‘API‘ and choose ‘Create‘ a popup will open. A reference image is provided below

4. In Host we should give API URL

Example: https://dummyjson.com/products

5. In the “Datasource Name” section, we can provide any name of our choice and the connection name will be saved and listed with that name.

6. In the ‘Configuration Editor‘ we need to provide the details based on the type of API you are using.

The configurations will vary based on API. All the sample configuration details are provided on the icon next to the ‘Configuration Editor’ An image is provided below for reference.

Groovy Editor: We can set properties dynamically by writing logic in Groovy.

Example: A script to set the Authorization token dynamically in headers.

Explanation of configuration:

	{
			"name" : "",
			"url": "https://dummyjson.com/auth/me", // not using right now , may use in future , value of the url will be taken from the provided jdbc url
			"strategy" : "in-memory",  // Allowed values [in-memory or persistent]
			"persistentLocation" : "",  // Blank in case of in-memory, otherwise we will need to provide the value wherein the data will get persisted]
			"preExecuteScript" : "",
			"libPaths" : [""],
			"authType": "jwt",  // Allowed values [basic, jwt, api-key] 
			"headers" : {
				"Authorization" : ""
			},
			"queryParams": {},
			"params": [],
			"postBody": {},
			"dataPath": "",
			"method" : "", // Allowed values [GET,POST]
			"requireTail": false, // Allowed values [true,false]
			"username" : "",
			"password" : "",
			"timeout" : 1000,
			"basicAuthScheme" : "BASIC",
			"requireTailCharacterMapping" : { "QM" : "?" , "AMP" : "&" },
			"sample": "", // Default sample size 10
			"schema": ""
		}

Explanation for Each Property:

Name :

The name of the connection. Useful for querying the API. If not provided, the base URL (unsupported characters like dots will be excluded) will be considered as the connection name.
     Example: “name”: “sample_connection”

URL: The base or full URL of the REST API endpoint depends on the requireTail property.

If requireTail is true, provide the full path.
If requireTail is false, provide the base URL without the tail (query parameters).
Example:

jdbc:https://example.com

persistentLocation: If the strategy is persistent, the path of the database file should be provided here.

Example: c:\\dbs\\test.db

preExecuteScript: The name of the Groovy script with its extension.

Example: preExecuteScript: token_generator.groovy

Note: Set the authType to jwt when using the setToken method.

libPaths: Directories containing the dependency files referenced in the Groovy script.

Example: libPaths: ["/home/helical/libs", "/home/helical/jars"]

authType: authentication type of endpoint. Currently supporting, jwt, basic, api-key endpoints, if auth not required leave it blank

Allowed value [jwt,basic,api-key]

headers: provide headers of the request here.

    Ex :

    	{
    		....
    		"Authorization" : "Bearer ",
    			__or__
    		"Authorization" : ""
    		....
    	}

If Authorization mentioned in Headers , please setlect jwt as authType

queryParams: provide query params of the endpoint here

    Ex : https://baseUrl?queryParam1=value&queryParam2=value2

    	"url" : "https://baseUrl",
    	"queryParams" : {
    		"queryParam1" : "value",
    		"queryParam2" : "value"
    	}

Params: specify the parameters as filters

Example :

    SELECT *
    FROM sunrise
    WHERE lat = 36.7201600
    	AND lng = -4.4203400
    	AND date = '2019-10-02'

postBody: provide request body , leave it blank if not required.

Note: Currently supporting json body only.

Ex:

		{
			...
			"postBody" : {
				"key" : "value"
			}
			...
		}

dataPath: if there are many objects in json data , provide an object name to consider it to query

Ex: There are 3 objects in json data, to query on results data only

		{
			"results": {
				"sunrise": "6:12:17 AM",
				"sunset": "6:01:54 PM",
				"solar_noon": "12:07:06 PM",
				"day_length": "11:49:37",
				"civil_twilight_begin": "5:47:49 AM",
				"civil_twilight_end": "6:26:22 PM",
				"nautical_twilight_begin": "5:17:51 AM",
				"nautical_twilight_end": "6:56:21 PM",
				"astronomical_twilight_begin": "4:47:41 AM",
				"astronomical_twilight_end": "7:26:31 PM",
				"recorded_time": ["6:12:14 AM","6:12:15 AM","6:12:16 AM"]
			},
			"status": "OK",
			"tzid": "UTC"
		}

dataPath should be $ for root element

dataPath should be $.results for results json

dataPath should be $.results.sunrise for sunrise present inside results json

dataPath should be $.results.recorded_time for recorded_time array present inside results json

dataPath should be $.results.recorded_time[0] for recorded_time array first element present inside results json

dataPath should be $.results.recorded_time[*] for all elements of recorded_time present inside results json

Note: It support nested objects too.

method: currently supporting GET and POST methods

requireTail: [true,false]

if true provide the tail part in query otherwise provide in url

Ex: Query

	SELECT sunrise, sunset 
	FROM sunrise.`?lat=36.7201600&lng=-4.4203400&date=2019-10-02` 
	WHERE sunrise='6:12:17 AM'

Note: tail part should be wrapped inside back quotes

    username: username to authenticate // only for basic auth endpoint

    password: password to authenticate

    timeout: maximum time allowed to send the request to an endpoint

    requireTailCharacterMapping: characters to replace special characters in query.

requireTailCharacterMapping:{"QM" : "?"}

Note: You can choose any name for the key.

Ex: Some Databases/Dialects do not support some special characters in query or some dialects replace them.

	SELECT  * FROM 
	sunrise.`?lat=36.7201600&lng=-4.4203400&date=2019-10-02`

In such cases the query can be re-written as

SELECT  * FROM

sunrise.`QMlat=36.7201600&lng=-4.4203400&date=2019-10-02`

here QM will be replaced with ? during the execution at Database level

sample: The size of the sample

This property is useful to decide the schema, how many data points it should parse to decide the schema.

    Ex:

    {
    	...
    	"sample" : "100"
    	...
    }

schema: manually user defined schema

This property is useful to set the schema like below.

    Ex:

    {
    	...
    	"schema" : "{\"booking_platform\":\"TEXT\",\"travel_id\":\"INTEGER\",\"travel_medium\":\"TEXT\",\"destination_id\":\"INTEGER\",\"destination\":\"TEXT\",\"travel_cost\":\"INTEGER\",\"mode_of_payment\":\"TEXT\",\"source\":\"TEXT\",\"source_id\":\"INTEGER\",\"travel_date\":\"DATETIME\"}";
    	...
    }

Groovy script as configuration :

NOTE: use below code format to set the properties dynamically.

Example: Script to set the Authroization token dynamically in Headers.

Supported methods

String setMethod()
String setDataPath()
Map setHeaders()
String setStrategy()
String setRequireTail()
List setParams()
Map setQueryParams()
String setPassword()
String setPostBody()
String setTableName()
String setUserName()
String setToken()
String setSample()
String setSchema()

/*
 *
 */
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ObjectNode;
import java.util.List;
import java.util.Map;


/**
    §. Comment the unnecessary methods
    §. Precedence of the return values high while setting up the connection configuration
**/

/**
    @return  token String
    Ex: return "bWFzdGhfc2hhZGVzX3VubmF5X25lZWVsb29v";
**/

String setToken() {
    def post = new URL("http://localhost:9091/hi-ee/rest/authToken").openConnection();
    def body = '{"username":"hiadmin","password":"hiadmin"}';
    post.setRequestMethod("POST")
    post.setDoOutput(true)
    post.setRequestProperty("Content-Type", "application/json")
    post.getOutputStream().write(body.getBytes("UTF-8"));
    def postRC = post.getResponseCode();
    if (postRC.equals(200)) {
        String response = post.getInputStream().getText();
        ObjectMapper mapper = new ObjectMapper();
        ObjectNode node = mapper.readValue(response,ObjectNode.class);
        return node.get("token").asText();
    }
    else {
        return "";
    }
}

/**
    @return json string
    Ex: return "{\"key\":\"value\",\"key1\":\"value1\"}";
**/

/**
String setPostBody() {
    return "";
}
**/



/**
    @return username string;
**/

/**
String setUserName() {
   
}
**/




/**
    @retrun password string
**/

/**

String setPassword() {
    return "";
}

**/



/**
    @return strategy string 
**/

/**
String setStratrgy() {
    return "";
}
**/



/**
    @return tablename 
**/

/**
String setTableName() {
    return "";
}

**/



/**

@return headers map

Ex:  Map map = new HashMap<>();
     map.put('Content-Type','application/json')
    
    retutn map;
**/

/**

Map setHeaders() {
    return Map.of();
}

**/

/**

@return Query parameter map

Ex:  Map queryParams = new HashMap<>();
     map.put('page','1');
     map.put('pazeSize','10');
    retutn map;
**/


/**
Map setQueryParams() {
    return Map.of();
}

**/



/**

@return params list

Ex:  List params = new ArrayList<>();
     params.add("lng");
     params.add("lat="),
    retutn params;
**/

/**

List setParams() {
    return List.of();
}

**/

/**
    @return datapath string 

**/

/**
String setDataPath() {
    return "";
}
**/

/**
    @return method
    Ex: return "GET";
        
**/

/**
String setMethod() {
    return "";
}

**/

/**
    @return requireTail string 
        Ex: return "false";
**/


/**
String setRequireTail() {
    return "";
}

**/

/**
    @return  sample size 
        Ex: return "100";
**/

/**
String setSample() {
	return "";
}

**/


/**
    @return  schema json string 
        Ex: return "{\"booking_platform\":\"TEXT\",\"travel_id\":\"INTEGER\",\"travel_medium\":\"TEXT\",\"destination_id\":\"INTEGER\",\"destination\":\"TEXT\",\"travel_cost\":\"INTEGER\",\"mode_of_payment\":\"TEXT\",\"source\":\"TEXT\",\"source_id\":\"INTEGER\",\"travel_date\":\"DATETIME\"}";
**/

/**
String setSchema() {
	return "";
}

**/

Provide the path in configuration file. “preExecuteScript” : “< script-file-name >“

Set authType to jwt

7. We have configured an API and provided below configuration for your reference. You can make changes accordingly to your API and specific requirement.

Host: https://dummyjson.com/products

Datasource Name: Products

{
“name”: “Products”,
“url”: “”,
“strategy”: “in-memory”,
“persistentLocation”: “”,
“preExecuteScript”: “”,
“libPaths”: [“”],
“authType”: “”,
“headers”: {},
“queryParams”: {},
“params”: [],
“postBody”: {},
“dataPath”: “products”,
“method”: “”,
“requireTail”: false,
“timeout”: 1000,
“requireTailCharacterMapping”: {},
“sample”: “”,
“schema”: “”
}

API Response data :

8. Click on Test Connection, it gives the message The connection test is successful
(If there are no issues with configuration) click on Save Datasource

9. Go to the Metadata module page and expand the API data source. It will show the table name that we provided in the data source connection configuration. Drag the table into metadata.

10. Create a report using the metadata and save it

In case of any questions please reach out to support@helicalinsight.com