Greenplum - GP常用函数和命令 - 《大数据》

连接数据库：

$ psql -h host -p port -U userName -W databaseName

基础命令

$ \d tableName  # 得到表结构
$ \d+ tableName  # 得到表详细信息，包括：分区表名
$ \c database # 切换数据库
$ \df # 查看函数
$ \dt # 查看所有表

查看所有 database 使用的磁盘空间

$ select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;

查看某个表使用的磁盘空间

$ select pg_size_pretty(pg_total_relation_size('tableName'));

查看索引大小

$ select pg_size_pretty(pg_relation_size('index_name'));

得到gp中所有的表，包括分区表
```
$ select * from pg_tables;
```
在服务器上执行文件中的sql
```
$ psql -f ./create_table.sql
```
在服务器上执行sql
```
$ psql -c "select id from test_table;"
```

安装、卸载插件。参考：http://postgres.cn/docs/10/sql-createextension.html

$ create extension postgis_topology; # gp中安装插件
$ drop extension ext_name; # gp中卸载插件

增加权限。参考：http://postgres.cn/docs/10/sql-grant.html
```
$ GRANT SELECT ON table_name TO bill
```

copy数据。参考：http://postgres.cn/docs/10/sql-copy.html

$ copy liq_test(id, date, segment) from '/home/gpadmin/liq_test_data.txt' with delimiter ',';

创建外表

$ CREATE EXTERNAL TABLE tmp_external_${gpTableName}(id text,time text,segment text,bitmap text) LOCATION ('path/*.gz') FORMAT 'TEXT' (delimiter '|');

复制外表数据到内表

$ insert into ${gpTableName} select id,time::bigint,segment::int,decode(bitmap,'base64')::varchar::roaringbitmap from tmp_external_${gpTableName};

测试数据生成

$ insert into wells_test select '20200426_001', '202004'::bigint, GENERATE_SERIES(1,9), decode('OjAAAAAAAAA=','base64')::varchar::roaringbitmap;

获取某个字段的大小

$ select id, pg_column_size(bitmap) from wells_test;

创建分区表

CREATE TABLE gbitmap_place(label text,createdate date,bitmap roaringbitmap)
DISTRIBUTED BY (label)
PARTITION BY RANGE (createdate)
( START (date '2019-07-18') INCLUSIVE
 END (date '2019-07-19') EXCLUSIVE
 EVERY (INTERVAL '1 day') );

增加一级分区

alter table wells_test add partition p_201901 start (202001) INCLUSIVE end (202002) EXCLUSIVE WITH (appendonly=true, compresslevel=5, compresstype=zlib);

增加二级分区

alter table wells_test alter PARTITION p_202005 add partition c1 values (1) WITH (appendonly=true, compresslevel=5, compresstype=zlib);

创建二级分区表

CREATE TABLE wells_test ( id text NOT NULL, date bigint, segment int, bitmap roaringbitmap ) DISTRIBUTED BY (id) partition by range(date) subpartition by list(segment) subpartition template ( subpartition c1 values (1) ) ( partition p1 START (20190000) INCLUSIVE END ( 20190001) EXCLUSIVE EVERY (1) );

删除分区

alter table wells_test drop PARTITION p_202005;

查询表的所有分区表

$ select count(*) from pg_partitions where tablename = 'wells_test';

查询表正在活动的线程执行情况，可以通过reason查看被锁住的语句、也可以查看正在执行的sql
```
$ select * from pg_stat_activity where waiting_reason='lock';
```
kill 正在lock的语句
```
$ select pg_terminate_backend(211675);
```

参考文档：
https://github.com/zeromax007/gpdb-roaringbitmap
https://gp-docs-cn.github.io/docs/best_practices/intro.html
http://www.postgres.cn/docs/10/