...
There are two possibilities how to use a database connection. For the one hand, you can create a persistent connection and reuse this connection in other queries. On the other hand, you can directly create a connection for each query. First, we show how to create and drop persistent connections and show how to use them in PQL and StreamSQL(CQL). Afterwards, we show how to directly setup a connection using PQL.
| Table of Contents |
|---|
Important Notices
...
| Code Block |
|---|
result = DATABASESINK({connection='con1', table='main', truncate='true'}, datastream) |
...
Using a connection in StreamSQL (CQL)
...
Remember the union compatibility, because the outgoing schema of "SELECT * FROM samplestream" that is written into "dbsink" must be union-compatible with the table of "dbsink", which is the schema of example in this case.
Direct Database Connections
...
datastream = DATABASESINK({table='main', jdbc='jdbc:mysql://localhost:3306/test', user='dbuser', password='dbpassword'}, otherops)
Datatype Mapping
This part introduces the concept of datatype mapping between the relational database system and Odysseus. Since you have to define a union-compatible schema between your database system and Odysseus (e.g. if you want to read a table without fetching the schema), the different definitions of datatypes may be confusing so that I give a short introduction.
Odysseus has different Datatypes (see Data Types for details) like Integer or String while e.g. a MySQL uses int and varchar for the same types. Furthermore, there is the JDBC driver between the database, which also uses other datatypes. So, Odysseus has a mapping that maps all (default) datatypes (called SDFDatatype) to appropriate datatypes from JDBC (see java.sql.Types). The following table shows how Odysseus maps a database data type to a Odysseus datatype:
| JDBC Types | SDFDatatypes |
|---|---|
| Array | Object |
| BigInt | Long |
| Binary | Long |
| Bit | Boolean |
| Blob | Object |
| Boolean | Boolean |
| Char | String |
| Clob | Object |
| Datalink | Object |
| Date | Date |
| Decimal | Integer |
| Distinct | Object |
| Double | Double |
| Float | Float |
| Integer | Integer |
| Java Object | Object |
| Long NVarChar | String |
| Long VarBinary | Long |
| Long VarChar | String |
| NChar | String |
| NClob | Object |
| Null | Object |
| Numeric | Double |
| NVarChar | String |
| Other | Object |
| Real | Float |
| Ref | Object |
| RowId | Object |
| SmallInt | Integer |
| SQLXML | String |
| Struct | Object |
| Time | Timestamp |
| Timestamp | Timestamp |
| TinyInt | Integer |
| VarBinary | Long |
| VarChar | String |
you may see, for example, that a TINYINT of JDBC is mapped to an integer in Odysseus. Therefore, if the DATABASESOURCE reads a table with datatypes BIGINT and VARCHAR, therse datatypes are mapped to LONG an STRING in Odysseus. Since this mapping is used when reading from the database, there is also a direction how Odysseus transforms its datatypes to JDBC-datatypes for writing data:
| SDFDatatypes | JDBC Types |
|---|---|
| Boolean | Boolean |
| Byte | Binary |
| Date | Date |
| Double | Double |
| End Timestamp | BigInt |
| Float | Float |
| Integer | Integer |
| Long | BigInt |
| Object | Object |
| Point in Time | BigInt |
| String | VarChar |
| Timestamp | BigInt |
| Start Timestamp | BigInt |
...
For example, if the DATABASESINK operator creates a new table to insert data, it uses, e.g. Types.BIGINT for a LONG or any timestamp.
...
- MySQL
- PostgreSQL
- Derby
HSQL(currently not supported)- Oracle
...