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

Limits

For more information about the COPY command, see COPY command.

Access method

You can run the commands described in this topic in the psql client. For more information, see psql client.

Command syntax

Use the COPY FROM command to import data from standard input on the client into Hologres. Use the COPY TO command to export data from Hologres 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'

Parameter descriptions

Examples

• Import local data using the COPY command

  • To import data into Hologres using STDIN, run the following command:

    -- 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 using STDIN. DataStudio and HoloWeb do not currently support command-line data import.

  • Import a CSV file into Hologres using STDIN.

    -- 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 the 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 using STDIN. DataStudio and HoloWeb do not currently support importing CSV files from STDIN on the command line.

  • To import a local file into Hologres, run the following command:

    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 using STDIN. DataStudio and HoloWeb do not currently support importing local files from STDIN on the command line. Because the psql client supports importing data only from STDIN (standard input), you must convert your file data to the standard input format.

    Parameter descriptions:

    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 run a command in your terminal to import a local file into Hologres.

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

      

      The standard input file contains the following data:

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

    • After the command runs, you can query the newly inserted data in the psql client, as shown in the following figure.

• Export data to a local file using the COPY command

  • You can use \copy to export Hologres data 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 using CopyManager

  • To import a file from a JDBC client into Hologres using CopyManager, run the following command:

    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", "******");// AccessKey ID of your current account. We recommend using environment variables to reduce the risk of password exposure.
        //set db password
            props.setProperty("password", "******");// AccessKey Secret of your current account. We recommend using environment variables to reduce the risk of password exposure.
            return DriverManager.getConnection(url, props);
        }
    
        /**
         * Import 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;
        }
    }

  • To export Hologres data to a file on the JDBC client using CopyManager, run the following command:

    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", "******");// AccessKey ID of your current account. We recommend using environment variables to reduce the risk of password exposure.
        //set db password
            props.setProperty("password", "******");// AccessKey Secret of your current account. We recommend using environment variables to reduce the risk of password exposure.
            return DriverManager.getConnection(url, props);
        }
    
        /**
         * Export 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;
        }
    }

HoloWeb visual one-click import

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

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

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

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

4. On the Select Destination Table page of the Import On-premises File dialog box, configure the parameters.

   Parameter	Description
   Job name	Name of the new job.
   Instance name	Select the name of the logged-in instance.
   Destination database	Name of the database created in the corresponding Hologres instance.
   Target Schema	Name of the schema created in Hologres. If you have not created a schema, you can only select the default public schema. If you have created schemas, you can also select a custom schema.
   Select the data table to import	Name of the table that stores the local file. Before importing a local file, create a table in the destination database to store the file.

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

   Parameter	Description
   Select file	Local file to upload. Only TXT, CSV, and LOG files are supported. Note The column order in the data file must match the column order defined in the table, and the number of columns must be the same.
   Select delimiter	Comma Tab Semicolon Space | # & You can also define a custom delimiter.
   Source character set	GBK UTF-8 CP936 ISO-8859
   First row as header	Select this option to treat the first row as a header.

6. Click Next. On the Confirm Import Information page, click Upload to complete the one-click import of the local file.

   On the Confirm Import Information page, you can view details about the data import, such as the destination schema, database, and table.