pipelinedb命令积累

1.查看所有插件

select * from pg_available_extensions; 

2.pipelinedb1.0创建流

create foreign table dwb.first_stream
(
	arrival_timestamp timestamp with time zone,
	id bigint
)
server pipelinedb;

comment on foreign table dwb.first_stream is '\u521d\u6b21\u4f7f\u7528\u6d41';

alter foreign table dwb.first_stream owner to postgres;

3.流规则

--使用SUM时，请务必使用COALESCE把NULL值替换掉，否则会导致严重的数据库后端进程崩溃重启！！！
drop  view if exists dwb.test_view_mq ;
create view dwb.test_view_mq with (action=materialize) as
    select to_date(data->>'datetime','YYYY-MM-DD HH24:MI:SS') as f_ds,
           sum(coalesce(cast(data->>'unreal_key' as numeric),0)) as "testName",
           count(*) as "车辆总数",
           count(data->>'veh_type') as "去重"
from dwb.streams_page_log_mq
group by
f_ds
;

　　

--清空cv数据
	
select pipelinedb.truncate_continuous_view('dws.flow_vehicle_min_statistic')