文档

使用COPY命令导入或导出本地数据

更新时间:

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

使用限制

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

  • 当前COPY命令支持的数据类型与Hologres引擎支持的数据类型一致,详情参见数据类型汇总

  • 如果导入的是分区表数据,则Hologres只支持导入数据至分区表子表,不支持导入数据至分区表父表。

  • Hologres仅支持使用COPY FROM STDIN命令导入数据和COPY ( query ) TO STDOUT命令导出数据。

  • 在v1.1.43+版本中,当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 [, ...] )
    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导入数据,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(标准输入)方式导入数据,需要将文件数据转换为标准输入格式。

      参数说明:

      参数描述
      username
      • 阿里云账号:当前阿里云账号的AccessKey ID。您可以单击AccessKey 管理,获取AccessKey ID。

        建议使用环境变量的方式调用用户名和密码,降低密码泄露风险。

      • 自定义账号:自定义账号的用户名,例如BASIC$abc。
      portHologres实例的公共网络端口。

      示例取值80

      endpointHologres实例的公共网络地址。

      示例取值xxx-cn-hangzhou.hologres.aliyuncs.com

      databasenameHologres的数据库名称。

      详情请参见创建数据库

      示例取值mydb

      tableHologres数据库中待导入数据的表名。
      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支持可视化一键上传本地文件,具体操作如下。

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

  • 本页导读 (1)
文档反馈