云数据仓库 PostgreSQL COS使用经验

发布于

背景

Snova云数仓支持直接分析或者导入腾讯对象存储COS里的数据，本文列举了在使用COS场景下的一些技巧和注意事项。

方法

1. 前缀匹配陷阱

如果我们有2张表需要从COS导入，分别叫t_user和t_user_all，那我们在COS上的目录名称很可能是{bucket_name}/t_user和{bucket_name}/t_user_all。这种情况我们创建COS外表的时候会有一个陷阱，就是前缀匹配。

如果我们的COS外表结构如下：

代码语言：txt
复制
 CREATE READABLE EXTERNAL TABLE t_user (
 a varchar(30) , 
 b int , 
 c varchar(30))
 LOCATION('cos://{bucket_name}/t_user secretKey=xx secretId=yy')
 FORMAT 'CSV';

那么在通过INSERT语句导入COS数据的时候，Snova会采用前缀匹配原则，将t_user和t_user_all的数据都导入到t_user表中（也就是以t_user开头的），这里的关键就是LOCATION里地址是否以/结尾，如上语法

LOCATION('cos://{bucket_name}/t_user secretKey=xx secretId=yy') t_user后没有反斜杠，采用前缀匹配

如果不想使用前缀匹配，也很简单，就是加上反斜杠即可

LOCATION('cos://{bucket_name}/t_user/ secretKey=xx secretId=yy') t_user后有反斜杠，只读取t_user下的数据

2. 外表加载错误格式处理

通常Snova on COS的场景下，COS上的数据都是业务系统上报的数据，这些数据的格式必须与Snova中表的Schema一致。但实际操作中，可能会出现上报数据格式错误的情况，比如一个整形的字段，含有非法字符等情况。

对于这种情况，用户可能会要求系统能有一定的容错程度，而不是整个数据都无法导入，Snova提供了这样的能力，例子如下：

代码语言：txt
复制
 CREATE READABLE EXTERNAL TABLE test_cos(
 a varchar(30) , 
 b int , 
 c varchar(30))
 LOCATION('cos://cos-url/dir/ secretKey=xx secretId=yy')
 FORMAT 'TEXT' ( DELIMITER ',')
 LOG ERRORS
 SEGMENT REJECT LIMIT 10;

说明：

  • 这里的SEGMENT REJECT LIMIT 10，代表的含义是每个计算节点最多能容忍10条数据出错，如果有超过10条数据，则整个导入任务就会失败。
  • 可以使用SELECT gp_read_error_log('table_name');查看错误记录。
  • 具体语法格式可参见Handling Load Errors。

3. 压缩文件支持

使用压缩文件，可以减少COS上的存储量，并且在Snova on COS的场景下，压缩文件也能减少网络传递带来的耗时。

目前Snova支持gz格式，在建表的时候不需要特别的语法，Snova根据文件后缀可以识别压缩文件。

4. windows行分隔符问题

文件的行分隔符如果是windows上的0D0A,就是\r\n，在创建cos外表的时候格式如下：

代码语言：txt
复制
CREATE READABLE EXTERNAL TABLE test_windows (
a varchar(32), 
b varchar(32))
LOCATION('cos://cos_url/dir/ secretKey=xx secretId=yy')
FORMAT 'csv' (NEWLINE 'CRLF');

说明：

  • 就是在行尾要加(NEWLINE 'CRLF');
  • 这里只能读取含windows换行符的数据，不能写入

5. 权限问题

如果A用户在某个DB下创建cos外表协议，如下：

代码语言：txt
复制
CREATE EXTENSION IF NOT EXISTS cos_ext SCHEMA public;

而B用户希望创建外表，则A用户需要给B用户授权

代码语言：txt
复制
GRANT ALL  PRIVILEGES ON PROTOCOL cos TO B;
#云数据仓库 PostgreSQL