KingbaseES file_dw 介绍
file_dw简介
file_fdw模块提供外部数据包装器file_fdw， 它能被用来访问服务器的文件系统中的数据文件，或者在服务器上执行程序并读取它们的输出。 数据文件或程序输出必须是能够被COPY FROM读取的格式. 详见COPY。当前只能读取数据文件。
参数设置：
-
filename
指定要被读取的文件。必须是一个绝对路径名。 必须指定filename或program， 但不能同时指定两个。
-
program
指定要执行的命令。该命令的标准输出将被读取， 就像使用COPY FROM PROGRAM一样。必须指定program 或filename，但不能同时指定两个。
注意该命令是由 shell 调用，因此如果你需要传递任何来自不可信来源的 参数给 shell 命令，你必须小心地剥离那些可能对 shell 有特殊意义的特殊字符。出于安全原因，最好使用一个固定的命令字符串，或者至少避免传递任何用户输入到其中。
-
format
指定数据的格式：text、 csv（逗号分隔值）或者binary。默认是text
-
header
指定数据是否具有一个头部行，指定文件包含标题行，其中有每一列的名称。在输出时，第一行包含来自表的列名。在输入时，第一行会被忽略。只有使用 CSV格式时才允许这个选项。。
-
delimiter
指定分隔文件每行中各列的字符。文本格式中默认是一个制表符，而CSV格式中默认是一个逗号。KingbaseES支持设置不大于8个字节的分隔符。使用binary格式时不允许这个选项。
-
quote
指定数据的引用字符，默认是双引号。这必须是一个单一的单字节字符。只有使用 CSV格式时才允许这个选项。
-
escape
指定数据的转义字符，这必须是一个单一的单字节字符。只有使用 CSV格式时才允许这个选项。
-
null
指定数据的空字符串，指定表示一个空值的字符串。文本格式中默认是 \N（反斜线-N），CSV格式中默认是一个未加引用的空串。在你不想区分空值和空串的情况下，即使在文本格式中你也可能更喜欢空串。使用binary格式时不允许这个选项。。
-
encoding
指定数据的编码，和COPY的ENCODING选项相同。
file_fdw使用
以下测试基于kingbase v8r6版本：
1.将file_fdw 安装为 一个扩展
test=# create extension file_fdw;
CREATE EXTENSION
2.创建一个外部服务接口
test=# create server ser_file_fdw foreign data wrapper file_fdw;
CREATE SERVER
查询已创建的外部服务接口
test=# \des
外部服务器列表
名称 | 拥有者 | 外部数据封装器
--------------+--------+----------------
ser_file_fdw | system | file_fdw
sysaudit_svr | system | sysaudit_fdw
(2 行记录)
3.通过program方式创建外部表
test=# CREATE FOREIGN TABLE etc_password (
test(# username TEXT,
test(# password TEXT,
test(# user_id INTEGER,
test(# group_id INTEGER,
test(# user_info TEXT
test(# ) SERVER fs OPTIONS (
test(# PROGRAM
test(# $$
test$# awk -F: 'NF && !/^[:space:]*#/ {print $1,$2,$3,$4,$5}' OFS='\037' /etc/passwd
test$# $$
test(# ,
test(# FORMAT 'csv', DELIMITER E'\037'
test(# );
CREATE FOREIGN TABLE
test=# select * from etc_password;
username | password | user_id | group_id | user_info
------------------+----------+---------+----------+-----------------------------------------------------------------
root | x | 0 | 0 | root
bin | x | 1 | 1 | bin
daemon | x | 2 | 2 | daemon
adm | x | 3 | 4 | adm
lp | x | 4 | 7 | lp
sync | x | 5 | 0 | sync
shutdown | x | 6 | 0 | shutdown
halt | x | 7 | 0 | halt
mail | x | 8 | 12 | mail
operator | x | 11 | 0 | operator
games | x | 12 | 100 | games
ftp | x | 14 | 50 | FTP User
nobody | x | 65534 | 65534 | Kernel Overflow User
systemd-coredump | x | 999 | 997 | systemd Core Dumper
systemd-network | x | 192 | 192 | systemd Network Management
systemd-resolve | x | 193 | 193 | systemd Resolver
systemd-timesync | x | 998 | 996 | systemd Time Synchronization
tss | x | 59 | 59 | Account used by the trousers package to sandbox the tcsd daemon
unbound | x | 997 | 995 | Unbound DNS resolver
polkitd | x | 996 | 994 | User for polkitd
rtkit | x | 172 | 172 | RealtimeKit
chrony | x | 995 | 993 |
dhcpd | x | 177 | 177 | DHCP server
sshd | x | 74 | 74 | Privilege-separated SSH
geoclue | x | 994 | 990 | User for geoclue
pulse | x | 171 | 171 | PulseAudio System Daemon
tcpdump | x | 72 | 72 |
dbus | x | 987 | 987 | System Message Bus
apache | x | 48 | 48 | Apache
kingbase | x | 1000 | 1000 |
test | x | 1001 | 1001 |
kdb1 | x | 1002 | 1002 |
(32 rows)
4.通过csv创建一个外部表
创建test.csv内容如下
ID ID2 NAME
2 55 single
6 58 multiple
8 53 single
根据csv文件创建外部表
test=# CREATE FOREIGN TABLE foreign_tab (
test(# "ID" character varying(50 char) NULL,
test(# "ID2" integer NULL,
test(# "NAME" character varying(50 char) NULL
test(# ) SERVER ser_file_fdw
test-# OPTIONS ( filename 'C:\Program Files\Kingbase\ES\V8\data\ECRecord.CSV',format 'csv', HEADER 'TRUE' );
CREATE FOREIGN TABLE
test=# SELECT * FROM FOREIGN_TAB;
ID | ID2 | NAME
----+-----+----------
2 | 55 | Single
6 | 58 | Multiple
8 | 53 | Single
(3 行记录)
对外部表的操作，外部表不支持insert ，update和delete操作。
1.连接查询
test=# select * from t1;
f_rowid | dingdan | yaopin
---------+----------+--------
1 | d001 | c001
3 | d003 | c003
2 | Single | c002
6 | Multiple | c006
11 | Single | Single
(5 行记录)
test=# SELECT * FROM foreign_tab f ,t1 b WHERE f.id=b.f_rowid;
ID | ID2 | NAME | f_rowid | dingdan | yaopin
----+-----+----------+---------+----------+--------
2 | 55 | Single | 2 | Single | c002
6 | 58 | Multiple | 6 | Multiple | c006
(2 行记录)
2.merge 操作 由于外部表不支持delete insert update操作，merge时外部表只能做参照表
test=# select * from t1;
f_rowid | dingdan | yaopin
---------+----------+--------
1 | d001 | c001
3 | d003 | c003
2 | Single | c002
6 | Multiple | c006
(4 行记录)
test=# select * from foreign_tab;
ID | ID2 | NAME
----+-----+----------
2 | 55 | Single
6 | 58 | Multiple
8 | 53 | Single
(3 行记录)
test=# merge into t1 a using foreign_tab b
test-# on (a."f_rowid" = b.id)
test-# when matched then update set dingdan=b.id2
test-# when not matched then insert values(b.id, b.id2,b.name);
MERGE 3
test=# select * from t1;
f_rowid | dingdan | yaopin
---------+---------+--------
1 | d001 | c001
3 | d003 | c003
2 | 55 | c002
6 | 58 | c006
8 | 53 | Single
(5 行记录)
file_fdw卸载
在ksql中运行drop命令进行卸载 如果有外部表依赖则需要加cascade
test=# drop extension file_fdw;
DROP EXTENSION
存在依赖：
test=# drop extension file_fdw;
错误: 无法删除 扩展 file_fdw 因为有其它对象倚赖它
描述: 服务器 pglog 倚赖于 外部数据封装器 file_fdw
外部表 ECRecord_new 倚赖于 服务器 pglog
外部表 ECRecord_new2 倚赖于 服务器 pglog
外部表 ECRecord_new3 倚赖于 服务器 pglog
外部表 ECRecord_new4 倚赖于 服务器 pglog
提示: 使用 DROP .. CASCADE 把倚赖对象一并删除.
test=# drop extension file_fdw cascade;
注意: 串联删除5个其它对象
描述: 递归删除 服务器 pglog
递归删除 外部表 ECRecord_new
递归删除 外部表 ECRecord_new2
递归删除 外部表 ECRecord_new3
递归删除 外部表 ECRecord_new4
DROP EXTENSION
file_fdw升级
file_fdw扩展插件通常随着KingbaseES安装包一并升级。通常情况下用户无须单独升级些插件。
