Greenplum怎么創(chuàng)建表的分布鍵

本篇內(nèi)容介紹了“Greenplum怎么創(chuàng)建表的分布鍵”的有關(guān)知識，在實(shí)際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

Greenplum創(chuàng)建表--分布鍵

Greenplum是分布式系統(tǒng)，創(chuàng)建表時需要指定分布鍵（創(chuàng)建表需要CREATEDBA權(quán)限），目的在于將數(shù)據(jù)平均分布到各個segment。選擇分布鍵非常重要，選擇錯了會導(dǎo)致數(shù)據(jù)不唯一，更嚴(yán)重的是會造成SQL性能急劇下降。

Greenplum有兩種分布策略：

1、hash分布。

Greenplum默認(rèn)使用hash分布策略。該策略可選一個或者多個列作為分布鍵（distribution key，簡稱DK）。分布鍵做hash算法來確認(rèn)數(shù)據(jù)存放到對應(yīng)的segment上。相同分布鍵值會hash到相同的segment上。表上最好有唯一鍵或者主鍵，這樣能保證數(shù)據(jù)均衡分不到各個segment上。語法，distributed by。

如果沒有主鍵或者唯一鍵，默認(rèn)選擇第一列作為分布鍵。增加主鍵

2、隨機(jī)（randomly）分布。

數(shù)據(jù)會被隨機(jī)分不到segment上，相同記錄可能會存放在不同的segment上。隨機(jī)分布可以保證數(shù)據(jù)平均，但是Greenplum沒有跨節(jié)點(diǎn)的唯一鍵約束數(shù)據(jù)，所以無法保證數(shù)據(jù)唯一?；谖ㄒ恍院托阅芸紤]，推薦使用hash分布，性能部分會另開一篇文檔詳細(xì)介紹。語法，distributed randomly。

一、hash分布鍵

創(chuàng)建表，未指定分布列、分布類型，默認(rèn)創(chuàng)建hash分布表，把第一列ID字段作為了分布鍵。

testDB=# create table t_hash(id int,name varchar(50)) distributed by (id);

CREATE TABLE

testDB=# 

testDB=# \d t_hash

           Table "public.t_hash"

 Column |         Type          | Modifiers 

--------+-----------------------+-----------

 id     | integer               | 

 name   | character varying(50) | 

Distributed by: (id)

添加主鍵后，主鍵升級為分布鍵替代了id列。

testDB=# alter table t_hash add primary key (name);

NOTICE:  updating distribution policy to match new primary key

NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "t_hash_pkey" for table "t_hash"

ALTER TABLE

testDB=# \d t_hash

           Table "public.t_hash"

 Column |         Type          | Modifiers 

--------+-----------------------+-----------

 id     | integer               | 

 name   | character varying(50) | not null

Indexes:

    "t_hash_pkey" PRIMARY KEY, btree (name)

Distributed by: (name)

驗(yàn)證hash分布表可實(shí)現(xiàn)主鍵或者唯一鍵值的唯一性

testDB=# insert into t_hash values(1,'szlsd1');

INSERT 0 1

testDB=#

testDB=# insert into t_hash values(2,'szlsd1');

ERROR:  duplicate key violates unique constraint "t_hash_pkey"(seg2 gp-s3:40000 pid=3855)

另外，主鍵列上依然能夠創(chuàng)建唯一鍵

testDB=# create unique index u_id on t_hash(name);

CREATE INDEX

testDB=#

testDB=#

testDB=# \d t_hash

           Table "public.t_hash"

 Column |         Type          | Modifiers

--------+-----------------------+-----------

 id     | integer               |

 name   | character varying(50) | not null

Indexes:

    "t_hash_pkey" PRIMARY KEY, btree (name)

    "u_id" UNIQUE, btree (name)

Distributed by: (name)

但是，非主鍵列無法單獨(dú)創(chuàng)建唯一索引，想創(chuàng)建的話必須包含多有分布鍵列

testDB=#  create unique index uk_id on t_hash(id);

ERROR:  UNIQUE index must contain all columns in the distribution key of relation "t_hash"

testDB=#  create unique index uk_id on t_hash(id,name);

CREATE INDEX

testDB=# \d t_hash

           Table "public.t_hash"

 Column |         Type          | Modifiers

--------+-----------------------+-----------

 id     | integer               |

 name   | character varying(50) | not null

Indexes:

    "t_hash_pkey" PRIMARY KEY, btree (name)

    "uk_id" UNIQUE, btree (id, name)

Distributed by: (name)

刪除主鍵后，原h(huán)ash分布鍵依然不變。

testDB=# alter table t_hash drop constraint t_hash_pkey;

ALTER TABLE

testDB=# \d t_hash

           Table "public.t_hash"

 Column |         Type          | Modifiers

--------+-----------------------+-----------

 id     | integer               |

 name   | character varying(50) | not null

Distributed by: (name)

當(dāng)分布鍵不是主鍵或者唯一鍵時，我們來驗(yàn)證分布鍵的相同值落在一個segment的結(jié)論。

下面的實(shí)驗(yàn)，name列是分布鍵，我們插入相同的name值，可以看到7條記錄都落在了2號segment節(jié)點(diǎn)中。

testDB=#  insert into t_hash values(1,'szlsd');

INSERT 0 1

testDB=#  insert into t_hash values(2,'szlsd');

INSERT 0 1

testDB=#  insert into t_hash values(3,'szlsd');

INSERT 0 1

testDB=#  insert into t_hash values(4,'szlsd');

INSERT 0 1

testDB=#  insert into t_hash values(5,'szlsd');

INSERT 0 1

testDB=#  insert into t_hash values(6,'szlsd');

INSERT 0 1

testDB=#

testDB=#

testDB=# select gp_segment_id,count(*) from t_hash group by gp_segment_id; 

 gp_segment_id | count

---------------+-------

             2 |     7

(1 row)

二、隨機(jī)分布鍵

創(chuàng)建隨機(jī)分布表需加distributed randomly關(guān)鍵字，具體使用哪列作為分布鍵不得而知。

testDB=# create table t_random(id int ,name varchar(100)) distributed randomly;

CREATE TABLE

testDB=#

testDB=#

testDB=# \d t_random

           Table "public.t_random"

 Column |          Type          | Modifiers

--------+------------------------+-----------

 id     | integer                |

 name   | character varying(100) |

Distributed randomly

驗(yàn)證主鍵/唯一鍵的唯一性，可以看到隨機(jī)分布表不能創(chuàng)建主鍵和唯一鍵

testDB=# alter table t_random add primary key (id,name);

ERROR:  PRIMARY KEY and DISTRIBUTED RANDOMLY are incompatible

testDB=#

testDB=# create unique index uk_r_id on t_random(id);

ERROR:  UNIQUE and DISTRIBUTED RANDOMLY are incompatible

testDB=#

從實(shí)驗(yàn)中可以看出無法實(shí)現(xiàn)數(shù)據(jù)的唯一性。并且，數(shù)據(jù)插入隨機(jī)分布表，并不是輪詢插入，實(shí)驗(yàn)中共有3個segment，但是在1號插入3條記錄，在2號segment節(jié)點(diǎn)插入2條記錄后，才在0號segment中插入數(shù)據(jù)。隨機(jī)分布表如何實(shí)現(xiàn)數(shù)據(jù)平均分配不得而知。這個實(shí)驗(yàn)也驗(yàn)證了隨機(jī)分布表的相同值分布在不同segment的結(jié)論。

testDB=# insert into t_random values(1,'szlsd3');

INSERT 0 1

testDB=# select gp_segment_id,count(*) from t_random group by gp_segment_id;

 gp_segment_id | count

---------------+-------

             1 |     1

(1 row)

testDB=#

testDB=# insert into t_random values(1,'szlsd3');

INSERT 0 1

testDB=# select gp_segment_id,count(*) from t_random group by gp_segment_id;

 gp_segment_id | count

---------------+-------

             2 |     1

             1 |     1

(2 rows)

testDB=# insert into t_random values(1,'szlsd3');

INSERT 0 1

testDB=# select gp_segment_id,count(*) from t_random group by gp_segment_id;

 gp_segment_id | count

---------------+-------

             2 |     1

             1 |     2

(2 rows)

testDB=# insert into t_random values(1,'szlsd3');

INSERT 0 1

testDB=# select gp_segment_id,count(*) from t_random group by gp_segment_id;

 gp_segment_id | count

---------------+-------

             2 |     2

             1 |     2

(2 rows)

testDB=# insert into t_random values(1,'szlsd3');

INSERT 0 1

testDB=# select gp_segment_id,count(*) from t_random group by gp_segment_id;

 gp_segment_id | count

---------------+-------

             2 |     2

             1 |     3

(2 rows)

testDB=# insert into t_random values(1,'szlsd3');

INSERT 0 1

testDB=# select gp_segment_id,count(*) from t_random group by gp_segment_id;

 gp_segment_id | count

---------------+-------

             2 |     2

             1 |     3

             0 |     1

(3 rows)

三、CTAS繼承原表分布鍵

Greenplum中有兩種CTAS語法，無論哪種語法，都默認(rèn)繼承原表的分布鍵。但是，不會繼承表的一些特殊屬性，如主鍵、唯一鍵、APPENDONLY、COMPRESSTYPE（壓縮）等。

testDB=# \d t_hash;

           Table "public.t_hash"

 Column |         Type          | Modifiers

--------+-----------------------+-----------

 id     | integer               |

 name   | character varying(50) | not null

Indexes:

    "t_hash_pkey" PRIMARY KEY, btree (name)

    "uk_id" UNIQUE, btree (id, name)

Distributed by: (name)

testDB=#

testDB=#

testDB=# create table t_hash_1 as select * from t_hash;

NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'name' as the Greenplum Database data distribution key for this table.

HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.

SELECT 0

testDB=# \d t_hash_1

          Table "public.t_hash_1"

 Column |         Type          | Modifiers

--------+-----------------------+-----------

 id     | integer               |

 name   | character varying(50) |

Distributed by: (name)

testDB=#

testDB=# create table t_hash_2 (like t_hash);

NOTICE:  Table doesn't have 'distributed by' clause, defaulting to distribution columns from LIKE table

CREATE TABLE

testDB=# \d t_hash_2

          Table "public.t_hash_2"

 Column |         Type          | Modifiers

--------+-----------------------+-----------

 id     | integer               |

 name   | character varying(50) | not null

Distributed by: (name)

如果CTAS創(chuàng)建表改變分布鍵，加上distributed by即可。

testDB=# create table t_hash_3 as select * from t_hash distributed by (id);

SELECT 0

testDB=#

testDB=# \d t_hash_3

          Table "public.t_hash_3"

 Column |         Type          | Modifiers

--------+-----------------------+-----------

 id     | integer               |

 name   | character varying(50) |

Distributed by: (id)

testDB=#

testDB=#

testDB=# create table t_hash_4 (like t_hash) distributed by (id);

CREATE TABLE

testDB=#

testDB=# \d t_hash5

Did not find any relation named "t_hash5".

testDB=# \d t_hash_4

          Table "public.t_hash_4"

 Column |         Type          | Modifiers

--------+-----------------------+-----------

 id     | integer               |

 name   | character varying(50) | not null

Distributed by: (id)

CTAS時，randomly隨機(jī)分布鍵要特別注意，一定要加上distributed randomly，不然原表是hash分布鍵，CTAS新表則是隨機(jī)分布鍵。

testDB=# \d t_random

           Table "public.t_random"

 Column |          Type          | Modifiers

--------+------------------------+-----------

 id     | integer                |

 name   | character varying(100) |

Distributed randomly

testDB=#

testDB=# \d t_random_1

          Table "public.t_random_1"

 Column |          Type          | Modifiers

--------+------------------------+-----------

 id     | integer                |

 name   | character varying(100) |

Distributed by: (id)

testDB=# create table t_random_2 as select * from t_random distributed randomly;

SELECT 7

testDB=#

testDB=# \d t_random_2

          Table "public.t_random_2"

 Column |          Type          | Modifiers

--------+------------------------+-----------

 id     | integer                |

 name   | character varying(100) |

Distributed randomly

“Greenplum怎么創(chuàng)建表的分布鍵”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！