PipelineDB example

gistfile1.txt

Data:

{"type":"add","uuid":"4b24edb2-5074-4a3a-95cb-927d5535da7c","topic":"clients","data":{"time":"2015-03-23T15:45:34.995Z"}}
{"type":"add","uuid":"26ff7bdb-9913-47ab-a6df-8c985320c1bb","topic":"clients","data":{"time":"2015-03-23T15:45:36.082Z"}}
{"type":"add","uuid":"914096b8-dc66-4239-bb1c-88baba0c1831","topic":"lines","data":{"time":"2015-03-23T15:45:40.323Z","clientId":"26ff7bdb-9913-47ab-a6df-8c985320c1bb","line":"Hello?"}}
{"type":"add","uuid":"bdb60b1e-b34b-490e-a737-0ceb81208f4e","topic":"lines","data":{"time":"2015-03-23T15:45:44.935Z","clientId":"4b24edb2-5074-4a3a-95cb-927d5535da7c","line":"What?"}}
{"type":"del","uuid":"4b24edb2-5074-4a3a-95cb-927d5535da7c","topic":"clients"}
{"type":"add","uuid":"f9aacc7e-1745-4a54-8e54-b9bea3cbc3bb","topic":"clients","data":{"time":"2015-03-23T15:45:59.800Z"}}
{"type":"add","uuid":"2c45cca3-cfa5-4ac2-bfec-12ca985d869b","topic":"lines","data":{"time":"2015-03-23T15:46:15.624Z","clientId":"f9aacc7e-1745-4a54-8e54-b9bea3cbc3bb","line":"Works finw"}}
{"type":"add","uuid":"bb178b47-52b7-4bad-9aa9-5b74fb6e818d","topic":"lines","data":{"time":"2015-03-23T15:46:19.989Z","clientId":"f9aacc7e-1745-4a54-8e54-b9bea3cbc3bb","line":"fine"}}
{"type":"add","uuid":"099b22cc-b5b3-4ee0-a87c-fa4227c1d258","topic":"lines","data":{"time":"2015-03-23T15:46:25.423Z","clientId":"26ff7bdb-9913-47ab-a6df-8c985320c1bb","line":"Yup"}}
{"type":"del","uuid":"f9aacc7e-1745-4a54-8e54-b9bea3cbc3bb","topic":"clients"}
{"type":"del","uuid":"26ff7bdb-9913-47ab-a6df-8c985320c1bb","topic":"clients"}
{"type":"add","uuid":"13146616-07fe-46ba-a18d-ed47a25a8b7b","topic":"clients","data":{"time":"2015-03-25T06:45:26.312Z"}}
{"type":"add","uuid":"5954ce8f-9b3b-47c3-a351-7a6d5f46aa11","topic":"clients","data":{"time":"2015-03-25T06:45:36.673Z"}}
{"type":"add","uuid":"ec019a69-f363-4e47-b608-640b4660a469","topic":"lines","data":{"time":"2015-03-25T06:45:42.306Z","line":"Test"}}
{"type":"add","uuid":"c5ec07e1-dcbc-4db0-b0cd-8fc5f4d0abc7","topic":"clients","data":{"time":"2015-03-25T06:46:20.558Z"}}
{"type":"add","uuid":"2dcbbbef-8809-4de3-adf0-d3d68a65c56e","topic":"lines","data":{"time":"2015-03-25T06:46:28.063Z","line":"Cool"}}
{"type":"add","uuid":"3bff99e3-d3bf-4ce5-b0e9-e0958ef2092f","topic":"lines","data":{"time":"2015-03-25T06:46:41.939Z","line":"Streaming"}}
{"type":"add","uuid":"067e2d17-eeba-420f-81a4-7cfc30a47523","topic":"lines","data":{"time":"2015-03-25T06:46:53.048Z","line":"Works great"}}
{"type":"add","uuid":"14408367-0404-4e49-ad4c-263df616c8e7","topic":"lines","data":{"time":"2015-03-25T06:47:10.977Z","line":"ujujuju"}}
{"type":"add","uuid":"1f08ce4a-cabd-4d24-be1e-d6f8e605d998","topic":"lines","data":{"time":"2015-03-25T06:47:15.983Z","line":"okokok"}}
{"type":"add","uuid":"69845bac-98d8-4c74-b1ef-c7431f10c4f6","topic":"lines","data":{"time":"2015-03-25T06:47:23.050Z","line":"Hhh"}}
{"type":"add","uuid":"e63399c4-7be5-47fc-ba69-b7a234287e65","topic":"clients","data":{"time":"2015-03-25T06:52:15.615Z"}}
{"type":"add","uuid":"d61a270d-d548-46f4-8045-76c0234024cd","topic":"lines","data":{"time":"2015-03-25T06:52:20.580Z","line":"aaaaaaaa"}}
{"type":"add","uuid":"7ab075b8-0a94-4ec4-b2b2-1119c7380487","topic":"clients","data":{"time":"2015-03-25T06:55:35.431Z"}}
{"type":"add","uuid":"6c5f2c70-4afd-4307-8bad-e7416d6c3c63","topic":"lines","data":{"time":"2015-03-25T06:55:37.951Z","clientId":"7ab075b8-0a94-4ec4-b2b2-1119c7380487","line":"xxxf"}}


Working, but sort-of-ugly continous view:

CREATE CONTINUOUS VIEW line_count_per_client AS
SELECT
    count(*) as "num",
    "clientId"
FROM (
    SELECT
        ("json"::json->'data'->>'time')::timestamptz as "time",
        ("json"::json->'data'->>'clientId')::uuid as "clientId",
        ("json"::json->'data'->>'line')::text as "line"
    FROM interlaced_json
    WHERE "json"::json->>'topic' = 'lines'
) lines
GROUP BY "clientId"
;


Would look nicer like this:

CREATE CONTINUOUS VIEW lines AS
SELECT
    ("json"::json->'data'->>'time')::timestamptz as "time",
    ("json"::json->'data'->>'clientId')::uuid as "clientId",
    ("json"::json->'data'->>'line')::text as "line"
FROM interlaced_json
WHERE "json"::json->>'topic' = 'lines'
;

CREATE CONTINUOUS VIEW line_count_per_ AS
SELECT
    count(*) as "num",
    "clientId"
FROM lines
GROUP BY "clientId"
;