编程知识 cdmana.com

Practice of importing greenplus database with Java

greenplum edition

greenplum: 4.3.17.1

Depend on the introduction of

<!-- 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 Import and export tool class

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);
                }
            }
        }
    }

}


Test verification

Test data preparation (E:\temp\test.txt), as follows :

1,zhangsan
2,lisi

Build a test table , Build the predicative sentence as follows :

create table test (
id int,
name text
)

Test class , as follows :

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");

    }
}

Run test class main Method , Verify that the data is imported , as follows :

[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=> 

According to the above output , Data has been imported normally greenplum.

Reference resources

版权声明
本文为[qingzhongli]所创,转载请带上原文链接,感谢

Scroll to Top