如何調整分布列？

在數據倉庫類型的數據庫中，大表的分布列選擇對于數據庫和語句查詢性能都有至關重要的影響。

如果表的分布列選擇不當，在數據導入后有可能出現數據分布傾斜，進而導致某些磁盤的使用明顯高于其他磁盤，極端情況下會導致集群只讀。

對于Hash分表策略，存在數據傾斜情況下，查詢時出現部分DN的I/O短板，從而影響整體查詢性能。 在對已經創建的表，該如何進行分布列的調整，也是我們經常思考的課題。

采用Hash分表策略之后需對表的數據進行數據傾斜性檢查，以確保數據在各個DN上是均勻分布的。一般來說，不同DN的數據量相差5%以上即可視為傾斜，如果相差10%以上就必須要調整分布列。

針對分布不均勻的表，盡可能通過調整分布列，以減少數據傾斜，避免帶來潛在的數據庫性能問題。

選擇合適的分布列

Hash分布表的分布列選取至關重要，需要滿足以下基本原則：

• 列值應比較離散，以便數據能夠均勻分布到各個DN。例如，考慮選擇表的主鍵為分布列，如在人員信息表中選擇身份證號碼為分布列；
• 在滿足第一條原則的情況下盡量不要選取存在常量filter的列；
• 在滿足前兩條原則的情況，考慮選擇查詢中的連接條件為分布列，以便Join任務能夠下推到DN中執行，且減少DN之間的通信數據量；
• 支持多分布列特性，可以更好地滿足數據分布的均勻性要求。

如何調整

通過 select version(); 語句查詢當前數據庫版本號，版本號不同，調整的方式不同：

• 8.0.x及以前版本，通過重建表來調整 ：

1.通過Data Studio或者Linux下使用gsql訪問數據庫。

2.創建新表。

                    
說明
                    
以下步驟語句中，table1為原表名，table1_new為新表名，column1和column2為分布列名稱。
                    
                  
                  

CREATE TABLE IF NOT EXISTS table1_new  
( LIKE table1 INCLUDING ALL EXCLUDING DISTRIBUTION)  
DISTRIBUTE BY  
HASH (column1, column2);

3.遷移數據到新表。

START TRANSACTION; 
LOCK TABLE table1 IN ACCESS EXCLUSIVE MODE; 
INSERT INTO table1_new SELECT * FROM table1; 
COMMIT;

4.查看表數據是否遷移成功，刪除原表。

SELECT COUNT(*) FROM table1_new; 
DROP TABLE table1;

5.替換原表。

ALTER TABLE table1_new RENAME TO table1;

• 8.1.0及以后版本后 ，通過ALTER TABLE語法進行調整，以下為示例。

1.查詢當前表定義，回顯發現該表分布列為c_last_name。

select pg_get_tabledef('customer_t1');

2.嘗試執行更新分布列中的數據提示錯誤信息。

update customer_t1 set c_last_name = 'Jimy' where c_customer_sk = 6885;

3.將該表的分布列修改為不會更新的列，例如c_customer_sk。

alter table customer_t1 DISTRIBUTE BY hash (c_customer_sk); 

4.重新執行更新舊的分布列的數據。更新成功。

update customer_t1 set c_last_name = 'Jimy' where c_customer_sk = 6885;

如何查看和設置數據庫的字符集編碼格式

查看數據庫字符集編碼

使用server_encoding參數查看當前數據庫的字符集編碼。例如，查看到數據庫music的字符集編碼為UTF8。

music=> show server_encoding; 
 server_encoding 
----------------- 
 UTF8 
(1 row)

設置數據庫的字符集編碼

                    
說明
                    
DWS不支持修改已創建數據庫的字符編碼格式。
                    
                  
                  

如果需要指定數據庫的字符集編碼格式，可按照下面的CREATE DATABASE語法格式，使用template0新建一個數據庫。為了適應全球化的需求，使數據庫編碼能夠存儲與表示絕大多數的字符，建議創建Database的時候使用UTF8編碼。

CREATE DATABASE語法格式

CREATE DATABASE database_name 
    [ [ WITH ] { [ OWNER [=] user_name ] | 
               [ TEMPLATE [=] template ] | 
               [ ENCODING [=] encoding ] | 
               [ LC_COLLATE [=] lc_collate ] | 
               [ LC_CTYPE [=] lc_ctype ] | 
               [ DBCOMPATIBILITY [=] compatibility_type ] | 
               [ CONNECTION LIMIT [=] connlimit ]}[...] ];

• TEMPLATE [ = ] template

模板名。即從哪個模板創建新數據庫。DWS采用從模板數據庫復制的方式來創建新的數據庫。初始時，DWS包含兩個模板數據庫template0、template1，以及一個默認的用戶數據庫postgres。

取值范圍：已有數據庫的名稱。不指定時，系統默認拷貝template1。另外，不支持指定為postgres數據庫。

                    
注意
                    
目前不支持模板庫中含有SEQUENCE對象。如果模板庫中有SEQUENCE，則會創建數據庫失敗。
                    
                  
                  

• ENCODING [ = ] encoding

指定數據庫使用的字符編碼，可以是字符串（如'SQL_ASCII'）、整數編號。

不指定時，默認使用模版數據庫的編碼。模板數據庫template0和template1的編碼默認與操作系統環境相關。template1不允許修改字符編碼，因此若要變更編碼，請使用template0創建數據庫。

常用取值：GBK、UTF8、Latin1。

                    
注意
                    
指定新的數據庫字符集編碼必須與所選擇的本地環境中（LC_COLLATE和LC_CTYPE）的設置兼容。
                    
                  
                  

當指定的字符編碼集為GBK時，部分中文生僻字無法直接作為對象名。這是因為GBK第二個字節的編碼范圍在0x40-0x7E之間時，字節編碼與ASCII字符@A-Z[]^ `a-z{|}重疊。其中@[]^ '{|}是數據庫中的操作符，直接作為對象名時，會語法報錯。例如“侤”字，GBK16進制編碼為0x8240，第二個字節為0x40，與ASCII“@”符號編碼相同，因此無法直接作為對象名使用。如果確實要使用，可以在創建和訪問對象時，通過增加雙引號來規避這個問題。

示例

創建一個UTF8編碼的數據庫music（本地環境的編碼格式必須也為UTF8）。

CREATE DATABASE music ENCODING 'UTF8' template = template0;

如何處理建表時date類型字段自動轉換為timestamp類型的問題？

創建數據庫時，可通過DBCOMPATIBILITY參數指定兼容的數據庫的類型，DBCOMPATIBILITY取值范圍：ORA、TD、MySQL。分別表示兼容Oracle、Teradata和MySQL數據庫。如果創建數據庫時不指定該參數，則默認為ORA，在ORA兼容模式下，date類型會自動轉換為timestamp(0)。

只有在MySQL兼容模式下才支持date類型，為解決以上問題，需要將兼容模式修改為MySQL，兼容模式僅支持在創建數據庫時設置。DWS從8.1.1集群版本開始支持MySQL兼容模式類型， 可參考如下示例進行操作：

gaussdb=> CREATE DATABASE mydatabase DBCOMPATIBILITY='mysql'; 
CREATE DATABASE 
gaussdb=> \c mydatabase 
Non-SSL connection (SSL connection is recommended when requiring high-security) 
You are now connected to database "mydatabase" as user "dbadmin". 
mydatabase=> create table t1(c1 int, c2 date); 
NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using round-robin as the distribution mode by default. 
HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. 
CREATE TABLE

是否需要定時對常用的表做VACUUM FULL和ANALYZE操作？

需要。

對于頻繁增、刪、改的表，需要定期執行VACUUM FULL和ANALYZE，該操作可回收已更新或已刪除的數據所占據的磁盤空間，防止因數據膨脹和統計信息不準造成性能下降。

• 一般情況下，對表執行完大量增、改操作后，建議進行ANALYZE。
• 對表執行過刪除操作后，建議進行VACUUM，一般不建議日常使用VACUUM FULL選項，但是可以在特殊情況下使用。例如，用戶刪除了一個表的大部分行之后，希望從物理上縮小該表以減少磁盤空間占用。VACUUM和VACUUM FULL具體的差異可以參考下方“VACUUM和VACUUM FULL”。

語法格式

指定某張表進行分析。

ANALYZE table_name;

對數據庫中的所有表（非外部表）進行分析。

ANALYZE;

指定某張表進行VACUUM。

VACUUM table_name;

指定某張表進行VACUUM FULL。

VACUUM FULL table_name;

更多語法參見《開發指南》的有關“VACUUM”和“ANALYZE | ANALYSE”章節。

                    
說明
                    
l? 如果執行VACUUM FULL命令后所占用物理空間無變化（未減少），請確認是否有其他活躍事務（刪除數據事務開始之前開始的事務，并在VACUUM FULL執行前未結束）存在，如果有等其他活躍事務退出進行重試。
l? 8.1.3及以上版本中Vacuum/Vacuum Full可在管控面操作調用，詳情可參見《數據倉庫服務用戶指南》中“智能運維”章節。
                    
                  
                  

VACUUM和VACUUM FULL

在DWS中，VACUUM的本質就是一個“吸塵器”，用于吸收“塵埃”。而塵埃其實就是舊數據，如果這些數據沒有及時清理，那么將會導致數據庫空間膨脹，性能下降，更嚴重的情況會導致宕機。

VACUUM的作用：

• 空間膨脹問題：清除廢舊元組以及相應的索引。包括提交的事務delete的元組（以及索引）、update的舊版本（以及索引），回滾的事務insert的元組（以及索引）、update的新版本（以及索引）、copy導入的元組（以及索引）。
• FREEZE：防止因事務ID回卷問題（Transaction ID wraparound）而導致的宕機，將小于OldestXmin的事務號轉化為freeze xid，更新表的relfrozenxid，更新庫的relfrozenxid，truncate clog。
• 更新統計信息：VACUUM ANALYZE時，會更新統計信息，使得優化器能夠選擇更好的方案執行SQL語句。

VACUUM命令存在兩種形式，VACUUM和VACUUM FULL，目前VACUUM對行存表有作用，對列存表無顯著的作用，列存表只能依靠VACUUM FULL釋放空間。具體區別見下表：

VACUUM和VACUUM FULL

差異項	VACUUM	VACUUM FULL
空間清理	如果刪除的記錄位于表的末端，其所占用的空間將會被物理釋放并歸還操作系統。而如果不是末端數據，會將表中或索引中dead tuple（死亡元組）所占用的空間置為可用狀態，從而復用這些空間。	不論被清理的數據處于何處，這些數據所占用的空間都將被物理釋放并歸還于操作系統。當再有數據插入后，分配新的磁盤頁面使用。
鎖類型	共享鎖，可以與其他操作并行。	排他鎖，執行期間基于該表的操作全部掛起。
物理空間	不會釋放。	會釋放。
事務ID	不回收。	回收。
執行開銷	開銷較小，可以定期執行。	開銷很大，建議確認數據庫所占磁盤頁面空間接近臨界值再執行操作，且最好選擇數據量操作較少的時段完成。
執行效果	執行后基于該表的操作效率有一定提升。	執行完后，基于該表的操作效率大大提升。

DWS數據庫設置主鍵后還需要設置分布鍵嗎？

僅設置主鍵即可，默認會選擇主鍵的第一列作為分布鍵。如果兩個同時設置，主鍵必須包含分布鍵。

DWS是否兼容PostgreSQL的存儲過程？

兼容。

DWS兼容PostgreSQL的存儲過程，請參見《開發指南》的“存儲過程”章節。

如何理解分區表、數據分區和分區鍵？

分區表：分區表是把邏輯上的一張表根據某種方案分成幾張物理塊進行存儲。這張邏輯上的表稱之為分區表，物理塊稱之為分區。分區表是一張邏輯表，不存儲數據，數據實際是存儲在分區上的。

數據分區：在DWS分布式系統中，數據分區是在一個節點內部按照用戶指定的策略對數據做進一步的水平分表，將表按照指定范圍劃分為多個數據互不重疊的部分（Partition）。

分區鍵：分區鍵是一個或多個表列的有序集合。表分區鍵列中的值用來確定每個表行所屬的數據分區。

分區表中指定了maxvalue分區時如何再添加新的分區

問題背景

用戶在最初建表時指定了Maxvalue分區后，數據被寫入該分區，如下圖中的40001，當用戶需要新建一個40000~50000的分區時報錯：upper boundary of adding partition MUST overtop last existing partition.

根據報錯提示，不能新增分區的原因是：分區的上邊界必須大于最后一個現有分區的上邊界。即若需要新增40000~50000的分區，需調整分區表分區中的上邊界，若直接刪除Maxvalue分區再新建分區可能會導致原有p8分區數據被刪除。

解決方法

業務數據在p8分區的分布應該是40000~某一個值之間，這個值到MAXVALUE應該是沒有數據的。那么假設這個值是60000。

1.使用split子句分割p8分區為p81分區范圍為[40000,60000）和p82分區范圍為[60000,MAXVALUE）。

ALTER TABLE customer_address SPLIT PARTITION P8 AT (60000) INTO 
 ( 
         PARTITION P81, 
         PARTITION P82 
  );

2.使用drop命令刪除分區p82（此時p82分區沒有數據）。

ALTER TABLE customer_address drop partition p82;

3.使用split命令將p81分割成為p8和p9， [40000,50000)的分區p8就創建成功了。

ALTER TABLE customer_address SPLIT PARTITION P81 AT (50000) INTO 
 ( 
         PARTITION P8, 
         PARTITION P9 
  );

4.之后操作中如果需要再新增分區，使用ALTER TABLE的ADD子句即可實現。例如新增分區P10：

ALTER TABLE customer_address ADD PARTITION p10 VALUES LESS THAN (70000);

如何導出某張表結構？

建議使用Data Studio圖形化客戶端進行表數據導出，支持以下幾種常見場景：

• 指定某張表數據的導出。
• 某個schema下的所有表數據的導出。
• 某個數據庫下的所有表數據的導出。

具體操作請參見《工具指南》的“導出表數據”章節。

如何導出數據庫中所有表和視圖？

您可以使用pg_tables視圖和pg_views視圖查詢數據庫中所有表信息和視圖。執行示例如下：

SELECT * FROM pg_tables; 
SELECT * FROM pg_views;

返回的字段請參見《數據倉庫服務開發指南》的“PG_TABLES”和“PG_VIEWS”章節。

是否有高效的刪除表數據的方法？

有。

刪除大批量的日志數據時，使用delete語法需要花費更大的時間，此時可以通過truncate語法進行大批量刪除操作，它的刪除速度比delete快得多。

詳情請參見《數據倉庫服務開發指南》的“TRUNCATE”章節。

功能描述

清理表數據，TRUNCATE快速地從表中刪除所有行。

它和在目標表上進行無條件的DELETE有同樣的效果，但由于TRUNCATE不做表掃描，因而快得多。在大表上操作效果更明顯。

功能特點

• TRUNCATE TABLE在功能上與不帶WHERE子句DELETE語句相同：二者均刪除表中的全部行。
• TRUNCATE TABLE比DELETE速度快且使用系統和事務日志資源少：

?DELETE語句每次刪除一行，并在事務日志中為所刪除每行記錄一項。

?TRUNCATE TABLE通過釋放存儲表數據所用數據頁來刪除數據，并且只在事務日志中記錄頁的釋放。

• TRUNCATE，DELETE，DROP三者的差異如下：

?TRUNCATE TABLE，刪除內容，釋放空間，但不刪除定義。

?DELETE TABLE，刪除內容，不刪除定義，不釋放空間。

?DROP TABLE，刪除內容和定義，釋放空間。

示例

--創建表。CREATE TABLE tpcds.reason_t1 AS TABLE tpcds.reason; 
 
--清空表tpcds.reason_t1。TRUNCATE TABLE tpcds.reason_t1; 
 
--刪除表。DROP TABLE tpcds.reason_t1;
--創建分區表。 
CREATE TABLE tpcds.reason_p 
( 
  r_reason_sk integer, 
  r_reason_id character(16), 
  r_reason_desc character(100) 
)PARTITION BY RANGE (r_reason_sk) 
( 
  partition p_05_before values less than (05), 
  partition p_15 values less than (15), 
  partition p_25 values less than (25), 
  partition p_35 values less than (35), 
  partition p_45_after values less than (MAXVALUE) 
); 
 
--插入數據。 
INSERT INTO tpcds.reason_p SELECT * FROM tpcds.reason; 
 
--清空分區p_05_before。 
ALTER TABLE tpcds.reason_p TRUNCATE PARTITION p_05_before; 
 
--清空分區p_15。 
ALTER TABLE tpcds.reason_p TRUNCATE PARTITION for (13); 
 
--清空分區表。 
TRUNCATE TABLE tpcds.reason_p; 
 
--刪除表。 
DROP TABLE tpcds.reason_p;

如何查看外部表信息？

如果需要查詢OBS、GDS等外表信息（如OBS路徑），可以執行以下語句查詢。

select * from pg_get_tabledef('外表名稱')

例如，表名為traffic_data.GCJL_OBS，查詢如下：

select * from pg_get_tabledef('traffic_data.GCJL_OBS');

如果建表時沒有指定分布列，數據會怎么存儲？

                    
說明
                    
8.1.2及以上集群版本，可通過GUC參數default_distribution_mode來查詢和設置表的默認分布方式。
                    
                  
                  

如果建表時沒有指定分布列，數據會以下幾種場景來存儲：

• 場景一

若建表時包含主鍵/唯一約束，則選取HASH分布，分布列為主鍵/唯一約束對應的列。

CREATE TABLE warehouse1 
( 
    W_WAREHOUSE_SK            INTEGER            PRIMARY KEY, 
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL, 
    W_WAREHOUSE_NAME          VARCHAR(20)  
); 
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "warehouse1_pkey" for table "warehouse1" 
CREATE TABLE 
 
SELECT getdistributekey('warehouse1'); 
 getdistributekey 
------------------ 
 w_warehouse_sk 
(1 row)

• 場景二

若建表時不包含主鍵/唯一約束，但存在數據類型支持作分布列的列，則選取HASH分布，分布列為第一個數據類型支持作分布列的列。

CREATE TABLE warehouse2 
( 
    W_WAREHOUSE_SK            INTEGER                       , 
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL, 
    W_WAREHOUSE_NAME          VARCHAR(20)  
); 
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'w_warehouse_sk' as the distribution column by default. 
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. 
CREATE TABLE 
 
SELECT getdistributekey('warehouse2'); 
 getdistributekey 
------------------ 
 w_warehouse_sk 
(1 row)

• 場景三

若建表時不包含主鍵/唯一約束，也不存在數據類型支持作分布列的列，選取ROUNDROBIN分布。

CREATE TABLE warehouse3 
( 
    W_WAREHOUSE_ID            CHAR(16)              NOT NULL, 
    W_WAREHOUSE_NAME          VARCHAR(20)  
); 
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'w_warehouse_id' as the distribution column by default. 
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. 
CREATE TABLE 
 
SELECT getdistributekey('warehouse3'); 
 getdistributekey 
------------------ 
 w_warehouse_id 
(1 row)

如何將聯結查詢的null結果替換成0？

在執行outer join（left join、right join、full join）聯結查詢時，outer join在匹配失敗的情況下結果集會補空產生大量NULL值， 可以在聯結查詢時將這部分null值替換為0。

可使用coalesce函數，它的作用是返回參數列表中第一個非NULL的參數值。例如：

SELECT coalesce(NULL,'hello'); 
 coalesce 
---------- 
 hello 
(1 row)

有表course1和表course2，使用left join對兩表進行聯結查詢：

SELECT * FROM course1; 
  stu_id  |  stu_name  |     cour_name 
----------+------------+-------------------- 
 20110103 | ALLEN      | Math 
 20110102 | JACK       | Programming Design 
 20110101 | MAX        | Science 
(3 rows) 
 
SELECT * FROM course2; 
 cour_id |     cour_name      | teacher_name 
---------+--------------------+-------------- 
    1002 | Programming Design | Mark 
    1001 | Science            | Anne 
(2 rows) 
 
SELECT course1.stu_name,course2.cour_id,course2.cour_name,course2.teacher_name FROM course1 LEFT JOIN course2 ON course1.cour_name = course2.cour_name ORDER BY 1; 
  stu_name  | cour_id |     cour_name      | teacher_name 
------------+---------+--------------------+-------------- 
 ALLEN      |         |                    | 
 JACK       |    1002 | Programming Design | Mark 
 MAX        |    1001 | Science            | Anne 
(3 rows) 

使用coalesce函數將查詢結果中的空值替換為0或其他非0值：

SELECT course1.stu_name, 
 coalesce(course2.cour_id,0) AS cour_id, 
 coalesce(course2.cour_name,'NA') AS cour_name, 
 coalesce(course2.teacher_name,'NA') AS teacher_name 
 FROM course1 
 LEFT JOIN course2 ON course1.cour_name = course2.cour_name 
 ORDER BY 1; 
  stu_name  | cour_id |     cour_name      | teacher_name 
------------+---------+--------------------+-------------- 
 ALLEN      |       0 | NA                 | NA 
 JACK       |    1002 | Programming Design | Mark 
 MAX        |    1001 | Science            | Anne 
(3 rows) 

如何查看表是行存還是列存？

表的存儲方式由建表語句中的ORIENTATION參數控制，row表示行存，column表示列存。

查看已創建的表是行存還是列存，可通過表定義函數PG_GET_TABLEDEF查詢。

如下orientation=column表示為列存表。

目前暫不支持通過ALTER

TABLE語句修改ORIENTATION參數，即行存表和列存表無法直接進行轉換。

SELECT * FROM PG_GET_TABLEDEF('customer_t1'); 
                                  pg_get_tabledef               
----------------------------------------------------------------------------------- 
 SET search_path = tpchobs;                                                       + 
 CREATE  TABLE customer_t1 (                                                      + 
         c_customer_sk integer,                                                   + 
         c_customer_id character(5),                                              + 
         c_first_name character(6),                                               + 
         c_last_name character(8)                                                 + 
 )                                                                                + 
 WITH (orientation=column, compression=middle, colversion=2.0, enable_delta=false)+ 
 DISTRIBUTE BY HASH(c_last_name)                                                  + 
 TO GROUP group_version1; 
(1 row)

如何使用自定義函數改寫CRC32函數

DWS目前未內置CRC32函數，但如果需要實現MySQL中的CRC32()函數功能，用戶可使用DWS的自定義函數語句對其進行改寫。

• 函數：CRC32(expr)
• 描述：用于計算循環冗余值。入參expr為字符串。如果參數為NULL，則返回NULL；否則，在計算冗余后返回32位無符號值。

DWS的自定義函數語句改寫CRC32函數示例：

CREATE OR REPLACE FUNCTION crc32(text_string text) RETURNS bigint AS $$ 
DECLARE 
    val bigint; 
    i int; 
    j int; 
    byte_length int; 
    binary_string bytea; 
BEGIN 
    IF text_string is null THEN 
        RETURN null; 
    ELSIF text_string = '' THEN 
        RETURN 0; 
    END IF; 
 
    i = 0; 
    val = 4294967295; 
    byte_length = bit_length(text_string) / 8; 
    binary_string = decode(replace(text_string, E'\\', E'\\\\'), 'escape'); 
    LOOP 
        val = (val # get_byte(binary_string, i))::bigint; 
        i = i + 1; 
        j = 0; 
        LOOP 
            val = ((val >> 1) # (3988292384 * (val & 1)))::bigint; 
            j = j + 1; 
            IF j >= 8 THEN 
                EXIT; 
            END IF; 
        END LOOP; 
        IF i >= byte_length THEN 
            EXIT; 
        END IF; 
    END LOOP; 
    RETURN (val # 4294967295); 
END 
$$ IMMUTABLE LANGUAGE plpgsql;
驗證改寫后的結果：
select crc32(null),crc32(''),crc32('1'); 
 crc32 | crc32 |   crc32 
-------+-------+------------ 
       |     0 | 2212294583 
(1 row)