Pipelinedb安装、部署和使用

Pipelinedb安装、部署和使用

1. 创建pipelinedb扩展

postgresql.conf配置项需要如下：

# At the bottom of <data directory>/postgresql.conf
shared_preload_libraries = 'pipelinedb'
max_worker_processes = 128

2. 运行pg10或其以上版本的数据库

数据库服务起来之后，执行如下命令

psql -c "CREATE EXTENSION pipelinedb"

这样就在默认的数据库创建了pipelinedb扩展插件

可通过\dx查看是否安装成功

3. Pipelinedb的使用

1. 创建foreign table

   CREATE FOREIGN TABLE wiki_stream (
           hour timestamp,
           project text,
           title text,
           view_count bigint,
           size bigint)
   SERVER pipelinedb;
   

2. 创建视图

   CREATE VIEW wiki_stats WITH (action=materialize) AS
   SELECT hour, project,
           count(*) AS total_pages,
           sum(view_count) AS total_views,
           min(view_count) AS min_views,
           max(view_count) AS max_views,
           avg(view_count) AS avg_views,
           percentile_cont(0.99) WITHIN GROUP (ORDER BY view_count) AS p99_views,
           sum(size) AS total_bytes_served
   FROM wiki_stream
   GROUP BY hour, project;
   

3. 查询视图

   SELECT * FROM wiki_stats ORDER BY total_views DESC