MaxCompute:CSV and TSV foreign tables

Supported data types

For more information about MaxCompute data types, see Data types V1.0 and Data types V2.0.

For more information about Smart Parse, see Smart Parse for more flexible type compatibility.

Supported compression formats

When you read from or write to compressed OSS files, you can add the with serdeproperties property to the CREATE TABLE statement. For more information, see with serdeproperties parameters.

Schema evolution support

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.

Unique parameters

Parameter settings

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

  • rigid: The skip logic must be executed and cannot be overwritten by session-level or project-level settings.

  • flexible: Enables flexible skipping at the data plane. This setting can be overwritten by session-level or project-level settings.

• Session-level or project-level parameters

  • odps.sql.unstructured.text.bad.row.skipping: This parameter can overwrite the flexible table-level parameter but not the rigid table-level parameter.

    • on: Actively enabled. If this parameter is not configured for the table, it is automatically enabled.

    • off: Actively disabled. If the table-level parameter is set to flexible, this feature is disabled. In other cases, the table-level parameter takes precedence.

    • <null>/If the input is invalid: The behavior is determined only by the table-level configuration.

  • odps.sql.unstructured.text.bad.row.skipping.debug.num: Specifies the number of error results that can be printed to standard output (stdout) in Logview.

    • The maximum value is 1000.

    • If the value is less than or equal to 0, this feature is disabled.

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

• Interaction between session-level and table-level parameters

  tbl property	session flag	result
  rigid	on	On, forcibly enabled
  	off
  	<null>, an invalid value, or this parameter is not configured
  flexible	on	On
  	off	Off, disabled by the session
  	<null>, an invalid value, or this parameter is not configured	On
  Not configured	on	Status: Session enabled
  	off	Off
  	<null>, an invalid value, or this parameter is not configured

Usage examples

1. Data preparation

   Upload the test data file csv_bad_row_skipping.csv, which contains dirty data, to the oss-mc-test/badrow/ directory in OSS.

2. Create a CSV foreign table

   The following three scenarios describe different combinations of table-level parameters and session-level flags.

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

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

   No table-level parameter is configured

   -- No table-level parameter is configured. Errors are reported as they occur. The behavior is controlled 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://databucketpath>';

   The table-level parameter is set to skip error rows, but this can be disabled at the session level

   -- The table-level parameter is set to skip error rows, but this can be actively 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://databucketpath>'
   tblproperties (
     'odps.text.option.bad.row.skipping' = 'flexible'   -- Flexibly enabled. Can be disabled at the session level.
   );

   The table-level parameter is set to skip error rows, and this cannot be disabled at the session level

   -- The table-level parameter is set to forcibly skip error 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://databucketpath>'
   tblproperties (
     'odps.text.option.bad.row.skipping' = 'rigid'  -- Forcibly enabled.
   );

3. Verify the query result

   No table-level parameter is configured

   -- Enable at the session level.
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- Disable at the session level. If the table-level parameter is set to 'flexible', this feature is disabled. If the table-level parameter is set to 'rigid', this setting has no effect.
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- Print problematic row data at the session level. A maximum of 1,000 rows can be printed. If the value is less than or equal to 0, printing is disabled.
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_csv_bad_data_skipping_flag;

   The following error is returned: FAILED: ODPS-0123131:User defined function exception

   The table-level parameter is set to skip error rows, but this can be disabled at the session level

   -- Enable at the session level.
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- Disable at the session level. If the table-level parameter is set to 'flexible', this feature is disabled. If the table-level parameter is set to 'rigid', this setting has no effect.
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- Print problematic row data at the session level. A maximum of 1,000 rows can be printed. If the value is less than or equal to 0, printing is disabled.
   SET odps.sql.unstructured.text.bad.row.skipping.debug.num=10;
   
   SELECT * FROM test_csv_bad_data_skipping_flexible;

   The following error is returned: FAILED: ODPS-0123131:User defined function exception

   The table-level parameter is set to skip error rows, and this cannot be disabled at the session level

   -- Enable at the session level.
   SET odps.sql.unstructured.text.bad.row.skipping=on;
   
   -- Disable at the session level. If the table-level parameter is set to 'flexible', this feature is disabled. If the table-level parameter is set to 'rigid', this setting has no effect.
   SET odps.sql.unstructured.text.bad.row.skipping=off;
   
   -- Print problematic row data at the session level. A maximum of 1,000 rows can be printed. If the value is less than or equal to 0, printing is disabled.
   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         | 
   +------------+------------+

Prerequisites

• A MaxCompute project is created.

• An OSS bucket and folder are available. For more information, see Create a bucket and Manage folders.

  MaxCompute supports automatic folder creation in OSS. If an SQL statement involves external tables and user-defined functions (UDFs), you can use a single statement to read from and write to the tables and use the UDFs. You can also create folders manually.

  MaxCompute is deployed only in specific regions. To avoid potential issues with cross-region data connections, ensure that your OSS bucket is in the same region as your MaxCompute project.

• Authorization

  • You must have permissions to access OSS. You can use an Alibaba Cloud account, a Resource Access Management (RAM) user, or a RAM role to access OSS external tables. For more information about authorization, see Authorize access in STS mode for OSS.

  • You must have the CreateTable permission in the MaxCompute project. For more information about table permissions, see MaxCompute permissions.

Create an OSS foreign table using the built-in text data parser

Create an OSS foreign table and specify the first row of the corresponding OSS file as the table header

Create the Demo11 folder in the oss-mc-test bucket from Example data and run the following statements:

--Create a foreign 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 set the data type of all fields to STRING. Otherwise, an error is reported when the table header is read.
--Alternatively, add the parameter to skip the table header when you create the table: 'odps.text.option.header.lines.count' = '1'
SELECT * FROM mf_oss_wtt;

The role used in this example is aliyunodpsdefaultrole. If you use another role, you can replace aliyunodpsdefaultrole with the name of your role and grant the role permissions to access OSS.

The following result is returned:

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

Create an OSS foreign table where the number of columns does not match the number of columns in the OSS data

1. Create the demo folder in the oss-mc-test bucket from Example 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 a foreign table.

   1. Set the handling mode for rows with a mismatched number of columns to TRUNCATE.

      -- Delete the table.
      DROP TABLE test_mismatch;
      -- Create a new foreign 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. Set the handling mode for rows with a mismatched number of columns to IGNORE.

      -- Delete the table.
      DROP TABLE test_mismatch01;
      -- Create a new foreign 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 table data.

      • Query the test_mismatch table.

        SELECT * FROM test_mismatch;
        
        --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;
        
        --Result
        +----+-------+----------------+-------------+
        | id | name  | dect           | col4        |
        +----+-------+----------------+-------------+
        | 2  | kyle2 | this is desc2  | this is two  
        +----+-------+----------------+-------------+

Create an OSS foreign table using the built-in open source parser

This example shows how to create an OSS foreign table using the built-in open source parser to read a comma-separated file and ignore the first and last rows.

1. Create the demo-test folder in the oss-mc-test bucket from Example data and upload the test file test.csv.

   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 a foreign table, specify the separator as a comma, and set parameters to ignore the first and last 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 first and last rows.
   TBLPROPERTIES (
   "skip.header.line.COUNT"="1",
   "skip.footer.line.COUNT"="1"
   )
   ;

3. Read the foreign table.

   SELECT * FROM ext_csv_test08;
   
   -- Only 8 rows were read, and the header and footer rows were ignored.
   +------------+------------+------------+------------+------------------+-------------------+------------+------------+
   | 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          | 
   +------------+------------+------------+------------+------------------+-------------------+------------+------------+

Create an OSS foreign table in CSV format with custom date and time data types

For more information about custom parsing and output formats for time types in CSV, see Smart Parse for more flexible type compatibility.

1. Create a CSV foreign table for each time data type (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:

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

   SELECT * FROM test_csv;

   The following result is returned:

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

A "column count mismatch" error is reported when reading CSV/TSV data

• Symptom

  If the number of columns in a CSV/TSV file does not match the number of columns in the foreign table DDL, a "column count mismatch" error is reported when you read the CSV/TSV data. For example: FAILED: ODPS-0123131:User defined function exception - Traceback:java.lang.RuntimeException: SCHEMA MISMATCH:xxx.

• Solution

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

  • SET odps.sql.text.schema.mismatch.mode=error: If the number of columns does not match, the system reports an error.

  • SET odps.sql.text.schema.mismatch.mode=truncate: If the file has more columns than the foreign table DDL, the extra data is discarded. If the file has fewer columns than the foreign table DDL, the missing columns are filled with NULL.