Greenplum Creating tables--Distribution keys _php Tutorials

Greenplum Creating tables--distribution keys

Greenplum is a distributed system, you need to specify a distribution key when creating a table (CREATEDBA permissions are required to create the table) to distribute the data evenly across segment. Choosing a distribution key is very important, and choosing the wrong one will result in data not being unique and, more serious, resulting in a sharp drop in SQL performance.

Greenplum has two distribution strategies:

1, hash distribution.

Greenplum uses hash distribution policy by default. This policy can choose one or more columns as the distribution key (distribution key, or DK). The distribution keys are hashed to confirm that the data is stored on the corresponding segment. The same distribution key value is hashed to the same segment. It is best to have a unique key or primary key on the table, which ensures that the data is not evenly divided across the segment. Grammar, distributed by.

If there is no primary key or unique key, the first column is selected as the distribution key by default. Add primary Key

2, random (randomly) distribution.

The data is randomly divided into segment, and the same record may be stored on different segment. Random distributions guarantee data averaging, but Greenplum does not have unique key constraint data across nodes, so data is not guaranteed to be unique. Based on uniqueness and performance considerations, hash distribution is recommended, and the performance section will be presented in detail in a separate document. Grammar, distributed randomly.

First, hash distribution key

Create TABLE, do not specify distribution column, distribution type, default create hash distribution table, the first column ID field as the distribution key.

testdb=# CREATE TABLE T_hash (ID int,name varchar ()) distributed by (ID); CREATE tabletestdb=#  testdb=# \d t_hash           Table "Public.t_hash" Column |         Type          | Modifiers--------+-----------------------+-----------ID     |  Name   | character varying (50) | Distributed by: (ID)

When you add a primary key, the primary key is promoted to the distribution key instead of the ID column.

testdb=# ALTER TABLE T_hash add primary key (name); NOTICE:  updating distribution policy to match new primary keynotice:  ALTER Table/add Primary KEY would create imp Licit index "T_hash_pkey" for Table "T_hash" ALTER tabletestdb=# \d t_hash           table "Public.t_hash" Column |         Type          | Modifiers--------+-----------------------+-----------ID     |  Name   | character Varying | not nullindexes:    ' T_hash_pkey ' PRIMARY KEY, Btree (name) distributed by: (name)

Verify that the hash distribution table can realize the uniqueness of the primary key or unique key value

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)

In addition, a unique key can still be created on the primary key column

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 | not Nullindexes:    "T_hash_pkey" PRIMARY KEY, Btree (name)    "u_id" UNIQUE, Btree (name) distributed by: (name)

However, a non-primary key column cannot create a unique index individually, and you must include a multiple distribution key column if you want to create it

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 in Dex uk_id on T_hash (id,name); CREATE indextestdb=# \d t_hash           Table "Public.t_hash" Column |         Type          | Modifiers--------+-----------------------+-----------ID     | integer               | name   | character Varying | not Nullindexes:    "T_hash_pkey" PRIMARY KEY, Btree (name)    "uk_id" UNIQUE, Btree (ID, name) distributed by: (name)

When the primary key is deleted, the original hash distribution key remains unchanged.

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)

When the distribution key is not a primary key or a unique key, let us verify that the same value of the distribution key falls on a segment conclusion.

In the following experiment, the Name column is the distribution key, and we insert the same name value, and we can see that 7 records fall in the number 2nd segment node.

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)

Two, the random distribution key

To create a random distribution table with distributed randomly keyword, it is unclear which column to use as the distribution key.

testdb=# CREATE TABLE t_random (ID int, name varchar ()) distributed randomly; CREATE tabletestdb= #testDB = #testDB =# \d t_random           Table "Public.t_random" Column |          Type          | Modifiers--------+------------------------+-----------ID     | integer                | name   | character varying (100) | Distributed randomly

Verify uniqueness of PRIMARY key/Unique key, you can see the random distribution table cannot create primary key and unique key

testdb=# ALTER TABLE T_random add primary key (Id,name); ERROR:  PRIMARY KEY and distributed randomly is incompatibletestdb= #testDB =# create unique index uk_r_id on t_random (i D); ERROR:  UNIQUE and distributed randomly is incompatibletestdb=#

It can be seen from the experiment that the uniqueness of the data cannot be achieved. Also, the data is inserted into the random distribution table, not polling inserts, there are 3 segment in the experiment, but in the number 1th inserted 3 records, after the 2nd segment node inserted 2 records, the data is inserted in the number No. 0 segment. How to achieve the average distribution of data in a random distribution table is unknown. This experiment also verifies the conclusion that the same value of the random distribution table is distributed in different segment.

testdb=# INSERT INTO t_random values (1, ' szlsd3 '); Insert 0 1testdb=# Select Gp_segment_id,count (*) from T_random GROUP by G p_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)

Third, CTAs inherit the original table distribution key

There are two types of CTAs syntax in Greenplum, which, by default, inherit the distribution key of the original table, regardless of the syntax. However, some special properties of the table, such as primary key, unique key, appendonly, Compresstype (compression), are not inherited.

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) test db= #testDB = #testDB =# CREATE TABLE t_hash_1 as SELECT * from T_hash; Notice:table doesn ' t has ' distributed by ' clause--Using column (s) named ' name ' as the Greenplum Database data distrib Ution key for this table. Hint:the ' distributed by ' clause determines the distribution of data. Make sure column (s) chosen is 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 the =# create table t_hash_2 (like T_hash); Notice:tableDoesn ' t has ' 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)

If CTAs CREATE table change distribution key, add 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 The =# 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 | not Nulldistributed by: (ID)

CTAs, randomly random distribution key to pay special attention, must add distributed randomly, otherwise the original table is hash distribution key, ctas new table is a random distribution key.

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

Reference:

"Greenplum Enterprise Application Practical"

"Greenplum4.2.2 Administrator's Guide"

The above is greenplum create table--distribution key _php tutorial content, more relevant content please pay attention to topic.alibabacloud.com (www.php.cn)!

