You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by Matt Burgess <ma...@apache.org> on 2022/12/16 03:20:47 UTC
Re: json into a json-enabled DB
Geoffrey,
The biggest problem with JSON columns across the board is that the
JDBC and java.sql.Types specs don't handle them natively, and NiFi
records don't recognize JSON as a particular type, we are only
interested in the overall datatype such as String since NiFi records
can be in any supported format. In my experience these are handled by
setting the JSON column to type java.sql.OTHER (like PostgreSQL) and
they are willing to accept the value as a String (see NIFI-5901 [1]),
and we put in code to handle it as such (see NIFI-5845 [2]). For NiFi
it's been more of an ad-hoc type of support where maybe if the SQL
type is custom and unique we can handle such things (like sql_variant
in MSSQL via NIFI-5819 [3]), but due to the nature of the custom type
it's difficult to handle in any sort of consistent way. Happy to hear
your thoughts and input, perhaps we can add some ad-hoc support for
your use case?
Regards,
Matt
[1] https://issues.apache.org/jira/browse/NIFI-5901
[2] https://issues.apache.org/jira/browse/NIFI-5845
[3] https://issues.apache.org/jira/browse/NIFI-5819
On Wed, Dec 14, 2022 at 3:55 PM Greene (US), Geoffrey N
<ge...@boeing.com> wrote:
>
> Some databases (postgres, sql server, others) support native json columns.
>
> With postgres, there’s a native jsonb type, with sql server it’s a string type, that you can treat as json.
>
>
>
> In any event, once you have the json in the database, one can then query it, e.g.:
>
>
>
> SELECT id,product_name,
>
> JSON_VALUE(attributes, '$.material') AS material
>
> FROM jsontest;
>
>
>
> So, here’s my question:
>
>
>
> If you have a flow file that contains json, whats the best way to insert that into a database?
>
> The only thing I’ve thought of so far is if you have the json string
>
> {“material” : “plastic”}
>
> You then use a TEXT processor to turn that into
>
> {“attributes”: {‘{“material” : “plastic”}’}
>
> And then use a PutDatabaseRecord to actually write the entry.
>
>
>
> Is there a better, or more efficient way to do it?
>
>
>
>
>
>
Re: [EXTERNAL] Re: json into a json-enabled DB
Posted by Mike Thomsen <mi...@gmail.com>.
UpdateRecord should be useful for that. If you use escapeJson, you can
create an escaped JSON string from the result of a record path
operation.
On Fri, Dec 16, 2022 at 11:22 AM Greene (US), Geoffrey N
<ge...@boeing.com> wrote:
>
> Yeah, I was able to get json into the db it by using strings.
>
> Unfortunately, I have some escape characters in my strings, and it looks like I have to escape my escapes. Which ends up being either a few text processors or a groovy script.
>
> To paraphrase the meme, "yo dawg I hear you like escapes with your escapes..."
>
> But you are correct, I was able to make it happen. I was just hoping for something a little more record-oriented (or something). I guess if it works, don't complain...
>
> -----Original Message-----
> From: Mike Thomsen [mailto:mikerthomsen@gmail.com]
> Sent: Friday, December 16, 2022 10:59 AM
> To: users@nifi.apache.org
> Subject: [EXTERNAL] Re: json into a json-enabled DB
>
> EXT email: be mindful of links/attachments.
>
>
>
> To Matt's point, I've tested insert by doing a record field of String going to JSON/JSONB in Postgres and MySQL, and that worked just fine.
> I'm not sure if we're at a point where we can do a reader with one schema and a writer with another schema, but it should be pretty straight forward to fix so that worst case scenario that is ConvertRecord -> PutDatabaseRecord
>
> On Thu, Dec 15, 2022 at 10:21 PM Matt Burgess <ma...@apache.org> wrote:
> >
> > Geoffrey,
> >
> > The biggest problem with JSON columns across the board is that the
> > JDBC and java.sql.Types specs don't handle them natively, and NiFi
> > records don't recognize JSON as a particular type, we are only
> > interested in the overall datatype such as String since NiFi records
> > can be in any supported format. In my experience these are handled by
> > setting the JSON column to type java.sql.OTHER (like PostgreSQL) and
> > they are willing to accept the value as a String (see NIFI-5901 [1]),
> > and we put in code to handle it as such (see NIFI-5845 [2]). For NiFi
> > it's been more of an ad-hoc type of support where maybe if the SQL
> > type is custom and unique we can handle such things (like sql_variant
> > in MSSQL via NIFI-5819 [3]), but due to the nature of the custom type
> > it's difficult to handle in any sort of consistent way. Happy to hear
> > your thoughts and input, perhaps we can add some ad-hoc support for
> > your use case?
> >
> > Regards,
> > Matt
> >
> > [1] https://issues.apache.org/jira/browse/NIFI-5901
> > [2] https://issues.apache.org/jira/browse/NIFI-5845
> > [3] https://issues.apache.org/jira/browse/NIFI-5819
> >
> > On Wed, Dec 14, 2022 at 3:55 PM Greene (US), Geoffrey N
> > <ge...@boeing.com> wrote:
> > >
> > > Some databases (postgres, sql server, others) support native json columns.
> > >
> > > With postgres, there’s a native jsonb type, with sql server it’s a string type, that you can treat as json.
> > >
> > >
> > >
> > > In any event, once you have the json in the database, one can then query it, e.g.:
> > >
> > >
> > >
> > > SELECT id,product_name,
> > >
> > > JSON_VALUE(attributes, '$.material') AS material
> > >
> > > FROM jsontest;
> > >
> > >
> > >
> > > So, here’s my question:
> > >
> > >
> > >
> > > If you have a flow file that contains json, whats the best way to insert that into a database?
> > >
> > > The only thing I’ve thought of so far is if you have the json string
> > >
> > > {“material” : “plastic”}
> > >
> > > You then use a TEXT processor to turn that into
> > >
> > > {“attributes”: {‘{“material” : “plastic”}’}
> > >
> > > And then use a PutDatabaseRecord to actually write the entry.
> > >
> > >
> > >
> > > Is there a better, or more efficient way to do it?
> > >
> > >
> > >
> > >
> > >
> > >
>
RE: [EXTERNAL] Re: json into a json-enabled DB
Posted by "Greene (US), Geoffrey N" <ge...@boeing.com>.
Yeah, I was able to get json into the db it by using strings.
Unfortunately, I have some escape characters in my strings, and it looks like I have to escape my escapes. Which ends up being either a few text processors or a groovy script.
To paraphrase the meme, "yo dawg I hear you like escapes with your escapes..."
But you are correct, I was able to make it happen. I was just hoping for something a little more record-oriented (or something). I guess if it works, don't complain...
-----Original Message-----
From: Mike Thomsen [mailto:mikerthomsen@gmail.com]
Sent: Friday, December 16, 2022 10:59 AM
To: users@nifi.apache.org
Subject: [EXTERNAL] Re: json into a json-enabled DB
EXT email: be mindful of links/attachments.
To Matt's point, I've tested insert by doing a record field of String going to JSON/JSONB in Postgres and MySQL, and that worked just fine.
I'm not sure if we're at a point where we can do a reader with one schema and a writer with another schema, but it should be pretty straight forward to fix so that worst case scenario that is ConvertRecord -> PutDatabaseRecord
On Thu, Dec 15, 2022 at 10:21 PM Matt Burgess <ma...@apache.org> wrote:
>
> Geoffrey,
>
> The biggest problem with JSON columns across the board is that the
> JDBC and java.sql.Types specs don't handle them natively, and NiFi
> records don't recognize JSON as a particular type, we are only
> interested in the overall datatype such as String since NiFi records
> can be in any supported format. In my experience these are handled by
> setting the JSON column to type java.sql.OTHER (like PostgreSQL) and
> they are willing to accept the value as a String (see NIFI-5901 [1]),
> and we put in code to handle it as such (see NIFI-5845 [2]). For NiFi
> it's been more of an ad-hoc type of support where maybe if the SQL
> type is custom and unique we can handle such things (like sql_variant
> in MSSQL via NIFI-5819 [3]), but due to the nature of the custom type
> it's difficult to handle in any sort of consistent way. Happy to hear
> your thoughts and input, perhaps we can add some ad-hoc support for
> your use case?
>
> Regards,
> Matt
>
> [1] https://issues.apache.org/jira/browse/NIFI-5901
> [2] https://issues.apache.org/jira/browse/NIFI-5845
> [3] https://issues.apache.org/jira/browse/NIFI-5819
>
> On Wed, Dec 14, 2022 at 3:55 PM Greene (US), Geoffrey N
> <ge...@boeing.com> wrote:
> >
> > Some databases (postgres, sql server, others) support native json columns.
> >
> > With postgres, there’s a native jsonb type, with sql server it’s a string type, that you can treat as json.
> >
> >
> >
> > In any event, once you have the json in the database, one can then query it, e.g.:
> >
> >
> >
> > SELECT id,product_name,
> >
> > JSON_VALUE(attributes, '$.material') AS material
> >
> > FROM jsontest;
> >
> >
> >
> > So, here’s my question:
> >
> >
> >
> > If you have a flow file that contains json, whats the best way to insert that into a database?
> >
> > The only thing I’ve thought of so far is if you have the json string
> >
> > {“material” : “plastic”}
> >
> > You then use a TEXT processor to turn that into
> >
> > {“attributes”: {‘{“material” : “plastic”}’}
> >
> > And then use a PutDatabaseRecord to actually write the entry.
> >
> >
> >
> > Is there a better, or more efficient way to do it?
> >
> >
> >
> >
> >
> >
Re: json into a json-enabled DB
Posted by Mike Thomsen <mi...@gmail.com>.
To Matt's point, I've tested insert by doing a record field of String
going to JSON/JSONB in Postgres and MySQL, and that worked just fine.
I'm not sure if we're at a point where we can do a reader with one
schema and a writer with another schema, but it should be pretty
straight forward to fix so that worst case scenario that is
ConvertRecord -> PutDatabaseRecord
On Thu, Dec 15, 2022 at 10:21 PM Matt Burgess <ma...@apache.org> wrote:
>
> Geoffrey,
>
> The biggest problem with JSON columns across the board is that the
> JDBC and java.sql.Types specs don't handle them natively, and NiFi
> records don't recognize JSON as a particular type, we are only
> interested in the overall datatype such as String since NiFi records
> can be in any supported format. In my experience these are handled by
> setting the JSON column to type java.sql.OTHER (like PostgreSQL) and
> they are willing to accept the value as a String (see NIFI-5901 [1]),
> and we put in code to handle it as such (see NIFI-5845 [2]). For NiFi
> it's been more of an ad-hoc type of support where maybe if the SQL
> type is custom and unique we can handle such things (like sql_variant
> in MSSQL via NIFI-5819 [3]), but due to the nature of the custom type
> it's difficult to handle in any sort of consistent way. Happy to hear
> your thoughts and input, perhaps we can add some ad-hoc support for
> your use case?
>
> Regards,
> Matt
>
> [1] https://issues.apache.org/jira/browse/NIFI-5901
> [2] https://issues.apache.org/jira/browse/NIFI-5845
> [3] https://issues.apache.org/jira/browse/NIFI-5819
>
> On Wed, Dec 14, 2022 at 3:55 PM Greene (US), Geoffrey N
> <ge...@boeing.com> wrote:
> >
> > Some databases (postgres, sql server, others) support native json columns.
> >
> > With postgres, there’s a native jsonb type, with sql server it’s a string type, that you can treat as json.
> >
> >
> >
> > In any event, once you have the json in the database, one can then query it, e.g.:
> >
> >
> >
> > SELECT id,product_name,
> >
> > JSON_VALUE(attributes, '$.material') AS material
> >
> > FROM jsontest;
> >
> >
> >
> > So, here’s my question:
> >
> >
> >
> > If you have a flow file that contains json, whats the best way to insert that into a database?
> >
> > The only thing I’ve thought of so far is if you have the json string
> >
> > {“material” : “plastic”}
> >
> > You then use a TEXT processor to turn that into
> >
> > {“attributes”: {‘{“material” : “plastic”}’}
> >
> > And then use a PutDatabaseRecord to actually write the entry.
> >
> >
> >
> > Is there a better, or more efficient way to do it?
> >
> >
> >
> >
> >
> >