全部產品
Search
文件中心

MaxCompute:建立OSS外部表格

更新時間:Nov 20, 2024

MaxCompute支援您在專案中建立OSS(Object Storage Service)外部表格,與儲存服務OSS上的目錄建立映射關係,您可以通過OSS外部表格訪問OSS目錄下的資料檔案中的非結構化資料,或將MaxCompute專案中的資料寫入OSS目錄。本文為您介紹建立OSS外部表格的文法、參數資訊並提供樣本。

背景資訊

Object Storage Service服務OSS是一種海量、安全、低成本、高可靠的雲端儲存體服務,適合存放任意類型的資料檔案。當您需要使用MaxCompute讀取儲存在OSS目錄中的資料或需要將MaxCompute專案中的資料寫入OSS目錄時,可以在MaxCompute專案中建立OSS外部表格,以建立與目錄的映射關係。

OSS外部表格包含分區表和非分區表兩種類型,實際需要建立哪種類型的表,主要取決於OSS中資料檔案的儲存路徑格式。當資料檔案以分區路徑方式儲存時,需要建立分區表;否則建立非分區表。更多讀取分區資料資訊,請參見讀取以分區方式儲存的OSS資料

前提條件

在建立OSS外部表格前,請確認執行操作的帳號已滿足如下條件:

  • 已授予訪問OSS的許可權。

    支援阿里雲帳號(主帳號)、RAM使用者或RAMRole身份訪問OSS外部表格。更多授權資訊,請參見OSS的STS模式授權

  • 已具備在MaxCompute專案中建立表(CreateTable)的許可權。

    更多表操作許可權資訊,請參見MaxCompute許可權

注意事項

在使用OSS外部表格時,您需要注意:

  • 對於不同格式的資料檔案,建立OSS外部表格語句僅個別參數設定有出入,請您仔細閱讀建立OSS外部表格文法及參數說明,確保建立符合實際業務需求的外部表格,否則讀取OSS資料或將資料寫入OSS操作會執行失敗。

  • OSS外部表格只是記錄與OSS目錄的映射關係。當刪除OSS外部表格時,不會刪除映射的OSS目錄下的資料檔案。

  • 如果OSS資料檔案類型為歸檔檔案,需要先解凍檔案。更多解凍操作,請參見解凍檔案

  • 需使用OSS傳統網路網域名稱。對於公網的網路網域名稱,MaxCompute不保證網路連通性。

使用限制

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

操作入口

MaxCompute支援您在如下平台建立OSS外部表格。

建立方式

平台

基於MaxCompute SQL建立OSS外部表格

MaxCompute用戶端

使用DataWorks串連

DataWorks控制台-ODPS SQL節點

MaxCompute Studio-SQL程式

以可視化方式建立OSS外部表格

MaxCompute Studio-SQL程式

DataWorks控制台-外部表格

建立OSS外部表格文法

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

情境

文法格式

支援讀取或寫入OSS的資料檔案格式

樣本

通過內建文本資料解析器建立外部表格

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>';
  • CSV

  • 以GZIP方式壓縮的CSV

  • TSV

  • 以GZIP方式壓縮的TSV

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

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

  • 以ZSTD、SNAPPY或GZIP方式壓縮的PARQUET

  • TEXTFILE(JSON、TEXT)

  • 以SNAPPY、LZO、BZ2、GZ、DEFLATE方式壓縮的TEXTFILE

  • ORC

  • 以SNAPPY、ZLIB方式壓縮的ORC

  • RCFILE

  • AVRO

  • SEQUENCEFILE

說明

僅支援讀取DLF產生的Hudi資料。

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

通過自訂解析器建立外部表格

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外部表格為分區表時,需要額外執行引入分區資料的操作。

  • 方式一(推薦):自動解析OSS目錄結構,識別分區,為OSS外部表格添加分區資訊。

    通過這種方式,MaxCompute會根據您建立OSS外部表格時指定的分區目錄,自動補全OSS外部表格的分區,而不用逐個按照分區列名和名稱增加,這適用於一次性補全全部缺失的歷史分區的情境,

    msck repair TABLE <mc_oss_extable_name> ADD partitions [ WITH properties (key:VALUE, key: VALUE ...)];
    說明

    該方式不適用於處理增量資料的補充,尤其是在OSS目錄包含大量分區(如超過1000個)的情況下。由於當新增分區遠少於已有分區時,頻繁使用msck命令會導致對OSS目錄大量的重複掃描和中繼資料更新要求,這將顯著降低命令執行的效率。因此,對於需要更新增量分區的情境,建議您採用方式二。

  • 方式二:手動執行如下命令為OSS外部表格添加分區資訊。

    當歷史分區已經建立完成,需要頻繁地周期性追加分區,建議採用該方式,在執行資料寫入任務之前提前建立好分區。分區建立完成後,即使OSS上有新資料寫入,也無需重新整理對應分區,外部表格即可讀取OSS目錄上的最新資料。

    ALTER TABLE < mc_oss_extable_name >
        ADD PARTITION (< col_name >= < col_value >)[
    ADD PARTITION (< col_name >= < col_value >)...][location URL];

    col_namecol_value的值需要與分區資料檔案所在目錄名稱對齊。假設,分區資料檔案所在的OSS目錄結構如下圖,col_name對應directioncol_value對應N、NE、S、SW、W。一個add partition對應一個子目錄,多個OSS子目錄需要使用多個add partition分區路徑

  • 樣本

    1. 在OSS上建立目錄demo8並分別在下面建立兩個分區檔案夾,分別放入對應的檔案。

      • 分區檔案夾:$pt1=1/$pt2=2,檔案名稱:demo8-pt1.txt

      • 分區檔案夾:$pt1=3/$pt2=4,檔案名稱:demo8-pt2.txt

    2. 建立外部表格並指定pt欄位。

      --建立外部表格
      CREATE EXTERNAL TABLE mf_oss_spe_pt (id int, name string)
      partitioned BY (pt1 string, pt2 string)
      stored AS TEXTFILE
      location "oss://oss-cn-beijing-internal.aliyuncs.com/mfoss*******/demo8/";
      
      --指定分區欄位
      MSCK REPAIR TABLE  mf_oss_spe_pt ADD PARTITIONS
      WITH PROPERTIES ('odps.msck.partition.column.mapping'='pt1:$pt1,pt2:$pt2');
      
      --查詢資料
      SELECT * FROM mf_oss_spe_pt WHERE pt1=1 AND pt2=2;
      --返回
      +------------+------------+------------+------------+
      | id         | name       | pt1        | pt2        |
      +------------+------------+------------+------------+
      | 1          | kyle       | 1          | 2          |
      | 2          | nicole     | 1          | 2          |
      +------------+------------+------------+------------+
      --查詢資料
      SELECT * FROM mf_oss_spe_pt WHERE pt1=3 AND pt2=4;
      +------------+------------+------------+------------+
      | id         | name       | pt1        | pt2        |
      +------------+------------+------------+------------+
      | 3          | john       | 3          | 4          |
      | 4          | lily       | 3          | 4          |
      +------------+------------+------------+------------+                                
    3. 當OSS外表中的分區列名與OSS的目錄結構不一致時,需要指定目錄。

      --MaxCompute分區與OSS的目錄對應如下:
      --pt1=8-->8
      --pt2=8-->$pt2=8
      --添加分區
      ALTER TABLE mf_oss_spe_pt ADD PARTITION (pt1=8,pt2=8)
            location 'oss://oss-cn-beijing-internal.aliyuncs.com/mfosscostfee/demo8/8/$pt2=8/';
      --需要關閉commit mode
      --插入資料
      SET odps.sql.unstructured.oss.commit.mode=false;
      INSERT INTO mf_oss_spe_pt PARTITION (pt1=8,pt2=8) VALUES (1,'tere');
      --查詢資料
      SET odps.sql.unstructured.oss.commit.mode=false;
      SELECT * FROM mf_oss_spe_pt WHERE pt1=8 AND pt2=8;
      +------+------+-----+-----+
      | id   | name | pt1 | pt2 |
      +------+------+-----+-----+
      | 1    | tere | 8   | 8   |
      +------+------+-----+-----+                                

樣本:資料準備

為便於理解,為您提供樣本資料如下:

  • oss_endpointoss-cn-hangzhou-internal.aliyuncs.com,即華東1(杭州)。

  • Bucket名稱oss-mc-test

  • 目錄名稱Demo1/Demo2/Demo3/SampleData/

    上述四個目錄下分別上傳以下資料檔案:

    • Demo1/目錄下上傳的檔案為vehicle.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
    • Demo2/目錄下包含五個子目錄direction=N/direction=NE/direction=S/direction=SW/direction=W/,分別上傳的檔案為vehicle1.csv、vehicle2.csv、vehicle3.csv、vehicle4.csv和vehicle5.csv,用於和通過內建文本資料解析器建立的分區表建立映射關係。檔案中包含的資料資訊如下。

      --vehicle1.csv
      1,7,53,1,46.81006,-92.08174,9/15/2014 0:00
      1,10,31,1,46.81006,-92.08174,9/15/2014 0:00
      
      --vehicle2.csv
      1,2,13,1,46.81006,-92.08174,9/14/2014 0:00
      1,3,48,1,46.81006,-92.08174,9/14/2014 0:00
      1,9,4,1,46.81006,-92.08174,9/15/2014 0:00
      
      --vehicle3.csv
      1,6,9,1,46.81006,-92.08174,9/15/2014 0:00
      1,5,47,1,46.81006,-92.08174,9/14/2014 0:00
      1,6,9,1,46.81006,-92.08174,9/15/2014 0:00
      
      --vehicle4.csv
      1,8,63,1,46.81006,-92.08174,9/15/2014 0:00
      
      --vehicle5.csv
      1,4,30,1,46.81006,-92.08174,9/14/2014 0:00

      Demo2

    • Demo3/目錄下上傳的檔案為vehicle.csv.gz,壓縮包內檔案為vehicle.csv,與Demo1/目錄下的檔案內容相同,用於和攜帶壓縮屬性的OSS外部表格建立映射關係。壓縮

    • SampleData/目錄下上傳的檔案為vehicle6.csv,用於和通過開來源資料解析器建立的OSS外部表格建立映射關係。檔案中包含的資料資訊如下。

      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/14/2014 0:00|S
      1|7|53|1|46.81006|-92.08174|9/14/2014 0:00|N
      1|8|63|1|46.81006|-92.08174|9/14/2014 0:00|SW
      1|9|4|1|46.81006|-92.08174|9/14/2014 0:00|NE
      1|10|31|1|46.81006|-92.08174|9/14/2014 0:00|N

樣本:通過內建文本資料解析器建立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外部表格

  • 映射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 formatrow 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> ...;
  • 映射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> ...;
  • 映射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外部表格-非文本資料

樣本:資料準備中的SpeechSentence/目錄建立映射關係。

操作流程如下:

  1. 使用MaxCompute Studio開發SpeechSentenceSnrExtractor.java、SpeechStorageHandler.java兩個Java程式。

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

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

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

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

    CREATE EXTERNAL TABLE [IF NOT EXISTS] speech_sentence_snr_external
    (
    sentence_snr double,
    id string
    )
    stored BY 'com.aliyun.odps.udf.example.speech.SpeechStorageHandler'
    WITH serdeproperties (
        'odps.properties.rolearn'='acs:ram::xxxxxxxxxxxxx:role/aliyunodpsdefaultrole',   
        'mlfFileName'='sm_random_5_utterance.text.label',
        'speechSampleRateInKHz' = '16'
    )
    location 'oss://oss-cn-hangzhou-internal.aliyuncs.com/oss-mc-test/SpeechSentence/'
    USING 'speechtest-1.0-SNAPSHOT.jar,sm_random_5_utterance.text.label';

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

樣本:建立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外部表格檔案

樣本:建立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 |
      +----+------+------+------+

參考:文法參數說明

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

參數名稱

可選/必填

說明

mc_oss_extable_name

必填

待建立的OSS外部表格的名稱。

表名大小寫不敏感,在查詢外部表格時,無需區分大小寫,且不支援強制轉換大小寫。

col_name

必填

OSS外部表格的列名稱。

在讀取OSS資料情境,建立的OSS外部表格結構必須與OSS資料檔案結構保持一致,否則無法成功讀取OSS資料。

data_type

必填

OSS外部表格的列資料類型。

在讀取OSS資料情境,建立的OSS外部表格各列資料類型必須與OSS資料檔案各列資料類型保持一致,否則無法成功讀取OSS資料。

partitioned by (<col_name> <data_type>, ...)

條件必填

當OSS中的資料檔案是以分區路徑方式儲存時,需要攜帶該參數,建立分區表。

  • col_name:分區列名稱。

  • data_type:分區列資料類型。

StorageHandler

條件必填

當您使用內建文本資料解析器或自訂解析器建立OSS外部表格時,需要攜帶該參數。MaxCompute解析器主要分為如下兩類:

  • 內建文本資料解析器:

    • com.aliyun.odps.CsvStorageHandler:讀寫CSV或以GZIP方式壓縮的CSV資料檔案。

    • com.aliyun.odps.TsvStorageHandler:讀寫TSV或以GZIP方式壓縮的TSV資料檔案。

  • 自訂解析器:您通過編寫MaxCompute UDF自訂的解析器。更多編寫MaxCompute UDF資訊,請參見開發UDF

'<property_name>'='<property_value>'

必填

OSS外部表格擴充屬性。詳細屬性列表,請參見參考:with serdeproperties屬性列表

oss_location

必填

資料檔案所在OSS路徑。格式為oss://<oss_endpoint>/<Bucket名稱>/<OSS目錄名稱>/。MaxCompute預設會讀取該路徑下的所有資料檔案。

  • oss_endpoint:OSS訪問網域名稱資訊。您需要使用OSS提供的傳統網路網域名稱,否則將產生OSS流量費用。例如oss://oss-cn-beijing-internal.aliyuncs.com/xxx。更多OSS傳統網路網域名稱資訊,請參見OSS地區和訪問網域名稱

    說明

    建議資料檔案存放的OSS地區與MaxCompute專案所在地區保持一致。由於MaxCompute只在部分地區部署,跨地區的資料連通性可能存在問題。

  • Bucket名稱:OSS儲存空間名稱,即Bucket名稱。更多查看儲存空間名稱資訊,請參見列舉儲存空間

  • 目錄名稱:OSS目錄名稱。目錄後不需要指定檔案名稱。

    --正確用法。
    oss://oss-cn-shanghai-internal.aliyuncs.com/oss-mc-test/Demo1/
    --錯誤用法。
    http://oss-cn-shanghai-internal.aliyuncs.com/oss-mc-test/Demo1/                -- 不支援HTTP串連。
    https://oss-cn-shanghai-internal.aliyuncs.com/oss-mc-test/Demo1/               -- 不支援HTTPS串連。
    oss://oss-cn-shanghai-internal.aliyuncs.com/Demo1                              -- 串連地址錯誤。
    oss://oss-cn-shanghai-internal.aliyuncs.com/oss-mc-test/Demo1/vehicle.csv     -- 不需要指定檔案名稱。

jar_name

條件必填

當您使用自訂解析器建立OSS外部表格時,需要攜帶該參數。指定自訂解析器代碼對應的JAR包。該JAR包需要添加為MaxCompute專案資源。

更多添加資源資訊,請參見添加資源

serde_class

條件可選

指定MaxCompute內建的開來源資料解析器。如果資料檔案格式為TEXTFILE,必須攜帶該參數,其他情境可以不配置。

MaxCompute支援的開來源資料格式對應的serde_class如下:

  • PARQUET:org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe

  • TEXTFILE:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDeorg.apache.hive.hcatalog.data.JsonSerDeorg.apache.hadoop.hive.serde2.OpenCSVSerde

  • ORC:org.apache.hadoop.hive.ql.io.orc.OrcSerde

  • AVRO:org.apache.hadoop.hive.serde2.avro.AvroSerDe

  • SEQUENCEFILE:org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

  • RCFILE:org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe

  • ORCFILE:org.apache.hadoop.hive.ql.io.orc.OrcSerde

file_format

條件必填

當OSS資料檔案為開源格式時,需要攜帶該參數,以指定OSS資料檔案的格式。

說明

單個檔案大小不能超過3 GB,如果檔案過大,建議拆分。

resource_name

條件可選

當您使用自訂的serde class時,需要指定依賴的資源。資源中包含了自訂的serde class

serde class相關的JAR包需要添加為MaxCompute專案資源。

更多添加資源資訊,請參見添加資源

'<tbproperty_name>'='<tbproperty_value>'

條件可選

OSS外部表格擴充屬性。詳細屬性列表,請參見參考:tblproperties屬性列表

參考:with serdeproperties屬性列表

property_name

使用情境

說明

property_value

預設值

odps.properties.rolearn

使用STS模式授權時,請添加該屬性。

指定RAM中Role(具有訪問OSS許可權)的ARN資訊。

您可以通過RAM控制台中的角色詳情擷取。

odps.text.option.gzip.input.enabled

當需要讀取以GZIP方式壓縮的CSV或TSV檔案資料時,請添加該屬性。

CSV、TSV壓縮屬性。配置該參數值為True時,MaxCompute才可以正常讀取壓縮檔,否則會讀取失敗。

  • True

  • False

False

odps.text.option.gzip.output.enabled

當需要將資料以GZIP壓縮方式寫入OSS時,請添加該屬性。

CSV、TSV壓縮屬性。配置該參數值為True時,MaxCompute才能將資料以GZIP壓縮方式寫入OSS,否則不壓縮。

  • True

  • False

False

odps.text.option.header.lines.count

當OSS資料檔案為CSV或TSV,且需要忽略OSS資料檔案中的前N行時,請添加該屬性。

MaxCompute讀取OSS資料檔案時,會忽略指定的行數。

非負整數

0

odps.text.option.null.indicator

當OSS資料檔案為CSV或TSV,且需要定義OSS資料檔案中NULL的解析規則時,請添加該屬性。

通過該參數配置的字串會被解析為SQL中的NULL。例如\N指代NULL,則a,\N,b會解析為a, NULL, b

字串

Null 字元串

odps.text.option.ignore.empty.lines

當OSS資料檔案為CSV或TSV,且需要定義OSS資料檔案中空行的處理規則時,請添加該屬性。

配置該參數值為True時,MaxCompute會忽略資料檔案中的空行,否則會讀取空行。

  • True

  • False

True

odps.text.option.encoding

當OSS資料檔案為CSV或TSV,且OSS資料檔案編碼規則非預設編碼規則時,請添加該屬性。

確保此處配置的編碼規則與OSS資料檔案編碼規則保持一致,否則MaxCompute無法成功讀取資料。

  • UTF-8

  • UTF-16

  • US-ASCII

  • GBK

UTF-8

odps.text.option.delimiter

當需要明確CSV或TSV資料檔案的資料行分隔符號時,請添加該屬性。

確保此處配置的資料行分隔符號可以正確讀取OSS資料檔案的每一列,否則MaxCompute讀取的資料會出現錯位問題。

單個字元

英文逗號(,)

odps.text.option.use.quote

當CSV或TSV資料檔案中的欄位包含換行(CRLF)、雙引號或英文逗號時,請添加該屬性。

當CSV某個欄位中包含換行、雙引號(需要在"前再加"轉義)或英文逗號時,整個欄位必須用雙引號("")括起來作為資料行分隔符號。該參數指定是否識別CSV的資料行分隔符號"

  • True

  • False

False

mcfed.parquet.compression

當需要將PARQUET資料以壓縮方式寫入OSS時,請添加該屬性。

PARQUET壓縮屬性。PARQUET資料預設不壓縮。

  • ZSTD

  • SNAPPY

  • GZIP

mcfed.parquet.compression.codec.zstd.level

'mcfed.parquet.compression'='zstd'時,可以添加該屬性。不填此屬性時,以預設值3進行壓縮。

level值越大,壓縮比越高,實測取值高時,寫出資料的減少量非常有限,但時間和資源消耗快速增加,性價比明顯降低,因此對於巨量資料讀寫壓縮parquet檔案的情境,低level(level3~level5)的zstd壓縮效果最好。例如:'mcfed.parquet.compression.codec.zstd.level'= '5'

取值範圍為1~22。

3

parquet.file.cache.size

在處理PARQUET資料情境,如果需要提升讀OSS資料檔案效能,請添加該屬性。

指定讀OSS資料檔案時,可快取的資料量,單位為KB。

1024

parquet.io.buffer.size

在處理PARQUET資料情境,如果需要提升讀OSS資料檔案效能,請添加該屬性。

指定OSS資料檔案大小超過1024 KB時,可快取的資料量,單位為KB。

4096

separatorChar

當需要明確以TEXTFILE格式儲存的CSV資料的資料行分隔符號時,請添加該屬性。

指定CSV資料資料行分隔符號。

單個字串

英文逗號(,)

quoteChar

當以TEXTFILE格式儲存的CSV資料中的欄位包含換行、雙引號或英文逗號時,請添加該屬性。

指定CSV資料的引用符。

單個字串

escapeChar

當需要明確以TEXTFILE格式儲存的CSV資料的轉義規則時,請添加該屬性。

指定CSV資料的轉義符。

單個字串

mcfed.orc.schema.resolution

同一張OSS外部表格中資料的Schema不一樣。

用於設定ORC檔案解析方式,name表示根據列名解析。

name

預設按列號解析,等價於: 'mcfed.orc.schema.resolution'='position'

odps.sql.text.option.flush.header

在往OSS寫資料的時候,檔案塊的第一行為表頭。

只有針對CSV檔案格式生效。

  • True

  • False

False

odps.sql.text.schema.mismatch.mode

當讀取的OSS檔案的資料列數和外部表格的Schema列數不一致時。

指定對於列數不一致行的處理方式。

說明

odps.text.option.use.quote參數值為True時,該功能不生效。

  • error:報錯。

  • truncate:超過外部表格列數部分資料被截斷;少於外部表格列數部分資料則補null

  • ignore:丟棄不一致的行。

error

參考:tblproperties屬性列表

property_name

使用情境

說明

property_value

預設值

skip.header.line.count

當需要忽略以TEXTFILE格式儲存的CSV檔案中的前N行資料時,請添加該屬性。

MaxCompute讀取OSS資料時,會忽略從首行開始指定行數的資料。

非負整數

skip.footer.line.count

當需要忽略以TEXTFILE格式儲存的CSV檔案中的後N行資料時,請添加該屬性。

MaxCompute讀取OSS資料時,會忽略從尾行開始指定行數的資料。

非負整數

mcfed.orc.compress

當需要將ORC資料以壓縮方式寫入OSS時,請添加該屬性。

ORC壓縮屬性。指定ORC資料的壓縮方式。

  • SNAPPY

  • ZLIB

mcfed.mapreduce.output.fileoutputformat.compress

當需要將TEXTFILE資料檔案以壓縮方式寫入OSS時,請添加該屬性。

TEXTFILE壓縮屬性。配置該參數值為True時,MaxCompute才可以將TEXTFILE資料檔案以壓縮方式寫入OSS,否則不壓縮。

  • True

  • False

False

mcfed.mapreduce.output.fileoutputformat.compress.codec

當需要將TEXTFILE資料檔案以壓縮方式寫入OSS時,請添加該屬性。

TEXTFILE壓縮屬性。設定TEXTFILE資料檔案的壓縮方式。

說明

只支援property_value中的四種壓縮方式。

  • com.hadoop.compression.lzo.LzoCodec

  • com.hadoop.compression.lzo.LzopCodec

  • org.apache.hadoop.io.compress.SnappyCodec

  • com.aliyun.odps.io.compress.SnappyRawCodec

io.compression.codecs

當OSS資料檔案為Raw-Snappy格式時,請添加該屬性。

配置該參數值為True時,MaxCompute才可以正常讀取壓縮資料,否則MaxCompute無法成功讀取資料。

com.aliyun.odps.io.compress.SnappyRawCodec

相關文檔

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