Greenplum建立表--分布鍵_PHP教程

Greenplum建立表--分布鍵

Greenplum是分布式系統，建立表時需要指定分布鍵（建立表需要CREATEDBA許可權），目的在於將資料平均分布到各個segment。選擇分布鍵非常重要，選擇錯了會導致資料不唯一，更嚴重的是會造成SQL效能急劇下降。

Greenplum有兩種分布策略：

1、hash分布。

Greenplum預設使用hash分布策略。該策略可選一個或者多個列作為分布鍵（distribution key，簡稱DK）。分布鍵做hash演算法來確認資料存放到對應的segment上。相同分布索引值會hash到相同的segment上。表上最好有唯一鍵或者主鍵，這樣能保證資料均衡分不到各個segment上。文法，distributed by。

如果沒有主鍵或者唯一鍵，預設選擇第一列作為分布鍵。增加主鍵

2、隨機（randomly）分布。

資料會被隨機分不到segment上，相同記錄可能會存放在不同的segment上。隨機分布可以保證資料平均，但是Greenplum沒有跨節點的唯一鍵約束資料，所以無法保證資料唯一。基於唯一性和效能考慮，推薦使用hash分布，效能部分會另開一篇文檔詳細介紹。文法，distributed randomly。

一、hash分布鍵

建立表，未指定分布列、分布類型，預設建立hash分布表，把第一列ID欄位作為了分布鍵。

testDB=# create table t_hash(id int,name varchar(50)) distributed by (id);CREATE TABLEtestDB=#  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 keyNOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "t_hash_pkey" for table "t_hash" ALTER TABLEtestDB=# \d t_hash           Table "public.t_hash" Column |         Type          | Modifiers --------+-----------------------+----------- id     | integer               |  name   | character varying(50) | not nullIndexes:    "t_hash_pkey" PRIMARY KEY, btree (name)Distributed by: (name)

驗證hash分布表可實現主鍵或者唯一索引值的唯一性

testDB=# insert into t_hash values(1,'szlsd1');INSERT 0 1testDB=#testDB=# insert into t_hash values(2,'szlsd1');ERROR:  duplicate key violates unique constraint "t_hash_pkey"(seg2 gp-s3:40000 pid=3855)

另外，主鍵列上依然能夠建立唯一鍵

testDB=# create unique index u_id on t_hash(name);CREATE INDEXtestDB=#testDB=#testDB=# \d t_hash           Table "public.t_hash" Column |         Type          | Modifiers--------+-----------------------+----------- id     | integer               | name   | character varying(50) | not nullIndexes:    "t_hash_pkey" PRIMARY KEY, btree (name)    "u_id" UNIQUE, btree (name)Distributed by: (name)

但是，非主鍵列無法單獨建立唯一索引，想建立的話必須包含多有分布鍵列

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 INDEXtestDB=# \d t_hash           Table "public.t_hash" Column |         Type          | Modifiers--------+-----------------------+----------- id     | integer               | name   | character varying(50) | not nullIndexes:    "t_hash_pkey" PRIMARY KEY, btree (name)    "uk_id" UNIQUE, btree (id, name)Distributed by: (name)

刪除主鍵後，原hash分布鍵依然不變。

testDB=# alter table t_hash drop constraint t_hash_pkey;ALTER TABLEtestDB=# \d t_hash           Table "public.t_hash" Column |         Type          | Modifiers--------+-----------------------+----------- id     | integer               | name   | character varying(50) | not nullDistributed by: (name)

當分布鍵不是主鍵或者唯一鍵時，我們來驗證分布鍵的相同值落在一個segment的結論。

下面的實驗，name列是分布鍵，我們插入相同的name值，可以看到7條記錄都落在了2號segment節點中。

testDB=#  insert into t_hash values(1,'szlsd');INSERT 0 1testDB=#  insert into t_hash values(2,'szlsd');INSERT 0 1testDB=#  insert into t_hash values(3,'szlsd');INSERT 0 1testDB=#  insert into t_hash values(4,'szlsd');INSERT 0 1testDB=#  insert into t_hash values(5,'szlsd');INSERT 0 1testDB=#  insert into t_hash values(6,'szlsd');INSERT 0 1testDB=#testDB=#testDB=# select gp_segment_id,count(*) from t_hash group by gp_segment_id;  gp_segment_id | count---------------+-------             2 |     7(1 row)

二、隨機分布鍵

建立隨機分布表需加distributed randomly關鍵字，具體使用哪列作為分布鍵不得而知。

testDB=# create table t_random(id int ,name varchar(100)) distributed randomly;CREATE TABLEtestDB=#testDB=#testDB=# \d t_random           Table "public.t_random" Column |          Type          | Modifiers--------+------------------------+----------- id     | integer                | name   | character varying(100) |Distributed randomly

驗證主鍵/唯一鍵的唯一性，可以看到隨機分布表不能建立主鍵和唯一鍵

testDB=# alter table t_random add primary key (id,name);ERROR:  PRIMARY KEY and DISTRIBUTED RANDOMLY are incompatibletestDB=#testDB=# create unique index uk_r_id on t_random(id);ERROR:  UNIQUE and DISTRIBUTED RANDOMLY are incompatibletestDB=#

從實驗中可以看出無法實現資料的唯一性。並且，資料插入隨機分布表，並不是輪詢插入，實驗中共有3個segment，但是在1號插入3條記錄，在2號segment節點插入2條記錄後，才在0號segment中插入資料。隨機分布表如何?資料平均分配不得而知。這個實驗也驗證了隨機分布表的相同值分布在不同segment的結論。

testDB=# insert into t_random values(1,'szlsd3');INSERT 0 1testDB=# 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 1testDB=# 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 1testDB=# 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 1testDB=# 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 1testDB=# 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 1testDB=# 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文法，無論哪種文法，都預設繼承原表的分布鍵。但是，不會繼承表的一些特殊屬性，如主鍵、唯一鍵、APPENDONLY、COMPRESSTYPE（壓縮）等。

testDB=# \d t_hash;           Table "public.t_hash" Column |         Type          | Modifiers--------+-----------------------+----------- id     | integer               | name   | character varying(50) | not nullIndexes:    "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 0testDB=# \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 tableCREATE TABLEtestDB=# \d t_hash_2          Table "public.t_hash_2" Column |         Type          | Modifiers--------+-----------------------+----------- id     | integer               | name   | character varying(50) | not nullDistributed by: (name)

如果CTAS建立表改變分布鍵，加上distributed by即可。

testDB=# create table t_hash_3 as select * from t_hash distributed by (id);SELECT 0testDB=#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 TABLEtestDB=#testDB=# \d t_hash4Did not find any relation named "t_hash4".testDB=# \d t_hash_4          Table "public.t_hash_4" Column |         Type          | Modifiers--------+-----------------------+----------- id     | integer               | name   | character varying(50) | not nullDistributed by: (id)

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

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 7testDB=#testDB=# \d t_random_2          Table "public.t_random_2" Column |          Type          | Modifiers--------+------------------------+----------- id     | integer                | name   | character varying(100) |Distributed randomly

參考：

《Greenplum公司專屬應用程式實戰》

《Greenplum4.2.2管理員指南》

以上就是Greenplum建立表--分布鍵_PHP教程的內容，更多相關內容請關注topic.alibabacloud.com（www.php.cn）！

