Striim 3.10.3 documentation

Replicating Oracle data to Cassandra

Cassandra Writer can continuously replicate one or many Oracle tables to a Cassandra or DataStax keyspace. First, create a table in Cassandra corresponding to each Oracle table to be replicated. Then load the existing data using DatabaseReader, for example:

CREATE SOURCE OracleJDBCSource USING DatabaseReader (
  Username:'Striim',
  Password:'****',
  ConnectionURL:'jdbc:oracle:thin:@192.168.123.14:1521/XE',
  Tables:'TPCH.H_CUSTOMER;TPCH.H_PART;TPCH.H_SUPPLIER'
)
OUTPUT TO DataStream;

CREATE TARGET TPCHInitialLoad USING CassandraWriter (
  ConnectionURL:'jdbc:cassandra://203.0.113.50:9042/mykeyspace',
  Username:'striim',
  Password:'******',
  Tables:'TPCH.H_CUSTOMER,customer;TPCH.H_PART,part;TPCH.H_SUPPLIER,supplier'
)
INPUT FROM DataStream; 

DatabaseWriter's Tables property maps each specified Oracle table to a Cassandra table, for example, TPCH.H_CUSTOMER to customer. Oracle table names must be uppercase and Cassandra table names must be lowercase. Since columns in Cassandra tables are not created in the same order they are specified in the CREATE TABLE statement, the ColumnMap option is required (see Mapping columns) and wildcards are not supported. See Database Reader and Cassandra Writer for more information about the properties.

Once the initial load is complete, the following application will read new data using LogMiner and continuously replicate it to Cassandra:

CREATE SOURCE OracleCDCSource USING OracleReader ( 
  Username: 'Striim',
  Password: '******',
  ConnectionURL: '203.0.113.49:1521:orcl',
  Compression:'True',
  Tables: 'TPCH.H_CUSTOMER;TPCH.H_PART;TPCH.H_SUPPLIER'
 ) 
OUTPUT TO DataStream;

CREATE TARGET CassandraTarget USING CassandraWriter(
  ConnectionURL:'jdbc:cassandra://203.0.113.50:9042/mykeyspace',
  Username:'striim',
  Password:'******',
  Tables: 'TPCH.H_CUSTOMER,customer;TPCH.H_PART,part;TPCH.H_SUPPLIER,supplier'
INPUT FROM DataStream;

OracleReader's Compression property must be True. Cassandra does not allow primary key updates. See Oracle Reader properties and Cassandra Writer for more information about the properties.

When the input stream of a Cassandra Writer target is the output of an Oracle source (DatabaseReader or OracleReader), the following types are supported:

Oracle type

Cassandra CQL type

BINARY_DOUBLE

double

BINARY_FLOAT

float

BLOB

blob

CHAR

text, varchar

CHAR(1)

bool

CLOB

ascii, text

DATE

timestamp

DECIMAL

double, float

FLOAT

float

INT

int 

INTEGER

int

NCHAR

text, varchar

NUMBER

int

NUMBER(1,0)

int

NUMBER(10)

int

NUMBER(19,0)

int

NUMERIC

int

NVARCHAR2

varchar

SMALLINT

int

TIMESTAMP

timestamp

TIMESTAMP WITH LOCAL TIME ZONE

timestamp

TIMESTAMP WITH TIME ZONE

timestamp

VARCHAR2

varchar