Qlik Sense RestConnector : Select and load data from a REST data source

Posted on by hackdl

Once a connection to aRESTdata source has been established, you can select data and load it into aQlik SenseorQlikViewapp. You select data by selectingRESTtables and fields in the data selection dialog. You can load data using eitherAdd dataor theData load editor. If you are using theData load editor, you can also select data by entering script manually. You can also enter script manually in theEdit Scriptdialog.

Information noteIf you use theData load editor, do not modify the generated script. Specifically, modifying or removing the generated LOADstatements can result in__extra_fields in your data.

Qlik Sense: Selection properties

Selection properties that can be configured
Properties Description
Response type The format of the data to be loaded: XML, JSON, or CSV.
Auto detect Detect the data format automatically when loading the data.
Preload symbols count Specifies the amount of character data that is preloaded to display the data selection structure. The default is 50K. If the preload symbol count is not large enough to load all the branches in the data hierarchy, then any branches that are not loaded cannot be selected and are be included in the data load.No limitcan be selected so that all the data is preloaded, but in that case, the preload could take a long time.
Tables The hierarchy of tables available.
Filter tables

Displays a field for entering filter criteria.
Fields The fields available in each selected table.Changing the field names in Qlik Sense
Data preview

Shows a preview of the fields and data of the selected table.
Metadata Shows a table of the fields in the selected branch.
Filter fields

Displays a field for entering filter criteria.

Changing the field names inQlik Sense

While theSELECTandLOADstatements are built automatically when you select tables and fields, you can make certain changes to those statements when usingQlik Sense.

Field names can be changed in theFieldspanel by clicking on the existing name and editing the name text.

The changed name is then used as an alias for the original name when the field is loaded intoQlik Sense.

QlikView: Selection properties

服务器托管网

Selection properties that can be configured
Properties Description
Response type The format of the data to be loaded: XML, JSON, or CSV.
Auto detect Detect the data format automatically when loading the data.
Preload symbols count Specifies the amount of character data that is preloaded to display the data selection structure. The default is 50K. If the preload symbol count is not large enough to load all the branches in the data hierarchy, then any branches that are not loaded cannot be selected and are be included in the data load.No limitcan be selected so that all the data is preloaded, but in that case, the preload could take a long time.
Objects The hierarchy of objects available.
Fields The fields available in each selected table.
Script

The load script generated for the object and field selections.
Preview

Shows a preview of the fields and data of the selected table.

Selectstatement syntax with theREST Connector

REST Connectorload scripts consist of oneSELECTstatement and one or moreLOADstatements.SELECTstatements use the same structure as the source data. For example, data aboutstoresfrom aJSONdata source might be selected as follows:

TheSELECTstatement generated for this selection would be:

SQL SELECT
	"__KEY_root",
    (SELECT
    	"storedId",
        "city",
        "region",
        "__FK_stores"
     FROM "stores" FK "__FK_stores")
FROM JSON (wrap on) "root" PK "__KEY_root";

[stores]:
LOAD [storedId] AS [storeId],
	[city] AS [city],
    [__FK_stores] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_stores]);Copy code to clipboard

SELECTstatement syntax

TheSELECTstatement uses Extended Backus-Naur Form (EBNF). The syntax for theSELECTstatement:

SQL ;

select_stmt=SELECT[{,}] FROM [][]

[‘PK’ ] [‘FK’ ] [‘ContentFieldAlias’ ] [‘ArrayValueAlias’ ]

SELECTstatement parameters

  • field = |
  • simple_field = [AS ]
  • response_type = CSV|XML|JSON. Only for top-level .
  • options = (wrap on|off) for JSON, (header on|off, delimiter “,”, quote “tab”) for CSV. Only for top-level .
  • pk_field_name – primary key field name for current table. Should be specified if there is any sub-se服务器托管网lect for current table.
  • fk_field_name – primary key field name for current table. Should be specified if current table is sub-select.
  • content_field_name – field name for data from XML element content (look at @Content in Metadata part).
  • array_item_field_name – field name for data from simple array items of JSON (look at @Value in Metadata part).
  • table_name – table name on the current hierarchical level (for XML and JSON). For CSV it is always CSV_Source.
  • field_name – string describing data field location in the data source.
  • field_alias – this name will be used as field name in QvxTable.

All field aliases must be unique. If conflicts exist with alias names, the connector adds auto-incremented suffixes to the aliases until they are unique. For example,Employee_u0,Employee_u1. Aliases are used as field names inQlik SenseandQlikViewtables.

For theXMLresponse type, the connector adds a subselect without fields for descendant nodes, even if they are not selected in the select dialog. This is required to calculate key values for theKey generation strategywhenCurrent recordorFull qualified recordis selected.

WITH CONNECTIONkeyword

TheREST Connectorsupports theWITH CONNECTIONkeyword, which can be used to overrideURLs, query parameters and query headers in a connection. ThePOSTbody can also be overridden when thePOSTmethod has been selected for the connection.WITH CONNECTIONallows you to alter some parts of a connection rather than create a separate connection.

Four parameters can be used with theWITH CONNECTIONkeyword:

  • URL
  • QUERY
  • HTTPHEADER
  • BODY

The syntax for each parameter is:

  • URL “new url”
  • QUERY “parameter name” “parameter value”
  • HTTPHEADER “header name” “header value”
  • BODY “request body text”

Examples:

WITH CONNECTION (

Url “https://localhost:81/northwind2.xml”,

QUERY “type” “XML”,

QUERY “size” “”,

HTTPHEADER “auth_type” “token”,

HTTPHEADER “token” “1234123123123”,

BODY “Post this”);

WITH CONNECTIONcan be used with any of the pagination types. WhenCustompagination is specified, aWITH CONNECTIONstatement is included automatically in the load script. In the following example, theWITH CONNECTIONstatement is inserted using theURLgenerated for theSelect data to loaddialog. The load script must be modified, however, to perform the desired pagination. As it is generated, the statement would simply return the same page with each call because thestartAtvariable is always set to 0. An example of how the load script can be modified to get a sequence of pages is shown below:

LIB CONNECT TO 'REST-Google-custom (abc_admin)';


// Action required: Implement the logic to retrieve the total records 
// from the REST source and assign to the 'total' local variable.
Let total = 0;

Let totalfetched = 0;
Let startAt = 0;
Let pageSize = 100;

for startAt = 0 to total step pageSize
RestConnectorMasterTable:
SQL SELECT 
	.
	.
	.
	.
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION(Url "https://content.googleapis.com/calendar/v3/calendars/primary/events?key=AIzaS...");
// Action required: change URL included in 'WITH CONNECTION' as 
// needed to support pagination for the REST source. 
// Please see the documentation for "Loading paged data."

NEXT startAt;

Copy code to clipboard

When any of the other pagination types are used, theWITH CONNECTIONstatement must be added manually.

Using WITH CONNECTION for pagination

Using pagination

The following script illustrates how the automatically generated template forCustompagination can be adapted to retrieve the desired number of pages in aGoogle Analyticsdata source. The query parameterstartAtis incremented by the script so each call to theGoogle Analytics REST APIbegins to retrieve data at a different point in the source. Without the adaptation, the script would retrieve only the first page of data.

LIB CONNECT TO 'REST-Google-custom (abc_admin)';


// Action required: Implement the logic to retrieve the total records 
// from the REST source and assign to the 'total' local variable.
Let total = 0;

Let totalfetched = 0;
Let startAt = 0;
Let pageSize = 100;

for startAt = 0 to total step pageSize
RestConnectorMasterTable:
SQL SELECT 
	.
	.
	.
	.
FROM JSON (wrap on) "root" PK "__KEY_root"
WITH CONNECTION(QUERY "startAt" "$(startAt)");
// Action required: change URL included in 'WITH CONNECTION' as 
// needed to support pagination for the REST source. 
// Please see the documentation for "Loading paged data."

NEXT startAt;

LOAD

	.
	.
	.
	.

DROP TABLE RestConnectorMasterTable;Copy code to clipboard

WITH CONNECTION keyword

UsingWITH CONNECTIONfor pagination

TheWITH CONNECTIONkeyword can be used with all of the pagination options. Unlike withCustompagination, which insertsWITH CONNECTIONin the load script automatically, theWITH CONNECTIONstatement must be inserted manually after theSELECTstatement.

When theSelect data to loaddialog opens, the tables and fields displayed are loaded by theURLand parameters specified in theURLfield in theCreate new connection (REST)dialog. Any entries in theQuery parametersandQuery headersfields in the dialog are also used. But when data is loaded, theWITH CONNECTIONstatement takes precedence over theQuery parametersandQuery headersfields. If theAdd missing query parameters to final requestoption is selected, then any parameters from theQuery parametersfields that are not in theWITH CONNECTIONstatement are added to the URL.

If theWITH CONNECTIONstatement contains aURL, thatURLis used when data is loaded rather than theURLspecified in theURLfield in theCreate new connection (REST)dialog.

If aWITH CONNECTIONstatement contains aURL, it must point to a data source that contains fields that match any of the selections made in theSelect data to loaddialog. Usually, the baseURLused in theWITH CONNECTIONstatement is the same as theURLspecified in the connection dialog. In that case, the tables and fields are likely to match.

WITH CONNECTION keyword

Several points are important to keep in mind when usingWITH CONNECTION:

  • Query parameters and headers can be added to theWITH CONNECTIONstatement, even if they are not specified in theCreate new connection (REST)dialog.

  • If theWITH CONNECTIONstatement includes aURL, then thatURLis used when data is loaded. All paging iterations are then derived from thatURL. In that case, theURLspecified in theURLfield in theCreate new connection (REST)dialog is not used when data is loaded.

    In the case ofNext URLpagination, each subsequent page request uses theURLreturned by the data source.

    Using Next URL pagination and WITH CONNECTION

  • If theWITH CONNECTIONstatement includes only query parameters and headers and does not include aURL, then theURLis taken from theURLfield in theCreate new connection (REST)dialog.

    In the case ofNext URLpagination, each subsequent page request uses theURLreturned by the data source.

    Using Next URL pagination and WITH CONNECTION

  • Query parameters and headers included in theWITH CONNECTIONstatement cannot conflict with any query parameters or headers returned by the data source for pagination.

    Warning noteAn error results if the query parameters or headers from theWITH CONNECTIONstatement conflict with the query parameters or headers returned by the data source. However, ifAdd missing query parameters to final requestis selected, the query parameters specified in theCreate new connection (REST)dialog and those returned from the data source are added and the query parameters specified in theWITH CONNECTIONstatement are ignored. In that case, there is no error for conflicting query parameters.

  • If theURLfrom theURLfield ofCreate new connection (REST)dialog is used, query parameters and headers specified in the connection dialog’sQuery parametersandQuery headersfields are added to theURLwhen it is used to open theSelect data to loaddialog. Those query parameters headers overwrite any entries with the same name that were added manually to theURL. If theWITH CONNECTIONstatement contains query parameters or headers, they are added to theURLat the time it is used to open theSelect data to loaddialog, and they overwrite any parameters or headers with the same name.

    Warning noteAn error results if the query parameters or headers from theWITH CONNECTIONstatement or from the connection dialog’sQuery parametersandQuery headersfields conflict with the query parameters or headers returned by the data source. If there are no conflicts, the query parameters and headers returned by the data source are simply added to the other parameters and headers.

  • Query parameters specified in theCreate new connection (REST)dialog are added to theWITH CONNECTIONstatement if theAdd missing query parameters to final requestoption is selected in theCreate new connection (REST)dialog.
  • Query parameters and headers added manually in theURLfield ofCreate new connection (REST)dialog are not used in theWITH CONNECTIONstatement unless they are added theWITH CONNECTIONstatement manually as well.
  • If thePOSTmethod is used, the body of text is taken from theWITH CONNECTIONstatement. If theWITH CONNECTIONstatement does not contain aBodyproperty, the text is taken from the text specified in theRequest bodyfield in theCreate new connection (REST)dialog.

UsingNext URLpagination andWITH CONNECTION

There are additional points to keep in mind when usingWITH CONNECTIONand theNext URLpagination option:

  • If theWITH CONNECTIONstatement includes aURL, thatURLis used for the first page of data.

  • Query parameters and headers from theWITH CONNECTIONstatement are added to theURLreturned from the data source and that returned URL is used for the next page request.

    Warning noteAn error results if the query parameters or headers from theWITH CONNECTIONstatement conflict with the query parameters or headers returned byNext URLpagination. However, ifAdd missing query parameters to final requestis selected, the query parameters specified in theCreate new connection (REST)dialog are added to theURLreturned from the data source, and the query parameters specified in theWITH CONNECTIONstatement are ignored. In that case, there is no error for conflicting query parameters.

    If parameters or headers from theCreate new connection (REST)dialog conflict with parameters or headers returned byNext URLpagination, theNext URLparameters and headers are used and the others are ignored. Any parameters or headers from the connection dialog that do not conflict are added to the URL returned byNext URLpagination.

  • Query parameters and headers included in theWITH CONNECTIONstatement take precedence over parameters and headers in theCreate new connection (REST)dialog.

    When theAdd missing query parameters to final requestoption is selected in theCreate new connection (REST)dialog, the parameters in theQuery parametersfields are added to theURLreturned from the data source, but if any of the parameters in theWITH CONNECTIONstatement are the same, the values from theWITH CONNECTIONstatement take precedence.

服务器托管，北京服务器托管，服务器租用 http://www.fwqtg.net

相关推荐: C++ Qt开发：自定义Dialog对话框组件

Qt 是一个跨平台C++图形界面开发库，利用Qt可以快速开发跨平台窗体应用程序，在Qt中我们可以通过拖拽的方式将不同组件放到指定的位置，实现图形化开发极大的方便了开发效率，本章将重点介绍自定义Dialog组件的常用方法及灵活运用。 在之前的文章中笔者已经为大家…

服务器托管，北京服务器托管，服务器租用，机房机柜带宽租用

服务器托管

咨询：董先生

电话13051898268 QQ/微信93663045！

上一篇:
下一篇: