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外部表格 | |
以可視化方式建立OSS外部表格 | |
建立OSS外部表格文法
建立OSS外部表格的情境、相應文法格式及樣本如下。詳細文法參數及屬性列表資訊,請參見參考:文法參數說明、參考:with serdeproperties屬性列表和參考:tblproperties屬性列表。
情境 | 文法格式 | 支援讀取或寫入OSS的資料檔案格式 | 樣本 |
通過內建文本資料解析器建立外部表格 |
|
| |
通過內建開來源資料解析器建立外部表格 |
|
說明 僅支援讀取DLF產生的Hudi資料。 | |
通過自訂解析器建立外部表格 |
| 除上述格式外的資料檔案。 |
如果您建立的OSS外部表格為分區表時,需要在執行
msck
或alter
命令後才可以使用,詳情請參見補全OSS外部表格分區資料文法。更多樣本請參見樣本:建立OSS外部表格並指定對應OSS檔案的第一行為表頭和樣本:建立OSS外部表格且外部表格列數與OSS資料列數不一致。
補全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_name和col_value的值需要與分區資料檔案所在目錄名稱對齊。假設,分區資料檔案所在的OSS目錄結構如下圖,col_name對應
direction
,col_value對應N、NE、S、SW、W
。一個add partition
對應一個子目錄,多個OSS子目錄需要使用多個add partition
。樣本
在OSS上建立目錄
demo8
並分別在下面建立兩個分區檔案夾,分別放入對應的檔案。分區檔案夾:
$pt1=1/$pt2=2
,檔案名稱:demo8-pt1.txt
。分區檔案夾:
$pt1=3/$pt2=4
,檔案名稱:demo8-pt2.txt
。
建立外部表格並指定
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 | +------------+------------+------------+------------+
當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_endpoint:
oss-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
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 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> ...;
映射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/
目錄建立映射關係。
操作流程如下:
使用MaxCompute Studio開發TextExtractor.java、TextOutputer.java、SplitReader.java和TextStorageHandler.java四個Java程式。
更多開發Java程式資訊,請參見開發UDF。
通過MaxCompute Studio的一鍵式打包功能,將TextStorageHandler.java打包上傳為MaxCompute資源。
假設,此處打包的資源名稱為
javatest-1.0-SNAPSHOT.jar
。更多打包上傳資訊,請參見打包、上傳及註冊。說明如果有多個依賴請分別打包後上傳為MaxCompute資源。
執行如下命令建立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/
目錄建立映射關係。
操作流程如下:
使用MaxCompute Studio開發SpeechSentenceSnrExtractor.java、SpeechStorageHandler.java兩個Java程式。
更多開發Java程式資訊,請參見開發UDF。
通過MaxCompute Studio的一鍵式打包功能,將SpeechStorageHandler.java打包上傳為MaxCompute資源。
假設,此處打包的資源名稱為
speechtest-1.0-SNAPSHOT.jar
。更多打包上傳資訊,請參見打包、上傳及註冊。執行如下命令建立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資料列數不一致
準備如下資料,儲存為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
建立外部表格。
指定對於列數不一致行的處理方式為
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/';
查詢表資料。
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中的資料檔案是以分區路徑方式儲存時,需要攜帶該參數,建立分區表。
|
StorageHandler | 條件必填 | 當您使用內建文本資料解析器或自訂解析器建立OSS外部表格時,需要攜帶該參數。MaxCompute解析器主要分為如下兩類:
|
'<property_name>'='<property_value>' | 必填 | OSS外部表格擴充屬性。詳細屬性列表,請參見參考:with serdeproperties屬性列表。 |
oss_location | 必填 | 資料檔案所在OSS路徑。格式為
|
jar_name | 條件必填 | 當您使用自訂解析器建立OSS外部表格時,需要攜帶該參數。指定自訂解析器代碼對應的JAR包。該JAR包需要添加為MaxCompute專案資源。 更多添加資源資訊,請參見添加資源。 |
serde_class | 條件可選 | 指定MaxCompute內建的開來源資料解析器。如果資料檔案格式為TEXTFILE,必須攜帶該參數,其他情境可以不配置。 MaxCompute支援的開來源資料格式對應的serde_class如下:
|
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才可以正常讀取壓縮檔,否則會讀取失敗。 |
| False |
odps.text.option.gzip.output.enabled | 當需要將資料以GZIP壓縮方式寫入OSS時,請添加該屬性。 | CSV、TSV壓縮屬性。配置該參數值為True時,MaxCompute才能將資料以GZIP壓縮方式寫入OSS,否則不壓縮。 |
| 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。例如 | 字串 | Null 字元串 |
odps.text.option.ignore.empty.lines | 當OSS資料檔案為CSV或TSV,且需要定義OSS資料檔案中空行的處理規則時,請添加該屬性。 | 配置該參數值為True時,MaxCompute會忽略資料檔案中的空行,否則會讀取空行。 |
| True |
odps.text.option.encoding | 當OSS資料檔案為CSV或TSV,且OSS資料檔案編碼規則非預設編碼規則時,請添加該屬性。 | 確保此處配置的編碼規則與OSS資料檔案編碼規則保持一致,否則MaxCompute無法成功讀取資料。 |
| UTF-8 |
odps.text.option.delimiter | 當需要明確CSV或TSV資料檔案的資料行分隔符號時,請添加該屬性。 | 確保此處配置的資料行分隔符號可以正確讀取OSS資料檔案的每一列,否則MaxCompute讀取的資料會出現錯位問題。 | 單個字元 | 英文逗號(,) |
odps.text.option.use.quote | 當CSV或TSV資料檔案中的欄位包含換行(CRLF)、雙引號或英文逗號時,請添加該屬性。 | 當CSV某個欄位中包含換行、雙引號(需要在 |
| False |
mcfed.parquet.compression | 當需要將PARQUET資料以壓縮方式寫入OSS時,請添加該屬性。 | PARQUET壓縮屬性。PARQUET資料預設不壓縮。 |
| 無 |
mcfed.parquet.compression.codec.zstd.level | 當 | level值越大,壓縮比越高,實測取值高時,寫出資料的減少量非常有限,但時間和資源消耗快速增加,性價比明顯降低,因此對於巨量資料讀寫壓縮parquet檔案的情境,低level(level3~level5)的zstd壓縮效果最好。例如: | 取值範圍為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檔案解析方式, |
| 預設按列號解析,等價於: |
odps.sql.text.option.flush.header | 在往OSS寫資料的時候,檔案塊的第一行為表頭。 | 只有針對CSV檔案格式生效。 |
| False |
odps.sql.text.schema.mismatch.mode | 當讀取的OSS檔案的資料列數和外部表格的Schema列數不一致時。 | 指定對於列數不一致行的處理方式。 說明 odps.text.option.use.quote參數值為True時,該功能不生效。 |
| 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資料的壓縮方式。 |
| 無 |
mcfed.mapreduce.output.fileoutputformat.compress | 當需要將TEXTFILE資料檔案以壓縮方式寫入OSS時,請添加該屬性。 | TEXTFILE壓縮屬性。配置該參數值為True時,MaxCompute才可以將TEXTFILE資料檔案以壓縮方式寫入OSS,否則不壓縮。 |
| False |
mcfed.mapreduce.output.fileoutputformat.compress.codec | 當需要將TEXTFILE資料檔案以壓縮方式寫入OSS時,請添加該屬性。 | TEXTFILE壓縮屬性。設定TEXTFILE資料檔案的壓縮方式。 說明 只支援 |
| 無 |
io.compression.codecs | 當OSS資料檔案為Raw-Snappy格式時,請添加該屬性。 | 配置該參數值為True時,MaxCompute才可以正常讀取壓縮資料,否則MaxCompute無法成功讀取資料。 | com.aliyun.odps.io.compress.SnappyRawCodec | 無 |
相關文檔
建立OSS外部表格後,您可以通過外部表格讀取儲存在OSS目錄中的資料檔案。具體操作請參見讀取OSS資料。