Kettle_ES_Two
Normal
0
/
ID_BATCH
Y
ID_BATCH
CHANNEL_ID
Y
CHANNEL_ID
TRANSNAME
Y
TRANSNAME
STATUS
Y
STATUS
LINES_READ
Y
LINES_READ
LINES_WRITTEN
Y
LINES_WRITTEN
LINES_UPDATED
Y
LINES_UPDATED
LINES_INPUT
Y
LINES_INPUT
LINES_OUTPUT
Y
LINES_OUTPUT
LINES_REJECTED
Y
LINES_REJECTED
ERRORS
Y
ERRORS
STARTDATE
Y
STARTDATE
ENDDATE
Y
ENDDATE
LOGDATE
Y
LOGDATE
DEPDATE
Y
DEPDATE
REPLAYDATE
Y
REPLAYDATE
LOG_FIELD
Y
LOG_FIELD
EXECUTING_SERVER
N
EXECUTING_SERVER
EXECUTING_USER
N
EXECUTING_USER
CLIENT
N
CLIENT
ID_BATCH
Y
ID_BATCH
SEQ_NR
Y
SEQ_NR
LOGDATE
Y
LOGDATE
TRANSNAME
Y
TRANSNAME
STEPNAME
Y
STEPNAME
STEP_COPY
Y
STEP_COPY
LINES_READ
Y
LINES_READ
LINES_WRITTEN
Y
LINES_WRITTEN
LINES_UPDATED
Y
LINES_UPDATED
LINES_INPUT
Y
LINES_INPUT
LINES_OUTPUT
Y
LINES_OUTPUT
LINES_REJECTED
Y
LINES_REJECTED
ERRORS
Y
ERRORS
INPUT_BUFFER_ROWS
Y
INPUT_BUFFER_ROWS
OUTPUT_BUFFER_ROWS
Y
OUTPUT_BUFFER_ROWS
ID_BATCH
Y
ID_BATCH
CHANNEL_ID
Y
CHANNEL_ID
LOG_DATE
Y
LOG_DATE
LOGGING_OBJECT_TYPE
Y
LOGGING_OBJECT_TYPE
OBJECT_NAME
Y
OBJECT_NAME
OBJECT_COPY
Y
OBJECT_COPY
REPOSITORY_DIRECTORY
Y
REPOSITORY_DIRECTORY
FILENAME
Y
FILENAME
OBJECT_ID
Y
OBJECT_ID
OBJECT_REVISION
Y
OBJECT_REVISION
PARENT_CHANNEL_ID
Y
PARENT_CHANNEL_ID
ROOT_CHANNEL_ID
Y
ROOT_CHANNEL_ID
ID_BATCH
Y
ID_BATCH
CHANNEL_ID
Y
CHANNEL_ID
LOG_DATE
Y
LOG_DATE
TRANSNAME
Y
TRANSNAME
STEPNAME
Y
STEPNAME
STEP_COPY
Y
STEP_COPY
LINES_READ
Y
LINES_READ
LINES_WRITTEN
Y
LINES_WRITTEN
LINES_UPDATED
Y
LINES_UPDATED
LINES_INPUT
Y
LINES_INPUT
LINES_OUTPUT
Y
LINES_OUTPUT
LINES_REJECTED
Y
LINES_REJECTED
ERRORS
Y
ERRORS
LOG_FIELD
N
LOG_FIELD
ID_BATCH
Y
ID_BATCH
CHANNEL_ID
Y
CHANNEL_ID
LOG_DATE
Y
LOG_DATE
METRICS_DATE
Y
METRICS_DATE
METRICS_CODE
Y
METRICS_CODE
METRICS_DESCRIPTION
Y
METRICS_DESCRIPTION
METRICS_SUBJECT
Y
METRICS_SUBJECT
METRICS_TYPE
Y
METRICS_TYPE
METRICS_VALUE
Y
METRICS_VALUE
0.0
0.0
10000
50
50
N
Y
50000
Y
N
1000
100
-
2020/09/10 09:27:01.527
-
2020/09/10 09:27:01.527
H4sIAAAAAAAAAAMAAAAAAAAAAAA=
N
10.10.14.107
10.10.14.107
GREENPLUM
Native
base_db_dev
5432
dsideal
Encrypted 2faebc6ac0fc6bed5ff4efc2586c1f983
FORCE_IDENTIFIERS_TO_LOWERCASE
N
FORCE_IDENTIFIERS_TO_UPPERCASE
N
IS_CLUSTERED
N
PORT_NUMBER
5432
PRESERVE_RESERVED_WORD_CASE
Y
QUOTE_ALL_FIELDS
N
SUPPORTS_BOOLEAN_DATA_TYPE
Y
SUPPORTS_TIMESTAMP_DATA_TYPE
Y
USE_POOLING
N
REST client
JavaScript代码【获取总数、游标】
N
字符串替换
REST client
N
JavaScript代码【获取总数、游标】
设置变量
Y
获取系统信息
字符串替换
Y
REST client
JavaScript代码【生成insert SQL】
N
JavaScript代码【生成insert SQL】
文本文件输出
Y
JavaScript代码【生成insert SQL】
执行SQL脚本
Y
字符串替换
HTTP post
Y
HTTP post
JavaScript代码【获取总数、游标】
Y
HTTP post
JavaScript代码【生成insert SQL】
Y
JavaScript代码【生成insert SQL】
ScriptValueMod
N
1
none
N
9
0
Script 1
eval('var result ='+ result );
var sql_row="";
eval('var columns ='+ es_columns );
for(var x=0;x< result.rows.length;){
var outputRow = new Array(_step_.getOutputRowMeta().size());
var my_sql = "";
//insert into table
my_sql = "insert into " + es_table + " values ";
for(var y=0;y<100;y++,x++){
my_sql =my_sql +"("
for(var z=0;z< columns.length;z++){
switch (columns[z].type){
case "keyword":
my_sql = my_sql +" '"+ result.rows[x][z]+"',";
break;
case "float":
my_sql = my_sql +" "+ result.rows[x][z]+",";
break;
case "long":
my_sql = my_sql +" "+ result.rows[x][z]+",";
break;
case "datetime":
my_sql = my_sql +" '"+ result.rows[x][z].substr(0,19).replace("T"," ")+"',";
break;
default:
my_sql = my_sql +" '"+ result.rows[x][z]+"',";
break;
}
}
my_sql = my_sql.substr(0,my_sql.length -1) +" ),";
}
my_sql = my_sql.substr(0,my_sql.length -1) +" ;";
outputRow[_step_.getOutputRowMeta().indexOfValue("sql_row")] = my_sql;
_step_.putRow(_step_.getOutputRowMeta(), outputRow);
}
var trans_Status = SKIP_TRANSFORMATION;
sql_row
sql_row
String
-1
-1
N
544
208
Y
JavaScript代码【获取总数、游标】
ScriptValueMod
Y
1
none
N
9
0
Script 1
eval('var result ='+ result );
var es_cursor = result.cursor;
var es_total = result.rows.length;
es_cursor
es_cursor
String
-1
-1
N
es_total
es_total
Integer
-1
-1
N
544
64
Y
REST client
Rest
N
1
none
JSON
POST
http://10.10.14.61:9200/_sql
N
Y
es_method
es_sql2
Encrypted
N
Encrypted
result
384
64
Y
字符串替换
ReplaceString
Y
1
none
es_sql
es_sql2
no
}
,"cursor":"${es_cursor}"}
N
no
no
no
256
64
Y
执行SQL脚本
ExecSQL
Y
1
none
10.10.14.107
Y
N
Y
N
?
N
sql_row
720
352
Y
文本文件输出
TextFileOutput
Y
1
none
;
N
N
DOS
None
N
N
/D:/KettleWork/SQL_File/${es_table}/ETL_All_
N
N
sql
Y
N
N
N
N
Y
yyyyMMddHHmmss
Y
N
N
sql_row
String
none
-1
-1
720
208
Y
获取系统信息
SystemInfo
Y
1
none
es_method
command line argument 1
es_sql
command line argument 2
es_cursor
command line argument 3
es_table
command line argument 4
es_columns
command line argument 5
112
64
Y
设置变量
SetVariable
Y
1
none
es_total
es_total
JVM
es_cursor
es_cursor
JVM
Y
720
64
Y
HTTP post
HTTPPOST
N
1
none
N
http://10.10.14.61:9200/_sql
N
es_sql2
Encrypted
30000
30000
-1
es_method
Content-Type
result
384
176
Y
N