Qlik Community

Qlik Sense Enterprise Documents & Videos

Documents & videos about Qlik Sense.

How to access QRS (Repository) from Load Script

Employee
Employee

How to access QRS (Repository) from Load Script

(Qlik Sense Server only, Qlik Sense Desktop has no Repository)

There are situations where you would like to know something from the system configuration (QMC) from inside of a load script, something like "Which custom attributes does my app have assigned?" Or "Which stream is the current app in?" I've seen the "bad" idea to connect to the Postgre repository database and read from the internal table structure. This internal structure may, and is likely to, change, so I would not build on this hack.

The correct way to do it is to use the QRS API (Qlik Repository Service). In theory, the QRS API operates on the https port 443 as well as on port 4242. If you used the latter, you have to present the public key of the server certificate, which cannot be done from the load script. In order to use the 443, you need to authenticate as a privileged user towards the server - your best bet from the script is to use the header authentication (pass the userid via http-header) and this requrires the setup of a Virtual Proxy on the server by a QMC admin.

  1. Setup a new Virtual Proxy with Header Authentication
  2. Use Postman or another tool to test the qrs endpoint response before going to the load script
  3. Load Script: Use REST Connector with CONNECTION parameter

Step 1 - Setup Virtual Proxy

We want the script to be able to query the QRS api, so we have to give it a new "door" to authenicate. Set up a new Virtual Proxy like this: There will be a static pseudo user directory which we call "APIUSERS", whereas the user id itself will be provided in a http-header.

2018-10-08 10_14_24-qmi-qs-aai [wird ausgeführt] - Oracle VM VirtualBox.png

Dont forget to

  • add the Server node(s) under "Load balancing"
  • link this virtual proxy to a Proxy in the "Associated Items".

2018-10-08 10_24_07-qmi-qs-aai [wird ausgeführt] - Oracle VM VirtualBox.png

Note: We will have to come back to QMC/Users once again after making the first call using the header.

Step 2 - Test the qrs endpoint with header authentication

It is literally impossible to go straight to the REST connector from the script if you haven't tried the results from a tool that shows all response details (return code, possible error messages etc). My tool of choice is "Postman" (the app, not the Chrome-Plugin of the same name)

Lets use the server url + /header/ (this is the virtual proxy we created) + /qrs/about + &xrfkey= and a 16 digit combination, which you repeat in the http-headers under the key "X-Qlik-xrfkey" again. Also provide the userid as a 2nd http-header

2018-10-08 10_36_45-Postman.png

You should get an answer like above in Json format. This means, you made it through the virtual proxy. A new user has been added to the QMC, namely the id "scriptload" (coming from the http-header) with the directory "APIUSERS" (coming from the virtual proxy setting).

Go back to the QMC and provide the new user the Role "AuditAdmin"

2018-10-08 10_41_01-qmi-qs-aai [wird ausgeführt] - Oracle VM VirtualBox.png

Now you can also query some details about the app. Try the following request, before we finally go to the Load Script. -.../qrs/app/full should provide all information about apps, including possible tags.

2018-10-08 10_32_53-Postman.png

In order to get the details about one specific app you should use the "filter=" query-string, the syntax is like the Json object key, a comparison operator, and a constant. In my case: id eq <app-guid>

eq is "equals", don't use "=" which is a reserved character already used at "filter="

If this works fine, lets edit the app script.

3 - Create REST Connector call from Load Script

Go to the app's dataload editor and create a new connection of type "REST".

The principle I follow here is:

  1. get over the initial dialog with hard-coded query-strings and http-headers to get the qrs call working (same Method as later, GET for GET, POST for POST)
  2. load the data once
  3. manipulate the created script with "WITH CONNECTION" argument and soft-code all the parameters to make it dynamic

Clipboard.gif

Then load the data using the "Select Data" button in the Data connections list:

2018-10-08 11_01_32-qmi-qs-aai [wird ausgeführt] - Oracle VM VirtualBox.png

You can expand the Json response tree and deselect what you don't need. Be aware that each tree branch, which expands, will become a separate script table, so be conservative with what to load using a "need to know" judgement.

2018-10-08 10_59_52-qmi-qs-aai [wird ausgeführt] - Oracle VM VirtualBox.png

Insert the script. You will see the block called "RestConnectorMasterTable: SQL SELECT ..." and then several LOAD statements (in our case 4, one for the root and 3 childs which we selected above)

Until this point, the script is fully static. It only loads exactly this QRS endpoint and always load all apps.

Now lets bring some dynamics in. Find the last row of the first block where it reads FROM JSON (...) "root" PK "__KEY_root";

2018-10-08 11_06_55-qmi-qs-aai [wird ausgeführt] - Oracle VM VirtualBox.png

Before the semicolon insert (in a new line) WITH CONNECTION ()

With this parameter you can soft-code and overwrite any hard-coded entry from the "Edit Connection" dialog. If you omit a parameter in WITH CONNECTION, the original hard-coded entry from the Edit Connection dialog will be taken. You can also add more parameters using WITH CONNECTION. Use the WITH CONNECTION script as follows:

  • If you want to overrule the endpoint address use URL "https://...." option
  • If you want to add another Query-string (filter for example) use QUERY "filter" "id eq cde61c53-b536-46f3-8538-6470042e4160" (QUERY takes 2 more parameters, key and value)
  • If you want to add another header-key use HTTPHEADER
  • Note: You can NOT change the method (GET/POST), you need another data connection if you have to use the other

Here is my example of getting the info just for the current app:

Add this row before the "LIB CONNECT TO ..." statement:

LET vAppId = DocumentName();

This will assign the app GUID to the variable.

Modify the last row of the first block as follows:

FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION (

URL "https://qmi-qs-aai/header/qrs/app/full"

    ,QUERY "filter" "id eq $(vAppId)" 

);

Note: the variable $(AppId) is slided into the filter. QUERY xrfkey;

When you execute the script, you will get the app info from the QRS API loaded as a table datamodel:

2018-10-08 11_28_40-qmi-qs-aai [wird ausgeführt] - Oracle VM VirtualBox.png

Now you can use some of your scripting skills to get, for example, to check which tags the given app has and do something:

FOR v = 1 TO FieldValueCount('name_u0')

    LET vTag = FieldValue('name_u0', 1);

    IF vTag LIKE 'byCSW' THEN

        TRACE --- this app has the tag of the tags ---;

    END IF

NEXT v

More Advanced Script Example or QRS API

In the following example I am loading the list of all tasks for a given app. For the tasks I also want a 1:n-List of Custom Properties (if any) and Tags (if any). The returned Json structure would be complex (8 tables for the qrs endpoint /qrs/reloadtask/full):

2018-10-08 14_22_37-qmi-qs-aai [wird ausgeführt] - Oracle VM VirtualBox.png

Deselect what you don't need. I also unchecked a lot of unneeded columns. You better spend a few minutes in the "select data" dialog of the Data Connector than in the script below.  To see which fields I skipped I commented them out in the code below. I also introduced "nice field names" like "tag.name" or "task.id" instead of auto-generated names such as "name_u4", "id_u6" ...

Finally, I joined some tables to have only 3 relevant ones after this.

2018-10-08 14_17_13-qmi-qs-aai [wird ausgeführt] - Oracle VM VirtualBox.png

Here is the sample script:


LET vAppId = DocumentName();

LET vXrfkey = Left(KeepChar(Repeat(Rand(),10),'0123456789'), 16);

TRACE Onetime Xrfkey = $(vXrfkey);

LIB CONNECT TO 'QRS full app info (qmi-qs-aai_vagrant)';

RestConnectorMasterTable:

SQL SELECT

   "id" AS "id_u6",

// "createdDate" AS "createdDate_u0",

// "modifiedDate" AS "modifiedDate_u0",

// "modifiedByUserName" AS "modifiedByUserName_u0",

// "isManuallyTriggered",

   "name" AS "name_u2",

   "taskType",

   "enabled",

// "taskSessionTimeout",

// "maxRetries",

// "privileges" AS "privileges_u5",

// "schemaPath" AS "schemaPath_u0",

   "__KEY_root",

   (SELECT

   // "id" AS "id_u0",

   // "createdDate",

   // "modifiedDate",

   // "modifiedByUserName",

      "value",

   // "schemaPath",

      "__KEY_customProperties",

      "__FK_customProperties",

      (SELECT

      // "id",

         "name",

      // "valueType",

      // "privileges",

         "__KEY_definition",

         "__FK_definition"

      FROM "definition" PK "__KEY_definition" FK "__FK_definition")

   FROM "customProperties" PK "__KEY_customProperties" FK "__FK_customProperties"),

   (SELECT

      "id" AS "id_u1",

      "name" AS "name_u0",

   // "appId",

   // "publishTime",

   // "published",

   // "stream",

   // "savedInProductVersion",

   // "migrationHash",

   // "availabilityStatus",

   // "privileges" AS "privileges_u0",

      "__FK_app"

   FROM "app" FK "__FK_app"),

   (SELECT

   // "id" AS "id_u5",

      "name" AS "name_u1",

   // "privileges" AS "privileges_u4",

      "__FK_tags"

    FROM "tags" FK "__FK_tags")

FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION (

   URL "https://qmi-qs-aai/header/qrs/reloadtask/full"

   ,QUERY "filter" "app.id eq $(vAppId)"

   ,QUERY "xrfkey" "$(vXrfkey)"

   ,HTTPHEADER "X-Qlik-Xrfkey" "$(vXrfkey)"

// ,HTTPHEADER "userid" "scriptload"

);

[root]:

LOAD

   [id_u6] AS [task.id],

// [createdDate_u0] AS [createdDate_u0],

// [modifiedDate_u0] AS [modifiedDate_u0],

// [modifiedByUserName_u0] AS [modifiedByUserName_u0],

// [isManuallyTriggered] AS [isManuallyTriggered],

   [name_u2] AS [task.name],

   [taskType] AS [task.taskType],

   [enabled] AS [task.enabled],

// [taskSessionTimeout] AS [taskSessionTimeout],

// [maxRetries] AS [maxRetries],

// [privileges_u5] AS [privileges_u5],

// [schemaPath_u0] AS [schemaPath_u0],

   [__KEY_root] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__KEY_root]);

LEFT JOIN

//[app]:

LOAD

   [id_u1] AS [app.id],

   [name_u0] AS [app.name],

// [appId] AS [appId],

// [publishTime] AS [publishTime],

// [published] AS [published],

// [stream] AS [stream],

// [savedInProductVersion] AS [savedInProductVersion],

// [migrationHash] AS [migrationHash],

// [availabilityStatus] AS [availabilityStatus],

// [privileges_u0] AS [privileges_u0],

   [__FK_app] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_app]);

[customProperties]:

LOAD

// [id_u0] AS [id_u0],

// [createdDate] AS [createdDate],

// [modifiedDate] AS [modifiedDate],

// [modifiedByUserName] AS [modifiedByUserName],

   [value] AS [customProp.value],

// [schemaPath] AS [schemaPath],

   [__KEY_customProperties] AS [__KEY_customProperties],

   [__FK_customProperties] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_customProperties]);

LEFT JOIN

//[definition]:

LOAD

// [id] AS [id],

   [name] AS [customProp.name],

// [valueType] AS [valueType],

// [privileges] AS [privileges],

   [__KEY_definition] AS [__KEY_definition],

   [__FK_definition] AS [__KEY_customProperties]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_definition]);

[tags]:

LOAD

// [id_u5] AS [id_u5],

   [name_u1] AS [tag.name],

// [privileges_u4] AS [privileges_u4],

   [__FK_tags] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_tags]);

DROP TABLE RestConnectorMasterTable;

DROP FIELDS [__KEY_definition], [__KEY_customProperties];


Tags (2)
Comments
arvind_patil
Valued Contributor III

good info

0 Likes
Partner
Partner

Thanks Christof

Works perfectly with POST method

0 Likes
oknotsen
Honored Contributor III

Thank you! Steps still work fine with Feb 2019. Only a minor (visual) change in the REST Connector dialog, but no different information needed.

0 Likes
Partner
Partner

Easy to follow steps . Thanks a lot Christof.

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2018-10-08 04:33 AM
Updated by: