JSON外部表

本文将详细介绍JSON格式的OSS外部表的创建、读取及写入方法。

前提条件

  • 已具备访问OSS的权限。阿里云账号(主账号)、RAM用户或RAMRole身份可以访问OSS外部表,授权信息请参见OSSSTS模式授权

  • (可选)已准备好OSS存储空间(Bucket)、OSS目录及OSS数据文件。具体操作请参见创建存储空间管理目录简单上传

    MaxCompute已支持在OSS侧自动创建目录,对于携带外部表及UDFSQL语句,您可以通过一条SQL语句执行读写外部表及UDF的操作。原手动创建目录方式仍然支持。
  • 已创建MaxCompute项目。具体操作请参见创建MaxCompute项目

    由于MaxCompute只在部分地域部署,跨地域的数据连通性可能存在问题,因此建议BucketMaxCompute项目所在地域保持一致。
  • 已具备在MaxCompute项目中创建表(CreateTable)的权限。表操作的权限信息请参见MaxCompute权限

使用限制

  • OSS外部表不支持cluster属性。

  • 单个文件大小不能超过3 GB,如果文件过大,建议拆分。

注意事项

读取JSON数据时,如果JSON文件中的列数小于外表DDL中的列数,系统会将缺少的列值补充为NULL。反之(大于时),会丢弃超出的列数据。

数据类型支持

MaxCompute数据类型详情请参见1.0数据类型版本2.0数据类型版本

数据类型

是否支持

数据类型

是否支持

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

不支持

创建外部表

语法结构

精简语法结构

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"。

注意事项

在创建JSON外部表时,如果某个字段的内容本身是一个嵌套的JSON对象(即该字段对应的值为一个JSON结构体),不能直接将该字段的数据类型定义为STRINGJSON,否则系统无法自动解析其中的子字段。 推荐的两种做法是:

  • 将该字段定义为STRING,在查询时结合get_json_object等函数按需提取内部的子字段内容。

  • 使用STRUCT类型对该字段进行结构化定义,将JSON对象的各个子字段映射为表中的独立子列。这样可以直接通过字段名.子字段名的方式访问内部数据。

具体操作方法,请参考下文示例二。

写入数据

MaxCompute写入数据至OSS的语法,详情请参见将数据写入OSS

查询分析

使用示例

示例一:创建、写入及查询JSON外部表

通过内置开源数据解析器创建JSON格式外部表并将数据写入OSS进行查询。

  1. 准备数据。

    登录OSS控制台,上传测试数据json2025.txtOSS Bucket指定目录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   |
    +------------+------------+------------+

示例二:读取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函数读取字段值

  1. 创建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"} |
    +------------------------------------+
  2. 通过get_json_object函数读取aid字段:

    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. 进一步读取嵌套字段xyid

    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类型接收

  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_objectTO_JSON函数读取xy字段:

    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文件设置前后缀和扩展名

  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');

    写入后生成的文件如下图所示:

    image

  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');

    写入后生成的文件如下图所示:

    image

  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');

    写入后生成的文件如下图所示:

    image.png

  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');

    写入后生成的文件如下图所示:

    image.png