OGG4BD to send boolean data to Cassandra

OGG4BD to send boolean data to Cassandra

–Create target table in Cassandra
CREATE TABLE TARGET.TARGET01 (
col1 text PRIMARY KEY,
col5 float,
col2 bigint,
col3 timestamp,
col4 boolean
) WITH bloom_filter_fp_chance = 0.01
AND caching = {‘keys’: ‘ALL’, ‘rows_per_partition’: ‘NONE’}
AND comment = ”
AND compaction = {‘class’: ‘org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy’, ‘max_threshold’: ’32’, ‘min_threshold’: ‘4’}
AND compression = {‘chunk_length_in_kb’: ’64’, ‘class’: ‘org.apache.cassandra.io.compress.LZ4Compressor’}
AND crc_check_chance = 1.0
AND dclocal_read_repair_chance = 0.1
AND default_time_to_live = 0
AND gc_grace_seconds = 864000
AND max_index_interval = 2048
AND memtable_flush_period_in_ms = 0
AND min_index_interval = 128
AND read_repair_chance = 0.0
AND speculative_retry = ’99PERCENTILE’;

—Create source table in Oracle
CREATE TABLE SOURCE.TST_TAB04
(
COL1 NVARCHAR2(10),
COL2 NUMBER,
COL3 TIMESTAMP(6),
COL4 CHAR(1 BYTE),
COL5 FLOAT(6)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
ROW STORE COMPRESS ADVANCED
NOCACHE
MONITORING;

–Craete avro file in OGG4BD
TARGET.TARGET01.mdp.avsc
{“namespace”: “TARGET”,
“type”: “record”,
“name”: “TARGET01”,
“fields”: [
{“name”: “COL1”, “type”: “string”, “primary_key”:true},
{“name”: “COL2”, “type”: [“long”,”null”]},
{“name”: “COL3”, “type”: [“timestamp”,”null”]},
{“name”: “COL4”, “type”: [“boolean”,”null”]},
{“name”: “COL5”, “type”: [“long”,”null”]}
]
}

–Create replicat process in OGG4BD
more RPT01.prm
replicat RPT01
SETENV (PLATFORM = “ogg4bd”)
SETENV (OGG_GROUP = “RPT01”)
SETENV (OGG_DATABASE = “ORCLPDB1”)
SETENV (OGG_USERALIAS = “SRCOGGCDB”)
SETENV (OGG_DOMAIN = “SRCOGGCDB”)
SETENV (OGG_NUM = “1”)
SETENV (OGG_PROCESS = “REPLICAT”)
SETENV (OGG_DEPLOYMENT = “/U01/app/oracle/ogg4bd/12.3.2.1.0”)
SETENV (OGG_CONF = “dirprm”)
SETENV (SRCDB_SCHEMA = “ORCLPDB1.SOURCE”)
SETENV (SRCDB_TYPE= “oracle”)
SETENV (TRGDB_TYPE= “cassandra”)
COMMENT Database Connection Info and Parameters
TARGETDB LIBFILE libggjava.so SET property=dirprm/?TERMINAL/cass.props

COMMENT Global Parameters Include file (for all processes)
DISCARDFILE ?OGG_DEPLOYMENT/dirrpt/?OGG_GROUP.dsc, APPEND, MEGABYTES 256
REPORTCOUNT EVERY 1 MINUTES, RATE
GROUPTRANSOPS 10
MAXTRANSOPS 50
ALLOWDUPTARGETMAP

MAP ?SRCDB_SCHEMA.TST_TAB04, TARGET TARGET.TARGET01
COLMAP ( USEDEFAULTS);

—-Test Senario 1:
insert into SOURCE.TST_TAB04
(col1, col2, col3, col4,col5)
values
(‘1’, 101, current_timestamp, ‘1’, 11.11);

commit;

select * from SOURCE.TST_TAB04;
1 101 10/10/2019 2:56:28.618251 PM 1 11

root@0102469423f8:/# cqlsh cassandra_homs 9042 -u cassandra -p password
CA@cqlsh> select * from TARGET.TARGET01;

col1 | col5 | col2 | col3 | col4
——+——+——+———————————+——
1 | null | 101 | 2019-10-10 14:56:28.618000+0000 | True

insert into SOURCE.TST_TAB04
(col1, col2, col3, col4,col5)
values
(‘2’, 102, current_timestamp, ‘0’, 12.12);
commit;

select * from SOURCE.TST_TAB04;
1 101 10/10/2019 2:56:28.618251 PM 1 11
2 102 10/10/2019 3:05:24.295595 PM 0 12

cassandra@cqlsh> select * from TARGET.TARGET01;

col1 | col5 | col2 | col3 | col4
——+——+——+———————————+——
2 | null | 102 | 2019-10-10 15:05:24.295000+0000 | True
1 | null | 101 | 2019-10-10 14:56:28.618000+0000 | True

—-Test Senario 2:
CREATE TABLE SOURCE.TST_TAB05
(
COL1 NVARCHAR2(10),
COL2 NUMBER,
COL3 TIMESTAMP(6),
COL4 CHAR(1 BYTE),
COL5 FLOAT(6)
)
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
ROW STORE COMPRESS ADVANCED
NOCACHE
MONITORING;

MAP ?SRCDB_SCHEMA.TST_TAB05, TARGET TARGET.TEST01
COLMAP ( USEDEFAULTS );

insert into SOURCE.TST_TAB05
(col1, col2, col3, col4,col5)
values
(‘1’, 101, current_timestamp, ‘1’, 11.11);
commit;

insert into SOURCE.TST_TAB05
(col1, col2, col3, col4,col5)
values
(‘2’, 102, current_timestamp, ‘0’, 12.12);
commit;

select * from SOURCE.TST_TAB05;
1 101 10/10/2019 4:39:16.090764 PM 1 11
2 102 10/10/2019 4:40:25.826737 PM 0 12

cassandra@cqlsh> select * from TARGET.TEST01;

col1 | col2 | col3 | col4 | col5
——+——+———————————+——+——
2 | 102 | 2019-10-10 16:40:25.826000+0000 | True | 12
1 | 101 | 2019-10-10 16:39:16.090000+0000 | True | 11

—Solution to map boolean value oracle(1, 0) to Cassandra(true, false)
MAP ?SRCDB_SCHEMA.TST_TAB05, TARGET TARGET.TEST01
COLMAP ( USEDEFAULTS, COL4= @CASE (HAZARDOUS_INDICATOR, ‘1’, ‘true’, ‘0’, ‘false’ )
);

 

Leave a Reply

Your email address will not be published. Required fields are marked *