nifi-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Bende <bbe...@gmail.com>
Subject Re: UpdateRecord Processor
Date Mon, 16 Oct 2017 20:13:45 GMT
Aruna,

If your writer schema has long for sys_create_ts then your UpdateRecord
needs to have ${now():toNumber()}.

If your writer schema has the original logicalType, then you need
${now():format('yyyy-MM-dd HH:mm:ss')}.

It looks like you have a mix of both because you said you changed it to
long, but the error shows "2017-10-16 20:03:58" which isn't a long.

-Bryan


On Mon, Oct 16, 2017 at 4:07 PM, Aruna Sankaralingam <
Aruna.Sankaralingam@cormac-corp.com> wrote:

> Bryan,
>
>
>
> I followed your methods and the flow was successful. However, the
> sys_creat_ts and actv_ind fields were not populated for any of the records.
>
>
>
>
>
> This is what I see in the “Data Provenance” of the PutDatabaseRecord
> processor.
>
>
>
> ENRLMT_ID,CITY_NAME,ZIP_CD,STATE_CD,SYS_CREAT_TS,ACTV_IND
>
> I20031103000005,MECHANICSBURG,170509414,PA,1508183538000,
>
> I20031103000013,HATO REY,917,PR,1508183538000,
>
> I20031103000015,TOMS RIVER,8757,NJ,1508183538000,
>
> I20031103000028,JERSEY CITY,73062305,NJ,1508183538000,
>
> I20031103000030,AGUADILLA,6055256,PR,1508183538000,
>
> I20031103000032,BINGHAM FARMS,480254514,MI,1508183538000,
>
>
>
> So I changed the writer schema to use long for the sys_creat_ts field and
> started the flow but I got an error
>
> “*20:03:58 UTC**ERROR*
>
> *1221b2fe-015f-1000-9235-11d12a9fdbcc*
>
> UpdateRecord[id=1221b2fe-015f-1000-9235-11d12a9fdbcc] Failed to process
> StandardFlowFileRecord[uuid=25c48eec-42a0-4766-8605-69c5a0dad3cf,claim=StandardContentClaim
> [resourceClaim=StandardResourceClaim[id=1508167656905-1,
> container=default, section=1], offset=8591, length=1606],offset=0,name=3-
> Address_Sub-_File.csv,size=1606]; will route to failure: java.lang.NumberFormatException:
> For input string: "2017-10-16 20:03:58"
>
>
>
> “
>
>
>
>
>
>
>
>
>
> *From:* Bryan Bende [mailto:bbende@gmail.com]
> *Sent:* Monday, October 16, 2017 3:29 PM
>
> *To:* users@nifi.apache.org
> *Subject:* Re: UpdateRecord Processor
>
>
>
> Sticking with the original logicalType of timestamp-millis, I was able to
> get it working using ${now():format('yyyy-MM-dd HH:mm:ss')}  as the value
> of the timestamp column.
>
>
>
> On Mon, Oct 16, 2017 at 3:23 PM, Bryan Bende <bbende@gmail.com> wrote:
>
> If it is helpful, here is a template of the working version where I
> changed to a long:
>
> https://gist.githubusercontent.com/bbende/3187d3905f868bef3143899841bb70
> f9/raw/f266b149eb845a371543a1b68f9797d5437c6a2d/update-record-
> timestamp.xml
>
>
>
> On Mon, Oct 16, 2017 at 3:19 PM, Bryan Bende <bbende@gmail.com> wrote:
>
> Aruna,
>
> I think the issue here might be on the reader side of things...
>
> If you're incoming data does not have SYS_CREAT_TS in it, then your schema
> needs to allow null values for this field so you can create records from
> the incoming data, or you can create two versions of your schema and have
> the reader use a version that doesn't have this field, and have the writer
> use a version that does.
>
>
>
> I am currently testing this using these examples:
>
>
>
> {
> "type": "record",
> "name": "schema1",
> "fields": [
>  { "name": "id", "type": "string" }
> ]
> }
>
> {
> "type": "record",
> "name": "schema2",
> "fields": [
>  { "name": "id", "type": "string" },
>  { "name": "timestamp", "type" : { "type" : "long", "logicalType" :
> "timestamp-millis" } }
> ]
> }
>
>
>
> I have a CSVReader using schema1 and a CSVWriter using schema2.
>
>
>
> I'm able to get past the issue you are having and now it is getting to the
> point where it is trying to take the result ofr ${now():toNumber()} and
> convert it to a timestamp, and I'm running into a different issue:
>
>
>
> org.apache.nifi.serialization.record.util.IllegalTypeConversionException:
> Could not convert value [1508180727622] of type java.lang.String to
> Timestamp for field timestamp because the value is not in the expected date
> format: org.apache.nifi.serialization.record.util.DataTypeUtils$$
> Lambda$414/572291470@33d51c66
> org.apache.nifi.serialization.record.util.IllegalTypeConversionException:
> Could not convert value [1508180727622] of type java.lang.String to
> Timestamp for field timestamp because the value is not in the expected date
> format: org.apache.nifi.serialization.record.util.DataTypeUtils$$
> Lambda$414/572291470@33d51c66
>     at org.apache.nifi.serialization.record.util.DataTypeUtils.
> toTimestamp(DataTypeUtils.java:564)
>     at org.apache.nifi.serialization.record.util.DataTypeUtils.
> convertType(DataTypeUtils.java:134)
>     at org.apache.nifi.serialization.record.util.DataTypeUtils.
> convertType(DataTypeUtils.java:84)
>     at org.apache.nifi.serialization.record.MapRecord.setValue(
> MapRecord.java:317)
>     at org.apache.nifi.record.path.StandardFieldValue.updateValue(
> StandardFieldValue.java:132)
>     at org.apache.nifi.processors.standard.UpdateRecord.lambda$
> process$1(UpdateRecord.java:177)
>
>
>
> I haven't figured out the issue, but it has something to do with the logic
> of trying to convert a string to timestamp and whether or not a date format
> is provided.
>
>
>
> If I change schema1 to use a regular long for the timestamp field then it
> works:
>
>
>
> {
> "type": "record",
> "name": "schema2",
> "fields": [
>  { "name": "id", "type": "string" },
>  { "name": "timestamp", "type" : "long" }
> ]
> }
>
>
>
> I don't know what the ramifications of this would be in PutDatabaseRecord.
>
>
>
>
>
> On Mon, Oct 16, 2017 at 2:23 PM, Aruna Sankaralingam <
> Aruna.Sankaralingam@cormac-corp.com> wrote:
>
> Matt,
>
>
>
> My issue is that when I am trying to assign current date to sys_creat_ts
> for every record, I am getting this error that it cannot be null.
> SYS_CREAT_TS is not coming from the source. It is present in the target
> table alone and I need to load the current datetime in that field for every
> record that is coming from source.
>
>
>
> Mark,
>
>
>
> I am still getting the same error.
>
>
>
> Also, actv_ind must display just “Y” in the target table.
>
>
>
>
>
>
>
>
>
> *From:* Mark Payne [mailto:markap14@hotmail.com]
> *Sent:* Monday, October 16, 2017 12:03 PM
> *To:* users@nifi.apache.org
> *Subject:* Re: UpdateRecord Processor
>
>
>
> Hi Aruna,
>
>
>
> I think the issue is likely that you are setting /sys_create_ts but your
> schema has that field as SYS_CREAT_TS (i.e., it is in upper case).
>
> Would recommend you change the property names to /ACTV_IND and
> /SYS_CREAT_TS. Also, the value that you have for /sys_creat_ts is
>
> set to "{now()}" but I think what you really are wanting is
> "${now():toNumber()}".
>
>
>
> Also, of note, /actv_ind is set to '/Y' so I just want to clarify that the
> literal value '/Y' (with the single quotes) is what will be placed there.
> Is that
>
> the intention? Or did you actually want just /Y to be there?
>
>
>
> Thanks
>
> -Mark
>
>
>
>
>
>
>
> On Oct 16, 2017, at 11:41 AM, Aruna Sankaralingam <
> Aruna.Sankaralingam@Cormac-Corp.com> wrote:
>
>
>
> Hi,
>
>
>
> I updated to version 1.4 now. I am using Updaterecord processor. I have
> assigned values to two of the columns that are there in the target table as
> shown below but I am getting the error that SYS_CREAT_TS cannot be NULL. Am
> I missing something?
>
> I have provided the screenshots of the CSVReordWriter, AVROSchemaRegistry
> and my Nifi Flow below.
>
>
>
> <image004.png>   <image005.png>
>
>
>
> CSVRecordSetWriter:
>
> <image001.png>
>
>
>
> AVROSchemaRegistry:
>
> <image002.png>
>
>
>
> <image006.png>
>
>
>
>
>
> *From:* Koji Kawamura [mailto:ijokarumawak@gmail.com
> <ijokarumawak@gmail.com>]
> *Sent:* Thursday, October 12, 2017 8:28 PM
> *To:* users@nifi.apache.org
> *Subject:* Re: Transformations using Nifi
>
>
>
> Hi Aruna,
>
>
>
> If you can not upgrade from NiFi 1.2.0, then I think the best bed is using:
>
> ScriptedReader or ScriptedRecordSetWriter for data conversion #1, 2 and 3.
>
> As Russ mentioned, EL might be helpful when you implement the scripted
> components.
>
>
>
> #4 is a bit harder since it requires a database connection, but doable, I
> don't know it works efficiently though..
>
>
>
> Alternative approach would be create a temporary table, insert rows as
> they are, then perform a insert/update query using the temporary table and
> postgresql lookup table such as:
>
> "insert into X select a, b, c, from T inner join L on X.j = T.j where d =
> ..."
>
> Probably data conversion #1, 2, and 3 can be performed from this query as
> well.
>
>
>
> Thanks,
>
> Koji
>
>
>
> On Thu, Oct 12, 2017 at 11:50 PM, Russell Bateman <russ@windofkeltia.com>
> wrote:
>
> Aruna,
>
> I don't think there is any generalized NiFi training course yet. I started
> writing a book a year ago, but it's a pretty thankless task and takes a lot
> of time. I mostly write custom processors so I tend to see the world
> differently from most "user" users of NiFi. When I answer questions, which
> I don't do too much, I often tell an impractical story that doesn't answer
> the question asked.
>
> Now, I haven't done much database work at all, so I'm not going to be too
> much help to you. However, looking at your questions, particularly the one
> about obtaining the current date when the flow is run (#2) and also #3, I
> would suggest that you study the NiFi Expression Language. This would allow
> you to insert the "now" date into your flow at some point.
>
> The other suggestions I have are to experiment, which I'm sure you're
> doing, and Google hard for help. For this forum, which is filled with very
> nice and helpful people, you'll tend to get a lot more and better help if
> you come in with a very specific question rather than a list of things or a
> general, "help me" sort of plea.
>
> Cheers,
>
> Russ
>
> P.S. NiFi absolutely rocks, which you'll see as soon as you get over your
> initial hump here. But, you're on the right track.
>
>
>
> On 10/12/2017 08:16 AM, Aruna Sankaralingam wrote:
>
> Hi,
>
>
>
> Could you someone please help me with these requirements in the email
> below?
>
>
>
> Thanks
>
> Aruna
>
>
>
> *From:* Aruna Sankaralingam [mailto:Aruna.Sankaralingam@Cormac-Corp.com
> <Aruna.Sankaralingam@Cormac-Corp.com>]
> *Sent:* Wednesday, October 11, 2017 11:26 AM
> *To:* users@nifi.apache.org
> *Subject:* Transformations using Nifi
>
>
>
> I am trying to see what kind of transformations that can be done in nifi
> and how.
>
> Now I have a basic flow that takes CSV from the local dir and puts into s3
> and loads into postgres database.
>
> There are 4 columns in my test file 3 of which are string and one is an
> integer field. I would like to do the following before I load the data into
> postgres. If someone can help me on how to go about these, it will be great.
>
> 1.       Convert one of the string columns to upper case
>
> *For converting to upper case, I was told to use the Update Record
> processor but  my version is 1.2.0 and the update record processor is not
> available.*
>
>
>
> 2.       Postgres has an extra column called “Load_Date” in which I would
> like to load the current date with timestamp when the flow is run
>
> 3.       If the integer column has more than 5 digits, I would like to
> take only the first 5 digits and load to the table
>
> 4.       There is a look up table in postgres. I would like to check if
> the first column value is present in the look up table and if yes, proceed
> ahead and if not ignore the record
>
>
>
> I am trying to learn nifi so I would really appreciate any kind of help
> here. Is there any training available online that I can take in order to
> understand and do all these?
>
>
>
> <image003.png>
>
>
>
>
>
>
>
>
>

Mime
View raw message