Bryan/Matt, Wouldn't a JDBC truncation blacklist be easiest? If the destination type is timestamp/any other applicables, then don't take the substring?
--Peter -----Original Message----- From: Bryan Bende [mailto:bbe...@gmail.com] Sent: Saturday, September 10, 2016 1:00 PM To: dev@nifi.apache.org Subject: Re: Re: PutSQL error sql.arg3.value cannot convert to timestamp Good find Matt, from a quick glance it looks like NIFI-2625 is definitely related, although it is more specific to when the timestamp is in milliseconds vs. nano-seconds. Part of the problem is that PutSQL can handle the epoch OR a date string, but the Avro produced by ExecuteSQL/QueryDatabaseTable always calls value.toString() for timestamps which seems to usually be yyyy-MM-dd HH:mm:ss. We end up passing around a date string with a JDBC type of 93 (timestamp), so comparing the size of the column in the target database would never line up. Should we modify JDBCCommon so that if rs.getObject(i) is a date/timestamp we take epoch and put that as the string value, rather than relying on value.toString() ?? https://github.com/apache/nifi/blob/master/nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/main/java/org/apache/nifi/processors/standard/util/JdbcCommon.java#L153-L157 On Sat, Sep 10, 2016 at 2:46 PM, Matt Burgess <mattyb...@gmail.com> wrote: > I think we're running into NIFI-2625 [1] and/or NIFI-1613 [2], the > column size returned is for the underlying data type, not the String > literal it corresponds to. > > Regards, > Matt > > [1] https://issues.apache.org/jira/browse/NIFI-2625 > [2] https://issues.apache.org/jira/browse/NIFI-1613 > > On Sat, Sep 10, 2016 at 2:43 PM, Bryan Bende <bbe...@gmail.com> wrote: > > Peng, > > > > Thanks for checking what the value for sql.args.3.value was... the > > fact that it is already truncated there and not including the > hours/mins/seconds > > means something is going wrong before it even gets to PutSQL. > > > > I was looking at the code in ConvertJsonToSQL and found this code: > > > > final Integer colSize = desc.getColumnSize(); > > > > final JsonNode fieldNode = rootNode.get(fieldName); > > > > if (!fieldNode.isNull()) { > > String fieldValue = rootNode.get(fieldName).asText(); > > if (colSize != null && fieldValue.length() > colSize) { > > fieldValue = fieldValue.substring(0, colSize); > > } > > attributes.put("sql.args." + fieldCount + ".value", fieldValue); > > } > > > > > > What could be happening is that the target DB is saying that the > > column size is 7 (comes from desc.getColumnSize() above), and then > > it sees that the string value is larger than 7 so it takes the substring > > from 0 to 6. > > It seems weird to me that 7 would be size of a timestamp column, but > > I can't come up with any other explanation, unless Oracle's driver > > is just doing something unusual. > > > > If that is what is happening, we probably have a couple of options, > > and > I'm > > curious to here what others with more DB experience think... > > > > 1) We could decide to leave ConvertJsonToSQL as is, and recommend > > that > for > > timestamp columns people need to manipulate the JSON between > > ConvertAvroToJson & ConvertJsonToSQL, this could probably be done > > using > the > > JOLT processor to transform the date string into a long, but not sure. > This > > would probably be confusing because no one would realize they need > > to do this. > > > > 2) We could completely remove this size check from ConvertJSONToSQL > > which can in some cases lead to insert statements that might fail > > downstream, > but > > that can be handled by routing the failure relationship of PutSQL > > somewhere. We know that PutSQL has logic to handle the epoch or date > > string, so it would only be other cases that could fail. > > > > 3) We could add special logic to ConvertJSONToSQL, similar to that > > in PutSQL, where we determined if it was a timestamp column and if > > it was a date string then don't perform this size check/truncation, > > but leave it > for > > other types. It could be a slippery slope starting to do special > > logic > for > > different types of columns. > > > > We need to consider that a lot of people are going to do > > ExecuteSQL/QueryDatabaseTable -> ConvertAvroToJSON -> > > ConvertJSONToSQL -> PutSQL but other people may be producing JSON in > > some other way and using the ConvertJSONToSQL -> PutSQL part. In the > > first case we know that we > are > > storing the date as a string in Avro so we will always run into this > case, > > but in the second we don't really know. > > > > Thoughts? > > > > -Bryan > > > > > > On Sat, Sep 10, 2016 at 9:59 AM, Li, Peng <peng....@hpe.com> wrote: > > > >> Hi Bryan, > >> > >> In provenance the attribute sql.args.3.value was given as "2016-04" > >> and sql.args.3.type was given as "93". > >> > >> But as already said in previous mail, i checked the content of the > output > >> of ConvertAvroToJSON and "2016-04-29 00:00:00.0" was given there, > >> so I think this value was given to the following ConvertJSONToSQL > >> processor. > >> > >> > >> > >> Perhaps it has nothing to do with format "yyyy-MM-dd HH:mm:ss.SSS" > >> but something else? > >> > >> > >> > >> Thanks > >> > >> Peng > >> > >> > >> > >> > >> -----Original Message----- > >> > >> > >> On Fri, 09 Sep 2016 12:06:35 GMT Bryan Bende bbe...@gmail.com<mailto: > >> bbe...@gmail.com> wrote: > >> > >> Hi Peng, > >> > >> In the stack trace it looks like it thinks the value of > sql.args.3.value is > >> "2016-04" rather than "2016-04-29 00:00:00.000". > >> > >> Can you double check the value of sql.args.3.value? You can look at > >> the flow file in provenance and look at the attributes. > >> > >> > >> >
