Postgresql - Foreign data wrappers - ElasticSearch

Postgresql的Foreign data wrapper不仅可以读取数据库，还可以读取文件系统，大数据存储等。

这一篇我们看一下PG该如何建立ES的外部表。

插件代码

https://github.com/matthewfranglen/postgres-elasticsearch-fdw

 

安装

# git clone https://github.com/Kozea/Multicorn /tmp/multicorn # cd Multicorn # make install

 

# git clone https://github.com/matthewfranglen/postgres-elasticsearch-fdw.git

# cd postgres-elasticsearch-fdw/

# python setup.py install

 

使用

1. create extension

mytest=# create extension multicorn ;

CREATE EXTENSION

 

2. Create Server

mytest=# create server pg_es_server foreign data wrapper multicorn options( wrapper 'pg_es_fdw.ElasticsearchFDW' );

CREATE SERVER

 

3. Create Foreign Table

mytest=# CREATE FOREIGN TABLE test_es

mytest-# (

mytest(# id BIGINT,

mytest(# name TEXT,

mytest(# age TEXT,

mytest(# tel numeric(11),

mytest(# query TEXT,

mytest(# score NUMERIC

mytest(# )

mytest-# SERVER multicorn_es

mytest-# OPTIONS

mytest-# (

mytest(# host 'localhost',

mytest(# port '9200',

mytest(# index 'test',

mytest(# type 'test',

mytest(# rowid_column 'id',

mytest(# query_column 'query',

mytest(# score_column 'score'

mytest(# );

CREATE FOREIGN TABLE

 

4. Insert Data

mytest=# INSERT INTO test_es

mytest-# (

mytest(# id,

mytest(# name,

mytest(# age,

mytest(# tel

mytest(# )

mytest-# VALUES

mytest-# (

mytest(# 1,

mytest(# 'chen',

mytest(# '1',

mytest(# '13812345678'

mytest(# );

INSERT 0 1

 

5. 从ES中查看数据

# curl -H "Content-Type: application/json" -XPOST 'localhost:9200/test/_search?pretty'

{

"took" : 3,

"timed_out" : false,

"_shards" : {

"total" : 5,

"successful" : 5,

"skipped" : 0,

"failed" : 0

},

"hits" : {

"total" : 1,

"max_score" : 1.0,

"hits" : [

{

"_index" : "test",

"_type" : "test",

"_id" : "1",

"_score" : 1.0,

"_source" : {

"tel" : 1.3812345678E10,

"name" : "chen",

"age" : "1",

"score" : null,

"query" : null

}

}

]

}

}

 

6. 从ES中插入数据

# curl -H "Content-Type: application/json" -XPUT 'localhost:9200/test/test/2?pretty' -d '{"name":"chen", "age": 2, "tel":"13812345678"}'

{

"_index" : "test",

"_type" : "test",

"_id" : "2",

"_version" : 1,

"result" : "created",

"_shards" : {

"total" : 2,

"successful" : 1,

"failed" : 0

},

"_seq_no" : 0,

"_primary_term" : 1

}

 

7. 查看 ES和PG

# curl -H "Content-Type: application/json" -XPOST 'localhost:9200/test/_search?pretty'

{

"took" : 5,

"timed_out" : false,

"_shards" : {

"total" : 5,

"successful" : 5,

"skipped" : 0,

"failed" : 0

},

"hits" : {

"total" : 2,

"max_score" : 1.0,

"hits" : [

{

"_index" : "test",

"_type" : "test",

"_id" : "2",

"_score" : 1.0,

"_source" : {

"name" : "chen",

"age" : 2,

"tel" : "13812345678"

}

},

{

"_index" : "test",

"_type" : "test",

"_id" : "1",

"_score" : 1.0,

"_source" : {

"tel" : 1.3812345678E10,

"name" : "chen",

"age" : "29",

"score" : null,

"query" : null

}

}

]

}

}

 

mytest=# select * from test_es;

id | name | age | tel | query | score

----+------+-----+-------------+-------+-------

2 | chen | 2 | 13812345678 | | 1.0

1 | chen | 1 | 13812345678 | | 1.0

(2 rows)