Calling a REST API

[hafer]

Hi,

Very very new on Qlik, but experienced with development and APIs.

I'm trying to query data from a REST API.

The process is twofold, first authenticate, grab a token in the response, then query the other APIs.

On postman I can authenticate fine, and grab a success json object with the session token I can use to query other APIs.

On click, I get a success=false and an empty token.

[stevedark]

Hi @hafer 

Obviously there are many REST APIs and they behave in many different ways.

To give some ideas on how it might work here are some extracts from a script to get data from the Twitter API. I think the API may have changed since I wrote this (and there is a native Qlik Twitter connector now), so this might not work, but it should give you an idea:

if vBearerKey = '' or IsNull(vBearerKey) or vBearerLastGot < (vNow - vBearerRefreshDays) then
	TRACE Getting New Bearer Key;
    
    LIB CONNECT TO '$(v_POST_Connector)';
    
    tmpBearerKey:
    SQL SELECT
		"token_type",
        "access_token"
    FROM JSON (wrap on) "root" PK "__KEY_root"
	WITH CONNECTION (  
      URL "https://api.twitter.com/oauth2/token",
      HTTPHEADER "content-type" "application/x-www-form-urlencoded",
      HTTPHEADER "Authorization" "Basic $(vBASE64Key)",
      QUERY "grant_type" "client_credentials"
    )
	;

	let vBearerKey = peek('access_token', -1, 'tmpBearerKey');

	DROP TABLE tmpBearerKey;

	let vBearerLastGot = now();
	let vLastBearerKey = vBearerKey;
end if

// Connect to the GET library
LIB CONNECT TO '$(v_GET_Connector)';

RestConnectorMasterTable:
SQL SELECT 
	"created_at" AS "created_at_u0",
	"id" AS "id_u4",
	"id_str" AS "id_str_u4",
	"full_text" AS "text_u3",
	"truncated" AS "truncated_u0",
	"source" AS "source_u0",
	"in_reply_to_status_id" AS "in_reply_to_status_id_u0",
	"in_reply_to_status_id_str" AS "in_reply_to_status_id_str_u0",
	"in_reply_to_user_id" AS "in_reply_to_user_id_u0",
	"in_reply_to_user_id_str" AS "in_reply_to_user_id_str_u0",
	"in_reply_to_screen_name" AS "in_reply_to_screen_name_u0",
	"geo" AS "geo_u0",
	"coordinates" AS "coordinates_u0",
	"place" AS "place_u0",
	"contributors" AS "contributors_u0",
	"is_quote_status" AS "is_quote_status_u0",
	"retweet_count" AS "retweet_count_u0",
	"favorite_count" AS "favorite_count_u0",
	"favorited" AS "favorited_u0",
	"retweeted" AS "retweeted_u0",
	"lang" AS "lang_u0",
	"possibly_sensitive",
	"__KEY_root",
	(SELECT 
		"__KEY_entities",
		"__FK_entities",
		(SELECT 
			"text",
			"__KEY_hashtags",
			"__FK_hashtags"
		FROM "hashtags" PK "__KEY_hashtags" FK "__FK_hashtags"),
		(SELECT 
			"text" AS "text_u0",
			"__KEY_symbols",
			"__FK_symbols"
		FROM "symbols" PK "__KEY_symbols" FK "__FK_symbols"),
		(SELECT 
			"screen_name",
			"name",
			"id",
			"id_str",
			"__KEY_user_mentions",
			"__FK_user_mentions"
		FROM "user_mentions" PK "__KEY_user_mentions" FK "__FK_user_mentions"),
		(SELECT 
			"url",
			"expanded_url",
			"display_url",
			"__KEY_urls",
			"__FK_urls",
			(SELECT 
				"@Value" AS "@Value_u2",
				"__FK_indices_u2"
			FROM "indices" FK "__FK_indices_u2" ArrayValueAlias "@Value_u2")
		FROM "urls" PK "__KEY_urls" FK "__FK_urls")
	FROM "entities" PK "__KEY_entities" FK "__FK_entities")
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION (  
      URL "https://api.twitter.com/1.1/statuses/user_timeline.json?screen_name=$(vTwitterName)&trim_user=true&tweet_mode=extended&count=200$(vMaxTweetURL)",    
      HTTPHEADER "Authorization" "Bearer $(vBearerKey)"
    )
;

The Twitter one is a nice example, as it requires a POST request to get the bearer key (after working out the Base64 version of the basic authentication key) and then a separate GET request, with the bearer key PEEKed out of the first call passed into the second. Many REST connectors are much simpler.

Note that you can create a reasonably generic REST connection in Sense and then change the parameters using WITH CONNECTION in the call to the connector, but you do need separate GET and POST connectors.

Hope that helps.

Steve