通过gpfdist unload 与load java 代码设计和实现
来源：互联网 发布：网络印刷系统多少钱 编辑：程序博客网 时间：2024/06/12 00:19
原理 通过外部表将数据文件写入客户端所在的服务器（中转）再通过外部表引入文件到目标库。每次操作一张或多张表
设计思想：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 0
- 通过gpfdist unload 与load java 代码设计和实现
- 使用gpfdist unload 数据
- scull 使用 load 和 unload
- Greenplum使用gpload通过gpfdist实现文件的高速加载
- GreenPlum 使用gpload通过gpfdist文件实现数据高速加载
- Load and Unload
- Load and Unload
- Load and Unload
- Load & Unload *.kext
- Ogre Plugin Load & Unload
- 关于greenPlum中通过gpfdist导入数据不成功的问题与原因
- Assetbundle.load/unload vs. Resources.load
- JAVA通过Session和Cookie实现网站自动登录代码
- "Java中可复用事件处理的设计与实现"代码
- Java Web 模板代码生成器的设计与实现
- Load and Unload - quoted from lostall
- Load/Unload XML data in DB2
- LKM -- load and unload process // My presentation
- vue项目构建-打包-发布--简易demo
- Spring Boot内嵌数据库的使用（H2）
- caffe 中 Batch Size、Iterations、epoch 的理解、设置
- Flyerinternational带你了解外汇！
- greenplum 权限说明
- 通过gpfdist unload 与load java 代码设计和实现
- mongodb 安装 用户权限分配
- Exception-try cath -if else使用
- Httpclient实现RPC
- 按非递减顺序合并两个单调递增集合
- 初识HID driver module
- R语言处理QQ群消息案例实现
- 相遇不晚，锐捷你好
- 开发中总结
