当前位置： 首页 > web >正文

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

web 2025/7/1 10:30:50

GreenPlum数据库及语法使用

1.启动和停止

su gpadmin

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

gpstop -a

2.查看数据库状态

psql -l

pgstate

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

命令意义
\h查看SQL命令的解释，比如\h select
?查看PSQL命令列表
\l列出所有数据库
\c [datebase_name]连接其他数据库
\d列出当前数据库的所有表格
\d [table_name]列出某一张表格的结构
\du列出所有用户
\e打开文本编辑器
\conninfo列出当前数据库和连接的信息
\d tblname查看表结构，相当于desc tblname
\dt 列举表相当于mysql的show tables
\di查看索引
\df查看函数
\copyright显示PostgreSQL的使用和发行条款
\encoding [字元编码]显示或设定用户端字元编码
\h [名称]SQL命令语法，用*显示全部命令
\password [Username]修改用户密码
\q推出命令行界面

4.DML操作

  • 创建新表
create table user(name varchar(20),signup_date DATE
);
  • 插入数据
insert into 
user(name,sing_date) values('张三','2021-10-21');
  • 选择记录
select * 
from user;
  • 更新记录
update user set name = 'zxy' where name = 'zhou';
  • 删除记录
delete from user where name = '李四';
  • 添加字段
alter table user add email varchar(40);
  • 更新字段
alter table user alter column sing_date set not null;
  • 更名字段
alter table user rename column sing_date to signup;
  • 删除字段
alter table user drop column email;
  • 删除表格
drop table if exists user;

5.基本语法

  • 创建数据库
createdb serviceDB(库名) -E utf-8
然后默认数据库
export GPDATABASE = serviceDB
进入命令界面
psql
  • 查询建表
-- 方法一
create user as select * from text1;--方法二
select * into user from text1;
  • Insert

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

  • Update

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

  • Delete

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

  • Truncate

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

6.常见数据类型

6.1 数值类型

类型名称存储空间描述范围
smallint2字节小范围整数-32768 ~ 32 767
integer4字节常用的整数-2 147 483 648~+2 147 483 647
bigint8字节大范围的整数-9 223 372 036 854 ~9 223 372 036 854
decimal变长用户声明精度，精确无限制
numeric变长用户声明精度，精确无限制
real4字节变精度，不精确6位十进制数字精度
double precision8字节变精度，不精确15位十进制数字精度
serial4字节自增整数1 - 2 147 483 647
bigserial8字节大范围的自增整数1 - 9 223 372 036 854 775 807

6.2 字符类型

类型名称描述
character varying(n),varchar(n)变长，有长度限制
character(n),char(n)定长，不足补空白
text变长，无长度限制

6.3 时间类型

类型名称存储空间描述最低值最高值时间精度
timestamp[§][without time zone]8字节日期和时间4713BC5874897AD1毫秒
timestamp[§] with time zone8字节日期和时间，带时区4713BC5874897AD1毫秒
interval[§]12字节时间间隔-178 000 000年178 000 000年1毫秒
date4字节只用于表示日期4713BC5 874 897AD1天
time[§][without time zone]8字节只用于表示一日内的时间00:00:0024:00:001毫秒
time[§] with time zone12字节只用于表示一日内时间，带时区00:00:00+145924:00:00-14591毫秒

7.常用函数

7.1 字符串函数

函数返回类型描述例子结果
string||stringtext字符串连接
length(string)intstring中字符的数目length(‘jose’)4
position(substring in string)int指定的子字符串的位置position(‘om’in’Tomas’)3
substring(string[from int][for int])text抽取子字符串substring(‘Thomas’from 2 for 3)hom
trim([leading|trailing|both][characters]from string)text从字符串string的开头/结尾/两边删除只包含characters中字符（默认是空白）的最长的字符串trim(both ‘x’ from ‘xTomxx’)Tom
lower(string)text把字符串转化为小写
upper(string)text把字符串转化为大写
overlay(string placing string from int [for int])text替换子字符串overlay(‘Txxxxas’ placing ‘hom’ from 2 for 4)Thomas
replace(string text,from text,to text)text把字符串string中出现的所有子字符串from替换成子字符串toreplace(‘abcdefabcdef’,’cd,’XX’)abXXefabXXef
split_part(string text, delimiter text,filed int)text根据delimiter分隔string返回生成的第field个子字符串（1开始）split_part(‘abc|def|ghi’,’|’,2)def

7.2 时间函数

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

函数返回类型描述例子结果
age(timestamp,timestamp)interval减去参数后的”符号化”结果age(timestamp’2001-04-10’,timestamp’1957-06-13)43 years 9 mons 27 das
age(timestam)interval从current_date减去参数中的日期age(timestam’1957-06-13)-
current_datedate当前的日期--
current_timetime with time zone当日时间--
current_timestamptimestamp with time zone当前事务开始时的事件戳--
date_part(text,timestamp)double precision获取子域（等效于extract)date_part(‘hour’,timestamp’2001-02-16 20:38:40)20
date_trunc(text,timestamp)timestamp截断成指定的精度date_trunc(‘hour’,timestamp ‘2001-02-16 20:38:40’)2001/2/16 20:00
extract(field from timestamp)double precision获取子域（同date_part）（同date_part）
now()timestampe with time zone当前事务开始的时间戳-

7.3 数值计算函数

函数返回类型描述例子结果
abs(x)(与x相同)绝对值--
ceil(dp或numeric)\ceiling(与输入相同)不小于参数的最小整数--
exp(dp或numeric)(与输入相同)自然指数--
ln(dp或numeric)(与输入相同)自然对数--
log(dp或numeric)(与输入相同)以10 为底的对数--
log(b numeric,x numeric)numeric以b为底的对数--
mod(y,x)(与参数类型相同)y/x的余数--
pi()dpπ--
power(a numeric,b numeric)numerica的b次幂--
radians(dp)dp把角度转为弧度--
random()dp0~1之间的随机数
floor(dp或numeric)(与输入相同)不大于参数的最大整数--
round(v numeric,s int)numeric圆整为s位小数round(42.4382,2)42.44
sign(dp或numeric)(与输入相同)参数的符号(-1,0,+1)sing(-8,4)-1
sqrt(dp或numeric)(与输入相同)平方根--
cbrt(dp)dp立方根--
trunc(v numeric,s int)numeric截断为s位小数--

7.4 其他常用函数

  • 序列号生成函数——generate_series
generate_series(x,y,t)

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

  • 字符串列转行——string_agg
string_agg(str,symbol [order by str])

（按照某字段排序）将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功能

group sets语法等价的普通SQL的语法
SELECT C1,C2,SUM(C3)FROM T GROUP BY GROUPING SETS((C1),(C2))SELECT C1,NULL AS C2,SUM(C3) FROM T GROUP BY T UNION ALL SELECT NULL AS C1,C2,SUM(C3) FROM T GROUP BY YEAR
GROUP BY GROUPING SETS((C1,C2,…Cn))GROUP BY C1,C2,…,Cn
GROUP BY ROLLUP(C1,C2,…,Cn-1,Cn)GROUP BY GROUPING SETS((C1,C2,…CCn-1,Cn),(C1,C2,…,Cn-1)…,(C1,C2),(C1),())
GROUP BY CUBE(C1,C2,C3)GROUP BY GROUPING SETS((C1,C2,C3),(C1,C2),(C1,C3),(C2,C3),(C1),(C2),(C3),())

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));

img

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

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));

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

img

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);

img

9.4 修改分区表

ALTER DEFAULT PARTITIONDROP DEFAULT PARTITION [IF EXISTS]DROP PARTITION [IF EXISTS] {partition_name| FOR (RANK(number))| FOR (value)}[CASCADE]TRUNCATE DEFAULT PARTITIONTRUNCATE PARTITION {partition_name| FOR (RANK(number))| FOR (value)}RENAME DEFAULT PARTITION TO new_partition_nameRENAME PARTITION {partition_name| FOR (RANK(number))| FOR (value)}TO new_partition_nameADD 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]
  • 新增分区
alter table test.test_partition_every add partition p20120105_6start ('2012-01-05'::date) END ('2012-01-07'::date);
  • drop/truncate分区
alter table test.test_partition_every drop partition p20120105_6;
alter table test.test_partition_every truncate partition p20120105_6;

拆分分区

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

交换分区

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

10.外部表

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

img
外部表就是一张表的数据是指向数据库之外的数据文件的。在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']

外部表需要指定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 &

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;
  • 3.外部表查询及数据加载
testDB=# select * from public.test001_ext

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 [,...]]]
copy public.number_fdata0_1_prt_p30001231 from '/home/gpadmin/member_his_init.dat' with delimiter ',';

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;
  • 存储过程结构模板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;
  • 存储过程结构模板3：
CREATE OR REPLACE FUNCTION dws.p_function()RETURNS voidLANGUAGE plpgsql
AS $function$ 
beginend;
$function$
;
  • 赋值

给一个变量或行/记录赋值用下面方法：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;
  • 错误和消息
	RAISE level 'format' [, expression [, ...]];Level:Info:信息输入Notice:信息提示Exception:产生一个例外，将退出存储过程Example: RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;
http://www.xdnf.cn/news/11678.html

相关文章：

  • js中对象方法hasOwnProperty()讲解
  • 线程池ThreadPoolExecutor 的使用
  • 排序算法总结——归并排序
  • 信号强度（RSSI）知识整理
  • AVPlayer 播放在线视频和本地文件的设置区别
  • Hydra(九头蛇)工具使用（非常详细）零基础入门到精通，收藏这一篇就够了！！！
  • linux 下理解文件系统的inode
  • SM2——适用于前后端（java+vue）公用的SM2国密加解密传输
  • MXNet 相关函数详解
  • 电脑安装ubuntu系统详细教程
  • JDK安装教程
  • 000-搭建Gitea-自己的git服务器
  • SSM框架讲解（史上最详细的文章）
  • scrapy框架--快速了解
  • IDEA配置JavaFX
  • （一）PaddlePaddle（深度学习框架）的搭建、使用
  • DFS（深度优先搜索）入门介绍
  • shuffle
  • JSP详细基础教学
  • Python网络爬虫之Xpath详解
  • Python的包安装工具——pip命令大全
  • netstat详解
  • Linux-文件查找find命令
  • Echarts热力图配置项，一篇文章告诉你。
  • 神仙级Python入门教程(非常详细)，从零基础入门到精通，看这篇就够了
  • 一文详细说明spring cloud和Spring Cloud Alibaba的各自组件以及联系和区别
  • Validate表单验证插件之常用参数介绍
  • 网关 GateWay 的使用详解、路由、过滤器、跨域配置
  • 神经网络（NN）网络构建及模型算法介绍
  • 从计网的角度讲明白什么是网关