You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@streampipes.apache.org by Florian Micklich <mi...@apache.org> on 2020/05/15 13:29:11 UTC
Extension of Postgres sink
Hi all,
I did some changes in the postgres sink and depending JdbcClient.class.
Here is the small changelog:
A. I improved the error messages if wrong host/port oder user
authentication is used.
B. Changing the enum SqlAttribute data types for Double and String. In
Postgres double leads to an error described in STREAMPIPES-127.
Instead DOUBLE PRECISION has to be used [1]. Also changes String
from varchar255 to TEXT. This allows "unlimited" string lentgh.
C. Added two new db settings:
* Use schema name [2] instead of only public.
* Drop Table if Exists Option. This deletes the table if table exists, so old will be removed and the same table name can be used.
D. Because of C. some changes had to be made in the JdbcClient.class
and also in the initializeJdbc method with two new parameters.
E. Extended documentation and string.en with the new parameters
This changes can be found in the feature/postgis-sink branch. The
compatibility with the IoT-DB, which also uses the class
JdbcClient.class, still needs to be tested! My first guess is, that
this may not be compatible, but because of STREAMPIPES-127 it seems to
be necessary to use another JdbcClien client anyway. At the moment I
used some default values(public and false) to start the IotDB.class
Sink like without compiling error.
initializeJdbc(
parameters.getGraph().getInputStreams().get(0).getEventSche
ma().getEventProperties(),
parameters.getIotDbHost(),
parameters.getIotDbPort(),
"", // Database does not exist in IoTDB model
"root." + parameters.getDbStorageGroup(),
parameters.getUsername(),
parameters.getPassword(),
".*",
"org.apache.iotdb.jdbc.IoTDBDriver",
"iotdb",
LOG,
"public",
false);
Greetings
Florian
[1] https://www.postgresql.org/docs/current/datatype-numeric.html
[2] https://www.postgresqltutorial.com/postgresql-schema/
[3] https://www.postgresql.org/docs/current/sql-droptable.html
Re: Extension of Postgres sink
Posted by Philipp Zehnder <ze...@fzi.de>.
Hi Florian,
thank you.
@Felix, you are currently working on the MySQL sink [1], right?
Do you think it is possible to have a base JDBC client or will there be problems with MySQL?
@all How should we solve it?
How about one JDBCClient base class with the core functionality.
The individual databases inherit from this class and can be adapted to the specific configurations of the class.
Philipp
[1] https://issues.apache.org/jira/projects/STREAMPIPES/issues/STREAMPIPES-115 <https://issues.apache.org/jira/projects/STREAMPIPES/issues/STREAMPIPES-115>
> On 21. May 2020, at 12:32, Florian Micklich <mi...@apache.org> wrote:
>
> Hi Philipp,
>
> yes, definitely there are some core functions.
>
>
> I only changed following methods:
>
> * changes only working in Postgres
> * initializeJdbc
> * ensureTableExists
> * generatePreparedStatement
> * createTable
> * executePreparedStatement
> * enum SqlAttribute
> * new methods only for Postgres
> * ensureSchemaExists
> * createSchema
>
>
> All other methods are reusable and the change in the connect-method
> should be valid for all other JBDC.
>
>
> I already tried to create an extended class but got some undefined
> error messages.
>
> For now I created a PostgresJBDCClient class and rechanged the IoT-DB
> Sink back to normal referencing to the JDBCClient like before. All
> changes are now committet in the feature/postgis branch
>
> Greetings
> Florian
>
>
> Am Montag, den 18.05.2020, 16:39 +0200 schrieb Philipp Zehnder:
>> Hi Florian,
>>
>> yes I agree. We should put database specific implementations in a
>> separate class into the package of the sink.
>>
>> Do you think we have to duplicate the whole class or can we leave
>> some core functionalities in the JDBClient?
>>
>> Philipp
>>
>>> On 15. May 2020, at 16:33, Florian Micklich <mi...@apache.org>
>>> wrote:
>>>
>>> Hi all,
>>>
>>> I just made a quick test and as aspected it is not compatible with
>>> the
>>> IoT-DB anymore. So what are the best options?
>>>
>>> I would say, move each working JDBClient into the depending Sink
>>> folder
>>> and rename it like IoTDbClient and PostgresClient. Similar solution
>>> is
>>> used for example in the InfluxDbClient sink folder.
>>>
>>> What do you think?
>>>
>>> Kind regards
>>> Florian
>>>
>>>
>>>
>>> Am Freitag, den 15.05.2020, 15:29 +0200 schrieb Florian Micklich:
>>>> Hi all,
>>>>
>>>> I did some changes in the postgres sink and depending
>>>> JdbcClient.class.
>>>>
>>>>
>>>> Here is the small changelog:
>>>>
>>>> A. I improved the error messages if wrong host/port oder user
>>>> authentication is used.
>>>> B. Changing the enum SqlAttribute data types for Double and
>>>> String. In
>>>> Postgres double leads to an error described in STREAMPIPES-
>>>> 127.
>>>> Instead DOUBLE PRECISION has to be used [1]. Also changes
>>>> String
>>>> from varchar255 to TEXT. This allows "unlimited" string
>>>> lentgh.
>>>> C. Added two new db settings:
>>>> * Use schema name [2] instead of only public.
>>>> * Drop Table if Exists Option. This deletes the table if
>>>> table
>>>> exists, so old will be removed and the same table name can be
>>>> used.
>>>> D. Because of C. some changes had to be made in the
>>>> JdbcClient.class
>>>> and also in the initializeJdbc method with two new
>>>> parameters.
>>>> E. Extended documentation and string.en with the new parameters
>>>>
>>>> This changes can be found in the feature/postgis-sink branch. The
>>>> compatibility with the IoT-DB, which also uses the class
>>>> JdbcClient.class, still needs to be tested! My first guess is,
>>>> that
>>>> this may not be compatible, but because of STREAMPIPES-127 it
>>>> seems
>>>> to
>>>> be necessary to use another JdbcClien client anyway. At the
>>>> moment I
>>>> used some default values(public and false) to start the
>>>> IotDB.class
>>>> Sink like without compiling error.
>>>>
>>>>
>>>> initializeJdbc(
>>>> parameters.getGraph().getInputStreams().get(0).getEven
>>>> tSc
>>>> he
>>>> ma().getEventProperties(),
>>>> parameters.getIotDbHost(),
>>>> parameters.getIotDbPort(),
>>>> "", // Database does not exist in IoTDB model
>>>> "root." + parameters.getDbStorageGroup(),
>>>> parameters.getUsername(),
>>>> parameters.getPassword(),
>>>> ".*",
>>>> "org.apache.iotdb.jdbc.IoTDBDriver",
>>>> "iotdb",
>>>> LOG,
>>>> "public",
>>>> false);
>>>>
>>>> Greetings
>>>> Florian
>>>>
>>>> [1] https://www.postgresql.org/docs/current/datatype-numeric.html
>>>> [2] https://www.postgresqltutorial.com/postgresql-schema/
>>>> [3] https://www.postgresql.org/docs/current/sql-droptable.html
>>
>>
>
.........................................................
M. Sc. Philipp Zehnder
Wissenschaftlicher Mitarbeiter | Research Scientist
Information Process Engineering (IPE)
FZI Forschungszentrum Informatik
Haid-und-Neu-Str. 10–14
76131 Karlsruhe, Germany
Tel.: +49 721 9654-805
Fax: +49 721 9654-806
zehnder@fzi.de <ma...@fzi.de>
https://www.fzi.de/mitarbeiter/philipp-zehnder
.........................................................
FZI Forschungszentrum Informatik
Stiftung des bürgerlichen Rechts
Stiftung Az: 14-0563.1 Regierungspräsidium Karlsruhe
Vorstand: Prof. Dr. Andreas Oberweis, Jan Wiesenberger, Prof. Dr.-Ing. J. Marius Zöllner
Vorsitzender des Kuratoriums: Ministerialdirigent Günther Leßnerkraus
.........................................................
Re: Extension of Postgres sink
Posted by Florian Micklich <mi...@apache.org>.
Hi Philipp,
yes, definitely there are some core functions.
I only changed following methods:
* changes only working in Postgres
* initializeJdbc
* ensureTableExists
* generatePreparedStatement
* createTable
* executePreparedStatement
* enum SqlAttribute
* new methods only for Postgres
* ensureSchemaExists
* createSchema
All other methods are reusable and the change in the connect-method
should be valid for all other JBDC.
I already tried to create an extended class but got some undefined
error messages.
For now I created a PostgresJBDCClient class and rechanged the IoT-DB
Sink back to normal referencing to the JDBCClient like before. All
changes are now committet in the feature/postgis branch
Greetings
Florian
Am Montag, den 18.05.2020, 16:39 +0200 schrieb Philipp Zehnder:
> Hi Florian,
>
> yes I agree. We should put database specific implementations in a
> separate class into the package of the sink.
>
> Do you think we have to duplicate the whole class or can we leave
> some core functionalities in the JDBClient?
>
> Philipp
>
> > On 15. May 2020, at 16:33, Florian Micklich <mi...@apache.org>
> > wrote:
> >
> > Hi all,
> >
> > I just made a quick test and as aspected it is not compatible with
> > the
> > IoT-DB anymore. So what are the best options?
> >
> > I would say, move each working JDBClient into the depending Sink
> > folder
> > and rename it like IoTDbClient and PostgresClient. Similar solution
> > is
> > used for example in the InfluxDbClient sink folder.
> >
> > What do you think?
> >
> > Kind regards
> > Florian
> >
> >
> >
> > Am Freitag, den 15.05.2020, 15:29 +0200 schrieb Florian Micklich:
> > > Hi all,
> > >
> > > I did some changes in the postgres sink and depending
> > > JdbcClient.class.
> > >
> > >
> > > Here is the small changelog:
> > >
> > > A. I improved the error messages if wrong host/port oder user
> > > authentication is used.
> > > B. Changing the enum SqlAttribute data types for Double and
> > > String. In
> > > Postgres double leads to an error described in STREAMPIPES-
> > > 127.
> > > Instead DOUBLE PRECISION has to be used [1]. Also changes
> > > String
> > > from varchar255 to TEXT. This allows "unlimited" string
> > > lentgh.
> > > C. Added two new db settings:
> > > * Use schema name [2] instead of only public.
> > > * Drop Table if Exists Option. This deletes the table if
> > > table
> > > exists, so old will be removed and the same table name can be
> > > used.
> > > D. Because of C. some changes had to be made in the
> > > JdbcClient.class
> > > and also in the initializeJdbc method with two new
> > > parameters.
> > > E. Extended documentation and string.en with the new parameters
> > >
> > > This changes can be found in the feature/postgis-sink branch. The
> > > compatibility with the IoT-DB, which also uses the class
> > > JdbcClient.class, still needs to be tested! My first guess is,
> > > that
> > > this may not be compatible, but because of STREAMPIPES-127 it
> > > seems
> > > to
> > > be necessary to use another JdbcClien client anyway. At the
> > > moment I
> > > used some default values(public and false) to start the
> > > IotDB.class
> > > Sink like without compiling error.
> > >
> > >
> > > initializeJdbc(
> > > parameters.getGraph().getInputStreams().get(0).getEven
> > > tSc
> > > he
> > > ma().getEventProperties(),
> > > parameters.getIotDbHost(),
> > > parameters.getIotDbPort(),
> > > "", // Database does not exist in IoTDB model
> > > "root." + parameters.getDbStorageGroup(),
> > > parameters.getUsername(),
> > > parameters.getPassword(),
> > > ".*",
> > > "org.apache.iotdb.jdbc.IoTDBDriver",
> > > "iotdb",
> > > LOG,
> > > "public",
> > > false);
> > >
> > > Greetings
> > > Florian
> > >
> > > [1] https://www.postgresql.org/docs/current/datatype-numeric.html
> > > [2] https://www.postgresqltutorial.com/postgresql-schema/
> > > [3] https://www.postgresql.org/docs/current/sql-droptable.html
>
>
Re: Extension of Postgres sink
Posted by Philipp Zehnder <ze...@apache.org>.
Hi Florian,
yes I agree. We should put database specific implementations in a separate class into the package of the sink.
Do you think we have to duplicate the whole class or can we leave some core functionalities in the JDBClient?
Philipp
> On 15. May 2020, at 16:33, Florian Micklich <mi...@apache.org> wrote:
>
> Hi all,
>
> I just made a quick test and as aspected it is not compatible with the
> IoT-DB anymore. So what are the best options?
>
> I would say, move each working JDBClient into the depending Sink folder
> and rename it like IoTDbClient and PostgresClient. Similar solution is
> used for example in the InfluxDbClient sink folder.
>
> What do you think?
>
> Kind regards
> Florian
>
>
>
> Am Freitag, den 15.05.2020, 15:29 +0200 schrieb Florian Micklich:
>> Hi all,
>>
>> I did some changes in the postgres sink and depending
>> JdbcClient.class.
>>
>>
>> Here is the small changelog:
>>
>> A. I improved the error messages if wrong host/port oder user
>> authentication is used.
>> B. Changing the enum SqlAttribute data types for Double and
>> String. In
>> Postgres double leads to an error described in STREAMPIPES-127.
>> Instead DOUBLE PRECISION has to be used [1]. Also changes
>> String
>> from varchar255 to TEXT. This allows "unlimited" string lentgh.
>> C. Added two new db settings:
>> * Use schema name [2] instead of only public.
>> * Drop Table if Exists Option. This deletes the table if table
>> exists, so old will be removed and the same table name can be used.
>> D. Because of C. some changes had to be made in the
>> JdbcClient.class
>> and also in the initializeJdbc method with two new parameters.
>> E. Extended documentation and string.en with the new parameters
>>
>> This changes can be found in the feature/postgis-sink branch. The
>> compatibility with the IoT-DB, which also uses the class
>> JdbcClient.class, still needs to be tested! My first guess is, that
>> this may not be compatible, but because of STREAMPIPES-127 it seems
>> to
>> be necessary to use another JdbcClien client anyway. At the moment I
>> used some default values(public and false) to start the IotDB.class
>> Sink like without compiling error.
>>
>>
>> initializeJdbc(
>> parameters.getGraph().getInputStreams().get(0).getEventSc
>> he
>> ma().getEventProperties(),
>> parameters.getIotDbHost(),
>> parameters.getIotDbPort(),
>> "", // Database does not exist in IoTDB model
>> "root." + parameters.getDbStorageGroup(),
>> parameters.getUsername(),
>> parameters.getPassword(),
>> ".*",
>> "org.apache.iotdb.jdbc.IoTDBDriver",
>> "iotdb",
>> LOG,
>> "public",
>> false);
>>
>> Greetings
>> Florian
>>
>> [1] https://www.postgresql.org/docs/current/datatype-numeric.html
>> [2] https://www.postgresqltutorial.com/postgresql-schema/
>> [3] https://www.postgresql.org/docs/current/sql-droptable.html
>
Re: Extension of Postgres sink
Posted by Florian Micklich <mi...@apache.org>.
Hi all,
I just made a quick test and as aspected it is not compatible with the
IoT-DB anymore. So what are the best options?
I would say, move each working JDBClient into the depending Sink folder
and rename it like IoTDbClient and PostgresClient. Similar solution is
used for example in the InfluxDbClient sink folder.
What do you think?
Kind regards
Florian
Am Freitag, den 15.05.2020, 15:29 +0200 schrieb Florian Micklich:
> Hi all,
>
> I did some changes in the postgres sink and depending
> JdbcClient.class.
>
>
> Here is the small changelog:
>
> A. I improved the error messages if wrong host/port oder user
> authentication is used.
> B. Changing the enum SqlAttribute data types for Double and
> String. In
> Postgres double leads to an error described in STREAMPIPES-127.
> Instead DOUBLE PRECISION has to be used [1]. Also changes
> String
> from varchar255 to TEXT. This allows "unlimited" string lentgh.
> C. Added two new db settings:
> * Use schema name [2] instead of only public.
> * Drop Table if Exists Option. This deletes the table if table
> exists, so old will be removed and the same table name can be used.
> D. Because of C. some changes had to be made in the
> JdbcClient.class
> and also in the initializeJdbc method with two new parameters.
> E. Extended documentation and string.en with the new parameters
>
> This changes can be found in the feature/postgis-sink branch. The
> compatibility with the IoT-DB, which also uses the class
> JdbcClient.class, still needs to be tested! My first guess is, that
> this may not be compatible, but because of STREAMPIPES-127 it seems
> to
> be necessary to use another JdbcClien client anyway. At the moment I
> used some default values(public and false) to start the IotDB.class
> Sink like without compiling error.
>
>
> initializeJdbc(
> parameters.getGraph().getInputStreams().get(0).getEventSc
> he
> ma().getEventProperties(),
> parameters.getIotDbHost(),
> parameters.getIotDbPort(),
> "", // Database does not exist in IoTDB model
> "root." + parameters.getDbStorageGroup(),
> parameters.getUsername(),
> parameters.getPassword(),
> ".*",
> "org.apache.iotdb.jdbc.IoTDBDriver",
> "iotdb",
> LOG,
> "public",
> false);
>
> Greetings
> Florian
>
> [1] https://www.postgresql.org/docs/current/datatype-numeric.html
> [2] https://www.postgresqltutorial.com/postgresql-schema/
> [3] https://www.postgresql.org/docs/current/sql-droptable.html