JSON external table

Syntax

If a JSON file has fewer columns than the external table definition, MaxCompute populates the missing columns with NULL. If the file has more columns, MaxCompute discards the extra columns.

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

Common parameters

For more information, see Common syntax parameters.

Exclusive parameters

Whitelist and blacklist

MaxCompute OSS external tables support whitelist and blacklist filtering. By setting whitelist and blacklist parameters in tblproperties, you can filter which files to read from a directory. For details, see OSS external tables.

Parameters

• Table-level parameter: odps.text.option.bad.row.skipping

  • rigid: Forces skipping. This setting cannot be overridden by session-level or project-level configurations.

  • flexible: Enables skipping. This setting is flexible, allowing it to be overridden by session-level or project-level configurations.

• session/project level parameters

  • The odps.sql.unstructured.text.bad.row.skipping parameter can override a flexible table-level parameter, but not a rigid one.

    • on: Enables the feature. If the feature is not configured for the table, it is enabled by default.

    • off: Disables the feature. If the table is configured as flexible, the feature is disabled. Otherwise, the setting of the table parameter is used.

    • <null> or invalid input: The table-level configuration is used.

  • odps.sql.unstructured.text.bad.row.skipping.debug.num: Specifies the number of error results to print to stdout in Logview.

    • The maximum value is 1000.

    • If the value is <=0, this feature is disabled.

    • If the value is invalid, this feature is disabled.

• Interaction between session-level parameters and table properties

  tbl property	session flag	result
  rigid	on	On, Forced on
  	off
  	<null>, an invalid value, or the parameter is not configured
  flexible	on	On
  	off	Off, Disabled by session
  	<null>, an invalid value, or the parameter is not configured	On
  Not configured	on	On, Enabled by session
  	off	Off
  	<null>, an invalid value, or the parameter is not configured

Examples

1. Prepare data

   Upload the test data json_bad_row_skipping.json, which contains some dirty data, to the oss-mc-test/badrow/ directory in OSS.

2. Create a JSON external table

   The behavior differs based on the table-level property and the session-level flag. The following three cases are possible:

   • Table parameter: odps.text.option.bad.row.skipping = flexible/rigid/<unspecified>

   • Session flag: odps.sql.unstructured.text.bad.row.skipping = on/off/<not set>

   No table-level property

   -- No table-level property is set. Errors are handled based on the session-level 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>';

   Flexible skipping

   -- The table-level property skips error rows, but the session-level flag can disable this behavior.
   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'   -- Flexible mode, can be disabled at the session level.
   );

   Rigid skipping

   -- The table-level property forces error skipping. This behavior cannot be disabled at the session level.
   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'  -- Forced on.
   );

3. Verify query results

   No table-level property

   -- Enable at the session level.
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- Disable at the session level. If the table property is 'flexible', it will be disabled. If the table property is 'rigid', this setting has no effect.
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- Print problematic rows. The maximum is 1,000. If the value is less than or equal to 0, printing is disabled.
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_json_bad_data_skipping_flag;

   If error skipping is disabled at the session level (SET odps.sql.unstructured.text.bad.row.skipping=off), the query fails with the following error: FAILED: ODPS-0123131:User defined function exception

   Flexible skipping

   -- Enable at the session level.
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- Disable at the session level. If the table property is 'flexible', it will be disabled. If the table property is 'rigid', this setting has no effect.
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- Print problematic rows. The maximum is 1,000. If the value is less than or equal to 0, printing is disabled.
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_json_bad_data_skipping_flexible;

   If error skipping is disabled at the session level (SET odps.sql.unstructured.text.bad.row.skipping=off), the query fails with the following error: FAILED: ODPS-0123131:User defined function exception

   Rigid skipping

   -- Enable at the session level.
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- Disable at the session level. If the table property is 'flexible', it will be disabled. If the table property is 'rigid', this setting has no effect.
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- Print problematic rows. The maximum is 1,000. If the value is less than or equal to 0, printing is disabled.
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_json_bad_data_skipping_rigid;

   The following result is returned:

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

Prerequisites

1. You have created a MaxCompute project.

2. You have prepared an OSS bucket and directory. For more information, see Create a bucket and Manage directories.

   Ensure your bucket is in the same region as your MaxCompute project.

3. Grant permissions.

   1. You have permission to access OSS. You can access an OSS external table by using an Alibaba Cloud account, a RAM user, or a RAM role. For more information about how to grant permissions, see STS-mode authorization for OSS.

   2. You have the CreateTable permission in the MaxCompute project. For more information about table-related permissions, see MaxCompute permissions.

Example 1: Create, write, and query a JSON table

This example shows how to create a JSON external table using the built-in open source data parser, write data to OSS, and then query the data.

1. Prepare data.

   Log on to the OSS console and upload the test data file json2025.txt to the external-table-test/json/dt=20250521/ directory in an OSS Bucket. For more information, see Upload files to OSS.

2. Create a JSON external table.

   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. If your OSS external table is a partitioned table, you must also import the partitioned data. For more information, see OSS external table.

   -- Add partitions.
   MSCK REPAIR TABLE mc_oss_extable_name_json ADD PARTITIONS;

4. Read data from the JSON external table.

   SELECT * FROM mc_oss_extable_name_json WHERE dt=20250526;

   The following result is returned:

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

5. Write data to the JSON external table.

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

6. View the written data.

   SELECT * FROM mc_oss_extable_name_json WHERE dt=20250526;

   The following result is returned:

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

Example 2: Read nested JSON fields

Prepare data

Create the JSON file events.json:

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

Log on to the OSS console and upload test data to the external-table-test/json-sturct/ directory in an OSS bucket. For more information, see Upload files to OSS.

Method 1: Create a TEXTFILE external table and use the get_json_object function to read field values

1. Create a TEXTFILE external table that contains only a single column of the string type:

   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;      

   The following result is returned:

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

2. Use the get_json_object function to read the a and id fields:

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

   The following result is returned:

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

3. Read the nested fields x, y, and 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;          

   The following result is returned:

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

Method 2: Create a JSON external table and use the STRUCT type to receive the data

1. Create a JSON-formatted external table and use the STRUCT type to receive nested fields:

   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. Query the data in the table directly:

   SELECT * FROM extable_json_test02;

   The following result is returned:

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

3. You can also use the get_json_object and TO_JSON functions to read the x and y fields:

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

   The following result is returned:

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

Example 3: Customize output filenames

1. Set the custom prefix for files written to OSS to test06_. The DDL is as follows:

   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 (
   -- Add a custom prefix.
       'odps.external.data.output.prefix'='test06_') 
   ;
   
   -- Write data to the external table.
   INSERT INTO  <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

   After writing data, the files generated in OSS are named with the custom prefix test06_, for example, test06_202509101.

2. To customize the suffix for files written to OSS as _beijing, the DDL is as follows:

   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 (
   -- Add a custom suffix.
       'odps.external.data.output.suffix'='_beijing') 
   ;
   
   -- Write data to the external table.
   INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

3. To automatically generate a file extension for output files, use the following 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 (
   -- Automatically generate a file extension.
       'odps.external.data.enable.extension'='true') 
   ;
   
   -- Write data to the external table.
   INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

4. To customize the file extension to jsonl for files written to OSS, the DDL is as follows:

   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 (
   -- Add a custom file extension.
      'odps.external.data.output.explicit.extension'='jsonl') 
   ;
   
   -- Write data to the external table.
   INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

   The generated file name is 20250905072538695g3mlopvxicr4_M1_1_0_0-0_TableSink1.jsonl, with the custom file extension .jsonl.

5. For files written to OSS, set the prefix to mc_, the suffix to _beijing, and the file extension to jsonl. The DDL is as follows:

   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 (
       -- Add a custom prefix.
       'odps.external.data.output.prefix'='mc_', 
       -- Add a custom suffix.
       'odps.external.data.output.suffix'='_beijing', 
       -- Add a custom file extension.
       'odps.external.data.output.explicit.extension'='jsonl') 
   ;  
   
   -- Write data to the external table.
   INSERT INTO <mc_oss_extable_name> VALUES (1,32,76,1,63.32106,-92.08174,'9/14/2014 0:10','NW');

   The generated file name is mc_20250905073013526gra1l214x6t6_M1_1_0_0-0_TableSink1_beijing.jsonl, where 20250905073013526 is the system-generated timestamp, and the middle part is the task identifier.

Error: Unexpected end-of-input: expected close marker for OBJECT

• Error message

  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)

• Cause

  This error typically occurs with invalid JSON data, such as a JSON Lines (JSONL) file. The error is triggered if a record contains an unescaped newline character, violating the one-record-per-line rule.

• Solution

  Escape the newline characters in the JSON file and then read the data.

• Troubleshooting

  When you enable BadRowSkipping, details about skipped rows are printed to the stdout log in Logview. This helps you diagnose data errors. Use the following settings to enable this feature:

  -- Set the BadRowSkipping parameter to on at the session level to skip the error data.
  SET odps.sql.unstructured.text.bad.row.skipping=on;
  
  -- Specify the number of error records that can be printed to stdout in Logview.
  SET odps.sql.unstructured.text.bad.row.skipping.debug.num=<number>;

Whitelist parameter error reading JSON external table: Start token not found where expected

• Error message

  FAILED: ODPS-0123131:User defined function exception - Traceback:
  com.aliyun.odps.serde.SerDeException: org.apache.hadoop.hive.serde2.SerDeException: java.io.IOException: Start token not found where expected
          at com.aliyun.odps.hive.wrapper.HiveSerDeWrapper.deserialize(HiveSerDeWrapper.java:122)
          at com.aliyun.odps.udf.HiveReaderHandler.next(HiveReaderHandler.java:152)
  Caused by: org.apache.hadoop.hive.serde2.SerDeException: java.io.IOException: Start token not found where expected
          at org.apache.hive.hcatalog.data.JsonSerDe.deserialize(JsonSerDe.java:190)
          at com.aliyun.odps.hive.wrapper.json.JsonEnhancedSerde.deserialize(JsonEnhancedSerde.java:50)
          at com.aliyun.odps.hive.wrapper.HiveSerDeWrapper.deserialize(HiveSerDeWrapper.java:120)
          ... 1 more
  Caused by: java.io.IOException: Start token not found where expected
          at org.apache.hive.hcatalog.data.JsonSerDe.deserialize(JsonSerDe.java:176)
          ... 3 more
   | fatalInstance: Odps/yyy_yueyi_dev_20251226063242326gd2yy12fi2h3_SQL_0_1_0_job_0/M1#0_0 

• Cause

  When matching by whitelist parameters, no files are matched.

• Solution

  Verify that the regex pattern in the whitelist parameter is correct.