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>
>
>
>
