Traditionally, there have been two ways to process data:
Load it exactly as is, then figure out how to clean it up. This implies an extra round of processing and means there will be a lag between data arriving and it being usable.
Treat it with the greatest suspicion and only load it after an elaborate sanity check. It’s the same work but done earlier.
But sometimes there’s a ‘middle way’.
CrateDB’s generated columns allow you to categorize and index data as it arrives. While a very simple feature, it can be remarkably useful, as the database applies it on every insert, no matter who is doing the inserting. The generated value is stored and indexed like any other column in CrateDB, so queries against it are cheap.
This post walks through a worked example: a table of baggage-handling events at Heathrow Terminal 5, with five generated columns each doing a different job.
All the code we use below is on GitHub.
Is This Standard SQL?
CrateDB supports persistent 'Virtual Columns', which were introduced in the SQL:2003 standard.
Our Example’s DDL
The DDL for the table is below. Note that some of our generated columns are BOOLEAN flags, set by CASE statements, or a WITHIN expression. We also have an example of a GEO_POINT expression, as well as a custom field for time-based partitioning.
CREATE TABLE gencol.bag_loading_events (
bag_id TEXT NOT NULL,
conveyer_timestamp TIMESTAMP NOT NULL,
reported_location OBJECT(STRICT) AS(lat DOUBLE PRECISION,long DOUBLE PRECISION),
bag_length_cm SMALLINT NOT NULL,
bag_width_cm SMALLINT NOT NULL,
bag_height_cm SMALLINT NOT NULL,
oversize BOOLEAN GENERATED ALWAYS AS (
CASE
WHEN bag_length_cm > 55 OR bag_width_cm > 40 OR bag_height_cm > 20 THEN true
ELSE false
END
),
reported_late BOOLEAN GENERATED ALWAYS AS (
CASE
WHEN conveyer_timestamp < CURRENT_TIMESTAMP - INTERVAL '5' MINUTE THEN true
ELSE false
END
),
geo_location GEO_POINT GENERATED ALWAYS AS [reported_location['long'], reported_location['lat']],
in_t5 BOOLEAN GENERATED ALWAYS AS within(
CAST([reported_location['long'], reported_location['lat']] AS GEO_POINT),
'POLYGON ((-0.4930 51.4695, -0.4845 51.4695, -0.4845 51.4745, -0.4930 51.4745, -0.4930 51.4695))'
),
event_week TIMESTAMP GENERATED ALWAYS AS date_trunc('week', conveyer_timestamp),
PRIMARY KEY (bag_id, conveyer_timestamp, event_week)
) PARTITIONED BY (event_week);
The input data is a stream of six values: the bag's ID, the event timestamp, a location object, and three size measurements. All the other columns are generated. Let's take the generated columns one at a time:
Oversize – implementing a business rule.
Ryanair's cabin bag limit is 55 x 40 x 20 cm. The oversize column encodes that rule as a CASE expression over the three measurement columns. Any insert, from any application, in any language, gets the same answer, and if the airline changes the rule there is exactly one place to change it.
Because the result is a stored Boolean, "how many oversize bags did we see today" is an indexed filter, not a three-way comparison run across the whole table at query time.
Reported_late – an Identifier of Problems with the Streaming Inserts.
reported_late compares the event's own timestamp against CURRENT_TIMESTAMP and flags rows that arrive more than five minutes after the event they describe. If the count of reported_late = true rows starts climbing, something upstream is backing up. An alternative implementation would be to store it as the number of seconds, as this would allow us to graph the streaming lag and spot trends over time,
geo_location – Creating a GEO_POINT out of raw data
Our stream gives us lat and long keys, but GEO_POINT is much more useful. geo_location turns lat and long into a proper GEO_POINT, which means the row can be used with CrateDB's geo functions and plotted on a map in Grafana without any query-time conversion.
in_t5 – solving the business question of ‘is this bag in Terminal 5’ instead of just storing latitude and longitude.
in_t5 runs a within() check against a polygon around the Terminal 5 building. Point-in-polygon tests aren't free, and dashboards tend to run the same ones over and over. Doing the check once at write time turns every later query into a boolean filter.
event_week – Partitioning by week
event_week truncates the event timestamp to the start of its week, and the table is PARTITIONED BY it. Rows route themselves into weekly partitions and the application never has to know partitions exist. When the retention policy says to drop data older than ninety days, you drop whole partitions, which is close to instant, instead of deleting millions of rows.
Watching it Work
The demo script inserts four bags, each special in its own way, and none of the inserts mention any of the generated columns:
+-----------+----------+---------------+-------+
| bag_id | oversize | reported_late | in_t5 |
+-----------+----------+---------------+-------+
| bad_bag | true | false | true |
| good_bag | false | false | true |
| late_bag | false | true | true |
| stray_bag | false | false | false |
+-----------+----------+---------------+-------+
good_bag fits the limits and is on time. bad_bag is over on every dimension. late_bag has a timestamp ten minutes in the past. stray_bag is within limits and on time, but its coordinates put it over by Terminal 2. After a REFRESH TABLE (inserts aren't visible to queries until a refresh), here is what comes back:
Each flag has exactly one row that trips it, every row carries a derived geo_location and event_week, and information_schema.table_partitions shows the week's partition was created automatically.
**When to Reach for This **
Generated columns earn their keep in three situations: when a business rule should be enforced in one place rather than in every writer; when a check is expensive to run at query time but cheap to run once at write time; and when you want time-series data to partition itself. The full script is in the cratedb-explore repository if you want to run it yourself.
Top comments (0)