CODEWITHSUNDEEP
google-bigquerygoogle-cloud-datalab
Yusuke
Yusuke

Reputation: 103

How to create and use UDF on cloud datalab?

I created a udf called "passthrough" using the command,

%%bigquery udf -m passthrough

function passthrough(row, emit) {
  emit({outputA: row.inputA, outputB: row.inputB});
}

bigquery.defineFunction(
  'passthrough',
  ['inputA', 'inputB'],
  [{'name': 'outputA', 'type': 'string'},
   {'name': 'outputB', 'type': 'string'}],
  passthrough
);

Then, it returned the error.

The JavaScript must declare the input row and output emitter parameters using valid jsdoc format comments. The input row param declaration must be typed as {{field:type, field2:type}} and the output emitter param declaration must be typed as function({{field:type, field2:type}}.

So, I added the jsdoc comments above the passthrough function,

/** 
 * @param {{field:string, field2:string}} row
 * @param function({{field:string, field2:string}}) emit 
 */

and ran the sql command. But it still returned an error "Unknown TVF: passthrough".

%%sql
SELECT outputA, outputB FROM (passthrough(SELECT "abc" AS inputA, "def" AS inputB))

How can I declare the params, and use the UDF later on datalab?

Upvotes: 1

Views: 493

Answers (2)

Graham Wheeler
Graham Wheeler

Reputation: 2814

Your UDF definition should be:

/** 
 * @param {{field:string, field2:string}} row
 * @param function({{field:string, field2:string}}) emit 
 */
function passthrough(row, emit) {
  emit({outputA: row.inputA, outputB: row.inputB});
}

If you want to use the UDF right now, you will need to use an intermediate step in Python code, and this will not work any more when we update (when the way you are currently doing it should be basically right).

You would need to apply the UDF to a table, and do something like this:

import gcp.bigquery as bq
tbl = bq.Query('SELECT "abc" AS inputA, "def" AS inputB').results()
udf_call = passthrough(tbl)

and then in your SQL:

%%sql
SELECT outputA, outputB FROM $udf_call

When the update comes then you can just do what you are doing right now:

%%sql
SELECT outputA, outputB FROM (passthrough(SELECT "abc" AS inputA, "def" AS inputB))

Upvotes: 1

Nikhil Kothari
Nikhil Kothari

Reputation: 5225

Our UDF support that we currently have was geared at an earlier UDFs when they were first introduced in BigQuery. We're actively working on updating the support we have.

You can track some of the progress at our github repo -- https://github.com/GoogleCloudPlatform/datalab ... and you can see a sample of the existing support (that will change) here: https://github.com/GoogleCloudPlatform/datalab/blob/master/dev/notebooks/BigQuery%20-%20JavaScript%20UDFs.ipynb

Upvotes: 0

Related Questions