使用COPY命令匯出本機資料 - Hologres

本文為您介紹如何使用COPY命令匯入本地的資料至Hologres或從Hologres中匯出資料至本地。

使用限制

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

當前COPY命令支援的資料類型與Hologres引擎支援的資料類型一致，詳情參見資料類型匯總。
如果匯入的是分區表資料，則Hologres只支援匯入資料至分區表子表，不支援匯入資料至分區表父表。
Hologres僅支援使用COPY FROM STDIN命令匯入資料和COPY ( query ) TO STDOUT命令匯出資料。
在v1.1.43+版本中，當COPY FROM STDIN時，支援表中有DEFAULT關鍵字以及serial類型欄位，早期版本不支援。
不支援僅向表中的某一列導資料。

更多關於COPY命令的用法請參見COPY指令。

操作入口

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

命令格式

COPY FROM命令用於從用戶端的標準輸入匯入資料至Hologres；COPY TO 命令用於將Hologres資料匯出至本地檔案。

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'

參數說明

參數說明如下表所示。

參數	描述
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編碼。預設使用當前的用戶端編碼。

使用樣本

使用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匯入資料，DataStudio及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匯入資料，DataStudio及HoloWeb暫不支援使用命令列方式通過STDIN匯入CSV格式的檔案。

匯入本地檔案至Hologres，命令如下。

psql -U <username> -p <port> -h <endpoint> -d <databasename> -c "COPY <table> from stdin with delimiter '|' csv;" <<filename>;

說明

psql用戶端支援使用STDIN匯入資料，DataStudio及HoloWeb暫不支援使用命令列方式通過STDIN匯入本地檔案。由於psql用戶端僅支援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`。

如下樣本將指導您在終端執行命令匯入本地檔案至Hologres。

輸入命令匯入本地檔案copy_test至Hologres。
其中，插入的標準檔案內容如下：
```
01,01,name1
02,01,name2
03,01,name3
04,01,name4
```
執行完成後，回到psql用戶端可以查詢新插入的資料，如下圖所示。

使用COPY命令匯出資料至本地

使用\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';

匯入Hologres資料至本地檔案。

說明

僅支援psql用戶端使用該方式匯出資料。

psql -U <username> -p <port> -h <endpoint> -d <databasename> -c "COPY (select * from <tablename>) to stdout with delimiter '|' csv;" ><filename>;

使用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", "******");//當前帳號的AccessKey ID，建議通過環境變數調用，降低密碼泄露風險。
    //set db password
        props.setProperty("password", "******");//當前帳號的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;
    }
}

使用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", "******");//當前帳號的AccessKey ID，建議通過環境變數調用，降低密碼泄露風險。
    //set db password
        props.setProperty("password", "******");//當前帳號的AccessKey SECRET，建議通過環境變數調用，降低密碼泄露風險。
        return DriverManager.getConnection(url, props);
    }

    /**
     * 匯出資料庫資料至用戶端檔案。
     *
     * @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可視化一鍵匯入

HoloWeb支援可視化一鍵上傳本地檔案，具體操作如下。

進入HoloWeb開發頁面，詳情請參見串連HoloWeb並執行查詢。
在HoloWeb開發頁面的頂部功能表列，單擊資料方案。
在左側導覽列，選擇一鍵本地檔案匯入 > 建立資料匯入。

配置一鍵本地檔案上傳對話方塊中選擇目標表頁面的各項參數。

參數	描述
作業名稱	建立的作業名稱。
執行個體名	選擇已登入的執行個體名稱。
目標庫	Hologres對應執行個體中已建立的資料庫名稱。
目標Schema	Hologres中已建立的Schema名稱。如果您沒有建立Schema，則只能選擇預設建立的public。如果有建立的Schema，您也可以選擇建立的Schema。
選擇要匯入的資料表	用於儲存本地檔案的表名稱。匯入本地檔案前，您需要在目標資料庫中建立一張用於儲存本地檔案的表。

單擊下一步，配置選擇資料來源表頁面的各項參數。

參數	描述
選擇檔案	需要上傳的本地檔案。僅支援上傳TXT、CSV和LOG類型的檔案。說明資料檔案列的順序必須和表定義的列順序一致，且列的個數相同。
選擇分隔字元	逗號 Tab 分號空格 \| # & 您也可以自訂分隔字元。
原始字元集	GBK UTF-8 CP936 ISO-8859
首行為標題	勾選則設定首行資料為標題。

單擊下一步，單擊匯入總覽頁面的執行，完成本地檔案一鍵匯入。
在匯入總覽頁面您可以查看本地檔案匯入的Schema、資料庫、表等資訊。