MaxCompute:JSON データ型

背景情報

半構造化データは、構造化データと非構造化データの中間に位置します。このタイプのデータにはスキーマがありますが、スキーマは柔軟で強い制約はありません。スキーマは通常、自己記述的です。JSON データはその典型的な例です。MaxCompute はすでに、スキーマ進化、JSON 文字列、複合型用のビルトイン関数、ラムダ式などの特徴で、半構造化データに対する SQL サポートを強化しています。このモデルでは、半構造化データを標準化された方法で処理してから、定義されたスキーマを持つ構造化テーブルにインポートする必要があります。ビジネスデータが変更された場合は、データ定義言語 (DDL) 文を明示的に実行してテーブルスキーマを変更する必要があります。

このモデルはスキーマによる制約が強く、半構造化データを迅速にシステムにインポートすることはできません。データインポート中に、テーブルスキーマに準拠しないデータは破棄され、完全に保存することはできません。これらの問題を解決するために、MaxCompute は新しいデータ型である JSON を提供します。JSON 型は、強力なスキーマ制約なしで半構造化データをサポートします。また、列指向ストレージの最適化を最大限に活用して、高い柔軟性と高いパフォーマンスの要件を満たします。

基本原則

JSON データ型は、他の型と同じように使用できる新しいデータ型です。スキーマ情報を管理する必要はありません。JSON データを挿入すると、MaxCompute は自動的に公開スキーマを抽出し、最適化を実行します。MaxCompute は、可能な限りデータを列指向フォーマットで保存し、パフォーマンスを向上させます。次の例では、テストデータを使用します。

CREATE TABLE json_table
(
    json_val  json
);

CREATE TABLE string_table
(
    string_val  STRING
);

INSERT INTO string_table VALUES
        ('{"a":1, "b":2}')
        ,('{"a":"key", "b":2}')
        ,('{"c":3}');

INSERT INTO json_table
SELECT  json_parse(string_val)
FROM    string_table;

データを書き込む際、MaxCompute は自動的に公開スキーマ <"a":binary, "b":bigint, "c":bigint> を抽出します。データを読み取る際、MaxCompute はスキーマに基づいて列プルーニングを実行できます。これにより、読み取る必要のあるデータ量が削減され、効率が向上します。例：

SELECT  json_val["b"]
        ,json_val["c"]
FROM    json_table
;  
-- テーブルを読み取る際、列プルーニングが実行され、b と c の変数のみが保持されます。
+-----+-----+
| _c0 | _c1 |
+-----+-----+
| 2   | NULL |
| 2   | NULL |
| NULL | 3   |
+-----+-----+

スキーマの非公開部分については、MaxCompute は BINARY 型を使用して保存します。これにより、STRING 型と比較してストレージスペースが削減されます。また、新しい JSON データ型は、ユーザー定義関数 (UDF) と比較して、STRING と JSON 間の変換効率を大幅に向上させます。

適用範囲

• SDK のバージョン要件

  • Java SDK V0.44.0 以降のバージョンのみがサポートされています。

  • PyODPS V0.11.4.1 以降のバージョンのみがサポートされています。

• テーブル操作の制限

  • テーブルに JSON 列を追加することはできません。

  • クラスター化テーブルはサポートされていません。

  • Delta Table 型のテーブルはサポートされていません。

• SQL 操作の制限

  • JSON 型での比較操作はサポートされていません。

  • JSON 型に対して ORDER BY 句や GROUP BY 句を使用することはできません。また、JSON 型の列を JOIN キーとして使用することもできません。

• データ精度

  • JSON NUMBER の整数部分は BIGINT 型として保存されます。整数が BIGINT の範囲外の場合、オーバーフローが発生します。

  • JSON NUMBER の小数部分は DOUBLE 型として保存されます。小数部分を DOUBLE 型に変換する際に、精度が失われる可能性があります。

• 文字の制限：JSON データの生成に使用される文字列では、Unicode 文字 \u0000 はサポートされていません。

• エンジンの互換性：Hologres などの別のエンジンを使用してテーブルからデータを読み取る場合、JSON データ型は読み取れません。

• Java UDF と Python UDF は JSON 型をサポートしていません。

• JSON データ型は最大 20 レベルまでネストできます。

• 開発ツール

  サポートされている開発ツールには、MaxCompute クライアント (odpscmd)、MaxCompute Studio、DataWorks があります。Dataphin などの外部エコシステムはサポートされていません。外部システムで JSON データ型を使用する場合は、開始する前にその互換性を確認する必要があります。odpscmd クライアントを使用する場合は、次の点に注意してください。

  • クライアントを V0.46.5 以降にアップグレードする必要があります。そうしないと、DESC json_table コマンドを実行したり、Tunnel を使用して JSON データをダウンロードしたりできません。

  • クライアントのインストールパスにある conf\odps_config.ini ファイルで、use_instance_tunnel パラメーターを false に設定します。そうしないと、クエリが失敗します。

リテラル定数

JSON 型は、JSON 標準に厳密に従って定義されています。BOOLEAN、NUMBER、STRING、NULL、ARRAY、OBJECT をサポートしています。NUMBER 型は、BIGINT と DOUBLE を使用して保存されます。制限を超える値は、精度が失われる可能性があります。json 'null' は sql null とは異なることに注意してください。

JSON 'null'
JSON '123'
JSON '123.34'
JSON 'true'
JSON '{"id":123,"name":"MaxCompute"}'
JSON '[12, 34]'

定数は JSON 標準に準拠している必要があります。たとえば、JSON '{id:123,"name":"MaxCompute"}' は、キー id が二重引用符 ("") で囲まれていないため、無効な JSON 文字列です。

JSON 型の定義

スキーマを指定する必要はありません。基本データ型と同じように JSON 型を作成できます。

CREATE TABLE mf_json_table (json_val JSON);

JSON データの生成

いくつかの方法で JSON データを生成できます。

• JSON リテラル

  INSERT INTO mf_json_table VALUES (json '123');

• JSON 関数

  -- JSON_OBJECT と JSON_ARRAY は MaxCompute のビルトイン関数です。
  INSERT INTO mf_json_table SELECT JSON_OBJECT("key",123, "value", "abc");
  
  SELECT * FROM mf_json_table;
  
  -- 次の結果が返されます。
  +----------+
  | json_val |
  +----------+
  | 123      |
  | {"key":123,"value":"abc"} |
  +----------+
  
  
  INSERT INTO mf_json_table SELECT JSON_ARRAY("key",234, "value", "abc");
  
  SELECT * FROM mf_json_table;
  
  -- 次の結果が返されます。
  +----------+
  | json_val |
  +----------+
  | 123      |
  | ["key",234,"value","abc"] |
  | {"key":123,"value":"abc"} |
  +----------+

• 型変換

  CAST を使用したデータ変換と、json_parse を使用したデータ解析の違いに注意してください。詳細については、「JSON 関数」をご参照ください。

  INSERT INTO mf_json_table SELECT CAST("abc" AS json);
  SELECT * FROM mf_json_table;
  -- 次の結果が返されます。
  +----------+
  | json_val |
  +----------+
  | 123      |
  | "abc"    |
  | ["key",234,"value","abc"] |
  | {"key":123,"value":"abc"} |
  +----------+

JSON データへのアクセス

インデックス、または json_extract 関数と get_json_object 関数を使用して JSON データにアクセスできます。これらの関数は JSON 型を返します。

-- 123 が返されます
SELECT v['id'] 
  FROM VALUES (JSON '{"id":123}') AS t(v);
  
-- 12 が返されます
SELECT v[0] 
  FROM VALUES (JSON '[12, 34]') AS t(v);
  
-- 1 が返されます
SELECT v['x']['a']  FROM VALUES (json '{"x": {"a": 1, "b": 2}}') AS t(v);

-- NULL が返されます
SELECT v[0] 
FROM VALUES (JSON '{"id":123}') AS t(v);

-- NULL が返されます
SELECT v['not_exists'] 
FROM VALUES (JSON '{"id":123}') AS t(v);

-- GET_JSON_OBJECT 関数を使用してデータにアクセスします。STRING 値 'MaxCompute' が返されます。
SELECT GET_JSON_OBJECT(v, '$.x.name')
  FROM VALUES (JSON '{"x": {"id": 1001, "name": "MaxCompute"}}') AS t(v);
  
-- 次の結果が返されます。
+-----+
| _c0 |
+-----+
| MaxCompute |
+-----+

-- JSON_EXTRACT 関数を使用してデータにアクセスします。JSON 値 'MaxCompute' が返されます。
SELECT JSON_EXTRACT(v, '$.x.name') 
  FROM VALUES (JSON '{"x": {"id": 1001, "name": "MaxCompute"}}') AS t(v);
  
-- 次の結果が返されます。
+-----+
| _c0 |
+-----+
| "MaxCompute" |
+-----+