原理 通过外部表将数据文件写入客户端所在的服务器(中转)再通过外部表引入文件到目标库。每次操作一张或多张表
设计思想: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();
}
}