Google Standard SQL UDF - writing to BigQuery

0
votes

My question is regarding writing to BigQuery from BigQuery Standard SQL UDF.

I have a requirement that I have to do some calculations from within Standard SQL. And after those calculation I have to save those calculations data to BigQuery.

What is the standard way to insert the data into BigQuery from within Standard SQL UDF?

2
google-bigquery

2 Answers

2
votes

What is the standard way to insert the data into big-query from within Standard SQL UDF

There is no way to insert data into BigQuery from within UDF itself
Think of UDF as of any other regular FUNCTION but with the exception that you can create your own one using JavaScript or SQL.
As you might know - these is no any BigQuery functions that allow saving to BigQuery.
Rather you can save the result of query into destination table - that is a standard way in BigQuery

Below are related UDF limitations that gives you idea what is not doable from within UDF

  • The DOM objects Window, Document and Node, and functions that require them, are unsupported.
  • JavaScript functions that rely on native code are unsupported.
  • Bitwise operations in JavaScript handle only the most significant 32 bits.
  • Because of their non-deterministic nature, queries that invoke user-defined functions cannot use cached results.
  • You cannot reference a table in a UDF.

see more at https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions

2
votes

Basically you can use UDF to emit one or more rows. In Standard UDF you must emit Array combined with Struct if you want to emit rows.

There is an example in the Migration guide that add data to non existing columns.

scroll down to: Functions operate on values rather than rows. In legacy SQL, JavaScript functions operate on rows from a table. In standard SQL, as in the example above, JavaScript functions operate on values. To pass a row value to a JavaScript function using standard SQL, define a function that takes a struct of the same row type as the table.

If you end up emitting a struct that holds repeated data, you can write the query result into a new table. That's your insert.

What this means is that you need to create your query results in such way, that the results of the query are written to a destination table, and that becomes your insert. As BQ doesn't offer a strict INSERT statement from UDF.