Create a REST connection

Access to the Qlik REST Connector is provided by Qlik Sense and QlikView. Qlik Sense connects to data sources through Add data and the Data load editor. QlikView connects to data sources through Edit script.

The difference between Add data and the Data load editor is mainly in how you manage data. Add data provides assistance with creating data associations in your data model. The Data load editor enables you to build a data model with ETL (Extract, Transform & Load) processes using the Qlik data load script language. The script language enables you to perform complex transformations and create a scalable data model.

The QlikView Edit script dialog also enables you to build a data model using the Qlik data load script language.

Specifying the data retrieval method

Do the following:

1. In Qlik Sense, open a new or existing app and select either Add data or Data load editor.
   

   In Qlik Cloud you can select Add data from an app or add as a data source in a space. You can also use the Data load editor.

   In QlikView, open a new or existing app and select Edit Script on the File menu.

2. From Add data, select REST from the list of connectors.

   From Data load editor, select Create a new connection in the Data connections column to get a list of connectors.

   From Edit Script, select QvRestConnector.exe from the list next to Connect... on the Data tab in the lower part of the Edit Script dialog. Then select Connect... to open the REST Connection dialog.

3. Enter the URL of the resource being requested from the REST service.

   The URL syntax must include http://. In addition to the location, the URL can include query parameters and other information about the data being retrieved. The parameters allowed or required are specified by the particular implementation of REST used for the data source. For example, a URL to Google Maps can be:

   http://maps.googleapis.com/maps/api/geocode/xml?address=1600+Amphitheatre+Parkway,+Mountain+View,+CA&sensor=false

4. Enter the Connection Timeout value in seconds.

   This is the time limit for the connection to be completed. If the connection request takes longer than the timeout value, an error message is displayed. The maximum is 1000 seconds. You can input -1 to disable timeout.

5. Enter Read/Write Timeout value in seconds.

   This is the time limit for how long it should take for data to be loaded after a successful connection. If a data reload request takes longer than the timeout value, an error message is displayed. The maximum is 1000 seconds. You can input -1 to disable timeout.

6. Select GET or POST from the Method list.

   The GET method retrieves information from the resource and does not change the state of the server. It is considered a safe method with regard to the server.

   The POST method creates a new entry in the resource. The entry is specified in the Request body field, which appears when the POST method is selected. The request or data sent with the POST method can be, for example, a message to a bulletin board or newsgroup.

   Information noteThe REST Connector sends the POST body without any transformation, even if a transformation is specified in the request header. If you want to encode the POST body, such as with the x-www-form-urlencoded format, you must do it manually. For example, to encode with the x-www-form-urlencoded format, the POST body would be written similar to following:

    

   format=json&start_date=2018-10-30&finish_date=2018-10-31&store=all&username=MarasSam&password=MarasSam0517

7. Enter the data to be posted to the resource with the POST method in the Request body field.

   If you use the GET method, this field is not displayed.

8. Select the HTTP Protocol version.
   Version 1.1 is the default selection because it is the newest version, and provides an automatic persistent HTTP connection. You can select 1.0 if your target web server requires that version, which also requires you to manually select whether to send keep-alive messages.

9. Select Add 'Expect: 100-continue' header if you have selected the POST method and the server you are posting to uses the 100 (Continue) initial response.

   When communicating with servers that respond with 100 (Continue), the initial request must include Expect: 100-Continue instead of sending the entire content of Request body. The content to be posted is then sent in the second request after the server responds with 100 (Continue).

10. Select Auto detect response type to detect the data format in the REST data source when the connection is made.

    If the data format is detected during connection, the Select data to load screen displays the fields required for selecting data in the source’s format when it opens. If Auto detect response type is not selected, you can use Auto detect on the Select data to load screen, or you can select the format manually if you know what it is.

11. Select Check response type during 'Test connection' to include verification that correct data format is returned.

    The Test Connection button at the bottom of the connection dialog in both the Qlik Sense Data load editor dialog and the QlikView Edit script dialog verifies that the connection to the data source can be made and that the credentials are valid. When Check response type during 'Test connection' is selected, Test Connection also validates that the correct data format (JSON, CSV, or XML) is returned.

    In Qlik Sense Add data, the connection is tested automatically before proceeding to data selection. The test connection step is described below. Test Connection.

12. Select Key generation strategy to use for creating primary and foreign keys in JSON and XML data.

    To associate parent and child fields appropriately, the connector generates ID fields to serve as primary and foreign keys. The key generation strategy determines how the keys are generated.

    • Sequence ID: Generates an ID for each child of a parent record. This strategy is simple and efficient, but it requires that the order of fields is the same in every record.
    • Current record: Generates a hash key based on all the values of the current record and uses that as the primary key. It also generates a hash key based on all the values of the direct parent and uses that as the foreign key.
    • Fully qualified record: Generates a hash key based on all the parent values.
    • No keys: Keys for parent-child relationships will not be shown in the Select data to load step, but they will be used in the SELECT statements.

13. Select XML DTD support to load an XML file with a DTD header.

    • Prohibited: A DTD header is prohibited and XML files with a DTD header will cause an unexpected DTD header error.

    • Internal Document: This option is for a DTD header that includes the entire definition in the XML.

    • Document in URL: This option is for a DTD header that includes a URL in the XML to link to the web available definition.

      Information noteThe REST Connector will not process or parse the DTD header of the XML document.

Specifying credentials for the REST service on Windows

Do the following:

1. Select an authentication mode from the Authentication Schema list.

   Authentication schema options

   Schema	Description
   Anonymous	Does not provide any authentication. Everyone using the connection has access to the resource.
   Basic	Sends a Base64-encoded string with a user name and password for the client. Base64 is not a form of encryption. It is the same as sending the user name and password in clear text.
   Digest	Provides a challenge-response. The server sends a string of random data called a nonce, and the server responds with a hash that includes the user name, password, and nonce along with additional information. The complexity of the response makes it difficult to steal and reuse the credentials. The Digest schema requires the use of Windows domain accounts. The digest realm is the Windows domain name. A server cannot use Digest authentication unless it is running an operating system that supports Windows domains. In the case of a client that does not run an operating system with Windows domain support, the domain account must be explicitly specified during the authentication.
   Windows NTLM	Provides a challenge-response that is more secure than the Digest schema. NT LAN Manager (NTLM) uses Windows credentials to transform the challenge data instead of the unencoded user name and password. NTLM requires multiple exchanges between the client and server. The server and any intervening proxies must support persistent connections to successfully complete the authentication.
   Current Windows User (NTLM)	Uses the authentication of the user whose context is used to start the REST Connector. The Current Windows User (NTLM) option can only be used with Qlik Sense Desktop. If the Current Windows User (NTLM) option is selected in the Qlik Sense Enterprise or Qlik Cloud environments, you will get an error message.

   Warning notePreviously, users were asked to if they wanted to use the Windows credentials on the local machine to connect to the REST service. Selecting Yes was equivalent to selecting Current Windows User (NTLM). If you are using Qlik Sense Desktop and the previous selection was No for Windows authentication, you will now get an error message. You must select from the current authentication options.

   If you are using Qlik Sense Enterprise and the previous selection for using Windows credentials was Yes, you will now get an error message indicating that you cannot use Windows authentication. You must use one of the HTTP authentication schemas: Anonymous, Basic, Digest, or Windows NTLM.

2. Enter the user name and password for the REST account.

   These two fields do not appear if you have selected Anonymous or Current Windows User (NTLM) because they are not needed.

3. Use the Certificate validation dropdown to select one of two options:

   • Select Skip server certificate validation if you do not need to verify that the web site server's public certificate is valid. The server certificate is checked even if you select Skip server certificate validation, but in that case, the connection will be made even if the certificate is invalid.

   • Select Use System Trust Store if you need the web site server’s certificate to be validated using root certificates installed in a system’s trust store.

4. If you are using mutual SSL/TLS on Qlik Sense Enterprise on Windows:

   • Select Installed or From file for the Use certificate property to use an X509 certificate for mutual authentication.

     Information noteThe Use certificate connection property does not appear when you are using Qlik Cloud.

     If you select Installed, additional properties are displayed for selecting the certificate location, the certificate store, and the certificate name. The certificate location can be either the current user or local machine.

     Warning noteWhen the certificate location is set to Local machine, Qlik Sense Desktop must be run with administrator privileges. If the user does not have administrator privileges, the REST Connector will not be able to access the certificate, and an error message will be displayed.

     The certificate store is one of the default Windows stores (Personal, Trusted Root Certification Authorities, Intermediate Certification Authorities, Trusted Publisher, Untrusted Certificates, Third-Party Root Certification Authorities, Trusted People, or Other People). The list of certificate names is derived from the certificate store.

     If you select From file, properties are displayed for specifying the PFX file name and the PFX file password. The PFX file name property requires the file name of the PFX file. It does require the full path of where the PFX file is stored.

     Information noteBy default, the PFX file should be stored in C:\ProgramData\Qlik\Sense\Engine\Certificates\. If From file is selected when using Qlik Sense Desktop, this path must be created manually.

     The location of the PFX file can be changed with the following parameter in the QvRestConnector.exe.config file.

     <add key="CertificateDirectoryPath" value="C:\ProgramData\Qlik\Sense\Engine\Certificates"/>

     The config file is located in C:\Program Files\Common Files\Qlik\Custom Data\QvRestConnector on a Qlik Sense server installation. On a Qlik Sense Desktop installation, the config file is located in C:\Users\[username]\AppData\Local\Programs\Common Files\Qlik\Custom Data\QvRestConnector.

     Warning noteWhen the certificate location is set to Local machine, QlikView must be run with administrator privileges. If the user does not have administrator privileges, the REST Connector will not be able to access the certificate, and an error message will be displayed.
5. If you are using mutual SSL/TLS on Qlik Sense SaaS:

   1. Select Enable mutual SSL/TLS check box.

   2. Use File button to select a PXF file containing a private key.

   3. Use PFX File Password field to provide a password of PFX container.

6. Specify Trusted locations by adding a URL with a port number to the Value field and an alias for the host identified by the URL to the Name field. If you specify multiple trusted locations, the alias names must be unique.

   The list of hosts specified as Trusted locations are the only hosts to which the user credentials can be sent. If a request is sent to a host that is not a trusted location, the user credentials will not be given to that host.

7. If you are using the Data load editor or Edit script, you can click Test Connection to verify that a connection can be made with the REST service properties entered.

   In both Data load editor and Add data, the connection is tested before it is created. Clicking Test Connection in the Data load editor enables you to test the connection before you attempt to create it.

   In Edit script, the connection is not tested unless you click Test Connection.

Specifying credentials for the REST service on Qlik Sense SaaS

Do the following:

1. Select an authentication mode from the Authentication Schema list.

   Authentication schema options

   Schema	Description
   Anonymous	Does not provide any authentication. Everyone using the connection has access to the resource.
   Basic	Sends a Base64-encoded string with a user name and password for the client. Base64 is not a form of encryption. It is the same as sending the user name and password in clear text.
   Digest	Provides a challenge-response. The server sends a string of random data called a nonce, and the server responds with a hash that includes the user name, password, and nonce along with additional information. The complexity of the response makes it difficult to steal and reuse the credentials. The Digest schema requires the use of Windows domain accounts. The digest realm is the Windows domain name. A server cannot use Digest authentication unless it is running an operating system that supports Windows domains. In the case of a client that does not run an operating system with Windows domain support, the domain account must be explicitly specified during the authentication.

2. Enter the user name and password for the REST account.

   These two fields do not appear if you have selected Anonymous or Current Windows User (NTLM) because they are not needed.

3. Use the Certificate validation list to select one of three options:

   • Select Skip server certificate validation if you do not need to verify that the web site server's public certificate is valid. The server certificate is checked even if you select Skip server certificate validation, but in that case, the connection will be made even if the certificate is invalid.

   • Select Use System Trust Store if you need the web site server’s certificate to be validated using root certificates installed in a system’s trust store.

   • Select Custom Root CA Certificate if you would like to upload and use a PEM file which contains root certificate(s). Use File button to select a file.

4. Select Enable mutual SSL/TLS to enable mutual verification. This is useful when the client also needs to be verified by the server using a client certificate. In this scenario the uploaded PFX file will be sent to the server and the check will be performed on the server side. The PFX file property allows you to upload a certificate that can verify the client to the server. The PFX file password is the password required to access the certificate data.

5. Specify Trusted locations by adding a URL with a port number to the Value field and an alias for the host identified by the URL to the Name field. If you specify multiple trusted locations, the alias names must be unique.

   The list of hosts specified as Trusted locations are the only hosts to which the user credentials can be sent. If a request is sent to a host that is not a trusted location, the user credentials will not be given to that host.

6. If you are using the Data load editor or Edit script, you can click Test Connection to verify that a connection can be made with the REST service properties entered.

   In both Data load editor and Add data, the connection is tested before it is created. Clicking Test Connection in the Data load editor enables you to test the connection before you attempt to create it.

   In Edit script, the connection is not tested unless you click Test Connection.

Specifying additional parameters

Do the following:

1. Enter the name and value for any additional query parameters.

   Query parameters are specific to the REST service to which you are connecting. They can specify information to return. For example, a parameter named “filter” could be used to specify a value such as “salary>10000.” Query parameters can also be used to provide security keys.

   Names used in the Query parameters section must be unique. To specify multiple query parameters with the same name, you must place the names and values in the URL or use the WITH CONNECTION keyword in the load script.

   The WITH CONNECTION keyword can be added to the script in the Data load editor.

   Select and load data from a REST data source

   To place identically named query parameters in the URL, add them consecutively as in the following simple example:

   http://localhost:9999/echo?q=3&q=4

   Name and Value fields can be added or deleted with the and icons to the right of the fields.

2. Select Add missing query parameters to final request to add any parameters specified in the Query parameters fields to any URL that does not automatically get the parameters.

   Any parameters in the Query parameters fields are automatically added to the URL in the URL field at the top of the Create new connection (REST) dialog when the Select data to load dialog is opened or data is loaded. However, in the case of Next URL pagination, the URLs returned by the data source after the first page of data is requested do not have the query parameters specified in the Query parameters fields. If Add missing query parameters to final request is selected, those query parameters are added to each of the URLs used to get subsequent pages.

   Add missing query parameters to final request also affects the URLs used in WITH CONNECTION statements. If a query parameter is not explicitly included in the WITH CONNECTION statement, it will be added if Add missing query parameters to final request is selected.

   Using WITH CONNECTION for pagination

3. Enter query header field names to be included in the http request header and the values for each query header field.

   Query header Name and Value fields can be added or deleted with the and icons to the right of the fields. “Accept-Language” is an example of a query header. To specify English as the language, you would use the value “en.”

   Warning noteIf you use Date as a query header, it must have the format: yyyy-MM-dd-HH:mm:ssZ. For example, 2014-09-28 16:25:09Z. Any other format produces an error.

Specifying the type of pagination

Do the following:

1. Select the type of paging to use from the Pagination Type menu.

   To select the appropriate type of paging, you must know how the data source to which you are connecting implements the REST API. The REST API allows for Offset, Next page, Next token, Next URL, and Custom pagination. You can also choose None from the list of Pagination Type selections.

   Loading paged data

Specifying security

Do the following:

1. Select Allow response headers to permit responses from the data source to return header fields in responses.

   By default, headers are not allowed in responses.

   Warning noteThe Qlik REST Connector supports the ability to include HTTP headers in the response object. Starting with version 1.2, loading of the headers does not happen by default. It must be explicitly allowed. Existing connections that load the response headers must be reconfigured with the Allow response headers parameter.

2. Select Allow HTTPS only to require that all URLs use the HTTP security protocol (HTTPS).

   If Allow HTTPS only is selected, all URLs must use HTTPS. That includes the URL used for the initial REST connection and all the URLs in the allow list.

3. Add an allow list of URLs that can be used when queries are redirected.

   The URLs in the allow list are the only URLs to which responses can redirect for paged data.

   Specify a URL in the Value field and create an alias for the host identified by the URL in the Name field. If you specify multiple URLs, the alias names must be unique. If Allow HTTPS only has been selected, then all URLs on the allow list must use HTTPS.

4. Check Use cookie container when redirect option if a query is redirected to another URL and authentication cookie obtained from original URL needs to be set in a redirected request.

Completing the connection

Do the following:

1. In Qlik Sense, enter a name for the connection in the Name field.

   The Name field contains a default name for the connection. You can use that name or replace it with another name.

2. In Add data, click Create to create the connection and move to the data selection step.

   In Data load editor, click Create to create the connection that will be used when loading data from the REST resource.

   In QlikView, click OK to insert the connection script into the Edit Script dialog.

In Add data, the data selection step follows immediately when the connection is created, but in Data load editor, you must initiate data selection.

Select and load data from a REST data source