Kettle gets system time, sets variables, gets variables

kettle gets system time, sets variables, gets variables

two ways

regular expression

java code

1. New Transformation

Create a new conversion, select Get System Information in the input

regular expression

Select regular expression script in script

Why choose a regular expression script (simple, easy to use)
2023/07/29 13:44:27.242 -- This is the system date format we obtained
Through the following regular expression
(\d{4})/(\d{2})/(\d{2})\s\d{2}:\d{2}:\d{2}\.\d{3}
Divide the data into three capturing groups
The first capture group is 2023
The second capture group is 07
The third capture group is 29
Corresponding to the year, month and day respectively, to facilitate the calculation of subsequent indicators

Enter regular expression

Select Set Variables in Jobs for Core Objects

Add field and set to Valid in the Java Virtual Machine type
concatenate the above

2. Using variables

Open another conversion and select Get Variables in the job
select table input in input

-- Take the following sql as an example
-- Incrementally fetch the previous day's data from the orders table
select *,date_sub(str_to_date(?,'%Y-%m-%d'), INTERVAL 1 DAY) dt
from orders where substr(createTime,1,10) = date_sub(str_to_date(?,'%Y-%m-%d'), INTERVAL 1 DAY);
-- The question mark represents the parameters to be obtained, similar to insert into table value(?,?,?,?,?,?) written in java;
-- If there are several question marks, several values need to be passed

table connection

Edit to get the variable, because there are two question marks in it, you need to pass in two values
Select insert/update in output
save connection

Close all transformations, create a new job in the main object’s jobs

Timed task
run

Java code

Create a new transformation and name it to get yesterday’s time

Select java code in script

Select, double-click the main function, and the code will be automatically generated

edit code

import java.util.Calendar;
import java.util.Date;
import java.text.SimpleDateFormat;
import java.text.ParseException;

public boolean processRow(StepMetaInterface smi, StepDataInterface sdi) throws KettleException {<!-- -->
  if (first) {<!-- -->
    first = false;
  }

  Object[] r = getRow();

  if (r == null) {<!-- -->
    setOutputDone();
    return false;
  }

 r = createOutputRow(r, data. outputRowMeta. size());

  String foobar = get(Fields.In, "dt").getString(r);
 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
    // Create a Calendar object and set it to the incoming time
    Calendar calendar = Calendar. getInstance();
    try {<!-- -->
        calendar.setTime(format.parse(foobar));
    } catch (ParseException e) {<!-- -->
         throw new RuntimeException(e);
    }

    // Subtract 1 from the Calendar date, which is yesterday's date
    calendar. add(Calendar. DATE, -1);

    // Get yesterday's date
    Date yesterdayDate = calendar. getTime();
    // System.out.println("Yesterday's time: " + yesterdayDate);
    // Format yesterday's date into the specified format
    String yesterdayStr = sdf. format(yesterdayDate);
    // Set the fields that need to be output
  get(Fields. Out, "yesterday"). setValue(r, yesterdayStr);


  // Send the row on to the next step.
  putRow(data. outputRowMeta, r);

  return true;
}

Set output fields

This field needs to be the same as the input field in the code

test

Set variables, select set variables in the job

Test to obtain variables, the same as the regular expression above

update sql

select *,str_to_date(?,'%Y-%m-%d') dt
from order_goods where substr(createTime,1,10) = str_to_date(?,'%Y-%m-%d');

just test