开发者社区> 曾文旌> 正文

从 OSS 装载数据到 PostgreSQL

简介: oss_fdw 在阿里云上,支持通过 oss_fdw 并行装载数据到 PostgreSQL 和 PPAS 中 oss_fdw 参数 oss_fdw 和其他 fdw 的接口一样,提供对外部数据源 oss 的数据封装,用户可以使用 oss_fdw 像一张表一样读取 oss 上的存放的文件。 和其
+关注继续查看

oss_fdw

在阿里云上,支持通过 oss_fdw 并行装载数据到 PostgreSQL 和 PPAS 中

oss_fdw 参数

oss_fdw 和其他 fdw 的接口一样,提供对外部数据源 oss 的数据封装,用户可以使用 oss_fdw 像一张表一样读取 oss 上的存放的文件。
和其他 fdw 一样,oss_fdw 提供独有的数个参数用于连接和解析 oss 上的文件数据。

和 oss 相关参数有

1. ossendpoint 参数,是内网访问oss的地址,也叫 host

2. id oss 账号 id

3. key oss 账号 key

4. bucket ossbucket,需要创建 oss 账号后分配

5. filepath oss 中带路径的文件名
  5.1 文件名包含文件路径,但不包含 bucket
  5.2 该参数匹配 oss 对应路径上的多个文件,支持将他们装载到数据库
  5.3 文件命名为 filepath 和 filepath.x 支持被导入到数据库,x 要求从 1 开始,且是连续的
  5.4 例 filepath filepath.1 filepath.2 filepath.3 filepath.5 前4个文件会被匹配和导入,但是 filepath.5 不会。
  
6. dir oss 中的虚拟文件目录
    6.1 dir 需要以 / 结尾
    6.2 dir 制定的虚拟文件目录中的所有文件(不包含子文件夹和子文件夹下的文件)都会被匹配和导入到数据库。

需要注意

1. 前4个参数 ossendpoint id key bucket 放在server对象中
2. filepath 和 dir 需要在 FDW 的 OPTIONS 参数参数中指定
3. filepath 和 dir 必须指定两个参数之一,且不能同时指定
4. 各参数的值使用‘’引起来,不能包括无用的空格

其他参数

1. format 
    指定文件的格式,目前只支持 csv

2. encoding 
    文件中数据的编码格式,支持常见 pg 编码,如 utf8

3. parse_errors 
    容错模式解析,按照行为单位,忽略文件分析过程中发生的错误
    
4. delimiter
   制定列的分割符
   
5. quote 
   指定文件的引用字符
   
6. escape 
    指定文件的逃逸字符
    
7. null 
    指定匹配对应字符串的列为 null
    例如 null 'test',即列值为 ‘test’ 的字符串为 null
    
8. force_not_null
    制定一列为多列的值不是 null
    例 force_not_null ‘id’,即表中 id 列如果是 null,替换成空字符串

用例

# 创建插件
create extension oss_fdw;

# 创建 server 
CREATE SERVER ossserver FOREIGN DATA WRAPPER oss_fdw OPTIONS 
     (host 'oss-cn-hangzhou-zmf.aliyuncs.com' , id 'xxx', key 'xxx',bucket 'mybucket');

# 创建 oss 外部表
CREATE FOREIGN TABLE ossexample 
    (date text, time text, open float,
     high float, low float, volume int) 
     SERVER ossserver 
     OPTIONS ( filepath 'osstest/example.csv', delimiter ',' , 
         format 'csv', encoding 'utf8', PARSE_ERRORS '100');
        
# 创建表,数据就装载到这张表中
create table example
        (date text, time text, open float,
         high float, low float, volume int);

# 数据并行的从 ossexample 装载到 example 中。
insert into example select * from ossexample;

# 可以看到
# oss_fdw 能够正确估计 oss 上的文件大小,正确的规划查询计划。
explain insert into example select * from ossexample;
                             QUERY PLAN                              
---------------------------------------------------------------------
 Insert on example  (cost=0.00..1.60 rows=6 width=92)
   ->  Foreign Scan on ossexample  (cost=0.00..1.60 rows=6 width=92)
         Foreign OssFile: osstest/example.csv.0
         Foreign OssFile Size: 728
(4 rows)
        

oss_fdw 使用注意

oss_fdw 打开了 oss 到 PostgreSQL 和 PPAS 的数据通道,用户可以把数据放到廉价的oss中,再导入到 PostgreSQL 或 PPAS 中。

1. oss_fdw 是在 PostgreSQL FOREIGN TABLE 框架下开发的外部表插件。
2. 数据导入的性能和 PostgreSQL 集群的资源(CPU IO MEM MET)相关,也和 OSS 相关。
3. 为了保证数据导入的性能 ossprotocol 中 ossendpoint 的需要匹配 PostgreSQL 云上所在 Region。相关信息请参考下面的链接。

id 和 key 隐藏

CREATE SERVER中的id和key信息如果不做任何处理,那么用户将可以 select * from pg_foreign_server看到明文信息,这样将会暴露用户的id和key。
为了对id和key隐藏,我们通过对id和key进行对称加密实现(不同的实例使用不同的秘钥,最大限度保护用户信息),但是不能使用类似GP那样,增加一个数据类型,因为会不兼容老实例。

最终的加密后的信息如下:

postgres=# select * from pg_foreign_server ;
  srvname  | srvowner | srvfdw | srvtype | srvversion | srvacl |                                                                              srvoptions

-----------+----------+--------+---------+------------+--------+------------------------------------------------------------------------------------------------------------------------------------
----------------------------------
 ossserver |       10 |  16390 |         |            |        | {host=oss-cn-hangzhou-zmf.aliyuncs.com,id=MD5xxxxxxxx,key=MD5xxxxxxxx,bucket=067862}

加密后的信息将会以MD5开头(总长度为len%8==3),这样导出之后再导入不会再次加密,但是用户不能创建MD5开头的key和id

参考链接

  1. oss endpiint 信息
  2. [oss help 页面] [2]
  3. [PostgreSQL CREATE FOREIGN TABLE 手册] [3]

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
POS机数据泄露已蔓延至云端
本文讲的是POS机数据泄露已蔓延至云端,最新一起针对POS机的黑客事件,入侵的是拥有3.8万企业客户的云销售终端系统公司Lightspeed。
1539 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
28093 0
PostgreSQL 数据类型
本章节,我们将讨论 PostgreSQL 的数据类型,数据类型是我们再创建表的时候为每个字段设置的。 设置数据类型的好处: PostgreSQL提 供了丰富的数据类型。用户可以使用 CREATE TYPE 命令在数据库中创建新的数据类型。PostgreSQL 的数据类型有很多种,下面我们具体来说明。 数值类型 数值类型由 2 字节、4 字节或 8 字节的整数以及 4 字节或 8 字节的浮点数和可选精度的十进制数组成。 下表列出了可用的数值类型。
44 0
js中请求数据的$post和$ajax区别(同步和异步问题)
$.post和$.Ajax都为页面上向后台发送请求,请求数据1.post 因为post默认为异步请求,可是有时候我们会发现,本来要求请求马上出现,可是异步会导致后面突然再执行,这样就出很多问题 2.Ajax 最原始的Ajax,可以控制同步或者异步,属性:async设置为false,代表同步;async设置为true,代表异步 语法如下: $.
970 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
20163 0
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
23538 0
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 5 章 数据定义_5.11. 外部数据
5.11. 外部数据 PostgreSQL实现了部分的SQL/MED规定,允许我们使用普通SQL查询来访问位于PostgreSQL之外的数据。这种数据被称为外部数据(注意这种用法不要和外键混淆,后者是数据库中的一种约束)。
1002 0
+关注
曾文旌
个人简介 曾文旌 阿里巴巴 RDS for PostgreSQL 数据库专家 目前在阿里云RDS 数据库内核服务组,负责PostgreSQL相关业务.
17
文章
3
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载