JSON外部表格 - MaxCompute

本文介紹JSON格式的OSS外部表格的建立、讀取及寫入方法。

適用範圍

OSS外部表格不支援cluster屬性。
單個檔案大小不能超過2GB，如果檔案過大，建議拆分。
MaxCompute需要與OSS部署在同一地區。

支援資料類型

資料類型	是否支援	資料類型	是否支援
TINYINT		STRING
SMALLINT		DATE
INT		DATETIME
BIGINT		TIMESTAMP
BINARY		TIMESTAMP_NTZ
FLOAT		BOOLEAN
DOUBLE		ARRAY
DECIMAL(precision,scale)		MAP
VARCHAR(n)		STRUCT
CHAR(n)		JSON

建立外部表格

文法結構

讀取JSON資料時，如果JSON檔案中的列數小於外表DDL中的列數，系統會將缺少的列值補充為NULL。反之（大於時），會丟棄超出的列資料。

精簡文法結構

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

公用參數

詳情請參見基礎文法參數說明。

專屬參數

tblproperties屬性參數

property_name	使用情境	說明	property_value	預設值
mcfed.mapreduce.output.fileoutputformat.compress	當需要將TEXTFILE資料檔案以壓縮方式寫入OSS時，請添加該屬性。	TEXTFILE壓縮屬性。配置該參數值為True時，MaxCompute才可以將TEXTFILE資料檔案以壓縮方式寫入OSS，否則不壓縮。	True False	False
mcfed.mapreduce.output.fileoutputformat.compress.codec	當需要將TEXTFILE資料檔案以壓縮方式寫入OSS時，請添加該屬性。	TEXTFILE壓縮屬性。設定TEXTFILE資料檔案的壓縮方式。預設輸出`.deflate`壓縮的檔案說明：只支援`property_value`中的壓縮方式。	com.hadoop.compression.lzo.LzoCodec com.hadoop.compression.lzo.LzopCodec org.apache.hadoop.io.compress.SnappyCodec com.aliyun.odps.io.compress.SnappyRawCodec org.apache.hadoop.io.compress.BZip2Codec org.apache.hadoop.io.compress.Lz4Codec org.apache.hadoop.io.compress.DeflateCodec org.apache.hadoop.io.compress.GzipCodec	org.apache.hadoop.io.compress.DeflateCodec
odps.external.data.output.prefix （相容odps.external.data.prefix）	當需要添加輸出檔案的自訂首碼名時，請添加該屬性。	僅包含數字、字母、底線（a-z、A-Z、0-9、_）。長度在1-10之間。	合格字元組合，例如`mc_`。	無
odps.external.data.enable.extension	當需要顯示輸出檔案的副檔名時，請添加該屬性。	True表示顯示輸出檔案的副檔名，反之不顯示副檔名。	True False	False
odps.external.data.output.suffix	當需要添加輸出檔案的自訂尾碼名時，請添加該屬性。	僅包含數字、字母、底線（a-z、A-Z、0-9、_）。	合格字元組合，例如'_hangzhou'。	無
odps.external.data.output.explicit.extension	當需要添加輸出檔案的自訂副檔名時，請添加該屬性。	僅包含數字、字母、底線（a-z、A-Z、0-9、_）。長度在1-10之間。優先順序高於參數`odps.external.data.enable.extension`。	合格字元組合，例如`jsonl`。	無
odps.text.option.bad.row.skipping	當需要跳過OSS資料檔案中的髒資料時，請添加該屬性。	MaxCompute讀取OSS資料檔案時，可以選擇跳過或者不跳過髒資料。	rigid：一定會執行跳過邏輯，不能被session/project層面的配置覆蓋。 flexible：資料層面開啟彈性跳過，但也可以被session/project層面的配置覆蓋。

在建立JSON外部表格時，如果某個欄位的內容本身是一個嵌套的JSON對象（即該欄位對應的值為一個JSON結構體），不能直接將該欄位的資料類型定義為STRING或JSON，否則系統無法自動解析其中的子欄位。

推薦的兩種做法如下，具體操作步驟，請參考下文樣本：

將該欄位定義為STRING，在查詢時結合get_json_object等函數按需提取內部的子欄位內容。
使用STRUCT類型對該欄位進行結構化定義，將JSON對象的各個子欄位對應為表中的獨立子列。這樣可以直接通過欄位名.子欄位名的方式訪問內部資料。

寫入資料

MaxCompute寫入資料至OSS的文法，詳情請參見將資料寫入OSS。

查詢分析

SELECT文法，詳情請參見讀取OSS資料。
最佳化查詢計劃，詳情請參見查詢最佳化。
BadRowSkipping詳情，請參見BadRowSkipping。

BadRowSkipping

當JSON資料中出現髒資料時，可以通過BadRowSkipping功能，設定參數選擇跳過或者不跳過報錯資料。參數開啟與否，不影響理解底層資料格式。

參數配置

表層級參數odps.text.option.bad.row.skipping
- rigid：一定要執行跳過邏輯，不能被session/project層面的配置覆蓋。
- flexible：資料層面開啟彈性跳過，但也可以被session/project層面的配置覆蓋。
session/project層級參數
- odps.sql.unstructured.text.bad.row.skipping，可以覆蓋flexible表參數，但不能覆蓋rigid表參數。
  - on：主動開啟，如果表上未配置，自動開啟。
  - off：主動關閉，如果表上配置成flexible，會執行主動關閉，其他情況以表參數為準。
  - <null>/輸入不合法時：只看錶層級配置行為。
- odps.sql.unstructured.text.bad.row.skipping.debug.num，表示允許列印出錯結果到Logview的Stdout中的條數。
  - 最大值只能為1000
  - 值<=0時，表示關閉
  - 輸入不合法時，表示關閉

Session層級參數和表層面參數互動關係

tbl property	session flag	result
rigid	on	開，強制開啟
	off
	<null>，或者不合法的值，或者不配置該參數值
flexible	on	開
	off	關，被session關閉
	<null>，或者不合法的值，或者不配置該參數值	開
沒有配置	on	開，session開啟
	off	關
	<null>，或者不合法的值，或者不配置該參數值	關

使用樣本

資料準備
將存在部分髒資料的測試資料json_bad_row_skipping.json上傳至OSSoss-mc-test/badrow/。

建立JSON外部表格

表層級參數和session層級flag不一樣，三種情況如下所示。

表參數： odps.text.option.bad.row.skipping = flexible/rigid/<不填>
session flag： odps.sql.unstructured.text.bad.row.skipping = on/off/<不填>

表層級沒有配置參數

-- 表層級沒有配置參數，該報錯就報錯，靠session層級flag來控制
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>';

表層級參數跳過錯誤行，Session層級可以關閉

-- 表層級參數跳過錯誤行，不過session層級的flag可以主動關閉
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'   -- 彈性開啟，session層級可關閉
);

表層級參數跳過錯誤行，Session層級無法關閉

-- 表層級參數強制開啟跳過錯誤，session層級無法關閉
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'  -- 強制開啟
);

驗證查詢結果

表層級沒有配置參數

-- session層級開啟
SET odps.sql.unstructured.text.bad.row.skipping=on;

-- session層級關閉，如果表層級配置為'flexible'，則會被關閉，如果表層級配置'rigid'則不受影響；
SET odps.sql.unstructured.text.bad.row.skipping=off;

-- session層級列印有問題的行資料，最大1000條，小於等於0時，就關閉了錯誤資料列印
SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;

SELECT * FROM test_json_bad_data_skipping_flag;

返回報錯：FAILED: ODPS-0123131:User defined function exception

表層級參數跳過錯誤行，Session層級可以關閉

-- session層級開啟
SET odps.sql.unstructured.text.bad.row.skipping=on;

-- session層級關閉，如果表層級配置為'flexible'，則會被關閉，如果表層級配置'rigid'則不受影響；
SET odps.sql.unstructured.text.bad.row.skipping=off;

-- session層級列印有問題的行資料，最大1000條，小於等於0時，就關閉了錯誤資料列印
SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;

SELECT * FROM test_json_bad_data_skipping_flexible;

返回報錯：FAILED: ODPS-0123131:User defined function exception

表層級參數跳過錯誤行，Session層級無法關閉

-- session層級開啟
SET odps.sql.unstructured.text.bad.row.skipping=on;

-- session層級關閉，如果表層級配置為'flexible'，則會被關閉，如果表層級配置'rigid'則不受影響；
SET odps.sql.unstructured.text.bad.row.skipping=off;

-- session層級列印有問題的行資料，最大1000條，小於等於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         | 
+------------+------------+

使用樣本

前置準備

已建立MaxCompute專案。
已準備好OSS儲存空間（Bucket）、OSS目錄。具體操作請參見建立儲存空間、管理目錄。
由於MaxCompute只在部分地區部署，跨地區的資料連通性可能存在問題，因此建議Bucket與MaxCompute專案所在地區保持一致。
授權
1. 具備訪問OSS的許可權。阿里雲帳號（主帳號）、RAM使用者或RAMRole身份可以訪問OSS外部表格，授權資訊請參見OSS的STS模式授權。
2. 已具備在MaxCompute專案中建立表（CreateTable）的許可權。表操作的許可權資訊請參見MaxCompute許可權。

樣本一：建立、寫入及查詢JSON外部表格

通過內建開來源資料解析器建立JSON格式外部表格並將資料寫入OSS進行查詢。

準備資料。
登入OSS控制台，上傳測試資料json2025.txt至OSS Bucket指定目錄external-table-test/json/dt=20250521/。具體操作請參見OSS檔案上傳。

建立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/';

引入分區資料。當建立的OSS外部表格為分區表時，需要額外執行引入分區資料的操作，詳情請參見補全OSS外部表格分區資料文法。
```
-- 引入分區
MSCK REPAIR TABLE mc_oss_extable_name_json ADD PARTITIONS;
```

讀取JSON外部表格。

SELECT * FROM mc_oss_extable_name_json WHERE dt=20250526;

返回結果如下：

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

寫入JSON外部表格。

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

查看寫入資料。

SELECT * FROM mc_oss_extable_name_json WHERE dt=20250526;

返回結果如下：

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

樣本二：讀取JSON格式的欄位值

準備資料

建立JSON資料events.json：

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

登入OSS控制台，上傳測試資料至OSS Bucket指定目錄external-table-test/json-sturct/。具體操作請參見OSS檔案上傳。

方式一：建立TEXTFILE外部表格，並用get_json_object函數讀取欄位值

建立TEXTFILE外部表格，僅包含一個string類型的列：

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"} |
+------------------------------------+

通過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 |
+-------------------+-----+

進一步讀取嵌套欄位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  |
+---+---+-----+

方式二：建立JSON外部表格，用STRUCT類型接收

建立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/';

直接查詢表內容：

SELECT * FROM extable_json_test02;

返回結果如下：

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

也可以通過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  |
+---+---+-----+

樣本三：寫出OSS檔案設定前尾碼和副檔名

為寫入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');

寫入後產生的檔案如下圖所示：

為寫入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');

寫入後產生的檔案如下圖所示：

為寫入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');

寫入後產生的檔案如下圖所示:

為寫入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');

寫入後產生的檔案如下圖所示：

為寫入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');

寫入後產生的檔案如下圖所示：