CSV and TSV external tables

Supported data types

For more information about MaxCompute data types, see Data Type Version 1.0 and Data Type Version 2.0.

For more information about SmartParse, see Flexible type compatibility of Smart Parse.

Supported compression formats

When you read from or write to compressed OSS files, you must include the with serdeproperties attribute in the CREATE TABLE statement. For more information, see with serdeproperties attribute parameters.

Supported schema evolution

Syntax

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

Common parameters

For more information about common parameters, see Basic syntax parameters.

Format-specific parameters

Whitelist and blacklist

MaxCompute OSS external tables support whitelist and blacklist filtering. By setting whitelist and blacklist parameters in tblproperties, you can filter which files to read from a directory. For details, see OSS external tables.

Parameters

• Table-level parameter: odps.text.option.bad.row.skipping

  • rigid: Forces skipping. This setting cannot be overridden by session-level or project-level configurations.

  • flexible: Enables skipping. This setting is flexible, allowing it to be overridden by session-level or project-level configurations.

• session/project level parameters

  • The odps.sql.unstructured.text.bad.row.skipping parameter can override a flexible table-level parameter, but not a rigid one.

    • on: Enables the feature. If the feature is not configured for the table, it is enabled by default.

    • off: Disables the feature. If the table is configured as flexible, the feature is disabled. Otherwise, the setting of the table parameter is used.

    • <null> or invalid input: The table-level configuration is used.

  • odps.sql.unstructured.text.bad.row.skipping.debug.num: Specifies the number of error results to print to stdout in Logview.

    • The maximum value is 1000.

    • If the value is <=0, this feature is disabled.

    • If the value is invalid, this feature is disabled.

• Interaction between session-level parameters and table properties

  tbl property	session flag	result
  rigid	on	On, Forced on
  	off
  	<null>, an invalid value, or the parameter is not configured
  flexible	on	On
  	off	Off, Disabled by session
  	<null>, an invalid value, or the parameter is not configured	On
  Not configured	on	On, Enabled by session
  	off	Off
  	<null>, an invalid value, or the parameter is not configured

Examples

1. Prepare the data

   Upload the test data file csv_bad_row_skipping.csv, which contains bad rows, to a directory in OSS, such as oss-mc-test/badrow/.

2. Create the CSV external tables

   The following examples show three scenarios based on different combinations of table-level and session-level parameters.

   • Table parameter: odps.text.option.bad.row.skipping = flexible | rigid | <not set>

   • Session flag: odps.sql.unstructured.text.bad.row.skipping = on | off | <not set>

   Parameter not set

   -- No table-level parameter is set. Queries will fail on bad rows unless overridden by the session-level 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://<your-bucket-name>/<your-file-path>/>';

   Flexible skipping

   -- The table is configured to skip bad rows, but this can be disabled by the session-level 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://<your-bucket-name>/<your-file-path>/>'
   tblproperties (
     'odps.text.option.bad.row.skipping' = 'flexible'   -- Enables flexible skipping, which can be disabled at the session level.
   );

   Rigid skipping

   -- The table is configured to forcibly skip bad rows. This cannot be disabled at the session level.
   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://<your-bucket-name>/<your-file-path>/>'
   tblproperties (
     'odps.text.option.bad.row.skipping' = 'rigid'  -- Forces skipping on.
   );

3. Verify query results

   Parameter not set

   -- The following command enables skipping, but it is immediately overridden by the next command in this example.
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- This command disables skipping and is the active setting for the SELECT query below, causing it to fail.
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- You can use this command to print details of skipped rows when skipping is enabled. It has no effect here because the query fails.
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_csv_bad_data_skipping_flag;

   The query fails with the following error: FAILED: ODPS-0123131:User defined function exception

   Flexible skipping

   -- The following command enables skipping, but it is immediately overridden by the next command in this example.
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- This command disables skipping, overriding the table's 'flexible' setting. It is the active setting for the SELECT query below, causing it to fail.
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- Print details of up to 10 bad rows at the session level. The maximum is 1,000. A value of 0 or less disables printing.
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_csv_bad_data_skipping_flexible;

   The query fails with the following error: FAILED: ODPS-0123131:User defined function exception

   Rigid skipping

   -- This command is redundant because the 'rigid' setting already enforces skipping.
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- This command attempts to disable skipping, but it is ignored because the 'rigid' table setting cannot be overridden.
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- This command prints details for up to 10 bad rows that are skipped by the 'rigid' setting.
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_csv_bad_data_skipping_rigid;

   The following result is returned:

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

The built-in CSV/TSV parser (OpenCSVSerde)

The built-in CSV/TSV parser (OpenCSVSerde) requires each line of data in a CSV file to be separated by \r\n or similar characters, and columns must not contain \r\n The parallel splitting and data integrity logic is as follows:

• first, the file is split by split size, and some lines may be cut in the middle.

• When subsequent workers consume splits, all splits except the first one actively skip the leading partial or full line.

• Each worker must also actively consume the trailing partial or full line, even if it falls within the next split's range.

This parser supports parallel splitting but does not support quote escaping.

The open-source CSV parser (CsvStorageHandler / TsvStorageHandler)

The open-source CSV parser (CsvStorageHandler / TsvStorageHandler) takes quote escaping into account when reading CSV/TSV files and supports \r\n. It can handle scenarios where special characters such as nested quotes or \r\n appear inside quotes.

For example, in the data "a\ra","b\nb","cc""cc", the \r\n and double quotes can be correctly parsed and output, supporting cross-line values. However, because the actual line break positions can only be determined through data parsing, the file cannot be simply split by \r\n. As a result, parallel consumption of a single file is not supported.

If you confirm that the data columns do not contain \r\n and \r\n is used only as a line delimiter, you can enable parallel splitting by setting odps.sql.unstructured.data.single.file.split.enabled. In this case, a large file can be divided into multiple splits by split size, and the built-in Text Extractor automatically aligns to line break boundaries to ensure data integrity.

Prerequisites

• A MaxCompute project. See Create a MaxCompute project.

• An OSS bucket in the same region as your MaxCompute project. See Create a bucket and Manage folders. MaxCompute can create OSS folders automatically — you do not need to create them before running SQL.

• OSS access permissions via an Alibaba Cloud account, a Resource Access Management (RAM) user, or a RAM role. See Authorize access in STS mode for OSS.

• The CreateTable permission in your MaxCompute project. See MaxCompute permissions.

Create an OSS external table with the built-in text parser

Create an external table with a header row

Create a Demo11 directory in the oss-mc-test bucket from the sample data, and run the following statements:

--Create the external table.
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 data.
INSERT OVERWRITE TABLE mf_oss_wtt VALUES (1, 'val1', 1.1),(2, 'value2', 1.3);

--Query data.
--When you create the table, you can define all columns as STRING. Otherwise, an error occurs when the header is read.
--Alternatively, add the 'odps.text.option.header.lines.count' = '1' parameter to the table definition to skip the header.
SELECT * FROM mf_oss_wtt;

This example uses the aliyunodpsdefaultrole RAM role. If you use a different RAM role, replace aliyunodpsdefaultrole with the name of your target RAM role and grant it the required permissions to access OSS.

The command returns the following result:

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

Create an external table with mismatched columns

1. Create a demo directory in the oss-mc-test bucket from the sample data and upload the test.csv file. The test.csv file contains the following content:

   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. Create the external tables.

   1. Set the handling method for rows with inconsistent column counts to TRUNCATE.

      -- Drop the table.
      DROP TABLE test_mismatch;
      -- Create the external table.
      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. Specify the handling method for rows with inconsistent column counts as IGNORE.

      -- Drop the table.
      DROP TABLE test_mismatch01;
      -- Create the external table.
      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. Query the data in the tables.

      • Query the test_mismatch table.

        SELECT * FROM test_mismatch;
        
        --Returned result
        +----+-------+---------------+---------------+
        | 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 |
        +----+-------+---------------+---------------+

      • Query the test_mismatch01 table.

        SELECT * FROM test_mismatch01;
        
        --Returned result
        +----+-------+----------------+-------------+
        | id | name  | dect           | col4        |
        +----+-------+----------------+-------------+
        | 2  | kyle2 | this is desc2  | this is two  
        +----+-------+----------------+-------------+

Create an external table with the open-source parser

This example shows how to use the built-in open source parser to create an OSS external table to read a comma-separated file while ignoring the header and footer rows.

1. Create a demo-test directory in the oss-mc-test bucket from the sample data and upload the test.csv file.

   The test file contains the following data:

   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 the external table, specify the comma as the separator, and set parameters to ignore the header and footer rows.

   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/***/'
   -- Set parameters to ignore the header and footer rows.
   TBLPROPERTIES (
   "skip.header.line.COUNT"="1",
   "skip.footer.line.COUNT"="1"
   )
   ;

3. Read data from the external table.

   SELECT * FROM ext_csv_test08;
   
   -- The result includes 8 rows of data because the header and footer rows are ignored.
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+
   | 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          | 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         |
   +------------+------------+------------+------------+------------------+-------------------+----------------+------------+

Create a CSV external table with custom time types

For details about parsing and output formats for custom time types in CSV, see Flexible type compatibility with Smart Parse.

1. Create a CSV external table that uses various time data types, such as DATE, DATETIME, TIMESTAMP, and 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. After the data is inserted, the content of the CSV file is as follows:

   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. Query the data again to view the result.

   SELECT * FROM test_csv;

   The command returns the following result:

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

Column count mismatch error

• Symptom

  This error occurs when the column count in a row of a CSV or TSV file does not match the one defined in the external table DDL. MaxCompute reports an error similar to FAILED: ODPS-0123131:User defined function exception - Traceback:java.lang.RuntimeException: SCHEMA MISMATCH:xxx.

• Resolution

  You can control how MaxCompute handles the mismatch by setting the odps.sql.text.schema.mismatch.mode parameter at the session level:

  • SET odps.sql.text.schema.mismatch.mode=error: Fails the query when a column count mismatch occurs. This is the default behavior.

  • SET odps.sql.text.schema.mismatch.mode=truncate: If a row has more columns than defined in the external table DDL, the extra columns are discarded. If a row has fewer columns, the missing columns are filled with NULL.