question Re: How to read json from S3 then edit json using Nifi in Support Questions

How to read json from S3 then edit json using Nifi

baymax277 — Thu, 23 Jun 2022 00:48:37 GMT

I am using Nifi 1.6.0. I am trying to copy an S3 file into redshift. The json file on S3 looks like this:
[

{"a":1,"b":2},

{"a":3,"b":4}
]
However this gives an error because of '[' and ']' in the file (https://stackoverflow.com/a/45348425).

I need to convert the json from above format to a format which looks like this:

{"a":1,"b":2}

{"a":3,"b":4}

So basically what I am trying to do is to remove '[' and ']' and replace '},' with '}'.

The file has over 14 million rows (30 MB in size)

How can I achieve this using Nifi?

Re: How to read json from S3 then edit json using Nifi

araujo — Wed, 22 Jun 2022 23:46:11 GMT

@baymax277 ,

The problem with your file is not the square brackets ([ ]). The issue is that single quotes are not a valid JSON quote. The correct JSON would be:

[ {"a":1,"b":2}, {"a":3,"b":4} ]

The content above is processed correctly by NiFi if you use record-based processors (like, for example QueryRecord).

Ideally your source data should be fixed to provide a valid JSON. For example, if those files are being generated by a Python script like this:

my_object = [{'a': 1, 'b': 2}, {'a': 3, 'b': 4}] print(my_object)

The Python script should be updated to do this instead:

import json my_object = [{'a': 1, 'b': 2}, {'a': 3, 'b': 4}] print(json.dumps(my_object))

If you cannot have the source data fixed, you can try to replace the single quotes with double quotes using a ReplaceText processor with the following configuration.

This is a simplistic approach, though, and can cause problems in some complex JSON objects if you have strings with unexpected sequences of characters in them.

Cheers,

André

Re: How to read json from S3 then edit json using Nifi

araujo — Thu, 23 Jun 2022 00:51:03 GMT

@baymax277 ,

Could you share a screenshot of the part of you flow that reads and processes the JSON content?

André

Re: How to read json from S3 then edit json using Nifi

baymax277 — Thu, 23 Jun 2022 00:52:06 GMT

@araujo
Sorry, my bad. I made a mistake in the json representation mentioned in the question. The records actually have double quotes ("). I have updated the question.

Re: How to read json from S3 then edit json using Nifi

baymax277 — Thu, 23 Jun 2022 00:57:07 GMT

@araujo
The jsons files are compressed and are placed in an S3 bucket. Here's the flow I have created to fetch s3 file and decompress it.

Re: How to read json from S3 then edit json using Nifi

araujo — Thu, 23 Jun 2022 01:01:00 GMT

@baymax277 ,

Could you also share the screenshot of the part of the flow where the error is happening? I think that's after the Decompress File step, right?

André

Re: How to read json from S3 then edit json using Nifi

baymax277 — Thu, 23 Jun 2022 01:16:03 GMT

@araujo , Right now there is no error in Nifi flow. But when I tried to execute a copy command (from an SQL query editor) on the file, it gave me an error "Invalid JSONPath format: Member is not an object. ". When I searched about this error, I found this - https://stackoverflow.com/a/45348425

This is why I wanted to convert the file.

Re: How to read json from S3 then edit json using Nifi

araujo — Thu, 23 Jun 2022 01:18:09 GMT

@baymax277

Ok. Can you share a screenshot of the part of the flow that insert the data into your database?

Cheers,

André

Re: How to read json from S3 then edit json using Nifi

baymax277 — Thu, 23 Jun 2022 01:45:40 GMT

I have not created that part of the flow yet, but that will be an ExecuteStreamCommand processor which will run a psql command with the copy sql query:
It will look something like this:

Re: How to read json from S3 then edit json using Nifi

araujo — Thu, 23 Jun 2022 01:49:30 GMT

@baymax277 ,

You should look into using PutDatabaseRecord instead of ExecuteStreamCommand. This would be more efficient and it would eliminate the need to manually remove those brackets.

Cheers,

André

Re: How to read json from S3 then edit json using Nifi

baymax277 — Thu, 23 Jun 2022 01:57:09 GMT

@araujo

I will try this solution, but I think the queue to PutDatabaseRecord processor will get clogged very soon because there are over 14 million rows in this file.

I tried Replace text and I was able to replace '},' to '}' within seconds. Can you tell me how to remove the first line '[' and the last line ']'?

Thanks

Re: How to read json from S3 then edit json using Nifi

araujo — Thu, 23 Jun 2022 02:32:03 GMT

@baymax277 ,

Try this:

This is the regex:

(?s)(^\[|\]$)

You may have to increase the buffer size to accommodate your file.

Cheers,

André