Best Practices for Translating Hive UDF Java Logic to BigQuery SQL

Question

In the process of migrating from Hive to BigQuery, people often face with converting Hive UDFs written in Java to BigQuery's SQL UDFs. What are the best practices or methodologies for translating complex Java logic (like loops and conditionals) into equivalent SQL statements in BigQuery. Is there a structured approach or tool that can assist in this translation, especially for complex Java functions?

Answer 1

The journey begins with the fundamental question of whether the UDF is needed in the new BigQuery data warehouse. We check during the assessment if the function is used in any transformation that will be migrated to the BQ side. If it's not, we're met with a simple resolution—no migration necessary, and we can bask in the simplicity of this outcome. However, if the UDF is indeed needed, we next explore BigQuery's native function library to see if there's an existing feature that can replicate our Hive UDF's capabilities. When a native BigQuery function is available, we adopt it, embracing BigQuery’s built-in efficiency.

In instances where BigQuery doesn't offer a native alternative, we delve into the type of function we're dealing with. For a standard UDF, we evaluate whether it's feasible to reimplement it in BigQuery SQL; if so, we proceed with this SQL-centric approach. If not, we turn to Google Cloud's serverless solutions and leverage the BQ Remote UDFs feature. This option is appealing because we can use Java and keep the core function code as is. If for some reason Remote Functions are not available in our case we can always fall back to using JS UDFs.

When it comes to UDAFs, the decision hinges on the volume of data—specifically, whether the aggregation operates within a bounded scope. For manageable data groupings, we can craft custom aggregations using BigQuery's ARRAY_AGG function. For more unwieldy aggregations, we may need to refactor our approach entirely or shift processing to Google Cloud Dataflow or Dataproc, ensuring scalability and performance.

Lastly, for UDTFs, if we would like to stay in the realm of SQL the path is straightforward: we transition the function to generate elements as an array, utilizing BigQuery’s UNNEST function to flatten the arrays into multiple rows. If this approach does not work we can always go back to using Cloud Dataflow or Dataproc to implement our functionality.

This decision tree not only aids in methodically migrating Hive's custom functions to BigQuery but also ensures that each step taken is in alignment with BigQuery’s optimal practices and architecture, guaranteeing a smooth and efficient transition.

Read this post for the wider context and more information.