Protobuf Address Book - Contact Directory

Read proto3 AddressBook binaries, flatten nested PhoneNumber messages, decode PhoneType enums, and handle sparse/repeated fields across 3 team files (13 contacts, 22 phones).

Syntax

-- Zone and schema
CREATE ZONE IF NOT EXISTS external TYPE EXTERNAL;
CREATE SCHEMA IF NOT EXISTS external.protobuf_demos;

-- Flattened contacts (13 rows) - phones joined as CSV
CREATE EXTERNAL TABLE IF NOT EXISTS external.protobuf_demos.contacts
USING PROTOBUF
LOCATION '/data/protobuf/addressbook'
OPTIONS (
    proto_schema = '/schema/addressbook.proto',
    message_type = 'tutorial.AddressBook',
    proto_flatten_config = '{
        "row_path": "people",
        "include_paths": [
            "people.name", "people.id", "people.email",
            "people.phones.number", "people.phones.type",
            "people.last_updated"
        ],
        "default_repeat_handling": "join_comma",
        "column_mappings": {
            "people.name": "contact_name",
            "people.id": "contact_id",
            "people.phones.number": "phone_numbers",
            "people.phones.type": "phone_types",
            "people.last_updated": "last_updated"
        },
        "decode_enums": true,
        "timestamp_format": "iso8601",
        "max_depth": 10
    }',
    file_metadata = '{"columns":["df_file_name","df_file_modified","df_dataset","df_row_number"]}'
);

-- Exploded phone rows (22 rows) - one row per PhoneNumber
CREATE EXTERNAL TABLE IF NOT EXISTS external.protobuf_demos.contact_phones
USING PROTOBUF
LOCATION '/data/protobuf/addressbook'
OPTIONS (
    proto_schema = '/schema/addressbook.proto',
    message_type = 'tutorial.AddressBook',
    proto_flatten_config = '{
        "row_path": "people",
        "explode_paths": ["people.phones"],
        "include_paths": [
            "people.name", "people.id", "people.email",
            "people.phones.number", "people.phones.type",
            "people.last_updated"
        ],
        "column_mappings": {
            "people.phones.number": "phone_number",
            "people.phones.type": "phone_type"
        },
        "decode_enums": true,
        "timestamp_format": "iso8601",
        "max_depth": 10
    }'
);

-- Verify
ASSERT ROW_COUNT = 13 SELECT * FROM external.protobuf_demos.contacts;
ASSERT ROW_COUNT = 22 SELECT * FROM external.protobuf_demos.contact_phones;
ASSERT VALUE count = 11 WHERE phone_type = 'MOBILE'
SELECT phone_type, COUNT(*) AS count
FROM external.protobuf_demos.contact_phones
GROUP BY phone_type;

-- Cleanup
DROP EXTERNAL TABLE IF EXISTS external.protobuf_demos.contacts WITH FILES;
DROP EXTERNAL TABLE IF EXISTS external.protobuf_demos.contact_phones WITH FILES;

Description

## When to Use Use this demo when you need to ingest Protocol Buffers (proto3) binary files containing nested messages, repeated fields, and well-known types. It uses the canonical Google `AddressBook` schema to cover the common proto-to-table patterns you hit with microservice event streams, mobile exports, or gRPC payload archives. ## What You Will Learn 1. Create a `CREATE EXTERNAL TABLE ... USING PROTOBUF` against a `.proto` schema and select a specific `message_type` (`tutorial.AddressBook`). 2. Set `row_path` to pivot a repeated top-level field (`people`) into the row axis. 3. Choose a repeat strategy: `default_repeat_handling = 'join_comma'` for a flat contacts view versus `explode_paths` for one row per `PhoneNumber`. 4. Decode enums to string labels via `decode_enums = true` (PhoneType 0/1/2 becomes MOBILE/HOME/WORK). 5. Convert `google.protobuf.Timestamp` into ISO 8601 strings with `timestamp_format = 'iso8601'`. 6. Rename deep proto paths into flat column names using `column_mappings`. 7. Attach file provenance columns (`df_file_name`, `df_file_modified`, `df_dataset`, `df_row_number`) via the `file_metadata` option. 8. Handle proto3 sparse semantics: an empty `repeated phones` list and a default-valued empty `email` string. ## Prerequisites - The three `.pb` files under `data/` and the `addressbook.proto` schema file. - A writable external zone (defaults to `external`). - Familiarity with `CREATE EXTERNAL TABLE`, `ASSERT`, and schema-qualified table names.

Pitfalls

• In proto3 every scalar has an implicit default (empty string, 0, false). Maria Schmidt's missing email arrives as `''` not NULL - filters must use `(email IS NULL OR email = '')` as the demo does.
• A missing repeated field is indistinguishable from an empty repeated field on the wire. Luis Hernandez's zero-phone contact produces zero rows in `contact_phones` and a NULL/empty `phone_numbers` in `contacts` - do not rely on NULL-vs-empty-list to detect optionality.
• Proto3 does not round-trip field presence for scalars, so evolving a field from `string email = 3` to `optional string email = 3` (editions / proto3 presence) changes the semantics of `email IS NULL` downstream - plan the schema change in both writer and reader.
• Enum tag numbers are the wire format, not the names. Renaming `MOBILE = 0` to `CELL = 0` in the `.proto` silently relabels every historical row when `decode_enums = true`; add new enum values instead of renaming existing ones.
• Enum zero value is the default for proto3 enums. A Person written without a `type` set on a PhoneNumber decodes to `MOBILE` (tag 0) even if that was never the intent - reserve tag 0 as `PHONE_TYPE_UNSPECIFIED` in new schemas.
• `google.protobuf.Timestamp` is a message, not a scalar - if you forget `timestamp_format = 'iso8601'` you will get a struct column, not a string, and downstream `ORDER BY last_updated` will compare seconds/nanos lexicographically.
• `row_path` must point at a repeated field. Pointing it at a singular message (e.g. the outer `AddressBook`) will produce a single row regardless of how many `Person` messages are inside.
• `column_mappings` keys must exactly match entries in `include_paths` - a typo silently falls back to the default flattened name (e.g. `people_name`) and the assertion on `contact_name` will fail.
• The `.proto` file must be reachable from the engine at query time via `proto_schema`, not just at CREATE time. Moving or renaming the schema file breaks every subsequent read.
• Field numbers, not field names, are the wire format. Changing `string email = 3` to `string email = 7` in the schema renders every previously-written `.pb` file unreadable for that column even though the name is unchanged.

See Also

• demo_protobuf_freight_shipping_manifest
• demo_protobuf_sensor_network