前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >云数据仓库 PostgreSQL COS使用经验

云数据仓库 PostgreSQL COS使用经验

原创
作者头像
lambgong
修改2020-09-01 19:19:11
11.2K0
修改2020-09-01 19:19:11
举报
文章被收录于专栏:云数据仓库 PostgreSQL

背景

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;

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 背景
  • 方法
    • 1. 前缀匹配陷阱
      • 2. 外表加载错误格式处理
        • 3. 压缩文件支持
          • 4. windows行分隔符问题
            • 5. 权限问题
            相关产品与服务
            对象存储
            对象存储(Cloud Object Storage,COS)是由腾讯云推出的无目录层次结构、无数据格式限制,可容纳海量数据且支持 HTTP/HTTPS 协议访问的分布式存储服务。腾讯云 COS 的存储桶空间无容量上限,无需分区管理,适用于 CDN 数据分发、数据万象处理或大数据计算与分析的数据湖等多种场景。
            领券
            问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档