If it is helpful, here is a template of the working version where I changed to a long:
https://gist.githubusercontent.com/bbende/3187d3905f868bef3143899841bb70f9/raw/f266b149eb845a371543a1b68f9797d5437c6a2d/update-record-timestamp.xml On Mon, Oct 16, 2017 at 3:19 PM, Bryan Bende <[email protected]> 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 < > [email protected]> 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:[email protected]] >> *Sent:* Monday, October 16, 2017 12:03 PM >> *To:* [email protected] >> *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 < >> [email protected]> 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:[email protected] >> <[email protected]>] >> *Sent:* Thursday, October 12, 2017 8:28 PM >> *To:* [email protected] >> *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 <[email protected]> >> 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:[email protected] >> <[email protected]>] >> *Sent:* Wednesday, October 11, 2017 11:26 AM >> *To:* [email protected] >> *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> >> >> >> > >
