文档

Hologres外部表

更新时间:

当您需要访问存储在Hologres中的数据时,MaxCompute支持使用外部表访问Hologres数据源数据,无需导入数据至MaxCompute中,实现快速查询Hologres数据。本文为您介绍如何在外部表建表语句中指定Hologres数据源、STS认证信息或打开双签名开关、映射目标表、JDBC驱动信息,来创建Hologres外部表。

背景信息

Hologres是兼容PostgreSQL协议的实时交互式分析数据仓库,在底层与MaxCompute无缝连接。

您可以使用在MaxCompute上创建Hologres外部表的方式,基于PostgreSQL JDBC驱动及STS认证信息查询Hologres数据源的数据。该方式无冗余存储,无需导入导出数据,可实现快速获取查询结果。

前提条件

创建Hologres外部表前,请确认已经满足如下条件:

  • 已准备好Hologres数据库及目标表。

    创建Hologres数据库信息,请参见创建数据库

    创建Hologres表信息,请参见建表概述

    假设已准备好的Hologres实例信息如下:

    • Hologres数据库名称:mc_test

    • Hologres数据库的Schema:public

    • Hologres数据库经典网络连接地址:hgprecn-cn-oew210ut****-cn-hangzhou-internal.hologres.aliyuncs.com:80

    • Hologres表名称:holo。表数据如下:表数据

  • 已准备好待创建Hologres外部表的目标MaxCompute项目。

    创建MaxCompute项目信息,请参见创建MaxCompute项目

  • 已安装MaxCompute客户端。

    更多安装MaxCompute客户端操作,请参见安装并配置MaxCompute客户端

使用限制

Hologres外部表的使用限制如下:

  • MaxCompute不支持对创建的Hologres外部表执行更新(UPDATE)、删除(DELETE)操作。

  • Hologres的分区表和MaxCompute的分区表没有对应关系。Hologres外部表不支持分区。

  • 当您需要向Hologres外部表写入大量数据时,采用并行多进程写入方式,会小概率出现某个写入进程数据重写情况,导致数据重复。

  • MaxCompute里建的Hologres外部表,其中DECIMAL数据类型默认小数18位,不能修改,只能建成decimal(38,18)。如果小数位数比较少,可以在MaxCompute中建外部表时数据类型选择String,使用的时候再使用cast函数强制转换后使用。

  • MaxCompute创建的Hologres外部表,不支持Array,Map,Struct复杂数据类型。

  • 在MaxCompute创建Hologres外部表时,Hologres中有的JSON、JSONB、MONEY等数据类型,目前在MaxCompute没有对应的数据类型,暂时不支持。

  • Hologres外部表不支持cluster属性。

注意事项

Hologres外部表使用过程中需要注意:

  • 对于Hologres的父、子表,Hologres外部表中会指定表名,并执行SQL语句。父、子表都可以映射至Hologres外部表,但是父表只能读不能写。

  • 向Hologres外部表写入数据时,暂不支持Hologres的INSERT ON CONFLICT(UPSERT)机制。如果Hologres源表有主键,请避免写入的数据与Hologres源表中的数据产生主键唯一性冲突。

  • 建表时,表名和字段名大小写不敏感。在查询表或字段时,无需区分大小写,且不支持强制转换大小写。

创建Hologres外部表语法

在创建外部表时,您需要在建表DDL语句中指定StorageHandler,并配置STS认证信息(或打开双签名开关)、JDBC连接地址实现访问Hologres数据源。建表语句定义如下。

  • STS模式创建Hologres外部表。

    create external table [if not exists] <table_name>(
      <col1_name> <data_type>,
      <col2_name> <data_type>,
      ......
    )
    stored by '<com.aliyun.odps.jdbc.JdbcStorageHandler>'
    with serdeproperties (
      'odps.properties.rolearn'='<ram_arn>')
    location '<jdbc:postgresql://<endpoint>:<port>/<database>?ApplicationName=MaxCompute&[currentSchema=<schema>&][useSSL={true|false}&]table=<holo_table_name>/>' 
    tblproperties (
      'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver', 
      'odps.federation.jdbc.target.db.type'='holo',
      ['odps.federation.jdbc.colmapping'='<col1:column1,col2:column2,col3:column3,...>']
    );
  • 双签名模式创建Hologres外部表。

    双签名Hologres外部表支持相同RAM用户可以访问MaxCompute和Hologres对应有权限的表,不需要再手工授权。同时支持HologresIP白名单能力,方便您使用。

    --打开双签名开关
    set odps.sql.common.table.planner.ext.hive.bridge=true;
    --创建外部表
    create external table [if not exists] <table_name>(
      <col1_name> <data_type>,
      <col2_name> <data_type>,
      ......
    )
    stored by '<com.aliyun.odps.jdbc.JdbcStorageHandler>'
    location'<jdbc:postgresql://<endpoint>:<port>/<database>?ApplicationName=MaxCompute&[currentSchema=<schema>&][useSSL={true|false}&]table=<holo_table_name>/>' 
    tblproperties (
      'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver', 
      'odps.federation.jdbc.target.db.type'='holo',
      ['odps.federation.jdbc.colmapping'='<col1:column1,col2:column2,col3:column3,...>']
    );
  • odps.sql.common.table.planner.ext.hive.bridge:通过双签名模式创建Hologres外部表必填;通过STS模式创建Hologres外部表不填。双签名开关,值设置为true,表示打开双签名开关,通过双签名模式创建Hologres外部表。

  • if not exists:可选。如果不指定if not exists选项而存在同名表,会报错。如果指定if not exists,无论是否存在同名表,即使原表结构与要创建的目标表结构不一致,均返回成功。已存在的同名表的元数据信息不会被改动。

  • table_name:必填。在MaxCompute上创建的Hologres外部表的名称。

  • col_name:必填。Hologres外部表的列名称。

  • data_type:必填。Hologres外部表的列的数据类型。

  • stored by:必填。指定StorageHandler,定义了如何查询Hologres外部表。固定取值为com.aliyun.odps.jdbc.JdbcStorageHandler,使用JdbcStorageHandler连接方式。

  • ram_arn:通过STS模式创建Hologres外部表必填;通过双签名模式创建Hologres外部表不填。指定RAM角色的ARN信息,用于创建外部表时STS认证信息的填写。您可以在RAM访问控制页面,单击目标RAM角色名称后,在基本信息区域获取。

  • location:必填。Hologres实例的JDBC连接地址。其中:

    • endpoint:必填。Hologres实例的经典网络域名。获取方式,请参见实例配置

    • port:必填。Hologres实例的网络端口。获取方式,请参见实例配置获取endpoint和端口

    • database:必填。连接的Hologres数据库名称。更多Hologres数据库信息,请参见CREATE DATABASE

    • ApplicationName:必填。默认为MaxCompute,无需修改。

    • schema:可选。如果表名在Hologres数据库内是唯一的,或源表是默认Schema中的表,可以不配置该属性。更多Schema信息,请参见CREATE SCHEMA

    • holo_table_name:必填。Hologres源表名称。更多Hologres源表信息,请参见建表概述查看表

  • tblproperties

    • mcfed.mapreduce.jdbc.driver.class:必填。指定连接Hologres数据库的驱动程序。固定取值为org.postgresql.Driver

    • odps.federation.jdbc.target.db.type:必填。指定连接的数据库类型。固定取值为holo

    • odps.federation.jdbc.colmapping:可选。如果需要将指定数据源的部分列映射至Hologres外部表,需要配置该参数,指定Hologres源表的字段和Hologres外部表字段的映射关系。如果不配置该参数,按照源表字段顺序映射至Hologres外部表。如果Hologres里的字段名称是大写的话,需要为Hologres字段名称添加双引号("")。格式为:MaxCompute字段1 : "Hologres字段1" [,MaxCompute字段2 : "Hologres字段2" ,...]

创建Hologres外部表(STS模式)

通过STS模式创建Hologres外部表的步骤如下:

  1. 创建RAM角色

    创建RAM角色获取ARN信息,用于创建外部表时填写STS认证信息。

    1. 登录RAM访问控制创建RAM角色。

      创建RAM角色创建RAM角色的可信实体类型根据实际需求选择阿里云账号或者身份提供商。

    2. 修改信任策略配置内容。

      1. 角色页面,单击已创建完成的RAM角色名称。

      2. 单击信任策略页签。

      3. 信任策略页签,单击编辑信任策略

      4. 编辑信任策略区域,参照如下内容修改信任策略配置。

        修改信任策略配置内容与选择的可信实体类型相关。

        • 可信实体类型为阿里云账号:

          {
            "Statement": [
              {
                "Action": "sts:AssumeRole",
                "Effect": "Allow",
                "Principal": {
                  "RAM": [
                    "acs:ram::<UID>:root"
                  ]
                }
              },
              {
                "Action": "sts:AssumeRole",
                "Effect": "Allow",
                "Principal": {
                  "Service": [
                    "<UID>@odps.aliyuncs.com"
                  ]
                }
              }
            ],
            "Version": "1"
          }
        • 可信实体类型为身份提供商:

          {
          "Statement": [
                  {
                      "Action": "sts:AssumeRole",
                      "Condition": {
                          "StringEquals": {
                              "saml:recipient": "https://signin.aliyun.com/saml-role/sso"
                          }
                      },
                      "Effect": "Allow",
                      "Principal": {
                          "Federated": [
                              "acs:ram::<UID>:saml-provider/IDP"
                          ]
                      }
                  },
                  {
                      "Action": "sts:AssumeRole",
                      "Effect": "Allow",
                      "Principal": {
                          "Service": [
                              "<UID>@odps.aliyuncs.com"
                          ]
                      }
                  }
              ],
              "Version": "1"
          }
          说明

          <UID>即阿里云账号ID,可在用户信息页面获取。

      5. 单击确定

  2. 添加RAM角色至Hologres实例并授权

    RAM角色需要有Hologres实例的开发权限,才能在权限范围内使用Hologres。由于RAM角色默认没有Hologres管理控制台的查看和操作实例的权限,因此需要阿里云账号完成RAM相关权限授予才能进行后续操作。添加RAM角色至Hologres实例,您可以通过如下方式进行授权。

    • 通过Hologres管理控制台授权。

      1. 登录Hologres管理控制台

      2. 在左侧导航栏单击实例列表,单击需要授权的Hologres实例名称。

      3. 在实例详情页面,单击账号管理

      4. 用户管理页面单击新增用户添加RAM角色至Hologres实例。新增用户

      5. DB授权页签,为该RAM角色授予实例的开发权限。DB授权

    • 通过SQL方式授权。

      您可以通过SQL方式进行授权,授权SQL请参见Hologres权限模型概述

    • 若是通过RAM用户扮演RAM角色,RAM用户默认没有Hologres管理控制台的权限,需要阿里云账号给RAM用户在访问控制页面授予AliyunRAMReadOnlyAccess权限,否则RAM用户无法在Hologres管理控制台进行任何操作。详情请参见文档授予RAM用户权限添加权限

  3. 创建Hologres外部表

    完成上述步骤后,基于已准备好的数据信息,您即可登录MaxCompute客户端,结合创建Hologres外部表语法创建Hologres外部表。

    1. 安装并登录MaxCompute本地客户端,进入目标MaxCompute项目。

      进入目标项目命令信息,请参见项目空间操作

    2. 执行如下命令创建Hologres外部表。

      命令示例如下。

      create external table if not exists my_table_holo_jdbc
      (
       id bigint,
       name string
      )
      stored by 'com.aliyun.odps.jdbc.JdbcStorageHandler' 
      with serdeproperties (
        'odps.properties.rolearn'='acs:ram::139699392458****:role/aliyunodpsholorole')
      location 'jdbc:postgresql://hgprecn-cn-oew210ut****-cn-hangzhou-internal.hologres.aliyuncs.com:80/mc_test?ApplicationName=MaxCompute&currentSchema=public&useSSL=false&table=holo/'
      tblproperties (
        'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver',
        'odps.federation.jdbc.target.db.type'='holo',
        'odps.federation.jdbc.colmapping'='id:id,name:name'
      );
    3. 执行如下命令基于新建的Hologres外部表查询Hologres源表信息。

      命令示例如下。

      --访问Hologres外部表需要添加如下属性。
      set odps.sql.split.hive.bridge=true;
      set odps.sql.hive.compatible=true;
      --查询Hologres外部表数据。
      select * from my_table_holo_jdbc limit 10;

      返回结果如下。

      +------------+------------+
      | id         | name       |
      +------------+------------+
      | 1          | kate       |
      | 2          | mary       |
      | 3          | bob        |
      | 4          | tom        |
      | 5          | lulu       |
      | 6          | mark       |
      | 7          | haward     |
      | 8          | lilei      |
      | 9          | hanmeimei  |
      | 10         | lily       |
      +------------+------------+
    4. 可选:基于Hologres外部表与Hologres进行数据交换、联合分析。

      例如,将MaxCompute加工后的数据利用Hologres外部表,写入Hologres实现加速分析、在线服务。命令示例如下。

      --访问Hologres外部表需要添加如下属性。
      set odps.sql.split.hive.bridge=true;
      set odps.sql.hive.compatible=true;
      --向Hologres外部表插入数据。
      insert into my_table_holo_jdbc values (12,'alice');
      --查询Hologres外部表数据。
      select * from my_table_holo_jdbc;

      返回结果如下。

      +------------+------------+
      | id         | name       |
      +------------+------------+
      | 12          | alice      |
      | 1          | kate       |
      | 2          | mary       |
      | 3          | bob        |
      | 4          | tom        |
      | 5          | lulu       |
      | 6          | mark       |
      | 7          | haward     |
      | 8          | lilei      |
      | 9          | hanmeimei  |
      | 10         | lily       |
      | 11         | lucy       |
      +------------+------------+

      频繁更新的维度表保存在Hologres,满足实时动态更新需要。MaxCompute通过外部表方式访问维度表与MaxCompute中事实表进行关联分析,命令示例如下。

      --访问Hologres外部表需要添加如下属性。
      set odps.sql.split.hive.bridge=true;
      set odps.sql.hive.compatible=true;
      --创建MaxCompute内部表。
      create table holo_test as select * from my_table_holo_jdbc;
      --MaxCompute内部表与Hologres外部表进行关联分析。
      select * from my_table_holo_jdbc t1 inner join holo_test t2 on t1.id=t2.id;

      返回结果如下。

      +------------+------------+------------+------------+
      | id         | name       | id2        | name2      |
      +------------+------------+------------+------------+
      | 1          | kate       | 1          | kate       |
      | 2          | mary       | 2          | mary       |
      | 3          | bob        | 3          | bob        |
      | 4          | tom        | 4          | tom        |
      | 5          | lulu       | 5          | lulu       |
      | 6          | mark       | 6          | mark       |
      | 7          | harward    | 7          | harward    |
      | 8          | lilei      | 8          | lilei      |
      | 9          | hanmeimei  | 9          | hanmeimei  |
      | 10         | lily       | 10         | lily       |
      | 11         | lucy       | 11         | lucy       |
      | 12         | alice      | 12         | alice      |
      +------------+------------+------------+------------+

创建Hologres外部表(双签名模式)

双签名是MaxCompute和Hologres共同研发的认证及鉴权协议,在MaxCompute侧使用账号登录信息加签名后,把认证数据传递给Hologres侧,Hologres根据MaxCompute底层达成的协议,进行同名认证及鉴权。这样只需要在MaxCompute和Hologres有相同的账号,就可以直接进行外部表访问,不需要额外设置认证信息。

  • 前提条件。

    Hologres中存在跟MaxCompute相同名称的账号,并且该账号具有Hologres中对应表的读写权限。

  • 使用限制。

    仅Hologres V1.3及以上版本支持MaxCompute使用双签名模式创建Hologres外部表,目前双签名模式只支持从Hologres外部表中读取数据,不支持写入数据至Hologres外部表。

  • 命令示例。

    您可直接登录MaxCompute客户端,结合创建Hologres外部表语法通过双签名模式创建Hologres外部表。

    --创建外部表
    CREATE EXTERNAL TABLE IF NOT EXISTS holo_mc_external_dbl
    (
      id int,
      name string,
      ds string
    )
    STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
    location 'jdbc:postgresql://hgprecn-cn-zvp2o6aq****-cn-beijing-internal.hologres.aliyuncs.com:80/mc_test?ApplicationName=MaxCompute&currentSchema=public&preferQueryMode=simple&useSSL=false&table=mf_holo_mc_up/'
    TBLPROPERTIES (
      'mcfed.mapreduce.jdbc.driver.class'='org.postgresql.Driver',
      'odps.federation.jdbc.target.db.type'='holo',
      'odps.federation.jdbc.colmapping'='id:id,name:name,ds:ds'
    );
    
    --查询外部表(必须与打开双签名开关命令一起执行)
    set odps.sql.common.table.planner.ext.hive.bridge=true;
    select * from holo_mc_external_dbl;

开启Hologres外部表存储直读功能

背景介绍

MaxCompute对Hologres外部表的读是通过走JDBC模式,目前版本支持MaxCompute直读Hologres存储层,能够带来以下优势:

  • 可以大幅降低读表的延迟,大幅度提高查询数据的速度。

  • 可以大幅度减少Hologres FE的连接数,大多数Query仅需要一个连接数即可。

使用限制

当开启Hologres直读模式时,使用限制如下,在条件不满足时会回退到JDBC模式。

  • 依赖Hologres实例版本 : V1.3.34及以上版本。

    Hologres版本低于V1.3.34不支持直读功能。

  • 由于网络连通性,只支持同Region的MaxCompute访问Hologres实例。

    目前不支持跨Region访问,跨Region访问会报错:FAILED: ODPS-0010000:System internal error - fuxi job failed, caused by: Pangu request failed with error code 3

  • 不支持直读Hologres冷存表。

  • 目前直读功能不支持Hologres行存表。

  • 当Hologres配置为主从架构,仅支持配置连接URL为主实例,不支持配置为从实例。

  • 直读功能会对MaxCompute与Hologres之间的列进行类型校验,当出现不匹配的类型时会自动回退为JDBC模式执行。MaxCompute创建Hologres外部表时,数据类型的限制如下:

    • JDBC模式与MaxCompute直读模式都不支持的数据类型:

      • 不支持Array、Map、Struct复杂数据类型。

      • 不支持JSON、MONEY等数据类型。

    • JDBC模式支持,而MaxCompute直读模式不支持的数据类型:

      • BINARY类型。

    • MaxCompute直读模式支持,而JDBC模式不支持的数据类型:

      • JSONB数据类型

    • MaxCompute直读模式使用Timestamp类型映射Hologres中的Timestamp With Time Zone类型会存在细微的时间误差,具体差异如下:

      • Hologres中Timestamp With Time Zone类型列的时间在1900-12-31 15:54:15 之前,MaxCompute查询出来的时间会多5分44秒。

      • Hologres中Timestamp With Time Zone类型列的时间在1900-12-31 15:54:161969-12-31 23:59:58之间,MaxCompute查询出来的时间会多1秒。

      • Hologres中Timestamp With Time Zone类型列的时间在1969-12-31 23:59:59之后,MaxCompute查询出来的时间与Hologres中的无区别。

        说明

        MaxCompute直读模式使用Timestamp类型映射Hologres中的Timestamp With Time Zone类型不仅会存在细微的时间误差,还会存在时区偏移,例如:

        • 以MaxCompute的时区为东八区为例,Hologres中Timestamp With Time Zone类型列的时间为2000-01-01 00:00:00,MaxCompute查询出来的时间为2000-01-01 08:00:00

        • 以MaxCompute的时区为东八区为例,Hologres中Timestamp With Time Zone类型列的时间为1969-01-01 00:00:00,MaxCompute查询出来的时间为1969-01-01 08:00:01

    • 其他数据类型映射如下:

      Hologres数据类型

      MaxCompute数据类型

      说明

      TEXT

      • STRING

      • VARCHAR

      不涉及

      SMALLINT

      SMALLINT

      不涉及

      • INT

      • INT4

      • INTEGER

      INT

      不涉及

      • INT8

      • BIGINT

      BIGINT

      不涉及

      • FLOAT4

      • REAL

      FLOAT

      不涉及

      • FLOAT

      • FLOAT8

      DOUBLE

      不涉及

      • BOOL

      • BOOLEAN

      BOOLEAN

      不涉及

      TIMESTAMP

      TIMESTAMP

      存储精度为微秒,存在时区的时间误差。

      TIMESTAMP WITH TIME ZONE

      TIMESTAMP

      MaxCompute与Hologres在底层已经进行了精度转换。MaxCompute输出不包含时区格式。

      NUMERIC

      DECIMAL

      MaxCompute的DECIMAL如果未指定精度,则默认为(38,18),使用IMPORT FOREIGN SCHEMA语句创建表时系统会自动转换精度。

      CHAR(n)

      CHAR(n)

      MaxCompute的CHAR(n)为固定长度字符类型,n为长度。最大取值为255。长度不足则使用空格填充。

      VARCHAR(n)

      VARCHAR(n)

      MaxCompute的VARCHAR(n)为可变长度字符类型,n为长度。取值范围为1~65535。

      DATE

      DATE

      不涉及

  • Foreign Server模式存在的额外限制:MaxCompute Project需要开启三层模型。

开启方式

在MaxCompute中查询Hologres外部表时SQL前加上如下参数。

set odps.table.api.enable.holo.table=true;

直读验证

可以在Logview里查看日志,判断查询是否走了直读模式,Logview使用详情请参见使用Logview 2.0查看作业运行信息

在Logview的Summary页签下,查找external holo tables字段查看属性,属性格式如下:

<project_name>.<table_name>:<访问方式>[<(回退原因)>]

参数说明:

参数

说明

project_name

项目名称。

table_name

表名称。

访问方式

外部表的访问方式,取值如下:

  • Optimized:代表走的是直读模式,Logview示例如下。直读模式

  • Fallback:代表回退到了JDBC模式,Logview示例如下。回退为JDBC模式

回退原因

如果访问方式Fallback,显示回退为JDBC模式的原因,取值及解决方案如下。

  • Column type map error Column name ${ColumnName}:MaxCompute表与Hologres表对应的列类型不匹配或者不兼容导致,需要参照数据类型映射更改外部表的数据类型后就可以走直读模式。

  • Holo connection error: Hologres实例连接异常,有可能是权限信息有误或者Hologres实例状态有误,请检查当前用户是否有权限访问对应的Hologres数据库或者查看Hologres实例目前是否处于可服务状态。

  • Odps table is partition table:MaxCompute外部表为分区表导致,目前暂不支持。

  • Select hg_version errorHologres version check errorFetch hg_version data error:Hologres实例版本有误导致的,需要升级Hologres实例版本到V1.3.34或以上版本,升级实例版本详情请参见实例升级

  • 本页导读 (1)