Hologres:Export data to a local file using the COPY command

Import inline data with STDIN

Use COPY FROM STDIN to load data entered directly in the psql session. Terminate the input with \. on its own line.

-- Create the table
CREATE TABLE copy_test (
  id    int,
  age   int,
  name  text
);

-- Import inline data
COPY copy_test FROM STDIN WITH DELIMITER AS ',' NULL AS '';
53444,24,wangming
55444,38,ligang
55444,38,luyong
\.

-- Verify the import
SELECT * FROM copy_test;

To import a CSV file in the same way:

-- Create the table
CREATE TABLE partsupp (
  ps_partkey     integer not null,
  ps_suppkey     integer not null,
  ps_availqty    integer not null,
  ps_supplycost  float   not null,
  ps_comment     text    not null
);

-- Import a CSV file with pipe delimiter
COPY partsupp FROM STDIN WITH DELIMITER '|' CSV;
1|2|3325|771.64|final theodolites
1|25002|8076|993.49|ven ideas
\.

-- Verify the import
SELECT * FROM partsupp;

COPY FROM STDIN is supported only in the psql client. DataStudio and HoloWeb do not support command-line data import.

Import a local file from the terminal

To load a local file, pipe it to Hologres using the psql -c flag:

psql -U <username> -p <port> -h <endpoint> -d <databasename> \
  -c "COPY <table> FROM STDIN WITH DELIMITER '|' CSV;" < <filename>

The following example imports a file named copy_test into Hologres:

The file contains:

01,01,name1
02,01,name2
03,01,name3
04,01,name4

After the import completes, query the table in the psql client to verify the data:

Export with \copy in psql

The \copy meta-command exports query results directly to a local file. This is the simplest export method and works entirely within the psql session.

-- Create and populate the table
CREATE TABLE copy_to_local (
  id    int,
  age   int,
  name  text
);

INSERT INTO copy_to_local VALUES
  (1, 1, 'a'),
  (1, 2, 'b'),
  (1, 3, 'c'),
  (1, 4, 'd');

-- Export to a local file
\copy (SELECT * FROM copy_to_local) TO '/root/localfile.txt';

\copy is supported only in the psql client.

Export to a local file from the terminal

To export data from the terminal and save it to a local file, redirect STDOUT to a file:

psql -U <username> -p <port> -h <endpoint> -d <databasename> \
  -c "COPY (SELECT * FROM <tablename>) TO STDOUT WITH DELIMITER '|' CSV;" > <filename>

Replace <username>, <port>, <endpoint>, <databasename>, and <filename> with your actual values (see the parameter table in Import a local file from the terminal).

Import a file using CopyManager

package com.aliyun.hologram.test.jdbc;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;

public class jdbcCopyFile {

    public static void main(String args[]) throws Exception {
        System.out.println(copyFromFile(getConnection(), "/Users/feng/Workspace/region.tbl", "region"));
    }

    public static Connection getConnection() throws Exception {
        Class.forName("org.postgresql.Driver");
        String url = "jdbc:postgresql://endpoint:port/dbname";
        Properties props = new Properties();
        // AccessKey ID of your current account. Use environment variables to avoid exposing credentials.
        props.setProperty("user", "******");
        // AccessKey secret of your current account. Use environment variables to avoid exposing credentials.
        props.setProperty("password", "******");
        return DriverManager.getConnection(url, props);
    }

    /**
     * Imports a local file into a Hologres table.
     *
     * @param connection  Active database connection
     * @param filePath    Path to the local file
     * @param tableName   Target table name
     * @return Number of rows imported
     */
    public static long copyFromFile(Connection connection, String filePath, String tableName)
            throws SQLException, IOException {
        long count = 0;
        FileInputStream fileInputStream = null;

        try {
            CopyManager copyManager = new CopyManager((BaseConnection) connection);
            fileInputStream = new FileInputStream(filePath);
            count = copyManager.copyIn("COPY " + tableName + " FROM STDIN DELIMITER '|' CSV", fileInputStream);
        } finally {
            if (fileInputStream != null) {
                try {
                    fileInputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return count;
    }
}

Export data using CopyManager

import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class copy_to_local_file {

    public static void main(String args[]) throws Exception {
        System.out.println(copyToFile(getConnection(), "/Users/feng/Workspace/region.tbl", "SELECT * FROM region"));
    }

    public static Connection getConnection() throws Exception {
        Class.forName("org.postgresql.Driver");
        String url = "jdbc:postgresql://endpoint:port/dbname";
        Properties props = new Properties();
        // AccessKey ID of your current account. Use environment variables to avoid exposing credentials.
        props.setProperty("user", "******");
        // AccessKey secret of your current account. Use environment variables to avoid exposing credentials.
        props.setProperty("password", "******");
        return DriverManager.getConnection(url, props);
    }

    /**
     * Exports Hologres data to a local file.
     *
     * @param connection  Active database connection
     * @param filePath    Path to the output file
     * @param SQL_Query   SELECT query to export
     * @return Path of the output file
     */
    public static String copyToFile(Connection connection, String filePath, String SQL_Query)
            throws SQLException, IOException {

        FileOutputStream fileOutputStream = null;

        try {
            CopyManager copyManager = new CopyManager((BaseConnection) connection);
            fileOutputStream = new FileOutputStream(filePath);
            copyManager.copyOut("COPY (" + SQL_Query + ") TO STDOUT DELIMITER '|' CSV", fileOutputStream);
        } finally {
            if (fileOutputStream != null) {
                try {
                    fileOutputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return filePath;
    }
}