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 |