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

TEXTFILE外部表

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

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

前提条件

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

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

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

  • 已创建MaxCompute项目。具体操作请参见创建MaxCompute项目。

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

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

使用限制

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

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

注意事项

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

数据类型支持

说明

下表中已开通表示支持，未开通表示不支持。

数据类型

是否支持

TINYINT

已开通

SMALLINT

已开通

INT

已开通

BIGINT

已开通

BINARY

已开通

FLOAT

已开通

DOUBLE

已开通

DECIMAL(precision,scale)

已开通

VARCHAR(n)

已开通

CHAR(n)

已开通

STRING

已开通

DATE

已开通

DATETIME

已开通

TIMESTAMP

未开通

TIMESTAMP_NTZ

已开通

BOOLEAN

已开通

ARRAY

已开通

MAP

已开通

STRUCT

已开通

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

无

写入数据

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

查询分析

使用示例

说明

  • 执行下述示例代码时，请将代码中的<uid>替换为您的阿里云账号ID。

  • 下述示例中使用的角色为aliyunodpsdefaultrole，如果您想使用其他角色，需要将aliyunodpsdefaultrole替换为目标角色名称，并为目标角色授予访问OSS的权限。

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

示例一：压缩数据

本示例将基于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/yyy-ceshi/external_table/format/nopt/textfile/text_textfile_test01/'
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外部表