downloading JSON in structured format

Highlighted
Seven Stars

downloading JSON in structured format

Hi All, 

I'm working on web services and I need to download the JSON response using http get requests and then read those JSON files and parse them.

This works out well for smaller files , the issue here is the JSON document which I get is downloaded in a single row ! , so I have 200 mb of data downloaded in a single row .

This file is then impossible to open using an editor and then if I try to parse it in talend I get out of memory exception.

Is there any work around for this issue ? 

Does JSON response from https get request always have to be in Single row ? can we break it down and do some formatting before saving it on disk ?

What is the best way to parse a large JSON file size greater than 200 MB ?

 

 


Accepted Solutions
Highlighted
Community Manager

Re: downloading JSON in structured format

I've looked at your file and the example you gave was not valid JSON. I suspect that is because of how you transferred it and cut it to here though. Before this code....

 

{ "ActionId":410768,

....you needed a comma. A small thing, but if your source does not provide that, it will not work.

 

Now, I changed the values of your file to make it easier for me to see whether it was working. I changed your file to this....

{
   "ExportSetGuid":"a7480ae9-5045-4e33-b6f5-875cd17e1711",
   "Actions":[
      {
         "ActionId":410766,
         "Allocations":[
            {
               "AllocationTargetOrgUnitId":82373,
               "FiscalYears":[
                  {
                     "FiscalYear":2017,
                     "PercentValue":1.0300
                  },
                  {
                     "FiscalYear":2018,
                     "PercentValue":2.0300
                  }

               ]
            }
         ]
      },
      { "ActionId":410768, 
         "Allocations":[ 
             { "AllocationTargetOrgUnitId":82377,
               "FiscalYears":
                [ 
                { "FiscalYear":2020, "PercentValue":3.0600 },
                { "FiscalYear":2021, "PercentValue":5.0900 }
                ]
             }
          ]
       }
   ]
}

I then built a job like this.....

Screenshot 2019-02-26 at 11.32.04.png

 

Ignore the deactivated tLogRows. I added those to see what I was getting after each component. This is a really good way of checking you are on the right track. The following screenshots show each of the components from left to right in order (ignoring the tLogRows....

Screenshot 2019-02-26 at 11.32.21.png

First I extract the outermost loop information. The Actions data in the above screen shot is essentially pulling out a json snippet from the bigger json file and sending it forward to be processed.Screenshot 2019-02-26 at 11.32.31.png

The above tExtractJsonFields component is passing through the ExportSetGuid value (hence it is left blank in the Json query) and is extracting the ActionId value and the Allocations array json snippet. Notice the Json Field value is set to Actions. Also notice the loop. It is imply looping over the array.

Screenshot 2019-02-26 at 11.32.41.png

The above tExtractJsonFields component is passing through the ExportSetGuid and ActionId values and is extracting the AllocationTargetOrgUnitId value and the FiscalYears array json snippet. Notice the Json Field value is set to Allocations. Also notice the loop. It is imply looping over the array.

Screenshot 2019-02-26 at 11.32.51.png

The above tExtractJsonFields component is passing through the ExportSetGuid, ActionId and AllocationTargetOrgUnitId values and is extracting the FiscalYear and PercentValue values. This is the lowest leaf on the tree. Notice the Json Field value is set to FiscalYears. Also notice the loop. It is imply looping over the array.

 

The output from this was.....

Starting job ExampleFilteringtXML at 11:28 26/02/2019.

[statistics] connecting to socket on port 3677
[statistics] connected
a7480ae9-5045-4e33-b6f5-875cd17e1711|410766|82373|2017|1.03
a7480ae9-5045-4e33-b6f5-875cd17e1711|410766|82373|2018|2.03
a7480ae9-5045-4e33-b6f5-875cd17e1711|410768|82377|2020|3.06
a7480ae9-5045-4e33-b6f5-875cd17e1711|410768|82377|2021|5.09
[statistics] disconnected

Job ExampleFilteringtXML ended at 11:28 26/02/2019. [exit code=0]

This is not necessarily a quick and easy way to achieve this, but it is a methodical way to work. 

View solution in original post


All Replies
Highlighted
Community Manager

Re: downloading JSON in structured format

The problem here is NOT the formatting. If anything the lack of formatting will save on space. The problem here is the size of data. You may want to find a text editor which handles large files better than the one you are using. I use UEStudio which would easily handle 200MB.

 

To read it in Talend, you may want to play with your job and Studio memory settings. 

This link will help you increase the memory for Studio (https://help.talend.com/reader/pd~gJPOP3y0tu8jedjNfIA/aNyC~EW3eVxmjwAUWgA~pw)

This link will help with increasing the memory for your job (https://community.talend.com/t5/Migration-Configuration-and/OutOfMemory-Exception/ta-p/21669)

 

Highlighted
Forteen Stars

Re: downloading JSON in structured format

+1 to all from @rhall_2_0 

 

also good to check your API endpoint documentation, many API contain additional settings to reduce response size - split it to chunks

example:

  • receive all customers - is very huge JSON
  • receive 100 customers, with offset XXX, it is 100 customers, but many iterations

 

additional + for UltraEdit, it could edit docs of any size, personally, I use it for 15-20Gb files

-----------
Highlighted
Seven Stars

Re: downloading JSON in structured format

Thanks @rhall_2_0 and @vapukov for your valuable suggestion.

I tried increasing memory of the talend studio and Job with below configurations

 

-vm
C:\Program Files (x86)\Talend-Studio\jre1.8.0_131\bin\server\jvm.dll
-vmargs
-Xms1024m
-Xmx40966m
-Dfile.encoding=UTF-8
-Dosgi.requiredJavaVersion=1.8
-XX:+UseG1GC
-XX:+UseStringDeduplication

Capture1.PNG

 

Gave temporary location in tMap as well

 

Capture3.PNG

 

But this did not help , now instead of getting out of memory exception , Talend is in a stuck state for 3 hours.
I'm working on a system which has 8GB RAM

 

My Job works  for other files which are less than or equal to 10mb and share a similar JSON structure to 208 mb file 

 

Capture2.png

 

T-MAP is not complicated, it is one-to-one mapping with addition of timestamp.

 

Is there any other way we can get this to work ?

 

Thanks

Highlighted
Community Manager

Re: downloading JSON in structured format

I think I see your problem. You are using XPath instead of JsonPath. What happens when you do this is that the JSON is converted to XML in memory and then the XML is parsed. With a 200MB  json file, this will be a massive overhead. If you are working with json, you should try to use JsonPath. 

Highlighted
Seven Stars

Re: downloading JSON in structured format

@rhall_2_0  Thanks for your reply

 

I had tried using the other options before but I did not get expected output so I switched to Xpath.
I tried to parse the JSON again with below configuration with a smaller file, this config is similar to the one I used in Xpath

 

Capture1.JPG

 

But I get an Error and Unexpected output

 

The Json resource datas maybe have some problems, please make sure the data structure with the same fields.
[WARN ]: integration_demo.j_08_transfer_transactional_tr_alloc_fy_0_1.j_08_transfer_transactional_tr_alloc_fy - tFileInputJSON_2 - The Json resource datas maybe have some problems, please make sure the data structure with the same fields.

Output file does not loop the outermost element of JSON , export id in this case .
In below screenshot we can see it prints exportid only once , but I need it in all Lines

 

Capture2.JPG

 

This is small JSON Snippet

 

{
   "ExportSetGuid":"a7480ae9-5045-4e33-b6f5-875cd17e1711",
   "Actions":[
      {
         "ActionId":410766,
         "Allocations":[
            {
               "AllocationTargetOrgUnitId":82373,
               "FiscalYears":[
                  {
                     "FiscalYear":2019,
                     "PercentValue":3.0200
                  }
               ]
            }
         ]
      }
   ]
}

 

Highlighted
Community Manager

Re: downloading JSON in structured format

Why are you using JsonPath without loop? Surely you need the loops?

Highlighted
Seven Stars

Re: downloading JSON in structured format

JSON path with loops only returns the data which are inside a  list (This is what I've found)

 

I need data from the list as well as the elements which are outside .

Highlighted
Community Manager

Re: downloading JSON in structured format

Are you saying that your 200MB json file does not have any loops in it? If you are extracting looped data, you will need to use JsonPath and not JsonPath without loops.

Highlighted
Seven Stars

Re: downloading JSON in structured format

All my file have loops , but if I use JSON Path and then specify a loop , I am able to retrieve only specific data from a Single Loop.

In my JSON there are many loops and I want to be able to retrieve data from all the loops at once in a single line (Xpath allows me to do that as we can see in earlier screenshot)   

 

If below is the Json 

 

{
   "ExportSetGuid":"a7480ae9-5045-4e33-b6f5-875cd17e1711",
   "Actions":[
      {
         "ActionId":410766,
         "Allocations":[
            {
               "AllocationTargetOrgUnitId":82373,
               "FiscalYears":[
                  {
                     "FiscalYear":2019,
                     "PercentValue":3.0200
                  },
                  {
                     "FiscalYear":2019,
                     "PercentValue":5.0200
                  }

               ]
            }
         ]
      }
{ "ActionId":410768,
"Allocations":[
{ "AllocationTargetOrgUnitId":82373,
"FiscalYears":
[
{ "FiscalYear":2019, "PercentValue":3.0200 },
{ "FiscalYear":2019, "PercentValue":5.0200 }
]
}
]
} ] }

I need output as below

 

a7480ae9-5045-4e33-b6f5-875cd17e1711;410766;82373;2019;3.0200
a7480ae9-5045-4e33-b6f5-875cd17e1711;410766;82373;2019;5.0200

a7480ae9-5045-4e33-b6f5-875cd17e1711;410768;82373;2019;3.0200
a7480ae9-5045-4e33-b6f5-875cd17e1711;410768;82373;2019;5.0200

 

Tried it many times , unable achieve above result with Json path or Json path without loop , it works only with Xpath 

Highlighted
Community Manager

Re: downloading JSON in structured format

I've looked at your file and the example you gave was not valid JSON. I suspect that is because of how you transferred it and cut it to here though. Before this code....

 

{ "ActionId":410768,

....you needed a comma. A small thing, but if your source does not provide that, it will not work.

 

Now, I changed the values of your file to make it easier for me to see whether it was working. I changed your file to this....

{
   "ExportSetGuid":"a7480ae9-5045-4e33-b6f5-875cd17e1711",
   "Actions":[
      {
         "ActionId":410766,
         "Allocations":[
            {
               "AllocationTargetOrgUnitId":82373,
               "FiscalYears":[
                  {
                     "FiscalYear":2017,
                     "PercentValue":1.0300
                  },
                  {
                     "FiscalYear":2018,
                     "PercentValue":2.0300
                  }

               ]
            }
         ]
      },
      { "ActionId":410768, 
         "Allocations":[ 
             { "AllocationTargetOrgUnitId":82377,
               "FiscalYears":
                [ 
                { "FiscalYear":2020, "PercentValue":3.0600 },
                { "FiscalYear":2021, "PercentValue":5.0900 }
                ]
             }
          ]
       }
   ]
}

I then built a job like this.....

Screenshot 2019-02-26 at 11.32.04.png

 

Ignore the deactivated tLogRows. I added those to see what I was getting after each component. This is a really good way of checking you are on the right track. The following screenshots show each of the components from left to right in order (ignoring the tLogRows....

Screenshot 2019-02-26 at 11.32.21.png

First I extract the outermost loop information. The Actions data in the above screen shot is essentially pulling out a json snippet from the bigger json file and sending it forward to be processed.Screenshot 2019-02-26 at 11.32.31.png

The above tExtractJsonFields component is passing through the ExportSetGuid value (hence it is left blank in the Json query) and is extracting the ActionId value and the Allocations array json snippet. Notice the Json Field value is set to Actions. Also notice the loop. It is imply looping over the array.

Screenshot 2019-02-26 at 11.32.41.png

The above tExtractJsonFields component is passing through the ExportSetGuid and ActionId values and is extracting the AllocationTargetOrgUnitId value and the FiscalYears array json snippet. Notice the Json Field value is set to Allocations. Also notice the loop. It is imply looping over the array.

Screenshot 2019-02-26 at 11.32.51.png

The above tExtractJsonFields component is passing through the ExportSetGuid, ActionId and AllocationTargetOrgUnitId values and is extracting the FiscalYear and PercentValue values. This is the lowest leaf on the tree. Notice the Json Field value is set to FiscalYears. Also notice the loop. It is imply looping over the array.

 

The output from this was.....

Starting job ExampleFilteringtXML at 11:28 26/02/2019.

[statistics] connecting to socket on port 3677
[statistics] connected
a7480ae9-5045-4e33-b6f5-875cd17e1711|410766|82373|2017|1.03
a7480ae9-5045-4e33-b6f5-875cd17e1711|410766|82373|2018|2.03
a7480ae9-5045-4e33-b6f5-875cd17e1711|410768|82377|2020|3.06
a7480ae9-5045-4e33-b6f5-875cd17e1711|410768|82377|2021|5.09
[statistics] disconnected

Job ExampleFilteringtXML ended at 11:28 26/02/2019. [exit code=0]

This is not necessarily a quick and easy way to achieve this, but it is a methodical way to work. 

View solution in original post

Highlighted
Seven Stars

Re: downloading JSON in structured format

Thank you very much @rhall_2_0  for such detailed explanation.

The approach you suggested is considerably fast for parsing JSON than the Xpath I was using .

 

It also solved another issue of invalid XML unicode character which I was getting in some of the files due to the Xpath approach

 

For the 208 mb file at first I got the heap-space issue (That was immediate didn't have to wait 4 hours to get this error) .

I increased the memory of the job to 12 GB (Ran it on a system which actually had more RAM) and then I got the GC overhead issue , found a way to fix that in some other Talend community post which is to add  JVM parameter to disable GC overhead limit , and after that everything worked Smiley Happy

File was processed within 2 minutes !!

 

Capture3.PNG

 

Thanks again , really appreciate your help .

 

Can't imagine how much time I invested to parse this JSON the wrong way , I think this approach can be  added as a Scenario under tfilejsoninput  in help.talend.com (This is the first page you land on when you google Talend + JSON)

The existing one's there are too basic (If this is not already present somewhere else , as I couldn't find it)

Highlighted
Community Manager

Re: downloading JSON in structured format

Hi @karandama2006, I am pleased that this worked for you. I will see if we can get this post reworded and promoted to an article.

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Put Massive Amounts of Data to Work

Learn how to make your data more available, reduce costs and cut your build time

Watch Now

How OTTO Utilizes Big Data to Deliver Personalized Experiences

Read about OTTO's experiences with Big Data and Personalized Experiences

Blog