MaxCompute:Tablestore external tables

Background information

Tablestore is a NoSQL data storage service built on the Alibaba Cloud Apsara distributed system. Tablestore provides storage and real-time access to massive amounts of structured data. For more information, see Tablestore documentation.

You can create, search for, configure, and process external tables in the DataWorks console. You can also query and analyze data using the external table feature. For more information, see External tables.

Precautions

• You must ensure network connectivity between MaxCompute and Tablestore. When you access Tablestore data from MaxCompute in the public cloud, we recommend that you use the private endpoint of Tablestore. The private endpoint ends with ots-internal.aliyuncs.com, for example, tablestore://odps-ots-dev.cn-shanghai.ots-internal.aliyuncs.com.

• Tablestore and MaxCompute use different data type systems. The following table lists the mappings between the data types supported by Tablestore and MaxCompute.

  MaxCompute Type	Tablestore Type
  STRING	STRING
  BIGINT	INTEGER
  DOUBLE	DOUBLE
  BOOLEAN	BOOLEAN
  BINARY	BINARY

• Tablestore external tables do not support the clustering attribute.

Prerequisites

• You are granted the permissions to access Tablestore. For more information about authorization, see STS authorization for Tablestore.

• A Tablestore instance, table, and data are created. For more information, see Quick Start for wide table model.

Create a foreign table

MaxCompute provides the external table feature. You can use external tables to import data from Tablestore to the meta system of MaxCompute for processing. The following section describes how to create a Tablestore 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,
  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.read.mode'='permissive',
  'tablestore.corrupt.column'='ColumnName',
  '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';

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

DESC extended <table_name>;

Query data in the foreign table

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 access Tablestore data using MaxCompute SQL statements, all operations, such as the selection of column names, are performed in MaxCompute. In the preceding example, the column names are odps_orderkey and odps_totalprice rather than the names of the primary key column o_orderkey and attribute column o_totalprice in the Tablestore table. This is because the mappings are defined in the DDL statement used to create the external table. You can also retain the names of the primary key columns and attribute columns in the Tablestore table as needed.

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

internal_orders is a MaxCompute table that supports all features of a MaxCompute internal table. The internal_orders table uses the efficiently compressed column store and contains complete internal macro data and statistical information. The internal_orders table is stored in MaxCompute. Therefore, you can access the internal_orders table faster than a Tablestore table. This method is suitable for data that needs to be computed multiple times.

Export data from MaxCompute to Tablestore

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;

Tablestore is a NoSQL data storage service that stores data in the key-value pair format. Data outputs from MaxCompute affect only the rows that include the primary keys of the Tablestore table. In this example, only the rows that include odps_orderkey and odps_orderdate are affected. Only the attribute columns that are specified when you create the ots_table_external table are updated. The columns that are not included in the external table are not modified.

Examples of dirty data processing

1. Create a Tablestore table named mf_ots_test and prepare data. For more information, see Quick Start for wide table model.

   The following sample code shows the default data of the Tablestore table.

   +----+------+------+
   | id | name | desc |
   +----+------+------+
   | 1  | Jack | Description of Jack |
   +----+------+------+

2. Create a MaxCompute external table.

   CREATE EXTERNAL TABLE IF NOT EXISTS mf_ots_external_permi
   (
    id string,
   	name bigint,
   	desc string,
   	corrupt_col string
   )
   STORED BY 'com.aliyun.odps.TableStoreStorageHandler'
   WITH SERDEPROPERTIES (
     'tablestore.columns.mapping'=':id,name,desc',
     'tablestore.table.name'='mf_ots_test',
     'tablestore.read.mode'='permissive',
     'tablestore.corrupt.column'='corrupt_col',
     'odps.properties.rolearn'='acs:ram::139699392458****:role/aliyunodpsdefaultrole'
   )
   LOCATION 'tablestore://santie-doc.cn-shanghai.ots-internal.aliyuncs.com';

3. Run the following code to query data in the MaxCompute external table:

   ---Query data
   SELECT * FROM mf_ots_external_permi;

   The following result is returned. The error field is written to the corrupt_col column in the JSON format.

   --
   +------------+------------+------------+-------------+
   | id         | name       | desc       | corrupt_col |
   +------------+------------+------------+-------------+
   | 1          | NULL       | Description of Jack      | {"name": "\"Jack\""} |
   +------------+------------+------------+-------------+

   Note

   If tablestore.read.mode is not configured or is set to permissive but tablestore.corrupt.column is not configured to specify the column to which dirty data is written, the error message "Columns not match with columns mapping and corrupt column" is returned when you query the external table.