An Easy Start of PostgreSQL on Greenplum

2018-02-07

What is PostgreSQL

PostgreSQL是一个对象-关系型数据库管理系统(object-relational database management system,ORDBMS)。

PostgreSQL具有以下特性:

  1. Complex query
  2. Foreign keys
  3. Triggers
  4. Views
  5. Transactional integrity
  6. Multiversion concurrency control

用户可以依据自己的需求扩展PostgreSQL:

  1. Data types
  2. Operators
  3. Aggregate functions
  4. Functions
  5. Index method
  6. Procedural Laguage

PostgreSQL Architecture

一个PostgreSQL会话包括了两个协同进程:

  • 服务端进程,其管理数据库文件,接收来自客户端的数据库连接请求,执行来自客户端的操作。数据库服务端进程名为postgres。
  • 客户端(前端)应用。客户端应用有很多种形式:面向文本工具,图形化应用,网页服务器(连接到服务器并将数据展示在网页上),或者定制的数据库维护工具。一些客户端应用提供了PostgreSQL。

一个client/server应用的客户端和服务端可以在不同的主机上,通过TCP/IP网络连接。

You should keep this in mind, because the files that can be accessed on a client machine might not be accessible (or might only be accessible using a different file name) on the database server machine.

PostgreSQL服务端支持来自客户端的多并发连接请求。它会为每个连接启动一个新的进程(称为”folk”)。在这种情况下,客户端和新进程将直接通信,而不是通过原来的postgres进程。因此,master服务进程将一直处于运行,等待客户端连接。

PostgreSQL Commands

创建数据库:
createdb dbname

如果显示无法连接到数据库,原因可能是数据库服务没有启动。

删除数据库:
dropdb dbname

主要通过三种方式连接到数据库:

  1. 通过命令行指令psql进行连接;也可以直接进行一些数据库操作。
  2. 通过一个图形化程序来连接数据库,例如pgAdmian
  3. 通过程序接口

可以通过psql --help查看使用方法。

连接到数据库后,可以通过\h查看一些指令:

  • 退出客户端:\q
  • 查看连接信息:\l
  • 连接到其他数据库:\c dbname -username
  • 查询表信息:\d tablename
  • 查看具体帮助信息:\h command(例如\h create table

Database Access

授权文件名为pg_hba.conf
在文件的最下方有访问权限记录,格式为:

1
host   database   role   CIDR-address   authentication-method

开放权限给所有用户:

1
host  all   all   0.0.0.0/0   md5

md5表示密码将会被加密。

更改后,需要重启服务,配置才能生效。

The SQL language

Creating a New Table

1
2
3
4
5
6
7
8
9
CREATE TABLE weather (
city varchar(80) NOT NULL DEFAULT '' COMMENT 'Primary Key',
temp_lo int, -- low temperature
temp_hi int, -- high temperature
prcp real, -- precipitation
date date,
PRIMARY KEY ('city'),
KEY ('temp_lo')
);

psql支持换行,一个语句在遇到分号时才会结束。
city表示一个关系列的属性的实际名称。
varchar(80)表示city的数据类型为varchar,长度为80个字符。
NOT NULL表示该列的值不能为空。DEFAULT表示如果用户未输入该列的值,将时候预设默认值。这里的默认值是空字符串(empty而不是null)。
COMMENT表示注释,将会作为列的显示名称。

PostgreSQL支持标准SQL类型:int, smallint, real, double precision, char(N), varchar(N), date, time, timestamp, interval。还有其他一些类型,以及自定义类型。

删除数据库表的操作为:
DROP TABLE tablename

在GREENPLUM中

  • 在Greenplum中建表需要指定分布健(distribution key)。
  • 如果需要按字段分区,可以通过partition by创建分区表。
  • 可以使用like创建与目标相同结构的表。功能与CREATE TABLE tablename AS SELECT * FROM targettable LIMIT 0;相似。

Greenplum是一个分布式数据库,数据分布于每个segment上。它有两种分布策略:

  1. Hash分布。指定一个分布键,计算hash值,并通过hash值路由至目标segment。如果不通过DISTRIBUTED BY指定分布键,则将默认以主键或唯一键为分布键。可以指定多个分布键。
  2. 随机分布,或称平均分布。数据随机分散在节点上,无论内容。但是在实际执行中,关联操作将导致数据充分不,导致性能较差。语句为DISTRIBUTED RANDOMLY
1
2
3
4
5
6
7
8
9
CREATE TABLE weather (
city varchar(80) NOT NULL DEFAULT '' COMMENT 'Primary Key',
temp_lo int, -- low temperature
temp_hi int, -- high temperature
prcp real, -- precipitation
date date,
PRIMARY KEY ('city'),
KEY ('temp_lo')
) DISTRIBUTED BY (city);

每一个segment都是一个单一的数据库,单个数据库可以保证数据的唯一性,但是在多个数据节点就无法保证数据的全剧唯一性。因此选择按唯一键分布,保证同值数据在同一个数据库上,以保证唯一性(存疑)。

Populating, Deleting and Updating a Table With Rows

插入语句的语法:
INSERT INTO tablename VALUES ('value1', 'value2', 6);

非简单数值类型(char,date等)必须要用''括起来。输入值的顺序要和表的列顺序相同。如果要指定输入列,可以在表名后明确地列出属性名:
INSERT INTO tablename (column1, column2, column3) VALUES ('value1', 'value2', 6);

属性名顺序可以打乱,只要和后面的值对应即可。

从外部文件导入大量数据可以尝试使用COPY指令:
COPY tablename FROM /path/to/file;

更新数据语法:

1
2
3
UPDATE tablename
SET column1 = 'value1', column2 = value2
WHERE column3 > 'value3';

WHERE关键字用于表示筛选条件,以AND包含多个条件。

删除数据语法:

1
DELETE FROM tablename WHERE column1 = value1;

DELETE FROM tablename;不带参数时将删除整张表的数据。涉及整张表的删除应该使用TRUNCATE(删除物理文件)。
TRUNCATE将删除整个表文件,然后重新创建数据文件。

Greenplum

在GP中,插入数据时分布键不能为空,否则数据将被存储到每个segment。
更新数据不能涉及分布键,否则会导致数据重分布。
删除数据时也会导致重分布。

Querying a Table

查询语句基本语法:

1
2
3
4
SELECT column1 AS c1, column2 AS c2, (column3 + column4)/2 AS c3
FROM tablename AS tb
WHERE tb.column1 = value1 AND tb.column2 > value2
ORDER BY column3, tb.column4

  • AS之后可以为目标(属性或关系)添加一个临时的别名。AS可以省略。在涉及到多个关系的查询中,就可以通过添加别名来区分不同关系的同名属性。用于区分关系的别名即为:元组变量(tuple variable)。一个属性明确地属于一个关系时,可以省略元组变量。
  • ORDER BY可以接收多个列。
  • SELECT中还可以对不同属性数据进行运算操作如(column3 + column4)/2
  • 模式匹配关键字LIKELIKE 'Som_thing%',下划线_表示单个字符,%表示任何形式字符串。
  • 并集关键字UNION(SELECT column1 AS c FROM table1) UNION (SELECT column2 AS c FROM table2)。语句的属性别名应该相同。并交差的用法类似。
  • 差集关键字EXCEPT
  • 交集关键字INTERSECT

Greenplum

对于GP,重点是要了解分布式执行计划。
需要注意的是,Master上排列的顺序,是根据数据导入时的排列的。从segment读取数据时,顺序是随机的,不固定的。所以两次搜索的结果,顺序可能是不同的。
EXPLAIN关键字可以用来查看SELECT语句的执行计划。

Eureka

..-. . -.