Doing multiple http queries (to ElastikSearch) and store the result in SAS Data

[jujupapaye_sas]

Hello,

I have a lot of text stored in ElastikSearch (a huge table with an id, and a text). To get content of these texts, I can do a query to Elastiksearch.

I want to access to these text from SAS Viya without having to 'really' store them. So, the idea is storing the query and not the data, and doing queries from SAS Viya to ElastikSearch when I want to acess the text:

It works doing ONE query like this with proc http :

The result is then store in a json file.

What I want is :

1) doing all the queries at the same time that are in the "query" column

2) putting the result in a WORK library with for example 4 columns (note_id, query, result_of_query) without passing throught that Json file

The easiest would be to call my macro get_elastik_text on the "Query" column and save the result in the same place for example.

I already read this article explaning how to integrate SAS and Viya : https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2018/2900-2018.pdf but they are just explaning how to do one query to elastik search and saving the result passing throught a json file. But I want to do multiple queries..

I can give more informations if it's not clear,

Thank you

[ballardw]

Strong suggestion when posting code: Instead of picture copy the code as text from the editor, on this forum open a code or text box using either the "running man" or </> icons that appear above the message window and paste the text.

Because of varying screen resolutions pictures of text can be next to impossible to read (I can't read that picture at all) and second, to make suggestions to change code it really helps to have code so we can modify it.

If you have something that looks like actual executable code in a data set you might look at the CALL execute instruction OR write the text to a program file and then %INCLUDE that file to execute the instructions.

One potential issue I see is related to performance. You might be better off creating one SAS data set and parsing/extracting from that instead of multiple queries. And if your queries are all the same as the few that you show you might be better off sorting that large data set and Proc Print by Doc_id from I would expect the results to look like.

[jujupapaye_sas]

Sorry, I didn't realize it was unreadable, here is the code working for one query but putting the result in a json file:

filename resp '/test_elk.json';

%MACRO get_elastik_text(query=);
proc http 
   	url="http://10.13.64.11:9200/_sql?format=json" 
	ct="application/json"
  	in=&query
	out=resp;
run;
%MEND get_elastik_text;

%get_elastik_text(query='{"query":"SELECT doc_informe FROM infall WHERE doc_id=205256858"}')

The query is different for every document (changing the doc_id). I could use Call execute instruction but I'm afraid like you said of the performance. Another problem is that proc http is now putting the result of the query in a json file. What could work would be something like this :

%MACRO get_elastik_text(query=); 
proc http url="http://10.13.64.11:9200/_sql?format=json" 
       ct="application/json" 
       in=&query 
       out=resp; 
run; 
// get the content of the json file (resp)
// putting it in the new table for example
// erase the content of the resp json file
%MEND get_elastik_text;

data _null_;
	set data_text;
	arg = cats('%nrstr(%get_elastik_text(query=',query,'))');
	call execute(arg);
run

It would work but for 5000 document for example it will take a while I imagine and it's very not optimized. Putting the result directly in a new table without passing throught that json file would allow to do the queries in parallel.

Thanks