topic Re: How to read huge json file? in SAS Programming

How to read huge json file?

PäiviR — Tue, 02 Jan 2018 10:18:00 GMT

Hi,

I have the code below which works:

filename resp temp;

proc http

url="http://172.17.166.22:9200/logstash-2017.02.*/ui/_search?pretty"

method= "GET"

out=resp;

run;

libname space JSON fileref=resp;

In real life json file is huge, how can I read only a part of the json file? I have try to use options IN=, but I can't find right syntax.

Re: How to read huge json file?

RW9 — Tue, 02 Jan 2018 10:43:59 GMT

Not sure what you mean by "read only a part of the file". The http call will retrieve the whole file to your temp workspace. As this is a text file, you could process that text file, i.e. search through for a start and end point, then save out that part of the text to a new text file, or straight to a dataset. Not sure that would save you any time above reading the text file and doing a where to keep the data you want. Maybe there is a method on the sending end which can filter the data - you have search criteria in the URL= _search=?pretty, maybe just add more information to the search to get more specific data?

Re: How to read huge json file?

PäiviR — Tue, 02 Jan 2018 12:54:15 GMT

Here is query which have made for elasctic:

curl --user tunnus:salasana -XGET 'http://172.17.166.22:9200/logstash-2017.02.*/ui/_search?pretty' -d

'{"size":0, "query" : {"bool": { "must" : {"terms": { "functionname": [ "BANK_PRODUCT_PRICING_CREATE", "BANK_PRODUCT_PRICING_DISCOUNT_CREATE", "BANK_PRODUCT_PRICING_SHOW", "PRODUCT_PRICING_CREATE_OR_UPDATE", "PRODUCT_PRICING_DELETE", "PRODUCT_PRICING_DISCOUNT_DELETE"] } },"must" : {"term": {"uichannel": "STP"} } }},"aggs": {"pankki": {"terms": {"field": "bankid"}, "aggs": {"konttori": {"terms": {"field": "branchid"}}} }}}'

I should do similar query for SAS, pick up only "rows" which functionname is

"BANK_PRODUCT_PRICING_CREATE", "BANK_PRODUCT_PRICING_DISCOUNT_CREATE", "BANK_PRODUCT_PRICING_SHOW", "PRODUCT_PRICING_CREATE_OR_UPDATE", "PRODUCT_PRICING_DELETE", "PRODUCT_PRICING_DISCOUNT_DELETE"

and uichannel is STP.

Re: How to read huge json file?

RW9 — Tue, 02 Jan 2018 13:05:09 GMT

No idea what your asking there. Anyways, what I was saying that in the url:

'http://172.17.166.22:9200/logstash-2017.02.*/ui/_search?pretty'

You have a search criteria, so why can that not be expanded to search for all the filters you need, then extract only that data?

Unfortunately I cannot access that site, but assuming it is doing a search just epxand that.

Re: How to read huge json file?

ChrisHemedinger — Tue, 02 Jan 2018 13:59:07 GMT

You can't use the JSON libname engine to read just part of a JSON file. JSON format uses nested constructs, so the entire file must be processed to ensure the integrity of the data.

However, once the data is accessible in the SAS library, you can use PROC SQL or DATA step to subset the fields/tables to keep just what you need for your reports/analysis.