Hi Philipp,
I think we should handel the mapping via following standard values,
returned by JDBC meta information [1].
This way an easy validation between column types
in the db and event properties can be done. Also this should be valid
for all DB's using JDBC.
If it is required to map e.g. DOUBLE to PG_DOUBLE_PRECISION, this
should be done based on referencing to the above list.
What do you think?
This way it is also easy for Adapters get the correct resource type.
Here also a possible solution for the validation using this approach:
protected void validateTable() throws SpRuntimeException {
//TODO: Add validation of an existing table
HashMap<String, Integer> columnsMap = new HashMap<>();
try {
ResultSet column = c.getMetaData().getColumns(null, null, tableName,
null);
while (column.next()) {
String columnName = column.getString("COLUMN_NAME");
Integer datatype = Integer.parseInt(column.getString("DATA_TYPE"));
columnsMap.put(columnName, datatype);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
//todo
// create hashmap from event properties with like HashMap<String, Integer>
eventMap = new HashMap<>();
HashMap<String, Integer> eventMap = new HashMap<>();
//eventProperties;
if (!columnsMap.equals(eventMap)) {
throw new SpRuntimeException("Table '" + tableName + "' does not match
the eventproperties");
}
}
Greetings
Florian
[1]
https://www.tutorialspoint.com/java-resultsetmetadata-getcolumntype-method-with-example
Am Dienstag, den 16.06.2020, 23:21 +0200 schrieb Philipp Zehnder:
> Hi Florian,
>
> great work with the wiki [1]. Indeed, I think this is a very
> important point and we should clarify this, because it can get quite
> confusing with all the different possibilities.
> Your description helps a lot! Currently, we do not support all data
> types.
> I think it makes sense to define the mappings between StreamPipes
> types and data base types for all available values, both for the sink
> and for adapters.
>
> Regarding the capital letters in the names, is this just a problem
> with Postgres or might there also be other JDBC databases with the
> same issue?
> And what about special characters, do we have to filter them out too?
>
> I also like the idea with the inheritance of the JdbcClient.
> What do you think, should we move all sinks using JDBC into one
> package?
>
> Your changes look good, I would say it makes sense to merge them into
> dev and try to use the components as much as possible to find
> possible problems.
> A would also suggest to implement an adapter for each of the sinks.
> It makes the testing and usage of the databases much easier, when we
> can use StreamPipes to write data into the database and then use an
> adapter to read the data again.
>
> Philipp
>
>
> [1]
> https://cwiki.apache.org/confluence/display/STREAMPIPES/JDBC+Database+Client
> <
> https://cwiki.apache.org/confluence/display/STREAMPIPES/JDBC+Database+Client
> >
>
> > On 12. Jun 2020, at 13:54, Florian Micklich <[email protected]>
> > wrote:
> >
> > Hi all,
> >
> > I created a ticket STREAMPIPES-162 and also found a solution to fix
> > the
> > described issue.
> >
> > I tested it with the Postgres and MYSQL Sink.
> > If I get a short OK I would push the rewrite_jdbcclient branch into
> > dev.
> >
> > Greetings
> > Florian
> >
> >
> >
> > Am Montag, den 08.06.2020, 12:57 +0200 schrieb Florian Micklich:
> > > Hi all,
> > >
> > > I just finished rewriting the JDBC class, and added an "extends"
> > > postgresJDBC-client, to handle postgres and postgis as a sink
> > > with
> > > more
> > > options.
> > >
> > > I tested it with the mysql sink already and everything is working
> > > with
> > > mother JDBCClient-class.
> > >
> > > During this process I also "outsourced" the SQLAttributeEnum out
> > > of
> > > the
> > > JDBC-class and changed some names and layout for better
> > > understanding:
> > >
> > >
> > > public enum SqlAttribute {
> > > // DEFAULT
> > > INTEGER("INT"),
> > > LONG("BIGINT"),
> > > FLOAT("FLOAT"),
> > > DOUBLE("DOUBLE"),
> > > STRING("VARCHAR(255)"),
> > > BOOLEAN("BOOLEAN"),
> > > //MYSQL
> > > MYSQL_DATETIME("DATETIME"),
> > > //POSTGRES / POSTGIS
> > > PG_DOUBLE("NUMERIC");
> > >
> > >
> > >
> > > I hope this is ok the way I did it.
> > >
> > >
> > > In this case postgres uses the TYPE numeric instead of the non
> > > existing
> > > DOUBLE type, as an example.
> > > So later on I will add also a postgres timestamp type, like it
> > > was
> > > done
> > > in the mysql sink last week.
> > >
> > > But there is one think, I need some assistance and it took a
> > > while to
> > > find out why and what is happening:
> > >
> > > So I used the randomDataset Adapter and the Postgres Sink.
> > >
> > > The database, schema and table is created as expected but during
> > > save,
> > > I get all the time:
> > >
> > > Table 'testtable' was unexpectedly not found and gets recreated.
> > > ERROR: column "randomText" of relation "testtable" does not exist
> > >
> > > So the first message is a litte bit misleading, because the table
> > > exists but can't be found and will be recreated in an endless
> > > loop.
> > >
> > > The reason is because this statement during the
> > > generatePreparedStatement method:
> > >
> > > INSERT INTO testschema.testtable ( "randomText",
> > > "randomNumber", "count", "timestamp" ) VALUES ( 'c', 69, 83531,
> > > 1591611708415 );
> > >
> > >
> > > The problem here is that postgres uses lowercases for the column
> > > names.
> > > So instead of randomText then name has to be randomtext:
> > >
> > > INSERT INTO testschema.testtable ( "randomtext",
> > > "randomnumber", "count", "timestamp" ) VALUES ( 'c', 69, 83531,
> > > 1591611708415 )
> > >
> > > Can someone give me a hint, where to add .toLowerCase to the
> > > correct
> > > positon to get a quick fix?
> > >
> > > I am working in the branch feature/jdbc_rewrite and the
> > > PostgresJDPCClient class.
> > >
> > > Greetings Florian
> > >
> > >
> > >
> > >
>
>
