Qlik - How To Load Azure Kusto Queries

[ChristofSchwarz]

Microsoft is launching a new export format for Azure Logging in the next weeks (https://docs.microsoft.com/en-us/azure/kusto/concepts/) and guess what, Power BI can already natively import this, so I wanted Qlik to read this, too.

The result of the Kusto POST-Call comes as JSON (see here for a sample) but the data model created by the Qlik's REST Connector "select data" wizard was sub-optimal: All values of all tables come in just one column, while you hoped to get n tables with x columns each. So I had to massage the initial data returned.

I spare you the details on how I made the script, you can check yourself in the attached .qvf file if you like. Basically, I had to

• join tablename information from the root table down to other tables and
• to add an auto-increment number in two tables which restarts at 1 when the foreign key changes

This is what the REST-Connector Wizard creates for you ...

After my massage with Qlik Scripting I have added necessary fields:

My script computes the correct ultimate load-statement itself, something like this (dont worry if you can't read it, it "pivots" rows into columns 🙂

LOAD
Only(If(GoesToColumn = 1, @Value)) AS [Timestamp],
Only(If(GoesToColumn = 2, @Value)) AS [ClientRequestId],
Only(If(GoesToColumn = 3, @Value)) AS [ActivityId],
Only(If(GoesToColumn = 4, @Value)) AS [SubActivityId],
Only(If(GoesToColumn = 5, @Value)) AS [ParentActivityId],
Only(If(GoesToColumn = 6, @Value)) AS [Level],
Only(If(GoesToColumn = 7, @Value)) AS [LevelName],
Only(If(GoesToColumn = 8, @Value)) AS [StatusCode],
Only(If(GoesToColumn = 9, @Value)) AS [StatusCodeName],
Only(If(GoesToColumn = 10, @Value)) AS [EventType],
Only(If(GoesToColumn = 11, @Value)) AS [EventTypeName],
Only(If(GoesToColumn = 12, @Value)) AS [Payload]
RESIDENT Rows
WHERE Rows.TableName = '$(vTable)'
GROUP BY __KEY_Rows;

Finally, I drop all the tables created by the REST Connector wizard, to get this nice to read format:

[ChristofSchwarz]

 

Kusto Json Import.qvf

[prasad_b]

Hi Sample data is not available at your link.

I m trying to fetch azure demo app data  in qlik , Rest wizard generating script without root table.

Please find below response 

RestConnectorMasterTable:
SQL SELECT
"name" AS "name_u0",
"__KEY_tables",
(SELECT
"name",
"type",
"__FK_columns"
FROM "columns" FK "__FK_columns"),
(SELECT
"__KEY_rows",
"__FK_rows",
(SELECT
"@Value",
"__FK_rows_u0"
FROM "rows" FK "__FK_rows_u0" ArrayValueAlias "@Value")
FROM "rows" PK "__KEY_rows" FK "__FK_rows")
FROM JSON (wrap off) "tables" PK "__KEY_tables";

[columns]:
LOAD [name],
[type],
[__FK_columns] AS [__KEY_tables]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_columns]);

[rows]:
LOAD [@Value],
[__FK_rows_u0] AS [__KEY_rows]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_rows_u0]);

[rows_u0]:
LOAD [__KEY_rows],
[__FK_rows] AS [__KEY_tables]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_rows]);

[tables]:
LOAD [name_u0] AS [name_u0],
[__KEY_tables]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_tables]);

DROP TABLE RestConnectorMasterTable;