...
Code Block |
---|
result = DATABASESINK({connection='con1', table='main', truncate='true'}, datastream) |
If the schema of the target table does not match the inputs of the operator, you can provide a prepared statement with the attribute names of the table.
Code Block |
---|
result = DATABASESINK({connection='con1', table='main', preparedStatement='INSERT INTO main (Att1, Att2, Att3) values(?,?,?)'}, datastream) |
See https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html for other examples how to use the prepated statement. The order of the attributes is just the order of the schema in the input of the operator. So it would easily be possible to update values as in the following example. In this case the first input attribute must be the new salery and the second attribute the id (you can use Project operator or Map operator to reorganise you input)
Code Block |
---|
result = DATABASESINK({connection='con1', table='EMPLOYEES', preparedStatement='UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?'}, datastream) |
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 | Timestamp |
StartTimestamp | BigInt |
EndTimestamp | 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.
...
The following databases are supported:
- MariaDB
- MySQL
- PostgreSQL
- Derby
HSQL(currently not supported) - Oracle
CQL has some problems at the moment, so please use PQL to create DB connections.
...