通过gpfdist unload 与load java 代码设计和实现

原理 通过外部表将数据文件写入客户端所在的服务器(中转)再通过外部表引入文件到目标库。每次操作一张或多张表
设计思想:java 的面向对象思想
1将 gpfdist 看成一个对象 创建Gpfdist 类,因其作用有2个 就是启动和停止
即shell执行的两条命令
2每个gpfdist 进程的参数是不一样的 所以再创建一个GpfdistBean 用于存储每个gpfdist 实例的参数,通过GpfdistBean 的实例创建不同的启动和关闭命令
3创建可写外部表然后将数据存入外部文件是一个对象,所以定义WritAbleExternalTable类有两个方法 创建外部表和写数据
4每次写的外部表的具体参数不一样 所以创建WritAbleExternalTableBean 类用来存储每次WritAbleExternalTable实例的参数
5可读外部表同可写外部表
6创建一个线程调用1-5实现数据卸载和装在的过程
具体实现

GpfdistBean

public class GpfdistBean {
    private String clientFilePath;//gpfdist 安装位置
    private String startPort;//起始端口号
    private String endPort;//终止端口号
    private String logFilePath;//日志文件路径
    private String pid;//gpfdist 进程pid
    public String getClientFilePath() {
        return clientFilePath;
    }
    public void setClientFilePath(String clientFilePath) {
        this.clientFilePath = clientFilePath;
    }
    public String getStartPort() {
        return startPort;
    }
    public void setStartPort(String startPort) {
        this.startPort = startPort;
    }
    public String getEndPort() {
        return endPort;
    }
    public void setEndPort(String endPort) {
        this.endPort = endPort;
    }
    public String getLogFilePath() {
        return logFilePath;
    }
    public void setLogFilePath(String logFilePath) {
        this.logFilePath = logFilePath;
    }
    public String getPid() {
        return pid;
    }
    public void setPid(String pid) {
        this.pid = pid;
    }

}

Gpfdist

public class Gpfdist {

    //Starting gpfdist
    public String getGpfdistStartingCommand(GpfdistBean gpfdistBean) {
        String clientFilePath =gpfdistBean.getClientFilePath() ;//gpfdist 安装位置
        String startPort = gpfdistBean.getStartPort();//起始端口号
        String endPort =gpfdistBean.getEndPort();//终止端口号
        String logFilePath = gpfdistBean.getLogFilePath();//日志文件路径
        StringBuilder  command =new StringBuilder("gpfdist -d");
        command.append(clientFilePath);
        command.append(" -p ");
        command.append(startPort);
        command.append(" -P ");
        command.append(endPort);
        if(null==logFilePath?false:true) {
            command.append(" -l ");
            command.append(logFilePath);
        }
        return command.toString();
    }

    //Stopping gpfdist
    public String getGpfdistStoppingCommand(GpfdistBean gpfdistBean) {
        StringBuilder  command =new StringBuilder("kill -9 ");
        String pid = gpfdistBean.getPid();
        command.append(pid);
        return command.toString();
    }
}

WritableExternalTableBean

public class WritableExternalTableBean {
    private String tableName;//原表名称
    private String externalTableName;//外部表名称
    private String protocol;//通讯协议
    private String etlhost;//etl  ip
    private String etlPort;//etl  端口
    private String expenses;//数据文件绝对路径
    private String fileFormat;//数据文件格式
    private String delimiter;//数据文件字段分割符
    private String distributed;//数据分发原则
    public String getEtlhost() {
        return etlhost;
    }
    public void setEtlhost(String etlhost) {
        this.etlhost = etlhost;
    }
    public String getEtlPort() {
        return etlPort;
    }
    public void setEtlPort(String etlPort) {
        this.etlPort = etlPort;
    }
    public String getExpenses() {
        return expenses;
    }
    public void setExpenses(String expenses) {
        this.expenses = expenses;
    }

    public String getTableName() {
        return tableName;
    }
    public void setTableName(String tableName) {
        this.tableName = tableName;
    }
    public String getExternalTableName() {
        return externalTableName;
    }
    public void setExternalTableName(String externalTableName) {
        this.externalTableName = externalTableName;
    }
    public String getProtocol() {
        return protocol;
    }
    public void setProtocol(String protocol) {
        this.protocol = protocol;
    }
    public String getFileFormat() {
        return fileFormat;
    }
    public void setFileFormat(String fileFormat) {
        this.fileFormat = fileFormat;
    }
    public String getDelimiter() {
        return delimiter;
    }
    public void setDelimiter(String delimiter) {
        this.delimiter = delimiter;
    }
    public String getDistributed() {
        return distributed;
    }
    public void setDistributed(String distributed) {
        this.distributed = distributed;
    }

}

WritableExternalTable

/**
 * regular table--external table --external file 
 * @author 26110
 *
 */
public class WritableExternalTable {


    //create WritableExternalTable

    //1 Defining a Command-Based Writable External Web Table 废弃


    //2 Defining a File-Based Writable External Table 
    /**
     * file relation table
     * @return
     */
    public String getCreateWriteExtTableSql(WritableExternalTableBean writeTable) {
        StringBuilder sql = null;
        String tableName =writeTable.getTableName();//原表名称
        String externalTableName =writeTable.getExternalTableName();//外部表名称
        String protocol =writeTable.getProtocol();//通讯协议
        String etlhost =writeTable.getEtlhost();//etl  ip
        String etlPort =writeTable.getEtlPort();//etl  端口
        String expenses =writeTable.getExpenses();//数据文件绝对路径
        String fileFormat =writeTable.getFileFormat();//数据文件格式
        String delimiter =writeTable.getDelimiter();//数据文件字段分割符
        String distributed =writeTable.getDistributed();//数据分发原则
        sql=new StringBuilder("CREATE WRITABLE EXTERNAL TABLE  ");
        sql.append(externalTableName);
        sql.append(" ( LIKE ");
        sql.append(tableName);
        sql.append(" ) LOCATION (");
        sql.append("'");
        sql.append(protocol);
        sql.append("://");
        sql.append(etlhost);
        sql.append(":");
        sql.append(etlPort);
        sql.append("/");
        sql.append(expenses);
        sql.append("')");
        sql.append(" FORMAT '");
        sql.append(fileFormat);
        sql.append("' (DELIMITER '");
        sql.append(delimiter);
        sql.append("')");
        if(distributed==null?false:true) {
            sql.append(" DISTRIBUTED BY (");
            sql.append(distributed);
            sql.append(")");
        }
        return sql.toString();
    }

    //3 Unloading Data Using a Writable External Table 
    /**
     * pass writable external table write data to file
     * @return
     */
    public String getUnloadingSql(WritableExternalTableBean writeTable) {
        String external = writeTable.getExternalTableName();
        String tableName = writeTable.getTableName();
        String sql = null;
        sql="INSERT INTO writable_ext_table SELECT * FROM regular_table";
        sql.replace("writable_ext_table", external);
        sql.replace("regular_table", tableName);
        return sql;
    }

    //4 Unloading Data Using COPY 废弃

}

ReadableExternalTableBean

public class ReadableExternalTableBean {

    private String tableName;//原表名称
    private String externalTableName;//外部表名称
    private String protocol;//通讯协议
    private String etlhost;//etl  ip
    private String etlPort;//etl  端口
    private String expenses;//数据文件绝对路径
    private String fileFormat;//数据文件格式
    private String delimiter;//数据文件字段分割符
    private String distributed;//数据分发原则
    private String[] columnAndType;//字段名和字段类型
    public String getEtlhost() {
        return etlhost;
    }
    public void setEtlhost(String etlhost) {
        this.etlhost = etlhost;
    }
    public String getEtlPort() {
        return etlPort;
    }
    public void setEtlPort(String etlPort) {
        this.etlPort = etlPort;
    }
    public String getExpenses() {
        return expenses;
    }
    public void setExpenses(String expenses) {
        this.expenses = expenses;
    }

    public String getTableName() {
        return tableName;
    }
    public void setTableName(String tableName) {
        this.tableName = tableName;
    }
    public String getExternalTableName() {
        return externalTableName;
    }
    public void setExternalTableName(String externalTableName) {
        this.externalTableName = externalTableName;
    }
    public String getProtocol() {
        return protocol;
    }
    public void setProtocol(String protocol) {
        this.protocol = protocol;
    }
    public String getFileFormat() {
        return fileFormat;
    }
    public void setFileFormat(String fileFormat) {
        this.fileFormat = fileFormat;
    }
    public String getDelimiter() {
        return delimiter;
    }
    public void setDelimiter(String delimiter) {
        this.delimiter = delimiter;
    }
    public String getDistributed() {
        return distributed;
    }
    public void setDistributed(String distributed) {
        this.distributed = distributed;
    }
    public String[] getColumnAndType() {
        return columnAndType;
    }
    public void setColumnAndType(String[] columnAndType) {
        this.columnAndType = columnAndType;
    }



}

ReadableExternalTable


/**
 * external file --external table --regular table
 * @author 26110
 *
 */
public class ReadableExternalTable {

    /*
     * 
     CREATE EXTERNAL TABLE load_zxs_test2 (
    e_id INTEGER,
    e_name CHARACTER VARYING(30),
    e_date DATE,
    e_address CHARACTER VARYING(100),
    e_tel CHARACTER VARYING(11),
    e_id_1 INTEGER,
    e_name_1 CHARACTER VARYING(30),
    e_date_1 DATE,
    e_address_1 CHARACTER VARYING(100),
    e_tel_1 CHARACTER VARYING(11)
    )  
     LOCATION ('gpfdist://192.168.12.94:8899/zxs_test2.txt')   FORMAT 'TEXT' ( DELIMITER '|' NULL ' ')  

      LOG ERRORS SEGMENT REJECT LIMIT 5;


    INSERT INTO zxs_test0 SELECT * FROM load_zxs_test2

     */
    /**
     * pass Readable external file table write data to database
     * @return
     */

    public String getCreateReadExtTableSql(ReadableExternalTableBean readTable) {
        StringBuilder sql = null;
        String externalTableName =readTable.getExternalTableName();//外部表名称
        String protocol =readTable.getProtocol();//通讯协议
        String etlhost =readTable.getEtlhost();//etl  ip
        String etlPort =readTable.getEtlPort();//etl  端口
        String expenses =readTable.getExpenses();//数据文件绝对路径
        String fileFormat =readTable.getFileFormat();//数据文件格式
        String delimiter =readTable.getDelimiter();//数据文件字段分割符
        String[] columnAndType = readTable.getColumnAndType();//字段名和字段类型
        sql=new StringBuilder("CREATE  EXTERNAL TABLE  ");
        sql.append(externalTableName);
        sql.append(" (  ");
        sql.append(columnAndType.toString());
        sql.append(" ) LOCATION (");
        sql.append("'");
        sql.append(protocol);
        sql.append("://");
        sql.append(etlhost);
        sql.append(":");
        sql.append(etlPort);
        sql.append("/");
        sql.append(expenses);
        sql.append("')");
        sql.append(" FORMAT '");
        sql.append(fileFormat);
        sql.append("' (DELIMITER '");
        sql.append(delimiter);
        sql.append("')");
        sql.append("  LOG ERRORS SEGMENT REJECT LIMIT 5;");
        return sql.toString();
    }
    //3 loading Data Using a Readable External Table 
        /**
         * pass Readable external file relation date to external table 
         * @return
         */
        public String getLoadingSql(ReadableExternalTableBean readTable) {
            String external = readTable.getExternalTableName();
            String tableName =readTable.getTableName();
            String sql = null;
            sql="INSERT INTO regular_table SELECT * FROM readable_ext_table ";
            sql.replace("readable_ext_table", external);
            sql.replace("regular_table", tableName);
            return sql;
        }
}

UnloadAndLoadThread


import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * one table one thread
 * contains unload and mask and load step
 * @author 26110
 *
 */
public class UnloadAndLoadThread implements Runnable {
    private WritableExternalTableBean wb;
    private ReadableExternalTableBean rb;
    private GpfdistBean gpfdistBean;

    public UnloadAndLoadThread(WritableExternalTableBean wb, ReadableExternalTableBean rb, GpfdistBean gpfdistBean) {
        this.wb = wb;
        this.rb = rb;
        this.gpfdistBean = gpfdistBean;
    }

    @Override
    public void run() {
        // 一 unload
        // 1 run gpfdist
        Gpfdist unloadGpfdist = new Gpfdist();
        String unloadCommand = unloadGpfdist.getGpfdistStartingCommand(this.gpfdistBean);
        Runtime unloadRunTime = Runtime.getRuntime();
        try {
            unloadRunTime.exec(unloadCommand);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        WritableExternalTable wet = new WritableExternalTable();
        // 2 get writable external table sql
        String createWriteExtTabsql = wet.getCreateWriteExtTableSql(wb);
        // 3 get write data to file sql
        String insert2WriteFileSql = wet.getUnloadingSql(wb);
        // 4 make connection and execute sql
        String unloadurl = "";
        String unloaduser = "";
        String unloadpassword = "";
        Connection unloadConn = null;
        try {
            unloadConn = GreenplumConnection.getConnection(unloadurl, unloaduser, unloadpassword);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        Statement unloadst = null;
        try {
            unloadst = unloadConn.createStatement();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            unloadst.execute(createWriteExtTabsql);
            unloadst.execute(insert2WriteFileSql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        // 5 释放资源,关闭连接,关闭gpfdist
        try {
            unloadst.close();
            GreenplumConnection.closeConnection(unloadConn);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        unloadGpfdist.getGpfdistStoppingCommand(this.gpfdistBean);

        // 三 load
        // 1 run gpfdist
        Gpfdist loadGpfdist = new Gpfdist();
        String loadCommand = loadGpfdist.getGpfdistStartingCommand(this.gpfdistBean);
        Runtime loadRunTime = Runtime.getRuntime();
        try {
            loadRunTime.exec(loadCommand);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        ReadableExternalTable ret = new ReadableExternalTable();
        // 2 get readable external table sql
        String createReadExtTabsql = ret.getCreateReadExtTableSql(rb);
        // 3 get write file to database sql
        String insert2DatabaseSql = ret.getLoadingSql(rb);
        // 4 make connection and execute sql
        String loadurl = "";
        String loaduser = "";
        String loadpassword = "";
        Connection loadconn = null;
        try {
            loadconn = GreenplumConnection.getConnection(loadurl, loaduser, loadpassword);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        Statement loadst = null;
        try {
            loadst = loadconn.createStatement();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        try {
            loadst.execute(createReadExtTabsql);
            loadst.execute(insert2DatabaseSql);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        // 5 释放资源,关闭连接,关闭gpfdist
        try {
            loadst.close();
            GreenplumConnection.closeConnection(loadconn);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        loadGpfdist.getGpfdistStoppingCommand(this.gpfdistBean);
    }

}

GreenplumConnection


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class GreenplumConnection {
    public static Connection getConnection(String url,String user,String password) throws SQLException {
        Connection conn = null;
        DriverManager.getConnection(url, user, password);
        return conn;
    }
    public static void closeConnection(Connection conn) throws SQLException {
        conn.close();
    }
}
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值