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
- 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 theCOPY 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.
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.
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
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: |
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.- 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.
- Execute the statement to import the on-premises file named copy_test from stdin to
Hologres.
- Import data from stdin to Hologres by executing the following statements:
- 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>;
- Execute the
- 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; } }
- Use CopyManager to import a file from a Java Database Connectivity (JDBC) client to
Hologres by executing the following statements:
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.
- Log on to the HoloWeb console. For more information, see HoloWeb quick start.
- In the HoloWeb console, click Data Solution in the top navigation bar.
- In the left-side navigation pane, click Import On-premises File. On the page that appears, click New data import.
- In the Import On-premises File dialog box, set the parameters in the Select Target Table step.
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.
- 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. - 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.