Hologres从V1.1版本开始,与OSS深度打通,支持将Hologres表中数据导出至OSS,快速实现Hologres数据统一存储,进一步降低存储成本。本文以一个示例为您介绍如何将Hologres数据导出至OSS。

前提条件

使用限制

  • 仅Hologres V1.1及以上版本支持导出数据至OSS,如果您的Hologres版本较低,请提交工单进行升级。
  • Hologres支持将数据类型为BOOLEAN、INTEGER、BIGINT、FLOAT、DOUBLE PRECISION、TEXT的数据导出至OSS,其他数据类型暂不支持。
  • Hologres数据导出至OSS时不支持执行INSERT ON CONFLICTUPDATEDELETE命令。
  • Hologres数据导出至OSS时,用于给OSS写入数据的SQL语句不支持事务处理,但支持原子性。

操作步骤

将Hologres数据导出至OSS的完整步骤如下。

  1. 创建extension
    Hologres数据导出至OSS是扩展功能,需要Superuser在数据库中执行以下语句创建extension。创建extension需要实例的Superuser执行,该操作针对整个数据库生效,一个数据库只需执行一次。
    CREATE EXTENSION oss_fdw;
  2. 创建server
    创建extension后,执行以下语句创建server,用于连接Hologres与OSS。
    说明 同一个数据库,可以创建多个server。
    • 语法示例
      CREATE SERVER IF NOT EXISTS <server_name> FOREIGN DATA WRAPPER oss_fdw OPTIONS (endpoint 'https://<oss_internal>');
    • 参数说明
      参数 说明 示例
      server_name 自定义的server名称。 oss_server
      endpoint OSS的endpoint需要以http:// 或者https://开头,并且使用OSS的经典网络(内网)。更多关于OSS的endpoint说明,请参见获取OSS经典网络(内网)地址 http://oss-cn-shanghai-internal.aliyuncs.com
  3. 创建用户映射
    创建完server之后,使用如下语句创建用户映射,用于查询数据,需要保证创建的用户映射有对应的原数据查询权限。
    说明 同一个数据库,可以创建多个用户映射。
    • 语法示例
      CREATE USER MAPPING FOR CURRENT_USER SERVER <server_name> 
      OPTIONS (
        access_id '<access_id>', 
        access_key '<access_key>'
      );
    • 参数说明

      access_idaccess_key两个参数,分别代表当前访问账号的AccessKey ID和AccessKey Secret,您可以单击用户中心,获取AccessKey ID和AccessKey Secret。

  4. 创建外部表
    创建完用户映射后,使用如下语句创建外部表,通过外部表将数据导出至OSS中。
    • 语法示例
      CREATE FOREIGN TABLE IF NOT EXISTS <foreign_table_name>(
        <col_name> <type>,
        ......
      ) SERVER <server_name> 
      OPTIONS(
        dir './<dir_url>/', 
        format '<format_type>', 
       delimiter '<delimiter_type>',
        line_delimiter <line_delimiter>, 
        null_indicator <null_indicator>'
      );
    • 参数说明
      参数 说明 示例
      foreign_table_name 创建的外部表名称,若是需要指定schema,需要在表名前加schema名,例如schema.table lineitem
      col_name 表中的数据列名称。 l_orderkey
      type 列的数据类型。 int8
      server_name 创建的server名称。 oss_server
      dir OSS表的存储URL,需要指定到目录名称。您需要注意如下事项:
      • 不支持指定到具体文件,只支持到目录。
      • 目录名称不能指定为./oss-holotmp/holotest/region_zlib_dict.text。
      dir_url:bucket名称+目录路径。例如:./holobeijing-test/holo-beijing-1/dir_url
      format 当前OSS存储的表的格式,当前仅支持text类型。 text
      delimiter TEXT类型的分隔符。 半角逗号(,)
      line_delimiter 指定文件的行终止符,配置时需要用E避免转义带来的问题。 E'\n'
      null_indicator 对NULL字段的处理,配置时需要用E避免转义带来的问题。 E'\\N'
  5. 导出数据至OSS
    创建外部表成功后,使用如下语句您就可以将Hologres内部表中数据导出至指定OSS目录下。您可以根据业务需求选择Hologres内部表全部字段导出,或导出部分字段。
    • 语法示例
      • 导出全部字段数据
        insert into <foreign_table_name> select * from <holo_table_name>;
      • 导出部分字段数据
        insert into <foreign_table_name>(<col_name>,......) select <col_name>,...... from <holo_table_name>;
    • 参数说明
      参数 说明
      foreign_table_name 创建的外部表名称。
      holo_table_name 导出数据的Hologres内部表名称。
      col_name 表中的数据列名称。
  6. 查看导出结果
    导出成功后,您可登录OSS管理控制台,在对应OSS存储空间的目录下,查看到导出的数据文件。

使用示例

在使用示例之前,需要准备好Hologres内部表及其数据和OSS如下。
  • Hologres的内部表DDL如下。
    BEGIN;
    CREATE TABLE public.holo_lineitem_100g(
     "l_orderkey" int8 NOT NULL,
     "l_linenumber" int8 NOT NULL,
     "l_suppkey" int8 NOT NULL,
     "l_partkey" int8 NOT NULL,
     "l_quantity" int8 NOT NULL,
     "l_extendedprice" int8 NOT NULL,
     "l_discount" int8 NOT NULL,
     "l_tax" int8 NOT NULL,
     "l_returnflag" text NOT NULL,
     "l_linestatus" text NOT NULL
    );
    COMMIT;
  • 为Hologres内部表插入测试数据如下。
    INSERT INTO public.holo_lineitem_100g VALUES ('8195','1333365','33366','2','14','19576','23','22','RAIL','ic excuses? express de');
    INSERT INTO public.holo_lineitem_100g VALUES ('8195','1855029','5066','3','39','38373','24','25','SHIP','requests cajole fluffily toward the');
  • OSS的相关信息如下。
    参数 内容
    region 华北2(北京)
    endpoint https://oss-cn-beijing-internal.aliyuncs.com
    说明 获取OSS的endpoint请参见获取OSS经典网络(内网)地址
    bucket名 holobeijing
    目录名 holo-beijing-1
将Hologres内部表数据导出至OSS的完整示例语句如下。
CREATE EXTENSION oss_fdw;

CREATE SERVER IF NOT EXISTS oss_server_beijing FOREIGN DATA WRAPPER oss_fdw
OPTIONS(endpoint 'https://oss-cn-beijing-internal.aliyuncs.com');

CREATE USER MAPPING FOR CURRENT_USER SERVER oss_server_beijing
OPTIONS (access_id 'LTAI5XXX', access_key 'TrjgMHYYY');

BEGIN;
CREATE FOREIGN TABLE public.oss_lineitem_100g(
 "l_orderkey" int8 NOT NULL,
 "l_linenumber" int8 NOT NULL,
 "l_suppkey" int8 NOT NULL,
 "l_partkey" int8 NOT NULL,
 "l_quantity" int8 NOT NULL,
 "l_extendedprice" int8 NOT NULL,
 "l_discount" int8 NOT NULL,
 "l_tax" int8 NOT NULL,
 "l_returnflag" text NOT NULL,
 "l_linestatus" text NOT NULL
)
SERVER oss_server_beijing 
OPTIONS (
  dir './holobeijing-test/holo-beijing-1/', 
  format 'text', 
  delimiter ',', 
  line_delimiter E'\n', 
  null_indicator E'\\N');
COMMIT;

INSERT INTO public.oss_lineitem_100g SELECT * FROM public.holo_lineitem_100g;