Protobuf IoT Sensor Network - Manufacturing Floor Sensors

Read proto3 SensorNetwork binaries across two factory floors and a warehouse (20 sensors, 82 readings) to practice exploding repeated readings, joining them as CSV, and preserving Timestamp well-known types.

Syntax

CREATE ZONE IF NOT EXISTS external TYPE EXTERNAL;
CREATE SCHEMA IF NOT EXISTS external.protobuf_iot;

-- Exploded readings: 82 rows, one per SensorReading
CREATE EXTERNAL TABLE IF NOT EXISTS external.protobuf_iot.sensor_readings
USING PROTOBUF
LOCATION '/data/protobuf/sensors'
OPTIONS (
    schema_path = '/data/protobuf/sensors/schema/sensor.proto',
    message_name = 'iot.SensorNetwork',
    proto_flatten_config = '{
        "row_path": "sensors",
        "explode_paths": ["sensors.readings"],
        "include_paths": [
            "sensors.sensor_id", "sensors.sensor_type",
            "sensors.location", "sensors.status",
            "sensors.readings.value",
            "sensors.readings.recorded_at",
            "sensors.readings.unit",
            "sensors.installed_at"
        ],
        "column_mappings": {
            "sensors.readings.value": "reading_value",
            "sensors.readings.recorded_at": "recorded_at",
            "sensors.readings.unit": "unit"
        },
        "timestamp_format": "iso8601",
        "max_depth": 10
    }',
    file_metadata = '{"columns":["df_file_name","df_file_modified","df_dataset","df_row_number"]}'
);

-- Summary: 20 rows, readings comma-joined per sensor
CREATE EXTERNAL TABLE IF NOT EXISTS external.protobuf_iot.sensor_summary
USING PROTOBUF
LOCATION '/data/protobuf/sensors'
OPTIONS (
    schema_path = '/data/protobuf/sensors/schema/sensor.proto',
    message_name = 'iot.SensorNetwork',
    proto_flatten_config = '{
        "row_path": "sensors",
        "include_paths": [
            "sensors.sensor_id", "sensors.sensor_type",
            "sensors.location", "sensors.status",
            "sensors.readings.value",
            "sensors.readings.recorded_at",
            "sensors.readings.unit",
            "sensors.installed_at"
        ],
        "default_repeat_handling": "join_comma",
        "column_mappings": {
            "sensors.readings.value": "reading_values",
            "sensors.readings.recorded_at": "reading_times",
            "sensors.readings.unit": "reading_units"
        },
        "timestamp_format": "iso8601",
        "max_depth": 10
    }'
);

-- Verify
ASSERT ROW_COUNT = 20 SELECT * FROM external.protobuf_iot.sensor_summary;
ASSERT ROW_COUNT = 82 SELECT * FROM external.protobuf_iot.sensor_readings;
ASSERT VALUE min_val = 22.5 WHERE sensor_id = 'TEMP-A001'
ASSERT VALUE max_val = 23.4 WHERE sensor_id = 'TEMP-A001'
SELECT sensor_id, MIN(reading_value) AS min_val, MAX(reading_value) AS max_val
FROM external.protobuf_iot.sensor_readings
WHERE sensor_id = 'TEMP-A001'
GROUP BY sensor_id;

-- Cleanup
DROP EXTERNAL TABLE IF EXISTS external.protobuf_iot.sensor_readings WITH FILES;
DROP EXTERNAL TABLE IF EXISTS external.protobuf_iot.sensor_summary WITH FILES;
DROP SCHEMA IF EXISTS external.protobuf_iot;

Description

## When to Use Use this demo for IoT / telemetry style protobuf payloads: a parent `Sensor` message carries device metadata plus a `repeated SensorReading` array of time-value samples. It is the right template when you need both a "one row per sample" analytics table and a "one row per device" summary table from the same `.pb` files. ## What You Will Learn 1. Build two tables from the same set of `.pb` files - one exploded (`sensor_readings`, 82 rows) and one joined (`sensor_summary`, 20 rows) - without reshuffling the data. 2. Point `row_path` at the top-level repeated `sensors` field so every Sensor becomes a row. 3. Flatten nested `SensorReading.value`, `recorded_at`, `unit` fields into top-level columns via `include_paths` and `column_mappings`. 4. Convert `google.protobuf.Timestamp` (`installed_at`, `readings.recorded_at`) into ISO 8601 strings. 5. Track provenance across 3 facility files (`factory_floor_a.pb`, `factory_floor_b.pb`, `warehouse.pb`) using `file_metadata` columns. 6. Assert exact double-precision reading values (e.g. `22.5`, `7.8`, `0.5`) to verify `float`/`double` fidelity end-to-end. ## Prerequisites - Three `.pb` files plus `schema/sensor.proto` and `schema/timestamp.proto`. - An `EXTERNAL` zone (defaults to `external`). - Basic familiarity with `CREATE EXTERNAL TABLE ... USING PROTOBUF` options.

Pitfalls

• `sensor_type` and `status` in this schema are plain `string` fields, not enums - the lowercase values (`temperature`, `humidity`, `active`, `offline`, `maintenance`) are writer-provided strings. Adding a new value does not require a schema change, but it also means typos go undetected; promote to an enum once the vocabulary is stable.
• A sensor that writes zero readings produces zero rows in `sensor_readings` but still produces one row in `sensor_summary` with empty `reading_values`. Do not join the two tables on `sensor_id` assuming matching row counts.
• `google.protobuf.Timestamp` is stored as seconds + nanos. Without `timestamp_format = 'iso8601'` the column arrives as a struct; comparing struct columns with `>` / `ORDER BY` silently orders by the seconds field only, which is fine at second granularity but hides sub-second reordering.
• Double-precision reading values (22.5, 7.8, 0.5 in the seed data) survive round-trip, but `ROUND(AVG(reading_value), 1)` depends on the grouping set. The demo asserts `avg_val = 8.1` for VIB-B002 - the same query over the whole table will not produce that value.
• Proto3 `repeated` fields use packed encoding by default for primitive numeric types. If a producer was pinned to proto2 semantics and emitted unpacked `repeated double value`, the DeltaForge reader decodes both forms, but byte-level file size comparisons between writers will not match even though data is equivalent.
• `row_path: "sensors"` requires `sensors` to be a repeated field of the root `iot.SensorNetwork` message. Pointing it at a singular field produces one row total, regardless of how many readings exist inside.
• `explode_paths` in one table and `default_repeat_handling: "join_comma"` in the other work because the two tables are independent CREATE statements - do not try to combine both in one flatten config; the explode wins and the comma-join is silently ignored.
• `schema_path` must be re-readable at query time, not just at CREATE time. If `sensor.proto` is deleted or moved after the table is registered, subsequent SELECTs fail with a schema resolution error even though the `.pb` data is intact.
• Changing `sensor_id` from `string = 1` to `string = 2` in a later schema revision makes every historical `.pb` file decode `sensor_id` as empty (field number 1 now maps to nothing). Reserve old field numbers with `reserved 1;` when deleting or renumbering fields.
• `file_metadata` columns (`df_file_name` etc.) are injected by DeltaForge, not by protobuf. They do not appear in `DESCRIBE` output before rows are scanned and cannot be referenced in partition pruning; use them for provenance, not for filter pushdown.

See Also

• demo_protobuf_addressbook_contacts
• demo_protobuf_freight_shipping_manifest