MaxCompute外部表支持使用数据库JDBC驱动机制访问Hologres数据源数据。本文为您介绍如何在外部表建表语句中指定Hologres数据源、STS认证信息、映射目标表、JDBC驱动信息,来创建Hologres的外部表。

背景信息

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

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

前提条件

创建Hologres外部表前,请确认已经满足如下条件:
  • 已准备好Hologres数据库及目标表。

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

    创建Hologres表信息,请参见CREATE TABLE

    假设已准备好的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外部表使用过程中需要注意:
  • 对于Hologres的父、子表,Hologres外部表中会指定表名,并执行SQL语句。父、子表都可以映射至Hologres外部表,但是父表只能读不能写。
  • 如果在执行查询Hologres外部表或向Hologres外部表插入数据时,报错FAILED: Generating job conf failed, gen jobconf failed: External table location scheme "jdbc:postgresql" is not supported,原因是未打开对SQL执行计划优化升级的功能jobconf2。大部分项目已默认打开此功能,部分项目还未打开此功能。如果需要打开此功能,请在Session级别增加如下4个属性。
    set odps.sql.jobconf.odps2=true;
    set odps.sql.jobconf.odps2.enforce=true;
    set odps.sql.split.hive.bridge=true;
    set odps.sql.hive.compatible=true;
  • 向Hologres外部表写入数据时,暂不支持Hologres的INSERT ON CONFLICT机制。如果Hologres源表有主键,请避免写入的数据与Hologres源表中的数据产生主键唯一性冲突。

创建Hologres外部表语法

在创建外部表时,您需要在建表DDL语句中指定StorageHandler,并配置STS认证信息、JDBC连接地址实现访问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>?[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,...>']
);
  • 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:必填。指定RAM角色的ARN信息。您可以在RAM角色管理页面,单击目标RAM角色名称后,在基本信息区域获取。
  • location:必填。Hologres实例的JDBC连接地址。其中:
    • endpoint:必填。Hologres实例的经典网络域名。获取方式,请参见实例配置
    • port:必填。Hologres实例的网络端口。获取方式,请参见实例配置
    • database:必填。连接的Hologres数据库名称。更多Hologres数据库信息,请参见CREATE DATABASE
    • schema:可选。如果表名在Hologres数据库内是唯一的,或源表是默认Schema中的表,可以不配置该属性。更多Schema信息,请参见CREATE SCHEMA
    • holo_table_name:必填。Hologres源表名称。更多Hologres源表信息,请参见CREATE TABLE
  • tblproperties
    • mcfed.mapreduce.jdbc.driver.class:必填。指定连接Hologres数据库的驱动程序。固定取值为org.postgresql.Driver
    • odps.federation.jdbc.target.db.type:必填。指定连接的数据库类型。固定取值为holo
    • odps.federation.jdbc.colmapping:可选。如果需要将指定数据源的部分列映射至Hologres外部表,需要配置该参数,指定Hologres源表的字段和MaxCompute外部表字段的映射关系。如果不配置该参数,按照源表字段顺序映射至Hologres外部表。格式为Hologres外部表列名1:Hologres源表列名1,Hologres外部表列名2:Hologres源表列名2,...

操作流程

创建Hologres外部表的流程如下:

  1. 步骤一:创建RAM角色
    创建RAM角色获取ARN信息,用于创建外部表时填写STS认证信息。
  2. 步骤二:添加RAM角色至Hologres实例并授权
    将新建的RAM角色添加至Hologres实例中,授予RAM角色访问Hologres实例的权限。
  3. 步骤三:创建Hologres外部表
    创建Hologres外部表,映射Hologres源表。

步骤一:创建RAM角色

使用阿里云账号登录访问控制,创建RAM角色,并记录ARN信息。当前可信实体类型,可以选择阿里云账号或者身份提供商。
RAM角色的信任策略配置内容如下。阿里云账号ID可在用户信息页面获取。
{
  "Statement": [
    {
      "Action": "sts:AssumeRole",
      "Effect": "Allow",
      "Principal": {
        "RAM": [
          "acs:ram::阿里云账号ID:root"
        ]
      }
    },
    {
      "Action": "sts:AssumeRole",
      "Effect": "Allow",
      "Principal": {
        "Service": [
          "odps.aliyuncs.com"
        ]
      }
    }
  ],
  "Version": "1"
}
创建RAM角色

步骤二:添加RAM角色至Hologres实例并授权

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

  • 通过Hologres管理控制台授权。
    1. 登录Hologres管理控制台
    2. 在左侧导航栏单击实例列表,单击需要授权的Hologres实例名称,在用户管理页面单击新增用户添加RAM角色至Hologres实例。
    3. DB授权页签,为该RAM角色授予实例的开发权限。
  • 通过SQL方式授权。

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

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

步骤三:创建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?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      |
    +------------+------------+------------+------------+