ApsaraDBForGreenplum試用指南

竇賢明發表於2016-11-21

在ApsaraDB獲取到一個GP例項後，會得到以下資訊：

  • 連線地址

    分為公網和私網，暫時不考慮VPC。私網地址只能通過阿里雲ECS訪問，公網地址則可以通過網際網路上任一主機訪問。例如：

    公網地址：mygpdbpub.gpdb.rds.aliyuncs.com:3568 
私網地址：mygpdb.gpdb.rds.aliyuncs.com:3432

  • 賬號密碼

    賬號：mygpdb    
密碼：mygpdb

有了這些資訊後，就可以連線到GP例項上進行相關的操作。

連線

GP目前基於的是PostgreSQL 8.3，完整支援PG 8.3的訊息協議。因此，在各種操作下，與PG幾無二致。

得益於PG 9.4訊息協議的良好向後相容性，所有基於PG 8.3以及之後的連線工具，大部分可以直接用於連線到GP，包括libpq、JDBC、ODBC、psycopg2、pgadmin III等。

GP官網提供了一個安裝包，包含了JDBC、ODBC和libpq，方便安裝和使用。請參考官方文件

psql && pgAdmin III

連線PG和GP，最為常用的莫過於psql和pgAdmin III了。psql是PG、GP安裝後自帶的類shell終端，可以執行所提供的諸多命令和SQL。以psql為例如：

$ ~/workspace/pgsql/bin/psql  -h mygpdbpub.gpdb.rds.aliyuncs.com -p 3568  -d postgres -U mygpdb
Password for user mygpdb: 
psql (9.4.4, server 8.3devel)
Type "help" for help.

postgres=> select version();
                                            version                                            
-----------------------------------------------------------------------------------------------
 PostgreSQL 8.3devel (Greenplum Database 4.3.99.00 build dev) compiled on May 10 2016 01:19:10
(1 row)

postgres=>

其中，“-h”指定主機地址，“-p”指定埠號，“-d“指定資料庫（預設的資料庫是postgres），“-U”指定連線的使用者。更多選項，通過“psql –help”可以看到。

在psql中，可以執行”?”檢視更多psql中支援的命令。

pgAdmin III是圖形客戶端，相對簡單，請參考或中文版。

JDBC

PostgreSQL的官方JDBC可以在這裡下載到，下載之後加入到環境變數中即可。

程式碼案例：

import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import java.sql.Statement;  
  
public class gp_conn {  
  
    public static void main(String[] args) {  
        try {  
            Class.forName("org.postgresql.Driver");  
            Connection db = DriverManager.getConnection("jdbc:postgresql://mygpdbpub.gpdb.rds.aliyuncs.com:3568/postgres","mygpdb","mygpdb");  

            Statement st = db.createStatement();  
            ResultSet rs = st.executeQuery("select * from gp_segment_configuration;");  
            while (rs.next()) {  
                System.out.print(rs.getString(1));  
                System.out.print("    |    ");  
                System.out.print(rs.getString(2));  
                System.out.print("    |    ");  
                System.out.print(rs.getString(3));  
                System.out.print("    |    ");  
                System.out.print(rs.getString(4));  
                System.out.print("    |    ");  
                System.out.print(rs.getString(5));  
                System.out.print("    |    ");  
                System.out.print(rs.getString(6));  
                System.out.print("    |    ");  
                System.out.print(rs.getString(7));  
                System.out.print("    |    ");  
                System.out.print(rs.getString(8));  
                System.out.print("    |    ");  
                System.out.print(rs.getString(9));  
                System.out.print("    |    ");  
                System.out.print(rs.getString(10));  
                System.out.print("    |    ");  
                System.out.println(rs.getString(11));  
            }  
            rs.close();  
            st.close();  
        } catch (ClassNotFoundException e) {  
            e.printStackTrace();  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }  
    }  
}

詳細文件請參考。

ODBC

PostgreSQL的ODBC是以LGPL協議開源的版本，可以在這裡下載到。

也可以參考德哥的部落格，裡面有更詳細的方法和案例。

Python

Python連線GP和PG用的庫是psycopg2。安裝方法很簡單：

yum -y install python-psycopg2

或者

pip install psycopg2

或者從原始碼安裝

yum install -y postgresql-devel*

wget  http://initd.org/psycopg/tarballs/PSYCOPG-2-6/psycopg2-2.6.tar.gz
tar xf psycopg2-2.6.tar.gz
cd psycopg2-2.6
python setup.py build
sudo python setup.py install

安裝後，設定PYTHONPATH，之後就可以引用，如：

import psycopg2

sql = `select * from gp_segment_configuration;`

conn = psycopg2.connect(database=`gpdb`, user=`mygpdb`, password=`mygpdb`, host=`mygpdbpub.gpdb.rds.aliyuncs.com`, port=3568)

conn.autocommit = True
cursor = conn.cursor()
cursor.execute(sql)
rows = cursor.fetchall()

for row in rows:
    print row

conn.commit()
conn.close()

會得到類似以下的結果：

(1, -1, `p`, `p`, `s`, `u`, 3022, `192.168.2.158`, `192.168.2.158`, None, None)
(6, -1, `m`, `m`, `s`, `u`, 3019, `192.168.2.47`, `192.168.2.47`, None, None)
(2, 0, `p`, `p`, `s`, `u`, 3025, `192.168.2.148`, `192.168.2.148`, 3525, None)
(4, 0, `m`, `m`, `s`, `u`, 3024, `192.168.2.158`, `192.168.2.158`, 3524, None)
(3, 1, `p`, `p`, `s`, `u`, 3023, `192.168.2.158`, `192.168.2.158`, 3523, None)
(5, 1, `m`, `m`, `s`, `u`, 3026, `192.168.2.148`, `192.168.2.148`, 3526, None)

libpq

libpq是PostgreSQL原生支援用於連線PG的C庫。在安裝了GP或者PG之後，在其lib目錄下可以找到其的靜態庫和動態庫。這裡有很多例子，此處不再列舉。

詳細的文件

常規操作

對GP的操作，整體與PG相較為一致，包括schema、型別支援、使用者許可權等。除了GP自身的一些特有的操作，如分佈表、AO表等，其他操作皆可參考PG。

當前ApsaraDB For Greenplum對並沒有開放DB例項的superuser許可權，對應的則是rds_superuser，這一點與ApsaraDB For RDS（PostgreSQL）中的許可權體系一致。

建立DB

Greenplum中建立DB的操作與PG相同，可以通過SQL來執行，如psql連線到GP後執行：

=> create database mygpdb;
CREATE DATABASE

=> c mygpdb
psql (9.4.4, server 8.3devel)
You are now connected to database "mygpdb" as user "mygpdb".
mygpdb=> create extension postgis;
CREATE EXTENSION

建立分佈表

在GP中，表是分佈在所有的Segment上的，其分佈規則是HASH或者隨機。在建表的時候，指定分佈鍵；當匯入資料的時候，會根據分佈鍵計算得到的HASH值分配到特定的Segment上。

=> create table vtbl(id serial, key integer, value text,  comment text) distributed by (key);
CREATE TABLE

當不指定分佈鍵的時候（即不帶後面的”distributed by (key)”），GP會預設對id欄位以round-robin的方式進行隨機分配。

分佈鍵

分佈鍵對於查詢效能至關重要。均勻為分佈鍵選擇的第一大原則，選取更有業務意義的欄位，並非必須選擇原庫的主鍵。參考中，《Greenplum 資料庫最佳實踐》做了很好的說明。

查詢

構造資料：

  • 建立隨機字串生成函式和型別
CREATE OR REPLACE FUNCTION random_string(integer) RETURNS text AS $body$
SELECT array_to_string(array
                         (SELECT substring(`0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz`
                                           FROM (ceil(random()*62))::int
                                           FOR 1)
                          FROM generate_series(1, $1)), ``); $body$ LANGUAGE SQL VOLATILE;

CREATE TYPE cuboid AS (LENGTH integer, width integer, height integer);
  • 建立分佈表和構造資料
CREATE TABLE tbl(id serial, KEY integer, value text, shape cuboid, locate geometry, COMMENT text) distributed by (key);

INSERT INTO tbl(KEY, COMMENT, shape, locate, value) SELECT KEY, COMMENT, shape, ST_GeomFromText(locate) AS locate, value FROM (SELECT (a.t + 1) AS KEY, random_string(ceil(random() * 24)::integer) AS COMMENT, row(ceil(random() * 36 + 49), ceil(random() * 6 + 99), ceil(random() * 12 + 199))::cuboid AS shape, `POINT(` || ceil(random() * 36 + 99) || ` ` || ceil(random() * 24 + 50) || `)` AS locate, (CASE WHEN a.b = 1 THEN `red` WHEN a.b = 2 THEN `green` WHEN a.b = 3 THEN `yellow` WHEN a.b = 4 THEN `blue` END) AS value FROM (SELECT ceil(random() * 4) b, t FROM generate_series(0, 99999) AS t) AS a) AS t;
  • 查詢
=> select * from vtbl where key = 13944;
   id   |  key  | value |    shape     |                   locate                   |    comment     
--------+-------+-------+--------------+--------------------------------------------+----------------
 113944 | 13944 | red   | (84,102,206) | 01010000000000000000405D400000000000C05140 | 22EaPlVqvfSgpq
(1 row)

Time: 513.101 ms
  • PG上的查詢
=> select * from tbl where key = 13944;
  id   |  key  | value |    shape     |                   locate                   |   comment    
-------+-------+-------+--------------+--------------------------------------------+--------------
 13944 | 13944 | green | (55,105,207) | 01010000000000000000C05C400000000000004C40 | 3wQrCJqnHPds
(1 row)

Time: 1162.243 ms

在同樣的表結構、記錄數和沒有新增任何索引的情況下，GP的查詢要比PG的快一些。這個GP的例項是兩個Segments，當記錄數更多、Segments更多的時候，理論上差距會更明顯。

參考

  1. Pivotal Greenplum官方文件
  2. ODBC編譯
  3. ODBC下載
  4. JDBC下載
  5. 《Greenplum：你不可不知的實施與維護最佳實踐》
  6. 《Greenplum 資料庫最佳實踐》


相關文章