Apache Hawq-使用中遇到的问题和解决方法

原创

Lynn_Yuan 2018-08-16 18:03:08 博主文章分类：hawq ©著作权

文章标签 hawq 空值 bucket_num 文章分类 数据仓库大数据

©著作权归作者所有：来自51CTO博客作者Lynn_Yuan的原创作品，请联系作者获取转载授权，否则将追究法律责任

\df 查看函数列表 \df+ 查看自定义函数

\dc 查看转换 \dc+

\l 查看数据库列表

\d table 查看表信息 \d+ table 查看表详细信息

select * from information_schema.schemata;

HAWQ空值问题: HAWQ导入数据时，默认情况下空值必须使用"\N"来表示

2.hawq load导入csv数据时，数据文件200G，启用一个gpfdist节点，花费时间1个小时40分钟，报错。原因可能是内存不足。

drop table t1;
create table t1(id int, mac varchar(20), year int) 
with (appendonly=true,orientation=parquet,compresstype=snappy, bucketnum=18)
distributed by(mac)
PARTITION BY RANGE (year)
( START (2001)INCLUSIVE END (2003)EXCLUSIVE EVERY (1),
  DEFAULT PARTITION extra );

```

ALTER TABLE t1 SPLIT DEFAULT PARTITION START (2003) INCLUSIVE END (2004) EXCLUSIVE INTO (PARTITION y2003, default partition);

报错：

NOTICE: exchanged partition "extra" of relation "t1" with relation "pg_temp_689110" NOTICE: dropped partition "extra" for relation "t1" ERROR: bucketnum requires a numeric value

解决方法： 去掉指定的bucketnum

set default_hash_table_bucket_number=9; drop table t1; create table t1(id int, mac varchar(20), year int) with (appendonly=true,orientation=parquet,compresstype=snappy) distributed by(mac) PARTITION BY RANGE (year) ( START (2001)INCLUSIVE END (2003)EXCLUSIVE EVERY (1), DEFAULT PARTITION extra );

ALTER TABLE t1 SPLIT DEFAULT PARTITION START (2003) INCLUSIVE END (2004) EXCLUSIVE INTO (PARTITION y2003, default partition); set default_hash_table_bucket_number=18;