I modified the custom UDX example which comes with vertica so that instead of reading from a file it reads from Mysql.
this code is reading from a table which has two columns: create table foo(id int primary key, name varchar(100)). it has two rows (1, 'test1'), (2, 'test2')
public StreamState process(ServerInterface srvInterface, DataBuffer output) throws UdfException {
long offset;
srvInterface.log("total size of buffer " + output.buf.length);
StringBuilder builder = new StringBuilder();
try {
if (rs.next()) {
for(int i = 1 ; i <= resultSetColumnCount; i++) {
builder.append(rs.getString(i));
if (i < resultSetColumnCount) {
builder.append("|");
}
}
String row = builder.toString();
srvInterface.log("got this row from db: " + row);
byte[] bytes = row.getBytes();
System.arraycopy(bytes, 0, output.buf, 0, bytes.length);
output.offset = bytes.length;
srvInterface.log("current value of offset " + output.offset);
srvInterface.log("current value of buffer " + output.buf);
return StreamState.OUTPUT_NEEDED;
} else {
srvInterface.log("came inside the function but there is no data in resultset");
return StreamState.DONE;
}
}
catch(SQLException sqlEx) {
throw new UdfException(0, sqlEx.getMessage(), sqlEx);
}
}The code executes perfectly and in the UDXLog i can see the following messages
2016-01-06 00:08:23.019 [Java-6610] 0x19 [UserMessage] MySqlSource - going to execute query: select * from Foo
2016-01-06 00:08:23.020 [Java-6610] 0x19 [UserMessage] MySqlSource - number of columns in the results: 2
2016-01-06 00:08:23.021 [Java-6610] 0x19 [UserMessage] MySqlSource - total size of buffer 1048576
2016-01-06 00:08:23.022 [Java-6610] 0x19 [UserMessage] MySqlSource - got this row from db: 1|test1
2016-01-06 00:08:23.022 [Java-6610] 0x19 [UserMessage] MySqlSource - current value of offset 10
2016-01-06 00:08:23.022 [Java-6610] 0x19 [UserMessage] MySqlSource - current value of buffer [B@25eab8a7
2016-01-06 00:08:23.023 [Java-6610] 0x19 [UserMessage] MySqlSource - total size of buffer 1048576
2016-01-06 00:08:23.023 [Java-6610] 0x19 [UserMessage] MySqlSource - got this row from db: 2|test2
2016-01-06 00:08:23.024 [Java-6610] 0x19 [UserMessage] MySqlSource - current value of offset 10
2016-01-06 00:08:23.024 [Java-6610] 0x19 [UserMessage] MySqlSource - current value of buffer [B@25eab8a7
2016-01-06 00:08:23.025 [Java-6610] 0x19 [UserMessage] MySqlSource - total size of buffer 1048576
2016-01-06 00:08:23.026 [Java-6610] 0x19 [UserMessage] MySqlSource - came inside the function but there is no data in resultset
2016-01-06 00:08:23.027 [Java-6610] 0x19 [UserMessage] MySqlSource - came inside destry
2016-01-06 00:08:23.027 [Java-6610] 0x19 [UserMessage] MySqlSource - closed all resources successfully
so it looks like that the code is working perfectly. it is called twice and both the times it gets the right row
but on the Vertica side it inserts 5 rows !!!!
vertica=> copy testing.Foo source MySqlSource(mysqlconnectionstring='jdbc:mysql://mysql:3306/test', tableName='Foo', username='foo', password='bar');
Rows Loaded
-------------
5
(1 row)
vertica=> select * from testing.Foo;
id | name
----+----------
2 | test2
2 | test2
2 | test2
2 | test2
2 | test2
(5 rows)
Why did it
1. Loose the first row (1, 'test1')?
2. why did it insert the 2nd row twice?
