title_wip

# Parse the message
        decoded_message = schemafile_pb2.MessageType()
        decoded_message.ParseFromString(message_data)
        print(text_format.MessageToString(decoded_message, as_utf8=True))
```

Now run the script from the command line. It is recommended to run it from a
python virtual environment, for example using `uv`:

```
uv venv proto-venv
source proto-venv/bin/activate
```

You will need to install the following python libraries:

```
uv pip install --upgrade protobuf
```

Run the script to generate the binary file:

```
python generate_protobuf_data.py
```

Create a ClickHouse table matching the schema:

```
CREATE DATABASE IF NOT EXISTS test;
CREATE TABLE IF NOT EXISTS test.protobuf_messages (
  name String,
  surname String,
  birthDate UInt32,
  phoneNumbers Array(String)
)
ENGINE = MergeTree()
ORDER BY tuple()
```

Insert the data into the table from the command line:

```
cat protobuf_messages.bin | clickhouse-client --query "INSERT INTO test.protobuf_messages SETTINGS format_schema='schemafile:MessageType' FORMAT Protobuf"
```

You can also write the data back to a binary file using the `Protobuf` format:

```
SELECT * FROM test.protobuf_messages INTO OUTFILE 'protobuf_message_from_clickhouse.bin' FORMAT Protobuf SETTINGS format_schema = 'schemafile:MessageType'
```

With your Protobuf schema, you can now deserialize the data which was written out from ClickHouse to file `protobuf_message_from_clickhouse.bin`.

### Reading and writing data using ClickHouse Cloud[​](#basic-examples-cloud "Direct link to Reading and writing data using ClickHouse Cloud")

With ClickHouse Cloud you are not able to upload a Protobuf schema file. However, you can use the `format_protobuf_schema`
setting to specify the schema in the query. In this example, we show you how to read serialized data from your local
machine and insert it into a table in ClickHouse Cloud.

As in the previous example, create the table according to the schema of your Protobuf schema in ClickHouse Cloud:

```
CREATE DATABASE IF NOT EXISTS test;
CREATE TABLE IF NOT EXISTS test.protobuf_messages (
  name String,
  surname String,
  birthDate UInt32,
  phoneNumbers Array(String)
)
ENGINE = MergeTree()
ORDER BY tuple()
```

The setting `format_schema_source` defines the source of setting `format_schema`

Possible values:

- 'file' (default): unsupported in Cloud
- 'string': The `format_schema` is the literal content of the schema.
- 'query': The `format_schema` is a query to retrieve the schema.

### `format_schema_source='string'`[​](#format-schema-source-string "Direct link to format-schema-source-string")

Insert the data into ClickHouse Cloud, specifying the schema as a string, run:

```
cat protobuf_messages.bin | clickhouse client --host <hostname> --secure --password <password> --query "INSERT INTO testing.protobuf_messages SETTINGS format_schema_source='syntax = "proto3";message MessageType {  string name = 1;  string surname = 2;  uint32 birthDate = 3;  repeated string phoneNumbers = 4;};', format_schema='schemafile:MessageType' FORMAT Protobuf"
```

Select the data inserted into the table:

```
clickhouse client --host <hostname> --secure --password <password> --query "SELECT * FROM testing.protobuf_messages"
```

```
Aisha Khan 19920815 ['(555) 247-8903','(555) 612-3457']
Javier Rodriguez 20001015 ['(555) 891-2046','(555) 738-5129']
Mei Ling 19980616 ['(555) 956-1834','(555) 403-7682']
```

### `format_schema_source='query'`[​](#format-schema-source-query "Direct link to format-schema-source-query")

You can also store your Protobuf schema in a table.

Create a table on ClickHouse Cloud to insert data into:

```
CREATE TABLE testing.protobuf_schema (
  schema String
)
ENGINE = MergeTree()
ORDER BY tuple();
```

```
INSERT INTO testing.protobuf_schema VALUES ('syntax = "proto3";message MessageType {  string name = 1;  string surname = 2;  uint32 birthDate = 3;  repeated string phoneNumbers = 4;};');
```

Insert the data into ClickHouse Cloud, specifying the schema as a query to run:

```
cat protobuf_messages.bin | clickhouse client --host <hostname> --secure --password <password> --query "INSERT INTO testing.protobuf_messages SETTINGS format_schema_source='SELECT schema FROM testing.protobuf_schema', format_schema='schemafile:MessageType' FORMAT Protobuf"
```

Select the data inserted into the table:

```
clickhouse client --host <hostname> --secure --password <password> --query "SELECT * FROM testing.protobuf_messages"
```

```
Aisha Khan 19920815 ['(555) 247-8903','(555) 612-3457']
Javier Rodriguez 20001015 ['(555) 891-2046','(555) 738-5129']
Mei Ling 19980616 ['(555) 956-1834','(555) 403-7682']
```

### Using autogenerated schema[​](#using-autogenerated-protobuf-schema "Direct link to Using autogenerated schema")

If you don't have an external Protobuf schema for your data, you can still output/input data in the Protobuf format
using an autogenerated schema. For this use the `format_protobuf_use_autogenerated_schema` setting.

For example:

```
SELECT * FROM test.hits format Protobuf SETTINGS format_protobuf_use_autogenerated_schema=1
```

In this case, ClickHouse will autogenerate the Protobuf schema according to the table structure using function
[`structureToProtobufSchema`](/docs/sql-reference/functions/other-functions#structureToProtobufSchema). It will then use this schema to serialize data in the Protobuf format.

You can also read a Protobuf file with the autogenerated schema. In this case it is necessary for the file to be created using the same schema:

```
$ cat hits.bin | clickhouse-client --query "INSERT INTO test.hits SETTINGS format_protobuf_use_autogenerated_schema=1 FORMAT Protobuf"
```

The setting [`format_protobuf_use_autogenerated_schema`](/docs/operations/settings/formats#format_protobuf_use_autogenerated_schema) is enabled by default and applies if [`format_schema`](/docs/operations/settings/formats#format_schema) is not set.

You can also save autogenerated schema in the file during input/output using setting [`output_format_schema`](/docs/operations/settings/formats#output_format_schema). For example:

```
SELECT * FROM test.hits format Protobuf SETTINGS format_protobuf_use_autogenerated_schema=1, output_format_schema='path/to/schema/schema.proto'
```

In this case autogenerated Protobuf schema will be saved in file `path/to/schema/schema.capnp`.

### Drop protobuf cache[​](#drop-protobuf-cache "Direct link to Drop protobuf cache")

To reload the Protobuf schema loaded from [`format_schema_path`](/docs/operations/server-configuration-parameters/settings#format_schema_path) use the [`SYSTEM DROP ... FORMAT CACHE`](/docs/sql-reference/statements/system#system-drop-schema-format) statement.

```
SYSTEM DROP FORMAT SCHEMA CACHE FOR Protobuf
```

- [Description](#description)- [Example usage](#example-usage)
    - [Reading and writing data](#basic-examples)- [Reading and writing data using ClickHouse Cloud](#basic-examples-cloud)- [`format_schema_source='string'`](#format-schema-source-string)- [`format_schema_source='query'`](#format-schema-source-query)- [Using autogenerated schema](#using-autogenerated-protobuf-schema)- [Drop protobuf cache](#drop-protobuf-cache)

Was this page helpful?