1

In this other question https://stackoverflow.com/a/36145155/2259571 a code example was posted that defines a Javascript UDF function inline, not in the BigQuery UI UDF Editor, not in a bq command-line --udf_resource option, but loaded directly from the text of the SQL query.

Where in the BigQuery documentation can I find more info about how this is done?

JS(...) looks like a function but I cannot find it in the documentation at https://cloud.google.com/bigquery/query-reference I also cannot find anything about this construct in https://cloud.google.com/bigquery/user-defined-functions Am I just missing it? Or is it undocumented?

Here is a simplified version of the query (this runs in the BigQuery UI and in the bq command line tool):

SELECT outputA
FROM JS(
// input table
(
  SELECT text2 as inputA 
  FROM 
    (SELECT 'mikhail' AS text2),
    (SELECT 'mike' AS text2),
    (SELECT 'michael' AS text2),
    (SELECT 'javier' AS text2),
    (SELECT 'thomas' AS text2)
)

// input columns
, inputA

// output schema
, "[{name: 'outputA', type:'string'}]"

// function
, "function(r, emit) {

    emit({
      outputA: 'XX ' + r.inputA + ' XX'
    });

  }"
)

Output:

outputA
XX mikhail XX
XX mike XX
XX michael XX
XX javier XX
XX thomas XX
Community
  • 1
  • 1
Emery Lapinski
  • 1,572
  • 18
  • 26

2 Answers2

2

I see now what you are looking for.
Looks like this is not available in google bigquery documentation

But the example you got - pretty much gives you the structure of such use - nothing is missed - everything else is in link you already know - https://cloud.google.com/bigquery/user-defined-functions - and still applicable and the only you need

As of me personally - i've learned about inline js udf relatively long ago from below links (just few of them)
http://www.slideshare.net/BigDataSpain/thomas-park-hands-on-with-big-query-javascript-udfs-bigdataspain-2014
https://www.youtube.com/watch?v=6TYA6hy44Jo
https://www.youtube.com/watch?v=GrD7ymUPt3M (go to 28+ min)

Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
2

Update 2022-03-31: BigQuery recommends using standard SQL, which includes the following syntax for inline JS UDFs:

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

The inline JS syntax is the "alpha" syntax and is deliberately left undocumented. We currently have no plans to remove or change this functionality, but it's also not an officially supported feature.

We do have plans to provide official support for inline JS UDFs at some point in the future, but with a slightly different syntax.

If you'd like to use this feature despite the caveat above, see Mikhail's answer.

Jeremy Condit
  • 6,766
  • 1
  • 28
  • 30
  • Thank you Jeremy! Good to know. I didn't know it is still alpha – Mikhail Berlyant Mar 24 '16 at 19:30
  • 1
    Just to be clear, BQ's JS UDFs are fully launched and generally available as documented at https://cloud.google.com/bigquery/user-defined-functions. It's only the inline syntax that is not officially supported. We changed the API prior to the final launch but left the old syntax to avoid breaking early users. – Jeremy Condit Mar 25 '16 at 02:43
  • yes. that is how i read your answer. thank you, again for leaving alpha syntax available! – Mikhail Berlyant Mar 25 '16 at 03:16
  • I can't promise that we'll continue to support the inline "js(...)" syntax forever, but we'll give users ample advance notification time before deprecating it. That said, I would strongly encourage you to **not** generate new queries that use it, to avoid a potential future migration. – thomaspark Mar 25 '16 at 22:20
  • Hi, seems this JS inline function is removed? I receive "table-valued function not found: JS" error – Lan Si Mar 31 '22 at 06:55
  • Not sure about the syntax mentioned here, but these days we recommend standard SQL for all use cases, which includes the following inline UDF syntax: https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions – Jeremy Condit Mar 31 '22 at 17:15