You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by Mike Thomsen <mi...@gmail.com> on 2018/04/05 19:05:29 UTC

PutDatabaseRecord won't put Timestamp field

Found these errors in the Docker logs:

postgres_1       | 2018-04-05 18:33:22.183 UTC [51] ERROR:  column
"timestamp_field" is of type timestamp without time zone but expression is
of type bigint at character 282
postgres_1       | 2018-04-05 18:33:22.183 UTC [51] HINT:  You will need to
rewrite or cast the expression.
postgres_1       | 2018-04-05 18:33:22.183 UTC [51] STATEMENT:  INSERT INTO
provenance (componentid, componentname, componenttype, details, entityid,
entitysize, entitytype, eventid, eventtype, processgroupid,
processgroupname, record_count, schema_name, timestamp_field) VALUES
($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
nifi2            | 2018-04-05 18:33:22,184 WARN [Timer-Driven Process
Thread-2] o.a.n.p.standard.PutDatabaseRecord
PutDatabaseRecord[id=dfdd3dd1-21ee-16e2-09cc-159b7c7f8f54] Failed to
process
StandardFlowFileRecord[uuid=8cf3b521-ac0b-4149-a60e-5fa7b2d2b3c5,claim=StandardContentClaim
[resourceClaim=StandardResourceClaim[id=1522953007606-749,
container=default, section=749], offset=434600,
length=528],offset=0,name=4306158395541,size=528] due to
java.sql.BatchUpdateException: Batch entry 0 INSERT INTO provenance
(componentid, componentname, componenttype, details, entityid, entitysize,
entitytype, eventid, eventtype, processgroupid, processgroupname,
record_count, schema_name, timestamp_field) VALUES
('8d08a7d3-0162-1000-7216-7e0b426e774a','EvaluateJsonPath','EvaluateJsonPath',NULL,'26a1efa3-bcd3-4015-8a43-2a2c43c05714',66307,'org.apache.nifi.flowfile.FlowFile','e841aa4e-a7d3-4a6e-b78c-4e785f53b60e','ATTRIBUTES_MODIFIED','8c5c89d5-0162-1000-4395-a38d1c7b0b2f','Mongo
ES Test',NULL,NULL,1522952968586) was aborted: ERROR: column
"timestamp_field" is of type timestamp without time zone but expression is
of type bigint

I have the following DDL and Avro schema:

create table provenance (
id serial,
componentId varchar(128),
componentName varchar(256),
componentType varchar(128),
details varchar(256),
entityId varchar(256),
entitySize int,
entityType varchar(128),
eventId varchar(128),
eventType varchar(128),
processGroupId varchar(128),
processGroupName varchar(128), record_count int,
schema_name varchar(64),
timestamp_field timestamp
)

{
"type": "record",
"name": "ProvenanceEvent",
"fields": [
{ "name": "componentId", "type": ["null", "string"] },
{ "name": "componentName", "type": ["null", "string"] },
{ "name": "componentType", "type": ["null", "string"] },
{ "name": "details", "type": ["null", "string"] },
{ "name": "entityId", "type": ["null", "string"] },
{ "name": "entitySize", "type": ["null", "int"] },
{ "name": "entityType", "type": ["null", "string"] },
{ "name": "eventId", "type": ["null", "string"] },
{ "name": "eventType", "type": ["null", "string"] },
{ "name": "processGroupId", "type": ["null", "string"] },
{ "name": "processGroupName", "type": ["null", "string"] },
{ "name": "record_count", "type": ["null", "int"] },
{ "name": "schema_name", "type": ["null", "string"] },
{ "name": "timestamp_field", "type": "long", "logicalType":
"timestamp-millis" }
]
}

I set the JsonTreeReader to use this formatting option for reading in the
original ISO8601 string:

yyyy-MM-dd'T'HH:mm:ssZ

Everything looks fine until it gets to the processor. Any ideas?

Thanks,

Mike

Re: PutDatabaseRecord won't put Timestamp field

Posted by Matt Burgess <ma...@apache.org>.
Mike,

Yeah I was using 1.6 (master) but I'm not aware of any changes between
the two versions that would affect that. It looks like in your version
when record.getValues() is called, it is not converting the logical
Avro type of "timestamp" into a Timestamp and is instead just
interpreting it as a Long. I will look at Jira and the Git history to
see if there was a bug that was fixed.

Regards,
Matt


On Thu, Apr 5, 2018 at 6:10 PM, Mike Thomsen <mi...@gmail.com> wrote:
> Matt were you using 1.6?
>
> On Thu, Apr 5, 2018 at 3:56 PM Mike Thomsen <mi...@gmail.com> wrote:
>>
>> 1.5
>>
>> Thanks,
>>
>> Mike
>>
>> On Thu, Apr 5, 2018 at 3:40 PM, Matt Burgess <ma...@apache.org> wrote:
>>>
>>> Mike,
>>>
>>> I can't reproduce this, I use the same DDL and Avro schema, with data
>>> coming in from the SiteToSiteProvenanceReportingTask and going to
>>> Postgres, and it works fine. What version of NiFi are you using?
>>>
>>> Regards,
>>> Matt
>>>
>>>
>>> On Thu, Apr 5, 2018 at 3:05 PM, Mike Thomsen <mi...@gmail.com>
>>> wrote:
>>> > Found these errors in the Docker logs:
>>> >
>>> > postgres_1       | 2018-04-05 18:33:22.183 UTC [51] ERROR:  column
>>> > "timestamp_field" is of type timestamp without time zone but expression
>>> > is
>>> > of type bigint at character 282
>>> > postgres_1       | 2018-04-05 18:33:22.183 UTC [51] HINT:  You will
>>> > need to
>>> > rewrite or cast the expression.
>>> > postgres_1       | 2018-04-05 18:33:22.183 UTC [51] STATEMENT:  INSERT
>>> > INTO
>>> > provenance (componentid, componentname, componenttype, details,
>>> > entityid,
>>> > entitysize, entitytype, eventid, eventtype, processgroupid,
>>> > processgroupname, record_count, schema_name, timestamp_field) VALUES
>>> > ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
>>> > nifi2            | 2018-04-05 18:33:22,184 WARN [Timer-Driven Process
>>> > Thread-2] o.a.n.p.standard.PutDatabaseRecord
>>> > PutDatabaseRecord[id=dfdd3dd1-21ee-16e2-09cc-159b7c7f8f54] Failed to
>>> > process
>>> >
>>> > StandardFlowFileRecord[uuid=8cf3b521-ac0b-4149-a60e-5fa7b2d2b3c5,claim=StandardContentClaim
>>> > [resourceClaim=StandardResourceClaim[id=1522953007606-749,
>>> > container=default, section=749], offset=434600,
>>> > length=528],offset=0,name=4306158395541,size=528] due to
>>> > java.sql.BatchUpdateException: Batch entry 0 INSERT INTO provenance
>>> > (componentid, componentname, componenttype, details, entityid,
>>> > entitysize,
>>> > entitytype, eventid, eventtype, processgroupid, processgroupname,
>>> > record_count, schema_name, timestamp_field) VALUES
>>> >
>>> > ('8d08a7d3-0162-1000-7216-7e0b426e774a','EvaluateJsonPath','EvaluateJsonPath',NULL,'26a1efa3-bcd3-4015-8a43-2a2c43c05714',66307,'org.apache.nifi.flowfile.FlowFile','e841aa4e-a7d3-4a6e-b78c-4e785f53b60e','ATTRIBUTES_MODIFIED','8c5c89d5-0162-1000-4395-a38d1c7b0b2f','Mongo
>>> > ES Test',NULL,NULL,1522952968586) was aborted: ERROR: column
>>> > "timestamp_field" is of type timestamp without time zone but expression
>>> > is
>>> > of type bigint
>>> >
>>> > I have the following DDL and Avro schema:
>>> >
>>> > create table provenance (
>>> > id serial,
>>> > componentId varchar(128),
>>> > componentName varchar(256),
>>> > componentType varchar(128),
>>> > details varchar(256),
>>> > entityId varchar(256),
>>> > entitySize int,
>>> > entityType varchar(128),
>>> > eventId varchar(128),
>>> > eventType varchar(128),
>>> > processGroupId varchar(128),
>>> > processGroupName varchar(128), record_count int,
>>> > schema_name varchar(64),
>>> > timestamp_field timestamp
>>> > )
>>> >
>>> > {
>>> > "type": "record",
>>> > "name": "ProvenanceEvent",
>>> > "fields": [
>>> > { "name": "componentId", "type": ["null", "string"] },
>>> > { "name": "componentName", "type": ["null", "string"] },
>>> > { "name": "componentType", "type": ["null", "string"] },
>>> > { "name": "details", "type": ["null", "string"] },
>>> > { "name": "entityId", "type": ["null", "string"] },
>>> > { "name": "entitySize", "type": ["null", "int"] },
>>> > { "name": "entityType", "type": ["null", "string"] },
>>> > { "name": "eventId", "type": ["null", "string"] },
>>> > { "name": "eventType", "type": ["null", "string"] },
>>> > { "name": "processGroupId", "type": ["null", "string"] },
>>> > { "name": "processGroupName", "type": ["null", "string"] },
>>> > { "name": "record_count", "type": ["null", "int"] },
>>> > { "name": "schema_name", "type": ["null", "string"] },
>>> > { "name": "timestamp_field", "type": "long", "logicalType":
>>> > "timestamp-millis" }
>>> > ]
>>> > }
>>> >
>>> > I set the JsonTreeReader to use this formatting option for reading in
>>> > the
>>> > original ISO8601 string:
>>> >
>>> > yyyy-MM-dd'T'HH:mm:ssZ
>>> >
>>> > Everything looks fine until it gets to the processor. Any ideas?
>>> >
>>> > Thanks,
>>> >
>>> > Mike
>>
>>
>

Re: PutDatabaseRecord won't put Timestamp field

Posted by Mike Thomsen <mi...@gmail.com>.
Matt were you using 1.6?
On Thu, Apr 5, 2018 at 3:56 PM Mike Thomsen <mi...@gmail.com> wrote:

> 1.5
>
> Thanks,
>
> Mike
>
> On Thu, Apr 5, 2018 at 3:40 PM, Matt Burgess <ma...@apache.org> wrote:
>
>> Mike,
>>
>> I can't reproduce this, I use the same DDL and Avro schema, with data
>> coming in from the SiteToSiteProvenanceReportingTask and going to
>> Postgres, and it works fine. What version of NiFi are you using?
>>
>> Regards,
>> Matt
>>
>>
>> On Thu, Apr 5, 2018 at 3:05 PM, Mike Thomsen <mi...@gmail.com>
>> wrote:
>> > Found these errors in the Docker logs:
>> >
>> > postgres_1       | 2018-04-05 18:33:22.183 UTC [51] ERROR:  column
>> > "timestamp_field" is of type timestamp without time zone but expression
>> is
>> > of type bigint at character 282
>> > postgres_1       | 2018-04-05 18:33:22.183 UTC [51] HINT:  You will
>> need to
>> > rewrite or cast the expression.
>> > postgres_1       | 2018-04-05 18:33:22.183 UTC [51] STATEMENT:  INSERT
>> INTO
>> > provenance (componentid, componentname, componenttype, details,
>> entityid,
>> > entitysize, entitytype, eventid, eventtype, processgroupid,
>> > processgroupname, record_count, schema_name, timestamp_field) VALUES
>> > ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
>> > nifi2            | 2018-04-05 18:33:22,184 WARN [Timer-Driven Process
>> > Thread-2] o.a.n.p.standard.PutDatabaseRecord
>> > PutDatabaseRecord[id=dfdd3dd1-21ee-16e2-09cc-159b7c7f8f54] Failed to
>> process
>> >
>> StandardFlowFileRecord[uuid=8cf3b521-ac0b-4149-a60e-5fa7b2d2b3c5,claim=StandardContentClaim
>> > [resourceClaim=StandardResourceClaim[id=1522953007606-749,
>> > container=default, section=749], offset=434600,
>> > length=528],offset=0,name=4306158395541,size=528] due to
>> > java.sql.BatchUpdateException: Batch entry 0 INSERT INTO provenance
>> > (componentid, componentname, componenttype, details, entityid,
>> entitysize,
>> > entitytype, eventid, eventtype, processgroupid, processgroupname,
>> > record_count, schema_name, timestamp_field) VALUES
>> >
>> ('8d08a7d3-0162-1000-7216-7e0b426e774a','EvaluateJsonPath','EvaluateJsonPath',NULL,'26a1efa3-bcd3-4015-8a43-2a2c43c05714',66307,'org.apache.nifi.flowfile.FlowFile','e841aa4e-a7d3-4a6e-b78c-4e785f53b60e','ATTRIBUTES_MODIFIED','8c5c89d5-0162-1000-4395-a38d1c7b0b2f','Mongo
>> > ES Test',NULL,NULL,1522952968586) was aborted: ERROR: column
>> > "timestamp_field" is of type timestamp without time zone but expression
>> is
>> > of type bigint
>> >
>> > I have the following DDL and Avro schema:
>> >
>> > create table provenance (
>> > id serial,
>> > componentId varchar(128),
>> > componentName varchar(256),
>> > componentType varchar(128),
>> > details varchar(256),
>> > entityId varchar(256),
>> > entitySize int,
>> > entityType varchar(128),
>> > eventId varchar(128),
>> > eventType varchar(128),
>> > processGroupId varchar(128),
>> > processGroupName varchar(128), record_count int,
>> > schema_name varchar(64),
>> > timestamp_field timestamp
>> > )
>> >
>> > {
>> > "type": "record",
>> > "name": "ProvenanceEvent",
>> > "fields": [
>> > { "name": "componentId", "type": ["null", "string"] },
>> > { "name": "componentName", "type": ["null", "string"] },
>> > { "name": "componentType", "type": ["null", "string"] },
>> > { "name": "details", "type": ["null", "string"] },
>> > { "name": "entityId", "type": ["null", "string"] },
>> > { "name": "entitySize", "type": ["null", "int"] },
>> > { "name": "entityType", "type": ["null", "string"] },
>> > { "name": "eventId", "type": ["null", "string"] },
>> > { "name": "eventType", "type": ["null", "string"] },
>> > { "name": "processGroupId", "type": ["null", "string"] },
>> > { "name": "processGroupName", "type": ["null", "string"] },
>> > { "name": "record_count", "type": ["null", "int"] },
>> > { "name": "schema_name", "type": ["null", "string"] },
>> > { "name": "timestamp_field", "type": "long", "logicalType":
>> > "timestamp-millis" }
>> > ]
>> > }
>> >
>> > I set the JsonTreeReader to use this formatting option for reading in
>> the
>> > original ISO8601 string:
>> >
>> > yyyy-MM-dd'T'HH:mm:ssZ
>> >
>> > Everything looks fine until it gets to the processor. Any ideas?
>> >
>> > Thanks,
>> >
>> > Mike
>>
>
>

Re: PutDatabaseRecord won't put Timestamp field

Posted by Mike Thomsen <mi...@gmail.com>.
1.5

Thanks,

Mike

On Thu, Apr 5, 2018 at 3:40 PM, Matt Burgess <ma...@apache.org> wrote:

> Mike,
>
> I can't reproduce this, I use the same DDL and Avro schema, with data
> coming in from the SiteToSiteProvenanceReportingTask and going to
> Postgres, and it works fine. What version of NiFi are you using?
>
> Regards,
> Matt
>
>
> On Thu, Apr 5, 2018 at 3:05 PM, Mike Thomsen <mi...@gmail.com>
> wrote:
> > Found these errors in the Docker logs:
> >
> > postgres_1       | 2018-04-05 18:33:22.183 UTC [51] ERROR:  column
> > "timestamp_field" is of type timestamp without time zone but expression
> is
> > of type bigint at character 282
> > postgres_1       | 2018-04-05 18:33:22.183 UTC [51] HINT:  You will need
> to
> > rewrite or cast the expression.
> > postgres_1       | 2018-04-05 18:33:22.183 UTC [51] STATEMENT:  INSERT
> INTO
> > provenance (componentid, componentname, componenttype, details, entityid,
> > entitysize, entitytype, eventid, eventtype, processgroupid,
> > processgroupname, record_count, schema_name, timestamp_field) VALUES
> > ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
> > nifi2            | 2018-04-05 18:33:22,184 WARN [Timer-Driven Process
> > Thread-2] o.a.n.p.standard.PutDatabaseRecord
> > PutDatabaseRecord[id=dfdd3dd1-21ee-16e2-09cc-159b7c7f8f54] Failed to
> process
> > StandardFlowFileRecord[uuid=8cf3b521-ac0b-4149-a60e-5fa7b2d2b3c5,claim=
> StandardContentClaim
> > [resourceClaim=StandardResourceClaim[id=1522953007606-749,
> > container=default, section=749], offset=434600,
> > length=528],offset=0,name=4306158395541,size=528] due to
> > java.sql.BatchUpdateException: Batch entry 0 INSERT INTO provenance
> > (componentid, componentname, componenttype, details, entityid,
> entitysize,
> > entitytype, eventid, eventtype, processgroupid, processgroupname,
> > record_count, schema_name, timestamp_field) VALUES
> > ('8d08a7d3-0162-1000-7216-7e0b426e774a','EvaluateJsonPath','
> EvaluateJsonPath',NULL,'26a1efa3-bcd3-4015-8a43-2a2c43c05714',66307,'org.
> apache.nifi.flowfile.FlowFile','e841aa4e-a7d3-4a6e-b78c-
> 4e785f53b60e','ATTRIBUTES_MODIFIED','8c5c89d5-0162-1000-
> 4395-a38d1c7b0b2f','Mongo
> > ES Test',NULL,NULL,1522952968586) was aborted: ERROR: column
> > "timestamp_field" is of type timestamp without time zone but expression
> is
> > of type bigint
> >
> > I have the following DDL and Avro schema:
> >
> > create table provenance (
> > id serial,
> > componentId varchar(128),
> > componentName varchar(256),
> > componentType varchar(128),
> > details varchar(256),
> > entityId varchar(256),
> > entitySize int,
> > entityType varchar(128),
> > eventId varchar(128),
> > eventType varchar(128),
> > processGroupId varchar(128),
> > processGroupName varchar(128), record_count int,
> > schema_name varchar(64),
> > timestamp_field timestamp
> > )
> >
> > {
> > "type": "record",
> > "name": "ProvenanceEvent",
> > "fields": [
> > { "name": "componentId", "type": ["null", "string"] },
> > { "name": "componentName", "type": ["null", "string"] },
> > { "name": "componentType", "type": ["null", "string"] },
> > { "name": "details", "type": ["null", "string"] },
> > { "name": "entityId", "type": ["null", "string"] },
> > { "name": "entitySize", "type": ["null", "int"] },
> > { "name": "entityType", "type": ["null", "string"] },
> > { "name": "eventId", "type": ["null", "string"] },
> > { "name": "eventType", "type": ["null", "string"] },
> > { "name": "processGroupId", "type": ["null", "string"] },
> > { "name": "processGroupName", "type": ["null", "string"] },
> > { "name": "record_count", "type": ["null", "int"] },
> > { "name": "schema_name", "type": ["null", "string"] },
> > { "name": "timestamp_field", "type": "long", "logicalType":
> > "timestamp-millis" }
> > ]
> > }
> >
> > I set the JsonTreeReader to use this formatting option for reading in the
> > original ISO8601 string:
> >
> > yyyy-MM-dd'T'HH:mm:ssZ
> >
> > Everything looks fine until it gets to the processor. Any ideas?
> >
> > Thanks,
> >
> > Mike
>

Re: PutDatabaseRecord won't put Timestamp field

Posted by Matt Burgess <ma...@apache.org>.
Mike,

I can't reproduce this, I use the same DDL and Avro schema, with data
coming in from the SiteToSiteProvenanceReportingTask and going to
Postgres, and it works fine. What version of NiFi are you using?

Regards,
Matt


On Thu, Apr 5, 2018 at 3:05 PM, Mike Thomsen <mi...@gmail.com> wrote:
> Found these errors in the Docker logs:
>
> postgres_1       | 2018-04-05 18:33:22.183 UTC [51] ERROR:  column
> "timestamp_field" is of type timestamp without time zone but expression is
> of type bigint at character 282
> postgres_1       | 2018-04-05 18:33:22.183 UTC [51] HINT:  You will need to
> rewrite or cast the expression.
> postgres_1       | 2018-04-05 18:33:22.183 UTC [51] STATEMENT:  INSERT INTO
> provenance (componentid, componentname, componenttype, details, entityid,
> entitysize, entitytype, eventid, eventtype, processgroupid,
> processgroupname, record_count, schema_name, timestamp_field) VALUES
> ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
> nifi2            | 2018-04-05 18:33:22,184 WARN [Timer-Driven Process
> Thread-2] o.a.n.p.standard.PutDatabaseRecord
> PutDatabaseRecord[id=dfdd3dd1-21ee-16e2-09cc-159b7c7f8f54] Failed to process
> StandardFlowFileRecord[uuid=8cf3b521-ac0b-4149-a60e-5fa7b2d2b3c5,claim=StandardContentClaim
> [resourceClaim=StandardResourceClaim[id=1522953007606-749,
> container=default, section=749], offset=434600,
> length=528],offset=0,name=4306158395541,size=528] due to
> java.sql.BatchUpdateException: Batch entry 0 INSERT INTO provenance
> (componentid, componentname, componenttype, details, entityid, entitysize,
> entitytype, eventid, eventtype, processgroupid, processgroupname,
> record_count, schema_name, timestamp_field) VALUES
> ('8d08a7d3-0162-1000-7216-7e0b426e774a','EvaluateJsonPath','EvaluateJsonPath',NULL,'26a1efa3-bcd3-4015-8a43-2a2c43c05714',66307,'org.apache.nifi.flowfile.FlowFile','e841aa4e-a7d3-4a6e-b78c-4e785f53b60e','ATTRIBUTES_MODIFIED','8c5c89d5-0162-1000-4395-a38d1c7b0b2f','Mongo
> ES Test',NULL,NULL,1522952968586) was aborted: ERROR: column
> "timestamp_field" is of type timestamp without time zone but expression is
> of type bigint
>
> I have the following DDL and Avro schema:
>
> create table provenance (
> id serial,
> componentId varchar(128),
> componentName varchar(256),
> componentType varchar(128),
> details varchar(256),
> entityId varchar(256),
> entitySize int,
> entityType varchar(128),
> eventId varchar(128),
> eventType varchar(128),
> processGroupId varchar(128),
> processGroupName varchar(128), record_count int,
> schema_name varchar(64),
> timestamp_field timestamp
> )
>
> {
> "type": "record",
> "name": "ProvenanceEvent",
> "fields": [
> { "name": "componentId", "type": ["null", "string"] },
> { "name": "componentName", "type": ["null", "string"] },
> { "name": "componentType", "type": ["null", "string"] },
> { "name": "details", "type": ["null", "string"] },
> { "name": "entityId", "type": ["null", "string"] },
> { "name": "entitySize", "type": ["null", "int"] },
> { "name": "entityType", "type": ["null", "string"] },
> { "name": "eventId", "type": ["null", "string"] },
> { "name": "eventType", "type": ["null", "string"] },
> { "name": "processGroupId", "type": ["null", "string"] },
> { "name": "processGroupName", "type": ["null", "string"] },
> { "name": "record_count", "type": ["null", "int"] },
> { "name": "schema_name", "type": ["null", "string"] },
> { "name": "timestamp_field", "type": "long", "logicalType":
> "timestamp-millis" }
> ]
> }
>
> I set the JsonTreeReader to use this formatting option for reading in the
> original ISO8601 string:
>
> yyyy-MM-dd'T'HH:mm:ssZ
>
> Everything looks fine until it gets to the processor. Any ideas?
>
> Thanks,
>
> Mike