Access Table Store data

You can authorize permissions in the following two ways:

• When the MaxCompute and Table Store's 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, you must 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 On the upper-right corner, click the avatar to open the Billing Management page, and then 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）

The statement is described as follows：

• com.aliyun.odps.TableStoreStorageHandler is MaxCompute’s built-in StorageHandler for processing Table Store data. It defines the interaction between MaxCompute and Table Store. The relevant logic is implemented by MaxCompute.
• SERDEPROPERITES is an interface that provides parameter options. When using TableStoreStorageHandler, two options must be specified, tablestore.columns.mapping and tablestore.table.name and odps.properties.rolearn.
  1. tablestore.columns.mapping option: required to describe the columns of the table store table that MaxCompute is going to access, including 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.
  2. tablestore.table.name：the name of the table store table that needs to be accessed. If you specify an incorrect Table Store table name (such as a table that does not exist), the system reports an error. MaxCompute does not create a new Table Store table with the specified name.
  3. odps.properties.rolearn: Arn information in RAM's AliyunODPSDefaultRole. You can get it through the details of the role in the RAM console.
• LOCATION clause: specific information for specifying Table Storeinstance names, endpoint, and so on. The secure access to Table Store data here is based on the premise of RAM/STS authorization introduced earlier.

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 fig> 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;

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;