REST Connector - Return JSON Response without SQL SELECT

[Anonymous]

Hi,

I have a field mapping stored in ElasticSearch that I'd like to query in order to build my data model. However, with the way the REST connector is implemented, you have to explicitly state each and every field and know the field hierarchy before pulling the data/mapping in order to create the "SQL Select" statement.

Is there a way to simply get the JSON response through the REST Connector (without having to use SQL SELECT)? The reason I ask about this connector specifically is because I haven't seen a way to pass authorization information through the Web File Connector.

Finding a dynamic way to do this would allow me to stay on top of any changes that get made without my knowledge (as it's another team that manages ElasticSearch) by automatically pulling in and flagging new fields in the mapping.

Best regards,

Justin

[balabhaskarqlik]

check these:

https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/Load-REST-data/Load-da...

https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/Create-REST-connection...

[Anonymous]

Hi Bala,

I've looked at these help pages before and they did help me figure out how to set up the connections, do pagination, etc. However, they have not helped me find a way to get the raw JSON response back using this connector (at least that I can tell). Everything they describe there assumes a static structure for the data and it's pulled into Qlik using SQL SELECT.

I've found a way to "dynamically" script the queries (both JSON and SQL SELECT) using a mapping file that is maintained in Excel. However, this isn't truly dynamic, because the Excel file isn't tied in any way to the ElasticSearch mapping - it's simply a way that I found to get everything working for the time being. What I need is a way to pull in the entire mapping from ElasticSearch so that I can know all of the fields in the database, not just the ones that I've seen before.

Best regards,

Justin

[duncanblaine]

Hi Justin

Did you ever find a way to get the REST connector to return the raw data?
I'm struggling with this right now. I had been using Qlik Web Connectors (a paid add-on) which does give you access to the raw response data - so might be good for your needs. 
However I am trying to migrate away from the paid connector to the free REST connector and am finding it very frustrating that I can't get a raw response. 

[stevedark]

Hi,

I'm in exactly the same position, wanting to put a * into my REST script, but finding that is not valid syntax.

If QWC can do it it must be possible. My guess is that it is doing one call to read the metadata for the query, building the select statement in a loop (which would be simple in Qlik code) and then executing the second call.

Has anyone come up with a solution they can share?

Steve

[duncanblaine]

I decided to avoid using REST Connector altogether and use Curl.exe to download the raw data to be queried. 
Then use the standard Table File import with the File Type as Fixed Record to load the XML from the local file. 
After that, parse the rows (lines of XML) however you need to. I found TextBetween() and SubField() particularly useful and have got it working on complex nested XML too.
This method proved to be extremely effective and I've now replaced other instances of REST Connector due to the huge gain in performance. 

Sample code below. 
Note: you'll need to have the Curl executable file in the right place. In my example it would be /Download/Curl.exe.

EXECUTE cmd.exe /C cd ../Download & curl -k -H "Accept: application/xml" -u user@example.com:password -o XML/Test.xml "<a href="https://example.com/webservice" target="_blank">https://example.com/webservice</a>";

XMLRaw:
LOAD 
[@1:n] as xmlText
FROM
[..\Download\XML\Test.xml]
(fix, codepage is 1252);

(Please ignore the hyperlink tags in snippet, it should just be the webservice url in quotes.)

If you need further code snippets showing the parsing, please let me know.

An aside note: I noticed that QlikWebConnector left all the downloaded source files in a Temporary folder (AppData\Local\Microsoft\Windows\INetCache\IE)  which I eventually had  to clear out after it almost filled the drive. Anyway, the point being that it also downloaded the file and might have used something similar to what I described above. It was also a lot faster than REST Connector.