云原生大数据计算服务 MaxCompute：Hologres外部表

前提条件

使用限制

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外部表（STS模式）

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

1. 创建RAM角色
   创建RAM角色获取ARN信息，用于创建外部表时填写STS认证信息。
   1. 登录RAM访问控制创建RAM角色。
      创建RAM角色的可信实体类型根据实际需求选择阿里云账号或者身份提供商。

      • 阿里云账号：

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

      • 身份提供商：

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

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

      1. 在角色页面，单击已创建完成的RAM角色名称。
      2. 单击信任策略管理页签。
      3. 在信任策略管理页签，单击修改信任策略。
      4. 在修改信任策略面板，参照如下内容修改信任策略配置。
         修改信任策略配置内容与选择的可信实体类型相关。

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

           {
             "Statement": [
               {
                 "Action": "sts:AssumeRole",
                 "Effect": "Allow",
                 "Principal": {
                   "RAM": [
                     "acs:ram::<阿里云账号（主账号）ID>:root"
                   ]
                 }
               },
               {
                 "Action": "sts:AssumeRole",
                 "Effect": "Allow",
                 "Principal": {
                   "Service": [
                     "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::<阿里云账号（主账号）ID>:saml-provider/IDP"
                           ]
                       }
                   },
                   {
                       "Action": "sts:AssumeRole",
                       "Effect": "Allow",
                       "Principal": {
                           "Service": [
                               "odps.aliyuncs.com"
                           ]
                       }
                   }
               ],
               "Version": "1"
           }

           说明 阿里云账号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角色授予实例的开发权限。
   • 通过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;