本文为您介绍如何使用COPY命令导入本地的数据至Hologres或从Hologres中导出数据至本地。

使用限制

使用COPY命令的限制说明如下:
  • 当前COPY命令支持的数据类型与Hologres引擎支持的数据类型一致,详情参见数据类型汇总
  • 如果导入的是分区表数据,则Hologres只支持导入数据至分区表子表,不支持导入数据至分区表父表。
  • Hologres仅支持使用COPY FROM STDIN命令导入数据和COPY ( query ) TO STDOUT命令导出数据。
  • 当COPY FROM STDIN时,暂不支持表中有DEFAULT关键字以及serial类型字段。
更多关于COPY命令的用法请参见PostgreSQL官网文档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 [, ...] )

参数说明

参数说明如下表所示。
参数 描述
table_name Hologres接收数据的表名称。
query 查询语句。
STDIN 指定从客户端使用标准输入。
STDOUT 导出至指定客户端。
FORMAT 支持TEXTCSV格式。

默认为TEXT格式。

DELIMITER 指定的字段分隔符。

文本格式默认为制表符,CSV格式默认为逗号。例如DELIMITER AS ','

使用示例

  • 使用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导入数据,HoloStudio及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导入数据,HoloStudio及HoloWeb暂不支持使用命令行方式通过STDIN导入CSV格式的文件。
    • 导入本地文件至Hologres,命令如下。
      psql -U <username> -p <port> -h <endpoint> -d <databasename> -c "COPY <table> from stdin with delimiter '|' csv;" <<filename>;
      说明 psql客户端支持使用STDIN导入数据,HoloStudio及HoloWeb暂不支持使用命令行方式通过STDIN导入本地文件。由于psql客户端仅支持STDIN(标准输入)方式导入数据,需要将文件数据转换为标准输入格式。
      如下示例将指导您在终端执行命令导入本地文件至Hologres。
      • 输入命令导入本地文件copy_test至Hologres。执行命令其中,插入的标准文件内容如下:
        01,01,name1
        02,01,name2
        03,01,name3
        04,01,name4
      • 执行完成后,回到psql客户端可以查询新插入的数据,如下图所示。查询结果
    • 使用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;
          }
      }
  • 使用COPY命令导出数据至本地
    • 使用\copy导出至本地文件。
      说明 仅支持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>;

HoloWeb可视化一键导入

HoloWeb支持可视化一键上传本地文件,具体操作如下。

  1. 进入HoloWeb开发页面,详情请参见连接HoloWeb
  2. 在HoloWeb开发页面的顶部菜单栏,单击数据方案
  3. 在左侧导航栏,选择一键本地文件导入 > 新建数据导入
  4. 配置一键本地文件上传对话框中选择目标表页面的各项参数。一键本地文件上传
    参数 描述
    作业名称 新建的作业名称。
    实例名 选择已登录的实例名称。
    目标库 Hologres对应实例中已创建的数据库名称。
    目标Schema Hologres中已创建的Schema名称。

    如果您没有新建Schema,则只能选择默认创建的public。如果有新建的Schema,您也可以选择新建的Schema。

    选择要导入的数据表 用于存储本地文件的表名称。

    导入本地文件前,您需要在目标数据库中创建一张用于存储本地文件的表。

  5. 单击下一步,配置选择数据源表页面的各项参数。
    参数 描述
    选择文件 需要上传的本地文件。

    仅支持上传TXT、CSV和LOG类型的文件。

    选择分隔符
    • 逗号
    • Tab
    • 分号
    • 空格
    • |
    • #
    • &

    您也可以自定义分隔符。

    原始字符集
    • GBK
    • UTF-8
    • CP936
    • ISO-8859
    首行为标题 勾选则设置首行数据为标题。
  6. 单击下一步,单击导入总览页面的执行,完成本地文件一键导入。

    导入总览页面您可以查看本地文件导入的Schema、数据库、表等信息。