CSV/TSV/纯TEXTFILE外部表

语法结构

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

• 内置文本数据解析器

  CSV格式

  CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> 
  (
    <col_name> <data_type>,
    ...
  )
  [COMMENT <table_comment>]
  [PARTITIONED BY (<col_name> <data_type>, ...)] 
  STORED BY 'com.aliyun.odps.CsvStorageHandler'  
  [WITH serdeproperties (
    ['<property_name>'='<property_value>',...]
  )] 
  LOCATION '<oss_location>'
  [tblproperties ('<tbproperty_name>'='<tbproperty_value>',...)];

  TSV格式

  CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> 
  (
    <col_name> <data_type>,
    ...
  )
  [COMMENT <table_comment>]
  [PARTITIONED BY (<col_name> <data_type>, ...)] 
  STORED BY 'com.aliyun.odps.TsvStorageHandler'  
  [WITH serdeproperties (
    ['<property_name>'='<property_value>',...]
  )] 
  LOCATION '<oss_location>'
  [tblproperties ('<tbproperty_name>'='<tbproperty_value>',...)];

• 内置开源数据解析器

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

公共参数

公共参数说明请参见基础语法参数说明。

独有参数

注意事项

• 对于任意类型，CSV数据文件中的空字符串被读入表中后，均会被解析为NULL。

• 不支持STRUCT/ARRAY/MAP等复杂类型，因为这些复杂类型的值很容易和CSV的常见分隔符（,）发生冲突导致解析失败。

• 暂不支持BINARY/INTERVAL类型，如有需求请联系MaxCompute技术支持。

• 对于数据类型INT/SMALLINT/TINYINT/BIGINT/FLOAT/DOUBLE/DECIMAL，默认的解析能力（DEFAULT）比较丰富。

  如果您只想对基础的数字串进行解析，可以在tblproperties中将odps.text.option.smart.parse.level参数设置为naive，使解析器仅支持解析普通数字串，例如“123”、“123.456”，其他形态的字符串在解析时会报错。

• 对于四种日期或时间相关类型DATA/DATATIME/TIMESTAMP/TIMESTAMP_NTZ，在底层代码中均使用java.time.format.DateTimeFormatter进行处理。MaxCompute内置了若干的默认解析格式，您也可以通过在tblproperties中设置odps.text.option.<date|datetime|timestamp|timestamp_ntz>.io.format来自定义多种解析格式与一种输出格式，解析格式之间用#分隔。

  • 自定义格式的优先级高于MaxCompute的内置解析格式，输出格式将采用第一个自定义格式。

    例如，若您自定义DATE类型的格式串为pattern1#pattern2#pattern3，则可以解析符合pattern1、pattern2或pattern3的字符串，但输出至文件时会采用pattern1格式来输出。详情请参见DateTimeFormatter。

  • 不建议使用 'z' (time-zone name) 作为自定义的时区格式（尤其对于中国区用户），因为 'z' 在某些上下文中可能存在歧义。

    例如：'CST' 在中国语境下通常表示中国标准时间（UTC+8），然而当 'CST' 被java.time.format.DateTimeFormatter解析时，会被识别为美国中部标准时间（UTC-6），这可能导致非预期的输入或输出结果。建议您改用 'x' (zone-offset) 或 'VV' (time-zone ID) 作为时区模式。

通过内置文本数据解析器创建OSS外部表-非分区表

1. 与示例数据中的Demo1/目录建立映射关系。创建OSS外部表的命令示例如下。

   CREATE EXTERNAL TABLE IF NOT EXISTS mc_oss_csv_external1
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongtitue DOUBLE,
     recordTime STRING,
     direction STRING
   )
   STORED BY 'com.aliyun.odps.CsvStorageHandler' 
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/Demo1/';
   
   -- 您可以执行desc extended mc_oss_csv_external1;命令查看创建好的OSS外部表结构信息。

2. 查询非分区外表。

   SELECT * FROM mc_oss_csv_external1;

   返回结果如下：

   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+
   | 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:00 | NE         |
   | 1          | 3          | 48         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:00 | NE         |
   | 1          | 4          | 30         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:00 | W          |
   | 1          | 5          | 47         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:00 | S          |
   | 1          | 6          | 9          | 1          | 46.81006         | -92.08174         | 9/15/2014 0:00 | S          |
   | 1          | 7          | 53         | 1          | 46.81006         | -92.08174         | 9/15/2014 0:00 | N          |
   | 1          | 8          | 63         | 1          | 46.81006         | -92.08174         | 9/15/2014 0:00 | SW         |
   | 1          | 9          | 4          | 1          | 46.81006         | -92.08174         | 9/15/2014 0:00 | NE         |
   | 1          | 10         | 31         | 1          | 46.81006         | -92.08174         | 9/15/2014 0:00 | N          |
   +------------+------------+------------+------------+------------------+-------------------+------------+----------------+

3. 写入数据至非分区外表，并查看数据是否已成功写入。

   INSERT INTO mc_oss_csv_external1 VALUES(1,12,76,1,46.81006,-92.08174,'9/14/2014 0:10','SW');
   SELECT * FROM mc_oss_csv_external1 WHERE recordId=12;

   返回结果如下：

   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+
   | vehicleid  | recordid   | patientid  | calls      | locationlatitute | locationlongtitue | recordtime     | direction  |
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+
   | 1          | 12         | 76         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:10 | SW         |
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+

   查看OSS路径Demo1/下已产生新文件。

通过内置文本数据解析器创建OSS外部表-分区表

1. 与示例数据中的Demo2/目录建立映射关系。创建OSS外部表并引入分区数据的命令示例如下。

   CREATE EXTERNAL TABLE IF NOT EXISTS mc_oss_csv_external2
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongtitue DOUBLE,
     recordTime STRING
   )
   PARTITIONED BY (
     direction STRING
   )
   STORED BY 'com.aliyun.odps.CsvStorageHandler' 
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   ) 
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/Demo2/';
   
   --引入分区数据。
   MSCK REPAIR TABLE mc_oss_csv_external2 ADD PARTITIONS;
   --等效于如下语句。
   ALTER TABLE mc_oss_csv_external2 ADD PARTITION (direction = 'N') PARTITION (direction = 'NE') PARTITION (direction = 'S') PARTITION (direction = 'SW') PARTITION (direction = 'W');
   
   -- 您可以执行DESC EXTENDED mc_oss_csv_external2;命令查看创建好的外部表结构信息。

2. 查询分区外表。

   SELECT * FROM mc_oss_csv_external2 WHERE direction='NE';

   返回结果如下：

   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+
   | vehicleid  | recordid   | patientid  | calls      | locationlatitute | locationlongtitue | recordtime     | direction  |
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+
   | 1          | 2          | 13         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:00 | NE         |
   | 1          | 3          | 48         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:00 | NE         |
   | 1          | 9          | 4          | 1          | 46.81006         | -92.08174         | 9/15/2014 0:00 | NE         |
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+

3. 写入数据至分区外表，并查看是否已成功写入。

   INSERT INTO mc_oss_csv_external2 PARTITION(direction='NE') VALUES(1,12,76,1,46.81006,-92.08174,'9/14/2014 0:10');
   SELECT * FROM mc_oss_csv_external2 WHERE direction='NE' AND recordId=12;

   返回结果如下：

   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+
   | vehicleid  | recordid   | patientid  | calls      | locationlatitute | locationlongtitue | recordtime     | direction  |
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+
   | 1          | 12         | 76         | 1          | 46.81006         | -92.08174         | 9/14/2014 0:10 | NE         |
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+

   查看OSS路径Demo2/direction=NE下已产生新文件。

通过内置文本数据解析器创建OSS外部表-压缩数据

本示例创建以GZIP压缩的CSV格式外表，进行数据读取和写入操作。

1. 创建内部表并写入测试数据，用来进行后续的写入测试。

   CREATE TABLE vehicle_test(
     vehicleid INT, 
     recordid INT, 
     patientid INT, 
     calls INT, 
     locationlatitute DOUBLE, 
     locationlongtitue DOUBLE, 
     recordtime STRING, 
     direction STRING
   );
   
   INSERT INTO vehicle_test VALUES (1,1,51,1,46.81006,-92.08174,'9/14/2014 0:00','S');

2. 创建以GZIP压缩的CSV格式外表，与示例数据中的Demo3/（压缩数据）目录建立映射关系。创建OSS外部表命令示例如下。

   CREATE EXTERNAL TABLE IF NOT EXISTS mc_oss_csv_external3
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongtitue DOUBLE,
     recordTime STRING,
     direction STRING
   )
   PARTITIONED BY (dt STRING)
   STORED BY 'com.aliyun.odps.CsvStorageHandler' 
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole',
     'odps.text.option.gzip.input.enabled'='true',
     'odps.text.option.gzip.output.enabled'='true' 
   ) 
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/Demo3/';
   
   -- 引入分区数据
   MSCK REPAIR TABLE mc_oss_csv_external3 ADD PARTITIONS;
   
   -- 您可以执行DESC EXTENDED mc_oss_csv_external3;命令查看创建好的外部表结构信息。

3. 使用MaxCompute客户端，从OSS中读取数据。命令示例如下：

   说明

   如果OSS压缩数据为开源数据格式，需要在SQL语句前添加set odps.sql.hive.compatible=true;命令，与SQL语句一起提交执行。

   --开启全表扫描，仅此Session有效。
   SET odps.sql.allow.fullscan=true;
   SELECT recordId, patientId, direction FROM mc_oss_csv_external3 WHERE patientId > 25;

   返回结果如下：

   +------------+------------+------------+
   | recordid   | patientid  | direction  |
   +------------+------------+------------+
   | 1          | 51         | S          |
   | 3          | 48         | NE         |
   | 4          | 30         | W          |
   | 5          | 47         | S          |
   | 7          | 53         | N          |
   | 8          | 63         | SW         |
   | 10         | 31         | N          |
   +------------+------------+------------+

4. 读取内部表的数据写入至OSS外表。

   通过MaxCompute客户端对外部表执行INSERT OVERWRITE或INSERT INTO命令，将数据写入OSS。

   INSERT INTO TABLE mc_oss_csv_external3 PARTITION (dt='20250418') SELECT * FROM vehicle_test;

   执行成功后，您可在OSS目录下查看导出的文件。

创建OSS外部表并指定对应OSS文件的第一行为表头

在示例数据的oss-mc-testBucket下创建Demo11目录，并执行如下语句：

--创建外部表
CREATE EXTERNAL TABLE mf_oss_wtt
(
  id BIGINT,
  name STRING,
  tran_amt DOUBLE
)
STORED BY 'com.aliyun.odps.CsvStorageHandler'
WITH serdeproperties (
  'odps.text.option.header.lines.count' = '1',
  'odps.sql.text.option.flush.header' = 'true'
)
LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/Demo11/';

--插入数据
INSERT OVERWRITE TABLE mf_oss_wtt VALUES (1, 'val1', 1.1),(2, 'value2', 1.3);

--查询数据
--在建表的时候可以把所有字段建成string，否则表头读取时会报错。
--或者在建表的时候需要加跳过表头的参数：'odps.text.option.header.lines.count' = '1'
SELECT * FROM mf_oss_wtt;

返回结果如下：

+----------+--------+------------+
| id       | name   | tran_amt   |
+----------+--------+------------+
| 1        | val1   | 1.1        |
| 2        | value2 | 1.3        |
+----------+--------+------------+

创建OSS外部表且外部表列数与OSS数据列数不一致

1. 在示例数据的oss-mc-testBucket下创建demo目录，并上传test.csv文件。test.csv文件内容如下。

   1,kyle1,this is desc1
   2,kyle2,this is desc2,this is two
   3,kyle3,this is desc3,this is three, I have 4 columns

2. 创建外部表。

   1. 指定对于列数不一致的行的处理方式为TRUNCATE。

      --删除表
      DROP TABLE test_mismatch;
      --新建外部表
      CREATE EXTERNAL TABLE IF NOT EXISTS test_mismatch
      (
        id string,
        name string,
        dect string,
        col4 string
      )
      STORED BY 'com.aliyun.odps.CsvStorageHandler'
      WITH serdeproperties ('odps.sql.text.schema.mismatch.mode' = 'truncate')
      LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/demo/';

   2. 指定对于列数不一致的行的处理方式为IGNORE。

      --删除表
      DROP TABLE test_mismatch01;
      --新建外部表
      CREATE EXTERNAL TABLE IF NOT EXISTS test_mismatch01
      (
        id STRING,
        name STRING,
        dect STRING,
        col4 STRING
      )
      STORED BY 'com.aliyun.odps.CsvStorageHandler'
      WITH serdeproperties ('odps.sql.text.schema.mismatch.mode' = 'ignore')
      LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/demo/';

   3. 查询表数据。

      • 查询test_mismatch表。

        SELECT * FROM test_mismatch;
        
        --返回结果
        +----+-------+---------------+---------------+
        | id | name  | dect          | col4          |
        +----+-------+---------------+---------------+
        | 1  | kyle1 | this is desc1 | NULL          |
        | 2  | kyle2 | this is desc2 | this is two   |
        | 3  | kyle3 | this is desc3 | this is three |
        +----+-------+---------------+---------------+

      • 查询test_mismatch01表。

        SELECT * FROM test_mismatch01;
        
        --返回结果
        +----+-------+----------------+-------------+
        | id | name  | dect           | col4        |
        +----+-------+----------------+-------------+
        | 2  | kyle2 | this is desc2  | this is two  
        +----+-------+----------------+-------------+

创建CSV格式的OSS外部表-自定义各种时间数据类型

CSV自定义时间类型的解析与输出格式详情，请参见Smart Parse更灵活的类型能力。

1. 创建各时间数据类型的CSV外表（datetime/timestamp/timestamp_ntz）。

   CREATE EXTERNAL TABLE test_csv
   (
     col_date DATE,
     col_datetime DATETIME,
     col_timestamp TIMESTAMP,
     col_timestamp_ntz TIMESTAMP_NTZ
   )
   STORED BY 'com.aliyun.odps.CsvStorageHandler'
   LOCATION 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/demo/'
   TBLPROPERTIES (
     'odps.text.option.date.io.format' = 'MM/dd/yyyy',
     'odps.text.option.datetime.io.format' = 'yyyy-MM-dd-HH-mm-ss x',
     'odps.text.option.timestamp.io.format' = 'yyyy-MM-dd HH-mm-ss VV',
     'odps.text.option.timestamp_ntz.io.format' = 'yyyy-MM-dd HH:mm:ss.SS'
   );
   
   INSERT OVERWRITE test_csv 
     VALUES(DATE'2025-02-21', DATETIME'2025-02-21 08:30:00', TIMESTAMP'2025-02-21 12:30:00', TIMESTAMP_NTZ'2025-02-21 16:30:00.123456789');

2. 插入数据后，CSV文件的内容为：

   02/21/2025,2025-02-21-08-30-00 +08,2025-02-21 12-30-00 Asia/Shanghai,2025-02-21 16:30:00.12

3. 再次读数据，可以看到结果。

   SELECT * FROM test_csv;

   返回结果如下：

   +------------+---------------------+---------------------+------------------------+
   | col_date   | col_datetime        | col_timestamp       | col_timestamp_ntz      |
   +------------+---------------------+---------------------+------------------------+
   | 2025-02-21 | 2025-02-21 08:30:00 | 2025-02-21 12:30:00 | 2025-02-21 16:30:00.12 |
   +------------+---------------------+---------------------+------------------------+