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:
- Open an existing Qlik Sense application or create a new one.
- Open the
Data load editor
. - Click on
Create new connection
and select QlikREST
Connector. - The Qlik
REST
Connector configuration window will open. - Insert the following URL from which you can fetch the data: http://stapi.co/api/v1/rest/movie/search.
- Set up
Authentication Schema
asAnonymous
.
- Name the connection as
QlikSense Cookbook REST
, as shown:
- Click
Test Connection
to check whether the parameters are OK. - Click
Create
to create the connection and close the configuration panel.
- In the list under
Data connections
, selectQlikSense 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. - 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).
- The preview of the table will look like the following screenshot:
- Save and load the data. Once the script is successfully loaded, the
Data model viewer
will show the loaded tables. - Click on
Insert script
to load the JSON data in the application. - Several commands have been inserted in the script.
- The main one has a name of
RestConnectorMasterTable
. This table contains the JSON data in a flattened format. - Two more tables are created by a resident load from the
mainDirector
main table andmovies
. In the end, theRestConnectorMasterTable
main table is dropped from memory.
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.
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.