Extracting data with QSL

Qlik Sense extracts data from a Qlik Big Data Index data model in two modes:

• Interactive Direct Query mode where every query the Qlik associative engine receives is passed to the Qlik Big Data Index system.
• ETL (Extract, Transform, Load) mode in which data is extracted from Qlik Big Data Index and imported into the Qlik associative engine in-memory storage.

ETL mode is used for extraction queries in which data is requested from the Qlik Big Data Index system in non-grouped, non-aggregated format.

Syntax:  

This type of query requests table data for a specified set of columns and is limited by the filtering condition indicated in the selection handle state.

Example:  

QSL SELECT l_order_key AS [order#], l_part_key AS partKey, l_quantity FROM [dbgen10gb].[lineitem] AT STATE H2;

Table aggregation query

This class of query applies an aggregation function over a rowset filtered by selection handle criteria, without grouping.

Syntax:  

 

 

 

 

 

 

aggregation-function ::=

'COUNT'

Examples:  

1. QSL SELECT COUNT(*) AS numberOfOrders FROM [dbgen10gb].[orders] AT STATE H2;
2. QSL SELECT COUNT(DISTINCT I_quantity) AS sumNonEmptyLineItems FROM [dbgen10gb].[lineitem];

Model aggregation query

When a FROM clause of a SELECT query refers to a model rather than a table, QSL derives a proper table join strategy from the table semantics and computes the Qlik Expression result for its columns. A model aggregation query is a special case of a hypercube query without grouping, and only containing aggregated expressions.

Syntax:  

 

Example:  

QSL SELECT COUNT(l_quantity) FROM [dbgengb] AT STATE H3;

Hypercube query

You can make a hypercube request by adding GROUP BY and ORDER BY clauses. You can group and order the result by columns that are included in the SELECT header. You refer to the ordinal of the column position. At least one of the column values need to be an aggregation.

Syntax:  

Example:  

 

This query returns three fields from the tpch100 dataset using the set handle referenced by h3 as base selection. Defining QSL selections with Set handles

o_order_date is the primary grouping expression as GROUP BY 1 refers to the first column.

The results are ordered by the second column of the results (max(o_ship_priority)) in descending order, and then by the first column in ascending order, which is the default if you do not state ASC or DESC.

Multi-table hypercube queries

You can perform hypercube queries with a multi-table data model, but there are some considerations. Typically, you would aggregate on a field in a fact table grouped by a field in a dimension table. The tables must have a relationship where the aggregation table is N-1 towards the grouping table.

Example:  

In this example, the field o_total_price is in the table Orders (aggregation table), and the field c_cust_key is in the table Customer (grouping table). One row in Customer can have several related rows in Orders.

The example aggregates the sum of order total price grouped by customer.

 

You can use this with star schemas, snowflake schemas or multi-fact schemas grouped on shared dimensions. In all cases you aggregate over a fact table field. You can group on a field from any table, with the exception of multi-fact schemas, where the grouping field must be from a shared dimension table.

Query handles

A query handle is a reference to the result of an ungrouped query. The syntax pattern of the query handle definition is the same as for selection handles.

Syntax:  

Example:  

SET QueryHandle1 = SELECT l_order_key, l_part_key, l_quantity FROM [dbgengb].[lineitem] AT STATE SetHandle2;

Handle destruction

The SET command creates and updates handles. Handles are read by referencing them from other statements. The only additional command needed is handle destruction.

Syntax:  

Example:  

DROP QueryHandle1;