MaxCompute:JSON 外部テーブル

構文

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>',...)];

共通パラメーター

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

固有パラメーター

使用上の注意

JSON 外部テーブルを作成する際、フィールドにネストされた JSON オブジェクト (フィールドの値が JSON 構造体) が含まれている場合、このフィールドのデータの型を STRING または JSON として定義しないでください。そうしないと、システムはサブフィールドを自動的に解析できません。次のいずれかのメソッドを使用できます:

• フィールドを STRING として定義します。データをクエリする際、必要に応じて get_json_object などの関数を使用して、内部サブフィールドのコンテンツを抽出できます。

• STRUCT 型を使用してフィールドの構造を定義できます。これにより、JSON オブジェクトのサブフィールドがテーブル内の個別のサブ列にマッピングされます。その後、field_name.subfield_name フォーマットを使用して内部データに直接アクセスできます。

これらの操作の詳細については、「例 2」をご参照ください。

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

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

1. データを準備します。

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

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 ディレクトリからパーティションを登録する必要があります。詳細については、「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-struct/ フォルダにアップロードします。詳細については、「ファイルのアップロード」をご参照ください。

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

1. string 型の列を 1 つだけ含む 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: 出力 OSS ファイルのプレフィックス、サフィックス、および拡張子を設定する

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. 出力 OSS ファイルの拡張子が自動的に生成されます。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');

   次の図は、生成されたファイルを示しています。