Hologres外部表

外部表创建规范

• 如果Hologres开启了IP白名单功能，用户需要采用双签名模式创建Hologres外部表。如果用STS模式创建Hologres外部表，MaxCompute访问Hologres时会被IP白名单拦截。

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

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

• 表名和字段名大小写不敏感，且在创建表、查询表及字段时不支持强制转换大小写。

Schema兼容性

当Hologres源表中的Schema与外表Schema不一致时：

• 列数不一致：如果Hologres源表中的列数小于外表DDL的列数，则读取RDS数据时，系统会报错。例如column "xxx" does not exist。当Hologres源表中的列数大于外表DDL的列数时，系统会丢弃超出的列数据。

• 列类型不一致：MaxCompute不支持使用INT类型接收Hologres源表中的STRING类型数据，支持使用STRING类型接收INT类型数据（不推荐）。

STS模式创建Hologres外部表

具体使用示例代码，详情请参见创建Hologres外部表（STS模式）。

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'='<table_column1>:<source_column1>, <table_column2>:<source_column2>,...'
);

双签名模式创建Hologres外部表

具体使用示例代码，详情请参见创建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'='<table_column1>:<source_column1>, <table_column2>:<source_column2>,...']
);

创建Hologres数据库

1. 登录Hologres管理控制台，在左上角选择地域。

2. 在左侧导航栏选择实例列表。

3. 若没有实例，需要先购买Hologres实例。

   在实例列表页面，单击目标实例名称。

4. 在实例详情页面，单击登录实例。

5. 单击上方元数据管理页签。

   单击新建数据库，在弹出的对话框中，填写数据库名称，其他参数保持默认。

创建Hologres表

1. 在实例详情页面，单击登录实例。

2. 单击上方SQL编辑器页签。

3. 输入如下语句

   CREATE TABLE IF NOT EXISTS holo (
       id   INT PRIMARY KEY,
       name TEXT
   );
   
   INSERT INTO holo (id, name) VALUES
       (1, 'kate'),
       (2, 'mary'),
       (3, 'bob'),
       (4, 'tom'),
       (5, 'lulu'),
       (6, 'mark'),
       (7, 'haward'),
       (8, 'lilei'),
       (9, 'hanmeimei'),
       (10, 'lily'),
       (11, 'lucy');
   
   SELECT * FROM holo ORDER BY id;

前置准备

1. 已安装并配置MaxCompute客户端

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

   创建MaxCompute项目信息，请参见创建MaxCompute项目。

RAM角色创建及授权

1. 创建RAM角色

   创建RAM角色获取ARN信息，用于创建外部表时填写STS认证信息。创建RAM角色的信任主体类型根据实际需求选择，本示例中信任主体类型为云账号。

   • 云账号：阿里云账号下的RAM用户可以通过扮演RAM角色来访问云资源，详情请参见创建可信实体为阿里云账号的RAM角色。

   • 身份提供商：通过设置SSO实现从企业本地账号系统登录至阿里云控制台，解决企业的统一用户登录认证要求。详情请参见创建可信实体为身份提供商的RAM角色。

   操作步骤如下：

   1. 登录RAM控制台。

   2. 在左侧导航栏选择身份管理 > 角色。

   3. 在角色页面，单击创建角色。

   4. 在创建角色页面，选择信任主体类型为云账号。

   5. 信任主体名称选择当前云账号。

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

   1. 在角色页面，单击目标角色名称，进入该角色详情页。

   2. 在信任策略页签，单击编辑信任策略，在编辑信任策略页面选择脚本编辑页签。

   3. 参照如下内容修改信任策略配置

      修改信任策略配置内容与选择的可信实体类型相关。其中<UID>即阿里云账号ID，可在用户信息页面获取。

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

      {
        "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"
      }

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

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

   通过Hologres管理控制台授权

   1. 登录Hologres管理控制台，在左上角选择地域。

   2. 在左侧导航栏选择实例列表。

   3. 在实例列表页面，单击目标实例名称。

   4. 在实例详情页面，单击登录实例。

   5. 单击上面安全中心页签。

      在左侧导航栏选择用户管理。

   6. 在用户管理页面，单击右上角新增用户，在弹出的新增用户页面，添加RAM角色至Hologres实例。

   7. 在DB授权页签，为该RAM角色授予实例的开发权限。

      若数据库的权限策略设置为专家模式，则需在用户管理页面将角色类型修改为SuperUser，后续将不再需要进行数据库授权操作。

      1. 在左侧导航栏选择DB 授权。

      2. 在DB 授权页面，单击目标数据库对应操作列的用户授权。

      3. 在授权页面，单击右上角新增授权，在弹出的新增授权对话框，选择RAM角色并配置Developer 用户组。

   通过RAM访问控制台授权

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

   1. 登录RAM控制台。

   2. 在左侧导航栏选择身份管理 > 角色。

   3. 在角色页面，单击目标角色名称所在行操作列的新增授权。

   4. 选择AliyunRAMReadOnlyAccess权限为当前角色授权。

   通过SQL方式授权

   通过SQL方式进行授权，授权SQL请参见Hologres权限模型。

在MaxCompute中创建外部表

1. 登录MaxCompute客户端，进入目标MaxCompute项目。

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

   按照准备数据步骤，准备好的Hologres实例信息如下：

   • Hologres数据库名称：holo_external_test。

   • Hologres数据库的Schema：public。

   • Hologres表名称：holo。

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

   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/<role name>')
   location 'jdbc:postgresql://hgprecn-cn-oew210ut****-cn-hangzhou-internal.hologres.aliyuncs.com:80/<holo database name>?ApplicationName=MaxCompute&currentSchema=public&useSSL=true&table=<table name>/'
   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;
   SET odps.table.api.enable.holo.table=true; --开启JDBC直读方式 
   -- 查询Hologres外部表数据。
   SELECT * FROM my_table_holo_jdbc limit 10;
   -- 返回结果。
   +------------+------------+
   | id         | name       | 
   +------------+------------+
   | 9          | hanmeimei  | 
   | 4          | tom        | 
   | 7          | haward     | 
   | 2          | mary       | 
   | 5          | lulu       | 
   | 8          | lilei      | 
   | 10         | lily       | 
   | 1          | kate       | 
   | 6          | mark       | 
   | 11         | lucy       | 
   +------------+------------+

4. 基于Hologres外部表与Hologres进行数据交换、联合分析。

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

     -- 访问Hologres外部表需要添加如下属性。
     SET odps.sql.split.hive.bridge=true;
     SET odps.sql.hive.compatible=true;
     SET odps.table.api.enable.holo.table=true; --开启JDBC直读方式 
     -- 向Hologres外部表插入数据。
     INSERT INTO my_table_holo_jdbc VALUES (12,'alice');
     -- 查询Hologres外部表数据。
     SELECT * FROM my_table_holo_jdbc;
     -- 返回结果。
     +------------+------------+
     | id         | name       | 
     +------------+------------+
     | 9          | hanmeimei  | 
     | 4          | tom        | 
     | 7          | haward     | 
     | 2          | mary       | 
     | 5          | lulu       | 
     | 12         | alice      | 
     | 8          | lilei      | 
     | 10         | lily       | 
     | 1          | kate       | 
     | 11         | lucy       | 
     | 6          | mark       | 
     | 3          | bob        | 
     +------------+------------+

   • 频繁更新的维度表保存在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      | 
     +------------+------------+------------+------------+
     | 9          | hanmeimei  | 9          | hanmeimei  | 
     | 4          | tom        | 4          | tom        | 
     | 7          | haward     | 7          | haward     | 
     | 2          | mary       | 2          | mary       | 
     | 5          | lulu       | 5          | lulu       | 
     | 12         | alice      | 12         | alice      | 
     | 8          | lilei      | 8          | lilei      | 
     | 10         | lily       | 10         | lily       | 
     | 1          | kate       | 1          | kate       | 
     | 11         | lucy       | 11         | lucy       | 
     | 6          | mark       | 6          | mark       | 
     | 3          | bob        | 3          | bob        | 
     +------------+------------+------------+------------+

前置准备

1. 已安装并配置MaxCompute客户端

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

   创建MaxCompute项目信息，请参见创建MaxCompute项目。

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

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

在MaxCompute中创建外部表

1. 登录MaxCompute客户端，进入目标MaxCompute项目。

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

   按照准备数据步骤，准备好的Hologres实例信息如下：

   • Hologres数据库名称：holo_external_test。

   • Hologres数据库的Schema：public。

   • Hologres表名称：holo。

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

   -- 创建外部表
   CREATE EXTERNAL TABLE IF NOT EXISTS holo_mc_external_dbl
   (
     id int,
     name string
   )
   STORED BY 'com.aliyun.odps.jdbc.JdbcStorageHandler'
   location 'jdbc:postgresql://hgpostcn-cn-****-cn-hangzhou-internal.hologres.aliyuncs.com:80/<holo database name>?ApplicationName=MaxCompute&currentSchema=public&preferQueryMode=simple&useSSL=false&table=<table name>/'
   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. 查询外部表

   -- 查询外部表（必须与打开双签名开关命令一起执行）
   SET odps.sql.common.table.planner.ext.hive.bridge=true;
   SELECT * FROM holo_mc_external_dbl;
   
   -- 返回结果。
   +------------+------------+
   | id         | name       | 
   +------------+------------+
   | 9          | hanmeimei  | 
   | 4          | tom        | 
   | 7          | haward     | 
   | 2          | mary       | 
   | 5          | lulu       | 
   | 12         | alice      | 
   | 8          | lilei      | 
   | 10         | lily       | 
   | 1          | kate       | 
   | 11         | lucy       | 
   | 6          | mark       | 
   | 3          | bob        | 
   +------------+------------+