materialize 试用

前边有大概介绍过materialize，以下是一个简单的试用（基于官方文档，官方同时也提供了容器的运行环境）

环境准备

• docker-compose 文件

version: "3"

services: 

  materialize: 

     image: materialize/materialized:v0.5.0

     command: --workers 1

     ports: 

     - "6875:6875"

简单试用

• 连接
  可以通过pg客户端连接
• 创建materialize view

 

CREATE MATERIALIZED VIEW pseudo_source (key, value) AS

    VALUES ('a', 1), ('a', 2), ('a', 3), ('a', 4),

    ('b', 5), ('c', 6), ('c', 7);

• 查询数据

SELECT * FROM pseudo_source;

效果

 

 

• 分组查询

SELECT key, sum(value) FROM pseudo_source GROUP BY key ;

效果

 

 

• join 查询
  创建新的view

 

CREATE MATERIALIZED VIEW lhs (key, value) AS

    VALUES ('x', 'a'), ('y', 'b'), ('z', 'c');

join

SELECT lhs.key, sum(rhs.value)

FROM lhs

JOIN pseudo_source AS rhs

ON lhs.value = rhs.key

GROUP BY lhs.key; 

效果

 

 

实时数据处理

需要进入容器操作,先安装curl

• 下载数据

 

while true; do

  curl --max-time 9999999 -N https://stream.wikimedia.org/v2/stream/recentchange >> wikirecent

done

• 创建source

CREATE SOURCE wikirecent

FROM FILE '/opt/wikirecent' WITH (tail = true)

FORMAT REGEX '^data: (?P<data>.*)';

查看列

SHOW COLUMNS FROM wikirecent;

效果

 

 

 

 

• 创建view

 

CREATE MATERIALIZED VIEW recentchanges AS

    SELECT

        val->>'$schema' AS r_schema,

        (val->'bot')::bool AS bot,

        val->>'comment' AS comment,

        (val->'id')::float::int AS id,

        (val->'length'->'new')::float::int AS length_new,

        (val->'length'->'old')::float::int AS length_old,

        val->'meta'->>'uri' AS meta_uri,

        val->'meta'->>'id' as meta_id,

        (val->'minor')::bool AS minor,

        (val->'namespace')::float AS namespace,

        val->>'parsedcomment' AS parsedcomment,

        (val->'revision'->'new')::float::int AS revision_new,

        (val->'revision'->'old')::float::int AS revision_old,

        val->>'server_name' AS server_name,

        (val->'server_script_path')::text AS server_script_path,

        val->>'server_url' AS server_url,

        (val->'timestamp')::float AS r_ts,

        val->>'title' AS title,

        val->>'type' AS type,

        val->>'user' AS user,

        val->>'wiki' AS wiki

    FROM (SELECT data::jsonb AS val FROM wikirecent);

​

CREATE MATERIALIZED VIEW counter AS

    SELECT COUNT(*) FROM recentchanges;

​

CREATE MATERIALIZED VIEW useredits AS

    SELECT user, count(*) FROM recentchanges GROUP BY user;

CREATE MATERIALIZED VIEW top10 AS

    SELECT * FROM useredits ORDER BY count DESC LIMIT 10;

查询top10 数据

SELECT * FROM top10 ORDER BY count DESC;

效果

 

 

参考资料

https://materialize.io/docs/get-started/