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

您可以通过以下两种方式授予权限：

• 当MaxCompute和Tablestore的Owner是同一个账号时，在登录阿里云账号后，需要完成一键授权。详情请参见一键授权。
• 自定义授权
  1. 首先在RAM控制台中授予MaxCompute访问Tablestore的权限。

     登录RAM控制台（若MaxCompute和Tablestore不是同一个账号，此处需由Tablestore账号登录进行授权），创建角色，例如角色AliyunODPSDefaultRole、AliyunODPSRoleForOtherUser。

  2. 修改策略内容设置。

     --当MaxCompute和Tablestore的Owner是同一个账号时，进行如下设置。
     {
     "Statement": [
     {
      "Action": "sts:AssumeRole",
      "Effect": "Allow",
      "Principal": {
        "Service": [
          "odps.aliyuncs.com"
        ]
      }
     }
     ],
     "Version": "1"
     }
     --当MaxCompute和Tablestore的Owner不是同一个账号时，进行如下设置。
     {
     "Statement": [
     {
      "Action": "sts:AssumeRole",
      "Effect": "Allow",
      "Principal": {
        "Service": [
          "MaxCompute的Owner云账号UID@odps.aliyuncs.com"
        ]
      }
     }
     ],
     "Version": "1"
     }

     您可以单击右上角的头像查看云账号的UID。
  3. 编辑该角色的授权策略AliyunODPSRolePolicy。

     {
     "Version": "1",
     "Statement": [
     {
      "Action": [
        "ots:ListTable",
        "ots:DescribeTable",
        "ots:GetRow",
        "ots:PutRow",
        "ots:UpdateRow",
        "ots:DeleteRow",
        "ots:GetRange",
        "ots:BatchGetRow",
        "ots:BatchWriteRow",
        "ots:ComputeSplitPointsBySize"
      ],
      "Resource": "*",
      "Effect": "Allow"
     }
     ]
     }
     --还可自定义其它权限。

  4. 将权限AliyunODPSRolePolicy授权给该角色。

建表时，表名和字段名大小写不敏感。创建外部表语句示例如下。

DROP TABLE IF EXISTS ots_table_external;
CREATE EXTERNAL TABLE IF NOT EXISTS ots_table_external
(
odps_orderkey bigint,
odps_orderdate string,
odps_custkey bigint,
odps_orderstatus string,
odps_totalprice double,
odps_createdate timestamp
)
STORED BY 'com.aliyun.odps.TableStoreStorageHandler'
WITH SERDEPROPERTIES (
'tablestore.columns.mapping'=':o_orderkey,:o_orderdate,o_custkey,o_orderstatus,o_totalprice',
'tablestore.table.name'='ots_tpch_orders',
'odps.properties.rolearn'='acs:ram::xxxxx:role/aliyunodpsdefaultrole',
'tablestore.timestamp.ticks.unit'='seconds',
'tablestore.column.odps_createdate.timestamp.ticks.unit'='millis',
'tablestore.table.put.row'='true'
)
LOCATION 'tablestore://odps-ots-dev.cn-shanghai.ots-internal.aliyuncs.com';

示例说明：

• com.aliyun.odps.TableStoreStorageHandler是MaxCompute内置的处理Tablestore数据的StorageHandler，定义了MaxCompute和Tablestore的交互，相关逻辑由MaxCompute实现。
• SERDEPROPERITES是提供参数选项的接口，在使用TableStoreStorageHandler时，有三个必选项和两个可选项。
  • 三个必选项为：
    • tablestore.columns.mapping：用于描述MaxCompute将访问的Tablestore表的列，包括主键和属性列。
      • 以冒号（：）开头用于表示Tablestore主键，例如示例中的:o_orderkey和:o_orderdate，其它的均为属性列。
      • Tablestore支持1~4个主键，主键类型为STRING、INTEGER和BINARY，其中第一个主键为分区键。
      • 在指定映射时，您必须提供指定Tablestore表的所有主键，无需提供全部的属性列。只需提供需要通过MaxCompute访问的属性列，提供的属性列必须是Tablestore表的列，否则即使外部表可以创建成功，查询时也会报错。
    • tablestore.table.name：需要访问的Tablestore表名。如果指定的Tablestore表名错误（不存在），则会报错，MaxCompute不会主动创建Tablestore表。
    • odps.properties.rolearn：RAM中AliyunODPSDefaultRole的ARN信息。您可以通过RAM控制台中的RAM角色管理进行获取。
  • 三个可选项为：
    • tablestore.timestamp.ticks.unit：表级别时间类型设置。用以指定该外部表中所有Integer的字段都处于同一个时间类型。选项值为seconds（秒）、millis（毫秒）、micros（微秒）、nanos（纳秒）。
    • tablestore.column.<col1_name>.timestamp.ticks.unit：列级别时间类型设置。用以指定该外部表中列字段的时间类型，选项值为seconds（秒）、millis（毫秒）、micros（微秒）、nanos（纳秒）。
    • tablestore.table.put.row：支持指定PutRow的写入方式。选项值为True（打开）、False（关闭），默认值为False。
    说明

    • 前两个选项主要是将Tablestore中字段类型为Integer的字段映射为MaxCompute中的timestamp类型。两个选项同时存在时，列级别优先级比表级别的高。
    • 可以通过设置以下Flag的参数值指定PutRow的写入方式，默认值为False。详情请参见Flag参数列表。

      set odps.sql.unstructured.tablestore.put.row=true;

• LOCATION ：用来指定Tablestore的Instance名、Endpoint等具体信息。这里的Tablestore数据的安全访问建立在前文介绍的RAM/STS授权的前提上。
  说明 如果您使用公网地址报错，显示网络不同，可尝试更换为经典网地址。

您可以执行如下语句，查看创建好的外部表结构信息。

desc extended <table_name>;

创建完外部表后，Tablestore的数据便引入到了MaxCompute生态中，即可通过正常的MaxCompute SQL语法访问Tablestore数据，如下所示。

SELECT odps_orderkey, odps_orderdate, SUM(odps_totalprice) AS sum_total
FROM ots_table_external
WHERE odps_orderkey > 5000 AND odps_orderkey < 7000 AND odps_orderdate >= '1996-05-03' AND odps_orderdate < '1997-05-01'
GROUP BY odps_orderkey, odps_orderdate
HAVING sum_total> 400000.0;

如果您需要对一份数据做多次计算，相比每次从Tablestore去远程读数据，更高效的方法是先一次性把需要的数据导入到MaxCompute内部成为一个MaxCompute（内部）表，示例如下。

CREATE TABLE internal_orders AS
SELECT odps_orderkey, odps_orderdate, odps_custkey, odps_totalprice
FROM ots_table_external
WHERE odps_orderkey > 5000 ;

根据上面的操作，您已创建了外部表ots_table_external来打通MaxCompute与Tablestore数据表ots_tpch_orders的链路，同时还有一份存储在MaxCompute内部表internal_orders的数据。现在，如果您需要对internal_orders中的数据进行处理后再写回Tablestore，则可通过对外部表执行insert overwrite table操作实现，示例如下。

INSERT OVERWRITE TABLE ots_table_external
SELECT odps_orderkey, odps_orderdate, odps_custkey, CONCAT(odps_custkey, 'SHIPPED'), CEIL(odps_totalprice)
FROM internal_orders;

INSERT OVERWRITE TABLE ots_table_external
SELECT odps_orderkey, odps_orderdate, odps_custkey, CONCAT(odps_custkey, 'SHIPPED'), CEIL(odps_totalprice)
FROM (SELECT * FROM internal_orders DISTRIBUTE BY rand()) t;