Initialize a data directory and start the PipelineDB server:

pipeline-init -D <data directory>
pipelinedb -D <data directory>

Now enable continuous query execution. This only needs to be done once and is remembered across restarts.

psql -h localhost -p 5432 -d pipeline -c "ACTIVATE"

Wikipedia Traffic

In this example we’ll compute some basic statistics on a day’s worth of Wikipedia page view data. Each record in the dataset contains hourly page view statistics for every Wikipedia page. The record format is as follows:

hour | project | page title | view count | bytes served

First, let’s create our continuous view using psql:

psql -h localhost -p 5432 -d pipeline -c "
CREATE STREAM wiki_stream (hour timestamp, project text, title text, view_count bigint, size bigint);
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;"

Now we’ll decompress the dataset as a stream and write it to stdin, which can be used as an input to COPY:

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"

Note that this dataset is large, so the above command will run for quite a while (cancel it whenever you’d like). As it’s running, select from the continuous view as it ingests data from the input stream:

psql -h localhost -p 5432 -d pipeline -c "
SELECT * FROM wiki_stats ORDER BY total_views DESC";