在成功建立了OSS外部表格後,您可以訪問和查詢儲存在OSS指定目錄下的資料檔案,實現高效的資料讀取操作。
背景資訊
完成OSS外部表格建立後,您可以根據需要選擇如下方式之一對OSS外部表格進行操作:
(推薦)方式一:將OSS的開源格式資料匯入MaxCompute內部表,然後再讀取OSS的資料。
如果直接使用OSS外部表格,每次讀取資料都涉及OSS的I/O操作,且MaxCompute本身針對內部儲存設計的許多高效能最佳化都無法應用,因此效能上會有所損失。如果您需要對資料進行反覆計算或對計算的高效性比較敏感,推薦先在MaxCompute專案中建立一個與OSS外部表格Schema相同的內部表,然後將OSS上的資料匯入MaxCompute內部表,在內部表上執行複雜查詢語句,以便獲得更高的計算效能。
命令樣本如下。
CREATE TABLE <table_internal> LIKE <mc_oss_extable_name>; INSERT OVERWRITE TABLE <table_internal> SELECT * FROM <mc_oss_extable_name>;
方式二:直接讀取OSS資料,與MaxCompute內部表的操作相同。
OSS外部表格會被當作一個普通的內部表一樣使用,不同之處在於,MaxCompute是直接從OSS讀取對應的資料。
注意事項
在使用外部表格讀取OSS資料時,需要注意:
您需要建立好OSS外部表格後,才可以通過外部表格讀取OSS資料。更多MaxCompute支援的OSS資料檔案類型及建立OSS外部表格命令資訊,請參見建立OSS外部表格文法。
SQL語句中涉及到複雜資料類型時,需要在SQL語句前添加
set odps.sql.type.system.odps2=true;
命令,和SQL語句一起提交執行。更多資料類型資訊,請參見資料類型版本說明。對於映射開來源資料的OSS外部表格,您需要在Session層級設定
set odps.sql.hive.compatible=true;
後再讀取OSS資料,否則會報錯。OSS對外提供的頻寬是有限制的,若您頻繁讀取OSS資料,或者大量向OSS寫入資料,若短時間內產生的資料流量超過了OSS執行個體當前的頻寬限額,就會導致頻寬打滿。頻寬打滿直接影響到OSS外表的資料讀取和寫入速度。關於OSS頻寬詳情,請參見OSS有頻寬和QPS限制嗎?。
樣本:讀取OSS資料-非分區表(內建文本資料解析器)
基於樣本:通過內建文本資料解析器建立OSS外部表格-非分區表中建立的OSS外部表格mc_oss_csv_external1讀取OSS資料。
使用MaxCompute用戶端,從OSS讀取資料。命令樣本如下。
SELECT recordId, patientId, direction FROM mc_oss_csv_external1 WHERE patientId > 25;
返回結果如下。
+------------+------------+------------+
| recordid | patientid | direction |
+------------+------------+------------+
| 1 | 51 | S |
| 3 | 48 | NE |
| 4 | 30 | W |
| 5 | 47 | S |
| 7 | 53 | N |
| 8 | 63 | SW |
| 10 | 31 | N |
+------------+------------+------------+
樣本:讀取OSS資料-分區表(內建文本資料解析器)
基於樣本:通過內建文本資料解析器建立OSS外部表格-分區表中建立的OSS外部表格mc_oss_csv_external2讀取OSS資料。
使用MaxCompute用戶端,從OSS讀取資料。命令樣本如下。
SELECT recordId, patientId, direction FROM mc_oss_csv_external2 WHERE direction = 'NE';
返回結果如下。
+------------+------------+------------+
| recordid | patientid | direction |
+------------+------------+------------+
| 2 | 13 | NE |
| 3 | 48 | NE |
| 9 | 4 | NE |
+------------+------------+------------+
樣本:讀取OSS壓縮資料(內建文本資料解析器)
基於樣本:通過內建文本資料解析器建立OSS外部表格-壓縮資料中建立的OSS外部表格mc_oss_csv_external3讀取OSS資料。
使用MaxCompute用戶端,從OSS讀取資料。命令樣本如下。
SELECT recordId, patientId, direction FROM mc_oss_csv_external3 WHERE patientId > 25;
如果OSS壓縮資料為開來源資料格式,需要在SQL語句前添加set odps.sql.hive.compatible=true;
命令,與SQL語句一起提交執行。
返回結果如下。
+------------+------------+------------+
| recordid | patientid | direction |
+------------+------------+------------+
| 1 | 51 | S |
| 3 | 48 | NE |
| 4 | 30 | W |
| 5 | 47 | S |
| 7 | 53 | N |
| 8 | 63 | SW |
| 10 | 31 | N |
+------------+------------+------------+
樣本:讀取OSS文本資料(自訂解析器)
基於樣本:通過自訂解析器建立OSS外部表格中建立的OSS外部表格ambulance_data_txt_external讀取OSS資料。
使用MaxCompute用戶端,從OSS讀取資料。命令樣本如下。
SELECT recordId, patientId, direction FROM ambulance_data_txt_external WHERE patientId > 25;
返回結果如下。
+----------+-----------+-----------+
| recordid | patientid | direction |
+----------+-----------+-----------+
| 1 | 51 | S |
| 3 | 48 | NE |
| 4 | 30 | W |
| 5 | 47 | S |
| 7 | 53 | N |
| 8 | 63 | SW |
| 10 | 31 | N |
+----------+-----------+-----------+
樣本:讀取OSS非文本資料(自訂解析器)
基於樣本:通過自訂解析器建立OSS外部表格-非文本資料中建立的OSS外部表格speech_sentence_snr_external讀取OSS資料。
使用MaxCompute用戶端,從OSS讀取並處理資料。命令樣本如下。
SELECT sentence_snr, id FROM speech_sentence_snr_external WHERE sentence_snr > 10.0;
返回結果如下。
--------------------------------------------------------------
| sentence_snr | id |
--------------------------------------------------------------
| 34.4703 | J310209090013_H02_K03_042 |
--------------------------------------------------------------
| 31.3905 | tsh148_seg_2_3013_3_6_48_80bd359827e24dd7_0 |
--------------------------------------------------------------
| 35.4774 | tsh148_seg_3013_1_31_11_9d7c87aef9f3e559_0 |
--------------------------------------------------------------
| 16.0462 | tsh148_seg_3013_2_29_49_f4cb0990a6b4060c_0 |
--------------------------------------------------------------
| 14.5568 | tsh_148_3013_5_13_47_3d5008d792408f81_0 |
--------------------------------------------------------------
樣本:讀取OSS資料-OSS中的資料有不同的Schema
讀取OSS外部表格,當OSS資料有不同的Schema且需要根據列名稱查詢資料時,需要開啟下面的Flag。
--查詢資料
SET odps.ext.oss.orc.native=true;
SELECT * FROM <tablename>;
樣本:最佳化查詢計劃
由於資料存放區在外部資料湖且缺乏預先統計資訊,查詢最佳化工具採用保守策略,導致查詢效率低。開啟下面的Flag後,該功能支援Optimizer在Query執行中臨時統計表的Stats來發現小表,並主動採用Hash Join、最佳化Join Order、減少大量Shuffle和縮短執行的Pipeline等方法,最終最佳化查詢計劃。
SET odps.meta.exttable.stats.onlinecollect=true;
SELECT * FROM <tablename>;