背景
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;