MaxCompute：Tablestore外部表格

背景資訊

Table Store（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的許可權。具體授權操作詳情，請參見訪問外部資料源授權方案。

• 已建立Tablestore執行個體、表和資料。具體操作，請參見寬表模型快速入門。

許可權說明

• 訪問 Tablestore 外部表格時，無論使用阿里雲帳號（主帳號）、RAM 使用者還是 RAM Role 身份，均通過 odps.properties.rolearn 參數指定角色的許可權訪問 Tablestore 資料。因此，需要建立 RAM 角色並授予其訪問 Tablestore 的許可權，再將該角色的 ARN 配置到 odps.properties.rolearn 參數中。

• 建立角色支援一鍵授權和自訂授權兩種方式，詳情請參見OSS/OTS的RAM普通角色授權。odps.properties.rolearn 參數的詳細說明請參見參數說明。

建立外部表格

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/<role_name>',
  '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）時指定的屬性列，而不會修改未在外部表格中出現的資料列。

髒資料處理樣本

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”。

常見問題