Greenplum——copy insert的Java工具类

CarsonBigData

已于 2023-04-20 11:18:56 修改

阅读量804

点赞数 2

CC 4.0 BY-SA版权

分类专栏：大数据文章标签： Greenplum copy in

于 2022-08-12 10:16:05 首次发布

本文链接：https://blog.youkuaiyun.com/Carson073/article/details/126298618

大数据专栏收录该内容

40 篇文章

订阅专栏

本文介绍如何使用Java程序实现Greenplum数据库的数据导出和导入操作，包括配置Maven依赖、设置数据源及执行SQL语句的具体步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>greenplum-test</artifactId>
    <version>1.0-SNAPSHOT</version>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>6</source>
                    <target>6</target>
                </configuration>
            </plugin>
        </plugins>
    </build>


    <dependencies>
        <dependency>
            <groupId>com.pivotal</groupId>
            <artifactId>greenplum-jdbc</artifactId>
            <version>5.1.4</version>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.23</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.7.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.11</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.83</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.8.0-beta4</version>
        </dependency>
    </dependencies>
</project>

2、GpCopyInDataUtils

package com.greenplum.test.util;

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;

public class GpCopyInDataUtils {

    private static Logger logger = LoggerFactory.getLogger(GpCopyInDataUtils.class);


    /**
     * copy in导出数据
     *
     * @param dataSource 数据源
     * @param querySql   查询sql
     * @param delimiter  列分隔符 例如 ,
     * @param filePath   导出文件路径
     * @param encode     编码 例如 UTF-8
     * @param header     是否需要表头
     * @return
     */
    public static long exportData(DataSource dataSource,
                                  String querySql,
                                  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(querySql);
            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);
                }
            }
        }
    }

    /**
     * copy in导入数据
     *
     * @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);
                }
            }
        }
    }

}

3、TestMain

package com.greenplum.test;

import com.greenplum.test.util.GpCopyInDataUtils;
import org.apache.commons.dbcp2.BasicDataSource;


public class TestMain {
    public static void main(String[] args) {
        BasicDataSource ds = new BasicDataSource();
        ds.setDriverClassName("org.postgresql.Driver");
        ds.setUrl("jdbc:postgresql://127.0.0.1:54432/pgbenchdb");
        ds.setUsername("gpadmin");
        ds.setPassword("changeme");
        ds.setMaxIdle(10);
        ds.setInitialSize(5);

        // 测试导出表数据到文件
        GpCopyInDataUtils.exportData(ds,
                "select * from tbl",
                ",",
                "/Users/chenweifeng/Downloads/greenplum-test/tmp/tbl.txt",
                "UTF-8",
                true);

        // 测试从文件导入数据到表
        GpCopyInDataUtils.importData(ds,
                "tbl",
                ",",
                "/Users/chenweifeng/Downloads/greenplum-test/tmp/tbl.txt");
    }
}