==>CREATE TABLE rank (id int, rank int, year int, gender char(1), count int) DISTRIBUTED BY (id) PARTITION BY RANGE (year) ( START (2001) END (2008) EVERY (1), DEFAULT PARTITION extra ); [列表分区] ==>CREATE TABLE rank (id int, rank int, year int, gender char(1), count ...
createtabletmp_001( month_id numeric(8), serv_id numeric(30), cust_id numeric(30) )with( appendonly=true, compresslevel=5, orientation=column, compresstype=zlib, oids=false )distributedby(month_id,serv_id) 1.分布键(哈希键) distributed by (a) 指定a字段为分布键 distributer randomly 随机分...
CREATE TABLE rank (id int, rank int, year int, gender char(1), count int) DISTRIBUTED BY (id) PARTITION BY RANGE (year) ( START (2006) END (2016) EVERY (1), DEFAULT PARTITION extra ); 1. 2. 3. 4. 5. 6. every:指定跨越基数。 列表分区(list) 根据值的分组,相同的数据归类到一...
create table test_ao(id int) with (appendonly=true, compresslevel=5) distributed by (id); compresslevel是压缩率,取值为1~9,一般选择5就足够了,值越高压缩率越高 5.2.3 AO表列存压缩 AO表列存压缩 与上表的压缩方式不同 drop table if exists test_ao; create table test_ao(id int) with (appe...
create table tb_cp_01(id int , date date, amt decimal(10,2)) distributed by (id) partition by range(date) --指定分区为date字段 ( start (date '2022-01-01') inclusive --inclusive是包含的意思 end (date '2023-01-01') exclusive --exclusive是不包含意思,默认也是start包含,end不包含 ...
(2)DISTRIBUTED BY (laowang),分布键,按照这个字段分布到各个segement服务器; (3)PARTITION ,按时间分区。 CREATE TABLE "public"."t_ruanjianlaowang" ( "laowang" text, "timestamp" text, ) WITH (appendonly=true, compresslevel=5) DISTRIBUTED BY (laowang) ...
create tabletb_dk_01(a int,b int)distributedby(b);create tabletb_dk_02(a int,b int);create tabletb_dk_03(a int primary key,b int);create tabletb_dk_04(a int,b int)distributed randomly; 1.2.Greenplum数据库表分区 分区并不会改变表数据在Segment之间的物理分布。
create table t1(a serial) distributed by (a);insert into t1 select nextval('t1_a_seq') from generate_series(1,100);postgres=# select sum(a) from t1;sum---5050(1 row) 此时,t1表的数据是存在ADB的本地存储中的,属于热数据。 (4) 将表数据迁移到冷存HDFS alter table t1 set (storage...
CREATE TABLE rank (id int, rank int, year int, gender char(1), count int ) DISTRIBUTED BY (id) PARTITION BY LIST (gender) ( PARTITION girls VALUES ('F'), PARTITION boys VALUES ('M'), DEFAULT PARTITION other ); 注意: 当前的Greenplum数据库传统优化器允许列表分区带有多列(组合)分区键。
create table t1(a serial) distributed by (a); insert into t1 select nextval('t1_a_seq') from generate_series(1,100); postgres=# select sum(a) from t1; sum --- 5050 (1 row) 此时,t1表的数据是存在ADB的本地存储中的,属于热数据。 (4) 将表数据迁移到冷存HDFS alter table t1 set (...