MaxCompute:JSON 外部テーブル

構文

JSON ファイルのカラム数が外部テーブル定義よりも少ない場合、MaxCompute は不足しているカラムに NULL を設定します。ファイルのカラム数が定義よりも多い場合、MaxCompute は余分なカラムを破棄します。

CREATE EXTERNAL TABLE <mc_oss_extable_name>
(
  <col_name> <data_type>,
  ...
)
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type>, ...)]
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS textfile
LOCATION '<oss_location>';

CREATE EXTERNAL TABLE <mc_oss_extable_name>
(
  <col_name> <data_type>,
  ...
)
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type>, ...)]
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
  [WITH serdeproperties (
    ['<property_name>'='<property_value>',...])
  ]
STORED AS textfile
LOCATION '<oss_location>'
[tblproperties ('<tbproperty_name>'='<tbproperty_value>',...)];

共通パラメーター

詳細については、「共通構文パラメーター」をご参照ください。

排他的パラメーター

例

1. データの準備

   ダーティデータを含むテストデータ json_bad_row_skipping.json を OSS の oss-mc-test/badrow/ ディレクトリにアップロードします。

2. JSON 外部テーブルの作成

   動作はテーブルレベルのプロパティとセッションレベルのフラグによって異なります。以下の 3 つのケースが考えられます。

   • テーブルパラメーター: odps.text.option.bad.row.skipping = flexible/rigid/<未指定>

   • セッションフラグ: odps.sql.unstructured.text.bad.row.skipping = on/off/<未設定>

   テーブルレベルのプロパティなし

   -- テーブルレベルのプロパティが設定されていないため、エラー処理はセッションレベルのフラグに基づいて行われます。
   CREATE EXTERNAL TABLE test_json_bad_data_skipping_flag
   (
     a INT,
     b INT
   )
   row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
   stored AS textfile
   location '<oss://databucketpath>';

   柔軟なスキップ

   -- テーブルレベルのプロパティによりエラー行がスキップされますが、セッションレベルのフラグでこの動作を無効化できます。
   CREATE EXTERNAL TABLE test_json_bad_data_skipping_flexible
   (
     a INT,
     b INT
   )
   row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
   stored AS textfile
   location '<oss://databucketpath>'
   tblproperties (
     'odps.text.option.bad.row.skipping' = 'flexible'   -- 柔軟モード。セッションレベルで無効化可能。
   );

   厳格なスキップ

   -- テーブルレベルのプロパティによりエラーのスキップが強制されます。この動作はセッションレベルで無効化できません。
   CREATE EXTERNAL TABLE test_json_bad_data_skipping_rigid
   (
     a INT,
     b INT
   )
   row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
   stored AS textfile
   location '<oss://databucketpath>'
   tblproperties (
     'odps.text.option.bad.row.skipping' = 'rigid'  -- 強制有効。
   );

3. クエリ結果の確認

   テーブルレベルのプロパティなし

   -- セッションレベルで有効化。
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- セッションレベルで無効化。テーブルプロパティが 'flexible' の場合は無効になります。'rigid' の場合はこの設定は無効です。
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- 問題のある行を出力。最大 1,000 行。値が 0 以下の場合、出力は無効になります。
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_json_bad_data_skipping_flag;

   セッションレベルでエラーのスキップが無効になっている場合 (SET odps.sql.unstructured.text.bad.row.skipping=off)、次のエラーでクエリが失敗します： FAILED: ODPS-0123131:User defined function exception

   柔軟なスキップ

   -- セッションレベルで有効化。
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- セッションレベルで無効化。テーブルプロパティが 'flexible' の場合は無効になります。'rigid' の場合はこの設定は無効です。
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- 問題のある行を出力。最大 1,000 行。値が 0 以下の場合、出力は無効になります。
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_json_bad_data_skipping_flexible;

   セッションレベルでエラーのスキップが無効になっている場合 (SET odps.sql.unstructured.text.bad.row.skipping=off)、次のエラーでクエリが失敗します： FAILED: ODPS-0123131:User defined function exception

   厳格なスキップ

   -- セッションレベルで有効化。
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- セッションレベルで無効化。テーブルプロパティが 'flexible' の場合は無効になります。'rigid' の場合はこの設定は無効です。
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- 問題のある行を出力。最大 1,000 行。値が 0 以下の場合、出力は無効になります。
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_json_bad_data_skipping_rigid;

   次の結果が返されます。

   +------------+------------+
   | a          | b          | 
   +------------+------------+
   | 1          | 2          | 
   | 15         | 16         | 
   +------------+------------+

前提条件

1. MaxCompute プロジェクトが作成済みであること。詳細については、「MaxCompute プロジェクトの作成」をご参照ください。

2. OSS バケットおよびディレクトリが準備済みであること。詳細については、「バケットの作成」および「ディレクトリの管理」をご参照ください。

   MaxCompute は一部のリージョンでのみ提供されています。クロスリージョンのデータ接続に関する問題を回避するため、MaxCompute プロジェクトと同じリージョンにあるバケットを使用することを推奨します。

3. 必要な権限が付与済みであること：

   1. OSS へのアクセス権限。Alibaba Cloud アカウント、RAM ユーザー、または RAM ロールを使用して OSS 外部テーブルにアクセスできます。権限付与の詳細については、「OSS の STS モードによる権限付与」をご参照ください。

   2. MaxCompute プロジェクトにおける CreateTable 権限。テーブル操作権限の詳細については、「MaxCompute 権限」をご参照ください。

例 1：JSON テーブルの作成、書き込み、クエリ

この例では、組み込みのオープンソースデータパーサを使用して JSON 外部テーブルを作成し、OSS にデータを書き込んでからデータをクエリする方法を示します。

1. データの準備

   OSS コンソール にログインし、テストデータファイル json2025.txt を OSS バケット内の external-table-test/json/dt=20250521/ ディレクトリにアップロードします。詳細については、「OSS へのファイルのアップロード」をご参照ください。

2. JSON 外部テーブルの作成

   CREATE EXTERNAL TABLE mc_oss_extable_name_json
   (
     action STRING,
     time STRING
   )
   PARTITIONED BY (dt STRING)
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   STORED AS textfile
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/external-table-test/json/';

3. OSS 外部テーブルがパーティションテーブルの場合、パーティションデータもインポートする必要があります。詳細については、「OSS 外部テーブル」をご参照ください。

   -- パーティションを追加。
   MSCK REPAIR TABLE mc_oss_extable_name_json ADD PARTITIONS;

4. JSON 外部テーブルからのデータの読み取り

   SELECT * FROM mc_oss_extable_name_json WHERE dt=20250526;

   次の結果が返されます。

   +------------+------------+------------+
   | action     | time       | dt         |
   +------------+------------+------------+
   | Close      | 1469679568 | 20250526   |
   | Close      | 1469679568 | 20250526   |
   +------------+------------+------------+

5. JSON 外部テーブルへのデータの書き込み

   INSERT INTO mc_oss_extable_name_json PARTITION (dt='20250526') VALUES ('test','1627273823');

6. 書き込まれたデータの確認

   SELECT * FROM mc_oss_extable_name_json WHERE dt=20250526;

   次の結果が返されます。

   +------------+------------+------------+
   | action     | time       | dt         |
   +------------+------------+------------+
   | test       | 1627273823 | 20250526   |
   | Close      | 1469679568 | 20250526   |
   | Close      | 1469679568 | 20250526   |
   +------------+------------+------------+

例 2：ネストされた JSON フィールドの読み取り

データの準備

JSON ファイル events.json を作成します。

{"a":{"x":1, "y":2}, "id":"123"}
{"a":{"x":3, "y":4}, "id":"345"}

OSS コンソール にログインし、テストデータを OSS バケット内の external-table-test/json-sturct/ ディレクトリにアップロードします。詳細については、「OSS へのファイルのアップロード」をご参照ください。

方法 1: TEXTFILE 外部テーブルを作成し、get_json_object 関数を使用してフィールド値を読み取ります。

1. string 型の単一カラムのみを含む TEXTFILE 外部テーブルを作成します。

   CREATE EXTERNAL TABLE extable_json_test01 (
     col STRING
   )
   ROW FORMAT DELIMITED FIELDS TERMINATED BY '\n'
   STORED AS textfile
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/external-table-test/json_struct/';  
   
   SELECT * FROM extable_json_test01;      

   次の結果が返されます。

   +------------------------------------+
   |               col                  |
   +------------------------------------+
   | {"a": {"x": 1, "y": 2},"id":"123"} |
   | {"a": {"x": 3, "y": 4},"id":"345"} |
   +------------------------------------+

2. get_json_object 関数を使用して a および id フィールドを読み取ります。

   SELECT 
       get_json_object(col, '$.a') AS a,
       get_json_object(col, '$.id') AS id
   FROM extable_json_test01;         

   次の結果が返されます。

   +-------------------+-----+
   |        a          | id  |
   +-------------------+-----+
   | {"x":1,"y":2}     | 123 |
   | {"x":3,"y":4}     | 345 |
   +-------------------+-----+

3. ネストされたフィールド x、y、および id を読み取ります。

   SELECT 
       get_json_object(get_json_object(col,'$.a'),'$.x') AS x,
       get_json_object(get_json_object(col,'$.a'),'$.y') AS y,
       get_json_object(col,'$.id') AS id
   FROM extable_json_test01;          

   次の結果が返されます。

   +---+---+-----+
   | x | y | id  |
   +---+---+-----+
   | 1 | 2 |123  |
   | 3 | 4 |345  |
   +---+---+-----+       

方法 2: JSON 外部テーブルを作成し、STRUCT 型を使用してデータを受け取ります。

1. JSON 形式の外部テーブルを作成し、STRUCT 型を使用してネストされたフィールドを受け取ります。

   CREATE EXTERNAL TABLE extable_json_test02
   (
     a STRUCT<x: BIGINT, y: BIGINT>,
     id STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   STORED AS textfile
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/external-table-test/json_struct/';          

2. テーブル内のデータを直接クエリします。

   SELECT * FROM extable_json_test02;

   次の結果が返されます。

   +----------+-----+
   |    a     | id  |
   +----------+-----+
   | {x:1, y:2}|123 |
   | {x:3, y:4}|345 |
   +----------+-----+

3. get_json_object 関数および TO_JSON 関数を使用して x および y フィールドを読み取ることもできます。

   SELECT 
       get_json_object(TO_JSON(a), '$.x') AS x,
       get_json_object(TO_JSON(a), '$.y') AS y,
       id
   FROM extable_json_test02;         

   次の結果が返されます。

   +---+---+-----+
   | x | y | id  |
   +---+---+-----+
   | 1 | 2 |123  |
   | 3 | 4 |345  |
   +---+---+-----+       

例 3：出力ファイル名のカスタマイズ

1. OSS に書き込まれるファイルのカスタムプレフィックスを test06_ に設定します。DDL は次のとおりです。

   CREATE EXTERNAL TABLE  <mc_oss_extable_name>
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongitude DOUBLE,
     recordTime STRING,
     direction STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   STORED AS textfile
   LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/***/'
   TBLPROPERTIES (
   -- カスタムプレフィックスを追加。
       'odps.external.data.output.prefix'='test06_') 
   ;
   
   -- 外部テーブルにデータを書き込み。
   INSERT INTO  <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

   書き込み操作後に生成されたファイルを次の図に示します。

   

2. OSS に書き込まれるファイルのサフィックスを _beijing にカスタマイズする場合、DDL は次のとおりです。

   CREATE EXTERNAL TABLE <mc_oss_extable_name>
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongitude DOUBLE,
     recordTime STRING,
     direction STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   STORED AS textfile
   LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/***/'
   TBLPROPERTIES (
   -- カスタムサフィックスを追加。
       'odps.external.data.output.suffix'='_beijing') 
   ;
   
   -- 外部テーブルにデータを書き込み。
   INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

   書き込み操作後に生成されたファイルを次の図に示します。

   

3. 出力ファイルに自動的にファイル拡張子を生成する場合、次の DDL を使用します。

   CREATE EXTERNAL TABLE <mc_oss_extable_name>
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongitude DOUBLE,
     recordTime STRING,
     direction STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   STORED AS textfile
   LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/***/'
   TBLPROPERTIES (
   -- ファイル拡張子を自動生成。
       'odps.external.data.enable.extension'='true') 
   ;
   
   -- 外部テーブルにデータを書き込み。
   INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

   書き込み操作後に生成されたファイルを次の図に示します。

4. OSS に書き込まれるファイルのファイル拡張子を jsonl にカスタマイズする場合、DDL は次のとおりです。

   CREATE EXTERNAL TABLE <mc_oss_extable_name>
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongitude DOUBLE,
     recordTime STRING,
     direction STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   STORED AS textfile
   LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/***/'
   TBLPROPERTIES (
   -- カスタムファイル拡張子を追加。
      'odps.external.data.output.explicit.extension'='jsonl') 
   ;
   
   -- 外部テーブルにデータを書き込み。
   INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

   書き込み操作後に生成されたファイルを次の図に示します。

   

5. OSS に書き込まれるファイルに対して、プレフィックスを mc_、サフィックスを _beijing、ファイル拡張子を jsonl に設定します。DDL は次のとおりです。

   CREATE EXTERNAL TABLE <mc_oss_extable_name>
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongitude DOUBLE,
     recordTime STRING,
     direction STRING
   )
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   STORED AS textfile
   LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/***/'
   TBLPROPERTIES (
       -- カスタムプレフィックスを追加。
       'odps.external.data.output.prefix'='mc_', 
       -- カスタムサフィックスを追加。
       'odps.external.data.output.suffix'='_beijing', 
       -- カスタムファイル拡張子を追加。
       'odps.external.data.output.explicit.extension'='jsonl') 
   ;  
   
   -- 外部テーブルにデータを書き込み。
   INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

   書き込み操作後に生成されたファイルを次の図に示します。

   

エラー：Unexpected end-of-input: expected close marker for OBJECT

• エラーメッセージ

  ODPS-0123131:User defined function exception - Traceback:
  com.aliyun.odps.serde.SerDeException: org.apache.hadoop.hive.serde2.SerDeException: org.codehaus.jackson.JsonParseException: Unexpected end-of-input: expected close marker for OBJECT (from [Source: java.io.ByteArrayInputStream@5a021cb9; line: 1, column: 0])
   at [Source: java.io.ByteArrayInputStream@5a021cb9; line: 1, column: 3]
  	at com.aliyun.odps.hive.wrapper.HiveSerDeWrapper.deserialize(HiveSerDeWrapper.java:122)
  	at com.aliyun.odps.udf.HiveReaderHandler.next(HiveReaderHandler.java:152)
  	at com.aliyun.odps.udf.HiveReaderHandler5c9b68c118d14fb2b392e8d916ddea8c.next(Unknown Source)

• 原因

  このエラーは通常、無効な JSON データ（JSON Lines (JSONL) ファイルなど）で発生します。レコードにエスケープされていない改行文字が含まれていると、1 行 1 レコードのルールに違反し、エラーがトリガーされます。

• 解決策

  JSON ファイル内の改行文字をエスケープしてからデータを読み取ります。

• トラブルシューティング

  BadRowSkipping を有効にすると、スキップされた行の詳細が Logview の stdout ログに出力され、データエラーの診断に役立ちます。この機能を有効にするには、次の設定を使用します。

  -- セッションレベルで BadRowSkipping パラメーターを on に設定し、エラーデータをスキップします。
  SET odps.sql.unstructured.text.bad.row.skipping=on;
  
  -- Logview の stdout に出力されるエラーレコードの件数を指定します。
  SET odps.sql.unstructured.text.bad.row.skipping.debug.num=<number>;