Visit Table Store data

You can authorize permissions in the following two ways:

• When the MaxCompute and Table Store owner are the same account, you can directly log on with the Alibaba Cloud account and click here to complete authorization.
• Custom authorization.
  1. Firstly, you must grant Table Store access permission to MaxCompute in the RAM console. 

     Log on to the  RAM console (if MaxCompute and Table Store are not the same account, log on with the Table Store account to authorize), and create the role AliyunODPSDefaultRole.

  2. Set its policy content as follows:

     --if MaxCompute and Table Store are same account  
     {
     "Statement": [
     {
      "Action": "sts:AssumeRole",
      "Effect": "Allow",
      "Principal": {
        "Service": [
          "odps.aliyuncs.com"
        ]
      }
     }
     ],
     "Version": "1"
     }
     --if MaxCompute and Table Store are not the same account
     {
     "Statement": [
     {
      "Action": "sts:AssumeRole",
      "Effect": "Allow",
      "Principal": {
        "Service": [
          "MaxCompute's Owner cloud account UID@odps.aliyuncs.com"
        ]
      }
     }
     ],
     "Version": "1"
     }

     	Note
     	In the upper-right corner, click the Avatar to open the Billing Management page, and check the account UID.

  3. Edit this role’s authorization policy 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"
     }
     ]
     }
     --You can also customize other permissions

  4. Grant the permission AliyunODPSRolePolicy to this role.

Use following statements to create an external table:

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
)
STORED BY 'com.aliyun.odps.TableStoreStorageHandler' -- (1)
WITH SERDEPROPERTIES ( -- (2)
'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' --③
)
LOCATION 'tablestore://odps-ots-dev.cn-shanghai.ots-internal.aliyuncs.com'; -- （3）

①tablestore.columns.mapping option: Required to describe the columns of the Table Store table that MaxCompute is going to access, includes primary key and attribute columns.

• At the beginning of the column name, : indicates a Table Store primary key. In this example:o_orderkey and :o_orderdate  are primary key columns and all others are attribute columns.
• Table Store supports up to 4 primary keys. Primary keys support the STRING, INTEGER, and BINARY data types. The first primary key is the partition key.
• When specifying a mapping relationship, you must provide all the primary keys of the specified Table Store table, but you do not have to provide all attribute columns, only the attribute columns you must access by using MaxCompute.

If you want to view the created external table structure, run the following statement:

desc extended <table_name>;

After creating an external table, you can introduce Table Store data to the MaxCompute ecosystem.There, you can use MaxCompute SQL syntax to access Table Store data as follows:

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;

If you perform multiple computations on a single data set, instead of remotely reading data from Table Store each time, you can import all the necessary data to MaxCompute, to create a MaxCompute (internal) table. For example:

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

In the preceding operations, the external table ots_table_external has been created to connect MaxCompute with the Table Store table ots_tpch_orders, and data has been stored in the internal MaxCompute table internal_orders. Now you can write the processed data from internal_orders back to Table Store, perform the INSERT OVERWITE TABLE operation on the external table as follows:

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