MaxCompute:Tablestore 外部テーブル

背景情報

Tablestore は、Alibaba Cloud Apsara 分散システム上に構築された NoSQL データストレージサービスです。Tablestore は、大量の構造化データへのストレージとリアルタイムアクセスを提供します。詳細については、「Tablestore ドキュメント」をご参照ください。

DataWorks を MaxCompute と組み合わせて使用すると、外部テーブルの作成、検索、クエリ、設定、処理、分析を視覚的に行うことができます。詳細については、「外部テーブル」をご参照ください。

注意事項

• MaxCompute と Tablestore 間のネットワーク接続を確保する必要があります。パブリッククラウドの MaxCompute から Tablestore データにアクセスする場合、Tablestore のプライベートエンドポイントを使用することをお勧めします。プライベートエンドポイントは ots-internal.aliyuncs.com で終わり、たとえば tablestore://odps-ots-dev.cn-shanghai.ots-internal.aliyuncs.com のようになります。

• Tablestore と MaxCompute は異なるデータ型システムを使用します。次の表に、Tablestore と MaxCompute でサポートされているデータ型のマッピングを示します。

  MaxCompute タイプ	Tablestore タイプ
  STRING	STRING
  BIGINT	INTEGER
  DOUBLE	DOUBLE
  BOOLEAN	BOOLEAN
  BINARY	BINARY

• Tablestore 外部テーブルはクラスタリング属性をサポートしていません。

前提条件

• Tablestore にアクセスするための権限が付与されている必要があります。権限付与の詳細については、「Tablestore の STS 権限付与」をご参照ください。

• Tablestore インスタンス、テーブル、およびデータが作成されている必要があります。詳細については、「ワイドテーブルモデルのクイックスタート」をご参照ください。

外部テーブルの作成

MaxCompute は外部テーブル機能を提供します。外部テーブルを使用して、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 で実行されます。前の例では、列名は odps_orderkey と odps_totalprice であり、Tablestore テーブルのプライマリキー列 o_orderkey と属性列 o_totalprice の名前ではありません。これは、マッピングが外部テーブルの作成に使用される DDL 文で定義されているためです。必要に応じて、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 テーブルです。internal_orders テーブルは、効率的に圧縮された列のストアを使用し、完全な内部マクロデータと統計情報を含みます。internal_orders テーブルは MaxCompute に保存されます。したがって、Tablestore テーブルよりも高速に internal_orders テーブルにアクセスできます。このメソッドは、複数回計算する必要があるデータに適しています。

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 は、キーと値のペアのフォーマットでデータを保存する NoSQL データストレージサービスです。MaxCompute からのデータ出力は、Tablestore テーブルのプライマリキーを含む行にのみ影響します。この例では、odps_orderkey と odps_orderdate を含む行のみが影響を受けます。ots_table_external テーブルの作成時に指定された属性列のみが更新されます。外部テーブルに含まれていない列は変更されません。

ダーティデータ処理の例

1. mf_ots_test という名前の Tablestore テーブルを作成し、データを準備します。詳細については、「ワイドテーブルモデルのクイックスタート」をご参照ください。

   次のサンプルコードは、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 external テーブルのデータをクエリします:

   -- データのクエリ
   SELECT * FROM mf_ots_external_permi;

   次の結果が返されます。エラーフィールドは JSON フォーマットで corrupt_col 列に書き込まれます。

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

   説明

   tablestore.read.mode が設定されていないか、permissive に設定されているが、ダーティデータを書き込む列を指定するために tablestore.corrupt.column が設定されていない場合、外部テーブルをクエリするとエラーメッセージ "Columns not match with columns mapping and corrupt column" が返されます。

よくある質問