CSV/TSV外部表

支持数据类型

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

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

支持压缩格式

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

支持Schema Evolution

语法结构

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>',...)];

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>',...)];

公共参数

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

独有参数

参数配置

• 表级别参数odps.text.option.bad.row.skipping

  • rigid：一定要执行跳过逻辑，不能被session/project层面的配置覆盖。

  • flexible：数据层面开启弹性跳过，但也可以被session/project层面的配置覆盖。

• session/project级别参数

  • odps.sql.unstructured.text.bad.row.skipping，可以覆盖flexible表参数，但不能覆盖rigid表参数。

    • on：主动打开，如果表上未配置，自动打开。

    • off：主动关闭，如果表上配置成flexible，会执行主动关闭，其他情况以表参数为准。

    • <null>/输入不合法时：只看表级别配置行为。

  • odps.sql.unstructured.text.bad.row.skipping.debug.num，表示允许打印出错结果到Logview的Stdout中的条数。

    • 最大值只能为1000

    • 值<=0时，表示关闭

    • 输入不合法时，表示关闭

• Session级别参数和表层面参数交互关系

  tbl property	session flag	result
  rigid	on	开，强制开启
  	off
  	<null>，或者不合法的值，或者不配置该参数值
  flexible	on	开
  	off	关，被session关闭
  	<null>，或者不合法的值，或者不配置该参数值	开
  没有配置	on	开，session开启
  	off	关
  	<null>，或者不合法的值，或者不配置该参数值

使用示例

1. 数据准备

   将存在部分脏数据的测试数据csv_bad_row_skipping.csv上传至OSSoss-mc-test/badrow/。

2. 创建CSV外部表

   表级别参数和session级别flag不一样，三种情况如下所示。

   • 表参数： odps.text.option.bad.row.skipping = flexible/rigid/<不填>

   • session flag： odps.sql.unstructured.text.bad.row.skipping = on/off/<不填>

   表级别没有配置参数

   -- 表级别没有配置参数，该报错就报错，靠session级别flag来控制
   CREATE EXTERNAL TABLE test_csv_bad_data_skipping_flag
   (
     a INT,
     b INT
   )
   STORED BY 'com.aliyun.odps.CsvStorageHandler' 
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   location '<oss://databucketpath>';

   表级别参数跳过错误行，Session级别可以关闭

   -- 表级别参数跳过错误行，不过session级别的flag可以主动关闭
   CREATE EXTERNAL TABLE test_csv_bad_data_skipping_flexible
   (
     a INT,
     b INT
   )
   STORED BY 'com.aliyun.odps.CsvStorageHandler' 
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   location '<oss://databucketpath>'
   tblproperties (
     'odps.text.option.bad.row.skipping' = 'flexible'   -- 弹性开启，session级别可关闭
   );

   表级别参数跳过错误行，Session级别无法关闭

   -- 表级别参数强制开启跳过错误，session级别无法关闭
   CREATE EXTERNAL TABLE test_csv_bad_data_skipping_rigid
   (
     a INT,
     b INT
   )
   STORED BY 'com.aliyun.odps.CsvStorageHandler' 
   WITH serdeproperties (
     'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   location '<oss://databucketpath>'
   tblproperties (
     'odps.text.option.bad.row.skipping' = 'rigid'  -- 强制开启
   );

3. 验证查询结果

   表级别没有配置参数

   -- session级别开启
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- session级别关闭，如果表级别配置为'flexible'，则会被关闭，如果表级别配置'rigid'则不受影响；
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- session级别打印有问题的行数据，最大1000条，小于等于0时，就关闭了错误数据打印
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_csv_bad_data_skipping_flag;

   返回报错：FAILED: ODPS-0123131:User defined function exception

   表级别参数跳过错误行，Session级别可以关闭

   -- session级别开启
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- session级别关闭，如果表级别配置为'flexible'，则会被关闭，如果表级别配置'rigid'则不受影响；
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- session级别打印有问题的行数据，最大1000条，小于等于0时，就关闭了错误数据打印
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_csv_bad_data_skipping_flexible;

   返回报错：FAILED: ODPS-0123131:User defined function exception

   表级别参数跳过错误行，Session级别无法关闭

   -- session级别开启
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- session级别关闭，如果表级别配置为'flexible'，则会被关闭，如果表级别配置'rigid'则不受影响；
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- session级别打印有问题的行数据，最大1000条，小于等于0时，就关闭了错误数据打印
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_csv_bad_data_skipping_rigid;

   返回结果如下：

   +------------+------------+
   | a          | b          | 
   +------------+------------+
   | 1          | 26         | 
   | 5          | 37         | 
   +------------+------------+

前置准备

• 已创建MaxCompute项目。

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

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

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

• 授权

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

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

通过内置文本数据解析器创建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;

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

返回结果如下：

+----------+--------+------------+
| 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  
        +----+-------+----------------+-------------+

通过内置开源解析器创建OSS外部表

通过内置开源解析器创建OSS外部表读取以逗号为分隔符的文件，并忽略首行和尾行的数据。

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

   测试文件包含的数据信息如下。

   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

2. 创建外部表，并指定分隔符为逗号，设置忽略首行和尾行参数。

   CREATE EXTERNAL TABLE ext_csv_test08
   (
     vehicleId INT,
     recordId INT,
     patientId INT,
     calls INT,
     locationLatitute DOUBLE,
     locationLongtitue DOUBLE,
     recordTime STRING,
     direction STRING
   )
   ROW FORMAT serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
   WITH serdeproperties (
   "separatorChar" = ","
   )
   stored AS textfile
   location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/***/'
   -- 设置忽略首行和尾行参数。
   TBLPROPERTIES (
   "skip.header.line.COUNT"="1",
   "skip.footer.line.COUNT"="1"
   )
   ;

3. 读取外部表。

   SELECT * FROM ext_csv_test08;
   
   -- 返回结果，只读了8条，忽略了首行和尾行。
   +------------+------------+------------+------------+------------------+-------------------+------------+------------+
   | 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          | 
   +------------+------------+------------+------------+------------------+-------------------+------------+------------+

创建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 |
   +------------+---------------------+---------------------+------------------------+

读取CSV/TSV数据时会报错“列数不匹配”

• 问题现象

  若CSV/TSV文件中的列数与外表DDL中的列数量的不一致，读取CSV/TSV数据时会报错“列数不匹配”，例如FAILED: ODPS-0123131:User defined function exception - Traceback:java.lang.RuntimeException: SCHEMA MISMATCH:xxx。

• 解决方案

  通过在Session级别设置odps.sql.text.schema.mismatch.mode参数，可以控制输出结果：

  • SET odps.sql.text.schema.mismatch.mode=error：表示列数不一致时，系统直接报错。

  • SET odps.sql.text.schema.mismatch.mode=truncate：表示列数不一致时，超过外表DDL中的列数部分将被丢弃；少于外表DDL中的列数部分，列值将被补充为NULL。