file_fdw模块提供外部数据包装器file_fdw, 它能被用来访问服务器的文件系统中的数据文件,或者在服务器上执行程序并读取它们的输出。 数据文件或程序输出必须是能够被COPY FROM读取的格式, 详见COPY。当前只能读取数据文件。
在ksql中运行:
create extension file_fdw;
用这个包装器创建的一个外部表可以有下列选项:
filename
指定要被读取的文件。必须是一个绝对路径名。 必须指定filename或program, 但不能同时指定两个。
program
指定要执行的命令。该命令的标准输出将被读取, 就像使用COPY FROM PROGRAM一样。必须指定program 或filename,但不能同时指定两个。
format
指定数据的格式,和COPY的FORMAT选项相同。
header
指定数据是否具有一个头部行,和COPY的HEADER选项相同。
指定数据的定界符字符,和COPY的DELIMITER选项相同。
quote
指定数据的引用字符,和COPY的QUOTE选项相同。
指定数据的转义字符,和COPY的ESCAPE选项相同。
null
指定数据的空字符串,和COPY的NULL选项相同。
encoding
指定数据的编码,和COPY的ENCODING选项相同。
注意虽然COPY允许诸如HEADER的选项不用一个相应的值指定, 但是外部表选项语法要求在所有情况下都出现一个值。要激活通常写入没有值的 COPY选项,你可以传递值 TRUE,因为所有这些选项都是布尔值。
使用这个包装器创建的表的一列可以具有下列选项:
force_not_null
这是一个布尔选项。如果为真,它指定该列的值不应该与空字符串匹配(也就是表级别的null选项)。这和把该列放在COPY的FORCE_NOT_NULL选项中具有相同的效果。
force_null
这是一个布尔选项。如果为真,它指定匹配空值字符串的列值会被返回为NULL, 即使该值被引号引用。如果没有这个选项,只有匹配空值字符串的未被引用的值会被返回为 NULL。这和在COPY的FORCE_NULL 选项中列出该列有同样的效果。
COPY的FORCE_QUOTE选项当前不被file_fdw支持。
这些选项只能为一个外部表及其列指定,而不能在file_fdw外部数据包装器的选项中指定,也不能在使用该包装器的服务器或者用户映射的选项中指定。
出于安全原因,改变表级别的选项要求超级用户特权或 具有默认角色pg_read_server_files(使用文件名)或 默认角色pg_execute_server_program (使用程序)的权限 只有特定用户能够控制读取哪个文件或者运行哪个程序。 原则上普通用户可以被允许改变其它选项,但是当前还不支持这样做。
当指定program选项时,请记住,选项字符串是通过shell执行的。 如果想传递任何参数到来自不受信任的源的命令, 必须小心去掉或转义任何对shell来说可能有特殊含义的字符。安全起见,最好使用固定的命令字符串, 或者至少避免传递任何用户输入。
对于一个使用file_fdw的外部表,EXPLAIN显示要读取的文件名或要运行的程序。对于文件来说,除非指定COSTS OFF,否则文件尺寸(以字节计)也会被显示。
一种file_fdw的用法是把可用的 PostgreSQL 活动日志变成一个表用于查询。要这样做,首先你必须正在将日志记录到一个 CSV 文件,这里我们称其为pglog.csv。首先,将file_fdw安装为一个扩展:
CREATE EXTENSION file_fdw;
然后创建一个外部服务器
CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
现在你已经准备好创建外部数据表。使用CREATE FOREIGN TABLE命令,你将需要为该表定义列、CSV 文件名以及格式。
CREATE FOREIGN TABLE pglog ( log_time timestamp(3) with time zone, user_name text, database_name text, process_id integer, connection_from text, session_id text, session_line_num bigint, command_tag text, session_start_time timestamp with time zone, virtual_transaction_id text, transaction_id bigint, error_severity text, sql_state_code text, message text, detail text, hint text, internal_query text, internal_query_pos integer, context text, query text, query_pos integer, location text, application_name text ) SERVER pglog OPTIONS ( filename '/home/josh/data/log/pglog.csv', format 'csv' );
就是这样了 — 现在你能够直接查询你的日志了。当然,在生产中你会需要定义一些方法来处理日志轮转。
在ksql中运行
drop extension file_fdw;
file_fdw扩展插件通常随着KingbaseES安装包一并升级。通常情况下用户无须单独升级些插件。
fix_security_hole是KingbaseES的一个扩展插件,主要用于禁止数据库通过copy执行操作系统shell命令。
插件名为 fix_security_hole
插件版本 V1.0
在使用 fix_security_hole 之前,我们需要将他添加到 kingbase.conf 文件的 shared_preload_libraries 中,并重启 KingbaseES 数据库。
示例:
shared_preload_libraries = 'fix_security_hole'
close_copy_crogram
启动禁止copy执行操作系统shell命令,取值范围为 on 或者 off,缺省为 on。
show close_copy_crogram; close_copy_crogram -------------------- on (1 row) \c - system alter system set close_copy_crogram = off; select sys_reload_conf(); show close_copy_crogram; close_copy_crogram -------------------- off (1 row)
KingbaseES加载 fix_security_hole 插件后,超级用户将无法通过copy执行操作系统的shell命令。
示例:
--功能为关闭状态时 create table t1 (a text); copy t1 from program 'ifconfig'; COPY 51 .. code:: --功能为开启状态时 create table t1 (a text); copy t1 from program 'ifconfig'; 错误: not support Copy_To/From_Porgram feature
修改 kingbase.conf 文件中 shared_preload_libraries
参数后重启数据库。
示例:
shared_preload_libraries = ''
fix_security_hole扩展插件通常随着KingbaseES安装包一并升级。通常情况下用户无须单独升级这些插件。
ftutils是一个KingbaseES的扩展,主要用于从存储流版式文件的blob类型字段中抽取文本内容。其中blob类型字段内容可以包括 pdf、doc、docx、wps、xls、xlsx、ppt和pptx格式文件。blob类型只在Oracle模式下支持,因此ftutils扩展也仅支持oracle模式.
在使用ftutils之前,需要将他添加到kingbase.conf文件的shared_preload_libraries中,并重启KingbaseES数据库。
shared_preload_libraries = 'ftutils' # (change requires restart)
使用KSQL创建插件
CREATE EXTENSION ftutils;
无需配置任何参数
ftutils插件提供了extracttext函数用于抽取存储在blob类型字段内的文件内容。extracttext函数接受一个代表文件内容的blob类型参数,返回抽取的text类型文本内容。
CREATE TABLE tab (title text, body blob); INSERT INTO tab VALUES ('test.doc', blob_import('/home/test/data.doc')); SELECT title, length(extracttext(body),null) FROM tab;
由于电子文档内容抽取速度较慢,为提高全文检索性能,可以在表中添加存储列,用于存储内容抽取结果或者词位列表。
方案一 CREATE EXTENSION zhparser; CREATE TEXT SEARCH CONFIGURATION zhparsercfg (PARSER = zhparser); ALTER TEXT SEARCH CONFIGURATION zhparsercfg ADD MAPPING FOR n,v,a,i,e,l WITH simple; CREATE EXTENSION ftutils; CREATE TABLE tab (title text, body blob); ALTER TABLE tab ADD COLUMN content text GENERATED ALWAYS AS (extracttext(body,null)) STORED; CREATE INDEX tab_idx ON tab USING GIN (to_tsvector('zhparsercfg', content)); INSERT INTO tab VALUES ('test.doc', blob_import('/home/test/data.doc')); SELECT title FROM tab WHERE to_tsvector('zhparsercfg', content) @@ to_tsquery('日志');
方案二 CREATE EXTENSION zhparser; CREATE TEXT SEARCH CONFIGURATION zhparsercfg (PARSER = zhparser); ALTER TEXT SEARCH CONFIGURATION zhparsercfg ADD MAPPING FOR n,v,a,i,e,l WITH simple; CREATE EXTENSION ftutils; CREATE TABLE tab (title text, body blob); ALTER TABLE tab ADD COLUMN tab_idx_col tsvector GENERATED ALWAYS AS (to_tsvector('zhparsercfg', extracttext(body,null))) STORED; CREATE INDEX tab_idx ON tab USING GIN (tab_idx_col); INSERT INTO tab VALUES ('test.doc', blob_import('/home/test/data.doc')); SELECT title FROM tab WHERE tab_idx_col @@ to_tsquery('日志');
1)在数据库默认扩展加载机制下,在一个会话中创建扩展以后,在新的会话开始后并不是立即加载扩展动态库,而是直到首次调用扩展中的接口时才会加载扩展动态库,导致在这之前在新会话中设置扩展参数无效。解决方法是修改数据库配置文件中的shared_preload_libraries或者session_preload_libraries两个参数之一,使参数值包括ftutils,即可在新会话开始后立即加载ftutils扩展动态库,并设置扩展参数。
2) 基于前面的全文检索联合使用方案,在系统内存较少的环境中,需要限制并行插入数据的会话进程数量,以免系统内存被耗尽。
DROP EXTENSION ftutils;
ALTER EXTENSION UPDATE ftutils TO '2.0';
ftutilx是一个KingbaseES的扩展,主要用于从存储流版式文件的blob类型字段中抽取文本内容。其中blob类型字段内容可以包括 pdf、doc、docx、wps、xls、xlsx、ppt和pptx格式文件。ftutilx插件不支持加密文件格式。
在使用ftutilx之前,需要将他添加到kingbase.conf文件的shared_preload_libraries中,并重启KingbaseES数据库。
shared_preload_libraries = 'ftutilx' # (change requires restart)
使用KSQL创建插件:
CREATE EXTENSION ftutilx;
ftutilx.max_string_length
抽取结果最大长度,默认值128M,该参数设置后立即生效。
ftutilx.jvm_option_string
JVM初始化参数,默认值"-Xmx1024m,-Xms1024m,-Xmn256m,-XX:MetaspaceSize=64m,-XX:MaxMetaspaceSize=128m,-XX:CompressedClassSpaceSize=256m",该参数只在会话进程中首次调用extracttext函数创建JVM时生效,再次设置该参数不再有效。
在数据库默认扩展加载机制下,在一个会话中创建扩展以后,在新的会话开始后并不是立即加载扩展动态库,而是直到首次调用扩展中的接口时才会加载扩展动态库,导致在这之前在新会话中设置扩展参数无效。 解决方法是修改数据库配置文件中的shared_preload_libraries或者session_preload_libraries两个参数之一,使参数值包括ftutilx,即可在新会话开始后立即加载ftutilx扩展动态库,并设置扩展参数。
ftutilx插件提供了extracttext函数用于抽取存储在blob类型字段内的文件内容。extracttext函数接受一个代表文件内容的blob类型参数,返回抽取的text类型文本内容。
CREATE TABLE tab (title text, body blob); INSERT INTO tab VALUES ('test.doc', blob_import('/home/test/data.doc')); SELECT title, length(extracttext(body)) FROM tab;
由于电子文档内容抽取速度较慢,为提高全文检索性能,可以在表中添加存储列,用于存储内容抽取结果或者词位列表。
方案一 CREATE EXTENSION zhparser; CREATE TEXT SEARCH CONFIGURATION zhparsercfg (PARSER = zhparser); ALTER TEXT SEARCH CONFIGURATION zhparsercfg ADD MAPPING FOR n,v,a,i,e,l WITH simple; CREATE EXTENSION ftutilx; CREATE TABLE tab (title text, body blob); ALTER TABLE tab ADD COLUMN content text GENERATED ALWAYS AS (extracttext(body)) STORED; CREATE INDEX tab_idx ON tab USING GIN (to_tsvector('zhparsercfg', content)); INSERT INTO tab VALUES ('test.doc', blob_import('/home/test/data.doc')); SELECT title FROM tab WHERE to_tsvector('zhparsercfg', content) @@ to_tsquery('日志');
方案二 CREATE EXTENSION zhparser; CREATE TEXT SEARCH CONFIGURATION zhparsercfg (PARSER = zhparser); ALTER TEXT SEARCH CONFIGURATION zhparsercfg ADD MAPPING FOR n,v,a,i,e,l WITH simple; CREATE EXTENSION ftutilx; CREATE TABLE tab (title text, body blob); ALTER TABLE tab ADD COLUMN tab_idx_col tsvector GENERATED ALWAYS AS (to_tsvector('zhparsercfg', extracttext(body))) STORED; CREATE INDEX tab_idx ON tab USING GIN (tab_idx_col); INSERT INTO tab VALUES ('test.doc', blob_import('/home/test/data.doc')); SELECT title FROM tab WHERE tab_idx_col @@ to_tsquery('日志');
1) ftutilx需要依赖于jre-1.8.0运行时环境,部署后需要设置LD_LIBRARY_PATH系统环境变量包含jre-1.8.0的libjvm.so路径。
2) ftutilx.max_string_length 参数用于配置抽取结果的最大长度,但由于tsvector目前最大支持(1M-1),所以extracttext结合to_tsvector使用时,分词结果大小不能超过(1M-1)。
3) ftutilx需要创建JVM,JVM会占用较多内存。虽然调整ftutilx.jvm_option_string的-Xmx可以限制JVM的内存占用,但过小的-Xmx值会导致大文件解析时JVM发生内存不足异常。
4) 基于前面的全文检索联合使用方案,在系统内存较少的环境中,需要限制并行插入数据的会话进程数量,以免系统内存被耗尽。
DROP EXTENSION ftutilx;
ALTER EXTENSION UPDATE ftutilx TO '2.0';
fuzzystrmatch提供了两个字符串之间的编辑距离的相关函数,即由一个转换成另一个所需的最少编辑操作次数。
插件名为fuzzystrmatch
插件版本 V1.0
使用时需要create extension fuzzystrmatch。
示例:
create extension fuzzystrmatch;
无需配置任何参数
计算两个字符串之间编辑距离。
语法
levenshtein(t1 text, t2 text ) RETURNS int;
参数
t1
源字符串。
t2
目标字符串。
示例
TEST=# select levenshtein('text','tst'); levenshtein ------------- 2 (1 行记录) TEST=# select levenshtein('text','test'); levenshtein ------------- 1 (1 行记录) TEST=# select levenshtein('text','text'); levenshtein ------------- 0 (1 行记录)
计算两个字符串之间的编辑距离。
语法
levenshtein_with_costs(t1 text, t2 text, i1 int, i2 int, i3 int) RETURNS int;
参数
t1
源字符串。
t2
目标字符串。
i1
insert的代价权重。
i2
replace的代价权重。
i3
delete的代价权重。
示例
TEST=# select levenshtein('tet','text', 1 ,2 ,3); levenshtein ------------- 1 (1 行记录) TEST=# select levenshtein('tet','text', 2 ,2 ,3); levenshtein ------------- 2 (1 行记录) TEST=# select levenshtein('tet','text', 2 ,2 ,3); levenshtein ------------- 2 (1 行记录)
求字符之间编码距离与大于指定的值的最小整数值之间的最小值。
语法
levenshtein_less_equal(t1 text, t2 text, i int) RETURNS int;
参数
t1
源字符串。
t2
目标字符串。
i
参与比较的值。
示例
SELECT levenshtein_less_equal('extensive', 'exhaustive', 2); levenshtein_less_equal ------------------------ 3 (1 row) SELECT levenshtein_less_equal('extensive', 'exhaustive', 4); levenshtein_less_equal ------------------------ 4 (1 row)
与上述函数相似,增加如levenshtein_with_costs一样的代价花销。
计算字符串的metaphone键。
语法
metaphone(text, int) returns text。
语法
text
参与计算的字符串。
int
参与运算的参考键值。
示例
SELECT metaphone('GUMBO', 4); metaphone ----------- KM (1 row)
生成字符串的soundex键。
语法
soundex(text) rerturns text。
参数
text
参与计算的字符串。
示例
TEST=# select soundex('A500'); soundex --------- A000 (1 行记录) TEST=# select soundex('A529sd800'); soundex --------- A230 (1 行记录) TEST=# select soundex('A529sds800'); soundex --------- A232 (1 行记录) TEST=# select soundex('hello world'); soundex --------- H464 (1 行记录)
获得两个对比的字符串转换为soundex值后匹配的位数。
语法
difference(t1 text, t2 text) RETURNS int;
参数
t1
参与计算的字符串1。
t2
参与计算的字符串2。
示例
TEST=# select difference('ab', 'as'); difference ------------ 3 (1 行记录) TEST=# select difference('ab', 'ab'); difference ------------ 4 (1 行记录)
获取text的dmetaphone。
语法
dmetaphone(text) returns text。
参数
text
参与计算的text字符串。
示例
TEST=# select dmetaphone('tetx'); dmetaphone ------------ TTKS (1 行记录)
不需要fuzzystrmatch插件只需要卸载插件即可。
示例
drop extension fuzzystrmatch;
fuzzystrmatch扩展插件通常随KingbaseES安装包一并升级。通常情况下用户无法单独升级插件。