4

We use protocol buffers(protobuf) to define our schemas internally and have translators to translate to various other internal representations such as JSON Schema(Protobuf to JSON schema converter) so as to have protobuf schemas to be the source of truth.

We are also planning on creating SQL DDLs from the protobuf schemas and couldn't find a reliable, well supported converter for that. Some of the ones we have found such as jsonutils don't seem to be active.

SQL to protobuf generators are plenty(Example: https://github.com/jackskj/protoc-gen-map/)

We want a simple converter to translate protobuf schemas to CREATE statements on protobuf schema creation and potentially ALTER statements when new columns are added to protobuf schema(we don't support changing field types, or deleting fields in our protobuf schemas) in SnowSQL. Nested messages can be stored as variant and doesn't have to be flattened out. Are there any tools out there or should we write a custom tool for this?

message Foo {
  string uuid = 1;
  MessageType1 message_type1 = 2;
  MessageType2 message_type2 = 3;
  google.protobuf.Timestamp updated_at = 4;
  float some_num = 5;
}

gets translated to

CREATE TABLE FOO (
    uuid string,
    message_type1 variant,
    message_type2 variant,
    updated_at TIMESTAMP_LTZ(9),
    some_num float
);

Update to a schema

message Foo {
  string uuid = 1;
  MessageType1 message_type1 = 2;
  MessageType2 message_type2 = 3;
  google.protobuf.Timestamp updated_at = 4;
  float some_num = 5;
  string new_field = 6;
}

will translate to

ALTER TABLE FOO ADD COLUMN new_field string;

or

CREATE TABLE FOO (
    uuid string,
    message_type1 variant,
    message_type2 variant,
    updated_at TIMESTAMP_LTZ(9),
    some_num float,
    new_field string
);
maverickz
  • 143
  • 1
  • 7
  • We are looking to solve this same problem. Were you able to find any existing solutions, or did you end up writing a custom tool? I think writing a custom protoc plugin could accomplish this, and would also allow for custom annotations to designate additional information (primary keys, etc.). – Jeff Mar 02 '22 at 15:56
  • I ended up writing a custom tool to translate protobuf message to SQL. We use a few message/field options in this case. We can accomplish more via these options, as suggested by you. – maverickz Mar 08 '22 at 13:16
  • @maverickz is it open source? How has the experience been so far? – Developer Jun 01 '22 at 20:30
  • @Pristan since we couldn't find any open source tool/lib, I wrote a custom proto to DDL converter myself, no issues so far since our schema evolutions are append only. – maverickz Jun 02 '22 at 22:35
  • @maverickz That's great! You should consider publishing that as an open source tool! – Dhwanil shah Sep 08 '22 at 05:16

0 Answers0