Search code examples
javascriptgoogle-bigqueryudf

Is there a way to print or log variable values in a udf function in bigquery?


I have both sql and udf functions that work fine together.

I want to compare record's date which is an integer and has yyyymmdd format and today's date in my determineText() function.

Is there a method like console.log() or document.write() in order to print a variable's value in a udf function in bigquery ?

my udf functions :

function myUdfFunction(row, emit) {

 text = determineText(row.localdate);
 emit({point_id: row.point_id, text: text});
}

function determineText(dateInteger){
  var resultText = "";
  var today = new Date();
  var dd = today.getDate();
  var mm = today.getMonth()+1; //January is 0!
  var yyyy = today.getFullYear();

  if(dd<10) {
      dd='0'+dd
  } 

  if(mm<10) {
      mm='0'+mm
  } 


  today = yyyy + mm + dd;
  today = parseInt(today);
  //console.log("today : "+today+"\ndateInteger : "+dateInteger); // not working
  if (today == dateInteger)
    resultText = "Okay";
  else
    resultText = "Not Okay";

  return resultText;
}


bigquery.defineFunction(
 'myUdfFunction',// Name of the function exported to SQL
 ['point_id', 'min_weight', 'localdate', 'temp_min', 'temp_max', 'precipitation', 'lat', 'lng'],// Names of input columns
 // Output schema 
 [{'name': 'point_id', 'type': 'integer'},  
  {'name': 'text', 'type': 'string'}],
 myUdfFunction// Reference to JavaScript UDF
);

Solution

  • Per https://cloud.google.com/bigquery/user-defined-functions#limitations

    The DOM objects Window, Document and Node, and functions that require them, are unsupported.

    Still, what you need - can be easily accomplished by
    1. testing your UDF outside of BigQuery or,
    2. if for some reason you need test you UDF from within BigQuery, you can use emit function to output anything as a output's fields