Blog do projektu Open Source JavaHotel

środa, 12 marca 2014

InfoSphere Sreams and Oracle

InfoSphere Streams can connect to Oracle database also. InfoSphere Streams installation contains Database Toolkit which provides several operators which allow integration with external databases (including Oracle). The functionality is very limited (it is nothing more then insert/update and select) but it is enough for typical InfoSphere Stream application.
Oracle and unixODBC
First thing to do is to download, install and setup unixODBC connection with Oracle database.
Oracle client
Download and install Oracle client software.  At least two products should be installed: Basic and ODBC. Also SQL*Plus is recommended for testing and administering.
unixODBC connection
Set LD_LIBRARY_PATH environment variable pointing to Instant Client installation.
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/instantclient_12_1/
Make sure that library has all dependency resolved.
[sb@host ~]$ ldd /usr/local/instantclient_12_1/ =>  (0x00007ffff43ff000) => /lib64/ (0x00007ff65554a000) => /lib64/ (0x00007ff6552c5000) => /lib64/ (0x00007ff6550a8000) => /lib64/ (0x00007ff654e8f000) => /lib64/ (0x00007ff654c86000) => /usr/local/instantclient_12_1/ (0x00007ff651f99000) => /usr/lib64/ (0x00007ff651d88000) => /lib64/ (0x00007ff6519f3000)
 /lib64/ (0x0000003732c00000) => /usr/local/instantclient_12_1/ (0x00007ff6512dd000) => /usr/local/instantclient_12_1/ (0x00007ff651099000) => /lib64/ (0x00007ff650e97000) => /usr/local/instantclient_12_1/ (0x00007ff650947000) => /usr/lib64/ (0x00007ff65073e000)
[sb@host ~]$ 
Create /etc/tnsnames.ora connection  file 
[sb@host ~]$ cat /etc/tnsnames.ora 
  (Host = think)
  (Port = 1521)
 (CONNECT_DATA = (SID = testdb)
Make sure that connection by sqlplus is working
[sb@host ~]$ sqlplus testuser/testuser@testdb

SQL*Plus: Release Production on Wed Mar 12 23:06:24 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Modify (or create) /etc/odbcinst and /etc/odbc.ini configuration files
[sb@host ~]$ cat /etc/odbcinst.ini
Description = Oracle
Driver   = /usr/local/instantclient_12_1/
[sb@host ~]$ cat /etc/odbc.ini
Description  = Oracle
Driver   = ORACLE
ServerName  = testdb
UserID   = testuser
Password = testuser
Verify that isql utility is working with Oracle
[sb@host ~]$ isql testdb
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
create table testid (id integer, name varchar(100))
InfoSphere Streams application
A simple StreamsStudio project is available here.
Database Toolkit operators use connection.xml file which specifies how application connects with external database. Connection.xml file contains information about connection and tables used in the database by InfoSphere Streams application.
Example of connection.xml file is available here.
Next step is to create application itself. It is very easy if one uses StreamsStudio framework, just drag operators to the application graph and connects input and output ports.
An example of sample application adding stream of data to external Oracle database is available here.
Visualization of this application:
Important limitation related to Oracle
Oracle does not support SPL int64 and uint64 data type. Also BIGINT in connection.xml file cannot containt BIGINT type (use INTEGER instead).

Brak komentarzy:

Prześlij komentarz