AI 助理
文档备案控制台
官方文档
输入文档关键字查找
首页 云原生大数据计算服务 MaxCompute 操作指南 开发参考：SQL 外部表 OSS外部表 TEXTFILE外部表

TEXTFILE外部表

更新时间：
产品详情
我的收藏

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

适用范围

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

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

  • MaxCompute需要与OSS部署在同一地域。

支持数据类型

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

更多有关SmartParse的说明，请参见Smart Parse更灵活的类型兼容能力。

数据类型

是否支持

数据类型

是否支持

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

未开通

支持压缩格式

  • 当读写压缩属性的OSS文件时，需要在建表语句中添加with serdeproperties属性配置，详情请参见with serdeproperties属性参数。

  • 支持读写的数据文件格式：以SNAPPY、LZO方式压缩的TEXTFILE。

创建外部表

语法结构

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

  • 精简语法结构

    CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
(
  <col_name> <data_type>,
  ...
)
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type>, ...)]
STORED AS textfile
LOCATION '<oss_location>';

  • 完整语法结构

    如果需要解析以TEXTFILE格式保存的CSV数据，可参考CSV/TSV外部表。

    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.lazy.LazySimpleSerDe'
  [WITH serdeproperties (
    ['<property_name>'='<property_value>',...])
  ]
STORED AS textfile
LOCATION '<oss_location>'
[tblproperties ('<tbproperty_name>'='<tbproperty_value>',...)];

公共参数

如下为公共参数，详细说明请参见基础语法参数说明。

  • mc_oss_extable_name

  • col_name

  • data_type

  • table_comment

  • oss_location

独有参数

with serdeproperties属性参数

property_name

使用场景

说明

property_value

默认值

field.delim

当需要明确以TEXTFILE格式保存的数据的列分隔符时，请添加该属性。

指定列分隔符。

单个字符串

\001

tblproperties属性参数

property_name

使用场景

说明

property_value

默认值

mcfed.mapreduce.output.fileoutputformat.compress

当需要将TEXTFILE数据文件以压缩方式写入OSS时，请添加该属性。默认输出.deflate压缩的文件。

TEXTFILE压缩属性。配置该参数值为True时，MaxCompute才可以将TEXTFILE数据文件以压缩方式写入OSS，否则不压缩。

  • True

  • False

False

mcfed.mapreduce.output.fileoutputformat.compress.codec

当需要将TEXTFILE数据文件以压缩方式写入OSS时，请添加该属性。

TEXTFILE压缩属性。设置TEXTFILE数据文件的压缩方式。

仅支持property_value列中展示的四种压缩方式。

  • com.hadoop.compression.lzo.LzoCodec

  • com.hadoop.compression.lzo.LzopCodec

  • org.apache.hadoop.io.compress.SnappyCodec

  • com.aliyun.odps.io.compress.SnappyRawCodec

无

io.compression.codecs

当OSS数据文件为Raw-Snappy格式时，请添加该属性。

当OSS数据文件为Raw-Snappy格式时，需要配置此参数，MaxCompute才可以正常读取压缩数据，否则MaxCompute无法成功读取数据。

com.aliyun.odps.io.compress.SnappyRawCodec

无

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

无

写入数据

MaxCompute写入语法详情，请参见写入语法说明。

查询分析

使用示例

通过开源数据解析器创建OSS外部表

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

前置准备

  1. 已创建MaxCompute项目。

  2. 已准备好OSS存储空间（Bucket）、OSS目录。具体操作请参见创建存储空间、管理目录。

    由于MaxCompute只在部分地域部署，跨地域的数据连通性可能存在问题，因此建议Bucket与MaxCompute项目所在地域保持一致。

  3. 授权

    1. 具备访问OSS的权限。阿里云账号（主账号）、RAM用户或RAMRole身份可以访问OSS外部表，授权信息请参见OSS的STS模式授权。

    2. 已具备在MaxCompute项目中创建表（CreateTable）的权限。表操作的权限信息请参见MaxCompute权限。

示例一：压缩数据

本示例将基于SNAPPY压缩文件创建TEXTFILE格式外部表，并进行读取和写入操作。

  1. 在示例数据的oss-mc-testBucket下创建Demo-textfile目录，并上传示例文件snappy到该目录下。

  2. 创建SNAPPY压缩格式的TEXTFILE外部表。

    CREATE EXTERNAL TABLE external_tb_textfile
(
  vehicleId INT,
  recordId INT,
  patientId INT,
  calls INT,
  locationLatitute DOUBLE,
  locationLongtitue DOUBLE,
  recordTime STRING,
  direction STRING
)
row format serde 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH serdeproperties (
    'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole')
STORED AS textfile
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/Demo-textfile/'
tblproperties (
    'mcfed.mapreduce.output.fileoutputformat.compress'='True',
    'mcfed.mapreduce.output.fileoutputformat.compress.codec'='org.apache.hadoop.io.compress.SnappyCodec')
;

  3. 读取TEXTFILE外部表数据。

    SELECT * FROM external_tb_textfile;

    返回结果：

    +------------+------------+------------+------------+------------------+-------------------+------------+------------+
| vehicleid  | recordid   | patientid  | calls      | locationlatitute | locationlongtitue | recordtime | direction  | 
+------------+------------+------------+------------+------------------+-------------------+------------+------------+
| 1          | 12         | 76         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:10 | SW         | 
| 1          | 1          | 51         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:00 | S          | 
| 1          | 2          | 13         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:01 | NE         | 
| 1          | 3          | 48         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:02 | NE         | 
| 1          | 4          | 30         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:03 | W          | 
| 1          | 5          | 47         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:04 | S          | 
| 1          | 6          | 9          | 1          | 46.81006         | -92.08174         | 9/14/2014 0:05 | S          | 
| 1          | 7          | 53         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:06 | N          | 
| 1          | 8          | 63         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:07 | SW         | 
| 1          | 9          | 4          | 1          | 46.81006         | -92.08174         | 9/14/2014 0:08 | NE         | 
| 1          | 10         | 31         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:09 | N          | 
+------------+------------+------------+------------+------------------+-------------------+------------+------------+

示例二：指定分隔符

本示例将创建TEXTFILE格式外表，读取以|为分隔符的txt文件，并向外部表写入数据。

  1. 在示例数据的oss-mc-testBucket下创建Demo-textfile-txt目录，并上传示例文件vehicle.txt到该目录下。

  2. 创建TEXTFILE外部表，并指定分隔符。

    CREATE EXTERNAL TABLE text_textfile_test01
(
  vehicleId INT,
  recordId INT,
  patientId INT,
  calls INT,
  locationLatitute DOUBLE,
  locationLongtitue DOUBLE,
  recordTime STRING,
  direction STRING
)
row format serde 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
STORED AS textfile
LOCATION 'oss://oss-cn-beijing-internal.aliyuncs.com/oss-mc-test/Demo-textfile//'
tblproperties ('field.delim'='|','odps.external.data.enable.extension'='True');

  3. 读取TEXTFILE外部表数据。

    SELECT * FROM text_textfile_test01;

    返回结果：

    +------------+------------+------------+------------+------------------+-------------------+------------+------------+
| vehicleid  | recordid   | patientid  | calls      | locationlatitute | locationlongtitue | recordtime | direction  | 
+------------+------------+------------+------------+------------------+-------------------+------------+------------+
| 1          | 1          | 51         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:00 | S          | 
| 1          | 2          | 13         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:01 | NE         | 
| 1          | 3          | 48         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:02 | NE         | 
| 1          | 4          | 30         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:03 | W          | 
| 1          | 5          | 47         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:04 | S          | 
| 1          | 6          | 9          | 1          | 46.81006         | -92.08174         | 9/14/2014 0:05 | S          | 
| 1          | 7          | 53         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:06 | N          | 
| 1          | 8          | 63         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:07 | SW         | 
| 1          | 9          | 4          | 1          | 46.81006         | -92.08174         | 9/14/2014 0:08 | NE         | 
| 1          | 10         | 31         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:09 | N          | 
+------------+------------+------------+------------+------------------+-------------------+------------+------------+

  4. 写入TEXTFILE外部表并查询数据。

    INSERT INTO text_textfile_test01 VALUES (1,12,76,1,46.81006,-92.08174,'9/14/2014 0:10','E');

SELECT * FROM text_textfile_test01 WHERE recordId=12;

    返回结果：

    +------------+------------+------------+------------+------------------+-------------------+------------+------------+
| vehicleid  | recordid   | patientid  | calls      | locationlatitute | locationlongtitue | recordtime | direction  | 
+------------+------------+------------+------------+------------------+-------------------+------------+------------+
| 1          | 12         | 76         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:10 | E          | 
+------------+------------+------------+------------+------------------+-------------------+------------+------------+
上一篇：CSV/TSV外部表下一篇：Parquet外部表