DATA Step, Macro, Functions and more

How to read huge json file?

Reply
New Contributor
Posts: 2

How to read huge json file?

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.

 

 

 

Super User
Super User
Posts: 9,427

Re: How to read huge json file?

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?

New Contributor
Posts: 2

Re: How to read huge json file?

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.
Super User
Super User
Posts: 9,427

Re: How to read huge json file?

??

 

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.

Community Manager
Posts: 3,382

Re: How to read huge json file?

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.

Ask a Question
Discussion stats
  • 4 replies
  • 173 views
  • 3 likes
  • 3 in conversation