Extracting data from web services with REST Connector

Qlik Sense provides connectivity to several data sources, such as SQL databases, Excel files, and text files, but we also have an increasing need to connect to web services that provide data in the JSON or XML format. This recipe shows you how to configure a REST connection to retrieve data in the JSON format from a public web service with data related to the Star Trek Series.

For this recipe, we will use a URL to extract all the Star Trek movies. When extracting the data from a REST data source:

1. Open an existing Qlik Sense application or create a new one.
2. Open the Data load editor.
3. Click on Create new connection and select Qlik REST Connector.
4. The Qlik REST Connector configuration window will open.
5. Insert the following URL from which you can fetch the data: http://stapi.co/api/v1/rest/movie/search.
6. Set up Authentication Schema as Anonymous.

7. Name the connection as QlikSense Cookbook REST, as shown:

8. Click Test Connection to check whether the parameters are OK.
9. Click Create to create the connection and close the configuration panel.

How to do it…

1. In the list under Data connections, select QlikSense Cookbook REST and click on Select data. This will open a preview window listing the tables found in the JSON response from the web service. When you carefully examine the table contents, you realize that it is a root node, and when you expand the node, you find a movies node.
2. Check the box next to movies and ensure that it is selected, so that the correct table is shown in the preview (when working with JSON data sources, we always have to expand the nodes to find the right table to be extracted).

3. The preview of the table will look like the following screenshot:

4. Save and load the data. Once the script is successfully loaded, the Data model viewer will show the loaded tables.
5. Click on Insert script to load the JSON data in the application.
6. Several commands have been inserted in the script.
7. The main one has a name of RestConnectorMasterTable. This table contains the JSON data in a flattened format.
8. Two more tables are created by a resident load from the mainDirector main table and movies. In the end, the  RestConnectorMasterTable main table is dropped from memory.

How it works…

Qlik Sense connects to the REST service using the stored data connection. Once connected,

it identifies the source formats and lists them in the preview window.

JSON data sources contains nested data with several children records. Qlik Sense creates a multi-table schema when the source contains nested data.

There's more…

Qlik Sense REST Connector can also extract data from other data formats, such as XML and CSV. The underlying principles remain the same as explained in the preceding recipes. You can also extract data using authentication methods, the POST method for SOAP web services, or the pagination method to retrieve a large amount of data.

See also...

• The Creating a master library from the Data model viewer recipe