Greenplum常用命令

Greenplum

Greenplum在v8版本之前还存在一个角色(role)的概念，角色把用户(user)和组(group)的概念包括在内。一个角色可能是一个数据库用户、一个组或者两者兼具。角色可以拥有数据库对象（例如表）并且可以那些对象上的特权分配给其他角色来控制对对象的访问。

不过再往后的版本将角色和用户简化为一个概念，创建角色即默认创建一个用户。

注意

• 子查询必须携带别名，否则报错
• postgres=# select 1/4; #在PG里如果想做除法并想保留小数，用上面的方法却行不通，因为"/" 运算结果为取整，并且会截掉小数部分。可以通过 cast 函数进行转换
• postgres=# select round( cast ( 1 as numeric )/ cast( 4 as numeric),2);

基础命令

1. \l #获取当前所有database
2. select datname from pg_database; #获取当前所有database
3. \c + database #切库
4. \d #显示当前模型下的所有表
5. \d + #展示当前所有表和表的大小
6. select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database; #查看所有数据库的大小：
7. psql -h 192.168.100.139 -p 5432 -U gpadmin -d postgres; #gpadmin登录
8. psql -h 192.168.100.139 -p 5432 -U duban_dev -d zjt;
9. \z #查询表权限
10. drop table zjt_ods.sys_user_df1; #删除自己创建的表
11. \du #查看所有用户和用户组
12. create user duban_admin createdb createrole login password 'Bigdata@2021'; #创建用户组
13. select COALESCE(b.price, 0) as price from fruit_sale b #查询为null时进行补0
14. 一列变多行
15. postgres=# \df // 查看所有函数
16. postgres=# \df name // 查看某一函数信息
17. \d+ table_name 展示表结构，分区表结构

SELECT unnest(string_to_array(subject, ' ')) as "token", flag FROM test;

schema

1. \dn #获取当前库的所有schema和对应所属者;
2. select oid,* from pg_catalog.pg_namespace #获取当前库的所有schema;
3. set search_path TO zjt_ods; #切模式
4. SHOW search_path; #显示当前使用的schema
5. create schema test authorization highgo; #创建schema并指定所有者
6. create schema duban_test1; #创建schema不指定所有者
7. alter schema test rename to testa; #修改schema的名称
8. DROP SCHEMA myschema ; #删除一个为空的模式
9. DROP SCHEMA myschema CASCADE; #删除一个模式以及其中包含的所有对象

grant select on all tables in schema duban_ods to jh_dev;

grant all on all tables in schema duban_ods to jh_dev;

grant all on schema duban_ods to jk_admin;

CREATE SCHEMA jh_dwd AUTHORIZATION jh_dev;

CREATE SCHEMA jh_ads AUTHORIZATION jh_dev;

db

1. SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname='zjt' AND pid<>pg_backend_pid(); #断开某个库的所有连接

1. create database zjt; #创建数据库
2. drop DATABASE duban_dev; #删除数据库
3. drop user duban_dev; #删除用户
4. grant all on database zjt_test0415 to jk_admin; 将创建好的数据库授权给jk_admin
5. CREATE DATABASE "zjt_test041517" WITH OWNER = "jk_admin" ENCODING = 'UTF8'; #创建数据库并指定所有者

特殊语法

1. 双冒号（::) ，强制类型转换符，等同于CAST。语法格式 expression::type 。

select '100'::int4;

权限模块

1. CREATE USER huangshenqi WITH PASSWORD 'Bigdata@2021'; #创建用户并指定密码
2. grant usage on schema zjt_ods to huangshenqi; #授予huangshenqi查询zjt_ods权限
3. grant select on all tables in schema zjt_ods to huangshenqi; #授予huangshenqi用户zjt_ods的查询权限

Access privileges 具体含义：

a: insert

r: select

w: update

d: delete

x: references

t: trigger

D: truncate

1. grant select on all tables in schema zjt_ods to huangshenqi; #授予huangshenqi用户zjt_ods的查询权限
2. grant all on all tables in schema zjt_ods to huangshenqi; #授权huangshenqi用户zjt_ods的所有权限
3. revoke all on all tables in schema zjt_ods from huangshenqi; #收回用户的zjt_ods的所有权限
4. select * from INFORMATION_SCHEMA.role_table_grants where grantee='huangshenqi'; #查询用户拥有权限
5. ALTER TABLE postgres.zjt_ods.sys_user_df OWNER TO huangshenqi; #将某个表权限赋值给某个用户
6. create role duban_group; #创建用户组
7. grant nw_dev to jk_admin; #将用户加入用户组
8. grant all on database zjt_dw to zjt_test; 授权某个数据库给某个用户
9. revoke zjt_group from jk_admin; #将用户移出某个组

自定义脚本模块

1. select * into zjt_ods.sys_user_df1 from zjt_ods.sys_user_df;
2. test_chawq_etl.sh

#!/bin/bash

export PGPASSWORD='gpadmin';

db_name="postgres"

db_host="192.168.100.139"

user_name="gpadmin"

port="5432"

base_path=$(cd "$(dirname "$0")"; pwd)

echo $base_path

#eg:1

psql -X -A -d ${db_name} -U ${user_name} -h ${db_host} -p ${port} -t -f $base_path/etl.sql

#eg:2

psql -X -A -d ${db_name} -U ${user_name} -h ${db_host} -p ${port} -t -c "select count(*) from zjt_ods.sys_user_role_df_test"

#eg:3result1=(

psql -X -A -d ${db_name} -U ${user_name} -h ${db_host} -p ${port} -t -c "select count(*) from zjt_ods.sys_user_role_df_test")

echo $result1

#eg:4

psql -X -A -d ${db_name} -U ${user_name} -h ${db_host} -p ${port} -v top_n=100 -t -f $base_path/etl1.sql

1. etl.sql

select count(*) from zjt_ods.sys_user_role_df_test;

truncate table zjt_ods.sys_user_role;

insert into zjt_ods.sys_user_role select * from zjt_ods.sys_user_role_df_test;

drop table if exists zjt_tmp.sys_user_role_tmp;

select * into zjt_tmp.sys_user_role_tmp from zjt_ods.sys_user_role_df_test;

建表模块

二维表同样是GP中重要的存储数据对象，为了更好的支持数据仓库海量数据的访问，GP的表可以分成：

• 面向行存储的普通堆积表
• 面向列存储的AOT表（append only table）

当然AOT表也可以是按行存储的，但是按列存储必须是AOT表。这样，我们在设计应用上可以获得相当的灵活性。比如经常需要更新的数据，或者较小的维度数据，应该使用普通堆积表存储。

例子：

CREATE TABLE ZJT_ODS.SYS_USER_ROLE (

USER_ID BIGINT,

ROLE_ID BIGINT

)

WITH (APPENDONLY=TRUE, COMPRESSLEVEL=6, ORIENTATION=ROW, COMPRESSTYPE=ZLIB,

OIDS=FALSE

)

TABLESPACE dfs_default

DISTRIBUTED BY (USER_ID,ROLE_ID);

1. 分布键（哈希键）

• distributed by (a) 指定a字段为分布键
• distributer randomly 随机分布

注意：

• 未指定分布键，则默认表的主键为分布键，若表没有主键，则默认把第一列当作哈希键
• 分布键可以被定义为一个或多个
• 分布键必须是唯一键
• 不能修改分布键，且哈希键的列不能update
• 一个表只能定义一个唯一键，且主键和唯一键必须作为哈希键
• 数值重复度低，保证数据均匀分布
• 防止数据倾斜，interger、varchar比较适合做分布键
• 大表经常做连接时，选择相同的分布键，避免跨节点进行join
• 尽量不用序列号，无意义

1. AOT 指定按列分布

appendonly=true #指定是否只append追加

compresslevel=5 zlib #压缩级别 1-9共9个级别 9压缩最大

orientation=column #指定是否按列存储

compresstype=zlib GP #提供两种压缩算法：zlib和quicklz

oids=false #对象标识符，不分配

1. 主键、外键、自增序列

gp不支持主键与外键约束。因为主键是用唯一索引实现，而gp不支持索引，因此不支持主键。根据外键的定义，既然没有主键，也就谈不上外键了。

即自增序列就无法在建表时直接添加，可以单独创建序列，然后设置字段的自增。

CREATE SEQUENCE websocket_notify_di_seq

START WITH 1 #自增序号的开始值，如果表里有数可以设的大一些

INCREMENT BY 1

NO MINVALUE

NO MAXVALUE

CACHE 1;

然后用alter table语句就可以将自己的表设置主键自增了。

alter table DUBAN_ODS.t_duban_websocket_notify_di alter column id set default nextval('websocket_notify_di_seq');

1. 分区键

与大多数关系数据库一样，gp也支持分区表。gp支持以下分区类型：

• 范围分区：基于数字范围分区，如日期、价格等。
• 列表分区：基于列表值分区，如销售区域、产品分类等。
• 两者混合的分区类型。

创建分区表需要定义分区键、分区类型、分区层次。下面是几个创建分区表的例子。

a. 定义日期范围分区表

create table sales (id int, date date, amt decimal(10,2))

distributed by (id)

partition by range (date)

( start (date '2017-01-01') inclusive

end (date '2017-02-01') exclusive

every (interval '1 day') );

b. 定义数字范围分区表

create table rank (id int, rank int, year int, gender

char(1), count int)

distributed by (id)

partition by range (year)

( start (2017) end (2018) every (1),

default partition extra );

c. 定义列表分区表

create table rank (id int, rank int, year int, gender

char(1), count int )

distributed by (id)

partition by list (gender)

( partition girls values ('f'),

partition boys values ('m'),

default partition other );

d. 查看分区表定义

select partitionboundary, partitiontablename, partitionname,partitionlevel, partitionrank

from pg_partitions

where tablename='sales';

e.对已有分区增加新分区

ALTER TABLE nw_ods.was_ess_sync_apas_di add partition was_ess_sync_apas_di_1_prt_4

start ('2023-01-01 00:00:00'::timestamp without time zone) inclusive

end ('2024-01-01 00:00:00'::timestamp without time zone) exclusive;

f.删除分区

ALTER TABLE nw_ods.was_ess_sync_apas_di DROP PARTITION FOR (RANK(4));

外部表模块

可参考：

HAWQ技术解析（九） —— 外部数据-CSDN博客

注意：

1、如果表是有分区的，最好一个分区一个分区去读

（每个分区创建一张表，否则会有一些奇奇怪怪的问题，比如读取慢，卡，甚至跑不动）

2、数据的分隔符最好是hive默认的，或者逗号，或者"|"，最好不要是一些奇奇怪怪的分隔符。

据说hive的默认分隔符在gp的不同版本写法不一样：

Greenplum v5以上版本 E'\x01'

Greenplum v5以下版本 '\u0001'

CREATE EXTERNAL TABLE database.table(

Column1 varchar,

Column2 varchar,

Column3 integer)

location ('gphdfs://nameservice1/user/hive/warehouse/hive上的数据库.db/hive上的表名') format 'text' (DELIMITER E'\x01');

zjt_dw数仓实现案例

1：创建用户

CREATE USER jk_admin WITH PASSWORD 'Bigdata@2022';

CREATE USER nw_dev WITH PASSWORD 'Bigdata@2022';

CREATE USER tc_dev WITH PASSWORD 'Bigdata@2022';

CREATE USER jh_dev WITH PASSWORD 'Bigdata@2022';

CREATE USER tz_dev WITH PASSWORD 'Bigdata@2022';

2：加入用户组

#使用gpdamin进行登录 [root@master ~]# psql -h 192.168.100.139 -p 5432 -U gpadmin -d postgres ; #将tc_dev 加入到jk_admin组中 postgres=# grant tc_dev to jk_admin; postgres=# grant nw_dev to jk_admin; postgres=# grant jh_dev to jk_admin; postgres=# grant tz_dev to jk_admin;

3：创建db

#使用gpadmin创建db并指定所有者 [root@master ~]# psql -h 192.168.100.139 -p 5432 -U gpadmin -d postgres ; postgres=# CREATE DATABASE "zjt_dw" WITH OWNER = "jk_admin" ENCODING = 'UTF8'; CREATE DATABASE

4：创建schema

#使用jk_admin创建schema并指定所有者 [root@master ~]# psql -h 192.168.100.139 -p 5432 -U jk_admin -d postgres; zjt_dw=> CREATE SCHEMA tc_ods AUTHORIZATION tc_dev; CREATE SCHEMA zjt_dw=> CREATE SCHEMA tc_dwd AUTHORIZATION tc_dev; CREATE SCHEMA zjt_dw=> CREATE SCHEMA tc_ads AUTHORIZATION tc_dev; CREATE SCHEMA