Welcome Guest, you are in: Login

QVSource: The QlikView & Qlik Sense API Connector (Docs)

NOTE: The following has not yet been updated to reflect the upcoming changes in QVSource 1.6.5.3 and later outlined here.


Request Types

There are now three (technically four if 'Auto' is included) different types of request which can be made using QVSource:
  • Synchronous
  • Asynchronous
  • Batch
  • Auto*

These are explained in more detail below. Auto is a special case because it essentially requests that QVSource make the best decision as to which mode to use and so results in one of the other three being used.

The main reason for these additional modes is to extract the maximum performance from QVSource and minimise load time of the QlikView application. Often in QVSource related QlikView script, for/next loops are constructed to run a large number of API requests. The classic example - illustrated below - of this is running a large number of text messages through the Sentiment Analysis & Text Analytics Connector, something which can take a great deal of time. There are many other examples and in fact, QVSource related QlikView script is often characterised by for/next loops.


LET noRows = NoOfRows('Tweets');
   
for i=0 to $(noRows)-1
	
     let id = peek('Search_id', $(i), 'Tweets');
     let textEncoded = peek('text_urlEncoded', $(i), 'Tweets');
	
     Sentiment:
     LOAD
    	'$(id)' as Search_id,
    	status as sentiment_status,
    	score as sentiment_score
     FROM
     [http://localhost:5555/QVSource/TextAnalyserV2/?table=Sentiment_Sentiment140&text=$(textEncoded)&appID=$(vQVSourceAppId)format=qvx]
     (qvx);
next

In the above example, each message would be processed sequentially, meaning that the sentiment score for the next message cannot be calculated until the current request has completed. If we estimate each request takes 250ms then we could be looking at 40 minutes to score 10,000 Tweets (or Facebook posts or whatever) for sentiment.

The thing is, many APIs allow multiple requests to be made in parallel from the same client and these requests will be handled by separate processes on the server (or cloud) allowing many more requests to be processed per second. It is the Asynchronous mode introduced in QVSource which allows us to take advantage of this feature.

We have already done some testing with asynchronous/parallel requests to many of the sentiment APIs QVSource connects to and shown some huge performance increases using this technique

Here are the results of a couple of test runs scoring 500 Tweets and showing the large performance improvements seen:

Image

Image

Further to this, some APIs also allow multiple requests to be combined into a single API call (i.e. http request), for example sending 1000 text messages in a single request to be processed for sentiment. This is often referred to as sending requests in a batch and it is the Batch mode introduced in QVSource which allows us to take advantage of this feature.

There are some additional benefits of the functionality discussed below which also applies to the 'traditional' synchronous style requests:

  • As mentioned, QVSource related QlikView script often contains for/next loops to loop through data already loaded into the application and then use this data as the basis for API requests via QVSource. Another example of this would be looping through a table of Twitter search terms and sending each query to the Twitter Connector's Search table. As you will see when you read on, the new functionality discussed below allows, in more cases, for these for/next loops to be completely eliminated, which makes the QlikView script shorter, cleaner and easier to read and maintain.

  • In the above script example note that the text needs to be URL encoded before being sent to the API (this is so that certain characters, for example the #, do not break the GET request to QVSource). This means either having additional complex QlikView script to URL encode the content or ensuring that it was URL encoded before loading into QlikView. The new functionality below makes this requirement redundant.

  • Similarly, in the above example, very long messages would occasionally break the request due to some maximum URL length (which we believed QlikView to be enforcing). Again, the new functionality below makes this issue redundant.

  • If the QlikView script execution was being logged to a file, for/next loops with a large number of iterations could create huge log files which is avoided with the techniques shown below.

  • In the above example you are constrained to needing your list of data for the for/next loop already in the QlikView data model. With the method outlined below you can take this data from any file location or http/https web url.

Synchronous Requests

If you have been using QVSource prior to 2013 it is likely that you have only ever used QVSource to make 'synchronous' requests, although some of our users may have started using the recently introduced asynchronous mode which was the basis of much of the new features discussed below.

Synchronous requests are the default mechanism and all tables of all connectors support this. It basically means that a request to QVSource results in one (or more) sequential requests to the underlying API as explained above and is the script you receive when copying the standard generated script from QVSource:

Image

As a side effect of the new modes introduced however it is now possible to simplify typical for/next loops such as this one:


LET noRows = NoOfRows('Tweets');
   
for i=0 to $(noRows)-1
	
     let id = peek('Search_id', $(i), 'Tweets');
     let textEncoded = peek('text_urlEncoded', $(i), 'Tweets');
	
     Sentiment:
     LOAD
    	'$(id)' as Search_id,
    	status as sentiment_status,
    	score as sentiment_score
     FROM
     [http://localhost:5555/QVSource/TextAnalyserV2/?table=Sentiment_Sentiment140&text=$(textEncoded)&appID=$(vQVSourceAppId)format=qvx]
     (qvx);
next

Into the following:


let vQVSourceAppId = 'test_app';
let vWorkingFolder = 'c:\QVSource\Temp\';
let vParamsFile = '$(vWorkingFolder)$(vQVSourceAppId)' & '_SentimentArgs.txt';

Params:
LOAD
	Search_id as rowKey,
	Search_text as text
resident
Tweets;

store Params into '$(vParamsFile)' (txt);
drop table Params; // Optional.

Sentiment:
LOAD
	rowKey as Search_id,
	status as Sentiment_status,
	score as Sentiment_score,
	language as Sentiment_language
FROM
[http://localhost:5555/QVSource/TextAnalyserV2/?table=Sentiment_Sentiment140&
processParamsSync=$(vParamsFile)&appID=$(vQVSourceAppId)&format=qvx]
(qvx);

Here are the steps in getting to this script:

1) Instead of a for/next loop we simply create a temporary table named Params (the table can be called anything) containing the same set of parameters which are passed in each iteration of the for/next loop above. In this case it contains the parameters text and api. It also has an additional column named rowKey which we set to the Tweet id. This is so we can link it back into the data model when the results come back. Note that there are certain parameters you cannot and should not put in the Params table. In particular the 'table' or 'format' parameters, these should be in the final QVSource request. See here for a complete list.

Note that the parameters in this Params table should not be url encoded.

2) We now save this table to a CSV file. It is important that this CSV file can be read by QVSource (e.g. the user account running QVSource must have read access to it) and of course it must also be somewhere QlikView has permission to write it. In our example above (and generally in our demo applications we store these CSV files into a subdirectory named \Temp in the folder where QVSource is running (e.g. c:\QVSource\Temp) so we just need to ensure that QlikView has write access to this folder.

3) Finally, we make a fairly typical request to QVSource except that instead of passing the usual parameters we pass the path of the previously created CSV file using the processParamsSync parameter. We also read back the rowKey and alias it back to the Tweet id.

In the background, QVSource will open the CSV file and process each row sequentially. Note also that we did not need the text to be URL encoded and in general the script is a little more tidy and readable.

You can now apply the above pattern to almost any QVSource request and it is the basis of the Asynchronous, Batch and Auto modes discussed below.

As a final note, as of QVSource 1.4.0.8 and above the above processParamsSync request will also accept rowKey2 and rowKey3 parameters.

Asynchronous Requests

IMPORTANT: You can use this mode if the table does not explicitly prohibit it. Most tables will show a question mark for this feature as illustrated below:

Image

This means we haven't explicitly tested or verified whether this table supports asynchronous requests or not. If there is not a tick here then we will not officially support this functionality however there is a reasonable chance it will work.

Some tables may explicitly prohibit making asynchronous requests as the underlying API does not support it. In this case you will see the following against the table:

Image

If the table prohibits asynchronous requests and you attempt to use it the request will fail immediately.

In order to make asynchronous requests, you simply need to change the parameter name processParamsSync into processParamsAsync:


Sentiment:
LOAD
	rowKey as Search_id,
	status as Sentiment_status,
	score as Sentiment_score,
	language as Sentiment_language
FROM
[http://localhost:5555/QVSource/TextAnalyserV2/?table=Sentiment_Sentiment140&
processParamsAsync=$(vParamsFile)&appID=$(vQVSourceAppId)&format=qvx]
(qvx);

QVSource will then attempt to run the requests on a number of parallel threads and the above call will only complete when all the requests have finished.

As a final note, as of QVSource 1.4.0.8 and above the above processParamsAsync request will also accept rowKey2 and rowKey3 parameters.

Specifying The Number Of Threads

By default QVSource will (currently) use a maximum of 30 parallel threads.

You can set an alternative value, currently between 1 and 50, by including a maxThreads=VALUE parameter in the above request.

Batch Requests

IMPORTANT: You can only use this mode if the table explicitly supports and (and at time of writing most connectors do not - the Sentiment tables of the Sentiment140 and Repustate API options in the TextAnalyserV2 Connector are the only current exceptions).

In order to ascertain if the table supports batch requests look for the following indication below the table in the QVSource UI:

Image

If the table does not support batch requests and you attempt to use it the request will fail immediately.

In order to make batch requests, you simply need to change the parameter name processParamsSync into processParamsBatch:


Sentiment:
LOAD
	rowKey as Search_id,
	status as Sentiment_status,
	score as Sentiment_score,
	language as Sentiment_language
FROM
[http://localhost:5555/QVSource/TextAnalyserV2/?table=Sentiment_Sentiment140&
processParamsBatch=$(vParamsFile)&appID=$(vQVSourceAppId)&format=qvx]
(qvx);

QVSource will then attempt to run the requests using the Batch feature which the API provides and the above call will only complete when all the requests have finished.

Auto Requests

If you would like QVSource to decide the best method then you can change the parameter name processParamsSync into processParamsAuto:


Sentiment:
LOAD
	rowKey as Search_id,
	status as Sentiment_status,
	score as Sentiment_score,
	language as Sentiment_language
FROM
[http://localhost:5555/QVSource/TextAnalyserV2/?table=Sentiment_Sentiment140&
processParamsAuto=$(vParamsFile)&appID=$(vQVSourceAppId)&format=qvx]
(qvx);

QVSource will then use the following rules:
  • If the table supports batch mode, QVSource will use this method.
  • If the table explicitly supports asynchronous mode, QVSource will use this method.
  • Otherwise QVSource will use the standard synchronous mode.

This mode is not currently recommended as very few connectors yet explicitly support asynchronous mode (even though this mode might work well) and so would invariably fall back on the slowest synchronous mode.

It is therefore currently recommended that you look at each table you are using in turn and decide whether to use synchronous, asynchronous or batch mode. The Auto mode is likely to become more powerful in the future as we review and test more connectors and code them to make batch requests or explicitly configure them to support asynchronous mode.

Advanced Notes

Using Urls instead of File Paths

In the above, whenever you see a processParams????? parameter, not only can you pass a file location to this but you can also pass a url beginning with http or https.

Special Parameters

Certain parameters are considered special by QVSource, usually because they can be used by any QVSource Connector. These parameters should not be placed in the params CSV file but specified on the request url for the table.

These are listed below:

table

This is the name of the table you will be requesting after you have create the Params table and saved it to CSV file so this should only be included in the final request.

format

This tells QVSource what format to return the data in (e.g. qvx, html etc.). As all the data for a given request must be returned in the same format this cannot appear in the params file. In almost all cases this should be set to qvx.

appID

In the above script samples you might notice the appID= parameter. This is in no way related to the other content on this page and is indeed included in all generated QlikView script now. See this page for more information.

noCache

This is a parameter supported by certain connectors which causes QVSource to ignore the cache completely.

requestStamp

Some tables in some connectors allow you to specify an id for a request which you can then use subsequently to access follow on data from another table related to the request.

One example is the Twitter Connector - you can see more information about this example here.


(QVSource works with Qlik Sense as well as QlikView - See this page for notes.)
QVSource - The QlikView & Qlik Sense API Connector | © Copyright 2011 - 2016 Industrial CodeBox Ltd