如何使用COPY命令進行資料匯入與匯出 - Hologres

本文為您介紹在Hologres中如何使用COPY命令進行資料匯入與匯出。

使用限制

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

如果匯入的是分區表資料，則Hologres只支援匯入資料至分區表子表，不支援匯入資料至分區表父表。
在v1.1.43+版本中，當使用COPY FROM STDIN命令時，支援表中有DEFAULT關鍵字以及serial類型欄位，早期版本不支援。

操作入口

您需要在PSQL用戶端中執行本文所述命令，詳情請參見PSQL用戶端。

命令介紹

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 字元串匹配。空值會被讀取為零長度字串，而非NULL。說明僅`COPY FROM`命令使用CSV格式時支援該選項。
ENCODING	指定檔案按照encoding_name編碼。預設使用當前的用戶端編碼。
STREAM_MODE	指定是否使用FIXED COPY模式，預設為FALSE。詳情請參見FIXED COPY，取值如下： TRUE：使用FIXED COPY模式，僅支援匯入資料至Hologres。 FALSE：不使用FIXED COPY模式。
ON_CONFLICT	主鍵衝突時的策略，取值如下：說明下述取值若不加單引號，大小寫均不影響；若加了單引號，則必須使用小寫形式，如`'none'`。 NONE：主鍵發生衝突時報錯。 IGNORE：主鍵發生衝突時，對該資料執行跳過操作。 UPDATE：主鍵發生衝突時，對該資料執行更新操作。說明 Hologres V3.0.4版本前，僅STREAM_MODE為TRUE時生效。 Hologres V3.0.4版本起，支援STREAM_MODE為FALSE時使用（需要開啟GUC參數`hg_experimental_copy_enable_on_conflict`），其中UPDATE僅支援匯入Hologres表的全部列。 Hologres V3.1.1版本起，當STREAM_MODE為FALSE時，UPDATE支援匯入Hologres表的部分列，即局部更新（GUC參數`hg_experimental_copy_enable_on_conflict`預設開啟）。

使用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 COPY模式與其他資料匯入模式的對比，請參見批量寫入模式對比。

針對非全列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（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;
    }
}