MaxCompute：建立OSS外部表格

前提條件

注意事項

使用限制

OSS外部表格不支援cluster屬性。

操作入口

建立OSS外部表格文法

建立OSS外部表格的情境、相應文法格式及樣本如下。詳細文法參數及屬性列表資訊，請參見參考：文法參數說明、參考：with serdeproperties屬性列表和參考：tblproperties屬性列表。

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> 
(
<col_name> <data_type>,
...
)
[partitioned BY (<col_name> <data_type>, ...)] 
stored BY '<StorageHandler>'  
WITH serdeproperties (
 ['<property_name>'='<property_value>',...]
) 
location '<oss_location>';

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
(
<col_name> <data_type>,
...
)
[partitioned BY (<col_name> <data_type>, ...)]
[row format serde '<serde_class>'
  [WITH serdeproperties (
    ['<property_name>'='<property_value>',...])
  ]
]
stored AS <file_format> 
location '<oss_location>' 
[USING '<resource_name>']
[tblproperties ('<tbproperty_name>'='<tbproperty_value>',...)];

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> 
(
<col_name> <date_type>,
...
)
[partitioned BY (<col_name> <data_type>, ...)] 
stored BY '<StorageHandler>' 
WITH serdeproperties (
 ['<property_name>'='<property_value>',...]
) 
location '<oss_location>' 
USING '<jar_name>';

補全OSS外部表格分區資料文法

樣本：資料準備

樣本：通過內建文本資料解析器建立OSS外部表格-非分區表

與樣本：資料準備中的Demo1/目錄建立映射關係。建立OSS外部表格命令樣本如下。

create external table if not exists mc_oss_csv_external1
(
vehicleId int,
recordId int,
patientId int,
calls int,
locationLatitute double,
locationLongtitue double,
recordTime string,
direction string
)
stored by 'com.aliyun.odps.CsvStorageHandler' 
with serdeproperties (
 'odps.properties.rolearn'='acs:ram::xxxxxx:role/aliyunodpsdefaultrole'
) 
location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/Demo1/';

您可以執行desc extended mc_oss_csv_external1;命令查看建立好的OSS外部表格結構資訊。

樣本：通過內建文本資料解析器建立OSS外部表格-分區表

與樣本：資料準備中的Demo2/目錄建立映射關係。建立OSS外部表格並引入分區資料命令樣本如下。

create external table if not exists mc_oss_csv_external2
(
vehicleId int,
recordId int,
patientId int,
calls int,
locationLatitute double,
locationLongtitue double,
recordTime string
)
partitioned by (
direction string
)
stored by 'com.aliyun.odps.CsvStorageHandler' 
with serdeproperties (
 'odps.properties.rolearn'='acs:ram::xxxxxx:role/aliyunodpsdefaultrole'
) 
location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/Demo2/';
--引入分區資料。
msck repair table mc_oss_csv_external2 add partitions;
--等效於如下語句。
alter table mc_oss_csv_external2 add partition (direction = 'N') partition (direction = 'NE') partition (direction = 'S') partition (direction = 'SW') partition (direction = 'W');

您可以執行desc extended mc_oss_csv_external2;命令查看建立好的外部表格結構資訊。

樣本：通過內建文本資料解析器建立OSS外部表格-壓縮資料

與樣本：資料準備中的Demo3/目錄建立映射關係。建立OSS外部表格命令樣本如下。

create external table if not exists mc_oss_csv_external3
(
vehicleId int,
recordId int,
patientId int,
calls int,
locationLatitute double,
locationLongtitue double,
recordTime string,
direction string
)
stored by 'com.aliyun.odps.CsvStorageHandler' 
with serdeproperties (
 'odps.properties.rolearn'='acs:ram::xxxxxx:role/aliyunodpsdefaultrole',
 'odps.text.option.gzip.input.enabled'='true',
 'odps.text.option.gzip.output.enabled'='true' 
) 
location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/Demo3/';

您可以執行desc extended mc_oss_csv_external3;命令查看建立好的外部表格結構資訊。

樣本：通過內建開來源資料解析器建立OSS外部表格

• 映射PARQUET資料檔案

  CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
  (
  <col_name> <data_type>,
  ...
  )
  [partitioned BY (<col_name> <data_type>, ...)]
  stored AS parquet  
  location '<oss_location>';
  SELECT ... FROM <mc_oss_extable_name> ...;

• 映射TEXTFILE資料檔案

  • 關聯TEXT資料建表示例

    CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
    (
    <col_name> <data_type>,
    ...
    )
    [partitioned BY (<col_name> <data_type>, ...)]
    row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
    stored AS textfile
    location '<oss_location>';
    SELECT ... FROM <mc_oss_extable_name> ...;

    建表時不支援自訂row format。row format預設值如下。

    FIELDS TERMINATED BY ：'\001'
    ESCAPED BY ：'\'
    COLLECTION ITEMS TERMINATED BY ：'\002'
    MAP KEYS TERMINATED BY ：'\003'
    LINES TERMINATED BY ：'\n'
    NULL DEFINED AS ：'\N'

  • 關聯CSV資料建表示例

    --關閉native的text reader。
    SET odps.ext.hive.lazy.simple.serde.native=false;
    --建立OSS外部表格。
    CREATE EXTERNAL TABLE <mc_oss_extable_name>
    (
    <col_name> <data_type>,
    ...
    )
    row format serde 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
    WITH serdeproperties (
    "separatorChar" = ",",
    "quoteChar"= '"',
    "escapeChar"= "\\"
    )
    stored AS textfile
    location '<oss_location>'
    tblproperties (
    "skip.header.line.count"="1",
    "skip.footer.line.count"="1"
    );
    SELECT ... FROM <mc_oss_extable_name> ...;

    說明

    OpenCSVSerde只支援STRING類型。

  • 關聯JSON資料建表示例

    CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
    (
    <col_name> <data_type>,
    ...
    )
    [partitioned BY (<col_name> <data_type>, ...)]
    row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
    stored AS textfile
    location '<oss_location>';
    SELECT ... FROM <mc_oss_extable_name> ...;

• 映射ORC資料檔案

  CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
  (
  <col_name> <data_type>,
  ...
  )
  [partitioned BY (<col_name> <data_type>, ...)]
  stored AS orc
  location '<oss_location>';
  SELECT ... FROM <mc_oss_extable_name> ...;

  說明

  具體樣本，請參見OSS外部表格根據欄位名稱Mapping資料。

• 映射RCFILE資料檔案

  CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_rcfile_extable>
  (
  <col_name> <data_type>,
  ...
  )
  [partitioned BY (<col_name> <data_type>, ...)]
  row format serde 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe'
  stored AS rcfile
  location '<oss_location>';
  SELECT ... FROM <mc_oss_rcfile_extable> ...;

• 映射AVRO資料檔案

  CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
  (
  <col_name> <data_type>,
  ...
  )
  [partitioned BY (<col_name> <data_type>, ...)]
  stored AS avro
  location '<oss_location>';
  SELECT ... FROM <mc_oss_extable_name> ...;

• 映射SEQUENCEFILE資料檔案

  CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
  (
  <col_name> <data_type>,
  ...
  )
  [partitioned BY (<col_name> <data_type>, ...)]
  stored AS sequencefile
  location '<oss_location>';
  SELECT ... FROM <mc_oss_extable_name> ...;

• 映射Hudi格式資料檔案

  說明

  • Hudi外表只支援讀取外表映射的全部檔案的資料，不支援自動隱藏系統列，不支援增量讀、快照讀操作，不支援寫操作。建議您使用MaxCompute Delta表或Paimon外部表格等功能實現ACID方式的讀寫操作。

  • MaxCompute系統預設整合的Hudi SDK版本為org.apache.hudi:hudi-hadoop-mr-bundle:0.12.2-emr-1.0.6，無法保證Hudi SDK向前或向後相容，相容性由開源社區保證。

  • MaxCompute將提供支援使用者上傳jar並指定SDK相容版本的方式建立外表，用於讀取相容特定版本的資料。

  CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
  (
  <col_name> <data_type>,
  ...
  )
  [partitioned BY (<col_name> <data_type>, ...)]
  ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
  stored AS 
  INPUTFORMAT 'org.apache.hudi.hadoop.HoodieParquetInputFormat'
  OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
  location '<oss_location>';
  SELECT ... FROM <mc_oss_extable_name> ...;

• 映射Delta Lake格式資料檔案

  說明

  • Delta Lake外表只支援讀取外表映射的全部檔案的資料，不支援自動隱藏系統列，不支援增量讀、快照讀操作，不支援寫操作。建議您使用MaxCompute Delta表或Paimon外部表格等功能實現ACID方式的讀寫操作。

  • MaxCompute系統預設整合的Delta Lake SDK版本為io.delta:delta-core_2.11:0.2.0.5，無法保證Delta Lake SDK向前或向後相容，相容性由開源社區保證。

  • MaxCompute將提供支援使用者上傳jar並指定SDK相容版本的方式建立外表，用於讀取相容特定版本的資料。

  CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> (
     <col_name> <data_type>,
     ...
  )
  [partitioned BY (<col_name> <data_type>, ...)]
  ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
  STORED AS
  INPUTFORMAT 'io.delta.hive.DeltaInputFormat'
  OUTPUTFORMAT 'io.delta.hive.DeltaOutputFormat'
  LOCATION '<oss_location>';
  SELECT ... FROM <mc_oss_extable_name> ...;

• 映射Paimon格式資料檔案

  CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
  (
  <col_name> <data_type>,
  ...
  )
  [partitioned BY (<col_name> <data_type>, ...)]
  stored BY 'org.apache.paimon.hive.PaimonStorageHandler'
  WITH serdeproperties (
      'odps.properties.rolearn'='acs:ram::xxxxxxxxxxxxx:role/aliyunodpsdefaultrole'
  )
  location '<oss_location>'
  USING 'paimon_maxcompute_connector.jar';

  paimon_maxcompute_connector.jar需要預先上傳至MaxCompute專案，詳情請參見Paimon外部表格。

樣本：通過自訂解析器建立OSS外部表格

與樣本：資料準備中的SampleData/目錄建立映射關係。

操作流程如下：

1. 使用MaxCompute Studio開發TextExtractor.java、TextOutputer.java、SplitReader.java和TextStorageHandler.java四個Java程式。

   更多開發Java程式資訊，請參見開發UDF。

2. 通過MaxCompute Studio的一鍵式打包功能，將TextStorageHandler.java打包上傳為MaxCompute資源。

   假設，此處打包的資源名稱為javatest-1.0-SNAPSHOT.jar。更多打包上傳資訊，請參見打包、上傳及註冊。

   說明

   如果有多個依賴請分別打包後上傳為MaxCompute資源。

3. 執行如下命令建立OSS外部表格。

   CREATE EXTERNAL TABLE [IF NOT EXISTS] ambulance_data_txt_external
   (
   vehicleId int,
   recordId int,
   patientId int,
   calls int,
   locationLatitute double,
   locationLongtitue double,
   recordTime string,
   direction string
   )
   stored BY 'com.aliyun.odps.udf.example.text.TextStorageHandler' 
     WITH serdeproperties (
   'delimiter'='|',  
   'odps.properties.rolearn'='acs:ram::xxxxxxxxxxxxx:role/aliyunodpsdefaultrole'
   )
   location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/SampleData/'
   USING 'javatest-1.0-SNAPSHOT.jar'; 

   其中delimiter是OSS資料的分隔字元名稱。

   您可以執行desc extended ambulance_data_txt_external;命令查看建立好的外部表格結構資訊。

   說明

   對於自訂解析器，預設不會對資料分區，防止解析器出現正確性問題。如果您確認可以處理分區，需要使用如下命令允許進行資料分區，即啟動多個mapper。

   set odps.sql.unstructured.data.single.file.split.enabled=true;

樣本：通過自訂解析器建立OSS外部表格-非文本資料

樣本：建立OSS外部表格並指定對應OSS檔案的第一行為表頭

--建立外部表格
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-beijing-internal.aliyuncs.com/mfosscostfee/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. 準備如下資料，儲存為CSV格式上傳至OSS的doc-test-01/demo目錄下。

   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. 建立外部表格。

   • 指定對於列數不一致行的處理方式為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-shanghai-internal.aliyuncs.com/doc-test-01/demo/';

   • 指定對於列數不一致行的處理方式為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-shanghai-internal.aliyuncs.com/doc-test-01/demo/';

3. 查詢表資料。

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

   • SELECT * FROM test_mismatch01;
     
     --返回結果
     +----+------+------+------+
     | id | name | dect | col4 |
     +----+------+------+------+
     | 2  | kyle2 | this is desc2 | this is two |
     +----+------+------+------+

參考：文法參數說明

上述文法中各參數的含義如下。

參考：with serdeproperties屬性列表

參考：tblproperties屬性列表

相關文檔

建立OSS外部表格後，您可以通過外部表格讀取儲存在OSS目錄中的資料檔案。具體操作請參見讀取OSS資料。