MaxCompute:CSV/TSV external tables

Syntax

For more information about the syntax for creating external tables in different formats, see OSS external tables.

• Built-in text data parser

  CSV format

  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 format

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

• Built-in open source data parser

  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

Notes

• For any data type, an empty string in a CSV data file is parsed as NULL when read into the table.

• Complex types such as STRUCT, ARRAY, and MAP are not supported because their values can easily conflict with common CSV delimiters, such as the comma (,), which can cause parsing to fail.

• BINARY and INTERVAL types are not currently supported. To use these types, you can contact MaxCompute technical support.

• For INT, SMALLINT, TINYINT, BIGINT, FLOAT, DOUBLE, and DECIMAL data types, the default parsing capability is flexible.

  If you want to parse only basic number strings, you can set the odps.text.option.smart.parse.level parameter to naive in tblproperties. This configures the parser to support only regular number strings, such as "123" and "123.456". Parsing other string formats results in an error.

• For the four date and time-related types (DATE, DATETIME, TIMESTAMP, and TIMESTAMP_NTZ), the underlying code uses java.time.format.DateTimeFormatter for processing. MaxCompute has several built-in default parsing formats. You can also define multiple parsing formats and one output format by setting odps.text.option.<date|datetime|timestamp|timestamp_ntz>.io.format in tblproperties. You can separate parsing formats with a hash symbol (#).

  • Custom formats have a higher priority than MaxCompute's built-in parsing formats. The output format uses the first custom format.

    For example, if you define a custom format string for the DATE type as pattern1#pattern2#pattern3, you can parse strings that match pattern1, pattern2, or pattern3. However, when you write data to a file, the output uses the pattern1 format. For more information, see DateTimeFormatter.

  • Do not use 'z' (time-zone name) as a custom time zone format, especially for users in China, because 'z' can be ambiguous in some contexts.

    For example, 'CST' usually means China Standard Time (UTC+8) in China. However, when 'CST' is parsed by java.time.format.DateTimeFormatter, it is identified as US Central Standard Time (UTC-6). This can lead to unexpected input or output. Use the 'x' (zone-offset) or 'VV' (time-zone ID) pattern instead.

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

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

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

--Create an 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 fields as STRING. Otherwise, an error occurs when the header is read.
--Alternatively, add the 'odps.text.option.header.lines.count' = '1' parameter when you create the table to skip the header.
SELECT * FROM mf_oss_wtt;

The following result is returned:

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

Create an OSS external table with a mismatched column count

1. Create the demo folder 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 an external table.

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

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

      --Delete the table.
      DROP TABLE test_mismatch01;
      --Create a new 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 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 external table using the built-in open source parser

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

1. Create the demo-test folder in the oss-mc-test bucket from the sample 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 an external table, specify the delimiter 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,
     locationLongitude 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 external table.

   SELECT * FROM ext_csv_test08;
   
   -- The result shows that only 8 rows were read, ignoring the first and last rows.
   +------------+------------+------------+------------+------------------+-------------------+------------+------------+
   | vehicleid  | recordid   | patientid  | calls      | locationlatitute | locationlongitude | 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 a CSV-formatted OSS external table with custom date and time data types

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

1. Create a CSV external table for various time data types (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 you insert the data, 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 see 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 |
   +------------+---------------------+---------------------+------------------------+