本文介绍如何使用OSS Foreign Table(OSS外表,简称OSS FDW)导入OSS中的数据并进行数据分析。
功能介绍
OSS FDW基于PG FDW(PostgreSQL Foreign Data Wrapper)框架进行开发,通过OSS FDW,您可以进行如下操作:
- 将OSS数据导入本地表(行存表或列存表)进行分析加速。
- 直接查询分析OSS的海量数据。
- OSS外表与本地表关联分析。
OSS FDW支持多种格式的数据文件,适用不同的业务场景,具体文件格式如下:
- 支持访问CSV、TEXT、JSON、JSONLINE格式的非压缩文本文件。
- 支持访问CSV、TEXT格式的GZIP压缩、标准SNAPPY压缩文本文件。
- 支持访问JSON、JSONLINE格式的GZIP压缩文本文件。
- 支持访问ORC格式的二进制文件。ORC数据类型与AnalyticDB PostgreSQL版数据类型的映射关系,请参见ORC文件数据类型对照表。
- 支持访问PARQUET格式的二进制文件。PARQUET数据类型与AnalyticDB PostgreSQL版数据类型的映射关系,请参见Parquet文件数据类型对照表。
- 支持访问AVRO格式的二进制文件。AVRO数据类型与AnalyticDB PostgreSQL版数据类型的映射关系,请参见Avro文件数据类型对照表。
准备工作
准备OSS数据
准备示例文件example.csv。
获取OSS Bucket信息
以下内容将指导您获取Bucket名称、文件的路径、Endpoint(地域节点)和Bucket域名。
获取AccessKey ID和AccessKey Secret
获取AccessKey ID和AccessKey Secret的具体操作,请参见获取AccessKey。
创建OSS Server
使用CREATE SERVER语句创建OSS Server,指定需要访问的OSS服务端。更多关于CREATE SERVER的介绍,请参见CREATE SERVER。
语法
CREATE SERVER server_name
FOREIGN DATA WRAPPER fdw_name
[ OPTIONS ( option 'value' [, ... ] ) ]
参数选项
参数 | 类型 | 是否必填 | 说明 |
---|---|---|---|
server_name | 字符串 | 是 | OSS Server的名称。 |
fdw_name | 字符串 | 是 | 管理服务器的外部数据容器的名称,固定为oss_fdw。 |
OPTIONS参数选项请参见下表:
参数 | 类型 | 是否必填 | 说明 |
---|---|---|---|
endpoint | 字符串 | 是 | Bucket域名,获取方法请参见准备工作。 |
bucket | 字符串 | 否 | 数据文件所属的Bucket的名称,获取方法请参见准备工作。
说明
|
speed_limit | 数值 | 否 | 触发超时的数据量。单位为字节,默认值为1024字节。
需要与speed_time参数配合使用。 说明 默认情况下,如果连续90秒传输的数据量少于1024字节,则会触发超时。具体信息,请参见OSS SDK 错误处理。
|
speed_time | 数值 | 否 | 触发超时的时间。单位为秒,默认值为90秒。
需要与speed_limit参数配合使用。 说明 默认情况下,如果连续90秒传输的数据量少于1024字节,则会触发超时。具体信息,请参见OSS SDK 错误处理。
|
connect_timeout | 数值 | 否 | 连接超时时间。单位为秒,默认值为10秒。 |
dns_cache_timeout | 数值 | 否 | DNS超时时间。单位为秒,默认值为60秒。 |
示例
CREATE SERVER oss_serv
FOREIGN DATA WRAPPER oss_fdw
OPTIONS (
endpoint 'oss-cn-********.aliyuncs.com',
bucket 'adb-pg'
);
您也可以通过ALTER SERVER语句修改OSS Server的配置,使用方法,请参见ALTER SERVER。
修改OSS Server配置示例如下:
- 修改OSS Server中的某个参数:
ALTER SERVER oss_serv OPTIONS(SET endpoint 'oss-cn-********.aliyuncs.com');
- 添加OSS Server中的某个参数:
ALTER SERVER oss_serv OPTIONS(ADD connect_timeout '20');
- 删除OSS Server中的某个参数:
ALTER SERVER oss_serv OPTIONS(DROP connect_timeout);
您也可以通过DROP SERVER语句删除该OSS Server,使用方法,请参见DROP SERVER。
创建OSS User Mapping
创建OSS Server后,您还需要创建一个访问OSS Server的用户。您可以使用CREATE USER MAPPING语句创建OSS User Mapping,用于定义AnalyticDB PostgreSQL版数据库用户与访问OSS Server用户的映射关系。更多介绍,请参见CREATE USER MAPPING。
语法
CREATE USER MAPPING FOR { username | USER | CURRENT_USER | PUBLIC }
SERVER servername
[ OPTIONS ( option 'value' [, ... ] ) ]
参数选项
参数 | 类型 | 是否必填 | 说明 |
---|---|---|---|
username | 字符串 | 是,四选一 | 指定映射的AnalyticDB PostgreSQL版实例的用户名。 |
USER | 字符串 | 映射当前的AnalyticDB PostgreSQL版实例的用户名。 | |
CURRENT_USER | 字符串 | ||
PUBLIC | 字符串 | 匹配所有AnalyticDB PostgreSQL版实例的用户名,包括以后创建的用户。 | |
servername | 字符串 | 是 | OSS Server的名称。 |
OPTIONS参数选项请参见下表:
参数 | 类型 | 是否必填 | 说明 |
---|---|---|---|
id | 字符串 | 是 | AccessKey ID,获取方法,请参见获取AccessKey。 |
key | 字符串 | 是 | AccessKey Secret,获取方法,请参见获取AccessKey。 |
示例
CREATE USER MAPPING FOR PUBLIC
SERVER oss_serv
OPTIONS (
id 'LTAI5t7Ge***************',
key 'FikziJd2La*******************'
);
您也可以通过DROP USER MAPPING语句删除该用户,使用方法,请参见DROP USER MAPPING。
创建OSS FDW
拥有OSS Server和访问OSS Server的用户后,您可以开始创建OSS FDW。您可以使用CREATE FOREIGN TABLE语句创建OSS FDW,更多信息,请参见CREATE FOREIGN TABLE。
语法
CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
[, ... ]
] )
SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]
参数选项
参数 | 类型 | 是否必填 | 说明 |
---|---|---|---|
table_name | 字符串 | 是 | OSS FDW名称。 |
column_name | 字符串 | 是 | 列名。 |
data_type | 字符串 | 是 | 该列的数据类型。 |
OPTIONS参数选项请参见下表:
参数 | 类型 | 是否必填 | 说明 |
---|---|---|---|
filepath | 字符串 | 是,三选一 | OSS中包含路径的文件名称。
如果使用filepath参数,则仅选择指定文件。 |
prefix | 字符串 | 指定数据文件对应路径名的前缀,不支持正则表达式,仅是匹配前缀。
如果使用prefix参数,则会选择含有这一前缀的所有OSS文件,例如:
|
|
dir | 字符串 | OSS中的目录路径。目录路径需要以/结尾,例如test/mydir/。
如果使用dir参数,会选择虚拟目录下的所有文件,但不包括它的子目录和子目录下的文件。 |
|
bucket | 字符串 | 否 | 数据文件所属的Bucket的名称,获取方法请参见准备工作。
说明
|
format | 字符串 | 是 | 文件格式,取值范围如下:
|
filetype | 字符串 | 否 | 文件类型,取值范围如下:
说明
|
log_errors | 布尔型 | 否 | 是否将错误记录到日志文件。默认值为false。更多信息,请参见容错机制。
说明 该参数仅适用于CSV和TEXT格式的文件。
|
segment_reject_limit | 数值 | 否 | 异常中止任务(error abort)的数量。
包含%时表示错误行百分比,不包含%时表示错误行数。例如:
说明 该参数仅适用于CSV和TEXT格式的文件。
|
header | 布尔型 | 否 | 源文件中字段名是否包含header行,取值如下:
说明 该参数仅适用于CSV格式的文件。
|
delimiter | 字符串 | 否 | 字段分隔符,仅允许设置为单字节字符。
说明 该参数仅适用于CSV和TEXT格式的文件。
|
quote | 字符串 | 否 | 字段引号,仅允许设置为单字节字符。默认为双引号(")。
说明 该参数仅适用于CSV格式的文件。
|
escape | 字符串 | 否 | 声明。默认为双引号(")。
说明 该参数仅适用于CSV格式的文件。
|
null | 字符串 | 否 | 指定文件中的NULL字符串。
说明 该参数仅适用于CSV和TEXT格式的文件。
|
encoding | 字符串 | 否 | 指定数据文件编码。默认情况下为客户端编码。
说明 该参数仅适用于CSV和TEXT格式的文件。
|
force_not_null | 布尔型 | 否 | 是否声明字段的值不匹配空字符串,取值如下:
说明 该参数仅适用于CSV和TEXT格式的文件。
|
force_null | 布尔型 | 否 | 空字符串处理方法,取值如下:
说明 该参数仅适用于CSV和TEXT格式的文件。
|
示例
CREATE FOREIGN TABLE ossexample (
date text,
time text,
open float,
high float,
low float,
volume int
) SERVER oss_serv OPTIONS (dir 'dir_oss_adb/', format 'csv');
- 方法一:
EXPLAIN VERBOSE SELECT * FROM <OSS FDW表名>;
- 方法二:
SELECT * FROM get_oss_table_meta('<OSS FDW表名>');
您也可以通过DROP FOREIGN TABLE语句删除该OSS FDW,使用方法,请参见DROP FOREIGN TABLE。
导入OSS数据到本地表
查询分析OSS数据
查询OSS FDW外表的数据与查询本地表数据方法一样,常见查询如下:
- 键值过滤查询,示例如下:
SELECT * FROM ossexample WHERE volume = 5;
- 聚合查询,示例如下:
SELECT count(*) FROM ossexample WHERE volume = 5;
- 过滤、分组和LIMIT查询,示例如下:
SELECT low, sum(volume) FROM ossexample GROUP BY low ORDER BY low limit 5;
OSS FDW外表与本地表关联分析
容错机制
OSS FDW通过log_errors和segment_reject_limit参数提供容错功能,原始数据中的错误数据不会导致OSS外表扫描停止。
关于log_errors和segment_reject_limit参数的信息,请参见创建OSS FDW。
- 创建支持容错的OSS FDW外表,示例如下:
CREATE FOREIGN TABLE oss_error_sales (id int, value float8, x text) SERVER oss_serv OPTIONS (log_errors 'true', -- 记录错误行信息 segment_reject_limit '10', -- 错误行数不得超过10行,否则会停止扫描。 dir 'error_sales/', -- 指定外表匹配的OSS文件目录 format 'csv', -- 指定按csv格式解析文件 encoding 'utf8'); -- 指定文件编码
- 查看错误行的日志,查看方式如下:
SELECT * FROM gp_read_error_log('oss_error_sales');
- 删除错误行的日志,删除方式如下:
SELECT gp_truncate_error_log('oss_error_sales');