Skip to content
BoilStream

Count application events

tutorial
TL;DR

Run this small SQL dataset in an authorized test streaming catalog and compare the query result with the expected values. Use PGWire for both insertion and recent-data reads. Insert once, allow for the commit cycle and reconcile any uncertain write before repeating it; identifiers alone do not provide deduplication.

Input and resultThree input events become two user counts
Three input events become two user countsalice: view; alice: click; bob: view. Grouping the example rows produces alice: 2; bob: 1.Input rows from the exampleuser_idevent_typealiceviewaliceclickbobviewCOUNT(*)Expected resultalice2bob1Three input events become two user countsalice: view; alice: click; bob: view. Grouping the example rows produces alice: 2; bob: 1.Input rows from the exampleuser_idevent_typealiceviewaliceclickbobviewCOUNT(*)Expected resultalice2bob1
These are the actual example inputs and expected aggregates, not throughput measurements. Verify the SQL result before expanding the example.

Prerequisites

Section titled “Prerequisites”

Complete the first workflow connection with permission to create tables in a test streaming catalog. Use an unused table name and run the INSERT once.

Application events

Section titled “Application events”
SQL
CREATE TABLE main.app_events (
    event_id BIGINT,
    user_id VARCHAR,
    event_type VARCHAR
);


INSERT INTO main.app_events VALUES
    (1, 'alice', 'view'),
    (2, 'alice', 'click'),
    (3, 'bob', 'view');


SELECT user_id, COUNT(*) AS events
FROM main.app_events
GROUP BY user_id
ORDER BY user_id;

Expected result: alice has two events and bob has one. Use a streaming view to filter future arrivals into a derived stream; a normal query does not create one.

If the result differs

Section titled “If the result differs”

Wait only by repeating SELECT within a bounded period. Check the selected catalog and any earlier inserts. After a lost connection, preserve the input and reconcile before retrying. See completion rules.