hasura graphql-engine 集成zombodb

zombodb 是一个很不错的pg 扩展，可以方便的把es 与pg 集成起来，使用方便 ，目前尽管有一些docker 镜像
但是版本都比较老，所以基于centos7 做了一个新的docker 镜像，同时基于view 的方式集成hasura graphql-engine

环境准备

docker image

• dockerfile

FROM centos/postgresql-10-centos7
LABEL mail="1141591465@qq.com"
LABEL author="dalong"
COPY zombodb_centos7_pg10-10-1.0.3_1.x86_64.rpm /app/zombodb_centos7_pg10-10-1.0.3_1.x86_64.rpm
USER root
RUN rpm -Uvh /app/zombodb_centos7_pg10-10-1.0.3_1.x86_64.rpm
RUN cp -rf /usr/pgsql-10/share/extension/* /opt/rh/rh-postgresql10/root/usr/share/pgsql/extension
RUN cp /usr/pgsql-10/lib/zombodb.so /opt/rh/rh-postgresql10/root/lib64/pgsql/
USER postgres

说明
因为rpm 包安装的问题，上边的扩展需要重新copy 目录

docker-compose 环境

• docker-compose 文件

version: '3'
services:
  elasticsearch:
    image: elasticsearch:6.6.0
    ports: 
    - "9200:9200"
    environment:
      - http.host=0.0.0.0
      - transport.host=0.0.0.0
      - network.host=0.0.0.0
      - "ES_JAVA_OPTS=-Xms512m -Xmx512m"
    ulimits:
      memlock:
        soft: -1
        hard: -1
  postgresql_db:
    build: ./
    image: dalongrong/zombodb-postgresql-10-centos7
    ports:
    - "5432:5432"
    environment: 
    - "POSTGRESQL_ADMIN_PASSWORD=dalong"
  graphql-engine:
    image: hasura/graphql-engine:v1.0.0-alpha41
    ports:
    - "8080:8080"
    environment:
    - "POSTGRES_PASSWORD:dalong"
    command: >
      /bin/sh -c "
      graphql-engine --database-url postgres://postgres:dalong@postgresql_db:5432/postgres serve --enable-console;
      "

启动&&测试

构建镜像&&启动服务

• 构建zombodb 镜像

docker-compose build

• 启动服务

docker-compose up -d

测试zombodb 集成es

• 创建扩展

CREATE EXTENSION zombodb;

• 创建测试表

CREATE TABLE products (
    id SERIAL8 NOT NULL PRIMARY KEY,
    name text NOT NULL,
    keywords varchar(64)[],
    short_summary text,
    long_description zdb.fulltext, 
    price bigint,
    inventory_count integer,
    discontinued boolean default false,
    availability_date date
);

• 创建索引

  注意使用的es 的地址，使用容器服务的

CREATE INDEX idxproducts ON products
                  USING zombodb ((products.*))
                   WITH (url='http://elasticsearch:9200/');

• 导入数据

COPY products FROM PROGRAM 'curl https://raw.githubusercontent.com/zombodb/zombodb/master/TUTORIAL-data.dmp';

• 查询

SELECT * FROM products WHERE products ==> 'sports box';

效果

2 Baseball {baseball,sports,round} It's a baseball Throw it at a person with a big wooden stick and hope they don't hit it 1249 2 FALSE 2015-08-21
4 Box {wooden,box,"negative space",square} Just an empty box made of wood A wooden container that will eventually rot away. Put stuff it in (but not a cat). 17000 0 TRUE 2015-07-01

• es 界面
  

集成hasura graphql-engine

具体操作就是鼠标点击，对于hasura graphql-engine 的集成，通过view 方式，也可以基于dsl 查询的方式（实际上还是view）

• 添加view 视图

create view zombodb_hg as
SELECT * FROM products WHERE products ==> 'sports box';

• 添加graphql 支持
  
• 查询
  

参考资料

https://www.cnblogs.com/rongfengliang/p/10635311.html
https://github.com/rongfengliang/zombodb-hasura-graphql
https://github.com/rongfengliang/zombodb-docker-image