Hologres:Use the COPY statement to export data to an on-premises file

Limits

For more information about the COPY statement, see COPY.

Operation platform

You must execute the statements described in this topic in the PostgreSQL client. For more information, see PostgreSQL client.

Syntax

You can use the COPY FROM statement to import data from stdin of a client to Hologres and the COPY TO statement to export Hologres data to a client.

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

• Use the COPY statement to import data from a client

  • Import data from stdin to Hologres by executing the following statements:

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

    Note

    A PostgreSQL client allows you to import data from stdin. DataStudio and HoloWeb do not allow you to use command lines to import data from stdin to Hologres.

  • Import a CSV file from stdin to Hologres by executing the following statements:

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

    Note

    A PostgreSQL client allows you to import data from stdin. DataStudio and HoloWeb do not allow you to use command lines to import a CSV file from stdin to Hologres.

  • Import an on-premises file to Hologres by executing the following statement:

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

    Note

    A PostgreSQL client allows you to import data from stdin. DataStudio and HoloWeb do not allow you to use command lines to import an on-premises file from stdin to Hologres. If you use a PostgreSQL client, you can import data only from stdin. Therefore, you must convert the format of the data in the file to be imported to the standard input format.

    The following table describes the parameters in the statement.

    Parameter	Description
    username	Alibaba Cloud account: the AccessKey ID of your Alibaba Cloud account. You can obtain the AccessKey ID from the AccessKey page. We recommend that you configure environment variables and obtain the AccessKey ID and AccessKey secret from the environment variables. This helps reduce the leak risk. Custom account: the username of the custom account. Example: BASIC$abc.
    port	The public port number of the Hologres instance. Example: 80.
    endpoint	The public endpoint of the Hologres instance. Example: xxx-cn-hangzhou.hologres.aliyuncs.com.
    databasename	The name of the Hologres database. For more information, see Create a database. Example: mydb.
    table	The name of the Hologres table to which you want to import data.
    filename	The path of the local file that you want to import to Hologres. Example: D:\tmp\copy_test.csv.

    The following example shows how to execute a statement to import an on-premises file from stdin to Hologres:

    • Execute the statement to import the on-premises file named copy_test from stdin to Hologres. The imported standard file includes the following content:

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

    • After the statement is executed, go back to the PostgreSQL client. Then, you can query newly imported data, as shown in the following figure.

• Use the COPY statement to export data to a client

  • Execute the \copy statement to export Hologres data to an on-premises file.

    Note

    You can use this method to export Hologres data only to on-premises files on PostgreSQL clients.

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

  • Export Hologres data to an on-premises file.

    Note

    You can use this method to export Hologres data only to on-premises files on PostgreSQL clients.

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

• Use CopyManager to import and export data

  • Use CopyManager to import a file from a Java Database Connectivity (JDBC) client to Hologres by executing the following statements:

    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", "******");// The AccessKey ID of the current account. We recommend that you configure environment variables and obtain the AccessKey ID from the environment variables. This helps prevent information leaks. 
        //set db password
            props.setProperty("password", "******");// The AccessKey secret of the current account. We recommend that you configure environment variables and obtain the AccessKey secret from the environment variables. This helps prevent information leaks. 
            return DriverManager.getConnection(url, props);
        }
    
        /**
         * Import the file to 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 a file from Hologres to a JDBC client by executing the following statements:

    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", "******");// The AccessKey ID of the current account. We recommend that you configure environment variables and obtain the AccessKey ID from the environment variables. This helps prevent information leaks. 
        //set db password
            props.setProperty("password", "******");// The AccessKey secret of the current account. We recommend that you configure environment variables and obtain the AccessKey secret from the environment variables. This helps prevent information leaks. 
            return DriverManager.getConnection(url, props);
        }
    
        /**
         * Export the data to a file on the client. 
         *
         * @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;
        }
    }

Import data in the HoloWeb console

You can use HoloWeb to upload on-premises files to Hologres with a few clicks in a visualized manner.

1. Log on to the HoloWeb console. For more information, see Connect to HoloWeb and perform queries.

2. In the HoloWeb console, click Data Solutions in the top navigation bar.

3. In the left-side navigation pane, click Import On-premises File. On the page that appears, click New Data Import.

4. In the Import On-premises File dialog box, configure the parameters in the Select Destination Table step.

   Parameter	Description
   Job Name	The name of the import job.
   Instance Name	The name of the connected instance.
   Destination Database	The name of the database to which the destination table belongs.
   Destination Schema	The name of the schema to which the destination table belongs. If you have not created a schema, select the default schema public. If you have created a custom schema, you can select the created schema.
   Destination Table	The name of the destination table to which you want to import the data of the on-premises file. Before you import data from an on-premises file, you must create a table in the destination database to store the data of the on-premises file.

5. Click Next and configure the parameters in the Upload File step.

   Parameter	Description
   Select File	The on-premises file to be uploaded. Only .txt, .csv, and .log files are supported. Note The on-premises file and the destination table must contain the same number of columns. The columns in the on-premises file must be in the same order as the columns defined in the destination table.
   Delimiter	The delimiter that is used to separate fields in the on-premises file. Valid values: Comma (,). Tab Semicolon (;) Space | # & You can also specify a custom delimiter.
   Character Encoding	GBK UTF-8 CP936 ISO-8859
   First Line as Header	Specifies whether to use the first row as the table header.

6. Click Next. In the Import Overview step, click Execution to import the on-premises file.

   In the Import Overview step, you can also view the task information, such as the schema, database, and table to which the on-premises file is to be imported.