Regex外部表-云原生大数据计算服务 MaxCompute(MaxCompute)-阿里云帮助中心

本文介绍如何创建Regex格式的OSS外部表以及该外部表支持的数据类型，从而实现通过正则表达式解析存储在OSS上的非结构化文本数据。

适用范围

OSS外部表不支持cluster属性。
单个文件大小不能超过2GB，如果文件过大，建议拆分。
MaxCompute需要与OSS部署在同一地域。
不支持写操作。
支持数据类型。

前提条件

授权
- 已具备在MaxCompute项目中创建表（CreateTable）的权限。表操作的权限信息请参见MaxCompute权限。
- 已具备访问OSS的权限。阿里云账号（主账号）、RAM用户或RAM Role身份可以访问OSS外部表。授权信息请参见OSS的STS模式授权。
已创建MaxCompute项目。
由于MaxCompute只在部分地域部署，跨地域的数据连通性可能存在问题，因此建议OSS 存储空间（Bucket）与MaxCompute项目所在地域保持一致。
（可选）已准备好OSS存储空间（Bucket）、OSS目录及OSS数据文件。具体操作请参见创建存储空间、管理目录和简单上传。
MaxCompute已支持在OSS侧自动创建目录，对于携带外部表及UDF的SQL语句，可以通过一条SQL语句执行读写外部表及UDF的操作。原手动创建目录方式仍然支持。

创建外部表

语法结构

各格式外部表语法结构详情，请参见OSS外部表。

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
(
  <col_name> <data_type>,
  ...
)
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type>, ...)]
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
    'mcfed.input.regex'='<your_regex>',
    'mcfed.columns.comments'=''
)
STORED AS TEXTFILE
LOCATION '<oss_location>'
;

公共参数

详情请参见基础语法参数说明。

SERDEPROPERTIES参数

参数名称

是否必填

说明

mcfed.input.regex

是

用于解析文本数据的正则表达式。使用说明如下：

\d类型的正则需要转义为\\d。
Regex外部表通过分组映射到列，每个字段对应的正则都需要用()括起来。例如，用(123.*)\\t(123.*)来映射以\t分割的两个字段。

mcfed.columns.comments

是

当需要用正则表达式解析文本数据时，需添加字段说明。

系统内部校验规则：

columns.comments必须和列数一一对应。例如，有4列时，需要4段comment，格式为a\0b\0c\0d。

查询分析

SELECT语法，详情请参见读取OSS数据。
优化查询计划，详情请参见查询优化。

使用示例

示例一：匹配以数字123开头的数据

准备测试文件regex_test1.txt上传到OSS目录。

创建Regex外部表，只读以数字123开头的数据，其他数据置为NULL。

CREATE EXTERNAL TABLE regex_example1 (
  id1 DOUBLE
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
    'mcfed.input.regex'='(123.*)',
    'mcfed.columns.comments'='')
STORED AS TEXTFILE
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/<bucket>/regex_test1/'; -- LOCATION路径指定到测试文件的上一层目录。

读取Regex外部表。

SELECT * FROM regex_example1;

-- 返回结果如下：
+------------+
| id1        |
+------------+
| 123.0      |
| NULL       |
| 123.24     |
+------------+

示例二：匹配两个字段均以数字123开头的数据

准备测试文件regex_test2.txt上传到OSS目录。

创建Regex外部表，只读两个字段均以数字123开头的数据，其他数据置为NULL。

CREATE EXTERNAL TABLE regex_example2 (
  id1 INT,
  id2 INT
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
    'mcfed.input.regex'='(123.*)\\t(123.*)',
    'mcfed.columns.comments'='a\0b'
)
STORED AS TEXTFILE
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/<bucket>/regex_test2/'; -- LOCATION路径指定到测试文件的上一层目录。

读取Regex外部表。

SELECT * FROM regex_example2;

-- 返回结果如下：
+------+------+
| id1  | id2  |
+------+------+
| 123  | 123  |
| NULL | NULL |
+------+------+

示例三：跨字段匹配数据

准备测试文件regex_test3.txt上传到OSS目录。

创建Regex外部表，只读字段id、city_org和ppl的值，跳过其他字段。

CREATE EXTERNAL TABLE regex_example3 (
  id INT,
  city_org STRING,
  ppl DOUBLE
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
    'mcfed.input.regex'='^(\\d+)\\t([^\\t]*)\\t\\S+\\t\\S+\\t(\\d++.\\d++).*', 
    'mcfed.columns.comments'='a\0b\0c'
)
STORED AS TEXTFILE
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/<bucket>/regex_test3/'; -- LOCATION路径指定到测试文件的上一层目录。

读取Regex外部表。

SELECT * FROM regex_example3 WHERE id>100 AND id<121; 

-- 返回结果如下：
+------+----------+------------+
| id   | city_org | ppl        |
+------+----------+------------+
| 110  | La Coruña | 0.37       |
| 112  | Cádiz     | 0.4        |
| 120  | Köln      | 0.97       |
+------+----------+------------+

示例四：匹配纯数字数据

准备测试文件regex_test4.txt上传到OSS目录。

创建Regex外部表，只读id1为3位数字、id2为以123开头的任意字符串、id3为2位数字。

CREATE EXTERNAL TABLE regex_example4 (
  id1 INT,
  id2 INT,
  id3 INT
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
    'mcfed.input.regex'='(\\d{3}) (123.*) (\\d{2})', 
    'mcfed.columns.comments'='a\0b\0c'
)
STORED AS TEXTFILE
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/<bucket>/regex_test4/'; -- LOCATION路径指定到测试文件的上一层目录。

读取Regex外部表。

SELECT * FROM regex_example4;

-- 返回结果如下：
+------+------+------+
| id1  | id2  | id3  |
+------+------+------+
| 123  | NULL | 12   |
+------+------+------+

示例五：通过嵌套分组匹配年月日

准备测试文件regex_test5.txt上传到OSS目录。

以((\\d{4})-(\\d{2})-(\\d{2}))嵌套分组，匹配date、year、month、day四列。

CREATE EXTERNAL TABLE regex_example5a (
  date STRING,
  year STRING,
  month STRING,
  day STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
    'mcfed.input.regex'='((\\d{4})-(\\d{2})-(\\d{2}))',  
    'mcfed.columns.comments'='a\0b\0c\0d'
)
STORED AS TEXTFILE
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/<bucket>/regex_test5/'; -- LOCATION路径指定到测试文件的上一层目录。

读取Regex外部表：

SELECT * FROM regex_example5a;

-- 返回结果如下：
+------------+------+-------+-----+
| date       | year | month | day |
+------------+------+-------+-----+
| 2022-10-12 | 2022 | 10    | 12  |
| 2023-01-10 | 2023 | 01    | 10  |
| 2024-09-07 | 2024 | 09    | 07  |
| 2025-06-29 | 2025 | 06    | 29  |
+------------+------+-------+-----+

以(\\d{4}-\\d{2}-\\d{2})匹配date一列。

CREATE EXTERNAL TABLE regex_example5b (
  date STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
    'mcfed.input.regex'='(\\d{4}-\\d{2}-\\d{2})',  
    'mcfed.columns.comments'=''
)
STORED AS TEXTFILE
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/<bucket>/regex_test5/'; -- LOCATION路径指定到测试文件的上一层目录。

读取Regex外部表：

SELECT * FROM regex_example5b;

-- 返回结果如下：
+------------+
| date       |
+------------+
| 2022-10-12 |
| 2023-01-10 |
| 2024-09-07 |
| 2025-06-29 |
+------------+

以(\\d{4})-(\\d{2})-(\\d{2})匹配year、month、day三列。

CREATE EXTERNAL TABLE regex_example5c (
  year STRING,
  month STRING,
  day STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
    'mcfed.input.regex'='(\\d{4})-(\\d{2})-(\\d{2})',  
    'mcfed.columns.comments'='a\0b\0c'
)
STORED AS TEXTFILE
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/<bucket>/regex_test5/'; -- LOCATION路径指定到测试文件的上一层目录。

读取Regex外部表：

SELECT * FROM regex_example5c;

-- 返回结果如下：
+------+-------+-----+
| year | month | day |
+------+-------+-----+
| 2022 | 10    | 12  |
| 2023 | 01    | 10  |
| 2024 | 09    | 07  |
| 2025 | 06    | 29  |
+------+-------+-----+

支持数据类型

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	不支持