There is a requirement I can't resolve.
1. The requirement:
1)The requirement is extract "swjg" table from oracle source database,
2)then transform. When transform, I should produce ETL log and insert those log information into postgres target database.
3)load data into postgres target "organ_level" table.
2.Those ETL log tables include two table:
1)main table
CREATE TABLE etl_runlog
(
id numeric(10) NOT NULL, // primary key
program varchar(100) NOT NULL, //transform class short name
starTime date NOT NULL DEFAULT ('now'::text)::date, //transform started time
endTime date, //transform finished time
succRows numeric(10) NOT NULL DEFAULT 0, //success load rows count
failRows numeric(10) NOT NULL DEFAULT 0, //failures load rows count
createTime date NOT NULL //inserted time
)
2)child table
CREATE TABLE etl_exceptionlog
(
etllogid numeric(10) NOT NULL, // foreign key references etl_runlog.id
sourcetable varchar(100) NOT NULL, //source table name
sourcerowid varchar(32) NOT NULL, //source row's primary key
remark varchar(200), //exception information
createdate date NOT NULL //inserted time
)
3.I create a graph named organ_level.grf, it like this:
swjg---->Reformat----->organ_level
|
|-->etl_runlog
|
|-->etl_exceptionlog
4.User transform editor (one of Clover.ETL GUI for eclipse puglin tools) creat a DataRecordTransform class:
public class TransOrganLevel extends DataRecordTransform
5.My question is: How can I write these ETL log into postgres target database except for user jdbc directly?
1. The requirement:
1)The requirement is extract "swjg" table from oracle source database,
2)then transform. When transform, I should produce ETL log and insert those log information into postgres target database.
3)load data into postgres target "organ_level" table.
2.Those ETL log tables include two table:
1)main table
CREATE TABLE etl_runlog
(
id numeric(10) NOT NULL, // primary key
program varchar(100) NOT NULL, //transform class short name
starTime date NOT NULL DEFAULT ('now'::text)::date, //transform started time
endTime date, //transform finished time
succRows numeric(10) NOT NULL DEFAULT 0, //success load rows count
failRows numeric(10) NOT NULL DEFAULT 0, //failures load rows count
createTime date NOT NULL //inserted time
)
2)child table
CREATE TABLE etl_exceptionlog
(
etllogid numeric(10) NOT NULL, // foreign key references etl_runlog.id
sourcetable varchar(100) NOT NULL, //source table name
sourcerowid varchar(32) NOT NULL, //source row's primary key
remark varchar(200), //exception information
createdate date NOT NULL //inserted time
)
3.I create a graph named organ_level.grf, it like this:
swjg---->Reformat----->organ_level
|
|-->etl_runlog
|
|-->etl_exceptionlog
4.User transform editor (one of Clover.ETL GUI for eclipse puglin tools) creat a DataRecordTransform class:
public class TransOrganLevel extends DataRecordTransform
5.My question is: How can I write these ETL log into postgres target database except for user jdbc directly?
-
correct the organ_level.grf fomat:
"etl_runlog" and "etl_exceptionlog" are from "Reformat" not from "swjg". -
You may want to consider using LOG4J from Apache.
I have good success writing my Clover LOG to a Oracle database using the "org.apache.log4j.jdbc" class.
I basically wrote a java class that launches my clover graph and logs what the graph does (it is not as sophisticated as what you want to do).
Alan. -
Thank you vary much! I think that's a good idea.
Please sign in to leave a comment.
Comments 3