Greenplum常见创建表方式与说明

• 1 创建Heap表
• 2 创建AO表
  • 2.1 AO表不压缩
  • 2.2 AO表压缩
  • 2.3 AO表列存压缩 与上表的压缩方式不同
    • 2.3.1 对orientation参数进行测试
      • 2.3.1.1 创建表语句
      • 2.3.1.2 查看数据的大小
      • 2.3.1.3 使用COPY命令导入数据
      • 2.3.1.4 查看在数据库中占用的大小
      • 2.3.1.5 查看表的行数
• 3 创建HDFS外表实例
  • 3.1 创建外部表实例
  • 3.2 查看错误数据的实例
• 4 快速复制表

1 创建Heap表

drop table if exists test_head; create table test_head(id int primary key) distributed by (id);

distributed by 表示制定分布键，便于segment储存数据

2 创建AO表

2.1 AO表不压缩

drop table if exists test_ao; create table test_ao(id int) with (appendonly=true) distributed by (id);

appendonly=true是表示AO(Append-optimized)存储表的表示，参数为true和false，例如appendonly=true或appendonly=false

2.2 AO表压缩

drop table if exists test_ao; create table test_ao(id int) with (appendonly=true, compresslevel=5) distributed by (id);

compresslevel是压缩率，取值为1~9,一般选择5就足够了，值越高压缩率越高

2.3 AO表列存压缩 与上表的压缩方式不同

drop table if exists test_ao; create table test_ao(id int) with (appendonly=true,compresslevel=5, orientation=column) distributed by (id);

orientation是对列进行压缩，写法只有orientation=column

2.3.1 对orientation参数进行测试

2.3.1.1 创建表语句

创建不对列压缩的表

CREATE TABLE **********_20180810( ******** ) WITH (appendonly=true, compresstype=zlib, compresslevel=5) DISTRIBUTED BY (pripid);

创建对列压缩的表

CREATE TABLE ********_20180812( ********* ) WITH (appendonly=true, compresstype=zlib, compresslevel=5,orientation=column) DISTRIBUTED BY (pripid);

一共15个字段

2.3.1.2 查看数据的大小

$ du -sh *******_20180922.csv 48G ********_20180922.csv

2.3.1.3 使用COPY命令导入数据

$ time psql -d stagging -h 192.****.11 -p 5432 -U gpadmin -c "\COPY *******_20180810 FROM '/data/oracle-export-data/DATA20180922/*******_20180922.csv' WITH csv DELIMITER E'\001' LOG ERRORS SEGMENT REJECT LIMIT 3000 ROWS" Password for user gpadmin:

real 11m49.978s user 1m17.379s sys 0m43.668s

time psql -d stagging -h 192.****.11 -p 5432 -U gpadmin -c "\COPY *******_20180812 FROM '/data/oracle-export-data/DATA20180922/*******_20180922.csv' WITH csv DELIMITER E'\001' LOG ERRORS SEGMENT REJECT LIMIT 3000 ROWS" Password for user gpadmin:

real 12m11.227s user 1m27.575s sys 0m50.548s

在以上结果中可以看出不对列压缩用时11m49.978s，而对列压缩的用时12m11.227s，相差23S

2.3.1.4 查看在数据库中占用的大小

select pg_size_pretty(pg_relation_size('*******_20180810')); -- 14 GB

select pg_size_pretty(pg_relation_size('*******_20180812')); -- 11 GB

使用列压缩竟然缩小了3G的空间，好恐怖，23S节省3G空间，值得拥有。

2.3.1.5 查看表的行数

select count(*) from *******_20180810; -- 156784862

select count(*) from *******_20180812; -- 156784862

3 创建HDFS外表实例

3.1 创建外部表实例

CREATE EXTERNAL TABLE  *******_20180812( ****************

) LOCATION ('gphdfs://nameservice1/tmp/******_20180812/******/*') format 'text' (delimiter E'\u0001' FILL MISSING FIELDS) LOG ERRORS SEGMENT REJECT LIMIT 3000 ROWS;

EXTERNAL外表需要添加关键字

nameservice1是HDFS的HA的地址，需要先配置好

tmp/******_20180812/*******/是HDFS上的路径

delimiter分隔符是 E'\u0001'，也就是隐藏符SOH

LOG ERRORS SEGMENT REJECT说明吧错误数据放到GP默认的gp_read_error_log中

LIMIT 3000 ROWS 表示允许错误的最大的错误数，可以调大也可以调小，最小为1

3.2 查看错误数据的实例

SELECT gp_read_error_log('tableName');

错误表字段解释:

Column | Type | Modifiers ----------+--------------------------+----------- cmdtime | timestamp with time zone | --操作时间 relname | text | --表名 filename | text | --文件名 linenum | integer | --错误行号 bytenum | integer | errmsg | text | --错误信息 rawdata | text | --整行数据 rawbytes | bytea | --行大小 Distributed randomly

4 快速复制表

CREATE TABLE ********_20180814 WITH ( appendonly = TRUE, compresstype = zlib, compresslevel = 5, orientation = column ) AS SELECT * FROM **********_20180812 Distributed BY (pripid)

查看执行的执行的时间

**************

FROM ********_20180812 Distributed BY (pripid)

时间: 69.977s

受影响的行: 1,5678,4862

可以看出用时 69.977s导入1,5678,4862行的数据