如何使用COPY命令进行数据导入与导出_实时数仓 Hologres(Hologres)-阿里云帮助中心

本文为您介绍在Hologres中如何使用COPY命令进行数据导入与导出。

使用限制

使用COPY命令的限制说明如下：

如果导入的是分区表数据，则Hologres只支持导入数据至分区表子表，不支持导入数据至分区表父表。
在v1.1.43+版本中，当使用COPY FROM STDIN命令时，支持表中有DEFAULT关键字以及serial类型字段，早期版本不支持。

命令介绍

COPY FROM命令用于从客户端的标准输入导入数据至Hologres；COPY TO命令用于导出Hologres数据。

说明

当前COPY命令支持的数据类型与Hologres引擎支持的数据类型一致，详情请参见数据类型汇总。
Hologres仅支持使用COPY FROM STDIN命令导入数据和COPY ( query ) TO STDOUT命令导出数据。
COPY命令保障数据导入与导出的原子性。FIXED COPY模式由于将表级锁优化为行级锁，因此不保障原子性，当业务出现脏数据时，仅会针对该条数据报错，其余数据可能部分写入或未写入。
Hologres V3.0版本前，COPY在元仓（hologres.hg_query_log）中只有COPY本身一条记录。V3.0版本起，COPY在元仓中会产生两条记录，包括COPY本身及COPY过程中产生的INSERT记录。二者可以通过Transaction ID实现关联，关联示例如下：
```
SELECT
    query_id,
    query,
    extended_info
FROM
    hologres.hg_query_log
WHERE
    extended_info ->> 'source_trx' = '<transaction_id>' -- 通过COPY本身记录的trans_id字段可以取到transaction id
ORDER BY
    query_start
;
```

命令格式

Hologres支持的COPY语句格式如下：

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'
    STREAM_MODE [ boolean]
    ON_CONFLICT 'none/ignore/update'

参数说明

参数	描述
table_name	Hologres接收数据的表名称。
query	查询语句。
STDIN	指定从客户端使用标准输入。
STDOUT	导出至指定客户端。
FORMAT	支持TEXT、CSV和BINARY格式。默认为TEXT格式。仅导出数据、FIXED COPY模式导入数据支持BINARY格式。
DELIMITER	指定的字段分隔符。文本格式默认为制表符，CSV格式默认为半角逗号（,）。例如`DELIMITER AS ','`。
NULL	指定表示一个空值的字符串。 TEXT格式：默认是`\N`。 CSV格式：默认是一个未加引用符的空串。 BINARY格式：不支持该选项。
HEADER	指定文件包含标题行，其中包含每一列的名称。说明仅CSV格式支持该选项。
QUOTE	指定一个数据值被引用时使用的引用字符，必须是一个单一的单字节字符。说明仅CSV格式支持该选项。默认为双引号。
ESCAPE	指定应该出现在一个匹配`QUOTE`值的数据字符之前的字符，必须是一个单一的单字节字符。说明仅CSV格式支持该选项。默认和`QUOTE`值相同。
FORCE_QUOTE	强制对指定列中的所有值使用引号（NULL除外）。说明仅`COPY TO`命令使用CSV格式时支持该选项。
FORCE_NOT_NULL	不将指定列的值与空字符串匹配。空值会被读取为零长度字符串，而非NULL。说明仅`COPY FROM`命令使用CSV格式时支持该选项。
ENCODING	指定文件按照encoding_name编码。默认使用当前的客户端编码。
STREAM_MODE	指定是否使用FIXED COPY模式，默认为FALSE。详情请参见使用COPY导入数据至Hologres中的FIXED COPY，取值如下： TRUE：使用FIXED COPY模式，仅支持导入数据至Hologres。 FALSE：不使用FIXED COPY模式。
ON_CONFLICT	主键冲突时的策略，仅STREAM_MODE为TRUE时生效，默认为NONE。 NONE：主键发生冲突时报错。 IGNORE：主键发生冲突时，对该数据执行跳过操作。 UPDATE：主键发生冲突时，对该数据执行更新操作。

使用COPY导入数据至Hologres

本地文件导入

Hologres支持使用COPY命令进行本地文件的导入与导出。更多关于COPY命令的原理与用法请参见PostgreSQL官网COPY。

使用示例：

使用STDIN导入数据至Hologres，命令如下。

--创建Hologres表。
CREATE TABLE copy_test (
  id    int,
  age   int,
  name  text
) ;

--导入数据至Hologres表。
COPY copy_test FROM STDIN WITH DELIMITER AS ',' NULL AS '';
53444,24,wangming
55444,38,ligang
55444,38,luyong
\.

--查询表中的数据。
SELECT * FROM copy_test;

说明

PSQL客户端仅支持使用STDIN（标准输入）方式导入数据，HoloWeb暂不支持使用命令行方式导入数据。

使用STDIN方式导入CSV格式的文件至Hologres，命令如下。

--创建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 );

--导入CSV格式的文件至Hologres表。
COPY partsupp FROM STDIN WITH DELIMITER '|' CSV;
1|2|3325|771.64|final theodolites
1|25002|8076|993.49|ven ideas
\.

--查询表中的数据。
SELECT * FROM partsupp;

说明

PSQL客户端支持使用STDIN导入数据，HoloWeb暂不支持使用命令行方式通过STDIN导入CSV格式的文件。

导入本地文件至Hologres，命令如下。

psql -U <username> -p <port> -h <endpoint> -d <databasename> -c "COPY <table> FROM STDIN WITH DELIMITER '|' CSV;" <<filename>;

说明

由于PSQL客户端支持使用STDIN（标准输入）方式导入数据，因此需要将文件数据转换为标准输入格式。HoloWeb暂不支持使用命令行方式通过STDIN导入本地文件。

参数说明：

参数	描述
username	阿里云账号：当前阿里云账号的AccessKey ID。您可以单击AccessKey 管理，获取AccessKey ID。建议使用环境变量的方式调用用户名和密码，降低密码泄露风险。自定义账号：自定义账号的用户名，例如BASIC$abc。
port	Hologres实例的公共网络端口。示例取值`80`。
endpoint	Hologres实例的公共网络地址。示例取值`xxx-cn-hangzhou.hologres.aliyuncs.com`。
databasename	Hologres的数据库名称。详情请参见创建数据库。示例取值`mydb`。
table	Hologres数据库中待导入数据的表名。
filename	需要导入的本地文件路径。示例取值`D:\tmp\copy_test.csv`。

如下示例将指导您在PSQL客户端执行命令导入本地文件至Hologres。

输入命令导入本地文件copy_test至Hologres。其中，插入的标准文件内容如下：
```
01,01,name1
02,01,name2
03,01,name3
04,01,name4
```
执行完成后，回到psql客户端可以查询新插入的数据，如下图所示。

CopyManager导入JDBC客户端文件

针对JDBC客户端文件，可以使用CopyManager工具进行数据复制。

CopyManager是JDBC针对PostgreSQL的COPY场景封装的API。Hologres兼容PostgreSQL协议，可以直接使用CopyManager进行JDBC客户端文件的导入与导出。更多关于CopyManager的信息请参见JDBC官网文档CopyManager。

使用示例：使用CopyManager导入JDBC客户端的文件至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", "AAA");//当前账号的AccessKey ID，建议通过环境变量调用，降低密码泄露风险。
    //set db password
        props.setProperty("password", "BBB");//当前账号的AccessKey SECRET，建议通过环境变量调用，降低密码泄露风险。
        return DriverManager.getConnection(url, props);
    }

    /**
     * 导入文件至数据库。
     *
     * @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;
    }
}

FIXED COPY

FIXED COPY是使用Fixed Plan优化COPY命令执行的新模式，是Hologres特有的执行引擎优化方式，仅支持导入数据至Hologres。更多关于Fixed Plan的原理请参见Fixed Plan加速SQL执行。

从Hologres V1.3.17版本起，支持FIXED COPY模式导入数据至Hologres。与未经Fixed Plan优化的COPY命令相比，有如下不同：

类别	FIXED COPY	COPY
锁力度	行级锁	表级锁
数据可见行	写入即可见	COPY结束后可见
性能	高	非常高
格式支持	TEXT、BINARY	TEXT
主键冲突策略	NONE（冲突则报错）、UPDATE、IGNORE	NONE（冲突则报错）

针对非全列COPY的场景，FIXED COPY的表现如下：

如果COPY写入的列不是全列，则为局部更新，表现如下：

CREATE TABLE t0 (id int NOT NULL, name text, age int, primary key(id));

COPY t0(id, name) FROM STDIN
WITH (
  STREAM_MODE TRUE,
  ON_CONFLICT UPDATE);

-- 上述COPY等价与如下INSERT INTO
INSERT INTO t0(id, name) VALUES(?,?)
ON CONFLICT(id) DO UPDATE SET
id = excluded.id, name = excluded.name;

如果COPY写入的列不是全列，且未参与写入的列包含default value，表现如下：

CREATE TABLE t0 (id int not null, name text, age int DEFAULT 0, primary key(id));

COPY t0(id, name) FROM STDIN
WITH (
  STREAM_MODE TRUE,
  ON_CONFLICT UPDATE);

-- 上述COPY等价与如下INSERT INTO
-- 若id数据不存在，age列赋值default value；
-- 若id数据已存在，age列不更新
INSERT INTO t0(id, name, age) VALUES(?, ?, default)
ON CONFLICT(id) DO UPDATE SET
id = excluded.id, name = excluded.name;

使用COPY从Hologres中导出数据

导出至本地文件

使用示例：

使用\copy导出Hologres的数据至本地文件。

说明

仅支持PSQL客户端使用该方式导出数据。

-- 建表
CREATE  TABLE copy_to_local (
  id    int,
  age   int,
  name  text
) ;

-- 写入数据
INSERT INTO copy_to_local VALUES
(1,1,'a'),
(1,2,'b'),
(1,3,'c'),
(1,4,'d');

-- 查数据
SELECT * FROM copy_to_local;

-- 导出数据至本地文件
\COPY (SELECT * FROM copy_to_local) TO '/root/localfile.txt';

使用STDOUT导出Hologres数据至本地文件。

说明

仅支持PSQL客户端使用该方式导出数据。

psql -U <username> -p <port> -h <endpoint> -d <databasename> -c "COPY (SELECT * FROM <tablename>) TO STDOUT WITH DELIMITER '|' CSV;" ><filename>;

导出至OSS

阿里云对象存储（Object Storage Service，简称OSS）是阿里云提供的安全、低成本及高可靠的云存储服务。Hologres支持通过以COPY命令语句的方式将查询的数据导出到指定的OSS。

使用限制
- 仅当前Hologres实例的Superuser或拥有pg_execute_server_program权限的用户，才可以使用hg_dump_to_oss导出Hologres的数据至OSS。Superuser可以授予其他用户pg_execute_server_program权限，命令如下。
```
--DB开启简单权限模型，执行以下语句
CALL spm_grant('pg_execute_server_program','云账号ID/云邮箱/RAM账号');

--DB使用的是专家权限模型，执行以下语句
GRANT pg_execute_server_program TO 云账号ID/云邮箱/RAM账号;
```
- 单次导入至OSS的数据量不能超过5GB。

命令介绍

COPY TO命令：
```
COPY ( query ) TO { PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]
```
其中，PROGRAM指一个需要执行的命令，输出会写入到该命令的标准输入。其余参数说明请参见上文中的参数说明。

hg_dump_to_oss命令（需要与COPY TO命令组合使用）：

COPY (query) TO PROGRAM 'hg_dump_to_oss --AccessKeyId <accessid> --AccessKeySecret <accesskey> --Endpoint <ossendpoint> --BucketName <bucketname> --DirName <dirname> --FileName <filename> --BatchSize <xxx> ' (DELIMITER ',', HEADER true, FORMAT CSV);

重要

<dirname>前请不要添加斜杠（/）、反斜杠（\）等字符。

参数说明：

参数	描述	示例
query	输入的查询语句	`select * from dual;`
AccessKeyId	当前账号的AccessKey ID。您可以进入AccessKey管理页面获取AccessKey ID。建议您使用环境变量的方式调用用户名和密码，降低密码泄露风险。	无
AccessKeySecret	AccessKey ID对应的AccessKey Secret。建议您使用环境变量的方式调用用户名和密码，降低密码泄露风险。	无
Endpoint	OSS的经典网络访问域名。获取方式如下：您可以单击Bucket列表页面的目标Bucket名称，进入Bucket详情页查看。您可以根据实例所属地域从OSS地域和访问域名中获取。	`oss-cn-beijing-internal.aliyuncs.com`
BucketName	OSS对应的bucket名字。	dummy_bucket
DirName	OSS存放输出结果的目录。	`testdemo/`
FileName	（可选）OSS对应的文件名称。说明不支持文件名中包含;#`\|?~<()"$\{}[]&*\n\r。	file_name
BatchSize	每次执行`hg_dump_to_oss`的行数，默认为1000。	5000
DELIMITER	结果列之间的分隔符，默认为制表符（Tab-separated Values，简称TSV）。	`,`

使用示例

在Hologres中hg_dump_to_oss命令与COPY TO命令的使用示例如下。

-- 将Hologres内部表数据dump到指定OSS
COPY (SELECT * FROM holo_test LIMIT 2) TO PROGRAM 'hg_dump_to_oss --AccessKeyId <access id> --AccessKeySecret <access key> --Endpoint oss-cn-hangzhou-internal.aliyuncs.com --BucketName hologres-demo --DirName holotest/ --FileName file_name --BatchSize 3000' DELIMITER ',';

-- 将Hologres外部表数据dump到指定OSS
COPY (SELECT * FROM foreign_holo_test LIMIT 20) TO PROGRAM 'hg_dump_to_oss --AccessKeyId <access id> --AccessKeySecret <access key> --Endpoint oss-cn-hangzhou-internal.aliyuncs.com --BucketName hologres-demo --DirName holotest/ --FileName file_name --BatchSize 3000' (DELIMITER ',', HEADER true);

-- 跨region dump到指定OSS
COPY (SELECT * FROM holo_test_1 LIMIT 20) TO PROGRAM 'hg_dump_to_oss --AccessKeyId <access id> --AccessKeySecret <access key> --Endpoint oss-cn-beijing-internal.aliyuncs.com --BucketName hologres-demo --DirName holotest/ --FileName file_name --BatchSize 3000' (DELIMITER ',', HEADER true, FORMAT CSV);

说明

Hologres支持跨地域导出数据至指定的OSS。例如，可以导出杭州地域的实例数据至北京地域的OSS。

常见问题

常见的报错内容及解决方法如下：

报错信息	解决方法
`ERROR: syntax error at or near ")"LINE 1: COPY (select 1,2,3 from ) TO PROGRAM 'hg_dump_to_oss2 --Acce...`	输入的query有误，请检查对应的查询语句。
`DETAIL: child process exited with exit code 255`	选择的OSS网络类型有误。如果您使用的是公共云，请选择经典网络。
`DETAIL: command not found`	您需要配置DUMP TO OSS的program为hg_dump_to_oss，否则会出现该报错。
`ERROR: program "hg_dump_to_oss ..." failed DETAIL: child process exited with exit code 101`	输入的AccessKeyId不合法，请使用当前账号的AccessKey ID。
`ERROR: program "hg_dump_to_oss ..." failed DETAIL: child process exited with exit code 102`	输入的AccessKeySecret不合法，请使用当前账号的AccessKey Secret。
`ERROR: program "hg_dump_to_oss ..." failed DETAIL: child process exited with exit code 103`	输入的Endpoint不合法，请确认对应OSS经典网络的Endpoint。
`ERROR: program "hg_dump_to_oss ..." failed DETAIL: child process exited with exit code 104`	输入的BucketName不合法，请确认对应的Bucket名称。
`ERROR: program "hg_dump_to_oss ..." failed DETAIL: child process exited with exit code 105`	缺少参数，请对照参数说明，检查必选参数是否均已配置。
`ERROR: program "hg_dump_to_oss ..." failed DETAIL: child process exited with exit code 255`	一般情况下是由于holo server与指定的OSS网络不通导致该报错，可以更换OSS域名（例如：OSS网络类型选择经典网络）。更多关于OSS的域名信息，请参见OSS地域和访问域名。

CopyManager导出至JDBC客户端文件

使用示例：使用CopyManager导出Hologres的数据至JDBC客户端的文件，代码样例如下。

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");//当前账号的AccessKey ID，建议通过环境变量调用，降低密码泄露风险。
    //set db password
        props.setProperty("password", "BBB");//当前账号的AccessKey SECRET，建议通过环境变量调用，降低密码泄露风险。
        return DriverManager.getConnection(url, props);
    }

    /**
     * 导出数据至JDBC客户端文件。
     *
     * @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;
    }
}