Hologres:Use the COPY command to import and export local data

Limitations

For more information about the COPY command, see COPY.

Client environment

You must run the commands in this topic from a PSQL client. For details, see Connect to a Hologres instance by using a PSQL client.

Syntax

Use the COPY FROM command to import data from the client's standard input into Hologres. Use the COPY TO command to export Hologres data to a local file.

COPY table_name [ ( column_name [, ...] ) ]
    FROM STDIN
    [ [ WITH ] ( option [, ...] ) ]
COPY { ( query ) }
    TO STDOUT
    [ [ WITH ] ( option [, ...] ) ]
where option can be one of:
    FORMAT format_name
    DELIMITER 'delimiter_character'
    NULL 'null_string'
    HEADER [ boolean ]
    QUOTE 'quote_character'
    ESCAPE 'escape_character'
    FORCE_QUOTE { ( column_name [, ...] ) | * }
    FORCE_NOT_NULL ( column_name [, ...] )
    ENCODING 'encoding_name'

Parameters

Examples

• Import data using the COPY command

  • Import data from stdin to Hologres.

    -- Create a Hologres table.
    CREATE TABLE copy_test (
      id    int,
      age   int,
      name  text
    ) ;
    -- Import data into the Hologres table.
    COPY copy_test FROM STDIN WITH DELIMITER AS ',' NULL AS '';
    53444,24,wangming
    55444,38,ligang
    55444,38,luyong
    \.
    -- Query the data in the table.
    SELECT * FROM copy_test;

    Note

    The PSQL client supports importing data from stdin. DataStudio and HoloWeb do not support this method.

  • Import a CSV file from stdin to Hologres.

    -- Create a Hologres 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 into the Hologres table.
    COPY partsupp FROM STDIN WITH DELIMITER '|' CSV;  
    1|2|3325|771.64|final theodolites 
    1|25002|8076|993.49|ven ideas
    \.
    -- Query the data in the table.
    SELECT * FROM partsupp;

    Note

    The PSQL client supports importing data from standard input. DataStudio and HoloWeb do not support importing a CSV file through standard input from the command line.

  • Import a local file into Hologres.

    psql -U <username> -p <port> -h <endpoint> -d <databasename> -c "COPY <table> from stdin with delimiter '|' csv;" <<filename>;

    Note

    The PSQL client supports importing data from standard input. DataStudio and HoloWeb do not support importing a local file through standard input from the command line. Because the PSQL client imports data only through standard input, you must convert the file data into standard input format.

    Parameters:

    Parameter	Description	Example
    username	Alibaba Cloud account: AccessKey ID. Custom account: username (e.g., BASIC$abc). Store the AccessKey ID in an environment variable to avoid exposing it in commands.	—
    port	Public port of the Hologres instance.	80
    endpoint	Public endpoint of the Hologres instance.	xxx-cn-hangzhou.hologres.aliyuncs.com
    databasename	Name of the Hologres database.	mydb
    table	Name of the target table.	—
    filename	Path to the local file.	D:\tmp\copy_test.csv

    The following example shows how to run the command in a terminal to import a local file into Hologres.

    • Run the command to import the local file copy_test into Hologres.

      C:\Users\nan>psql -U LTxxx -p 80 -h hgrxxx.hologres.aliyuncs.com -d mydb -c "COPY copy_test from stdin with delimiter ',' ;" <D:\tmp\copy_test.csv
          LTAxxx
      COPY 3

      The content of the file is as follows:

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

    • After the command is complete, return to the PSQL client to query the newly imported data.

      mydb=# select * from copy_test;
       id | age | name
      ----+-----+-------
        1 |   1 | 01
        1 |   1 | 01
        1 |   1 | 01
        1 |   1 | 01
        1 |   1 | name1
        2 |   1 | name2
        3 |   1 | name3
        4 |   1 | name4

• Export data to a local file using the COPY command

  • Use the \copy meta-command to export data from Hologres to a local file.

    Note

    This method is supported only in the PSQL client.

    -- Create a table.
    CREATE  TABLE copy_to_local (
      id    int,
      age   int,
      name  text
    ) ;
    -- Insert data.
    INSERT INTO copy_to_local VALUES
    (1,1,'a'),
    (1,2,'b'),
    (1,3,'c'),
    (1,4,'d');
    -- Query data.
    select * from  copy_to_local;
    -- Export data to a local file.
    \copy (select * from copy_to_local) to '/root/localfile.txt';

  • Export Hologres data to a local file.

    Note

    This method is supported only in the PSQL client.

    psql -U <username> -p <port> -h <endpoint> -d <databasename> -c "COPY (select * from <tablename>) to stdout with delimiter '|' csv;" ><filename>;

• Import and export data using CopyManager

  • Use CopyManager in a JDBC client to import a file into Hologres.

    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();
        //set db user
            props.setProperty("user", "******");// Your AccessKey ID. Using an environment variable is recommended to avoid exposing credentials.
        //set db password
            props.setProperty("password", "******");// Your AccessKey Secret. Using an environment variable is recommended to avoid exposing credentials.
            return DriverManager.getConnection(url, props);
        }
        /**
         * Imports a file into the database.
         * 
         * @param connection
         * @param filePath
         * @param tableName
         * @return
         * @throws SQLException
         * @throws IOException
         */
        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;
        }
    }

  • Use CopyManager to export data from Hologres to a file on a JDBC client.

    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();
        //set db user
            props.setProperty("user", "******");// Your AccessKey ID. Using an environment variable is recommended to avoid exposing credentials.
        //set db password
            props.setProperty("password", "******");// Your AccessKey Secret. Using an environment variable is recommended to avoid exposing credentials.
            return DriverManager.getConnection(url, props);
        }
        /**
         * Exports database data to a client file.
         *
         * @param connection
         * @param filePath
         * @param SQL_Query
         * @return
         * @throws SQLException
         * @throws IOException
         */
        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;
        }
    }

Visual import with HoloWeb

HoloWeb supports one-click visual upload of local files. Follow these steps:

1. Connect to HoloWeb. For more information, see Connect to HoloWeb and run queries.

2. In the top menu bar of the HoloWeb development page, click Data Solutions.

3. In the left-side navigation pane, choose Import On-premises File > New Data Import.

4. In the Import On-premises File dialog box, follow the wizard through the Select Destination Table, Upload File, and Confirm Import Information steps. This feature supports files up to 100 MB; for larger files, use the COPY command in a PSQL client. On the Select Destination Table page, configure the following parameters:

   Parameter	Description
   Job Name	The name for the new import job.
   Instance Name	Select the name of the instance that you logged on to.
   Destination Database	The name of an existing database in the Hologres instance.
   Destination Schema	The name of an existing schema in Hologres. If you have not created a new schema, you can select only the default public schema. If you created a new schema, you can select it from the list.
   Select Table for Import	The name of the table that stores the local file. Before you import a local file, you must create a table in the destination database to store it.

5. Click Next and configure the parameters on the Upload File page.

   Parameter	Description
   Select File	The local file to upload. Only .txt, .csv, and .log files are supported. Note The data file's column order and count must match the table's definition.
   Select Delimiter	Comma Tab Semicolon Space | # & You can also specify a custom delimiter.
   Source character set	GBK UTF-8 CP936 ISO-8859
   First Line as Header	Select this option if the first row of your file is a header.

6. Click Next. On the Confirm Import Information page, click Upload to complete the import.

   On the Confirm Import Information page, you can review the import details, including the destination schema, database, and table.