[jira] [Commented] (NIFI-2200) QueryDatabaseTable subquery
[ https://issues.apache.org/jira/browse/NIFI-2200?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15435208#comment-15435208 ] Charles Bryan Clifford commented on NIFI-2200: -- This issue is not a bug. The QueryDatabaseTable documentation does not state that (any variation of) SQL syntax can be used to initialize the literal string value of the table name property. Using an SQL statement to initialize a literal string value of a single table name is not an appropriate technique. Support such a technique requires a NiFi processor to also be an SQL query parser (of 1 unidentified SQL syntax out of many possible variations), and that is just not an realistic expectation. > QueryDatabaseTable subquery > --- > > Key: NIFI-2200 > URL: https://issues.apache.org/jira/browse/NIFI-2200 > Project: Apache NiFi > Issue Type: Bug >Reporter: Nabegh > > QueryDatabaseTable throws an exception if given a subquery as the table name > with a where condition. > For example, Table Name = > (select * from table_name where condition = value) t > will throw the following error > 2016-07-07 16:23:22,668 ERROR [Timer-Driven Process Thread-7] > o.a.n.p.standard.QueryDatabaseTable > QueryDatabaseTable[id=354261e7-cfa5-4809-a60c-a9c5136faa70] Failed to process > session due to org.apache.nifi.processor.exception.FlowFileHandlingException: > StandardFlowFileRecord[uuid=5b7fca67-9fd6-4dd3-9cc4-f2a0bfcf7095,claim=,offset=0,name=196391993568910,size=0] > transfer relationship not specified: > org.apache.nifi.processor.exception.FlowFileHandlingException: > StandardFlowFileRecord[uuid=5b7fca67-9fd6-4dd3-9cc4-f2a0bfcf7095,claim=,offset=0,name=196391993568910,size=0] > transfer relationship not specified > 2016-07-07 16:23:22,671 ERROR [Timer-Driven Process Thread-7] > o.a.n.p.standard.QueryDatabaseTable > org.apache.nifi.processor.exception.FlowFileHandlingException: > StandardFlowFileRecord[uuid=5b7fca67-9fd6-4dd3-9cc4-f2a0bfcf7095,claim=,offset=0,name=196391993568910,size=0] > transfer relationship not specified > at > org.apache.nifi.controller.repository.StandardProcessSession.checkpoint(StandardProcessSession.java:212) > ~[nifi-framework-core-0.6.1.jar:0.6.1] > at > org.apache.nifi.controller.repository.StandardProcessSession.commit(StandardProcessSession.java:282) > ~[nifi-framework-core-0.6.1.jar:0.6.1] > at > org.apache.nifi.processors.standard.QueryDatabaseTable.onTrigger(QueryDatabaseTable.java:348) > ~[na:na] > at > org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1059) > ~[nifi-framework-core-0.6.1.jar:0.6.1] > at > org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136) > [nifi-framework-core-0.6.1.jar:0.6.1] > at > org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47) > [nifi-framework-core-0.6.1.jar:0.6.1] > at > org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:123) > [nifi-framework-core-0.6.1.jar:0.6.1] > at > java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) > [na:1.8.0_91] > at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) > [na:1.8.0_91] > at > java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180) > [na:1.8.0_91] > at > java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294) > [na:1.8.0_91] > at > java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) > [na:1.8.0_91] > at > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) > [na:1.8.0_91] > at java.lang.Thread.run(Thread.java:745) [na:1.8.0_91] -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (NIFI-1613) ConvertJSONToSQL Drops Type Information
[
https://issues.apache.org/jira/browse/NIFI-1613?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15431579#comment-15431579
]
Charles Bryan Clifford commented on NIFI-1613:
--
As first pointed out by Aaron, ConvertJsonToSQL is the likely source of the
in-coming flow file attribute entry value truncation.
> ConvertJSONToSQL Drops Type Information
> ---
>
> Key: NIFI-1613
> URL: https://issues.apache.org/jira/browse/NIFI-1613
> Project: Apache NiFi
> Issue Type: Bug
> Components: Core Framework
>Affects Versions: 0.4.1, 0.5.1
> Environment: Ubuntu 14.04 LTS
>Reporter: Aaron Stephens
>Assignee: Toivo Adams
> Labels: ConvertJSONToSQL, Phoenix, SQL
>
> It appears that the ConvertJSONToSQL processor is turning Boolean (and
> possibly Integer and Float) values into Strings. This is okay for some
> drivers (like PostgreSQL) which can coerce a String back into a Boolean, but
> it causes issues for others (specifically Phoenix in my case).
> {noformat}
> org.apache.phoenix.schema.ConstraintViolationException:
> org.apache.phoenix.schema.TypeMismatchException: ERROR 203 (22005): Type
> mismatch. VARCHAR cannot be coerced to BOOLEAN
> at
> org.apache.phoenix.schema.types.PDataType.throwConstraintViolationException(PDataType.java:282)
> ~[na:na]
> at
> org.apache.phoenix.schema.types.PBoolean.toObject(PBoolean.java:136) ~[na:na]
> at
> org.apache.phoenix.jdbc.PhoenixPreparedStatement.setObject(PhoenixPreparedStatement.java:442)
> ~[na:na]
> at
> org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:166)
> ~[na:na]
> at
> org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:166)
> ~[na:na]
> at
> org.apache.nifi.processors.standard.PutSQL.setParameter(PutSQL.java:728)
> ~[na:na]
> at
> org.apache.nifi.processors.standard.PutSQL.setParameters(PutSQL.java:606)
> ~[na:na]
> at
> org.apache.nifi.processors.standard.PutSQL.onTrigger(PutSQL.java:223) ~[na:na]
> at
> org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
> ~[nifi-api-0.4.1.jar:0.4.1]
> at
> org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1146)
> ~[nifi-framework-core-0.4.1.jar:0.4.1]
> at
> org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:139)
> [nifi-framework-core-0.4.1.jar:0.4.1]
> at
> org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:49)
> [nifi-framework-core-0.4.1.jar:0.4.1]
> at
> org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:119)
> [nifi-framework-core-0.4.1.jar:0.4.1]
> at
> java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
> [na:1.7.0_79]
> at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:304)
> [na:1.7.0_79]
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:178)
> [na:1.7.0_79]
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
> [na:1.7.0_79]
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
> [na:1.7.0_79]
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
> [na:1.7.0_79]
> at java.lang.Thread.run(Thread.java:745) [na:1.7.0_79]
> Caused by: org.apache.phoenix.schema.TypeMismatchException: ERROR 203
> (22005): Type mismatch. VARCHAR cannot be coerced to BOOLEAN
> at
> org.apache.phoenix.exception.SQLExceptionCode$1.newException(SQLExceptionCode.java:71)
> ~[na:na]
> at
> org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
> ~[na:na]
> ... 20 common frames omitted
> {noformat}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Created] (NIFI-2625) ConvertJsonToSql truncates SQL timestamp values
Charles Bryan Clifford created NIFI-2625:
Summary: ConvertJsonToSql truncates SQL timestamp values
Key: NIFI-2625
URL: https://issues.apache.org/jira/browse/NIFI-2625
Project: Apache NiFi
Issue Type: Bug
Affects Versions: 0.7.0
Environment: Ubuntu 16.04
Reporter: Charles Bryan Clifford
The ConvertJsonToSql processor is incorrectly initializing colSize.
In the ColumnDescription constructor method, ResultSet.getInt("COLUMN_SIZE") is
used to initialize colSize. That method appear to return a size of a Java
TimeStamp data type (which is in milliseconds).
When generateInsert and generateUpdate parse the in-flowing JSON field node's
text value in this manner:
fieldValue = fieldValue.substring(0, colSize);
And, next, do the following:
attributes.put("sql.args." + fieldCount + ".value", fieldValue);
Nanoseconds in timestamp field values, present in the in-flowing JSON content,
are not making it into the timestamp field values stored in sql.args.N.value
FlowFile attribute.
My source timestamp values are in nano seconds, and all target database
timestamp columns are likewise in nano seconds.
For your consideration, here's a potential fix to the
ConvertJsonToSql.ColumnDescription timestamp value truncation problem:
public static ColumnDescription from(final ResultSet resultSet) throws
SQLException {
final ResultSetMetaData md = resultSet.getMetaData();
List columns = new ArrayList<>();
HashMap columncache = new HashMap(); // NEW - used to
store column size, as per database service
for (int i = 1; i < md.getColumnCount() + 1; i++) {
columns.add(md.getColumnName(i));
columncache.put(md.getColumnName(i),md.getPrecision(i)); // NEW - get physical
column size as per the database service
}
final String columnName = resultSet.getString("COLUMN_NAME");
final int dataType = resultSet.getInt("DATA_TYPE");
//final int colSize = resultSet.getInt("COLUMN_SIZE");
final int colSize = columncache.get(columnName); // NEW
In this way, the target data type lengths used by the database service (not by
Java) will be used to initalize colSize. This could fix the timestamp value
truncation problem, and other Java-data-type-size conflicts with target
database-service-data-type-size.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
