This topic describes how to use the COPY statement to import on-premises data to Hologres or export Hologres data to on-premises files.

Limits

When you use the COPY statement to import or export data, take note of the following items:
  • The COPY statement supports the data types supported by Hologres. For more information, see Data types.
  • To use the COPY statement to import data from a partitioned table to Hologres, you can import the data only to child partitioned tables rather than parent partitioned tables.
  • To import data to Hologres, you must execute the COPY FROM STDIN statement. To export data from Hologres, you must execute the COPY TO STDOUT statement.
  • In Hologres v1.1.43 and later, the COPY FROM STDIN statement supports tables that are created with the DEFAULT constraint and tables that contain columns of the SERIAL data type. The COPY FROM STDIN statement does not support these tables in earlier Hologres versions.
For more information about the COPY statement, see COPY in PostgreSQL documentation.

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.

Hologres allows you to execute the COPY statement in the following format:
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 [, ...] )

Parameters

The following table describes the parameter in the syntax.
Parameter Description
table_name The name of the Hologres table to which the data is imported.
query The query statement.
STDIN Imports data from stdin of the specified client.
STDOUT Exports data to the specified client.
FORMAT The type of the data to be imported or exported. Valid values: text and csv.

Default value: text.

DELIMITER The delimiter that is used to separate columns.

The default delimiter is a tab character in the text format or a comma (,) in the CSV format. Example: DELIMITER AS ','.

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. HoloStudio or HoloWeb does 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. HoloStudio or HoloWeb does 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. HoloStudio or HoloWeb does 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 example shows you 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. Execute the statementThe 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. Query result
  • 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", "AAA");// The AccessKey ID of your Alibaba Cloud account. 
          //set db password
              props.setProperty("password", "BBB");// The AccessKey secret of your Alibaba Cloud account. 
              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", "AAA");// The AccessKey ID of your Alibaba Cloud account. 
          //set db password
              props.setProperty("password", "BBB");// The AccessKey secret of your Alibaba Cloud account. 
              return DriverManager.getConnection(url, props);
          }
      
          /**
           * Export the file from the database. 
           *
           * @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 HoloWeb quick start.
  2. In the HoloWeb console, click Data Solution 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, set the parameters in the Select Target Table step. Import On-premises File
    Parameter Description
    Job Name The name of the import task.
    Instance Name The name of the connected instance.
    Target Database The name of the database to which the destination table belongs.
    Target Schema The name of the schema to which the destination table belongs.

    If you have not created a schema, select the default public schema. 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 Step and set the parameters in the Select 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 Step. 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.