多年来,Postgres 一直是我选择的数据库,但我仍然经常发现我还没有意识到的新的有趣的特性。这个长列表中的最新条目是“个外部数据包装器”,它是 Postgres 9.1 中添加的SQL/MED(“外部数据的 SQL 管理”)规范的实现。

这种机制允许将我们的数据库与存储在其外部的数据集成在一起。最常见的用例可能是访问远程 RDBMS(Postgres 或其他),但在此示例中,我们将使用文件远程数据包装器从磁盘访问 CSV 文件。

整合外部数据

在此示例中,我们将使用来自Simplemaps的基本世界城市数据集,因此,如果您想继续,请立即下载此文件。

为了能够定义“外表”,我们首先需要安装file_fdw扩展并定义使用它的服务器。

CREATE EXTENSION file_fdw;
CREATE SERVER cities FOREIGN DATA WRAPPER file_fdw;

进入全屏模式 退出全屏模式

一旦我们的服务器就位,我们可以定义一个外部表来访问它的数据:

CREATE FOREIGN TABLE cities (
  city text,
  city_ascii text,
  lat real,
  long real,
  population real,
  country text,
  iso2 text,
  iso3 text,
  province text
) SERVER cities
OPTIONS ( 
  filename '/tmp/simplemaps-worldcities-basic.csv', 
  format 'csv', 
  header 'true'
);

进入全屏模式 退出全屏模式

上面创建了我们表的模式,还指定了我们数据文件的位置,文件格式(file_fdw不仅支持 CSV)并指示 Postgres 忽略文档的第一行,因为它是标题。

我们现在可以像任何常规旧表一样查询这个 CSV 文件:

test=# select city, population::int from cities order by population desc limit 3;
    city     | population
-------------+------------
 Tokyo       |   22006300
 Mumbai      |   15834918
 Mexico City |   14919501
(3 rows)

进入全屏模式 退出全屏模式

我不了解你,但我发现无需先编写导入器即可访问 CSV 数据的能力非常令人兴奋,而且性能也相当不错:

test=# explain analyze select avg(population) from cities;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1896.14..1896.15 rows=1 width=8) (actual time=12.437..12.437 rows=1 loops=1)
   ->  Foreign Scan on cities  (cost=0.00..1851.60 rows=17816 width=4) (actual time=0.064..10.648 rows=7322 loops=1)
         Foreign File: /tmp/simplemaps-worldcities-basic.csv
         Foreign File Size: 570124
 Planning time: 0.605 ms
 Execution time: 13.517 ms
(6 rows)

进入全屏模式 退出全屏模式

进入续集

现在是时候从 Ruby 访问我们的外部表了。为此,我们将使用 Jeremy Evans 出色的Sequelgem。它带有自己的控制台,因此命令sequel postgres://localhost/test将使我们进入一个 IRB 会话,其中 gem 已经需要并且Sequel::Database对象存储在常量DB中:

→ sequel postgres://localhost/test
Your database is stored in DB...
>> DB
#=> #<Sequel::Postgres::Database: "postgres://localhost/test" {:test=>true, :loggers=>[]}>

进入全屏模式 退出全屏模式

作为第一步,我们将cities表分配给一个变量。这将返回一个Sequel::Dataset,它表示当我们想要检索数据时将执行的查询。

cities = DB[:cities]
#=> #<Sequel::Postgres::Dataset: "SELECT * FROM \"cities\"">

进入全屏模式 退出全屏模式

现在我们可以定义一个名为top3的数据集,它与上面显示的 SQL 查询非常相似。

top3 = cities.select(:city, :population).reverse_order(:population).limit(3)
#=> #<Sequel::Postgres::Dataset: "SELECT \"city\", \"population\" FROM \"cities\" ORDER BY \"population\" DESC LIMIT 3">

进入全屏模式 退出全屏模式

注意:如果我们启用了 Sequel 的核心扩展order子句可能会写成order(:population.desc),但是否要使用这种风格取决于个人喜好。

所有Sequel::Datasets都包含Enumerable模块,因此我们可以使用它的#to_a方法来具体化我们的数据集:

top3.to_a
#=> [{:city=>"Tokyo", :population=>22006300.0}, {:city=>"Mumbai", :population=>15834900.0}, {:city=>"Mexico City", :population=>14919500.0}]

进入全屏模式 退出全屏模式

Ruby 和 Sequel 幸福地忽略了它们正在处理外部表的事实,而我们正在执行从 Postgres 控制台发出的相同查询。

但我想要对象!

虽然上述查询有效,但它返回了一个普通哈希数组。如果您更喜欢对象,最好使用Sequel::Model代替。为此,我们需要像这样定义一个类:

class City < Sequel::Model ; end

进入全屏模式 退出全屏模式

这通过类方法公开了底层数据集:

City.dataset
=> #<Sequel::Postgres::Dataset: "SELECT * FROM \"cities\"">

进入全屏模式 退出全屏模式

但是,将方法直接链接到它上并不被认为是好的风格。相反,文档建议对每个任务使用单一方法。如果您在这里想知道dataset_module,这是一种方便的方法,让我们创建返回缓存数据集的命名数据集方法:

class City < Sequel::Model
  dataset_module do
    def top3
      select(:city, :population).
      reverse_order(:population).
      limit(3)
    end
  end
end

进入全屏模式 退出全屏模式

现在让我们使用我们新定义的类:

top3 = City.top3.to_a
#=> [#<City @values={:city=>"Tokyo", :population=>22006300.0}>, #<City @values={:city=>"Mumbai", :population=>15834900.0}>, #<City @values={:city=>"Mexico City", :population=>14919500.0}>]

top3.first.city
#=> "Tokyo"

进入全屏模式 退出全屏模式

结果与以前相同,但使用对象访问而不是普通哈希。

总结

Postgres 的外部数据包装器是一个有趣的概念,我肯定会花更多时间来探索它们,尤其是Multicorn 项目提供的 LDAP、IMAP 和 RSS 等更奇特的包装器。

Sequel 是一个绝妙的宝石,也很好地提醒我们,当谈到 Ruby 中的数据库访问和 ORM 时,ActiveRecord远不是城里唯一的游戏。