Is that even possible to decode Protobuf binary message to human readable view from Mysql blob column using only Mysql?

user_2247626 0
Viewed 381

We store a protobuf messages using:

...
payload.toByteArray(),
...

into a longblob column and normally we read them out in a Spring-based application and do handling there.

However, there is a quite straightforward question: is it possible to read those longblob column values just within a SQL query (probably using a stored procedure) avoiding loading data into the application and handling it there?

We need that converter only for one particular message type structure:

message Entry
{
   int64 time = 1;
   repeated Point points = 2;
}
message Point
{
   uint64 status = 1;
   int64 value = 2;
   google.protobuf.Timestamp timestamp = 3;
}

Personally I don't see any possible solution here, however, maybe it's because I don't have enough experience with protobuf.

protocol-buffers mysql
1 Answers

MySQL stored procedures do have all the necessary programming constructs to do this, so theoretically it is possible. However, it will involve quite a bit of programming and is probably not worth doing on database side.

However, here is the rough sketch of what the stored procedures would have to do. Refer to Protobuf encoding documentation for details.

  1. Create a stored procedure read_varint() that will read the first varint from a blob, and returns it as integer and the remaining blob.
  2. Create a stored procedure decode_point() that repeatedly calls read_varint() to take a tag, then calls read_varint() to read field data and assigns it to variable based on tag number.
  3. Create similar decode_timestamp() for the built-in google.protobuf.Timestamp datatype and call it from decode_point() for the timestamp field.
  4. Create a stored procedure decode_entry() that repeatedly calls read_varint() to take a tag, and either decodes time directly or calls decode_point(), depending on the tag value.
  5. Extend all these procedures to skip fields with unknown tag numbers.

Interestingly, your message types only have varint fields. So at the bare minimum, you could just call read_varint() repeatedly to get all the varints to a list. Then each field is always at a specific index. However, I don't recommend this, because it wouldn't be possible to extend it to handle future fields of different types.

user_9147160
Related