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

前提条件

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

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

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

  • 已准备好目标MaxCompute项目。

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

背景信息

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

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

使用限制

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

  • MaxCompute不支持对创建的MC-Hologres外部表进行更新(Update)、删除(Delete)操作。
  • MC-Hologres的分区表和MaxCompute的分区表没有对应关系。MaxCompute的外部表不支持分区。

注意事项

MC-Hologres外部表使用过程中需要注意:
  • 对于MC-Hologres的父、子表,MaxCompute的JDBC外部表中会指定表名,并执行SQL语句。父、子表都可以映射至MC-Hologres外部表,但是父表只能读。
  • 如果在执行查询MC-Hologres外部表或向MC-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;
  • MaxCompute写入MC-Hologres外部表的功能,暂不支持MC-Hologres的INSERT ON CONFLICT机制。在向MC-Hologres外部表写入数据时,如果MC-Hologres源表有主键,请避免写入的数据与MC-Hologres源表中的数据产生主键唯一性冲突。

MC-Hologres外部表定义

在创建外部表时,您需要在建表DDL语句中指定StorageHandler,并配置JDBC驱动机制参数实现访问MC-Hologres数据源。建表语句定义如下:
create external table <table_name>(
  <col1_name> <data_type>,
  <col2_name> <data_type>,
  ......
)
stored by '<com.aliyun.odps.jdbc.JdbcStorageHandler>'
location '<jdbc:postgresql://<accessid>:<accesskey>@<endpoint>:<port>/<database>?currentSchema=<schema>&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,...>']
);
  • table_name:必填。在MaxCompute上创建的MC-Hologres外部表的名称。
  • col_name:必填。MC-Hologres外部表的列名称。
  • data_type:必填。MC-Hologres外部表的列的数据类型。
  • stored by:必填。指定StorageHandler,定义了如何查询MC-Hologres外部表。固定取值为com.aliyun.odps.jdbc.JdbcStorageHandler,使用JdbcStorageHandler连接方式。
  • location:必填。MC-Hologres实例的JDBC连接地址。其中:
    • accessid:必填。具备访问MC-Hologres实例权限的AccessKey ID。
    • accesskey:必填。AccessKey ID对应的AccessKey Secret。
    • endpoint:必填。MC-Hologres实例的经典网络域名。获取方式,请参见实例配置
    • port:必填。MC-Hologres实例的网络端口。获取方式,请参见实例配置
    • database:必填。连接的MC-Hologres数据库名称。更多MC-Hologres数据库信息,请参见CREATE DATABASE
    • schema:可选。如果表名在MC-Hologres数据库内是唯一的,或源表是默认Schema中的表,可以不配置该属性。更多Schema信息,请参见CREATE SCHEMA
    • holo_table_name:必填。MC-Hologres源表名称。更多MC-Hologres源表信息,请参见CREATE TABLE
    说明 JDBC连接字符串中需要使用明文AccessKey才能创建表,请注意保护AccessKey信息。
  • tblproperties:必填。需要配置如下两个参数:
    • mcfed.mapreduce.jdbc.driver.class:必填。指定连接MC-Hologres数据库的驱动程序。固定取值为org.postgresql.Driver
    • odps.federation.jdbc.target.db.type:必填。指定连接的数据库类型。固定取值为holo
    • odps.federation.jdbc.colmapping:可选。如果需要将指定数据源的部分列映射至MC-Hologres外部表,需要配置该参数,指定MC-Hologres源表的字段和MaxCompute外部表字段的映射关系。如果不配置该参数,按照源表字段顺序映射至MC-Hologres外部表。

使用示例

假设准备好的MC-Hologres实例信息如下:
  • MC-Hologres数据库名称:mc_test
  • MC-Hologres数据库的Schema:public
  • MC-Hologres数据库经典网络连接地址:hgprecn-cn-oew210ut****-cn-hangzhou-internal.hologres.aliyuncs.com:80
  • MC-Hologres表名称:holo。表数据如下:表数据
  1. 登录MaxCompute客户端,进入目标MaxCompute项目。
    进入目标项目命令信息,请参见 项目空间操作
  2. 执行如下命令创建MC-Hologres外部表。
    命令示例如下:
    create external table if not exists my_table_holo_jdbc
    (
     id bigint,
     name string
    )
    stored by 'com.aliyun.odps.jdbc.JdbcStorageHandler' 
    LOCATION 'jdbc:postgresql://LTAI4FzxmCwzb4BJqFce****:hKZMEFjdLe8ngRT5qp75UYufvT****@hgprecn-cn-oew210utf003-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. 执行如下命令基于新建的MC-Hologres外部表查询MC-Hologres源表信息。
    命令示例如下:
    --访问MC-Hologres外部表需要添加如下属性。
    set odps.sql.split.hive.bridge=true;
    set odps.sql.hive.compatible=true;
    --查询MC-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. 可选:基于MC-Hologres外部表与MC-Hologres进行数据交换、联合分析。
    例如,将MaxCompute加工后的数据利用MC-Hologres外部表,写入MC-Hologres实现加速分析、在线服务。命令示例如下:
    --访问MC-Hologres外部表需要添加如下属性。
    set odps.sql.split.hive.bridge=true;
    set odps.sql.hive.compatible=true;
    --向MC-Hologres外部表插入数据。
    insert into my_table_holo_jdbc values (12,'alice');
    --查询MC-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       |
    +------------+------------+
    频繁更新的维度表保存在MC-Hologres,满足实时动态更新需要。MaxCompute通过外部表方式访问维度表与MaxCompute中事实表进行关联分析,命令示例如下:
    --访问MC-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内部表与MC-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          | alice      |
    | 2          | mary       | 2          | mary       |
    | 3          | bob        | 3          | bob        |
    | 4          | tom        | 4          | tom        |
    | 5          | lulu       | 5          | lulu       |
    | 6          | mark       | 6          | mark       |
    | 7          | haward     | 7          | haward     |
    | 8          | lilei      | 8          | lilei      |
    | 9          | hanmeimei  | 9          | hanmeimei  |
    | 10         | lily       | 10         | lily       |
    | 11         | lucy       | 11         | lucy       |
    | 12         | alice      | 12         | alice      |
    +------------+------------+------------+------------+