使用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指令。

命令格式

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。说明仅`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、数据库、表等信息。