Google launches UDFs to export BigQuery Data as Protocol Buffer Columns

Better Query Performance in high Cardinality Joins

After Google has announced great new features when working with unstructured and semi-structured data such as the new JSON functions and the Like Operator, now they have again released some interesting new functionalities.

Export Data as Protobuf Columns

Protocol buffers (Protobuf) is a binary format created by Google to serialize data between different services. Google has made this protocol as open source. It now also provides support for languages like JavaScript, Java, C#, Ruby & Co. and is also working faster than XML or JSON[1]. If more information is needed, please feel free to check out the article down below.

BigQuery now offers the possibility to merge multiple column values into a single Protobuf value, which has the following benefits[2][3]:

• Object type safety.
• Better compression, data transfer time, and cost in comparison with with JSON.
• Flexibility as most programming languages have libraries to handle Protobuf.
• Less overhead when reading from multiple columns and building a single object.

Google has stated that other column types can also provide type safety, but using Protobuf columns provides a fully typed object, which can reduce the amount of work that needs to be done on the application layer or on another part of the pipeline[2]. Besides these advantages, there are also some limitations to exporting BigQuery data as Protobuf columns. These two points should be taken into consideration[2][3]:

• Protobuf columns are not well indexed or filtered. The approach of searching by the content of the Protobuf columns might be less effective.
• Sorting data in Protobuf format can be difficult.

Query Performance Insights about High Cardinality Joins

Query Performance Insights in High Cardinality Joins is already generally available now. When a query contains a join with non-unique keys on both sides of the join, the size of the output table can be considerably larger than the size of either of the input tables. This particular insight implies the fact that the ratio of output rows to input rows is higher and offers information about these row counts. Hence, one has to check their join conditions to confirm that the increase in the size of the output table is expected. In this regard, it must be said that the usage of cross joins has to be avoided. If the need of using a cross join is really there, one shoud try using a GROUP BY clause to pre-aggregate results, or use a window function. For more information, see Reduce data before using a JOIN.

Sources and Further Readings

[1] auth0, Beating JSON performance with Protobuf (2023)

[2] Google, BigQuery release notes (2023)

[3] Google, Export data as Protobuf columns (2023)