PG中文社区 /

PipelineDB 快速入门

原作者:张文升  创作时间:2018-01-11 21:28:48+08
doudou586 发布于2018-01-11 21:28:48           评论: 4   浏览: 17002   顶: 1279  踩: 1175 

PipelineDB 快速入门

作者:张文升

发布:2018-01-11

欢迎大家踊跃投稿,投稿信箱: press@postgres.cn


背景

PipelineDB基于PostgreSQL数据库改造而来,是一款开源的流式计算数据库。它允许我们通过sql的方式,对数据流做操作,并把操作结果持续储存到表中。主要特性:允许只使用 SQL 进行实时数据处理而没有应用代码,兼容 PostgreSQL,无 ETL,高效可持续。

详情可访问官网www.pipelinedb.com。

安装

从rpm安装

axel -n 5 https://s3-us-west-2.amazonaws.com/download.pipelinedb.com/pipelinedb-0.9.6-centos6-x86_64.rpm
rpm -ivh pipelinedb-0.9.6-centos6-x86_64.rpm

从源码安装

下载代码

git clone https://github.com/pipelinedb/pipelinedb.git

安装所需依赖

yum install libreadline6 libreadline6-dev check g++ flex bison zlib1g-dev libpq-dev libncurses-dev 
libcurl4-openssl-dev readline-devel zlib-devel

安装postgis和postgis所需的依赖

yum install libxml2 libxml2-devel geos geos-devel proj-devel gdal gdal-devel

安装支持pipelinedb的postgis,在configure的时候需要指定pg_config文件,在这里是pipeline-config

git clone https://github.com/pipelinedb/postgis.git
./autogen.sh
./configure --with-pgconfig=/usr/lib/pipelinedb/bin/pipeline-config
make
make install

编译

./configure CFLAGS="-g -O2" --prefix=/opt/pipilinedb
make
make install

快速上手

实例化数据目录

创建数据目录,修改数据目录属主

mkdir -p /export/pipeline_data
chown postgres.postgres /export/pipeline_data

实例化数据目录

/usr/lib/pipelinedb/bin/pipeline-init -D /export/pipiline_data/

启动pipelinedb

修改配置文件,开启日志,调整监听端口等等,方法与postgresql一样。然后启动它:

/usr/lib/pipelinedb/bin/pipeline-ctl -D /export/pipiline_data/ start

连接到pipelinedb

[postgres@work.com pipiline_data]$ /usr/lib/pipelinedb/bin/pipeline pipeline
pipeline (9.5.3)
Type "help" for help.
pipeline=# select version();
                            version           
---------------------------------------------
 PostgreSQL 9.5.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 
(Red Hat 4.4.7-17), 64-bit
(1 row)

aha,熟悉的终端界面,这里可以很清楚了,我使用的这个版本是基于postgresql的9.5.3开发的,版本还算比较新。:-)

查看版本

查看pipelinedb的版本可以用pipeline_version()函数:

pipeline=# select pipeline_version();

                     pipeline_version  
------------------------------------------------------------------------------------
 PipelineDB 0.9.6 at revision afe6cf1c681f680ecd6ab9a55070abe9b61b494d on 
x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-17), 64-bit

(1 row)

举个栗子

创建一个Stream

CREATE STREAM wiki_stream (hour timestamp, project text, title text, view_count bigint, size bigint);

创建一个CONTINUOUS VIEW

CREATE CONTINUOUS VIEW wiki_stats 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;

写入测试数据

从外部获取数据实时写入pipelinedb

curl -sL http://pipelinedb.com/data/wiki-pagecounts | gunzip | 
    psql -h localhost -p 5432 -d pipeline -c "
COPY wiki_stream (hour, project, title, view_count, size) FROM STDIN"

查询结果

SELECT * FROM wiki_stats ORDER BY total_views DESC; \watch 1

(结果略)

客户端连接(python) 和postgresql一样,需要使用 psycopg2 。

import psycopg2
conn = psycopg2.connect('dbname=test user=user host=localhost port=5432')
pipeline = conn.cursor()

create_cv = """
     CREATE CONTINUOUS VIEW continuous_view AS SELECT x::integer, COUNT(*) FROM stream GROUP BY x
"""
pipeline.execute(create_cv)
conn.commit()
rows = []

for n in range(100000):
    # 10 unique groupings
    x = n % 10
    rows.append({'x': x})
# Now write the rows to the stream

pipeline.executemany('INSERT INTO stream (x) VALUES (%(x)s)', rows)

# Now read the results

pipeline.execute('SELECT * FROM continuous_view')

rows = pipeline.fetchall()

for row in rows:
    x, count = row
    print x, count
pipeline.execute('DROP CONTINUOUS VIEW continuous_view')
pipeline.close()

PostgreSQL_Community


评论:4   浏览: 17002                   顶: 1279  踩: 1175 

请在登录后发表评论,否则无法保存。

1# __ xcvxcvsdf 回答于 2024-11-24 20:36:00+08
http://nalei.zjtcbmw.cn/twlj/ http://bjtcxxw.cn/xianning/ http://ouyu.hftcbmw.cn/zhongguo/ http://ly.shtcxxw.cn/lvliang/ https://lc.tiancebbs.cn/ https://honglan.tiancebbs.cn/chongmingxian/ http://shengshun.njtcbmw.cn/qujing/ http://bjtcxxw.cn/mcmzl/ http://jinqiang.ahtcbmw.cn/bnq/ https://qlchengdong.tiancebbs.cn/ http://shengshun.njtcbmw.cn/luwanqu/ http://taiying.njtcbmw.cn/taizhou/ http://fuyang.tjtcbmw.cn/myq/ https://nanmatou.tiancebbs.cn/ http://js.sytcxxw.cn/hefei/ https://ssqzhoubian.tiancebbs.cn/ http://huilong.sctcbmw.cn/jjth/

2# __ xcvxcvsdf 回答于 2024-10-24 20:44:05+08
https://zulin.tiancebbs.cn/sh/3234.html https://aihuishou.tiancebbs.cn/sh/4681.html https://aihuishou.tiancebbs.cn/sh/2993.html https://zulin.tiancebbs.cn/sh/3343.html https://guyuan.tiancebbs.cn/qths/462956.html https://zulin.tiancebbs.cn/sh/3661.html https://aihuishou.tiancebbs.cn/sh/1096.html https://changshushi.tiancebbs.cn/hjzl/462850.html https://kunshan.tiancebbs.cn/wtzl/62253.html https://su.tiancebbs.cn/hjzl/459547.html https://zulin.tiancebbs.cn/sh/1683.html https://www.tiancebbs.cn/ershoufang/473546.html https://www.tiancebbs.cn/ershouwang/467693.html https://aihuishou.tiancebbs.cn/sh/2339.html https://taicang.tiancebbs.cn/hjzl/459795.html https://dinghuqu.tiancebbs.cn/qths/467035.html https://www.tiancebbs.cn/ershoufang/467008.html

3# __ xcvxcvsdf 回答于 2024-10-24 17:23:45+08
https://zulin.tiancebbs.cn/sh/770.html https://www.tiancebbs.cn/ershouwang/468746.html https://www.tiancebbs.cn/ershouwang/474365.html https://zulin.tiancebbs.cn/sh/3134.html https://qingyang.tiancebbs.cn/qths/473739.html https://aihuishou.tiancebbs.cn/sh/5.html https://zulin.tiancebbs.cn/sh/2625.html https://aihuishou.tiancebbs.cn/sh/3518.html https://www.tiancebbs.cn/ershoufang/473864.html https://www.tiancebbs.cn/ershoufang/468330.html https://zulin.tiancebbs.cn/sh/1367.html https://www.tiancebbs.cn/ershouwang/474290.html https://aihuishou.tiancebbs.cn/sh/1586.html https://zulin.tiancebbs.cn/sh/5023.html https://aihuishou.tiancebbs.cn/sh/1343.html https://zulin.tiancebbs.cn/sh/2495.html https://sh.tiancebbs.cn/hjzl/461349.html

4# __ xiaowu 回答于 2024-04-22 09:43:55+08
本手妙手俗手高考作文:https://www.nanss.com/xuexi/3709.html 如何提高党性修养:https://www.nanss.com/shenghuo/3391.html 三年级优秀写植物作文300字:https://www.nanss.com/xuexi/3701.html 改善报告:https://www.nanss.com/gongzuo/3425.html 入党自传:https://www.nanss.com/xuexi/3078.html 立夏的诗句:https://www.nanss.com/xuexi/2924.html 在职证明模板:https://www.nanss.com/gongzuo/3091.html 妈妈的爱作文:https://www.nanss.com/xuexi/3272.html LOL温柔至极的高端局ID:https://www.nanss.com/mingcheng/3718.html 让媳妇感动到哭的留言:https://www.nanss.com/yulu/3544.html 我的梦想二年级作文:https://www.nanss.com/xuexi/3686.html 家庭群名:https://www.nanss.com/mingcheng/3895.html 桂花香的句子唯美短句:https://www.nanss.com/xuexi/3953.html 追风筝的人读后感:https://www.nanss.com/yuedu/3213.html 团名好听的唯美:https://www.nanss.com/mingcheng/3550.html 开心聚餐幽默的句子:https://www.nanss.com/shenghuo/2524.html 感恩的句子经典语录:https://www.nanss.com/yulu/3070.html 男生的qq名字好听又帅气:https://www.nanss.com/mingcheng/3572.html 唯美诗意名字:https://www.nanss.com/mingcheng/3560.html 给老师的一封信:https://www.nanss.com/xuexi/2923.html 采购合同:https://www.nanss.com/gongzuo/3116.html 施工方案:https://www.nanss.com/gongzuo/3137.html 我的心儿怦怦跳:https://www.nanss.com/xuexi/3404.html 提升服务质量:https://www.nanss.com/gongzuo/3583.html 国旗下的讲话演讲稿:https://www.nanss.com/xuexi/2892.html 入园第一天的经典语录:https://www.nanss.com/wenan/3989.html 酷酷的网名:https://www.nanss.com/mingcheng/3773.html 列数字的句子:https://www.nanss.com/xuexi/3592.html 工作鉴定:https://www.nanss.com/gongzuo/3588.html 丧尸恐怖作文:https://www.nanss.com/xuexi/3658.html



发表评论:
加入我们
QQ群1:5276420
QQ群2:3336901
QQ群3:254622631
文档群:150657323
文档翻译平台:按此访问
社区邮件列表:按此订阅
扫码关注
© PostgreSQL中文社区 ... (自2010年起)