PostgreSQL 助力文本分析，让文本分析飞起来

众所周知文本数据的分析,一直是EXCEL的天下, 通过多种EXCEL的方法来进行数据的处理. 但如果涉及到几个文本文件的数据的聚合分析,合并分析,函数分析等等这样的情况. 所以怎么能让文本文件进行数据分析成为一个数据分析领域的卖点.

这里有三个问题 

1  文本数据是否需要导入到表

2  文本数据在查询时是否可以使用数据库本身的特性,或者优化的条件来

    进行查询

3  查询的方式是否完全可以通过PGPLSQL的方式来进行大部分功能的查询

如果数据还需要导入到数据库中，则这个功能和其他的数据库并未有明显的区别，都是需要导入数据，就与我们本次的初衷所违背，我们需要的是，数据不导入到数据库的表中。

实际上POSTGRESQL 本身是可以进行对文件文件进行直接读取的。

下面我们就演示这个操作

create extension file_fdw;

create server fdw_server foreign data wrapper file_fdw;

create foreign table  actor
(actor_id  int,
first_name  text,
last_name  text,
last_update  timestamp) SERVER fdw_server
OPTIONS (filename '/pgdata/actor.csv', format 'csv' );


create foreign table film_actor
(actor_id int,
film_id int,
last_update timestamp) SERVER fdw_server
OPTIONS (filename '/pgdata/film_actor.csv', format 'csv' );


create foreign table film(
 film_id     int,
 title       text,
 description text,
 release_year    text,
 language_id     text,
 rental_duration text,
 rental_rate     text,
 length          text,
 replacement_cost text,
 rating           text,
 last_update      timestamp,
 special_features text) SERVER fdw_server
OPTIONS (filename '/pgdata/film.csv', format 'csv' );

我们依次的创建 file_fdw, 创建外部表服务器， 创建对已经存在的三个CSV文件创建外部表。

此时我们已经可以通过POSTGRESQL的数据库服务引擎对三张CSV文件来进行数据的读取。

此时我们可以针对表进行复杂的查询的活动

查询本表本身并不是一个难题

实际上POSTGRESQL  file_fdw 中的 data wapper  重要的作用在于可以让对外部数据源的查询使用数据库的优化手段，这点是其他的数据库无法做到的。

我们将两个外部表进行互相的INNER JOIN ，可以发现这两张表之间的INNER JOIN 的运算使用了 hash 的运算。

如果表更多可以吗？当然可以

select a.first_name,f.title from actor as a
inner join film_actor as fa on a.actor_id = fa.actor_id
inner join film as f on fa.film_id = f.film_id
where a.first_name = 'Nick' and  f.title = 'Adaptation Holes';

下面的查询中使用了hash join 的方式提高了查询的速度，尤其在针对文本数据较多的情况下。

实际上file_fdw的功能是一直在演进的，他作为POSTGRESQL 默认提供的模块在contrib module 中，对于本地文件的进行处理和读取的一种方法，这里要求文件必须能被COPY 方式能操作的文件。对于 file_fdw 操作的文件的属性来说，文件只能进行只读的访问。  在POSTGRESQL 11 之前只能使用super_user来操作文件，而在11后，我们有了几个默认的角色 ru  pg_read_server_files  pg_writer_server_files  pg_execute_server_program 等角色。

对于

在读取文件前，是可以针对文件载入到数据库系统中进行评估一般使用默认的评估手段当然这也可能导致评估不准。在评估对外部FILE_FDW表扫描的情况上，也是使用与本地表一样的扫描的方法，seqscan 但在CPUS对于每一行的扫描中对比内部表的每行扫描的CPU 乘以10倍来计算。