MaxCompute：CSV/TSV外部表格

文法結構

各格式的外部表格文法結構詳情，請參見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 字元串被讀入表中後，均會被解析為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外部表格

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

通過內建開源解析器建立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 |
   +------------+---------------------+---------------------+------------------------+