MaxCompute：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。