MaxCompute：OSS外部表格

文法說明

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 '<StorageHandler>'  
WITH serdeproperties (
 ['<property_name>'='<property_value>',...]
) 
location '<oss_location>';

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 '<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>,
...
)
[comment <table_comment>]
[partitioned BY (<col_name> <data_type>, ...)] 
stored BY '<StorageHandler>' 
WITH serdeproperties (
 ['<property_name>'='<property_value>',...]
) 
location '<oss_location>' 
USING '<jar_name>';

參數說明

以下為各類格式外部表格的公用參數說明，在外部表格具體格式的文檔中可以查看並使用其專屬參數。

• 基礎文法參數說明

  參數名稱	是否必填	說明
  mc_oss_extable_name	是	待建立的OSS外部表格的名稱。 表名大小寫不敏感，在查詢外部表格時，無需區分大小寫，且不支援強制轉換大小寫。
  col_name	是	OSS外部表格的列名稱。 在讀取OSS資料情境，建立的OSS外部表格結構必須與OSS資料檔案結構保持一致，否則無法成功讀取OSS資料。
  data_type	是	OSS外部表格的列資料類型。 在讀取OSS的資料情境，OSS外部表格各列資料類型必須與OSS資料檔案各列資料類型保持一致，否則無法成功讀取OSS資料。
  table_comment	否	表注釋內容。注釋內容為長度不超過1024位元組的有效字串，否則報錯。
  partitioned by (col_name data_type, ...)	否	當OSS中的資料檔案是以分區路徑方式儲存時，需要攜帶該參數，建立分區表。 col_name：分區列名稱。 data_type：分區列資料類型。
  '<(tb)property_name>'='<(tb)property_value>'	是	OSS外部表格擴充屬性，參數詳解見各格式文檔專屬參數。
  oss_location	是	資料檔案所在OSS路徑。預設讀取該路徑下的所有資料檔案。 格式為oss://<oss_endpoint>/<Bucket名稱>/<OSS目錄名稱>/。 oss_endpoint： OSS訪問網域名稱資訊。需要使用OSS提供的傳統網路網域名稱，即帶 -internal 的endpoint。 例如oss://oss-cn-beijing-internal.aliyuncs.com/xxx。 更多OSS傳統網路網域名稱資訊，請參見地區和Endpoint。 建議資料檔案存放的OSS地區與MaxCompute專案所在地區保持一致。如果跨地區，資料連通性可能存在問題。 不寫endpoint。系統會預設補充一個當前Project所在地區的endpoint。 不推薦這種方式，如果檔案儲存體跨地區，可能存在資料連通性問題。 Bucket名稱：OSS儲存空間名稱。Bucket名稱需要在oss_endpoint後面。 例如oss://oss-cn-beijing-internal.aliyuncs.com/your_bucket/path/。 更多查看儲存空間名稱資訊，請參見列舉儲存空間。 目錄名稱：OSS目錄名稱。目錄後不需要指定檔案名稱。 例如oss://oss-cn-beijing-internal.aliyuncs.com/oss-mc-test/Demo1/。 錯誤樣本： -- 不支援HTTP串連。 http://oss-cn-shanghai-internal.aliyuncs.com/oss-mc-test/Demo1/ -- 不支援HTTPS串連。 https://oss-cn-shanghai-internal.aliyuncs.com/oss-mc-test/Demo1/ -- 串連地址錯誤。 oss://oss-cn-shanghai-internal.aliyuncs.com/Demo1 -- 不需要指定檔案名稱。 oss://oss-cn-shanghai-internal.aliyuncs.com/oss-mc-test/Demo1/vehicle.csv 許可權規範（RamRole）： 顯示指定（推薦）：建立自訂角色並綁定權限原則，使用該自訂角色的ARN資訊，建立詳情請參見訪問外部資料源授權方案。 使用Default（不推薦）：使用aliyunodpsdefaultrole角色的ARN資訊。

• WITH serdeproperties屬性

  property_name	使用情境	property_value	預設值
  odps.properties.rolearn	使用STS模式授權時，請添加該屬性。	指定RAM中Role（具有訪問OSS許可權）的ARN資訊。 通過RAM控制台中的角色詳情擷取。樣本如下acs:ram::xxxxxx:role/aliyunodpsdefaultrole。	當MaxCompute和OSS的Owner是同一個帳號時： 若建表語句中不寫odps.properties.rolearn，預設使用aliyunodpsdefaultrole角色的ARN資訊。需要提前通過訪問外部資料源授權方案建立出aliyunodpsdefaultrole角色。 若想用自訂角色的ARN資訊，需要提前建立自訂角色，建立詳情請參見OSS的STS模式授權（自訂授權）。 當MaxCompute和OSS的Owner不是同一個帳號時，需要填寫自訂角色的ARN資訊，建立詳情請參見OSS的STS模式授權（自訂授權）。

支援黑白名單

MaxCompute OSS外部表格支援白名單/黑名單機制。通過設定OSS外表的tblProperties白名單和黑名單參數，可過濾同一目錄下需要讀取的檔案。

• tblProperties屬性列表

  單擊展開查看tblProperties屬性詳情

  property_name	使用情境	說明	property_value	預設值
  odps.external.data.file.whitelist.regex	當需要自訂檔案名稱白名單過濾機制，請添加該屬性。	Regex	合格字元組合，例如'.*\\.json'	無
  odps.external.data.file.blacklist.regex	當需要自訂檔案名稱黑名單過濾機制，請添加該屬性。	Regex	合格字元組合，例如'.*\\.txt'	無
  odps.external.data.file.recursive.listing.enabled	當需要定義是否支援檔案遞迴搜尋機制時，請添加該屬性。		True False	True
  odps.sql.unstructured.file.pattern.black.list（已存在的session flag）	當需要自訂session層級的檔案名稱黑名單過濾機制，請添加該屬性。	Regex	合格字元組合，例如'.*\\.json'	無

• 生效規則

  • 黑名單優先順序高於白名單，即檔案符合任何一個黑名單條件，則該檔案就被排除在外。

  • 黑名單的 session flag與 tblProperties 參數之間是"或"關係。如果同時配置，兩者都生效，一個檔案匹配任意一個黑名單條件即被排除。 建議使用 tblProperties 配置。

    配置方式	過濾路徑類型	說明
    session flag	絕對路徑	odps.sql.unstructured.file.pattern.black.list
    tblProperties	相對路徑	odps.external.data.file.blacklist.regex

• Regex說明

  • Regex中的行首 ^ 和行尾 $ 可省略。例如匹配 .json 尾碼檔案，寫 .*\.json 即可。

  • 僅支援單個Regex，無法配置多個 pattern。如需排除多種檔案類型，用 | 合并：

    -- 排除 csv、txt、parquet 檔案 
    "odps.external.data.file.blacklist.regex": ".*\\.(txt|csv|parquet)$" 

• 更新外表的黑名單、白名單參數

  ALTER TABLE {table_name} 
  SET tblproperties('odps.external.data.file.whitelist.regex'='新的Regex')
  
  ALTER TABLE {table_name} 
  SET tblproperties('odps.external.data.file.blacklist.regex'='新的Regex')

• 使用樣本

  • 樣本資料

    -- 假設oss目錄下面存在以下的檔案
    
    oss://xxx/bucket/level1/data1.txt
    oss://xxx/bucket/level1/data2.json
    oss://xxx/bucket/level1/data3.csv
    oss://xxx/bucket/level1/level2/data4.txt
    oss://xxx/bucket/level1/level2/data5.json
    oss://xxx/bucket/level1/level2/data6.parquet

  • 通過白名單篩選唯讀取json檔案

    CREATE EXTERNAL TABLE test_whitelist_table
    (
        id INT,
        name STRING 
    )
    row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
    STORED AS textfile
    LOCATION 'oss://xxx/bucket/level1'
    TBLPROPERTIES (
      'odps.external.data.enable.extension'='true', -- 確保開啟副檔名
      'odps.external.data.file.whitelist.regex'='.*\\.json'); -- 白名單篩選json檔案
    
    -- 通過select * from test_whitelist_table，篩選到所有的json檔案
    -- 因此只能查詢到data2.json, level2/data5.json的所有的資料

  • 通過黑名單來過濾txt、csv、parquet檔案，因此只能讀取json檔案

    CREATE EXTERNAL TABLE test_blacklist_table
    (
        id INT,
        name STRING 
    )
    row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
    stored as textfile
    LOCATION 'oss://xxx/bucket/level1'
    TBLPROPERTIES (
      'odps.external.data.enable.extension'='true', -- 確保開啟副檔名
      'odps.external.data.file.blacklist.regex'='.*\\.(txt|csv|parquet)$'); -- 黑名單過濾尾碼為txt，csv，parquet的檔案
    
    -- 通過select * from test_blacklist_table，過濾了所有的txt，csv，parquet檔案
    -- 因此只能查詢到data2.json, level2/data5.json的所有的資料

  • 通過白名單來篩選txt檔案，通過黑名單排除level2目錄下的檔案

    CREATE EXTERNAL TABLE test_whitelist_blacklist_table
    (
        id INT,
        name STRING 
    )
    row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
    stored as textfile
    LOCATION 'oss://xxx/bucket/level1'
    TBLPROPERTIES (
      'odps.external.data.enable.extension'='true', -- 確保開啟副檔名
      'odps.external.data.file.whitelist.regex'='.*\\.txt', -- 白名單篩選txt檔案
      'odps.external.data.file.blacklist.regex'='.*level2/.*'); -- 過濾level2目錄下的檔案
    
    -- 通過select * from test_whitelist_blacklist_table，
    -- 首先白名單篩選了txt檔案，然後黑名單過濾了所有level2目錄下的檔案
    -- 因此只能查詢到data1.txt所有的資料

  • 通過檔案遞迴的配置參數來控制是否遞迴讀取檔案

    CREATE EXTERNAL TABLE test_recursive_enabled
    (
        id INT,
        name STRING 
    )
    row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
    stored as textfile
    LOCATION 'oss://xxx/bucket/level1'
    TBLPROPERTIES (
      'odps.external.data.enable.extension'='true', -- 確保開啟副檔名
      'odps.external.data.file.whitelist.regex'='.*\\.json' -- 白名單篩選json檔案
      'odps.external.data.file.recursive.listing.enabled'='true'); -- 只要不配置為false，預設支援遞迴
    
    -- 通過select * from test_recursive_enabled，篩選到所有的json檔案
    -- 因此只能查詢到data2.json, level2/data5.json的所有的資料
    
    
    CREATE EXTERNAL TABLE test_recursive_disabled
    (
        id INT,
        name STRING 
    )
    row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
    stored as textfile
    LOCATION 'oss://xxx/bucket/level1'
    TBLPROPERTIES (
      'odps.external.data.enable.extension'='true', -- 確保開啟副檔名
      'odps.external.data.file.whitelist.regex'='.*\\.json' -- 白名單篩選json檔案
      'odps.external.data.file.recursive.listing.enabled'='false'  -- 禁止檔案遞迴讀
    );
    
    -- 通過select * from test_recursive_disabled，篩選到所有的json檔案，但是禁止遞迴讀
    -- 因此只能查詢到data2.json的所有的資料

  • 通過修改tblProperties屬性，來調整白名單、黑名單的內容

    CREATE EXTERNAL TABLE test_alter_table_properties
    (
      id INT,
      name STRING 
    )
    row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
    stored as textfile
    LOCATION 'oss://xxx/bucket/level1'
    TBLPROPERTIES (
      'odps.external.data.enable.extension'='true', -- 確保開啟副檔名
      'odps.external.data.file.whitelist.regex'='.*\\.json'); -- 白名單篩選json檔案
    
    -- 通過select * from test_alter_table_properties，篩選到所有的json檔案
    -- 應該能查詢到data2.json, level2/data5.json的所有的資料
    
    alter table test_alter_table_properties 
    set tblproperties(
      'odps.external.data.file.whitelist.regex'='.*level2/.*\\.json'
    )
    -- 通過select * from test_alter_table_properties
    -- 因此只能查詢到level2目錄下的json檔案，所以只能查詢到level2/data5.json檔案

注意事項

• 建立好OSS外部表格後，才可以通過外部表格讀取OSS資料。MaxCompute支援的OSS資料檔案類型及建立OSS外部表格文法，請參見文法說明。

• SQL語句中涉及到複雜資料類型時，需要在SQL語句前添加set odps.sql.type.system.odps2=true;命令，然後一起提交執行。資料類型相關資訊請參見資料類型版本說明。

• 對於映射開來源資料的OSS外部表格，需要在Session層級設定set odps.sql.hive.compatible=true;後再讀取OSS資料，否則會報錯。

• OSS對外提供的頻寬資源有限，若短時間內資料讀寫流量超過執行個體頻寬上限，會直接影響OSS外部表格的資料讀寫速度。關於OSS頻寬詳情，請參見使用限制及效能指標。

文法說明

<select_statement> FROM <from_statement>;

• select_statement：SELECT子句，從源表中查詢需要插入目標表的資料。

• from_statement：FROM子句，資料來源，如外表名稱。

查詢最佳化

SET odps.meta.exttable.stats.onlinecollect=true;
SELECT * FROM <tablename>;

-- 如下參數二選一即可。

-- 單位MiB，預設值為256 MiB，可作用於內表或外部表格。
SET odps.stage.mapper.split.size=<value>;
SELECT * FROM <tablename>;

-- 單位MiB，預設值為256 MiB，僅作用於外部表格。
SET odps.sql.unstructured.data.split.size=<value>;
SELECT * FROM <tablename>;

-- 二層模型下的文法: set odps.sql.split.dop={"project.table": xxx};
-- 三層模型下的文法: set odps.sql.split.dop={"project.schema.table": xxx};

SET odps.sql.split.dop={
  "project.schema.table1": xxx, 
  "project.schema.table2": yyy
};

SET odps.sql.common.table.planner.ext.hive.bridge=FALSE;

SELECT * FROM <YOUR_TABLE>;

SET odps.sql.split.dop = {"lakehouse47_3.tpch_1t_parquet_snappy.lineitem": 2};
SET odps.input.file.num.limit = 5000;

文法說明

INSERT {INTO|OVERWRITE} TABLE <table_name> PARTITION (<ptcol_name>[, <ptcol_name> ...]) 
<select_statement> FROM <from_statement>;

如果需要向動態分區中插入資料，請參考插入或覆寫動態分區資料（DYNAMIC PARTITION）。

通過OSS分區上傳功能將資料寫入OSS

當需要將資料以開源格式寫入OSS時，可以通過基於開來源資料解析器建立的OSS外部表格及OSS的分區上傳功能，執行INSERT操作將資料寫入OSS。

開啟OSS分區上傳功能設定如下：

setproject odps.sql.unstructured.oss.commit.mode =true;

set odps.sql.unstructured.oss.commit.mode =true;

屬性odps.sql.unstructured.oss.commit.mode預設取值為false，不同取值時的實現原理如下：

匯出檔案管理

以動態分區形式寫出大檔案

-- 需關閉service.mode模式
SET odps.service.mode=off;

-- 必須開啟動態分區能力。
SET odps.sql.reshuffle.dynamicpt=true; 	

-- 設定期望每個reducer消費的資料,假設希望每個檔案是4G。
SET odps.adaptive.shuffle.desired.partition.size=4096;	

• 通過外部表格處理OSS資料時，報錯Inline data exceeds the maximun allowed size，如何解決？

• 在MaxCompute上訪問OSS外部表格，編寫UDF本地測試通過，上傳後報錯記憶體溢出，如何解決？

• 如何通過OSS外部表格將多個小檔案輸出為一個檔案？

• 讀取OSS外部表格時報錯Couldn't connect to server，如何解決？

• 建立OSS外部表格時報錯Network is unreachable (connect failed) ，如何解決？

• 通過OSS分區上傳功能將資料寫入OSS情境下，舊資料被刪除，但是新資料未寫入，如何解決？

• Spark訪問OSS外部表格報錯：找不到表解決方案。

通過外部表格處理OSS資料時，報錯Inline data exceeds the maximun allowed size，如何解決？

• 問題現象

  處理OSS資料時，報錯Inline data exceeds the maximum allowed size。

• 產生原因

  OSS Store對於每一個小檔案有一個大小限制，如果超過3 GB則報錯。

• 解決措施

  針對該問題，您可以通過調整以下兩個屬性進行處理。其原理是通過屬性值調整執行計畫，控制每個Reducer寫入外部表格OSS的資料大小，使得OSS Store檔案不超過3 GB的限制。

  set odps.sql.mapper.split.size=256; #調整每個Mapper讀取資料的大小，單位是MB。
  set odps.stage.reducer.num=100; #調整Reduce階段的Worker數量。

在MaxCompute上訪問OSS外部表格，編寫UDF本地測試通過，上傳後報錯記憶體溢出，如何解決？

• 問題現象

  在MaxCompute上訪問OSS外部表格，編寫UDF本地測試通過，上傳後返回如下報錯。

  FAILED: ODPS-0123131:User defined function exception - Traceback:
  java.lang.OutOfMemoryError: Java heap space        

  設定如下參數後已耗用時間增加但依然報錯。

  set odps.stage.mapper.mem = 2048; 
  set odps.stage.mapper.jvm.mem = 4096;       

• 產生原因

  外部表格的對象檔案太多，記憶體佔用過大且未設定分區。

• 解決措施

  • 使用小資料量查詢。

  • 將對象檔案進行分區，以減少記憶體佔用。

如何通過OSS外部表格將多個小檔案輸出為一個檔案？

通過Logview日誌，查看SQL的執行計畫中最後一個是Reducer還是Joiner。

• 如果是Reducer，則執行語句set odps.stage.reducer.num=1;

• 如果是Joiner，則執行語句set odps.stage.joiner.num=1;

讀取OSS外部表格時報錯Couldn't connect to server，如何解決？

• 問題現象

  讀取OSS外部表格的資料時，報錯ODPS-0123131:User defined function exception - common/io/oss/oss_client.cpp(95): OSSRequestException: req_id: , http status code: -998, error code: HttpIoError, message: Couldn't connect to server。

• 產生原因

  • 原因一：建立OSS外部表格時，oss_location地址中的oss_endpoint使用了公網地址，未使用內網地址。

  • 原因二：建立OSS外部表格時，oss_location地址中的oss_endpoint使用了其他Region的地址。

• 解決措施

  • 對於原因一：

    您需要核查OSS外部表格的建表語句中oss_location參數的oss_endpoint是否為內網地址，如果是公網地址則需要修改為內網地址，具體參數資訊請參見參數說明。

    例如，使用者在印尼（雅加達）地區，使用了oss://oss-ap-southeast-5.aliyuncs.com/<bucket>/....地址建立外部表格，應該改為對應的內網地址oss://oss-ap-southeast-5-internal.aliyuncs.com/<bucket>/....。

  • 對於原因二：

    您需要核查OSS外部表格的建表語句中，oss_location參數的oss_endpoint是否為要訪問的Region的Endpoint，更多OSS傳統網路網域名稱資訊，請參見地區和Endpoint。

建立OSS外部表格時報錯Network is unreachable (connect failed) ，如何解決？

• 問題現象

  建立OSS外部表格時，報錯ODPS-0130071:[1,1] Semantic analysis exception - external table checking failure, error message: Cannot connect to the endpoint 'oss-cn-beijing.aliyuncs.com': Connect to bucket.oss-cn-beijing.aliyuncs.com:80 [bucket.oss-cn-beijing.aliyuncs.com] failed: Network is unreachable (connect failed)。

• 產生原因

  建立OSS外部表格時，oss_location地址中的oss_endpoint使用了公網地址，未使用內網地址。

• 解決措施

  你需要核查OSS外部表格的建表語句中，oss_location參數的oss_endpoint是否為內網地址。如果是公網地址，則需要修改為內網地址，具體參數資訊請參見參數說明。

  例如，使用者在華北2（北京）地區，使用了oss://oss-cn-beijing.aliyuncs.com/<bucket>/....地址建立外部表格，應該改為對應的內網地址oss://oss-cn-beijing-internal.aliyuncs.com/<bucket>/....。

基於OSS外部表格執行SQL作業時，運行慢，如何解決？

• OSS外部表格中的GZ壓縮檔讀取慢

  • 問題現象

    使用者建立了一個OSS外部表格，資料來源為OSS中的GZ壓縮檔，大小為200 GB。在讀取資料過程中執行緩慢。

  • 產生原因

    由於Map端執行計算的Mapper數量過少，所以SQL處理速度慢。

  • 解決措施

    • 對於結構化資料，您可以設定以下參數調整單個Mapper讀取資料量的大小，加速SQL執行。

      set odps.sql.mapper.split.size=256; #調整每個Mapper讀取Table資料的大小，單位是MB。       

    • 對於非結構化資料，您需要查看OSS外部表格路徑下的OSS檔案是否只有1個。如果只有1個，由於壓縮方式下的非結構化資料不支援拆分，所以只能生產1個Mapper，導致處理速度較慢。建議您在OSS對應的外部表格路徑下，將OSS大檔案拆分為小檔案，從而增加讀取外部表格產生的Mapper數量，提升讀取速度。

• 使用SDK搜尋MaxCompute外部表格資料速度慢

  • 問題現象

    使用SDK搜尋MaxCompute外部表格資料速度慢。

  • 解決措施

    外部表格僅支援全量搜尋，所以較慢，建議您改用MaxCompute內部表。

通過OSS分區上傳功能將資料寫入OSS情境下，舊資料被刪除，但是新資料未寫入，如何解決？

• 問題現象

  insert overwrite操作環境，在極端情況下如果作業運行失敗會出現與預期不一致的問題，表現為舊資料被刪除，但是新資料並沒有寫入。

• 產生原因

  新寫入的資料因為極低機率的硬體故障或中繼資料更新失敗等原因沒有成功寫入目標表，且OSS的刪除操作不支援復原，導致被刪除的舊資料無法恢複。

• 解決措施

  • 如果基於舊資料再覆寫OSS外部表格，例如insert overwrite table T select * from table T;，需要提前做好OSS資料備份，作業運行失敗時，再基於備份的舊資料覆寫OSS外部表格。

  • 如果insert overwrite作業可以重複提交，當作業運行失敗時重新提交作業即可。

Spark訪問OSS外部表格報錯：找不到表解決方案

• 問題現象

  在使用Spark訪問OSS外部表格過程中，任務報錯：找不到表解決方案。

• 解決措施

  • 添加如下參數：

    • 開啟外表配置：spark.sql.catalog.odps.enableExternalTable=true;

    • 配置oss所在地區：spark.hadoop.odps.oss.region.default=cn-<region>

  • 如果添加上述參數後仍然報錯如下：

    at java.lang.Thread.run(Thread.java:745)
    Caused by: java.lang.Exception: com.aliyun.odps.cupid.CupidException: connectionId and rolearn are mutually exclusive
        at com.aliyun.odps.cupid.table.v1.commontable.impl.reader.CupidCommonTableReadSession.splitTable

    需要重新建立OSS外部表格後，再訪問。