GreenPlum的那些事《三》——常用操作

我們前面已經說了，GPDB是一個列式資料庫，資料庫都有什麼特性呢？都能做什麼呢？熟悉MySQL，Oracle的同學可能就比較清楚關系型資料庫的管理，下面我們就來說下GPDB的一些管理和常用操作。

資料庫常用操作分為:建立資料庫、建立schema、建立資源池、建立角色、建立使用者、建立表。下面我們一一來說下GPDB這些常用操作，和關系型資料庫MYSQL有什麼不同呢？

1. 建立資料庫

   gp_sydb=# CREATE DATABASE GPTEST;

   CREATE DATABASE

删除資料庫

gp_sydb=# DROP DATABASE GPTEST;
DROP DATABASE
           

你也可以通過 createdb 建立資料庫

[gpadmin@mpp01 ~]$ createdb -h mpp01 -p 5432 GPTEST 
           

檢視建立的資料庫：

gp_sydb=# \l
                  List of databases
   Name    |  Owner  | Encoding |  Access privileges  
-----------+---------+----------+---------------------
 GPTEST    | gpadmin | UTF8     | 
 gp_sydb   | gpadmin | UTF8     | 
 postgres  | gpadmin | UTF8     | 
 template0 | gpadmin | UTF8     | =c/gpadmin          
                                : gpadmin=CTc/gpadmin
 template1 | gpadmin | UTF8     | =c/gpadmin          
                                : gpadmin=CTc/gpadmin
(5 rows)
           

1. 建立schema

GPTEST=# CREATE SCHEMA bdp;
CREATE SCHEMA
           

檢視schema

GPTEST=# \dn
       List of schemas
        Name        |  Owner  
--------------------+---------
 bdp                | gpadmin
 gp_toolkit         | gpadmin
 information_schema | gpadmin
 pg_aoseg           | gpadmin
 pg_bitmapindex     | gpadmin
 pg_catalog         | gpadmin
 pg_toast           | gpadmin
 public             | gpadmin
(8 rows)
           

檢視目前的schema

GPTEST=# SELECT current_schema();
 current_schema 
----------------
 public
(1 row)
           

可以看到目前schema是public，這是建立使用者的時候，我們沒有給它配置設定schema，那就是用預設的schema public。

把建立的schema賦給資料庫gptest

gptest=# \dn
       List of schemas
        Name        |  Owner  
--------------------+---------
 bdp                | gpadmin
 gp_toolkit         | gpadmin
 information_schema | gpadmin
 pg_aoseg           | gpadmin
 pg_bitmapindex     | gpadmin
 pg_catalog         | gpadmin
 pg_toast           | gpadmin
 public             | gpadmin
(8 rows)

gptest=# ALTER DATABASE GPTEST SET search_path TO bdp,                                                                                                    public, pg_catalog;
ALTER DATABASE
gptest=# SELECT current_schema();
 current_schema 
----------------
 bdp
(1 row)
           

可以看到賦了新的schema。

1. 建立角色

建立角色，如果有很多使用者對于一批表都有一樣的權限，這時候可以建立一個角色，把這些權限先賦給角色，然後把角色賦權給各個使用者。

gptest=# create role selectbdp;
CREATE ROLE
gptest=# \du
                                                                      List of roles
 Role name |                                                           Attributes                                                            | Member of 
-----------+---------------------------------------------------------------------------------------------------------------------------------+-----------
 bdp01     |                                                                                                                                 | {}
 gpadmin   | Superuser, Create role, Create DB, Ext gpfdist Table, Wri Ext gpfdist Table, Ext http Table, Ext hdfs Table, Wri Ext hdfs Table | {}
 selectbdp | Cannot login                                                                                                                    | {}

gptest=# 
           

可以看到角色已經建立成功。

賦權角色。

gptest=# grant selectbdp to gpadmin;     
GRANT ROLE           

角色其實就是一個使用者組。

1. 建立使用者：

gptest=# create user bdp01 WITH PASSWORD 'passwd123';
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE
gptest=#            

使用使用者登入：

psql -Ubdp01 -d gptest -w 'passwd123'           

發現報錯了：

[gpadmin@mpp01 gpseg-1]$ psql -Ubdp01 -d gptest -w 'passwd123'
Password for user bdp01: 
psql: FATAL:  no pg_hba.conf entry for host "[local]", user "bdp01", database "gptest", SSL off
           

GPDB登入需要在pg_hba.conf檔案中添加信任的使用者：

local    all         bdp01      trust           

添加後，使配置檔案生效，再次登入：

[gpadmin@mpp01 gpseg-1]$ psql -Ubdp01 -d gptest -w 'passwd123'
psql: warning: extra command-line argument "passwd123" ignored
psql (8.3.23)
Type "help" for help.

gptest=>            

登入成功！

1. 建立資源隊列

為什麼要建立資源隊列呢？我們知道一個資料庫肯定不是給一個使用者使用的，通常是有ETL使用者，進行資料加載，資料清洗。還有WEB端使用者進行查詢，還有個人使用者進行開發查詢使用等等。這麼多使用者，如果不進行資源隊列管控，那麼就會出現資源争搶現象，影響線上應用功能。比如一個新手開發人員寫了一個比較菜的查詢語句，占用了大量資源，此時資料庫的其他查詢使用者都需要排隊等待了。再比如加載資料是需要使用大量資源，那麼也會影響查詢性能，以上你會發現，你需要把不同類型的操作進行資源隔離，這就是為什麼要有資源隊列了。

檢視已有的資源隊列：

gptest=> SELECT rolname, rsqname FROM pg_roles, gp_toolkit.gp_resqueue_status
gptest-> WHERE pg_roles.rolresqueue=gp_toolkit.gp_resqueue_status.queueid;
  rolname  |  rsqname   
-----------+------------
 bdp01     | pg_default
 selectbdp | pg_default
 gpadmin   | pg_default
(3 rows)           

建立一個新的資源隊列：

gptest=#  create resource queue load_queue with (active_statements=3,MEMORY_LIMIT='1024MB',PRIORITY=LOW);                                                 CREATE QUEUE           

把建立的資源隊列賦權給剛才建立的使用者bdp01:

gptest=# ALTER USER bdp01  resource queue load_queue;                                              
ALTER ROLE           

再次查詢資源池配置設定情況：

gptest=# SELECT rolname, rsqname FROM pg_roles, gp_toolkit.gp_resqueue_status                                                                             WHERE pg_roles.rolresqueue=gp_toolkit.gp_resqueue_status.queueid;
  rolname  |  rsqname   
-----------+------------
 bdp01     | load_queue
 selectbdp | pg_default
 gpadmin   | pg_default
(3 rows)           

可以看到bpd01已經使用了新的資源池了。

• 建立表

簡單的建表語句，此處不在講解了，來說兩個地方，一個是分區，一個是分布鍵。

• 分區：

來看下分區的資料結構

分區是把一張大表按照适合的次元進行分割，通過表的繼承，規則，限制實作。

并不是每個表都适合分區，隻有很大的表才适合分區，應為分區多了會增加表的中繼資料資訊，特别是多級分區。如果一個表被按照日和城市劃分并且有1000個日以及1000個城市，那麼分區的總數就是一百萬。列存表會把每一列存在一個實體表中，是以如果這個表有100個列，系統就需要為該表管理一億個檔案。一方面Linux的iNode可能不會有這麼大，就會出現檔案查詢報錯等，分區過多了，對于資料庫停止和恢複也會造成很大影響的。是以建立分區表的時候，對分區進行自動的建立和删除，以保障合适的資料周期很重要。

• 分布鍵 在MPP架構中，一個查詢是需要從所有segment擷取資料然後在master彙總得到結果的。那麼這樣，影響查詢性能的就有兩個問題，查詢最慢的segment和網絡帶寬。分布鍵就是為了解決第一個問題，讓資料盡可能均勻的分布在每個segment上，進而避免資料傾斜導緻的查詢效率問題。第二個網絡問題，MPP資料叢集搭建要求叢集内部為萬兆網連接配接，并且機器配置一樣，進而避免影響。

分布鍵一般選擇主鍵，比如手機号等。

盡量選擇經常需要 JOIN 的列，當關聯鍵和分布鍵均一緻時，可以在 Segment 中完成 JOIN，不需要重分布或者廣播小表。

以上就是GPDB簡單的管理流程，後面我們會再介紹更深入的查詢優化和管理知識。