You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by l vic <lv...@gmail.com> on 2018/08/31 23:33:58 UTC
saving record into different tables?
Hi,
I have a json record that contains array "mylst":
{
"id": 0,
"name": "Root",
"mylist": [{
"id": 10,
"info": "2am-3am"
},
{
"id": 11,
"info": "3AM-4AM"
},
{
"id": 12,
"info": "4am-5am"
}]
}
I have to save root data into one db table and array into another... Can
someone recommend an approach to "splitting" of record for 2 different
database writers?
Thank you,
V
Re: saving record into different tables?
Posted by l vic <lv...@gmail.com>.
Would it be possible to have some example of such transform?
On Mon, Sep 3, 2018 at 4:18 AM DEHAY Aurelien <au...@faurecia.com>
wrote:
> Hello.
>
>
>
> I would flatten the records with jolt, with a kind of flag to know if it’s
> “root” data or not. Then I would extract the flag in an attribute with
> evaluatejson path, and at the end route with RouteOnAttribute.
>
>
>
> *Aurélien*
>
>
>
> *From:* l vic [mailto:lvic4594@gmail.com]
> *Sent:* samedi 1 septembre 2018 01:34
> *To:* users@nifi.apache.org
> *Subject:* saving record into different tables?
>
>
>
> Hi,
>
> I have a json record that contains array "mylst":
>
> {
>
> "id": 0,
>
> "name": "Root",
>
> "mylist": [{
>
> "id": 10,
>
> "info": "2am-3am"
>
> },
>
> {
>
> "id": 11,
>
> "info": "3AM-4AM"
>
> },
>
> {
>
> "id": 12,
>
> "info": "4am-5am"
>
> }]
>
>
>
> }
>
> I have to save root data into one db table and array into another... Can
> someone recommend an approach to "splitting" of record for 2 different
> database writers?
>
> Thank you,
>
> V
>
> This electronic transmission (and any attachments thereto) is intended
> solely for the use of the addressee(s). It may contain confidential or
> legally privileged information. If you are not the intended recipient of
> this message, you must delete it immediately and notify the sender. Any
> unauthorized use or disclosure of this message is strictly prohibited.
> Faurecia does not guarantee the integrity of this transmission and shall
> therefore never be liable if the message is altered or falsified nor for
> any virus, interception or damage to your system.
>
RE: saving record into different tables?
Posted by DEHAY Aurelien <au...@faurecia.com>.
Hello.
I would flatten the records with jolt, with a kind of flag to know if it’s “root” data or not. Then I would extract the flag in an attribute with evaluatejson path, and at the end route with RouteOnAttribute.
Aurélien
From: l vic [mailto:lvic4594@gmail.com]
Sent: samedi 1 septembre 2018 01:34
To: users@nifi.apache.org
Subject: saving record into different tables?
Hi,
I have a json record that contains array "mylst":
{
"id": 0,
"name": "Root",
"mylist": [{
"id": 10,
"info": "2am-3am"
},
{
"id": 11,
"info": "3AM-4AM"
},
{
"id": 12,
"info": "4am-5am"
}]
}
I have to save root data into one db table and array into another... Can someone recommend an approach to "splitting" of record for 2 different database writers?
Thank you,
V
This electronic transmission (and any attachments thereto) is intended solely for the use of the addressee(s). It may contain confidential or legally privileged information. If you are not the intended recipient of this message, you must delete it immediately and notify the sender. Any unauthorized use or disclosure of this message is strictly prohibited. Faurecia does not guarantee the integrity of this transmission and shall therefore never be liable if the message is altered or falsified nor for any virus, interception or damage to your system.
Re: saving record into different tables?
Posted by Ed B <bd...@gmail.com>.
After split you have it in ff content. If you need them in attributes, you
need to use evaluateJsonPath
:)
On Wed, Sep 5, 2018, 4:58 PM l vic <lv...@gmail.com> wrote:
> doesn't seem to work for me: attributes don't show up after split
>
> On Wed, Sep 5, 2018 at 3:57 PM Ed B <bd...@gmail.com> wrote:
>
>> Not sure what you mean by "standard processors", but if you do split, it
>> won't be array anymore. check the attached image again - there are now
>> three flow files - each of them is an element from that mylist.
>>
>> On Wed, Sep 5, 2018 at 3:20 PM l vic <lv...@gmail.com> wrote:
>>
>>> Ok, but "mylist" is an array... Do standard processors support multiple
>>> "upsert" queries?
>>>
>>> On Wed, Sep 5, 2018 at 10:29 AM Ed B <bd...@gmail.com> wrote:
>>>
>>>> You can do SplitJson by $.mylist
>>>> Use original to get into flow that will store root elements, and take
>>>> all the splits into a flow that will store each split into another table.
>>>> I've used your JSON as an example. So there are three elements in your
>>>> list, they are now separated and you can save them independently.
>>>> [image: image.png]
>>>>
>>>> Regards,
>>>> Ed.
>>>>
>>>> On Fri, Aug 31, 2018 at 7:34 PM l vic <lv...@gmail.com> wrote:
>>>>
>>>>> Hi,
>>>>> I have a json record that contains array "mylst":
>>>>> {
>>>>> "id": 0,
>>>>> "name": "Root",
>>>>> "mylist": [{
>>>>> "id": 10,
>>>>> "info": "2am-3am"
>>>>> },
>>>>> {
>>>>> "id": 11,
>>>>> "info": "3AM-4AM"
>>>>> },
>>>>> {
>>>>> "id": 12,
>>>>> "info": "4am-5am"
>>>>> }]
>>>>>
>>>>> }
>>>>> I have to save root data into one db table and array into another...
>>>>> Can someone recommend an approach to "splitting" of record for 2 different
>>>>> database writers?
>>>>> Thank you,
>>>>> V
>>>>>
>>>>
Re: saving record into different tables?
Posted by l vic <lv...@gmail.com>.
doesn't seem to work for me: attributes don't show up after split
On Wed, Sep 5, 2018 at 3:57 PM Ed B <bd...@gmail.com> wrote:
> Not sure what you mean by "standard processors", but if you do split, it
> won't be array anymore. check the attached image again - there are now
> three flow files - each of them is an element from that mylist.
>
> On Wed, Sep 5, 2018 at 3:20 PM l vic <lv...@gmail.com> wrote:
>
>> Ok, but "mylist" is an array... Do standard processors support multiple
>> "upsert" queries?
>>
>> On Wed, Sep 5, 2018 at 10:29 AM Ed B <bd...@gmail.com> wrote:
>>
>>> You can do SplitJson by $.mylist
>>> Use original to get into flow that will store root elements, and take
>>> all the splits into a flow that will store each split into another table.
>>> I've used your JSON as an example. So there are three elements in your
>>> list, they are now separated and you can save them independently.
>>> [image: image.png]
>>>
>>> Regards,
>>> Ed.
>>>
>>> On Fri, Aug 31, 2018 at 7:34 PM l vic <lv...@gmail.com> wrote:
>>>
>>>> Hi,
>>>> I have a json record that contains array "mylst":
>>>> {
>>>> "id": 0,
>>>> "name": "Root",
>>>> "mylist": [{
>>>> "id": 10,
>>>> "info": "2am-3am"
>>>> },
>>>> {
>>>> "id": 11,
>>>> "info": "3AM-4AM"
>>>> },
>>>> {
>>>> "id": 12,
>>>> "info": "4am-5am"
>>>> }]
>>>>
>>>> }
>>>> I have to save root data into one db table and array into another...
>>>> Can someone recommend an approach to "splitting" of record for 2 different
>>>> database writers?
>>>> Thank you,
>>>> V
>>>>
>>>
Re: saving record into different tables?
Posted by Ed B <bd...@gmail.com>.
Not sure what you mean by "standard processors", but if you do split, it
won't be array anymore. check the attached image again - there are now
three flow files - each of them is an element from that mylist.
On Wed, Sep 5, 2018 at 3:20 PM l vic <lv...@gmail.com> wrote:
> Ok, but "mylist" is an array... Do standard processors support multiple
> "upsert" queries?
>
> On Wed, Sep 5, 2018 at 10:29 AM Ed B <bd...@gmail.com> wrote:
>
>> You can do SplitJson by $.mylist
>> Use original to get into flow that will store root elements, and take all
>> the splits into a flow that will store each split into another table.
>> I've used your JSON as an example. So there are three elements in your
>> list, they are now separated and you can save them independently.
>> [image: image.png]
>>
>> Regards,
>> Ed.
>>
>> On Fri, Aug 31, 2018 at 7:34 PM l vic <lv...@gmail.com> wrote:
>>
>>> Hi,
>>> I have a json record that contains array "mylst":
>>> {
>>> "id": 0,
>>> "name": "Root",
>>> "mylist": [{
>>> "id": 10,
>>> "info": "2am-3am"
>>> },
>>> {
>>> "id": 11,
>>> "info": "3AM-4AM"
>>> },
>>> {
>>> "id": 12,
>>> "info": "4am-5am"
>>> }]
>>>
>>> }
>>> I have to save root data into one db table and array into another... Can
>>> someone recommend an approach to "splitting" of record for 2 different
>>> database writers?
>>> Thank you,
>>> V
>>>
>>
Re: saving record into different tables?
Posted by l vic <lv...@gmail.com>.
Ok, but "mylist" is an array... Do standard processors support multiple
"upsert" queries?
On Wed, Sep 5, 2018 at 10:29 AM Ed B <bd...@gmail.com> wrote:
> You can do SplitJson by $.mylist
> Use original to get into flow that will store root elements, and take all
> the splits into a flow that will store each split into another table.
> I've used your JSON as an example. So there are three elements in your
> list, they are now separated and you can save them independently.
> [image: image.png]
>
> Regards,
> Ed.
>
> On Fri, Aug 31, 2018 at 7:34 PM l vic <lv...@gmail.com> wrote:
>
>> Hi,
>> I have a json record that contains array "mylst":
>> {
>> "id": 0,
>> "name": "Root",
>> "mylist": [{
>> "id": 10,
>> "info": "2am-3am"
>> },
>> {
>> "id": 11,
>> "info": "3AM-4AM"
>> },
>> {
>> "id": 12,
>> "info": "4am-5am"
>> }]
>>
>> }
>> I have to save root data into one db table and array into another... Can
>> someone recommend an approach to "splitting" of record for 2 different
>> database writers?
>> Thank you,
>> V
>>
>
Re: saving record into different tables?
Posted by Ed B <bd...@gmail.com>.
You can do SplitJson by $.mylist
Use original to get into flow that will store root elements, and take all
the splits into a flow that will store each split into another table.
I've used your JSON as an example. So there are three elements in your
list, they are now separated and you can save them independently.
[image: image.png]
Regards,
Ed.
On Fri, Aug 31, 2018 at 7:34 PM l vic <lv...@gmail.com> wrote:
> Hi,
> I have a json record that contains array "mylst":
> {
> "id": 0,
> "name": "Root",
> "mylist": [{
> "id": 10,
> "info": "2am-3am"
> },
> {
> "id": 11,
> "info": "3AM-4AM"
> },
> {
> "id": 12,
> "info": "4am-5am"
> }]
>
> }
> I have to save root data into one db table and array into another... Can
> someone recommend an approach to "splitting" of record for 2 different
> database writers?
> Thank you,
> V
>
Re: saving record into different tables?
Posted by l vic <lv...@gmail.com>.
Ok, I've got "split" working but that still leaves me with the dilemma: i
have to write multiple rows for "mylist" into the same table... Note that I
am working with db (Phoenix Query Server) that doesn't support "batching"
of multiple records in one insert as mysql. Any advise?
On Wed, Sep 5, 2018 at 10:04 PM Matt Burgess <ma...@apache.org> wrote:
> V,
>
> Perhaps ironically but for the greater good, NiFi (IMHO, with record
> processors) performs better when you don't touch the content but just
> describe what you want from it. So in your case you could send the
> content to two different branches, one could be a PutDatabaseRecord
> with a reader schema that ignores the "mylist" element, and the other
> could use Jolt / SplitRecord / ForkRecord to "hoist" the fields from
> the "mylist" element into a root array/RecordSet, then use
> PutDatabaseRecord to process it. Then you don't need a "split" or "get
> into attribute" or any other operation that isn't in line with your
> business logic.
>
> Regards,
> Matt
>
> On Fri, Aug 31, 2018 at 7:34 PM l vic <lv...@gmail.com> wrote:
> >
> > Hi,
> > I have a json record that contains array "mylst":
> > {
> > "id": 0,
> > "name": "Root",
> > "mylist": [{
> > "id": 10,
> > "info": "2am-3am"
> > },
> > {
> > "id": 11,
> > "info": "3AM-4AM"
> > },
> > {
> > "id": 12,
> > "info": "4am-5am"
> > }]
> >
> > }
> > I have to save root data into one db table and array into another... Can
> someone recommend an approach to "splitting" of record for 2 different
> database writers?
> > Thank you,
> > V
>
Re: saving record into different tables?
Posted by Matt Burgess <ma...@apache.org>.
V,
Perhaps ironically but for the greater good, NiFi (IMHO, with record
processors) performs better when you don't touch the content but just
describe what you want from it. So in your case you could send the
content to two different branches, one could be a PutDatabaseRecord
with a reader schema that ignores the "mylist" element, and the other
could use Jolt / SplitRecord / ForkRecord to "hoist" the fields from
the "mylist" element into a root array/RecordSet, then use
PutDatabaseRecord to process it. Then you don't need a "split" or "get
into attribute" or any other operation that isn't in line with your
business logic.
Regards,
Matt
On Fri, Aug 31, 2018 at 7:34 PM l vic <lv...@gmail.com> wrote:
>
> Hi,
> I have a json record that contains array "mylst":
> {
> "id": 0,
> "name": "Root",
> "mylist": [{
> "id": 10,
> "info": "2am-3am"
> },
> {
> "id": 11,
> "info": "3AM-4AM"
> },
> {
> "id": 12,
> "info": "4am-5am"
> }]
>
> }
> I have to save root data into one db table and array into another... Can someone recommend an approach to "splitting" of record for 2 different database writers?
> Thank you,
> V