You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by Flo Rance <tr...@gmail.com> on 2018/12/14 15:18:15 UTC
JSON record in database
Hello,
I'm trying to store a whole json in a json field in postgresql db.
I've tried to use PutDatabaseRecord, but I've no idea how to use
RecordReader to send the whole json as a record.
Any ideas ?
Re: JSON record in database
Posted by Flo Rance <tr...@gmail.com>.
My bad, If I explicitly set the the type to OTHERS, it works perfectly.
Object data = "{...}";
...
ps.setObject(2, data.toString(), java.sql.Types.OTHER);
On Mon, Dec 17, 2018 at 10:30 AM Flo Rance <tr...@gmail.com> wrote:
> What I mean, if I try to get an object and try to store a "stringify"
> version, I get the following error:
>
> Object data = "{...}";
> ...
> ps.setObject(2, data.toString());
> ...
> ERROR: column "data" is of type jsonb but expression is of type character
> varying
>
> On Mon, Dec 17, 2018 at 9:28 AM Flo Rance <tr...@gmail.com> wrote:
>
>> This may work with JSON field, but I'm not sure it will with JSONB
>> because it's a binary format.
>>
>>
>>
>>
>> On Sat, Dec 15, 2018 at 9:48 PM Matt Burgess <ma...@gmail.com> wrote:
>>
>>> Does NIFI-5845 [1] help? If the drivers return OTHER for JSON or JSONB
>>> types (like PostgreSQL does) then this improvement should do the stringify
>>> of JSON columns.
>>>
>>> Regards,
>>> Matt
>>>
>>> [1] https://issues.apache.org/jira/browse/NIFI-5845
>>>
>>>
>>> On Dec 15, 2018, at 8:47 AM, Mike Thomsen <mi...@gmail.com>
>>> wrote:
>>>
>>> After a little digging, it appears that the JDBC spec hasn't caught up
>>> to the inclusion of JSON as a data type. Might be the result of only a few
>>> newer databases supporting it and JSONB as native data types. Regardless,
>>> it looks like something we can probably implement for 1.9. According to the
>>> examples I found like this:
>>>
>>>
>>> https://stackoverflow.com/questions/43283689/how-to-read-json-data-type-in-mysql-using-jdbc
>>>
>>> It appears to just be "stringify the record" on the write side and then
>>> when reading it we would check to see if the schema type is RECORD for the
>>> field.
>>>
>>> Mike
>>>
>>> On Fri, Dec 14, 2018 at 11:18 AM Bryan Bende <bb...@gmail.com> wrote:
>>>
>>>> PutDatabaseRecord needs the content of the flow file to match a schema
>>>> that lines up with your DB table.
>>>>
>>>> So if you have table with a field called "json_content" then you need
>>>> a schema with that field and your flow file content would need to be
>>>> something like:
>>>>
>>>> {
>>>> "json_content" : <insert nested json as string>
>>>> }
>>>>
>>>> There may be a better way to do this with one of the other DB related
>>>> processors, or you could definitely write a custom processor that did
>>>> this.
>>>>
>>>> On Fri, Dec 14, 2018 at 10:18 AM Flo Rance <tr...@gmail.com> wrote:
>>>> >
>>>> > Hello,
>>>> >
>>>> > I'm trying to store a whole json in a json field in postgresql db.
>>>> > I've tried to use PutDatabaseRecord, but I've no idea how to use
>>>> RecordReader to send the whole json as a record.
>>>> >
>>>> > Any ideas ?
>>>>
>>>
Re: JSON record in database
Posted by Flo Rance <tr...@gmail.com>.
What I mean, if I try to get an object and try to store a "stringify"
version, I get the following error:
Object data = "{...}";
...
ps.setObject(2, data.toString());
...
ERROR: column "data" is of type jsonb but expression is of type character
varying
On Mon, Dec 17, 2018 at 9:28 AM Flo Rance <tr...@gmail.com> wrote:
> This may work with JSON field, but I'm not sure it will with JSONB because
> it's a binary format.
>
>
>
>
> On Sat, Dec 15, 2018 at 9:48 PM Matt Burgess <ma...@gmail.com> wrote:
>
>> Does NIFI-5845 [1] help? If the drivers return OTHER for JSON or JSONB
>> types (like PostgreSQL does) then this improvement should do the stringify
>> of JSON columns.
>>
>> Regards,
>> Matt
>>
>> [1] https://issues.apache.org/jira/browse/NIFI-5845
>>
>>
>> On Dec 15, 2018, at 8:47 AM, Mike Thomsen <mi...@gmail.com> wrote:
>>
>> After a little digging, it appears that the JDBC spec hasn't caught up to
>> the inclusion of JSON as a data type. Might be the result of only a few
>> newer databases supporting it and JSONB as native data types. Regardless,
>> it looks like something we can probably implement for 1.9. According to the
>> examples I found like this:
>>
>>
>> https://stackoverflow.com/questions/43283689/how-to-read-json-data-type-in-mysql-using-jdbc
>>
>> It appears to just be "stringify the record" on the write side and then
>> when reading it we would check to see if the schema type is RECORD for the
>> field.
>>
>> Mike
>>
>> On Fri, Dec 14, 2018 at 11:18 AM Bryan Bende <bb...@gmail.com> wrote:
>>
>>> PutDatabaseRecord needs the content of the flow file to match a schema
>>> that lines up with your DB table.
>>>
>>> So if you have table with a field called "json_content" then you need
>>> a schema with that field and your flow file content would need to be
>>> something like:
>>>
>>> {
>>> "json_content" : <insert nested json as string>
>>> }
>>>
>>> There may be a better way to do this with one of the other DB related
>>> processors, or you could definitely write a custom processor that did
>>> this.
>>>
>>> On Fri, Dec 14, 2018 at 10:18 AM Flo Rance <tr...@gmail.com> wrote:
>>> >
>>> > Hello,
>>> >
>>> > I'm trying to store a whole json in a json field in postgresql db.
>>> > I've tried to use PutDatabaseRecord, but I've no idea how to use
>>> RecordReader to send the whole json as a record.
>>> >
>>> > Any ideas ?
>>>
>>
Re: JSON record in database
Posted by Flo Rance <tr...@gmail.com>.
This may work with JSON field, but I'm not sure it will with JSONB because
it's a binary format.
On Sat, Dec 15, 2018 at 9:48 PM Matt Burgess <ma...@gmail.com> wrote:
> Does NIFI-5845 [1] help? If the drivers return OTHER for JSON or JSONB
> types (like PostgreSQL does) then this improvement should do the stringify
> of JSON columns.
>
> Regards,
> Matt
>
> [1] https://issues.apache.org/jira/browse/NIFI-5845
>
>
> On Dec 15, 2018, at 8:47 AM, Mike Thomsen <mi...@gmail.com> wrote:
>
> After a little digging, it appears that the JDBC spec hasn't caught up to
> the inclusion of JSON as a data type. Might be the result of only a few
> newer databases supporting it and JSONB as native data types. Regardless,
> it looks like something we can probably implement for 1.9. According to the
> examples I found like this:
>
>
> https://stackoverflow.com/questions/43283689/how-to-read-json-data-type-in-mysql-using-jdbc
>
> It appears to just be "stringify the record" on the write side and then
> when reading it we would check to see if the schema type is RECORD for the
> field.
>
> Mike
>
> On Fri, Dec 14, 2018 at 11:18 AM Bryan Bende <bb...@gmail.com> wrote:
>
>> PutDatabaseRecord needs the content of the flow file to match a schema
>> that lines up with your DB table.
>>
>> So if you have table with a field called "json_content" then you need
>> a schema with that field and your flow file content would need to be
>> something like:
>>
>> {
>> "json_content" : <insert nested json as string>
>> }
>>
>> There may be a better way to do this with one of the other DB related
>> processors, or you could definitely write a custom processor that did
>> this.
>>
>> On Fri, Dec 14, 2018 at 10:18 AM Flo Rance <tr...@gmail.com> wrote:
>> >
>> > Hello,
>> >
>> > I'm trying to store a whole json in a json field in postgresql db.
>> > I've tried to use PutDatabaseRecord, but I've no idea how to use
>> RecordReader to send the whole json as a record.
>> >
>> > Any ideas ?
>>
>
Re: JSON record in database
Posted by Flo Rance <tr...@gmail.com>.
Ok, I did so.
https://issues.apache.org/jira/browse/NIFI-5901
Thanks,
Flo
On Mon, Dec 17, 2018 at 2:29 PM Matt Burgess <ma...@gmail.com> wrote:
> No, that case was only for reading from RDBMS or Hive, because the type is
> OTHER we assume the object can be represented as a String so we just get
> the Object and call toString() on it, basically a “best-effort”
> interpretation which happens to work for JSON and JSONB fields (at least
> for the DBs I tried).
>
> We might be able to do some DB-specific stuff using the Database adapters,
> but it will be tricky to indicate something like a JSON field in the
> schema, we may need to add metadata to the avro field schema and then later
> expand the string to a sub-record or something. But not all SQL processors
> use the DB adapters, such as ExecuteSQL and ExecuteSQLRecord. So far the
> adapters have been used to generate SQL, not to parse result sets. So this
> kind of support is likely a good deal of somewhat invasive work. Please
> feel free to write a Jira with your use case and we can fill in the
> discussion there.
>
> Regards,
> Matt
>
>
> On Dec 17, 2018, at 5:25 AM, Flo Rance <tr...@gmail.com> wrote:
>
> Does NIFI-5845 allow to use an avro schema with a type of OTHER for
> json/jsonb and then use it in PutDatabaseRecord ?
>
> fields": [
> {"name": "data", "type": "other"},
>
>
> On Sat, Dec 15, 2018 at 9:48 PM Matt Burgess <ma...@gmail.com> wrote:
>
>> Does NIFI-5845 [1] help? If the drivers return OTHER for JSON or JSONB
>> types (like PostgreSQL does) then this improvement should do the stringify
>> of JSON columns.
>>
>> Regards,
>> Matt
>>
>> [1] https://issues.apache.org/jira/browse/NIFI-5845
>>
>>
>> On Dec 15, 2018, at 8:47 AM, Mike Thomsen <mi...@gmail.com> wrote:
>>
>> After a little digging, it appears that the JDBC spec hasn't caught up to
>> the inclusion of JSON as a data type. Might be the result of only a few
>> newer databases supporting it and JSONB as native data types. Regardless,
>> it looks like something we can probably implement for 1.9. According to the
>> examples I found like this:
>>
>>
>> https://stackoverflow.com/questions/43283689/how-to-read-json-data-type-in-mysql-using-jdbc
>>
>> It appears to just be "stringify the record" on the write side and then
>> when reading it we would check to see if the schema type is RECORD for the
>> field.
>>
>> Mike
>>
>> On Fri, Dec 14, 2018 at 11:18 AM Bryan Bende <bb...@gmail.com> wrote:
>>
>>> PutDatabaseRecord needs the content of the flow file to match a schema
>>> that lines up with your DB table.
>>>
>>> So if you have table with a field called "json_content" then you need
>>> a schema with that field and your flow file content would need to be
>>> something like:
>>>
>>> {
>>> "json_content" : <insert nested json as string>
>>> }
>>>
>>> There may be a better way to do this with one of the other DB related
>>> processors, or you could definitely write a custom processor that did
>>> this.
>>>
>>> On Fri, Dec 14, 2018 at 10:18 AM Flo Rance <tr...@gmail.com> wrote:
>>> >
>>> > Hello,
>>> >
>>> > I'm trying to store a whole json in a json field in postgresql db.
>>> > I've tried to use PutDatabaseRecord, but I've no idea how to use
>>> RecordReader to send the whole json as a record.
>>> >
>>> > Any ideas ?
>>>
>>
Re: JSON record in database
Posted by Matt Burgess <ma...@gmail.com>.
No, that case was only for reading from RDBMS or Hive, because the type is OTHER we assume the object can be represented as a String so we just get the Object and call toString() on it, basically a “best-effort” interpretation which happens to work for JSON and JSONB fields (at least for the DBs I tried).
We might be able to do some DB-specific stuff using the Database adapters, but it will be tricky to indicate something like a JSON field in the schema, we may need to add metadata to the avro field schema and then later expand the string to a sub-record or something. But not all SQL processors use the DB adapters, such as ExecuteSQL and ExecuteSQLRecord. So far the adapters have been used to generate SQL, not to parse result sets. So this kind of support is likely a good deal of somewhat invasive work. Please feel free to write a Jira with your use case and we can fill in the discussion there.
Regards,
Matt
> On Dec 17, 2018, at 5:25 AM, Flo Rance <tr...@gmail.com> wrote:
>
> Does NIFI-5845 allow to use an avro schema with a type of OTHER for json/jsonb and then use it in PutDatabaseRecord ?
>
> fields": [
> {"name": "data", "type": "other"},
>
>> On Sat, Dec 15, 2018 at 9:48 PM Matt Burgess <ma...@gmail.com> wrote:
>> Does NIFI-5845 [1] help? If the drivers return OTHER for JSON or JSONB types (like PostgreSQL does) then this improvement should do the stringify of JSON columns.
>>
>> Regards,
>> Matt
>>
>> [1] https://issues.apache.org/jira/browse/NIFI-5845
>>
>>
>>> On Dec 15, 2018, at 8:47 AM, Mike Thomsen <mi...@gmail.com> wrote:
>>>
>>> After a little digging, it appears that the JDBC spec hasn't caught up to the inclusion of JSON as a data type. Might be the result of only a few newer databases supporting it and JSONB as native data types. Regardless, it looks like something we can probably implement for 1.9. According to the examples I found like this:
>>>
>>> https://stackoverflow.com/questions/43283689/how-to-read-json-data-type-in-mysql-using-jdbc
>>>
>>> It appears to just be "stringify the record" on the write side and then when reading it we would check to see if the schema type is RECORD for the field.
>>>
>>> Mike
>>>
>>>> On Fri, Dec 14, 2018 at 11:18 AM Bryan Bende <bb...@gmail.com> wrote:
>>>> PutDatabaseRecord needs the content of the flow file to match a schema
>>>> that lines up with your DB table.
>>>>
>>>> So if you have table with a field called "json_content" then you need
>>>> a schema with that field and your flow file content would need to be
>>>> something like:
>>>>
>>>> {
>>>> "json_content" : <insert nested json as string>
>>>> }
>>>>
>>>> There may be a better way to do this with one of the other DB related
>>>> processors, or you could definitely write a custom processor that did
>>>> this.
>>>>
>>>> On Fri, Dec 14, 2018 at 10:18 AM Flo Rance <tr...@gmail.com> wrote:
>>>> >
>>>> > Hello,
>>>> >
>>>> > I'm trying to store a whole json in a json field in postgresql db.
>>>> > I've tried to use PutDatabaseRecord, but I've no idea how to use RecordReader to send the whole json as a record.
>>>> >
>>>> > Any ideas ?
Re: JSON record in database
Posted by Flo Rance <tr...@gmail.com>.
Does NIFI-5845 allow to use an avro schema with a type of OTHER for
json/jsonb and then use it in PutDatabaseRecord ?
fields": [
{"name": "data", "type": "other"},
On Sat, Dec 15, 2018 at 9:48 PM Matt Burgess <ma...@gmail.com> wrote:
> Does NIFI-5845 [1] help? If the drivers return OTHER for JSON or JSONB
> types (like PostgreSQL does) then this improvement should do the stringify
> of JSON columns.
>
> Regards,
> Matt
>
> [1] https://issues.apache.org/jira/browse/NIFI-5845
>
>
> On Dec 15, 2018, at 8:47 AM, Mike Thomsen <mi...@gmail.com> wrote:
>
> After a little digging, it appears that the JDBC spec hasn't caught up to
> the inclusion of JSON as a data type. Might be the result of only a few
> newer databases supporting it and JSONB as native data types. Regardless,
> it looks like something we can probably implement for 1.9. According to the
> examples I found like this:
>
>
> https://stackoverflow.com/questions/43283689/how-to-read-json-data-type-in-mysql-using-jdbc
>
> It appears to just be "stringify the record" on the write side and then
> when reading it we would check to see if the schema type is RECORD for the
> field.
>
> Mike
>
> On Fri, Dec 14, 2018 at 11:18 AM Bryan Bende <bb...@gmail.com> wrote:
>
>> PutDatabaseRecord needs the content of the flow file to match a schema
>> that lines up with your DB table.
>>
>> So if you have table with a field called "json_content" then you need
>> a schema with that field and your flow file content would need to be
>> something like:
>>
>> {
>> "json_content" : <insert nested json as string>
>> }
>>
>> There may be a better way to do this with one of the other DB related
>> processors, or you could definitely write a custom processor that did
>> this.
>>
>> On Fri, Dec 14, 2018 at 10:18 AM Flo Rance <tr...@gmail.com> wrote:
>> >
>> > Hello,
>> >
>> > I'm trying to store a whole json in a json field in postgresql db.
>> > I've tried to use PutDatabaseRecord, but I've no idea how to use
>> RecordReader to send the whole json as a record.
>> >
>> > Any ideas ?
>>
>
Re: JSON record in database
Posted by Matt Burgess <ma...@gmail.com>.
Does NIFI-5845 [1] help? If the drivers return OTHER for JSON or JSONB types (like PostgreSQL does) then this improvement should do the stringify of JSON columns.
Regards,
Matt
[1] https://issues.apache.org/jira/browse/NIFI-5845
> On Dec 15, 2018, at 8:47 AM, Mike Thomsen <mi...@gmail.com> wrote:
>
> After a little digging, it appears that the JDBC spec hasn't caught up to the inclusion of JSON as a data type. Might be the result of only a few newer databases supporting it and JSONB as native data types. Regardless, it looks like something we can probably implement for 1.9. According to the examples I found like this:
>
> https://stackoverflow.com/questions/43283689/how-to-read-json-data-type-in-mysql-using-jdbc
>
> It appears to just be "stringify the record" on the write side and then when reading it we would check to see if the schema type is RECORD for the field.
>
> Mike
>
>> On Fri, Dec 14, 2018 at 11:18 AM Bryan Bende <bb...@gmail.com> wrote:
>> PutDatabaseRecord needs the content of the flow file to match a schema
>> that lines up with your DB table.
>>
>> So if you have table with a field called "json_content" then you need
>> a schema with that field and your flow file content would need to be
>> something like:
>>
>> {
>> "json_content" : <insert nested json as string>
>> }
>>
>> There may be a better way to do this with one of the other DB related
>> processors, or you could definitely write a custom processor that did
>> this.
>>
>> On Fri, Dec 14, 2018 at 10:18 AM Flo Rance <tr...@gmail.com> wrote:
>> >
>> > Hello,
>> >
>> > I'm trying to store a whole json in a json field in postgresql db.
>> > I've tried to use PutDatabaseRecord, but I've no idea how to use RecordReader to send the whole json as a record.
>> >
>> > Any ideas ?
Re: JSON record in database
Posted by Mike Thomsen <mi...@gmail.com>.
After a little digging, it appears that the JDBC spec hasn't caught up to
the inclusion of JSON as a data type. Might be the result of only a few
newer databases supporting it and JSONB as native data types. Regardless,
it looks like something we can probably implement for 1.9. According to the
examples I found like this:
https://stackoverflow.com/questions/43283689/how-to-read-json-data-type-in-mysql-using-jdbc
It appears to just be "stringify the record" on the write side and then
when reading it we would check to see if the schema type is RECORD for the
field.
Mike
On Fri, Dec 14, 2018 at 11:18 AM Bryan Bende <bb...@gmail.com> wrote:
> PutDatabaseRecord needs the content of the flow file to match a schema
> that lines up with your DB table.
>
> So if you have table with a field called "json_content" then you need
> a schema with that field and your flow file content would need to be
> something like:
>
> {
> "json_content" : <insert nested json as string>
> }
>
> There may be a better way to do this with one of the other DB related
> processors, or you could definitely write a custom processor that did
> this.
>
> On Fri, Dec 14, 2018 at 10:18 AM Flo Rance <tr...@gmail.com> wrote:
> >
> > Hello,
> >
> > I'm trying to store a whole json in a json field in postgresql db.
> > I've tried to use PutDatabaseRecord, but I've no idea how to use
> RecordReader to send the whole json as a record.
> >
> > Any ideas ?
>
Re: JSON record in database
Posted by Bryan Bende <bb...@gmail.com>.
PutDatabaseRecord needs the content of the flow file to match a schema
that lines up with your DB table.
So if you have table with a field called "json_content" then you need
a schema with that field and your flow file content would need to be
something like:
{
"json_content" : <insert nested json as string>
}
There may be a better way to do this with one of the other DB related
processors, or you could definitely write a custom processor that did
this.
On Fri, Dec 14, 2018 at 10:18 AM Flo Rance <tr...@gmail.com> wrote:
>
> Hello,
>
> I'm trying to store a whole json in a json field in postgresql db.
> I've tried to use PutDatabaseRecord, but I've no idea how to use RecordReader to send the whole json as a record.
>
> Any ideas ?