Tablestore外部表

背景信息

表格存储（Tablestore）是构建在阿里云飞天分布式系统之上的NoSQL数据存储服务，提供海量结构化数据的存储和实时访问，详情请参见Tablestore文档。

您可以通过DataWorks配合MaxCompute对外部表进行可视化的创建、搜索、查询、配置、加工和分析。详情请参见外部表。

注意事项

• MaxCompute与Tablestore是两个独立的大数据计算和存储服务，所以两者之间的网络必须保证连通性。MaxCompute公共云服务访问Tablestore存储时，推荐您使用Tablestore私网地址，即Host名以ots-internal.aliyuncs.com作为结尾的地址，例如tablestore://odps-ots-dev.cn-shanghai.ots-internal.aliyuncs.com。

• Tablestore与MaxCompute都有其自身的类型系统。在MaxCompute处理Tablestore数据时，两者之间的数据类型对应关系如下所示。

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

• Tablestore外部表不支持cluster属性。

前提条件

• 已授权访问OTS的权限。具体授权操作详情，请参见OTS的STS模式授权。

• 已创建Tablestore实例、表和数据。具体操作，请参见通过控制台使用宽表模型。

创建外部表

MaxCompute通过创建外部表，把对Tablestore表数据的描述引入到MaxCompute的meta系统内部后，即可实现对Tablestore数据的处理。本节通过下述示例为您说明MaxCompute对接Tablestore的一些概念和实现。

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

查询外部表

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;

使用常见的MaxCompute SQL语句访问Tablestore时，所有的操作细节（例如列名的选择）是在MaxCompute内部处理完成的。上述SQL示例中，使用的列名是odps_orderkey、odps_totalprice等，而不是原始Tablestore中的主键名o_orderkey或属性列名o_totalprice。这是因为在创建外部表的DDL语句中，已经完成了对应的Mapping。您也可以在创建外部表时，按需选择保留原始的Tablestore主键/列名。

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

现在internal_orders就是一个MaxCompute表了，也拥有所有MaxCompute内部表的特性，包括高效的压缩列存储数据格式、完整的内部宏数据以及统计信息等。同时因为表存储在MaxCompute内部，所以访问速度会比访问外部的Tablestore更快。这种方法非常适用于需要进行多次计算的热点数据。

MaxCompute导出数据到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这种KV数据的NoSQL存储介质，MaxCompute的输出将只影响相对应主键所在的行，例如示例中只影响所有odps_orderkey + odps_orderdate这两个主键值对应行上的数据。而且在这些Tablestore行上，也只会更新在创建外部表（ots_table_external）时指定的属性列，而不会修改未在外部表中出现的数据列。

更多详情请参见MaxCompute访问TableStore（OTS）数据。

脏数据处理示例

1. 准备Tablestore表mf_ots_test和数据。具体操作，请参见通过控制台使用宽表模型。

   默认Tablestore的表格数据如下所示。

   +----+------+------+
   | id | name | desc |
   +----+------+------+
   | 1  | 张三 | 张三的描述 |
   +----+------+------+

2. 创建MaxCompute外部表。

   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. 执行以下代码，查询MaxCompute外部表数据。

   ---查询数据
   select * from mf_ots_external_permi;

   返回结果如下，错误字段会以JSON格式写入至corrupt_col列中。

   --
   +------------+------------+------------+-------------+
   | id         | name       | desc       | corrupt_col |
   +------------+------------+------------+-------------+
   | 1          | NULL       | 张三的描述      | {"name": "\"张三\""} |
   +------------+------------+------------+-------------+

   说明

   若tablestore.read.mode未配置或配置为permissive，但未配置tablestore.corrupt.column指定脏数据存入列，查询外表时会报错“Columns not match with columns mapping and corrupt column”。