Kettle开发之常用步骤开发

来源：互联网发布：java md5 编辑：程序博客网时间：2024/06/16 11:18

1. 输入步骤

1.1 csv输入

csv 输入对应的代码如下所示：

        CsvInputMeta csvInputMeta = new CsvInputMeta();        csvInputMeta.setDefault();          csvInputMeta.setDelimiter(",");        csvInputMeta.setFilename("/etl/data.csv");        csvInputMeta.setHeaderPresent(true);        String[] headers = {"name","firstname","zip","city","birthdate","street","housenr","stateCode","state"};//设置头部信息        TextFileInputField[] textFileInputFields = new  TextFileInputField[headers.length];        for(int i=0;i<headers.length;i++){            textFileInputFields[i] = new TextFileInputField();            textFileInputFields[i].setName(headers[i]);        }         csvInputMeta.setInputFields(textFileInputFields);        PluginRegistry registry = PluginRegistry.getInstance();        String textId = registry.getPluginId(StepPluginType.class,csvInputMeta);        StepMeta csvStepMeta = new StepMeta(textId,"csvInput",csvInputMeta);

1.2 数据库表的输入
数据库表输入的对应代码如下：

//第一个表输入步骤(TableInputMeta)            TableInputMeta tableInput = new TableInputMeta();            String tableInputPluginId = registry.getPluginId(StepPluginType.class, tableInput);            //给表输入添加一个DatabaseMeta连接数据库            DatabaseMeta database_bjdt = transMeta.findDatabase("bjdt");            tableInput.setDatabaseMeta(database_bjdt);            String select_sql = "SELECT name  FROM "+bjdt_tablename;            tableInput.setSQL(select_sql);            //添加TableInputMeta到转换中            StepMeta tableInputMetaStep = new StepMeta(tableInputPluginId,"table input",tableInput);

2. 转换步骤（去重，排序，序列）

2.1 去重

      UniqueRowsMeta uniqueRowsMeta = new UniqueRowsMeta();      uniqueRowsMeta.setDefault();      uniqueRowsMeta.setCompareFields(cpmareFields);      uniqueRowsMeta.setCaseInsensitive(new boolean[]{true});      String uniqueId = registry.getPluginId(StepPluginType.class,uniqueRowsMeta);      StepMeta uniqueStepMeta = new StepMeta(uniqueId,"",uniqueRowsMeta);

2.2 排序

      SortRowsMeta sortRowsMeta = new SortRowsMeta();      sortRowsMeta.setDefault();      sortRowsMeta.allocate(1);      sortRowsMeta.setFieldName(sortFields);      sortRowsMeta.setCaseSensitive(new boolean[]{false});//是否区分大小写      sortRowsMeta.setAscending(new boolean[]{true});//是否是升序排序 a-z      String sortId = registry.getPluginId(StepPluginType.class,sortRowsMeta);      StepMeta sortStepMeta = new StepMeta(sortId,"sort step",sortRowsMeta);

2.2 序列（添加一列）

      AddSequenceMeta addSequenceMeta = new AddSequenceMeta();      String addSequencePluginId = registry.getPluginId( StepPluginType.class, addSequenceMeta );      addSequenceMeta.setDefault();      addSequenceMeta.setMaxValue(100);      addSequenceMeta.setValuename( "counter" );      addSequenceMeta.setCounterName( "counter_1" );      addSequenceMeta.setStartAt( 1 );      addSequenceMeta.setMaxValue( Long.MAX_VALUE );      addSequenceMeta.setIncrementBy( 1 );      StepMeta addSequenceStepMeta = new StepMeta( addSequencePluginId, "Add Counter Field", addSequenceMeta );

3. 输出步骤

3.1 excel 输出

        ExcelOutputMeta excelOutputMeta = new ExcelOutputMeta();        String excelOutId=registry.getPluginId(StepPluginType.class, excelOutputMeta);        excelOutputMeta.setDefault();        excelOutputMeta.setCreateParentFolder(true);        excelOutputMeta.setFileName("etl/ceshiexce2");        excelOutputMeta.setHeaderEnabled(true);        StepMeta stepMeta2 = new StepMeta(excelOutId, "exceloutput", excelOutputMeta);

3.2 数据库输出

            InsertUpdateMeta insertUpdateMeta = new InsertUpdateMeta();            String insertUpdateMetaPluginId = registry.getPluginId(StepPluginType.class,insertUpdateMeta);            //添加数据库连接            DatabaseMeta database_kettle = transMeta.findDatabase("kettle");            insertUpdateMeta.setDatabaseMeta(database_kettle);            //设置操作的表            insertUpdateMeta.setTableName(kettle_tablename);            //设置用来查询的关键字            insertUpdateMeta.setKeyLookup(new String[]{"age","address","name"});            insertUpdateMeta.setKeyStream(new String[]{"age","address","name"});            insertUpdateMeta.setKeyStream2(new String[]{"","",""});//一定要加上            insertUpdateMeta.setKeyCondition(new String[]{"=","=","="});            //设置要更新的字段         String [] updateStream = {"address","age","name"};//获取KeyStream传递的值         String[] updatelookup = {"address1","age","name"} ;//对应数据库中的字段名            Boolean[] updateOrNot = {false,false,false};            insertUpdateMeta.setUpdateLookup(updatelookup);            insertUpdateMeta.setUpdateStream(updateStream);            insertUpdateMeta.setUpdate(updateOrNot);            String[] lookup = insertUpdateMeta.getUpdateLookup();            insertUpdateMeta.setKeyLookup(lookup);            //添加步骤到转换中            StepMeta insertUpdateStep = new StepMeta(insertUpdateMetaPluginId,"insert_update",insertUpdateMeta);

0 0