今天说一下这个云数据仓库 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外表结构如下:

 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提供了这样的能力,例子如下:

 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外表的时候格式如下:

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外表协议,如下:

CREATE EXTENSION IF NOT EXISTS cos_ext SCHEMA public; 

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

GRANT ALL  PRIVILEGES ON PROTOCOL cos TO B;

正文完