维护分区表 ----------- 通常分区集在定义表的时候就已经确定了，但我们常常需要周期性的删除旧分区并添加新分区,往新分区里添加新数据。 分区最重要的好处是它能恰到好处的适应这个需求：以极快的速度操作分区的结构， 而不是痛苦的物理移动大量数据。 .. note:: 以下内容中仅适用于静态分区表，动态分区表会由 OushuDB 自动管理，不需要手动操作。 添加一个分区 +++++++++++++ 如果创建分区表时使用了分区模版定义，则新添加的分区会根据模版进行分区： :: ALTER TABLE mult_measurement ADD PARTITION START (date '2020-02-01') INCLUSIVE END (date '2020-03-01') EXCLUSIVE; 如果你在创建表的时候没有使用subpartition template，你需要在添加分区的时候给出子分区定义： :: ALTER TABLE sales ADD PARTITION START (date '2009-02-01') INCLUSIVE END (date '2009-03-01') EXCLUSIVE ( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ('asia'), SUBPARTITION europe VALUES ('europe') ); 你也可以单独修改一个二级分区： :: CREATE TABLE sales_two_level (trans_id int, date date, amount decimal(9,2), region text) PARTITION BY RANGE (date) SUBPARTITION BY LIST (region) SUBPARTITION TEMPLATE ( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ('asia'), SUBPARTITION europe VALUES ('europe')) (START (date '2011-01-01') INCLUSIVE END (date '2012-01-01') EXCLUSIVE EVERY (INTERVAL '1 month'), DEFAULT PARTITION outlying_dates); ALTER TABLE sales_two_level ALTER PARTITION FOR (RANK(12)) ADD PARTITION africa VALUES ('africa'); 其中RANK(12)表示第12个分区。 注：指定一个分区可以使用 PARTITION FOR (value) or PARTITION FOR(RANK(number)) 语法。 如果你的分区表有一个Default分区的话，你不可以向该分区表添加分区，你只可以通过分裂Default分区的方法来添加分区。 重命名分区 ++++++++++++ 分区表默认使用__prt_命名规则，例如：mult_measurement_1_prt_1指的是父表名称为mult_measurement，第一级分区名称为1的分区。在创建Range分区表时，如果没有指定分区名字，分区的名字会自动生成为数字。 改变父表的名字同时会改变分区表的名字，重命名使用如下命令： :: ALTER TABLE mult_measurement RENAME TO dc_measurement; postgres=# ALTER TABLE mult_measurement RENAME TO dc_measurement; ALTER TABLE postgres=# \dS+ dc_measurement Table "public.dc_measurement" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -----------+---------+-----------+----------+---------+----------+--------------+------------- city | text | | | | extended | | logdate | date | | | | plain | | peaktemp | integer | | | | plain | | unitsales | integer | | | | plain | | Child tables: dc_measurement_1_prt_1, dc_measurement_1_prt_2, dc_measurement_1_prt_3 Options: appendonly=true 一般默认分区名称不利于用户理解，可根据实际业务含义对分区进行命名，以便识别理解： :: ALTER TABLE dc_measurement RENAME PARTITION FOR ('2020-01-01') TO jan20; postgres=# \dS+ dc_measurement Table "public.dc_measurement" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -----------+---------+-----------+----------+---------+----------+--------------+------------- city | text | | | | extended | | logdate | date | | | | plain | | peaktemp | integer | | | | plain | | unitsales | integer | | | | plain | | Child tables: dc_measurement_1_prt_2, dc_measurement_1_prt_3, dc_measurement_1_prt_jan20 你可以改变一个分区的名字，例如： :: ALTER TABLE sales RENAME PARTITION FOR ('2008-01-01') TO jan08; 添加默认分区 ++++++++++++ 若在创建分区表时未定义默认分区，可使用ALTER TABLE命令未分区表添加默认分区。若分区表有多级分区时，需要为每个分区比添加默认分区： :: ALTER TABLE dc_measurement ADD DEFAULT PARTITION other; ALTER TABLE dc_measurement ALTER PARTITION FOR (RANK(1)) ADD DEFAULT PARTITION other; ALTER TABLE dc_measurement ALTER PARTITION FOR (RANK(2)) ADD DEFAULT PARTITION other; ALTER TABLE dc_measurement ALTER PARTITION FOR (RANK(3)) ADD DEFAULT PARTITION other; 如果定义分区表时没有添加默认分区，那么加载插入数据时，如果与已有分区约束都不匹配时，将拒绝加载该数据内容，为了保障数据完整性，一般会使用默认分区确保将不匹配其他分区的数据插入到默认分区中。 删除一个分区 ++++++++++++ 通常使用ALTER TABLE ... DROP PARTITION命令删除一个分区。如果一个分区有子分区，在删除该分区的时候，它的子分区也会被删除。 :: ALTER TABLE dc_measurement DROP PARTITION FOR (RANK(1)); Truncate分区 ++++++++++++ 通常使用ALTER TABLE ... TRUNCATE PARTITION命令truncate分区，在Truncate一个分区时，其子分区也会被Truncate： :: ALTER TABLE dc_measurement TRUNCATE PARTITION FOR (RANK(2)); 交换分区 ++++++++ 通常使用ALTER TABLE...EXCHANGE PARTITION命令交换一个分区，交换分区操作把一个表和一个已存在分区进行交换（Swap）。你只可以交换叶子节点分区。 分区交换通常对数据加载很有用。例如，你可以首先加载数据到一个中间表，然后把该中间表交换到分区表内部。 你也可以利用分区交换改变分区表的类型。例如： :: CREATE TABLE exc_tb (LIKE measurement) WITH (appendonly=true); INSERT INTO exc_tb SELECT * FROM measurement_1_prt_1; ALTER TABLE measurement EXCHANGE PARTITION FOR (DATE '2020-01-01') WITH TABLE exc_tb; 或者 :: CREATE TABLE sales (id int, date date, amt decimal(10,2)) PARTITION BY RANGE (date) ( START (date '2008-01-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE EVERY (INTERVAL '1 month') ); CREATE TABLE jan (LIKE sales) WITH (appendonly=true, orientation=row, compresstype = snappy); INSERT INTO jan SELECT * FROM sales_1_prt_1 ; ALTER TABLE sales EXCHANGE PARTITION FOR (RANK(1)) WITH TABLE jan; 可读或可写外部表也可用于交换分区使用，但是外部表的结构要与分区表一致，例如以下样例是交换measurement_1_prt_2分区表和外部表： :: //创建与分区表结构一样的可写外部表 CREATE EXTERNAL TABLE measurement_ext ( LIKE measurement_1_prt_2 ) LOCATION ('hdfs://localhost:8020/test') FORMAT 'csv' (DELIMITER '|') DISTRIBUTED BY (city_id); //创建可读外部表从上述创建的可写外部表读取数据。 CREATE readable EXTERNAL TABLE measurement_ext_feb ( LIKE measurement_1_prt_2) LOCATION ('hdfs://localhost:8020/test') FORMAT 'csv' (DELIMITER '|'); //将分区表数据复制到可写外部表 INSERT INTO measurement_ext SELECT * FROM measurement_1_prt_2 ; //交换分区和外部表 ALTER TABLE measurement ALTER PARTITION FOR (DATE '2020-02-01') EXCHANGE PARTITION FOR (DATE '2020-02-01') WITH TABLE measurement_ext_feb WITHOUT VALIDATION; //删除可读分区表 DROP TABLE measurement_ext_feb; 分区分裂 ++++++++++ 使用ALTER TABLE...SPLITE PARTITION命令分裂一个已经存在的分区，分割分区只适用于范围分区，不能用于列表分区。且只分裂最后一层保存数据的分区。例如： :: ALTER TABLE measurement SPLIT PARTITION FOR ('2020-01-01') AT ('2020-01-16') INTO (PARTITION jan20early, PARTITION jan20last); postgres=# \dS+ measurement Table "public.measurement" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description -----------+---------+-----------+----------+---------+---------+--------------+------------- city_id | integer | | not null | | plain | | logdate | date | | not null | | plain | | peaktemp | integer | | | | plain | | unitsales | integer | | | | plain | | Child tables: measurement_1_prt_2, measurement_1_prt_3, measurement_1_prt_jan20early, measurement_1_prt_jan20last 又例如： :: CREATE TABLE sales_split (id int, date date, amt decimal(10,2)) PARTITION BY RANGE (date) ( START (date '2008-01-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE EVERY (INTERVAL '1 month') ); ALTER TABLE sales_split SPLIT PARTITION FOR ('2008-01-01') AT ('2008-01-16') INTO (PARTITION jan081to15, PARTITION jan0816to31); 如果分区表有默认分区，必须使用默认分区作分裂分区，且需指定当前默认分区为第二个分割分区。例如为measurement表从默认分区中分割添加一个20年5月的分区： :: ALTER TABLE measurement SPLIT DEFAULT PARTITION START ('2020-04-01') INCLUSIVE END ('2020-05-01') EXCLUSIVE INTO (PARTITION may20, default partition); 又例如，下面的例子通过分裂 Default分区的方式添加一个jan2009分区： :: CREATE TABLE sales_split_default (id int, date date, amt decimal(10,2)) PARTITION BY RANGE (date) ( START (date '2008-01-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE EVERY (INTERVAL '1 month'), DEFAULT PARTITION extra); ALTER TABLE sales_split_default SPLIT DEFAULT PARTITION START ('2009-01-01') INCLUSIVE END ('2009-02-01') EXCLUSIVE INTO (PARTITION jan2009, default partition); 修改子分区模版 ++++++++++++++ 你可以通过Alter命令修改子分区模版。先创建一个两级分区表。 :: CREATE TABLE sales (trans_id int, date date, amount decimal(9,2), region text) DISTRIBUTED BY (trans_id) PARTITION BY RANGE (date) SUBPARTITION BY LIST (region) SUBPARTITION TEMPLATE ( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ('asia'), SUBPARTITION europe VALUES ('europe'), DEFAULT SUBPARTITION other_regions ) ( START (date '2014-01-01') INCLUSIVE END (date '2014-04-01') EXCLUSIVE EVERY (INTERVAL '1 month') ); 下面这条命令修改子分区模版。 :: ALTER TABLE sales SET SUBPARTITION TEMPLATE ( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ('asia'), SUBPARTITION europe VALUES ('europe'), SUBPARTITION africa VALUES ('africa'), DEFAULT SUBPARTITION regions ); 下面这条命令可以删除子分区模版。 :: ALTER TABLE sales SET SUBPARTITION TEMPLATE (); 对已存在非分区表进行分区 对已存在表进行分区，你需要创建一个新的分区表，并把需要分区的表的数据导入新的表。并把相关权限分配好。 :: CREATE TABLE sales2 (LIKE sales) PARTITION BY RANGE (date) ( START (date '2008-01-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE EVERY (INTERVAL '1 month') ); INSERT INTO sales2 SELECT * FROM sales; DROP TABLE sales; ALTER TABLE sales2 RENAME TO sales; GRANT ALL PRIVILEGES ON sales TO admin; GRANT SELECT ON sales TO guest;