greenplum版本
greenplum: 4.3.17.1
依赖引入
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.8.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.11</version>
</dependency>
gp导入导出工具类
package com.qingzhongli.gp;
import org.apache.commons.lang3.StringUtils;
import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.sql.DataSource;
import java.io.*;
import java.sql.Connection;
import java.sql.SQLException;
/**
* @author qingzhongli.com
*/
public class GpDataUtils {
private static Logger logger = LoggerFactory.getLogger(GpDataUtils.class);
/**
* @param dataSource
* @param sql
* @param delimiter
* @param filePath
* @param encode
* @param header
* @return
*/
public static long exportData(DataSource dataSource,
String sql,
String delimiter,
String filePath,
String encode,
boolean header) {
Connection con = null;
OutputStream out = null;
Writer writer = null;
try {
con = dataSource.getConnection();
CopyManager cm = new CopyManager(con.unwrap(BaseConnection.class));
StringBuffer sb = new StringBuffer();
sb.append("copy (");
sb.append(sql);
sb.append(" ) TO STDOUT ");
sb.append("WITH DELIMITER '");
sb.append(delimiter);
sb.append("'");
if (header) {
sb.append(" HEADER ");
}
String copySql = sb.toString();
logger.info("exportData data begin , sql is {}", copySql);
long startTime = System.currentTimeMillis();
File file = new File(filePath);
if (!file.getParentFile().exists()) {
file.getParentFile().mkdirs();
}
out = new FileOutputStream(file);
long handledRowCount = 0;
if (StringUtils.isNotEmpty(encode)) {
writer = new OutputStreamWriter(out, encode);
handledRowCount = cm.copyOut(copySql, writer);
} else {
handledRowCount = cm.copyOut(copySql, out);
}
long elapsedTime = System.currentTimeMillis() - startTime;
logger.info("exportData data end, sql is {}, elapsed time = {}", copySql, elapsedTime);
return handledRowCount;
} catch (Exception e) {
logger.error(e.getMessage(), e);
return 0L;
} finally {
if (out != null) {
try {
out.close();
} catch (IOException e) {
logger.error(e.getMessage(), e);
}
}
if (writer != null) {
try {
writer.close();
} catch (IOException e) {
logger.error(e.getMessage(), e);
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
logger.error(e.getMessage(), e);
}
}
}
}
/**
* @param dataSource
* @param table
* @param delimiter
* @param file
* @return
*/
public static long importData(DataSource dataSource,
String table,
String delimiter,
String file) {
Connection con = null;
InputStream in = null;
try {
logger.info("import data begin");
con = dataSource.getConnection();
CopyManager cm = new CopyManager(con.unwrap(BaseConnection.class));
StringBuffer sb = new StringBuffer();
sb.append("copy ");
sb.append(table);
sb.append(" from STDIN ");
sb.append("WITH DELIMITER '");
sb.append(delimiter);
sb.append("'");
String copySql = sb.toString();
logger.info("import data begin, sql is {}", copySql);
long startTime = System.currentTimeMillis();
in = new FileInputStream(file);
long handledRowCount = cm.copyIn(copySql, in);
long elapsedTime = System.currentTimeMillis() - startTime;
logger.info("import data end, sql is {}, elapsed time = {}", copySql, elapsedTime);
return handledRowCount;
} catch (Exception e) {
logger.error(e.getMessage(), e);
return 0L;
} finally {
if (in != null) {
try {
in.close();
} catch (IOException e) {
logger.error(e.getMessage(), e);
}
}
if (con != null) {
try {
con.close();
} catch (SQLException e) {
logger.error(e.getMessage(), e);
}
}
}
}
}
测试验证
测试数据准备（E:\temp\test.txt），如下：
1,zhangsan
2,lisi
建立测试表，建表语句如下：
create table test (
id int,
name text
)
测试类，如下：
package com.qingzhongli.gp;
import org.apache.commons.dbcp2.BasicDataSource;
/**
* @author qingzhongli.com
*/
public class GpTest {
public static void main(String[] args) {
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName("org.postgresql.Driver");
ds.setUrl("jdbc:postgresql://192.168.37.90:5432/liqingzhong?gssEncMode=disable");
ds.setUsername("lqz");
ds.setPassword("Test123!@#");
ds.setMaxIdle(2);
ds.setInitialSize(1);
GpDataUtils.importData(ds, "test", ",", "E:\\temp\\test.txt");
}
}
运行测试类main方法，验证数据是否导入，如下：
[gpadmin@database-gp ~]$ psql -d liqingzhong -U test -h 192.168.37.90 -p 5432
Password for user test:
psql (8.2.15)
Type "help" for help.
liqingzhong=> select * from test;
id | name
----+----------
2 | lisi
1 | zhangsan
(2 rows)
liqingzhong=>
根据以上输出，数据已正常导入greenplum。
