MaxCompute:Create a Tablestore external table

You can use one of the following methods to grant a user the required permissions to access data in Tablestore:

• One-click authorization. If the MaxCompute project and the Tablestore instance that you want to use belong to the same Alibaba Cloud account, you must log on to the Alibaba Cloud Management Console by using the Alibaba Cloud account and then perform one-click authorization on the Cloud Resource Access Authorization page of the RAM console. For more information, see Cloud Resource Access Authorization.

• Custom 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 the MaxCompute project and the Tablestore instance that you want to use belong to different Alibaba Cloud accounts, you must log on to the RAM console by using the Alibaba Cloud account to which the Tablestore instance belongs.

  2. Modify policy settings.

     • If the MaxCompute project and the Tablestore instance that you want to use belong to the same Alibaba Cloud account, use the following policy configuration:

       {
       "Statement": [
       {
        "Action": "sts:AssumeRole",
        "Effect": "Allow",
        "Principal": {
          "Service": [
            "odps.aliyuncs.com"
          ]
        }
       }
       ],
       "Version": "1"
       }

     • If the MaxCompute project and the Tablestore instance that you want to use belong to different Alibaba Cloud accounts, use the following policy configuration:

       {
       "Statement": [
       {
        "Action": "sts:AssumeRole",
        "Effect": "Allow",
        "Principal": {
          "Service": [
            "ID of the Alibaba Cloud account to which the MaxCompute project belongs@odps.aliyuncs.com"
          ]
        }
       }
       ],
       "Version": "1"
       }

     You can click your profile picture in the upper-right corner of the current page to view the ID of your Alibaba Cloud account.

  3. Edit the AliyunODPSRolePolicy policy for the role. You can also specify other permissions based on your business requirements.

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

When you create an external table, the table name and field names are not case-sensitive. Sample statements:

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

Description:

• com.aliyun.odps.TableStoreStorageHandler is a built-in MaxCompute storage handler that is used to process Tablestore data. The storage handler defines the interaction between MaxCompute and Tablestore. The related logic is implemented by MaxCompute.

• SERDEPROPERITES provides parameters. If you use TableStoreStorageHandler, you must configure three required parameters. You can also configure two optional parameters based on your business requirements.

  • Three required parameters:

    • tablestore.columns.mapping: the columns of the Tablestore table that you want 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 in the preceding example. Other columns are attribute columns.

      • A Tablestore table supports up to four primary key columns. The data types of the primary key columns must be STRING, INTEGER, or BINARY. The first primary key column is the partition key column.

      • You must specify all primary key columns of the Tablestore table and the attribute columns that you want to access. Make sure that all the attribute columns that you specify are in the Tablestore table. Otherwise, errors are returned when you query data by using the external table.

    • tablestore.table.name: the name of the Tablestore table that you want to access. If you specify an invalid table name or the table name that you specify does not exist, an error is returned. MaxCompute does not actively create a Tablestore table.

    • odps.properties.rolearn: the Alibaba Cloud Resource Name (ARN) of the AliyunODPSDefaultRole role in RAM. You can obtain the ARN on the RAM Roles page of the RAM console.

  • Three optional parameters:

    • tablestore.timestamp.ticks.unit: the time unit for tables. This parameter specifies that all fields of the INTEGER data type in the external table use the same time unit. Valid values: seconds, millis, micros, and nanos.

    • tablestore.column.<col1_name>.timestamp.ticks.unit: the time unit for columns. This parameter specifies the time unit of a specific column in the external table. Valid values: seconds, millis, micros, and nanos.

    • tablestore.table.put.row: specifies the write mode of the PutRow operation. Valid values: True and False. Default value: False.

    Note

    • You can use the preceding two parameters to map the fields of the INTEGER data type in a Tablestore table to the fields of the TIMESTAMP data type in a MaxCompute table. If both parameters are specified, the tablestore.column.<col1_name>.timestamp.ticks.unit parameter has a higher priority than the tablestore.timestamp.ticks.unit parameter.

    • You can configure the following flag parameter to specify the write mode of the PutRow operation. The default value is False. For more information, see Flag parameters.

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

• LOCATION specifies the name and endpoint of the Tablestore instance. You must complete RAM or STS authorization to ensure secure access to Tablestore data.

  Note

  If an error indicating inconsistent network types is returned when you use the public endpoint, you can change the network type to the classic network.

You can execute the following statement to view the structure of the external table that you created:

desc extended <table_name>;

After you create an external table, you can execute a MaxCompute SQL statement to access Tablestore data by using the external table. 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 you want to compute the data by using MaxCompute. Example:

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 stored in an internal MaxCompute table named internal_orders. 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;