MaxCompute外部表支持使用数据库JDBC驱动机制访问Hologres数据源数据。本文为您介绍如何在外部表建表语句中指定Hologres数据源、STS认证信息或打开双签名开关、映射目标表、JDBC驱动信息,来创建Hologres的外部表。
背景信息
Hologres是兼容PostgreSQL协议的实时交互式分析数据仓库,在底层与MaxCompute无缝连接。
您可以使用在MaxCompute上创建Hologres外部表的方式,基于PostgreSQL JDBC驱动及STS认证信息查询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数据库名称:
- 已准备好待创建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的父、子表,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(UPSERT)机制。如果Hologres源表有主键,请避免写入的数据与Hologres源表中的数据产生主键唯一性冲突。
- 建表时,表名和字段名大小写不敏感。在查询表或字段时,无需区分大小写,且不支持强制转换大小写。
创建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实例的网络端口。获取方式,请参见实例配置。
- 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源表的字段和MaxCompute外部表字段的映射关系。如果不配置该参数,按照源表字段顺序映射至Hologres外部表。格式为
Hologres外部表列名1:Hologres源表列名1,Hologres外部表列名2:Hologres源表列名2,...
。
- mcfed.mapreduce.jdbc.driver.class:必填。指定连接Hologres数据库的驱动程序。固定取值为
创建Hologres外部表(STS模式)
通过STS模式创建Hologres外部表的步骤如下:
- 创建RAM角色创建RAM角色获取ARN信息,用于创建外部表时填写STS认证信息。
- 添加RAM角色至Hologres实例并授权RAM角色需要有Hologres实例的开发权限,才能在权限范围内使用Hologres。由于RAM角色默认没有Hologres管理控制台的查看和操作实例的权限,因此需要阿里云账号完成RAM相关权限授予才能进行后续操作。添加RAM角色至Hologres实例,您可以通过如下方式进行授权。
- 通过Hologres管理控制台授权。
- 登录Hologres管理控制台。
- 在左侧导航栏单击实例列表,单击需要授权的Hologres实例名称。
- 在实例详情页面,单击账号管理。
- 在用户管理页面单击新增用户添加RAM角色至Hologres实例。
- 在DB授权页签,为该RAM角色授予实例的开发权限。
- 通过SQL方式授权。
您可以通过SQL方式进行授权,授权SQL请参见Hologres权限模型概述。
- 若是通过RAM用户扮演RAM角色,RAM用户默认没有Hologres管理控制台的权限,需要阿里云账号给RAM用户在访问控制页面授予AliyunRAMReadOnlyAccess权限,否则RAM用户无法在Hologres管理控制台进行任何操作。详情请参见文档授予RAM用户权限。
- 通过Hologres管理控制台授权。
- 创建Hologres外部表完成上述步骤后,基于已准备好的数据信息,您即可登录MaxCompute客户端,结合创建Hologres外部表语法创建Hologres外部表。
创建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¤tSchema=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外部表存储直读功能
背景介绍
- 可以大幅降低读表的延迟,大幅度提高查询数据的速度。
- 可以大幅度减少Hologres FE的连接数,大多数Query仅需要一个连接数即可。
使用限制
- 依赖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配置为主从架构,仅支持配置连接URL为主实例,不支持配置为从实例。
- 直读功能会对MaxCompute与Hologres之间的列进行类型校验,当出现不匹配的类型时会自动回退为JDBC模式执行。MaxCompute创建Hologres外部表时,数据类型的限制如下:
- JDBC模式与MaxCompute直读模式都不支持的数据类型:
- 不支持Array、Map、Struct复杂数据类型。
- 不支持JSON、JSONB、MONEY等数据类型。
- JDBC模式支持,而MaxCompute直读模式不支持的数据类型:
- BINARY类型。
- Foreign Server模式下的BOOL类型。
- MaxCompute直读模式使用Timestamp类型会存在一定的时区误差。
- 其他数据类型映射如下:
Hologres数据类型 MaxCompute数据类型 说明 TEXT - STRING
- VARCHAR
不涉及 SMALLINT SMALLINT 不涉及 - INT
- INT4
- INTEGER
INT 不涉及 - INT8
- BIGINT
BIGINT 不涉及 - FLOAT4
- REAL
FLOAT 不涉及 - FLOAT
- FLOAT8
DOUBLE 不涉及 - BOOL
- BOOLEAN
BOOLEAN Foreign Server模式下不支持BOOL类型。 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 不涉及
- JDBC模式与MaxCompute直读模式都不支持的数据类型:
- Foreign Server模式存在的额外限制:MaxCompute Project需要开启三层模型。
- 不支持读取Hologres侧加密后的数据。
开启方式
set odps.table.api.enable.holo.table=true;
直读验证
可以在Logview里查看日志,判断查询是否走了直读模式,Logview使用详情请参见使用Logview 2.0查看作业运行信息。
external holo tables
字段查看属性,属性格式如下:<project_name>.<table_name>:<访问方式>[<(回退原因)>]
参数说明:参数 | 说明 |
---|---|
project_name | 项目名称。 |
table_name | 表名称。 |
访问方式 | 外部表的访问方式,取值如下:
|
回退原因 | 如果访问方式为Fallback ,显示回退为JDBC模式的原因,取值及解决方案如下。
|