建立分區表 - - 阿里雲

通常在關係型資料庫中為提高查詢效能，會對巨量資料量的表進行分區。同樣，在DLA中您也可以使用分區表對OSS、Tablestore中的資料進行細化處理，縮短查詢回應時間。本文檔以OSS資料為例，介紹如何通過DLA建立、使用和管理分區表。

分區表與OSS目錄的關係

在DLA中，可以將儲存在OSS中的目錄或檔案對應成一張分區表，表中的資料是OSS的檔案內容，分區列對應OSS中的目錄，該目錄需遵守以下命名規則：

分區列對應表的LOCATION下的一個子目錄，目錄的命名規則為分區列名=分區列值。
如果有多個分區列，則需要按照建表語句中指定的分區列順序依次嵌套。

例如，以下是OSS中的目錄結構：

https://bucket-name.oss-cn-hangzhou.aliyuncs.com/dla/y=2018/m=12/kv2.txt
https://bucket-name.oss-cn-hangzhou.aliyuncs.com/dla/y=2019/m=2/kv1.txt

針對上述OSS目錄結構，在DLA中建立分區表時，有兩個分區列分別為y（年份）和m（月份）。

CREATE EXTERNAL TABLE dla (
    prod_name string,
    cnt bigint)
PARTITIONED BY (y string, m string)
STORED AS TEXTFILE
LOCATION 'oss://oss-bucket-name/dla/';

注意事項

在DLA中建立和使用OSS分區表時，以下注意事項需要您知曉：

OSS中分區列的目錄結構嵌套順序應與表中定義的順序一致。

針對上述OSS目錄結構，以下建立分區表的語句為錯誤語句。

  CREATE EXTERNAL TABLE dla (
      prod_name string,
      cnt bigint)
  PARTITIONED BY (m string, y string)
  STORED AS TEXTFILE
  LOCATION 'oss://bucket-name/dla/';

分區表只會掃描分區列所在目錄下的資料。
對於以下目錄結構，如果建表語句中指定的分區列為y和m，則通過分區表只能查詢kv3.txt中的資料，無法查詢kv4.txt中的資料。
```
  https://bucket-name.oss-cn-hangzhou.aliyuncs.com/dla/y=2019/m=3/kv3.txt
  https://bucket-name.oss-cn-hangzhou.aliyuncs.com/dla/kv4.txt
```

前提條件

在DLA中建立和使用OSS分區表時，需要通過以下操作在OSS中準備測試資料：

開通OSS服務，請參見開通OSS服務。
建立儲存空間，請參見建立儲存空間。
按照分區列名=分區列值規則建立目錄，請參見建立目錄。
上傳檔案資料，請參見上傳檔案資料。

步驟一：建立Schema

登入DLA控制台。
單擊左側導覽列的SQL訪問點，然後單擊登入DMS，執行以下SQL建立OSS Schema。
您也可以通過MySQL用戶端或者程式碼等方式串連DLA，然後執行以下SQL建立OSS Schema。
```
 CREATE SCHEMA dla_oss_db with DBPROPERTIES(
  catalog='oss',
  location= 'oss://oss-bucket-name/dla/'
  );
```

catalog：指定建立的Schema類型為OSS。
location：檔案所在的OSS Bucket目錄，需以/結尾。

步驟二：建立分區表

在DLA中執行以下SQL建立為OSS檔案建立分區表。

CREATE EXTERNAL TABLE dla (
    prod_name string,
    cnt bigint)
PARTITIONED BY (y string, m string)
STORED AS TEXTFILE
LOCATION 'oss://oss-bucket-name/dla/';

步驟三：使用MSCK命令更新分區資訊

分區表建立成功後，需要執行MSCK REPAIR TABLE將分區資訊同步到DLA中。

MSCK REPAIR TABLE dla;

重要

MSCK命令只能識別符合DLA分區列命名規則的目錄，即分區列的目錄名為分區列名=分區列值。
當分區表對應的OSS目錄發生變化時，需重新執行MSCK命令，DLA根據OSS中當前分區值自動同步分區資訊。

MSCK命令執行成功後，返回以下提示資訊。

Repair: Added partition to metastore dla_oss_db.dla:y=2018/m=12
Repair: Added partition to metastore dla_oss_db.dla:y=2019/m=2

步驟四：使用SHOW PARTITIONS命令查看分區資訊

MSCK執行成功後，可以通過SHOW PARTITIONS查看分區表中所有的分區資訊。

show partitions dla;
+-----------+
| y=2018/m=12|
| y=2019/m=2 |

步驟五：查詢分區表資料

select count(*) from dla;
+-------+
| _col0 |
+-------+
|  4    |
select * from dla;
+---------------------------+
|prod_name | cnt | y  |  m  |
+---------------------------+
|顯示器    | 2   |2019 |  2  |
|硬碟      | 6   |2018 |  12 |
|鍵盤      | 3   |2018 |  12 |
|滑鼠      | 1   |2019 |  2  |

可以使用分區列作為查詢過濾條件：

select * from dla where y='2019';
+---------------------------+
|prod_name | cnt | y  |  m  |
+---------------------------+
|顯示器    | 2   |2019 |  2  |
|滑鼠      | 1   |2019 |  2  |

管理分區

同步分區資訊
當OSS中的分區目錄發生變化，例如OSS中新增分區目錄時，執行MSCK命令使分區生效。
```
  MSCK REPAIR TABLE table_name;
```

添加分區

  ALTER TABLE table_name ADD [IF NOT EXISTS] PARTITION partition_spec [LOCATION 'location'][, PARTITION partition_spec [LOCATION 'location'], ...];
   partition_spec:
    : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

可以一次添加多個分區，分區之間用逗號分隔。

  ALTER TABLE order_part ADD 
      PARTITION (dt='2008-08-08', status='ready') location '/path/to/ready/part080808',
      PARTITION (dt='2008-08-09', status='new') location '/path/to/new/part080809';

重要

新增分區時可以使用IF NOT EXISTS判斷分區是否存在，若新增分區已存在，則新的LOCATION會覆蓋原有分區所指向的目錄。

刪除分區
```
  ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...];
```
可以一次刪除多個分區，分區之間以英文逗號（,）分隔。
```
  ALTER TABLE order_part DROP
      PARTITION (dt='2008-08-08', status='ready');
```
重要
- 目前只支援以分區列名=分區列值刪除指定分區，不支援以運算式方式，例如partitionCol > 100指定分區值。
- 如果已經刪除的分區目錄符合分區命名規則即分區列名=分區列值，則執行MSCK命令將自動添加已經刪除的分區。