Access Tablestore data

You can use one of the following methods to grant permissions:

• If MaxCompute and Tablestore are under the same Alibaba Cloud account, log on to the Alibaba Cloud Management Console and click here to perform one-click authorization.
• Customize authorization.
  1. Authorize access from MaxCompute to Tablestore in the RAM console.

     Log on to the RAM console and create a role such as AliyunODPSDefaultRole or AliyunODPSRoleForOtherUser. If MaxCompute and Tablestore are not under the same account, log on to the RAM console by using the Tablestore account.

  2. Modify policy settings.

     -- If MaxCompute and Tablestore are under the same account, use the following policy configuration:
     {
     "Statement": [
     {
      "Action": "sts:AssumeRole",
      "Effect": "Allow",
      "Principal": {
        "Service": [
          "odps.aliyuncs.com"
        ]
      }
     }
     ],
     "Version": "1"
     }
     -- If MaxCompute and Tablestore are not under the same account, use the following policy configuration:
     {
     "Statement": [
     {
      "Action": "sts:AssumeRole",
      "Effect": "Allow",
      "Principal": {
        "Service": [
          "ID of the Alibaba Cloud account of MaxCompute@odps.aliyuncs.com"
        ]
      }
     }
     ],
     "Version": "1"
     }

     Note You can click your profile picture in the upper-right corner of the page that appears to view the ID.
  3. Edit the AliyunODPSRolePolicy authorization policy for the role.

     {
     "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 AliyunODPSRolePolicy permission to the role.

Execute the following statement 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', -- 1
'tablestore.table.name'='ots_tpch_orders' -- 2
'odps.properties.rolearn'='acs:ram::xxxxx:role/aliyunodpsdefaultrole' --3
)
LOCATION 'tablestore://odps-ots-dev.cn-shanghai.ots-internal.aliyuncs.com'; -- (3)

Description:

• com.aliyun.odps.TableStoreStorageHandler is a built-in MaxCompute storage handler that is used to process Tablestore data. It defines the interaction between MaxCompute and Tablestore. The related logic is implemented by MaxCompute.
• SERDEPROPERITES provides parameter options. If you use TableStoreStorageHandler, you must specify tablestore.columns.mapping, tablestore.table.name, and odps.properties.rolearn.
  1. tablestore.columns.mapping: the columns of the Tablestore table that MaxCompute needs to access. The columns include primary key columns and attribute columns.
     • The column whose name starts with a colon (:) is a primary key column, such as :o_orderkey and :o_orderdate. Other columns are attribute columns.
     • A Tablestore table supports a maximum of four primary key columns. The data types of these columns must be STRING, INTEGER, or BINARY. The first primary key column is the partition key.
     • If you specify column mappings, you must specify all primary key columns of the Tablestore table. You only need to specify the attribute columns that MaxCompute needs to access instead of specifying all attribute columns. The specified attribute columns must be in the Tablestore table. Otherwise, errors are returned when you query the new external table.
  2. tablestore.table.name: the name of the Tablestore table that MaxCompute needs to access. If the table name does not exist in Tablestore, an error is returned. MaxCompute does not create a Tablestore table.
  3. odps.properties.rolearn: the ARN of AliyunODPSDefaultRole in RAM. You can obtain the ID on the RAM Roles page of the RAM console.
• LOCATION specifies the name and endpoint of the Tablestore instance. You must complete RAM or STS authorization to ensure secure access to Tablestore data.

You can execute the following statement to view the structure of the new external table:

desc extended <table_name>;

After you create an external table, Tablestore data is imported to MaxCompute. Then, you can use MaxCompute SQL syntax to access Tablestore data. Example:

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 want to compute one piece of data multiple times, you can import the data from Tablestore to an internal table of MaxCompute. This way, you do not need to read the data from Tablestore every time.

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

An external table named ots_table_external is created to allow MaxCompute to access the ots_tpch_orders table in Tablestore. The data is also stored in the internal_orders table of MaxCompute. If you want to process data in the internal_orders table and export the processed data to Tablestore, execute the INSERT OVERWRITE TABLE statement. Example:

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;