邏輯分區表
更新(xin)時間 2025-02-14 10:21:39
最近(jin)更新時間: 2025-02-14 10:21:39
分享文章
本(ben)文為您介紹如何管理range分(fen)(fen)區表(biao)、list分(fen)(fen)區表(biao)和多級分(fen)(fen)區表(biao)。
range分區表
創建主分區
teledb=# create table t_native_range (f1 bigint,f2 timestamp default now(), f3 integer) partition by range ( f2 ) distribute by shard(f1); CREATE TABLE建立兩(liang)個子表
teledb=# create table t_native_range_201709 partition of t_native_range (f1 ,f2 , f3 ) for values from ('2017-09-01') to ('2017-10-01'); CREATE TABLE teledb=# create table t_native_range_201710 partition of t_native_range (f1 ,f2 , f3 ) for values from ('2017-10-01') to ('2017-11-01'); CREATE TABLE查看表結構
teledb=# \d+ t_native_range Table "public.t_native_range" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+-----------------------------+-----------+----------+---------+---------+--------------+------------- f1 | bigint | | | | plain | | f2 | timestamp without time zone | | | now() | plain | | f3 | integer | | | | plain | | Partition key: RANGE (f2) Partitions: t_native_range_201709 FOR VALUES FROM ('2017-09-01 00:00:00') TO ('2017-10-01 00:00:00'), t_native_range_201710 FOR VALUES FROM ('2017-10-01 00:00:00') TO ('2017-11-01 00:00:00') Distribute By: SHARD(f1) Location Nodes: ALL DATANODES創建default分區
不創建default 分(fen)區(qu)，插入范(fan)圍越界出錯。
teledb=# insert into t_native_range values(1,'2016-09-01',1); ERROR: node:dn01, backend_pid:39912, nodename:dn01,backend_pid:39912,message:no partition of relation "t_native_range" found for row DETAIL: Partition key of the failing row contains (f2) = (2016-09-01 00:00:00).創建default 分區后(hou)能正(zheng)常(chang)插入數(shu)據。
teledb=# CREATE TABLE t_native_range_default PARTITION OF t_native_range DEFAULT; CREATE TABLE teledb=# insert into t_native_range values(1,'2016-09-01',1); INSERT 0 1
list分區表(biao)
創建(jian)主分(fen)區
teledb=# create table t_native_list(f1 bigserial not null,f2 text, f3 integer,f4 date) partition by list( f2 ) distribute by shard(f1); CREATE TABLE建立兩個子表，分別(bie)存入“廣東”和“北(bei)京(jing)”
teledb=# create table t_list_gd partition of t_native_list(f1 ,f2 , f3,f4) for values in ('廣東'); CREATE TABLE teledb=# create table t_list_bj partition of t_native_list(f1 ,f2 , f3,f4) for values in ('北京'); CREATE TABLE看(kan)表(biao)結構
teledb=# \d+ t_native_list Table "public.t_native_list" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+--------------------------------+-----------+----------+-------------------------------------------+----------+------------- -+------------- f1 | bigint | | not null | nextval('t_native_list_f1_seq'::regclass) | plain | | f2 | text | | | | extended | | f3 | integer | | | | plain | | f4 | timestamp(0) without time zone | | | | plain | | Partition key: LIST (f2) Partitions: t_list_bj FOR VALUES IN ('北京'), t_list_gd FOR VALUES IN ('廣東') Distribute By: SHARD(f1) Location Nodes: ALL DATANODES創建default 分區
沒有(you)default 分(fen)區情況下會出錯，插入會出錯。
teledb=# insert into t_native_list values(1,'上海',1,current_date); ERROR: node:dn01, backend_pid:40092, nodename:dn01,backend_pid:40092,message:no partition of relation "t_native_list" found for row DETAIL: Partition key of the failing row contains (f2) = (上海).創建后就能正常插(cha)入。
teledb=# CREATE TABLE t_native_list_default PARTITION OF t_native_list DEFAULT; CREATE TABLE teledb=# insert into t_native_list values(1,'上海',1,current_date); INSERT 0 1
多級分區表
創建主表
teledb=# create table t_native_mul_list(f1 bigserial not null,f2 integer,f3 text,f4 text, f5 date) partition by list ( f3 ) distribute by shard(f1); CREATE TABLE創建二級表
teledb=# create table t_native_mul_list_gd partition of t_native_mul_list for values in ('廣東')partition by range(f5); CREATE TABLE teledb=# create table t_native_mul_list_bj partition of t_native_mul_list for values in ('北京') partition by range(f5); CREATE TABLE teledb=# create table t_native_mul_list_sh partition of t_native_mul_list for values in ('上海');創建(jian)三(san)級表(biao)
teledb=# create table t_native_mul_list_gd_201701 partition of t_native_mul_list_gd(f1,f2,f3,f4,f5) for values from ('2017-01-01') to ('2017-02-01'); CREATE TABLE teledb=# create table t_native_mul_list_gd_201702 partition of t_native_mul_list_gd(f1,f2,f3,f4,f5) for values from ('2017-02-01') to ('2017-03-01'); CREATE TABLE teledb=# create table t_native_mul_list_bj_201701 partition of t_native_mul_list_bj(f1,f2,f3,f4,f5) for values from ('2017-01-01') to ('2017-02-01'); CREATE TABLE teledb=# create table t_native_mul_list_bj_201702 partition of t_native_mul_list_bj(f1,f2,f3,f4,f5) for values from ('2017-02-01') to ('2017-03-01'); CREATE TABLE查看表結(jie)構
teledb=# \d+ t_native_mul_list Table "public.t_native_mul_list" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+-----------+-----------+-------------+-----------------+-----------+---------------+------------- f1 | bigint | | not null | nextval('t_native_mul_list_f1_seq'::regclass) | plain | | f2 | integer | | | | plain | | f3 | text | | | | extended | | f4 | text | | | | extended | | f5|timestamp(0) without time zone| | | | plain | | Partition key: LIST (f3) Partitions: t_native_mul_list_bj FOR VALUES IN ('北京'), PARTITIONED, t_native_mul_list_gd FOR VALUES IN ('廣東'), PARTITIONED, t_native_mul_list_sh FOR VALUES IN ('上海') Distribute By: SHARD(f1) Location Nodes: ALL DATANODES teledb=# \d+ t_native_mul_list_gd Table "public.t_native_mul_list_gd" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+--------------------------------+-----------+----------+-----------------------------------------------+----------+--------- -----+------------- f1 | bigint | | not null | nextval('t_native_mul_list_f1_seq'::regclass) | plain | | f2 | integer | | | | plain | | f3 | text | | | | extended | | f4 | text | | | | extended | | f5 | timestamp(0) without time zone | | | | plain | | Partition of: t_native_mul_list FOR VALUES IN ('廣東') Partition constraint: ((f3 IS NOT NULL) AND (f3 = ANY (ARRAY['廣東'::text]))) Partition key: RANGE (f5) Partitions: t_native_mul_list_gd_201701 FOR VALUES FROM ('2017-01-01 00:00:00') TO ('2017-02-01 00:00:00'), t_native_mul_list_gd_201702 FOR VALUES FROM ('2017-02-01 00:00:00') TO ('2017-03-01 00:00:00') Distribute By: SHARD(f1) Location Nodes: ALL DATANODESTeleDB支持(chi)存在1級和2級分(fen)區(qu)混用，大家不(bu)需要(yao)都平(ping)級。
