Toivo, I think for VARBINARY, LONGVARBINARY, ARRAY, BLOB, and CLOB, I would use an Avro Bytes type.
Basically, anything that could be serialized into a byte array i'd use a bytes type. Otherwise, I'd probably just either fail or skip that column. Not sure what else we can really do with this, at this point. I have an instance of MySQL that I should be able to try this against at some point in the next few days. Thanks -Mark > On Sep 18, 2015, at 11:39 AM, Toivo Adams <[email protected]> wrote: > > Hi Jonathan, > > It should not be tinyint, because such type is present in mapping code. > I assume MySQL tinyint corresponds to JDBC java.sql.Types > public final static int TINYINT = -6; > > I don't have MySQL so can't test directly. > > Your table definition looks OK, nothing unusual and exotic. > So hard to predict which type is culprit. > > But ExecuteSQL needs some modifications anyway > because currently it does not recognize some rarely used types. > > For example VARBINARY, LONGVARBINARY, OTHER, JAVA_OBJECT, ARRAY, BLOB, CLOB, > etc. > > How ExecuteSQL should handle type's which can't directly converted to Avro > types? > > 1. Always try to convert to String type? (we already convert some types to > String) > 2. Throw error? > 3. what else...? > > Current mapping: > > for (int i = 1; i <= nrOfColumns; i++) { > switch (meta.getColumnType(i)) { > case CHAR: > case LONGNVARCHAR: > case LONGVARCHAR: > case NCHAR: > case NVARCHAR: > case VARCHAR: > builder.name > <http://builder.name/>(meta.getColumnName(i)).type().stringType().noDefault(); > break; > > case BOOLEAN: > builder.name > <http://builder.name/>(meta.getColumnName(i)).type().booleanType().noDefault(); > break; > > case INTEGER: > case SMALLINT: > case TINYINT: > builder.name > <http://builder.name/>(meta.getColumnName(i)).type().intType().noDefault(); > break; > > case BIGINT: > builder.name > <http://builder.name/>(meta.getColumnName(i)).type().longType().noDefault(); > break; > > // java.sql.RowId is interface, is seems to be database > // implementation specific, let's convert to String > case ROWID: > builder.name > <http://builder.name/>(meta.getColumnName(i)).type().stringType().noDefault(); > break; > > case FLOAT: > case REAL: > builder.name > <http://builder.name/>(meta.getColumnName(i)).type().floatType().noDefault(); > break; > > case DOUBLE: > builder.name > <http://builder.name/>(meta.getColumnName(i)).type().doubleType().noDefault(); > break; > > // Did not find direct suitable type, need to be clarified!!!! > case DECIMAL: > case NUMERIC: > builder.name > <http://builder.name/>(meta.getColumnName(i)).type().stringType().noDefault(); > break; > > // Did not find direct suitable type, need to be clarified!!!! > case DATE: > case TIME: > case TIMESTAMP: > builder.name > <http://builder.name/>(meta.getColumnName(i)).type().stringType().noDefault(); > break; > > default: > break; > } > > toivo > > 2015-09-18 16:38 GMT+03:00 Jonathan Lyons <[email protected] > <mailto:[email protected]>>: > Hi Toivo, > > I see. Thanks for the response and the diagnosis. Sorry I didn't post the > table definition, on the off chance it helps resolve the issue: > > CREATE TABLE `users` ( > `id` int(11) NOT NULL AUTO_INCREMENT, > `email` varchar(255) NOT NULL, > `password` varchar(255) DEFAULT NULL, > `activation_code` varchar(255) DEFAULT NULL, > `forgotten_password_code` varchar(255) DEFAULT NULL, > `forgotten_password_time` datetime DEFAULT NULL, > `created` datetime NOT NULL, > `active` tinyint(1) NOT NULL DEFAULT '0', > `home_module_id` int(11) DEFAULT NULL, > PRIMARY KEY (`id`), > UNIQUE KEY `email` (`email`), > KEY `home_module_id` (`home_module_id`), > CONSTRAINT `users_ibfk_1` FOREIGN KEY (`home_module_id`) REFERENCES > `modules` (`id`) ON DELETE SET NULL > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 > > Could it be 'tinyint?' > > On Thu, Sep 17, 2015 at 11:40 AM, Toivo Adams <[email protected] > <mailto:[email protected]>> wrote: > Hi, > > I was not able to reproduce problem using Derby. > > static String createPersons = "create table persons (id integer, name > varchar(100), code integer)"; > > final String query = "select * from persons"; > > Maybe some data type will cause error? > Or is it related somehow how MySQL JDBC driver works? > > Wait, It’s seem to be bug in createSchema() which creates Arvo schema. > When createSchema() will encounter unknown type, no Arvo column info will be > created. And Arvo schema has less columns than ResultSet row… > > I created ticket. > https://issues.apache.org/jira/browse/NIFI-972 > <https://issues.apache.org/jira/browse/NIFI-972> > > Thanks > Toivo > > 2015-09-17 18:10 GMT+03:00 Toivo Adams <[email protected] > <mailto:[email protected]>>: > Hi, > > No, JDBC metadata is used always. > > I’ll try to create Junit test to reproduce problem. > I don’t have MySQL so I will use Derby for testing. > Hopefully MySQL JDBC driver does not have specific different behavior. > > toivo > > 2015-09-17 16:48 GMT+03:00 Jonathan Lyons <[email protected] > <mailto:[email protected]>>: > Hi, > > Thanks for the response. Indeed it looks like changing the query from: > > SELECT * from users > > to: > > SELECT id, email from users > > causes it to start working. Does the JDBC metadata get dropped when using the > column wildcard? > > Jonathan > > On Sat, Sep 12, 2015 at 4:59 AM, Toivo Adams <[email protected] > <mailto:[email protected]>> wrote: > Hi, > > ExecuteSQL generates Avro schema automatically using JDBC metadata from query > result. > It seems number of columns in generated Avro schema and in row from ResultSet > is different. > > Probably bug in ExecuteSQL. > Please can you share your SQL select query and database table definition? > And maybe even some sample data which causes the problem? > > Thanks > Toivo > > > 2015-09-11 18:43 GMT+03:00 Jonathan Lyons <[email protected] > <mailto:[email protected]>>: > Hi, > > Just getting started with NiFi here. I am attempting to run a static query in > MySQL using the ExecuteSQL processor. It is set to run on a 5 second > interval. Since ExecuteSQL appears to need an input flow file I'm using a > GenerateFlowFile processor to produce a random file every 5 seconds. > Unfortunately, I'm getting a very vague ArrayIndexOutOfBounds exception when > I hit play on the flow: > > java.lang.ArrayIndexOutOfBoundsException: 8 > > at org.apache.avro.generic.GenericData$Record.put(GenericData.java:129) > > at org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStream > > at > org.apache.nifi.processors.standard.ExecuteSQL$1.process(ExecuteSQL.java:141) > ~[na:na] > > > Any idea why this is? > > Thanks, > Jonathan > > > > > >
