《GreenPlum系列-开发相关》GreenPlum数据库及语法使用_greenplum数据库语法

GreenPlum数据库及语法使用

1.启动和停止

su gpadmin

gpstart -a(-a的原因是不需要在手动输入yes)

gpstop -a

2.查看数据库状态

psql -l

pgstate

3.命令行连接访问数据库

4.DML操作

• 创建新表

create table user(
	name varchar(20),
    signup_date DATE
);
1
2
3
4

• 插入数据

insert into 
user(name,sing_date) values('张三','2021-10-21');
1
2

• 选择记录

select * 
from user;
1
2

• 更新记录

update user set name = 'zxy' where name = 'zhou';
1

• 删除记录

delete from user where name = '李四';
1

• 添加字段

alter table user add email varchar(40);
1

• 更新字段

alter table user alter column sing_date set not null;
1

• 更名字段

alter table user rename column sing_date to signup;
1

• 删除字段

alter table user drop column email;
1

• 删除表格

drop table if exists user;
1

5.基本语法

• 创建数据库

createdb serviceDB(库名) -E utf-8
然后默认数据库
export GPDATABASE = serviceDB
进入命令界面
psql
1
2
3
4
5

• 查询建表

-- 方法一
create user as select * from text1;

--方法二
select * into user from text1;  
1
2
3
4
5

• Insert

在执行insert的时候吗，注意分布建不要为空，否则分布建默认会变成null,数据都被保存在一个节点上，导致数据分布不均。

• Update

不能批量对分布建执行update,因为对分布建执行update需要数据重分布，Greenplum暂时不支持这个功能。

• Delete

Greenplum 3.X版本中，如果delete操作设计子查询，并且子查询的结果还会涉及数据重分布，这样的删除语句会报错。 对整张表执行Delete较慢的，有此需求的建议使用truncate

• Truncate

与Oracle一样，执行Truncate直接删除表的物理文件，然后创建新的数据文件。truncate操作比delete操作在性能上有非常大的提升，当前如果有SQL正在操作这张表，那么truncate操作会被锁住，直到表上所有所被释放。

6.常见数据类型

6.1 数值类型

6.2 字符类型

6.3 时间类型

7.常用函数

7.1 字符串函数

7.2 时间函数

使用interval类型可以直接对事件类型进行计算，用来计算时间的加减

7.3 数值计算函数

7.4 其他常用函数

• 序列号生成函数——generate_series

generate_series(x,y,t)
1

生成多行数据从x到另外y，步长为t，步长默认是1

• 字符串列转行——string_agg

string_agg(str,symbol [order by str])
1

（按照某字段排序）将str列转行，以symbol分隔

• 字符串行转列——regexp_split_to_table

• hash函数——md5，hashbpchar

md5的hash算法精度为128位，返回一个字符串
Hashbpchar的精度是32位，返回一个integer类型

8.分析函数

8.1 开窗函数

聚合函数返回各个分组的结果，开窗函数则为每一行返回结果。

rank() 不连续不重复
row_number() 连续不重复

8.2 grouping sets

如果需要对几个字段的组合进行group ，需要用到grouping sets功能

9.分区表

Greenplum支持分区表。

可以使用时间分区、Every分区、list分区、

通过实验得到：向主表插入数据时，数据会被自动存放至相应的分区表。

也可以直接向分区子表插入符合条件的数据，

当向分区子表插入不符合条件的额数据时，会提示：
[Err] ERROR: Trying to insert row into wrong partition (seg1 hadoop3:40000 pid=6679) DETAIL: Expected partition: test_partition_range_1_prt_p20111231, provided partition: test_partition_range_1_prt_p20111230
删除主表是会自动删除关联的分区表

9.1 按时间分区

create table public.test_partition_range(

  id numeric,

  name character varying(32),

  dw_end_date date

)Distributed by (id)

PARTITION BY range(dw_end_date)

(

​    PARTITION p20111230 START ('2011-12-30'::date) END ('2011-12-31'::date),

​    PARTITION p20111231 START ('2011-12-31'::date) END ('2012-01-01'::date)

);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

在各自的分区表中插入数据,总表中会有显示.删除总表后,分区表直接删除

9.2 使用Every分区

create table test.test_partition_every(

  id numeric,

  name character varying(32),

  dw_end_date date

) distributed by(id)

partition by range(dw_end_Date)

(

  partition p201112 start('2011-12-1'::date) end ('2011-12-31'::date)

  every ('1 days'::interval)

);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

使用interval类型可以直接对事件类型进行计算，用来计算时间的加减

9.3 使用list分区

create table test.test_partition_list(

  member_id numeric,

  city character varying(32)

)distributed by (member_id)

partition by list(city)

(

  partition guangzhou values('guangzhou'),

  partition hangzhou values('hangzhou'),

  default partition other_city

);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

9.4 修改分区表

ALTER DEFAULT PARTITION

DROP DEFAULT PARTITION [IF EXISTS]

DROP PARTITION [IF EXISTS] {

  partition_name

  | FOR (RANK(number))

  | FOR (value)

}

[CASCADE]

TRUNCATE DEFAULT PARTITION

TRUNCATE PARTITION {

  partition_name

  | FOR (RANK(number))

  | FOR (value)

}

RENAME DEFAULT PARTITION TO new_partition_name

RENAME PARTITION {

  partition_name

  | FOR (RANK(number))

  | FOR (value)

}

TO new_partition_name

ADD DEFAULT PARTITION NAME [(subpartition_spec)]

ADD PARTITION [name] partition_element

  [(subpartition_spec)]

EXCHANGE PARTITION {

  partition_name

  | FOR (RANK(number))

  | FOR (value)

} WITH TABLE TABLE_NAME

  [WITH|WITHOUT VALIDATION]

EXCHANGE EFAULT PARTITION WITH TABLE TABLE_NAME

  [WITH|WITHOUT VALIDATION]

SET SUBPARTITION TEMPLATE (subpartition_spec)

SPLIT DEFAULT PARTITION {

​    AT (list_value)

  |START([datatype] range_value) [INCLUSIVE|EXCLUSIVE]

   END ([datatype]) range_value) [INCLUSIVE|EXCLUSIVE]

}

[INTO (PARTITION new_partition_name,

​       PARTITION default_partition_name)]

SPLIT DPARTITION {

  partition_name

  | FOR (RANK(number))

  | FOR (value)

} AT(value)

[INTO (PARTITION partition_name, PARTITION partition_name]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

• 新增分区

alter table test.test_partition_every add partition p20120105_6

start ('2012-01-05'::date) END ('2012-01-07'::date);
1
2
3

• drop/truncate分区

alter table test.test_partition_every drop partition p20120105_6;
alter table test.test_partition_every truncate partition p20120105_6;
1
2

拆分分区

alter table test.test_partition_every split partition p20120105_6
at(('2012-01-06'::date)) into (partition p20120105,partition p20120106);
1
2

交换分区

alter table test.test_partition_every exchange partition p20120102 
with table test.test_on_partition;
1
2

10.外部表

Greenplum在数据加载上有一个明显的优势，就是支持数据并发加载，gpfdist就是并发加载的工具，在数据库中对应的就是外部表。

外部表就是一张表的数据是指向数据库之外的数据文件的。在Greenplum中，可以对一个外部表执行正常的DML操作，当读取数据的时候，数据库就从数据文件中加载数据。外部表支持在Segment上并发的告诉从gpfdist导入数据，由于是直接从Segment上导入数据，所以效率非常高。

10.1 创建外部表

CREATE [READABLE] EXTERNAL TABLE TABLE_NAME

(column_name data_type [,...] | LIKE other_table)

LOCATION ('file://seghost[:port]/path/file' [,...])

  | ('gpfdist://filehost[:port]/file_pateern' [,...])

  | ('gphdfs://hdfs_host[:port]/path/file')

FORMAT 'TEXT'

[( [HEADER]

   [DELIMITER [AS] 'delimeter' | 'OFF']

   [NULL [AS] 'null string']

   [ESCAPTE [AS] 'escape'|'OFF']

   [NEWLINE [AS] 'LF'|'CR'|'CRLF']

   [FILL MISSING FIELDS] )]

| 'CSV'

[( [HEADER]

   [QUOTE [AS] 'quote']

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

外部表需要指定gpfdist的IP和端口，详细目录地址。其中文件名支持通配符匹配。 可以编写多个gpfdist的地址，但是不能超过总的Segment数，否则会报错。在创建外部表的时候可以指定分隔符、err表、指定允许出错的数据条数，以及源文件的字符编码等。 外部表还支持本地文本文件的导入，效率较低，不建议使用。 外部表还支持HDFS的文件操作。

10.2 启动gpfdist及创建外部表

• 1.首先在文件服务器上启动gpfdist服务，指定文件目录及端口

nohup $GPHOME/bin/gpfdist -d /home/admin -p 8888 > /tmp/gpfdist.log 2>&1 &
1

nohup保证程序在Server端执行，当前会话关闭后，程序仍然正常运行
nohup是Unix/Linux中的一个命令，普通进程通过&符号放到后台运行，如果启动该程序的额控制台退出，则该进程终止。nohup命令启动程序，则在控制台退出后，进程仍然继续运行，起到守护进程额作用。

• 2.准备好需要加载的数据文件，将其放在外部表文件及其的、home/admin/目录或该目录的子目录下，在Greenplum中创建对应的外部表

create external table test.test001_ext(

  id integer,

  name varchar(128)

)

location (

  'gpfdist://10.20.151.11:8888/gpextdata/test001.txt'

)

format 'TEXT' (delimiter as E'|' null as '' escape 'OFF')

encoding 'gb18030' log errors into test.test001_err segment reject limit 10rows;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

• 3.外部表查询及数据加载

testDB=# select * from public.test001_ext
1

10.3 COPY命令

使用COPY命令可以实现将文件导出和导入，但是要通过Master，效率没有外部表高，但是在数据量比较小的情况下，COPY命令比外部表要方面很多。

COPY table [(colum [, ...]) ] FROM {'file'|STDIN}

[ [WITH]

  [OIDS]

  [HEADER]

  [DELIMITER [AS] 'delimeter']

  [NULL [AS] 'null string']

  [ESCAPE [AS] 'escape' | 'OFF']

​    [NEWLINE [AS] 'LF'|'CR'|'CRLF']

  [CSV [QUOTE [AS] 'quote']

​    [FORCE NOT NULL column [,...]]

  [FILL MISSING FIELDS]

  [ [LOG ERRORS INTO error_table] [KEEP]

​     SEGMENT REJECT LIMIT count [ROWS|PERCENT]]

COPY {table [(column [,...])] |(query) } TO {'file'|STDOUT}

  [ [WITH]

​    [OIDS]

​    [HEADER]

​    [DELIMIETER [AS] 'delimeter']

​    [NULL [AS] 'null string']

​    [ESCAPE [AS] 'escape'|'OFF']

​    [CSV [QUOTE [AS] 'quote']

​         [FORCE QUOTE column [,...]]]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

copy public.number_fdata0_1_prt_p30001231 from '/home/gpadmin/member_his_init.dat' with delimiter ',';
1

Greenplum 4.x中引入了可写外部表，在导出数据的时候可以用可写外部表并发导出，性能很好。但在Greeplum3.x版本中，导出数据只能通过COPY命令实现，数据在Master上汇总导出。
如果需要将数据远程导出到其他机器上，可以使用copy to stdout，远程执行psql连接到数据库上，然后通过管道将数据重定向成文件。

11 GP的存储过程

Greenplum支持SQL/PYTHON/PERL/C语言构建函数，以下着重介绍SQL 存储过程。 一个存储过程就是一个事务，包括对子过程的调用都在一个事务内

• 存储过程结构模板1：

CREATE FUNCTION somefunc() RETURNS integer AS $$

DECLARE

​    quantity integer := 30;

BEGIN

 

​    RETURN ....;

END;

$$ LANGUAGE plpgsql;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• 存储过程结构模板2：

create or replace function 存储过程名称(in 参数1 参数类型,in 参数2 参数类型,in 参数3 参数类型 )
returns 返回类型 as $$ 
begin
存储过程语句
end;
$$ language plpgsql;


create or replace function prc_test(in par1 text,in par2 numeric,in par3 integer)
returns void as $$ 
begin
insert into table1 values(par1,par2,par3);
end;
$$ language plpgsql;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

• 存储过程结构模板3：

CREATE OR REPLACE FUNCTION dws.p_function()
 RETURNS void
 LANGUAGE plpgsql
AS $function$ 
begin

end;
$function$
;
1
2
3
4
5
6
7
8
9

• 赋值

给一个变量或行/记录赋值用下面方法：identifier := expression
例子：user_id := 20;

• perform query

执行一个没有结果的查询: PERFORM query;
一个例子：
PERFORM create_mv(‘cs_session_page_requests_mv’, my_query);

• 动态****SQL

EXECUTE command-string [INTO [STRICT] target];

• SELECT INTO

Example：SELECT ID INTO VAR_ID FROM TABLEA

• 获取结果状态

GET DIAGNOSTICS variable = item [, …];
一个例子：
​ ·GET DIAGNOSTICS integer_var = ROW_COUNT;

• SQL返回变量

SQLERRM， SQLSTATE

• 控制结构

IF … THEN … ELSEIF … THEN … ELSE
LOOP, EXIT, CONTINUE, WHILE, FOR

• 从函数返回

有两个命令可以用来从函数中返回数据：RETURN 和 RETURN NEXT 。
Syntax：RETURN expression;

• 设置回调

EXEC SQL WHENEVER condition action;
condition 可以是下列之一：
SQLERROR，SQLWARNING，NOT FOUND

• 异常处理

  EXCEPTION WHEN unique_violation 
  
  THEN 
  
  -- do nothing 
  
  END;
  
  忽略错误：
  
  EXCEPTION  WHEN OTHERS THEN
  
  ​            RAISE NOTICE 'an EXCEPTION is about to be raised';
  
  ​            RAISE EXCEPTION 'NUM:%, DETAILS:%', SQLSTATE, SQLERRM;
  
  END;
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17

• 错误和消息

	
	RAISE level 'format' [, expression [, ...]];
	
	Level:
	
	Info:信息输入
	
    Notice:信息提示
	
	Exception:产生一个例外，将退出存储过程
	
	Example: RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;
1
2
3
4
5
6
7
8
9
10
11
12