1、kettle/kettle建立数据交换数据库连接 wrx_dc/wrx_dcwrx_jw/wrx_jwwrx_xg/wrx_xg- Repository creation/upgrade DDL: - Nothing was created nor modified in the target repository database.- Hit the OK button to execute the generated SQL or Close to reject the changes.- Please note that it is possible to change/edit the
2、 generated SQL before execution.-CREATE TABLE R_REPOSITORY_LOG(ID_REPOSITORY_LOG INTEGER, REP_VERSION VARCHAR2(255), LOG_DATE DATE, LOG_USER VARCHAR2(255), OPERATION_DESC CLOB, PRIMARY KEY (ID_REPOSITORY_LOG);ALTER TABLE R_VERSION ADD ( UPGRADE_DATE_KTL DATE ) ;UPDATE R_VERSION SET UPGRADE_DATE_KTL=
3、UPGRADE_DATE;ALTER TABLE R_VERSION DROP ( UPGRADE_DATE ) ;ALTER TABLE R_VERSION ADD ( UPGRADE_DATE DATE ) ;UPDATE R_VERSION SET UPGRADE_DATE=UPGRADE_DATE_KTL;ALTER TABLE R_VERSION DROP ( UPGRADE_DATE_KTL ) ;INSERT INTO R_VERSION(ID_VERSION, MAJOR_VERSION, MINOR_VERSION, UPGRADE_DATE, IS_UPGRADE) VAL
4、UES (1,5,0,TO_DATE(2016/08/20 10:16:49, YYYY/MM/DD HH24:MI:SS),N);CREATE TABLE R_DATABASE_TYPE(ID_DATABASE_TYPE INTEGER, CODE VARCHAR2(255), DESCRIPTION VARCHAR2(255), PRIMARY KEY (ID_DATABASE_TYPE);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (1,ORACLERDB,Oracle RDB);INSE
5、RT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (2,INFINIDB,Calpont InfiniDB);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (3,INFOBRIGHT,Infobright);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (4,PALO,Palo MOLAP Server);INSER
6、T INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (5,GENERIC,Generic database);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (6,AS/400,AS/400);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (7,SYBASEIQ,SybaseIQ);INSERT INTO R_DATABA
7、SE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (8,HIVE2,Hadoop Hive 2);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (9,SQLITE,SQLite);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (10,DERBY,Apache Derby);INSERT INTO R_DATABASE_TYPE(ID_DATABAS
8、E_TYPE, CODE, DESCRIPTION) VALUES (11,INFORMIX,Informix);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (12,INGRES,Ingres);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (13,HIVE,Hadoop Hive);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRI
9、PTION) VALUES (14,IMPALASIMBA,Cloudera Impala);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (15,REMEDY-AR-SYSTEM,Remedy Action Request System);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (16,POSTGRESQL,PostgreSQL);INSERT INTO R_DATABASE_TYPE(ID_
10、DATABASE_TYPE, CODE, DESCRIPTION) VALUES (17,SAPR3,SAP ERP System);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (18,REDSHIFT,Redshift);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (19,CACHE,Intersystems Cache);INSERT INTO R_DATABASE_TYPE(ID_DATAB
11、ASE_TYPE, CODE, DESCRIPTION) VALUES (20,MSSQL,MS SQL Server);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (21,TERADATA,Teradata);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (22,DB2,IBM DB2);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DES
12、CRIPTION) VALUES (23,SQLBASE,Gupta SQL Base);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (24,MYSQL,MySQL);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (25,FIREBIRD,Firebird SQL);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VA
13、LUES (26,HYPERSONIC,Hypersonic);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (27,MSACCESS,MS Access);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (28,KINGBASEES,KingbaseES);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (
14、29,INTERBASE,Borland Interbase);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (30,VERTICA5,Vertica 5+);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (31,MSSQLNATIVE,MS SQL Server (Native);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPT
15、ION) VALUES (32,EXASOL4,Exasol 4);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (33,EXTENDB,ExtenDB);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (34,KettleThin,Pentaho Data Services);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION
16、) VALUES (35,OpenERPDatabaseMeta,OpenERP Server);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (36,UNIVERSE,UniVerse database);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (37,NEOVIEW,Neoview);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DE
17、SCRIPTION) VALUES (38,LucidDB,LucidDB);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (39,SAPDB,MaxDB (SAP DB);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (40,VERTICA,Vertica);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES
18、 (41,DBASE,dBase III, IV or 5);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (42,H2,H2);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (43,VECTORWISE,Ingres VectorWise);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (44,SYBA
19、SE,Sybase);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (45,ORACLE,Oracle);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (46,MONDRIAN,Native Mondrian);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (47,NETEZZA,Netezza);INS
20、ERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (48,IMPALA,Impala);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (49,GREENPLUM,Greenplum);INSERT INTO R_DATABASE_TYPE(ID_DATABASE_TYPE, CODE, DESCRIPTION) VALUES (50,MONETDB,MonetDB);CREATE TABLE R_DATABAS
21、E_CONTYPE(ID_DATABASE_CONTYPE INTEGER, CODE VARCHAR2(255), DESCRIPTION VARCHAR2(255), PRIMARY KEY (ID_DATABASE_CONTYPE);INSERT INTO R_DATABASE_CONTYPE(ID_DATABASE_CONTYPE, CODE, DESCRIPTION) VALUES (1,Native,Native (JDBC);INSERT INTO R_DATABASE_CONTYPE(ID_DATABASE_CONTYPE, CODE, DESCRIPTION) VALUES
22、(2,ODBC,ODBC);INSERT INTO R_DATABASE_CONTYPE(ID_DATABASE_CONTYPE, CODE, DESCRIPTION) VALUES (3,OCI,OCI);INSERT INTO R_DATABASE_CONTYPE(ID_DATABASE_CONTYPE, CODE, DESCRIPTION) VALUES (4,Plugin,Plugin specific access method);INSERT INTO R_DATABASE_CONTYPE(ID_DATABASE_CONTYPE, CODE, DESCRIPTION) VALUES
23、 (5,JNDI,JNDI);INSERT INTO R_DATABASE_CONTYPE(ID_DATABASE_CONTYPE, CODE, DESCRIPTION) VALUES (6,Custom);CREATE TABLE R_NOTE(ID_NOTE INTEGER, VALUE_STR CLOB, GUI_LOCATION_X INTEGER, GUI_LOCATION_Y INTEGER, GUI_LOCATION_WIDTH INTEGER, GUI_LOCATION_HEIGHT INTEGER, FONT_NAME CLOB, FONT_SIZE INTEGER, FON
24、T_BOLD CHAR(1), FONT_ITALIC CHAR(1), FONT_COLOR_RED INTEGER, FONT_COLOR_GREEN INTEGER, FONT_COLOR_BLUE INTEGER, FONT_BACK_GROUND_COLOR_RED INTEGER, FONT_BACK_GROUND_COLOR_GREEN INTEGER, FONT_BACK_GROUND_COLOR_BLUE INTEGER, FONT_BORDER_COLOR_RED INTEGER, FONT_BORDER_COLOR_GREEN INTEGER, FONT_BORDER_C
25、OLOR_BLUE INTEGER, DRAW_SHADOW CHAR(1), PRIMARY KEY (ID_NOTE);CREATE TABLE R_DATABASE(ID_DATABASE INTEGER, NAME VARCHAR2(255), ID_DATABASE_TYPE INTEGER, ID_DATABASE_CONTYPE INTEGER, HOST_NAME VARCHAR2(255), DATABASE_NAME CLOB, PORT INTEGER, USERNAME VARCHAR2(255), PASSWORD VARCHAR2(255), SERVERNAME
26、VARCHAR2(255), DATA_TBS VARCHAR2(255), INDEX_TBS VARCHAR2(255), PRIMARY KEY (ID_DATABASE);CREATE TABLE R_DATABASE_ATTRIBUTE(ID_DATABASE_ATTRIBUTE INTEGER, ID_DATABASE INTEGER, CODE VARCHAR2(255), VALUE_STR CLOB, PRIMARY KEY (ID_DATABASE_ATTRIBUTE);CREATE UNIQUE INDEX IDX_RDAT ON R_DATABASE_ATTRIBUTE
27、(ID_DATABASE, CODE);CREATE TABLE R_DIRECTORY(ID_DIRECTORY INTEGER, ID_DIRECTORY_PARENT INTEGER, DIRECTORY_NAME VARCHAR2(255), PRIMARY KEY (ID_DIRECTORY);CREATE UNIQUE INDEX IDX_RDIR ON R_DIRECTORY(ID_DIRECTORY_PARENT, DIRECTORY_NAME);CREATE TABLE R_TRANSFORMATION(ID_TRANSFORMATION INTEGER, ID_DIRECT
28、ORY INTEGER, NAME VARCHAR2(255), DESCRIPTION CLOB, EXTENDED_DESCRIPTION CLOB, TRANS_VERSION VARCHAR2(255), TRANS_STATUS INTEGER, ID_STEP_READ INTEGER, ID_STEP_WRITE INTEGER, ID_STEP_INPUT INTEGER, ID_STEP_OUTPUT INTEGER, ID_STEP_UPDATE INTEGER, ID_DATABASE_LOG INTEGER, TABLE_NAME_LOG VARCHAR2(255),
29、USE_BATCHID CHAR(1), USE_LOGFIELD CHAR(1), ID_DATABASE_MAXDATE INTEGER, TABLE_NAME_MAXDATE VARCHAR2(255), FIELD_NAME_MAXDATE VARCHAR2(255), OFFSET_MAXDATE NUMBER(12, 2), DIFF_MAXDATE NUMBER(12, 2), CREATED_USER VARCHAR2(255), CREATED_DATE DATE, MODIFIED_USER VARCHAR2(255), MODIFIED_DATE DATE, SIZE_R
30、OWSET INTEGER, PRIMARY KEY (ID_TRANSFORMATION);CREATE TABLE R_TRANS_ATTRIBUTE(ID_TRANS_ATTRIBUTE INTEGER, ID_TRANSFORMATION INTEGER, NR INTEGER, CODE VARCHAR2(255), VALUE_NUM INTEGER, VALUE_STR CLOB, PRIMARY KEY (ID_TRANS_ATTRIBUTE);CREATE UNIQUE INDEX IDX_TATT ON R_TRANS_ATTRIBUTE(ID_TRANSFORMATION
31、, CODE, NR);CREATE TABLE R_JOB_ATTRIBUTE(ID_JOB_ATTRIBUTE INTEGER, ID_JOB INTEGER, NR INTEGER, CODE VARCHAR2(255), VALUE_NUM INTEGER, VALUE_STR CLOB, PRIMARY KEY (ID_JOB_ATTRIBUTE);CREATE UNIQUE INDEX IDX_JATT ON R_JOB_ATTRIBUTE(ID_JOB, CODE, NR);CREATE TABLE R_DEPENDENCY(ID_DEPENDENCY INTEGER, ID_T
32、RANSFORMATION INTEGER, ID_DATABASE INTEGER, TABLE_NAME VARCHAR2(255), FIELD_NAME VARCHAR2(255), PRIMARY KEY (ID_DEPENDENCY);CREATE TABLE R_PARTITION_SCHEMA(ID_PARTITION_SCHEMA INTEGER, NAME VARCHAR2(255), DYNAMIC_DEFINITION CHAR(1), PARTITIONS_PER_SLAVE VARCHAR2(255), PRIMARY KEY (ID_PARTITION_SCHEM
33、A);CREATE TABLE R_PARTITION(ID_PARTITION INTEGER, ID_PARTITION_SCHEMA INTEGER, PARTITION_ID VARCHAR2(255), PRIMARY KEY (ID_PARTITION);CREATE TABLE R_TRANS_PARTITION_SCHEMA(ID_TRANS_PARTITION_SCHEMA INTEGER, ID_TRANSFORMATION INTEGER, ID_PARTITION_SCHEMA INTEGER, PRIMARY KEY (ID_TRANS_PARTITION_SCHEM
34、A);CREATE TABLE R_CLUSTER(ID_CLUSTER INTEGER, NAME VARCHAR2(255), BASE_PORT VARCHAR2(255), SOCKETS_BUFFER_SIZE VARCHAR2(255), SOCKETS_FLUSH_INTERVAL VARCHAR2(255), SOCKETS_COMPRESSED CHAR(1), DYNAMIC_CLUSTER CHAR(1), PRIMARY KEY (ID_CLUSTER);CREATE TABLE R_SLAVE(ID_SLAVE INTEGER, NAME VARCHAR2(255),
35、 HOST_NAME VARCHAR2(255), PORT VARCHAR2(255), WEB_APP_NAME VARCHAR2(255), USERNAME VARCHAR2(255), PASSWORD VARCHAR2(255), PROXY_HOST_NAME VARCHAR2(255), PROXY_PORT VARCHAR2(255), NON_PROXY_HOSTS VARCHAR2(255), MASTER CHAR(1), PRIMARY KEY (ID_SLAVE);CREATE TABLE R_CLUSTER_SLAVE(ID_CLUSTER_SLAVE INTEG
36、ER, ID_CLUSTER INTEGER, ID_SLAVE INTEGER, PRIMARY KEY (ID_CLUSTER_SLAVE);CREATE TABLE R_TRANS_SLAVE(ID_TRANS_SLAVE INTEGER, ID_TRANSFORMATION INTEGER, ID_SLAVE INTEGER, PRIMARY KEY (ID_TRANS_SLAVE);CREATE TABLE R_TRANS_CLUSTER(ID_TRANS_CLUSTER INTEGER, ID_TRANSFORMATION INTEGER, ID_CLUSTER INTEGER,
37、PRIMARY KEY (ID_TRANS_CLUSTER);CREATE TABLE R_TRANS_HOP(ID_TRANS_HOP INTEGER, ID_TRANSFORMATION INTEGER, ID_STEP_FROM INTEGER, ID_STEP_TO INTEGER, ENABLED CHAR(1), PRIMARY KEY (ID_TRANS_HOP);CREATE TABLE R_TRANS_STEP_CONDITION(ID_TRANSFORMATION INTEGER, ID_STEP INTEGER, ID_CONDITION INTEGER);CREATE
38、TABLE R_CONDITION(ID_CONDITION INTEGER, ID_CONDITION_PARENT INTEGER, NEGATED CHAR(1), OPERATOR VARCHAR2(255), LEFT_NAME VARCHAR2(255), CONDITION_FUNCTION VARCHAR2(255), RIGHT_NAME VARCHAR2(255), ID_VALUE_RIGHT INTEGER, PRIMARY KEY (ID_CONDITION);CREATE TABLE R_VALUE(ID_VALUE INTEGER, NAME VARCHAR2(2
39、55), VALUE_TYPE VARCHAR2(255), VALUE_STR VARCHAR2(255), IS_NULL CHAR(1), PRIMARY KEY (ID_VALUE);CREATE TABLE R_STEP_TYPE(ID_STEP_TYPE INTEGER, CODE VARCHAR2(255), DESCRIPTION VARCHAR2(255), HELPTEXT VARCHAR2(255), PRIMARY KEY (ID_STEP_TYPE);INSERT INTO R_STEP_TYPE(ID_STEP_TYPE, CODE, DESCRIPTION, HE
40、LPTEXT) VALUES (1,MappingInput,映射输入规范,指定一个映射的字段输入);INSERT INTO R_STEP_TYPE(ID_STEP_TYPE, CODE, DESCRIPTION, HELPTEXT) VALUES (2,SystemInfo,获取系统信息 ,获取系统信息，例如时间 、日期.);INSERT INTO R_STEP_TYPE(ID_STEP_TYPE, CODE, DESCRIPTION, HELPTEXT) VALUES (3,OpenERPObjectOutputImport,OpenERP Object Output,Writes dat
41、a into OpenERP objects using the object import procedure);INSERT INTO R_STEP_TYPE(ID_STEP_TYPE, CODE, DESCRIPTION, HELPTEXT) VALUES (4,SASInput,SAS 输入 ,This step reads files in sas7bdat (SAS) native format);INSERT INTO R_STEP_TYPE(ID_STEP_TYPE, CODE, DESCRIPTION, HELPTEXT) VALUES (5,GPBulkLoader,Gre
42、enplum Bulk Loader,Greenplum Bulk Loader);INSERT INTO R_STEP_TYPE(ID_STEP_TYPE, CODE, DESCRIPTION, HELPTEXT) VALUES (6,MergeJoin,记录集连接 ,Joins two streams on a given key and outputs a joined set. The input streams must be sorted on the join key);INSERT INTO R_STEP_TYPE(ID_STEP_TYPE, CODE, DESCRIPTION
43、, HELPTEXT) VALUES (7,ExecSQLRow,执行 SQL 脚本(字段流替换),Execute SQL script extracted from a field|chr(13)|created in a previous step.);INSERT INTO R_STEP_TYPE(ID_STEP_TYPE, CODE, DESCRIPTION, HELPTEXT) VALUES (8,TableExists,检查表是否存在,Check if a table exists on a specified connection);INSERT INTO R_STEP_TYPE
44、(ID_STEP_TYPE, CODE, DESCRIPTION, HELPTEXT) VALUES (9,GetFileNames,获取文件名,Get file names from the operating system and send them to the next step.);INSERT INTO R_STEP_TYPE(ID_STEP_TYPE, CODE, DESCRIPTION, HELPTEXT) VALUES (10,SocketWriter,Socket 写,Socket writer. A socket server that can send rows of
45、data to a socket reader.);INSERT INTO R_STEP_TYPE(ID_STEP_TYPE, CODE, DESCRIPTION, HELPTEXT) VALUES (11,MongoDbInput,MongoDB Input,Reads from a Mongo DB collection);INSERT INTO R_STEP_TYPE(ID_STEP_TYPE, CODE, DESCRIPTION, HELPTEXT) VALUES (12,Injector,记录注射,Injector step to allow to inject rows into
46、the transformation through the java API);INSERT INTO R_STEP_TYPE(ID_STEP_TYPE, CODE, DESCRIPTION, HELPTEXT) VALUES (13,SingleThreader,Single Threader,Executes a transformation snippet in a single thread. You need a standard mapping or a transformation with an Injector step where data from the parent
47、 transformation will arive in blocks.);INSERT INTO R_STEP_TYPE(ID_STEP_TYPE, CODE, DESCRIPTION, HELPTEXT) VALUES (14,MailValidator,检验邮件地址, Check if an email address is valid.);INSERT INTO R_STEP_TYPE(ID_STEP_TYPE, CODE, DESCRIPTION, HELPTEXT) VALUES (15,AutoDoc,自动文档输出 ,This step automatically genera
48、tes documentation based on input in the form of a list of transformations and jobs);INSERT INTO R_STEP_TYPE(ID_STEP_TYPE, CODE, DESCRIPTION, HELPTEXT) VALUES (16,PropertyOutput,配置文件输出 ,Write data to properties file);INSERT INTO R_STEP_TYPE(ID_STEP_TYPE, CODE, DESCRIPTION, HELPTEXT) VALUES (17,UniqueRowsByHashSet,唯一行 (哈希值) ,Remove double rows and leave only unique occurrences by using a HashSet.);INSERT INTO R_STEP_TYPE(ID_STEP_TYPE, CODE, DESCRIPTION, HELPTEXT) VALUES (18,CheckSum,增加校验列 ,Add a checksum column for each
