全部產品
Search
文件中心

MaxCompute:表操作

更新時間:Jun 19, 2024

表是MaxCompute的資料存放區單元。資料倉儲的開發、分析及營運都需要對錶資料進行處理。本文以具體樣本為您介紹如何建立、刪除和查看錶等常用表操作。

表操作常用命令如下。表操作詳情請參見表操作

類型

功能

角色

操作入口

建立表

建立非分區表或分區表。

具備專案空間建立表許可權(CreateTable)的使用者。

本文中的命令您可以在如下工具平台執行:

修改表的所有人

修改表的所有人,即表Owner。

專案空間Owner

刪除表

刪除非分區表或分區表。

具備刪除表許可權(Drop)的使用者。

查看錶或視圖資訊

查看MaxCompute內部表、視圖或外部表格的資訊。

具備讀取表元資訊許可權(Describe)的使用者。

查看分區資訊

查看某個分區表具體的分區資訊。

具備讀取表元資訊許可權(Describe)的使用者。

列出專案空間下的表和視圖

列出專案空間下所有的表、視圖或符合某規則(支援Regex)的表、視圖。

具備專案空間查看對象列表許可權(List)的使用者。

列出所有分區

列出一張表中的所有分區。

具備專案空間查看對象列表許可權(List)的使用者。

關於分區和列的操作詳情請參見分區和列操作

關於表生命週期的操作詳情請參見生命週期操作

建立表

建立非分區表、分區表、外部表格或聚簇表。

  • 限制條件

    • 分區表的分區層級不能超過6級。例如某張表以日期為分區列,分區層級為年/月/周/日/時/分

    • 一張表允許的分區個數支援按照具體的專案配置,預設為6萬個。

    更多表的限制條件,請參見SQL使用限制項

  • 命令格式

    --建立新表。
     create [external] table [if not exists] <table_name>
     [primary key (<pk_col_name>, <pk_col_name2>),(<col_name> <data_type> [not null] [default <default_value>] [comment <col_comment>], ...)]
     [comment <table_comment>]
     [partitioned by (<col_name> <data_type> [comment <col_comment>], ...)]
     
    --用於建立聚簇表時設定表的Shuffle和Sort屬性。
     [clustered by | range clustered by (<col_name> [, <col_name>, ...]) [sorted by (<col_name> [asc | desc] [, <col_name> [asc | desc] ...])] into <number_of_buckets> buckets] 
    
    --僅限外部表格。
     [stored by StorageHandler] 
     --僅限外部表格。
     [with serdeproperties (options)] 
     --僅限外部表格。
     [location <osslocation>] 
    
    --指定表為Transactional1.0表,後續可以對該表執行更新或刪除表資料操作,但是Transactional表有部分使用限制,請根據需求建立。
     [tblproperties("transactional"="true")]
    
    --指定表為Transactional2.0表,後續可以做upsert,增量查詢,time-travel等操作
     [tblproperties ("transactional"="true" [, "write.bucket.num" = "N", "acid.data.retain.hours"="hours"...])] [lifecycle <days>]
    ;
    
    --基於已存在的表建立新表並複製資料,但不複製分區屬性。支援外部表格和湖倉一體外部項目中的表。
    create table [if not exists] <table_name> [lifecycle <days>] as <select_statement>;
    
    --基於已存在的表建立具備相同結構的新表但不複製資料,支援外部表格和湖倉一體外部項目中的表。
    create table [if not exists] <table_name> like <existing_table_name> [lifecycle <days>];
  • 通用參數

    • external:可選。表示建立的表為外部表格。

    • if not exists:可選。如果不指定if not exists選項而存在同名表,會報錯。如果指定if not exists,只要存在同名表,即使原表結構與要建立的目標表結構不一致,均返回成功。已存在的同名表的中繼資料資訊不會被改動。

    • table_name:必填。表名。表名大小寫不敏感,不能有特殊字元,只能包含a~z、A~Z、數字和底線(_)。建議以字母開頭,名稱的長度不超過128位元組,否則報錯。

    • col_name:可選。表的列名。列名大小寫不敏感,不能有特殊字元,只能包含a~z、A~Z、數字、底線(_)或中文。建議以字母開頭,名稱的長度不超過128位元組,否則報錯。

    • col_comment:可選。列的注釋內容。注釋內容為長度不超過1024位元組的有效字串,否則報錯。

    • data_type:可選。列的資料類型,包含BIGINT、DOUBLE、BOOLEAN、DATETIME、DECIMAL和STRING等多種資料類型,詳情請參見資料類型版本說明

    • not null:可選。禁止該列的值為NULL。更多修改非空屬性資訊,請參見修改表的列非空屬性

    • default_value:可選。指定列的預設值,當insert操作不指定該列時,該列寫入預設值。

      說明

      當前預設值不支援函數,例如getdate()now()等。

    • table_comment:可選。表注釋內容。注釋內容為長度不超過1024位元組的有效字串,否則報錯。

    • lifecycle:可選。表的生命週期,僅支援正整數。單位:天。

      • 非分區表:自最後一次修改表資料開始計算,經過days天后資料無改動,則您無需幹預此表,MaxCompute會自動回收(類似drop table操作)。

      • 分區表:系統根據各分區的LastModifiedTime判斷是否需要回收分區。不同於非分區表,分區表的最後一個分區被回收後,該表不會被刪除。生命週期只能設定到表層級,不支援在分區層級設定生命週期。

  • 分區表參數

    partitioned by (<col_name> <data_type> [comment <col_comment>], ...:可選。指定分區表的分區欄位。

    • col_name:表的分區列名。列名大小寫不敏感,不能有特殊字元,只能包含a~z、A~Z、數字、底線(_)或中文。建議以字母開頭,名稱的長度不超過128位元組,否則報錯。

    • data_type:分區列的資料類型。MaxCompute 1.0版本僅支援STRING類型。MaxCompute 2.0版本擴充了分區類型,包含TINYINT、SMALLINT、INT、BIGINT、VARCHAR和STRING類型。詳情請參見資料類型版本說明。當使用分區欄位對錶進行分區時,新增分區、更新分區內資料和讀取分區資料均不需要做全表掃描,提高處理效率。

    • col_comment:分區列的注釋內容。注釋內容為長度不超過1024位元組的有效字串,否則報錯。

    說明

    分區值不能包含雙位元組字元(如中文),必須以字母開頭,包含字母、數字和允許的字元,長度不超過255位元組。允許的字元包括空格、冒號(:)、底線(_)、貨幣符號($)、井號(#)、英文句點(.)、驚嘆號(!)和at(@),其他字元的行為未定義,例如逸出字元\t\n/

  • 聚簇表參數

    clustered by | range clustered by (<col_name> [, <col_name>, ...]) [sorted by (<col_name> [asc | desc] [, <col_name> [asc | desc] ...])] into <number_of_buckets> buckets:可選。用於建立聚簇表時設定表的Shuffle和Sort屬性。

    聚簇表分為Hash聚簇表和Range聚簇表兩種。

    Hash聚簇表

    • clustered by:指定Hash Key。MaxCompute將對指定列進行Hash運算,按照Hash值分散到各個Bucket中。為避免資料扭曲和熱點,並取得較好的並存執行效果,clustered by列適宜選擇取值範圍大,重複索引值少的列。此外,為了達到join最佳化的目的,也應該考慮選取常用的Join或Aggregation Key,即類似於傳統資料庫中的主鍵。

    • sorted by:指定Bucket內欄位的排序方式。建議sorted byclustered by保持一致,以取得較好的效能。此外,當指定sorted by子句後,MaxCompute將自動產生索引,並且在查詢時利用索引來加快執行。

    • number_of_buckets:指定雜湊桶的數量。該值必須填寫,且由資料量大小決定。此外,MaxCompute預設支援最多1111個Reducer,所以此處最多隻支援1111個雜湊桶。您可以使用set odps.stage.reducer.num =<並發數>;來提升這個限制,但最大不得超過4000,否則會影響效能。

      選擇雜湊桶數目時,請您遵循以下兩個原則:

      • 雜湊桶大小適中:建議每個Bucket的大小為500 MB左右。例如,分區大小估計為500 GB,粗略估算Bucket數目應該設為1000,這樣平均每個Bucket大小約為500 MB。對於特別大的表,500 MB的限制可以突破,每個Bucket在2 GB~3 GB左右比較合適。同時,可以結合set odps.stage.reducer.num =<並發數>;來突破1111個桶的限制。

      • 對於join最佳化情境,去除Shuffle和Sort步驟能顯著提升效能。因此要求兩個表的雜湊桶數目成倍數關係,例如256和512。建議雜湊桶的數量統一使用2n,例如512、1024、2048或4096,這樣系統可以自動進行雜湊桶的分裂和合并,也可以去除Shuffle和Sort的步驟,提升執行效率。

    Range聚簇表

    • range clustered by:指定範圍聚簇列。MaxCompute將對指定列進行分桶運算,按照分桶編號分散到各個Bucket中。

    • sorted by:指定Bucket內欄位的排序方式,使用方法與Hash聚簇表相同。

    • number_of_buckets:指定雜湊桶的數量。Range聚簇表的Bucket桶數沒有Hash聚簇表的2n最佳實務,在資料量分布合理的情況下,任何桶數都可以。Range聚簇表的Bucket數不是必須的,可以省略,此時系統會根據資料量自動決定最佳的Bucket數目。

    • 當Join或Aggregation的對象是Range聚簇表,且Join Key或Group Key是Range Clustering Key或其首碼時,可以通過控制Flag消除資料的重分布,即Shuffle Remove,提升執行效率。您可以通過set odps.optimizer.enable.range.partial.repartitioning=true/false;進行設定,預設關閉。

      說明
      • 聚簇表的優點:

        • 最佳化Bucket Pruning。

        • 最佳化Aggregation。

        • 最佳化儲存。

      • 聚簇表的限制:

        • 不支援insert into,只能通過insert overwrite來添加資料。

        • 不支援Tunnel直接Upload到Range聚簇表,因為Tunnel上傳資料是無序的。

        • 不支援備份恢複功能。

  • 外部表格參數

    • stored by StorageHandler:可選。按照外部表格資料格式指定StorageHandler。

    • with serdeproperties (options):可選。外部表格的授權、壓縮、字元解析等相關參數。

    • osslocation:可選。外部表格資料OSS儲存位置,詳情請參見建立OSS外部表格

  • Transaction Table1.0與Transaction Table2.0表參數

    說明
    • 僅設定tblproperties("transactional"="true")表示表類型為Transaction Table1.0,除此之外還設定primary key (PK)或其他Transaction表參數時,表示表類型為Transaction Table2.0。

    • 目前Transaction Table2.0處於邀測階段,預設不支援直接使用,如果樣本中涉及到Transaction Table2.0功能,您可以點擊申請開通,在新功能試用申請頁面申請開通使用Transaction Table2.0功能後,再運行相關命令。詳情請參見Transaction Table2.0概述

    Transaction Table1.0表參數

    tblproperties("transactional"="true"):可選。設定表為Transactional表。後續可以對Transactional表執行updatedelete操作實現行級更新或刪除資料。更多資訊,請參見更新或刪除資料(UPDATE | DELETE)

    Transactional表的使用限制如下:

    • MaxCompute只允許在建立表時設定Transactional屬性。已建立的表不允許通過alter table方式修改Transactional屬性,執行如下語句會報錯:

      alter table not_txn_tbl set tblproperties("transactional"="true");
      --報錯。
      FAILED: Catalog Service Failed, ErrorCode: 151, Error Message: Set transactional is not supported
    • 在建立表時,不支援將聚簇表、外部表格設定為Transactional表。

    • 不支援MaxCompute內部表、外部表格、聚簇表與Transactional表互轉。

    • 不支援自動合并Transactional表檔案,需要手動執行合併作業,詳情請參見合并Transactional表檔案

    • 不支援merge partition操作。

    • 其他系統的作業訪問Transactional表有一些限制,例如Graph不支援讀寫;Spark、PAI只支援讀,不支援寫。

    • 在對Transactional表的重要資料執行updatedeleteinsert overwrite操作前需要手動通過select+insert操作將資料備份至其他表中。

    Transaction Table2.0表參數

    • primary key (PK)

      建立Transaction Table2.0類型表時必填,可包含多列,文法遵循標準SQL primary key文法,PK列必須設定not null,不允許修改。 設定後,後續表資料會根據PK列進行去重,Unique約束在單個partition範圍內有效,或非分區表內有效。

    • tblproperties ("transactional"="true" [, "write.bucket.num" = "N", "acid.data.retain.hours"="hours"...])]

      • transactional:建立Transaction Table2.0類型表時必填,必須設定為true。表示符合MaxCompute的ACID表的事務特性,採用MVCC交易管理模式,保證快照隔離等級。

      • write.bucket.num:可選,預設取值為16,取值範圍為(0, 4096]。表示每個partition或者非分區表的分桶數量,也表示資料寫入的並發節點數量。分區表支援修改,新分區預設生效,非分區表不支援修改。該參數用法可參考如下建議:

        • 如果是通過Tunnel匯入,代表Tunnel並發節點數,設定結果會影響匯入流量,也會受Tunnel最大並發節點數約束。

        • 如果是通過SQL寫入,代表寫入資料的Reducer的並發度,受Reducer最大並發節點數約束。

        • 建議每個Bucket的資料寫入大小為500 MB左右。例如,分區大小估計為500 GB,粗略估算Bucket數目應該設為1000,這樣平均每個Bucket大小約為500 MB。對於特別大的表,500 MB的限制可以突破,每個Bucket在2 GB~3 GB左右比較合適。

      • acid.data.retain.hours:可選,預設取值為72,取值範圍為[0, 168]。表示TimeTravel可查詢資料歷史狀態的時間範圍(單位為小時)。

        • 取值為0時表示不保留資料歷史狀態,也就是不支援timetravel查詢。

        • 如果資料歷史狀態存在時間超過了此參數設定的值,可被刪除或者Compact。

        • 如果SQL Timetraval查詢的時間早於該參數的值,會直接報錯,比如屬性值為72小時,Timetraval要查詢72小時之前的資料歷史狀態,會直接報錯。

      • acid.incremental.query.out.of.time.range.enabled:可選,預設false。True表示增量查詢指定的endTimestamp可大於表最大的資料Commit Time,在endTimestamp大於目前時間情境下,使用者多次查詢可能得到不同的結果,因為可能有新的資料插入。支援修改表的此參數取值。

    • Transaction Table2.0其他通用參數的設定要求

      • lifecycle: 表生命週期必須大於等於timetravel可查詢的生命週期,即lifecycle >= acid.data.retain.hours / 24。建立表時會做檢查,不符合會報錯。

      • cluster by:Transaction Table2.0不支援設定該參數,實際上Transaction Table2.0內部實現採用類似HashClusterTable儲存結構,同時設定可能引發衝突。

      • 其他不支援的特性:不支援設定range cluster by、external外表、Create Table As。

    • 其他使用限制:

      • 當前不支援除MaxCompute SQL外的其他引擎系統的作業,當前只支援MaxCompute SQL使用Transaction Table2.0表,其他引擎(如MaxCompute Spark、Hologres等)暫不支援。

      • 存量普通表不支援修改為transactional表。

  • 基於已有資料/表建立表

    • 通過create table [if not exists] <table_name> [lifecycle <days>] as <select_statement>;語句可以再建立一個表,並在建表的同時將資料複製到新表中。

      • 但通過該語句建立的表不會複製分區屬性,只會把源表的分區列作為目標表的一般列處理,也不會複製源表的生命週期屬性。

      • 您還可以通過lifecycle參數回收表。同時也支援建立內部表複製外部表格的資料。

    • 通過create table [if not exists] <table_name> like <existing_table_name> [lifecycle <days>];語句可以再建立一個表,使目標表和源表具有相同的表結構。

      • 但通過該語句建立的表不複製資料,也不會複製源表的生命週期屬性。

      • 您還可以通過lifecycle參數回收表。同時也支援建立內部表複製外部表格的結構。

  • 使用樣本

    • 樣本1:建立非分區表test1。

      create table test1 (key STRING);
    • 樣本2:建立一張分區表sale_detail。

      create table if not exists sale_detail(
       shop_name     STRING,
       customer_id   STRING,
       total_price   DOUBLE)
      partitioned by (sale_date STRING, region STRING); 
    • 樣本3:建立一個新表sale_detail_ctas1,將sale_detail的資料複製到sale_detail_ctas1中,並設定生命週期。

      SET odps.sql.allow.fullscan=true;
      create table sale_detail_ctas1 lifecycle 10 as select * from sale_detail;

      您可以通過desc extended sale_detail_ctas1;命令查看到表的結構及生命週期等詳細資料。

      此處sale_detail是一張分區表,而通過create table ... as select_statement ...語句建立的表sale_detail_ctas1不會複製分區屬性,只會把源表的分區列作為目標表的一般列處理。即sale_detail_ctas1是一個含有5列的非分區表。

    • 樣本4:建立一個新表sale_detail_ctas2,在select子句中使用常量作為列的值。

      SET odps.sql.allow.fullscan=true;
      --指定列的名字。
      create table sale_detail_ctas2
      as
      select shop_name, customer_id, total_price, '2013' as sale_date, 'China' as region
      from sale_detail;
      --不指定列的名字。
      create table sale_detail_ctas3
      as
      select shop_name, customer_id, total_price, '2013', 'China' 
      from sale_detail;
      說明

      如果在select子句中使用常量作為列的值,建議您指定列的名字。建立的表sale_detail_ctas3的第四、五列類似於_c4_c5

    • 樣本5:建立一個新表sale_detail_like,與sale_detail具有相同的表結構,並設定生命週期。

      create table sale_detail_like like sale_detail lifecycle 10;

      您可以通過desc extended sale_detail_like;命令查看到表的結構及生命週期等詳細資料。

      sale_detail_like的表結構與sale_detail完全相同。除生命週期屬性外,列名、列注釋以及表注釋等均相同。但sale_detail中的資料不會被複製到sale_detail_like表中。

    • 樣本6:建立一個新表mc_oss_extable_orc_like,與外部表格mc_oss_extable_orc具有相同的表結構。

      create table mc_oss_extable_orc_like like mc_oss_extable_orc;

      您可以通過desc mc_oss_extable_orc_like;命令查看錶結構等詳細資料。

      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$****@***.aliyunid.com | Project: max_compute_7u************yoq              |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2022-08-11 11:10:47                                      |
      | LastDDLTime:              2022-08-11 11:10:47                                      |
      | LastModifiedTime:         2022-08-11 11:10:47                                      |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field           | Type       | Label | Comment                                     |
      +------------------------------------------------------------------------------------+
      | id              | string     |       |                                             |
      | name            | string     |       |                                             |
      +------------------------------------------------------------------------------------+
    • 樣本7:建立使用新資料類型的表test_newtype。

      set odps.sql.type.system.odps2=true;
      CREATE TABLE test_newtype (
          c1 TINYINT
          ,c2 SMALLINT
          ,c3 INT
          ,c4 BIGINT
          ,c5 FLOAT
          ,c6 DOUBLE
          ,c7 DECIMAL
          ,c8 BINARY
          ,c9 TIMESTAMP
          ,c10 ARRAY<MAP<BIGINT,BIGINT>>
          ,c11 MAP<STRING,ARRAY<BIGINT>>
          ,c12 STRUCT<s1:STRING,s2:BIGINT>
          ,c13 VARCHAR(20))
      LIFECYCLE 1
      ;
    • 樣本8:建立Hash聚簇非分區表t1。

      create table t1 (a STRING, b STRING, c BIGINT) clustered by (c) sorted by (c) into 1024 buckets; 
    • 樣本9:建立Hash聚簇分區表t2。

      create table t2 (a STRING, b STRING, c BIGINT) partitioned by (dt STRING) clustered by (c) sorted by (c) into 1024 buckets; 
    • 樣本10:建立Range聚簇非分區表t3。

      create table t3 (a STRING, b STRING, c BIGINT) range clustered by (c) sorted by (c) into 1024 buckets;
    • 樣本11:建立Range聚簇分區表t4。

      create table t4 (a STRING, b STRING, c BIGINT) partitioned by (dt STRING) range clustered by (c) sorted by (c); 
    • 樣本12:建立Transactional非分區表t5。

      create table t5(id bigint) tblproperties("transactional"="true");
    • 樣本13:建立Transactional分區表t6。

      create table if not exists t6(id bigint) partitioned by(ds string) tblproperties ("transactional"="true");
    • 樣本14:建立非分區表test_default,並為欄位指定預設值。

      create table test_default
      (
      tinyint_name tinyint not NULL default 1Y,
      smallint_name SMALLINT not NULL DEFAULT 1S,
      int_name INT not NULL DEFAULT 1,
      bigint_name BIGINT not NULL DEFAULT 1,
      binary_name BINARY ,
      float_name FLOAT ,
      double_name DOUBLE not NULL DEFAULT 0.1,
      decimal_name DECIMAL(2, 1) not NULL DEFAULT 0.0BD,
      varchar_name VARCHAR(10) ,
      char_name CHAR(2) ,
      string_name STRING not NULL DEFAULT 'N',
      boolean_name BOOLEAN not NULL DEFAULT TRUE
      );
    • 樣本15:使用create table [if not exists] <table_name> [lifecycle <days>] as <select_statement>;語句建立內部表複製外部分區表資料,內部表不包含分區屬性。

      -- MaxCompute側查詢湖倉一體外部表格
      select * from hive_external2_1.myhive_0110;
      -- 結果
      a    b    c
      101    1    20230110
      102    2    20230110
      103    3    20230110
      
      -- 使用create table as建立內部表
      create table from_exetbl_as_par as select * from hive_external2_1.myhive_0110_par;
      
      -- 查詢建立內部表
      select * from from_exetbl_as_par;
      -- 返回結果,全表資料都有
      a    b    c
      101    1    20230110
      102    2    20230110
      103    3    20230110
      
      
      -- 查詢建立內部表結構
      desc from_exetbl_as_par;
      -- 返回結果
      +------------------------------------------------------------------------------------+
      | Owner:                    ALIYUN$***********                                       |
      | Project:                  ***_*****_***                                            |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2023-01-10 15:16:33                                      |
      | LastDDLTime:              2023-01-10 15:16:33                                      |
      | LastModifiedTime:         2023-01-10 15:16:33                                      |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 919                                                |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field           | Type       | Label | Comment                                     |
      +------------------------------------------------------------------------------------+
      | a               | string     |       |                                             |
      | b               | string     |       |                                             |
      | c               | string     |       |                                             |
      +------------------------------------------------------------------------------------+
    • 樣本16:使用create table [if not exists] <table_name> like <existing_table_name> [lifecycle <days>];語句建立內部表複製外部分區表結構,內部表包含分區屬性。

      -- MaxCompute側查詢湖倉一體外部表格
      select * from hive_external2_1.myhive_0110_par;
      -- 返回結果
      a    b    c
      101    1    20230110
      102    2    20230110
      103    3    20230110
      
      -- 使用create table like建立內部表
      create table from_exetbl_like like hive_external2_1.myhive_0110_par;
      
      -- 查詢建立內部表
      select * from from_exetbl_like;
      -- 返回結果,只有表結構出現
      a    b    c
      
      -- 查詢內部表結構
      desc from_exetbl_like;
      -- 返回結果
      +------------------------------------------------------------------------------------+
      | Owner:                    ALIYUN$************                                      |
      | Project:                  ***_*****_***                                            |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2023-01-10 15:09:47                                      |
      | LastDDLTime:              2023-01-10 15:09:47                                      |
      | LastModifiedTime:         2023-01-10 15:09:47                                      |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field           | Type       | Label | Comment                                     |
      +------------------------------------------------------------------------------------+
      | a               | string     |       |                                             |
      | b               | string     |       |                                             |
      +------------------------------------------------------------------------------------+
      | Partition Columns:                                                                 |
      +------------------------------------------------------------------------------------+
      | c               | string     |                                                     |
      +------------------------------------------------------------------------------------+
    • 樣本17:建立Transaction Table2.0表

      create table mf_tt (pk bigint not null primary key, 
                          val bigint) 
                          tblproperties ("transactional"="true");
    • 樣本18,建立Transaction Table2.0表,設定主要表屬性

      create table mf_tt2 (pk bigint not null, 
                        pk2 bigint not null, 
                        val bigint, 
                        val2 bigint, primary key (pk, pk2)
                       ) 
                   tblproperties ("transactional"="true", 
                                  "write.bucket.num" = "64", 
                                  "acid.data.retain.hours"="120") 
                   lifecycle 7;

修改表的所有人

修改表的所有人,即表Owner。

  • 命令格式

    alter table <table_name> changeowner to <new_owner>;
  • 參數說明

    • table_name:必填。待修改Owner的表名。

    • new_owner:必填。修改後的Owner帳號。如果要修改Owner為RAM使用者,格式為:RAM$<UID>:<ram_name>,其中UID為阿里雲帳號的帳號ID,ram_name為RAM使用者顯示名稱。

      說明

      修改表Owner為RAM使用者,需確保RAM使用者已加入到表所在的專案中。

  • 使用樣本

    • 將表test1的所有人修改為ALIYUN$xxx@aliyun.com。

      alter table test1 changeowner to 'ALIYUN$xxx@aliyun.com';
    • 將表test1的所有人修改為名稱為ram_test的RAM使用者。

      alter table test1 changeowner to 'RAM$13xxxxxxxxxxx:ram_test';

刪除表

刪除非分區表或分區表。

  • 注意事項

    • 請謹慎操作,確認表可以刪除後,再執行刪除操作。如果誤刪了表,當專案開啟了備份恢複功能,且刪除操作未超過專案設定的備份資料保留天數時,則可以恢複表。更多備份恢複資訊,請參見備份與恢複

    • 刪除表之後,MaxCompute專案的儲存量會降低。

  • 命令格式

    drop table [if exists] <table_name>; 
  • 參數說明

    • if exists:可選。如果不指定if exists且表不存在,則返回異常。如果指定if exists,無論表是否存在,均返回成功。

    • table_name:必填。待刪除的表名。

  • 使用樣本

    --刪除表sale_detail。無論sale_detail表是否存在,均返回成功。
    drop table if exists sale_detail; 

查看錶或視圖資訊

查看MaxCompute內部表、視圖、外部表格、聚簇表或Transactional表的資訊。如果您需要查看錶的詳細資料資訊,請參見SELECT文法

  • 命令格式

    --查看錶或視圖資訊。
    desc <table_name|view_name> [partition (<pt_spec>)]; 
    --查看外部表格、聚簇表或Transactional表資訊。也可以查看內部表的擴充資訊。
    desc extended <table_name>; 
  • 參數說明

    • table_name:必填。待查看錶的名稱。

    • view_name:必填。待查看視圖的名稱。

    • pt_spec:可選。待查看分區表的指定分區。格式為(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)

    • extended:如果表為外部表格、聚簇表或Transactional表,需要包含此參數。顯示錶的擴充資訊。也可以查看內部表的擴充資訊,例如列的非空屬性。

  • 使用樣本

    • 樣本1:查看建立的test1表的資訊。

      desc test1;

      返回結果如下。

      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$maoXXX@alibaba-inc.com | Project: $project_name                      |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2020-11-16 17:47:48                                      |
      | LastDDLTime:              2020-11-16 17:47:48                                      |
      | LastModifiedTime:         2020-11-16 17:47:48                                      |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field           | Type       | Label | Comment                                     |
      +------------------------------------------------------------------------------------+
      | key             | string     |       |                                             |
      +------------------------------------------------------------------------------------+
    • 樣本2:查看建立的sale_detail表的資訊。

      desc sale_detail;

      返回結果如下。

      +--------------------------------------------------------------------+
      | Owner: ALIYUN$maoXXX@alibaba-inc.com | Project: $project_name      |
      | TableComment:                                                      |
      +--------------------------------------------------------------------+
      | CreateTime:               2017-06-28 15:05:17                      |
      | LastDDLTime:              2017-06-28 15:05:17                      |
      | LastModifiedTime:         2017-06-28 15:05:17                      |
      +--------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                  |
      +--------------------------------------------------------------------+
      | Native Columns:                                                    |
      +--------------------------------------------------------------------+
      | Field           | Type       | Label | Comment                     |
      +--------------------------------------------------------------------+
      | shop_name       | string     |       |                             |
      | customer_id     | string     |       |                             |
      | total_price     | double     |       |                             |
      +--------------------------------------------------------------------+
      | Partition Columns:                                                 |    
      +--------------------------------------------------------------------+
      | sale_date       | string     |                                     |
      | region          | string     |                                     |
      +--------------------------------------------------------------------+
    • 樣本3:查看建立的sale_detail_ctas1表的詳細資料。

      desc extended sale_detail_ctas1;

      返回結果如下。

      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$maoXXX@alibaba-inc.com | Project: $project_name                      |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2021-07-07 15:29:53                                      |
      | LastDDLTime:              2021-07-07 15:29:53                                      |
      | LastModifiedTime:         2021-07-07 15:29:53                                      |
      | Lifecycle:                10                                                       |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
      +------------------------------------------------------------------------------------+
      | shop_name | string |       |               | true     | NULL         |              |
      | customer_id | string |       |               | true     | NULL         |              |
      | total_price | double |       |               | true     | NULL         |              |
      | sale_date | string |       |               | true     | NULL         |              |
      | region   | string |       |               | true     | NULL         |              |
      +------------------------------------------------------------------------------------+
      | Extended Info:                                                                     |
      +------------------------------------------------------------------------------------+
      | TableID:                  98cb8a38733c49eabed4735173818147                         |
      | IsArchived:               false                                                    |
      | PhysicalSize:             0                                                        |
      | FileNum:                  0                                                        |
      | StoredAs:                 AliOrc                                                   |
      | CompressionStrategy:      normal                                                   |
      +------------------------------------------------------------------------------------+

      sale_date和region兩個欄位僅會作為普通列存在,而不是表的分區。

    • 樣本4:查看建立的sale_detail_ctas2表的資訊。

      desc sale_detail_ctas2;

      返回結果如下。

      +--------------------------------------------------------------------+
      | Owner: ALIYUN$xxxxx@alibaba-inc.com | Project: $project_name       |
      | TableComment:                                                      |
      +--------------------------------------------------------------------+
      | CreateTime:               2017-06-28 15:42:17                      |
      | LastDDLTime:              2017-06-28 15:42:17                      |
      | LastModifiedTime:         2017-06-28 15:42:17                      |
      +--------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                  |
      +--------------------------------------------------------------------+
      | Native Columns:                                                    |
      +--------------------------------------------------------------------+
      | Field           | Type       | Label | Comment                     |
      +--------------------------------------------------------------------+
      | shop_name       | string     |       |                             |
      | customer_id     | string     |       |                             |
      | total_price     | double     |       |                             |
      | sale_date       | string     |       |                             |
      | region          | string     |       |                             |
      +--------------------------------------------------------------------+
    • 樣本5:查看建立的sale_detail_like表的詳細資料。

      desc extended sale_detail_like;

      返回結果如下。

      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$xxxxx@alibaba-inc.com | Project: $project_name                       |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2021-07-07 15:40:38                                      |
      | LastDDLTime:              2021-07-07 15:40:38                                      |
      | LastModifiedTime:         2021-07-07 15:40:38                                      |
      | Lifecycle:                10                                                       |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
      +------------------------------------------------------------------------------------+
      | shop_name | string |       |               | true     | NULL         |              |
      | customer_id | string |       |               | true     | NULL         |              |
      | total_price | double |       |               | true     | NULL         |              |
      +------------------------------------------------------------------------------------+
      | Partition Columns:                                                                 |
      +------------------------------------------------------------------------------------+
      | sale_date       | string     |                                                     |
      | region          | string     |                                                     |
      +------------------------------------------------------------------------------------+
      | Extended Info:                                                                     |
      +------------------------------------------------------------------------------------+
      | TableID:                  61782ff7713f426e9d6f91d5deeac99a                         |
      | IsArchived:               false                                                    |
      | PhysicalSize:             0                                                        |
      | FileNum:                  0                                                        |
      | StoredAs:                 AliOrc                                                   |
      | CompressionStrategy:      normal                                                   |
      +------------------------------------------------------------------------------------+

      除生命週期屬性外,sale_detail_like的其它屬性(欄位類型、分區類型等)均與sale_detail完全一致。

      說明

      通過desc table_name查看到的Size包含了在資源回收筒的資料Size。如果您需要清空資源回收筒,可以先執行purge table table_name,然後再執行desc table_name查看除資源回收筒以外的資料大小。您也可以執行show recyclebin查看本專案中資源回收筒內的資料明細。

    • 樣本6:查看建立的test_newtype表的資訊。

      desc test_newtype;

      返回結果如下。

      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field           | Type       | Label | Comment                                     |
      +------------------------------------------------------------------------------------+
      | c1              | tinyint    |       |                                             |
      | c2              | smallint   |       |                                             |
      | c3              | int        |       |                                             |
      | c4              | bigint     |       |                                             |
      | c5              | float      |       |                                             |
      | c6              | double     |       |                                             |
      | c7              | decimal    |       |                                             |
      | c8              | binary     |       |                                             |
      | c9              | timestamp  |       |                                             |
      | c10             | array<map<bigint,bigint>> |       |                              |
      | c11             | map<string,array<bigint>> |       |                              |
      | c12             | struct<s1:string,s2:bigint> |       |                            |
      | c13             | varchar(20) |       |                                            |
      +------------------------------------------------------------------------------------+
      
      OK
    • 樣本7:查看建立的Hash聚簇非分區表t1的資訊。聚簇屬性將顯示在Extended Info中。

      desc extended t1;

      返回結果如下。

      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$xxxxx@alibaba-inc.com | Project: $project_name                       |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2020-11-16 18:00:56                                      |
      | LastDDLTime:              2020-11-16 18:00:56                                      |
      | LastModifiedTime:         2020-11-16 18:00:56                                      |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
      +------------------------------------------------------------------------------------+
      | a        | string |       |               | true     | NULL         |              |
      | b        | string |       |               | true     | NULL         |              |
      | c        | bigint |       |               | true     | NULL         |              |
      +------------------------------------------------------------------------------------+
      | Extended Info:                                                                     |
      +------------------------------------------------------------------------------------+
      | TableID:                  e6b06f705dc34a36a5b72e5af486cab7                         |
      | IsArchived:               false                                                    |
      | PhysicalSize:             0                                                        |
      | FileNum:                  0                                                        |
      | StoredAs:                 AliOrc                                                   |
      | CompressionStrategy:      normal                                                   |
      | ClusterType:              hash                                                     |
      | BucketNum:                1024                                                     |
      | ClusterColumns:           [c]                                                      |
      | SortColumns:              [c ASC]                                                  |
      +------------------------------------------------------------------------------------+
      
      OK
    • 樣本8:查看建立的Hash聚簇分區表t2的資訊。聚簇屬性將顯示在Extended Info中。

      desc extended t2;

      返回結果如下。

      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$xxxxx@alibaba-inc.com | Project: $project_name                       |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime: 2017-12-25 11:18:26                                                    |
      | LastDDLTime: 2017-12-25 11:18:26                                                   |
      | LastModifiedTime: 2017-12-25 11:18:26                                              |
      | Lifecycle: 2                                                                       |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES | Size: 0                                                       |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field | Type   | Label | Comment                                                   |
      +------------------------------------------------------------------------------------+
      | a     | string |       |                                                           |
      | b     | string |       |                                                           |
      | c     | bigint |       |                                                           |
      +------------------------------------------------------------------------------------+
      | Partition Columns:                                                                 |
      +------------------------------------------------------------------------------------+
      | dt    | string |                                                                   |
      +------------------------------------------------------------------------------------+
      | Extended Info:                                                                     |
      +------------------------------------------------------------------------------------+
      | TableID: 91a3395d3ef64b4d9ee1d2852755                                              |
      | IsArchived: false                                                                  |
      | PhysicalSize: 0                                                                    |
      | FileNum: 0                                                                         |
      | ClusterType: hash                                                                  |
      | BucketNum: 1024                                                                    |
      | ClusterColumns: [c]                                                                |
      | SortColumns: [c ASC]                                                               |
      +------------------------------------------------------------------------------------+
      
      OK
    • 樣本9:查看Range聚簇非分區表t3的資訊。聚簇屬性將顯示在Extended Info中。

      desc extended t3;

      返回結果如下。

      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$xxxxx@alibaba-inc.com | Project: $project_name                       |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2020-11-16 18:01:05                                      |
      | LastDDLTime:              2020-11-16 18:01:05                                      |
      | LastModifiedTime:         2020-11-16 18:01:05                                      |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
      +------------------------------------------------------------------------------------+
      | a        | string |       |               | true     | NULL         |              |
      | b        | string |       |               | true     | NULL         |              |
      | c        | bigint |       |               | true     | NULL         |              |
      +------------------------------------------------------------------------------------+
      | Extended Info:                                                                     |
      +------------------------------------------------------------------------------------+
      | TableID:                  38d170aca2684f4baadbbe1931a6ae1f                         |
      | IsArchived:               false                                                    |
      | PhysicalSize:             0                                                        |
      | FileNum:                  0                                                        |
      | StoredAs:                 AliOrc                                                   |
      | CompressionStrategy:      normal                                                   |
      | ClusterType:              range                                                    |
      | BucketNum:                1024                                                     |
      | ClusterColumns:           [c]                                                      |
      | SortColumns:              [c ASC]                                                  |
      +------------------------------------------------------------------------------------+
      
      OK
    • 樣本10:查看Range聚簇分區表t4的資訊。聚簇屬性將顯示在Extended Info中。

      desc extended t4;

      返回結果如下。

      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$xxxxx@alibaba-inc.com | Project: $project_name                       |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2020-11-16 19:17:48                                      |
      | LastDDLTime:              2020-11-16 19:17:48                                      |
      | LastModifiedTime:         2020-11-16 19:17:48                                      |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
      +------------------------------------------------------------------------------------+
      | a        | string |       |               | true     | NULL         |              |
      | b        | string |       |               | true     | NULL         |              |
      | c        | bigint |       |               | true     | NULL         |              |
      +------------------------------------------------------------------------------------+
      | Partition Columns:                                                                 |
      +------------------------------------------------------------------------------------+
      | dt              | string     |                                                     |
      +------------------------------------------------------------------------------------+
      | Extended Info:                                                                     |
      +------------------------------------------------------------------------------------+
      | TableID:                  6ebc3432e283449188c861427bcd6ee4                         |
      | IsArchived:               false                                                    |
      | PhysicalSize:             0                                                        |
      | FileNum:                  0                                                        |
      | StoredAs:                 AliOrc                                                   |
      | CompressionStrategy:      normal                                                   |
      | ClusterType:              range                                                    |
      | BucketNum:                0                                                        |
      | ClusterColumns:           [c]                                                      |
      | SortColumns:              [c ASC]                                                  |
      +------------------------------------------------------------------------------------+
      
      OK
    • 樣本11:查看非分區表t5是否為Transactional表。

      說明

      推薦您使用MaxCompute用戶端查看錶是否為Transactional表,需要將MaxCompute用戶端升級到0.35.4版本。其他工具可能會存在版本升級未就緒的問題,導致查詢結果不顯示Transactional資訊。

      desc extended t5;

      返回結果如下。

      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$xxxxx@aliyun.com | Project: $project_name                            |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2021-02-18 10:56:27                                      |
      | LastDDLTime:              2021-02-18 10:56:27                                      |
      | LastModifiedTime:         2021-02-18 10:56:27                                      |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field    | Type   | Label | ExtendedLabel | Nullable | DefaultValue | Comment      |
      +------------------------------------------------------------------------------------+
      | id       | bigint |       |               | true     | NULL         |              |
      +------------------------------------------------------------------------------------+
      | Extended Info:                                                                     |
      +------------------------------------------------------------------------------------+
      ...
      | Transactional:            true                                                     |
      +------------------------------------------------------------------------------------+
    • 樣本12:查看分區表t6是否為Transactional表。

      說明

      推薦您使用MaxCompute用戶端查看錶是否為Transactional表,需要將MaxCompute用戶端升級到0.35.4版本。其他工具可能會存在版本升級未就緒的問題,導致查詢結果不顯示Transactional資訊。

      desc extended t6;

      返回結果如下。

      +------------------------------------------------------------------------------------+
      | Owner: ALIYUN$xxxxx@test.aliyunid.com | Project: $project_name                     |
      | TableComment:                                                                      |
      +------------------------------------------------------------------------------------+
      | CreateTime:               2021-02-18 15:34:54                                      |
      | LastDDLTime:              2021-02-18 15:34:54                                      |
      | LastModifiedTime:         2021-02-18 15:34:54                                      |
      +------------------------------------------------------------------------------------+
      | InternalTable: YES      | Size: 0                                                  |
      +------------------------------------------------------------------------------------+
      | Native Columns:                                                                    |
      +------------------------------------------------------------------------------------+
      | Field           | Type       | Label | Comment                                     |
      +------------------------------------------------------------------------------------+
      | id              | bigint     |       |                                             |
      +------------------------------------------------------------------------------------+
      | Partition Columns:                                                                 |
      +------------------------------------------------------------------------------------+
      | ds              | string     |                                                     |
      +------------------------------------------------------------------------------------+
      | Extended Info:                                                                     |
      +------------------------------------------------------------------------------------+
      ...
      | Transactional:            true                                                     |
      +------------------------------------------------------------------------------------+

查看分區資訊

查看某個分區表具體的分區的資訊。

  • 命令格式

    desc <table_name> partition (<pt_spec>);
  • 參數說明

    • table_name:必填。待查看分區資訊的分區表名稱。

    • pt_spec:必填。待查看的分區資訊。格式為partition_col1=col1_value1, partition_col2=col2_value1...。對於有多級分區的表,必須指明全部的分區值。

  • 使用樣本

    --查詢分區表sale_detail的分區資訊。
    desc sale_detail partition (sale_date='201310',region='beijing');

    返回結果如下。

    +------------------------------------------------------------------------------------+
    | PartitionSize: 2109112                                                             |
    +------------------------------------------------------------------------------------+
    | CreateTime:               2015-10-10 08:48:48                                      |
    | LastDDLTime:              2015-10-10 08:48:48                                      |
    | LastModifiedTime:         2015-10-11 01:33:35                                      |
    +------------------------------------------------------------------------------------+
    OK

列出專案下的表和視圖

列出專案下所有的表和視圖,或符合某規則的表和視圖。

  • 命令格式

    --列出專案下所有的表和視圖。
    show tables;
    --列出專案下表名或視圖名與chart匹配的表。
    show tables like '<chart>';
  • 使用樣本

    --列出專案下表名與sale*匹配的表。*表示任意欄位。
    show tables like 'sale*';              

    返回結果如下。

    ALIYUN$account_name:sale_detail
    ......
    --ALIYUN是系統提示符,表示您是阿里雲主帳號使用者。如果您是阿里雲RAM使用者,系統提示符為RAM。

列出所有分區

列出一張表中的所有分區。當表不存在或為非分區表時,返回報錯。

  • 命令格式

    show partitions <table_name>; 
  • 參數說明

    table_name:必填。待查看分區資訊的分區表名稱。

  • 使用樣本

    --列出sale_detail中的所有分區。
    show partitions sale_detail;

    返回結果如下。

    sale_date=201310/region=beijing
    sale_date=201312/region=shenzhen
    sale_date=201312/region=xian
    sale_date=2014/region=shenzhen
    
    OK

相關文檔